MySql常用命令总结
PS:感谢Dean Xu的第一个Star,值得纪念哈。
SQL脚本下载地址:欢迎star
- 1、使用SHOW语句找出在服务器上当前存在什么数据库:
mysql> SHOW DATABASES;
- 2、创建一个数据库MYSQLDATA
mysql> CREATE DATABASE MYSQLDATA;
- 3、选择你所创建的数据库
mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)
- 4、查看现在的数据库中存在什么表
mysql> SHOW TABLES;
- 5、创建一个数据库表
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
- 6、显示表的结构:
mysql> DESCRIBE MYTABLE;
- 7、往表中加入记录
mysql> insert into MYTABLE values (”hyq”,”M”);
- 8、用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;
- 9、导入.sql文件命令(例如D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
- 10、删除表
mysql>drop TABLE MYTABLE;
- 11、清空表
mysql>delete from MYTABLE;
- 12、更新表中数据
mysql>update MYTABLE set sex=”f” where name=’hyq’;
匿名帐户删除、 root帐户设置密码:
use mysql;
delete from User where User=”";
update User set Password=PASSWORD(’newpassword’) where User=’root’;
GRANT的常用用法如下:
grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;
全局管理权限:
- FILE: 在MySQL服务器上读写文件。
- PROCESS: 显示或杀死属于其它用户的服务线程。
- RELOAD: 重载访问控制表,刷新日志等。
- SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限:
- ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
- CREATE: 建立新的数据库或数据表。
- DELETE: 删除表的记录。
- DROP: 删除数据表或数据库。
- INDEX: 建立或删除索引。
- INSERT: 增加表的记录。
- SELECT: 显示/搜索表的记录。
- UPDATE: 修改表中已存在的记录。
特别的权限:
- ALL: 允许做任何事(和root一样)。
- USAGE: 只允许登录–其它什么也不允许做。
MySQL-Practice-Questions
1、取得每个部门最高薪水的人员名称
- 第一步:取得每个部门最高薪水『按照部门分组求最大值』
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
deptno | maxsal |
---|---|
10 | 5000.00 |
20 | 3000.00 |
30 | 2850.00 |
- 第二步:将上面的查询结果当作临时表t,t表和emp e表进行连接
条件:e.deptno=t.deptno and e.sal=t.sal
mysql> select
-> e.ename t.*
-> from
-> emp e
-> join
-> (select deptno,max(sal) as maxsal from emp group by deptno) t
-> on
-> e.deptno=t.deptno and e.sal = t.maxsal;
ename | deptno | maxsal |
---|---|---|
BLAKE | 30 | 2850.00 |
SCOTT | 20 | 3000.00 |
KING | 10 | 5000.00 |
FORD | 20 | 3000.00 |
2、哪些人的薪水在部门的平均薪水之上
- 第一步:找出部门的平均薪水『按部门编号分组求平均薪水』
select deptno,avg(sal) as avgsal from emp group by deptno;
deptno | avgsal |
---|---|
10 | 2916.666667 |
20 | 2175.000000 |
30 | 1566.666667 |
- 第二步:将上面的查询结果当作临时表t,与emp e表进行连接
条件:t.deptno=t.deptno and e.sal > t.avgsal
select
e.ename,e.sal,t.*
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
e.deptno=t.deptno and e.sal > t.avgsal;
ename | sal | deptno | avgsal |
---|---|---|---|
ALLEN | 1600.00 | 30 | 1566.666667 |
JONES | 2975.00 | 20 | 2175.000000 |
BLAKE | 2850.00 | 30 | 1566.666667 |
SCOTT | 3000.00 | 20 | 2175.000000 |
KING | 5000.00 | 10 | 2916.666667 |
FORD | 3000.00 | 20 | 2175.000000 |
3、1取得部门中(所有人)平均薪水的等级
- 第一步:取得部门中的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
deptno | avgsal |
---|---|
10 | 2916.666667 |
20 | 2175.000000 |
30 | 1566.666667 |
- 第二部:将上面的查询结果当作临时表t,t表和salgrade s表进行关联
条件:e.sal between s.losal and s.hisal
select
t.*,s.grade
from
salgrade s
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
on
t.avgsal between s.losal and s.hisal;
deptno | avgsal | grade |
---|---|---|
10 | 2916.666667 | 4 |
20 | 2175.000000 | 4 |
30 | 1566.666667 | 3 |
3、2取得部门中(所有人)薪水的平均等级
PS:感谢westmelon提出错误,并给出了解决方案。如下:
select
t.deptno,avg(t.grade) as avggrade
from
(select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal ) t group by t.deptno
- 第一步:每个员工的薪水等级(oder by 以部门编号排序,为了好理解)
select
e.ename,e.sal,e.deptno,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal ;
ename | sal | deptno | grade |
---|---|---|---|
MILLER | 1300.00 | 10 | 2 |
KING | 5000.00 | 10 | 5 |
CLARK | 2450.00 | 10 | 4 |
ADAMS | 1100.00 | 20 | 1 |
SCOTT | 3000.00 | 20 | 4 |
FORD | 3000.00 | 20 | 4 |
JONES | 2975.00 | 20 | 4 |
SMITH | 800.00 | 20 | 1 |
MARTIN | 1250.00 | 30 | 2 |
ALLEN | 1600.00 | 30 | 3 |
JAMES | 950.00 | 30 | 1 |
BLAKE | 2850.00 | 30 | 4 |
WARD | 1250.00 | 30 | 2 |
TURNER | 1500.00 | 30 | 3 |
- 第二步:在以上基础上继续以部门编号分组,求平均薪水等级
select
e.deptno,avg(s.grade) as avggrade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
deptno | avggrade |
---|---|
10 | 3.6667 |
20 | 2.8000 |
30 | 2.5000 |
4、不用组函数(MAX),取得最高薪水(给出两种解决方案)
- 方案一:按照薪水降序排,取得第一个
mysql> select sal from emp order by sal desc limit 1;
- 方案二:自连接
mysql>mysql> select sal from emp where sal not in(select a.sal from emp a join emp b on a.sal < b.sal);
sal |
---|
5000.00 |
5、取得平均薪水最高的部门的编号(至少给出两种解决方案)
- 第一种方案:平均薪水降序排取第一个
第一步:取得每个部门的平均薪水
mysql> select deptno,avg(sal) avgsal from emp group by deptno;
deptno | avgsal |
---|---|
10 | 2916.666667 |
20 | 2175.000000 |
30 | 1566.666667 |
第二步:取得平均薪水的最大值
mysql> select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
avgsal |
---|
2916.666667 |
第三步:将第一步和第二步结合
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avg(sal)=( select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);
deptno | avgsal |
---|---|
10 | 2916.666667 |
- 第二种方案:MAX函数
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avg(sal)=( select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);
deptno | avgsal |
---|---|
10 | 2916.666667 |
6、取得平均薪水最高的部门的部门名称
select
d.dname,avg(e.sal) as avgsal
from
emp e
join
dept d
on e.deptno=d.deptno
group by
d.dname
having
avg(e.sal)=( select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);
dname | avgsal |
---|---|
ACCOUNTING | 2916.666667 |
7、求平均薪水的等级最高的部门的部门名称
第一步:求各个部门平均薪水的等级
select
t.dname,t.avgsal,s.grade
from
(select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
dname | avgsal | grade |
---|---|---|
ACCOUNTING | 2916.666667 | 4 |
RESEARCH | 2175.000000 | 4 |
SALES | 1566.666667 | 3 |
第二步:获得最高等级
select
max(s.grade)
from
(select avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
第三步:将第一步和第二步联合
select
t.dname,t.avgsal,s.grade
from
(select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal
where
s.grade=(select
max(s.grade)
from
(select avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal);
dname | avgsal | grade |
---|---|---|
ACCOUNTING | 2916.666667 | 4 |
RESEARCH | 2175.000000 | 4 |
8、取得比普通员工的最高薪水还要高的领导人姓名
第一步:取得普通员工
select * from emp where empno not in (select distinct mgr from emp);
以上语句无法查村到结果,因为not in 不会自动忽略NULL,需要自己手动排除NULL。 in 自动忽略NULL
select * from emp where empno not in (select distinct mgr from emp where mgr is not null);
empno | ename | job | mgr | hiredate | sal | comm | deptno |
---|---|---|---|---|---|---|---|
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
第二步:找出员工最高薪水的人
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
max(sal) |
---|
1600.00 |
第三步:找出薪水大于1600即可
select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
ename | sal |
---|---|
JONES | 2975.00 |
BLAKE | 2850.00 |
CLARK | 2450.00 |
SCOTT | 3000.00 |
KING | 5000.00 |
FORD | 3000.00 |
补充:case ... when ... then ... when ... then ... else ... end 类似于Java中的switch..case
select
ename,sal,(case job when 'MANAGER' then sal*1.1 when 'CLERK' then sal*1.5 end) as newsal
from
emp;
ename | sal | newsal |
---|---|---|
SMITH | 800.00 | 1200.00 |
ALLEN | 1600.00 | NULL |
WARD | 1250.00 | NULL |
JONES | 2975.00 | 3272.50 |
MARTIN | 1250.00 | NULL |
BLAKE | 2850.00 | 3135.00 |
CLARK | 2450.00 | 2695.00 |
SCOTT | 3000.00 | NULL |
KING | 5000.00 | NULL |
TURNER | 1500.00 | NULL |
ADAMS | 1100.00 | 1650.00 |
JAMES | 950.00 | 1425.00 |
FORD | 3000.00 | NULL |
MILLER | 1300.00 | 1950.00 |
select
ename,sal,(case job when 'MANAGER' then sal*1.1 when 'CLERK' then sal*1.5 else sal end) as newsal
from
emp;
ename | sal | newsal |
---|---|---|
SMITH | 800.00 | 1200.00 |
ALLEN | 1600.00 | 1600.00 |
WARD | 1250.00 | 1250.00 |
JONES | 2975.00 | 3272.50 |
MARTIN | 1250.00 | 1250.00 |
BLAKE | 2850.00 | 3135.00 |
CLARK | 2450.00 | 2695.00 |
SCOTT | 3000.00 | 3000.00 |
KING | 5000.00 | 5000.00 |
TURNER | 1500.00 | 1500.00 |
ADAMS | 1100.00 | 1650.00 |
JAMES | 950.00 | 1425.00 |
FORD | 3000.00 | 3000.00 |
MILLER | 1300.00 | 1950.00 |
9、取得薪水最高的前五名员工
mysql> select ename,sal from emp order by sal desc limit 5;
ename | sal |
---|---|
KING | 5000.00 |
FORD | 3000.00 |
SCOTT | 3000.00 |
JONES | 2975.00 |
BLAKE | 2850.00 |
10、取得薪水最高的第六名到第十名。
``
mysql> select ename,sal from emp order by sal desc limit 5,5;
``
ename | sal |
---|---|
CLARK | 2450.00 |
ALLEN | 1600.00 |
TURNER | 1500.00 |
MILLER | 1300.00 |
WARD | 1250.00 |
11、取得最后入职的五名员工
mysql> select ename,hiredate from emp order by hiredate desc limit 5;
ename | hiredate |
---|---|
ADAMS | 1987-05-23 |
SCOTT | 1987-04-19 |
MILLER | 1982-01-23 |
JAMES | 1981-12-03 |
FORD | 1981-12-03 |
12、取得每个薪水等级有多少员工
第一步:找出每个员工的薪水的等级
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
ename | sal | grade |
---|---|---|
SMITH | 800.00 | 1 |
ALLEN | 1600.00 | 3 |
WARD | 1250.00 | 2 |
JONES | 2975.00 | 4 |
MARTIN | 1250.00 | 2 |
BLAKE | 2850.00 | 4 |
CLARK | 2450.00 | 4 |
SCOTT | 3000.00 | 4 |
KING | 5000.00 | 5 |
TURNER | 1500.00 | 3 |
ADAMS | 1100.00 | 1 |
JAMES | 950.00 | 1 |
FORD | 3000.00 | 4 |
MILLER | 1300.00 | 2 |
第二步:在以上结果的基础上,按照grade进行分组,count计数
select
s.grade,count(*)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
s.grade;
grade | count(*) |
---|---|
1 | 3 |
2 | 3 |
3 | 2 |
4 | 5 |
5 | 1 |
13、面试题(建议自己动手设计下)
S 学生表
sno(pk) | sname |
---|---|
1 | 张三 |
2 | 李四 |
3 | 王五 |
4 | 赵六 |
C 课程表
cno(pk) | cname | cteacher |
---|---|---|
1 | linux | 张老师 |
2 | MySQL | 李老师 |
3 | Git | 王老师 |
4 | Java | 赵老师 |
5 | Redis | 黎明 |
SC 学生选课表
【sno+cno是复合主键,主键只有一个,同时sno、cno又是外键。外键可以有两个】
sno | cno | scgrede |
---|---|---|
1 | 1 | 50 |
1 | 2 | 50 |
1 | 3 | 50 |
2 | 2 | 80 |
2 | 3 | 70 |
2 | 4 | 59 |
3 | 1 | 60 |
3 | 2 | 61 |
3 | 3 | 99 |
3 | 4 | 100 |
3 | 5 | 52 |
4 | 3 | 82 |
4 | 4 | 99 |
4 | 5 | 46 |
1、找出没选过“黎明”老师的所有学生姓名
第一步:找出黎明老师所授课的编号
select cno from C where cteacher = '黎明';
cno |
---|
5 |
第二步:通过学生选课表查询cno=上面结果的sno,这些sno是选黎明老师课程的学号
``
select sno from SC where cno = (select cno from C where cteacher = '黎明');
``
sno |
---|
3 |
4 |
第三步:在学生表中查询sno not in 上面结果的数据
select
sname
from
S
where
sno not in (select sno from SC where cno = (select cno from C where cteacher = '黎明'));
sname |
---|
张三 |
李四 |
2、列出2门以上(含2门)不及格学生姓名及平均成绩
第一步:找出分数小于60并且按sno分组,计数大于2的
select
sc.sno
from
SC sc
where
sc.scgrade < 60
group by
sc.sno
having
count(*) >=2;
sno |
---|
1 |
第二步:与学生表S进行连接
select
sc.sno,s.sname
from
SC sc
join
S s
on
sc.sno=s.sno
where
sc.scgrade < 60
group by
sc.sno,s.sname
having
count(*) >=2;
sno | sname |
---|---|
1 | 张三 |
第三步:找出每个学生的平均成绩
select sno,avg(scgrade) as avggrade from SC group by sno;
sno | avggrade |
---|---|
1 | 50 |
2 | 69.66666666666667 |
3 | 74.4 |
4 | 75.66666666666667 |
第四步:第二步当作临时表t1和第三步当作临时表t2进行联合
`
select t1.sname,t2.avggrade from t1 join t2 on t1.sno=t2.sno;
`
select
t1.sno,t1.sname,t2.avggrade
from
(select
sc.sno,s.sname
from
SC sc
join
S s
on
sc.sno=s.sno
where
sc.scgrade < 60
group by
sc.sno,s.sname
having
count(*) >=2) t1
join
(select sno,avg(scgrade) as avggrade from SC group by sno) t2
on
t1.sno=t2.sno;
sno | sname | avggrade |
---|---|---|
1 | 张三 | 50 |
3、即学过1号课又学过2号课所有学生的姓名
第一步:找出学过1号课程的学生
select sno from SC where cno=1;
sno |
---|
1 |
3 |
第二步:找出学过2号课程的学生
select sno from SC where cno=2;
sno |
---|
1 |
2 |
3 |
第三步:将第一步和第二部进行联合
select sno from SC where cno=1 and sno in(select sno from SC where cno=2);
sno |
---|
1 |
3 |
第四步:将上面结果和S表进行联合
select
sc.sno,s.sname
from
SC sc
join
S s
on
sc.sno=s.sno
where
sc.cno=1 and sc.sno in(select sno from SC where cno=2);
sno | sname |
---|---|
1 | 张三 |
3 | 王五 |
14、列出所有员工及领导名字
表的自关联emp a<员工表> emp b <领导表>
select
a.ename empname,b.ename leardername
from
emp a
left join
emp b
on
a.mgr=b.empno;
+---------+-------------+
| empname | leardername |
+---------+-------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+-------------+
13 rows in set (0.06 sec)
15、列出受雇日期早于其直接上级领导的所有员工编号,姓名、部门名称
第一步:表的自关联emp a<员工表> emp b <领导表>找出所有员工
select
a.empno '员工编号', a.ename '员工姓名',a.hiredate '员工入职日期',
b.empno '领导编号',b.ename '领导姓名',b.hiredate '领导入职日期'
from
emp a
join
emp b
on
a.mgr=b.empno
where
a.hiredate<b.hiredate;
员工编号 | 员工姓名 | 员工入职日期 | 领导编号 | 领导姓名 | 领导入职日期 |
---|---|---|---|---|---|
7369 | SMITH | 1980-12-17 | 7902 | FORD | 1981-12-03 |
7499 | ALLEN | 1981-02-20 | 7698 | BLAKE | 1981-05-01 |
7521 | WARD | 1981-02-22 | 7698 | BLAKE | 1981-05-01 |
7566 | JONES | 1981-04-02 | 7839 | KING | 1981-11-17 |
7698 | BLAKE | 1981-05-01 | 7839 | KING | 1981-11-17 |
7782 | CLARK | 1981-06-09 | 7839 | KING | 1981-11-17 |
第二步:与dept表进行关联
select
a.empno '员工编号', a.ename '员工姓名',a.hiredate '员工入职日期',
b.empno '领导编号',b.ename '领导姓名',b.hiredate '领导入职日期',
d.dname '部门名称'
from
emp a
join
emp b
on
a.mgr=b.empno
join
dept d
on
a.deptno=d.deptno
where
a.hiredate<b.hiredate;
员工编号 | 员工姓名 | 员工入职日期 | 领导编号 | 领导姓名 | 领导入职日期 | 部门名称 |
---|---|---|---|---|---|---|
7369 | SMITH | 1980-12-17 | 7902 | FORD | 1981-12-03 | RESEARCH |
7499 | ALLEN | 1981-02-20 | 7698 | BLAKE | 1981-05-01 | SALES |
7521 | WARD | 1981-02-22 | 7698 | BLAKE | 1981-05-01 | SALES |
7566 | JONES | 1981-04-02 | 7839 | KING | 1981-11-17 | RESEARCH |
7698 | BLAKE | 1981-05-01 | 7839 | KING | 1981-11-17 | SALES |
7782 | CLARK | 1981-06-09 | 7839 | KING | 1981-11-17 | ACCOUNTING |
16、列出部门名称和这些员工信息同时列出那些没有员工的部门
PS;使用表关联和右外连接emp e <员工表> dept d <部门表>
select
e.*,d.dname
from
emp e
right join
dept d
on
e.deptno=d.deptno;
空间利用,不展示了。
17、列出至少有五个员工的部门详细信息
PS:分组可以使用多个字段联合起来。
select
d.deptno,d.dname,d.loc,count(e.ename)
from
emp e
join
dept d
on
e.deptno=d.deptno
group by
d.deptno,d.dname,d.loc
having
count(e.ename)>=5;
+--------+----------+---------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+----------+---------+----------------+
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
+--------+----------+---------+----------------+
2 rows in set (0.07 sec)
18、列出薪金比“SMITH”多的所有员工
select * from emp where sal > (select sal from emp where ename='SMITH');
空间利用,不展示了。
19、列出所有“CLERK”(办事员)的姓名及其部门名称,部门人数
第一步:找出工作是“CLERK”所有员工
select ename from emp where job='CLERK';
+--------+
| ename |
+--------+
| SMITH |
| ADAMS |
| JAMES |
| MILLER |
+--------+
4 rows in set (0.00 sec)
第二步:进行表关联,得出部门名称
select
e.ename,d.dname
from
dept d
join
emp e
on
e.deptno=d.deptno
where
e.job='CLERK';
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ADAMS | RESEARCH |
| JAMES | SALES |
| MILLER | ACCOUNTING |
+--------+------------+
4 rows in set (0.00 sec)
第三步:按部门编号分组,求每个部门人数
select deptno,count(*) as totalEmp from emp group by deptno;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+----------+
3 rows in set (0.00 sec)
第四步: 将第二步和第三步(当作临时表t)进行关联
select
e.ename,d.dname,t.totalEmp
from
dept d
join
emp e
on
e.deptno=d.deptno
join
(select deptno,count(*) as totalEmp from emp group by deptno) t
on
t.deptno=d.deptno
where
e.job='CLERK';
+--------+------------+----------+
| ename | dname | totalEmp |
+--------+------------+----------+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+----------+
4 rows in set (0.00 sec)
20、列出 最低薪金大于1500的各种工作及从事此工作的全部雇员人数
mysql> select job,min(sal),count(*) as totalEmp from emp group by job having min(sal)>1500;
+-----------+----------+----------+
| job | min(sal) | totalEmp |
+-----------+----------+----------+
| ANALYST | 3000.00 | 2 |
| MANAGER | 2450.00 | 3 |
| PRESIDENT | 5000.00 | 1 |
+-----------+----------+----------+
3 rows in set (0.00 sec)
21、列出部门在“SALES”<销售部>工作的姓名,假定不知道销售部的部门的部门编号
第一步:查处部门编号(30)
select deptno from dept where dname='SALES';
第二步:表关联
select ename from emp where deptno=(select deptno from dept where dname='SALES');
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.00 sec)
22、列出薪金高于公司平均薪金的所有员工、所在的部门、上级领导、雇员的工资等级
emp a <员工表>
emp b <领导表>
dept d <部门表>
salgrade <工资等级表>
第一步:先不考虑公司的平均薪水,表自关联取出后面数据
第二步:在后面加where条件
第三步:在emp b<领导表>上加left。左边表全部显示,因为KING是大BOSS,不能没有他。
select
a.ename empname,d.deptno,b.ename leardername,s.grade
from
emp a
join
dept d
on
a.deptno=d.deptno
left join
emp b
on
a.mgr=b.empno
join
salgrade s
on
a.sal between s.losal and s.hisal
where
a.sal>(select avg(sal) from emp);
+---------+--------+-------------+-------+
| empname | deptno | leardername | grade |
+---------+--------+-------------+-------+
| JONES | 20 | KING | 4 |
| BLAKE | 30 | KING | 4 |
| CLARK | 10 | KING | 4 |
| SCOTT | 20 | JONES | 4 |
| KING | 10 | NULL | 5 |
| FORD | 20 | JONES | 4 |
+---------+--------+-------------+-------+
6 rows in set (0.00 sec)
23、列出所有与“SCOTT”从事相同工作的所有员工及部门名称
select
e.ename,e.job,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno
where
e.job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
1 row in set (0.00 sec)
24、列出薪金等于部门20中员工薪金的其他员工的姓名和薪金
第一步:找出30部门中所有员工的薪金,并且去重
第二步:使用in查找上面结果,并排出30部门的
select
ename,sal
from
emp
where
sal in (select distinct sal from emp where deptno=30) and deptno<>30;
Empty set (0.00 sec) 数据量不够。
25、列出薪金高于在30部门工作的所有员工的薪金的员工姓名和薪金,部门名称
第一步:找出30部门最大的薪金
mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+
1 row in set (0.00 sec)
第二步:员工表和部门表进行关联
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno
where
e.sal>(select max(sal) from emp where deptno=30);
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
4 rows in set (0.00 sec)
26、列出每个部门工作的员工数量,平均工资、平均服务期限
第一步:使用右外连接,部门表全部显示。按领导编号分组(部门编号有些为空),count()计数,求出每个部门的员工数量
select
d.deptno,count(e.ename)
from
emp e
right join
dept d
on
e.deptno=d.deptno
group by
d.deptno;
+--------+----------------+
| deptno | count(e.ename) |
+--------+----------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
| 40 | 0 |
| 50 | 0 |
| 60 | 0 |
+--------+----------------+
6 rows in set (0.01 sec)
第二步:在以上查询结果的基础上,求平均工资。利用ifnull函数处理NULL。
select
d.deptno '部门编号',count(e.ename) '员工数量',ifnull(avg(sal),0) '平均工资'
from
emp e
right join
dept d
on
e.deptno=d.deptno
group by
d.deptno;
+--------------+--------------+--------------+
| 部门编号 | 员工数量 | 平均工资 |
+--------------+--------------+--------------+
| 10 | 3 | 2916.666667 |
| 20 | 5 | 2175.000000 |
| 30 | 6 | 1566.666667 |
| 40 | 0 | 0.000000 |
| 50 | 0 | 0.000000 |
| 60 | 0 | 0.000000 |
+--------------+--------------+--------------+
6 rows in set (0.00 sec)
第三步:在以上结果的基础上,求平均服务期限。使用ifnull()、to_days()、now()函数。 参考avg(sal),只是把每个员工的服务期限放到avg()函数中
先求出每个员工的服务年限:
select (to_days(now()) - to_days(hiredate))/365 from emp;
select
d.deptno '部门编号',
count(e.ename) '员工数量',
ifnull(avg(sal),0) '平均工资',
ifnull(avg((to_days(now()) - to_days(hiredate))/365),0) '服务期限'
from
emp e
right join
dept d
on
e.deptno=d.deptno
group by
d.deptno;
+--------------+--------------+--------------+--------------+
| 部门编号 | 员工数量 | 平均工资 | 服务期限 |
+--------------+--------------+--------------+--------------+
| 10 | 3 | 2916.666667 | 35.93793333 |
| 20 | 5 | 2175.000000 | 33.96494000 |
| 30 | 6 | 1566.666667 | 36.23651667 |
| 40 | 0 | 0.000000 | 0.00000000 |
| 50 | 0 | 0.000000 | 0.00000000 |
| 60 | 0 | 0.000000 | 0.00000000 |
+--------------+--------------+--------------+--------------+
6 rows in set (0.01 sec)
27、列出所有员工的姓名、部门名称、工资
select
e.ename,d.dname,e.sal
from
emp e
join
dept d
on
e.deptno=d.deptno;
不展示数据了
28、列出所有部门的详细信息和人数
PS:需要使用右外连接,显示全部部门。按部门多个字段分组,并按员工姓名计数。
select
d.deptno,d.dname,d.loc,count(e.ename)
from
emp e
right join
dept d
on
e.deptno=d.deptno
group by
d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+----------+----------------+
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
| 40 | OPERATIONS | BOSTON | 0 |
| 50 | HR | SY | 0 |
| 60 | NULL | MARKET | 0 |
+--------+------------+----------+----------------+
6 rows in set (0.00 sec)
29、列出各种工作的最低工资及从事此工作的雇员姓名
第一步:按工作岗位分组,使用min()函数求工资最小值
select
job,min(sal) as minsal
from
emp
group by
job;
+-----------+---------+
| job | minsal |
+-----------+---------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+---------+
5 rows in set (0.00 sec)
第二步:将上面的查询结果当作临时表t,
select
e.ename,t.*
from
emp e
join
(select job,min(sal) as minsal from emp group by job) t
on
e.job=t.job and e.sal=t.minsal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)
30、列出各个部门MANAGER的最低薪金
PS:找出每个部门的MANAGER,并按部门编号分组
select
deptno,min(sal) as minsal
from
emp
where
job='MANAGER'
group by
deptno;
+--------+---------+
| deptno | minsal |
+--------+---------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)
31、列出所有员工的年工资,按年薪从低到高排序
PS:年薪=(工资+佣金)×12,需要判断佣金是否为null
select
ename,((sal+ifnull(comm,0))*12) as yearsal
from
emp
order by
yearsal;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| JAMES | 11400.00 |
| ADAMS | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD | 21000.00 |
| ALLEN | 22800.00 |
| CLARK | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| JONES | 35700.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| KING | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)
32、求出员工领导的薪水超过3000的员工姓名和领导名称
PS:表的自关联,条件是领导的薪水大于3000
select
a.ename '员工姓名',a.sal '员工薪水', b.ename '领导姓名',b.sal '领导薪水'
from
emp a
join
emp b
on
a.mgr=b.empno
where
b.sal > 3000;
+--------------+--------------+--------------+--------------+
| 员工姓名 | 员工薪水 | 领导姓名 | 领导薪水 |
+--------------+--------------+--------------+--------------+
| JONES | 2975.00 | KING | 5000.00 |
| BLAKE | 2850.00 | KING | 5000.00 |
| CLARK | 2450.00 | KING | 5000.00 |
+--------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)
33、求出部门名称中带“S”字符的部门员工的工资合计,部门人数
第一步:先找出所有部门的员工,使用右连接,显示全部部门
select
e.*,d.*
from
emp e
right join
dept d
on
e.deptno=d.deptno;
共17条记录,数据不展示。
第二步:在以上结果的基础上,按部门编号分组,使用sum()函数求和,count()计数。<r>
select
d.deptno '部门编号',d.dname '部门名称',
ifnull(sum(e.sal),0) '工资合计',count(e.ename) '部门人数'
from
emp e
right join
dept d
on
e.deptno=d.deptno
group by
d.deptno;
+--------------+--------------+--------------+--------------+
| 部门编号 | 部门名称 | 工资合计 | 部门人数 |
+--------------+--------------+--------------+--------------+
| 10 | ACCOUNTING | 8750.00 | 3 |
| 20 | RESEARCH | 10875.00 | 5 |
| 30 | SALES | 9400.00 | 6 |
| 40 | OPERATIONS | 0.00 | 0 |
| 50 | HR | 0.00 | 0 |
| 60 | NULL | 0.00 | 0 |
+--------------+--------------+--------------+--------------+
第三步:使用like进行模糊查询,并按部门姓名分组
select
d.dname '部门名称',
ifnull(sum(e.sal),0) '工资合计',count(e.ename) '部门人数'
from
emp e
right join
dept d
on
e.deptno=d.deptno
where
d.dname like '%S%'
group by
d.dname;
+--------------+--------------+--------------+
| 部门名称 | 工资合计 | 部门人数 |
+--------------+--------------+--------------+
| OPERATIONS | 0.00 | 0 |
| RESEARCH | 10875.00 | 5 |
| SALES | 9400.00 | 6 |
+--------------+--------------+--------------+
3 rows in set (0.00 sec)
34、给任职超过30年的员工加薪10%,
第一步:创建emp_bak
`create table emp_bak as select * from emp;
`
第二步:使用(to_days(now())-to_days(hiredate))/35 >30
mysql> update emp_bak set sal=sal*1.1 where (to_days(now()) - to_days(hiredate))/365 >30;
Query OK, 14 rows affected (0.34 sec)
Rows matched: 14 Changed: 14 Warnings: 0
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。