查询不重复记录
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 4 |
+--------+
3 rows in set
条件查询,where后面还可以使用>,=,等比较运算符,使用or,and多条件查询
如:查询deptno为1的记录
mysql> select * from emp where deptno=1;
+--------+------------+--------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+--------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| bjguan | 2004-04-02 | 300.00 | 1 |
+--------+------------+--------+--------+
2 rows in set
mysql> select * from emp where deptno=1 and sal
;
+-------+------------+--------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+--------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
+-------+------------+--------+--------+
1 row in set
排序和限制
desc降序,asc升序,默认为升序,order by后面可以有多个不同的排序字段,如果排序字段值一样,则按照第二个排序字段进行排序...
mysql> select * from emp order by sal;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
+--------+------------+---------+--------+
4 rows in set
如果只有一个排序字段,则字段相同的记录将会无序排列
mysql> select * from emp order by deptno;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
先将deptno升序排列,再将sal降序排列
mysql> select * from emp order by deptno,sal desc;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 | 1 |
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
对于排序后的字段,可使用limit显示一部分(limit在其它数据库不能用)
显示前3条记录
mysql> select * from emp order by sal limit 3;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
+--------+------------+---------+--------+
3 rows in set
从第2条记录开始,显示3条记录(起始偏移量为0)
mysql> select * from emp order by sal limit 1,3;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
+--------+------------+---------+--------+
3 rows in set
聚合
常用的聚合函数有sum求和,count(*)记录数,max最大值,min最小值
从emp表统计人数
mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set
统计各部门(deptno)人数,group by表示要进行分类聚合的字段
mysql> select deptno,count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
+--------+----------+
3 rows in set
统计各部门(deptno)人数,并统计总人数,with rollup表示对分类聚合后的结果进行再汇总
mysql> select deptno,count(1) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| NULL | 4 |
+--------+----------+
4 rows in set
统计人数大于1的部门,having表示对分类后的结果再进行条件过滤
mysql> select deptno,count(1) from emp group by deptno having count(1)>1;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
+--------+----------+
1 row in set