数据库要期末考试了,来一波SQL语句总结
基本表的定义、删除与修改
1.定义基本表
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ] );
- <表名>:所要定义的基本表的名字
- <列名>:组成该表的各个属性(列)
- <列级完整性约束条件>:涉及相应属性列的完整性约束条件
- <表级完整性约束条件>:涉及一个或多个属性列的完整性约束条件
- 如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。
1.1 约束
约束 | 说明 |
---|---|
NOT NULL | 约束强制列不接受 NULL 值。 |
UNIQUE | 约束不出现重复值,但允许是NULL值。 |
PRIMARY KEY | 约束条件从功能上看相当于非空(NOT NULL)且唯一(UNIQUE)的组合,主键可以用来在表中唯一的确定一行数据,一个表上只允许建立一个主键 |
FOREIGN KEY | 外键约束条件定义在两个表的字段或一个表的两个字段上,用于保证相关两个字段的关系 |
CHECK | 检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件 |
DEFAULT | 约束用于向列中插入默认值。 |
1.2 数据类型
数据类型 | 含义 |
---|---|
CHAR(n),CHARACTER(n) | 长度为n的定长字符串 |
VARCHAR(n), CHARACTERVARYING(n) | 最大长度为n的变长字符串 |
CLOB | 字符串大对象 |
BLOB | 二进制大对象 |
INT,INTEGER | 长整数(4字节) |
SMALLINT | 短整数(2字节) |
BIGINT | 大整数(8字节) |
NUMERIC(p,d) | 定点数,由p位数字(不包括符号、小数点)组成,小数后面有d位数字 |
DECIMAL(p, d), DEC(p, d) | 同NUMERIC |
REAL | 取决于机器精度的单精度浮点数 |
DOUBLE PRECISION | 取决于机器精度的双精度浮点数 |
FLOAT(n) | 可选精度的浮点数,精度至少为n位数字 |
BOOLEAN | 逻辑布尔量 |
DATE | 日期,包含年、月、日,格式为YYYY-MM-DD |
TIME | 时间,包含一日的时、分、秒,格式为HH:MM:SS |
TIMESTAMP | 时间戳类型 |
INTERVAL | 时间间隔类型 |
2. 修改基本表
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
- <表名>是要修改的基本表
- ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN子句用于删除表中的列
如果指定了CASCADE短语,则自动删除引用了该列的其他对象
如果指定了RESTRICT短语,则如果该列被其他对象引用,关系数据库管理系统将拒绝删除该列
- DROP CONSTRAINT子句用于删除指定的完整性约束条件
- ALTER COLUMN子句用于修改原有的列定义,包括修改列名和数据类型
3. 删除基本表
DROP TABLE <表名>[RESTRICT| CASCADE];
RESTRICT:删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象,则此表不能被删除
CASCADE:删除该表没有限制。
在删除基本表的同时,相关的依赖对象一起删除
数据查询
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
- SELECT子句:指定要显示的属性列
- FROM子句:指定查询对象(基本表或视图)
- WHERE子句:指定查询条件
- GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
- HAVING短语:只有满足指定条件的组才予以输出
- ORDER BY子句:对查询结果表按指定列值的升序或降序排序
1. 选择表中若干列
1.1 查询指定列
查询全体学生的学号与姓名。
SELECT Sno,Sname FROM Student;
1.2 查询全部列
选出所有属性列:
在SELECT关键字后面列出所有列名
将<目标列表达式>指定为 *
查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
1.3 查询经过计算的值
- SELECT子句的<目标列表达式>不仅可以为表中的属性列,也可以是表达式
查全体学生的姓名及其出生年份
SELECT Sname,2017-Sage /*假设当时为2017年,2017减去年龄即为出生年份*/
FROM Student;
2. 选择表中的若干元组
2.1 消除取值重复的行
- 指定DISTINCT关键词,去掉表中重复的行
- 如果没有指定DISTINCT关键词,则缺省为ALL
2.2 查询条件
查 询 条 件 | 谓 词 |
---|---|
比 较 | =, >, <, >=, <=, !=, <>, !>, !<; NOT+上述比较运算符 |
确定范围 | BETWEEN AND, NOT BETWEEN AND |
确定集合 | IN, NOT IN |
字符匹配 | LIKE, NOT LIKE |
空 值 | IS NULL, IS NOT NULL |
多重条件(逻辑运算) | AND, OR, NOT |
比较:
/*查询考试成绩有不及格的学生的学号*/
SELECT DISTINCT Sn
FROM SC
WHERE Grade<60;
/*查询计算机科学系全体学生的名单*/
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
确定范围:
/*查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄*/
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
/*查询年龄不在20~23岁之间的学生姓名、系别和年龄*/
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
确定集合:
/*查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别*/
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA’,'CS' );
字符匹配:
谓词: [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
<匹配串>可以是一个完整的字符串,也可以含有通配符%和 _
- % (百分号) 代表任意长度(长度可以为0)的字符串
例如a%b表示以a开头,以b结尾的任意长度的字符串 - _ (下横线) 代表任意单个字符。
例如a_b表示以a开头,以b结尾的长度为3的任意字符串
/*查询姓"欧阳"且全名为三个汉字的学生的姓名*/
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';
/*查询所有不姓刘学生的姓名、学号和性别*/
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
空值:
/*查询缺少成绩的学生的学号和相应的课程号*/
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL
/*查所有有成绩的学生学号和课程号*/
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
多重条件查询:
- 逻辑运算符:AND和 OR来连接多个查询条件
AND的优先级高于OR
可以用括号改变优先级
3. ORDER BY子句
ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
- 对于空值,排序时显示的次序由具体系统实现来决定
/*查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列*/
SELECT Sno, Grade
FROM SC
WHERE Cno= ' 3 '
ORDER BY Grade DESC;
/*查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列*/
SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;
4. 聚集函数
统计元组个数
COUNT(*)
统计一列中值的个数
COUNT([DISTINCT|ALL] <列名>)
计算一列值的总和(此列必须为数值型)
SUM([DISTINCT|ALL] <列名>)
计算一列值的平均值(此列必须为数值型)
AVG([DISTINCT|ALL] <列名>)
求一列中的最大值和最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)
/*查询学生总人数*/
SELECT COUNT(*)
FROM Student;
/*查询选修了课程的学生人数*/
SELECT COUNT(DISTINCT Sno)
FROM SC;
/*计算1号课程的学生平均成绩*/
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 ';
/*查询选修1号课程的学生最高分数*/
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;
5.GROUP BY子句
GROUP BY子句分组:
细化聚集函数的作用对象
- 如果未对查询结果分组,聚集函数将作用于整个查询结果
- 对查询结果分组后,聚集函数将分别作用于每个组
- 按指定的一列或多列值分组,值相等的为一组
/*查询选修了3门以上课程的学生学号*/
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
/*查询平均成绩大于等于90分的学生学号和平均成绩
*/
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
6.连接查询
- 连接查询:同时涉及两个以上的表的查询
连接条件或连接谓词:用来连接两个表的条件
一般格式:
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
连接字段:连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的,但名字不必相同
等值连接:
连接运算符为=
/*查询每个学生及其选修课程的情况*/
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
自身连接:
- 自身连接:一个表与其自己进行连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性,因此必须使用别名前缀
查询每一门课的间接先修课(即先修课的先修课)
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 Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
7.嵌套查询
- 一个SELECT-FROM-WHERE语句称为一个查询块
- 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/
FROM Student
WHERE Sno IN
( SELECT Sno /*内层查询/子查询*/
FROM SC
WHERE Cno= ' 2 ');
- 上层的查询块称为外层查询或父查询
- 下层查询块称为内层查询或子查询
SQL语言允许多层嵌套查询
即一个子查询中还可以嵌套其他子查询
子查询的限制
不能使用ORDER BY子句
/*查询与“刘晨”在同一个系学习的学生*/
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ' 刘晨 ');
/*查询选修了课程名为“信息系统”的学生学号和姓名*/
SELECT Sno,Sname /*最后在Student关系中取出Sno和Sname*/
FROM Student
WHERE Sno IN
(SELECT Sno /*然后在SC关系中找出选修了3号课程的学生学号*/
FROM SC
WHERE Cno IN
(SELECT Cno /*首先在Course关系中找出“信息系统”的课程号*/
FROM Course
WHERE Cname= '信息系统'
)
);
8. 集合查询
- 集合操作的种类
- 并操作UNION
- 交操作INTERSECT
- 差操作EXCEPT
- 参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
UNION:将多个查询结果合并起来时,系统自动去掉重复元组
UNION ALL:将多个查询结果合并起来时,保留重复元组
/*查询计算机科学系的学生及年龄不大于19岁的学生*/
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
插入数据
两种插入数据方式
- 插入元组
- 插入子查询结果
可以一次插入多个元组
语句格式:
INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );
* INTO子句*:
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列:表示要插入的是一条完整的元组,且属性列属性与表定义中的顺序一致
- 指定部分属性列:插入的元组在其余属性列上取空值
VALUES子句
- 提供的值必须与INTO子句匹配
值的个数
值的类型
/*将一个新学生元组(学号:201215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中*/
INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈冬','男','IS',18);
/*对每一个系,求学生的平均年龄,并把结果存入数据库*/
/*第一步:建表*/
CREATE TABLE Dept_age
( Sdept CHAR(15) /*系名*/
Avg_age SMALLINT); /*学生平均年龄*/
/*第二步:插入数据*/
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;
修改数据
语句格式:
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
功能
- 修改指定表中满足WHERE子句条件的元组
- SET子句给出<表达式>的值用于取代相应的属性列
- 如果省略WHERE子句,表示要修改表中的所有元组
/*将学生201215121的年龄改为22岁*/
UPDATE Student
SET Sage=22
WHERE Sno=' 201215121 ';
/*将所有学生的年龄增加1岁*/
UPDATE Student
SET Sage= Sage+1;
/*将计算机科学系全体学生的成绩置零*/
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS' );
删除数据
语句格式:
DELETE
FROM <表名>
[WHERE <条件>];
功能
- 删除指定表中满足WHERE子句条件的元组
WHERE子句
- 指定要删除的元组
- 缺省表示要删除表中的全部元组,表的定义仍在字典中
/*删除学号为201215128的学生记录*/
DELETE
FROM Student
WHERE Sno= 201215128 ';
/*删除所有的学生选课记录*/
DELETE
FROM SC;
/*删除计算机科学系所有学生的选课记录*/
DELETE
FROM SC
WHERE Sno IN
(SELETE Sno
FROM Student
WHERE Sdept= 'CS') ;
视图
视图的特点
- 虚表,是从一个或几个基本表(或视图)导出的表
- 只存放视图的定义,不存放视图对应的数据
- 基表中的数据发生变化,从视图中查询出的数据也随之改变
1. 定义视图
1.1 建立视图
语句格式:
CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION
对视图进行UPDATE,INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)
- 子查询可以是任意的SELECT语句,是否可以含有ORDER BY子句和DISTINCT短语,则决定具体系统的实现。
/*建立信息系学生的视图*/
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
/*建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 */
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
1.2 删除试图
语句格式:
DROP VIEW <视图名>[CASCADE];
- 该语句从数据字典中删除指定的视图定义
- 如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除
- 删除基表时,由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
2.查询视图
- 用户角度:查询视图与查询基本表相同
关系数据库管理系统实现视图查询的方法
视图消解法(View Resolution)
进行有效性检查
转换成等价的对基本表的查询
执行修正后的查询
/*在信息系学生的视图中找出年龄小于20岁的学生*/
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
/*视图消解转换后的查询语句为:*/
SELECT Sno,Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<20;