mysql综合练习题

作业1

• 第一题
  D错误。别名有空格要用“”都引起来

• 第三题
  别名和之前的列名都可以使用

作业2

SELECT * FROM emp
SELECT * FROM dept
SELECT * FROM salgrade
-- 作业2
-- 2.查看dept表和emp表的结构
DESC dept;
DESC emp;
-- 3.1显示所有部门的名称
SELECT dname FROM dept
-- 3.2显示所有雇员名及其全年收入 13月
-- （工资+补助）并购指定列别名为年收入
SELECT ename,(sal+IFNULL (comm,0.0))*13 AS '年收入'
FROM emp

-- 4.1显示工资超过2850的员工的姓名和工资

SELECT ename,sal FROM emp
WHERE sal>2850
-- 4.2显示工资不在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)
-- 4.3显示编号为7566的雇员姓名及其所在部门编号

SELECT ename,deptno FROM emp
WHERE empno=7566
-- 4.4显示部门10和30中工资超过1500的雇员名及工资
-- 写法一
SELECT ename,sal FROM emp
WHERE (deptno = 10 OR deptno =30) AND sal>1500
-- 写法二
-- 部门10和30的员工名和工资可以作为一张临时表

SELECT ename,sal FROM(SELECT ename ,sal FROM emp

WHERE deptno = 10 OR deptno=30)AA
WHERE sal>1500

-- 4.5显示无管理者的员工名以及岗位

SELECT ename,job FROM emp
WHERE mgr IS NULL

-- 5.1显示在1991年2月1日到1991年5月1日之间雇佣的雇员名，岗位及雇佣日期，并
-- 按雇佣日期进行默认排序(日期可以直接进行比较)
-- 思路：1.先查询到对应的结果2.考虑排序
SELECT ename,job,hiredate FROM emp
WHERE hiredate>='1991-02-01'AND hiredate<='1991-05-01' ORDER BY hiredate
-- 5.2显示获得补助的所有雇员名，工资及补助，并以工资降序排列
SELECT ename,sal,comm FROM emp
ORDER BY sal DESC

• 启迪
  1.日期可以直接进行排序，但是日期的格式需要使用特定的格式
  2.order by进行排序放的位置比较靠后（比where后）order by +需要排序的字段 +desc(降序)/asc(升序，默认情况，可以省略不写)
  作业3
  

-- 作业3
SELECT * FROM emp
-- 1.选择部门30的所有员工
SELECT ename FROM emp
WHERE deptno =30
-- 2.列出所有办事员(CLERK)的姓名，编号和部门编号
SELECT ename,empno,deptno FROM emp
WHERE job='CLERK'

-- 3.找出佣金高于薪资的员工
SELECT ename FROM emp
WHERE IFNULL(comm,0)>sal-- 注意为空的情况

-- 4.找出佣金高于薪资60%的员工
SELECT ename FROM emp
WHERE comm>(sal*0.6)

-- 5.找出部门10中所有经理（MANAGER）和部门20中所有的办事员（CLERK）的详细资料

SELECT * FROM emp
WHERE (deptno=10 AND job='MANAGER')OR (deptno=20 AND job='CLERK')

-- 6.找出部门10中所有经理（MANAGER）和部门20中所有的办事员（CLERK），还有既不是经理
-- 又不是办事员但是薪资大于或等于2000的所有员工的详细资料



SELECT * FROM emp
WHERE (deptno=10 AND job='MANAGER')OR (deptno=20 AND job='CLERK')
OR(NOT(job='MANAGER'OR job='CLERK')AND sal>=2000)

-- 7.找出收取佣金的员工的不同工作

SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL


-- 8.找出不收佣金或者收取佣金低于100的员工


SELECT * FROM emp
WHERE comm IS NULL OR IFNULL(comm,0)<100

-- 9.找出各月倒数第三天受雇的所有员工
-- last_day()(日期)返回该日期所在月份的最后一天
SELECT * FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate
-- 草率了，日期可以直接相减
-- 传统做法
SELECT ename FROM emp
WHERE DATE_SUB(LAST_DAY(hiredate),INTERVAL 2 DAY)=hiredate

-- 10.找出早于12年前受雇的所有员工（即 入职时间超过12年）


SELECT * FROM emp
 WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR)<NOW()
-- 11.以首字母小写的方式显示所有员工的姓名
-- substring的第三个参数没有写默认将后面都取出来
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1,)),SUBSTRING(ename,2))FROM emp
-- 12.显示正好为5个字符的员工的姓名

  SELECT ename FROM emp
  WHERE LENGTH(ename)=5

作业4

-- 练习4
-- 13.显示不带有'R'的员工的姓名
-- instr(String,subString)返回subString在String中出现的位置m，没有返回0
SELECT ename FROM emp
WHERE INSTR(ename,'R')=0
-- 14.显示所有员工姓名的前三个字符
SELECT SUBSTRING(ename,1,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 ASC
-- 19.显示员工姓名 工作和薪资，按工作降序排序，若工作相同按薪资升序排序
SELECT ename ,job,sal FROM emp
ORDER BY job DESC ,sal DESC-- 可以并列的写 job相同按照sal排列

-- 20.显示所有员工的姓名 加入公司的年份和月份，按照日期所在月排序，若月份相同
-- 则按照最早年份的员工排在最前面
SELECT ename,CONCAT (YEAR(hiredate),'-',MONTH(hiredate))FROM emp
ORDER BY MONTH(hiredate),YEAR(hiredate)ASC

-- 21.显示在一个月为30天的情况下所有员工的日薪资，忽略余数
SELECT FLOOR(sal/30)AS '日薪资' FROM emp
-- 22.找出在(任何年份)2月受雇的所有员工

SELECT * FROM emp
WHERE MONTH(hiredate)=2
-- 23.对于每个员工显示其加入公司的天数
SELECT ename,DATEDIFF(NOW(),hiredate) AS '加入公司的天数' 
FROM emp
-- 24.显示姓名字段的任何位置包含‘A’的所有员工的姓名
SELECT ename FROM emp
WHERE INSTR(ename,'A')!=0
-- extra写法(like模糊查询)
SELECT ename FROM emp
WHERE ename LIKE '%A%'

-- 25.以年月日的方式显示所有员工的服务年限（大概）
-- 思路：先获取工作天数，然后通过相除和取余得到年月日
SELECT  ename,
DATEDIFF(NOW(),hiredate)/365 '工作年',
DATEDIFF(NOW(),hiredate)%365/31 '工作月',
DATEDIFF(NOW(),hiredate)%31 '工作日'
FROM emp

作业5(作业5开始进行多表查询，有一点难度了)

SELECT * FROM dept

SELECT * FROM emp

-- 作业5
-- 根据emp 和dept表，工资=薪资sal+佣金comm写出正确的sal
-- 1.列出至少有一个员工的所有部门
-- 思路：1.先分组查出各个部门有多少人
-- 2.实验having进行过滤
SELECT COUNT(*)AS c,deptno
FROM emp
GROUP BY deptno
HAVING c>1
-- 2.列出薪资比'SMITH'多的所有员工
-- 先找出SMITH的薪资，然后作为子查询
SELECT  * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH')
-- 3.列出受雇日晚于其直接上级的所有员工
-- 其直接上级不易确定，使用多表匹配
-- 思路：1.将emp表当成两张表，worker,leader
-- 过滤条件1.worker.empno=leader.mgr
-- 2.worker.hiredate>leader.hiredate
SELECT worker.ename AS '员工名',worker.hiredate AS'员工入职时间',
leader.ename AS '上级名',leader.hiredate AS '上级入职时间' 
FROM emp worker,emp leader
WHERE worker.empno=leader.mgr
 AND worker.hiredate>leader.hiredate

-- 4.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
-- 使用外连接（因为要显示所有部门）
SELECT dname,ename,job,mgr,hiredate,sal,comm FROM   emp RIGHT JOIN dept
ON
 emp.`deptno`=dept.`deptno`
-- 5.列出所有'CLERK'(办事员)的姓名以及部门名称
-- 比较简单，第一次没有做出来
SELECT ename,dname FROM dept,emp
WHERE emp.`deptno`=dept.`deptno` AND job='CLERK'
-- 6.列出最低薪金大于1500的各种工作
-- 按照工作进行分组，并找出各组中最低工资
-- 2.使用having进行过滤
SELECT MIN(sal)min_sal,job
 FROM emp
 GROUP BY job
 HAVING min_sal>1500

 
-- 7.列出在部门'SALES'(销售部)工作的员工的姓名
SELECT  ename FROM emp,dept
WHERE emp.`deptno`=dept.`deptno`AND dname='SALES'
-- 8.列出薪金高于公司平均薪金的所有员工
  

-- 先找出公司的平均薪水，然后进行select嵌套即可
-- 1.公司平均薪水
SELECT AVG(sal) FROM emp
-- 2.嵌套之后
SELECT * FROM emp
WHERE sal> (SELECT AVG(sal) FROM emp)

作业7

-- 作业7

-- 7.根据：emp员工表，dept部门表，工资=薪金+佣金
-- 9.列出和SCOTT相同工作的所有员工
-- 1.找到SCOTT的工资（SQL嵌套）
SELECT * FROM emp
WHERE (sal+comm)>(SELECT (sal+IFNULL(comm,0)) FROM emp
WHERE ename='SCOTT'
)

-- 10.列出薪金高于在部门30工作的所有员工的薪金的员工的姓名和薪金
-- 1.求出部门30员工的最高薪资
-- 2.SQL嵌套
SELECT ename,sal FROM emp
WHERE sal>(SELECT MAX(sal) FROM emp
GROUP BY deptno
HAVING deptno=30)
-- 方式二
-- 里面的子句可以这样写，就不用写group by 了
SELECT MAX(sal) FROM emp
WHERE deptno=30

-- 11.列出在每个部门工作的员工的数量 平均工资和平均服务期限
SELECT COUNT(*)'部门员工数量',deptno,AVG(sal)'部门平均工资'
,AVG(DATEDIFF(NOW(),hiredate)/365)'平均工资期限（年）'
FROM emp GROUP BY deptno
-- 12.列出所有员工的姓名 部门名称和工资
SELECT ename,dname,sal FROM emp,dept
WHERE emp.`deptno`=dept.`deptno`
-- 13.列出所有部门的详细信息 和部门人数
-- 1.找出各个部门的人数
-- 2.将上面作为一个表，双表笛卡尔
SELECT dept.`deptno`,dname,loc FROM dept,
(SELECT deptno,COUNT(*)'部门人数' FROM emp
GROUP BY deptno)AS aa
WHERE aa.deptno=dept.`deptno`


-- 14.列出各种工作的最低工资

SELECT job,MIN(sal) FROM emp
GROUP BY job


-- 15.列出MANAGER(经理)的最低薪金
-- 这里第一次写没有反应过来
SELECT MIN(sal) FROM emp
WHERE job='MANAGER'



-- 16.列出所有员工的年工资，按年薪从低往高排序
SELECT ename,(sal+IFNULL(comm,0))*12 AS year_sal FROM emp
ORDER BY year_sal ASC

作业8

• 系表
  
• 班级表
  
• 学生表
  
  第二次复习的时候将该题完成