mysql架构sql基础

设置
sel global sql_mode='';

查看字符集
show charset;
utf8 ----字符最大长度3个字节
utf8mb4 ----- 字符最大长度4个字节
不同版本中有默认字符集 8.0之后都是utf8mb4 
character_set_server=utf8mb4
需要在配置文件指定响应的字符集
1B（byte，字节）= 8 bit；
1KB（Kibibyte，千字节）=1024B= 2^10 B；
1MB（Mebibyte，兆字节，百万字节，简称“兆”）=1024KB= 2^20 B；
1GB（Gibibyte，吉字节，十亿字节，又称“千兆”）=1024MB= 2^30 B；
1TB（Tebibyte，万亿字节，太字节）=1024GB= 2^40 B；
1PB（Pebibyte，千万亿字节，拍字节）=1024TB= 2^50 B；
1EB（Exbibyte，百亿亿字节，艾字节）=1024PB= 2^60 B；
1ZB（Zebibyte，十万亿亿字节，泽字节）= 1024EB= 2^70 B；
1YB（Yobibyte，一亿亿亿字节，尧字节）= 1024ZB= 2^80 B；

查看 
show collation
字符排序是转换成ascll进行排序
mysql5.7 utf8mb4默认的排序规则为 utf8mb4_general_ci，8.0 默认的排序规则改为了utf8mb4_0900_ai_ci ，这两种排序规则的字段不能关联，关联时会报错，故同一个库里只能有一种排序规则，不能混用。
校对规则是来控制大小写敏感不敏感,用不用区分大小写

查看 
show engines;
innodb 现在大多默认
表可以单独指定存储引擎

ibd文件就是一个表空间

tinyint  占用1字节  无符号范围0~255 有符号范围 -128~127  数据长度3 #不能存到-255
smallint 占用2字节 无符号范围0~65532 有符号范围-32768~32767 数据长度5
mediumint 占用3字节 无符号范围0~16777215 有符号范围 -8388608~8388607  数据长度8
int 占用4字节 无符号范围 0~2^32 有符号范围-2^31~2^32-1 数据长度10
bigint 占用8字节 无符号范围0-2^64 有符号范围 -2^63~2^63-1 数据长度 20

float（m,d） 表示一共存储M个有效数字，其中小数部分站d位
float(10,2) 整数部分为8位， 小数部分为2位
double
double又称之韦双精度，系统采用8个字节来存储数据 表示范围更大 10^308次方，但是精度也15位左右
decimal
decimal 系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的
定点数 能够爆炸数据精确的小数 （小数部分可能不精确，超出长度会四舍五入），整数部分一定精确
decimal(m,d) M表示总长度，最大值不能超过65，d代表小数部分长度，最长不能超过30

 char 定长字符 指定长度,系统一定会分配指定的空间用于存储数据
 基本语法 char(L) 代表字符数(中文于英文字母一样) L的长度0-255
 超过最大长度就不能定义了，

varchar  变长字符 指定长度之后,系统会根据实际存储的数据来计算长度 分配合适的长度
基本语法 varchar(L) L代表字符数 L的长度理论值为0到65535字节（字符集和字符影响）
如果存储的全是中文要用65535 -2-1*3
每次存储数据的时候会额外加1-2个字节的开销来记录存储字节大小
如果数据本身小于255个字符额外开销一个字节 如果大于255个字符 就开销两个字符

1char一定会使用指定的空间，varchar是根据数据来定空间的
2char的插入数据效率理论上比varchar高 varchar是需要通过后面的记录数来计算使用哪一种类型（不同的数据类型是否会影响查询效率）
如果数据长度超过255个字符(大字段) 不论是否固定长度 都会使用text(对于b树索引不支持) 不在使用char和varchar
使用varchar
行格式
compact (索引前缀长度不能超过765字节,无论是单列索引还是组合索引)
在 utf-8字符集中
 varchar 最多250
在utf-8mb4字符集中
 varchar 最多190
dymanic (索引前缀长度不能超过3072字节)
在 utf-8字符集中
 varchar 最多 1024
在utf-8mb4字符集中
 varchar 最多768
索引前缀长度,表的行格式

enum
在数据插入之前,先设定几个项 这几个项就是可能最终出现的数据结结果
基本语法 enum(数据值1,数据值2)
系统提供了1到2个字节来存储枚举数据,通过计算enum列举的具体值来选择实际的存储空间
如果数据值列表在255个以内,那么一个字节就够了 如果超过255但是小于65535那么系统采用两个字节保存

datetime 占用8字节 范围1000-01-01 00:00:00/9999-12-31 23:59:59 用途混合日期和时间值
timestamp 占用4字节 范围1970-01-01 00:00:00/2038-1-19 11:14:07 用途日期和时间值时间戳(时区会影响时间记录)

primary key : 主键约束 同时保证唯一性和非空 每张表只能设置一个pk 我们建议业务无关列(数字列) 一个主键可以包含多个列,做联合主键
foreign key : 外键约束, 用于限制两个表的关系保证从表该字段的值来自于主表想关联的字段的值(生产一般禁用) innodb独有的特性 一般通过多表链接进行9动一张表的数据其他的表也会受到关联,会产生严重锁的影响
not null : 非空约束 保证字段的值不能为空
default : 默认约束 保证字段总会有值,即使没有插入值,都会有默认值
unique : 唯一 保证唯一性但是可以为空 比如手机号
auto_increment :自增长列
unsigned : 无符号 让数字列无负数
comment: 注释

##创建库
create database bbbb charset utf8mb4 collate utf8mb4_0900_ai_ci;
##查询库定义
show databases;
##查询建库语句
show create database bbbb;
##修改库定义 只会影响后续的数据
alter database aaaa charset utf8mb4;
show create database aaaa;
##删除库定义 
drop database aaaa;
##库定义规范 
## 1库名不能是数字开头, 不能是mysql中的关键字 (如果要数字开头或者关键字需要加上决明子符号)
## 2生产禁用 drop database (管理员有权限)
## 3 显示的设置字符集 
## 4库名要和业务有关

##创建表定义
use oldguo
create table stu(
sid     int unsigned not null primary key auto_increment comment'学号',
sname   varchar(64) not null comment'学生姓名',
sage    tinyint unsigned not null default 18 comment'年龄',
sgender char(1) not null default '男' comment'性别:男|女',
saddr   enum('北京','天津','上海','重庆','香港') not null comment'省份',
stel    char(11) not null unique key comment '手机号',
sqq     char(11) not null unique key comment'qq号',
intime datetime not null default now() comment'入学时间'
)engine = innodb charset=utf8mb4 comment '学生表';

## 查询表定义
show tables;
##查询建表语句
show create table stu;
1 表名 不要大写字母 不要数字开头 不要超过18字符 不要使用内置字符串 和业务有关
2 列名 业务有关 不要内置字符 不要超过18字符
3 数据类型 合适的 精简的 完整的
4 每个表要有1个主键 每个列要 not null 禁止外键
5 每个列有注释
6 存储引擎innodb 字符集utf-8mb4
7 每个表建议在30个字段以内
8机密数据 加密后存储 
9 整数类型,默认加上unigned
10 如果遇到 blob text 大字段单独存储或者附件形式存储
11每个表必须有主键 int/bigint并且自增作为主键 分布式架构使用sequence序列生成器保存

##克隆表 只会克隆表结构不会克隆数据
create table t1 like stu;
##修改表名
rename table stu to stu_1;
##修改存储引擎
alter table stu_1 engine=innodb;
##此方法可以修改字符集,注释等
##修改字段
##生产需求 在原表中添加一个状态列 
alter table stu_1 add is_deleted tinyint not null default 0 comment'状态列 1代表删除 0代表没删'
##生产需求 在sname列后面加一个列
alter table stu_1 add is_del tinyint not null default 0 comment '状态列 1代表删除 0代表没删' after sname;
##生产需求删除一个列
alter table stu_1 drop is_del;
##生产需求 修改数据类型 sname的varchar(50) 不够用的修改为100 需要把这一列的所有属性都加上不然会被覆盖
alter table stu_1 modify sname varchar(100) not null comment '学生名';
##生产需求在表中的一个列添加索引
alter table stu_1 add index i_sname(sname);
##删除表
drop table stu_1;
##清空表中数据(表中所有的数据行)
truncate table stu_1;