1 DML 操作表中的数据
用于对表中的记录进行增删改操作

1.1 插入记录

1
2
3
4
insert [into] 表名 [字段名] values (字段值)
insert into 表名：-- 表示往哪张表中添加数据
(字段名 1, 字段名 2, …)：-- 要给哪些字段设置值
values (值 1, 值 2, …)：-- 设置具体的值
1
2
3
4
5
6
7
8
-- 插入全部字段
-- 所有的字段名都写出来
insert into 表名 (字段名 1, 字段名 2, 字段名 3…) values (值 1, 值 2, 值 3);
-- 不写字段名
insert into 表名 values (值 1, 值 2, 值 3…);

-- 插入部分数据
insert into 表名 (字段名 1, 字段名 2, ...) values (值 1, 值 2, ...);

注：没有添加数据的字段会使用 NULL

1.2 蠕虫复制

将一张已经存在的表中的数据复制到另一张表中

1
2
3
4
-- 将表名 2 中的所有的列复制到表名 1 中
insert into 表名 1 select * from 表名 2;
-- 只复制部分列
insert into 表名 1(列 1, 列 2) select 列 1, 列 2 from student;

1.3 更新表记录

1
2
3
4
5
6
update 表名 set 列名=值 [where 条件表达式]
update: 需要更新的表名
set: 修改的列值
where: 符合条件的记录才更新
你可以同时更新一个或多个字段。
你可以在 where 子句中指定任何条件
1
2
3
4
5
-- 不带条件修改数据
update 表名 set 字段名=值; -- 修改所有的行

-- 带条件修改数据
update 表名 set 字段名=值 where 字段名=值;

1.4 删除表记录

1
2
3
delete 表名 [where 条件表达式]
如果没有指定 where 子句，MySQL 表中的所有记录将被删除。
你可以在 where 子句中指定任何条件
1
2
3
4
5
6
7
8
-- 不带条件删除数据
delete from 表名;

-- 带条件删除数据
delete from 表名 where 字段名=值;

-- 使用 truncate 删除表中所有记录
truncate table 表名;

truncate 和 delete 的区别： truncate 相当于删除表的结构，再创建一张表

2. DQL 查询表中的数据

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

2.1 简单查询

1
2
3
4
5
-- 使用*表示所有列
select * from 表名;

-- 查询指定列的数据,多个列之间以逗号分隔
select 字段名 1, 字段名 2, 字段名 3, ... fromm 表名;

2.2 指定列的别名进行查询

使用别名的好处： 显示的时候使用新的名字，并不修改表的结构

1
2
3
4
5
6
-- 对列指定别名
select 字段名 1 as 别名, 字段名 2 as 别名... from 表名;


-- 对列和表同时指定别名
select 字段名 1 as 别名, 字段名 2 as 别名... from 表名 as 表别名;

表使用别名的原因：用于多表查询操作

2.3 清除重复值

查询指定列并且结果不出现重复数据

1
select distinct 字段名 from 表名;

2.4 查询结果参与运算

1
2
3
4
5
-- 某列数据和固定值运算
select 列名 1 + 固定值 from 表名;

某列数据和其他列数据参与运算
select 列名 1 + 列名 2 from 表名;

注意: 参与运算的必须是数值类型

2.5 条件查询

1
2
select 字段名 from 表名 where 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

运算符

比较运算符 说明
>、<、<=、>=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==
BETWEEN…AND 在一个范围之内，如：between 100 and 200
相当于条件在 100 到 200 之间，包头又包尾
IN(集合) 集合表示多个值，使用逗号分隔
LIKE ‘张%’ 模糊查询
IS NULL 查询某一列为 NULL 的值，注：不能写=NULL

逻辑运算符

逻辑运算符 说明
and 或 && 与，SQL 中建议使用前者，后者并不通用
or 或 || 或
not 或 ! 非

in 关键字

1
2
select 字段名 from 表名 where 字段 in (数据 1, 数据 2...);
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示

范围查询

1
2
3
between 值 1 and 值 2
表示从值 1 到值 2 范围，包头又包尾
比如：age between 80 adn 100 -- 相当于： age>=80 && age<=100

like 关键字

1
2
-- like 表示模糊查询
select * from 表名 WHERE 字段名 like '通配符字符串';
通配符 说明
% 匹配多个字符串
_ 匹配一个字符

3. 高级DQL

1
2
3
4
-- 通过 order by 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
select 字段名 from 表名 where 字段=值 order by 字段名 [asc|desc];
asc: 升序，默认值
desc: 降序

单列排序

只按某一个字段进行排序，单列排序

1
2
-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;

组合排序

同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推

1
2
3
select 字段名 from 表名 where 字段=值 order by 字段名 1 [asc|desc], 字段名 2 [asc|desc];
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

3.2 聚合函数

使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值
聚合函数会忽略空值 NULL

五个聚合函数

SQL中的聚合函数 作用
max(列名) 求这一列的最大值
min(列名) 求这一列的最小值
avg(列名) 求这一列的平均值
count(列名) 统计这一列有多少条记录
sum(列名) 对这一列求总和 
1
2
3
4
select 聚合函数(列名) from 表名;
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;

我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列

1
2
3
4
5
ifnull(列名，默认值)   -- 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
-- 查询 id 字段，如果为 null，则使用 0 代替
select ifnull(id,0) from student;
-- 我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;

3.3 分组

1
2
-- 分组查询是指使用 group by 语句对查询信息进行分组，相同数据作为一组
select 字段 1,字段 2... from 表名 group by 分组字段 [having 条件];

group by 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

1
2
-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

having 与 where 的区别

子句 作用
where 子句 1) 对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤在分组
2) where后面不可以使用聚合函数
having 子句 1) having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤
2) having 后面可以使用聚合函数

3.4 limit 语句

limit 是限制的意思，所以 limit 的作用就是限制查询记录的条数

1
2
3
4
5
6
7
8
9
select *|字段列表 [as 别名] from 表名 [where 子句] [group by 子句][having 子句][order by 子
句][limit 子句];

LIMIT offset,length;
offset：起始行数，从 0 开始计数，如果省略，默认就是 0
length： 返回的行数

-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,6;

4. 表连接查询

4.1 多表查询

多表查询的作用

如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中，每张表取 1 列或多列

多表查询的分类

  1. 内连接
    • 隐式内连接
    • 显示内连接
  2. 外连接
    • 左外连接
    • 右外连接

4.2 笛卡尔积现象

什么是笛卡尔积

左表的每一条数据和右边的每一条数据组合,这种效果成为笛卡儿乘积

如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的
所以需要通过条件过滤掉没用的数据

1
2
3
4
5
-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

4.3 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

隐式内连接 : 看不到 JOIN 关键字，条件使用 WHERE 指定

1
2
3
select 字段名 from 左表, 右表 where 条件

select * from emp,dept where emp.`dept_id` = dept.`id`;

显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER

1
2
3
select 字段名 from 左表 [inner] join 右表 on 条件

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

4.4 左外连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示

左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略

1
2
3
4
5
6
7
8
select 字段名 from 左表 left [outer] join 右表 on 条件


-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;

-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;

4.5 右外连接

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证右表的数据全部显示

右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略

1
2
3
4
5
6
7
select 字段名 from 左表 right [outer]join 右表 on 条件

-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;

-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

5. 子查询

5.1 什么是子查询

子查询的概念

  1. 一个查询的结果做为另一个查询的条件
  2. 有查询的嵌套，内部的查询称为子查询
  3. 子查询要使用括号

5.2 子查询的结果是一个值的时候

子查询的结果是一个值的时候

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=

1
2
3
4
5
6
7
select 查询字段 from 表 where 字段=（子查询）;


-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

5.3 子查询结果是多行单列的时候

子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

1
2
3
4
5
6
7
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务
部'));

5.4 子查询的结果是多行多列

子查询的结果是多行多列

子查询结果只要是多列，肯定在 FROM 后面作为表

1
select 查询字段 from （子查询） 表别名 where 条件;

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

1
2
3
4
5
6
7
8
9
-- 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

-- 也可以使用表连接：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

6. 事务

6.1 事务的应用场景说明

什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的
转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败

事务执行是一个整体，所有的 SQL 语句都必须执行成功
如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败

6.2 手动提交事务

MYSQL 中可以有两种方式进行事务的操作：

  1. 手动提交事务
  2. 自动提交事务

手动提交事务的 SQL 语句

功能 SQL语句
开启事务 start transaction
提交事务 commit
回滚事务 rollback

手动提交事务使用过程

  1. 执行成功的情况： 开启事务 →执行多条 SQL 语句→成功提交事务
  2. 执行失败的情况： 开启事务→执行多条 SQL 语句→事务的回滚

6.3 自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务