30天学会JAVA—练习题(2021韩顺平)——Day19

30天学会JAVA—练习题(2021韩顺平)——Day19

数据库练习题

-- 2. 查看表结构
DESC dept
DESC emp

SELECt * FROM emp
-- 3. 简单查询
-- （1）
SELECt dname FROM dept;
-- （2）
SELECt `ename`, (sal*12 + IFNULL(comm,0)) '年收入' FROM emp

-- 4.限制查询
-- （1）
SELECt `ename`, sal FROM emp
	WHERe sal > 2850
	
-- (2)
SELECt `ename`, sal FROM emp
	WHERe sal NOT BETWEEN 1500 AND 2850
	
-- (3)
SELECt `ename`,deptno FROM emp
	WHERe empno = 7566

-- (4)
SELECt `ename`,sal FROM emp
	WHERe (deptno = 10 OR deptno = 30)
	AND sal > 1500

-- (5)
SELECt `ename`,job FROM emp
	WHERe mgr IS NULL

-- 5. 排序数据
-- (1)
SELECt `ename`, job, hiredate FROM emp
	WHERe hiredate BETWEEN '1991-02-01' AND '1991-05-01'
	ORDER BY hiredate
-- (2)
SELECt `ename`,sal,comm FROM emp
	WHERe comm IS NOT NULL
	ORDER BY sal DESC

-- 6. 
-- (1)
SELECt * FROM emp	
	WHERe deptno = 30

-- (2)
SELECt `ename`,empno,deptno FROM emp
	WHERe job = 'CLERK'

-- (3)
SELECt * FROM emp
	WHERe comm > sal
	
-- (4)
SELECt * FROM emp
	WHERe comm > sal*0.6
	
-- (5)韩老师未去重
SELECt * FROM emp
	WHERe deptno = 10 AND job = 'MANAGER'
UNIOn
SELECt * FROM emp
	WHERe deptno = 20 AND job = 'CLERK'
	
-- (6)韩老师未去重
SELECt * FROM emp
	WHERe deptno = 10 AND job = 'MANAGER'
UNIOn
SELECt * FROM emp
	WHERe deptno = 20 AND job = 'CLERK'
UNIOn
SELECt * FROM emp
	WHERe ((job != 'MANAGER') AND (job != 'CLERK')) AND sal >= 2000
	
-- (7)
SELECt DISTINCT job FROM emp
	WHERe comm IS NOT NULL
	
-- (8)
SELECt * FROM emp
	WHERe (comm IS NULL) OR (comm < 100)

-- 日期的使用
-- ------(9)找出各月倒数第3天受雇的所有员工.
-- 老韩提示: last_day(日期)，
-- 可以返回该日期所在月份的最后一天
-- last_day(日期) - 2 得到日期所有月份的倒数第3天
SELECt * FROM emp
	WHERe LAST_DAY(hiredate) - 2  =  hiredate

-- !(10)找出早于12年前受雇的员工.（即： 入职时间超过12年） 直接加年即可
SELECt * FROM emp
	WHERe DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW()
	
-- !字符串的使用
-- (11)以首字母小写的方式显示所有员工的姓名
SELECt CONCAT(LCASE(LEFT(ename,1)), SUBSTR(ename,1,LENGTH(ename))) FROM emp
	
-- (12)显示正好为5个字符的员工的姓名
SELECt * FROM emp
	WHERe LENGTH(ename) = 5
	
-- (13)显示不带有"R"的员工的姓名.
SELECt * 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,sal

-- (20)
SELECt ename, CONCAT(YEAR(hiredate),'-',MONTH(hiredate)) FROM emp
ORDER BY MONTH(hiredate),YEAR(hiredate)

-- (21)忽略余数 FLOOR
SELECt ename, FLOOR(sal/30) daysal FROM emp

-- （22）
SELECt * FROM emp
	WHERe MONTH(hiredate)=2

-- (23)
SELECt ename,DATEDIFF(NOW(),hiredate) FROM emp

-- (24)显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECt * FROM emp
WHERe ename LIKE '%A%'

-- (25)以年月日的方式显示所有员工的服务年限
-- 思路 1. 先求出 工作了多少天 
SELECt ename, FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS " 工作年 ", 
	FLOOR((DATEDIFF(NOW(), hiredate) % 365) / 31) AS " 工作月 ",
	DATEDIFF(NOW(), hiredate) % 31 AS " 工作天"
	FROM emp;

-- 7.
-- (1)
SELECt COUNT(*) AS c, deptno FROM emp 
	GROUP BY deptno 
	HAVINg c > 1
	
-- (2)
SELECt * FROM emp
	WHERe sal > (
	SELECt sal FROM emp
	WHERe `ename`= 'SMITH')
	
-- (3)
SELECt worker.ename AS '员工名', worker.hiredate AS '员工入职时间',leader.ename  AS '上级名', leader.hiredate AS '上级入职时间' FROM emp worker , emp leader
	WHERe worker.hiredate > leader.hiredate 
	AND worker.mgr = leader.empno;
	
-- (4)
SELECt dname, emp.* FROM dept LEFT JOIN emp 
ON dept.deptno = emp.deptno

-- (5)
SELECt ename, dname, job FROM emp,dept
WHERe emp.deptno = dept.deptno
AND emp.job = 'CLERK'

-- (6)
SELECt job, MIN(sal) FROM emp
	GROUP BY job
	HAVINg MIN(sal) > 1500
	
-- (7)
SELECt `ename` FROM emp,dept
	WHERe emp.deptno =dept.deptno
	AND dept.dname = 'SALES'
	
-- (8)
SELECt * FROM emp
	WHERe sal > (
	SELECt AVG(sal) FROM emp)

-- (9)
SELECt * FROM emp
	WHERe job = (SELECt job FROM emp
	WHERe ename = 'SCOTT')
	AND ename !='SCOTT'	
	
-- (10)
SELECt `ename`,sal FROM emp	
	WHERe sal > (SELECt MAX(sal) FROM emp
	WHERe deptno = 30)
	
-- (11)老师的没有去重哦
SELECt COUNT(DISTINCT empno) '员工数量', AVG(sal) '平均工资', AVG(hiredate) '平均服务期限', deptno '部门' FROM emp
	GROUP BY deptno
SELECt * FROM emp;

-- (12)
SELECt `ename`,dname,sal FROM emp, dept
	WHERe dept.deptno = emp.deptno

-- （13）列出所有部门的详细信息和部门人数。看成临时表 和 dept表联合查询
SELECt dept.*, tmp.c AS "部门人数" FROM dept, (
		SELECt deptno,COUNT(DISTINCT emp.empno) c FROM emp
	GROUP BY deptno
	) tmp 
	WHERe dept.deptno = tmp.deptno

-- (14)
SELECt MIN(sal) FROM emp
GROUP BY job 

-- (15)
SELECt MIN(sal) FROM emp
WHERe job = 'MANAGER'

-- (16)
SELECt ename, (sal + IFNULL(comm,0))*12 year_sal
FROM emp
ORDER BY year_sal