视频链接：韩顺平循序渐进学Java零基础
第24章 零基础学MySQL 章节练习题- 建表语句
- 章节作业2
- 章节作业3
- 章节作业4
- 章节作业5
- 章节作业6
- 章节作业7、8
-- MySQL dump 10.13 Distrib 5.7.36, for Win64 (x86_64) -- -- Host: localhost Database: temp -- ------------------------------------------------------ -- Server version 5.7.36 ; ; ; ; ; ; ; ; ; ; -- -- Table structure for table `emp` -- DROp TABLE IF EXISTS `emp`; ; ; CREATE TABLE `emp` ( `empno` mediumint(8) unsigned NOT NULL DEFAULT '0', `ename` varchar(20) NOT NULL DEFAULT '', `job` varchar(9) NOT NULL DEFAULT '', `mgr` mediumint(8) unsigned DEFAULT NULL, `hiredate` date NOT NULL, `sal` decimal(7,2) NOT NULL, `comm` decimal(7,2) DEFAULT NULL, `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `emp` -- LOCK TABLES `emp` WRITE; ; INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1991-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1991-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1991-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1991-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1991-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1991-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1997-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1991-09-08',1500.00,NULL,30),(7900,'JAMES','CLERK',7698,'1991-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1991-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1992-01-23',1300.00,NULL,10); ; UNLOCK TABLES; -- -- Table structure for table `salgrade` -- DROP TABLE IF EXISTS `salgrade`; ; ; CREATE TABLE `salgrade` ( `grade` mediumint(8) unsigned NOT NULL DEFAULT '0', `losal` decimal(17,2) NOT NULL, `hisal` decimal(17,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `salgrade` -- LOCK TABLES `salgrade` WRITE; ; INSERT INTO `salgrade` VALUES (1,700.00,1200.00),(2,1201.00,1400.00),(3,1401.00,2000.00),(4,2001.00,3000.00),(5,3001.00,9999.00); ; UNLOCK TABLES; -- -- Table structure for table `dept` -- DROP TABLE IF EXISTS `dept`; ; ; CREATE TABLE `dept` ( `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0', `dname` varchar(20) NOT NULL DEFAULT '', `loc` varchar(13) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `dept` -- LOCK TABLES `dept` WRITE; ; INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON'); ; UNLOCK TABLES; ; ; ; ; ; ; ; ; -- Dump completed on 2021-11-07 19:12:42章节作业2
-- 2.1 查看dept表和emp表的结构 DESC emp DESC dept -- 2.2 显示所有部门名称 SELECT dname FROM dept -- 2.3 显示所有雇员名及其全年收入13月（工资+补助），并指定列别名“年收入” SELECt ename,(sal+IF(comm IS NULL,0.0,comm))*13 AS '年收入' FROM emp -- 2.4 显示工资超过2850的雇员姓名和工资 SELECt ename,sal FROM emp WHERe sal > 2850 -- 2.5 显示工资不在1500~2850之间的所有雇员名及工资 SELECt ename,sal FROM emp WHERe sal NOT BETWEEN 1500 AND 2850 -- 2.6 显示编号为7566的雇员姓名及所在部门编号 SELECt ename,dname FROM emp,dept WHERe empno = 7566 AND emp.deptno = dept.deptno -- 2.7 显示部门为10和30中员工工资超过1500的雇员名及工资 SELECt ename,sal FROM emp WHERe sal > 1500 AND deptno IN(10,30) -- 2.8 显示无管理者的雇员名及工资 SELECt ename,job FROM emp WHERe mgr IS NULL -- 2.9 显示在1991-2-1到1991-5-1之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序 SELECt ename,job,hiredate FROM emp WHERe hiredate >= '1991-2-1' AND hiredate <= '1991-5-1' ORDER BY hiredate -- 显示获得补助的所有雇员名、工资及补助，并以工资降序排序 SELECt ename,sal,comm FROM emp WHERe comm IS NOT NULL ORDER BY sal DESC章节作业3
-- 3.1 选择部门30中的所有员工
SELECt ename FROM emp WHERe deptno = 30
-- 3.2 列出所有办事员(CLERK)的姓名、编号及部门编号
SELECt ename,empno,deptno FROM emp WHERe job='CLERK'
-- 3.3 找出雇佣金高于薪金的员工
SELECt * FROM emp WHERe IFNULL(comm,0.0)>sal
-- 3.4 找出佣金高于薪金60%的员工
SELECt * FROM emp WHERe IFNULL(comm,0.0) > sal*0.6
-- 3.5 找出部门10中所有的经理（MANAGER）和部门20中所有的办事员（CLERK）的详细资料
SELECt * FROM emp WHERe (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK')
-- 3.6 找出部门10中所有经理，部门20中所有办事员，还有既不是经理也不是办事员但其佣金大于或等于2000的所有员工的详细资料
SELECt * FROM emp WHERe (deptno=30 AND job='MANAGER') OR (deptno=20 AND job='CLERK') OR (job NOT IN('MANAGER','CLERK') AND sal >= 2000)
-- 3.7 找出有奖金的员工的工作类型
SELECt DISTINCT job FROM emp WHERe comm IS NOT NULL
-- 3.8 找出没有奖金或奖金金额小于100的员工的雇员名
SELECt ename FROM emp WHERe comm IS NULL OR (comm IS NOT NULL AND comm < 100)
-- 3.9 找出各月倒数第3天受雇的员工
SELECt * FROM emp WHERe DATE_SUB(LAST_DAY(hiredate),INTERVAL 2 DAY)=hiredate
-- 3.10 找出早于12年前受雇的员工
SELECt * FROM emp WHERe hiredate < DATE_SUB(NOW(),INTERVAL 12 YEAR)
-- 3.11 以首字母小写的方式显示所有员工的姓名
SELECt CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp
-- 3.12 显示正好为5个字符的员工的姓名
SELECt ename FROM emp WHERe LENGTH(ename)=5
章节作业4
-- 4.1 显示不带'R'字母的员工的姓名 SELECt ename FROM emp WHERe INSTR(ename,'R')=0 -- 4.2 显示所有员工姓名的前三个字符 SELECt SUBSTRING(ename,1,3) FROM emp -- 4.3 显示所有员工的姓名，用a替换所有的A SELECt REPLACE(ename,'A','a') FROM emp -- 4.4 显示满10年服务年限的员工的姓名和受雇日期 SELECt ename,hiredate FROM emp WHERe DATE_SUB(NOW(),INTERVAL 10 YEAR)>hiredate -- 4.5 显示员工的详细资料，按姓名排序 SELECt * FROM emp ORDER BY ename -- 4.6 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面 SELECt ename,hiredate FROM emp ORDER BY hiredate -- 4.7 显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序 SELECt ename,job,sal FROM emp ORDER BY job,sal -- 4.8 显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将按年份升序排序 SELECt ename,YEAR(hiredate) AS `year`,MONTH(hiredate) AS `month` FROM emp ORDER BY `month`,`year` -- 4.9 显示一个月为30天的员工的日薪金，单位为元 SELECt ename,FLOOR(sal/30) AS day_pay FROM emp WHERe DAY(LAST_DAY(hiredate))=30 -- 4.10 找出所有年份中2月受聘的员工 SELECt ename FROM emp WHERe MONTH(hiredate)=2 -- 4.11 显示每个员工加入公司的天数，按天数降序排列 SELECt ename,DATEDIFF(NOW(),hiredate) AS days FROM emp ORDER BY days DESC -- 4.12 显示员工姓名中含有A的所有员工的姓名 SELECt ename FROM emp WHERe ename LIKE '%A%' -- 4.13 以年月日的方式显示所有员工的服务年限 SELECt ename, FROM_DAYS(DATEDIFF(NOW(),hiredate)) AS service_time FROM emp章节作业5
-- 5.1 列出至少有一个员工的部门 SELECt deptno,dname FROM dept WHERe deptno IN (SELECt DISTINCT deptno FROM emp) -- 5.2 列出薪金比 "SMITH" 高的员工 SELECt ename,sal FROM emp WHERe sal > (SELECt sal FROM emp WHERe ename='SMITH') -- 5.3 列出受雇日期晚于其直接上级的所有员工 SELECt emp1.ename AS 'employee',emp2.ename AS 'leader' FROM emp emp1,emp emp2 WHERe emp1.mgr=emp2.empno AND emp1.hiredate > emp2.hiredate -- 5.4 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 SELECt emp.*,dname FROM emp RIGHT JOIN dept ON dept.deptno=emp.deptno ORDER BY dname -- 5.5 列出所有办事员（CLERK）的姓名及其部门名称 SELECt ename,dname FROM emp,dept WHERe emp.job='CLERK' AND dept.deptno=emp.deptno -- 5.6 列出最低薪金大于1500的各种工作 SELECt DISTINCT job FROM emp WHERe emp.sal > 1500 -- 5.7 列出在销售部门（SALES）工作的员工的姓名 SELECt ename FROM emp,dept WHERe emp.deptno=dept.deptno AND dname='SALES' -- 5.8 列出薪金高于公司平均薪金的员工姓名 SELECt ename FROM emp WHERe sal > (SELECt AVG(sal) FROM emp)章节作业6
-- 6.1 列出与 “SCOTT” 工作相同的所有员工 SELECt ename FROM emp WHERe job = (SELECt job FROM emp WHERe ename = 'SCOTT') AND ename != 'SCOTT' -- 6.2 列出薪金高于30号部门最高工资的其它部门的员工姓名和薪金 SELECt ename,sal FROM emp WHERe sal > (SELECt MAX(sal) FROM emp WHERe deptno = 30) AND deptno != 30 -- 6.3 列出每个部门的员工数量，平均工资和员工平均服务天数 SELECt COUNT(empno) AS numbers_of_dept, AVG(sal) AS avg_sal ,AVG(DATEDIFF(NOW(),hiredate)) AS avg_days FROM emp GROUP BY deptno -- 6.4 列出所有员工的姓名，部门名称及工资 SELECt ename,dname,sal FROM emp,dept WHERe emp.deptno = dept.deptno -- 6.5 列出所有部门的详细信息和部门人数 SELECt dept.*,temp.cnt_emp FROM dept,(SELECt COUNT(empno) AS cnt_emp,deptno FROM emp GROUP BY deptno) AS temp WHERe dept.deptno = temp.deptno -- 6.6 列出各种工作的最低工资 SELECt MIN(sal) AS min_sal, job FROM emp GROUP BY job -- 6.7 列出经理（MANAGER）的最低薪金 SELECt MIN(sal) AS min_sal_manager FROM emp WHERe job = 'MANAGER' -- 6.8 列出所有员工的年薪，按年薪多少升序排列 SELECt ename, (sal*12 + IFNULL(comm,0)) AS year_sal FROM emp ORDER BY year_sal章节作业7、8
- 设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。现要建立关于系、班级、学生的数据库，数据库名为stu_db。关系模式如下：
dept(系)：deptid(系号)，deptname(系名)
class(班)：classid(班号)，subject(专业),deptname(所在系),enrolltime(入学年份),num(人数)
student(学生)：sudid(学号),stuname(姓名),stuage(年龄),classid(班级)
-- MySQL dump 10.13 Distrib 5.7.36, for Win64 (x86_64) -- -- Host: localhost Database: stu_db -- ------------------------------------------------------ -- Server version 5.7.36 ; ; ; ; ; ; ; ; ; ; -- -- Current Database: `stu_db` -- CREATE DATAbase `stu_db` ; USE `stu_db`; -- -- Table structure for table `class` -- DROp TABLE IF EXISTS `class`; ; ; CREATE TABLE `class` ( `classid` int(11) NOT NULL, `subject` varchar(32) DEFAULT NULL, `deptname` varchar(32) DEFAULT NULL, `enrolltime` year(4) DEFAULT NULL, `num` int(11) DEFAULT NULL, PRIMARY KEY (`classid`), KEY `deptname` (`deptname`), CONSTRAINT `class_ibfk_1` FOREIGN KEY (`deptname`) REFERENCES `dept` (`deptname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `class` -- LOCK TABLES `class` WRITE; ; 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); ; UNLOCK TABLES; -- -- Table structure for table `dept` -- DROP TABLE IF EXISTS `dept`; ; ; CREATE TABLE `dept` ( `deptid` int(11) NOT NULL, `deptname` varchar(32) DEFAULT NULL, PRIMARY KEY (`deptid`), UNIQUE KEY `deptname` (`deptname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `dept` -- LOCK TABLES `dept` WRITE; ; INSERT INTO `dept` VALUES (4,'中文'),(3,'化学'),(1,'数学'),(5,'经济'),(2,'计算机'); ; UNLOCK TABLES; -- -- Table structure for table `student` -- DROP TABLE IF EXISTS `student`; ; ; CREATE TABLE `student` ( `stuid` int(11) NOT NULL, `stuname` varchar(32) NOT NULL, `stuage` smallint(6) DEFAULT NULL, `classid` int(11) DEFAULT NULL, PRIMARY KEY (`stuid`), KEY `classid` (`classid`), CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; ; 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),(8203,'王亮',17,111),(8302,'周瑜',16,112),(8305,'董庆',19,102),(8409,'赵龙',18,101),(8510,'李丽',20,142); ; UNLOCK TABLES; ; ; ; ; ; ; ; ; -- Dump completed on 2021-11-08 15:49:09 -- 1. 找出所有姓李的学生 SELECT * FROM student WHERe stuname LIKE '李%' -- 2. 列出专业数量大于1的系的名字 SELECt COUNT(deptname) AS sub_nums FROM class GROUP BY deptname HAVINg sub_nums>1 -- 3. 列出人数大于等于30的系的编号和名称 SELECt dept.* FROM dept,class WHERe class.num >= 30 AND dept.deptname = class.deptname -- 4. 学校又增加了一个物理系，编号为006 INSERT INTO dept VALUES (6,'物理系') -- 5. 学生张三退学，请更新相关的表 START TRANSACTION UPDATe class SET num = num-1 WHERe classid = (SELECT classid FROM student WHERe stuname = '张三'); DELETe FROM student WHERe stuname = '张三'; COMMIT;
