Sum-3

SQL的定义功能

表的定义

表的结构定义

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

定义主关键字

1. 在列的定义后追加PRIMARY KEY
2. 列出所有属性后，附加PRIMARY KEY (<属性1>[<属性2>..])

定义外部关键字

1. 在列的定义后追加REFERENCE <表名>(<属性>)

2. 列出所有属性后，附加

   FOREIGN KEY (<属性1> ）REFERENCE<表名>（<属性2>)

定义默认值

列的定义后追加DEFAULT

表的修改

ALTER TABLE <表名>
[ADD <列名><类型>[完整性约束]]
[DROP <列名>[<完整性约束名>]]
[ALTER COLUMN <列名> <类型>]

DROP TABLE <表名> [RESRICT|CASCADE]
--CASCADE级联操作，将与该表关联的表一同删除

索引

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

数据查询

单表查询

SQL中的投影

SELECT 姓名 FROM 学生；

SQL中的选择运算

WHERE 语句加谓词

BETWEEN AND 锁定的是闭区间

字符匹配LIKE '王%' 其中%代表任意长度，_代表单个字符

空值 IS NULL

对查询结果进行分组

--统计各课程的选课人数，并把选课人数为3的排除在外
mysql> select 课程号,count(学号) 选课人数 from 学习 group by 课程号
    -> having count(学号)<>3;
+--------+----------+
| 课程号 | 选课人数 |
+--------+----------+
| C1     |        4 |
| C3     |       16 |
| C4     |        7 |
| C5     |        8 |
+--------+----------+
4 rows in set (0.00 sec)

WHERE 作用于基本表或视图，而HAVING作用于组，用于筛选满足条件的组；

集函数

COUNT SUM AVG MAX MIN

一般取别名使用

排序

ORDER BY --- DESC/ASC

连接查询

等值与非等值连接查询

mysql> SELECT *FROM 学生 INNER JOIN 学习 ON 学生.`学号` = `学习`.`学号`;
+------+--------+------+----------+------+--------+--------+------+
| 学号 | 姓名   | 性别 | 学院代码 | 学号 | 课程号 | 教师号 | 成绩 |
+------+--------+------+----------+------+--------+--------+------+
| T01  | 王娜   | 女   | 100      | T01  | C1     | 1      |   88 |

除了=，还可用>=等数值比较运算符

自身连接查询

这是一种非自然连接，注意起别名以区分

外连接查询

左外连接：记录从左表返回，右表缺失的填充NULL，右外连接查询反是

mysql> SELECT *FROM 学生 LEFT  JOIN 学习 ON 学生.`学号` = `学习`.`学号`;
+------+--------+------+----------+------+--------+--------+------+
| 学号 | 姓名   | 性别 | 学院代码 | 学号 | 课程号 | 教师号 | 成绩 |
+------+--------+------+----------+------+--------+--------+------+
| T01  | 王娜   | 女   | 100      | T01  | C1     | 1      |   88 |
| T17  | WEJFH` | 男   | 22       | NULL | NULL   | NULL   | NULL |
+------+--------+------+----------+------+--------+--------+------+
42 rows in set (0.00 sec)

复合条件查询

集合运算连接查询

UNION INTERSECT EXCEPT [ALL] 不加all则不保留重复元组

嵌套查询

IN 谓词

ANY ALL谓词

在元组前修饰，但一般可以用><=等替换

EXISTS 谓词

EXISTS ： 代表存在量词 ，带有 EXISTS 的子查询不返回任何数据，只产生逻辑真值或逻辑假值。所有带IN谓词、比较运算符、 ANY 和 ALL 谓词的子查询都能用带EXISTS谓词的子查询等价替换。 EXISTS能完成更多的功能，效率也更高，加上否定词的NOT EXISTS功能格外强大。

IN 与 EXISTS 对比： EXISTS 比 IN 要多一句，里外对应。此外， IN 先算 出 里面 的，求出集合，比较 一次 ；而 EXISTS 先算出外面 的， 每次取一条 ，放进内 部进行查询，符合条件则输出。

--差运算：查询没有选修课程号为C4的学生信息
SELECT
	`学号`,`姓名` 
FROM
	学生
WHERE
	NOT EXISTS (
	SELECT
		* 
	FROM
		`学习`
	WHERE
	    `学生`.`学号` = `学习`.`学号` 
	AND `学习`.`课程号` = 'C4')

--除运算：查询至少选修了T01号同学选修的全部课程的学生编号
mysql> SELECT
    ->  `学号`
    -> FROM
    ->  学生
    -> WHERE
    ->  NOT EXISTS (
    ->  SELECT  * FROM `学习` AS fir
    ->  WHERE
    ->          fir.`学号`='T01' AND NOT        EXISTS(
    ->          SELECT * FROM   `学习` AS sec
    ->          WHERE sec.`学号`=`学生`.`学号`
    ->          AND sec.`课程号`=fir.`课程号`));
+------+
| 学号 |
+------+
| T01  |
| T03  |
| T16  |
+------+
3 rows in set (0.00 sec)

--查询既选修了操作系统又选修了数据库原理课程的学生学号
SELECT DISTINCT 学号 FROM 学习成绩 学习1 WHERE NOT EXISTS (
	SELECT * FROM 课程 WHERE(课程名='操作系统' OR 课程名='数据库原理	')
	AND NOT EXISTS (
	SELECT * FROM 学习成绩 学习2 WHERE 学号=学习1.学号 AND 课程号=课程.课程号)
	);

数据更新

插入数据

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

--对每一个系，求学生的平均年龄，并存入数据库
INSERT INTO Dept_age (Sdept, Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;

删除数据

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

DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept ='计算机');

修改数据

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

--将计算机学院全体学生的成绩置零
UPDATE SC SET Grade=0 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept ='计算机');

视图

视图的优点

1. 简化用户操作
2. 灵活
3. 提供一定的逻辑独立性
4. 可以有安全保护

建立视图

CREATE VIEW < 视图名>[(<列名>[, <列名>]…)]

AS <子查询> [WITH CHECK OPTION];

CREATE VIEW CS AS SELECT * FROM S# WHERE SD='计算机' WITH CHECK OPTION;
--带了check option 在插入删除更新视图时会检查where后的条件，没有会自动补上；

除此以外，视图可以建立在多个表上，也可以建立在其他视图上，视图是一种虚表；

删除视图

DROP VIEW <视图名> [CASCADE]

CASCADE级联操作，缺省时，若其他视图建立在该视图上，则拒绝删除；加上该条件时，会连同建立在此基础上的视图一起删除；这是视图删除特有的；删除视图与删除表互不影响；

查询视图

SELECT Sno,Sage FROM CS_VIEW WHERE Sage<20;

更新视图

UPDATE CS_VIEW SET Sname='Tim' WHERE Sno='1';

在有些DBMS中，视图的权限比表小，更新操作会受限，比如视图包含计算得到的数据则不允许更新；

数据控制

授权

GRANT <权限> [ON <对象类型><对象名>] [TO <用户>] [WITH GRANT OPTION]

GRANT UPDATE(成绩),SELECT ON TABLE 学习	TO user4

收回权限

REVOKE <权限> [ON <对象类型><对象名>] [FROM <用户>]

REVOKE UPDATE(成绩) ON TABLE 学习 FROM user4;

知识关联

SQL语句包含数据定义，数据操作，数据控制语句；数据查询只有SELECT语句，却兼有关系代数和元组演算的特点，功能性更比关系代数的查询强，操作也更为复杂，分为单表查询、连接查询、嵌套查询，同时也提供了关系代数做不到的排序、分类、计算简单的统计数据等功能；

SQL中的视图可以看作虚表，在用户层面，功能与表无异，但在操作上却与表隔离，只存在定义却不存储数据，一定程度上减少了数据冗余，维护数据安全。

重难点

数据查询（尤其是嵌套查询执行差、除运算）

数据更新

课后题

3.2 解释下列术语：

SQL模式：SQL语言所支持的关系数据库的体系结构，包括视图、基本表、存储文件三个层次。

基本表：简称基表。是数据库中实际存在的表，在SQL中一个关系对应于一个表。

视图：SQL用视图概念支持非标准的外模式概念。视图是从一个或几个基表导出的表，虽然它也是关系形式，但它本身不实际存储在数据库中，只存放对视图的定义信息（没有对应的数据）。因此，视图是一个虚表（Virtual Table）或虚关系，而基表是一种实关系（Practical Relation）。

单表查询：只涉及到一个关系的查询。

连接查询：同时涉及两个以上的表的查询。

嵌套查询：将一个查询块嵌套在另一个查询块的WHERE子句或HAVING条件中的查询。

3.5 SQL是如何实现实体完整性、参照完整性和用户定义完整性的？

实体完整性简单的说，就是将表中的每一行看作一个实体。实体完整性要求表的标示符列或主键的完整性。可以通过建立唯一索引、PRIMARY KEY约束、UNIQUE约束，以及列的IDENTITY属性来实施实体完整性。

参照完整性维持被参照表和参照表之间的数据一致性，通过主键（PRIMARY KEY）约束和外键（FOREIGN KEY）约束来实现。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。在被参照表中，当其主键值被其他表所参照时，该行不能被删除也不允许改变。在参照表中，不允许参照不存在的主键值。

用户定义完整性使得可以定义不属于其它任何完整性分类的特定业务规则。所有的完整性类型都支持用户定义完整性。