MySQL练习题（五）

环境搭建（MySQL:8.0.25）:

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              #对应dept表的外键
);INSERT INTO EMP
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMP
VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP
VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP
VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP
VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP
VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-03', 3000, NULL, 20);
INSERT INTO EMP
VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP
VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20);
INSERT INTO EMP
VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP
VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP
VALUES (7934, 'MILLER', 'CLERK', 7782, '1981-01-23', 1300, NULL, 10);-- 添加部门和员工的外键
ALTER TABLE EMPADD CONSTRAINT FOREIGN KEY EMP (DEPTNO) REFERENCES DEPT (DEPTNO);-- 工资等级表
CREATE TABLE SALGRADE
(GRADE int,    #等级LOSAL double, #最低工资HISAL double  #最高工资
);INSERT INTO SALGRADE
VALUES (1, 700, 1200);
INSERT INTO SALGRADE
VALUES (2, 1201, 1400);
INSERT INTO SALGRADE
VALUES (3, 1401, 2000);
INSERT INTO SALGRADE
VALUES (4, 2001, 3000);
INSERT INTO SALGRADE
VALUES (5, 3001, 9999);

1、写出部门 和 员工之间的1-N外键关系语句：

ALTER TABLE EMPADD CONSTRAINT FK_DEPTNOFOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO);

2、找出从事clerk工作的员工的编号、姓名、部门号。

select ep.DEPTNO, ep.ENAME, ep.DEPTNO
from EMP ep
where ep.JOB = 'CLERK';

3、检索出奖金多于基本工资60%的员工信息。

select *
from EMP ep
where ep.COMM > ep.SAL * 0.6;

4、找出10部门的经理（MANAGER）、20部门的职员（CLERK） 的员工信息。

SELECT *
FROM EMP
WHERE (JOB = 'MANAGER' AND DEPTNO = 10)OR (JOB = 'CLERK' AND DEPTNO = 20);

5、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

SELECT *
FROM EMP
WHERE (JOB = 'MANAGER' AND DEPTNO = 10)OR (JOB = 'CLERK' AND DEPTNO = 20)OR (JOB != 'CLERK' AND JOB != 'MANAGER' AND SAL > 2000);

6、找出奖金少于100或者没有获得奖金的员工的信息。

select *
from EMP ep
where ep.COMM < 100or ep.COMM is null;

7、找出姓名以A、B、S开始的员工信息。

select ep.ENAME
from EMP ep
where ep.ENAME like 'A%'or ep.ENAME like 'B%'or ep.ENAME like 'S%';

8、找到名字长度为6个字符的员工信息。

select ep.ENAME
from EMP ep
where char_length(ep.ENAME) = 6;

9、名字中不包含R字符的员工信息。

select ep.ENAME
from EMP ep
where ep.ENAME not like '%R%';

10、返回员工的信息并按工作降序工资升序排列。

SELECT *
FROM EMP
ORDER BY JOB DESC, SAL ASC;

11、工资水平多于smith的员工信息。

SELECT ep2.ENAME
FROM EMP ep1JOIN EMP ep2 ON ep2.SAL > ep1.SAL
WHERE ep1.ENAME = 'SMITH';

12、返回从事clerk工作的员工姓名和所在部门名称。

SELECT ep.ENAME, dp.DNAME
FROM EMP ep
LEFT JOIN DEPT dp ON dp.DEPTNO = ep.DEPTNO
WHERE ep.JOB = 'CLERK';

13、返回员工和所属经理的姓名。

SELECT ep1.ENAME AS Employee, ep2.ENAME AS Manager
FROM EMP ep1LEFT JOIN EMP ep2 ON ep1.MGR = ep2.EMPNO;

14、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

SELECT ep1.ENAME AS Employee, ep2.ENAME AS Manager
FROM EMP ep1
LEFT JOIN EMP ep2 ON ep1.MGR = ep2.EMPNO
WHERE ep1.HIREDATE < ep2.HIREDATE;

15、返回销售部(sales)所有员工的姓名。

SELECT ep.ENAME
FROM EMP epLEFT JOIN DEPT dp ON dp.DEPTNO = ep.DEPTNO
WHERE dp.DNAME = 'SALES';

16、返回与SCOTT从事相同工作的员工。

select ep2.ENAME
from EMP ep1inner join EMP ep2 on ep1.JOB = ep2.JOB
where ep1.ENAME = 'SCOTT' and ep2.ENAME != 'SCOTT';

17、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

SELECT d.DEPTNO, d.DNAME, d.LOC, COUNT(e.EMPNO) AS EMP_COUNT
FROM DEPT dLEFT JOIN EMP e ON d.DEPTNO = e.DEPTNO
GROUP BY d.DEPTNO, d.DNAME, d.LOC;

18、计算出员工的年薪，并且以年薪排序。

SELECT EMPNO, ENAME, JOB, SAL, (SAL * 12 + IFNULL(COMM, 0)) AS ANNUAL_SALARY
FROM EMP
ORDER BY ANNUAL_SALARY DESC;

19、返回工资处于第四级别的员工的姓名。

SELECT e.ENAME
FROM EMP eJOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
WHERE s.GRADE = 4;

20、工资等级多于smith的员工信息。

SELECT e.*
FROM EMP eJOIN SALGRADE s1 ON e.SAL BETWEEN s1.LOSAL AND s1.HISALJOIN EMP smith ON smith.ENAME = 'SMITH'JOIN SALGRADE s2 ON smith.SAL BETWEEN s2.LOSAL AND s2.HISAL
WHERE s1.GRADE > s2.GRADE;

21、创建一个存储过程proc_pager实现通用分页功能处理，具体要求如下：

提供如下输入参数变量：

表名p_table_name , 查询字段p_fields , 分页单位 p_page_size

当前页 p_curr_page , where条件p_where_string , 排序条件p_order_string

输出参数变量：

总记录数p_out_counts

DELIMITER //CREATE PROCEDURE proc_pager(IN p_table_name VARCHAR(64),IN p_fields VARCHAR(255),IN p_page_size INT,IN p_curr_page INT,IN p_where_string VARCHAR(255),IN p_order_string VARCHAR(255),OUT p_out_counts INT
)
BEGINDECLARE v_offset INT;DECLARE v_sql_query TEXT;DECLARE v_sql_count_query TEXT;-- 计算偏移量SET v_offset = (p_curr_page - 1) * p_page_size;-- 构建查询总记录数的SQL语句SET v_sql_count_query = CONCAT('SELECT COUNT(*) INTO @total_counts FROM ', p_table_name, ' WHERE ', p_where_string);-- 准备并执行查询总记录数的SQL语句SET @sql_count_query = v_sql_count_query;PREPARE count_stmt FROM @sql_count_query;EXECUTE count_stmt;DEALLOCATE PREPARE count_stmt;-- 将总记录数赋值给输出变量SET p_out_counts = @total_counts;-- 构建分页查询的SQL语句SET v_sql_query =CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' WHERE ', p_where_string, ' ORDER BY ', p_order_string,' LIMIT ', v_offset, ', ', p_page_size);-- 准备并执行分页查询的SQL语句SET @sql_query = v_sql_query;PREPARE stmt FROM @sql_query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END //DELIMITER ;

-- 定义一个变量来存储输出的总记录数
SET @total_counts = 0;-- 调用存储过程
CALL proc_pager('EMP', -- 表名'EMPNO, ENAME, JOB', -- 查询字段5, -- 每页记录数1, -- 当前页码'1=1', -- where 条件（这里没有实际条件，所以用 '1=1'）'EMPNO ASC', -- 排序条件@total_counts -- 输出参数);-- 查询输出参数的值
SELECT @total_counts;