数据库查询

#按员工首字母排序，写出姓名长度
SELECT LENGTH(last_name) AS 姓名长度,SUBSTR(last_name,1,1) AS 首字母,last_name
FROM employees
ORDER BY 首字母;
#<last_name> earns <salary> monthly but wants <salary*3>  dream salary
SELECT CONCAT(last_name,'earns',salary,'but wants',salary*3) AS "dreams salary"
FROM employees
WHERE salary=24000;
#查询员工编号为90的总人数
SELECT COUNT(*)
FROM employees
WHERE department_id=90;
#查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#查询邮箱中包含e的字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%e%'
GROUP BY department_id;
#查询有奖金的每个领导手下员工最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加分组后的筛选
#1、查询哪个部门员工>2
#a、查询每个部门员工数大于2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#b根据a的结果进行筛选，得出哪个部门得人数大于2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#查询每个工种有奖金得员工得最高工资大于12000得工种编号和最大工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#查询领导编号>102得每个领导手下员工最低工资大于5000得领导编号，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按员工姓名的长度分组，查询每一组员工的个数，筛选个数>5
SELECT COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#查询每个部门每个工种员工的平均工资
SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;
#查询各个管理者手下员工的最低工资，其中最低工资不低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000;
#查询所有部门的编号,员工的数量he平均工资，并按平均工资降序
SELECT COUNT(*),AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)  DESC;
#查询员工名和对应部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#查询有奖金的员工名和他所在部门名
SELECT last_name,department_name,commission_pct
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL;
#查询城市名中第二个字母为o的城市名和部门名
SELECT city,department_name
FROM locations AS l,departments AS d
WHERE city LIKE '_o%' AND l.`location_id`=d.`location_id`;
#f分组案例
#查询每个城市的部门个数
SELECT COUNT(*) AS 个数,department_name AS 部门,city 城市
FROM locations AS l,departments AS d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
#查询有奖金的每个部门名和部门领导编号和该部门最低的工资
SELECT MIN(salary),department_name,d.manager_id
FROM departments AS d,employees AS e
WHERE commission_pct IS NOT NULL AND d.`department_id`=e.`department_id`
GROUP BY department_name,d.`manager_id`;
##################sql99 内连接
####等值连接
#查询员工名和部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.last_name LIKE '%e%';
#查询部门>3的城市名和部门个数
SELECT city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#查询哪个部门的员工数>3的部门名和员工个数，并按个数降序
SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC; 
#查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs j
ON  j.`job_id`=e.`job_id`
ORDER BY department_name DESC;
#####非等值连接
#员工的工资级别
SELECT  salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON salary BETWEEN lowest_sal AND highest_sal ;
#查询每个工资级别的个数>2的个数，并按工资级别降序
SELECT grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades
ON e.`salary` BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
##########自连接
#查询员工的名字和他上级的名字
SELECT e.last_name,m.last_name
FROM employees e
CROSS JOIN employees m
ON e.`employee_id`=m.`manager_id`;
#############外连接
/*
用于查询一个表里面有，一个表里面没有记录的查询
*/
#查询哪个部门没有员工  左外
SELECT d.*,employee_id
FROM departments d
LEFT OUTER JOIN employees AS e
ON d.`department_id`=e.`department_id`
WHERE employee_id IS NULL;
#右外
SELECT d.*,employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE employee_id IS NULL;
##############连接查询习题
#查询编号>3的女神的男朋友信息，如果有则全部列出，没有就用null填充
SELECT be.`id`,be.name,bo.*
FROM beauty be
LEFT OUTER JOIN boys bo
ON be.`boyfriend_id`=bo.`id`
WHERE be.`id`>3;
#查询哪个城市没有部门
SELECT city
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE department_id IS NULL;
#查询部门名为sal或it的员工信息
SELECT department_name,e.*
FROM employees e
RIGHT JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE department_name IN('SAL','IT');
#########################################################################子查询
######################标量子查询
###谁的工资比Abel 高
#1查询abel的工资
SELECT salary 
FROM employees
WHERE last_name='Abel';
#将1作为的结果作为条件
SELECT last_name
FROM employees 
WHERE salary>(
	SELECT salary 
	FROM employees
	WHERE last_name='Abel'
);
###返回job_id与141号员工相同，salary比143员工多的员工姓名，job——id和工资
SELECT salary
FROM employees
WHERE employee_id=143;
#2
SELECT job_id
FROM employees
WHERE employee_id=141;
#3
SELECT last_name,job_id,salary
FROM employees
	WHERE job_id=(SELECT job_id
	FROM employees
	WHERE employee_id=141
)
AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);
###查询最低工资大于50号部门最低工资部门的id和其最低工资
#1
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#2
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);
###########列子查询
###查询location1s——id是1400或1700的部门中所有员工姓名
SELECT department_id
FROM departments 
WHERE location_id IN(1400,1700);
# 2
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments 
	WHERE location_id IN(1400,1700)
);
#####返回其他工种比中job_id为‘IT—prog’工种任一工资低的员工姓名，job——id，工号，salary
#查询工种为it的薪水
SELECT DISTINCT salary 
FROM employees
WHERE job_id="IT_PROG";
#1中任一工资高于工种不为it的
SELECT last_name,job_id,salary,employee_id
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary 
	FROM employees
	WHERE job_id="IT_PROG"
) AND job_id<>"IT_PROG";
#或
SELECT last_name,job_id,salary,employee_id
FROM employees
WHERE salary <(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id="IT_PROG"
) AND job_id<>"IT_PROG";
#####返回其他工种比中job_id为‘IT—prog’工种所有工资低的员工姓名，job——id，工号，salary
SELECT last_name,job_id,salary,employee_id
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary 
	FROM employees
	WHERE job_id="IT_PROG"
) AND job_id<>"IT_PROG";
#############行子查询
########################select后面
####查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees
	WHERE employee_id=department_id
) FROM
departments d;
###查询员工号=102的部门名
SELECT (SELECT department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE employee_id=102);
######from后面的（作为一个表格使用
#查询每个部门的平均工资的工资等级
#1
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
#2
SELECT grade_level,av_d.*
FROM (
	SELECT AVG(salary) av,department_id
	FROM employees
	GROUP BY department_id
) AS av_d
INNER JOIN job_grades g
ON av_d.av BETWEEN lowest_sal AND highest_sal ;