文章目录

• 单表查询（基本查询、分组查询）
• • select子句
  • LIMIT
  • where
  • • 关系/逻辑/取值/空值表达式
    • 模糊查询
  • 分组查询
  • • 聚集函数
    • Group by
• 多表查询
• • 表连接
  • 子查询
  • 子查询与数据更新
  • 联合查询 UNION[ALL]
  • 查询效率
• 高级查询（复杂子查询）
• • 包含关系查询
  • ROLLUP
  • CUBE
  • with

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

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

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

关系 = < > >= <= != <> <=>

逻辑 AND OR NOT

取值范围 BETWEEN A AND B

• 等价于 <= B and >= A

空值 is null / is not null

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

模糊查询

% 任意多个字符

_ 任意一个字符

使用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

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

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

多表查询

表连接

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

JOIN TYPE 三种类型

INNER JOIN(默认)

OUTER JOIN

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

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号课的学生姓名

连接查询

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';

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 SCYwhere SCY.Sno='200215122' ANDnot 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)

先对 A,B,C group by

然后分开的部分对 A,B进行 GROUP BY

然后A进行GROUP BY

最后对全表 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

先对 A,B,C group by

然后对 (A,B) (A,C) (B,C) (A) (B) © GROUP BY

全表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; -- 有最大预算值的系

总结

以上是生活随笔为你收集整理的数据库实践LAB大纲 02 检索的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。