mysql练习

练习
使用简单查询语句完成
1.显示所有部门名称
2.显示所有雇员及其全年收入13薪（工作+补助）

SELECT * FROM dept
SELECT * FROM emp

1.显示所有部门名称
SELECT dname FROM dept
2.显示所有雇员名及年收入13薪
SELECT ename , (sal + comm)*13 AS `money`
	FROM emp
SELECT ename , (sal + IFNULL(comm,0))*13 AS `money` FROM emp
3.显示工资超过2850的雇员及工资
SELECT ename, sal
	FROM emp
	WHERE sal > 2850
4.显示工资不在1500到2850之间
SELECT ename, sal
	FROM emp
	WHERE sal <1500 OR sal >2850 
SELECT ename, sal
	FROM emp
	WHERE NOT (sal >=1500 AND sal <=2850) 
5.显示编号为。。。
SELECT ename, sal
	FROM emp
	WHERE empno = 7566
6.显示部门编号为10和30并且薪水大于
SELECT ename,sal
	FROM emp
	WHERE 
	(deptno = 10 OR deptno = 30) AND
	sal > 1500
7.没有管理者的人
SELECT ename,job
	FROM emp
	WHERE mgr IS NULL 
8.这个时间段入职，并按时间排序
SELECT ename,job,hiredate
	FROM emp
	WHERE hiredate >= '1991-02-01' AND 
	hiredate <= '1991-05-01'
	ORDER BY hiredate
9.降序排序
SELECT ename,sal,comm
	FROM emp
	ORDER BY sal DESC

SELECT * FROM emp
1.部门号为30
SELECT *
	FROM emp
	WHERE deptno = 30 
2.工作为clerk
SELECT ename,empno,deptno
	FROM emp
	WHERE job = 'clerk'
3.奖金高于工资
SELECT *
	FROM emp 
	WHERE comm > sal
4.奖金大于工资的60%
SELECT *
	FROM emp
	WHERE comm > sal*0.6
5.	部门号为10且是manager，部门号为30且clerk
SELECT * 
	FROM emp
	WHERE 
	(deptno = 10 AND job = 'manager')
	OR
	(deptno = 30 AND job = 'clerk')
6.asdf
SELECT * 
	FROM emp
	WHERE 
	(deptno = 10 AND job = 'manager')
	OR
	(deptno = 20 AND job = 'clerk')
	OR
	(job != 'manager' AND job != 'clerk' AND sal >= 2000)
7.有奖金的工作岗位
SELECT DISTINCT job
	FROM emp
	WHERE comm IS NOT NULL
8.没有奖金或奖金小于1000
SELECT *
	FROM emp
	WHERE comm IS NULL OR IFNULL(comm,0) < 1000
9.干了12年以上的
SELECT *
	FROM emp
	WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) <NOW()
10.首字母小写
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
	FROM emp
11.名字字符数为5
SELECT *
	FROM emp
	WHERE LENGTH(ename)=5

SELECT * FROM emp
1.显示不带r的名字
SELECT *
	FROM emp
	WHERE ename NOT LIKE '%r%'
2.显示名字前三个字符
SELECT LEFT(ename,3)
	FROM emp	
3.代替
SELECT REPLACE(ename,'A','a')
	FROM emp
4.工龄大于10
SELECT ename,hiredate
	FROM emp
	WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW()
5.按名字排序
SELECT *
	FROM emp
	ORDER BY ename
6.按工资排序
SELECT ename,hiredate
	FROM emp
	ORDER BY hiredate
7.先按照工作排序，在按照薪水
SELECT ename,job,sal
	FROM emp
	ORDER BY job DESC ,sal
8.日薪
SELECT ename,FLOOR(sal/30),sal/30
	FROM emp
9.2月雇佣
SELECT *
	FROM emp
	WHERE MONTH(hiredate) = 2
10.显示加入公司到现在的日数
SELECT ename,DATEDIFF(NOW(),hiredate)
	FROM emp
11.干了多少年多少月多少日
SELECT ename,
	FLOOR(DATEDIFF(NOW(),hiredate)/365),
	FLOOR((DATEDIFF(NOW(),hiredate) %365)/31),
	DATEDIFF(NOW(),hiredate)%31
	FROM emp

1.至少有一个员工的所有部门
SELECT COUNT(*) AS a , deptno
	FROM emp
	GROUP BY deptno
	HAVING a > 1
2.薪水比他高的员工
SELECT sal
	FROM emp
	WHERE `ename` = 'smith'

SELECT *
	FROM emp
	WHERE sal > (
		SELECT sal
		FROM emp
		WHERE `ename` = 'smith' )
3.下级比上级来的晚
SELECT *
	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.所有‘clerk’的名字及其员工名
SELECT ename,dname,job
	FROM emp,dept
	WHERE job = 'clerk' AND emp.deptno = dept.deptno
6.工资大于1500的工作
SELECT MIN(sal) AS min_sal ,job
	FROM emp
	GROUP BY job
	HAVING min_sal>1500
7.列出在‘sales’部门工作的所有员工
SELECT deptno
	FROM dept
	WHERE dname = 'sales'

SELECT *
	FROM emp
	WHERE deptno = (
		SELECT deptno
		FROM dept
		WHERE dname = 'sales')

SELECT ename , dname
	FROM emp,dept
	WHERE emp.deptno = dept.deptno AND dname = 'sales'
8.列出工资高于平均工资
SELECT *
	FROM emp
	WHERE sal > (
		SELECT  AVG(sal)
		FROM emp
		)

1.和这个人工作内容相同的所有员工
SELECT job
	FROM emp
	WHERE ename = 'scott'
	
SELECT *
	FROM emp
	WHERE job = (
		SELECT job
		FROM emp
		WHERE ename = 'scott'
		)
2.工资高于30号部门的人
SELECT MAX(sal)
	FROM emp
	WHERE deptno =30

SELECT *
	FROM emp
	WHERE sal>(
		SELECT MAX(sal)
		FROM emp
		WHERE deptno =30
		)
3.每个部门工作人员数量，平均工资，平均服务时间
SELECT COUNT(*),AVG(sal),
	AVG(DATEDIFF(NOW(),hiredate)),
	deptno
	FROM emp
	GROUP BY deptno
4.查看所有
SELECT *
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
5.每种岗位，最少钱
SELECT MIN(sal) ,job
	FROM emp
	GROUP BY job
6.最少钱的经理
SELECT MIN(sal),job
	FROM emp
	WHERE job = 'manager'
7.年工资，排序
SELECT ename,(sal+IFNULL(comm,0))*12 AS year_sal
	FROM emp
	ORDER BY year_sal