本文包括以下内容
- 简单查询
- 条件查询：where
- 分组查询：group by
- 排序查询：order by
- 查询结果去重：distinct
- 限制查询量：limit
0 准备
先建立两个表employee和department：
mysql> select * from employee;
+----+----------+--------+--------+------+------+---------+
| id | name | gender | salary | age | gmr | dept_id |
+----+----------+--------+--------+------+------+---------+
| 1 | lisan | f | 100.00 | 20 | 4 | 1001 |
| 2 | Will | f | 130.00 | 28 | 3 | 1001 |
| 3 | lucy | m | 500.70 | 18 | 2 | 1002 |
| 4 | 李雷 | 男 | 670.00 | 20 | 4 | 1003 |
| 5 | WangYong | m | 700.90 | 25 | 5 | NULL |
| 6 | Sam | m | 600.90 | 25 | 5 | 1004 |
+----+----------+--------+--------+------+------+---------+
mysql> select * from department;
+------+-----------+--------------+
| id | name | description |
+------+-----------+--------------+
| 1001 | 人事部 | 人事管理 |
| 1002 | 行政部 | 行政管理 |
| 1003 | 研发部 | 研发 |
| 1004 | 质量部 | 质量保障 |
+------+-----------+--------------+1 简单查询
select * from employee;
//
select id，name,salary from employee;2 条件查询
where 条件表达式
//查询条件有以下几种
- 比较：=、<、>、>=,<=,!=,<>
- 指定范围：between [] and [],NOT between [] and []
- 指定集合：IN [],NOT IN []
- 匹配字符：like,NOT LIKE
- 是否为空：IS NULL, IS NOT NULL
- 多个查询条件： AND ，OR
//比较
select * from employ where age>20;
select * from employee where age<>20;
//指定范围
select * from employee where age between 18 and 25;
//指定集合
select * from employee where name in ("Will","lucy");
//匹配字符，其中为"%"为通配符，"_"匹配单一字符
select * from employee where name like "w%";
+----+----------+--------+--------+------+------+---------+
| id | name | gender | salary | age | gmr | dept_id |
+----+----------+--------+--------+------+------+---------+
| 2 | Will | f | 130.00 | 28 | 3 | 1001 |
| 5 | WangYong | m | 700.90 | 25 | 5 | NULL |
+----+----------+--------+--------+------+------+---------+
select * from employee where name like "w_l";//查询结果为空
变成
select * from employee where name like "w_ll";就可以查到这一条数据了。
//AND
select * from employ where age>20 and salary<500;
select * from employee where name like "w%" AND age>20 AND salary>500;
//OR
select * from employee where name like "w%" AND age>25 OR salary>500;3 查询结果去重
使用DISTINCT关键字来消除重复的记录
select distinct [value Name]
//eg:
select distinct age from employee;4 排序
排序分为降序DESC，升序ASC两种，使用ORDER BY关键字。
ORDER BY [value] [ASC | DESC]
默认情况下安装ASC方式排序示例：
//升序
select * from employee order by age ASC;
//降序
select * from employee order by age DESC;
//多个排序条件：先按照age降序，相同age的字段按照id升序排序
select * from employee order by age DESC,id ASC;5 分组查询
GROUP BY [value] [HAVAING 条件表达式] [WITH ROLLUP]其中，value是按照该字段值分组，HAVING用来限制分组后的显示，WITH ROLLUP将会在坐在记录的最后加一条记录，该记录是上面所有记录的总和。
5.1 单独使用group by查询
//单独使用group by分组，查询结果只显示分组的一条记录
select * from employee group by age;5.2 与GRIOUP_CONCAT()函数一起使用
每个分组指定字段都显示出来
//与"GROUP_CONCAT()"函数一起使用
select age,group_concat(name) from employee group by age;
+------+--------------------+
| age | group_concat(name) |
+------+--------------------+
| 18 | lucy |
| 20 | lisan,李雷 |
| 25 | WangYong,Sam |
| 28 | Will |
+------+--------------------+
结果显示分为4组，每组所有人的name 列出来了。5.3 与集合函数一起使用
通过集合函数计算分组中的总记录最大值、最小值。
//按照age分组查询，age相同的为一组，然后对每组用集合函数count()计算出每一组的记录数
select age,count(age) from employee group by age;5.4与HAVING条件表达式一起使用
加上HAVCING…，可以限制输出的结果，只有满足条件的才会显示。
//与HAVING一起使用
//按照age字段查询，然后显示记录大于等于2的分组。
select age,count(age) from employee group by age HAVING count(age)>=2;
+------+------------+
| age | count(age) |
+------+------------+
| 20 | 2 |
| 25 | 2 |
+------+------------+5.5 多个字段分组
//先按照age分组，age中有相同的记录用gender再分组
select * from employee group by age,gender;
+----+----------+--------+--------+------+------+---------+
| id | name | gender | salary | age | gmr | dept_id |
+----+----------+--------+--------+------+------+---------+
| 3 | lucy | m | 500.70 | 18 | 2 | 1002 |
| 1 | lisan | f | 100.00 | 20 | 4 | 1001 |
| 4 | 李雷 | 男 | 670.00 | 20 | 4 | 1003 |
| 5 | WangYong | m | 700.90 | 25 | 5 | NULL |
| 2 | Will | f | 130.00 | 28 | 3 | 1001 |
+----+----------+--------+--------+------+------+---------+5.6 与WITH ROLLUP一起使用
WITH ROLLUP将会在坐在记录的最后加一条记录，该记录是上面所有记录的总和。
//最后一条是总和
select age,count(*) from employee group by age WITH ROLLUP;
+------+----------+
| age | count(*) |
+------+----------+
| 18 | 1 |
| 20 | 2 |
| 25 | 2 |
| 28 | 1 |
| NULL | 6 |
+------+----------+6 限制查询量
6.1 不指定初始位置
记录的条数从第一条开始显示。
limit [num]//只显示前两条
select * from employee limit 2；6.2 指定初始位置
从指定位置开始显示，第一条记录为0.
limit [start],[limitNUm]//指定初始，显示第二到第四条
select * from employee limit 1,3;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
