我们的情绪背后藏着动机,动机总是正面的,因为意识从来不会伤害自己,只是误以为某些行为可以满足自己的这份动机。
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.非空约束
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
创建字段时