数据库实践LAB大纲 02 检索

单表查询（基本查询、分组查询）

select

/*
SELECT [ALL | DISTINCT] <目标列表达式> [，<目标列
表达式>]
FROM <表名或视图名>[，<表名或视图名>]
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]]
*/

select子句

除了 select from 外其余内容可选

• select 后面列出字段列名，列名之间逗号分隔
• 若要显示所有，直接用 *（多表连接若想指定某个表如q1的所有内容，可以写 q1.*）
• 定义别名
  • 列名 列标题
  • 列名 as 列标题
• ALL显示所有数据行，重复的也显示（默认）
• DISTINCT 只显示不重复

LIMIT

选取确定数量行

LIMIT [start,] count

结果集中，第一行记录为0

也可以用OFFSET配合LIMIT一起使用
OFFSET start LIMIT count等价于上面的操作

where

关系/逻辑/取值/空值表达式

1. 关系 = < > >= <= != <> <=>
2. 逻辑 AND OR NOT
3. 取值范围 BETWEEN A AND B

• 等价于 <= B and >= A

4. 空值 is null / is not null

使用关系运算符号 （详见上一章数据库实践LAB大纲 01 管理

模糊查询

1. % 任意多个字符
2. _ 任意一个字符

使用LIKE关键字

SELECT B_Name,B_Publisher,B_SalePrice 
FROM BookInfo
WHERE B_Name LIKE '%MySQL%';

转义字符

字符有通配符，要使用ESCAPE关键字

查询会员中含 _ 的会员信息

SELECT * FROM Users 
WHERE U_Name LIKE '%/_%' ESCAPE '/';

分组查询

聚集函数

聚合函数	说明
SUM（）	返回某列所有值的总和
AVG（）	返回某列的平均值
MAX（）	返回某列的最大值
MIN（）	返回某列的最小值
COUNT（）	返回某列的行数

除了COUNT， 其他聚合函数忽略NULL的行

Group by

SELECT B_Publisher,MAX(B_MarketPrice) AS max_price, 
MIN(B_MarketPrice) AS min_price
FROM BookInfo
GROUP BY B_Publisher;

GROUP BY 可配合 HAVING使用

• 分组之后按条件给组筛选

SELECT B_Publisher ,COUNT(*) AS total_number
FROM BookInfo 
WHERE B_MarketPrice>=50
GROUP BY B_Publisher
HAVING COUNT(*)>=2;

HAVING对比WHERE

1. WHERE在group by前过滤， having在 group by之后过滤

如果使用了 group by
select和having的内容要么在group by中出现，要么要用聚集函数框住。

多表查询

表连接

SELECT ...
FROM ... [JOIN TYPE] JOIN ... ON ...
WHERE ...

JOIN TYPE 三种类型

1. INNER JOIN(默认)
2. OUTER JOIN

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN (可以用LEFT 和 RIGHT 实现)

3. CROSS JOIN

LEFT和RIGHT OUTER JOIN的 结果集中包含左（右）表全部记录，若右（左）表没有满足链接的记录，相应数据填为NULL

可以join两边使用相同的table形成 自连接

使用 CROSS JOIN
不带where直接返回笛卡尔积
MySQL中和inner join的区别在于 CROSS join 不能用 on 而inner join 可以

子查询

返回单行的子查询（这种子查询可以使用比较运算符来连接）

SELECT OD_ID,OD_Number,OD_Price
FROM OrderDetails WHERE B_ID=
(SELECT B_ID FROM BookInfo WHERE B_Name='ASP.NET4 5016789:')

多行子查询
要配合IN EXISTS ALL ANY SOME使用
ANY和SOME是同义词

SELECT U_ID,U_Name,U_Phone
FROM Users
WHERE U_ID IN
(SELECT U_ID FROM Orders WHERE O_TotalPrice<50);

EXIST关键字
不返回数据，只返回逻辑真值、假值

WHERE [NOT] EXISTS (子查询)

子查询与数据更新

insert into <table_name> [column_name] <子查询>

联合查询 UNION[ALL]

SELECT1 UNION [ALL] SELECT2

ALL保留重复记录，默认的时候自动删除

查询效率

子查询多次遍历数据 —— 内部创建临时表
连接查询只遍历一次

• 一般来说 连接查询效率更高
• 数据量较少，子查询更容易控制

高级查询（复杂子查询）

查询所有选1号课的学生姓名

1. 连接查询

SELECT Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='1';

SELECT Sname
FROM Student INNER JOIN SC ON 
Student.Sno=SC.Sno WHERE SC.Cno= '1';

2. exists

select Sname from Student where exists
( select * from SC
where Sno = Student.Sno AND Cno= '1');

包含关系查询

$$B-A=\varnothing \iff B\subseteq A$$

包含B的A结构
not exists (B except A)

选了所有课程的学生姓名

select Sname from Student where not exists
    (select * from Course where not exists
        (select * from SC where Sno=Student.Sno AND Cno=Course.Cno ));

-- 没有一门课 他不选修
-- B是全部课程
-- B except A就是把学生已经学的去掉 那么剩下的课程就是没上的
-- 因此还有剩下的课程的B except A的学生再去掉，就是已经修完课程的学分

select distinct Sno from SC SCX
where not exists
    (select * from SC SCY
    where SCY.Sno='200215122' AND
    not exists 
        (select * from SC SCZ where SCZ.Sno=SCX.Sno 
        AND SCZ.Cno=SCY.Cno));
-- B为200215122选的课
-- A为其他学生选的课
-- B减去其他学生选的课，那么如果还有数据，说明某个学生没选B选过的课

ROLLUP

GROUP BY ROLLUP(A,B,C)

1. 先对 A,B,C group by
2. 然后分开的部分对 A,B进行 GROUP BY
3. 然后A进行GROUP BY
4. 最后对全表 GROUP BY —— 凑成一张表

select classNo,studentNo,SUM(absent) ,SUM(sickleave), SUM(late)
from test_rollup_class
group by classNo,studentNo with rollup

ROLLUP和ORDER BY 互斥

CUBE

1. 先对 A,B,C group by
2. 然后对 (A,B) (A,C) (B,C) (A) (B) © GROUP BY
3. 全表group by 并拼起来

select classNo,studentNo,SUM(absent) ,SUM(sickleave), SUM(late)
from test_rollup_class
group by classNo,studentNo with cube

CUBE在ROLLUP上继续细分，所以可以用ROLLUP查询表示CUBE

数据有NULL，rollup可能会出现问题
所以可以使用ifnull()转换，ifnull(column_name, 0)

with

提供 定义临时关系方法
只对包含with子句查询有效

with max_budget (value) as 
	(select max(budget)
	from department)
select budget, dept_name
from department, max_budget
where department.budget = max_budget.value;
-- 有最大预算值的系