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)

Original: https://www.cnblogs.com/journeyhch/p/15563250.html
Author: journeyhch
Title: mysql常用语句 3