单表查询
基本查询 select from
格式
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]
FROM <表名或视图名>[,<表名或视图名>]
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]]
[LIMIT [start,] count];
除了select外,其他都是可选的
若要输出所有数据,使用select *
定义别名
- select A B
- select A as B
ALL是默认显示所有的数据行,包括重复
DINSTINCT值显示不重复的行
LIMIT的使用,指定数据行数
LIMIT [START,] count
LIMIT 5返回前五行记录
LIMIT 10,20 从11行开始返回20行记录
条件查询 where
关系式查询
- =
- >
- <
- >=
- <-
- != or <>
逻辑表达式
- and
- or
- not
取值范围表达式
between and
控制查询
- IS NULL
- IS NOT NULL
别用!=判断
模糊查询
通配符
- % 任意多个字符
- _ 单个字符
通配符使用like来匹配
WHERE B_Name LIKE '%MySQL%';
匹配字符含有MySQL的数据
若字符串本身有通配符,要用ESCAPE转义
比如查询字符串本身含有‘_'下划线的数据
可以使用escape
select * from users
where uname like '%/_%' escape '/';
分组查询
聚集函数
- SUM
- AVG
- MAX
- MIN
- 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 <查询条件>
三种连接
- 内连接 inner join
- 外连接 outer join
- 交叉连接 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值返回到结果集中
也有三种类型
- 左外连接
- 右外连接
- 全外连接
左外连接:集中包含左表的所有记录,右表若没有满足连接记录填null
右外连接类似
自连接
同一张表进行的连接:看作这个表的两个副本的连接,需要指定两个别名
交叉连接
使用crooss join关键字
- 若不带where子句,返回结果是表的笛卡尔积
- 代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 = ∅ B-A=\empty B−A=∅
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}]
- GROUP BY ROLLUP(A,B,C)
对A,B,C groupyby,然后对A,B,group by,最后对A进行group by,最后再对全表group by - 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;