SQL编程练习

表结构

部门表

员工表

练习

1.按部门编号升序、工资倒序排列员工信息

SELECT * FROM emp ORDER BY deptno ASC, sal DESC;

2.列出deptno=30的部门名称及员工

SELECT emp.*, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno and dept.deptno = 30;

3.列出每个部门最高、最低及平均工资

SELECT deptno, max(sal), min(sal), avg(sal) FROM emp GROUP BY deptno;

补充列出每个部门的人数

SELECT deptno, max(sal), min(sal), avg(sal), count(*) FROM emp GROUP BY deptno;

4.列出市场部（SALES）及研发部（RESEARCH）的员工

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno and (dept.dname = 'SALES' or dept.dname = 'RESEARCH');

5.列出人数超过3人的部门

SELECT deptno, count(*) FROM emp GROUP BY deptno HAVING count(*) > 3;

SELECT d.dname, count(*) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY d.dname HAVING count(*) > 3;

！WHERE子句对原始数据进行筛选，在GROUP BY分组之前执行，HAVING关键字用于分组后二次筛选，在GROUP BY之后执行

6.计算MILLER的年薪比SMITH高多少

SELECT m.salary - s.salary FROM 
(SELECT sal * 12 as salary FROM emp WHERE ename = 'MILLER') as m, 
(SELECT sal * 12 as salary FROM emp WHERE ename = 'SMITH') as s;

7.列出直接向King汇报的员工

WHERE子句子查询

SELECT * FROM emp WHERE mgr = (SELECT empno FROM emp WHERE ename = 'King');

FROM子句子查询

SELECT e.* FROM emp e, (SELECT empno FROM emp WHERE ename = 'King') k WHERE e.mgr = k.empno;

8.列出公司所有员工的工龄，并倒序排列

SQL中获取当前系统时间并格式化显示

SELECT DATE_FORMAT(NOW(), "%Y/%m/%d");

SELECT * FROM 
(SELECT emp.*, DATE_FORMAT(NOW(), "%Y") - DATE_FORMAT(hiredate, "%Y") wage FROM emp) d 
ORDER BY d.wage DESC;

9.计算管理者与基层员工平均薪资差距

SELECT a.avg_sal - b.avg_sal FROM
(SELECT avg(sal) avg_sal FROM emp where job = 'MANAGER' or job = 'PRESIDENT') a, 
(SELECT avg(sal) avg_sal FROM emp where job in('CLERK', 'SALESMAN', 'ANALYST')) b;

MySQL优化

1.选取最适用的字段属性：表中字段宽度尽可能小；字段应尽量设定为NOTNULL；将如城市、性别这类有限可选择的字段设置为ENUM类型

2.使用JOIN来代替子查询，因为JOIN不需要建立临时表查询

3.使用UNION代替手动创建临时表

4.使用事务保证数据库的安全访问

5.用锁定表的方法完成事务

6.使用外键的方式保证数据库的关联性

7.使用合适的索引，尽量避免对重复的字段添加索引

8.优化SQL语句：在相同类型的字段间进行比较操作；尽量不要在索引的字段值执行函数；用比较符代替通配符；用NOT EXISTS代替NOT IN