1.distinct关键字可使得表中的记录去掉重复后显示出来:
mysql> select * from emp; +------+--------+-------+------------+-----------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+-----------+--------+
| NULL | zzx1 | NULL | 2000-01-01 | 200000.00 | 1 |
| NULL | lisa | NULL | 2003-02-01 | 4000.00 | 2 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 3 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 | +------+--------+-------+------------+-----------+--------+
4 rows in set (0.00 sec)
mysql> select distinct deptno from emp; +--------+
| deptno | +--------+
| 1 |
| 2 |
| 3 | +--------+
3 rows in set (0.06 sec)用where实现限定条件查询:
mysql> select * from emp where deptno = 1; +------+--------+-------+------------+-----------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+-----------+--------+
| NULL | zzx1 | NULL | 2000-01-01 | 200000.00 | 1 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 | +------+--------+-------+------------+-----------+--------+
2 rows in set (0.08 sec)ORDER BY实现数据库排序操作，语法:
SELECT * FROM tablename [WHERE CONDITION][ORDER BY field1[DESC|ASC]field2[DESC|ASC],……fieldn[DESC|ASC]]
DESC指按降序排列，ASC为按升序排列，如将emp中的sal由低至高进行排序:
mysql> select * from emp order by sal; +------+--------+-------+------------+-----------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+-----------+--------+
| NULL | lisa | NULL | 2003-02-01 | 4000.00 | 2 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 3 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 |
| NULL | zzx1 | NULL | 2000-01-01 | 200000.00 | 1 | +------+--------+-------+------------+-----------+--------+
4 rows in set (0.00 sec)对于deptno相同的前两条记录，按照工资由高到低排序:
mysql> select * from emp order by deptno,sal desc; +------+--------+-------+------------+-----------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+-----------+--------+
| NULL | zzx1 | NULL | 2000-01-01 | 200000.00 | 1 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 |
| NULL | lisa | NULL | 2003-02-01 | 4000.00 | 2 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 3 | +------+--------+-------+------------+-----------+--------+
4 rows in set (0.00 sec)对于记录如果只想显示一部分的话，用LIMIT实现，语法:SELECT …… [LIMIT offset_start,row_count]
offset_start表示起始偏移量，row_count表示显示的行数，显示按sal排序的前3条记录:
mysql> select * from emp order by sal limit 3; +------+--------+-------+------------+---------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+---------+--------+
| NULL | lisa | NULL | 2003-02-01 | 4000.00 | 2 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 3 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 | +------+--------+-------+------------+---------+--------+
3 rows in set (0.00 sec)要从第二条记录开始显示的话:
mysql> select * from emp order by sal limit 1,3; +------+--------+-------+------------+-----------+--------+
| age1 | ename | birth | hiredate | sal | deptno | +------+--------+-------+------------+-----------+--------+
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 3 |
| NULL | bjguan | NULL | 2004-04-02 | 5000.00 | 1 |
| NULL | zzx1 | NULL | 2000-01-01 | 200000.00 | 1 | +------+--------+-------+------------+-----------+--------+
3 rows in set (0.00 sec)2.对SQL进行聚合操作的语法:
SELECT[field1,field2,……fieldn]fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1,field2,……fieldn
[WITH POLLUP]
[HAVING where_condition]
fun_name为聚合函数，如sum,count(*)(记录函数),max,min.
GROUP BY 表示要进行分类聚合的字段
WITH ROLLUP 是否对分类聚合后的结果再汇总.
HAVING 对分类后的结果进行条件的过滤(having为对聚合后的结果进行过滤，where为对聚合前的结果进行过滤)
统计emp表中的人数进行统计:
mysql> select count(1) from emp; +----------+
| count(1) | +----------+
| 4 | +----------+
1 row in set (0.10 sec)统计各个部门人数:
mysql> select deptno,count(1) from emp group by deptno; +--------+----------+
| deptno | count(1) | +--------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 | +--------+----------+
3 rows in set (0.06 sec)既统计各部门人数，又统计总人数:
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 (0.00 sec)统计人数大于1的部门:
mysql> select deptno,count(1) from emp group by deptno having count(1)>1; +--------+----------+
| deptno | count(1) | +--------+----------+
| 1 | 2 | +--------+----------+
1 row in set (0.07 sec)统计所有员工sal总额，最高和最低sal:
mysql> select sum(sal),max(sal),min(sal) from emp; +-----------+-----------+----------+
| sum(sal) | max(sal) | min(sal) | +-----------+-----------+----------+
| 214000.00 | 200000.00 | 4000.00 | +-----------+-----------+----------+
1 row in set (0.03 sec)将emp和dept进行内连接，查出雇员的名字和所在的部门:
mysql> select * from dept; +--------+----------+
| deptno | deptname | +--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr | +--------+----------+
3 rows in set (0.00 sec)
mysql> select ename,deptname from emp,dept where emp.deptno = dept.deptno; +--------+----------+
| ename | deptname | +--------+----------+
| zzx1 | tech |
| lisa | sale |
| bjguan | hr |
| bjguan | tech | +--------+----------+
4 rows in set (0.10 sec)