mysql-5.7 基础篇_mysql 修改字段编码索引会重新构建吗-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_47647077/article/details/118091813

1、数据类型、表属性、字符集

2、常用sql分类

***pt-osc(在线DDL) 表的改动不锁表（mysql8.0不需要）

1、数据类型、表属性、字符集

二进制类型尽量不要用（不做介绍）

2.1、数值类型

常用的两个：

tinyint  ： -128~127
int       ：-2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2.2、字符串类型

常用的：

char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

类型	大小	用途
CHAR	0-255 bytes	定长字符串（插入快不需要判断字符串长度）
VARCHAR	0-65535 bytes	变长字符串（需要判断字符串长度）
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

2.3、时间类型

DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

2.4、表属性

常用的：

存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8       
utf8mb4

2.4.1、列属性

约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null**      ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。

其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default**           :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释

2.5、字符集

utf8       
utf8mb4

2、常用sql分类

DDL：数据定义语言

DCL：数据控制语言

DML：数据操作语言

2.1、DDL（数据定义语言）

数据库操作：(增删改查)

建库规范：
1.库名不能有大写字母   
2.建库要加字符集         
3.库名不能有数字开头
4. 库名要和业务相关

下面两条是查询字符集的语句：
show charset;
show collation;

建库语句(后面在见库时加了字符集)：

create database sk;
create schema sk1;
create database test charset utf8;
create database test1 charset utf8mb4 collate utf8mb4_bin;

删库（在生产线上非必要不要操作*危险*）
 drop database sk;

修改库
ALTER DATABASE sk  CHARSET utf8;
注意：修改字符集，修改后的字符集一定是原字符集的严格超集

数据库查询（初始数据库）：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show create database sk;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| sk       | CREATE DATABASE `sk` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

表的操作

增删改（会锁表/mysql8.0以后就不会出现锁死了）查

建表规范：
1. 表名小写
2. 不能是数字开头
3. 注意字符集和存储引擎
4. 表名和业务有关
5. 选择合适的数据类型
6. 每个列都要有注释
7. 每个列设置为非空，无法保证非空，用0来填充。
范例：

USE sk;

CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

删表（生产线上也要再三考虑再用）
drop table stu;

改表（改表会使表锁死暂时不能使用）
追加一列
alter table stu add qq varchar(20) not null unique comment 'qq号';
在sname后面加一列
alter table stu add wechat varchar(32) not null unique comment '微信' after sname;
添加到头列
alter table stu add num int not null comment '数字' first;
删列（尽量谨慎执行）
alter table stu drop wechat;
改列属性：
alter table stu modify sname varchar(128) not null;
改列名：
alter table stu change sgender sg char(1) not null default 'n';

***pt-osc(在线DDL) 表的改动不锁表（mysql8.0不需要）

2.2、DQL

use sk;
#查看库里有那些表
show tables；
#查看列信息
desc stu;
#下面两个可以查看建表语句
show create table stu;
show create table stu /G;
#建一个一样但不同表名
CREATE TABLE ceshi LIKE stu;

2.3、 DCL应用

grant 
revoke

2.4、DML

对表中的数据行进行增、删、改

2.5、insert

--- 最标准的insert语句
INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUES
(1,'zs',18,'m','123456',NOW());

SELECT * FROM stu;
--- 省事的写法
INSERT INTO stu VALUES
(2,'ls',18,'m','1234567',NOW());

--- 针对性的录入数据
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');

--- 同时录入多行数据
INSERT INTO stu(sname,sfz) VALUES 
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');

SELECT * FROM stu;

2.6、update、delete

desc stu;
select * from stu;
update stu SET sname='zhao4' where id=2;
注意：update语句必须要加where。

删除列（慎用）
delete from stu  where id=3;

删除全表（操作慎用）

delete from stu;
truncate table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

1.添加状态列
alter table stu add state tinyint not null default 1;
select * from stu;
2. update 替代 delete
update stu set state=0 where id=6;
3. 业务语句查询
select * from stu where state=1;