目录
操作表
创建表
- 基本表的定义、删除与修改
格式:create table 语句
CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>] ] …
[,<表级完整性约束条件> ] );
<表名>:所要定义的基本表的名字
<列名>:组成该表的各个属性(列)
<列级完整性约束条件>:涉及相应属性列的完整性约束条件
<表级完整性约束条件>:涉及一个或多个属性列的完整性约束条件
[例] 建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空,值是唯一的,并且姓名取值也唯一。
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1) ,
Sage INT,
Sdept CHAR(15));
- 常用完整性约束
主码约束: PRIMARY KEY
唯一性约束:UNIQUE
非空值约束:NOT NULL
参照完整性约束
- PRIMARY KEY与 UNIQUE的区别?
[例] 建立一个“学生选课”表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno, Cno)为主码。
CREATE TABLE SC(
Sno CHAR(5) ,
Cno CHAR(3) ,
Grade int,
Primary key (Sno, Cno));
删除基本表
DROP TABLE <表名> [restrict|cascade];
(restrict 表的删除是有限制条件的,如果存在这些依赖该表的对象,则此表不能被删除。Casecade 改表的删除没有限制条件,删除表的同时,相关依赖都被删除)
[例]
drop table Student cascade;
修改基本表
ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ];
<表名>:要修改的基本表
ADD子句:增加新列和新的完整性约束条件
DROP子句:删除指定的完整性约束条件
MODIFY子句:用于修改列名和数据类型
[例] 向Student表增加“入学时间”列,其数据类型为日期型。
ALTER TABLE Student ADD Scome DATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
查找表
语句格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
FROM 子句:指定查询对象(基本表或视图)
WHERE 子句:指定查询条件
GROUP BY 子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING 短语:筛选出只有满足指定条件的组
ORDER BY 子句:对查询结果表按指定列值的升序或降序排序
操作数据
查询数据
SELECT [ALL|DISTINCT] <目标列表达式>[别名][,<目标列表达式> [别名]] …
FROM <表名/视图名> [别名] [, <表名/视图名> [别名]] … | (<select 语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
1.目标列表达式的可选格式
1. *
2. <表名.>*
3. COUNT([DISTINCT|ALL] *)
4. [<表名.>]<属性列名表达式>[,[<表名.>]<属性列名表达式>]...Tips :<属性列名表达式> 可以是由属性列、作用于属性列的聚集函数、属性和常量的任意算术运算(+、-、*、/)组成的运算公式。
- 表达式
SELECT Sname,2016-Sage FROM Student;
- 字符串常量、函数
SELECT Sname,'Year of Birth:',2016-Sage,LOWER(Sdept) FROM Student; // 小写字母表示系名
+--------+-----------+
| Sname | 2016-Sage |
+--------+-----------+
| Jason | 1995 |
| Taylor | 1995 |
| dsf | 1995 |
+--------+-----------+
+--------+----------------+-----------+--------------+
| Sname | Year of Birth: | 2016-Sage | LOWER(Sdept) |
+--------+----------------+-----------+--------------+
| Jason | Year of Birth: | 1995 | ele |
| Taylor | Year of Birth: | 1995 | ele |
| dsf | Year of Birth: | 1995 | ele |
+--------+----------------+-----------+--------------+
- 查询时可以指定别名,这对于算术表达式、常量、函数名的目标列表达尤为有用
SELECT Sname NAME,'Year of Birth:' BIRTH,2016-Sage BIRTHDAY,LOWER(Sdept) DEPARTMANT FROM Student;
+--------+----------------+----------+------------+
| NAME | BIRTH | BIRTHDAY | DEPARTMANT | //指定别名,界面友好性。
+--------+----------------+----------+------------+
| Jason | Year of Birth: | 1995 | ele |
| Taylor | Year of Birth: | 1995 | ele |
| dsf | Year of Birth: | 1995 | ele |
+--------+----------------+----------+------------+
2.聚集函数的一般格式
COUNT(*) 统计元组的个数
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型)
AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值DISTINC 短语,指明在计算时要取消指定列中的重复值。默认为ALL,表示不取消重复值。 当聚集函数遇到空值时,除COUNT()外,都跳过空值而只处理非空值。 COUNT()对元组进行计数时,包含空值所在的元组。
注意: WHERE 子句中是不能用聚集函数作为条件表达式的。 聚集函数只能用于 SELECT子句 和 GROUP BY 中的 HAVING 子句。
SELECT COUNT(*) FROM Student;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
SELECT COUNT(DISTINCT Sno) FROM SC; // 查询选修了课程的学生人数
+---------------------+
| COUNT(DISTINCT Sno) |
+---------------------+
| 5 |
+---------------------+
学生每选一门课在SC表中都有一条相应的记录. 一个学生选多门课,SC表会记录多次,COUNT函数必须用DISTINCT短语去掉重复的记录
3.WHERE 子句的条件表达式的可选格式
- 比较大小
常见运算符: =(等于),>(大于),<(小于),>=,<=, !=或<>,!>(不大于),!<(不小于)
- 确定范围
[NOT] BETWEEN 下限 AND 上限
[例] 查询年龄在20~23岁之间的学生的姓名、系名和年龄
SELECT Sname,Sdept,Sage FROM WHERE Sage BETWEEN 20 AND 24;
- 确定集合
IN 用来查找属性值属于指定集合的元组
NOT IN 属性不属于任何指定集合的元组
[例] 查找 CS系,ELE系,IS系 中的学生的姓名、性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','ELE','IS');
- 字符匹配
LIKE 用来进行字符串的匹配。 查找指定的属性列值与<匹配字符串>相匹配的元组
语法格式:
[NOT] LIKE <'匹配字符串> [ESCAPE <'换码字符'>]
% 代表任意长度(>=0个)的字符串
_ 下划线代表任意单个字符
[例] a_b 表示以a开头,以b结尾的长度为3的任意字符串
[例] 查找学号为3120开头的学生的信息
SELECT * FROM Student WHERE Sno LIKE '3120%';
+--------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+--------+--------+------+------+-------+
| 312001 | Jason | fe | 21 | ELE |
| 312028 | Taylor | fe | 21 | ELE |
+--------+--------+------+------+-------+
如果用户要查询的字符串本身就含有通配符 % 或 _ ,这时就要用 ESCAPE '<换码字符>' 短语对通配符进行转义了
[例] 以 & 作为转义字符。mysql不支持''
select Cno,Cname,Ccredit from Course where Cname LIKE 'DB&_Design' ESCAPE '&';
+-----+-----------+---------+
| Cno | Cname | Ccredit |
+-----+-----------+---------+
| 004 | DB_Design | 6 |
+-----+-----------+---------+
- 涉及空值的查询
IS NULL // 为空
IS NOT NULL // 不为空
[例]
select Sno,Cno from Sc where Grade is not null;
+--------+-----+
| Sno | Cno |
+--------+-----+
| 20123 | 004 |
| 20132 | 001 |
| 312001 | 001 |
| 312028 | 003 |
| 312029 | 006 |
+--------+-----+
-
多重条件查询
AND 和 OR 用来连接多个查询条件. AND 的优先级高于 OR,用括号可以改变优先级
SELECT Sname FROM Student WHERE Sdept='ELE' AND Sage<22;
+--------+
| Sname |
+--------+
| Jason |
| Taylor |
| dsf |
+--------+
4.ORDER BY 子句
对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认升序.
SELECT * FROM Student ORDER BY Sage DESC; // DESC降序
+--------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+--------+--------+------+------+-------+
| 31223 | dsf | ma | 23 | IS |
| 312028 | Taylor | fe | 21 | ELE |
| 312001 | Jason | fe | 19 | CS |
+--------+--------+------+------+-------+
5.GROUP BY 子句
将查询结果按某一列或多列的值分组,属性值相等的为一组。 对查询结果进行分组是为了细化聚集函数的作用对象,分组后,聚集函数将作用于每一个分组,即每一组都有一个函数值。
SELECT Cno,COUNT(Sno) FROM Sc GROUP BY Cno;
// 按照属性 Cno 进行分组,再对每一个分组计算 Sno的个数
+-----+------------+
| Cno | COUNT(Sno) |
+-----+------------+
| 001 | 2 |
| 003 | 1 |
| 004 | 1 |
| 006 | 1 |
+-----+------------+
如果分组后还要求按一定的条件对这些分组进行筛选,最终只输出满足条件的组,则使用 HAVING 短语指定筛选条件。
SELECT * From Sc;
+--------+------+-------+
| Sno | Cno | Grade |
+--------+------+-------+
| 20123 | 004 | 96 |
| 20132 | 001 | 88 |
| 312001 | 001 | 98 |
| 312028 | 003 | 99 |
| 312028 | 004 | 100 |
| 312028 | 006 | 100 |
| 312028 | 009 | 100 |
| 312028 | 010] | 100 |
| 312029 | 006 | 100 |
+--------+------+-------+
SELECT Sno FROM Sc GROUP BY Sno HAVING COUNT(*) > 3;
先用 GROUP BY 子句按 Sno 进行分组,再用聚集函数对每一个分组计数,HAVING短语给出选择组的条件,满足条件(元组个数>3)的组才会被选出来。
+--------+
| Sno |
+--------+
| 312028 |
+--------+
注意: WHERE 子句与 HAVING 短语的区别在于 作用对象不同,WHERE 子句作用于 基本表或者视图,从中选择满足条件的元组。HAVING 短语作用于分组,从中选出满足条件的组。
[例] 查询平均成绩大于等于90分的学生学号和平均成绩,
SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno HAVING AVG(Grade)>=90;
// 先按学号进行分组,得出每一个学生课程组,再计算平均值,根据 HAVING 条件,输出平均分>=90分的分组.
+--------+------------+
| Sno | AVG(Grade) |
+--------+------------+
| 20123 | 96.0000 |
| 312001 | 98.0000 |
| 312028 | 99.8000 |
| 312029 | 100.0000 |
+--------+------------+
6.嵌套查询
在 SQL 语言中, 一个 SELECT-FROM-WHERE 语句成为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING短语的条件中 的查询称为嵌套查询(nested query)。
select Sname from Student where Sno IN // 外层查询或父查询
(select Sno from Sc where Cno='006'); // 内层查询或子查询
SQL 允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。
注意: 子查询的 SELECT 语句中不能使用 ORDER BY 子句, ORDER BY 子句只能对最终查询结果排序。
嵌套查询使用户可以用多个简单的查询构成复杂的查询,从而增强SQL的查询能力。 以层层嵌套的方式来构造程序正是 SQL 中 “结构化” 的含义所在。
- 带有 IN 谓词的子查询
在嵌套查询中,子查询的结果往往是元组的集合,所以 IN 在嵌套查询中最经常使用。
[例]
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='huangxin');
// 与 huangxin 在同一个系的学生信息
+--------+---------------+-------+
| Sno | Sname | Sdept |
+--------+---------------+-------+
| 312019 | Linus Travlds | Linux |
| 312022 | huangxin | Linux |
+--------+---------------+-------+
这种子查询的条件不依赖于父查询,称为不相关子查询。( 执行过程: 由里向外处理,即先执行子查询,子查询的结果用于建立父查询的查找条件).
如果子查询的查询条件依赖于父查询,这类子查询称为 相关子查询. 整个查询语句称为 相关嵌套查询(correlated nested query).
- 带有比较运算符的子查询
指的是父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时,可以用(>< >= <= !=或<> )等比较运算符。
[例] 相关子查询,子查询的查询条件 x.Sno 依赖于父查询传进来的元组 x,是与父查询相关的,> 用于连接父查询 、子查询
SELECT Sno,Cno FROM Sc x WHERE Grade > (SELECT AVG(Grade) FROM Sc y WHERE y.Sno = x.Sno);
// x,y 是表的别名
+--------+-----+
| Sno | Cno |
+--------+-----+
| 312028 | 004 |
| 312028 | 006 |
| 312028 | 009 |
+--------+-----+
- 带有 ANY(SOME) 或 ALL 谓词的子查询
子查询返回单值时,可以用比较运算符,但返回 多个值时 要用ANY、ALL来进行连接。 而是用ANY/ALL 时,必须同时使用 比较运算符。
[例] 查询 ELE 系 中任意一个年龄小于ELE系学生的信息
SELECT Sname,Sage,Sdept FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';
+-------+------+-------+
| Sname | Sage | Sdept |
+-------+------+-------+
| Jason | 19 | CS |
+-------+------+-------+
也可以用聚集函数实现:
SELECT Sname,Sage,Sdept FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';
+-------+------+-------+
| Sname | Sage | Sdept |
+-------+------+-------+
| Jason | 19 | CS |
+-------+------+-------+
小结: 使用聚集函数实现子查询通常比 直接用 ANY/ALL 查询效率要高!
=ANY 等价于 IN
<ANY 等价于 <MAX
!=ALL 等价于 NOT IN
<ALL 等价于 <MIN
- 带有 EXISTS 的子查询
这种子查询不返回任何数据,只产生 逻辑真值‘true’ 或逻辑假值 ‘false’.
[例] 相关子查询,子查询依赖于父查询的 Student.Sno 进行条件判断,若成功,则 WHERE子句 返回 '真',对“父”的每一个元组,重复完成这一过程。
SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');
+--------+ // WHERE 返回真值后,外层查询从元组中提取 Sname 列
| Sname |
+--------+
| Taylor |
+--------+
NOT EXIST : 内层查询结果为空时,则外层的 WHERE 子句返回真值。
SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');
+---------------+
| Sname |
+---------------+
| dsf |
| huangxin |
| Jason |
| Linus Travlds |
+---------------+
7. 集合查询
SELECT 语句查询的结果是元组的集合,所以多个SELECT 语句的结果可以进行 集合操作。
注意: 参加集合操作的各查询结果的列数必须相同,对应的数据类型也必须相同。
- UINON 会自动去掉重复元组, 如果要保留重复元组则使用 UNION ALL 操作符。
select * from Student where Sdept='CS' UNION select * from Student where Sage <=19;
+--------+-------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+--------+-------+------+------+-------+
| 312001 | Jason | fe | 19 | CS |
+--------+-------+------+------+-------+
MySQL 不支持集合的 INTERSECT 、EXCEPT 操作。
可以用 WHERE 的 条件句实现:
select * from Student where Sage <= 22 AND Sdept='CS';
// 年龄小于22并且是CS系
8. 基于派生表的查询
子查询出现在 FROM 子句中, 这时子查询生成的临时派生表(derived table)成为主查询的查询对象。
[例] mysql 不能指定派生表的属性列,子查询SELECT后面的列名为其默认属性。 可以在子查询指定别名
SELECT Sname FROM Student,(SELECT Sno FROM Sc WHERE Cno='001') AS Sc1 WHERE Student.Sno=Sc1.Sno;
//派生表
+-------+
| Sname |
+-------+
| Jason |
+-------+
AS 关键字可以省略,但是必须为 派生关系指定一个别名。
SELECT Sname FROM Student,(SELECT Sno www FROM Sc WHERE Cno='001') Sc1 WHERE Student.Sno=Sc1.www;
+-------+
| Sname |
+-------+
| Jason |
+-------+