检索表记录 e2

单表查询

基本查询 select from

格式

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

除了select外，其他都是可选的

若要输出所有数据，使用select *
定义别名

1. select A B
2. select A as B

ALL是默认显示所有的数据行，包括重复
DINSTINCT值显示不重复的行

LIMIT的使用，指定数据行数

LIMIT [START,] count

LIMIT 5返回前五行记录
LIMIT 10,20 从11行开始返回20行记录

条件查询 where

关系式查询

1. =
2. >
3. <
4. >=
5. <-
6. != or <>

逻辑表达式

1. and
2. or
3. not

取值范围表达式

between and

控制查询

1. IS NULL
2. IS NOT NULL

别用!=判断

模糊查询
通配符

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

通配符使用like来匹配

WHERE B_Name LIKE '%MySQL%';

匹配字符含有MySQL的数据

若字符串本身有通配符，要用ESCAPE转义

比如查询字符串本身含有‘_'下划线的数据
可以使用escape

select * from users
where uname like '%/_%' escape '/';

分组查询

聚集函数

1. SUM
2. AVG
3. MAX
4. MIN
5. COUNT

统计Users数量

select count(*) from users
select count(uphone) from users

count(*)会把null也算入
其他的聚集函数都会忽略掉null值

group by
查出版社出版图书数量

SELECT B_Publisher ,COUNT(*) AS sum_of_publisher
FROM BookInfo 
GROUP BY B_Publisher;

最大最小值

SELECT B_Publisher,MAX(B_MarketPrice) , MIN(B_MarketPrice) 
FROM BookInfo
GROUP BY B_Publisher

having

having要和group by同时使用，因为having表示分组后对这些组进行筛选

查出了两本以上价格大于50的图书信息

select b_publisher, count(*)
from bookinfo
where b_marketprice >= 50
group by b_publisher
having count(*) >= 2

having与where的比较

having与where都是设置查询条件，作用对象不同
where在数据分组前过滤，而having的对象在分组后进行过滤

多表查询（表连接、子查询）

表连接

SELECT <查询列表>
FROM <表名1> [连接类型] JOIN <表名1> ON <连接条件>
WHERE <查询条件>

三种连接

1. 内连接 inner join
2. 外连接 outer join
3. 交叉连接 cross join

用来连接两个表的条件称为连接条件，通常是通过匹配多个表中的公共字段实现的

内连接

内连接是最常用的连接，也是默认的链接，在from语句中的joinn就是默认使用inner join连接

检索bookinfo和booktype表，查询每本图书所属图书类别

select B_name, bookinfo.BT_ID, BT_NAME
from bookinfo inner join booktype
on bookinfo.BT_ID = booktype.BT_ID
order by BT_ID;

外连接

主表每行数据匹配从表的数据行，如果匹配连接条件则返回到结果集当中；

若没找到匹配行，则主表的行仍然保留，且返回到结果集中，相应的从表中数据行被填上NULL值返回到结果集中

也有三种类型

1. 左外连接
2. 右外连接
3. 全外连接

左外连接：集中包含左表的所有记录，右表若没有满足连接记录填null

右外连接类似

自连接
同一张表进行的连接：看作这个表的两个副本的连接，需要指定两个别名

交叉连接
使用crooss join关键字

1. 若不带where子句，返回结果是表的笛卡尔积
2. 代where语句，为表笛卡尔积 减去 where子句限定而省略的行数

子查询

外层查询包含另一个内层查询
外层select语句称为主查询，where子句中包含的select语句为子查询

比如

SELECT OD_ID,OD_Number,OD_Price
FROM OrderDetails WHERE B_ID=
(SELECT B_ID FROM BookInfo WHERE B_Name='ASP.NET');

返回多行子查询包含多行数据，通常与
IN, EXITSTS, ALL, ANY, SOME一起使用

WHERE <表达式> [NOT] IN (<子查询>)
WHERE [NOT] EXISTS (<子查询>)
EXISTS不返回数据，只返回真假值，使用EXISTS的时候子查询通常全输出（输出*）
WHERE <表达式> <比较运算符> [ALL | ANY | SOME ] (<子查询>)
ANY 表示任何一个元素和子查询中一个值匹配， SOME和ANY同义
ALL表示子查询中所有值相符合

数据更新

insert
insert into <表名> [<表名>] <子查询>
表要存在，表结构要和子查询结果结构兼容

CREATE TABLE AvgPrice(B_ID int,Avg_Price float);

INSERT INTO AvgPrice
SELECT BT_ID,AVG(B_SalePrice) FROM BookInfo GROUP BY BT_ID ;

UPDATE与DELETE类似

联合查询

````SELECT <语句1> UNION [ALL] SELECT <语句2>```
默认情况去除重复数据，但是选择ALL可以保留重复数据

效率比较

一般连接查询效率高
because: 子查询多次遍历数据， 内部创建临时表，但是连接查询只遍历一次。数据少的话子查询更容易控制。数据大的时候，几乎都是连接查询更快

子查询多次便利所有数据

高级查询（复杂子查询）

独立子查询： 子查询执行与外层无关，将子查询结果带入上层查询条件再进行上层查询
相关子查询（双重循环）：子查询与外层有关。外层查询的迭代，内层要根据外层查询当前数据行重新执行一遍——子查询要用外层查询的值

连接查询查询

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

# 拿出student表一个元素
# SC找出sno和元素相同的sno，并且对应cno = 1的元素
# 外层where子句返回真表示这个元素可以输出

包含关系查询

$$B-A=\varnothing$$

not exist (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
	)
)
#双重否定表示全称量词FOR ALL
#等价于 没有一门课程他没上过的学生

至少选秀嘘声编号200215122选秀的全部课程学生号码

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
	)
);
# 不存在课程y， 202215122选了但是结果中的学生x没选

ROLLUP CUBE

GROUP BY [分组表达式] [WITH {CUBE|ROLLUP}]

1. GROUP BY ROLLUP(A,B,C)
   对A,B,C groupyby，然后对A,B，group by，最后对A进行group by，最后再对全表group by
2. GROUP BY CUBE(A,B,C)
   先对(A,B,C)，然后(A,B) (A,C) (B,C) (A) (B)
   ©，最后对全表group by

CUBE和ROLLUP进一步从各种维度给出细化统计汇总结果(可以用ROLLUP表示CUBE)

注意
ROLLUP与ORDER BY互斥
分组包含NULL，ROLLUP结果可能不正确，因为rollup可以将null转换成别的数据：如IFNULL(XXX,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;