MySQL基础——练习

-- 练习
USE db2;

-- 3.使用简单查询语句完成:
--   (1)显示所有部门名称。
--   (2)显示所有雇员名及其全年收入：13月(工资+补助)，并指定列别名"年收入"
SELECT dname FROM dept;
-- SELECT ename, IFNULL((sal + comm) * 13, sal * 13) AS '年收入' FROM emp;
SELECT ename, (sal + IFNULL(comm, 0)) * 13 AS '年收入' FROM emp;

-- 4.限制查询数据。
--   (1)显示工资超过 2850 的雇员姓名和工资。
--   (2)显示工资不在 1500 到 2850 之间的所有雇员名及工资。
--   (3)显示编号为 7566 的雇员姓名及所在部门编号。
--   (4)显示部门 10 和 30 中工资超过 1500 的雇员名及工资。
--   (5)显示无管理者的雇员名及岗位。
SELECT ename, sal FROM emp WHERE sal > 2850;
SELECT ename, sal FROM emp WHERE sal < 1500 OR sal > 2850;
SELECT ename, deptno FROM emp WHERE empno = 7566;
SELECT ename, sal FROM emp WHERE deptno IN (10, 30) AND sal > 1500;
SELECT ename, job FROM emp WHERE mgr IS NULL;

-- 5.排序数据。
--   (1)显示在 1991年2月1日 到 1991年5月1日 之间雇用的雇员名,岗位及雇佣日期,并以雇佣日期进行排序
--   (2)显示获得补助的所有雇员名,工资及补助,并以工资降序排序
SELECT ename, job, hiredate FROM emp WHERE hiredate BETWEEN '1991-02-01' AND '1991-05-01' ORDER BY hiredate;
SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC;

-- 练习
USE db2;

-- 1.选择部门 30 中的所有员工.
SELECT * FROM emp WHERE deptno = 30;

-- 2.列出所有 办事员(CLERK) 的姓名，编号和部门编号.
SELECT ename, empno, deptno FROM emp WHERE job = 'CLERK';

-- 3.找出佣金高于薪金的员工.
SELECT ename, sal, comm FROM emp WHERE IFNULL(comm, 0) > sal;

-- 4.找出佣金高于薪金 60% 的员工.
SELECT ename, sal, comm FROM emp WHERE IFNULL(comm, 0) > (sal * 0.6);

-- 5.找出部门 10 中所有 经理(MANAGER) 和部门 20 中所有 办事员(CLERK)的详细资料.
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') 

-- 6.找出部门 10 中所有 经理(MANAGER),部门 20 中所有 办事员(CLERK)，
--   还有既不是 经理 又不是 办事员 但其薪金大于或等于 2000 的所有员工的详细资料.
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK') 
OR (job NOT IN ('MANAGER', 'CLERK') AND sal >= 2000);

-- 7.找出收取佣金的员工的不同工作.
SELECT distinct job FROM emp WHERE comm IS NOT NULL;

-- 8.找出不收取佣金或收取的佣金低于 100 的员工.
SELECT * FROM emp WHERE (comm IS NULL) OR (IFNULL(comm, 0) < 100);

-- 9.找出各月倒数第 3 天受雇的所有员工.
SELECT ename, hiredate FROM emp WHERE hiredate + 2 = LAST_DAY(hiredate);

-- 10.找出早于 30年前 受雇的员工.
SELECT ename, hiredate, DATE_ADD(hiredate, INTERVAL 30 YEAR) AS '30年后的日期' FROM emp 
WHERE DATE_ADD(hiredate, INTERVAL 30 YEAR) < NOW();

-- 11.以首字母小写的方式显示所有员工的姓名.
SELECT  CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) FROM emp;

-- 12.显示正好为 5 个字符的员工的姓名.
SELECT ename FROM emp WHERE LENGTH(ename) = 5;

-- 13.显示不带有 "R" 的员工的姓名.
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';

-- 14.显示所有员工姓名的前三个字符.
SELECT LEFT(ename, 3) FROM emp;

-- 15.显示所有员工的姓名,用 a 替换所有 "A"
SELECT REPLACE(ename, 'A', 'a') FROM emp;

-- 16.显示满 10 年服务年限的员工的姓名和受雇日期.
SELECT ename, hiredate FROM emp WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) < NOW();

-- 17.显示员工的详细资料,按姓名排序.
SELECT * FROM emp ORDER BY ename;

-- 18.显示员工的姓名和受雇日期,根据其服务年限将最老的员工排在最前面.
SELECT ename, hiredate FROM emp ORDER BY hiredate;

-- 19.显示所有员工的姓名、工作和薪金,按工作降序排序,若工作相同则按薪金排序.
SELECT ename, job, sal FROM emp ORDER BY job DESC,sal;

-- 20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
SELECT ename, YEAR(hiredate), MONTH(hiredate) FROM emp ORDER BY MONTH(hiredate),YEAR(hiredate);

-- 21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT ename, FLOOR(sal / 30) AS '日薪' FROM emp;

-- 22.找出在(任何年份的)2月受聘的所有员工。
SELECT ename, hiredate FROM emp WHERE MONTH(hiredate) = 2;

-- 23.对于每个员工,显示其加入公司的天数.
SELECT DATEDIFF(NOW(), hiredate) FROM emp;

-- 24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ename FROM emp WHERE ename LIKE '%A%';

-- 25.以年月日的方式显示所有员工的服务年限．(大概)
SELECT FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS '年', FLOOR((DATEDIFF(NOW(), hiredate) % 365) / 31) AS '月', 
(DATEDIFF(NOW(), hiredate) % 31) AS '日' FROM emp;

-- 练习
USE db2;

-- 1.列出至少有一个员工的所有部门
SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno HAVING c > 1;

-- 2.列出薪金比 “SMITH” 多的所有员工。
SELECT ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

-- 3.列出受雇日期晚于其直接上级的所有员工。
SELECT worker.hiredate, worker.ename, boss.hiredate AS mgr_hiredate, boss.ename AS mgr_ename FROM emp worker, emp boss 
WHERE worker.mgr = boss.empno AND worker.hiredate > boss.hiredate;


-- 4.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT dept.deptno, empno, ename, sal, job, hiredate FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

-- 5.列出所有 “CLERK”(办事员) 的姓名及其部门名称。
SELECT ename, dname, job FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.job = 'CLERK';

-- 6.列出最低薪金大于1500的各种工作。
-- SELECT MIN(sal) FROM emp GROUP BY job;
SELECT job, MIN(sal) AS min_sal FROM emp GROUP BY job HAVING min_sal > 1500;

-- 7.列出在部门 “SALES” (销售部)工作的员工的姓名。
SELECT ename, deptno FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

-- 8.列出薪金高于公司平均薪金的所有员工。
SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

-- 9.列出与 “SCOTT” 从事相同工作的所有员工。
SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT');

-- 10.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

-- 11.列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT deptno, COUNT(*) AS emp_num, AVG(sal) AS avg_sal, AVG(DATEDIFF(NOW(), hiredate)) AS avg_date FROM emp GROUP BY deptno;

-- 12.列出所有员工的姓名、部门名称和工资。
SELECT ename, dname, sal FROM emp, dept WHERE emp.deptno = dept.deptno;

-- 13.列出所有部门的详细信息和部门人数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; -- 这个查询结果可以看成临时表

SELECT dept.deptno, dname, loc, temp.emp_num FROM dept, (SELECT deptno, COUNT(*) AS emp_num FROM emp GROUP BY deptno) temp 
WHERE dept.deptno = temp.deptno;



-- 14. 列出各种工作的最低工资。
SELECT MIN(sal), job FROM emp GROUP BY job;

-- 15.列出MANAGER(经理)的最低薪金。
SELECT MIN(sal) FROM emp WHERE job = 'MANAGER';

-- 16.列出所有员工的年工资,按年薪从低到高排序。
SELECT ename, (sal + IFNULL(comm, 0)) * 12 AS sal_year FROM emp ORDER BY sal_year DESC;

-- 设学校环境如下:一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生
-- 现要建立关于系、学生、班级的数据库，关系模式为:
-- 班 CLASS (班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)
-- 学生 STUDENT(学号studentid，姓名name，年龄age，班号classid)
-- 系 DEPARTMENT (系号departmentid，系名deptname)
-- (1) 建表，在定义中要求声明:
--    (1) 每个表的主外码。
--    (2) deptname是唯一约束。
--    (3) 学生姓名不能为空。
-- (2)插入数据

-- 系名
CREATE TABLE department(departmentid VARCHAR(32) PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL);

-- 班级
CREATE TABLE class(
classid INT PRIMARY KEY, 
subject VARCHAR(32) NOT NULL DEFAULT '', 
deptname VARCHAR(32), 
enrolltime INT NOT NULL DEFAULT 2000, 
num INT NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES department(deptname));

-- 学生
CREATE TABLE student(studentid INT PRIMARY KEY, 
`name` VARCHAR(32) NOT NULL DEFAULT '', 
age INT NOT NULL DEFAULT 0, 
classid INT,
FOREIGN KEY (classid) REFERENCES class(classid));

-- 插入系数据
INSERT INTO department VALUES (001, '数学'),(002, '计算机'),(003, '化学'),(004, '中文'),(005, '经济');
SELECT * FROM department;

INSERT INTO class VALUES 
(101, '软件', '计算机', 1995, 20),
(102, '微电子', '计算机', 1996, 30),
(111, '无机化学', '化学', 1995, 29),
(112, '高分子化学', '化学', 1996, 25),
(121, '统计数学', '数学', 1995, 20),
(131, '现代语言', '中文', 1996, 20),
(141, '国际贸易', '经济', 1997, 30),
(142, '国际金融', '经济', 1996, 14);
SELECT * FROM class;

INSERT INTO student VALUES 
(8101, '张三', 18, 101),
(8102, '钱四', 16, 121),
(8103, '王玲', 17, 131),
(8105, '李飞', 19, 102),
(8109, '赵四', 18, 141),
(8110, '李可', 20, 142),
(8201, '张飞', 18, 111),
(8302, '周瑜', 16, 112),
(8203, '王亮', 17, 111),
(8305, '董庆', 19, 102),
(8409, '赵龙', 18, 101),
(8510, '李丽', 20, 142);
SELECT * FROM student;


-- (3)完成以下查询功能
--    3.1找出所有姓李的学生。
--    3.2列出所有开设超过1个专业的系的名字。
--    3.3列出人数大于等于30的系的编号和名字。
SELECT * FROM student WHERE `name` LIKE '李%';

SELECT deptname, COUNT(*) FROM class GROUP BY deptname HAVING COUNT(*) > 1;

-- 先查询人数大于 30 的系的名字
SELECT deptname, SUM(num) FROM class GROUP BY deptname HAVING SUM(num) > 30;
-- 将以上的表当作临时表
SELECT department.deptname, departmentid, sum_num
FROM department, (SELECT deptname, SUM(num) AS sum_num FROM class GROUP BY deptname HAVING SUM(num) > 30) temp
WHERE department.deptname = temp.deptname;

-- (4)学校又新增加了一个物理系，编号为006
INSERT INTO department VALUES(006, '物理');
SELECT * FROM department;

-- (5)学生张三退学，请更新相关的表
-- 分析：1. 张三所在班级人数 -1
--       2. 将张三从学生表中删除
--       3. 使用事务
START TRANSACTION;
SAVEPOINT a;
UPDATE class SET num = (num - 1) WHERE classid = (SELECT classid FROM student WHERE `name` = '张三');
DELETE FROM student WHERE `name` = '张三';
COMMIT;

SELECT * FROM class;
SELECT * FROM student;