MySQL总结

发表于 | 分类于 |

mysql使用小技巧
.
W3C MySQL教程

常用补充

2023-09-22 10:58:24 补

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
# 登录 && 退出
mysql -u用户名 -p # 回车后输入密码
mysql -u用户名 -p密码 # 指定密码

quit / exit / ctrl+D # 退出

select version();   # 查看版本
select now();       # 显示时间

# 数据库
show databases;     # 查看所有库
select database();  # 查看当前数据库
create database 数据库 charset=utf8;  # 创建数据库
use 数据库;             # 使用数据库
drop database 数据库;   # 删除数据库

# 表
create table 表名(
    字段名 数据类型 可选的约束条件,
    column1 type condition,
    column2 type,
    ...
    columnN type,
    
    primary key(id)
);

show tables;  # 查看所有表
desc 表名;     # 查看表结构
show create table 表名;  # 查看表创建语句

# 表-结构修改
alter table 表名 add 列名 类型; # 添加字段
alter table 表名 change 原名 新名 类型及约束; # 重命名字段
alter table 表名 modify 列名 类型及约束; # 修改字段类型
alter table 表名 drop 列名; # 删除字段
drop table 表名;  # 删除表

# 添加数据
insert into 表名 values(...); # 全列插入 单行（顺序对应）
insert into 表名 values(...), (...), ...; # 全列插入 多行
insert into 表名 (列1, ...) values(值1, ...); # 部分列插入 单行
insert into 表名 (列1, ...) values(值1, ...), (...), ...; # 部分列插入 多行

# 查 / 改 / 删
select * from 表名;
update 表名 set 列1=值1, 列2=值2 where 条件;
delete from 表名 where 条件;

# 查询入表
insert into 表名(字段名) select语句;

# 连表更新
update 表1 join 表2 on 连接条件 set 某表.字段=值

# 视图 => sql封装的虚拟表（真正还是查表）
create view 视图名 as select语句; # 创建视图
show tables;         # 查看视图
select * from 视图名; # 查询视图
drop view 视图名;     # 删除视图

# 事务
begin; 或 start transaction;
commit;
rollback;

# 索引
alter table 表名 add index 索引名(可选) (字段名, ...); # 创建索引
show index from 表名;     # 查看索引
drop index 索引名 on 表名; # 删除索引

登录&退出

1
2
3
4
5
6
7
* MySQL登录
    1. mysql -uroot -p密码
    2. mysql -hip -uroot -p连接目标的密码
    3. mysql --host=ip --user=root --password=连接目标的密码
* MySQL退出
    1. exit
    2. quit

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
1. 什么是SQL？
    Structured Query Language：结构化查询语言
    其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
    
2. SQL通用语法
    1. SQL 语句可以单行或多行书写，以分号结尾。
    2. 可使用空格和缩进来增强语句的可读性。
    3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
    4. 3 种注释
        * 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) 
        * 多行注释: /* 注释 */
3. SQL分类
    1) DDL(Data Definition Language)数据定义语言
        用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
    2) DML(Data Manipulation Language)数据操作语言
        用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
    3) DQL(Data Query Language)数据查询语言
        用来查询数据库中表的记录(数据)。关键字：select, where 等
    4) DCL(Data Control Language)数据控制语言(了解)
        用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

注释&表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--查看表注释
SELECT table_name 表名, table_comment 表说明 FROM information_schema.TABLES
    WHERE table_schema = '表名' ORDER BY table_name
--查看单个表的字段注释
select COLUMN_NAME 字段名, column_comment 字段说明, column_type 字段类型, column_key 约束 from information_schema.columns
    where table_schema = '库名' and table_name = '表名' ;
--查看所有表和字段注释
select a.table_name 表名, a.table_comment 表说明, b.column_name 字段名, b.column_comment 字段说明, b.column_type 字段类型, b.column_key 约束
    from information_schema.tables a left join information_schema.columns b on a.table_name = b.TABLE_NAME
    where a.table_schema = '库名' order by a.table_name
--修改表注释
alter table 表名 comment = '注释';
--修改字段注释
alter table 表名 modify column 列名 int comment '注释';

--显示表信息
desc 表名;
--比上面全，显示表的全信息，包括注释
show full columns from 表名;
--显示表的创建SQL
show create table 表名;

DDL

数据定义语言（data definition language）

操作数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1. C(Create):创建
    * 创建数据库：
        * create database 数据库名称;
    * 创建数据库，判断不存在，再创建：
        * create database if not exists 数据库名称;
    * 创建数据库，并指定字符集
        * create database 数据库名称 character set 字符集名;

    * 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
        * create database if not exists db4 character set gbk;
        * create database if not exists db4 character set utf8;
2. R(Retrieve)：查询
    * 查询所有数据库的名称:
        * show databases;
    * 查询某个数据库的字符集:查询某个数据库的创建语句
        * show create database 数据库名称;
    * 查看字符集
        * show variables like '%char%';
3. U(Update):修改
    * 修改数据库的字符集
        * alter database 数据库名称 character set 字符集名称;
4. D(Delete):删除
    * 删除数据库
        * drop database 数据库名称;
    * 判断数据库存在，存在再删除
        * drop database if exists 数据库名称;
5. 使用数据库
    * 查询当前正在使用的数据库名称
        * select database();
    * 使用数据库
        * use 数据库名称;

INNODB/MYISAM

在这里插入图片描述

操作表

  • C(Create):创建
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    1. 语法：
        create table 表名(
            列名1 数据类型1,
            列名2 数据类型2,
            ....
            列名n 数据类型n
        );
        * 注意：最后一列，不需要加逗号（,）
        * 数据库类型：
            1. int：整数类型
                * age int,
            2. double:小数类型
                * score double(5,2)
            3. date:日期，只包含年月日，yyyy-MM-dd
            4. datetime:日期，包含年月日时分秒  yyyy-MM-dd HH:mm:ss
            5. timestamp:时间错类型  包含年月日时分秒     yyyy-MM-dd HH:mm:ss    
                * 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
            6. varchar：字符串
                * name varchar(20):姓名最大20个字符
                * zhangsan 8个字符  张三 2个字符
    * 创建表
        create table student(
            id int,
            name varchar(32),
            age int ,
            score double(4,1),
            birthday date,
            insert_time timestamp
        );
    * 复制表：
        * create table 表名 like 被复制的表名;
  • R(Retrieve):检索
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    * 查询某个数据库中所有的表名称
        * show tables;
        * show create table 表名称;
    * 查询表结构
        * describe 表名;
        * desc 表名;
    * 查看表t1的索引信息
        * SHOW INDEX FROM t1;
    * 最后一个插入的数据，自增ID的值是多少
        * SELECT @last := LAST_INSERT_ID();
  • U(Update):修改
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    1. 修改表名
        alter table 表名 rename to 新的表名;
    2. 修改表的字符集
        alter table 表名 character set 字符集名称;
    3. 添加一列
        alter table 表名 add 列名 数据类型;
    4. 修改列名称 类型
        alter table 表名 change 列名 新列别 新数据类型;
        alter table 表名 modify 列名 新数据类型;
    5. 删除列
        alter table 表名 drop 列名;
  • D(Delete):删除
    1
    2
    * drop table 表名;
    * drop table  if exists 表名 ;

DML

数据操作语言（Data Manipulation Language）

INSERT

1
2
3
4
5
6
7
8
添加数据：
 * 语法：
     * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
 * 注意：
     1. 列名和值要一一对应。
     2. 如果表名后，不定义列名，则默认给所有列添加值
         insert into 表名 values(值1,值2,...值n);
     3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
1
2
3
4
5
6
--正确，因为用于col2的值引用了col1，而col1已经被赋值
insert into tbl_name (col1,col2) values(15,col1*2);
--错误，因为用于col1的值引用了col2，而col2在col1之后被赋值
insert into tbl_name (col1,col2) values(col2*2,15);
--如果上面的col1是自增长，那么就等同于
insert into tbl_name (col2) values(15);
1
2
3
4
5
6
7
8
9
10
11
12
13
--如果t1存在主键s1=1，就更新数据s2=999【受影响行的值为2。】
--如果t1不是主键，或没有主键s1=1，就插入数据s1=1，s2=2【受影响行的值为1】
insert into t1 (s1,s2) values (1,2)
    on DUPLICATE key update s2=999;

--VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。
insert into table (a,b,c) values (1,2,3),(4,5,6)
    on DUPLICATE key update c=values(a)+values(b);
--本语句与以下两个语句作用相同：
insert into table (a,b,c) values (1,2,3)
    on DUPLICATE key update c=3;
insert into table (a,b,c) values (4,5,6)
    on DUPLICATE key update c=9;

UPDATE

1
2
3
4
5
修改数据：
 * 语法：
     * update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
 * 注意：
     1. 如果不加任何条件，则会将表中所有记录全部修改。
1
2
3
4
5
--先*2 再+5
update t2 set s1=s1*2,s1=s1+5 [where s2=1] [order by ...] [limit row_count]

--多表操作
update items,month set items.price=month.price where items.id=month.id;

DELETE

1
2
3
4
5
6
7
8
删除数据：
 * 语法：
     * delete from 表名 [where 条件]
 * 注意：
     1. 如果不加条件，则删除表中所有记录。
     2. 如果要删除所有记录
         1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
         2. truncate table 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
1
2
3
4
5
6
7
8
9
10
11
12
--删除单个表
delete from t1 [where s1=1] [order by timestamp_column] [limit 1]

--删除多个表【两种写法相同】
delete t1, t2 from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;
delete from t1, t2 using t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;

--如果设置别名，需要使用别名
delete t1 from test as t1, test2 where ...

--多表跨库删除
delete test1.tmp1, test2.tmp2 from test1.tmp1, test2.tmp2 where ...

DQL

数据查询语言（data query language）

执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

运算符

1
2
3
4
5
6
7
8
9
10
11
> 、< 、<= 、>= 、= 、<>
between...and  
in( 集合) 或 not in
is null 或 is not null
and  或 &&
or  或 || 
not  或 !
like：模糊查询
    占位符：
        _:单个任意字符
        %：多个任意字符

DCL

数据控制语言（Data Control Language）
【DBA：数据库管理员】

管理用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1. 创建用户：
    * 语法：create user '用户名'@'主机名' identified by '密码';
2. 删除用户：
    * 语法：drop user '用户名'@'主机名';
3. 修改用户密码：
    
    update user set password = password('新密码') where user = '用户名';
    update user set password = password('abc') where user = 'lisi';
    
    set password for '用户名'@'主机名' = password('新密码');
    set password for 'root'@'localhost' = password('123');

    * mysql中忘记了root用户的密码？
        1. cmd -- > net stop mysql 停止mysql服务
            * 需要管理员运行该cmd

        2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
        3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
        4. use mysql;
        5. update user set password = password('你的新密码') where user = 'root';
        6. 关闭两个窗口
        7. 打开任务管理器，手动结束mysqld.exe 的进程
        8. 启动mysql服务
        9. 使用新密码登录。
4. 查询用户：
    -- 1. 切换到mysql数据库
    use myql;
    -- 2. 查询user表
    select * from user;
    
    * 通配符： % 表示可以在任意主机使用用户登录数据库
1
2
3
4
5
create user 'username'@'host' identified by 'password';           #创建用户
grant privileges on databasename.tablename to 'username'@'host'   #授权用户
revoke all on *.* from 'username'@'host';                         #取消授权
#允许任何ip地址（上面的 % ）的电脑 用root帐户和密码（1234）来访问这个mysql
grant all on *.* to root@'%' identified by '1234' with grant option;

管理权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1. 查询权限：
    -- 查询权限
    show grants for '用户名'@'主机名';
    show grants for 'lisi'@'%';
    
2. 授予权限：
    -- 授予权限
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    -- 给张三用户授予所有权限，在任意数据库任意表上
    grant all on *.* to 'zhangsan'@'localhost';
    
3. 撤销权限：
    -- 撤销权限：
    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    revoke update on db3.`account` from 'lisi'@'%';

SELECT

版本

1
select version();

DUAL临时表

1
2
3
4
5
6
7
--在没有表被引用的情况下，允许您指定DUAL作为一个假的表名。
select 1 + 1 from dual;
-> 2

--不需要引用表，可以直接写。
select 1 + 1;
-> 2

having

HAVING不能用于应被用于WHERE子句的条目。

1
2
3
4
5
6
7
8
--错误写法
SELECT col_name FROM tbl_name HAVING col_name > 0;

--正确写法
SELECT col_name FROM tbl_name WHERE col_name > 0;

--HAVING子句可以引用总计函数，而WHERE子句不能引用
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10;

limit

1
2
3
limit子句可以被用于限制被select语句返回的行数。
两个自变量，1表示跳过多少条，2表示查询多少条
limit 5 等同于 limit 0,5
1
2
--返回第6~15条记录。
select * from tbl limit 5,10;

多表关联

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--别名：
inner join       等同于 cross join 等同于 join
left outer join  等同于 left join
right outer join 等同于 right join
union            等同于 union distinct

--笛卡尔积(交叉相乘)【一般我使用第一种】
select * from student,grade [where student.s_g_id=grade.g_id]
select * from student full join grade [where s_g_id=g_id]
select * from student inner join grade [on student.s_g_id=grade.g_id]
select * from student inner join grade [where s_g_id=g_id]
select * from student inner join grade [on student.s_g_id=grade.g_id] [where s_g_id=g_id]

--查询与左表记录数相等，关联不上的右边显示null
select * from student left join grade on student.s_g_id=grade.g_id [where s_id=1]
--也可以进行多表关联
select * from student left join (grade,my_user) 
 on (student.s_g_id=grade.g_id and student.s_id=my_user.u_id) [where s_id=1]
--查询与右表记录数相等，关联不上的左边显示null
select * from student right join grade on student.s_g_id=grade.g_id [where s_id=1]

--错误写法，`full join`不能有on关联
select * from student full join grade on student.s_g_id=grade.g_id
--错误写法，`left join`必须有on关联
select * from student left join grade
--错误写法，`right join`必须有on关联
select * from student right join grade

--关联两表的列需要对应。【union去重。union all不去重。】
select s_g_id from student union select g_id from grade 
select s_g_id from student union all select g_id from grade
  • 笛卡尔积(交叉相乘)【一般我的用法】：select * from student,grade where student.s_g_id=grade.g_id [and xxx='xxx']
  • 左连接连接规则如下（右连接也是同理，只不过以右表为准了）
    在这里插入图片描述

子查询

1
2
一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列）。
这些子查询被称为标量、列、行和表子查询。可返回一个特定种类结果的子查询经常只被用于特定的语境中

子查询作为标量操作数

1
2
3
4
5
6
7
8
create table t1 (s1 int, s2 char(5) not null);
insert into t1 values(100, 'abcde');
select (select s2 from t1);

在本SELECT中的子查询返回一个单一值('abcde')。
该单一值的数据类型为CHAR，长度为5，字符集和整序与在CREATE TABLE时有效的默认值相同，
    并有一个指示符号，指示列中的值可以为NULL。实际上，基本上所有的子查询都为NULL。
如果在本例中使用的表为空表，则子查询的值应为NULL。
1
2
3
4
5
6
7
8
9
drop table t1
create table t1 (s1 int);
insert into t1 values (1);
create table t2 (s1 int);
insert into t2 values (2);

select (select s1 from t2) from t1;

结果为2，因为t2中有一行包含s1，s1有一个值为2。
1
2
3
4
5
6
7
--正确写法
select max((select s1 from t1)) from t2;

--错误写法
select max(select s1 from t1) from t2;

不能忘记给子查询加上()

用子查询进行比较

语法

1
2
3
4
non_subquery_operand comparison_operator (subquery)

comparison_operator可以是以下操作符之一
=  >  <  >=  <=  <> !=
1
2
3
4
5
6
7
--表t1中有些值与表t2中的最大值相同。
select column1 from t1 
    where column1 = (select max(column2) from t2);

--查询某一列数据重复出现2次
select * from t1 as t
    where 2 = (select count(*) from t1 where t1.id = t.id);

in,any,some

语法

1
2
3
4
5
operand in (subquery)

--any 等同于 some
operand comparison_operator any (subquery)
operand comparison_operator some (subquery)
1
2
3
4
5
6
any的意思是“对于在子查询返回的列中的任一数值，如果比较结果为true的话，则返回true”

--in是＝any的别名
--但是not in不是<> any的别名，但是是<> all的别名
select s1 from t1 where s1 in    (select s1 from t2);
select s1 from t1 where s1 = any (select s1 from t2);

all

语法

1
operand comparison_operator all (subquery)
1
2
3
4
5
6
7
8
9
10
11
12
13
all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true。”

--t2有一条null，false【与null进行了比较】
select s1 from t1 where s1 > all (select s1 from t2);

--t2为空表，true【为空不比较】
select s1 from t1 where s1 > all (select s1 from t2);

--t2为空表，false【与null进行了比较，因为子查询作为标量null使用】【注意这是标量操作】
select * from t1 where 1 > (select s1 from t2);

--t2为空表，false【与null进行了比较，因为子查询返回null】
select * from t1 where 1 > all (select max(s1) from t2);

总结一下：只要后者有null，结果就为false。返回null。

1
2
3
4
--not in是<> all的别名。因此，这两个语句是相同的
--同样，子查询只要有null，false
select s1 from t1 where s1 <> all (select s1 from t2);
select s1 from t1 where s1 not in (select s1 from t2);

行子查询

1
2
这里讨论属于标量或列子查询，即返回一个单一值或一列值的子查询。
行子查询是一个能返回一个单一行的子查询变量，因此可以返回一个以上的列值。
1
2
3
4
5
6
7
8
--如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为true。
--(1,2)等同于row(1,2)
select * from t1 where (1,2) = (select column1, column2 from t2);
select * from t1 where row(1,2) = (select column1, column2 from t2);

--可以被优化
select * from t1 where (column1,column2) = (1,1);
select * from t1 where column1 = 1 and column2 = 1;
1
2
3
4
5
6
行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

--在表t1中查找同时也存在于表t2中的所有的行
select column1,column2,column3 from t1
    where (column1,column2,column3) in
    (select column1,column2,column3 from t2);

exists和not exists

1
2
3
--exists只要子查询记录大于0，无论是否有null，都会为true
--not exists只要子查询记录大于0，无论是否有null，都会为false
select column1 from t1 where exists (select * from t2);

优化子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--子查询，转化为联合
select distinct column1 from t1 where t1.column1 in (select column1 from t2);
select distinct t1.column1 from t1, t2 where t1.column1 = t2.column1;

--子查询的内部嵌套，转移到外部
select * from t1 where s1 in (select s1 from t1 union all select s1 from t2);
select * from t1 where s1 in (select s1 from t1) or s1 in (select s1 from t2);

select (select column1 + 5 from t1) from t2;
select (select column1 from t1) + 5 from t2;

--行子查询，代替关联子查询
select * from t1 where (column1,column2) in (select column1,column2 from t2);
select * from t1 where exists (select * from t2 where t2.column1=t1.column1 and t2.column2=t1.column2);

约束

简介

  • 概念
    1
    对表中的数据进行限定，保证数据的正确性、有效性和完整性。
  • 分类
    1
    2
    3
    4
    1. 主键约束：primary key
    2. 非空约束：not null
    3. 唯一约束：unique
    4. 外键约束：foreign key

非空约束

：not null，值不能为null

1
2
3
4
5
6
7
8
9
10
1. 创建表时添加约束
    create table stu(
        id int,
        name varchar(20) not null -- name为非空
    );
2. 创建表完后，添加非空约束
    alter table stu modify name varchar(20) not null;

3. 删除name的非空约束
    alter table stu modify name varchar(20);

唯一约束

：unique，值不能重复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1. 创建表时，添加唯一约束
    create table stu(
        id int,
        phone_number varchar(20) unique -- 添加了唯一约束
    
    );
    * 注意mysql中，唯一约束限定的列的值可以有多个null


2. 删除唯一约束，删除索引

    alter table stu drop index phone_number;

3. 在创建表后，添加唯一约束
    alter table stu modify phone_number varchar(20) unique;

主键约束

：primary key。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
1. 注意：
    1. 含义：非空且唯一
    2. 一张表只能有一个字段为主键
    3. 主键就是表中记录的唯一标识

2. 在创建表时，添加主键约束
    create table stu(
        id int primary key,-- 给id添加主键约束
        name varchar(20)
    );

3. 删除主键
    -- 错误 alter table stu modify id int ;
    alter table stu drop primary key;

4. 创建完表后，添加主键
    alter table stu modify id int primary key;

5. 自动增长：
    1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

    2. 在创建表时，添加主键约束，并且完成主键自增长
    create table stu(
        id int primary key auto_increment,-- 给id添加主键约束
        name varchar(20)
    );

    
    3. 删除自动增长
    alter table stu modify id int;
    4. 添加自动增长
    alter table stu modify id int auto_increment;

外键约束

：foreign key,让表于表产生关系，从而保证数据的正确性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1. 在创建表时，可以添加外键
    * 语法：
        create table 表名(
            ....
            外键列
            constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
        );

2. 删除外键
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
    例如：给学生表添加外键关联年级表
    ALTER TABLE `student` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`id`);

4. 级联操作
    1. 添加级联操作
        语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
                FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
    2. 分类：
        1. 级联更新：ON UPDATE CASCADE 
        2. 级联删除：ON DELETE CASCADE

备份&还原

参考MySQL 备份与还原

数据复制

1
2
3
4
5
6
7
8
9
10
11
--新表不存在，创建与旧表结构一样的新表，但不会设置主键，再把数据添加到新表。
create table new_table as select * from old_table
--同上，但不复制数据。
create table new_table as select * from old_table where 1=2;
--同上，完全复制结构，也会复制主键，不复制数据。
create table new_table like old_table

--新表已存在，把旧表的数据插入到新表，数据列需对应。
insert into new_table select * from old_table
--同上，但可以自定义插入哪些列，但新表的主键不自动生成，列中需要指定主键
insert into new_table(column1,column2...) select column1,column2... from old_table
1
2
3
--tableName2  表可以不存在，会在执行的过程中自动创建。
--待确认mysql不可用
select  column_name1,column_name2,....  into  tableName2  from  tableName1;

三大范式

  1. 概述
    1
    2
    3
    4
    5
    6
    7
    8
    1NF:字段不可分;
    2NF:有主键，非主键字段依赖主键;
    3NF:非主键字段不能相互依赖;

    解释:
    1NF:原子性 字段不可再分,否则就不是关系数据库;
    2NF:唯一性 一个表只说明一个事物;
    3NF:每列都与主键有直接关系，不存在传递依赖;
  2. 不符合第一范式的例子(关系数据库中create不出这样的表)：
    1
    2
    3
    表:字段1, 字段2(字段2.1, 字段2.2), 字段3 ......

    存在问题: 因为设计不出这样的表, 所以没有问题;
  3. 不符合第二范式的例子:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    表:学号, 姓名, 年龄, 课程名称, 成绩, 学分;
    这个表明显说明了两个事务:学生信息, 课程信息;

    存在问题:
        数据冗余：每条记录都含有相同信息；
        删除异常：删除所有学生成绩，就把课程信息全删除了；
        插入异常：学生未选课，无法记录进数据库；
        更新异常：调整课程学分，所有行都调整。
    修正:
        学生：Student(学号, 姓名, 年龄)；
        课程：Course(课程名称, 学分)；
        选课关系：SelectCourse(学号, 课程名称, 成绩)。
        满足第2范式只消除了插入异常。
  4. 不符合第三范式的例子:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    学号, 姓名, 年龄, 所在学院, 学院联系电话，关键字为单一关键字"学号";
    存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)

    存在问题:
        数据冗余:有重复值；
        更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况
        删除异常
    修正：
        学生：(学号, 姓名, 年龄, 所在学院)；
        学院：(学院, 地点, 电话)。

事务

基本介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
1. 概念：
    *  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2. 操作：
    1. 开启事务： START TRANSACTION; BEGIN【这一步也开启了手动提交】
    2. 回滚：ROLLBACK;【对此次事务的所有操作进行取消】
    3. 提交：COMMIT;【对此次事务的所有操作进行持久化】
3. 例子：
    -- 0. 开启事务
    START TRANSACTION;
    -- 1. 张三账户 -500
    
    UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
    -- 2. 李四账户 +500
    -- 出错了...
    UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
    
    -- 发现执行没有问题，提交事务
    COMMIT;
    
    -- 发现出问题了，回滚事务
    ROLLBACK;
4. MySQL数据库中事务默认自动提交
    
    * 事务提交的两种方式：
        * 自动提交：
            * mysql就是自动提交的
            * 一条DML(增删改)语句会自动提交一次事务。
        * 手动提交：
            * Oracle 数据库默认是手动提交事务
            * 需要先开启事务，再提交
    * 修改事务的默认提交方式：
        * 查看事务的默认提交方式：SELECT @@AUTOCOMMIT; -- 1 代表自动提交  0 代表手动提交
        * 修改默认提交方式： SET @@AUTOCOMMIT = 0;

四大特征

1
2
3
4
1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变

四种隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
* 概念：多个事务之间隔离的，相互独立的。
        但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
* 存在问题：
    1. 脏读：一个事务，读取到另一个事务中没有提交的数据
    2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
* 隔离级别：
    1. read uncommitted：读未提交
        * 可以读取未提交的数据
        * 产生的问题：脏读、不可重复读、幻读
    2. read committed：读已提交 （Oracle默认）
        * 只能读取已提交的数据
        * 产生的问题：不可重复读、幻读
    3. repeatable read：可重复读 （MySQL默认）
        * 是否读取其他事务提交修改后的数据
        * 产生的问题：幻读
    4. serializable：串行化
        * 可以解决所有的问题

    * 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
    * 数据库查询隔离级别：
        * SELECT @@TX_ISOLATION;--MySQL8之前
        * SELECT @@TRANSACTION_ISOLATION;--MySQL8【当前会话】
        * SELECT @@GLOBAL.TRANSACTION_ISOLATION;--MySQL8【系统】
    * 数据库设置隔离级别：
        * SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;【当前会话】
        * SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;【当前会话】
        * SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;【当前会话】
        * SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;【当前会话】
        * 系统需要把 SESSION 改为 GLOBAL

七种传播机制

可参考Spring事务的隔离级别和传播特性
别名：传播机制 传播特性 传播行为 传播方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 常用
REQUIRED:如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。（默认值）
SUPPORTS:支持当前事务，如果当前没有事务，就以非事务方式执行（没有事务）
===> 举例说明：
A有注解，B有注解。
1、REQUIRED：A有就加入A事务；      A没有，就创建B事务。
2、SUPPORTS：A有就加入A事务；      A没有，B就不用事务，即使B上有事务注解。

# 非常用
MANDATORY：使用当前的事务，如果当前没有事务，就抛出异常
REQUERS_NEW:新建事务，如果当前在事务中，把当前事务挂起。
NOT_SUPPORTED:以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
NEVER:以非事务方式运行，如果当前存在事务，抛出异常
NESTED:如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行 REQUIRED 类似的操作。

事务SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT @@TRANSACTION_ISOLATION;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;

SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 0;
SET @@AUTOCOMMIT = 1;

BEGIN;
START TRANSACTION;
ROLLBACK;
COMMIT;

个人理解

以下纯个人见解（有些东西自己知道，实在是难以表述，下面逻辑可能混乱。）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SESSION 与 GLOBAL 的区别：
    GLOBAL是对每次打开新会话时的默认隔离级别设置。
        例如：你设置了GLOBAL为SERIALIZABLE，你每次打开新会话时，默认隔离级别就是SERIALIZABLE
    SESSION是你对当前会话的隔离级别设置，跟GLOBAL没任何关系，真正使用的，还是SESSION的隔离级别设置。
        GLOBAL只是对打开新会话的默认设置而已，而真正当前会话的隔离级别用的还是SESSION的隔离级别。

开启事务与自动提交的区别：
    开始事务(BEGIN;或 START TRANSACTION;)只是针对要开启的这次事务进行设置了手动提交。
    如果设置了自动提交：每次操作都会自动提交事务，进行持久化。
    如果设置了手动提交：每次操作都要手动提交，才能持久化。

隔离级别
    1. READ UNCOMMITTED 读未提交
        开始事务后，可以读取到别人没有提交的数据。也就是脏读。
    2. READ COMMITTED 读已提交
        开始事务后，只能读取别人已提交的数据。也就解决了脏读。
        但是不可重复读，如果你开始事务，别人添加了一条记录并提交，你就看到多了一条记录。也就是不可重复读。
    3. REPEATABLE READ 可重复读
        如果我和你都开启了事务，你增删改了一条记录后，
            即使你提交了，我未提交的话，我查询到的数据也不会改变，
            只有当我也提交，才会改变。也就解决了不可重复读。
        如果我和你都开启了事务，你增加了一条记录，但未提交，我此时读取不到此条记录，
            但可以对此条记录进行修改，并卡在这条SQL不能执行任何操作，
            只有当你提交之后，我这条更新才能执行成功。也就是幻读。
    4. SERIALIZABLE 串行化
        对操作的表进行上锁，如果执行了对表的增删改操作的SQL，就会卡在这条SQL，
            从而不能执行任何操作，只有当其他人都提交之后（释放锁），
            你才能成功获取到锁，从而执行这条SQL。也就解决了全部的问题。
    
    对于：1,2,3，开启事务后，会对"行"进行上锁，只有提交或回滚才会释放行锁，
            当别人释放锁，自己才能拿到锁，从而执行对这行记录的增删改。
    对于：4，是对整张表进行上锁，如果对表进行增删改，只有当别人提交或回滚之后，
            释放了表锁，自己才能拿到锁，从而对整张表进行增删改操作。
    对于：幻读，根据对4的解释，我们可以知道，为什么叫幻读？
            我读取不到，但那条记录确确实实存在，我还可以对其进行修改。