MySQL深入02-DML之Select查询

江南才子

查询分类
单表查询：简单查询
多表查询：连接查询
联合查询：多个查询结果汇总
查询的组成
投影查询：挑选要显示的字段

select array1,array2,... from tb_name;

选择查询：挑选符合条件的行

select * from tb_name where-clause

where-clause：布尔条件表达式
= # 等值比较
<=> # 等值比较，包括与NULL的安全比较
<>或!= # 不等值比较
<，<=，>，>= # 其它比较符
IS NULL # 是否为空值
IS NOT NULL # 是否不为空值
LIKE # 支持的通配符有%和_
RLIKE或REGEXP # 正则表达式匹配
IN # 判指定字段的值是否在给定的列表中
BETWEEN … AND …  # 在某取值范围内

组合条件查询

NOT，!  # 非
AND，&& # 和
OR，|| # 或

聚合函数查询

SUM() # 求总和
AVG() # 求平均值
MAX() # 求最大值
MIN() # 求最小值
COUNT() # 求记录总数
#注：count(*)效率最低，可指定某一字段求总数，如count(Name)

查询语句
语法

select ... from tb_name where-clause [others-clause]

各子句类型及作用

where # 条件过滤
group by # 对查询结果分组
having # 对group by的结果进行条件过滤
order by # 排序
limit # 限制输出行数，如”limit 2“表示只显示前2行，“limit 2,3”表示偏移前2行，显示3-5行

其它修饰符

distinct # 指定的结果相同的只显示一次
sql_cache # 缓存于查询缓存中
sql_no_cache # 不缓存查询结果

select的执行流程

from-->where-->group by-->having-->order by-->select-->limit

多表查询
说明
事先将两张或多张表连接(join)，根据连接的结果进行查询
语法

select … from tb1 Join_Type tb2 [on (Join_Condition)] 连接类型有： cross join，inner join，left join和right join等

连接分类
cross join：交叉连接，又称笛卡尔乘积
表1中的记录(共M条)与表2中的记录(共N条)逐一连接，交叉连接数据量较大，共M*N条记录；

select * from students,classes
# 注：不带where子句哦

inner join：内连接，根据连接判断的运算符又可分类如下：
等值连接：根据表1和表2中某字段值相等进行连接，其查询结果中列出被连接表中的所有列，包括其中的重复列

select * from authors as a inner join publishers as p on a.city=p.city

条件比较连接：在连接条件中使用除等号(=)运算符之外的运算符，如>,>=,<,<=,<>等
自然连接：在连接条件中使用等号(=)运算符，且只选择结果集中的部分列，并删除连接表中的重复列

select a.name,p.pub_name from authors as a inner join publishers as p on a.city=p.city

外连接：返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接)、右表(右外连接)或两个边接表(全外连接)中的所有数据行
左外连接：返回包括左表中的所有记录和右表中联结字段相等的记录

tb1 LEFT JOIN tb2 ON 连接条件

右外连接：返回包括右表中的所有记录和左表中联结字段相等的记录

tb1 RIGHT JOIN tb2 ON 连接条件

全外连接：连接运算两边的关系中的元组都保留，MySQL不支持此种连接
自连接：同一表中某字段的多次比较使用

select stu1.Name as StuName,stu2.Name as TeachName from students as stu1,students as stu2 where stu1.TeacherID=stu2.StuID;

子查询
说明：
在外层查询中嵌套的内层查询叫做子查询，类型有：
where型子查询：把内层查询结果当做外层查询的比较条件

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods)
# 子查询的返回值只能有1个

exists型子查询：把内层查询结果拿到外层，看外层的查询是否成立

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);
# 可通过转换为内连接查询：
select cat_id,cat_name from category inner join goods on goods.cat_id=category.cat_id;
# 与exists类似的还有in子查询，表示外层查询条件在一个内层查询的结果集内
select * from user_core where userId in (select userId from user_classes where class_id=’A001’);

from型子查询：把内层的查询结果供外层查询使用

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2)) group by name；

注：MySQL不擅长于子查询的优化，故应避免使用子查询


联合查询
说明
把两个或多个查询语句的结果合并成一个结果进行输出，前提是多个查询结果的输出字段是一致的
语法

SELECT clause UNION SELECT clause UNION …

union和union all的区别
union：会将多个表的查询结果合并后进行排序，同时删除重复的行，故效率略慢；
union all：只是简单的将多个表的结果合并后返回，不删除重复行，效率较高
实例
涉及的表结构：

　　查询需求有：

　　

　　上一篇：MySQL深入01-SQL语言-数据字典-服务器变量-数据操作DML-视图
　　下一篇：MySQL深入03-锁-事务-GTID