特点

• 综合统一
• 高度非过程化
• 面向集合的操作方式
• 以同一种语法结构提供两种使用方式（命令方式、程序方式）
• 语言简洁，易学易用

组成部分

• 查询语言 DQL
• 定义语言 DDL
• 操纵语言 DML
• 控制语言 DCL

1 查询语言 DQL

基本结构

SELECT desired attributes
FROM one or more tables
WHERE condition about tuples of the tables
GROUP BY one or more attributes
ORDER BY one or more attribute;

• SELECT子句：指定要显示的属性列
• FROM子句：指定查询对象(基本表或视图)
• WHERE子句：指定查询条件
• GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
• HAVING短语：筛选出只有满足指定条件的组
• ORDER BY子句：对查询结果表按指定列值的升序或降序排序

例

下面内容全部以以下的关系模型为例
学生-课程数据库

• 学生表：Student(Sno，Sname，Ssex，Sage，Sdept)
• 课程表：Course(Cno，Cname，Cpno，Ccredit)
• 学生选课表：SC(Sno，Cno，Grade)

1.1 单表查询

• 查询仅涉及一个表，是一种最简单的查询操作

选择表中的若干列

[例1] 查询全体学生的学号与姓名。
SELECT Sno，Sname
FROM Student；

[例2] 查询全体学生的姓名、学号、所在系。
SELECT Sname，Sno，Sdept
FROM Student；

[例3] 查询全体学生的详细记录。
SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student；
或
SELECT *
FROM Student；

查询经过计算的值

SELECT子句的<目标列表达式>为表达式

• 算术表达式
• 字符串常量
• 函数
• 列别名
• 等

[例4] 查全体学生的姓名及其出生年份。
SELECT Sname，2022-Sage
FROM Student；
输出结果：

[例5] 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECT Sname ， 2020-Sage AS 'Year of Birth: ' ，
LOWER(Sdept)
FROM Student；

使用列别名改变查询结果的列标题

SELECT Sname AS NAME，'Year of Birth: ’ AS BIRTH，2022-Sage AS BIRTHDAY，LOWER(Sdept) AS DEPARTMENT
FROM Student；

选择表中的若干元组

保留重复行

• 用ALL关键字
  SELECT ALL Sno FROM SC;

• ALL为默认关键字，可以省略，等价于：
  SELECT Sno FROM SC;

消除取值重复的行

• 在SELECT子句中使用DISTINCT短语消除重复行
  SELECT DISTINCT Sno FROM SC;

• 注意 DISTINCT短语的作用范围是所有目标列
  例：查询选修课程的各种成绩
  错误的写法
  SELECT DISTINCT Cno，DISTINCT Grade FROM SC;
  正确的写法
  SELECT DISTINCT Cno，Grade FROM SC;

用where子句查询满足条件的元祖

比较大小

• 在WHERE子句的比较条件中使用比较运算符 = ，>，<，>=，<=，!= 或 <>，!>，!<，逻辑运算符NOT + 比较运算符
  [例6] 查询所有年龄在20岁以下的学生姓名及其年龄。
  SELECT Sname，Sage
  FROM Student
  WHERE Sage < 20；
  或
  SELECT Sname，Sage
  FROM Student
  WHERE NOT Sage >= 20；

确定范围

• 使用谓词 BETWEEN … AND … NOT BETWEEN … AND …
  [例7] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
  SELECT Sname，Sdept，Sage
  FROM Student
  WHERE Sage BETWEEN 20 AND 23；

确定集合

• 使用谓词 IN <值表>, NOT IN <值表>
  <值表>：用逗号分隔的一组取值
  [例8] 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
  SELECT Sname，Ssex
  FROM Student
  WHERE Sdept IN ( 'IS'，'MA'，'CS' );

  [例9] 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
  SELECT Sname，Ssex
  FROM Student
  WHERE Sdept NOT IN ( 'IS'，'MA'，'CS' );

字符串匹配

• WHERE 子句中可以对字符串进行模版匹配，形如：
  WHERE <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>
  其中<pattern>是匹配模板，即：固定字符串或含通配符的字符串

  • 通配符：
    % (百分号) 代表任意长度（长度可以为0）的字符串
    _ (下横线) 代表任意单个字符
    [例10] 查询学号为95001的学生的详细情况。
    SELECT FROM Student
    WHERE Sno LIKE '95001'；
    等价于：
    SELECT FROM Student
    WHERE Sno = '95001'；

  [例11] 查询所有姓刘学生的姓名、学号和性别。
  SELECT Sname，Sno，Ssex
  FROM Student
  WHERE Sname LIKE ‘刘%’；

  [例12] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
  SELECT Sname
  FROM Student
  WHERE Sname LIKE '欧阳__'；

  [例13] 查询名字中第2个字为"阳"字的学生的姓名和学号
  SELECT Sname，Sno
  FROM Student
  WHERE Sname LIKE '__阳%'；

  [例14] 查询所有不姓刘的学生姓名。
  SELECT Sname，Sno，Ssex
  FROM Student
  WHERE Sname NOT LIKE '刘%'；

• 当用户要查询的字符串本身就含有 % 或 时，要使用ESCAPE '<换码字符>' 短语对通配符进行转义。(! Mysql不能用\作为转义符)
  [例15] 查询DB_Design课程的课程号和学分。
  SELECT Cno，Ccredit
  FROM Course
  WHERE Cname LIKE 'DB_Design'
  ESCAPE '
  [例16] 查询以“DB”开头，且倒数第3个字符为 i 的课程的详细情况。
  SELECT
  FROM Course
  WHERE Cname LIKE ‘DB_%i_ _' ESCAPE ‘ *'；

• 当进行固定字符串匹配时用可以‘=’代替‘like’

• 对于包含单引号的字符串，在条件表达式中用双引号代替单引号
  [例16] 查询课程名为‘数据库’的课程的详细情况。
  SELECT *
  FROM Course
  WHERE Cname = ’’’数据库’’’ ；

多重条件查询

• 用逻辑运算符AND，OR和 NOT来联结多个查询条件
  [例17] 查询计算机系年龄在20岁以下的学生姓名。
  SELECT Sname
  FROM Student
  WHERE Sdept= 'CS' AND Sage<20；

  [例18] 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
  SELECT Sname，Ssex
  FROM Student
  WHERE Sdept IN ( 'IS'，'MA'，'CS' )
  可改写为：
  SELECT Sname，Ssex
  FROM Student
  WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS '；

  [例19]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
  SELECT Sname，Sdept，Sage
  FROM Student
  WHERE Sage BETWEEN 20 AND 23；
  可改写为：
  SELECT Sname，Sdept，Sage
  FROM Student
  WHERE Sage>=20 AND Sage<=23；

涉及空值的查询

• Where 语句中的条件表达式有三种可能的计算结果：True，False，或者 UnKnown
• 任何一个值与NULL进行比较，返回的结果是UnKnown
• Where子句对被查询表中每一条记录进行条件表达式的计算，只有在计算结果为True时当前记录才会被选中
• 三值逻辑计算(Three-Valued Logic)：
  TRUE = 1, FALSE = 0, and UNKNOWN = ½
  AND = MIN; OR = MAX, NOT(x) = 1-x
  例：
  TRUE AND (FALSE OR NOT(UNKNOWN))
  = MIN(1, MAX(0, (1 - ½ )))
  = MIN(1, MAX(0, ½ )
  = MIN(1, ½ ) = ½.

对上表执行如下查询：
SELECT Sno
FROM Stu
WHERE Age< 20 OR Age >= 20;
没有任何一条记录会被选中

• 测试是否空值需用谓词 IS NULL 或 IS NOT NULL

• “IS NULL” 不能用 “= NULL” 代替
  [例20] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
  SELECT Sno，Cno FROM SC
  WHERE Grade IS NULL；

  [例21] 查所有有成绩的学生学号和课程号。
  SELECT Sno，Cno
  FROM SC
  WHERE Grade IS NOT NULL；

对查询结果进行排序

• 使用ORDER BY子句

  • 可以按一个或多个属性列排序
  • 升序：ASC；降序：DESC；缺省值为升序

• 当排序列含空值时

  • ASC：排序列为空值的元组最后显示
  • DESC：排序列为空值的元组最先显示

• Order子句只能出现在select语句的最后部分

  [例22] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
  SELECT Sno，Grade
  FROM SC
  WHERE Cno= ' 3 '
  ORDER BY Grade DESC；

  [例23] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
  SELECT *
  FROM Student
  ORDER BY Sdept，Sage DESC；

1.2 聚集和分组

聚集-集函数

只有select和having能使用集函数

在Select子句中可以使用集函数，对指定的列进行聚合计算

• 计数 COUNT（[DISTINCT|ALL] <列名> | *）

• 计算总和 SUM （[DISTINCT|ALL] <列名>）

• 计算平均值 AVG（[DISTINCT|ALL] <列名>）

• 求最大值 MAX（[DISTINCT|ALL] <列名>）

• 求最小值 MIN（[DISTINCT|ALL] <列名>）

• DISTINCT短语：在计算时要取消指定列中的重复值

• ALL短语：不取消重复值

• ALL为缺省值

  [例24] 查询学生总人数。
  SELECT COUNT(*)
  FROM Student；

  [例25] 查询选修了课程的学生人数。
  SELECT COUNT(DISTINCT Sno)
  FROM SC；
  注：用DISTINCT以避免重复计算学生人数

  [例26] 计算1号课程的学生平均成绩。
  SELECT AVG(Grade)
  FROM SC
  WHERE Cno= ' 1 '；

  [例27] 查询选修1号课程的学生最高分数。
  SELECT MAX(Grade)
  FROM SC
  WHER Cno= ' 1 '；

• 集函数中的空值NULL

  • 空值不加入SUM、AVG和COUNT的计算，也不会成为列中的MIN、MAX值
  • 但如果列中没有非空值，则集函数结果会返回空值

  
  SELECT count(*)
  FROM SC
  WHERE Cno = ‘1001’;
  VS
  SELECT count(Grade)
  FROM SC
  WHERE Cno = ‘1001’;

分组

group by

• 使用GROUP BY子句分组
  • 细化集函数的作用对象
  • 未对查询结果分组时，集函数将作用于整个查询结果
  • 对查询结果分组后，集函数将分别作用于每个组
• GROUP BY子句的作用对象是查询的中间结果表
• 分组方法：按指定的一列或多列值分组，值相等的为一组
• 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数
  
  
  [例28] 求各个课程号及相应的选课人数。
  SELECT Cno，COUNT(Sno)
  FROM SC
  GROUP BY Cno；
  
  

having

• 只有满足HAVING短语指定条件的组才输出

• HAVING短语与WHERE子句的区别：作用对象不同

  • WHERE子句作用于基表或视图，从中选择满足条件的元组
  • HAVING短语作用于组，从中选择满足条件的组。

• HAVING子句中出现的列只能是在group by子句或集函数中出现的列

  [例30] 查询选修了3门以上课程的学生学号。
  SELECT Sno
  FROM SC
  GROUP BY Sno
  HAVING COUNT(*) >3；

  [例31] 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数
  SELECT Sno, COUNT(*)
  FROM SC
  WHERE Grade>=90
  GROUP BY Sno
  HAVING COUNT(*)>=3;

  

1.3 多表查询

• 在From子句中涉及

• SQL中连接查询的主要类型

• 和自己连接：自身连接

• 和其他表连接

  • 不带连接谓词：广义笛卡尔积
  • 带连接谓词
    • 不满足连接条件的元组一并输出：外链接
    • 只输出满足连接条件的元祖
      • 非单一条件：复合条件连接
      • 单一条件
        • 等值：等值连接
        • 不等值：非等值连接

广义笛卡尔积

• 是不带链接谓词的连接
• 一般格式是 SELECT <属性或表达式列表> FORM <表名> CROSS JOIN <表名>
• 例
  SELECT Student.*, SC.* FROM Student, SC

等值连接

• 语法：[<表名1>.]<列名1> = [<表名2>.]<列名2>
  • 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。
  • 引用唯一属性名时可以加也可以省略表名前缀。
    [例32] 查询每个学生及其选修课程的情况。
    SELECT Student.sno，sname, cno
    FROM Student，SC
    WHERE Student.Sno = SC.Sno；

自然连接

等值连接的一种特殊情况，把目标列中重复的属性列去掉

非等值连接查询

连接运算符不是=号的连接

内连接

传统的连接操作被称为内连接（INNER JOIN），其一般格式是：
SELECT <属性或表达式列表>
FROM <表名> [INNER] JOIN <表名>
ON <连接条件>
[WHERE <限定条件>]

• 上式中INNER可以省略，这里用ON短语指定连接条件，用WHERE短语指定其它限定条件。

自身连接查询

• 一个表与其自己进行连接
• 需要给表的两个副本起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀

[例33] 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno，SECOND.Cpno
FROM Course AS FIRST，Course AS SECOND
WHERE FIRST.Cpno = SECOND.Cno；

外连接查询

普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

• 语法
  SELECT <属性或表达式列表>
  FROM <表名> [LEFT | RIGHT | FULL] [OUTER] JOIN <表名>
  ON <连接条件>
  [WHERE <限定条件>]

• Mysql不支持全外连接
  解决方法：Left join Union Right join

[例34] 查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作
SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade
FROM Student，SC
WHERE Student Right Join SC
on Student.Sno =SC.Sno；

复合条件连接查询

WHERE子句中含多个连接条件时，称为复合条件连接
[例34] 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名
SELECT Student.Sno, student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND / 连接谓词/
SC.Cno= ' 2 ' Or /* 其他限定条件 */
Not SC.Grade > 90；/* 其他限定条件 */

1.4 子查询

• 一个SELECT-FROM-WHERE语句称为一个查询块
• 子查询一定要跟在比较符之后（p26）
  错误的例子：
  SELECT Sno，Sname，Sdept
  FROM Student
  WHERE ( SELECT Sdept
  FROM Student
  WHERE Sname= ‘ 刘晨 ’ )
  = Sdept；

子查询分类

• 可以嵌套在另一个查询块的WHERE子句或HAVAING短语的条件中
  SELECT Sname FROM Student WHERE Sno IN 外层查询/父查询
  （SELECT Sno FROM SC WHERE Cno= ' 2 '）；内层查询/子查询

• 可以插入到FROM子句中，作为临时表使用

  • 临时表需起个别名来引用
  • 不是所有数据库产品都支持临时表
    SELECT IS.Sno, Sname, Cno
    FROM SC, (SELECT Sno, Sname from Student WHERE Sdept= ‘ IS ‘) as SIS
    WHERE SC.Sno=SIS.Sno

• 可以插入到SELECT子句中

  • 确保返回结果为单列单行
  • 没有太大意义，多数数据库不支持

• 相关子查询

  • 子查询的查询条件依赖于父查询
  • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；
  • 然后再取外层表的下一个元组；
  • 重复这一过程，直至外层表全部检查完为止。

• 不相关子查询

  • 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件
    上面那个例子（插入From子句）是不相关子查询

引出子查询的谓词

IN

[例35] 查询与“刘晨”在同一个系学习的学生。
此查询可以分步来完成

1. 确定“刘晨”所在系名（是‘IS’系）
   SELECT Sdept FROM Student
   WHERE Sname= ' 刘晨 ‘；
2. 查找所有在IS系学习的学生。
   SELECT Sno，Sname，Sdept
   FROM Student
   WHERE Sdept= ' IS '；

[例36] 查询与“刘晨”在同一个系学习的学生。
也可以直接构造嵌套查询：将第一步查询嵌入到第二步查询的条件中
SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept IN
(SELECT Sdept FROM Student
WHERE Sname= ‘ 刘晨 ’)；
此查询为不相关子查询

[例37]. 查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno，Sname ③ 最后在Student关系中
FROM Student 取出Sno和Sname
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选
FROM SC 修了3号课程的学生学号
WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出“信
FROM Course 息系统”的课程号，结果为3号
WHERE Cname= ‘信息系统’));
或
SELECT Sno，Sname
FROM Student，SC，Course
WHERE Student.Sno = SC.Sno AND
SC.Cno = Course.Cno AND
Course.Cname=‘信息系统’；

比较运算符

当能确切知道内层查询返回单值时，可用比较运算符 (>, <, =, >=, <=, != (或 <>))。

假设一个学生只可能在一个系学习，并且必须属于一个系，则在
[例36]中可以用 = 代替IN
SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept =
SELECT Sdept FROM Student
WHERE Sname= ' 刘晨 '；

ANY或ALL

• ANY：任意一个值
• ALL：所有值
• 与比较运算符配合使用
  

[例38] 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄
SELECT Sname，Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ' ;
/ 注意这是父查询块中的条件 /

• ANY和ALL谓词有时可以用集函数实现
• 用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数
  

[例39] 用集函数实现 [例38] 的查询
SELECT Sname，Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ’;

[例40] 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词
SELECT Sname，Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <> ' IS ’;

方法二：用集函数
SELECT Sname，Sage
FROM Student
WHERE Sage <
(SELECT MIN(Sage)
FROM Student
WHERE Sdept= ' IS ')
AND Sdept <>' IS ’;

EXISTS

1. EXISTS谓词
   • 存在量词
   • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
   • 若内层查询结果非空，则返回真值
   • 若内层查询结果为空，则返回假值
   • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
2. NOT EXISTS谓词

• 用于相关子查询
  [例41] 查询所有选修了1号课程的学生姓名。
  SELECT Sname
  FROM Student
  WHERE EXISTS
  (SELECT *
  FROM SC /*相关子查询*/
  WHERE Sno=Student.Sno AND
  Cno= ' 1 ')；

• 用于不相关子查询
  select from a where exists (select from b)

• 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

  • 用EXISTS/NOT EXISTS实现全称量词，
    [例42] 查询选修了全部课程的学生姓名。
    SELECT Sname FROM Student
    WHERE NOT EXISTS
    (SELECT FROM Course
    WHERE NOT EXISTS
    (SELECT FROM SC
    WHERE Sno= Student.Sno
    AND Cno= Course.Cno));
  • 用EXISTS/NOT EXISTS实现蕴含逻辑，
    [例43] 查询至少选修了学生95002选修的全部课程的学生号码
    • 用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。
    • 形式化表示：
      用P表示谓词 “学生95002选修了课程y”
      用q表示谓词 “学生x选修了课程y”
      则上述查询为:
      
      

• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
  [例36] 中的IN查询可以用带EXISTS谓词的子查询替换：
  SELECT Sno，Sname，Sdept
  FROM Student S1
  WHERE EXISTS
  (SELECT *
  FROM Student S2
  WHERE S2.Sdept = S1.Sdept AND
  S2.Sname = ' 刘晨 '）；

  变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。
  SELECT DISTINCT Sno FROM SC SCX
  WHERE NOT EXISTS
  (SELECT FROM SC SCY
  WHERE SCY.Sno = ' 95002 ' AND
  NOT EXISTS
  (SELECT FROM SC SCZ
  WHERE SCZ.Sno=SCX.Sno AND
  SCZ.Cno=SCY.Cno))；

1.5 集合查询

• 并操作 ( subquery ) UNION ( subquery )
• 交操作 ( subquery ) INTERSECT ( subquery )
• 差操作 ( subquery ) EXCEPT ( subquery )

[例44] 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT
FROM Student
WHERE Sage<=19；

[例45] 查询计算机科学系选修95001号课程的学生的学号。
SELECT Sno
FROM Student
WHERE Sdept= 'CS'
Intersect
SELECT Sno
FROM SC
WHERE Cno=‘95001’；

[例46] 查询计算机科学系没有选修95001号课程的学生的学号。
SELECT Sno
FROM Student
WHERE Sdept= 'CS'
Except
SELECT Sno
FROM SC
WHERE Cno=‘95001’；

2 定义语言 DDL

• 数据库模式（schema）主要由数据库中关系（relation）的声明构成。我们首先介绍关系的定义
• 数据库模式也包括视图（view）、索引（index）、触发器（trigger）等对象，这些对象我们在稍后课程中介绍
• 对数据库模式的定义通过SQL语言中的数据定义语言（Data Definition Language）部分来完成
• DDL语言完成数据库对象的创建、删除和修改
  

2.1 定义基本表

语法

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

• <表名>：所要定义的基本表的名字
• <列名>：组成该表的各个属性（列）
• <列级完整性约束条件>：涉及相应属性列的完整性约束条件
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

常见的数据类型

• INT or INTEGER
• REAL or FLOAT
• CHAR(n ) = 定长字符串
• VARCHAR(n ) = 变长字符串，n是字符串最大长度
• DATE, TIME, and DATETIME

常用完整性约束

• 主键约束： PRIMARY KEY
• 唯一性约束：UNIQUE
• 非空值约束：NOT NULL
• 参照完整性约束： REFERENCES

例

[例1] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE，
Sname CHAR(20) UNIQUE，
Ssex CHAR(1) ，
Sage INT，
Sdept VARCHAR(15))；

[例2] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主键。

CREATE TABLE SC(
Sno CHAR(5) ,
Cno CHAR(3) ,
Grade int,
Primary key (Sno, Cno));

2.2 删除基本表

• 语法：DROP TABLE <表名>;

[例3] 删除Student表
DROP TABLE Student ;

2.3 修改基本表

语法

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；
0

• <表名>：要修改的基本表
• ADD子句：增加新列和新的完整性约束条件
  • 不论基本表中原来是否已有数据，新增加的列一律为空值。
• DROP子句：删除指定的列或完整性约束条件
• MODIFY子句：用于修改列名和数据类型

例

[例4] 向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD Scome DATE；

[例5] 删除属性列
ALTER TABLE Student Drop Sage；

[例6] 将年龄的数据类型改为半字长整数。
ALTER TABLE Student MODIFY Sage SMALLINT；

[例7] 删除学生姓名必须取唯一值的约束。
ALTER TABLE Student DROP UNIQUE(Sname)；

3 操纵语言 DML

• 数据更新语句不返回关系表，而是会改变数据库的内容
• 三种数据更新类型
  • Insert 插入一条或多条元组
  • Delete 删除一条或多条元组
  • Update 更新现有一条或多条元组中的值

3.1 插入数据

插入单条元组

语法

INSERT
	INTO <表名> [(<属性列1>[，<属性列2 >…)]
	VALUES (<常量1> [，<常量2>] … )

• INTO子句
  • 指定要插入数据的表名及属性列
  • 属性列的顺序可与表定义中的顺序不一致
  • 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
  • 指定部分属性列：插入的元组在其余属性列上取空值
• VALUES子句
  • 提供的值必须与INTO子句匹配：个数、类型

例

[例7] 将一个新学生记录
（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。
INSERT
INTO Student
VALUES ('95020'， '陈冬' ， '男' ，'IS'，18)；

[例8] 插入一条选课记录( '95020'，'1 ')。
INSERT
INTO SC(Sno，Cno)
VALUES (' 95020 '，' 1 ')；
新插入的记录在Grade列上取空值

插入子查询结果

语法

语句格式
INSERT
INTO <表名> [(<属性列1> [，<属性列2>… )]
子查询；
功能：将子查询结果插入指定表中

例

[例9] 对每一个系，求学生的平均年龄，并把结果存入数据库。

第一步：建表
CREATE TABLE Deptage
(Sdept CHAR(15), Avgage SMALLINT)
第二步：插入数据
INSERT INTO Deptage(Sdept，Avgage)
SELECT Sdept，AVG(Sage)
FROM Student
GROUP BY Sdept

3.2 修改数据

• 语句格式
  UPDATE <表名>
  SET <列名>=<表达式>[，<列名>=<表达式>]…
  [WHERE <条件>]；

  • SET子句
    • 指定修改方式
      • 要修改的列
      • 修改后取值
  • WHERE子句
    • 指定要修改的元组
    • 缺省表示要修改表中的所有元组

• 功能
  修改指定表中满足WHERE子句条件的元组

• 三种修改方式

  • 修改某一个元组的值
  • 修改多个元组的值
  • 带子查询的修改语句

修改某一个元组的值

[例10] 将学生95001的年龄改为22岁。

UPDATE Student
SET Sage=22
WHERE Sno=' 95001 '；

修改多个元组的值

[例11] 将所有学生的年龄增加1岁。

UPDATE Student
SET Sage = Sage+1；

带子查询的修改语句

[例12] 将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；

3.3 删除数据

• 语法格式
  DELETE FROM <表名>
  [WHERE <条件>];

• 功能
  删除指定表中满足WHERE子句条件的元组

• WHERE子句
  指定要删除的元组
  缺省表示要修改表中的所有元组

• 三种删除方式

  • 删除某一个元组的值
    [例13] 删除学号为95019的学生记录。
    DELETE
    FROM Student
    WHERE Sno='95019'；
  • 删除多个元组的值
    [例14] 删除2号课程的所有选课记录。
    DELETE
    FROM SC
    WHERE Cno='2'；
    [例15] 删除所有的学生选课记录。
    DELETE
    FROM SC；
  • 带子查询的删除语句
    [例16] 删除计算机科学系所有学生的选课记录。
    DELETE
    FROM SC
    WHERE 'CS'=
    (SELETE Sdept
    FROM Student
    WHERE Student.Sno=SC.Sno)；

4 控制语言 DCL

• 授予或回收访问数据库的某种特权
• 控制数据库操纵事务发生的时间及效果
• 对数据库实行监视等

5 视图

• 视图是一种虚表（virtual table），是从一个或几个基本表（或视图）导出的表
  • 视图其实是在数据字典中存储的一条Select 语句
  • DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句
  • 在对视图查询时，按视图的定义从基本表中将数据查出
  • 对于某些视图（可更新视图），可以对视图执行数据更新操作，数据库会根据视图定义去更新对应的基本表数据

5.1 视图定义

在定义视图时，组成视图的属性列名需全部省略或全部指定

• 当如下情形时，全部省略：
  视图属性由子查询中SELECT目标列中的诸字段组成
• 当如下情形时，需明确指定视图的所有列名:
  • 某个目标列是集函数或列表达式
  • 多表连接时选出了几个同名列作为视图的字段
  • 需要在视图中为某个列启用新的更合适的名字

语法

CREATE VIEW
<视图名> [(<列名> [，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]；

例

建立新视图

[例17] 建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno，Sname，Sage
FROM Student
WHERE Sdept= 'IS'；

[例18] 建立信息系选修了1号课程的学生视图。
CREATE VIEW IS_S1(Sno，Sname，Grade)
AS
SELECT Student.Sno，Sname，Grade
FROM Student，SC
WHERE Sdept= 'IS' AND
Student.Sno=SC.Sno AND
SC.Cno= '1'；

基于视图建立新视图

[例19] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno，Sname，Grade
FROM IS_S1
WHERE Grade>=90；

带表达式的视图

[例20] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno，Sname，Sbirth)
AS
SELECT Sno，Sname，2000-Sage
FROM Student
设置一些派生属性列, 也称为虚拟列--Sbirth
带表达式的视图必须明确定义组成视图的各个属性列名

带分组的视图

[例21] 将学生的学号及他的平均成绩定义为一个视图
假设SC表中“成绩”列Grade为数字型
CREAT VIEW S_G(Sno，Gavg)
AS
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno；

不建议以 SELECT * 方式创建视图，因这样创建的视图可扩展性差

[例22]将Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student1(stdnum，name，sex，age，dept)
AS SELECT *
FROM Student
WHERE Ssex='女'；
缺点：当修改基表Student的结构后，Student表与F_Student1视图的映象关系会被破坏，导致该视图不能正确工作。

[例23]将Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student2 (stdnum，name，sex，age，dept)
AS SELECT Sno，Sname，Ssex，Sage，Sdept
FROM Student
WHERE Ssex='女'；
为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。

5.2 视图使用

• 视图查询：对视图的查询与对基本表的查询完全相同
• 视图更新
  • 从用户角度，对视图的更新与对基本表的更新方法上也完全相同
  • 但是存在一些不可更新视图，因为对这些视图的更新不能唯一地/有意义地转换成对相应基本表的更新

With Check Option

透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）
[例25] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT *
FROM Student
WHERE Sdept= 'IS’
WITH CHECK OPTION

5.3 视图的作用

• 视图能够简化用户的操作
  当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作，如对于下述的情形：
  • 基于多张表连接形成的视图
  • 基于复杂嵌套查询的视图
  • 含导出属性的视图
• 视图使用户能以多种角度看待同一数据
  视图对基本表进行数据抽取和组合，形成不同的观察窗口，使不同用户以不同方式看待同一数据，适应数据库共享的需要
• 视图对重构数据库提供了一定程度的逻辑独立性
  例：数据库逻辑结构发生改变
  学生关系Student(Sno，Sname，Ssex，Sage，Sdept)
  “垂直”地分成两个基本表：
  SX(Sno，Sname，Sage)
  SY(Sno，Ssex，Sdept)
• 视图能够对机密数据提供安全保护
  • 对不同用户定义不同视图，使每个用户只能看到他有权看到的数据
  • 通过WITH CHECK OPTION对关键数据施加操作限制