尚硅谷宋红康MySQL笔记 10-18

是记录，我不会记录的特别详细

第10章 创建和管理表

标识符命名规则

1. 数据库名、表名不得超过30个字符，变量名限制为29个
2. 只能包含 A–Z, a–z, 0–9, _共63个字符
3. 数据库名、表名、字段名等对象名中间不要包含空格
4. 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
   必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使
   用`（着重号）引起来
5. 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据
   类型在一个表里是整数，那在另一个表里可就别变成字符型了

数据类型

常用的

创建和管理数据库

创建数据库

//方式1:创建数据库
CREATE DATABASE 数据库名;
//方式2:创建数据库并指定字符集合
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
//方式3:判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

tips:数据库不能改名，一些工具可以改名，它是先建库，再复制所有表到新库，删除旧库.

使用数据库

• 查看当前所有数据库

SHOW DATABASES;

• 查看当前正在使用的数据库

SELECT DATABASE();

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE 数据库名\G;//这种在不使用可视化工具的前提下能好看点

• 使用/切换数据库

USE 数据库名;

修改数据库

• 更改数据库字符集
  乱码的时候需要用到这条命令

ALTER DATABASE 数据库名 CHARACTER SET 字符集; //字符集就比如utf8,gbk等等

删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;//存在就删，不存在就不执行操作

创建表

• 方式1

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
//IF NOT EXISTS:若存在该表那么就不创建，反之就创建

CREATE TABLE emp (
-- int类型
emp_id INT(8) AUTO_INCREMENT primary key,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

• 方式2
  使用AS subquery选项，将创建表和插入数据结合起来

CREATE TABLE table [(column1,column2,...)] AS subquery;

CREATE TABLE e1 AS SELECT * FROM employees;
CREATE TABLE e2 AS SELECT * FROM employeees WHERE 1=2;//创建的emp2是空表
CREATE TABLE dept1
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

查看数据表结构

SHOW CREATE TABLE 表名\G

修改表

这里是指修改表的结构，比如添加/删除/重命名列

添加列

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST | AFTER 字段名];

修改列

修改数据类型，长度，默认值和位置

ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST | AFTER 字段名2]

ALTER TABLE dept MODIFY last_name VARCHAR(30);//把last_name列的数据类型改为varchar(30)
ALTER TABLE dept MODIFY salary double(6,2) default 1000;//把salary的默认值改为1000
--重命名列
ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型;

ALTER TABLE dept CHANGE department_name dept_name varchar(15);//把department_name列改为 dept_name varchar(15)

ALTER TABLE 表名 DROP [COLUMN] 字段名;
ALTER TABLE dept DROP COLUMN job_id;

重命名表

• RENAME

RENAME TABLE emp TO emp1;

• ALTER TABLE

ALTER TABLE dept RENAME [TO] dept1;

删除表

DROP TABLE [IF EXISTS] 数据表1 [,数据表2,..,数据表n];

这玩意不能回滚

清空表

删除表中的所有数据，释放表的存储空间

TRUNCATE TABLE test;

不能回滚

扩展

• 阿里巴巴 《Java开发手册》之MySQL字段命名

• 表名，字段名必须使用小写字母或数字。
  如:xiyi_user,xiyi_config,xiyi3_username
• 表必备字段:id,gmt_create,gmt_modified
  id自增，bigint unsigned，gmt_create表示主动式创建，或者过去分词表示被动更新
• 表的命名最好遵循“业务名称_表的作用”
  如admin_config
• 库名尽量与应用名称保持一致

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
正例：无符号值可以避免误存负数，且扩大了表示范围。

• 如何理解清空表、删除表等操作需要谨慎?

因为MySQL在执行清空表、删除表操作时候不会有任何的确认提示，万一是误删除怎么办？所以在删除之前需要做一个备份
ALTER TABLE同理，表结构是无法撤销的，如果你添加了一个无关紧要的字段那删除了也不碍事，但是如果删除了一个需要的列，那该列的数据全部丢失，数据无价啊！！！

• MySQL8新特性—DDL的原子化
  就是DDL操作要么成功要么回滚，并且回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log(这玩意还是隐藏的,可以通过设置参数，将DDL操作日志打印输出到MySQL错误日志中)

DROP TABLE a,b;//b不存在

然后a,b两个表都没有删除.

第11章 表数据操作之增删改

这个我不会记录的详细

插入数据

• VALUES的方式添加

INSERT INTO 表名 [(COLUMN1,COLUMN2,...,COLUMNN)]
	VALUES (VALUE1,VALUE2,...,VALUEN)
	[,(VALUE1,VALUE2,...,VALUEN)];
INSERT INTO 表名
SET 列名 = 值,列名 = 值 ....

插入多条记录的时候最好选择使用单条INSERT语句的方式插入，如我有一张用户表，username,password,若是大量用户注册会产生如下语句

INSERT INTO `User` VALUES('ABC','ABC'),('ABCD','ABCD'),('ABCDE','ABCED');//这样效率高
INSERT INTO `User` VALUES('ABC','ABC');INSERT INSERT INTO `User` VALUES ('ABCD','ABCD');
INSERT INTO `User` VALUES ('ABCDE','ABCED');

• 将查询结果插入到表中
  把表A的查询结果插入到表B中,前提是结果集的列与表B的列保持一致(列名，数据类型相同)
  结果集的意思是:

source表有a,b,c,d,e,f,g列，target表有a,b,c列，现在把source表的a,b,c列插入到target表的a,b,c列
从source表中查出来a,b,c三列就是结果集

INSERT INTO target 
SELECT a,b,c
FROM source;

语法格式

INSERT INTO TargetTableName
(tar_column1 [,tar_column2,...,tar_columnn])
SELECT 
(src_column1 [,src_column2,...,src_columnn])
FROM SourceTableName
[WHERE condition];

INSERT INTO emp3
SELECT *
FROM employees
WHERE department_id = 100;

INSERT INTO emp4(id,name,salary,commission_pct)
SELECT employee_id,last_name,salary,commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

更新数据

语法

UPDATE table_name
SET column1 = value1 [,column2 = value2,...,columnn = valuen ] [WHERE condition];

这里记录一下更新多表数据

//下面的是SQL92语法
UPDATE table1 alias1,table2 alias2 SET COLUMN1 = VALUE1 [,COLUMN2=VALUE2,...,COLUMNN=VALUEN]
WHERE JoinCondition; 
//下面的是SQL99语法
UPDATE table1 alias1 [INNER | LEFT |RIGHT] JOIN table2 alias2 ON JoinCondition SET COLUMN1 = VALUE1 [,COLUMN2=VALUE2,...,COLUMNN=VALUEN];

删除数据

语法

DELETE FROM TableName [WHERE condition];

删除多表记录

//SQL92语法
DELETE Table1_alias,Table2_alias FROM Table1 alias1,Table2 alias2 WHERE JoinCondition;

//SQL99
DELETE Table1_alias,Table2_alias FROM Table1 alias1 [INNER | LEFT | RIGHT] JOIN Table2 alias2 ON JoinCondition;

MySQL8新特性:计算列

某一列是通过别的列计算得出的
如创建a表,d,e列,f列是由d列+e列得出的

CREATE TABLE a(
				id int,
				d,int,
				e,int,
				f int GENERATED ALWAYS AS (d+e) VIRTUAL
				);

第12章 MySQL数据类型

数据类型这东西，记住几个常用的,不知道选用什么类型的时候去查,查多了就记住了，我是这么干的

MySQL的数据类型

常见数据类型的属性:

整数类型

类型介绍

可选属性

• M

显示宽度,M的取值范围是(0,255),一般需要配合"ZEROFILL"来使用,表示用"0"填满宽度,否则指定显示宽度无效,只对数据宽度小于显示宽度的生效.并且显示宽度与类型可以存储的值范围无关，但是MySQL8以后的整数数据类型不推荐使用显示宽度属性

CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
);

• UNSIGNED
  无符号类型，就是非负，适合存储非负整数值，比如年龄
• ZEROFILL
  用0填充，如果某列是ZEROFILL，那么MySQL自动为当前列添加UNSIGNED属性,填充是在左边填充的(比如显示宽度为3，数据是1，在右边填充0会影响数据)必须和UNSIGNED ZEROFILL一起使用才有意义

适用场景

• TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
• SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
• MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
• INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
• BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

如何选择

主要考虑存储空间和可靠性的问题,但必须要确保数据不会超过取值范围，在这个基础上再去考虑如何节省存储空间，因为系统故障产生的成本远远超过增加几个字段存储空间所产生的成本.

浮点类型

可以处理小数，应用范围比整数大

• FLOAT:单精度浮点数
• DOUBLE双精度浮点数
  
• REAL:默认是DOUBLE,如果启用REAL_AS_FLOAT那么就是FLOAT，可以通过下面的语句来启用

SET sql_mode ="REAL_AS_FLOAT";

• ``Q1:FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？

所占的字节数不同,一般情况下，所占的字节数越大那么取值范围也大

• Q2:为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于 有符号数取值范围大于等于零的部分呢？

MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。无论有没有符号,MySQL的浮点数都会存储符号位,浮点数的无符号数的取值范围就是把负数的部分丢掉即保留大于等于0的部分

精度说明

MySQL8.0.17之后的，FLOAT(M,D)和DOUBLE(M,D)的用法官方文档不推荐使用，且FLOAT和DOUBLE的UNSIGNED也不推荐使用了

DOUBLE(M,D)

• M:精度,整数位+小数位
• D:小数位
  取值范围: D<=M<=255,0<=D<=30
  不指定的时候按照实际的精度(由硬件和操作系统决定)

超出了怎么办?

• 整数超过了范围就报错
• 小数超出了范围分情况

1. 情况1:若四舍五入后整数部分没有超出范围，就只是警告,但能成功操作并四舍五入删除多余的小数位后保存
2. 情况二:若四舍五入后整数部分超出了范围，MySQL报错,并拒绝存储

精度误差

他不够精准，是浮点数都有这个问题

CREATE TABLE A(t double(5,2));
INSERT INTO A VALUES(1.12),(2.13),(3.1415);
# 6.3915
SELECT SUM(t)=6.3915 FROM A; //这个的结果是0

在对精度要求特别高的时候，用定点数decimal，不要用浮点数

定点类型

就只有一种，decimal

存储方式以字符串的方式进行存储,不指定精度和标度的时候，默认是DECIMAL(10,0),当精度超过了定点数类型的精度范围时，MySQL同样会进行四舍五入处理。

位类型:BIT

存储的是二进制，比如1010010

没有指定M就是一位,这里的M是指二进制的位数,使用SELECT命令查询位字段的时候，可以用BIN()或HEX()函数进行读取，BIN()以二进制的方式展现,HEX()以十六进制的方式展现。b+0查询数据的时候，直接查询存储的十进制数据的值

SELECT f1+0 FROM test;
+--------+
| f1 + 0 |
+--------+
| NULL |
| 23 |
+--------+
2 rows in set (0.00 sec)

日期与时间类型

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

TIME可以表示一个时间间隔且可以超过24个小时

YEAR类型

存储格式:

• 以4位字符串或数字格式表示YEAR类型，格式为YYYY,最小值为1901，最大值为2155
• 以2为字符串格式表示YEAR类型，最小值为00，最大值为99
• 1. 当取值为01~69时表示2001到2069
• 2. 当取值为70~99时表示1970~1999
• 3. 当取值整数的0或00添加时，是0000年
• 4. 取值是日期/字符串的’0’添加，是2000年

但是MySQL5.5.27开始，2位格式的YEAR不推荐使用，YEAR的默认格式就是"YYYY"
从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

CREATE TABLE test(
f1 YEAR,
f2 YEAR(4)
);
mysql> DESC test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1 | year(4) | YES | | NULL | |
| f2 | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
INSERT INTO test
VALUES('2020','2021');
mysql> SELECT * FROM test;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
+------+------+
1 rows in set (0.00 sec)
INSERT INTO test
VALUES('45','71');
INSERT INTO test
VALUES(0,'0');
mysql> SELECT * FROM test;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

DATE类型

只存储日期，没有时间部分，格式位YYYY-MM-DD,需要三个字节的存储空间,在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

• 以 YYYY-MM-DD格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
• 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

CREATE TABLE test_date1(
f1 DATE
);
Query OK, 0 rows affected (0.13 sec)
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'),
('99-01-01'), ('990101');

INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);
INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());
SELECT *
FROM test_date1;

TIME类型

只表示时间，需要3个字节的存储空间来存储TIME类型的数据，可以使用"HH:MM:SS"格式来表示TIME类型,其中,HH表示小时，MM表示分钟，SS表示秒.

几种不同的格式

• 使用带有冒号的字符串,比如D HH:MM:SS,HH:MM:SS,HH:MM,D HH:MM,D HH,SS格式都可以,D表示天，范围是0~34,若使用带D的字符串插入到TIME字段中，D会转换为小时,计算格式:D*24+HH;反之就是表示当天的时间，比如12:10表示12:10:00
• 不带有冒号的字符串或者数字，格式为HHMMSS或者HHMMSS,插入一个非法的字符串或者数字的时候，MySQL会自动转换为00:00:00进行存储，1210，把最右边的解析成秒表示"00:12:10"
• 使用CURRENT_TIME()或者NOW()会插入当前系统时间

举例:

CREATE TABLE test_time1(
f1 TIME
);
Query OK, 0 rows affected (0.02 sec)
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;

DATETIME类型

在日期时间类型中所占的字节最大，需要8个，格式上就是DATE和TIME的组合，表示为YYYY-MM-DD HH:MM:SS
满足如下格式条件:

• 以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS，最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59
• 1. 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式
• 以 YY-MM-DD HH:MM:SS 格式或者YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
• 使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间

CREATE TABLE test_datetime1(dt DATETIME);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());

TIMESTAMP类型

和DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，4个字节的存储空间，只能存储1970-01-01 00:00:00~2038-01-19 03:14:07 UTC之间的时间，UTC表示世界统一时间

CREATE TABLE test_timestamp(ts TIMESTAMP);
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'),
('990101030405');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');

TIMESTAMP和DATETIME的区别

• TIMESTAMP的存储空间比较小，表示的范围也比较小
• TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0毫秒的毫秒值
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
• TIMESTAMP和时区有关

CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());
mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
#修改当前的时区
SET time_zone = '+9:00';
mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

开发中经验

用的最多的是DATETIME。注册时间、商品发布时间用时间戳来存储的

SELECT UNIX_TIMESTAMP();

文本字符串类型

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。``

CHAR与VARCHAR类型

CHAR类型

1. 一般需要指定字符串长度，不指定(M)，就是1个字符
2. 实际长度小于CHAR类型声明的长度，在右侧填充空格，MySQL检索CHAR类型的数据，CHAR类型的字段会去除尾部的空格
3. 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数

CREATE TABLE test_char1(
c1 CHAR,//默认是CHAR(1)
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1
VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;
INSERT INTO test_char1(c2)
VALUES('a ');
SELECT CHAR_LENGTH(c2)
FROM test_char1;

VARCHAR类型

这个必须要指定长度，检索该类型的数据的时候，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

CREATE TABLE test_varchar1(
NAME VARCHAR #错误
);
#Column length too big for column 'NAME' (max = 21845);
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) #错误
);
CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES('尚硅谷'),('尚硅谷教育');
#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar3
VALUES('尚硅谷IT教育');

如何选择?

1. 存储比较短的数据,长度固定的数据用CHAR
2. 存储长度变化幅度比较大的数据的时候用VARCHAR
3. 具体存储引擎中的情况

• MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。
• MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
  InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素
  是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，
  其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

TEXT类型

保存和查询数据的时候并没有删除TEXT类型的数据尾部的空格
四种类型，TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT

这玩意不能作为主键，只能采用CHAR(M)或者VARCHAR(M)

开发中经验

主要用来存储比较大的文本端

ENUM类型

设置字段值的时候，ENUM类型只允许从成员中选取单个值，所需要的存储空间由定义ENUM类型时指定的成员个数决定

• 当ENUM类型包含1~255个成员，需要1个字节的存储空间
• 当ENUM类型包含256~65535个成员时，需要两个字节的存储空间

INSERT INTO test_enum
VALUES('春'),('秋');
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

SET类型

SET表示一个字符串对象，可以包含0个或多个成员，成员个数的上限为64，设置字段值时，可以取取值范围内的 0 个或多个值。
SET类型包含的成员个数不同时，占用的存储空间也是不同,

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;


CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码');
INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败
INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功
);

二进制字符串类型

主要是用来存储一些二进制数据，比如图片的二进制数据，MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和
LONGBLOB类型。

BINARY与VARBINARY类型

存储的都是二进制字符串
BINARY(M)为固定长度的二进制字符串,M表示最多能存储的字节数，取值范围是0~255个字符，未指定(M)就是1个字节，不足数据宽度的时候在右边填充’\0’补齐指定长度
VARBINARY(M)可变，还需要1或2个字节来存储数据的字节数，总字节数不能超过行的字节长度限制65535.

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
# f3 VARBINARY,
f4 VARBINARY(10)
);
INSERT INTO test_binary1(f1,f2)
VALUES('a','a');
INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失败
INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
mysql> SELECT LENGTH(f2),LENGTH(f4)
-> FROM test_binary1;
+------------+------------+
| LENGTH(f2) | LENGTH(f4) |
+------------+------------+
| 3 | NULL |
| 3 | 2 |
+------------+------------+
2 rows in set (0.00 sec)

BLOB

二进制大对象，容纳可变数量的数据，BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，一般情况下会将图片、音频和视频文件存储到服务器的磁盘上,将路径存储到MySQL当中

TEXT和BLOB的注意事项

1. 在执行大量的更新或删除操作的时候，会在数据表中留下很大的空洞，所以为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理
2. 对大文本字段进行模糊查询，MySQL提供了前缀索引。
3. 把BLOB或TEXT列分离到单独的表中，会减少主表中的碎片

JSON类型

JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式。

CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');
mysql> SELECT *
-> FROM test_json;

当需要检索JSON类型的字段中数据的某个具体值时，可以使用->或->>符号

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'
AS province, js -> '$.address.city' AS city FROM test_json;

空间类型

这个我实在是记录不下去了，先水一下

空间类型扩展支持地理特征的生成、存储和分析,存储世界上具有位置的任何东西。MySQL使用Geometry（几何）表示所有地理特征

第13章 约束

一些概念

数据完整性(Data Integriry)是指数据的精确性(Accuracy)和可靠性(Reliability)。

约束主要是为了防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的，主要是从四个方面考虑

• 实体完整性（Entity Integrity） ：同一个表中确保记录的唯一性
• 域完整性（Domain Integrity） ：对于一些字段，取值应该在值域内，如性别只能在男女两者之间选。
• 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity） ：例如密码不能为空，邮箱不能为空

什么是约束

这个了解一下，
约束是表级的强制规定。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定
约束。

分类

看导图可能会直接点，图后面有文字版本

• 根据约束数据列的限制，约束可分为：

1. 单列约束：每个约束只约束一列
2. 多列约束：每个约束可约束多列数据

• 根据约束的作用范围，约束可分为：

1. 列级约束：只能作用在一个列上，跟在列的定义后面
2. 表级约束：可以作用在多个列上，不与列一起，而是单独定义

• 根据约束起的作用，约束可分为：

1. NOT NULL 非空约束，规定某个字段不能为空
2. UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
3. PRIMARY KEY 主键(非空且唯一)约束
4. FOREIGN KEY 外键约束
5. CHECK 检查约束
6. DEFAULT 默认值约束

查看某个表已有的约束

# information_schema数据库名 
# table_constraint 专门存储各个表的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名字';

NOT NULL 非空约束

限制某个字段的值不能为空,

特点

1. 所有的数据类型都可以是NULL
2. 只能出现在列上，不能组合非空
3. 可以多列限定非空
   注意：空字符串和0都不等于NULL

• 添加非空约束

1. 建表的时候

CREATE TABLE table_name(
	字段名 数据类型,
	字段名 数据类型 NOT NULL
);

2. 建表后

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

• 删除非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
# 去掉NOT NULL等同于NULL
# 或者
ALTER TABLE 表名称 MODIFY 字段名 数据类型;//默认就是NULL

唯一性约束

确保某个字段的值不能重复,但是可以出现多个NULL

特点

1. 可以有多个唯一约束
2. 可以是单列唯一，也可以是组合唯一
3. 唯一约束允许列值为空
4. 若不指定约束名就是列名1
5. MySQL会给唯一约束的列上默认创建一个唯一索引

添加唯一约束

• 建表时

CREATE TABLE 表名(
	字段名 数据类型,
	字段名 数据类型 UNIQUE,
	字段名 数据类型 UNIQUE KEY,
);
CREATE TABLE 表名(
	字段名 数据类型,
	[CONSTRAINT 约束名] UNIQUE KEY(字段名)
);

• 建表后

# 方式1:
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);
# 方式2:
ALTER TANLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

删除唯一约束

• 添加唯一性约束的列上会自动创建唯一索引
• 删除唯一约束只能通过删除唯一索引名的方式删除
• 若未指定索引名称，若是单列就是列名，组合列则默认和排在第一个的列名相同，可自定义约束名字

ALTER TABLE table_name DROP INDEX index_name;
# 查看表的索引
SHOW INDEX FROM table_name;

PRIMARY KEY约束

用来标识表中的一行记录

特点

1. 等同于 UNIQUE+NOT NULL
2. 主键名总是为PRIMARY
3. 主键约束对应着表中的一列或多列，若是多列则这些列不允许为空值,组合的值不允许重复
4. 一个表只能有一个主键约束
5. 创建主键约束时，默认会在所在的列或列组合上建立对应的主键索引（若是删除主键约束，主键约束对应的索引就自动删除）
6. 别修改主键字段的值，可能会破坏数据的完整性

添加

• 建表时指定主键约束

create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段列表) #表级模式
);

• 建表后添加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

删除

一般情况下不会这么干

ALTER TABLE 表名称 DROP PRIMARY KEY;

自增列 AUTO_INCREMENT

让指定字段的值自增

特点

1. 最多有一个
2. 可顺序增长，可指定初始值
3. 自增列约束的列必须为键列（主键列，唯一键列），并且列的数据类型必须是整数类型
4. 若不指定值就是在最大值的基础上加一，若指定就按指定的来

添加

• 建表时

create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);

• 建表后

ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO_INCREMENT;

删除

ALTER TABLE 表名 MODIFY 字段名 数据类型;#去掉了auto_increment了

MySQL8的新特性 自增变量持久化

这个举例说明比较好点，
下面的专业点

在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典
内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

这个是我自己复述的,有点啰嗦
MySQL8以前的版本是把当前自增列的最大值放在内存的一块区域，每次自增的时候去读取那块区域的值，在此基础上加一，若MySQL服务重启，内存被操作系统回收掉了，那么再次插入记录，自增列的值就是插入之前的最大值+1，MySQL8以后的版本是把自增列的最大值好像是放在文件里，这样重启服务也不碍事
看例子吧:

CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT;
);
INSERT INTO test VALUES(0),(0),(0);
# 查询数据
/*
| id |
+----+
| 1 |
| 2 |
| 3 |
*/
#删除id=3的记录
DELETE FROM test WHERE id = 3;
# 再次插入空值
INSERT INTO test VALUES (0);
# 再次查询
/*
| id |
+----+
| 1 |
| 2 |
| 4 |
*/
# 删除id=3的记录，再次插入的时候并不是在现有id列的最大值的基础上实现+1
# 删除id =4的
DELETE FROM test WHERE id =4;
#重启服务,插入空值
#Windows 是net restart MySQL80
#linux是 systemctl restart mysql
INSERT INTO test VALUES(0);
# 再次查询
/*
| id |
+----+
| 1 |
| 2 |
| 3 |
*/

MySQL8以后的不一样，还是按照以上的操作，但是最后一步的结果是

| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |

自增变量持久化了
MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志
中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

FOREIGN KEY 约束

这个阿里巴巴的文档中写的是不要使用这个，但是你要从应用的层面去完成FOREIGN KEY的效果

限定某个表的字段的引用完整性，这个举个例子比较好点,在学校填一些信息的时候，会收集你是那个班的，你不能填一个不存在的班级。

主表和从表/父表和子表

主表(父表):被引用的表，被参考的表，就是上面例子的班级名称
从表(子表):引用别人的表，参考的表，就是上面例子中的存放收集信息的表

特点

1. 从表的外键列必须引用主表的主键或唯一约束的列
2. 若不指定约束名会自动产生一个外键名(如:user_ibfk_1)，可自定义约束名
3. 创建表时指定外键约束，先主后从（先创建主表，再创建从表）
4. 删表时，先删除从表，再删除主表
5. 主表的记录被从表引用的时候，主表的记录不允许删除，若要删除，先删除从表中依赖该记录的数据，再删除主表
6. 从表中指定外键约束，并且一个表可以建立多个外键约束
7. 从表的外键列和主表被参照的列的名字可以不相同，但是数据类型必须一样。
8. 创建外检约束时，系统默认会在所在的列上建立对应的普通索引
9. 删除外键约束后，必须手动删除对应的索引

添加

• 建表的时候

create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

• 建表后
  通常表与表之间的关联都是提前设计好的，若创建表后要修改表的设计，按照如下来

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];

常见问题

• 失败：不是键列

create table dept(
did int , #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列

• 失败：数据类型不一致

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid char, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是从表的deptid字段和主表的did字段的数据类型不一致，并且要它俩的逻辑意义一致

约束等级

• Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录，就是级联
• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
• No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• Restrict方式 ：同no action， 都是立即检查外键约束
• Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
  默认是Restrict方式，对于外键约束，最好采用ON UPDATE CASCADE ON DELETE RESTRICT的方式,ON UPDATE CASCADE ON DELETE RESTRICT表示更新的时候采用Cascade方式，删除的时候采用Restrict方式。
  可以把ON UPDATE CASECADE的CASCADE换成上面的Cascade，Set null，No action，Restrict，Set default.
  这里我不会写的很详细

删除外键约束

1. 检查约束名和删除外键约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

2. 查看索引名和删除索引。这里只能手动删除

SHOW INDEX FROM 表名称; //查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

开发场景

1. 若两个表之间有关系，一定要建立外键约束吗？
   不是
2. 建不建外键约束有何区别
   建外键约束，操作会在语法层受到限制，这里的操作主要是指创建表、删除表、添加、修改、删除，下面的操作指的意思和这个一样
   不建外键约束，操作不受限制，但要保证数据的引用完整性，这个只能依靠程序员的自觉了，或者用程序来实现限定的效果。
3. 建不建外键约束和查询有何关系
   没有

阿里开发规范

不得用外键约束，一切外键概念必须在应用层解决。

说明：学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于 单 机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库 更新风暴 的风险；外键影数据库的 插入速度 。
强阻塞就是更新的太多了，要排队，这是我的理解

CHECK约束

检查指定字段值是否符合某某要求
MySQL5.7不支持，但是你写CHECK关键字不报错，但是没效果。MySQL8支持
举例

CREATE TABLE user(
id int primary key auto_increment,
username char(10) not null,
gender char(1) not null check ('男' or '女')
# gender char(1) not null check ( gender in ('男' or '女'))这样也可以
);

DEFAULT约束

给指定字段指定默认值文章来源地址https://www.toymoban.com/news/detail-663465.html

添加

• 建表时

create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
再举例：
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
# 说明：默认值约束一般不在唯一键和主键列上加

• 建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被
删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默
认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

删除

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

一些题目

1. 为何建表的时，加NOT NULL DEFAULT ''或DEFAULT 0
   不想让表中出现null值
2. 为何不想要null值

• 不好比较，null参与运算结果都是null，只能用is null和is not null
• 效率不高，影响索引效果

3. 带AUTP_INCREMENT约束的字段值是从1开始的？
   默认初始值是从1开始的，但是也可以指定
4. 每个表都可以任意选择存储引擎吗？
   不能，FOREIGN KEY不能跨引擎使用，主表采用的是Innodb,从表采用的是MyISAM那就不行

到了这里，关于尚硅谷宋红康MySQL笔记 10-18的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！