SQL

DDL

#如果不存在则创建数据库
create database if not exists user default charset utf8mb4 collate utf8mb4_general_ci;
#查看所有数据库
show databases;
#使用数据库
use user;
#查看当前使用的是哪个数据库
select database();
#创建表
create table users (
    id int,
    name varchar(20) comment '年龄',
    age int,
    birth datetime
);
#查看所有表
show tables;
#查看创建表的语句
show create table user;
#查看表结构
desc user;
#添加新的字段
alter table user add email varchar(30);
#修改字段的数据类型
alter table user modify age tinyint unsigned comment '年龄';
#修改字段名和字段的数据类型
alter table user change birth birthday date comment '生日';
#删除字段
alter table user drop email;
#重命名表名
alter table user rename to user;
#截断表
truncate table user;
#如果存在则删除表
drop table if exists user;
#删库跑路
drop database if exists user;

DML

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

DQL

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段列表
having
    分组后条件列表
order by
    排序字段列表
limit
    分页参数

#查询多个字段
select 字段1, 字段2, ... from 表名;
select * from 表名;
#设置别名
select 字段1 [as 别名1] from 表名;
#去除重复记录
select distinct 字段列表 from 表名;

select * from user where name like "王%";
select * from user where name like "__";
select * from user where name is null;
select * from user where age in (20, 21, 22);
select * from user where age between 20 and 22;

select count(*) from sc;
select max(score) from sc;
select min(score) from sc;
select avg(score) from sc;
select sum(score) from sc;

select gender, count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;
select workaddress, count(*) from emp where age < 30 group by workaddress;
select workaddress, count(*) num from emp where age < 30 group by workaddress having num >= 3;

select * from emp order by age [asc];
select * from emp order by age desc;
select * from emp order by age desc, entrydate desc;
#查询前10条记录
select * from emp limit [0,] 10;
#查询索引从3开始的10条记录
select * from emp limit 3, 10;

DCL

use mysql;
#查询所有用户
select * from user;
#创建用户
create user 'tao'@'localhost' identified by '123456';
create user 'test'@'%' identified by 'test';
#修改用户密码
alter user 'test'@'%' identified with mysql_native_password by '123456';
#删除用户
drop user 'test'@'%';

#查询用户的权限
show grants for '用户名'@'主机名';
#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
#撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

字符串函数

数值函数

日期函数

流程函数

约束

create table user (
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique,
    age int check (age > 0 && age <= 120),
    status char(1) default '1',
    gender char(1)
);

#添加外键
create table 表名 (
    字段名 数据类型
    [constraint] [外键名称] foreign key (外键字段名) referemce 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
#删除外键
alter table 表名 drop foreign key 外键名称; 
#外键的更新和删除行为
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 连接条件 ...;

自连接

select 字段列表 from 表1 别名1 join 表2 别名2 on 连接条件 ...;

联合查询

#union会对合并后的数据进行去重，而union all不会
select 字段列表 from 表1 ...
union [all]
select 字段列表 from 表2 ...;

子查询

select * from t1 where column1 = (select column1 from t2);

#标量子查询
select * from emp where dept_id = (select id from dept where name = '研发部');

列子查询

select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '小明');

表子查询

select * from emp where (job, salary) in (select job, salary from emp where name = '小明' or name = '小红');

事务

事务开启方式

#查看事务提交方式
select @@autocommit;
#设置事务提交方式
set @@autocommit = 0;
#提交事务
commit;
#回滚事务
rollback;

#开启事务
start transaction;
begin;

事务的四大特性

• 原子性（Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency) :事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability)︰事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

事务隔离级别

#查询事务隔离级别
select @@transaction_isolation;
#设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable};