python Mysql 数据库 （4）

Mysql 记录长度
Mysql 中规定：任务一条记录最长不能超过65505个字节，（varchar永远达不到理论值）
Varchar的实际存储长度能达到多少呢？看字符集编码
UTF8下的varchar的实际顶配
GBK下的varchar的实际顶配
#求出varchar在utf8和gbk下的实际最大值
CREATE TABLE my_utf8(
nname VARCHAR(21844) # 21844*3+2=65534
)CHARSET utf8;
CREATE TABLE my_cbk(
nname VARCHAR(32766) # 32766*2+2=65534
)CHARSET gbk;

想用完整个65535个字节长度，增加一个tinyint字段即可
CREATE TABLE my_utf81(
age TINYINT,         #这里占一个字节
nname VARCHAR(21844) # 21844*3+2=65534
)CHARSET utf8;
CREATE TABLE my_cbk1(
age TINYINT,         #这里占一个字节
nname VARCHAR(32766) # 32766*2+2=65534
)CHARSET gbk;
上面的做法错误

Mysql记录中：如果有任何一个字段允许为空，那么系统自动从整个记录中保留一个字节
来存储NULL（若想释放NULL，所占用的字节，必须保留所有的字段都不允许为空）
CREATE TABLE my_utf82(
age TINYINT NOT NULL,         #这里占一个字节
nname VARCHAR(21844) NOT NULL # 21844*3+2=65534
)CHARSET utf8;
CREATE TABLE my_cbk2(
age TINYINT NOT NULL,         #这里占一个字节
nname VARCHAR(32766) NOT NULL # 32766*2+2=65534
)CHARSET gbk;

Mysql 中 text文本字符串，不占用记录长度，额外存储，但是text文本字符串也是属于记录的
一部分，一定需要占据记录中的部分长度，10个字节（保存数据的地址以及长度）
CREATE TABLE my_text(
nname VARCHAR(21841) NOT NULL, #21841*3+2=65523=65525
content TEXT NOT NULL          #10
)CHARSET utf8;

列属性
列属性：真正的约束字段是数据类型，但是数据类型的约束很单一，需要有一些额外的约束
来更加保证数据的合法性
列属性有很多：NULL/NOT NULL,default,primary key,unique key,auto_increment,comment

空属性
两个值：NULL默认的  和NOT NULL不为空
虽然默认的，数据库基本都是字段为空，但是实际上在真实开发的时候，尽可能要保证所有
是数据都不应该为空，空数据没有意义，空数据没有办法参加与运算
SELECT 1+NULL; #结果还是NULL
创建一个实际案例表，班级表（名字，教室）
CREATE TABLE my_class(
nname VARCHAR(20) NOT NULL,
room VARCHAR(20) NULL  #代表允许为空 不写是默认为空
)CHARSET utf8;

列描述
列描述：comment描述，没有实际含义，是专门用来描述字段，会根据表创建语句保存，用
来给程序员（数据库管理员）来进行了解的
#创建表
CREATE TABLE my_teacher1(
nname VARCHAR(20) NOT NULL COMMENT '姓名',
money DECIMAL(10.2) NOT NULL  COMMENT '工资'
)CHARSET utf8;

默认值
默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据
的时候，用户可以选择的使用默认值
默认值关键字，default
CREATE TABLE my_default(
nname VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 0,
gender ENUM('男','女','保密') DEFAULT '男'
)CHARSET utf8;

默认值的生效，使用在数据进行插入的时候，不 给该字段赋值
#插入数据 
INSERT INTO my_default (nname) VALUES('高清');

想要使用默认值，可以不一定去指定列表，故意不使用字段列表，可以使用default关键字代替值
INSERT INTO my_default VALUES('张三',18,DEFAULT);

主键
主键：prinary fey主要的键，一张表中只能有一个字段可以使用对应的键，用来唯一的约束该字段里面
的数据，不能重复，这种称之为主键
一张表只能有最多一个主键
增加主键
SQL操作中有多种方式可以给表增加主键，大体分为三种
方案1：在创建表的时候直接在字段之后跟 frinary key关键字（主键本身不允许为空）
#增加主键
CREATE TABLE my_pril(
nname VARCHAR(20) NOT NULL COMMENT '姓名',
number CHAR(10) PRIMARY KEY COMMENT '学号：itcast + 0000 不能重复'
)CHARSET utf8; 
优点：非常直接，缺点：只能使用一个字段作为主键

方案2：在创建表的时候，在所有的字段之后，使用primary key（主键字段列表）来创建主键
（如果有多个字段作为主键，可以是复合主键）
#复合主键
CREATE TABLE my_pri2(
number CHAR(10) COMMENT '学号：itcast + 0000',
course CHAR(10) COMMENT '课程代号：3091 +0000',
score TINYINT UNSIGNED DEFAULT 60 COMMENT '成绩',
#增加主键限制：学号和课程号应该是一个对应的，具有唯一性
PRIMARY KEY(number,course)
)CHARSET utf8;

方案3：当表创建好之后，额外追加主键，可以通过修改表字段属性，也可以直接追加
Alter table 表名 add primary key(字段列表);
#追加主键
CREATE TABLE my_pri3(
curse CHAR(10) NOT NULL COMMENT '课程编号：3091+0000',
nname VARCHAR(10) NOT NULL COMMENT '课程名称'
)CHARSET utf8;

ALTER TABLE my_pri3 ADD PRIMARY KEY(curse);

前提：表中字段对应的数据本身是独立的（不重复）

主键约束
主键对应的字段中的数据不允许重复：一旦重复数据操作失败（增和改）
#向pri1表插入数据
INSERT INTO my_pril VALUES('李四','itcast0001'),('王五','itcast0002');
INSERT INTO my_pri2 VALUES('itcast0001','39010001',90),('itcast0001','39010002',85),('itcast0002','39010002',92);
#主键冲突（重复）
INSERT INTO my_pril VALUES('赵四','itcast0002');# 不可以：主键冲突
INSERT INTO my_pri2 VALUES('itcast0001','39010001',100);#不可以：冲突

更新主键&删除主键
没有办法更新主键，主键必须先删除，才能增加
alter table 表名 drop primary key;
#删除主键
ALTER TABLE my_pril DROP PRIMARY KEY;
#增加主键
ALTER TABLE my_pril ADD PRIMARY KEY(number);

主键分类
在实际创建表的过程中，很少使用真实业务数据为主键字段（业务主键，如学号，课程号）
大部分的时候是使用逻辑性的字段（字段没有业务含义，值是什么都没有关系），将这种字段
主键称之为逻辑主键
create table my_student(
Id int primary key auto_increment '逻辑主键：自增长', #逻辑主键
Number char(10) not null comment '学号',
nname varchar(10) not null
)charset utf8;

自动增长
自增长：当对应的自带，不给值或者说给默认值，或者给NULL的时候，会自动的被系统触发，
系统会从当前字段中已有的最大值进行+1操作，得到一个新的在不同的字段
自增长通常是跟主键搭配
自增长特点：auto_increment
  1.任何一个字段要做自增长必须前提本身是一个索引（key一栏有值）
    CREATE TABLE my_auto(
    id INT AUTO_INCREMENT COMMENT '自增长',
    nname VARCHAR(10) NOT NULL
    )CHARSET utf8;  # 错误 没有设置主键
  2.自增长字段必须是数字（整型）
    CREATE TABLE my_auto(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增长',
    nname VARCHAR(10) NOT NULL
    )CHARSET utf8;
  3.一张表最多只能有一个自增长

自增长使用
当自增长被给定的值为null或者默认值的时候会触发自动增长
#触发自增长
INSERT INTO my_auto(nname) VALUES('李四');
INSERT INTO my_auto VALUES(NULL,'王五');
INSERT INTO my_auto VALUES(DEFAULT,'张三');

自增长如果对应的自段输入了值，那么自增长失效，但是下一次还是能够正确的自增长（从
最大值+1）
#指定数据
INSERT INTO my_auto VALUES(6,'苏斯');
INSERT INTO my_auto VALUES(NULL,'刘八');

如何锁定下一次是什么呢？可以通过查看创建语句 看到

修改自增长
自增长如果是涉及到字段改变，必须先删除正增长后增加（一张表只能有一个正增长）
修改当前正增长已经存在的值，修改只能比当前已有的正增长的最大值大，不能小（
小不生效）
Alter table 表名 auto_increment = 值；
#修改表选项的值
ALTER TABLE my_auto AUTO_INCREMENT=4; #向小修改 不生效
ALTER TABLE my_auto AUTO_INCREMENT=10;#向大修改

思考：为什么正增长是从1开始，为什么每次都是正增长1呢
所有系统的表现（如字符集，校对集）都是由系统内部的变量进行控制的
查看正增长对应的变量：show variables like 'auto_increment%';
#查看正增长变量
SHOW VARIABLES LIKE 'auto_increment%';

可以修改变量现实不同的效果，修改是对整个数据修改，而不是单张表（修改是会话集）
set auto_increment_increment=5; #一次正增长5
#修改正增长不步长
SET auto_increment_increment=5;
测试效果，自动使用正增长
#插入记录，使用自增长
INSERT INTO my_auto VALUES(NULL,'刘李');

删除正增长
正增长是字段的一个属性：可以通过modify未进行保证修改（保证字段没有zuto_increment即可）
Alter table 表名 modify 字段 类型；
#删除正增长
ALTER TABLE my_auto MODIFY id INT PRIMARY KEY;  #错误的 主键的单独存在的
ALTER TABLE my_auto MODIFY id INT; #有主键的时候，千万不要再加主键
        
唯一键
一张往往有很多字段需要具有唯一性，数据不能重复，但是一张表中只有一个主键：唯一
键（unique key)就可以解决表中有多个字段需要唯一性约束的问题
唯一键的本质与主键差不多，唯一键默认的允许自动为空，而且可以多个为空（空字段不参与
唯一性不较）

增加唯一键
基本与主键差不多：三种方案
方案1：在创建表的时候，字段之后直接跟unique unique key
#唯一键
CREATE TABLE my_unique1(
number CHAR(10) UNIQUE COMMENT '学号：唯一，允许为空',
nname VARCHAR(20) NOT NULL
)CHARSET utf8;           
方案2：在所有的字段之后增加 unique key(字段列表）；复合唯一键            
CREATE TABLE my_unique2(
number CHAR(10) NOT NULL COMMENT '学号',
nname VARCHAR(20) NOT NULL,
#增加唯一键
UNIQUE KEY(number)
)CHARSET utf8; 
方案3：在创建表之后增加唯一键
CREATE TABLE my_unique3(
id INT PRIMARY KEY AUTO_INCREMENT,
number CHAR(10) NOT NULL,
nname VARCHAR(20) NOT NULL
)CHARSET utf8;            
#追加唯一键
ALTER TABLE my_unique3 ADD UNIQUE KEY(number); 

唯一键与主键本质相同：唯一的区别就是唯一键默认允许为空，而且是多个空
#插入数据
INSERT INTO my_unique1 VALUES(NULL,'吴娜'),('itcast0001','正气'),(NULL,'浩然');
INSERT INTO my_unique1 VALUES('itcast0001','邪王')#如果itcast0001存在 就会报错
如果唯一键也不允许为空，与主键的约束作用是一致的

更新唯一键 和 删除唯一键
更新唯一键：先删除后新增（唯一键可以有多个，可以不删除）
删除唯一键
Alter table 表名 drop unique key; #错误;唯一键可以有多个
Alter table 表名 drop index 索引名字；#唯一键默认的使用字段名作为索引名字
#删除唯一键
ALTER TABLE my_unique3 DROP INDEX number;

索引
几乎所有的索引都建立在字段之上
索引;系统根据某种算法，将已有的数据（未来可能新增的数据）单独建立一个文件，文件
能够实现快速的匹配数据，并且能够快速的找到对应表中的记录
索引的意义
  1.提升查询数据的效率
  2.约束数据的有效性（唯一性）
增加索引的前提条件，索引本身会产生索引文件（有时候可能比数据文件还大），会非常耗
磁盘空间
如果某个字段需要查询条件经常使用，那么可以使用索引（一定会想办法增加）
如果某个字段需要进行数据有效性约束，也可以使用索引（主键，唯一键）
Mysql中提供了多种索引
  1.主键索引; primary key
  2.唯一键索引：unique key
  3.全文索引：fulltext index
  4.普通索引：index
全文索引：针对文章内容的关键字进行索引
   全文索引最大的问题;在于如何确定关键字
      英文很容易：英文单词与单词之间有空格
      中文很难：没有空格，而且中文可以各种随意组合（分词）