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