22-05-10 西安 mysql基础篇（02） 修改表 、表数据之增删改 DML 、列级约束、表级约束

我们的情绪背后藏着动机，动机总是正面的，因为意识从来不会伤害自己，只是误以为某些行为可以满足自己的这份动机。

---

MySQL字符集设置

字符编码

在8.0版本之前，MySQL默认的字符集为Latin1，而8.0版本默认字符集为utf8mb4。 Latin1是ISO-8859-1的别名

mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记**utf8**，永远使用**utf8mb4**。

查看mysql版本

select version();

---

mysql5.7版本

mysql5.7版本 插入数据如果是中文，会报错

INSERT INTO employees VALUES(1003,'杰瑞');

为什么报错呢

创建数据库和数据表的时候，如果没有显示的指明使用什么字符集，就是使用查看编码指令下的编码，默认是latin1

查看编码指令

SHOW VARIABLES LIKE 'character_%';

mysql5.7默认如下：

=====================

当然你可以去查看建表、建库语句，从而知道employees表使用的是什么字符集

怎么在建表建库时不显示指定字符集，默认使用utf8呢

要改my.ini。把这3行粘贴到相应位置

配置文件格式：

配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[]扩起来

重启mysql服务， 重新查看编码

---

mysql8.0版本

在mysql8.0下是utf8mb4，是用4个字节表示一个字符，uft8的升级版

SHOW VARIABLES LIKE 'character_%';

utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。如果原来某些库和表的字符集是utf8，可以直接修改为utf8mb4，不需要做其他转换。但是从uft8mb4转回utf8就会有问题。

---------------------

MySQL中utf8字符集，它是utf8mb3的别称，使用三个字节编码表示一个字符。自MySQL4.1版本被引入，能够支持绝大多数语言的字符，但依然有些字符不能正确编码，如emoji表情字符等，为此MySQL5.5引入了utf8mb4字符集。在MySQL5.7对utf8mb4进行了大幅优化，并丰富了校验字符集。mb4就是“most byte 4”的意思，专门用来兼容四字节的Unicode，

---

字符集指定

创建库、创建表、创建字段的时候，都可以指明字符集

• 创建字段未声明字符集，则按照表声明的字符集，
• 如果表也未声明，则按照所在库的字符集，
• 如果库也未声明则按照my.ini中的配置。

可以使用如下sql查看

SHOW VARIABLES LIKE 'character_%';

---

创建表和修改表

创建表

CREATE table 表名(字段1 字段1的数据类型 【约束】,
		          字段2 字段2的数据类型 【约束】,
		          ......
		         )【约束】;

要自增的列必须是主键

1. 新建表

# 新建表
CREATE TABLE `dept1`(
     `id` INT(7),
     `name` VARCHAR(25) -- name是保留字可以用``
);

2. 基于现有表创建新表   as关键字

# 基于现有表创建新表(相当于表的复制)
CREATE TABLE dept2
AS
SELECT * FROM `departments`;

第2种创建表的方式，不想要表数据时，只要表结构 可以加一个 where 1=2；

实际使用举例：

DROP TABLE IF EXISTS `mail_send_log`;
CREATE TABLE `mail_send_log` (
  `msgId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `empId` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT '0' COMMENT '0发送中，1发送成功，2发送失败',
  `routeKey` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `exchange` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `count` int(11) DEFAULT 0 COMMENT '重试次数',
  `tryTime` datetime DEFAULT NULL COMMENT '第一次重试时间',
  `createTime` datetime DEFAULT NULL,
  `updateTime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ENGINE 设置存储引擎，CHARSET 设置编码。

---

COLLATE

总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

COLLATE通常是和数据编码（CHARSET）相关的

例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。

show collation;

以utf8mb4为例，该编码所支持的所有COLLATE如下图所示

utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的

推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。

---

修改表

1.添加列，有无默认值 add
2.修改列的类型和长度。modify
3.重命名列  change [也可以修改列的类型]
4.删除列，drop 
5.重命名表 rename

-- 修改表 ALTER TABLE
-- 修改表emp5

-- 添加列 可以有默认值 DEFAULT 0;
ALTER TABLE emp5
ADD test_column VARCHAR(2);


-- 修改列 修改列的类型和长度
ALTER TABLE emp5
MODIFY gender VARCHAR(20);


-- 重命名列 重命名的同时也可以修改列的类型和长度
ALTER TABLE emp5
CHANGE gender gender222 VARCHAR(2);

-- 删除列
ALTER TABLE emp5
DROP COLUMN gender222;

-- 重命名表
ALTER TABLE emp5
RENAME empy6;

---

删除表

删除表 drop 【 DROP则删除整个表（结构和数据）】
delete from表名   删除表中数据[可以用rollback回滚表中数据]

-- 清空表  TRUNCATE 【表结构还存在，ddl,删了就彻底完蛋，不可以回滚】
TRUNCATE TABLE empy6;


-- 删除表 DROP 【表数据和表结构都会不存在】
DROP TABLE emp5;

-- 删除表 删除表中数据，表结构还有，可以用rollback回滚表中数据
DELETE FROM emp5;

---

清空表

清空表 truncate 【表结构还存在，ddl,这个删除数据删了就彻底完蛋，不可以回滚】

truncate清空表原理：会删除原来的表，再重新创建一张表

---

增删改 DML 

向表中插入空值
隐式方式: 在列名表中省略该列的值
显示方式: 在VALUES 子句中指定空值

添加一条记录

到表"anchor"

#值列表中的值的顺序、类型、个数必须与表结构一一对应
INSERT INTO anchor VALUES (NULL,'骚白','斗鱼');
#也可以这样写
INSERT INTO anchor (`name`,plat)  VALUES ('纯皇','斗鱼');

---

添加多条记录

到表"anchor"，每一个值列表直接使用逗号分隔

#值列表中的值的顺序、类型、个数必须与(字段列表)一一对应
INSERT INTO anchor (`name`,plat) 
VALUES('张大仙','虎牙'),
('慢慢','斗鱼');


#一个insert语句有几个(值列表)就表示添加几行记录
INSERT INTO anchor  VALUES
(NULL,'慕少','花样'),
(NULL,'二蛋','虎牙');

---

带条件修改

不带条件修改，会对表中所有行修改，慎重！！！

#不带条件修改，会对表中所有行修改，慎重
UPDATE anchor SET `name`='韩涵',plat='虎牙' WHERE id=5;

---

删除表数据

DELETE FROM anchor WHERE id=2;
-- 删除现存数据 DELETE FROM [不写where会清空表] 可以回滚
DELETE FROM anchor;

---

约束

对表中数据做限制

约束是表级的强制规定
方式1：可以在创建表时规定约束（通过 CREATE TABLE 语句），
方式2：或者在表创建之后也可以（通过 ALTER TABLE 语句）

1. 键约束和检查约束是表级约束，即不仅要看约束字段当前单元格的数据，还要看其他单元格的数据。
2. 非空约束和默认值约束都是列级约束，即约束字段只看当前单元格的数据即可，和其他单元格无关。

---

1.非空约束     

not null 声明列的同时去指定约束，叫列级约束。not null只支持列级约束

mysql注意：not null约束后，在插入数据时显示加不进去，隐式可以加null可以

所有的类型的值都可以是null，包括int、float等数据类型   

创建表时指定：                       

-- NOT NULL 非空约束，规定某个字段不能为空
CREATE TABLE emp1(
    id INT(10),
    `name` VARCHAR(20) NOT NULL
);

-- 对现有表

添加非空约束

ALTER TABLE emp2
MODIFY NAME VARCHAR(20) NOT NULL;

-- 删除非空约束

ALTER TABLE emp2
MODIFY NAME VARCHAR(20) NULL;

创建字段时指定

---

2.唯一约束 unique

列都声明好了，再加约束。是表级约束，constraint

-- UNIQUE  唯一约束，规定某个字段在整个表中是唯一的
CREATE TABLE emp3(
	id INT(10),
	NAME VARCHAR(20),
	phone VARCHAR(30),
	CONSTRAINT emp3_phone_un UNIQUE(phone) -- 起名--给唯一约束起名emp3_phone_un
);

-- 表已经创建好添加唯一约束
ALTER TABLE emp3
ADD CONSTRAINT emp3_name_un UNIQUE(NAME);

-- 删除唯一约束 drop index[mysql会为唯一约束列添加索引]
ALTER TABLE emp3
DROP INDEX emp3_name_un;

---

3.主键约束【非空并且唯一】

primary key 可以锁定唯一一条数据，

-- 主键(非空且唯一),通常利用逐渐锁定一条数据从
-- PRIMARY KEY  添加主键约束方式1
CREATE TABLE emp4(
    id INT(10) PRIMARY KEY,
    NAME VARCHAR(20)
);

-- 添加主键约束方式2
CREATE TABLE emp5(
    id INT(10),
    NAME VARCHAR(20),
    CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

-- 添加主键约束方式3
ALTER TABLE emp6
ADD CONSTRAINT emp6_id_pk PRIMARY KEY(id);

-- 删除主键约束 【只把唯一约束删除了，没有删除非空约束】
ALTER TABLE emp6
DROP PRIMARY KEY;

---

4.外键约束

某字段关联另外一个表的主键
出现在外键表【有外键限制的表】中的数据一定要出现在主键表

在多方表中定义外键，指向一方表的主键

-- FOREIGN KEY 外键:通常关联另一个表的主键，出现在外键表中的数据，一定出现在主键表中 
FOREIGN KEY: 在表级指定子表中的列
REFERENCES: 标示在父表中的列


CREATE TABLE dept1(
	dept_id INT(10) PRIMARY KEY,
	dept_name VARCHAR(20)
);

CREATE TABLE emp7(
	id INT(10) PRIMARY KEY,
	NAME VARCHAR(20),
	depart_id INT(10),
	CONSTRAINT emp7_departId_fk FOREIGN KEY(depart_id) REFERENCES dept1(dept_id)
	-- ON DELETE CASCADE -- (级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
	ON DELETE SET NULL -- (级联置空): 子表中相应的列置空
);


#添加外键约束
ALTER TABLE emp7
ADD CONSTRAINT emp7_depart_id_fk FOREIGN KEY(depart_id) REFERENCES dept1(dept_id);

#删除外键约束
ALTER TABLE emp7
DROP FOREIGN KEY emp7_departId_fk;

---

5.约束等级

约束等级，没有设置，则默认不允许删除或修改操作

在从表上设置主外键依赖关系，设置约束等级时建议使用可视化，如下：

 1.在俩张表中添加数据，不加级联置空和级联删除，普通主外键的情况下

 

-- 因为主外键关系直接删除会报错
DELETE FROM dept1 WHERE dept_id=10

2.on delete set null; 级联置空 平和。删除主表数据时，

DELETE FROM dept1 WHERE dept_id=10

3.on delete cascade [.级联删除]   危险

 

---

6.检查约束

mysql5.7不支持check约束，不支持不是用的时候报错，是不起作用，mysql8.0才有效果。

-- CHECK  检查约束  MySQL 8.0
CREATE TABLE emp8(
	id INT(10),
	NAME VARCHAR(20),
	salary DOUBLE(10, 2),
	CONSTRAINT emp8_salary_ck CHECK(salary > 3000)
);

此时，执行如下sql会报错

INSERT INTO emp8
VALUES(101, '张三', 1000);

---

7.default约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显示赋值，则赋值为默认值

创建表时：

CREATE TABLE test_default(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
)

DESC test_default;

1

创建字段时