第三章-关系数据库标准语言SQL

关系数据库标准语言SQL

一、SQL概述

SQL是一个通用的、功能性极强的关系数据库语言，集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体

• 高度非过程化（无需了解存储路径）
• 面向集合的操作方式（操作对象是一条记录）
• 以同一种语法提供多种使用方式（独立的语言，嵌入式语言）
• 语言简洁，易学易用

三级模式：

• 基本表：本身独立存在的表，一个关系对于一个基本表
• 存储文件：逻辑结构组成内模式，物理结构对用户隐藏
• 视图：是一个虚表，可以从一个或几个基本表中导出。用于对外接口

二、SQL数据定义

• 一个数据库中可以建立多个模式
• 一个模式下通常包含多个表、视图和索引等数据库对象

定义模式

CREATE SCHEMA UTHORIZATION <用户名> <模式名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

注意：MySQL中定义模式相当于定义一个database，所以CREATE SCHEMA就是CREATE DATABASE

注意：MySQL中不需要使用AUTHORIZATION <用户名>，因为你登录的时候就已经确定用户了

删除模式

DROP SCHEMA <模式名> <CASCADE|RESTRICT>

CASCADE表示删除模式的同时把该模式中所有的数据库对象全部删除；RESTRICT如果还存在表会不让你删除

定义基本表

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


完整性约束条件：

• PRIMARY KEY ：主键
• NOT NULL：非空

注意：定义外键可以在括号中的下方加上如下语句：

FOREIGN KEY <外码名称> REFERENCES <表名>(<列名>)

SQL中的数据类型：

修改基本表

ALTER TABLE <表名> [ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;

删除基本表

DROP TABLE <表名>［RESTRICT| CASCADE］

• RESTRICT：如果表被其他对象依赖则不能删除
• CASCADE：无限制

索引

建立索引的目的：加快查询速度。

常见索引：

• 顺序文件上的索引
• B+树索引
• 散列索引
• 位图索引

建立索引

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

• UNIQUE：此索引的每一个索引值只对应唯一的数据记录
• CLUSTER：表示要建立的索引是聚簇索引

三、SQL数据查询

数据查询：

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
       FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)      
                   [AS]<别名>
[ WHERE <条件表达式> ]
[GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ORDER BY <列名2> [ ASC|DESC ] ];

其中：

• WHERE指定查询条件
• GROUP BY对查询结果按指定的列分组，值相等的元素为一个组。每组中可以使用聚集函数
• HAVING：只能使用于GROUP BY中，只有满足条件的列才可以输出
• ORDER BY按指定的列升序或降序

查询全部列：

SELECT * FROM Student

查询经过计算的值

目标列表达式中可以不仅仅是属性列，也可以是计算的表达式，例如SELECT 2014-Sage FROM Student

改变列标题

可以在一个目标列表达式后面多加一个名称，来替换原来这个列的名字。这也可以作为这个列的别名

SELECT Sname NAME,'Birth Year' BIRTH FROM Student;

这里多添加了一列BIRTH列，并使用Birth Year来填充这一列

消除取消重复的行

使用DISTINCT关键词，例如

SELECT DISTINCT Sno FROM SC;

这样选出来不会出现重复的列

查询满足条件的元组

WHERE子句中可以出现的运算符：

Like：使用匹配串。匹配串中可以使用通配符%（表示任意长度字符，可以为0）和_（任意单个字符）。也可以使用转义字符。

比较重要的：

• IN确定集合：

SELECT Sname, Ssex
    FROM  Student
    WHERE Sdept IN ('CS','MA’,'IS' );

IN可以改为NOT IN

聚集函数

• 统计元素个数：COUNT
• 统计一列中值的个数：COUNT([DISTINCT|ALL]<列名> )
• 计算一列值的总和SUM([DISTINCT|ALL]<列名>)
• 计算一列值的平均值AVG([DISTINCT|ALL]<列名>)
• 求一列中的最大值和最小值MAX/MIN([DISTINCT|ALL]<列名>)

例如：查询选秀了课程的学生人数：SELECT COUNT(DISTINCT Sno)

GROUP BY子句分组

• 细化聚集函数的作用对象，按指定的一列或多列值分组，值相等的为一组

求各个课程号及相应的选课人数：

Cno，COUNT(Sno)

FROM    SC
GROUP BY Cno; 

HAVING短语：筛选分组后的数据。与where的不同：where子句作用域基表或视图，选择满足条件的组。having短语作用于组，从中选择满足条件的组

连接查询

同时涉及两个以上的表的查询

连接条件或连接谓词：用来连接两个表的条件

• [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
• [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

等值连接：连接符号为=

查询每个学生及其选秀课程的情况：

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

连接操作的执行过程

• 嵌套循环法：每次在表1中找一个元组，然后每次都在表2中从头开始找满足条件的元组，然后拼接。
• 排序合并法：常用于=连接：首先对表1和表2排序，然后每次找表1的1个元组。每次当找表2找到值大于表1时就不找了
• 索引连接：对表2的字段建立索引，然后每次查询就直接查索引。

同时选择连接：例如 WHERE Student.Sno=SC.Sno AND SC.Cno=’ 2 ’ AND SC.Grade>90;

自身连接：一个表与其自己进行连接。需要给表起别名，例如：

SELECT  FIRST.Cno, SECOND.Cpno
    FROM  Course  FIRST, Course  SECOND
    WHERE FIRST.Cpno = SECOND.Cno;

外连接：以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

• 左外连接：列出左边关系中所有的元组
• 右外连接：列出右边关系中所有的元组

例如：

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM  Student  LEFT OUT JOIN SC ON    
                 (Student.Sno=SC.Sno); 

多表连接：两个以上的表进行连接

嵌套查询

一个select-from-where子句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询

• 父查询：上层的嵌套块
• 子查询：下层的嵌套块

子查询的限制：不能使用ORDER BY子句

不相关子查询：子查询的条件不依赖于父查询，例如

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept  IN
   (SELECT Sdept
    FROM Student
    WHERE Sname= ' 刘晨 ');

带有比较运算符的子查询：当确切直到内存查询返回单值时，可以用比较运算符

SELECT Sno,Sname,Sdept
     FROM    Student
     WHERE Sdept   =
                   (SELECT Sdept
                    FROM    Student
                    WHERE Sname= '刘晨');

带有ANY（SOME）或ALL谓词的子查询

例如：

• <=ANY表示小于等于子查询结果中的某个值
• <=ALL 小于等于子查询结果中的所有值
• = ANY 等于子查询结果中的某个值
• = ALL 等于子查询结果中的所有值（通常没有实际意义）

SELECT Sname,Sage
FROM    Student
WHERE Sage < ANY (SELECT  Sage
                                  FROM    Student
                           WHERE Sdept= ' CS ')
   AND Sdept <> ‘CS ‘ ;

转换关系

带有EXISTS谓词的子查询

子查询不返回任何数据，只产生真值"true"或逻辑假值"false"

• NOT EXIST:与exist相反

例如：

SELECT Sname
     FROM Student
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=Student.Sno AND Cno= ' 1 ');

例题：查询至少选修了学生201215122选修的全部课程的学生号码

解体思路：查询学号为x的学生，对对所有的课程y，只201215122学生选修了课程y，则x也选修了y。

SELECT DISTINCT Sno
       FROM SC SCX
       WHERE NOT EXISTS
                     (SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno = ' 201215122 '  AND 
                                    NOT EXISTS
                                    (SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
       SCZ.Cno=SCY.Cno));

四、集合

集合操作的种类

• 并操作UNION
• 交操作INTERSECT
• 差操作EXCEPT

UNION 并操作

将多个查询结果合并起来时，系统自动去掉重复元组

例如：查询计算机科学系的学生及年龄不大于19岁的学生

SELECT *
        FROM Student
        WHERE Sdept= 'CS'
        UNION
        SELECT *
        FROM Student
        WHERE Sage<=19;

INTERSECT 交操作

查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19 

EXCEPT 差操作

查询计算机科学系的学生与年龄不大于19岁的学生的差集

SELECT *
    FROM Student
    WHERE Sdept='CS'
    EXCEPT
    SELECT  *
    FROM Student
    WHERE Sage <=19;

基于派生表的查询

子查询还可以出现在from子句中，子查询生成的临时派生表成为主查询的查询对象

SELECT Sno, Cno
    FROM SC, (SELECT Sno, Avg(Grade) 
                        FROM SC
           GROUP BY Sno)
                        AS   Avg_sc(avg_sno,avg_grade)
    WHERE SC.Sno = Avg_sc.avg_sno
      and SC.Grade >=Avg_sc.avg_grade

五、数据更新

插入

插入元组：

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

INTO语句：指定要插入的属性列。如果没有指定属性列就必须按照原有的属性列顺序来写

VALUES子句

• 提供的值必须与INTO子句匹配

INSERT
INTO  Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈冬','男','IS',18);

子查询：select子句目标必须与into子句匹配值的个数/类型

INSERT
       INTO  Dept_age(Sdept,Avg_age)
              SELECT  Sdept，AVG(Sage)
              FROM     Student
              GROUP BY Sdept;                          

修改

语句格式：

UPDATE  <表名>
    SET  <列名>=<表达式>[,<列名>=<表达式>]…
    [WHERE <条件>];

例如：修改某一个元组的值

UPDATE  Student
         SET Sage=22
         WHERE  Sno=' 201215121 '; 

关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

删除

语句格式

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

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

例如：

DELETE
         FROM Student
         WHERE Sno= 201215128 ';

六、SQL中的空值

空值：不知道/不存在/无意义的值

出现的原因：

• 该属性有一个值，但目前不知道它的具体值。例如课程成绩
• 该属性不应该有值
• 由于某种原因不便于填写

判断是否有值：IS NULL/IS NOT NULL

SELECT  *
  FROM Student
  WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

约束条件：有NOT NULL约束条件的不能取空值/加了UNIQUE限制的属性不能取空值/码属性不能取空值

空值的运算：

• 空值与另一个值的算术运算结果为空值
• 比较运算结果为unknown（将会把原来的true/false扩展为unknown）

例如：找出选修1号课程的不及格的学生。

SELECT Sno
   FROM SC
   WHERE Grade < 60 AND Cno='1';

查询结果不包含缺考的考生，因为它们的Grade值为null

七、视图

视图的特点：

• 虚表，是从一个或几个基本表（或视图）导出的表
• 只存放视图的定义，不存放视图对应的数据
• 基表中的数据发生变化，从视图中查询出的数据也随之改变

视图的创建

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

with-check-option：对视图进行更新插入删除要保证插入或删除的行满足视图中定义的谓词条件

组成视图的属性列名：全部省略或全部指定

CREATE VIEW IS_Student
        AS 
        SELECT Sno,Sname,Sage
        FROM     Student
        WHERE  Sdept= 'IS';
        WITH CHECK OPTION

加上with check option之后，插入修改删除数据时会自动加上Sdept='IS’的条件

如果定义视图时使用的是select *,那么如果修改了基表的结构，就会导致视图不可用

删除视图

DROP VIEW <视图名>[CASCADE];

如果该视图上还导出了其他视图，就会使用CASCADE级联删除语句，把由它导出的视图也一块删除

删除基表时，所有到处都视图需要显式删除

查询视图

用户角度：查询视图与查询基本表相同。使用视图消解法（转换成等价的对基本表的查询）

例如：

SELECT   Sno,Sage
       FROM      IS_Student
       WHERE   Sage<20;

转换消解转换后的查询语句：

SELECT  Sno,Sage       
  FROM  Student
  WHERE  Sdept= 'IS'  AND  Sage<20;

局限：有些情况不能生成正确的查询，例如当使用group 时，应该使用having而不是where

更新视图

UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= ' 201215122 ';

转换为

UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

允许行列子集视图进行更新，但是对其他类型的视图更新不同系统不同限制，因为视图的更新不能唯一有意义地转换成对相应基本表的更新

不允许更新的视图上也不允许更新

视图的作用

• 简化用户的操作
• 使不同用户以不同方式看待同一数据
• 对重构数据库提供了一定程度的逻辑独立性（当学生关系被分成男女两个表时，可以建立一个视图将其联合）
• 提供机密数据的安全保护（用户只看到有权看到的数据）
• 更清晰的表达查询（例如，对每个同学找出它获得最高成绩的课程号，可以先定义一个每个学生最高成绩的视图，然后直接对视图与课程进行连接）