第3天：SQL语句的增删改查

 

MySQL中查看当前连接的自动事务提交是否开启：

SHOW VARIABLES LIKE '%autocommit%';

MySQL关闭本次事务自动提交连接操作：

SET autocommit = 0;

注意：这种做法只能够关闭当前连接的自动事务提交功能，但是每次重新连接数据库，都会失效。

②数据添加操作

关键字：insert

（1）向全部字段中添加数据：

在向全部字段中添加数据的时候，可以不用手动声明字段名称

INSERT INTO job_level VALUES ('实习工资'，1000.00,2999.00);
COMMIT;

（2）向部分字段中添加数据：

如果在插入数据的时候仅是向部分字段进行添加，那么必须手动声明字段名称；

并且在插入数据的时候，需要保证数据顺序和字段顺序一致。

INSERT INTO job (job_title,min_salary,max_salary) VALUES('董事长夫人',12999.00,13000.00);
COMMIT;

(3)向数据库表进行批量添加数据：

属于MySQL数据库方言

INSERT INTO location (city) VALUES ('武汉'),('苏州'),('南京'),('台北'),('无锡');
COMMIT;

③数据库删除操作：

关键字：delete

（1）全部删除操作：

DELETE FROM location;

（2）条件删除操作：

DELETE FROM location WHERE location_id >=9;

④数据修改操作：

（1）全修改操作：

UPDATE job SET job_title = '小秘',min_salary = 9999.99,max_salary = 99999.99;

（2）条件修改操作：

UPDATE job SET job_title = '小秘',min_salary = 9999.99,max_salary = 99999.99 WHERE job_id = 7;

#2.DQL

作用：执行数据查询操作，不需要使用数据库操作事务操作。

关键字：select

①查询表中全部数据：

SELECT * FROM emp;

注意：在查询数据库中所有字段的时候，不推荐使用*，而是推荐将所有的字段名称都写在SQL语句中。

②条件查询

（1）等值查询

#查询emp表中2号部门的全部员工
#一般的等值查询，使用=即可判断
SELECT * FROM emp WHERE dept_id = 2;

#查询emp表中谁是老板
#空值的等值查询，需要使用关键字is
SELECT * FROM emp WHERE manager_id IS NULL;

（2）不等值查询

#查询emp表中除了2号部门之外其他的员工
SELECT * FROM emp WHERE dept_id != 2;

#非空查询
SELECT * FROM emp WHERE dept_id IS NOT NULL;

（3）范围查询

#查询薪资在5000.00以上的所有员工信息
SELECT * FROM emp WHERE salary >= 5000.00;

#查询薪资在8000.00以下的所有员工信息
SELECT * FROM emp WHERE salary <= 8000.00;

#查询薪资在5000.00~8000.00之间的所有员工信息
SELECT * FROM emp WHERE salary >= 5000.00 AND salary <= 8000.00;
SELECT * FROM emp WHERE salary BETWEEN 5000.00 AND 8000.00;

#查询1，3，4，6部门下所有的员工信息
SELECT * FROM emp WHERE dept_id = 1 OR dept_id = 3 OR dept_id = 4 OR dept_id = 6;
SELECT * FROM emp WHERE dept_id IN (1,3,4,6);

（4）模糊查询

#查询名字当中以丝为结尾的，长度为2的员工信息
SELECT * FROM emp WHERE e_name LIKE '_丝';

#查询名字当中包含丝的员工信息，名字长度不限
SELECT * FROM emp WHERE e_name LIKE '%丝%';

（5）逻辑连词：

and：与条件，全真为真，一假全假

or：或条件，有真则真，全假为假

(6)分组查询

关键字：group by

#查询每个部门下的最高薪资
SELECT MAX(salary) FROM emp GROUP BY dept_id;

#查询每个工种下的最高薪资
SELECT MAX(salary) FROM emp GROUP BY job_id;

#查询薪资在6000.00以上的所有员工，并按照部门进行分组之后查询每一组的平均薪资
SELECT AVG(salary)
FROM emp
WHERE salary > 6000.00
GROUP BY dept_id;

#查询薪资在6000.00以上的所有员工，并按照部门进行分组之后统计人数
SELECT dept_id, COUNT(*)
FROM emp
WHERE salary > 6000.00
GROUP BY dept_id;

#统计每组员工每月的薪资总支出
SELECT dept_id, SUM(salary) FROM emp GROUP BY dept_id;

常见的分组函数：

max()：求取最大值

min()：求取最小值

avg()：求取平均值

count()：计总数

sum()：求和

③组筛选:

关键字：having

#统计每组员工的平均薪资，并且只保留平均薪资在6999.00以上的组
SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id HAVING avg_salary > 6999.00;

④排序操作：

关键字：order by  asc  desc 

#查询公司全部员工信息按照入职时间进行排序
SELECT * FROM emp ORDER BY hire_date;

#查询公司全部员工信息按照入职时间进行降序排序
SELECT * FROM emp ORDER BY hire_date DESC;

#查询公司中全部员工信息，按照部门编号升序排序，相同部门的员工按照工资降序排序
SELECT * FROM emp ORDER BY dept_id ASC, salary DESC;

⑤分页查询：

关键字：limit

#假设一页显示10条记录，查询第2页的emp表数据
SELECT * FROM emp LIMIT 10,10;
#LIMIT 关键字的两个参数：
# 参数1：表示当前页中数据的起始下标，取值从0开始
# 参数2: 表示当前页的篇幅
# 假设给定查询页码page和页面篇幅size，那么page页的起始记录下标：
# startIndex = （page-1）* size；

⑥查询语句关键字的执行顺序：

1.from：从目标数据表中取得所有字段和所有记录

2.where：针对每一条数据记录进行筛选之后，留下所有符合条件的记录

3.group by：将上一步骤中所有符合条件的单条记录按照分组要求进行筛选，留下所有符合条件的记录

4.having：在执行分组之后执行分组函数，然后按照分组函数的计算结果决定哪些组留下，哪些组舍弃

5.select：决定查询结果集合保留哪些字段

6.order by：根据某些指定的字段进行排序

⑦子查询：

定义：将一次查询的结果作为另一次（外层）查询的条件或者数据源使用

（1）使用子查询当作数据源

#子查询：使用内部查询结果作为外层查询数据源
SELECT * FROM (SELECT * FROM emp WHERE salary > 5000.00)AS e WHERE e.salary < 8000.00;

注意1：这种子查询的结果集可以是多行多列的，但是子查询的结果集必须包含外层查询的所需字段

注意2：我们需要为子查询的结果集起一个别名

（2）使用子查询作为外层查询条件使用：

情况1：一行一列一个数

#查询公司中工资大于平均薪资的员工信息
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

情况2：多行一列多个数

#查询哪些组的平均薪资在8200.00以上，查询这些组的员工有谁
SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING AVG(salary) > 8200.00);

⑧级联查询：

作用：可以实现跨表查询

原理：基于笛卡尔乘积的条件筛选

    集合A={1，2，3}，集合B={a,b,c}

    笛卡尔乘积：A×B={1a,1b,1c,2a,2b,2c,3a,3b,3c}

（1）十字连接：

关键字：cross join

作用：没有太大的实际作用，就是表示两张数据表进行全排

#十字连接
SELECT e.*, d.*
FROM emp AS e CROSS JOIN dept AS d;

（2）左外连接：

关键字：left outer join....on

作用：在笛卡尔乘积的基础上，根据on后面的级联条件筛选记录，并且保证左表中的未匹配项。

#左外连接
SELECT e.*,d.* FROM emp AS e LEFT OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;

（3）右外连接：

关键字：reight outer join....on

作用：在笛卡尔乘积的基础上，根据on后面的级联条件筛选记录，并且保证右表中的未匹配项。

#右外连接
SELECT e.*,d.* FROM emp AS e RIGHT OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;

（4）内连接：

关键字：inner join....on

作用：在笛卡尔乘积的基础上，根据on后面的级联条件筛选记录，左右两张数据表中的未匹配项都不保留

#内连接
SELECT e.*,d.* FROM emp AS e INNER JOIN dept AS d
ON e.dept_id = d.dept_id;

(5)全外连接：

关键字：full outer join....on

作用：在笛卡尔乘积的基础上，根据on后面的级联条件筛选记录，左右两张数据表中的未匹配项都保留

#全外连接
SELECT e.*,d.* FROM emp AS e FULL OUTER JOIN dept AS d
ON e.dept_id = d.dept_id;

注意：MySQL数据库不支持全外连接。