mysql常用语句 3

1.找出每个部门平均薪水的薪资等级，from后面嵌套子查询
第一步先求出每个部门的平均薪水

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步把第一步的结果当做一个表，再和salgrade结合查询
mysql> select s.grade ,t.* from ( select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-------+--------+-------------+
| grade | deptno | avgsal |
+-------+--------+-------------+
| 3 | 30 | 1566.666667 |
| 4 | 10 | 2916.666667 |
| 4 | 20 | 2175.000000 |
+-------+--------+-------------+
3 rows in set (0.00 sec)

2.找出每个部门薪资等级的平均值

第一步先找每个部门的员工薪水等级
mysql> select s.grade,e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal;
+-------+--------+--------+---------+
| grade | ename | deptno | sal |
+-------+--------+--------+---------+
| 1 | SMITH | 20 | 800.00 |
| 3 | ALLEN | 30 | 1600.00 |
| 2 | WARD | 30 | 1250.00 |
| 4 | JONES | 20 | 2975.00 |
| 2 | MARTIN | 30 | 1250.00 |
| 4 | BLAKE | 30 | 2850.00 |
| 4 | CLARK | 10 | 2450.00 |
| 4 | SCOTT | 20 | 3000.00 |
| 5 | KING | 10 | 5000.00 |
| 3 | TURNER | 30 | 1500.00 |
| 1 | ADAMS | 20 | 1100.00 |
| 1 | JAMES | 30 | 950.00 |
| 4 | FORD | 20 | 3000.00 |
| 2 | MILLER | 10 | 1300.00 |
+-------+--------+--------+---------+
14 rows in set (0.00 sec)

第二步求平均值
mysql> select t.deptno,avg(t.grade) from (select s.grade,e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal) t join salgrade s group by t.deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

或者
mysql> select s.grade,avg(s.grade),e.ename,e.deptno,e.sal from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
+-------+--------------+-------+--------+---------+
| grade | avg(s.grade) | ename | deptno | sal |
+-------+--------------+-------+--------+---------+
| 4 | 3.6667 | CLARK | 10 | 2450.00 |
| 1 | 2.8000 | SMITH | 20 | 800.00 |
| 3 | 2.5000 | ALLEN | 30 | 1600.00 |
+-------+--------------+-------+--------+---------+
3 rows in set (0.00 sec)

3.找出每个员工所在的部门名称，要求显示员工名和部门名。(使用嵌套查询)

mysql> select e.ename ,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

嵌套查询可以使用在select，from，where后面。

4.union(将结果集相加)，找出工作岗位是salesman,manager的员工
mysql> select ename,job from emp where job = \'manager\' union select ename,job from emp where job = \'salesman\';
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

mysql> select ename,job from emp where job = \'manager\';
+-------+---------+
| ename | job |
+-------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
3 rows in set (0.01 sec)

mysql> select ename,job from emp where job = \'salesman\';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
4 rows in set (0.00 sec)

使用union要求两张表列数量必须一致。

5.limit 0(startIndex),8(length)

mysql> select ename from emp limit 0,8;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
+--------+
8 rows in set (0.00 sec)

运行顺序
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7

mysql> select ename from emp limit 2,5;
+--------+
| ename |
+--------+
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
+--------+
5 rows in set (0.00 sec)

mysql> select ename from emp limit 5;默认前面下标为0
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
+--------+
5 rows in set (0.00 sec)

6.找出工资排名在第四到第七的员工
mysql> select ename,sal from emp order by sal desc limit 3,3;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
+-------+---------+
3 rows in set (0.00 sec)

7.创建一个学生表：
create table t_student(
stu_name varchar(10),
stu_num varchar(10),
stu_teacher varchar(10),
stu_house varchar(10)

);

mysql> create table t_student(
-> stu_name varchar(10),
-> stu_num varchar(10),
-> stu_teacher varchar(10),
-> stu_house varchar(10)
->
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_cqust_db |
+--------------------+
| dept |
| emp |
| salgrade |
| t_student |
+--------------------+
4 rows in set (0.00 sec)

8.向表中插入数据。
insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
values (\'hch\',\'2019465335\',\'laoyu\',\'3210\');

mysql> insert into t_student (stu_name,stu_num,stu_teacher,stu_house)//可以省略前面字段括号，后面必须和表一一对应。
-> values (\'hch\',\'2019465335\',\'laoyu\',\'3210\');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+----------+------------+-------------+-----------+
| stu_name | stu_num | stu_teacher | stu_house |
+----------+------------+-------------+-----------+
| hch | 2019465335 | laoyu | 3210 |
+----------+------------+-------------+-----------+
1 row in set (0.00 sec)

9.插入多条数据。
insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
values (\'qwe\',\'2019456123\',\'laoyu\',\'3211\'),(\'asd\',\'2019123456\',\'laoyu\',\'3122\');
mysql> insert into t_student (stu_name,stu_num,stu_teacher,stu_house)
-> values (\'qwe\',\'2019456123\',\'laoyu\',\'3211\'),(\'asd\',\'2019123456\',\'laoyu\',\'3122\');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t_student;
+----------+------------+-------------+-----------+
| stu_name | stu_num | stu_teacher | stu_house |
+----------+------------+-------------+-----------+
| qwe | 2019456123 | laoyu | 3211 |
| asd | 2019123456 | laoyu | 3122 |
+----------+------------+-------------+-----------+
2 rows in set (0.00 sec)

10.表的复制
mysql> create table emp1 as select ename,sal from emp;
Query OK, 14 rows affected (0.03 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> show tables;
+--------------------+
| Tables_in_cqust_db |
+--------------------+
| dept |
| emp |
| emp1 |
| salgrade |
| t_student |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from emp1;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

11.将查询的结果插入到表中。
mysql> insert into emp1 select * from emp1;
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> select * from emp1;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
28 rows in set (0.00 sec)

来源：https://www.cnblogs.com/journeyhch/p/15563250.html
图文来源于网络，如有侵权请联系删除。