数据库原理及应用

参考教材：数据库原理及应用（第二版）姜代红 蒋秀莲主编

个人学习笔记，供自己复习使用，语言不严谨之处希望大家多多包涵，如果有错误，恳请大佬们帮忙指出、改正！

数据库基本语句的汇总以及注意事项

SQL的数据定义

数据类型

（1）整数数值：int 

        使用int时不需要输入需要几位（如char()需要输入需要几位)，在插入数据时，不需要加' '，对于int型数据可以使用运算符号对数据进行修改

age int;
insert into X values(20);

set score=score*1.1

（2）小数数值：decimal

        使用decimal时要给出整个数的位数与小数有几位，例如：

create table test
(score decimal(5,3)
)

insert into test values(12.3456)
select * from test 
/*结果是12.346*/

/*如这个decimal(5,3)的含义是整个数有5位，其中小数占了3位，那么整数部分只剩下2位
如果插入数据12.3456，则会显示12.346，会自动四舍五入
如果输入的是123.45，则会报错'出现算术溢出错误'
*/

（3）浮点型float

        使用浮点型时，不用在定义时给出需要几位，而是直接使用就行，相比decimal使用更方便

alter table test add salary float
/*在之前的test表中新加一列salary，数据是float型*/

insert into test values(10,12.3456)
select * from test

/*结果是10.000 12.3456*/

（4）日期datetime

        datetime的格式是2023-4-26 11:12:13.000，具体到每分每秒

（5）日期 date

        date型不包含时间，格式是2023-4-26，常规使用时直接使用date型就行。常见的题目是求年龄，这时候需要使用datediff()函数来计算。

使用格式：datediff(时间间隔单位,startdate,enddate)，其中时间类型有：

时间间隔单位	说明
yy	年
qq	季度
mm	月
wk	周
dd	天
hh	小时
mi	分钟
ss	秒

在使用时，比如要根据出生日期求出年龄，就需要像下面的例子一样，其中birthdate是表age中的列名birthdate，getdate()是获得目前的日期

create table age
(
name char(10),
birthdate date
)

insert into age values('张三','2002-1-1')

select name as 姓名,datediff(yy,birthdate,getdate()) as 年龄
from age
/*
姓名  年龄
张三  21
*/

（6）字符类型 char

        使用char时，需要给出有几个字符，使用格式是 [列名 char(n)]，n是字符的数目。
注意：如果输入的数据长度<n，则会自动补全空格；但是如果数据长度>n，则会截掉超出的部分，所以在使用是，可以让char多几位，或者直接使用varchar

（7）字符类型varchar

        使用varchar时，与char一样要给出有几个字符，使用格式是[列名 varchar(n)]，与char不同的是，如果输入的数据长度>n了，就会自动增加长度，不会导致数据缺失。

定义数据库

（1）创建数据库

CREATE DATABASE 数据库名
[ON 
	[PRIMARY][<数据文件定义1>[,<数据文件定义2>,[...N]]]
	[,<文件组1>[,<文件组2>,[...n]]]
	[LOG ON<日志文件定义1>[,<日志文件定义2>,[...N]]]
]
<数据文件定义>::=
(	[NAME=逻辑文件名,]
	FILENAME='操作系统下的物理路径和文件名'
	[,SIZE=文件初始大小]
	[,MAXSIZE=文件最大大小|UNLIMITED]
	[,FILEGROWTH=增量值])[,...]
)

CREATE DATABASE databasename/*如果只有这一句，那么会自动在默认位置创建主文件和事务日志文件*/
ON /*on 后面跟着的是要将数据库放在哪个目录下*/
	(NAME=databasename_dat,
	FILENAME='/*绝对路径*/',
	SIZE=10/*这里的大小是MB*/
	MAXSIZE=100/*MB*/
	FILEGROWTH=5/*内存不够时每次扩大5MB*/
	)
LOG ON
	(NAME=databasename_log,
	FILENAME='/*绝对路径*/',
	SIZE=5,
	MAXSIZE=20,
	FILEGROWTH=2
	)

/*注意：在写绝对路径时，路径上的文件夹必须真实存在*/

①数据库名在服务器中必须是唯一的，并且符合标识符的规则

②ON关键字表示数据库是根据后面的参数来创建的，用来定义主数据文件和辅助数据文件。ON后跟以逗号分隔的数据文件定义列表和文件组。

③PRIMARY用来指定主文件。一个数据库只能有一个主文件，默认情况下，如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。

④n是一个占位符，表示可为新数据库指定多个文件。

⑤LOG ON子句用来定义存储数据库日志的事务日志文件。

⑥NAME用于指定数据库文件的逻辑文件名

⑦FILENAME用于指定数据库文件在操作系统下的物理路径和文件名。

⑧SIZE用于指定数据库文件的初始大小。

⑨MAXSIZE用于指定数据库文件的最大大小，单位为MB、KB、GB、TB或%，默认为MB。省略此项表示文件大小不自动增长。

⑩FILEGROWTH用于指定数据库文件的增加量，单位为MB、KB或%，默认为KB，省略此项表示文件大小不自动增长。

（2）修改数据库

ALTER DATABASE 数据库名
{
	ADD FILE<数据文件定义1>[,<数据文件定义2>,[...N]]
	|ADD LOG FILE<日志文件定义1>[,<日志文件定义2>,[...n]]
	|REMOVE FILE<逻辑文件名>
	|MODIFY FILE<数据文件定义>
	|MODIFY NAME=<新数据库名>
}
/*ALTER DATABASE指定要修改的数据库名称*/
/*ADD FILE表示向数据库中添加新的数据文件*/
/*ADD LOG FILE表示向数据库中添加日志文件*/
/*REMOVE FILE表示从数据库中删除文件*/
/*MODIFY FILE指定要更改给定的数据文件定义，更改选项包括FILENAME,FILE,MAXSIZE,FILEGROWTH*/
/*MODIFY NAME用于重命名数据库*/

ALTER DATABASE databasename
	ADD FILE
		(NAME=databasename2,
		FILENAME='物理路径'	,
		SIZE=10,
		MAXSIZE=100,
		FILEGROWTH=5)

（3）删除数据库

DROP DATABASE 数据库名

DROP DATABASE databasename

定义基本表

（1）建立基本表

CREATE TABLE <表名>
(    <列名><数据类型>[列级完整性约束条件]
    [,<列名><数据类型>[列级完整性约束条件],[...n]]
    [,<表级完整性约束条件>]
)
/*表名是所要定义的基本表的名字，要求为合法标识符*/
/*列名是所要定义的基本表的每个属性列的名字，所定义的基本表可以由一个或多个属性（列）组成*/
/*定义表的各个列时，需要指明其数据类型及长度；可以采用SQL标准数据类型，也可以选用用户
自己定义的数据类型。*/
/*完整性约束条件。定义表的同时通常还可以定义与该表有关的完整性约束条件，包括列级和表级完整性
约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时，由DBMS自动检查该操作
是否违背这些完整性约束条件*/

（2）完整性约束条件

在SQL SERVER中，对于基本表的约束分为列级约束和表级约束。

列级约束是对某一特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名。列级约束有6种，主键(PRIMARY KEY)约束、外键(FOREIGN KEY)约束、唯一(UNIQUE)约束、检查(CHECK)约束、默认(DEFAULT)约束、非空/空值(NULL/NOT NULL)约束。列级约束是行定义的一部分，只能应用于一列上。

表约束与列定义相互独立，不包含在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表级约束时必须指出要约束的列的名称。表级约束有4种：主键(PRIMARY KEY)约束、外键(FOREIGN KEY)约束、唯一(UNIQUE)约束、检查(CHECK)约束。表级约束是独立于列的定义，可以应用在一个表中的多列上。

【说明】如果完整性约束涉及该表的多个属性列，必须定义在表级上，否则既可以定义在列级，也可以定义在表级。

①NULL/NOT NULL约束

[CONSTRAINT 约束名][NULL|NOT NULL]
Sno char(10) NOT NULL;
其中：
a.CONSTRAINT表示创建约束的关键字，可以省略
b.约束名表示创建约束的名称，可以省略
c.NULL约束允许字段值为空，此时，该属性值表示不知道、不确定或没有数据。
  NOT NULL约束不允许某一字段值为空。
d.若列定义时未定义NULL|NOT NULL，则默认该列为NULL约束，即允许取空值
【注意】空值不等同于零和空格，它不占用任何空间。

②UNIQUE约束

UNIQUE约束是唯一值的约束，用于约束基本表在某一列或多列的组合上的取值必须唯一。该约束要求不能包含重复值，但允许有一列或多列值为NULL。

Sno char(10) UNIQUE;

③DEFAULT约束

DEFAULT约束为默认值约束，在没有给该列输入值时，则会填充默认值进去。

Ct INT DEFAULT 64;

④PRIMARY KEY约束

PRIMARY KEY约束用于定义主关系键，能够保证主关系键的唯一性和非空性，是实体完整性约束。

Sno char(10) PRIMARY KEY;

当一个表的主关系键是由两个或两个以上的列组成时，需要使用表级完整性约束条件定义实体完整性约束。

Sno char(10),
Cno char(10),
primary key(Sno,Cno);

PRIMARY KEY约束和UNIQUE约束有相似之处，但也存在着很大的区别：

a.在一个基本表中只能定义一个PRIMARY KEY约束，因为一个表只能有一个关键字，但可以定义多个UNIQUE约束。

b.对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。

【注意】不能为一个列或一组列既定义PRIMARY KEY约束，又定义UNIQUE约束。

⑤CHECK约束

CHECK约束为检查约束，通过约束条件表达式设置列值应满足的条件。

Gender char(2) check(Gender='男' or Gender='女')

⑥FOREIGN KEY约束

FOREIGN KEY约束指定某一列或一组列作为外部关系键，其中包含外部关系键的表称为外表或从表，而被参照的外部关系键所对应的主关系键或唯一键的表称为主表。根据参照完整性规则，外部关系键的值可以是主表中的某一个主关系键或唯一键值，也可以取空值。

Dno char(10),
FOREIGN KEY(Dno) REFERENCES D(Dno)
/*其中，Dno是外键，被参照关系表是D，被参照列是Dno*/

【注意】本例中定义Dno是S的外键，前提要求Dno是它所在表的主键。

（3）修改基本表

对表的修改包括增加新列、增加新的完整性约束条件、修改原有的列定义或删除已有的完整性约束条件等。SQL语言用ALTER TABLE语句修改表的结构。

ALTER TABLE<表名>
{    [ALTER COLUMN<列名><数据类型>[NULL|NOT NULL]]
       |[ADD<新列名><数据类型>[完整性约束>]]
       |[DROP<完整性约束名>];
}
/*
a.ALTER COLUMN子句用于修改属性的数据类型、长度等属性。
  注意：修改数据类型时，要求表中没有数据；
        将一个原来允许为空的列改为不允许为空时，
        必须满足列中没有存放空值的记录，
        以及在该列上没有创建索引。

b.ADD子句用于添加新列或新的完整性约束条件。
c.DROP子句用于删除已存在的完整性约束条件。
【注意】不论基本表中是否有数据，新增的列一律为空。
*/

①向学生表‘S’中增加“入学时间”(Sdt)列，其数据类型为日期型。

ALTER TABLE S ADD Sdt DATE;

②删除SC表中成绩取值范围的约束。

ALTER TABLE SC DROP CONSTRAINT SC_CHK
/*SC_CHK是给这个CHECK约束起的别名*/

③将选课表SC中的Score属性列的数据类型改为字符型，取两个字符宽度。

ALTER TABLE SC ALTER COLUMN Score CHAR(2)

④为课程表C增加一个约束，设置课程名是唯一键。

ALTER TABLE C ADD CONSTRAINT SN_UNIQUE(Cn)

（4）删除基本表

SQL语言用DROP TABLE语句删除表，基本表一旦被删除，表中的数据和索引也将自动删除，被删除的表结构和记录不可恢复。

DROP TABLE S

定义索引

索引是基本表的目录，按某一字段或一组字段对数据表进行排序，以加快查询速度。

索引文件只有两个字段，一个是要排序的字段，一个是该记录在原数据表中的记录号，其文件大小要比原数据表小很多；其次，索引文件使用二分法查找记录，这种方法比顺序查找快数倍。

（1）索引的分类

①聚集索引和非聚集索引

a.聚集索引（也称聚簇索引）按照索引字段来排列记录，并且按照拍好的顺序将记录存储在表中。

b.非聚集索引按照索引字段排列记录，但排列的结果并不存储在表中，而是另外存储。

由于聚集索引规定数据在表中的物理存储顺序，一个表只能包含一个聚集索引，聚集索引可定义在一个或多个列上（组合索引）。

对于经常更新的列不宜建立聚集索引。

如果在一个表中既建立聚集索引又建立非聚集索引，应该先建立聚集索引，然后建立非聚集索引。

②唯一索引

唯一索引不允许具有索引值相同的行，即每一个索引值只对应唯一的记录。

主键与唯一索引的区别如下：

a.主键一定是唯一性索引，但唯一性索引并不一定是主键

b.一个表中可以有多个唯一性索引，但只能有一个主键

c.主键列不允许空值，而唯一性索引列允许空值

③组合索引

组合索引（又称复合索引），是将两个或两个以上的字段组合起来建立的索引，单独的字段允许有重复值。

（2）建立索引

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED] INDEX<索引名>
    ON <表名>(<列名>[次序][,<列名>[<次序>]][,...N]);
/*
a.表名指定要建索引的基本表的名字，索引可以建立在该表的一列或多列上，
  各列名之间用逗号分隔。
b.每个列名后面可以用次序指定索引值的排列次序，包括ASC（升序）和DESC（降序）
c.UNIQUE表示此索引为唯一索引
d.CLUSTERED表示要建立的索引是聚集索引
*/

例如，给课程表C的课程名(Cn)列上建立一个聚集索引Coursename，且表C中的记录按照Cn值的升序存放。

CREATE CLUSTERED INDEX Coursename
    ON C(Cn ASC);

为表SC在Sno和Cno上建立一个唯一索引SCI

CREATE UNIQUE INDEX SCI
    ON SC(Sno,Cno);

建立索引能够加快表的查询速度，但要注意下列问题：

a.虽然索引大大加快了查询速度，但是却会降低更新表的速度，如对表进行INSERT,UPDATE,DELETE时，SQL不仅要保存数据，还要更新索引文件。

b.建立索引时，索引文件会占用磁盘空间。如果表很大，并且创建了多种组合索引，索引文件就会迅速增大。

（3）删除索引

DROP INDEX <索引名>

SQL的数据查询

数据库的查询功能是通过SELECT语句实现的，语法格式如下：

SELECT[ALL|DISTINCT] *|<选择列表> [INTO 新表名]
FROM <表名>[,<表名>]...
[WHERE <条件表达式1>]
[GROUP BY<字段名1>[HAVING<条件表达式2>]]
[ORDER BY<字段名2>[ASC|DESC]];
/*
a.ALL，表示输出所有符合条件的记录，默认值为ALL
b.DISTINCT，表示输出时删除重复的记录
c.*|<选择列表>，表示要选择的列。（*）表示选择所有的列；
    <选择列表>是由逗号(,)分隔的多个项，这些项可以是列名、
    常量或者系统函数。若<选择列表>中的字段来源于多个表，
    则应在字段名前写明表名
d.INTO，表明查询结果的去向，可以将查询结果存储到一个新表中。
    本子句可以省略，表示只显示查询结果
e.FROM子句说明数据查询的来源，若查询中用到多张表，应将表名
   用逗号隔开，并将每两个表的关联条件写在WHERE子句中。
f.WHERE子句中说明元组的选择条件，只输出满足<条件表达式1>的所有元组。
g.可选项GROUP BY子句将结果按<字段名1>进行分组，
  即该属性值相等的元组为一组，每个组在结果表中产生一条记录。
  如果GROUP BY子句带有HAVING短语，则只输出满足<条件表达式2>的记录
h.可选项ORDER BY子句将结果按<字段名2>的值进行升序或降序排列
*/

单表查询：

单表查询指涉及一个表的查询，包括一下内容：
a.选择表中的若干列
b.选择表中的若干行
c.对查询结果进行排序
d.使用库函数及统计汇总查询
e.对查询结果进行分组计算

使用SELECT语句选择表中的若干列

SELECT Sno,Sn FROM S;

也可以使用 * 来查看一个表中所有字段

SELECT * FROM S

用DISTINCT删除重复的行

例如，查询所有选修了课程的学生的学号，并去除重复值。

SELECT DISTINCT Sno FROM SC;

修改查询结果中的列标题

起别名

SELECT 
Sn AS 姓名,
YEAR(GETDATE())-Age AS 出生日期,
BP AS 籍贯
FROM S;

GETDATE()和YEAR()函数

如果表中只有年龄，则可以取现在的年份减去年龄以得到出生年份。

YEAR(GETDATE())-Age AS 出生日期

如果只有出生年份，也可以用当前年份减去出生年份得到日期

YEAR(GETDATE())-BIRTHDATE AS 年龄

其中，YEAR(date)表示返回指定日期中的年份的整数，DAY(date)返回指定日期的天的日期部分的整数，MONTH(date)返回指定日期中的月份的整数。

选择表中的若干行

可以使用WHERE子句来实现选择满足条件的若干行

运算符	含义
=,>,<,>=,<=,!=,<>	比较运算符，比较两个表达式值的大小
[NOT]BETWEEN...AND...	确定范围
AND,OR,NOT	多重条件。也可以把AND,OR,NOT结合起来，使用圆括号来组成复杂的表达式
IN | NOT IN	确定集合
LIKE | NOT LIKE	字符匹配。LIKE是用于查找与指定字符串相匹配的字符串，可使用通配符“%”与“_”，一个“_”只代表一个字符，一个“%”可代表多个字符。 注意：只允许在LIKE子句中使用通配符
IS [NOT] NULL	字段是否为空值

比较大小

查询性别为“女”的学生的学号与姓名

SELECT Sno,Sn
FROM S
WHERE Gender='女';

查询年龄在20岁以下的学生的情况

SELECT * 
FROM S
WHERE Age<20

确定范围

BETWEEN...AND...和NOT BETWEEN...AND...用来确定查询范围，意指“在...和...之间”或者“不在...和...之间”的数据。

查询学号在100102~100104之间的学生的情况

SELECT *
FROM S
WHERE Sno BETWEEN '100102'AND'100104'

/*等价于*/
SELECT *
FROM S
WHERE Sno>='100102'AND Sno<='100104'

【注意】BETWEEN后面的是范围的下界，AND是范围的上界

多重条件查询

当WHERE子句需要指定一个以上查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连接成复合的逻辑表达式，其优先级由高到低为NOT、AND、OR，但用户可以使用括号改变优先级。

查询学号在100102~100104之间男生的情况

SELECT *
FROM S
WHERE(Sno BETWEEN'100102'AND'100104' AND Gender='男'
/*等价于*/
SELECT * 
FROM S
WHERE Sno>='100102'AND Sno<='100104' AND Gender='男'

确定集合

IN | NOT IN运算符用于查找列值是否属于指定集合的元组。当列值与IN中的某个常量值相等时，结果为TRUE，表明此记录符合查询条件；当列值与NOT IN中的某个常量值相等时，结果为FALSE，表明此记录不符合查询条件。

查询选修了课程号为150101或150102的学生的学号、课程号和成绩

SELECT Sno,Cno,Score
FROM SC
WHERE Cno IN('150101','150102');
/*等价于*/
SELECT Sno,Cno,Score
FROM SC
WHERE Cno='150101' OR Cno='150102'

查询籍贯不是“湖南”、“湖北”和“江苏”的学生的学号、姓名、籍贯。

SELECT Sno,Sn,Bp
FROM S
WHERE BP NOT IN('湖南','湖北','江苏')
/*等价于*/
SELECT Sno,Sn,BP
FROM S
WHERE BP<>'湖南'AND BP<>'湖北' AND BP<>'江苏'

字符串匹配

当不知道精确的字符串值时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询，也称模糊查询

通配符	功能	实例
%	代表0个或多个字符	张%，表示姓张的人
_	代表1个字符	张_，表示姓张，名字只有2个字的人

查询所有姓“张”的教师的教师号和姓名

SELECT Tno,Tn
FROM T
WHERE Tn LIKE'张%'

涉及空值的查询

当需要判定一个表达式的值是否为空值时，可使用IS NULL关键字

查询没有考试成绩的学生的学号和相应的课程号

SELECT Sno,Cno
FROM SC
WHERE Score IS NULL

【注意】在使用IS NULL时，应该在空值条件后直接接IS NULL，而不能使用=
例如：可以使用Score IS NULL，但不能用Score=NULL。

对查询结果进行排序

在表中进行查询数据时，用户总是希望查询的结果按照一定的顺序输出。在SELECT语句中，使用ORDER BY子句即可实现。ASC表示升序，DESC表示降序。如果要按多个字段排序，则将字段列在ORDER BY之后，字段之间用逗号隔开。排序时，首先按第一个字段的值排序，若第一个字段的值相同，再按照第二个字段的值排序，以此类推。

查询所有选修课程号为150102的课程的学生学号和成绩，并按照成绩降序排列。

SELECT Sno,Score
FROM SC
WHERE Cno='150102'
ORDER BY Score DESC

查询所有教师的姓名、性别、年龄、职称，并按照年龄降序排列，年龄相同的按照职称降序排列。

SELECT Tn,Gender,Age,Prof
FROM T
ORDER BY Age DESC,Prof DESC

使用聚集函数查询

为了方便查询，SQL提供了很多内置函数，可分为聚集函数、数学函数、日期函数等。

函数名	功能
COUNT([DISTINCT | ALL]*)	统计元组数量
COUNT([DISTINCT | ALL]<列名>)	统计一列中值的数量
SUM([DISTINCT | ALL]<列名>)	计算一列值的总和(此列必须是数值型)
AVG([DISTINCT | ALL]<列名>)	计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT | ALL]<列名>)	求一列值中的最大值
MIN([DISTINCT | ALL<列名>)	求一列值中的最小值

求学号为100101的学生的总分和平均分

SELECT SUM(Score) AS TOTAL,AVG(Score) AS AVG
FROM SC
WHERE Sno='100101'

查询来自“江苏”的学生的总数

SELECT COUNT(Sno) AS 江苏学生数
FROM S 
WHERE BP='江苏'
/*或者*/
SELECT COUNT(*) AS 江苏学生数
FROM S
WHERE BP='江苏'
/*
【注意】：COUNT(*)统计的是行数，而COUNT(Sno)
统计的是Sno具有值（非空）的行数。本例中Sno是
主关系键，其值不能为空，所以二者等价。
在其他情况下，二者不一定等价
*/

查询被学生选修的课程总共有多少门

SELECT COUNT(DISTINCT Cno) AS CNUM
FROM SC
/*
【注意】：加入关键字DISTINCT后表示消去重复行，
可计算字段Cno不同值的数目。
COUNT函数不对空值进行计算，但对0进行计算
*/

查询选修150102号课程的最高分、最低分以及其之间相差的分数

SELECT 
    MAX(Score) AS Mxs,
    MIN(Score) AS Mns,
    MAX(Score)-MIN(Score) AS Df
FROM SC
WHERE Cno='150102'

对查询结果进行分组计算

有时，需要先将数据分组，然后再对每组进行计算，该操作可使用GROUP BY子句和HAVING子句实现。
GROUP BY子句用于将表或视图中数据的查询结果按照某一列或多列值分组，值相等的为一组。该子句通常与库函数联合使用，用于针对分组进行统计汇总，使得每个分组都有一个函数值。SELECT子句的列表中只能包含在GROUP BY中指出的列或在库函数中指定的列。
HAVING子句表示只选择满足条件的分组必须与GROUP BY一起使用。WHERE子句是从基本表中选择满足条件的记录，而不是指定满足条件的分组，这是二者的根本区别。

统计教师人数在2人以上的职称名称

SELECT COUNT(*) AS 人数,Prof
FROM T
GROUP BY Prof
HAVING COUNT(*)>1；
/*
该语句对查询结果按Prof的值分组，所有具有相同Prof的值的元组为一组，
然后对每一组使用聚集函数COUNT计算该组人数
*/

查询选修课程在3门以上，并且各门课程均及格的学生学号和平均成绩，查询结果按照平均成绩降序输出

SELECT Sno,AVG(Score) AS 平均成绩
FROM SC
WHERE Score>=60
GROUP BY Sno
HAVING COUNT(*)>=3
OEDER BY 2 DESC;
/*
本例中，先用GROUP BY子句按Sno进行分组，
再用聚集函数COUNT对各组进行统计
使用HAVING子句筛选出每一组数目超过3的
ORDER BY 2 DESC表示按照第二列(即平均成绩)排序
*/

连接查询

当一个查询请求涉及数据库的多个表时，必须用一定的连接条件将这些表连接起来，才能提供用户需要的信息。一般地，若一个查询同时涉及两个或两个以上的数据库表，则称为连接查询。连接查询是数据库中最主要的查询，表的连接有两种方式：在非ANSI标准的实现中，连接操作是按WHERE子句指定连接方式；而在ANSI-92中，连接操作是利用JOIN关键字进行连接。

WHERE连接查询方式

（1）查询的格式
WHERE连接查询的一般格式如下：

SELECT[ALL | DISTINCT] *|<选择列表> [INTO 新表名]
FROM <表名1><表名2>
WHERE [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
/*
其中，比较运算符主要有
=、>、<、>=、<=、!=
当比较运算符为等号时，称为等值连接，
当为其他运算符时，称为非等值连接
【注意】：连接条件中的各连接字段类型必须是可比的
         (如实型和整型)或是相同的。
*/

（2）连接操作的执行过程
①在表1中找到第一个元组
②在表2中从头开始顺序扫描，查找满足连接条件的元组，每找到一个元组，就将表1中的第一个元组与该元组拼接起来，形成结果表的一个元组。
③表2全部扫描完后，再选取表1的第二个元组，然后从头开始扫描表2。
④重复上述操作，直到表1中的全部元组都处理完毕为止。

查询每个学生及其选修课程的情况
学生信息存放在学生表S中，而学生选课的情况存放在选课表SC中，所有本查询涉及S表和SC表，二者之间的联系是通过其共有属性Sno来实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的元组连接起来。

SELECT *
FROM S,SC
WHERE S.Sno=SC.Sno
/*或者使用JOIN连接进行查询*/
SELECT *
FROM S INNER JOIN SC
ON S.Sno=SC.Sno
/*
【注意】：连接运算可以使用关系代数中的连接运算表示，
本例使用了S与SC的按照学号进行的等值连接
*/

JOIN连接查询方式

此处仅涉及内连接

INNER JOIN(内连接)，是最常见的连接，也被称为普通连接，只显示符合ON条件的记录，此为默认连接。

某读者遗失了他的借书证，他提供了手机号“15624889999”，希望查询他是否有未归还的图书，请将他所需要的信息列出：姓名、未还图书名、归还时间

select 
    R1.Rname as 姓名,
    B2.Bname as 未归还图书,
    B1.Due_date as 归还时间 
from Readers as R1
join Borrow as B1 on B1.RID=R1.RID
join Books as B2 on B2.BID=B1.BID
where R1.RID in
(select R1.RID
where R1.Tel='15624889999')

自身连接查询

自身连接是指相互连接的表物理上为同一张表，通常情况下，为了对连接过程进行区别，要为这张表取两个别名以方便操作。

假设课程表C，查询每一门课程的间接先修课（即先修课的先修课）

/*使用WHERE连接查询*/
SELECT first.Cno,second.Cpno
FROM C AS first,C AS second
WHERE first.Cpno=second.Cno
/*使用JOIN连接查询*/
SELECT first.Cno,second.Cpno
FROM C AS first INNER JOIN C AS second
ON first.Cpno=second.Cno

嵌套查询

SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将若干个查询块嵌套在一起，可形成复杂的嵌套查询。也就是说，嵌套查询时指将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。
内嵌的SELECT语句称为子查询、内查询或下层查询，与此对照，外层查询称为父查询或上层查询。嵌套查询的求解方法是由里向外处理，即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查询条件。子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果进行排序。

嵌套查询通常分为不相关子查询和相关子查询两类。

不相关子查询

查询选修150102号课程的学生姓名。

SELECT Sn
FROM S
WHERE Sno IN
    (SELECT Sno
     FROM SC
     WHERE Cno='150102')

相关子查询

子查询的查询条件依赖于父查询。子查询不能独立运行，必须依靠父查询数据，并且外部查询执行一次，子查询就执行一次。

查询每门课程中，成绩低于该课程平均分的学生学号、课程号、成绩。

SELECT Sno,Cno,Score
FROM SC AS SC1
WHERE Score<(SELECT AVG(Score)
            FROM SC AS SC2
            WHERE SC1.Cno=SC2.Cno)

带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常使用的谓词。带有IN谓词的子查询是指父查询和子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。

查询与“王红”职称相同的教师。

/*本查询可分为两步*/
/*第一步，确定“王红”的职称*/
SELECT Prof
FROM T
WHERE Tn='王红';
/*第二步，确定和“王红”职称相同的教师*/
SELECT Tn
FROM T
WHERE Prof IN
    (
    SELECT Prof
    FROM T
    WHERE Tn='王红'
    )

查询选修了课程名为“数据结构”学生的学号和姓名

本查询涉及学号、姓名和课程名3个属性。有关学号和姓名的信息存放在学生表S中，有关课程名的信息存放在课程表C中，但表S和表C没有直接联系，必须通过表SC建立二者之间的联系。

/*在表C中找出课程名为“数据结构”的课程号Cno*/
SELECT Cno
FROM C
WHERE Cn='数据结构'
/*在表SC中找出Cno等于第一步给出的Cno集合中某个元素的Sno*/
SELECT Sno
FROM SC
WHERE Cno IN
    (
    SELECT Cno
    FROM C
    WHERE Cn='数据结构'
    )
/*在表S中选出Sno等于第二步中求出的Sno
集合中某个元素的元组，取出Sno和Sn送入结果表列*/
SELECT Sno,Sn
FROM S
WHERE Sno IN
    (
    SELECT Sno
    FROM SC
    WHERE Cno IN
        (
        SELECT Cno
        FROM C
        WHERE Cn='数据结构'
        )
    )

带有比较运算符的子查询

当用户能确切的知道内层查询返回的是单值时，可以使用>,<,=,>=,<=,!=或<>等比较运算符。带有比较运算符的子查询是指父查询和子查询之间用比较运算符进行连接。需要注意的是，子查询一定要跟在比较运算符之后。

查询与“王红”职称相同的教师

SELECT T.Tn
FROM T
WHERE Prof=
    (SELECT Prof
    FROM T
    WHERE Tn='王红'
    )

带有ANY或ALL谓词的子查询

当子查询的返回值为一个集合是，除了可以使用IN连接词以外，还可以使用ANY或ALL谓词。注意，使用ANY或ALL谓词时必须同时使用比较运算符。

表达式	含义
>ANY	大于子查询中的某个值
<ANY	小于子查询中的某个值
>=ANY	大于等于子查询中的某个值
<=ANY	小于等于图子查询中的某个值
=ANY	等于子查询中的某个值
!=ANY OR <>ANY	不等于子查询中的某个值
>ALL	大于子查询中的所有值
<ALL	小于子查询中的所有值
>=ALL	大于等于子查询中的所有值
<=ALL	小于等于子查询中的所有值
=ALL	等于子查询中的所有值
!=ALL OR <>ALL	不等于子查询中的所有值

ANY或ALL谓词有时可以使用集函数来实现，比直接使用ANY或ALL谓词的查询效率高，因为通常使用集函数能够减少比较次数。

ANY或ALL谓词与集函数对照表

	=	<>	<	<=	>	>=
ANY	IN		<MAX	<=MAX	>MAX	>=MAX
ALL		NOT IN	<MIN	<=MIN	>MIN	>=MIN

查询其他部门中比01部门中任一一个学生年龄小的学生的姓名和年龄

SELECT Sn,Age
FROM S
WHERE Dno<>'01' AND Age < ANY
(SELECT Age
FROM S
WHERE Dno='01')

查询其他部门中比01部门所有学生年龄都小的学生的姓名和年龄

SELECT Sn,Age
FROM S
WHERE Dno <>'01' AND Age < ALL
(SELECT Age
FROM S
WHERE Dno='01')

带有EXISTS谓词的子查询

EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何实际数据，只产生逻辑真值TRUE或逻辑假值FALSE。使用存在量词EXISTS后，若内层查询结果为非空，则外层的WHERE子句返回真值，否则返回假值。
由EXISTS引出的子查询所要选择的字段通常都用“*”表示，因为带有EXISTS的子查询只返回真值或假值，给出列名也无实际意义。带有EXISTS谓词的子查询是相关子查询，根据相关子查询的定义可知，带有EXISTS的嵌套查询必须反复求值。

使用相关子查询的一般处理过程如下：
（1）首先取外层查询中表的第一个元组
（2）根据本元组与内层查询相关的属性值来处理内层查询，若内层查询结果集为空，WHERE子句的值为假；否则，WHERE子句的值为真。
（3）若WHERE子句为真，则取外层表的此元组指定属性（或表达式）放入结果表中。
（4）如果外层表没有结束，则取外层表的下一个元组，返回第（2）步；否则，结束相关子查询。

【注意】
（1）与EXISTS相对应的是NOT EXISTS谓词，使用NOT EXISTS谓词后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。
（2）有些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但带有IN、比较运算符、ANY和ALL谓词的子查询都能用带有EXISTS谓词的子查询等价替换。

查询所有选修了150102号课程的学生的姓名。

SELECT Sn
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=S.Sno AND Cno='150102'
)

查询没有选修150102号课程的学生的姓名和部门。

SELECT Sn,Dept
FROM S,D
WHERE S.Dno=D.Dno AND NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=S.Sno AND Cno='150102'
)

查询所有选修了全部课程的学生的姓名。

SELECT sn
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
    (SELECT *
    FROM SC
    WHERE Sno=S.Sno AND Cno=C.Cno)
)

集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作，主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

UNION操作

UNION操作是将多个查询结果合并在一起的操作，使用该操作，形成一个完整的查询结果时，系统会自动删除重复的元组，如果不消除重复的行，则可以使用UNION ALL运算符。需要注意的是，参加UNION操作的各数据项数目必须相同，对应项的数据类型也必须相同。使用UNION运算符对集合进行并运算，得到的结果集的记录自动按其第一列的值升序排列。
SQL语句中可以出现任意数目的UNION运算符，在默认情况下，从左到右对包含UNION运算符的语句求值，但可使用圆括号指定求值顺序。如果所有的表都使用UNION运算符组合，或者都使用UNION ALL运算符组合，无论采用什么样的组合顺序，结果都是一样的；但是，如果UNION运算符与UNION ALL运算符混合使用，则不同的组合顺序，保留的重复行可能不同，得到的结果可能不一样。

查询年龄在30岁以下和部门号为01的所有教师的情况。

SELECT *
FROM T
WHERE Age<30
UNION
SELECT *
FROM T
WHERE Dno='01'

集合差/集合交运算

使用EXCEPT运算符可以实现集合差操作，即从左查询中返回右查询没有找到的所有非重复值。而使用INERTSECT运算符可实现集合交操作，即返回INTERSECT操作符左右两边的两个查询都返回的所有非重复值。
使用 EXCEPT 和INTERSECT 运算符进行集合运算时，需注意以下几点:
(1)如果 EXCEPT 或INTERSECT 操作符左边和右边的查询返回的可比较列的数据类型是具有不同排序规则的字符数据类型，则根据排序规则优先级的规则执行所需的比较。
(2)通过比较行来确定非重复值时，视两个 NULL 值相等。
(3)EXCEPT或INTERSECT 返回的结果集的列名与操作数左侧的查询返回的列名要相同。
(4)EXCEPT或INTERSECT 返回的结果集中的任何列的为空性(是否可以为空)与操作数左侧的查询返回的对应列的为空性相同。

查询所有没有选修数据结构的学生。

SELECT * FROM S
EXCEPT
SELECT * FROM S
WHERE Sno IN(SELECT Sno
            FROM SC
            WHERE Cno IN(SELECT Cno
                        FROM C
                        WHERE Cn='数据结构'))

查询同时选修了数据结构和操作系统的学生学号。

SELECT Sno
FROM SC,C
WHERE Sc.Cno=C.Cno AND Cn='数据结构'
INTERSECT
SELECT Sno
FROM SC,C
WHERE Sc.Cno=C.Cno AND Cn='操作系统'

SQL的数据操作

所谓数据操作是指对已经存在的数据库进行记录的插入、删除和修改操作。SQL提供了3条语句完成相应的数据操作：INSERT,UPDATE,DELETE。

插入数据

插入数据是把新的记录插入到一个存在的表中，使用INSERT语句，可以一次插入一个或多个元组。

插入单个元组

/*插入单个元组的格式如下*/
INSERT INTO <目标表> [(<属性列1>[,<属性列2>...])]
    VALUES (<常量1>[,<常量2>]...)

(1)<目标表>是指要插入新纪录的表，要求<目标表>必须存在。
(2)注意<目标表>的主键约束，如果<目标表>有主键而且不为空，则<属性列1>、<属性列2>··-中必须包括主键。
(3)<属性列>是可选项，指定待添加数据的列。
(4)VALUES 子句指定待添加数据的具体值。列名的排列顺序不一定要和表定义时的顺序一致，但当指定列名时，VALUES 子句值的排列顺序必须和列名表中的列名顺序一致个数相等，且数据类型一一对应。
(5)INTO子句中<目标表>的属性列可部分或全部省略，部分省略时，则新记录在这些列上将取空值;全部省略时，则新插入的记录必须在每个属性列上均给出值，且和<目标表>中的列名顺序一致、数据类型一一对应。
(6)在表定义时说明了 NOT NULL的属性列不能取空值，否则会出错

在学生表S中插入学生记录

INSERT INTO S
VALUES('100107','男','23','03')

插入多个元组

插入多个元组用于表间的复制，即抽取一个表中的数行数据插入另一表中，这可以通过子查询来实现。子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。

将每一个部门学生的平均年龄存入新表SAVGA中（本表包括两个属性列：部门号Dno和平均年龄Avgage)

CREATE TABLE SAVGA
(Dno CHAR(10),
Avgage INT)
INSERT INTO SAVGA
    SELECT Dno,AVG(Age)
    FROM S
    GROUP BY Dno;

修改数据

修改数据主要是针对数据库表中一条或多条记录某个或某些列的值进行更改，其语法格式如下：

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

其功能是修改指定表中满足WHERE子句条件的元组。
其中，SET子句用来指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

修改单个或部分元组的值

将学生100105的年龄改为21岁

UPDATE S
SET Age=21
WHERE Sno='100105'

修改全部元组的值

将所有学生的分数提高10%

UPDATE SC
SET Score=Score*1.1

利用子查询修改部分元组的值

修改部分元组的值可以使用带子查询的修改语句，子查询也可以嵌套在UPDATE语句中，用以构造执行修改操作的条件。

将信电学院全体学生的成绩置0

UPDATE SC
SET Score=0
WHERE Sno IN
(SELECT Sno
FROM S
WHERE Dno=(SELECT Dno
            FROM D
            WHERE Dept='信电')
)

删除数据

使用DELETE语句可以删除表中的一行或多行记录，删除数据的一般格式如下：

DELETE FROM<表名>
[WHERE<表名>]

其中，DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中的全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是表的结构。
【注意】：DELETE与UPDATE操作一次只能操作一张表，因此会影响参照完整性

删除单个或多个元组

删除学号为100107和100108的学生的记录

DELETE FROM S
WHERE Sno='100107' OR Sno='100108'

删除全部元组的值

删除所有学生的选课记录

DELETE FROM SC

带子查询的删除语句

删除数理学院所有学生的选课记录

DELETE FROM SC
WHERE Sno IN
(SELECT Sno
FROM S
WHERE Dno=(SELECT Dno
            FROM D
            WHERE Dept='数理')
)

视图

视图可以看成是虚拟表或存储查询，是从一个或几个基本表（或视图）导出的表，但与基本表不同，视图是一个虚表。也就是说，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在基本表中。基本表中的数据发生变化的时候，从视图中查询的数据也随之发生变化。通过视图，SQL用户不需要知道底层TABLE的表结构及其之间的关系，视图提供了一个统一访问数据的接口。

视图的定义

定义视图使用CREATE VIEW语句，其语法格式如下：

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

其中
（1）子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。
（2）WITH CHECK OPTION防止用户通过视图对数据进行增加、删除、修改时，对不属于视图范围内的基本表数据进行操作。
（3）如果CREATE VIEW语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列的诸字段构成。

行列子集视图

若视图是从一个表经选择、投影而导出的，并在视图中包含了表的主关系键或某个候选关系键，则这类视图称为行列子集视图。

建立信电学院教师的视图，并要求进行修改和插入操作时仍保持该视图只有信电学院教师。

CREATE VIEW ITteacher
AS
SELECT Tno,Tn,Prof,Dno
FROM T
WHERE Dno=(SELECT Dno
            FROM D
            WHERE Dept='信电')
WITH CHECK OPTION

多表视图

视图不仅可以建立在单个基本表上，也可以建立在多个基本表（视图）上，这样的视图称为多表视图。

创建选修了“操作系统”课程的所有学生的视图

CREATE VIEW Tstu(Sno,Sn,Sex,Dno)
AS 
SELECT S.Sno,Sn,Sex,S.Dno
FROM S,SC,C
WHERE Cn='操作系统' AND S.Sno=SC.Sno AND SC.Cno=C.Cno

带表达式的视图

由于视图中的数据并不实际存储，定义视图时可以根据应用的需要，设置一些派生属性列。因为这些派生属性列在基本表中并不实际存在，所有有时也称它们为虚拟列。带虚拟列的视图称为带表达式的视图。

定义一个反映学生出生年份的视图

CREATE VIEW SBir(Sno,Sn,Bir)
AS 
SELECT Sno,Sn,YEAR(GETDATE())-Age
FROM S

分组视图

使用带有集函数和GROUP BY子句的查询来定义的视图称为分组视图。

定义一个反映学生的学号及其平均成绩的视图

CREATE VIEW StuAvg(Sno,Savg)
AS
SELECT Sno,AVG(Score)
FROM SC
GROUP BY Sno

修改视图

创建一个视图后，若发现视图定义不满足实际需要，可以对其定义进行修改

修改视图，使视图只包含学生姓名和学生出生年份。

ALTER VIEW SBir(Sn,Bir)
AS
SELECT Sn,YEAR(GETDATE())-Age
FROM S

删除视图

删除视图使用DROP VIEW语句

视图查询

视图查询即使用视图查询数据，用户对视图的查询与对基表的查询相同，但在系统内部，对视图的查询必须转换为对其所依赖的基表的查询。

查找视图ITteacher中职称为“教授”的教师号和姓名

SELECT Tno,Tn
FROM ITteacher
WHERE Prof='教授'

视图更新

视图更新即使用视图更新数据，是指通过视图来插入 (INSERT)、删除(DELETE)和修改 (UPDATE) 数据。由于视图是虚表，因此对视图的更新，最终要转换为对基本表的更新。为了防止用户对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION 子句。

更新视图有以下 3 条规则:
(1)若视图是基于多个表使用连接操作而导出的，那么对这个视图执行更新操作时每次只能影响其中的一个表。
(2)若视图导出时包含有分组和聚合操作，则不允许对这个视图执行更新操作.
(3)若视图是从一个表经选择、投影而导出的，并在视图中包含了表的主键字或某个候选键，这类视图称为“行列子集视图”。对这类视图可执行更新操作。

视图的UPDATE操作

将信电学院教师的视图中工号为05的教师的姓名改为“张宾”

UPDATE ITteacher
SET Tn='张宾'
WHERE Tno='05'

视图的INSERT操作

向视图插入数据

INSERT 
INTO ITteacher
VALUES()

视图的DELETE操作

删除视图中的一条记录

DELETE
FROM ITteacher
WHERE Tno='07'

视图的作用

（1）视图能使用户以多种角度看待同一模型
（2）视图能够简化用户操作
（3）视图为重构数据库提供了一定程度的逻辑独立性
（4）视图能够为机密数据提供安全保护