数据库day2_partTwo

1.字段控制查询

distinct: 去除重复记录
as: 起别名，用法 ：select 字段 as 别名

演示；
a).去除重复记录

mysql> select id from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 1    |
+------+
6 rows in set (0.00 sec)

mysql> select distinct id from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
+------+
5 rows in set (0.01 sec)

b).给列名起别名

mysql> select name,gender  from student;
+----------+--------+
| name     | gender |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)

mysql> select name as 姓名,gender as 性别  from student;
+----------+--------+
| 姓名     | 性别   |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)

2.排序

order by:指定数据返回的顺序

​ asc：ascending,升序

​ desc: descending，降序

用法：select * from 表 order by xxx

演示：
a).查询所有的记录，按照年龄升序排序

mysql> select * from student order by age asc;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 5    | eee      |   20 | female |
| 1    | ffff     |   30 | male   |
+------+----------+------+--------+
6 rows in set (0.00 sec)

b).查询所有学生记录，按照年龄降序排序，如果年龄相等，则按照编号进行升序排序

mysql> select * from student order by age desc,id asc; 
+------+----------+------+--------+ 
| id   | name     | age  | gender | 
+------+----------+------+--------+ 
| 1    | ffff     |   30 | male   | 
| 2    | bbbbbbbb |   20 | male   | 
| 5    | eee      |   20 | female | 
| 1    | aaaa     |   19 | female | 
| 4    | ddd      |   16 | female | 
| 3    | cc       |   15 | male   | 
+------+----------+------+--------+ 
6 rows in set (0.00 sec) 

3.聚合函数

聚合函数主要用来做纵向运算

a).count():统计指定列不为null的记录行数

查询年龄大于20的人数

mysql> select count(*)  from student where age>20;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

b).sum():计算指定列的数值和

查询所有学生的年龄和

mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      120 |
+----------+
1 row in set (0.01 sec)

查询所有学生的年龄和，以及所有学生的编号

mysql> select sum(age),sum(id) from student;
+----------+---------+
| sum(age) | sum(id) |
+----------+---------+
|      120 |      16 |
+----------+---------+
1 row in set (0.00 sec)

c).求指定列中的最大值和最小值
max():
min():
求最大年龄和最小年龄

mysql> select max(age),min(age) from student;
+----------+----------+
| max(age) | min(age) |
+----------+----------+
|       30 |       15 |
+----------+----------+
1 row in set (0.00 sec)

d).avg()
average:平均数

查询所有学生的平均年龄

mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  20.0000 |
+----------+
1 row in set (0.00 sec)

4).分组查询

group by：分组查询

having：有…,表示条件，类似于where的用法

演示：

#在当前数据库下创建新的表
mysql> create table emp(			
 -> empno int primary key,
 -> enname varchar(20),
 -> job varchar(20),
 -> mgr int,
 -> hiredate date,
 -> sal double,
 -> comm double,
 -> deptno int
 -> );

#1.查询各个部门的人数
mysql> select count(*) from emp group by deptno;
+----------+
| count(*) |
+----------+
|        2 |
|        2 |
|        4 |
+----------+
3 rows in set (0.00 sec)

#2.查询每个部门的部门编号和每个部门的工资和
mysql> select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     20 |  3800.00 |
|     30 |  8675.00 |
+--------+----------+
3 rows in set (0.00 sec)


#3.查询每个部门的部门编号和每个部门的人数
mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        2 |
|     30 |        4 |
+--------+----------+
3 rows in set (0.00 sec)


#4.查询每个部门的部门编号和每个部门工资大于1500的人数
mysql> select deptno,count(*) from emp where sal>1500 group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        1 |
|     30 |        3 |
+--------+----------+
3 rows in set (0.01 sec)


#5.查询工资总和大于7000的部门编号以及工资和
mysql> select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  8675.00 |
+--------+----------+
2 rows in set (0.00 sec)

总结：

​ having和where的区别

​ a.二者都表示对数据执行条件

​ b.having是在分组之后对数据进行过滤

​ where是在分组之前对数据进行过滤

​ c.having后面可以使用聚合函数

​ where后面不可以使用聚合函数

#查询工资大于1500，工资总和大于6000的部门编号和工资和
mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  7425.00 |
+--------+----------+
2 rows in set (0.00 sec)

5. 分页查询

limit：用来限定查询的起始行，以及总行数
演示：

#1.查询4行记录，起始行从0开始
mysql> select * from emp limit 0,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | smith  | clark    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from emp limit 2,3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.01 sec)

总结：

​ 查询语句书写顺序：select----》from—》where—》group by-----》having-----》order by----->limit

​ 查询语句执行顺序：from----》where-----》group by----》having----》order by ----》select-----》limit