【MySQL】详细版SQL用法 (含多表查询、约束、触发器)

  • DDL：用于定义和管理数据库结构（如创建、修改、删除表等）
  • DML：用于对表中的数据进行增删改
  • DQL：用于查询数据

DDL

数据库操作

  1. 查询所有的数据库：
1
show databases;
  1. 查询当前数据库是哪个：
1
select database();
  1. 创建：
1
create database [if not exists] 数据库名 [default charset 字符集]（collate 排序规则)
  1. 删除：
1
drop database [if exists] 数据库名;
  1. 使用某个数据库：
1
use 数据库名

表操作

  1. 查询当前数据库的所有表：
1
show tables;
  1. 查询表结构：
1
desc 表名;
  1. 创建：
1
2
3
4
5
6
create table (
    字段1  字段1类型 (comment 字段 1 注释),
    字段2  字段2类型 (comment 字段 2 注释),
    ...... 
)(comment 表注释);
create table 表名 as select *from 另一张表名   ----- 复制另一张表
  1. 添加字段：
1
alter table 表名 add 字段名 类型 (长度) [comment 注释] [约束];
  1. 修改数据类型:
1
alter table 表名 modify 字段名 新数据类型 (长度);
  1. 修改字段名和字段类型：
1
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
  1. 删除字段：
1
alter table 表名 drop 字段名;
  1. 修改表名：
1
alter table rename to 新表名;
  1. 删除表：
1
drop table [if exists] 表名;
  1. 删除指定表，并重新创建表：
1
truncate table 表名;

DML

  1. 给指定字段添加数据：
1
insert into 表名(字段1, 字段2...) values(值1, 值2...);
  1. 给全部字段添加数据：
1
insert into 表名 values(值1, 值2...);
  1. 批量添加：
1
2
insert into 表名(字段1, 字段2...) values(值1, 值2...), (值1, 值2),...；
insert into 表名 values(值1, 值2...), (值1, 值2),...；
  1. 修改数据：
1
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ...[where 条件];
  1. 删除数据：
1
delete from 表名 [where 条件];

DQL

select 查询

  1. 查询多个字段：
1
2
select 字段1, 字段2, .... from 表名;
select *from 表名;
  1. 设置别名：
1
select 字段1 [as 别名1], 字段2[as 别名2],...from 表名;
  1. 去除重复记录：
1
select distinct 字段列表 from 表名;
  1. 条件查询：
1
select 字段列表 from 表名 where 条件列表;
  • like 模糊查询 _，一个下划线表示一个占位符
  • 如选择名字为两个字的，where name like '__ __'；
  • '%' 的使用，如查询身份证号最后一位为 X 的，where id like '%X'； 这个是不管前面为多少，只管末位为 X
  1. 分组查询：
1
select 字段列表 from 表名 [where 条件] group by [having 分组过滤后的条件];

注意：

  • where 是分组之前进行过滤，不满足 where 条件的，不参与分组，而 having 是分组之后对结果进行过滤。
  • where 不能对聚合函数进行过滤，而 having 可以
  • 执行顺序：where > 聚合函数 > having
  • 分组后，一般查询的字段为聚合函数和分组的字段，其他字段无意义
  1. 排序查询：
1
select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式；

注意： 降序：desc，升序：asc，升序是默认，可以不写

  1. 分页查询：
1
select 字段列表 from 表名 limt 起始索引, 查询记录数;
  • 起始索引从 0 开始，起始索引 =（查询页码 - 1) * 每页显示记录数
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

聚合函数

  • count —> 统计数量
  • max —> 最大值
  • min —> 最小值
  • avg —> 平均值
  • sum —> 求和
1
2
select count (id) from 表名;
select count (*) from 表名;
  • 这是作用于列的
  • null 不参与聚合函数运算

约束

约束条件

约束 描述 关键字
非空约束 限制该字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一、不重复的 unique
主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 default
检查约束 保证字段值满足某一个条件 check
外键约束 用来让两张表的数据之间建立连接欸，保证数据的一致性和完整性 foreign key
自增 自动增长 auto_increment

外键约束

  1. 添加外键：
1
2
3
4
5
6
7
create table 表名 (
   字段名   数据类型
   ..........
  [constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);
或者
alter table 表名 add constraint 外键名称 foreign key [外键名称] references 主表[主表列名];
  1. 删除外键：
1
alter table 表名 drop foreign key 外键名称;
  1. 外键更新和删除行为：
行为 说明
No action 在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新
Restrict 与 no action 一致 Cascade
cascade 在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
set null 在父表中删除对应记录时，首先检查记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为空）
set default 父表有变更时，设置子表中外键值为一个默认值（innodb不支持） 
1
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名（主表字段名 on update cascade on delete cascade;

主键约束

  1. 创建：
1
2
3
4
5
6
7
create table 表名 (
   字段名   数据类型
   ..........
   constraint [主键名称] primary key (字段1，字段2...)
);
或者
alter table 表名 add constraint 主键名称  primary key(字段);
  1. 删除：
1
alter table 表名 drop primary key;

唯一约束

  1. 创建：
1
2
3
4
5
6
7
create table 表名 (
   字段名 数据类型
   ..........
  constraint [名称] unique(字段)
);
或者
alter table 表名 add constraint 名称 unique(字段);
  1. 删除：
1
alter table 表名 drop index 名称;

检查约束

  1. 创建：
1
2
3
4
5
6
7
create table 表名 (
   字段名   数据类型
   ..........
  constraint [名称] check(age>1 and age<20)
);
或者
alter table 表名 add constraint 名称 check(age>1 and age<20);
  1. 删除：
1
alter table 表名 drop constraint 名称;

触发器

  1. 创建：
1
2
3
4
5
6
create trigger 触发器名字
  before/after insert/update/delete
  on 表的名字 for each row
begin
  具体往哪张表插入什么数据
end;
  1. 查看触发器：
1
show triggers;
  1. 删除：
1
drop trigger 触发器的名字;

多表查询

连接

  1. 内连接：
  • 隐式内连接：
1
select 字段列表 from 表1, 表2 where 条件;
  • 显示内连接：
1
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
  1. 外连接：
  • 左外连接：表 1 为全部信息显示
1
select 字段列表  from 表1 left [outer] join 表2 on 条件;
  • 右外连接：表 2 为全部信息显示
1
select 字段列表 from  表1 right [outer] join 表2 on 条件;
  1. 自连接：
1
select 字段列表  from  表A  别名A  join 表A 别名B on 条件;

查询

  1. 联合查询：
1
2
3
select 字段列表 from 表A
union [all]           -----------------加了 all全部查询，没加 all，会去重
select 字段列表 from 表B;

注意：联合查询的列数需要保持一致，还有字段类型也要保持一致

  1. 子查询：
  • 标量子查询：
1
select *from 表名 where 字段名 = (select *from 表名 where 条件);
  • 列子查询：
1
select *from 表名 where 字段名 in (select *from 表名 where 条件);
操作符 描述
in 在指定的集合范围内选择，多选一
not in 不在指定范围内
any 有任意一个满足即可
Some 与 any 同理
all 所有值都必须满足
<> 不等于
  • 行子查询：示例：
1
select *from 表名 where (id，name)=(select id，name from 表名 where 条件);
  • 表子查询：返回的是多行多列，常用 in
1
select e.*, d.* from (select *from emp where 条件) e left join depet_id on e.dept._id=d.id;

事务

  • 概念：是一组操作的集合，不可分割，事务会把所有的操作作为一个整体一起向系统提交或撤销操作的请求，即这些操作要么同时成功，要么同时失败
  • 四大特性：
名称 描述
原子性 事务是不可分割的最小单元，要么全部成功，要么全部失败
一致性 事务完成时，必须使所有的数据都保持一致的状态
隔离性 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性 事务一旦提交或回滚，它对数据库中的数据改变就是永久的
  • 并发事务问题
问 题 描 述
脏读 一个事务读到另一个事务还没有提交的数据
不可重复读 一个事务先后读取同一条记录，但两次读取得数据不同
幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在
  • 事务的隔离级别
隔离级别 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read (默认) × × √
serializable × × ×