Mysql5.7常用查询

创建一个员工表
create table employee
(
empno int,#员工编号（主键，自动增长）
ename varchar(20),#员工姓名（非空）
mgr varchar(4),#上司的员工编号
hiredate date,#雇佣日期（非空）
job varchar(40),#职位（职位可以是‘职员’,‘办事员’，’销售员’,’总经理’等）
sal decimal(7,2),#薪水
comm decimal(7,2),#佣金
deptno int#所在的部门编号（可以设为：10,20,30 等，外键，引用部门表的部门编号）
)
创建部门表：
deptno部门编号
deptname部门名称
loc部门所在地
完成以下功能：
1. 查看库中所有表
2. 查看表的创建细节 show create table employee;
3. 查看表的结构 desc employee;
4. 使用 insert 语句向表中插入一个员工的信息。
5. 查看插入的数据
6. 使用 insert 语句一次性向表中插入 10 个员工的信息。
7. 给部门表添加 4 条记录，部门编号分别为 10,20,30,40 的部门
8. 将所有员工薪水修改为 5000 元。
9. 将姓名为’aaa’的员工薪水修改为 3000 元。（姓名自定，表中存在的姓名）
10. 将姓名为’aaa’的员工薪水修改为 5000 元,job 改为 ccc
11. 将 aaa 的薪水在原有基础上增加 1000 元。
12. 选择部门 30 中的雇员
13. 列出所有办事员的姓名、编号和部门
14. 找出佣金高于薪金的雇员
15. 找出佣金高于薪金 60%的雇员
16. 找出部门 10 中所有经理和部门 20 中所有办事员的详细资料
17. 找出部门 10 中所有经理、部门 20 中所有办事员以及既不是
18. 经理又不是办事员但其薪金大于或等于 2000 的所有雇员的详细资料
19. 找出收取佣金的雇员的不同工作.
20. 找出不收取佣金或收取的佣金低于 100 的雇员
21. 找出各月最后一天受雇的所有雇员.
22. 找出早于 22 年之前受雇的雇员
23. 显示只有首字母大写的所有雇员的姓名
24. 显示正好为 5 个字符的雇员姓名.
25. 显示不带有"r"的雇员姓名
26. 显示所有雇员的姓名的前三个字符.
27. 显示所有雇员的姓名，用 a 替换所有"a"
28. 显示所有雇员的姓名以及满 10 年服务年限的日期
29. 显示雇员的详细资料,按姓名排序
30. 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
31. 显示雇员姓名，工作和薪金，按工作类型降序顺序排序，在工作类型内工作按薪金排序
32. 显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,并将最早年份的记录排在最前面
33. 显示在一个月为 30 天的情况下所有雇员的日薪金,忽略余数
34. 找出在(任何年份的)2 月受雇的所有雇员
35. 对于每个雇员，显示其加入公司的天数
36. 显示姓名字段的任何位置包含"a"的所有雇员的姓名

注意：以下查询会用到分组查询或者连接查询或者子查询
37. 列出至少有一个雇员的所有部门
38. 列出薪金比'smith'多的所有雇员.
39. 列出所有雇员的姓名及其上级的姓名.
40. 列出入职日期早于其直接上级的所有雇员.
41. 列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
42. 列出所有职位为'办事员'的姓名及其部门名称.
43. 列出各种职位的最低薪金
44. 列出从事“sales”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
45. 列出薪金高于公司平均水平的所有雇员
46. 列出与“scott”从事相同工作的所有雇员
47. 列出某些雇员的姓名和薪金，条件是他们的薪金等于部门 30 中任何一个雇员的薪金
48. 列出某些雇员的姓名和薪金，条件是他们的薪金高于部门 30 中所有雇员的薪金
49. 列出每个部门的信息以及该部门中雇员的数量

50. 列出所有雇员的雇员名称、部门名称和薪金

 

create table dept(
        deptno int not null primary key,
        deptname varchar(30) not null,
        loc varchar(100)
);
create table employee(
        empno int not null auto_increment primary key comment '员工编号',
        ename varchar(20) not null comment '员工姓名',
        mgr varchar(4) comment '上司的员工编号',
        hiredate date not null comment '雇用日期',
        job varchar(40) comment '职位',
        sal decimal(7,2),
        comm decimal(7,2),
        deptno int comment '所在部门编号',
        foreign key(deptno) references dept(deptno)        
);

1、show tables;
2、show create table employee;
3、desc employee;
4、insert into dept values(10,'技术部','北京');
      insert into employee values(9527,'scott',,'1990-01-22','ceo',5000,8000,10);
5、select * from employee;
6、insert into dept values
         (20,'销售部','北京'),
         (30,'人事部','北京');
      insert into employee values
         (0,'zhangsan1','9527','2012-03-22','职员',5000,2500,10),
         (0,'zhangsan2','9522','2012-01-22','办事员',2000,3000,20),
         (0,'zhangsan3','9522','2012-05-22','办事员',2000,3300,20),
         (9522,'zhangsan4','9527','2012-07-22','总经理',5000,2500,10),
         (0,'smith','9559','2014-01-22','职员',3000,99,30),
         (9559,'zhangsan6','9527','2016-02-22','总经理',5000,2500,10),
         (0,'zhangsan7','9559','2014-05-22','职员',3000,,30),
         (0,'zhangsan8','9527','2015-05-22','经理',5000,,10),
         (0,'zhangsan9','9522','2014-08-22','办事员',2000,,20),
         (0,'zhangsanr','9559','2016-09-30','职员',3000,,30);
7、insert into dept values(40,'管理部','北京');
8、update employee set sal=5000;
9、update employee set sal=3000 where ename='张三';
10、update employee set sal=5000,job='销售员' where ename='张三';
11、update employee set sal=sal+1000 where ename='张三';
12、select * from employee where deptno=30;
13、select ename 姓名,empno 编号, deptno 部门 from employee where job='办事员';
14、select * from employee where comm>sal;
15、select * from employee where comm>sal*0.6;
16、select * from employee where (deptno=10 and job='经理') or (deptno=20 and job='办事员');
17、select * from employee where (deptno=10 and job='经理') or (deptno=20 and job='办事员') or (job<>'经理' and job<>'办事员' and sal>=2000);
19、select * from employee where comm is not null;
20、select * from employee where comm is null or comm<100;
21、select * from employee where monthname(hiredate)<>monthname(date_add(hiredate,interval 1 day));
22、select * from employee where not now()between hiredate and date_add(hiredate,interval 22 year);
23、select ename from employee where ascii(left(ename,1))>=65 and ascii(left(ename,1))<=90;
24、select * from employee where length(ename)=5;
25、select * from employee where ename not like '%r%';
26、select left(ename,3) from employee;
27、select replace(ename,'a','a') from employee;
28、select ename,date_add(hiredate,interval 10 year)10年服务年限 from employee;
29、select * from employee order by ename;
30、select ename,hiredate from employee order by timestampdiff(day,hiredate,now()) desc;
31、select ename,job,sal from employee order by job desc,sal;
32、select ename,year(hiredate)年份,month(hiredate)月份 from employee order by 年份,月份;
33、select ename,floor(sal/30) 日薪资 from employee;
34、select ename from employee where month(hiredate)=2;
35、select ename,timestampdiff(day,hiredate,now()) from employee;
36、select ename from employee where ename like '%a%';
37、select a.deptno,a.deptname,b.人数 from dept a inner join (select deptno,count(*)人数 from employee group by deptno) b on a.deptno=b.deptno where b.人数>1;
38、select * from employee where sal>(select sal from employee where ename='smith');
39、select a.empno 员工编号,a.ename 员工姓名,b.ename 上级姓名 from employee a left join (select * from employee) b on a.mgr=b.empno;
40、select a.ename 员工姓名,a.hiredate 入职时间,b.hiredate 上级入职时间 from employee a inner join (select * from employee) b on a.mgr=b.empno and (a.hiredate not between b.hiredate and now());
41、select * from dept a left join employee b on a.deptno=b.deptno; 
42、select * from dept a inner join employee b on a.deptno=b.deptno and job='办事员'; 
43、select a.deptname,min(b.sal)最低薪资 from dept a left join employee b on a.deptno=b.deptno group by b.deptno;
44、select * from dept a inner join employee b on a.deptno=b.deptno and a.deptname='销售部';
45、select * from employee where sal>(select avg(sal) from employee);
46、select * from employee where job=(select job from employee where ename='scott');
47、select ename,sal from employee where sal in (select sal from employee where deptno=30);
48、select ename,sal from employee where sal>(select max(sal) from employee where deptno=30);
49、select a.*,b.人数 from dept a left join (select deptno,count(*)人数 from employee group by deptno) b on a.deptno=b.deptno;
50、select b.ename,a.deptname,b.sal from dept a inner join employee b on a.deptno=b.deptno;