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

结构化查询语言Structured Query Language ：

数据查询、数据定义、数据操纵、 数据控制 

3. SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。
4. SQL语言既是自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。
自含式语言可以独立使用交互命令，适用于终端用户、应用程序员和DBA；
嵌入式语言使其嵌入在高级语言中使用，供应用程序员开发应用程序。
5. SQL语言具有:

数据查询（QUERY）
数据定义（DEFINITION）
数据操纵（MANIPULATION）
数据控制（CONTROL）

四种语言一体化的功能。

由Enterprise Manager产生的SQL脚本是一个后缀名为.sql的文件
使用查询分析器的熟练程度是衡量一个SQL Server用户水平的标准。

文件组

文件组（File Group）是将多个数据文件集合起来形成的一个整体
主要文件组+次要文件组
一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用
日志文件不分组，它不能属于任何文件组

sql

SQL语言使用数据定义语言（DATA DEFINITION LANGUAGE，简称DDL）实现其数据定义功能，可对数据库用户、基本表、视图、索引进行定义和撤消。

3.2 学生-课程模式 S-T :
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
课程表：Course(Cno,Cname,Cpno,Ccredit)
学生选课表：SC(Sno,Cno,Grade)

创建

创建数据表
数据表是关系数据库的基本组成单位，它物理地存储于数据库的存储文件中。

1. 创建一个数据表时主要包括以下几个组成部分：
   （1）字段名（列名）：字段名可长达128个字符。字段名可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号(@)。同一表中不许有重名列；
   （2）字段数据类型
   （3）字段的长度、精度和小数位数；

①字段的长度：指字段所能容纳的最大数据量，但对不同的数据类型来说，长度对字段的意义可能有些不同。
对数值类的数据类型而言，长度则代表字段使用多少个字节来存放数字。
对BINARY、VARBINARY、IMAGE数据类型而言，长度代表字段所能容纳的字节数。
②精度和小数位数
精度是指数中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分；
小数位数则是指数字小数点右侧的位数。
例如：数字12345.678，其精度为8，小数位数为3；
所以只有数值类的数据类型才有必要指定精度和小数位数。

（4）NULL值与DEFAULT值
DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。

[例3-1]
用SQL命令创建一个教学数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件物理地存放在D：盘的根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在D：盘的根目录下，文件名为TeachLog.ldf，初始存储空间大小为10MB，最大存储空间为25MB，存储空间自动增长量为5MB。

CREATE DATABASE Teach
ON
(	NAME=Teach_Data,
	FILENAME='D:\TeachData.mdf',
	SIZE=10,
	MAXSIZE=50,
	FILEGROWTH=5)
LOG ON
(	NAME=Teach_Log,
	FILENAME='D:\TeachLog.ldf',
	SIZE=5,
	MAXSIZE=25,
	FILEGROWTH=5) 

2. 基本表的定义，删除与修改

   CREATE TABLE创建数据表：
	CREATE TABLE <表名>(<列定义>[{，<列定义>|<表约束>}])
<表名>是合法标识符，最多可有128个字符，如S,SC,C，不允许重名。
<列定义>：<列名><数据类型>[DEFAULT]  [{<列约束>}]
DEFAULT：若是某字段设置有默认值，当该字段未被输入数据时，则以该默认值自动填入该字段。

例:建立一学生表

USE STUDENT
CREATE TABLE S
(SNO CHAR(8) ,
SN VARCHAR(20),
AGE INT,
SEX CHAR(2) DEFAULT '男' ,
DEPT VARCHAR(20));

执行该语句后，便产生了学生基本表的表框架，此表为一个空表。
其中，SEX列的缺省值为“男”。

3. 定义完整性约束
   上例为创建基本表的最简单形式，还可以对表进一步定义，如主键、空值的设定，使数据库用户能够根据应用的需要对基本表的定义做出更为精确和详尽的规定。
   在SQL SERVER中，对于基本表的约束分为列约束和表约束。
   列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；
   表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，定义表约束时必须指出要约束的那些列的名称。

在SQL SERVER中可以定义五种类型的完整性约束:
（1）NULL/NOT NULL
是否允许该字段的值为NULL。
NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意思。
当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。
如主码列就不允许出现空值，否则就失去了唯一标识一条记录的作用
只能用于定义列约束

例1 建立一个S表，对SNO字段进行NOT NULL约束。

USE STUDENT
CREATE TABLE S
(SNO CHAR(10) NOT NULL，
SN VARCHAR(20),
AGE INT,
SEX CHAR(2) DEFAULT ’男’ ,
DEPT VARCHAR(20));

当SNO为空时，系统给出错误信息，无NOT NULL约束时，系统缺省为NULL。

PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：
①在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；
②对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。
注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。
PRIMARY KEY既可用于列约束，也可用于表约束。

例4 建立一个S表，定义SNO为S的主键

USE STUDENT
CREATE TABLE S
(SNO CHAR(5) PRIMARY KEY,
SN CHAR(8),
AGE NUMERIC(2));

PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键。

例5 建立一个选课关系SC表，定义SNO+CNO为SC的主键

USE STUDENT
CREATE TABLE SC
(SNO CHAR(5) NOT NULL,
CNO CHAR(5) NOT NULL,
SCORE NUMERIC(3),
PRIMARY KEY(SNO,CNO));

（4）FOREIGN KEY约束
FOREIGN KEY约束指定某一个列或一组列作为外码，其中，包含外码的表称为从表，包含外码所引用的主码或唯一键的表称主表。
系统保证从表在外码上的取值要么是主表中某一个主码值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。
FOREIGN KEY既可用于列约束，也可用于表约束。

例6 建立一个SC表，定义SNO,CNO为SC的外部键。

USE STUDENT
CREATE TABLE SC
(SNO CHAR(5) ,
CNO CHAR(5) , 
SCORE NUMERIC(3),
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (CNO) REFERENCES C(CNO)
);

（5）CHECK约束
CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。
CHECK既可用于列约束，也可用于表约束。

例7 建立一个SC表，定义SCORE 的取值范围为0到100之间。

USE STUDENT
CREATE TABLE SC
(SNO CHAR(5),
CNO CHAR(5),
SCORE NUMERIC(5,1) CHECK(SCORE>=0 AND SCORE <=100));

例8 建立包含完整性定义的学生表

USE STUDENT
CREATE TABLE S
(SNO CHAR(6) PRIMARY KEY,
SN CHAR(8) NOT NULL,
AGE NUMERIC(2) NOT NULL
CHECK (AGE BETWEEN 15 AND 50),
SEX CHAR(2) DEFAULT '男',
DEPT CHAR(10) NOT NULL);

修改基本表

由于应用环境和应用需求的变化，经常需要修改基本表的结构，比如，增加新列和完整性约束、修改原有的列定义和完整性约束等。
SQL语言使用ALTER TABLE命令来完成这一功能：

1. ADD方式
   用于增加新列和完整性约束，定义方式同CREATE TABLE语句中的定义方式相同：
   ALTER TABLE <表名> ADD <列定义> | <完整性约束定义>

例9 在S表中增加一个班号列和住址列。

ALTER TABLE S 
ADD
CLASS_NO CHAR(6),
ADDRESS CHAR(40)

注意：使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束 。

例10 在SC表中增加完整性约束定义，使SCORE在0-100之间。

ALTER TABLE SC 
ADD 
CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100)

2. ALTER 方式
   用于修改某些列：

ALTER TABLE<表名>
	ALTER COLUMN <列名><数据类型>

例11 把S表中的SNO列加宽到8位字符宽度

ALTER TABLE S ALTER COLUMN SNO CHAR(8)

注意：使用此方式有如下一些限制：
①不能改变列名；
②不能将含有空值的列的定义修改为NOT NULL约束；
③若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；
④只能修改NULL|NOT NULL约束，其它类型的约束在修改之前必须先删除，然后再重新添加修改过的约束定义。

删除数据库

3.DROP方式
删除完整性约束定义，其语法格式为：

ALTER TABLE<表名>
	DROP CONSTRAINT <约束名>

例12 删除S表中的AGE_CHK约束

USE STUDENT
ALTER TABLE S
DROP 
CONSTRAINT AGE_CHK

DROP DATABASE database_name [,...n] 

[例3-3] 删除数据库Teach。

DROP DATABASE Teach 

改变基本表的名字

RENAME <旧表名> TO <新表名>

例13 将S表的名字更改为STUDENT

USE STUDENT 		
RENAME S TO STUDENT

删除基本表

删除后，表中的数据和在此表上所建的索引都被删除，在SQL Server 2000中，建立在表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。
如果重新恢复该表，这些视图可重新使用。

删除表的语法格式：
		DROP TABLE <表名>

例14 删除表STUDENT

USE STUDENT 	
DROP TABLE STUDENT

注意：只能删除自己建立的表，不能删除其他用户所建的表。

索引的作用

在日常生活中我们会经常遇到索引,例如？
如果把数据库表比作一本书，则表的索引就如书的目录一样，通过索引可大大提高查询速度。
索引是数据库随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。
在SQL SERVER中，行的唯一性也是通过建立唯一索引来维护的。
索引的作用可归纳为：

1. 加快查询速度；
2. 保证行的唯一性。
   索引的分类
3. 按照索引记录的存放位置可分为聚集索引与非聚集索引
   聚集索引：按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。
   非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。
4. 唯一索引：唯一索引表示表中每一个索引值只对应唯一的数据记录。
   当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集的唯一性索引。
   而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个聚集索引。

建立索引

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [次序] [{,<列名>}] [次序]…)
UNIQUE表明建立唯一索引。
CLUSTER表示建立聚集索引。
次序用来指定索引值的排列顺序，可为ASC（升序）或DESC（降序），缺省值为ASC。

为表SC在SNO和CNO上建立唯一索引。

CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)

执行此命令后，为SC表建立一个索引名为SCI的唯一索引。
此索引对SC表中的行先按SNO的递增顺序索引，对于相同的SNO，又按CNO的递增顺序索引。

例16 为教师表T在TN上建立聚集索引。

CREATE CLUSTER INDEX TI ON T(TN)

执行此命令后，为T表建立一个索引名为TI的聚集索引，T表中的记录将按照TN值的升序存放。

注意：

1. 改变表中的数据（如增加或删除记录）时，索引将自动更新。索引建立后，在查询使用该列时，系统将自动使用索引进行查询。
2. 索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。

删除索引

DROP INDEX 数据表名.索引名

例17 删除表SC的索引SCI。

	
DROP INDEX SC.SCI

SELECT命令的格式与基本使用

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
	〈列名〉[AS 别名1] [{，〈列名〉[ AS 别名2]}]
	[INTO 新表名]
	FROM〈表名1或视图名1〉[[AS] 表1别名] [{，〈表名2或视图名2〉[[AS] 表2别名]}]
	[WHERE〈检索条件〉]
	[GROUP BY <列名1>[HAVING <条件表达式>]]
	[ORDER BY <列名2>[ASC|DESC]] 

SELECT语句的执行过程是:
根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。
如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。
如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。
如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。

[例1]  查询全体学生的学号、姓名和年龄。
SELECT SNo, SN, Sage
FROM S 

[例2]  查询学生的全部信息。
SELECT * 
FROM S 

[例3]  查询选修了课程的学生号。
SELECT DISTINCT SNo  
FROM SC

[例4]  查询全体学生的姓名、学号和年龄。
SELECT SN Name, SNo, Sage 
FROM S
SELECT SN AS Name, SNo, Sage

[例5]  查询全体学生的姓名、出生年份和所在系，要求用大写字母表示所有系名。
SELECT SN, 2021-Sage, upper(sdept) 
FROM S 


[例6]查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。
SELECT SN, 2021-Sage, lower(sdept) 
FROM SC

select    top  2    *    from   s 
select   学号 , 姓名 , 性别   from  s 
select   学号 , 课程号 , 成绩,  成绩+ 5   
	from     sc 
select   学号, 课程号, 成绩 as  原成绩, 调整成绩=成绩+ 5
	from     sc
select   学号, 姓名,   ‘其性别’,    性别    from   s

	[例9]  查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。
SELECT SNo, CNo, Score
FROM SC
WHERE (CNo = 'C1' OR CNo = 'C2') AND (Score >= 85) 

[例10]  查询工资在1000至1500元之间的教师的教师号、姓名及职称。
SELECT TNo,TN,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500 
==WHERE Sal>=1000 AND Sal<=1500 
[例11]  查询工资不在1000至1500之间的教师的教师号、姓名及职称。
SELECT TNo,TN,Prof
FROM T
WHERE Sal NOT BETWEEN 1000 AND 1500 


利用“IN”操作可以查询属性值属于指定集合的元组。 
[例12]  查询选修C1或C2的学生的学号、课程号和成绩。
	SELECT SNo, CNo, Score 
	FROM SC 
	WHERE CNo IN('C1'，'C2') 
利用“NOT IN”可以查询指定集合外的元组。
[例13]  查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。
	SELECT SNo, CNo, Score
	FROM SC 
	WHERE CNo NOT IN('C1'，'C2') 

部分匹配查询 
当不知道完全精确的值时，用户可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）

               <属性名> LIKE <字符串常量>  
[例14]  查询所有姓张的教师的教师号和姓名。
SELECT TNo, TN 
FROM T
WHERE TN LIKE '张%'
[例15]  查询姓名中第二个汉字是“力”的教师号和姓名。
SELECT TNo, TN 
FROM T
WHERE TN LIKE‘_ _力%'

    如果要查询的字符串本身就含有通配符“%”或“   ”，就要使用ESCAPE’	<换码字符>’短语，对通配符进行转义。
例：查询DB_Design课程的课程号
        SELECT CNO
        FROM C
        WHERE CNAME LIKE ‘DB\_Design’ ESCAPE ‘\’

练习：查询以“DB_”开头，且倒数第3个字符为a,最后一个字符为b的课程的详细情况。
分析下列查询语句执行的功能和结果.
select    *   from  s
      where   联系电话  like  ‘ %2 ’查询电话尾数为2的信息
select    *   from   s
      where    姓名     like  ‘ 陈_ _ ’查询陈姓且单名的信息
select    *   from   s
      where    学号    like  ‘ %[1-3] ’查询学号尾数为123的信息
select    *   from   s
      where   学号  like  ‘ %[ ^123] ’查询学号尾数不为123的信息
      where   学号  not   like   ‘ %[ 123] ’


写出实现下列功能的查询语句。
在s表中，查询学号中含有1的记录信息

select    *    from    s    
where   学号    like   ‘ %1% '

在s表中，查询电话号码第7位为4和6的记录信息
select    *    from    s  
where    联系电话   like   ‘ _ _ _ _ _ _ [46] _’

空值查询
某个字段没有值称之为具有空值（NULL）
空值不同于零和空格，它不占任何存储空间

[例16]  查询没有考试成绩的学生的学号和相应的课程号。
SELECT SNo, CNo
FROM SC
WHERE Score IS NULL 

[例17]  求学号为S1学生的总分和平均分。
	SELECT SUM(Score) AS TotalScore, AVG(Score) AS AveScore
	FROM SC
	WHERE (SNo = 'S1') 
[例18]  求选修C1号课程的最高分、最低分及之间相差的分数。
	SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score) －MIN(Score) AS Diff
	FROM SC
	WHERE (CNo = 'C1')
[例19]  求学校中共有多少个系。
	SELECT COUNT(DISTINCT Dept) AS DeptNum
	FROM S 
例20]  统计有成绩同学的人数。
	SELECT COUNT (Score) 
	FROM SC
成绩为零的同学他计算在内，没有成绩（即为空值）的不计算。

[例21]  利用特殊函数COUNT(*)求计算机系学生的总数。
	SELECT COUNT(*) FROM S
	WHERE Dept='计算机'
COUNT（*）用来统计元组的个数，不消除重复行，
不允许使用DISTINCT关键字。

GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

[例22]  查询各个教师的教师号及其任课的门数。
SELECT TNo,COUNT(*) AS C_Num
FROM TC
GROUP BY TNo
GROUP BY子句按TNo的值分组，所有具有相同TNo的元
组为一组，对每一组使用函数COUNT进行计算，统计出各位教
师任课的门数

若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句

[例23]  查询选修两门以上课程的学生的学号和选课门数。
SELECT SNo, COUNT(*) AS SC_Num
FROM SC
GROUP BY SNo
HAVING (COUNT(*) >= 2)
 GROUP BY子句按SNo的值分组，所有具有相同SNo的元组为一
组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门
数。HAVING子句去掉不满足COUNT（*）>=2的组 
 

当需要对查询结果排序时，应该使用ORDER BY子句，ORDER BY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。

[例24]  查询选修C1 的学生学号和成绩，并按成绩降序排列。
SELECT SNo, Score
FROM SC
WHERE (CNo = 'C1')
ORDER BY Score DESC

[例25]  查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。
SELECT SNo, CNo, Score
FROM SC
WHERE (CNo IN ('C2', 'C3', 'C4', 'C5'))
ORDER BY SNo, Score DESC


[例26]  求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
SELECT SNo, SUM(Score) AS TotalScore
FROM SC
WHERE (Score >= 60)
GROUP BY SNo
HAVING (COUNT(*) >= 3)
ORDER BY SUM(Score) DESC 

ORDER BY 2 DESC ; “2”代表查询结果的第二列 

连接查询：一个查询需要对多个表进行操作
表之间的连接：连接查询的结果集或结果表
连接字段：数据表之间的联系是通过表的字段值来体现的
连接操作的目的：从多个表中查询数据
表的连接方法 :

表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件

利用关键字JOIN进行连接：当将JOIN 关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件

等值连接与非等值连接 
[例27]  查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。
方法1：
SELECT T.TNo,TN,CNo
FROM T,TC
WHERE (T.TNo = TC. TNo) AND (TN='刘伟') 
连接条件 ，当比较运算符为“＝”时，称为等值连接。其他情况为非等值连接。
方法2：
SELECT T.TNo, TN, CNo
FROM T INNER JOIN TC
ON T.TNo = TC.TNo
WHERE (TN = '刘伟')

引用列名TNo时要加上表名前缀，这是因为两个表中的列名相同，
必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。

[例28]  查询所有选课学生的学号、姓名、选课名称及成绩。
SELECT S.SNo,SN,CN,Score
FROM S,C,SC
WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo 

[例29]  查询每门课程的课程号、课程名、及选课人数。
SELECT CNo,CN,COUNT(SC.SNo)
FROM C,SC
WHERE  SC.CNo=C.CNo
GROUP BY SC.CNo,CN


自身连接 
[例30]  查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。 
方法1：
SELECT X.TN,X.Sal AS  Sal_a,Y.Sal AS Sal_b
FROM T AS X ,T AS Y 
WHERE X.Sal>Y.Sal AND Y.TN='刘伟'

方法2：
SELECT X.TN, X.Sal,Y.Sal 
FROM T AS X INNER JOIN T AS Y 
ON X.Sal>Y.Sal AND Y.TN='刘伟'

外连接
在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。
符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。

[例31]  查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。
SELECT S.SNo,SN,CN,Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo=SC.SNo
LEFT OUTER JOIN C
ON C.CNo=SC.CNo  

在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询。

返回一个值的子查询

[例32]  查询与“刘伟”老师职称相同的教师号、姓名
	SELECT TNo,TN
	FROM T
	WHERE Prof= (	SELECT Prof
	       		FROM T
	       		WHERE TN= '刘伟')  

返回一组值的子查询 
使用ANY
[例33]  查询讲授课程号为C5的教师姓名。
SELECT TN
FROM T
WHERE (TNo = ANY (SELECT TNo
         		    FROM TC
         		    WHERE CNo = 'C5')) 
SELECT TN
FROM T,TC
WHERE T.TNo=TC.TNo
AND TC.CNo= 'C5 '

= ANY即为in

[例34]  查询其他系中比计算机系某一教师工资高的教师的姓名和工资。
SELECT TN, Sal
FROM T
WHERE (Sal > ANY (	SELECT Sal
         			FROM T
         			WHERE Dept = '计算机'))
AND (Dept <> '计算机') 

SELECT TN, Sal
FROM T
WHERE Sal > (	SELECT MIN(Sal)
         		FROM T
         		WHERE Dept = '计算机') 
AND Dept <> '计算机' 


使用ALL 
[例35]  查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。
	SELECT TN, Sal
	FROM T
	WHERE (Sal > ALL (	SELECT Sal	         					FROM T
         			WHERE Dept = '计算机'))
	AND (Dept <> '计算机') 
 (Sal > ALL (	SELECT Sal即为Sal > (	SELECT MAX(Sal) 
[例36]  查询不讲授课程号为C5的教师姓名。
	SELECT DISTINCT TN
	FROM T
	WHERE ('C5' <> ALL (	SELECT CNo
         		        	FROM TC
         		        	WHERE TNo = T.TNo))
 <> ALL 表示not in

子查询包含普通子查询和相关子查询。
前面所讲的子查询均为普通子查询，而例36(查询不讲授课程号为C5的教师姓名)中子查询的查询条件引用了父查询表中的属性值（T表的TNO值），我们把这类查询称为相关子查询。

如例36表T中每的一行即每个教师记录都要执行一次子查询以确定该教师是否讲授C5这门课，当 C5不是教师的任一门课时，则该教师被选取。

二者的执行方式不同：
普通子查询的执行顺序是：
首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。
普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。

相关子查询的执行顺序是：
首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询；
然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。
然后再取父查询表中的下一行记录，重复执行这一过程，直到处理完父查询表中的每一行数据

例：找出每个学生超过他选修课程平均成绩的课程号。
   SELECT Sno，Cno  
   FROM SC x
   WHERE Grade >=（SELECT AVG（Grade）
                   FROM SC y
                   WHERE y.Sno=x.Sno）

内层查询是求一个学生所选修课程平均成绩的，是哪个学生的平均成绩要看参数x.Sno的值，该值是与父查询相关的，因此这类查询称为相关子查询。
这个语句一种可能执行的过程是：
1）从外层查询中取出sc的一个元组x，将元组x的Sno值传送给内层的查询；
SELECT AVG（Grade）
FROM SC y
WHERE y.Sno=‘01’
2）执行内层查询，得到成绩平均值，用该值代替内层查询，得到外层查询：
SELECT Sno，Cno
FROM SC x WHERE Grade>=平均值
3）执行这个查询，得到结果；
然后外查询取下一个元组重复以上三个步骤，直到外层的SC元组全部处理完。得到最终结果。

使用EXISTS

带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假” 。
当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。
含有IN的查询通常可用EXISTS表示，但反过来不一定。

例37 查询选修所有课程的学生姓名。
SELECT SN 
FROM S 
WHERE NOT EXISTS 
      (SELECT * 
       FROM C 
       WHERE NOT EXISTS
            (SELECT * FROM SC
             WHERE SNO = S.SNO
             AND CNO=C.CNO)
       )

查询这样的学生，没有一门课程是他们不选修的。
换言之：在表s中找学生，在c中不存在一门课程，这个学生没有学。

例38：检索所学课程包含学生95002所学课程的学号。
思路如下：
           1  在sc表中找一个学生（sno）/*在sc表中找*/
           2  对于95002学的每一门课（cno） /*在sc表中找*/
           3  该学生都学了    /*在sc表中存在一个元组*/
     改写成双重否定形式：
          在sc表中找一个学生（sno），不存在95002学的一门课（cno），该学生没有学
           这样可以很容易写出select语句

 select distinct sno
    from sc as x
    where not exists
              (select * 
                from sc as y
                where y.sno=‘95002’  and
                not exists
                      (select * from sc as z
                       where z.sno=x.sno and z.cno=y.cno)

另一个例子:查询所有选修了‘C01’号课程的学生姓名。

思路分析：
本查询涉及S和SC关系
在S中依次取每个元组的Sno值，用此值去检查SC关系
若SC中存在这样的元组，其Sno值等于此S.Sno值，并且其Cno= ‘C01’，则取此S.Sname送入结果关系

用嵌套查询
     SELECT Sname
     FROM S
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=S.Sno 
                                    AND Cno= ' C01 ‘
                   )  

用连接运算
SELECT Sname
FROM S, SC
WHERE S.Sno=SC.Sno AND SC.Cno= '1'

观察下例演示：
查询没有选修‘C01’号课程的学生姓名和所在系。

如果利用多表连接和子查询分别实现这个查询，则有如下几种形式可以实现：

（1）用多表连接实现：
           select  distinct sname,sdept
           from s,sc
           where s.sno=sc.sno and cno!=‘C01’
（2）用嵌套子查询实现：
  在子查询中否定
              select sname,sdept
              from s
              where sno in (select sno   from sc 
                                       where cno!=‘C01’)                       
 在外层中否定：
       select  sname,sdept
       from s
       where sno not in(select sno from sc
                                    where cno=‘C01’)

（3）用相关子查询实现：
   在子查询中否定
     select sname,sdept 
     from s
     where exists(select * from sc
                            where sno=s.sno and cno!=‘C01’)
   在外层中否定
      select sname,sdept 
     from s
     where not exists(select * from sc
                               where sno=s.sno and cno=‘C01’)

问题：上述5种实现方式，哪些是正确的？ 产生不同结果的原因是什么？

对多表连接，所有的条件都是在连接之后的结果表上进行的，而且是逐行进行判断，一旦发现满足要求的数据，则此行即做为结果产生。因此结果必然包含没有选修‘c01’的学生，也包含了选修了‘c01’同时又选修了其他课程的学生

对含有嵌套子查询，首先执行子查询，然后在子查询结果基础上再执行外层查询，而在子查询中也是逐行进行判断，当发现有满足条件的数据，即将此行数据作为外层查询的一个比较条件。分析该查询，，若将否定放在子查询中，则结果必然包含没有选修‘c01’的学生，也包含选修了‘c01’同时又选修了其他课程的学生 。显然否定范围不够。

合并查询
合并查询就是使用UNION 操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。
参加合并查询的各子查询的使用的表结构应该相同。
[例38] 从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。

SELECT SNo AS 学分, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S1')
GROUP BY SNo
UNION
SELECT SNo AS 学分, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S5')
GROUP BY SNo  

SQL数据更新

SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。
1 插入数据记录
插入数据是把新的记录插入到一个存在的表中。
语句INSERT INTO
有几种方式：
插入一行新记录；
插入一行的部分数据值；
插入多行记录（也就是插入子查询结果）；

插入一行新记录
INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)

列名的排列顺序不一定要和表定义时的顺序一致。
但当指定列名表后，VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。

例1 在S表中插入一条学生记录（学号：S7；姓名：郑冬；性别：女；年龄：21；系别：计算机）。

INSERT INTO  S
VALUES ('s7','郑冬','女',21,'计算机')

注意：
必须用逗号将各个数据分开，字符型数据要用单引号括起来。
INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。

例2 在SC表中插入一条选课记录（’S7’,’C1’）。

INSERT INTO SC (SNO,CNO)
VALUES ('s7',‘c1')

将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中
对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值，如上例的Grade即赋空值。
但在表定义时有NOT NULL约束的属性列不能取空值。

插入多行记录
用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。
INSERT INTO <表名> [(<列名1>[,<列名2>…])]
子查询
例3:求出各系教师的平均工资，把结果存放在新表AVGSAL中。

第一步：建立新表AVGSAL
CREATE TABLE AVGSAL
(DEPARTMENT VARCHAR(20),  /*系名*/
 AVGSAL SMALLINT)         /*教师平均工资*/
 第二步：插入数据
INSERT INTO AVGSAL
SELECT DEPT,AVG(SAL) 
FROM T
GROUP BY DEPT 

练习：对每一个系，求学生的平均年龄，并把结果存入数据库。

修改数据记录
使用UPDATE语句对表中的一行或多行记录的某些列值进行修改：
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]…
[WHERE <条件>]

修改方式：
1 修改一行
2 修改多行
3 用子查询选择要修改的行
4 用子查询提供要修改的值

例5 把刘伟教师转到信息系。
UPDATE T 
SET DEPT='信息' WHERE TN='刘伟'
例6 将所有学生年龄增加1岁
UPDATE S SET AGE=AGE+1


例7 把教师表中工资小于等于1000元的讲师的工资提高20%。
UPDATE T 
SET SAL=1.2*SAL
WHERE PROF='讲师' 
      AND SAL <=1000
例8 把讲授C5课程的教师的岗位津贴增加100元。
UPDATE T 
SET COMN=COMN+100
WHERE TNO IN 
              (SELECT TNO 
                FROM TC
                WHERE CNO='C5')

子查询的作用是得到讲授C5课程的教师号。


例8 把讲授C5课程的教师的岗位津贴增加100元。
UPDATE T 
SET COMN=COMN+100
WHERE TNO IN 
              (SELECT TNO 
                FROM TC
                WHERE CNO='C5')

子查询的作用是得到讲授C5课程的教师号。

删除数据记录
使用DELETE语句可以删除表中的一行或多行记录，其语法格式为：
DELETE
FROM<表名>
[WHERE <条件>]
其中，
<表名>是指要删除数据的表。
WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。

三种删除方式：
1 删除一行记录
2 删除多行记录
3 利用子查询选择要删除的行
例10  删除刘伟教师的记录。
DELETE 
FROM T 
WHERE TN=’刘伟’
例11 删除所有教师的授课记录
DELETE 
FROM TC
执行后，TC表即为一个空表，但其定义仍存在。

例12 删除刘伟教师授课的记录。
DELETE
FROM TC 
WHERE TNO=  
     (SELECT TNO
      FROM T
      WHERE TN=’ 刘伟’)

视图是虚表，其数据不存储，其记录来自基本表，只在数据库中存储其定义。
视图在概念上与基本表等同。
1 定义和删除视图
1.1 定义视图
定义视图使用语句CREATE VIEW，其语法格式为：
CREATE VIEW <视图名>[(<视图列表>)]
AS <子查询>
其中，<视图列表>为可选项，省略时，视图的列名由子查询的结果决定。

子查询中不许使用ORDER BY 子句和DISTINCT短语，若需排序，则可在视图定义后，对视图查询时再进行排序。
视图创建后，只在数据字典中存放视图的定义，而其中的子查询SELECT语句并不执行。只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。

例1 创建一个计算机系教师情况的视图SUB_T。
CREATE VIEW SUB_T
AS 
SELECT TNO,TN,PROF
FROM T 
WHERE DEPT ='计算机' 

例2 创建一学生情况视图S_SC_C（包括学号、姓名、课程名及成绩）。
CREATE VIEW S_SC_C(SNO, SN, CN, GRADE) 
AS 
SELECT S.SNO, SN, CN, GRADE
FROM S, C, SC
WHERE S.SNO = SC.SNO AND 
               SC.CNO = C.CNO
例3 创建一学生平均成绩视图S_AVG
CREATE VIEW S_AVG (SNO,AVG)
AS 
SELECT SNO, AVG (GRADE)
FROM SC 
GROUP BY SNO

删除视图
视图定义后可随时删除：
DROP VIEW <视图名>

例4 删除计算机系教师情况的视图SUB_T。
DROP VIEW SUB_T

视图删除后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任何意义。用户应该把这些视图删除。
2 查询视图
视图定义后，对视图的查询操作如同对基本表的查询操作一样。

例5　查找视图SUB_T中职称为教授的教师号和姓名。
SELECT TNO,TN
FROM SUB_T 
WHERE PROF=‘教授’

首先从数据字典中找到SUB_T的定义;
然后把此定义和用户的查询结合起来，转换成等价的对基本表T的查询;
这一转换过程称为视图消解（View Resolution）
相当于执行什么查询？

SELECT TNO,TN
FROM T 
WHERE DEPT =’计算机’　AND PROF=’教授’

由上例可以看出，当对一个基本表进行复杂的查询时，可以先对基本表建立一个视图，然后只需对此视图进行查询，这样就不必再输入复杂的查询语句，而将一个复杂的查询转换成一个简单的查询，从而简化了查询操作。

视图消解法的局限
一般情况下，视图查询的转换是直截了当的，但有些情况下，这种转换不能直接进行，查询时就会出现问题。如下例：

     例：在S_AVG视图（例3）中查询平均成绩在80分以上的学生学号和平均成绩
      SELECT *
FROM   S_AVG
WHERE  AVG>=80；

 S_AVG视图的子查询定义： 
            CREATE VIEW  S_AVG (SNO，AVG)
             AS 
SELECT  Sno，AVG(Grade)
FROM  SC
GROUP BY Sno；

错误：
SELECT Sno，AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=80
GROUP BY Sno；

正确：
SELECT  Sno，AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=80；

更新视图
由于视图是一张虚表，所以对视图的更新，最终实际上是转换成对基本表的更新。
其更新操作包括插入、修改和删除数据，
其语法格式如同对基本表的更新操作一样。

插入（INSERT）
例6 向计算机系教师视图SUB_T中插入一条记录（教师号：T6；姓名：李丹；职称：副教授）。
INSERT INTO SUB_T
VALUES (‘T6’,‘李丹’,‘副教授’)
系统在执行此语句时，首先从数据字典中找到SUB_T的定义，然后把此定义和插入操作结合起来，转换成等价的对基本表T的插入。相当于执行以下操作：
INSERT INTO T
VALUES (‘T6’,’ 李丹’，’副教授’，’计算机’)

3.2 修改（UPDATE）
例7 将计算机系教师视图SUB_T中刘伟的职称改为“副教授“。
UPDATE SUB_T
SET PROF = ‘副教授’
WHERE TN = ‘刘伟’

转换成对基本表的修改操作：
UPDATE T
SET PROF=’副教授’
WHERE TN=’刘伟’ AND DEPT=’计算机’

例8  删除计算机系教师视图SUB_T中刘伟教师的记录。
DELETE 
FROM SUB_T
WHERE TN=’刘伟’
转换成对基本表的删除操作：
DELETE 
FROM T
WHERE TN=’刘伟’ AND DEPT=’计算机’

更新视图的限制：
一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
例：视图S_AVG为不可更新视图。
UPDATE S_AVG
SET AVG=90
WHERE Sno= ‘S1’；
这个对视图的更新无法转换成对基本表SC的更新
目前各个关系数据库系统一般都只允许对行列子集视图进行更新；对其他类型视图的更新不同系统有不同限制

视图的优点（作用）

由于视图中的数据不是存放在视图中的，即视图没有相应的存储空间，对视图的一切操作最终都要转换成对基本表的操作，这样看来使操作更加复杂，那么为什么还要使用视图呢？
使用视图有如下几个优点：

1. 利于数据保密，对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。
   例如,对教师表创建了计算机系视图，本系教师只能使用此视图，而无法访问其他系教师的数据。
2. 简化查询操作，为复杂的查询建立一个视图，用户不必键入复杂的查询语句，只需针对此视图做简单的查询即可。如例5查找视图SUB_T中职称为教授的教师号和姓名。
3. 保证数据的逻辑独立性。对于视图的操作，比如查询，只依赖于视图的定义。当构成视图的基本表要修改时，只需修改视图定义中的子查询部分。而基于视图的查询不用改变。这就是第一章介绍过的外模式与模式之间的独立性，即数据的逻辑独立性。
4. 适当地利用视图可以更清晰地表达查询

例如经常需要执行这样的查询“对每个同学找出他获得最高成绩的课程号”。
可以先定义一个视图，求出每个同学获得的最高成绩：
create view VMGRADE
as
select sno,max(grade) Mgrade
from sc
group by sno
然后用如下语句完成查询：
select sc.sno,cno
from sc,VMGRADE
where sc.sno=VMGRADE.sno
and sc.grade=VMGRADE.Mgrade