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 EMP
    ADD 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 EMP
   ADD CONSTRAINT FK_DEPTNO
       FOREIGN 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 < 100
  or 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 ep1
        JOIN 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 ep1
        LEFT 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 ep
        LEFT JOIN DEPT dp ON dp.DEPTNO = ep.DEPTNO
WHERE dp.DNAME = 'SALES';

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

select ep2.ENAME
from EMP ep1
         inner 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 d
         LEFT 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 e
        JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL
WHERE s.GRADE = 4;

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

SELECT e.*
FROM EMP e
         JOIN SALGRADE s1 ON e.SAL BETWEEN s1.LOSAL AND s1.HISAL
         JOIN 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
)
BEGIN
    DECLARE 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;