mysql基础之多表练习题

随笔练习（mysql多表查询）

一、 部门表

创建表

CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,
    DNAME VARCHAR(14), -- 部门名称
    LOC VARCHAR(13)-- 部门地址
) ; 

插入数据

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

二、 员工表

创建表

CREATE TABLE EMP(
    EMPNO INT  PRIMARY KEY, -- 员工编号
    ENAME VARCHAR(10), -- 员工姓名
    JOB VARCHAR(9), -- 员工工作
    MGR INT, -- 员工直属领导编号
    HIREDATE DATE, -- 入职时间
    SAL DOUBLE, -- 工资
    COMM DOUBLE, -- 奖金
    DEPTNO INT,  -- 所在部门
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) -- 关联dept表  
);  

插入数据

-- ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
INSERT INTO EMP VALUES(7369,'SMITH','职员',7566,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','销售员',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','销售员',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','经理',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','销售员',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','经理',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','经理',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','职员',7566,'1987-07-03',3000,2000,20);
INSERT INTO EMP VALUES(7839,'KING','董事长',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNERS','销售员',7698,'1981-09-08',1500,50,30);
INSERT INTO EMP VALUES(7876,'ADAMS','职员',7566,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','职员',7698,'1981-12-03',1250,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','销售员',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','职员',7782,'1981-01-23',1300,NULL,10);

三、 工资等级表

创建表

CREATE TABLE SALGRADE( 
    GRADE INT,-- 等级
    LOSAL DOUBLE, -- 最低工资
    HISAL DOUBLE -- 最高工资
); 

插入数据

INSERT INTO SALGRADE VALUES (1,500,1000);
INSERT INTO SALGRADE VALUES (2,1001,1500);
INSERT INTO SALGRADE VALUES (3,1501,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

完成下面的功能

1.查找部门30中员工的详细信息。
2.找出从事职员工作的员工的编号、姓名、部门号
3.检索出奖金多于基本工资的员工信息
4.检索出奖金多于基本工资60%的员工
5.找出姓名中包含A的员工信息。
6.找出姓名以A、B、S开始的员工信息
7.找到名字长度为7个字符的员工信息
8.名字中不包含R字符的员工信息。
9.返回员工的详细信息并按姓名升序
10.返回员工的信息并按姓名降序,工资升序排列。
11.计算员工的日薪(按30天)。
12.找出获得奖金的员工的工作。
13.找出奖金少于100或者没有获得奖
14.找出10部门的经理、20部门的职员的员工信息
15.找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

1.分析：需要查找部门30中的员工数据，我们需要用到员工表emp，部门表dept，查找条件就是通过员工表中的deptno = 30 来寻找,并且还需要输出员工部门信息.这里我们使用内连接，筛选条件就是emp.deptno = dept.deptno 。

SELECT * FROM emp, dept WHERE emp.deptno = 30 AND emp.deptno = dept.deptno; 

2.分析：找出从事职员工作的员工的编号、姓名、部门号，我们发现职员工作和员工编号都是在emp表中的，所以我们只需要使用emp表就足够了，并且我们需要使用emp表中的职员工作作为条件来寻找empno

SELECT empno,ename,deptno FROM emp WHERE job = '职员';

3.分析：奖金和基本工资都在员工表中，还需要关联一下员工的部门信息。所以，我们需要使用emp表和
dept表，限定emp.sal < emp.comm寻找满足条件的员工,然后使用内连接筛选笛卡尔积。

SELECT * FROM emp t1,dept t2 WHERE t1.sal< IFNULL(t1.comm,0) AND t1.deptno = t2.deptno;

4.分析：奖金多余百分之60基本工资的员工，和上面类似的做法

SELECT * FROM emp t1,dept t2 WHERE (0.6*t1.sal)<IFNULL(t1.comm,0) AND t1.deptno=t2.deptno;

5.分析：找出姓名中包含A的员工信息,我们需要用到emp表中的ename属性来进行判断，然后使用内连接来得到员工部门信息。注意：对字符串进行判断，需要使用like

SELECT * FROM emp t1,dept t2 WHERE t1.ename like '%A%' AND t1.deptno = t2.deptno;

6.分析：需要找姓名以A.B.S开始的员工，和上面类似，我们可以使用LIKE ‘A%’ OR t1.ename LIKE ‘B%’ OR t1.ename LIKE ‘S%’来作为条件,最后对找到的数据进行筛选t1.deptno = t2.deptno。

SELECT * FROM emp t1,dept t2 WHERE t1.ename LIKE 'A%' OR t1.ename LIKE 'B%'  OR t1.ename LIKE 'S%' HAVING t1.deptno = t2.deptno;

7.分析：找到名字为7个的员工信息，那么我们需要使用ename like ‘_‘占位符来进行判断，最后再对员工信息进行筛选

SELECT * FROM emp t1,dept t2 WHERE t1.ename LIKE '_______' AND t1.deptno = t2.deptno;

8.分析：名字中不包含R字符的员工信息。也和上面类似，加一个反义字符即可

SELECT * FROM emp t1,dept t2 WHERE t1.ename NOT LIKE '%R%' AND t1.deptno = t2.deptno;

9.分析：返回员工的详细信息并按姓名升序。先找到员工的所有详细信息，然后使用GROUP BY 进行排序操作,ASC升序，DESC降序

SELECT * FROM emp t1,dept t2 WHERE t1.deptno = t2.deptno GROUP BY t1.ename ASC;

10.分析：返回员工的信息并按姓名降序,工资升序排列。和上面类似。

SELECT * FROM emp t1,dept t2 WHERE t1.deptno = t2.deptno GROUP BY t1.ename ASC , t1.sal DESC;

11.分析：计算员工的日薪(按30天)。获取到员工的工资和奖金即可计算，需要输出员工姓名和日薪即可

SELECT ename,((sal+IFNULL(comm,0))/30) as 日薪 FROM emp;

12.分析：找出获得奖金的员工的工作。判断条件emp.comm is not null.输出员工JOB和姓名即可

SELECT ename,job FROM emp WHERE emp.comm IS NOT NULL;

13.分析：找出奖金少于100或者没有获得奖。使用子查询，得到奖金数，然后将奖金数作为条件进行判断(select * from emp where IFNULL(comm,0)< 100）t1 ,得到虚拟表 ,然后通过t1.deptno 作为条件找到员工的部门信息

SELECT * FROM (SELECT * FROM emp WHERE IFNULL(comm,0) < 100) t1,dept WHERE t1.deptno = dept.deptno;

14.分析：找出10部门的经理、20部门的职员的员工信息.这样，我们通过条件进行判断,
最后使用HAVING 进行筛选，去除笛卡尔积中不满足的条件

SELECT * FROM emp t1,dept t2 WHERE t1.deptno = 10 AND t1.job = "经理"  OR t1.deptno = 20 AND t1.job = "职员" HAVING  t1.deptno = t2.deptno;

15.分析：找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。这里需要使用双重条件查询，找到10部门的经理和20部门的职员的信息，然后将它返回一个整体的条件，然后这个新条件中的empno 不能等于 emp.empno 并且 emp.sal+IFNULL(emp.comm,0) >2000

SELECT * FROM emp t1,dept t2 WHERE  (t1.job NOT IN ("经理","职员")  AND (t1.sal+ IFNULL(t1.comm,0))>2000) OR (t1.deptno = 10 AND t1.job = "经理"  OR t1.deptno = 20 AND t1.job = "职员" )HAVING  t1.deptno = t2.deptno ;