MYSQL——DQL : 数据查询语句(select)

1、SELECT 查询语句和条件语句

SELECT [ALL | DISTINCT | DISTINCTROW ]
select_list
FROM tbl_name [AS] alias_name
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

select过滤、去重

DISTINCT、DISTINCTROW对查询返回的结果集提供了一个最基本但是很有用的过滤。那就是结果集中只含非重复行。重复的记录，指的是，字段、值，都相同的记录，而不是部分字段相同的记录。在这里要注意的是，对关键词DISTINCT、DISTINCTROW来说，空值都是相等的，无论有多少NULL值，只选择一个。默认是“all”，表示所有。

select list：其中可以包含一项或多项下列内容：

1、“*”，表示按照create table的顺序排列的所有列。
2、按照用户所需顺序排列的列名的清单。
3、可以使用别名取代列名，形式如下：column name as column_heading。
4、表达式（列名、常量、函数，或以算术或逐位运算符连接的列名、常量和函数的任何组合）。
5、内部函数或集合函数。
6、上述各项的任何一种组合。

表名： 数据库.表名|表名 [AS] 表别名

常用条件：

1. <、= 等于 、<>不等于、>、in (n,m) 包含 、 not in (n,m)不包含、like 匹配 、 between n and m (在n~m的范围) 、 not BETWEEN 不在范围 、is null：在搜索一个NULL值时使用。
2. 条件运算： and 、 or 、 ( )

2、排序,分组,指针查询,计算

分组语句：group by 字段 (语句最后 order之前)
排序语句：order by 字段，字段 ASC / DESC (语句最后 limit之前，默认 升序，asc)
指针查询：limit 初始值，结束值 (语句最后)

聚合（合计）函数(计算)：

COUNT(*) 统计函数

MAX(*)最大值函数

MIN (*)最小值函数

AVG(*) 平均值函数

SUM(*)累计值函数（∑）

控制流程函数

select (case when 1<2 then 'yes' else 'no' end) as test;

select if(1<2,'yes ','no') as test;

3 联合查询（Union）：将多条select语句的结果，合并到一起。称之为联合操作。

union联合

使用 union关键字 联合 两个select语句即可：

mysql> select * from `users` where `age`=12
     union
     select * from `users` where `age`=13;
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  12 | 女    |
| 小红        |  12 | 女    |
| 小明        |  13 | 女    |
| 小刚        |  13 | 男    |
| 小默        |  13 | 女    |
+----------+-----+------+
5 rows in set (0.00 sec)

Union all:环境：

1，获得0115班所有代课教师，结果按照代课天数升序排序，同时需要获得0228班，结果按照降序排序
注意，如果union 的结果存在重复的记录，那么会消除重复.
可以通过 union选项 all 达到目的。

mysql> select * from `users` where `age`=12
     union all
     select * from `users` where `age`=13;
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  12 | 女    |
| 小敏        |  12 | 女    |
| 小敏        |  12 | 女    |
| 小红        |  12 | 女    |
| 小明        |  13 | 女    |
| 小明        |  13 | 女    |
| 小刚        |  13 | 男    |
| 小默        |  13 | 女    |
+----------+-----+------+
8 rows in set (0.00 sec)

mysql>

union排序

子语句结果的排序：

1. 将子语句包裹子括号内。
2. 子语句的order by，只要在配合limit时，才生效。union在做子语句时，会对没有limit的子语句进行优化

mysql> (select * from `users` where `userName`='小敏' ORDER BY `age`)
     union all
     (select * from `users` where `userName`='小明' ORDER BY `age` desc);
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  16 | 女    |
| 小敏        |  10 | 女    |
| 小敏        |  12 | 女    |
| 小明        |   5 | 女    |
| 小明        |   9 | 女    |
+----------+-----+------+
5 rows in set (0.00 sec)

#子语句的order by，只要在配合limit时，才生效
mysql> (select * from `users` where `userName`='小敏' ORDER BY `age` limit 3)
     union all
     (select * from `users` where `userName`='小明' ORDER BY `age`  limit 2);
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  10 | 女    |
| 小敏        |  12 | 女    |
| 小敏        |  16 | 女    |
| 小明        |   5 | 女    |
| 小明        |   9 | 女    |
+----------+-----+------+
5 rows in set (0.00 sec)

mysql>

对所以结果进行统一排序：

只需要在最后一个select语句后，增加相应的排序规则即可

mysql> (select * from `users` where `userName`='小敏')
union all
(select * from `users` where `userName`='小明')
ORDER BY `age` ;
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小明        |   5 | 女    |
| 小明        |   9 | 女    |
| 小敏        |  10 | 女    |
| 小敏        |  12 | 女    |
| 小敏        |  16 | 女    |
+----------+-----+------+
5 rows in set (0.00 sec)

**提示：
子语句的括号不是必须的，是为了方便语句识别
注意：
规定，多个select语句的检索到的字段数，必须一致。
更加严格的是，数据类型上，应该也有要求一致：
但是，mysql内部会做类型转换处理，要求是能够转换成功；
检索结果中的列名称问题：
以第一条select语句的列名称而定**

4、子查询：语句内部的查询语句，就是子查询语句。

返回值分类：单一值，一列，多列，表（多行，多列）

出现位置：

1. where后：where(subquery)
2. exists后：exists(subquery)
3. from后：from(subquery)

如何使用：获得一个值（也就是说返回一个值），使用关系运算符，进行判断=、>、>=、<、<=、!=

mysql> SELECT * FROM `users` WHERE `age` = (SELECT max(age) FROM `users`);
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  16 | 女    |
+----------+-----+------+
1 row in set (0.00 sec)

集合运算符：当返回多列（多个值时），
使用集合运算符，
in、not in
=any(集合) 等于集合中的任何一个即可。等同于in
!=any(集合)不等于集合中的任意一个。
!=all(集合) 不等于集合中的所有元素
some(集合)集中的一些与any(集合)等同
Not any(集合) 不是任何一个的,与!=all(集合)等同
some==any some在语法上与any相同

返回一行：

在参与比较时，使用括号可以构建一行：
(filed1, field2)

mysql> SELECT * FROM `users` WHERE (`userName`, `age`) = ( SELECT `userName`, `age` FROM `users` ORDER BY `age` DESC LIMIT 1 );
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  16 | 女    |
+----------+-----+------+
1 row in set (0.00 sec)

返回一个表：

如果用from子语句内，from子语句内，要求使用一个表，而能是一个结果，一应该给这个查询结果起一个别名。

外部查询所使用的列名，是由子查询指定的。

mysql> SELECT * FROM ( SELECT * FROM `users` WHERE age > 12 ) AS temp WHERE userName LIKE '小%';
+----------+-----+------+
| userName | age | sex  |
+----------+-----+------+
| 小敏        |  16 | 女    |
| 小刚        |  13 | 男     |
+----------+-----+------+
2 rows in set (0.00 sec)

mysql>

Exists

Exists(subquery) 判断依据：
如果子查询的 可以返回数据，则认为 exists 表达式 返回真。否者，返回假。

下面的两个语句完成的是同样的事情：

select * from `teacher_class` where exists (select * from `teacher` where `teacher_class`.`id`=`t_id`);

select * from `teacher_class` where `id` in (select `t_id` from `teacher`);

解决思路是不一样的：

exists： 先获得每一条 teacher_class的数据，然后获得id字段，去teacher表内查找对应值，找到，说明符合条件。

in：先获得所有的id的可能性。再在检索teacher_class数据时，判断当前的id是否在id集合内。

5、多表查询（jion）

每个实体，一个表
一个业务逻辑，使用多个实体的数据，
多张表应该在一起使用，将多个表的记录连接起来。

总体思路：

将所有的数据，按照某种条件，连接起来，再进行筛选处理。

连接的分类：
根据连接的条件不同，分类入如下：

1、内连接（inner join）
2、外连接 (outer join)
3、自然连接

测试表

class表

class_id	class_name
1	a
2	b
3	c

student表

student_id	class_id	student_name
1	1	李萌
2	2	王武
3	1	李浩

内连接(inner join)：

数据内部的连接，要求，连接的多个数据都必须存在才能进行连接

例：

select c.*,s.* from  student as s [inner] join class as c on c.id=s.class_id

inner join是Mysql默认的连接方案，可以省略 inner。

---

这个语法是内连接的另外一种写法，其执行结果与inner join 一样

select * from class,student where class.class_id=student.class_id

查询结果如下

class_id	class_name	student_id	class_id1	student_name
1	a	1	1	李萌
2	b	2	2	王武
1	a	3	1	李浩

外连接(outer join)：

数据内部的连接，要求，连接的多个数据都必须存在才能进行连接

A left  join   B   #的连接的记录数与A表的记录数同

A right  join   B   #的连接的记录数与B表的记录数同

A left join  B
#等价
B right join A

例：

select c.*,s.* from  student as s left/right join class as c on c.id=s.class_id

（RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，建议您使用LEFT JOIN代替RIGHT JOIN。）

左外连接（left join）

select c.*,s.* from  student as s left join class as c on c.class_id=s.class_id

如果左外连接（left join）的话，它将显示class表中所有记录

查询结果如下

class_id	class_name	student_id	class_id1	student_name
1	a	1	1	李萌
2	b	2	2	王武
1	a	3	1	李浩

右外连接（rgint join）

select c.*,s.* from  student as s right join class as c on c.class_id=s.class_id

如果右外连接（rgint join）的话，它将显示student表中所有记录

查询结果如下

class_id	class_name	student_id	class_id1	student_name
1	a	1	1	李萌
2	b	2	2	王武
1	a	3	1	李浩
3	c	null	null	null

自然连接：

通过 mysql 自己的判断完成连接过程！不需要指定连接条件。mysql会使用多表内的，相同字段，作为连接条件（注意，两个表同名的列不能超过1个。） 自然连接分成 内外之分：
内:* natural join *
外：左外：Natrual left join
右外：Natrual right join

注意：
* on student.class_id = class.class_id 等同于 using(c1) *
* using:会去掉结果中的 重复字段，并放在列前。 *

* natural join *

select * from student natural join class;
#等同于
select * from student inner join class using(`class_id`);

查询结果如下

class_id	student_id	student_name	class_name
1	1	李萌	a
1	3	李浩	a
2	2	王武	b

* Natrual left join *

select * from student natural left join class;
#等同于
select * from student left join class using(`class_id`);

查询结果如下

class_id	student_id	student_name	class_name
1	1	李萌	a
2	2	王武	b
1	3	李浩	a

* Natrual right join *

select * from student natural right join class;
#等同于
select * from student  right join class using(class_id);

查询结果如下

class_id	class_name	student_id	student_name
1	a	1	李萌
2	b	2	王武
1	a	3	李浩
3	c	null	null

交叉连接或笛卡尔积(cross join):

内连接，在连接时，是可以省略连接条件的。意味着，所有的左表的数据，都要与右表的记录做一个连接。共存在 N*M个连接，这种连接，就称之为，交叉连接，或者笛卡尔积。如本例会产生3*2=16条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

SELECT * FROM TableA CROSS JOIN TableB ; 
#等同于
SELECT * FROM TableA [INNER] JOIN TableB ;

查询结果如下

id	class_name	student_id	class_id	student_name
1	1	李萌	1	a
2	2	王武	1	a
3	1	李浩	1	a
1	1	李萌	2	b
2	2	王武	2	b
3	1	李浩	2	b
1	1	李萌	3	c
2	2	王武	3	c
3	1	李浩	3	c

注意：
mysql中 cross join与inner join相同，但在数据库的定义上，交叉连接就是笛卡尔积。是没有条件的inner join。