MySQL_小练习

1）所有部门名称  
SELECT
	dname 
FROM
	dept

2）所有雇员名及全年收入（工资+补助） ，并指定列别名“年收入”
SELECT
	ename,
	( sal + ifnull( comm, 0 ) ) * 12 "年收入" 
FROM
	emp

3）存在雇员的所有部门号
SELECT DISTINCT
	deptno 
FROM
	emp;
	
1）工资超过2850的雇员姓名和工资
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal > 2850

2）工资不在1500到2850之间的所有雇员名及工资  
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal NOT BETWEEN 1500 
	AND 2850

3）代码为7566的雇员姓名及所在部门代码 
SELECT
	ename,
	deptno 
FROM
	emp 
WHERE
	empno = 7566
 
4）部门10和30中工资超过1500的雇员名和工资 
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	deptno IN ( 10, 30 )

5）无管理者的雇员名及岗位  
SELECT
	ename,
	job 
FROM
	emp 
WHERE
	mgr IS NULL;

6)无奖金的雇员的名字及岗位   
SELECT
	ename,
	job 
FROM
	emp 
WHERE
	comm IS NULL;

1）在1981年2月1日~~1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期的先后进行排序
SELECT
	ename,
	job,
	hiredate 
FROM
	emp 
WHERE
	hiredate BETWEEN "1981-2-1" 
	AND "1981-5-1" 
ORDER BY
	hiredate ASC;
	
2）获得补助的所有雇员名，工资及补助金额，并以工资和补助的降序排序
SELECT
	ename,
	sal,
	comm 
FROM
	emp 
WHERE
	comm IS NOT NULL 
ORDER BY
	sal DESC,
	comm DESC;

1）所有雇员的平均工资、总计工资、最高工资、最低工资  
SELECT
	avg( sal ),
	sum( sal ),
	max( sal ),
	min( sal ) 
FROM
	emp;
	
2）每种岗位的雇员总数、平均工资  
SELECT
	count( empno ),
	avg( sal ) 
FROM
	emp 
GROUP BY
	job;

3）雇员总数以及获得补助的雇员数 
SELECT
	count( empno ),
	count( comm ) 
FROM
	emp
 
4）管理者的总人数  
SELECT
	count( DISTINCT mgr ) 
FROM
	emp;
	
1）部门20的部门名以及该部门的所有雇员名、雇员工资及岗位
SELECT
	dname,
	ename,
	sal,
	job 
FROM
	emp,
	dept 
WHERE
	emp.deptno = 20 
	AND emp.deptno = dept.deptno;

2）获得补助的所有雇员名、补助金额及所在部门名
SELECT
	ename,
	comm,
	dname 
FROM
	emp,
	dept 
WHERE
	comm IS NOT NULL 
	AND emp.deptno = dept.deptno;

3）在“DALLAS”工作的所有雇员名、雇员工资及所在部门名
SELECT
	ename,
	sal,
	dname 
FROM
	emp,
	dept 
WHERE
	loc = "DALLAS"
4）雇员SCOTT的管理者名
SELECT
	e2.ename 
FROM
	emp e1,
	emp e2 
WHERE
	e1.ename = "SCOTT" 
	AND e1.mgr = e2.empno;

5）查询EMP表和SALGRADE表，显示部门20的雇员名、工资及其工资级别
SELECT
	ename,
	sal,
	grade 
FROM
	emp,
	salgrade 
WHERE
	deptno = 20 
	AND sal BETWEEN losal 
	AND hisal;

6）部门10的所有雇员名、部门名以及其他部门名
SELECT
	ename,
	dname 
FROM
	emp
	RIGHT JOIN dept ON emp.deptno = 10 
	AND emp.deptno = dept.deptno;
7）部门10的所有雇员名、部门名以及其他雇员名
SELECT
	ename,
	dname 
FROM
	emp
	LEFT JOIN dept ON emp.deptno = dept.deptno 
	AND emp.deptno = 10;
-- 使用子查询显示：
-- 1）BLAKE同部门的所有雇员，单不显示BLAKE
SELECT
	ename 
FROM
	emp 
WHERE
	deptno = ( SELECT deptno FROM emp WHERE ename = "BLAKE" ) 
	AND ename <> "BLAKE";
-- 2）超过平均工资的所有雇员名、工资及部门号
SELECT
	ename,
	sal,
	deptno 
FROM
	emp 
WHERE
	sal > ( SELECT avg( sal ) FROM emp ) 
-- 3）超过部门平均工资的所有雇员名、工资及部门号

SELECT
	ename,
	sal,
	e.deptno 
FROM
	emp e,
	( SELECT deptno, avg( sal ) avg FROM emp GROUP BY deptno ) t2 
WHERE
	e.deptno = t2.deptno 
	AND sal > avg;

-- 4）高于CLERK岗位所有雇员工资的所有雇员名、工资及补助额
SELECT
	ename,
	sal,
	comm 
FROM
	emp 
WHERE
	sal > ALL ( SELECT sal FROM emp WHERE job = "CLERK" )

-- 5）工资、补助额与SCOTT完全一致的所有雇员名、工资及补助额
SELECT
	ename,
	e.sal,
	e.comm 
FROM
	emp e,
	( SELECT sal, comm FROM emp WHERE ename = "SCOTT" ) t2 
WHERE
	e.sal = t2.sal 
	AND e.comm <=> t2.comm 
	AND ename <> "SCOTT";
-- 6）显示部门工资总和高.于雇员工资总和三分之一的部门名和工资总和
SELECT
	dname,
	deptsum 
FROM
	dept,
	( SELECT deptno, sum( sal ) deptsum FROM emp GROUP BY deptno ) t1 
WHERE
	dept.deptno = t1.deptno 
	AND deptsum > ( SELECT sum( sal ) / 3 sum FROM emp )
	
-- 查询emp表第45-50条记录
SELECT
	* 
FROM
	emp 
	LIMIT 44,
	6;

SELECT
	* 
FROM
	( SELECT * FROM emp, rownum r FROM emp WHERE rownum <= 50 ) 
WHERE
	r >= 45;
-- 部门名和每个部门的总人数
SELECT
	dname,
	ifnull( num, 0 ) 
FROM
	dept
	LEFT JOIN ( SELECT deptno, count( * ) num FROM emp GROUP BY deptno ) t1 ON dept.deptno = t1.deptno

-- 创建时间在李四之后的所有用户名和对应的权限名

SELECT NAME
	,
	modelname 
FROM
	scmuser su,
	systemmodel sm,
	usermodel um 
WHERE
	createdate > ( SELECT createdate FROM scmuser WHERE NAME = "lisi" ) 
	AND su.account = um.account 
	AND sm.modelcode = um.modelcode;
	
-- 查询各科成绩最高分最低分
SELECT
	couid,
	max( score ),
	min( score ) 
FROM
	score 
GROUP BY
	couid;
-- 查询学生个数大于3的班级id、该班级所有学生id、姓名和学生个数
SELECT
	stuid,
	stuname,
	stu.classid 
FROM
	student stu,
	( SELECT classid, count( * ) num FROM student GROUP BY classid ) t1 
WHERE
	t1.classid = stu.classid 
	AND t1.num >3
-- 查询平均成绩大于60分的学生姓名、学好和平均成绩
SELECT
	stu.stuid,
	stuname,
	avg 
FROM
	student stu,
	( SELECT stuid, avg( score ) avg FROM score GROUP BY stuid ) t1 
WHERE
	stu.stuid = t1.stuid 
	AND avg > 60;
	
-- 数学成绩比语文成绩高的同学的姓名和数学成绩语文成绩

SELECT
	stuname,
	s1.score math,
	s2.score chinese 
FROM
	student stu,
	score s1,
	score s2 
WHERE
	stu.stuid = s1.stuid 
	AND s1.stuid = s2.stuid 
	AND s1.couid = ( SELECT couid FROM course WHERE cname = "数学" ) 
	AND s2.couid = ( SELECT couid FROM course WHERE cname = "语文" ) 
	AND s1.score > s2.score