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的解释,我们可以知道,为什么叫幻读?
我读取不到,但那条记录确确实实存在,我还可以对其进行修改。