MySQL高级查询习题

MySQL高级查询习题

提示：
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno所属部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
工资 ＝ 薪金 ＋ 佣金
首先先知道主外键的关系，从提示中不难看出 dept 部门表是主键，emp 员工表是外键

在Emp 表中完成如下练习：

– 创建部门表

CREATE TABLE dept(
teptno VARCHAR(20) PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20));

– 创建员工表

CREATE TABLE emp(empno VARCHAR(20) PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
mar VARCHAR(20),
hiredate DATE,
sal DOUBLE,
comm DOUBLE,
deptno VARCHAR(20) REFERENCES dept(deptno));

– 给部门表添加数据

INSERT INTO dept(teptno,dname,loc) VALUES("1","研发部","六楼");
INSERT INTO dept(teptno,dname,loc) VALUES("2","测试部","五楼");
INSERT INTO dept(teptno,dname,loc) VALUES("3","销售部","四楼");
INSERT INTO dept(teptno,dname,loc) VALUES("4","推广部","三楼");
INSERT INTO dept(teptno,dname,loc) VALUES("5","生产部","二楼");
INSERT INTO dept(teptno,dname,loc) VALUES("6","咨询部","一楼");

– 给员工表添加数据

INSERT INTO emp VALUES("001","小团团","java开发工程师","111","2020-01-15",10000,4000,"1");
INSERT INTO emp VALUES("002","韩冰","妲己","111","2019-02-14",20000,2000,"2");
INSERT INTO emp VALUES("003","呆妹","java咨询师","111","2019-03-14",10000,1000,"3");
INSERT INTO emp VALUES("004","Uzi","乌老师","111","2017-05-14",50000,5000,"4");
INSERT INTO emp VALUES("005","letme","理论大师","111","2017-04-14",20000,8000,"5");
INSERT INTO emp VALUES("006","七七饿了","金牌讲师","111","2019-08-14",10000,2000,"6");
INSERT INTO emp VALUES("007","小橙子","金牌讲师","111","2020-07-14",20000,5000,"6");
INSERT INTO emp VALUES("008","小橘子","java开发工程师","111","2020-09-25",50000,5000,"1");
INSERT INTO emp VALUES("010","小兔子","老师","111","2020-10-01",30000,000,"6");
INSERT INTO emp VALUES("011","达摩","峡谷艺术家","111","2017-09-14",10000,2000,"3");

1、在emp表中查询出所有的记录。

-- 1、在emp表中查询出所有的记录。
SELECT * FROM emp;

2、在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。

-- 2、在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。
SELECT * FROM emp ORDER BY sal+comm DESC;

3、在emp表中查询出comm字段为空值的记录。

-- 3、在emp表中查询出comm字段为空值的记录。
-- 先添加一个comm为空的数据
INSERT INTO emp (empno,ename,job,mar,hiredate,sal,deptno)VALUES("011","猪八戒","峡谷艺术家","111","2015-12.12","9000","2");
SELECT * FROM emp WHERE comm IS NULL;

4、查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）

-- 4、查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）
SELECT COUNT(DISTINCT deptno) FROM emp;

5、在emp表中查询出部门编号为5或6的记录。（要求使用IN关键字）

-- 5、在emp表中查询出部门编号为5或6的记录（要求使用IN关键字）
SELECT* FROM emp WHERE deptno IN(5,6);

6、在emp表中查询出姓名的第二个字母为A的记录。

-- 6、在emp表中查询出姓名的第二个字母为A的记录。
-- 先添加一条第二个字母为A的数据
INSERT INTO emp VALUES("012","AAA","峡谷艺术家","111","2017-09-18",10000,6000,"3");
SELECT * FROM emp WHERE ename LIKE "_A%";

7、查询出emp表中总共有多少条记录。

-- 7、查询出emp表中总共有多少条记录。
SELECT COUNT(*) FROM emp;

8、查询emp表中出每个部门的部门代码、薪水之和、平均薪水。

-- 8、查询emp表中出每个部门的部门代码、薪水之和、平均薪水
SELECT AVG(sal+IFNULL(comm,0))AS '平均薪水',SUM(sal+IFNULL(comm,0))AS '薪水之和',deptno AS '部门编号' FROM emp GROUP BY deptno;

不忘初心，砥砺前行