初学者都会接触到三种表:emp、dept、salgrade表,进行练习各种语句操作再合适不过
但是,网上大多数的操作语句都是用oracle进行操作的,小编在学习mysql的时候,参考网上的书写遇到了不少问题
都是由于oracle语句和mysql语句的不兼容的引起的。
写多行sql语句的时候或者嵌套查询的时候,切记,分行与缩进,条理清晰
大家在学习的时候,注意看小编的书写格式和书写缩进,同一级的SELECT、WHERE尽量对其,子级的要缩进,避免干扰
简单的语句给出答案即可,复杂的语句,会给出书写思路的
一:单表查询
1 -- 1) 试用SQL语言完成下列查询(单表查询): 2 -- a) 查询20号部门的所有员工信息: 3 SELECT * FROM emp WHERE deptno = 20; 4 -- b) 查询奖金(COMM)高于工资(SAL)的员工信息: 5 SELECT * FROM emp WHERE comm > sal; 6 -- c) 查询奖金高于工资的20%的员工信息: 7 SELECT * FROM emp WHERE comm > sal*0.2; 8 -- d) 查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息: 9 SELECT * FROM emp WHERE (job = 'manager' AND deptno = 10) OR (job = 'clerk' AND deptno = 20); 10 -- e) 查询所有工种不是MANAGER和CLERK,且工资大于或等于2000员工的详细信息: 11 SELECT * FROM emp WHERE (sal >= 2000) AND (job NOT IN('manager','clerk')); 12 -- f) 查询没有奖金或奖金低于100的员工信息: 13 SELECT * FROM emp WHERE (comm < 100 ) OR comm IS NULL; 14 -- g) 查询员工工龄大于或等于10年的员工信息: 15 SELECT * FROM emp WHERE (NOW() - hiredate) >= 10; 16 -- h) 查询员工信息,要求以首字母大写的方式显示所有员工的姓名: 17 SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)),LOWER(SUBSTRING(ename,2,(CHAR_LENGTH(ename)-1))))FROM emp;
(h)解析:用到的五个函数:
upper():将字符串大写处理
lower():将字符串小写处理
substring(ename, 1 , 1 ):从一段字符串中截取字符串,例如:smith:
substring(Smith,1,1):表示:从第一个字符开始,截取1个字符,结果就是:s
substring(smith,2,(char_length('smith')-1)):表示:从第二个字符开始,截取字符长度-1个字符,即除去首字母剩下的字符,结果:mith:
char_length():获取指定字符串的的长度
concat(str1,str2);拼接两个字符串
-- i) 查询在2月份入职的所有员工信息: SELECT * FROM emp WHERE hiredate LIKE '%-02-%'; -- j) 显示所有员工的姓名、入职的年份和月份,按入职日期所在的月份排序,若月份相同则按入职的年份排序: SELECT ename,SUBSTR(hiredate FROM 1 FOR 7) FROM emp ORDER BY SUBSTR(hiredate FROM 6 FOR 2),SUBSTRING(hiredate FROM 1 FOR 4);
二:多表查询
-- a) 查询从事同一种工作但不属于同一部门的员工信息: SELECT e.* FROM emp e CROSS JOIN emp m ON (e.job = m.job) AND (e.deptno <> m.deptno); -- b) 查询各个部门的详细信息以及部门人数、部门平均工资: SELECT d.*,COUNT(e.ename),AVG(e.sal) FROM emp e,dept d WHERE e.deptno = d.deptno GROUP BY d.deptno,d.dname;
多表查询不是很难,不做过多的解释,不懂得,可以留言或者查看小编的前两篇随笔,有解释的。
三:嵌套子查询
-- a) 查询10号部门员工以及领导的信息: -- 不相关子查询 SELECT * FROM emp WHERE empno IN(SELECT empno FROM emp WHERE deptno = 10); -- b) 查询工资为某个部门平均工资的员工信息: -- 不相关子查询 SELECT * FROM emp WHERE sal IN(SELECT AVG(sal) FROM emp GROUP BY deptno); -- c) 查询工资高于本部门平均工资的员工的信息: -- 相关子查询 SELECT * FROM emp e WHERE sal > (SELECT AVG(sal) FROM emp WHERE e.deptno = deptno GROUP BY deptno); -- d) 查询工资高于本部门平均工资的员工的信息及其部门的平均工资: -- 不相关子查询 SELECT e.*,a.avgsal FROM emp e,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) a WHERE e.sal > a.avgsal AND e.deptno = a.deptno;
嵌套子查询,不是很难,如果有不懂得,看小编下一篇文章,这篇文章主要以语句练习为主,不做太多的知识讲解。
四:使用聚合函数进行查询(重点介绍)
常见的五个聚合函数:sum()、count()、max()、min()、avg()。聚合函数通常与group by 子句一起使用
-- a) 统计各个工种的人数与平均工资: SELECT job,COUNT(ename),AVG(sal) FROM emp GROUP BY job;
-- b) 统计每个部门中各个工种的人数与平均工资:
SELECT deptno,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,job;
注:很简单,不做解释
-- c) 查询人数最多的部门信息:
有两种方法:
方法一:利用分页 语句
-- 1、查询出各部门的人数, 按人数进行降序排序 SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc ; -- 2、分页查询,只显示第一行,即最大的人数部门 SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc LIMIT 1; -- 3、将上面的查询结果当做一张表,联合部门表进行查询 SELECT * FROM dept d WHERE d.deptno =( SELECT deptno FROM emp GROUP BY deptno ORDER BY count(*) desc LIMIT 1);
方法二:较麻烦