数据库mysql no.4

1.流程控制函数

①if(条件表达式，表达式1，表达式2)：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?如果条件表达式成立，返回表达式1，否则返回表达式2
? ? ?case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end

? ? case情况2
case?
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end

2.分组函数

sum? 求和? ? ?avg? 平均值? ? ? ? ? ? ? max 最大值? ? min 最小值? ? count 计算个数

只能处理数值型? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 任何类型都可以处理

他们都忽略null值

3.分组查询 group by?

筛选条件

注意：分组函数作为筛选条件的时候一定是放在having子句中

? ? ? ? ? 分组查询也可以用排序order by，一般置于最后

? ? ? ? ? 优先使用分组前筛选，即where筛选

4.连接查询

内连接：等值连接，非等值连接（betweenand..）自连接（两个表为一个）

外连接：左外连接（left outer join），右外连接，全外连接（full outer join）

交叉连接（cross outer join）

注意：如果给表起了别名，则查询的字段就不能使用原来的表名去限定

5.SQL练习

-- 分组查询GROUP BY
-- 查询每个部门的平均工资
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;
-- 查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;

-- 添加筛选条件  查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

-- 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

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(*) c,LENGTH(last_name) a from employees GROUP BY a HAVING c>5;

-- 按多个字段分组
-- 查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id,job_id HAVING AVG(salary)>12000 ORDER BY AVG(salary) DESC;

-- 查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id;
-- 查询员工最高工资和最低工资的差距
SELECT max(salary)-MIN(salary) difference FROM employees;
-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>=6000;
-- 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC
-- 选择具有各个job_id的员工人数
SELECT COUNT(*)，job_id FROM employees GROUP BY job_id;


SELECT last_name,department_name FROM employees,departments where employees.department_id=departments.department_id;

-- 查询员工名 工种号，工种名
SELECT last_name,jobs.job_id,job_title FROM employees,jobs WHERE employees.job_id=jobs.job_id;
-- 别名
SELECT last_name,a.job_id,job_title FROM employees a,jobs b WHERE a.job_id=b.job_id;

-- 查询有奖金的员工名、部门名
SELECT last_name,department_name, commission_pct FROM employees e,departments d WHERE e.department_id=d.department_id AND e.commission_pct is not null;

-- 查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id=l.location_id and city LIKE '_o%'

-- 查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM departments,locations
WHERE departments.location_id=locations.location_id GROUP BY city;

-- 查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*) FROM jobs,employees WHERE
jobs.job_id=employees.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;

-- 查询员工名，部门名和所在的城市
SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE
e.department_id=d.department_id and d.location_id=l.location_id GROUP BY last_name,department_name,city ORDER BY department_name DESC;

-- 自连接
-- 查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m WHERE e.manager_id=m.employee_id;

-- 显示员工表的最大工资，工资平均值
SELECT max(salary),ROUND(AVG(salary),2) FROM employees;

SELECT employee_id,job_id,last_name FROM employees ORDER BY department_id DESC,salary ASC;

 SELECT job_id FROM employees WHERE job_id LIKE '%a%e%';

SELECT NOW();
SELECT TRIM(' s s s   ');
SELECT TRIM('a' FROM 'aaa hejun aaa');
SELECT SUBSTR('hejun',3,3);

-- 显示所有员工的姓名，部门号和部门名称
SELECT last_name,e.department_id,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id; 

-- 查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id FROM employees e,departments d WHERE
e.department_id=d.department_id AND e.department_id=90;


-- 查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数 FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY country_id HAVING 部门个数>2;

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.manager_id "Mgr#" FROM employees e,employees m WHERE
e.manager_id=m.employee_id AND e.last_name='kochhar';

-- 用内连接实现等值连接
-- 查询员工名 部门名
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%';

-- 查询员工名部门名工种名并按部门名降序
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 e.job_id=j.job_id ORDER BY department_name
DESC;

-- 查询姓名中包含字符k的员工的名字，上级的名字
SELECT e.last_name,m.last_name FROM employees e
INNER JOIN employees m on e.manager_id=m.employee_id
AND e.last_name like '%k%';
SELECT e.last_name,m.last_name FROM employees e
INNER JOIN employees m on e.manager_id=m.employee_id
WHERE e.last_name like '%k%';

SELECT * FROM beauty;
SELECT * FROM boys;

SELECT b.`name`,bo.* FROM beauty b left OUTER JOIN
boys bo on b.boyfriend_id=bo.id WHERE bo.id is not NULL;

SELECT b.`name`,bo.* FROM boys bo right OUTER JOIN
beauty b on b.boyfriend_id=bo.id WHERE bo.id is not NULL;

-- 查询哪个部门没有员工
SELECT d.*,e.employee_id 
FROM employees e 
RIGHT OUTER JOIN departments d 
on d.department_id=e.department_id 
WHERE e.department_id is null;

SELECT b.id,b.name,bo.*
FROM beauty b LEFT JOIN
boys bo on b.boyfriend_id=bo.id
WHERE b.id>3;

SELECT city,d.* FROM locations l 
left outer join departments d ON
l.location_id=d.location_id
WHERE d.location_id is NULL;