SQL学习(二)

具体学习视频请参考：https://www.bilibili.com/video/BV12b411K7Zu?

分页查询

应用场景：
当需要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 from 表名【join type】join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段 limit offset,size
offset 要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数

特点：

• limit语句放在查询语句的最后

#案例1：查询前五条员工信息
SELECT *  FROM employees LIMIT 0,5;
SELECT *  FROM employees LIMIT 5；

#查询第11条到第25条的数据
SELECT * FROM employees LIMIT 10,15;

#有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 工资
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 工资 DESC
LIMIT 10;

经典子查询案例

#查询工资最低的员工信息：last_name,salary
#1.查询最低工资
SELECT MIN(salary)
FROM employees;
#2.查询最低工资等于1的
SELECT last_name,salary
FROM employees
WHERE salary=(
 SELECT MIN(salary)
 FROM employees
);

#查询工资最低的部门信息
SELECT *
FROM departments
WHERE department_id=(
 SELECT department_id
 FROM employees
 GROUP BY department_id
 ORDER BY salary
 LIMIT 1,1
);

#查询平均工资最低的部门信息和该部门的平均工资
#查询各个部门的平均工资并排序选出最低的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1,1;

SELECT d.*,ag
FROM departments d
INNER JOIN(
 SELECT AVG(salary) ag,department_id
 FROM employees
 GROUP BY department_id
 ORDER BY AVG(salary)
 LIMIT 1,1
) ag_dep
ON ag_dep.department_id=d.department_id;

#查询平均工资最高的job的信息
#查询各个各个job_id的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1,1;

SELECT j.*,ag_dep.ag
FROM jobs j
INNER JOIN(
 SELECT AVG(salary)ag,job_id
 FROM employees
 GROUP BY job_id
 ORDER BY AVG(salary) DESC
 LIMIT 1,1 
) ag_dep
ON ag_dep.job_id=j.`job_id`;

#查询平均工资高于公司平均工资的部门有哪些
#公司的平均工资
SELECT AVG(salary)
FROM employees;
#查询各个部门的平均工资以及其部门名
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;

SELECT department_id
FROM(
 SELECT department_id,AVG(salary) ag
 FROM employees
 GROUP BY department_id 
) ag_dep
WHERE ag_dep.ag>(
 SELECT AVG(salary)
 FROM employees 
);

#查询公司所有的manager的详细信息
#1查询所有员工的manager编号
SELECT DISTINCT manager_id
FROM employees;

SELECT *
FROM employees
WHERE employee_id IN (
 SELECT DISTINCT manager_id
 FROM employees
);
#查询各个部门中最高工资中最低的部门的最低工资是多少
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1,1;

#查询平均工资最高的部门的manager的详细信息
#1.工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1,1;
#将departments和employees连接，筛选条件用1
SELECT d.*
FROM departments d
INNER JOIN employees e 
ON d.`manager_id`=e.`employee_id`
WHERE e.`department_id`=(
 SELECT department_id
 FROM employees
 GROUP BY department_id
 ORDER BY AVG(salary)
 LIMIT 1,1 
);

联合查询

union：联合，合并
将多条查询语句合并成一个结果
语法：
查询语句一
union
查询语句二
…

应用场景
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的每一列的类型和顺序最好一致
• 使用union关键字默认去重，如果使用union all 可以包含重复项

#查询部门编号大于90或者邮箱中包含a的
SELECT * FROM employees WHERE department_id >90 OR email LIKE '%a%';

SELECT * FROM employees WHERE department_id >90
UNION 
SELECT * FROM employees WHERE email LIKE '%a%';

DML语言(数据操作语言)

数据操作语言

• 插入：insert
• 删除：delete
• 修改：update

插入语句

语法：
第一种经典的插入方式：
insert into 表名(列名…) values(值1…)
注意：

• 插入的值的类型要和列的类型一致或兼容
• 不可以为NULL的列是必须要插入值的，可以为NULL的列是如何插入值的：两种方式
• 插入的时候列的顺序是可以颠倒的
• 列数和值的个数必须匹配
• 可以省略列名，默认是所有列，而且列的顺序和表中的列的顺序是一致的

插入的方式二：
语法：
insert into 表名
set 列名=值，列名=值…

两种方式比较：

• 方式一可以批量的插入多行，但是方式二不支持
• 方式一支持子查询，方式二不支持

#插入
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1999-8-12','1999213',NULL,2);

#利用方式一批量插入多行
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕1','女','1999-8-12','1999213',NULL,2)，
VALUES(14,'唐艺昕2','女','1999-8-12','1999213',NULL,2)，
VALUES(15,'唐艺昕3','女','1999-8-12','1999213',NULL,2)；

修改语句

语法：

• 修改单表中的记录
  语法：
  update 表
  set 列=新值，列=新值…
  where 筛选条件；(满足条件再更新)
• 修改多表的记录
  语法：
  sql92语法
  update 表1 别名，表2 别名
  set 列=值1，列=值2…
  where 连接条件
  and 筛选条件；

sql99语法：
update 表1 别名
【连接类型】 join 表2 别名
on 连接条件
set 列=值1，列=值2…
where 筛选条件；

#修改单表的记录
#案例1：修改beauty表中姓唐的电话为124923212
UPDATE beauty SET phone='378273821' WHERE NAME LIKE '唐%';

#修改多表的记录
#案例：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE bo.`boyName`='张无忌';

#修改没有男朋友的女神的男朋友id都为2
UPDATE