05.查询表

查询表

• 字段显示可以使用别名:

  • col1 AS alias1, col2 AS alias2, …

• WHERE子句:指明过滤条件以实现“选择"的功能:

  • 过滤条件:
    • 布尔型表达式
    • 算术操作符:+,-,*,/,%
    • 比较操作符:=,<=>(相等或都为空),<>,!=(非标准SQL),>,>=,<,<=
    • 范围查询: BETWEEN min_num AND max_num
    • 不连续的査询:IN(element1,element2,…)
    • 空查询: IS NULL, IS NOT NULL
    • IN 判断某字段是否在一组值中, NOT IN 判断某字段不在一组值中,IN() 可以接受常量列表或子查询
    • DISTINCT 去除重复行
    • 模糊査询: LIKE 使用 % 表示任意长度的任意字符，_ 表示任意单个字符
    • RLIKE:正则表达式，索引失效，不建议使用
    • REGEXP:匹配字符串可用正则表达式书写模式，同上
  • 逻辑操作符:NOT，AND，OR，XOR

• GROUP BY:根据指定的条件把查询结果进行"分组"以用于做"聚合"运算

  • 常见聚合函数: count(), sum(), max(),min(), avg(),注意:聚合函数不对null统计
  • HAVING: 对分组聚合运算后的结果指定过滤条件
  • 一旦分组 group by，select语句后只跟分组的字段，聚合函数

• ORDER BY: 根据指定的字段对查询结果进行排序

  • 升序:ASC
  • 降序:DESC

• LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

• 对查询结果中的数据请求施加“锁”

  • FOR UPDATE:写锁，独占或排它锁，只有一个读和写操作
  • LOCK IN SHARE MODE:读锁，共享锁，同时多个读操作

1. 单表查询

1.1 简单查询

mysql> select * from students where id < 5;
mysql> select * from students where gender = 'm';注意：第一条记录的索引是 0
mysql> select * from students order by name desc limit 5;
mysql> select * from students order by name desc limit 0,5;# 判断是否为NULL
mysql> select * from students where classid is null;
mysql> select * from students where classid <=> null;
mysql> select * from students where classid is not null;mysql> select * from students where stuid >= 2 and stuid <= 8;
mysql> select * from students where stuid between 2 and 8;mysql> select * from students where name like 's%';
mysql> select * from students where name rlike '.*[s].*';mysql> select * from students where classid in (1,2,3);
mysql> select * from students where classid not in (1,2,3);# 字段别名
mysql> select stuid 学员ID,name 姓名,gender 性别 from students;# ifnu11函数判断指定的字段是否为空值,如果空值则使用指定默认值
mysql> select stuid 学号, name 姓名, ifnull(classid,'无班级') 班级 from students where classid is null;
+--------+-------------+-----------+
| 学号   | 姓名        | 班级      |
+--------+-------------+-----------+
|     24 | Xu Xian     | 无班级    |
|     25 | Sun Dasheng | 无班级    |
+--------+-------------+-----------+# 记录去重
mysql> select distinct classid from students;
mysql> select distinct age,gender,classid from students;# 分页查询
mysql> select * from students limit 0,3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)mysql> select * from students limit 3,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     4 | Ding Dian |  32 | M      |       4 |         4 |
|     5 | Yu Yutong |  26 | M      |       3 |         1 |
|     6 | Shi Qing  |  46 | M      |       5 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)# 查询第n页的数据,每页显示m条记录
mysql>select * from students limit (n-1)*m,m;# 聚合函数
mysql> select count(*) from students where gender = 'm';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.01 sec)mysql> select sum(age) from students where gender = 'm';
+----------+
| sum(age) |
+----------+
|      495 |
+----------+
1 row in set (0.00 sec)mysql> select sum(age)/count(*) from students where gender = 'm';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           33.0000 |
+-------------------+
1 row in set (0.00 sec)# 分组统计
注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL-MODE的值不同而不同的结果mysql> select classid,count(*) from students group by classid;
mysql> select gender,classid,count(*) from students group by gender,classid;mysql> select gender,classid,count(*) from students group by gender,classid having count(*) > 2;
mysql> select gender,classid,count(*) from students group by gender,classid having classid > 2;# group_concat函数实现分组信息的集合
mysql> select classid,group_concat(name) from students group by classid;# with rollup 分组后聚合函数统计后再做汇总
mysql> select ifnull(gender,'总计')性别,count(*) from students group by gender with rollup;# 排序
mysql> select * from students order by stuid desc limit 3;
mysql> select * from students order by stuid desc limit 3,3;
mysql> select * from students where classid is not null order by gender desc,age asc;# 正序排序时将NULL记录排在最后
mysql> select classid from students order by -classid desc;# 分组后排序
mysql> select classid, count(*) from students group by classid order by classid desc;
mysql> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender desc,classid desc;注意：分组和排序的次序 顺序:group by,having,order by
mysql> select classid,count(*) from students group by classid having classid is not null order by classid asc;

2. 多表查询

多表查询，即查询结果来自于多张表

• 子查询:在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
• 联合查询:UNION
• 交叉连接:笛卡尔乘积 CROSS JOIN
• 内连接:
  • 等值连接:让表之间的字段以"等值”建立连接关系
  • 不等值连接
  • 自然连接:去掉重复列的等值连接,语法: FROM table1 NATURAL JOIN table2;
• 外连接:
  • 左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
  • 右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
  • 完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL不支持此SQL语法
• 自连接:本表和本表进行连接查询
• 注意：ON 定义两个表之间的连接条件， JOIN 把两个表按照某种方式合并

2.1 子查询

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,

主要有以下四种常见的用法:

1. 用于比较表达式中的子查询;子查询仅能返回单个值

mysql> select avg(age) from students;
mysql> select name,age from students where age > (select avg(age) from students);

2. 用于IN中的子查询:子查询应该单独查询并返回一个或多个值重新构成列表

mysql> select name,age from students where age in (select age from teachers);

3. 用于EXISTS 和 Not EXISTS

EXISTS（包括 NOT EXISTS）子句返回一个布尔值（TRUE 或 FALSE）。它内部包含一个子查询（称为内查询），用于判断该子查询是否返回了任何行。

对于外查询中的每一行数据，系统都会将该行的值带入到内查询中进行验证。如果内查询返回了至少一行结果，则 EXISTS 返回 TRUE，该行数据就会被包含在外查询的结果集中；否则返回 FALSE，该行不会出现在最终结果中。

NOT EXISTS 的工作方式类似，只不过是在内查询结果为空时才返回 TRUE。

mysql> select * from students s where exists (select * from teachers t where s.teacherid=t.tid);# 说明:
1、EXISTS(或 NOT EXISTS))用在 where之后，且后面紧跟子查询语句(带括号)
2、EXISTS(或 NOTEXISTS)只关心子查询有没有结果,并不关心子查询的结果具体是什么
3、上述语句把students的记录逐条代入到Exists后面的子查询中，如果子查询结果集不为空，即说明存在，那么这条students的记录出现在最终结果集，否则被排除。

4. 用于FROM子句中的子查询

mysql> select classid,avg(age) age from students where classid is not null group by classid;# 主查询 + 子查询
mysql> select s.classid,s.age from (select classid,avg(age) age from students where classid is not null group by classid) s where s.age > 30;

子查询优化

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使査询语句很灵活，但执行效率不高。执行子查询时，需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

可以使用连接(J0IN)查询来替代子査询。连接査询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。

2.2 联合查询

联合査询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的

mysql> select tid id,name,age,gender from teachers union select stuid id,name,age,gender from students;# 合并数据并去重 UNION, 合并数据且无需去重 UNION ALL
mysql> select * from teachers union select * from teachers;
mysql> select * from teachers union all select * from teachers;

2.3 交叉连接

cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加,"雨露均沾

比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列

交叉连接生成的记录可能会非常多,建议慎用

# 完全等价，都表示笛卡尔积
mysql> select * from teachers cross join students;
mysql> select * from teachers,students;

2.4 内连接

inner join 内连接取多个表的交集

mysql> select * from students s inner join teachers t on s.teacherid=t.tid;# 内连接后再过滤
mysql> select * from students s inner join teachers t on s.teacherid=t.tid where s.age > 30;# 自然连接
1. 当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
2. 在使用纯自然连接时，如没有相同的列时，会产生交叉连接(笛卡尔乘积)语法:SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;

2.5 左和右外连接

左连接: 以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用nul值填充

右连接:以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s left join teachers t on s.teacherid=t.tid;mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s left outer join teachers t on s.teacherid=t.tid;# 先左外连接再过滤
mysql> select * from students s left join teachers t on s.teacherid=t.tid where s.teacherid is null;# 右外连接
mysql> select * from students s right join teachers t on s.teacherid=t.tid;
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid;# 右外连接再过滤
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid where t.tid is null;注意：右外连接其实就是把左外连接的表位置对换一下，结果是一样的。

2.6 完全外连接

MySQL不支持完全外连接 full outer join语法

# 用这个方法替代 full outer join
mysql> select * from students left join teachers on students.teacherid=teachers.tid-> union-> select * from students right join teachers on students.teacherid=teachers.tid;

2.7 自连接

自连接, 即表自身连接自身

3. 补充说明

• SQL语句查询顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• 查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎