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

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

一、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）消除取值重复的行，使用$DISTINCT$实现，没有指定，则默认为$ALL$；
   【例】：查询选修了课程的学生学号

   SELECT DISTINCT SNO FROM SC
   

   （2）查询满足条件的元组，使用$WHERE$子句实现；

   条件	谓词
   比较	$=,>,>=,<=,!=,<>,!>,!<,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');
   

   ④字符匹配：谓词$LIKE$可以用来进行字符匹配

   [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 更新视图

• 转换为等价的对基本表的更新；