精华内容
下载资源
问答
  • 结构化查询语言(SQL)是用于存储,操作和检索许多数据库中数据语言。 它是许多关系数据库管理系统标准语言,这些关系数据库管理系统是世界各地组织使用一种数据库。 这些类型的关系数据库管理系统用于将...
  • 目前已成为关系数据库的标准语言。 3.1.2 SQL特点 综合统一 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式 语言简洁,易学易用 3.1.3 SQL的基本概念 3.3 数据定义 3.3.1

    数据库 关系数据库标准语言

    一、SQL概貌及特点

    1.1 SQL简介

    • 定义:结构化查询语言SQL(Structured Query Language)是一种介于 关系代数与 关系演算之间的语言,其功能包括查询、操纵、定义和控制四个方面,是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。

    1.2 SQL特点

    1. 综合统一
    2. 高度非过程化
    3. 面向集合的操作方式
    4. 以同一种语法结构提供两种使用方式
    5. 语言简洁,易学易用

    1.3 SQL的基本概念

    二、数据定义

    2.1 基本表的定义、删除与修改

    1. 定义基本表

      CREATE TABLE <表名>(
      	<列名><数据类型>[列级完整性约束条件] 
      	[,<列名><数据类型>[列级完整性约束条件]]
      	... 
      	[,<表级完整性约束条件>] 
      );
      
    2. 修改基本表

      ALTER TABLE <表名>
      [ADD <新列名><数据类型>[完整性约束]]
      [DROP<完整性约束名>]
      [MODIFY<列名> <数据类型><数据类型>]; 
      
    3. 删除基本表

      DROP TABLE<表名> 
      

    2.2 索引的建立与删除

    1. 建立索引

      CREATE [UNIQUE][CLUSTER] INDEX <索引名> 
      ON <表名> (
      	<列名>[<次序>]
          [,<列名>[<次序>]]
          ...
      ); 
      
    2. 修改索引

      ALTER INDEX <旧索引名> RENAME TO <新索引名>
      
    3. 删除索引

      DROP INDEX<索引名>;
      

    三 数据查询

    3.1 单表查询

    1. 选择表中的若干列
      (1)查询指定列
      【例】:查询全体学生的学号和姓名

      SELECT SNO,SNAME FROM STUDENT;
      

      (2)查询全部列
      【例】:查询全体学生的详细信息

      SELECT * FROM STUDENT;  
      

      (3)查询经过计算的值,即目标列也可以是表达式

      【例】:查询全体学生的姓名以及出生年月

      SELECT SANME,2014-SAGE FROM STUDENT;
      

      注意:2014-SAGE BIRTHDAY 指定别名;

    2. 选择表中的若干元组
      (1)消除取值重复的行,使用DISTINCTDISTINCT实现,没有指定,则默认为ALLALL
      【例】:查询选修了课程的学生学号

      SELECT DISTINCT SNO FROM SC
      

      (2)查询满足条件的元组,使用WHEREWHERE子句实现;

      条件 谓词
      比较 =,>,>=,<=,!=,<>,!>,!<,NOT...=,>,>=,<=,!=,<>,!>,!<,NOT...
      确定范围 BETWEEN AND, NOT BETWEEN AND
      确定集合 IN, NOT IN
      字符匹配 LIKE, NOT LIKE
      空值 IS NULL, IS NOT NULL
      多重条件 AND, OR

      ①比较大小
      【例】:查询计算机全体学生的名单;

      SELECT SANME FROM STUDENT WHERE SDEPT='CS';
      

      【例】:查询考试不及格的学生学号;

      SELECT DISTINCT SNO FROM SC WHEREGRADE<60
      

      ②确定范围
      【例】:查询年龄在20~23岁(包括20和23岁)的学生姓名,系名和年龄;

      SELECT SNAME,SDEPT,SAGE FROM STUDENT WHERE SAGE BETWEEN 20 AND 23;
      

      ③确定集合
      【例】:查询计算机系、数学系、信息系学生的姓名;

      SELECT SNAME FROM STUDENT WHERE SDEPT IN('CS','MA','IS');
      

      ④字符匹配:谓词LIKELIKE可以用来进行字符匹配

      [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']
      
      通配符 功能
      _ 匹配任意一个字符
      % 匹配0个或多个字符
      [ ] 匹配[ ]中的任意一个字符
      [ ^ ] 不匹配[ ]中的任意一个字符

      【例】:查询所有姓刘的学生的姓名、学号和性别;

      SELECT SNAME,SNO,SEX FROM STUDENT WHERE SNAME LIKE '刘%';
      

      ⑤涉及空串的查询
      【例】:查询缺少成绩的学生的学号和相应的课程号

      SELECT SNO,CNO FROM SC WHERE GRADE IS NULL;
      

      ⑥多重条件查询:AND的优先级高于OR,但用户可以用括号改变优先级;
      【例】:查询计算机系年龄在20岁以下的学生姓名;

      SELEC SNAME FROM STUDENT WHERE SDEPT = 'CS' AND SNAME<20;
      
    3. ORDER BY字:用户可以对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认为升序;
      【例】:查询全体学生,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列;

      SELECT * FROM STUDENT ORDER BY SDEPT,SAGE DESC;
      
    4. 聚集函数

      功能
      count() 统计元组个数
      sum() 计算数据和
      max() 计算最大值
      min() 计算最小值
      avg() 计算平均值

      【例】:查询选修了课程的学生人数;

      SELECT COUNT(DISTINCT SNO) FROM SC;
      
    5. GROUP BY字句:将查询结果按某一列或多列的值分组,值相等为一组;
      【例】:求各个课程号及相应的选课人数;

      SELECT CNO,COUNT(SNO) FROM SC GROUP BY CNO;
      

      【例】:查询选修了三门以上选修课的学生学号;

      SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*)>=3;
      

      【例】:查询平均成绩大于等于90分的学生学号和平均成绩;

      SELECT SNO,AVG(GRADE) FROM SC GROUP BY SNO HAVING AVG(GRADE)>=90;
      

    3.4.2 连接查询

    1. 等值与非等值连接查询:用来连接两个表的条件称为连接条件或连接谓词;
      【例】:查询选修2号课程且成绩在90分以上的所有学生的学号和姓名;

      SELECT STUDENT.SNO,SNAME FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND SC.CNO='2' AND SC.GRADE>90;
      
    2. 自身连接:连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,这种连接称为表的自身连接;
      【例】:查询一门课的间接先选课;
      为此,要为Coures表取两个别名,一个是FIRST,另一个是SECOND

      SELECT FIRST.CNO,SECOND.CPNO FROM COURES FIRST,COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO;
      
    3. 外连接:在通常的连接操作中,只有满足连接条件的元组才能作为结果输出。但是,有时也需输出那些不满足连接条件的元组,这个时候就需要使用外连接(Outer join);
      (1)左外连接(LEFT [OUTER] JOIN)只将左边关系R要舍弃的元组保留;
      (2)右外连接( RIGHT [OUTER] JOIN )只将右边关系S要舍弃的元组保留;
      语法形式一:

      SELECT fieldlist
      FROM table1 left [right] [outer] join table2
      ON table1.column=table2.column
      

      语法形式二:
      左外连接

      SELECT  fieldlist
      FROM table1 ,table2
      Where  table1.column=table2.column(+)
      

      右外连接

      SELECT fieldlist
      FROM table1 ,table2
      Where  table1.column(+)=table2.column
      
    4. 多表连接:连接操作除了两个表,还可以两个以上的表进行连接;

    3.4.3 嵌套查询

    1. 带有IN谓词的字句查询: 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接,判断某个属性列值是否在子查询的结果中;
      【例】:查询与“刘晨”在同一个系学习的学生
      (1)确定“刘晨”所在系名;

      SELECT SDEPT
      FROM STUDENT
      WHERE SNAME='刘晨';
      

      (2)查找所有CS系学习的学生;

      SELECT SNO,SNAME,SDEPT
      FROM STUDENT
      WHERE SDEPT ='CS';
      

      (3)构造嵌套结构;

      SELECT SNO,SNAME,SDEPT
      FROM STUDENT
      WHERE SDEPT IN(
      		SELECT SDEPT
      		FROM STUDENT
      		WHERE SNAME='刘晨');
      

      本例中子查询的查询条件不依赖父查询,称为不相关子查询

    2. 带有比较运算符的子查询:带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时,可以用>、 <、 =、 >=、<=、!=或<>等比较运算符;
      【例】:找出每个学生超过他自己选修课程平均成绩的课程号;

      SELECT SNO,CNO
      FROM SC X
      WHERE GRADE>=(
      		SELECT AVG(GRADE)
      		FROM SC Y
      		WHERE Y.SNO=X.SNO);
      

      X Y表是CS表的别名,本例是一个相关子查询。求解相关子查询,子查询不能一次求解出了。内层查询与外层查询有关,因此必须反复求值;

    3. 带有ANY(SOME)或ALL谓词的子查询:子查询返回单值时可以用比较运算符。而使用ANY或ALL谓词时则必须同时使用比较运算符;

      语义
      > ANY 大于子查询结果中的某个值
      < ANY 小于子查询结果中的某个值
      >= ANY 大于等于子查询结果中的某个值
      <= ANY 小于等于子查询结果中的某个值
      <= ANY 小于等于子查询结果中的某个值
      = ANY 等于子查询结果中的某个值

      【例】:查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄;

      SELECT SNAEM,SAGE
      FROM STUDENT
      WHERE SAGE<ANY(
      		SELEC AGE
      		FROM STUDENT
      		WHERE SDEPT='CS')
      AND SDEPT <> 'CS';
      
    4. 带有EXISTS谓词的子查询:EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据,它只产生逻辑真值“true”或逻辑假值“false”;
      【例】:查询所有选修1号课程的学生姓名;

      SELECT SNAME 
      FROM STUDENT
      WHERE EXISTS(
      		SELECT * FROM SC
      		WHERE SNO=STUDENT.SNO AND CNO='1');
      

    3.4.4 集合查询

    • 集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。使用UNION将多个查询结果合并起来形成一个完整的查询结果时,系统会自动去掉重复的元组。

    四、数据更新

    4.1 插入数据

    1. 插入元组

      INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] VALUES (<常量1> [,<常量2>]...); 
      
    2. 插入子查询结果

      INSERT INTO <表名> [(<属性列1> [,<属性列2>...)] 
      子查询;
      

      【例】:对于每一个系,求学生的平均年龄,并把结果存入数据库

      INSERT INTO DEPT_AGE(SDEPT,AVG_AGE)
      SELECT STUDENT
      FROM STUDENT
      GROUP BY SDEPT;
      

    4.2 修改数据

    • 修改操作又称为更新操作,其语句的一般格式为:

      UPDATE <表名>
      SET <列名>=<表达式>[,<列名>=<表达式>]...
      [WHERE <条件>];
      
    1. 修改某一个元组的值
      【例】:将学生201215121的年龄改为22岁

      UPDATE STUDENT
      SET AGE=22
      WHERE SNO='201215121';
      
    2. 修改多个元组的值
      【例】:将所有学生的年龄增加1岁;

      UPDATE STUDENT 
      SET SAGE=SAGE+1;
      
    3. 带子查询的修改语句
      【例】:将计算机系全体学生的成绩置零;

      UPDATE SC
      SET GRADE=0
      WHERE SNO IN(
      		SELECT SNO
      		FROM STUDENT
      		WHERE SDEPT='CS');
      

    4.3 删除数据

    • 删除语句的一般格式为:

      DELETE  
      FROM <表名>
      [WHERE <条件>]; 
      

      DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。

    1. 删除某一个元组的值
      【例】:删除学号为201215128的学生记录

      DELETE
      FROM STUDENT
      WHERE SNO='201215128';
      
    2. 删除多个元组的值
      【例】:删除所有学生的选课记录;

      DELETE
      FROM SC
      
    3. 带子查询的删除语句
      【例】:删除计算机系所有学生的选课记录;

      DELETE
      FROM SC
      WHERE SNO IN(
      		SELECT SNO
      		FROM STUDENT
      		WHERE SDEPT='CS';
      

    五、视图

    • 视图的特点主要作用在于“查询”
      1. 视图能够简化用户的操作;
      2. 视图使用户能以多种角度看待同一数据;
      3. 视图对重构数据库提供了一定程度的逻辑独立性;
      4. 视图能够对机密数据提供安全保护;

    5.1 建立视图

    1. 语法

      CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
      AS <子查询> 
      [WITH CHECK OPTION]; 
      
    2. 说明:
      (1)视图的属性名(列名)缺省为子查询结果中的属性名,也可以显式指明;
      (2)with check option指明当对视图进行insert,update时,要检查是否满足视图定义中的条件;
      (3)视图一旦建立,可以同基本表一样使用.对视图的操作最终被转换为对基本表的操作。

    3. 例子

         CREATE VIEW IS _Student  
         AS 
         SELECT Sno,Sname,Sage,sdept
         FROM student  
         WHERE Sdept ='IS' 
         WITH CHECK OPTION;
      

    5.2 删除视图

    1. 语法:

      DROP   VIEW <视图名>;
      
    2. 一个视图被删除后,由此视图导出的其他视图也将失效,用户应该使用DROP VIEW语句将他们一一删除。

    5.3 查询视图

    • 将对视图的查询转换为对基本表的查询的过程称为视图的消解(View Resolution)。

    5.4 更新视图

    • 转换为等价的对基本表的更新;
    展开全文
  • 在模糊理论基础上,提出了将权重概念引入数据库模糊查询中,使用户对查询中各个属性相对重视程度得以体现。为每条记录提供了一个匹配度,按匹配度降序输出结果,方便用户选择。权重和匹配度都是语言变量[7]...
  • 关系数据库系统的查询处理 一、关系数据库系统的查询处理 1.查询处理步骤 关系数据库管理系统查询处理阶段 : 1)查询分析 :对查询语句进行扫描、词法分 析和语法分析 词法分析:从查询语句中识别出正确的语言符号...

    数据库从入门到精通:戳我

    关系数据库系统的查询处理

    一、关系数据库系统的查询处理
    1.查询处理步骤
    关系数据库管理系统查询处理阶段 :
    1)查询分析 :对查询语句进行扫描、词法分 析和语法分析

    • 词法分析:从查询语句中识别出正确的语言符号
    • 语法分析:进行语法检查

    2)查询检查

    • 查询检查的任务
      合法权检查
      视图转换
      安全性检查
      完整性初步检查
    • 根据数据字典中有关的模式定义检查语句中的数据库对象,如关系名、属性名是否存在和有效
    • 如果是对视图的操作,则要用视图消解方法把对视图的操作转换成对基本表的操作
    • 根据数据字典中的用户权限和完整性约束定义对 用户的存取权限进行检查
    • 检查通过后把SQL查询语句转换成内部表示,即等价的关系代数表达式
    • 关系数据库管理系统一般都用查询树,也称为语法分析树来表示扩展的关系代数表达式。

    3)查询优化:选择一个高效执行的查询处理策略
    查询优化分类:

    • 代数优化/逻辑优化:指关系代数表达式的优化
    • 物理优化:指存取路径和底层操作算法的选择

    查询优化的选择依据:

    • 基于规则(rule based)
    • 基于代价(cost based)
    • 基于语义(semantic based)

    4)查询执行

    • 依据优化器得到的执行策略生成查询执行计划
    • 代码生成器(code generator)生成执行查询计划 的代码
    • 两种执行方法:自顶向下;自底向上。

    2.实现查询操作的算法示例
    1)选择操作的实现
    全表扫描方法 (Table Scan)

    • 对查询的基本表顺序扫描,逐一检查每个元组是否满足 选择条件,把满足条件的元组作为结果输出。
    • 适合小表,不适合大表。

    索引扫描方法 (Index Scan)

    • 适合于选择条件中的属性上有索引(例如B+树索引或Hash索引)。
    • 通过索引先找到满足条件的元组主码或元组指针,再通过元组指针直接在查询的基本表中找到元组。

    2)连接操作的实现
    连接操作是查询处理中最耗时的操作之一。

    []
    SELECT * 
    FROM Student, SC 
    WHERE Student.Sno=SC.Sno;
    

    嵌套循环算法(nested loop join)

    • 对外层循环(Student表)的每一个元组(s),检索内层循 环(SC表)中的每一个元组(sc)
    • 检查这两个元组在连接属性(Sno)上是否相等
    • 如果满足连接条件,则串接后作为结果输出,直到外 层循环表中的元组处理完为止。

    排序-合并算法(sort-merge join 或merge join)

    • 如果连接的表没有排好序,先对Student表和SC表按 连接属性Sno排序
    • 取Student表中第一个Sno,依次扫描SC表中具有相 同Sno的元组
    • 当扫描到Sno不相同的第一个SC元组时,返回 Student表扫描它的下一个元组,再扫描SC表中具有 相同Sno的元组,把它们连接起来
    • 重复上述步骤直到Student 表扫描完

    注意:

    • Student表和SC表都只要扫描一遍
    • 如果两个表原来无序,执行时间要加上对两个表的排序时间
    • 对于大表,先排序后使用排序-合并连接算法执行连接,总的时间一般仍会减少

    索引连接(index join)算法

    • 在SC表上已经建立属性Sno的索引。
    • 对Student中每一个元组,由Sno值通过SC的索引查 找相应的SC元组。
    • 把这些SC元组和Student元组连接起来 循环执行前两步,直到Student表中的元组处理完为止

    Hash Join算法
    前提:假设两个表中较小的表 在第一阶段后可以完全放入内存的hash桶中。

    • 把连接属性作为hash码,用同一个hash函数把Student表和SC表 中的元组散列到hash表中。
    • 划分阶段(building phase, 也称为partitioning phase)
      对包含较少元组的表(如Student表)进行一遍处理
      把它的元组按hash函数分散到hash表的桶中
    • 试探阶段(probing phase,也称为连接阶段join phase)
      对另一个表(SC表)进行一遍处理
      把SC表的元组也按同一个hash函数(hash码是连接属性)进 行散列
      把SC元组与桶中来自Student表并与之相匹配的元组连接起来

    关系数据库系统的查询优化

    • 查询优化在关系数据库系统中有着非常重要的地位
    • 关系查询优化是影响关系数据库管理系统性能的关键因素
    • 由于关系表达式的语义级别很高,使关系系统可以从关系表达式中分析查询语义,提供了执行查询优化的可能性

    一、概述

    • 1.关系系统的查询优化
      是关系数据库管理系统实现的关键技术又是关系系统的优点所在。
      减轻了用户选择存取路径的负担。
    • 2.非关系系统
      用户使用过程化的语言表达查询要求,执行何种记录级的操作,以及操作的序列是由用户来决定的。
      用户必须了解存取路径,系统要提供用户选择存取路径的手段,查询效率由用户的存取策略决定。
      如果用户做了不当的选择,系统是无法对此加以改进的。

    3.查询优化的优点

    • 用户不必考虑如何最好地表达查询以获得较好的效率
    • 系统可以比用户程序的“优化”做得更好
      (1)优化器可以从数据字典中获取许多统计信息,而用户程序则难以获得这些信息。
      (2)如果数据库的物理统计信息改变了,系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序,而重写程序在实际应用中往往是不太可能的。
      (3)优化器可以考虑数百种不同的执行计划,程序员一 般只能考虑有限的几种可能性。
      (4)优化器中包括了很多复杂的优化技术,这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术

    4.关系数据库管理系统通过某种代价模型计算出各种查询执行策略的执行代价,然后选取代价最小的执行方案

    • 集中式数据库
      执行开销主要包括:磁盘存取块数(I/O代价);处理机时间(CPU代价);查询的内存开销
      I/O代价是最主要的
    • 分布式数据库
      总代价=I/O代价+CPU代价+内存代价+通信代价

    5.查询优化的总目标

    • 选择有效的策略
    • 求得给定关系表达式的值
    • 使得查询代价最小(实际上是较小)

    代数优化

    一、关系代数表达式等价变换规则

    • 代数优化策略:通过对关系代数表达式的等价变换来提高查询效率。
    • 关系代数表达式的等价:指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的。
    • 两个关系表达式E1和E2是等价的,可记为E1≡E。

    常用的等价变换规则:
    1.连接、笛卡尔积交换律
    设E1和E2是关系代数表达式,F是连接运算的条件,则有
    在这里插入图片描述
    2.连接、笛卡尔积的结合律
    设E1,E2,E3是关系代数表达式,F1和F2是连接运算的条件
    在这里插入图片描述
    3.投影的串接定律
    在这里插入图片描述

    • E是关系代数表达式
    • Ai(i=1,2,…,n),Bj(j=1,2,…,m)是属性名
    • {A1,A2,…,An}构成{B1,B2,…,Bm}的子集

    4.选择的串接定律
    在这里插入图片描述

    • E是关系代数表达式,F1、F2是选择条件
    • 选择的串接律说明选择条件可以合并,这样一次就可检查全部条件

    5.选择与投影操作的交换律
    在这里插入图片描述

    • 选择条件F只涉及属性A1,…,An。
    • 若F中有不属于A1,…,An的属性B1,…,Bm有更一般规则:
      在这里插入图片描述
      6. 选择与笛卡尔积的交换律
    • 如果F中涉及的属性都是E1中的属性,则
      在这里插入图片描述
    • 如果F=F1∧F2,并且F1只涉及E1中的属性,F2只涉及E2中的属性,则由上面的等价变换规则1,4,6可推出:
      在这里插入图片描述
    • 若F1只涉及E1中的属性,F2涉及E1和E2两者的属性,则仍有
      在这里插入图片描述
      它使部分选择在笛卡尔积前先做。

    7. 选择与并的分配律
    设E=E1∪E2,E1,E2有相同的属性名,则
    在这里插入图片描述
    8. 选择与差运算的分配律
    若E1与E2有相同的属性名,则
    在这里插入图片描述
    9. 选择对自然连接的分配律
    在这里插入图片描述
    F只涉及E1与E2的公共属性

    10. 投影与笛卡尔积的分配律
    设E1和E2是两个关系表达式,A1,…,An是E1的属性,B1,…,Bm是E2的属性,则
    在这里插入图片描述
    11. 投影与并的分配律
    设E1和E2有相同的属性名,则
    在这里插入图片描述
    二、查询树的启发式优化
    典型的启发式规则
    (1)选择运算应尽可能先做
    在优化策略中这是最重要、最基本的一条。
    (2)把投影运算和选择运算同时进行
    如有若干投影和选择运算,并且它们都对同一个关系操作,则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。
    (3) 把投影同其前或其后的双目运算结合起来,没有必要为了去掉某些字段而扫描一遍关系。
    (4) 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算,连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间。
    (5) 找出公共子表达式

    • 如果这种重复出现的子表达式的结果不是很大的关系
    • 并且从外存中读入这个关系比计算该子表达式的时间少得多
    • 则先计算一次公共子表达式并把结果写入中间文件是合算的。
    • 当查询的是视图时,定义视图的表达式就是公共子表达式的情况

    物理优化

    • 代数优化改变查询语句中操作的次序和组合,不涉及底层的存取路径
    • 对于一个查询语句有许多存取方案,它们的执行效率不同, 仅仅进行代数优化是不够的
    • 物理优化就是要选择高效合理的操作算法或存取路径,求得优化的查询计划

    物理优化方法

    • 基于规则的启发式优化
      启发式规则是指那些在大多数情况下都适用,但不是在每种情况下都是适用的规则。
    • 基于代价估算的优化
      优化器估算不同执行策略的代价,并选出具有最小代价的执行计划。
    • 两者结合的优化方法
      常常先使用启发式规则,选取若干较优的候选方案,减少代价估算的工作量
      然后分别计算这些候选方案的执行代价,较快地选出最终的优化方案

    一、基于启发式规则的存取路径选择优化
    1.选择操作的启发式规则
    对于小关系,使用全表顺序扫描,即使选择列上有索引
    对于大关系,启发式规则有:
    (1)对于选择条件是“主码=值”的查询

    • 查询结果最多是一个元组,可以选择主码索引
    • 一般的关系数据库管理系统会自动建立主码索引

    (2)对于选择条件是“非主属性=值”的查询,并且选择列上有索引

    • 要估算查询结果的元组数目
    • 如果比例较小(<10%)可以使用索引扫描方法
    • 否则还是使用全表顺序扫描

    (3)对于选择条件是属性上的非等值查询或者范围查询,并且选择列上有索引
    要估算查询结果的元组数目:

    • 如果比例较小(<10%)可以使用索引扫描方法
    • 否则还是使用全表顺序扫描

    (4)对于用AND连接的合取选择条件

    • 如果有涉及这些属性的组合索引
      优先采用组合索引扫描方法
    • 如果某些属性上有一般的索引,可以用索引扫描方法
      通过分别查找满足每个条件的指针,求指针的交集
      通过索引查找满足部分条件的元组,然后在扫描这些元组时判断是否满足剩余条件
    • 其他情况:使用全表顺序扫描

    (5)对于用OR连接的析取选择条件,一般使用全表顺序扫描

    2.连接操作的启发式规则
    (1)如果2个表都已经按照连接属性排序

    • 选用排序-合并算法

    (2)如果一个表在连接属性上有索引

    • 选用索引连接算法

    (3)如果上面2个规则都不适用,其中一个表较小

    • 选用Hash join算法

    (4)可以选用嵌套循环方法,并选择其中较小的表,确切地讲是占用的块数(b)较少的表,作为外表(外循环的表) 。理由:

    • 设连接表R与S分别占用的块数为Br与Bs
    • 连接操作使用的内存缓冲区块数为K
    • 分配K-1块给外表
    • 如果R为外表,则嵌套循环法存取的块数为Br+BrBs/(K-1)
    • 显然应该选块数小的表作为外表

    二、基于代价的优化

    • 启发式规则优化是定性的选择,适合解释执行的系统
      解释执行的系统,优化开销包含在查询总开销之中
    • 编译执行的系统中查询优化和查询执行是分开的
      可以采用精细复杂一些的基于代价的优化方法

    1.统计信息

    基于代价的优化方法要计算查询的各种不同执行方案的执行代价,它与数据库的状态密切相关
    优化器需要的统计信息
    (1)对每个基本表

    • 该表的元组总数(N)
    • 元组长度(l)
    • 占用的块数(B)
    • 占用的溢出块数(BO)

    (2)对基表的每个列

    • 该列不同值的个数(m)
    • 列最大值
    • 最小值
    • 列上是否已经建立了索引
    • 哪种索引(B+树索引、Hash索引、聚集索引)
    • 可以计算选择率(f)
      如果不同值的分布是均匀的,f=1/m。
      如果不同值的分布不均匀,则要计算每个值的选择率,f=具有该值的元组数/N。

    (3)对索引

    • 索引的层数(L)
    • 不同索引值的个数
    • 索引的选择基数S(有S个元组具有某个索引值)
    • 索引的叶结点数(Y)

    2.代价估算示例
    (1)全表扫描算法的代价估算公式

    • 如果基本表大小为B块,全表扫描算法的代价 cost=B
    • 如果选择条件是“码=值”,那么平均搜索代价 cost=B/2

    (2)索引扫描算法的代价估算公式

    如果选择条件是“码=值”

    • 则采用该表的主索引;
    • 若为B+树,层数为L,需要存取B+树中从根结点到叶结点L块,再加上基本表中该元组所在的那一块,所以cost=L+1

    如果选择条件涉及非码属性

    • 若为B+树索引,选择条件是相等比较,S是索引的选择基数(有S个元组满足条件)
    • 满足条件的元组可能会保存在不同的块上,所以(最坏的情况)cost=L+S

    如果比较条件是>,>=,<,<=操作

    • 假设有一半的元组满足条件
    • 就要存取一半的叶结点
    • 通过索引访问一半的表存储块
    • cost=L+Y/2+B/2
    • 如果可以获得更准确的选择基数,可以进一步修正Y/2与B/2

    (3)嵌套循环连接算法的代价估算公式

    嵌套循环连接算法的代价
    cost=Br+BrBs/(K-1)
    如果需要把连接结果写回磁盘
    cost=Br+Br Bs/(K-1)+(Frs*Nr*Ns)/Mrs

    • 其中Frs为连接选择性(join selectivity),表示连接结果元组数的比例
    • Mrs是存放连接结果的块因子,表示每块中可以存放的结果元组数目

    (4)排序-合并连接算法的代价估算公式

    • 如果连接表已经按照连接属性排好序,则
      cost=Br+Bs+(Frs*Nr*Ns)/Mrs
    • 如果必须对文件排序
      还需要在代价函数中加上排序的代价
      对于包含B个块的文件排序的代价大约是
      (2*B)+(2*B*log2B)

    ==查询处理是关系数据库管理系统的核心,查询优化技术是查询处理的关键技术 ==

    展开全文
  • 数据库 - 关系数据库标准语言SQL

    千次阅读 2015-05-05 09:57:12
    SQL(Structured Query Language)结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言SQL特点1.综合统一 集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于...

    SQL(Structured Query Language)

    结构化查询语言,是关系数据库的标准语言
    

    SQL是一个通用的、功能极强的关系数据库语言

    SQL特点

    1.综合统一
    集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体。
    可以独立完成数据库生命周期中的全部活动:
    定义关系模式,插入数据,建立数据库;
    对数据库中的数据进行查询和更新;
    数据库重构和维护
    数据库安全性、完整性控制等
    用户数据库投入运行后,可根据需要随时逐步修改模式,不影响数据的运行。
    数据操作符统一

    非关系数据模型的数据操纵语言“面向过程”,必须制定存取路径
    SQL只要提出“做什么”,无须了解存取路径。
    存取路径的选择以及SQL的操作过程由系统自动完成。
    非关系数据模型采用面向记录的操作方式,操作对象是一条记录
    SQL采用集合操作方式

     操作对象、查找结果可以是元组的集合
     一次插入、删除、更新操作的对象可以是元组的集合

    SQL是独立的语言
    能够独立地用于联机交互的使用方式
    SQL又是嵌入式语言
    SQL能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用

    SQL的基本概念

    基本表
    本身独立存在的表
    SQL中一个关系就对应一个基本表
    一个(或多个)基本表对应一个存储文件
    一个表可以带若干索引
    存储文件
    逻辑结构组成了关系数据库的内模式
    物理结构是任意的,对用户透明
    视图
    从一个或几个基本表导出的表
    数据库中只存放视图的定义而不存放视图对应的数据
    视图是一个虚表
    用户可以在视图上再定义视图

    学生-课程 数据库设计

    学生-课程模式 S-T :    
        学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
        课程表:Course(Cno,Cname,Cpno,Ccredit)
        学生选课表:SC(Sno,Cno,Grade)
    学号 姓名 性别 年龄 所在系
    200215121 张三 21 CS
    200215122 李四 22 MA
    200215123 王五 25 IA

    定义模式

     [例1]定义一个学生-课程模式S-T
                CREATE SCHEMA “S-T” AUTHORIZATION WANG;
                 为用户WANG定义了一个模式S-T
        [例2]CREATE SCHEMA AUTHORIZATION WANG;
                <模式名>隐含为用户名WANG
    

    定义模式实际上定义了一个命名空间
    在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。
    在CREATE SCHEMA中可以接受CREATE TABLE,CREATE VIEW和GRANT子句。
    CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

    [例3]
    CREATE SCHEMA TEST AUTHORIZATION ZHANG 
        CREATE TABLE TAB1(COL1 SMALLINT, 
                                                COL2 INT,
                                                COL3 CHAR(20),
                                                COL4 NUMERIC(103),
                                                COL5 DECIMAL(52)
                                              );
        为用户ZHANG创建了一个模式TEST,并在其中定义了一个表TAB1。
    

    删除模式

    DROP SCHEMA (模式名) (CASCADE|RESTRICT)
    CASCADE(级联)
    删除模式的同时把该模式中所有的数据库对象全部删除
    RESTRICT(限制)
    如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。
    当该模式中没有任何下属的对象时 才能执行。

    [例4]  DROP SCHEMA ZHANG CASCADE;
             删除模式ZHANG
             同时该模式中定义的表TAB1也被删除
    

    基本表的定义、删除与修改

    一、定义基本表

    CREATE TABLE <表名><列名> <数据类型>[ <列级完整性约束条件> ]
          [,<列名> <数据类型>[ <列级完整性约束条件>] ] …
          [,<表级完整性约束条件> ] );

    如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。

    [例5]  建立“学生”表Student,学号是主码,姓名取值唯一。
    
        CREATE TABLE Student          
              (Sno   CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/                  
                Sname  CHAR(20) UNIQUE,     /* Sname取唯一值*/
                Ssex    CHAR(2),
                Sage   SMALLINT,
                Sdept  CHAR(20)
               ); 
    
     [例6] 建立一个“课程”表Course
          CREATE TABLE  Course
                   ( Cno       CHAR(4) PRIMARY KEY,
                     Cname  CHAR(40),            
                     Cpno     CHAR(4) ,                                       
                     Ccredit  SMALLINTFOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
                ); 
    
    [例7]  建立一个“学生选课”表SC
        CREATE TABLE  SC
            (Sno  CHAR(9), 
            Cno  CHAR(4),  
            Grade    SMALLINTPRIMARY KEY (Sno,Cno),  
                         /* 主码由两个属性构成,必须作为表级完整性进行定义*/
            FOREIGN KEY (Sno) REFERENCES Student(Sno),
                        /* 表级完整性约束条件,Sno是外码,被参照表是Student */
            FOREIGN KEY (Cno) REFERENCES Course(Cno)
                       /* 表级完整性约束条件, Cno是外码,被参照表是Course*/
            ); 
    

    数据类型

    SQL中域的概念用数据类型来实现
    定义表的属性时 需要指明其数据类型及长度
    选用哪种数据类型
    取值范围
    要做哪些运算

    模式与表

    每一个基本表都属于某一个模式
    一个模式包含多个基本表
    定义基本表所属模式
    方法一:在表名中明显地给出模式名
    Create table “S-T”.Student(……); /模式名为 S-T/
    Create table “S-T”.Cource(……);
    Create table “S-T”.SC(……);
    方法二:在创建模式语句中同时创建表
    方法三:设置所属的模式
    创建基本表(其他数据库对象也一样)时,若没有指定模式,系统根据搜索路径来确定该对象所属的模式
    RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名
    若搜索路径中的模式名都不存在,系统将给出错误
    显示当前的搜索路径: SHOW search_path;
    搜索路径的当前默认值是:$user, PUBLIC

    展开全文
  • 结构化查询语言简称SQL,是一种特殊目的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件扩展名。 ...

    1,SQL概述

    1.1,SQL产生于发展

    结构化查询语言简称SQL是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名

    SQL86,数据库语言SQL

    1986年美国ANSI批准SQL作为关系数据库语言的美国标准。

    1987ISOSQL采纳为关系数据库语言的国际标准。

    SQL89,具有完整性增强的数据库语言SQL 1989年颁布,增加了对完整性约束的支持
    SQL92,SQL/2 SQL-89的超集,增加了许多新特性,如新的数据类型,更丰富的数据操作,更强的完整性、安全性支持等,标准化文本有600多页,1992年颁布
    SQL99,SQL/3 增加了对面向对象模型的支持,标准化文本有1000多页厚。
    SQL-2003 对于新的ANSI标准(SQL-2003)DBMS声称的兼容有两个级别:核心SQL支持和增强SQL支持。

    1.2,SQL的特点

    综合统一:集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体,语言风格统一,可以独立完成数据库生命周期中的全部活动。定义关系模式,建立数据库,对数据进行查询和更新,数据库重构和维护,数据库完整性安全性控制等。可以在不影响数据库运行的情况下对数据库进行模式的修改。实体及实体间的联系都用关系表示,数据结构的单一性带来操作符的统一,简化了操作。

    高度非过程化:用户只需提出“做什么”,无须告诉“怎么做”,不必了解存取路径,路径的选择及SQL的操作过程由系统自动完成,减轻了用户的负担,有利于提高数据独立性

    面向集合的操作方式:集合SQL采用集合操作方式,操作对象和操作结果都是关系 --- 元组,可以一次插入、删除、更新整个集合的元组。

    两种使用方式,统一的语法结构:SQL既是自含式语言(用户使用),又是嵌入式语言(程序员使用), 不同的使用方式, 但语法结构基本是一致的。

    语言简洁,语法简单,接近英语口语,易学易用:SQL完成核心的功能只用了9个命令动词

    1.3,SQL的基本概念

    SQL语言支持关系数据库三级模式结构。即SQL数据库的体系结构基本上也是三级结构,但术语与传统的关系模型术语不同

     基本表:基本表是独立、存在的表,SQL中一个关系就对应一个基本表(基表);一个基表上可以带多个索引,索引存放在存储文件里;

    视图:视图是一个或多个基表导出的表,数据库中只存放视图定义以及与其关联的基本表名等信息,而不存放视图对应的数据;数据仍存放在导出视图的基本表中,因此视图又称为虚拟表。

    存储文件:由多个基本表、视图、相关索引和存储过程等数据库对象组成;存储文件的逻辑结构对应数据库的内模式。

    1.4,SQL数据库体系结构

    SQL用户可以用SQL语句对基本表和视图进行查询等操作,在用户看来,两者是一样的,都是表。

    一个基本表只能放一个存储文件中;一个存储文件可以放一个或多个基本表。每个存储文件都与外存储器上的一个物理文件对应。一个表可以带若干索引,索引也放在存储文件中

    视图是从一个或几个基本表中导出的表,数据库中只存放视图的定义。

    2,数据定义

    操作对象 操作方式
    创建 删除 修改
    数据库(模式) create database drop database  
    表(关系) create table drop table alter table
    视图 create view drop view  
    索引 create index drop index alter index

    2.1,数据库(模式)的定义与删除

    create database YSY;

    定义模式实际上定义了一个命名空间。

    在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。

    drop database YSY;

    2.2,基本表的定义、删除与修改

    基本单位:https://www.runoob.com/mysql/mysql-data-types.html

    create table YSY(A int primary key,
                     B char(20) not null,
                     C int not null unique,
                     foreign key(C) references YSY2(C));

    模(数据库)式与表:每一个基本表都属于某一个模式;一个模式包含多个基本表

    alert table YSY add schoolName char(20);
    drop table YSY;

    2.3,SQL索引的建立与删除

    索引可以提供多种存取路径,加快查找速度。

    索引优点通过创建唯一索引,可以保证数据库表中每一行数据的唯一性②可以大大加快数据的查询速度,这也是创建索引的最主要的原因。实现数据的参照完整性,可以加速表和表之间的连接。使用分组和排列子句进行数据查询时,也可以显著减少查询中分组和排序的时间

    索引缺点:创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快到达到最大文件尺寸。对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度

    索引设计的原则

    1)索引并非越多越好,一个表中如果有大量的索引,不仅占用大量的磁盘空间,而且会影响INSERTDELETEUPDATE等语句的性能。因为当表中数据更改的同时,索引也会进行调整和更新。

    2)避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

    3)数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果

    4)在条件表达式中经常用到的、不同值较多的列上建立索引,在不同值少的列上不要建立索引。比如在学生表的性别字段上只有两个不同值,因此就无需建立索引。如果建立索引,不但不会提高查询效率,反而会严重降低更新速度。

    5)当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引能够确保定义的列的数据完整性,提高查询速度。

    6)在频繁进行排序或分组(即进行GROUP BYORDER BY操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引

    索引分类
    按照存储结构 聚集索引 数据行的键值在表内排序并存储对应的数据记录,使得数据表物理顺序与索引顺序一致。当以某字段作为关键字建立聚集索引时,表中数据以该字段作为排序根据。因此,一个表只能建立一个聚集索引,但该索引可以包含多个列(组合索引) 
    非聚集索引 完全独立于数据行的结构。数据存储在一个地方,索引存储在另一个地方。非聚集索引中的数据排列顺序并不是表格中数据的排列顺序。
    按照数据唯一行
     
    唯一索引 指索引值必须是唯一的。聚集索引和非聚集索引均可用于强制表内的唯一性,方法是在现有表上创建索引时指定UNIQUE关键字。确保表内唯一性的另一种方法是使用UNIQUE约束。
    全文索引

    可以对存储在数据库中的文本数据进行快速检索。全文索引是一种特殊类型的基于标记的功能性索引。每个表只允许有一个全文索引

    聚集索引和非聚集索引的比较

    性能\种类 聚集索引 非聚集索引
    存取速度
    更新速度
    索引数量 一表一个 一表可有多个
    所需空间
    使用情况

    查询命令的回传结果是以该字段为排序条件

    需要回传局部范围的大量数据

    表格中某字段内容的重复性比较大

    查询所获数据量较少时

    某字段中的数据的唯一性比较高时

    修改一个表的数据时,同时要维护索引

    1,创建表时,创建UNIQUE约束或者PRIMARY KEY约束时,自动创建聚集索引
    2,create unique clustered/noclustered index 索引名称 on 表名(表属性 desc/asc)
    clustered/noclustered:聚集索引/非聚集索引
    unique:可选,是否唯一索引
    desc/asc:倒序/正序
    alter oldIndex rename to newIndex
    drop index indexName

    SQL中的索引是非显式索引,也就是在索引创建以后,用户在索引撤消前不会再用到该索引键的名字,但是索引在用户查询时会自动起作用。

    索引一经建立,就由系统使用和维护,不需用户的干预。不允许用户在数据操作中引用索引。索引如何使用完全由系统决定,这支持数据的物理独立性。

    应该在使用频率高的、经常用于连接的列上建索引。

    一个表上可根据查询需要建立多个索引。索引可提高查询效率,但索引过多则耗费空间,且降低了插入、删除、更新的效率,因此不要建立过多的索引

    2.4,数据字典

    https://shao12138.blog.csdn.net/article/details/109103706#t4

    3,数据查询

    3.1,数据查询

    单列查询 select name from student;
    全部列查询 select * from student;
    部分列查询 select name,number from student;
    设置别名 select name as studentName from sutdent;
    计算列值 select 2020-age from student;
    消除重复项 select distinct sex from student;
    限制查询行数 select top 10 * from student;
    查询满足条件 比较运算符

    select number from student where name='燕双嘤';

    select name from student where age<20;

    确定范围

    select name from student where age between 20 and 23;

    select name from student where age not between 20 and 23;

    确定集合

    select number from student where name in('燕双嘤','滑小稽');

    select number from student where name not in('燕双嘤','滑小稽');

    模糊查询

    % (百分号)  代表任意长度的字符串

    a%b表示以a开头,以b结尾的任意长度的字符串

    select * from student where name like '燕%嘤'

    select * from student where name not like '燕%嘤'

    _ (下横线)  代表任意单个字符

    a_b表示以a开头,以b结尾的长度为3的任意字符串

    select * from student where name like '燕_嘤'

    select * from student where name not like '燕_嘤'

    涉空查询 select * from student where grade is null;
    多重查询 select * from student where name='燕双嘤' and age='21';
    排序 正序 select * from student order by grade asc;正序默认,可省略;
    倒序 select * from student oder by grade desc;

    3.2,聚集函数

    SELECT语句中可以使用统计函数进行统计,并返回统计结果。聚合函数用于处理单个列中所选的全部值,并生成一个结果值。常用的聚合函数(也称统计函数)包括COUNT()AVG()SUM()MAX()、和MIN()

    count():统计元组数目。

    select count(*) from student;

    sum():统计元组的某项和。

    select sum(grade) from student;

    max(),min():统计最大值,最小值。

    select max(age)/min(age) from student;

    3.3,分组查询

    GROUP BY子句用于对表或视图中的数据按字段分组,还可以利用HAVING短语按照一定的条件对分组后的数据进行筛选。

    ★格式:GROUP BY [ALL] 分组表达式 [HAVING 查询条件]

    当使用HAVING短语指定筛选条件时,HAVING短语必须与GROUP BY配合使用

    如果未对查询结果分组,聚集函数将作用于整个查询结果

    对查询结果分组后,聚集函数将分别作用于每个组

    按指定的一列或多列值分组,值相等的为一组

    select count(name) from student group by sex;//查询男女人数
    select number from schoolClass group by number having count(*)>3;//查询有三个课程的学生

    注意:WHERE 子句和HAVING 短语的区别,在于作用的对象不同, WHERE作用于基本表或视图, 从中选择满足条件的元组。HAVING作用于组,从中选择满足条件的组。 HAVING一定要结合GROUP使用。

    3.4,连接查询

    连接类型:等值连接、自然连接、非等值连接。自身连接(一个表自己和自己连接)。内连接(即自然连接)。外连接(左、右、全)。⑤复合条件连接

    等值连接:连接条件是在WHERE子句中给出的,只有满足连接条件的行才会出现在查询结果中

    select student.*,schoolClass.* from student,schoolClass where student.number=schoolClass.number;

    自然连接:公共属性上的等值连接,结果中把目标列中重复的属性列去掉

    select student.number,name,sex,className,grade from student,schoolGrade where student.number=schoolGrade.number;

    非等值连接:等值连接基础上破坏满足条件。

    select student.number,name from student,schoolClass where student.number=schoolClass.number and schoolClass.number='2' and schoolClass.grade > 90;

    自身连接:一个表与其自己连接。

    select first.name,second.number from student first,student second where first.number=second.number;

    外连接:普通连接操作只输出满足连接条件的元组,而外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出。连接分为:左外连接(列出左边关系中所有的元组 )和 右外连接(列出右边关系中所有的元组

    注意:*表示为所在边的表(例中是SC表)增加一个“万能”的行,这个行全部由空值组成,它可以和另一边的表(例中是Student表)中所有不满足连接条件的元组(称失配的元组)进行连接

    左外连接,左边表放*万能行;右外连接,右边表放*万能行

    select student.number,name,sex,age,className,grade from student,schoolClass where student.number=schoolClass.number(*);//左连接
    select student.number,name,sex,age,className,grade from student,schoolClass where student.number(*)=schoolClass.number;//右连接

    T-SQL扩展了连接的形式,引入了JOIN...ON关键字连接形式,从而使表的连接运算得到了增强。

    JOIN提供了多种类型的连接方法:内连接、外连接和交叉连接。

    内连接/普通连接/自然连接 SELECT student.sno,sname,sdept,cno,grade  FROM student JOIN sc ON student.sno=sc.sno
    外连接-左外连接 SELECT student.sno,cno,sname,grade FROM student LEFT JOIN sc ON sc.sno=student.sno
    外连接-右外连接 SELECT student.sno,cno,sname,grade FROM student RIGHT JOIN sc ON sc.sno=student.sno
    交叉连接

    SELECT student.*,sc.* FROM student CROSS JOIN sc

    即两个表的笛卡尔积,返回结果是由第一个表的每行与第二个表的所有行组合后形成的表,因此,数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。交叉连接关键字CROSS JOIN后不跟ON短语引出的连接条件。

    符合条件连接:WHERE子句中可以有多个连接条件

    SELECT student.sno,sname,sdept,cno,grade FROM student,sc WHERE student.sno=sc.sno and grade<60
    

    3.5,嵌套查询

    嵌套查询:将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询

    说明:
    不相关子查询:子查询与主查询独立, 子查询的查询条件不依赖于父查询,子查询只执行一次。子查询在上一级查询处理之前求解,子查询的结果集用于建立其上一级查询的查找条件。
    相关子查询:子查询中涉及主查询的信息,子查询的查询结果依赖于父查询,子查询不只执行一次。
    子查询中不能使用ORDER BY子句, ORDER BY只能对最终结果排序。
    一个涉及多个表的查询要求,往往既可以用连接查询表达,也可以用嵌套子查询表达 --- 多种方式表达查询。
    

    带有IN谓词的子查询 :由于子查询的结果是纪录的集合,故常使用谓词IN来实现

    select SNO from SC where CNO ='1' and SNO in (select SNO from SC where CNO ='2');
    列出选修了1号和2号课程的学生的学号
    

    带有比较运算符的子查询 :使用带有比较运算符的子查询,是当用户能确切知道子查询返回的是单值时,可以在父查询WHERE子句中,使用比较运算符进行比较查询。这种查询可以认为是IN子查询的扩展。

    SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade)FROM SC y WHERE y.Sno=x.Sno);
    找出每个学生超过他选修课程平均成绩的课程号。
    

    带有ANY,SOME,ALL关键字的子查询:ALL代表所有值,ALL指定的表达式要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较的关系是,才返回TRUE,否则返回FALSE

    SOME或ANY代表某些或者某个值,表达式只要与子查询结果集中的某个值满足比较的关系时,就返回TRUE,否则返回FALSE

    SELECT * FROM sc WHERE grade>ALL(SELECT grade FROM sc WHERE sno=(SELECT sno FROM student WHERE sname='王小华'))
    查询考试成绩比王小华同学高的学生信息。
    SELECT * FROM sc WHERE grade >ANY(SELECT grade FROM sc WHERE sno=(SELECT sno FROM student WHERE sname='王小华'))
    比王小华同学所有科目的成绩都要高的学生信息

    带有EXISTS谓词的子查询:EXISTS称为存在量词,WHERE子句中使用EXISTS表示当子查询的结果非空时,条件为TRUE,反之则为FALSEEXISTS前面也可以加NOT,表示检测条件为“不存在”。EXISTS语句与IN非常类似,它们都根据来自子查询的数据子集测试列的值。不同之处在于,EXISTS使用联接将列的值与子查询中的列联接起来,而IN不需要联接,它直接根据一组以逗号分隔的值进行比较。

    SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
    查询所有选修了1号课程的学生姓名
    

    3.6,集合(联合)查询

    集合查询的种类:并操作UNION;交操作INTERSECT;差操作EXCEPT;

    说明:参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同集合操作自动去除重复元组,如果要保留重复元组的话,必须用ALL关键词指明。如果系统没有提供UNION INTERSECT EXCEPT 运算符, 集合的并、交、差运算也可以用其他方法实现

    并:
    SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19;
    SELECT * FROM Student WHERE Sdept =‘CS’ OR Sage<= 19;
    查询计算机科学系的学生及年龄不大于19岁的学生并集。
    交:
    SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19 
    SELECT * from  Student WHERE Sdept =‘CS’ AND  Sage<= 19;
    查询计算机科学系的学生与年龄不大于19岁的学生的交集。
    差:
    SELECT * FROM Student  WHERE Sdept='CS' EXCEPT SELECT  * FROM Student WHERE Sage <=19;
    SELECT * from   Student WHERE Sdept =‘CS’ AND  Sage> 19;
    查询计算机科学系的学生与年龄不大于19岁的学生的差集。/实际上是查询计算机科学系中年龄大于19岁的学生。
    

    4,数据更新

    4.1,插入数据

    插入元组

    INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES('201215128','陈冬','男','IS',18);
    将一个新学生元组(学号:201215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。
    

    插入子查询结果

    insert into EXCE(Sno,AVGGrade)select Sno,avg(Grade)from SC group by Sno having avg(Grade)> 90;
    将平均成绩大于90的学生加入到EXCE表中。
    

    4.2,更新数据

    修改某一个元组的值

    UPDATE Student SET Sage=22WHERE Sno='201215121'; 
    将学生201215121的年龄改为22岁

    修改多个元组的值

    UPDATE Student  SET Sage= Sage+1;
    将所有学生的年龄增加1岁。
    

    带子查询的修改语句

    UPDATE SC SET Grade=0 WHERE Sno IN(SELETE Sno FROM Student WHERE Sdept= 'CS');
    将计算机科学系全体学生的成绩置零。
    

    4.3,删除数据

    删除一个元组

    DELETE FROM Student WHERE Sno= 201215128 ';
    删除学号为201215128的学生记录。
    

    删除多个元组

    DELETE FROM SC;
    删除所有的学生选课记录。

    带子查询的删除语句

    DELETE FROM SC WHERE Sno IN(SELETE Sno FROM Student WHERE Sdept='CS');
    删除计算机科学系所有学生的选课记录。

    4.4,更新操作与数据库一致性

    增删改命令一次只能对一个表操作这会带来一些问题 --- 破坏关系数据库的参照完整性。  

    系统应保证实体完整性和参照完整性, 可采用策略:

    1往参照表(子表)中插入元组时, 系统自动检查被参照表(主表)中是否存在相应的元组, 如果存在则插入操作成功, 否则插入操作失败(称限制插入)

    2删除被参照表(主表)中的元组时采用策略:

      系统自动删除参照表(子表)中相应的元组(称级联删除)

      系统检查参照表(子表)中是否存在相应的元组, 如果存在, 则删除操作失败(称限制删除)

    5,空值处理

    空值:就是“不知道”或“不存在”或“无意义”的值。

    存在情况:该属性应该有一个值,但目前不知道它的具体值;该属性不应该有值;由于某种原因不便于填写

    空值是一个很特殊的值,含有不确定性。对关系运算带来特殊的问题,需要做特殊的处理

    空值的产生:插入或者更新。

    空值的判断:name is null; 注意IS不能用‘=’代替, 不能写为name = NULL

    空值的约束条件:NOT NULL约束条件的不能取空值;②加了UNIQUE限制的属性不能取空值;码属性不能取空值;

    空值的注意事项:如果null参与算术运算,则该算术表达式的值为null如果null参与比较运算,则结果可视为false如果null参与聚集运算,则除count(*)之外其它聚集函数都忽略null

    6,视图

    视图是从基表中导出的逻辑表,它不像基表一样物理地存储在数据库中,视图没有自己独立的数据实体。视图作为一种基本的数据库对象,是查询一个表或多个表的另一种方法,通过将预先定义好的查询作为一个视图对象存储在数据库中,然后就可以像使用表一样在查询语句中调用它。

    视图的特点:虚表,是从一个或几个基本表(或视图)导出的表;②只存放视图的定义,不存放视图对应的数据;③基表中的数据发生变化,从视图中查询出的数据也随之改变;④视图之上可以再定义视图

    6.1,建立视图

    CREATE VIEW IS_Student AS (SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS');
    建立信息系学生的视图IS_Student。
    CREATE VIEW IS_Student AS (SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS')WITH CHECK OPTION;
    建立信息系学生的视图,并要求进行修改和插入时仍保证该视图只有信息系的学生。
    修改操作:RDBMS自动加上Sdept= 'IS'的条件
    删除操作:RDBMS自动加上Sdept= 'IS'的条件
    插入操作:RDBMS自动检查Sdept属性值是否为'IS' 如果不是,则拒绝该插入操作
    如果没有提供Sdept属性值,则自动定义Sdept为'IS'
    

    若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,我们称这类视图为行列子集视图。

    CREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,2014-Sage FROM  Student;
    

    视图中设置了派生属性列, 这些列在基本表中并不实际存在, 导出视图时通过计算得到。即与视图相应的查询语句的SELECT目标列是表达式。带表达式的视图必须明确定义组成视图的各个属性列名。

    6.2,删除视图

    drop view 视图名[CASCADE]
    该语句从数据字典中删除指定的视图定义
    如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除 
    删除基表时,由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 

    6.3,查询视图&更新视图

    从用户角度:查询视图与查询基本表相同;更新视图与更新基本表相同;

    查询方法&更新方法

    实体化视图:有效性检查:检查所查询的视图是否存执行视图定义,将视图临时实体化,生成临时表;查询视图转换为查询临时表;查询完毕删除被实体化的视图(临时表);

    视图消解法:进行有效性检查,检查查询的表、视图等是否存在。如果存在,则从数据字典中取出视图的定义;把视图定义中的子查询与用户的查询结合起来,转换成等价的对基本表的查询;执行修正后的查询;

    视图更新有限制,要求定义视图时:select子句中的目标列不能包含聚集函数;②select子句中不能使用distinct关键字;③不能包括group by子句;④不能包括经算术表达式计算出来的列;⑤不能从两个以上的基本表中导出;⑥视图定义中不能有嵌套查询

    一般来说,对于行列子集视图可以更新,其他视图更新有限制或者不能

    6.4,视图的作用

    视图能够简化用户的操作,简化用户观点。

    用户可以通过视图组织自己所关心的数据,不管这些数据来自哪些基本表,是怎么来的,使用户觉得数据库结构简单、清晰、查询操作方便。

    视图使不同用户可以从多种不同角度观察同一数据。

    视图对重构数据库提供了一定程度的逻辑独立性。

    视图能够对机密数据提供安全保护。

    展开全文
  • SQL(Structured Query Language):结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言 一、SQL的特点 1.综合统一 集数据定义语言(DDL),数据操纵语言(DML),数据控制语言...
  • SQL语言 (structural query language)结构化的查询语言关系数据库的基础语言 SQL的特点: 综合统一: 集合DDL、DCL、DML功能于一体 可以独立完成数据库系统生命周期的全部活动 高度非过程化的: SQL只...
  • 关系数据库的标准语言是:结构化查询语句(SQL) SQL分为四类: 1.DQL(数据查询语言) 关键字:select 举例:select * from c_student; 2.DDL(数据定义语言) 关键字:create,alter,drop 举例: --create ...
  • )SQL是关系数据库的标准语言,也是一个通用的功能极强的关系数据库语言,其功能不仅仅是查询,而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。 二、SQL特点 综合...
  • 关系数据库标准语言SQL

    千次阅读 2017-12-07 23:55:17
    结构化查询语言(SQL)是关系数据库的标准语言,也是一个通用的、功能极强的关系数据语言。其功能不仅是查询,还包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。 1、SQL的...
  • SQL(Structured Query Language) 结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言 SQL特点: 1⃣️:综合统一 一:集数据定义语言(DDL),数据操纵语言(DML),数据控制语言...
  • 一、结构化查询语言(Structured Query Language)是一个综合的,通用的,功能极强的语言 SQL的动词 SQL功能 核心动词 数据查询 SELECT 数据定义 ... 二、SQL支持关系数据库的三级模式,外模...
  • SQL是结构化查询语言(Structured Query Language)缩写,它是当前最成功、应用最广泛的关系数据库语言,尽管名称是"查询语言",但其功能包括数据查询、数据定义、数据操纵和数据控制四部分。
  • SQL:结构化查询语言,是关系数据库的标准语言。 SQL是一个通用的、功能极强的关系数据库语言。
  • 如果读者对于SQL语言的演进有所了解的话,就知道是它直接推动了关系数据库的发展。此外,互联网的崛起还催生了NoSQL的诞生和崛起,其中很大的一个因素是关系型数据库无法很好的应对数据处理速度、数据建
  • SQL语言SQL是strcture query language 的缩写,是关系数据库的标准语言结构化 查询 语言包括三个大的内容:A、 DDL数据定义语言:create database table •••••••B、 DML数据操纵语言:insert update delete...
  • 结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言 SQL 的产生与发展 目前,没有一个数据库系统能够支持SQL标准的所有概念和特性。 SQL的特点 1.综合统一 2.高度非过程化 3...
  • Sql数据库查询语言

    千次阅读 2016-02-28 14:55:07
    Sql是一种面向数据库的结构化查询语言,是符合美国国家标准化组织ANSI的一种计算机标准语言。 Sql具对数据库的操作有:增删改查,创建数据库、创建表、创建存储过程、创建视图等 RDBMS关系型数据库管理系统,是指...
  • SQL(StructuredQueryLanguage,结构查询语言)是一个功能32313133353236313431303231363533e78988e69d8331333431376561强大的数据库语言。SQL通常使用于数据库的通讯。ANSI(美国国家标准学会)声称,SQL是关系数据库...
  • 关系数据库标准语言SQL概述SQL概述1.1 SQL 的产生与发展1.2 SQL的特点1.2.1 综合统一1.2.2 高度非过程化1.2.3 ...SQL(Structured Query Language) 结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能...
  • SQL(Structured Query Language):结构化查询语言,是关系数据库的标准语言。 SQL是一个通用的、功能极强的关系数据库语言。 3.1.1SQL的产生与发展 SQL标准的进展过程: 3.1.2SQL的特点 综合统一 (1)集数据定义...
  • 关系数据库标准语言SQL 一、SQL概述 1.SQL特点 SQL是因为它是一个综合、功能极强同时又简洁易学的语言。SQL集数据查询(dataquery)、数据操纵(datamanipulation)、 数据定义(data definition) 和数据控制(data...
  • 结构化查询语言,是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言 SQL 的产生与发展 目前,没有一个数据库系统能够支持SQL标准的所有概念和特性。 SQL的特点 1.综合统一 2.高度非过程化 3...
  • 结构化查询语言SQL是关系数据库的标准语言,也是一个通用的、功能极强的关系数据库语言。其功能呢个不仅仅是查询,而是包括数据库模式创建、数据库数据的插入和修改、数据库安全性完整性定义域控制等一系列功能。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,695
精华内容 2,678
关键字:

关系数据库的查询语言是