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)
以上是 mysql常用语句3 的全部内容， 来源链接： utcz.com/z/536083.html
