1、简单数据记录查询
【语法】
select 字段名 from 表名;
(1)查询所有字段数据
mysql> select empno,ename,job,MSG,Hiredate,sal,comm,deptno from t_employee;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
+-------+--------+-------+------+------------+--------+------+--------+
4 rows in set (0.11 sec)
mysql> select * from t_employee;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
+-------+--------+-------+------+------------+--------+------+--------+
4 rows in set (0.00 sec)
(2)查询指定字段数据
mysql> select ename,comm from t_employee;
+--------+------+
| ename | comm |
+--------+------+
| SMITH | NULL |
| ename1 | 1.00 |
| ename2 | 2.00 |
| ename3 | 3.00 |
+--------+------+
4 rows in set (0.00 sec)
(3)避免重复数据查询-distinct
mysql> select distinct ename from t_employee;
+--------+
| ename |
+--------+
| SMITH |
| ename1 |
| ename2 |
| ename3 |
+--------+
4 rows in set (0.08 sec)
(4)实现数学四则运算数据查询
mysql> select ename,sal*12 from t_employee;
+--------+---------+
| ename | sal*12 |
+--------+---------+
| SMITH | 9600.00 |
| ename1 | 12.00 |
| ename2 | 24.00 |
| ename3 | 36.00 |
| ename1 | 24.00 |
+--------+---------+
5 rows in set (0.04 sec)
#使用as给字段sal * 12重命名为yearsalary显示
mysql> select ename,sal * 12 as yearsalary from t_employee;
+--------+------------+
| ename | yearsalary |
+--------+------------+
| SMITH | 9600.00 |
| ename1 | 12.00 |
| ename2 | 24.00 |
| ename3 | 36.00 |
| ename1 | 24.00 |
+--------+------------+
5 rows in set (0.00 sec)
(5)设置显示格式数据查询
mysql> select concat(ename,'’s annual salary is :',sal * 12) yearsalary from t_employee;
+-------------------------------------+
| yearsalary |
+-------------------------------------+
| SMITH’s annual salary is :9600.00 |
| ename1’s annual salary is :12.00 |
| ename2’s annual salary is :24.00 |
| ename3’s annual salary is :36.00 |
| ename1’s annual salary is :24.00 |
+-------------------------------------+
5 rows in set (0.00 sec)
2、条件数据记录查询
【语法】
select 字段名 from 表名 where 条件语句;
(1)带关系运算符和逻辑运算符的条件数据查询
- 单条件数据查询
mysql> select ename from t_employee where job='job2';
+--------+
| ename |
+--------+
| ename2 |
+--------+
1 row in set (0.00 sec)
- 多条件数据查询
mysql> select ename from t_employee where job='job1'&&sal>1;
+--------+
| ename |
+--------+
| ename1 |
+--------+
1 row in set (0.00 sec)
(2)带between and关键字的范围查询
【语法】
select 字段名 from 表名 where between value1 and value2;
- 符合范围的数据记录查询
mysql> select ename from t_employee where sal between 2 and 3;
+--------+
| ename |
+--------+
| ename2 |
| ename3 |
| ename1 |
+--------+
3 rows in set (0.00 sec)
- 不符合范围的数据记录查询
mysql> select ename from t_employee where sal not between 2 and 3;
+--------+
| ename |
+--------+
| SMITH |
| ename1 |
+--------+
2 rows in set (0.00 sec)
(3)带is null 关键字的空值查询
【语法】
select 字段名 from 表名 where 字段名 is null;
- 空值数据记录查询
mysql> select ename from t_employee where comm is null;
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)
- 不是空值数据记录查询
mysql> select ename from t_employee where not comm is null;
+--------+
| ename |
+--------+
| ename1 |
| ename2 |
| ename3 |
| ename1 |
+--------+
4 rows in set (0.00 sec)
(4)带in关键字的集合查询
【语法】
select 字段名 from 表名 where field in (value1,value2……,valuen);
- 在集合中数据记录查询
mysql> select ename from t_employee where sal in (2,7,8);
+--------+
| ename |
+--------+
| ename2 |
| ename1 |
+--------+
2 rows in set (0.00 sec)
mysql> select ename from t_employee where sal=2 or sal=7 or sal=8;
+--------+
| ename |
+--------+
| ename2 |
| ename1 |
+--------+
2 rows in set (0.00 sec)
- 不在集合中数据记录查询
mysql> select ename from t_employee where not sal in (2,7,8);
+--------+
| ename |
+--------+
| SMITH |
| ename1 |
| ename3 |
+--------+
3 rows in set (0.01 sec)
mysql> select ename from t_employee where sal not in (2,7,8);
+--------+
| ename |
+--------+
| SMITH |
| ename1 |
| ename3 |
+--------+
3 rows in set (0.00 sec)
- 关于集合查询注意点
#使用关键字in时,查询集合中存在null,不影响查询
mysql> select ename from t_employee where sal in (2,7,8,null);
+--------+
| ename |
+--------+
| ename2 |
| ename1 |
+--------+
2 rows in set (0.00 sec)
#使用关键字not in时,查询集合中存在null,不会有任何查询结果
mysql> select ename from t_employee where sal not in (2,7,8,null);
Empty set (0.00 sec)
(5)带like关键字的模糊查询
【语法】
select 字段名 from 表名 where field like value;
- 带有”%“通配符的查询(该通配符能够匹配任意长度的字符串)
mysql> select ename from t_employee where ename like 'e%';
+--------+
| ename |
+--------+
| ename1 |
| ename2 |
| ename3 |
| ename1 |
+--------+
4 rows in set (0.00 sec)
mysql> select ename from t_employee where ename not like 'e%';
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.18 sec)
- 带有”_“通配符的查询(该通配符能匹配单个字符)
mysql> select ename from t_employee where ename like '_n%';
+--------+
| ename |
+--------+
| ename1 |
| ename2 |
| ename3 |
| ename1 |
+--------+
4 rows in set (0.00 sec)
mysql> select ename from t_employee where ename not like '_n%';
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)
(5)排序数据记录查询
【语法】
select 字段名 from 表名 where 条件语句 order by 字段 [asc|desc];
- 按照单子段排序
#升序排序
mysql> select * from t_employee order by sal;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 1 | ename1 | job1 | 3 | 2019-03-12 | 2.00 | 3.00 | 3 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+--------+-------+------+------------+--------+------+--------+
5 rows in set (0.06 sec)
mysql> select * from t_employee order by sal asc;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 1 | ename1 | job1 | 3 | 2019-03-12 | 2.00 | 3.00 | 3 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+--------+-------+------+------------+--------+------+--------+
5 rows in set (0.00 sec)
#降序排序
mysql> select * from t_employee order by sal desc;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 1 | ename1 | job1 | 3 | 2019-03-12 | 2.00 | 3.00 | 3 |
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
+-------+--------+-------+------+------------+--------+------+--------+
5 rows in set (0.00 sec)
- 按照多字段排序
mysql> select * from t_employee order by sal,MSG desc;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 1 | ename1 | job1 | 3 | 2019-03-12 | 2.00 | 3.00 | 3 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+--------+-------+------+------------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from t_employee order by sal asc ,MSG desc;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 1 | ename1 | job1 | 3 | 2019-03-12 | 2.00 | 3.00 | 3 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 2.00 | 2 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+--------+-------+------+------------+--------+------+--------+
5 rows in set (0.00 sec)
- 限制数据记录查询数量
【语法】
select 字段名 from 表名 where 条件语句 limit offset_start,row_count;
(1)不指定初始位置
mysql> select * from t_employee where comm is null limit 2;
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.01 sec)
(2)指定初始位置
mysql> select * from t_employee where comm is null order by MSG limit 0,5;
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
| 73 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 69 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
3 rows in set (0.06 sec)
mysql> select * from t_employee where comm is null order by MSG limit 5,10;
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
| 73 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 73 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 69 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
| 73 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
5 rows in set (0.00 sec)
4、统计函数和分组数据记录查询
【语法】
select 统计函数(字段) from 表名 where 条件语句;
【统计函数】
- count()函数:统计表中记录的条数 avg()函数:计算字段值的平均值
- sum()函数:计算字段值的总和
- max()函数:查询字段值的最大值
- min()函数:查询字段值的最小值
- 如果所操作的表里没有任何数据,则count()函数返回数据0,其他函数则返回null
(1)mysql支持的统计函数
- 统计数据记录条数
mysql> select count(*) number from t_employee;
+--------+
| number |
+--------+
| 14 |
+--------+
1 row in set (0.00 sec)
mysql> select count(comm) number from t_employee;
+--------+
| number |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
mysql> select count(comm) number from t_employee where not comm=0;
+--------+
| number |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
- 统计计算平均值
mysql> select avg(comm) average from t_employee where not comm=0;
+----------+
| average |
+----------+
| 2.333333 |
+----------+
1 row in set (0.00 sec)
- 统计计算求和
mysql> select sum(comm) sumvalue from t_employee where not comm=0;
+----------+
| sumvalue |
+----------+
| 7.00 |
+----------+
1 row in set (0.00 sec)
- 统计计算最大值和最小值
mysql> select max(sal) maxval,min(sal) minval from t_employee;
+--------+--------+
| maxval | minval |
+--------+--------+
| 800.00 | 1.00 |
+--------+--------+
1 row in set (0.01 sec)
(2)分组数据查询
- 简单分组查询(分组中显示的数据记录为随机的)
【语法】
select 统计函数 from 表名 where 条件语句 group by 字段名;
【实例】
mysql> select * from t_employee group by deptno;
+-------+--------+-------+------+------------+--------+------+--------+
| empno | ename | job | MSG | Hiredate | sal | comm | deptno |
+-------+--------+-------+------+------------+--------+------+--------+
| 1 | ename1 | job1 | 1 | 2019-03-12 | 1.00 | 1.00 | 1 |
| 2 | ename2 | job2 | 2 | 2019-03-12 | 2.00 | 0.00 | 2 |
| 3 | ename3 | job3 | 3 | 2019-03-12 | 3.00 | 3.00 | 3 |
| 73 | SMITH | CLERK | 7902 | 2019-03-12 | 800.00 | 0.00 | 20 |
+-------+--------+-------+------+------------+--------+------+--------+
4 rows in set (0.01 sec)
- 实现统计功能分组查询
【语法】
select group_concat(字段名) from 表名 where 条件语句 group by 字段名;
【实例】
mysql> select deptno,group_concat(ename) enames,count(ename) number
-> from t_employee group by deptno;
+--------+-------------------------------------------------------+--------+
| deptno | enames | number |
+--------+-------------------------------------------------------+--------+
| 1 | ename1 | 1 |
| 2 | ename2 | 1 |
| 3 | ename3,ename1 | 2 |
| 20 | SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH | 9 |
+--------+-------------------------------------------------------+--------+
4 rows in set (0.06 sec)
- 实现多个字段分组查询
【语法】
select group_concat(field),function(field) from 表名 where 条件语句 group by field1……fieldn;
【实例】
mysql> select deptno,MSG,group_concat(ename) enames,count(ename) number
-> from t_employee group by deptno,MSG;
+--------+------+-------------------------------------------------------+--------+
| deptno | MSG | enames | number |
+--------+------+-------------------------------------------------------+--------+
| 1 | 1 | ename1 | 1 |
| 2 | 2 | ename2 | 1 |
| 3 | 3 | ename3,ename1 | 2 |
| 20 | 7902 | SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH | 9 |
+--------+------+-------------------------------------------------------+--------+
4 rows in set (0.00 sec)
- 实现having子句限定分组查询
【语法】
select function(field) from 表名 where 条件语句 group by field1……fieldn having 条件语句;
【实例】
mysql> select deptno,avg(sal) average,group_concat(ename) enames,count(ename) number
-> from t_employee
-> group by deptno
-> having avg(sal) > 3;
+--------+------------+-------------------------------------------------------+--------+
| deptno | average | enames | number |
+--------+------------+-------------------------------------------------------+--------+
| 20 | 800.000000 | SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH,SMITH | 9 |
+--------+------------+-------------------------------------------------------+--------+
1 row in set (0.09 sec)