MySQL语句大全

数据库操作

-- 显示数据库
show databases;

-- 创建一个数据库 demo
create database demo;

-- 若不存在数据库 demo 则创建一个数据库 demo；反之不创建 demo
create database if not exists demo;

-- 删除数据库 demo
drop database demo;

-- 若存在数据库 demo 则删除数据库 demo
drop database if exists demo;

-- 使用数据库 demo
use demo;

-- 查询当前数据库
select database();

数据表操作

-- 查看当前数据库的所有表，注意先选中某一个数据库
show tables;

-- 查看表结构
desc tables;

-- 查询指定表的建表语句
show create table 表名;

-- 创建表
create table 表名(

	字段1  字段1类型 comment  字段1注释,
	字段2  字段2类型 comment  字段2注释,
	字段3  字段3类型 comment  字段3注释,
	 ......
	字段n  字段n类型 comment  字段n注释

) comment  表注释;

-- 删除表 demo，包括原表中的数据
drop table demo;

-- 若存在表 demo 则删表 demo
drop table if exists demo;

-- 为表添加一个新字段
alter table 表名 add 字段名 类型(长度) comment 注释;

-- 修改表中的字段名
alter table 表名 modify 字段名 新数据类型(长度);

-- 修改表中的字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) comment 注释;

-- 删除表中的字段
alter table 表名 drop 字段名;

-- 修改表名
alter table 表名 rename to 新表名;

往表中插入数据

-- 给指定字段添加数据
insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);

-- 给全部字段添加数据
insert into 表名 values (值1, 值2, ...);

-- 批量添加数据
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

修改表中数据

-- 为指定的字段修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... where 条件;

-- 当没有 where 条件时，则会修改整张表的某字段数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2;

删除表中数据

-- 根据条件删除表中某一行的记录
delete from 表名 where 条件;

-- 当没有 where 条件时，则会删除整张表的数据；
-- 但整张表还在，只不过是一张空表
delete from 表名;

查询表中数据

-- 查询表中的多个字段, select 后紧跟的字段即需要展示出来的字段
select 字段1, 字段2, 字段3 ... from 表名;

-- 查询表中的所有字段
select * from 表名;

-- 查询表，并为字段设置临时的别名
select 字段1 as 别名1, 字段2 as 别名2 ... from 表名;

-- 查询字段去重
select distinct 字段 from 表名;

-- 条件查询
select 字段1, 字段2, 字段3 ... from 表名 where 条件列表;

-- 聚合函数，可搭配条件查询
select 聚合函数(字段) from 表名;

-- 分组查询
-- where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤
-- select 后紧跟的字段列表一般为聚合函数或分组字段，查询其他字段无意义
select 字段列表 from 表名 where 条件 group by 分组字段名 having 分组后过滤条件;

-- 排序查询, asc 升序, desc 降序
-- oder by 默认就是 asc
-- 若进行多字段排序，先按字段1排序，之后基于相同的字段1，再按字段2排序，一次类推...
select 字段列表 from 表名 oder by 字段1 排序方式1, 字段2 排序方式2 ...;

-- 分页查询，可自定义显示某一页显示多少记录数
-- 第一页的起始索引从 0 开始
-- 后面页的起始索引 = （查询页码 - 1）* 每页显示记录数。
select 字段列表 from 表名 limit 起始索引, 查询记录数;

关于查询数据的编写顺序与执行顺序：

用户管理

-- 创建用户
create user '用户名'@'主机名' identified by '密码';

-- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

-- 删除用户
drop user '用户名'@'主机名';

-- 查询权限
show grants for '用户名'@'主机名' ;

-- 授予权限
-- 数据库名和表名可以使用 * 进行通配，代表所有。
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

-- 字符串函数
concat(),
lower(),
upper(),
lpad(),
rpad(),
trim(),
substring()

-- 数值函数
ceil(),
floor(),
mod(),
rand(),
round()

-- 日期函数
curdate(),
curtime(),
now(),
year(),
month(),
day(),
date_add(),
datediff()

-- 流程函数
if(),
ifnull(),
case when ... then ... else ... end

约束 (限制表中的字段值)

-- 添加非空约束
create table 表名(
	字段名 数据类型 not null,
	...
);

-- 添加唯一约束
create table 表名(
	字段名 数据类型 unique,
	...
);

-- 添加主键约束
create table 表名(
	字段名 数据类型 primary key,
	...
);

-- 添加默认约束
create table 表名(
	字段名 数据类型 default,
	...
);

-- 添加检查约束，仅 MySQL 8.0.16 版本之后支持
create table 表名(
	字段名 数据类型 check(),
	...
);

-- 添加外键约束，至少涉及两张表
create table 表名(
	字段名 数据类型,
 	...
 	constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名)
);

-- 修改外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);

-- 删除外键约束
alter table 表名 drop foreign key 外键名称;

-- 外键的更新和删除行为

-- 外键默认，当有子表的字段依赖父表的字段时，不允许删除父表的字段，但允许删除子表的字段
no action

-- 同 no action
restrict

-- 当有子表的字段依赖父表的字段时，删除父表的字段，就会删除子表的字段
cascade

-- 当有子表的字段依赖父表的字段时，删除父表的字段，就会将子表的字段设置为 null
set null

-- 当有子表的字段依赖父表的字段时，删除父表的字段，就会将子表的字段设置为默认值
set default

-- 删除和更新外键，使用 cascade 例子
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名) on update cascade on delete cascade;

多表查询

-- 内连接用于查询两张表交集的部分

-- 内连接，写法一
select 字段列表 from 表1, 表2 where 连接条件;
-- 内连接，写法二
select 字段列表 from 表1 inner join 表2 on 连接条件;

-- 左外连接用于查询左表 + 两张表交集的部分
select 字段列表 from 表1 left outer join 表2 on 连接条件; 

-- 右外连接用于查询右表 + 两张表交集的部分
select 字段列表 from 表1 right outer join 表2 on 连接条件;

-- 自连接，将同一张表查询两次
-- 将同一张表分别视为两张表： 表A 和 表B，然后 表A 连接 表B
select 字段列表 from 表1 as 别名A join 表1 as 别名B on 连接条件;

-- 联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
-- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
-- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
select 字段列表 from 表1  ...  
union all
select 字段列表 from 表1  ...;

-- 子查询，SQL 语句中嵌套 select 语句
-- 子查询的核心思想，后面嵌套的语句有一个返回值，类似于 C语言中的一个函数的返回值，
-- 利用前面的主句 + 后面的返回值，就可以得出查询的结果

-- 标量子查询，嵌套的子查询结果为单个值
-- 列子查询，嵌套的子查询结果为一列
-- 行子查询，嵌套的子查询结果为一行多列
-- 表子查询，嵌套的子查询结果为多行多列
select 字段列表 from 表1 where column = ( select column from 表2 );

事务

-- 开启事务
start transaction;

	...SQL代码...
	...SQL代码...
	
-- 如果正常执行完毕, 则提交事务	
commit;

-- 如果执行过程中报错, 则回滚事务
rollback;

-- 查看事务隔离级别
select @@transaction_isolation;

-- 设置事务隔离级别
set session transaction isolation level 事务隔离级别;

-- 事务隔离级别分类
read uncommitted (未解决所有问题)
read committed (解决了脏读问题)
repeatable Read (MySQL默认，解决了脏读，不可重复读问题)
serializable (解决了脏读，不可重复读，幻读问题)