Mysql基础语句练习

先来建表：

/*创建部门表*/
CREATE TABLE dept( deptnu INT PRIMARY KEY comment '部门编号', 
dname VARCHAR(50) comment '部门名称',
addr VARCHAR(50) comment '部门地址' );

//某个公司的员工表
CREATE TABLE employee( empno INT PRIMARY KEY comment '雇员编号',
 ename VARCHAR(50) comment '雇员姓名',
  job VARCHAR(50) comment '雇员职位', 
  mgr INT comment '雇员上级编号', 
 hiredate DATE comment '雇佣日期', 
 sal DECIMAL(7,2) comment '薪资', 
 deptnu INT comment '部门编号' );

CREATE TABLE salgrade(
grade INT PRIMARY KEY comment '等级',
 lowsal INT comment '最低薪资', 
 higsal INT comment '最高薪资' );

插入数据：

/*插入dept表数据*/ 
INSERT INTO dept VALUES (10, '研发部', '北京'); 
INSERT INTO dept VALUES (20, '工程部', '上海'); 
INSERT INTO dept VALUES (30, '销售部', '广州'); 
INSERT INTO dept VALUES (40, '财务部', '深圳');

/*插入emp表数据*/ 
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10); 
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20); 
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30); 
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10); 
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20); 
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20); 
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30); 
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30); 
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30); 
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30); 
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30); 
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
 INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20); 
 INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
/*插入salgrade表数据*/
 INSERT INTO salgrade VALUES (1, 7000, 12000); 
 INSERT INTO salgrade VALUES (2, 12010, 14000);
  INSERT INTO salgrade VALUES (3, 14010, 20000); 
  INSERT INTO salgrade VALUES (4, 20010, 30000); 
  INSERT INTO salgrade VALUES (5, 30010, 99990);

实现：

1. 查询出employee表中全部的：雇员编号，雇员姓名，雇员职位信息
2. 查询出employee表中雇员姓名为‘后裔’的人的所有信息
3. 查询出employee表中薪资大于10000的人的所有信息
4. 查询出employee表中雇员姓名以‘林’开头的人的所有信息
5. 统计出employee表中一共有多少条数据
6. 计算出employee表中薪资总和
7. 查询出employee表中薪资最高的人的所有信息
8. 查询出employee表中薪资最低的人的所有信息
9. 计算出employee表中薪资的平均值
10. 查询出employee表中每个部门各有多少人（tips:分组查询)
11. 查询出employee表中雇员职位为文员的有多少人（tips:分组查询+having)
12. 查询出employee表中所有数据的信息并按薪资进行升序排序
13. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。（tips:涉及表：dept employee）
14. 列出employee表中最低薪金大于15000的各种工作及从事此工作的员工人数。
15. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。（tips:涉及表employee dept salgrade）

答案：

1. select empno,ename,job as ename_job from employee;
2. select * from employee where ename=‘后裔’;
3. select * from employee where sal > 10000;
4. select * from employee where ename like ‘林%’;
5. select count(*) from employee;
6. select sum(sal) from employee;
7. select * from employee where sal= (select max(sal) from employee);
8. select * from employee where sal= (select min(sal) from employee);
9. select avg(sal) from employee;
10. select deptnu,count(*) from employee group by deptnu;
11. select job,count(*) from employee group by job having job =‘文员’;
12. select * from employee order by sal;
13. select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;
14. select job,count(*) from employee group by job having min(sal) > 15000;
15. elect a.*,c.dname,b.ename,d.grade from employee a,employee b,dept c ,salgrade d where a.mgr=b.empno and a.deptnu =c.deptnu and a.sal > (select avg(sal) from employee) and a.sal between d.lowsal and d.higsal;