-- MAX MIN COUNT AVG SUMSELECTMAX(salary),MIN(salary),AVG(salary),SUM(salary),COUNT(salary)FROM employees;-- 字符型SELECTMAX(last_name),MIN(last_name),MAX(LENGTH(last_name)),COUNT(last_name)FROM employees;SELECTCOUNT(employee_id)FROM employees;SELECTCOUNT(*)FROM employees;SELECTCOUNT(commission_pct)FROM employees;SELECT commission_pct
FROM employees
WHERE commission_pct ISNOTNULL;-- 组函数不计算空值SELECTAVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct)FROM employees;
– GROUP BY 分组(重点、难点)
SELECTAVG(salary)FROM employees;#求各个部门的平均工资SELECT department_id,AVG(salary)FROM employees
GROUPBY department_id;#****出现在 SELECT 子句后的非分组函数,一定要出现在 GROUP BY 子句后SELECT employee_id, department_id,AVG(salary)FROM employees
GROUPBY department_id, employee_id;-- 多列分组#各个部门中每个工种(job_id)的平均工资SELECT department_id, job_id,AVG(salary)FROM employees
GROUPBY department_id, job_id;#WHERE 不能过滤组函数,若过滤组函数需要使用 HAVINGSELECTAVG(salary)FROM employees
GROUPBY department_id
HAVINGAVG(salary)>9000;-- MySQL 中组函数不能嵌套,但是 Oracle 可以,但是不能嵌套太深/*SELECT max(AVG(salary))
FROM employees
GROUP BY department_id;*/SELECTMAX(avg_sal)FROM(SELECTAVG(salary) avg_sal
FROM employees
GROUPBY department_id
) e;
– GROUP BY 分组练习
-- 1.where子句可否使用组函数进行过滤? -- 2.查询公司员工工资的最大值,最小值,平均值,总和SELECTMAX(salary),MIN(salary),AVG(salary),SUM(salary)FROM employees;-- 3.查询各job_id的员工工资的最大值,最小值,平均值,总和SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)FROM employees
GROUPBY job_id;-- 4.选择具有各个job_id的员工数SELECT job_id,COUNT(employee_id)FROM employees
GROUPBY job_id;-- 5.查询员工 最高工资和最低工资的差距(DIFFERENCE)SELECTMAX(salary),MIN(salary),MAX(salary)-MIN(salary)"DIFFERENCE"FROM employees;-- 6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内SELECT manager_id,MIN(salary)FROM employees
WHERE manager_id ISNOTNULLGROUPBY manager_id
HAVINGMIN(salary)>6000;-- 7.查询所有部门的名字,location_id,员工数量和工资平均值SELECT department_name, location_id,COUNT(employee_id),AVG(salary)FROM employees e, departments d
WHERE e.`department_id`= d.`department_id`GROUPBY department_name, location_id;
– 子查询
#谁的工资比 Abel 高?SELECT salary
FROM employees
WHERE last_name ="Abel";SELECT*FROM employees
WHERE salary >11000;-- 子查询-- 主查询(外查询)SELECT*FROM employees
WHERE salary >(-- 子查询(内查询)SELECT salary
FROM employees
WHERE last_name ='Abel');-- 题目:返回job_id与141号员工相同,salary比143号员工多的 员工姓名,job_id 和工资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);-- 题目:返回公司 工资最少 的员工的 last_name,job_id和salarySELECT last_name, job_id, salary
FROM employees
WHERE salary =(SELECTMIN(salary)FROM employees
);-- 题目:查询最低工资 大于 50号部门最低工资的 部门id和其最低工资SELECT department_id,MIN(salary)FROM employees
GROUPBY department_id
HAVINGMIN(salary)>(SELECTMIN(salary)FROM employees
WHERE department_id =50);-- 非法子查询SELECT employee_id, last_name
FROM employees
WHERE salary =(SELECTMIN(salary)FROM employees
GROUPBY department_id);-- 空值问题SELECT last_name, job_id
FROM employees
WHERE job_id =(SELECT job_id
FROM employees
WHERE last_name ='Haas');-- 题目:返回其它部门中比 job_id为‘IT_PROG’部门 任一 工资低的员工的 员工号、姓名、job_id 以及salary-- any : 满足条件的任意一个SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ANY(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';-- 题目:返回其它部门中比job_id为‘IT_PROG’部门 所有 工资都低的员工的员工号、姓名、job_id 以及salary-- all : 满足所有的条件SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ALL(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary IN(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';
– 子查询练习
-- 1.查询和Zlotkey相同部门的员工姓名SELECT last_name
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 >(SELECTAVG(salary)FROM employees
);-- 3.查询各部门中工资 比 本 部门平均工资高的员工的员工号, 姓名和工资SELECT employee_id, last_name, salary
FROM employees e1
WHERE salary >(SELECTAVG(salary)FROM employees e2
WHERE e2.`department_id`= e1.`department_id`);SELECT employee_id, last_name, salary
FROM employees e1,(SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUPBY department_id
) e2
WHERE e1.`department_id`= e2.department_id
AND e1.`salary`> e2.avg_sal;-- 4.查询和姓名中包含字母u的员工在相同部门的员工的 员工号和姓名SELECT employee_id, last_name
FROM employees
WHERE department_id =ANY(SELECT department_id
FROM employees
WHERE last_name LIKE'%u%');-- 5.查询在部门的 location_id为1700的部门 工作的员工的 员工号SELECT employee_id
FROM employees e, departments d
WHERE e.`department_id`= d.`department_id`AND location_id =1700;SELECT employee_id
FROM employees e, departments d
WHERE e.`department_id`= d.`department_id`AND e.`department_id`=ANY(SELECT department_id
FROM departments d
WHERE location_id =1700);SELECT employee_id
FROM employees
WHERE department_id IN(SELECT department_id
FROM departments
WHERE location_id =1700);-- 6.查询管理者是King的员工姓名和工资SELECT last_name, salary
FROM employees
WHERE manager_id IN(SELECT employee_id
FROM employees
WHERE last_name ='King');
-- DDL : 数据定义语言CREATETABLE emp6(
id INT(10),
NAME VARCHAR(20),
salary DOUBLE(10,2),
hire_date DATE);-- DML : 数据操纵语言#添加数据 INSERT INTO ... VALUES...INSERTINTO emp6
VALUES(101,'张三',9999.99,'1999-9-9');INSERTINTO emp6(id, NAME, hire_date)VALUES(102,'李四',NOW());#基于现有表导入数据INSERTINTO emp6(id, NAME, salary)SELECT employee_id, last_name, salary
FROM employees
WHERE department_id =90;#修改数据 UPDATE ... SET ...-- 注意:不要忘记 where 条件UPDATE emp6
SET salary =1000WHERE id =100;#同时修改多列UPDATE emp6
SET salary =20000, hire_date ='2000-10-10'WHERE id =100;#删除数据 DELETE FROM ...DELETEFROM emp6
WHERE id =100;#查询数据SELECT*FROM emp6;
约束
-- NOT NULL 非空约束,规定某个字段不能为空CREATETABLE emp1(
id INT(10),`name`VARCHAR(20)NOTNULL);INSERTINTO emp1
VALUES(101,NULL);INSERTINTO emp1(id)VALUES(NULL);SELECT*FROM emp1;CREATETABLE emp2(
id INT(10),
NAME VARCHAR(20));SELECT*FROM emp2;INSERTINTO emp2
VALUES(101,NULL);#对现有表添加非空约束ALTERTABLE emp2
MODIFY NAME VARCHAR(20)NOTNULL;#删除非空约束ALTERTABLE emp2
MODIFY NAME VARCHAR(20)NULL;-- UNIQUE 唯一约束,规定某个字段在整个表中是唯一的CREATETABLE emp3(
id INT(10),
NAME VARCHAR(20),
phone VARCHAR(30),CONSTRAINT emp3_phone_un UNIQUE(phone));DESC emp3;INSERTINTO emp3
VALUES(102,'张三666','123');SELECT*FROM emp3;#添加唯一约束ALTERTABLE emp3
ADDCONSTRAINT emp3_name_un UNIQUE(NAME);#删除唯一约束ALTERTABLE emp3
DROPINDEX emp3_name_un;-- PRIMARY KEY 主键(非空且唯一),通常利用逐渐锁定一条数据CREATETABLE emp4(
id INT(10)PRIMARYKEY,
NAME VARCHAR(20));SELECT*FROM emp4;INSERTINTO emp4
VALUES(101,'Tom222');CREATETABLE emp5(
id INT(10),
NAME VARCHAR(20),CONSTRAINT emp5_id_pk PRIMARYKEY(id));SELECT*FROM emp5;CREATETABLE emp6(
id INT(10),
NAME VARCHAR(20));#添加主键约束ALTERTABLE emp6
ADDCONSTRAINT emp6_id_pk PRIMARYKEY(id);#删除主键约束ALTERTABLE emp6
DROPPRIMARYKEY;INSERTINTO emp6
VALUES(NULL,'Tom');SELECT*FROM emp6;-- FOREIGN KEY 外键:通常关联另一个表的主键,出现在外键表中的数据,一定出现在主键表中 CREATETABLE dept1(
dept_id INT(10)PRIMARYKEY,
dept_name VARCHAR(20));CREATETABLE emp7(
id INT(10)PRIMARYKEY,
NAME VARCHAR(20),
depart_id INT(10),CONSTRAINT emp7_departId_fk FOREIGNKEY(depart_id)REFERENCES dept1(dept_id)-- ON DELETE CASCADE -- (级联删除): 当父表中的列被删除时,子表中相对应的列也被删除ONDELETESETNULL-- (级联置空): 子表中相应的列置空);SELECT*FROM dept1;SELECT*FROM emp7;INSERTINTO dept1
VALUES(10,'IT');INSERTINTO emp7
VALUES(101,'张三',10);DELETEFROM emp7
WHERE id =101;DELETEFROM dept1
WHERE dept_id =10;#添加外键约束ALTERTABLE emp7
ADDCONSTRAINT emp7_depart_id_fk FOREIGNKEY(depart_id)REFERENCES dept1(dept_id);#删除外键约束ALTERTABLE emp7
DROPFOREIGNKEY emp7_departId_fk;-- CHECK 检查约束 MySQL 8.0CREATETABLE emp8(
id INT(10),
NAME VARCHAR(20),
salary DOUBLE(10,2),CONSTRAINT emp8_salary_ck CHECK(salary >3000));SELECT*FROM emp8;INSERTINTO emp8
VALUES(101,'张三',1000);
MySQL 分页(重要)
-- LIMIT 用于 MySQL 分页,使用在最末尾-- 公式:(当前页数-1)*每页条数, 每页条数SELECT employee_id, last_name, salary
FROM employees
ORDERBY salary DESCLIMIT30,10;-- 窗口函数 ROW_NUMBER()SELECT last_name, salary
FROM(SELECT row_number()over(PARTITIONBY department_id ORDERBY salary DESC) rownum, department_id, last_name, salary
FROM employees
) e
WHERE e.rownum =1;SELECT*FROM(SELECT row_number()over(PARTITIONBY salary) rownum, department_id, last_name, salary
FROM employees
) e
WHERE e.rownum =1;