[大数据]SQL_select_题型总结

涉及的数据库：链接：https://pan.baidu.com/s/17p2QxOt-wlLuTciQchm6Qw
提取码：1234

1.运算符

#1.算数运算符：+ - * / div % mod
#'a'此时看作0处理
SELECT 100+'a';

#null 值参与运算，结果为null
SELECT 100+NULL;

#1.选择工资不在5000和12000的员工的姓名和工资
SELECT
	`last_name`,
	`salary`
FROM
	`employees`
WHERE 
	`salary` NOT BETWEEN 5000 AND 12000;

#2.选择在20或者50号部门工作的员工姓名和部门号
SELECT
	`last_name`,
	`department_id`
FROM
	`employees`
#where `department_id`in(20,50)
WHERE 
	`department_id`=20 OR `department_id`=50;

#3.选择公司中没有管理者的员工姓名和`job_id`
SELECT
	`last_name`,
	`job_id`
FROM
	`employees`
WHERE 
	`manager_id` IS NULL;
	
#4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT
	`last_name`,
	`salary`
FROM
	`employees`
WHERE 
	`commission_pct` IS NOT NULL;
	
#5.选择员工姓名的第三个字母是a的员工姓名
SELECT
	`last_name`
FROM
	`employees`
WHERE 
	`last_name` LIKE '__a%';

#6.选择姓名中有字母a和k的员工姓名
SELECT
	`last_name`
FROM
	`employees`
WHERE 
	`last_name`LIKE '%a%' OR
	`last_name`LIKE '%k%';

#7.显示出表 `employees` 中 `first_name`以 ‘e’结尾的员工信息
SELECT
	*
FROM
	`employees`
WHERE
	`first_name`LIKE'%e';

#8.显示出表`employees`部门标号在80——100之间的姓名和工种
SELECT
	`last_name`,
	`job_id`
FROM
	`employees`
WHERE 
	`department_id` BETWEEN 80 AND 100;

#9.显示出表`employees`的`manager_id`是100，101，110 的员工姓名，工资和管理者id
SELECT
	`last_name`,
	`salary`,
	`manager_id`
FROM
	`manager_id` IN (100,101,110);
	

2.排序和分页

#1.查询员工的姓名和部门号和年薪，按年薪降序，按照姓名增序
SELECT
	`last_name`,
	`department_id`,
	salary*(1+IFNULL(`commission_pct`,0)) AS sumsalary
FROM
	`employees`
ORDER BY
	sumsalary DESC,
	`last_name`
	
#2.选择工资不在8000和17000的员工的姓名和工资，按照工资降序，显示第21到40位置的数据
SELECT
	`last_name`,
	`salary`
FROM
	`employees`
WHERE 
	`salary` NOT BETWEEN 8000 AND 17000
ORDER BY
	`salary` DESC
LIMIT 
	20,20;
	
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按照部门号升序
SELECT
	*
FROM
	`employees`
WHERE 
	`email`LIKE'%e%'
ORDER BY
	LENGTH(`email`) DESC,
	`department_id` ;

3.多表查询

#1.显示所有员工的姓名，部门号和部门名称
SELECT
	e.`last_name`,
	e.`department_id`,
	d.`department_name`
FROM
	`employees` e
#可能存在有员工没分配`department_id`，所有使用左外连接，outer可以省略
LEFT OUTER JOIN 
	`departments` d
ON
	e.`department_id`=d.`department_id`;

#2.查询90号部门员工的job_id和90号部门的location_id
SELECT
	e.`job_id`,
	d.`location_id`
FROM
	`departments` d
INNER JOIN
	`employees` e
ON
	d.`department_id`=e.`department_id`
WHERE 
	d.`department_id`=90;

#3.选择所有有奖金的员工的`last_name`，`department_name`，`location_id`，`city`
SELECT
	e.`last_name`,
	e.`department_id`,
	l.`location_id`,
	l.`city`
FROM
	`departments` d
INNER JOIN
	`locations` l
ON
	d.`location_id`=l.`location_id`
#可能存在有员工没分配`department_id`，`employees`表较大，所有使用右外连接，outer可以省略
RIGHT JOIN 	
	`employees` e
ON
	d.`department_id`=e.`department_id`
WHERE 
	e.`commission_pct`IS NOT NULL; #也应该是35条记录
#验证结果	
SELECT
	COUNT(*)
FROM
	`employees`
WHERE 
	`commission_pct` IS NOT NULL; #35条记录
	
#4.选择`city`在Toronto工作的员工的`last_name`，`job_id`，`department_id`，`department_name`
SELECT
	e.`last_name`,
	e.`job_id`,
	e.`department_id`,
	d.`department_name`
FROM
	`departments` d
INNER JOIN
	`locations` l
ON
	d.`location_id`=l.`location_id`
INNER JOIN
	`employees` e
ON
	d.`department_id`=e.`department_id`
WHERE
	l.`city`='Toronto';
	
	
#5.查询员工所在部门名称，部门地址，姓名，工作，工资，其中员工所在部门的部门名称为‘Executive’
SELECT
	d.`department_name`,
	l.`street_address`,
	e.`last_name`,
	e.`job_id`,
	e.`salary`
FROM
	`employees` e
RIGHT JOIN
	`departments` d
ON
	d.`department_id`=e.`department_id`
INNER JOIN
	`locations` l
ON
	d.`location_id`=l.`location_id`
WHERE
	d.`department_name`='Executive';
	
#6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
SELECT	
	e.`last_name` AS '员工id',
	e.`employee_id`,
	b.`last_name` AS '老板id',
	b.`employee_id`
FROM
	`employees` e
#存在有员工没有老板，所有left join
LEFT JOIN
	`employees` b
ON
	e.`manager_id`=b.`employee_id`;
	
#7.查询哪些部门没有员工
SELECT
	d.`department_id`
FROM
	`departments` d
LEFT JOIN
	`employees` e
ON
	d.`department_id`=e.`department_id`
WHERE 
	e.`department_id` IS NULL;

#8.查询哪些城市没有部门
SELECT
	l.`city`
FROM
	`locations` l
LEFT JOIN
	`departments` d
ON
	l.`location_id`=d.`location_id`
WHERE 
	d.`location_id`IS NULL;
	
#9.查询部门名为Sales或者IT的员工信息
SELECT	
	e.*
FROM
	`employees` e
JOIN
	`departments` d
ON
	e.`department_id`=d.`department_id`
WHERE 
	`department_name`IN('Sales','IT');
	

4.单行函数

#1.显示系统时间
SELECT NOW();

#2.查询员工号，姓名工资，以及工资提高20%之后的结果
SELECT
	`employee_id`,
	`salary`,
	`salary`*(1+0.2) AS newsalary
FROM
	`employees`;
	
#3.将员工的姓名按首字母排序，并写出姓名的长度
SELECT
	`last_name`,
	LENGTH(`last_name`)
FROM
	`employees`
ORDER BY
	 SUBSTR(`last_name`,1,1) ASC;
	 
#4.查询员工id,`last_name`,`salary`并作为一个列输出，别名为OUT_PUT
SELECT
	CONCAT(`employee_id`,`last_name`,`salary`) AS OUT_PUT
FROM
	`employees`;

#5.查询公司员工的工作的年数，工作的天数，并按工作年数的降序排序
#DATEDIFF(date1,date2) date1-date2
#curdate 计算当前时间
SELECT
	`employee_id`,
	DATEDIFF(CURDATE(),`hire_date`) AS workday,
	DATEDIFF(CURDATE(),`hire_date`)/365 AS workyear
FROM
	`employees`
ORDER BY
	workyear DESC;
	
#6.查询员工姓名，`hire_date`，`department_id`，满足：`hire_date`在1997年之和，`department_id`为80或90或100，`commission_pct`不空
SELECT
	`last_name`,
	`hire_date`,
	`manager_id`
FROM
	`employees`
WHERE 
	#`hire_date`>='1997-01-01' #存在隐式转换
	DATE_FORMAT(`hire_date`,'%Y-%m-%d')>='1997-01-01'
	AND
	`department_id`IN(80,90,100)
	AND
	`commission_pct` IS NOT NULL;

#7.查询公司中入职时间超过10000天的员工姓名、入职时间
SELECT
	`last_name`,
	`hire_date`
FROM
	`employees`
WHERE 
	DATEDIFF(CURDATE(),`hire_date`)>=10000;

5. 聚合函数

#2.查询公司员工工资的最大值，最小值，平均值和总和
SELECT
	MAX(salary),
	MIN(salary),
	AVG(salary),
	SUM(salary)
FROM
	`employees`
	
#3.查询各`job_id`的员工工资的最大值，最小值，平均值和总和
SELECT
	MAX(salary),
	MIN(salary),
	AVG(salary),
	SUM(salary)
FROM
	`employees`
GROUP BY
	`job_id`
	
#4.选择具有各个`job_id`的员工个数
SELECT
	`job_id`,
	COUNT(*)
FROM
	`employees`
GROUP BY
	`job_id`;
	
#5.查询员工最高工资和最低工资的差距
SELECT
	MAX(salary)-MIN(salary)
FROM
	`employees`;
	
#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
	MIN(salary)
FROM
	`employees`
WHERE 
	`manager_id` IS NOT NULL
GROUP BY
	`manager_id`
HAVING
	MIN(salary)>=6000;

#7.查询所有部门的名字，`location_id`，员工数量和平均工资，并且按照平均工资降序
SELECT
	`department_name`,
	d.`location_id`,
	COUNT(*),
	AVG(`salary`) AS avgsalary
FROM
	`employees` e
RIGHT JOIN
	`departments` d
ON
	e.`department_id`=d.`department_id`
GROUP BY
	d.`department_id`
ORDER BY
	avgsalary DESC;

#8.查询每个(所有)工种、每个部门的部门名工种名和最低工资
SELECT	
	`job_id`,
	`department_name`,
	MIN(salary)
FROM
	`departments` d
LEFT JOIN
	`employees` e
ON
	d.`department_id`=e.`department_id`
GROUP BY
	`department_name`,
	`job_id`

6.子查询

#1、查询和Zlotkey相同部门的员工姓名和工资
SELECT
	`last_name`,
	`salary`
FROM
	`employees` 
WHERE 
	`department_id`=
	(
	SELECT
		`department_id`
	FROM
		`employees`
	WHERE 
		`last_name`='Zlotkey'
	);



#2、查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT
	`employee_id`,
	`last_name`,
	`salary`
FROM
	`employees`
WHERE 
	`salary`>=(
	SELECT
		AVG(salary)
	FROM
		`employees`
	);

#3、查询工资大于所有`job_id`=‘SA_MAN’的员工的工资的员工的`last_name`,`job_id`,`salary`
SELECT
	`last_name`,
	`job_id`,
	`salary`
FROM
	`employees`
WHERE 
	`salary`>ALL(
	SELECT
		`salary`
	FROM
		`employees`
	WHERE 
		`job_id`='SA_MAN'
	);

#4、查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT
	`employee_id`,
	`last_name`
FROM
	`employees`
WHERE 
	`department_id`IN(
	SELECT
		DISTINCT `department_id`
	FROM
		`employees`
	WHERE 
		`last_name` LIKE'%u%'
	);

#5、查询在部门的`location_id`为1700的部门工作的员工的员工号
SELECT
	`employee_id`
FROM
	`employees` e
WHERE 
	e.`department_id` IN(
	SELECT
		d.`department_id`
	FROM
		`departments` d
	WHERE 
		d.`location_id`='1700'
	);
	
#6、查询管理者是King的员工的姓名和工资
SELECT
	e.`last_name`,
	e.`salary`
FROM
	`employees` e
WHERE 
	e.`manager_id`IN
	(
		SELECT
			`employee_id`
		FROM
			`employees`
		WHERE 	
			`last_name`='King'
	);


#7、查询工资最低的员工信息：`last_name`，`salary`
SELECT
	`last_name`,
	`salary`
FROM
	`employees`
WHERE 
	`salary`=
	(
	SELECT
		MIN(salary)
	FROM
		`employees`
	);
	
#8、查询平均工资最低的部门信息
SELECT
	*
FROM
	`departments` d
WHERE 
	d.`department_id`=(
	SELECT
		e.`department_id`
	FROM
		`employees` e
	GROUP BY
		e.`department_id`
	ORDER BY
		AVG(salary) ASC
	LIMIT 1
	)
	
#方法2
SELECT
	*
FROM
	`departments`
WHERE 
	`department_id`=(
		SELECT
			`department_id`
		FROM
			`employees`
		GROUP BY
			`department_id`
		HAVING
			AVG(salary)=(
				SELECT
					MIN(avg_sal)
				FROM	(SELECT
						AVG(salary) avg_sal
					FROM
						`employees`
					GROUP BY
						`department_id`)avgsalary
			)
	)
	

#9、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT
	d.*,
	(
	SELECT
		AVG(salary)
	FROM
		`employees`
	GROUP BY
		`department_id`
	HAVING
		`department_id`=d.`department_id`
	) min_avg
FROM
	`departments` d
WHERE 
	d.`department_id`=(
		SELECT
			`department_id`
		FROM
			`employees`
		GROUP BY
			`department_id`
		HAVING
			AVG(salary)=(
				SELECT
					MIN(avg_sal)
				FROM	(SELECT
						AVG(salary) avg_sal
					FROM
						`employees`
					GROUP BY
						`department_id`)avgsalary
			)
	)
#方式2
SELECT
	d.*,
	(
	SELECT
		AVG(`salary`)
	FROM
		`employees`
	GROUP BY
		`department_id`
	HAVING
		`department_id`=d.`department_id`
	
	
	)min_avg
FROM
	`departments` d
WHERE 
	d.`department_id`=(
	SELECT
		e.`department_id`
	FROM
		`employees` e
	GROUP BY
		e.`department_id`
	ORDER BY
		AVG(salary) ASC
	LIMIT 1
	)

#10、查询平均工资最高的job信息
SELECT
	*
FROM
	`jobs` j
WHERE 
	j.`job_id`=(
	SELECT
		e.`job_id`
	FROM
		`employees` e
	GROUP BY
		e.`job_id`
	ORDER BY
		AVG(salary) DESC
	LIMIT 1
	);

#11、查询平均工资高于公司平均工资的部门有哪些
SELECT
	`department_id`
FROM
	`employees`
WHERE 
	`department_id` IS NOT NULL
GROUP BY
	`department_id`
HAVING AVG(salary)>(
	SELECT
		AVG(salary)
	FROM
		`employees`
	);
	
#12、查询公司中所有manager的详细信息
SELECT
	e1.`employee_id`,
	e1.`first_name`,
	e1.`last_name`
FROM
	`employees` e1
WHERE EXISTS(
	SELECT
		*
	FROM
		`employees` e2
	WHERE 
		e1.`employee_id`=e2.`manager_id`
	)

#13、各个部门中最高工资中最低的那个部门的最低工资是多少
SELECT
	MIN(salary)
FROM
	(
	SELECT
		e1.`salary`
	FROM
		`employees` e1
	WHERE 
		e1.`department_id`=(
		SELECT
			e2.`department_id`
		FROM
			`employees` e2
		GROUP BY
			e2.`department_id`
		ORDER BY
			MAX(e2.salary) ASC
		LIMIT 1
		)
	)e
	
SELECT
	MIN(salary)
FROM
	`employees`
WHERE 
	`department_id`=(
	SELECT
		`department_id`
	FROM
		`employees`
	GROUP BY
		`department_id`
	HAVING
		MAX(salary)=(
		SELECT
			MIN(max_sal)
		FROM   (
			SELECT
				MAX(salary) max_sal
			FROM
				`employees`
			GROUP BY
				`department_id`

			)t_dept_max_sala
		)
	);

#14、查询平均工资最好的部门的manager的详细信息：`last_name`，`department_id`，`email`，`salary`
SELECT
	DISTINCT b.`last_name`,
	b.`department_id`,
	b.`salary`
FROM
	`employees` b
JOIN
	`employees` e
ON
	b.`employee_id`=e.`manager_id`
	AND
	e.`department_id`=
	(
	SELECT
		e1.`department_id`
	FROM
		`employees` e1
	GROUP BY
		e1.`department_id`
	HAVING 
		AVG(e1.salary)=(
		SELECT
			AVG(e2.salary)
		FROM
			`employees` e2
		GROUP BY
			e2.`department_id`
		ORDER BY
			AVG(e2.salary) DESC
		LIMIT 1
	)
	)
#方式2
SELECT
	DISTINCT `last_name`,
	`department_id`,
	`salary`
FROM
	`employees`
WHERE 
	`employee_id`IN(
	SELECT
		DISTINCT `manager_id`
	FROM
		`employees`
	WHERE 
		`department_id`=(
		SELECT
			`department_id`
		FROM
			`employees` 
		GROUP 	BY
			`department_id`
		HAVING
			AVG(salary)=(
			SELECT
				MAX(avg_sal)
			FROM(
					SELECT
						AVG(salary) avg_sal
					FROM
						`employees`
					GROUP BY
						`department_id`
				)t_dept_avg_sal
			)
		)
	)


#15、查询部门的部门号，其中不包括`job_id`是‘ST_CLERK’的部门号
SELECT
	DISTINCT`department_id`
FROM
	`employees`
WHERE 
	`department_id` NOT IN(
	SELECT
		DISTINCT`department_id`
	FROM
		`employees`
	WHERE 
		`job_id`=('ST_CLERK')
	)

	
#16、选择所有没有管理者的员工的`last_name`
SELECT
	e1.`last_name`
FROM
	`employees` e1
WHERE NOT EXISTS(
	SELECT
		*
	FROM
		`employees` e2
	WHERE 
		e1.`manager_id`=e2.`employee_id`
);

#17、查询员工号、姓名、雇佣时间、工资、其中员工的管理者为‘De Haan’
SELECT
	e.`employee_id`,
	e.`last_name`,
	e.`hire_date`,
	e.`salary`
FROM
	`employees` e
WHERE 
	e.`manager_id`IN(
	SELECT
		b.`employee_id`
	FROM
		`employees` b
	WHERE 
		b.`last_name`='De Haan'
	);
#方式2
SELECT
	e.`employee_id`,
	e.`last_name`,
	e.`hire_date`,
	e.`salary`
FROM
	`employees` e
WHERE EXISTS(
	SELECT
		*
	FROM
		`employees` b
	WHERE 
		e.`manager_id`=b.`employee_id`
	AND
		b.`last_name`='De Haan'
	);
	
#18、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
SELECT
	e1.`employee_id`,
	e1.`last_name`,
	e1.`salary`
FROM
	`employees` e1
WHERE 
	e1.salary>(
	SELECT
		AVG(salary)
	FROM
		`employees` e2
	GROUP BY
		e2.`department_id`
	HAVING
		e1.`department_id`=e2.`department_id`
	)
#19、查询每个部门下的部门人数大于5的部门名称
SELECT
	d.`department_name`
FROM
	`departments` d
WHERE 
	5<(
	SELECT
		COUNT(*)
	FROM
		`employees` e
	WHERE
		e.`department_id`=d.`department_id`
	)
#20、查询每个国家下的部门个数大于2的国家编码
SELECT
	l.`country_id`
FROM
	`locations` l
WHERE 
	2<(
	SELECT
		COUNT(*)
	FROM
		`departments` d
	WHERE 
		d.`location_id`=l.`location_id`
	)