查询总结-理论
语法:
select 查询列表 7
from 表1 别名 1
连接类型 join 表2 2
on 连接条件 3
where 筛选 4
group by 分组列表 5
having 筛选 6
order by 排序列表 8
limit 起始条目索引,条目数 9
一、基础查询-测试题
#1. 查询工资大于 12000 的员工姓名和工资
SELECT
last_name AS 姓名,
`salary` AS 工资
FROM employees
WHERE salary >12000;
#2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT
last_name AS 名字,
`department_id` AS 部门号,
`salary`*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees;
WHERE `department_id`<=> 176;
#3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT
last_name,
`salary`
FROM employees
WHERE NOT(`salary` BETWEEN 5000 AND 12000);
#4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT
`department_id` AS 部门号,
`last_name` AS 名字,
`manager_id` AS 管理号
FROM employees
WHERE `department_id` <=>20 OR`department_id`<=>50;
#5. 选择公司中没有管理者的员工姓名及 job_id
SELECT
last_name,
`job_id`
FROM employees
WHERE `manager_id` IS NULL;
#6. 选择公司中有奖金的员工姓名,工资和奖金级别
SELECT
last_name,
`salary`,
`commission_pct`
FROM employees
WHERE `commission_pct`IS NOT NULL;
#7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
#8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%';
#9. 显示出表 employees 表中 first_name 以 'e’结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%%e'
#10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT last_name, `job_id`
FROM employees
WHERE `department_id` BETWEEN 80 AND 100;
#11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name, `job_id`
FROM employees
WHERE `manager_id` IN (100,101,110);
二、条件查询-测试题
#1、查询没有奖金,且工资小于18000的salary,last_name
SELECT `last_name`, `salary`
FROM employees
WHERE salary <= 18000 AND `commission_pct` IS NULL;
#2、查询employees表中,job_id不为"IT"或者工资为12000的员工信息
SELECT *
FROM employees
WHERE
#`job_id` LIKE 'IT%' OR `salary' = 12000;
#3、查看部门department的表结构
DESC departments;
#4、查询部门department表中涉及到了那些位置编号
SELECT DISTINCT location_id
FROM departments;
三、常见函数-测试题
#1.查询员工的姓名和部门号和年薪,按年薪降序 按名字升序
SELECT `last_name`, `department_id`,
salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,
last_name ASC;
#2、选择工资不在8000到17000的员工的名字和工资,按工资降序
SELECT `last_name`, `salary`
FROM employees
WHERE `salary` NOT BETWEEN 8000 AND 17000
ORDER BY `salary` DESC;
#3、查询邮箱中包含e的员工信息,并先按邮箱的字节计数降序,再按部门号升序
SELECT *,LENGTH(`email`) AS 邮箱
FROM employees
WHERE `email` LIKE '%e%'
ORDER BY LENGTH(`email`) DESC , `department_id` ASC;
#1、显示系统时间(注:日期+时间)
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS 系统时间;
#2、查询员工号,姓名,工资,以及工资提高百分之20%之后的结果(new salary)
SELECT
`job_id`,
`last_name`,
`salary`,
`salary`* 1.2 "new salary"
FROM employees;
``
#3、将员工的姓名按首字母排序,并写出姓名的长度(length)
```sql
SELECT
LENGTH(`last_name`) AS 姓名长度,
SUBSTR(last_name,1,1) AS 首字符,
last_name 名字
FROM employees
ORDER BY
首字符 ASC;
#4、做一个查询,产生下面的结果
<last_name> earns monthly but want <salary*3>
Dream Salary
King earns 24000 monthly but want 72000
SELECT
CONCAT(last_name,' earns ',salary, ' monthly but want ',salary*3)
AS "Deeam Salary"
FROM employees
WHERE salary > 20000;
#5、要求使用case-when实现下面的条件
job_id grade
AD_PRES A
ST_MAN B
IT_PROG C
SELECT
`last_name`,
`job_id` AS job,
CASE `job_id`
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';
#6、查询公司员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary) 最大值,MIN(salary) 最小值,ROUND(AVG(salary),2) 平均值,COUNT(salary) 个数
FROM employees;
#7、查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFERNCE
FROM employees
SELECT
DATEDIFF(NOW(),'1999-4-29');
#8、查询部门编号为90的员工个数
SELECT COUNT(`department_id`) AS 编号为90
FROM employees
WHERE `department_id`<=> 90;
四、分组查询-测试题
案例分析2
#1、查询各job_id的员工工资的最大值,最小值,平均值、总和,并按job_id升序
SELECT job_id 员工id,MAX(salary)最大值,MIN(salary)最小值,AVG(salary)平均,SUM(salary)总和
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;
#2、查询员工最高工资和最低工资的差距(difference)
SELECT MAX(salary)-MIN(salary) AS difference
FROM employees
#3、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT MIN(salary),manager_id,last_name
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >=6000;
#4、查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT `department_id`,COUNT(*) 个数,AVG(`salary`)平均工资
FROM employees
GROUP BY department_id
ORDER BY AVG(`salary`) DESC;
#5、选择具有各个job_id的员工人数
SELECT COUNT(*) 个数, job_id
FROM employees
GROUP BY job_id
五、连接查询-测试题
#1、显示所有员工的姓名,部门号和部门名称
USE myemployees;
SELECT `last_name`,e.`department_id`,`department_name`
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
#2、查询90号部门员工的job_id和90号部门的location_id
SELECT e.`job_id`,d.`location_id`
FROM employees e,`departments` d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90 ;
#3、选择所有有奖金的员工的`
SELECT last_name,`department_name`,l.`location_id`,`city`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`
AND e.`commission_pct` IS NOT NULL;
#4、选择city在Toronto工作的员工的last_name
,job_id
,department_id
,department_name
SELECT `last_name`,`job_id`,d.`department_id`,`department_name`
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`
AND `city` = 'Toronto';
#5、查询每个工种、每个部门的部门名,工种名和最低工资
SELECT `department_name`,`job_title`,MIN(salary)
FROM `employees` e,`departments` d,`jobs` j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY department_name ,job_title;
#6、查询每个国家下的部门个数大于2的国家编号
SELECT `country_id` ,COUNT(*)部门个数
FROM `locations` l ,`departments` d
WHERE l.`location_id` = d.`location_id`
GROUP BY `country_id`
HAVING COUNT(*) > 2;
#7、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似下面
/*
employees Emp# manager Mgr#
kochhar 101 king 100
*/
SELECT e.`last_name` employees,
e.`employee_id` "Emp#",
m.`last_name` manager,
m.`employee_id` "Mgr#"
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`
AND e.last_name = 'Kochhar';
#8、查询编号 >3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
SELECT b.id , b.name , bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id
WHERE b.id >3;
#9、查询哪个城市没有部门
SELECT d.*, l.city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`IS NULL
#10、查询部门名为SAL或IT的员工信息
SELECT d.`department_name`, e.*, d.`department_id`
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`= e.`department_id`
WHERE department_name IN( 'SAL' , 'IT');
六、子查询-测试题
#1、查询工资最低的员工信息:last_name, salary
#①最低工资的员工
SELECT MIN(salary) FROM employees
#②在①中查询信息
SELECT last_name ,salary
FROM employees
WHERE salary = (
SELECT MIN(salary) FROM employees
);
#2、查询平均工资最低的部门信息
#方式一:
#各部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
GROUP BY department_id
#②查询①上的最低平均工资(把①当表)
SELECT MIN(ag)
FROM(
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
)ag_dep
#③查询那个部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(ag)
FROM(
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
)ag_dep
)
#④ 查询部门信息
SELECT d.*
FROM departments d
WHERE d.department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(ag)
FROM(
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
)ag_dep
)
);
#方式二:
#各部门的平均工资
SELECT AVG(salary) , department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#③查询部门信息
SELECT *
FROM departments
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);
#3、查询平均工资最低的部门信息和该部门的平均工资
#①查询部门的 平均 工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②最低的平均工资
SELECT AVG(salary) ,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
#③查询部门信息
SELECT d.* ,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id
SELECT d.* ,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id
#4、查询平均工资最高的job信息
#①查询最高的job的平均工资
SELECT AVG(salary) ,job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#②查询job信息
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
#5、查询平均工资高于公司平均工资的部门有哪些?
#①查询平均工资
SELECT AVG(salary)
FROM employees
#②每个部门平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#③筛选②结果集,满足平均工资 >①
SELECT AVG(salary) ,department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
)
#6、查询出公司中所有manager的详细信息
#①所有的manager的员工编号
SELECT DISTINCT manager_id
FROM employees;
#②查询详细信息,满足employee_id =①
SELECT *
FROM employees
WHERE employee_id = ANY(
SELECT DISTINCT manager_id
FROM employees
);
#7、各个部门中最高工资中最低的那个部门的 最低工资是多少
#①查询各部门的最高工资最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
#②查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
)
#8、查询平均工资最高的部门的manager的详细信息:last_name ,department_id ,email ,salary
#①查询平均工资最高的部门的manager的详细信息
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#②详细信息:last_name ,department_id ,email ,salary
SELECT last_name ,d.department_id ,email ,salary
FROM employees e
JOIN departments d
ON d.manager_id = e.employee_id
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
七、各种查询方式
#1、查询每个专业的学生人数
#每个专业有多少人
SELECT majorid ,COUNT(*)
FROM student
GROUP BY majorid
#-------------看部门------------
SELECT COUNT(*) ,m.majorname
FROM student s
JOIN major m
ON s.majorid = m.majorid
GROUP BY m.majorname
#2、参加考试的学生中,每个学生的平均分,最高分
SELECT AVG(score) ,MAX(score) ,studentno
FROM restult
GROUP BY studentno
#--------------下面是错误示例--------------
SELECT m.majorid ,AVG(r.score) ,MAX(r.score)
FROM result r
JOIN major m
ON r.id = m.majorid
GROUP BY m.majorname
#3、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno ,s.studentname, MIN(score)
FROM student s
JOIN result r
ON r.studentno = s.studentno
WHERE s.studentname LIKE "张%"
GROUP BY s.studentno
HAVING MIN(score) > 60;
#4、查询专业生日在‘1988-1-1’ 后 的学生姓名、专业名称
SELECT s.studentname ,m.`majorname`,s.borndate
FROM student s
JOIN major m
ON s.`majorid` = m.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1') > 0;
#5、查询每个专业的男生人数和女生人数分别是多少
#方式一:
SELECT majorid ,sex ,COUNT(*)
FROM student
GROUP BY majorid ,sex;
#方式二:
SELECT majorid ,
(SELECT COUNT(*) FROM student WHERE sex = '男' AND majorid = s.`majorid`) 男,
(SELECT COUNT(*) FROM student WHERE sex = '女' AND majorid = s.majorid) 女
FROM student s
GROUP BY majorid;
#6、查询专业和张翠山一样的学生的最低分
#①查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'
#②查询编号= ①的所有学生编号
SELECT studentno
FROM student
WHERE majorid = (
SELECT majorid
FROM student
WHERE studentname = '张翠山'
)
#③ 查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN (
SELECT studentno
FROM student
WHERE majorid = (
SELECT majorid
FROM student
WHERE studentname = '张翠山'
)
);
#7、查询大于60分的学生的姓名、密码、专业名
SELECT studentname ,loginpwd ,majorname
FROM student s
JOIN major m ON s.`majorid` = m.`majorid`
JOIN result r ON s.studentno = r.`studentno`
WHERE r.score > 60
#8、按邮箱位数分组,查询每组的学生个数
SELECT COUNT(*) ,LENGTH(email)
FROM student
GROUP BY LENGTH(email);
#9、查询学生名、专业名、分数
SELECT studentname ,score ,majorname
FROM student s
JOIN major m ON s.`majorid` = m.`majorid`
LEFT JOIN result r ON s.studentno = r.`studentno`
#10、查询哪些专业没有学生,分别用左连接和右连接实现
SELECT m.`majorid` ,m.`majorname` ,s.studentno
FROM major m
LEFT JOIN student s ON m.majorid = s.`majorid`
WHERE s.`studentno` IS NULL
#11、查询没有成绩的学生人数
SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL
#12、
已知表stuinfo
id 学号
name 姓名
email 邮箱 job@136.com
gradeld 年级编号
sex 性别 男 女
age 年龄
#已知表 grade
id 年纪编号
gradeName 年龄名称
#①、查询 所有学院的邮箱的用户名(注:邮箱中 @前面的字符)
SELECT SUBSTR(email,1,INSTR(email,'@')-1)
FROM stuinfo
#②、查询男生和女生的个数
SELECT COUNT(*) ,sex
FROM stuinfo
GROUP BY sex;
#③、查询年龄 >18岁的所有学生的姓名和年纪名称
SELECT NAME ,gradeName
FROM stuinfo s
JOIN grade g
IN s.gradeId = g.id
WHERE s.age > 18;
#④、查询哪个年级的学生最小的年龄 > 20岁
#①那个年的学生最小
#②在①的结果上筛选
SELECT MIN(age),gradeid
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age) > 20
#⑥、试说出查询语句中涉及到的所有的关键字,以及执行先后顺序
SELECT 查询列表 7
FROM 表 1
连接类型 JOIN 表2 2
ON 连接条件 3
WHERE 筛选条件 4
GROUP BY 分组列表 5
HAVING 分组后的筛选 6
ORDER BY 排序列表 8
LIMIT 偏移,条目数 9