MySQL安全管理 目录： 一、外键约束 1、外键 2、外键约束 3、外键管理 新增外键 删除外键 二、事务安全 1、事务 2、事务处理 3、事务特点 三、预处理 1、预处理 2、预处理传参 四、视图 1、视图 2、视图管理 视图查看 视图修改：修改视图逻辑 视图删除 3、视图数据操作 4、视图算法 五、数据备份与还原 1、表数据备份 2、表数据还原 3、文件备份 步骤 示例 4、文件还原 5、SQL备份 6、SQL还原 7、总结 六、用户管理 1、账号管理 2、权限管理 3、角色管理 一、外键约束 1、外键 外键：foreign key，表中指向外部表主键的字段定义成外键 外键必须要通过语法指定才能称之为外键 [constraint外键名] foreign key(当前表字段名) references 外部表(主键字段) 外键构成条件 外键字段必须与对应表的主键字段类型一致 外键字段本身要求是一个索引（创建外键会自动生成一个索引） 【例】创建专业表和学生表，学生表中的专业id指向专业表id create table t_47 ( id int primary key auto_increment, name varchar(50) not null unique )charset utf8mb4; create table t_48 ( id int primary key auto_increment, name varchar(50) not null, c_id int, constraint `c_id` foreign key(c_id) references t_47(id) )charset utf8mb4; 外键可以不指定名字，系统会自动生成 create table t_49 ( id int primary key auto_increment, name varchar(50) not null, c_id int, foreign key(c_id) references t_47(id) )charset utf8mb4; 2、外键约束 外键约束：当表建立外键关系后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果 外键约束的是写操作（默认操作） 新增：子表插入的数据对应的外键必须在主表存在 修改：主表的记录如果在子表存在，那么主表的主键不能修改（主键不能修改） 删除：主表的记录如果在子表存在，那么主表的主键不能删除 外键约束控制：外键可以在定义时控制外键的约束作用 控制类型 on update：父表更新时子表的表现 on delete：父表删除时子表的表现 控制方式 cascade：级联操作，父表操作后子表跟随操作 set null：置空操作，父表操作后，子表关联的外键字段置空 restrict：严格模式，不允许父表操作（默认的） no action：子表不管 外键约束增强了数据的安全性和可靠性，但是会增加程序对于数据的不可控性，所以是实际开发中一般会通过程序逻辑控制来保证数据的完整性和安全性，外间使用较少 【例1】子表不能插入主表不存在的数据 insert into t_48 values(null,'Tony',2); # 错误 insert into t_47 values(null,'English'); insert into t_48 values(null,'Peny',1); 【例2】默认的外键产生后，主键不能更新被关联的主键字段或者删除被关联的主键记录 # 错误 update t_47 set id = 2; delete from t_47 where id = 1; 【例三】限制外键约束，一般使用更新级联，删除置空 on update cascade：更新级联 on delete set null：删除置空 create table t_50( id int primary key auto_increment, name varchar(50) not null unique )charset utf8; create table t_51( id int primary key auto_increment, name varchar(50) not null, c_id int, # 如果要允许置空，就不能not null foreign key(c_id) references t_50(id) on update cascade on delete set null )charset utf8; insert into t_50 values(null,'Chinese'),(null,'Computer'); insert into t_51 values(null,'Tony',1),(null,'Petter',2); 3、外键管理 外键管理：在表创建后期维护外键 新增外键 alter table 表名 add [constraint `外建名`] foreign key(外键字段) references 表名(主键) [on 外键约束] 注意：追加外键需要保证外键字段里的值要么为Null，要么在父表中都能找到 删除外键 alter table 表名 drop foreign key 外键名; 二、事务安全 1、事务 计算机中的事务是指某个程序执行单元（写操作） 事务安全：当事务执行后，保障事务的执行是有效的，而不会导致数据错乱 事务安全通常针对的是一连串操作（多个事务）而产生的统一结果 MySQL中默认的写操作是直接写入的 执行写操作SQL 同步到数据表 【例】银行转账：从A账户转账到B账户 创建数据库 create table t_52 ( id int primary key auto_increment, name varchar(50) not null, account decimal(10, 2) default 0.00 )charset utf8mb4; insert into t_52 values(null, 'Tom', 10000), (null, 'Lucy', 100); 转帐，一定是分为两步 # Tom扣钱 update t_52 set account = account - 1000 where id = 1; # Lucy收钱 update t_52 set account = account + 1000 where id = 2; 以上两步必须都成功转账才能叫成功 两步操作无法确保哪一步会出问题（尤其是第二步） 为了保障两步都成功才能叫事务安全 事务安全是在操作前告知系统，接下来所有的操作都暂不同步到数据表，而是记录到事务日志，指导后续所有操作都成功，再进行同步；否则取消所有操作 以上述转账为例 graph TB A(转账开始)-->B[开启事务] B-->C{事务1:Tom转出1000} C-->|成功|D[记录到事务日志] C-->|失败|G D-->E{事务2:Lucy转入1000} D-->|失败|G E-->|成功|F[记录到事务日志] F-->G[关闭事务<br>成功:提交事务 同步到数据表\清除事务日志<br>失败:回滚事务 清除事务日志] G-->H((结束)) 2、事务处理 事务处理：利用自动或者手动方式实现事务管理 自动事务处理：系统默认，操作结束直接同步到数据表（事务关闭状态） 系统控制：变量 autocommit（值为ON，自动提交） 手动事务处理 开启事务： start transaction 关闭事务 提交事务：commit（同步到数据表同时清空日志数据） 回滚事务：rollback（清空日志数据） 事务回滚：在长事务执行中，可以在某个已经成功的节点处设置回滚点，后续回滚的话可以回到某个成功点 设置回滚点：savepoint 回滚点名字 回滚到回滚点：rollback to 回滚点名字 扩展：事务处理的支持是有条件的 —— 存储引擎需要为InnoDB 【例1】手动事务：启用事务转账，成功提交事务 # 开启事务 start transaction; # Tom扣钱 update t_52 set account = account - 1000 where id = 1; # Lucy收钱 update t_52 set account = account + 1000 where id = 2; # 提交事务 commit; 【例2】手动事务：启用事务转账，成功提交事务（回滚点） # 开启事务 start transaction; # Tom扣钱 update t_52 set account = account - 1000 where id = 1; # 设置回滚点 savepoint sp1; # Lucy收钱 update t_52 set account = account + 1000 where id = 2; # 操作失败回到回滚点 rollback to sp1; # Lucy收钱 update t_52 set account = account + 1000 where id = 2; # 提交事务 commit; 自动事务 MySQL默认是自动提交事务的：所以事务一旦发生就会立即写入到数据表（不能多个事务一起完成任务） show variables like 'autocommit'; 关闭自动提交事务（当前设置级别用户级：当前用户档次连接有效） set autocommit = 0; 手动提交事务 insert into t_52 values(null,'Liu',1000); commit; 3、事务特点 事务特点：事务处理具有ACID四大特性 原子性（Atomicity ）：一个事务操作是一个整体，不可拆分，要么都成功，要么都失败 一致性（Consistency）：事务执行之前和执行之后都必须处于一致性状态，数据的完整性没有被破坏（事务逻辑的准确性） 隔离性（Isolation ）：事务操作过程中，其他事务不可见 持久性（Durability ）：事务一旦提交，结果不可改变 事务锁：当一个事务开启时，另外一个事务是不能对当前事务锁占用的数据进行操作的 行所：当前事务只占用了一行（id精确检索数据），那么其他事务可以操作其他行数据 表所：当前事务占用了整张表（like扫码整个表），那么其他事务对整张表都不能操作 脏读：一个事务在对某个数据进行操作但尚未提交，而另外一个事务读到了这个“历史”数据其实已经被修改 三、预处理 1、预处理 预处理：prepare statement，一种预先编译SQL指令的方式（然后命令执行） 预处理不同于直接处理，是将要执行的SQL指令先发送给服务器编译，然后通过指令执行 发送预处理：prepare 预处理名字 from '要执行的SQL指令' 执行预处理：execute 预处理名字 预处理管理 预处理属于会话级别：即当前用户当次连接有效（断开会被服务器清理掉） 删除预处理：deallocate | drop prepare 预处理名字 预处理的作用 性能优化 效率优化：同样的SQL不用每次都进行编译（编译耗时） 普通处理：每次都需要编译 预处理：编译一次 网络传输优化：复杂的SQL指令只需要传输一次 普通处理：每次都需要网络传输SQL指令 预处理：传输一次SQL指令，以后都是执行指令 安全：有效防止SQL注入（外部通过数据的特殊使用使得SQL的执行方式改变） 普通处理：直接发送给服务器执行（容易出现SQL注入） 预处理：发送的是结构，数据是后期执行传入（传入协议不一样，数据安全性高） 【例】查询学生的SQL指令需要重复执行很多次 # 普通操作 select * from t_42; # 预处理操作：发送预处理 prepare p1 from 'select * from t_42'; # 预处理操作：执行预处理 execute p1; # 删除预处理 deallocate prepare p1; 普通处理和预处理对比 graph TB A(普通处理)-->B[接收SQL指令] B-->C[编译SQL] C-->D[执行SQL] D-->E((返回结果)) A1(预处理)-->B1[接收预处理指令] B1-->C1[编译预处理指令] C1-->D1{是否执行} D1-->|execute<br>以后都不需要执行接收SQL和编译SQL|E1[执行SQL<br>可重复执行] D1-->|不执行|F1 E1-->F1((返回结果)) 2、预处理传参 预处理传参：在执行预处理的时候传入预处理需要的可变数据 一般预处理都不会是固定死的SQL指令，而是具有一些数据可变的执行（条件） 可变数据的位置使用占位符 ? 占位 prepare 预处理名字 from `预处理指令 变化部分使用?代替` 在执行预处理的时候将实际数据传进去代替占位符执行SQL 数据存储到变量（预处理传入的值必须是变量保存的） set @变量名 = 值 使用using关键字传参 execute 预处理名字 using @变量名 数据传入的顺序与预处理中占位符的顺序一致 预处理传参是实际应用预处理时最常见的方式 预处理指令可以适用于增删改查各种指令 如果预处理的指令不是在一次连接中重复使用，那么预处理反而会降低效率。所以预处理的执行如果不是考虑到安全因素，那么一定是SQL需要重复执行 【例】向t_40表中插入数据 # 准备预处理：涉及参数 prepare t_40_insert from 'insert into t_40 values(null,?,?,?,?)'； # 设置变量并传入参数 set @name = '药师兜'; set @gender = '男'; set @age = 23; set @class_name = '木叶1班'; # 执行预处理 execute t_40_insert using @name, @gender, @age, @class_name; 四、视图 1、视图 视图：view，一种由select指令组成的虚拟表 视图是用来提供数据支持的，是由select指令组成的结构 存在结构 不存在数据（数据是使用时调用select指令动态获取数据） 视图的目的 方便提供全面数据：可以根据需求组织数据，而实际上不会在数据库产生数据冗余 数据安全：视图本质是来源于数据基表，但是对外可以保护基本的数据结构 # 创建视图 create view 视图名字 as select 指令; # 访问视图：一般都是查询 select */字段名 from 视图名字; 【例1】需要对外提供一个学生详情的数据，经常使用：可以利用视图实现 create view v_student_info as select * from t_45 left join t_46 using(c_id); select * from v_student_info; 【例2】有些复杂的SQL又是经常用到的，如多张表的连表操作：可以利用视图实现 # 院系表 create table t_53( id int primary key auto_increment, name varchar(50) not null )charset utf8; insert into t_53 values(null,'语言系'),(null,'考古系'); # 专业表 create table t_54( id int primary key auto_increment, name varchar(50) not null, s_id int not null comment '学院id' )charset utf8; insert into t_54 values(null,'English',1),(null,'Chinese',1); # 学生表 create table t_55( id int primary key auto_increment, name varchar(50) not null, s_id int not null comment '专业Id' )charset utf8; insert into t_55 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1); # 获取所有学生的明细信息 select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id; # 以视图保存这类复杂指令，后续可以直接访问视图 create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id; select * from v_student_detail; 2、视图管理 视图管理：对视图结构的管理 视图操作与表操作类似，通常情况下不会经常的去修改维护，而是会在一开始就维护好 视图管理可以与表一样对结构进行管理 视图查看 查看全部视图 show tables; 查看视图创建指令 show create table / view 视图名; 查看视图结构 desc 视图名; 视图修改：修改视图逻辑 更改试图 alter view 视图名 as 新的查询语句; 创建新的或者替换新的视图 create or replace view 视图名 as 新的查询指令; 视图删除 drop view 视图名; 3、视图数据操作 视图数据操作：直接对视图进行写操作（增删改）然后实现基表数据的变化 视图所有的数据操作都是最终对基表的数据操作 视图操作条件 多基表视图：不允许操作（增删改都不行） 单基表视图：允许增删改 新增条件：视图的字段必须包含基表中所有不允许为空的字段 with check option：操作检查规则 默认不需要这个规则（创建视图时指定）：视图操作只要满足前面上述条件即可 增加此规则：视图的数据操作后，必须要保证该视图还能把通过视图操作的数据查出来（否则失败） 视图数据操作一般情况下是不允许的，通常之所以对外提供视图就提供数据的只读操作 with check option是针对有where条件的视图组成有效，需要手动选择是否增加该选项 视图数据的新增、修改后，必须与原来的查询结果是一致的（新增一定要能在视图中看到） 视图数据的删除不受with check option影响 视图数据的新增、修改都是针对当前视图能查出来的，否则既不报错也不生效 【例1】增加一个单表视图和多表视图 create view v_student_1 as select s_id,s_name from t_45; create view v_student_2 as select s.*,c.c_name from t_45 s left join t_46 c using(c_id); create or replace view v_student_3 as select * from t_45 where c_id is not null with check option; 【例2】新增数据 insert into v_student_1 values(null, 'student7'); # 正确 insert into v_student_2 values(null, 'student8', null, null) # 错误，不可插入 insert into v_student_3 values(null,'student8',null); # 错误：check option，因为第三个字段c_id为NULL，不符合视图筛选条件，查不出来 insert into v_student_3 values(null,'Student9',1); # 正确 【例3】更新数据 update v_student_1 set s_name = 'boy' where s_id = 8; update v_student_2 set s_name = 'boy' where s_id = 7; # 错误：不可修改 update v_student_3 set c_id = null where s_id = 1; # 错误：check option，修改后c_id为null，变得不符合视图筛选条件了 update v_student_3 set s_name = 'boy' where s_id = 1; # 正确 【例4】删除数据 delete from v_student_1 where s_id = 2; delete from v_student_2 where s_id = 3; # 错误：不可删除 delete from v_student_3 where s_id = 1; # 可以删除，说明with check option不影响删除操作 4、视图算法 视图算法：指视图在执行过程中对于内部的select指令的处理方式 create ALGORITHM = 算法 view 视图名字 as select 指令; 视图算法一共有三种 undefined：默认的，未定义算法，即系统自动选择算法 merge：合并算法，就是将视图外部查询语句跟视图内部select语句合并后执行，效率高（系统优先选择） temptable：临时表算法，即系统将视图的select语句查出来先得出一张临时表，然后外部再查询（ temptable 算法视图不允许写操作） 一般在设计视图的时候要考虑到视图算法的可行性，通常视图中如果出现了 order by 排序的话，就要考虑使用 temptable 算法 只要merge以后，不会导致数据因为子句的先后顺序而混乱（order by 与 group by 的顺序混乱容易出问题） 【例1】创建三种不同算法视图 create algorithm = undefined view v_student_4 as select * from t_42 order by age desc; create algorithm = merge view v_student_5 as select * from t_42 order by age desc; create algorithm = temptable view v_student_6 as select * from t_42 order by age desc; 【例2】使用视图：为了体现算法效果，给视图增加分组效果 select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_4 group by class_name; select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_5 group by class_name; select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_6 group by class_name; # 正确显示 【例3】临时表算法的视图不能进行数据插入操作 insert into v_student_6 values(null,'冥加','男',100,'神妖1班'); # 错误：不可插入 五、数据备份与还原 备份：backup，将数据或者结构按照一定的格式存储到另外一个文件中，以保障阶段数据的完整性和安全性 将当前正确数据进行数据保存 备份通常是有固定的时间节点 还原：restore，在当前数据出问题的情况下，将之前备份的数据替换掉当前数据，保证系统的持续、正确的运行 基于备份进行数据还原 备份还原不一定能够保证所有损失挽回 1、表数据备份 表数据备份：单独针对表里的数据部分进行备份（数据导出） 将数据从表中查出，按照一定格式存储到外部文件 字段格式化：fields terminated by：字段数据结束后使用的符号，默认是空格 enclosed by：字段数据包裹，默认什么都没有 escaped by：特殊字符的处理，默认是转义 行格式化：lines terminated by：行结束符号，默认是\n，自动换行 starting by：行开始符号，默认没有 表数据备份是一种将表中的数据按照一定的格式导出到外部文件进行保存 数据取出后方便进行加工管理 SQL有不同的语法，但是数据的识别是一致的，所以方便进行数据库间的切换 表数据备份通常是为了进行数据加工后存入回表中，或者到其他表 目前比较少用这种方式进行数据备份 select 字段列表|* into outfile 外部文件路径 [fields terminated by 格式 enclosed by 格式] [lines terminated by 格式 starting by 格式] from 数据表; 【例1】将t_40表的数据按照默认的方式导出到文件 select * into outfile 'D:/t_40.csv' from t_40; 如果系统提示：secure-file-priv问题，说明配置没有允许进行文件的导入导出。需要在配置文件里配置好这个配置项：secure-file-priv = 数据导入导出路径/不指定值（重启MySQL生效） 【例2】将t_40表的数据按照指定格式导出到文件 select name, gender, age, class_name into outfile 'D:/t_40_self.csv' fields terminated by '-' enclosed by '"' lines starting by 'GO:' from t_40; 【例3】多表数据导出：t_45连接t_46表 select * into outfile 'D:/t_45_46.csv' from t_45 left join t_46 using(c_id); 2、表数据还原 表数据还原：将符合数据表结构的数据导入到数据表中（数据导入） 将一定格式的数据按照一定的解析方式解析成符合表字段格式的数据导入到数据表 字段处理 行处理 load data infile '数据文件所在路径' into table 表名 [fields terminated by 格式 enclosed by 格式] [lines terminated by 格式 starting by 格式] [(字段列表)]; # 如果是部分表字段，那么必须将字段列表放到最后 表数据还原其实是将外部符合条件的数据，按照一定的格式要求导入到数据表中 数据导入可以解决不同格式数据或者不同数据库产品间的数据互相导入到对应数据库产品的问题 目前较少使用这种方式进行数据导入：数据的生成应该是业务产生，而不是人工参与（破坏数据的客观有效性，使得数据不真实） 【例1】将t_40.csv数据导入到db_3数据库中的一个与t_40表结构一致的表中 create table t_40 like db_2.t_40; load data infile 'D:/t_40.csv' into table t_40; # 有可能因为字符集出现问题 load data infile 'D:/t_40.csv' into table t_40 charset utf8; 注意：数据加载的时候需要注意外部数据的字符集，在加载的时候需要指定字符集为外部文件数据格式，在表后增加字符集charset 外部文件数据字符集 【例2】将t_40_self文件里的数据导入到db_3.t_40表中 load data infile 'D:/t_40_self.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'GO:' (name,gender,age,class_name) ; 3、文件备份 文件备份：直接对数据表进行文件保留，属于物理备份 文件备份操作简单，直接将数据表（或者数据库文件夹）进行保存迁移 MySQL中不同表存储引擎产生的文件不一致，保存手段也不一致 InnoDB：表结构文件在ibd文件中，数据和索引存储在外部统一的ibdata文件中(Mysql7以前话是frm后缀) MyIsam：每张表的数据、结构和索引都是独立文件，直接找到三个文件迁移即可 文件备份是一种简单粗暴的数据备份方式，是直接将数据文件打包管理的方式 MyIsam存储引擎相对比较适合文件备份，因为MyIsam存储引擎表文件独立，不关联其他表 InnoDB不适合文件备份，因为不管是备份一张表还是全部数据表，都需要备份整个数据存储文件ibdata（适合整库迁移） 文件备份方式非常占用磁盘空间 步骤 1、设定备份时间节点 2、设定备份文件存储位置 3、确定备份表的存储引擎 4、根据节点进行文件备份：将文件转移（复制）到其他存储位置 示例 MyIsam表的文件备份：找到三个文件，复制迁移 sdi：表结构文件 MYI：索引文件 MYD：数据文件 InnoDB表的文件备份：找到两个文件，复制迁移 ibd：表结构文件 ibdata：所有InnoDB数据文件 4、文件还原 文件还原：利用备份的文件，替换出现问题的文件，还原到备份前的良好状态 直接将备份的文件放到对应的位置即可 文件还原影响 MyIsam存储引擎：单表备份，单表还原，不影响其他任何数据 InnoDB存储引擎：单表结构，整库数据，只适合整库备份还原，否则会影响其他InnoDB存储表 文件备份的还原通常使用较少 数据备份占用空间大，这种备份方式就少 InnoDB的备份是针对整个数据库里所有InnoDB表，还原会覆盖掉所有不需要还原的表 文件备份与还原通常可以在数据迁移的情况下使用 MyIsam：独立表的迁移（现在很少用，myisam很少用） InnoDB：整个数据库的迁移 步骤 1、找到出问题的数据文件 MyIsam：表结构、表数据、表索引三个文件（删掉即可） InnoDB：表结构、整库数据表ibdata（删掉） 2、将备份数据放到相应删除的文件位置 示例 1、MyIsam数据备份表的数据迁移：单表迁移到不同数据库 2、InnoDB数据备份完成整个数据库的迁移（包括数据库用户信息） 5、SQL备份 SQL备份：将数据库的数据以SQL指令的形式保存到文件当中，属于逻辑备份 SQL备份是利用Mysqldump.exe客户端实现备份 SQL备份是将备份目标（数据表）以SQL指令形式，从表的结构、数据和其他信息保存到文件 mysqldump -h -P -u -p [备份选项] 数据库名字 [数据表列表] > SQL文件路径 备份选项很多，常见的主要是数据库的备份多少 全库备份：--all-databases 所有数据库的所有表，也不需要指定数据库名字 单库备份：[--databases] 数据库 指定数据库里的所有表（后面不要给表名） 部分表（单表）备份：数据库名字 表1[ 表2...表N] 【例】全库备份（借助于Windows下的cmd访问mysqldump.exe，当前用户使用root账号） mysqldump -uroot -p****** --all-databases > ./mysql.sql 【例】单库备份 mysqldump -uroot -p****** --databases db_2 > ./db_2.sql 【例】单表备份（没有创建数据库的指令） mysqldump -uroot -p****** db_2 t_40 t_42 > ./t_40_42.sql 6、SQL还原 SQL还原：在需要用到SQL备份数据时，想办法让SQL执行，从而实现备份数据的还原 方法一： mysql -h -P -u -p [数据库名字] < SQL文件路径 方法二：SQL还原可以在进入到数据库之后利用SQL指令还原 source SQL文件路径; 7、总结 1、数据的备份与还原是作为数据库管理者和使用者都应该掌握的一项技能 保障数据安全 保证系统正常运行 保障公司和客户的利益 2、数据库备份与还原的方式很多，每一种都有自己的特点和适用点，需要我们熟练区分和选择 表数据备份与还原：适用于数据导出和导入，数据具有结构，但是不包含字段和类型 文件备份与还原：简洁方便，但是需要区分存储引擎InnoDB和MyIsam（InnoDB不适合进行文件备份） SQL备份与还原：不限定存储引擎，随时随地可以备份，不过备份和还原的效率都比较低（完整备份） 3、数据库的备份与还原是一门学问，所以不同的企业、业务都会选择不同的备份策略，也有可能使用交叉策略备份来确保数据的安全，而且一般会将备份文件与运行环境分离开来以确保数据真正的隔离和安全。 六、用户管理 1、账号管理 账号管理：根据项目的需求设置和管理账号 账号是权限依赖的对象，先有账号才有权限 MySQL中账号的组成分为两个部分：用户名 @ 主机地址（root@localhost） 用户名为用户登录时的名字 主机地址：是允许账号所在客户端的访问的客户端IP（如上述root只能在服务器本机通过客户端访问） 账号管理 创建账号： create user 用户名@主机地址 identified by '明文密码'; 删除账号： drop user 用户名@主机地址 账号管理是用户管理的基础，但是账号管理也只是用户管理的一部分 账号管理是要考虑数据安全因素划分 账号管理单独应用较少，一般都要配合权限控制 账号管理也是DBA对于数据库管理的一种重要手段：根据项目划分账号 大的项目或者大的数据库服务器上几乎不会给开发使用root账号（权限太大） 【例1】根据项目情况，跟不同的项目组创建不同的账号 # A团队只允许在公司访问服务器，公司IP为163.177.151.110 create user `admin`@`163.177.151.110` identified by 'admin123'; # B团队不限定负责数据库管理，不限定工作地点 create user `admin` identified by 'admin321'; 【例2】开发任务结束，A团队的任务已经完成，不需要进行数据库操作 drop user `admin`@`163.177.151.110`; 2、权限管理 权限管理：对账号进行权限的支持与回收 账号创建之初除了登录是没有其他操作权限的 账号的管理通常需要配合权限的使用 赋权：给账号绑定相应的权限 grant 权限列表 on 数据库|*.数据表|* to 用户名@主机地址 回收：将账号已有的权限回收 revoke 权限列表 on 数据库|*.数据表|* from 用户名@主机地址 刷新权限： flush privileges 查看权限 show grants for 用户名@主机地址 权限管理是整个用户管理的核心：账号只能让用户能够连接服务器，而权限管理才能给用户提供各类操作 权限的操作是根据使用账号的用户需要出发的 DBA用户通常可以分配整个数据库所有库的权限：all on *.* 项目管理级别的用户可以针对所负责项目的权限：all on 数据库.*（多个项目分配多次） 项目开发者用户可以针对所负责项目模块的权限：权限列表 on 数据库.表名/*（如果是跨项目分配多次） 常用的开发者权限有： create、alter、drop：库、表结构操作 insert、select、update、delete：数据操作 references：外键权限 index：索引 扩展：可以直接使用赋权创建新用户（MySQL7以上不允许这么操作） grant select on db_2.* to `user`@`localhost` with grant option; MySQL提供的权限列表 Privilege Grant Table Column Context ALL [PRIVILEGES] Synonym for “all privileges” Server administration ALTER Alter_priv Tables ALTER ROUTINE Alter_routine_priv Stored routines CREATE Create_priv Databases, tables, or indexes CREATE ROLE Create_role_priv Server administration CREATE ROUTINE Create_routine_priv Stored routines CREATE TABLESPACE Create_tablespace_priv Server administration CREATE TEMPORARY TABLES Create_tmp_table_priv Tables CREATE USER Create_user_priv Server administration CREATE VIEW Create_view_priv Views DELETE Delete_priv Tables DROP Drop_priv Databases, tables, or views DROP ROLE Drop_role_priv Server administration EVENT Event_priv Databases EXECUTE Execute_priv Stored routines FILE File_priv File access on server host GRANT OPTION Grant_priv Databases, tables, or stored routines INDEX Index_priv Tables INSERT Insert_priv Tables or columns LOCK TABLES Lock_tables_priv Databases PROCESS Process_priv Server administration PROXY See proxies_priv table Server administration REFERENCES References_priv Databases or tables RELOAD Reload_priv Server administration REPLICATION CLIENT Repl_client_priv Server administration REPLICATION SLAVE Repl_slave_priv Server administration SELECT Select_priv Tables or columns SHOW DATABASES Show_db_priv Server administration SHOW VIEW Show_view_priv Views SHUTDOWN Shutdown_priv Server administration SUPER Super_priv Server administration TRIGGER Trigger_priv Tables UPDATE Update_priv Tables or columns USAGE Synonym for “no privileges” Server administration 【例1】给用户admin@localhost分配权限：db_2下所有表的所有权限 create user `admin`@`localhost` identified by '123456'; grant all privileges on db_2.* to `admin`@`localhost`; 【例2】给用户admin分配权限：db_2下的查看视图权限 grant select on db_2.v_student_1 to `admin1`; grant select on db_2.v_student_2 to `admin1`; grant select on db_2.v_student_3 to `admin1`; grant select on db_2.v_student_4 to `admin1`; grant select on db_2.v_student_5 to `admin1`; grant select on db_2.v_student_6 to `admin1`; 【例3】回收权限 # 如果用户不要了，可以直接删除用户，保留用户不给权限，就回收全部权限 revoke all on db_2.* from `admin`@`localhost`; # 针对单独授权表的权限回收：只能针对表进行操作 revoke select on db_2.v_student_1 from `admin1`; 3、角色管理 角色管理：role，即根据角色来分配权限，然后用户只需要关联角色即可（分配角色）：Mysql8以后才有的 角色的存在可以更方便的用户维护多个具有相同权限的用户（核心价值） 角色相关操作和语法 创建角色：create role 角色名字1[,角色名字2,...角色名字N]（可批量创建） 分配权限：grant 权限列表 on 数据库|*.数据表|* to 角色名字 绑定角色：grant 角色名字 to 用户名@主机地址 撤销角色：revoke 角色名字 from 用户名@主机地址 回收角色权限：revoke 权限列表 on 数据库|*.数据表|* from 角色名字 删除角色：drop role 角色名字1[,角色名字2,...角色名字N] 角色管理是利用角色与权限关联，实现角色批量关联用户 方便权限的重复利用 方便相同权限用户的批量维护 角色的使用需要角色创建者（有权限的就行）激活角色才能使用（关联角色的用户需要重新登录才会生效） 【例1】创建用户角色，分配给具有同样权限的用户 # 创建角色（角色与用户名很相似） create role developer,app_read,app_write; # 给角色分配权限 grant all on db_2.* to developer; grant select on db_2.* to app_read; grant insert,update,delete on db_2.* to app_write; # 创建用户，并分配角色给用户 create user 'admin1'@'%' identified by '1234'; create user 'admin2'@'%' identified by '1234'; create user 'admin3'@'%' identified by '1234'; grant developer to 'admin1'@'%'; grant app_read to 'admin2'@'%','admin1'@'%'; # 允许批量给用户分配角色 grant app_write to 'admin3'@'%'; 注意：虽然权限已经最终关联到用户，但是用户并不能真正使用权限，还需要权限分配者每次登陆服务器时激活角色：set default role all to 用户名@主机地址（一次只能激活一个角色） 激活之后对应的用户需要退出之后重新登录才行 【例2】回收角色权限或者角色 # 回收角色权限 revoke insert,delete on db_2.* from app_write; # 回收角色 revoke app_read from 'admin2'@'%'; # 删除角色 drop role developer;

MySQL多表操作 目录： 一、联合查询 1、联合查询 2、联合查询排序 二、连接查询 1、交叉连接 2、内连接 3、外连接 4、自然连接 5、using 关键字 三、子查询 1、标量子查询 2、 列子查询 3、行子查询 4、表子查询 6、exists 子查询 7、比较方式 一、联合查询 1、联合查询 联合查询：union，是指将多个查询结果合并成一个结果显示 联合查询是针对查询结果的合并（多条select语句合并） 联合查询要求：联合查询是结果联合显示 多个联合查询的字段结果数量一致 联合查询的字段来源于第一个查询语句的字段 查询选项：union默认是去重的，想要保留全部查询结果，需要使用union all all：保留所有记录 distinct：保留去重记录（默认） select 查询[字段名字] union 查询选项 select 查询 ... 【例】创建一个表同t_40，并插入数据，联合查询 create table t_42 like t_40; insert into t_42 values(null,'犬夜叉','男',200,'神妖1班'), (null,'日暮戈薇','女',16,'现代1班'), (null,'桔梗','女',88,'法师1班'), (null,'弥勒','男',28,'法师2班'), (null,'珊瑚','女',20,'法师2班'), (null,'七宝','保密',5,'宠物1班'), (null,'杀生丸','男',220,'神妖1班'), (null,'铃','女',4,'现代1班'), (null,'钢牙','男',68,'神妖1班'), (null,'奈落','男',255,'神妖1班'), (null,'神乐','女',15,'神妖2班'); 使用联合查询将两张表的数据拼接到一起显示 select * from t_40 union select * from t_42; # 联合查询选项默认是distinct select * from t_40 union all select * from t_42; 联合查询不要求字段类型一致，只对数量要求一致，而且字段与第一条查询语句相关 select name from t_40 union all select age from t_40; 注意：如果数据不能对应，那么查询没有意义 如果使用where对数据进行筛选，where针对的是select指令，而不是针对union结果 select * from t_40 union all select * from t_42 where gender = '女'; 注意：where只针对第二条select有效；若要全部有效，需要select都使用where 2、联合查询排序 联合查询排序：针对联合查询的结果进行排序 order by本身是对内存结果进行排序，union的优先级高于order by，所以order by默认是对union结果进行排序 如果想要对单独select的结果进行排序，需要两个步骤 将需要排序的select指令进行括号包裹（括号里使用order by） order by必须配合limit才能生效（limit一个足够大的数值即可） 【例】将t_40和t_42表的结果使用年龄降序排序 select * from t_40 union all select * from t_42 order by age desc; 【例】t_40表按年龄降序排序，t_42表按年龄升序排序 # 无效方式 (select * from t_40 order by age desc) union (select * from t_42 order by age); # 正确方式 (select * from t_40 order by age desc limit 99999) union (select * from t_42 order by age limit 99999); 二、连接查询 连接查询：join，将两张表依据某个条件进行数据拼接 join左右各一张表：join关键字左边的表叫左表，右边的表叫右表 连接查询的结果都是记录会保留左右表的所有字段（字段拼接） 具体字段数据依据查询需求确定 表字段冲突需要使用表别名和字段别名区分 不同的连表有不同的连接方式，对于结果的处理也不尽相同 连接查询不限定表的数量，可以进行多表连接，只是表的连接需要一个一个的连（A join B join C ...） 1、交叉连接 交叉连接：cross join，不需要连接条件的连接 交叉连接产生的结果就是笛卡尔积 左表的每一条记录都会与右表的所有记录连接并保留 交叉连接没有实际数据价值，只是丰富了连接查询的完整性 【例】交叉连接t_41和t_42表 select * from t_41 cross join t_42; 这种连接无意义，尽量避免使用 2、内连接 内连接：[inner] join，将两张表根据指定的条件连接起来，严格连接 内连接是将一张表的每一条记录去另外一张表根据条件匹配 匹配成功：保留连接的数据 匹配失败：都不保留 内连接可以没有on条件，那么得到的结果就是交叉连接（笛卡尔积），无意义 内连接的on关键字可以换成where，结果是一样（但是不建议使用） 左表 join 右表 on 连接条件 【例】设计学生表和专业表，获取已经选择了专业的学生信息，包括所选专业 # 学生表 create table t_43( id int primary key auto_increment, name varchar(50) not null, course_no int )charset utf8; insert into t_43 values(null,'Student1',1), (null,'Student2',1), (null,'Student3',2), (null,'Student4',3), (null,'Student5',1), (null,'Student6',default); # 专业表 create table t_44( id int primary key auto_increment, name varchar(50) not null unique )charset utf8; insert into t_44 values(null,'Computer'),(null,'Software'),(null,'Network'); 学生和专业在两个表中，所以需要连表 学生必须有专业，而专业也必须存在，所以是内连接 连接条件：专业编号 两张表有两个字段冲突：id、name select t_43.*, t_44.name as course_name from t_43 join t_44 on t_43.course_no = t_44.id; # 使用别名 select s.*, c.name as c_name from t_43 as s join t_44 c on s.course_no = c.id; 字段冲突的话在MySQL里倒是不影响，只是会同时存在，但是后续其他地方使用就不方便了 3、外连接 外连接：outer join，是一种不严格的连接方式 外连接分为两种 左外连接（左连接）：left join 右外连接（右连接）：right join 外连接有主表和从表之分 左连接：左表为主表 右连接：右表为主表 外连接是将主表的记录去匹配从表的记录 匹配成功保留 匹配失败（全表）：也保留，只是从表字段置空 外连接不论是左连接还是右连接，字段的顺序不影响，都是先显示左表数据，后显示右表数据 外连接必须使用on作为连接条件（不能没有或者使用where替代） 外连接与内连接的区别在于数据匹配失败的时候，外连接会保留一条记录 主表数据保留 从表数据置空 【例】查出所有的学生信息，包括所在班级（左连接） select s.*, c.name Course_Name from t_43 s left join t_44 c on s.course_no = c.id; 【例】查出所有班级里的所有学生（右连接） select s.*, c.name Course_Name from t_43 s right join t_44 c on s.course_no = c.id; 4、自然连接 自然连接：natural join，是一种自动寻找连接条件的连接查询 自然连接包含自然内连接和自然外连接 自然内连接：natural join 自然外连接：natural left/right join 自然连接条件匹配模式：自动寻找相同字段名作为连接条件（字段名相同） 自然连接本身不是一种特别连接，是基于内连接、外连接和交叉连接实现自动条件匹配而已 没有条件（没有同名字段）：交叉连接 有条件：内连接/外连接（看关键字使用） 自然连接使用较少，因为一般情况下表的设计很难做到完全标准或者不会出现无关同名字段 【例】自然连接t_43和t_44表 select * from t_43 natural join t_44; 【例】 create table t_45( s_id int primary key auto_increment, s_name varchar(50) not null, c_id int comment '课程id' )charset utf8; insert into t_45 select * from t_43; create table t_46( c_id int primary key auto_increment, c_name varchar(50) not null unique )charset utf8; insert into t_46 select * from t_44; # 自然连接：条件只有一个相同的c_id select * from t_45 natural join t_46; 5、using 关键字 using关键字：连接查询时如果是同名字段作为连接条件，using可以代替on出现（比on更好） using是针对同名字段（using(id) === A.id = B.id） using关键字使用后会自动合并对应字段为一个 using可以同时使用多个字段作为条件 【例】查询t_45中所有的学生信息，包括所在班级名字 select s.*, c.c_name from t_45 s left join t_46 c using(c_id); select * from t_45 s left join t_46 c using(c_id); 三、子查询 子查询：sub query，通过select查询结果当做另外一条select查询的条件或者数据源 【例】想查出某个专业的所有学生信息 select * from t_45 where c_id = ( select c_id from t_46 where c_name = '专业名称' ); 1、标量子查询 标量子查询：子查询返回的结果是一行一列，一个值 标量子查询是用来做其他查询的条件的，通常用简单比较符号来制作条件的 【例】获取computer专业的所有学生 select * from t_45 where c_id = ( select c_id from t_46 where c_name = 'Computer' ); 2、 列子查询 列子查询：子查询返回的结果是一列多行 列子查询通常是作为外部主查询的条件，而且是使用in来进行判定 【例】获取所有有学生的班级信息 select * from t_46 where c_id in ( select distinct c_id from t_45 where c_id is not null ); 3、行子查询 行子查询：子查询返回的结果是一行多列 行子查询需要条件中构造行元素（多个字段组成查询匹配条件） (元素1,元素2,..元素N) 行子查询通常也是用来作为主查询的结果条件 【例】获取学生表中性别和年龄都与弥勒相同的学生信息 # 方法一：两个标量子查询 select * from t_40 where gender = ( select gender from t_42 where name = '弥勒' ) and age = ( select age from t_42 where name = '弥勒' ); # 方法二：构造条件行元素(gender, age) select * from t_40 where (gender, age) = ( select gender, age from t_42 where name = '弥勒' ); 4、表子查询 表子查询：子查询返回的结果是多行多列（二维表） 表子查询多出现在from之后当做数据源（from子查询） 表子查询通常是为了想对数据进行一次加工处理，然后再交给外部进行二次加工处理 【例】获取学生表中每个班级里年龄最大的学生信息（姓名、年龄、班级名字），然后按年龄降序排序显示 # 尝试直接解决 select any_value(name),max(age) m_age,class_name from t_42 group by class_name order by m_age desc; 分组统计中any_value取的是分组后的第一条记录数据（犬夜叉），而我们要的是最大 解决方案：要是在分组之前将所有班级里的学生本身是降序排序，那么分组的第一条数据就是满足条件的数据。但是问题是：order by必须出现在 group by之后，如何解决？ # order by必须在group by之前解决：就要想办法让order by在group by之前而且不在同一条select指令中（同一条无解） # 必须使用子查询解决在不用SQL中的问题，而子查询的结果应该是全部记录信息，所以应该是表子查询，而且是数据源 select any_value(name),max(age),class_name from ( select name, age, class_name from t_42 order by age desc ) as t group by class_name; 依然无效：原因是MySQL7以后若要子查询中的order by生效，需要像联合查询那样，让子查询带上limit select any_value(name), max(age), class_name from ( select name, age, class_name from t_42 order by age desc limit 99999 ) as t group by class_name; 因为order by在子查询的时候已经对结果进行过排序了，所以分组统计后最终结果也就不用再进行排序了，如果需要再进行排序，只要在最终结果后排序即可 如果要用到字段排序，建议在外部查询select字段里使用别名（否则又要统计） select any_value(name), max(age) m_age, class_name from ( select name, age, class_name from t_42 order by age desc limit 99999 ) as t group by class_name order by m_age; 6、exists 子查询 exists子查询：代入查询，将主表（外部查询）的每一行代入到子表（子查询表）进行校验 子查询返回的结果是布尔结果 成功返回true 失败返回false exists子查询通常是作为where条件使用 where exists(子查询) 【例】获取所有有学生的班级信息t_46 select * from t_46 c where exists ( select c_id from t_45 where c.c_id = c_id ); 7、比较方式 比较方式：在子查询中可以使用一些特定的比较方式 特定的比较方式都是基于比较符号一起使用 all：满足后面全部条件 > all(结果集)：数据要大于结果集中的全部数据 any：满足任意条件 \= any(结果集)：数据只要与结果集中的任何一个元素相等 some：满足任意条件（与any完全一样） 结果集：可以是直接的数据也可以是子查询结果（通常是列子查询） 【例】找出t_40表中与t_42表中年龄相同的信息 方案一：使用in列子查询 select * from t_40 where age in ( select distinct age from t_42 ); 方案二：使用exists子查询 select * from t_40 t1 where exists ( select id from t_42 where t1.age = age ); 方案三：使用any或者some匹配（列子查询） select * from t_40 where age = some(select age from t_42);

MySQL高级操作 目录： 一、数据新增 1、批量插入 2、蠕虫复制 3、主键冲突 二、数据查询 1、查询选项 2、字段选择&别名 3、数据源 4、where 语句 5、运算符 6、group by 语句 7、回溯统计 8、分组排序 9、having 子句 10、order by 子句 11、limit 子句 12、总结 三、数据更新 四、数据删除 1、限制删除 2、清空数据 一、数据新增 1、批量插入 批量插入：是一种优化数据逐条插入的方式 MySQL8 以后默认有事务安全，即批量要么都成功要么都失败，不会出现部分问题 全字段批量插入 insert into 表名 values(值列表1),(值列表2),...,(值列表N); 部分字段批量插入（注意字段默认值） insert into 表名 (字段列表) values(值列表1),(值列表2),...,(值列表N); 2、蠕虫复制 蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表） 蠕虫复制的目标是快速增加表中的数据 实现表中数据复制（用于数据备份或者迁移） 实现数据的指数级递增（多用于测试） insert into 表名[(字段列表)] select 字段列表 from 表名; 注意事项 字段列表必须对应上 字段类型必须匹配上 数据冲突需要事先考虑 【例】创建一张新表，将t_30表中的数据迁移到新表中 create table t_35( id int primary key auto_increment, stu_name varchar(20) not null, course varchar(20) not null, score decimal(5,2) )charset utf8; insert into t_35 select * from t_30; # 快速让t_35表中的数据达到超过100条（重复执行） insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35; 3、主键冲突 主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在 主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义） 主键冲突的解决方案 忽略冲突：保留原始记录 insert ignore into 表名 [(字段列表)] values(值列表); 冲突更新：冲突后部分字段更新 insert into 表名 [(字段列表)] values(值列表) on duplicate key update [字段 = 新值...]; 冲突替换：先删除原有记录，后新增记录 效率没有insert高：需要检查是否冲突 replace into 表名 [(字段列表)] values(值列表); 【例1】用户名作为主键的用户注册（冲突不能覆盖） create table t_36( username varchar(50) primary key, password char(32) not null, regtimr int unsigned not null )charset utf8mb4; insert into t_36 values('username','password',12345678); # 冲突忽略 insert ignore into t_36 values('username','12345678',12345678); 【例2】用户名作为主键的记录用户使用信息（不存在新增、存在则更新时间） create table t_37( username varchar(50) primary key, logintime int unsigned )charset utf8; insert into t_37 values('username',12345678); # 冲突更新（替换部分字段数据） insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp(); # 当前时间戳 【例3】用户名作为主键的记录用户使用信息（不存在新增、存在则更新全部） create table t_38( username varchar(50) primary key, logintime int unsigned, clientinfo varchar(255) not null )charset utf8; insert into t_38 values('username',unix_timestamp(),'{PC:chrome}'); # 替换插入 replace into t_38 values('username',unix_timestamp(),'{phone:uc}'); 二、数据查询 1、查询选项 查询选项：用于对查询结果进行简单数据筛选 查询选项是在select关键字之后，有两个互斥值 all：默认，表示保留所有记录 distinct：去重，重复的记录（所有字段都重复） 【例】查看商品表中所有品类的商品信息：重复的商品只保留一次（名字、价格、属性都一致） create table t_39( id int primary key auto_increment, goods_name varchar(50) not null, goods_price decimal(10,2) default 99999999.00, goods_color varchar(20), goods_weight int unsigned )charset utf8mb4; insert into t_39 values(null,'mate10',5499.00,'blue',320), (null,'mate10',5499.00,'gray',320), (null,'nokia3301',1299,'black',420); # 考虑所有字段的去重（不含逻辑主键） select distinct goods_name,goods_price,goods_color,goods_weight from t_39; select goods_name,goods_price,goods_color,goods_weight from t_39; # 不考虑颜色去重 select distinct goods_name,goods_price,goods_weight from t_39; select all goods_name,goods_price,goods_weight from t_39; 2、字段选择&别名 字段选择：根据实际需求选择的要获取数据的字段信息 根据实际需求，明确所需要的字段名字，使用英文逗号,分隔 获取所有字段，使用星号*通配所有字段 字段数据可以不一定是来自数据源（select只要有结果即可） 数据常量：select 1 函数或者变量：select unix_timestamp(),@@version （@@是系统变量的前缀，后面跟变量名） 字段的选择只要在保证数据需求能实现的情况下，尽可能少使用*代替（MySQL优化） 减少服务器的数据读取压力 减少网络传输压力 让客户端能够精确解析数据（不用大海捞针） 字段别名：给字段取的临时名字 字段别名使用as语法实现 字段名 as 别名 字段名 别名 字段别名的目的通常为了保护数据 字段冲突：多张表同时操作有同名字段（系统默认覆盖），想保留全部 数据安全：对外提供数据不使用真实字段名字 【例】查询商品信息 # 全部查询 select * from t_39; # 需求为商品名字和价格 select goods_name, goods_price from t_39; # 别名使用 select goods_name as gn, goods_price as gp from t_39; # 获取当前时间戳和版本号(不需要数据源) select unix_timestamp() as now,@@version as version,@@version; 3、数据源 数据源：from关键字之后，数据的来源。只要最终结果是一个二维表，都可以当做数据源 数据源是为查询、检索提供数据支持的，使用时需要明确指定 单表数据源：数据源就是一张表 select * from t1; 多表数据源：数据来源是多张表。利用一张表的一条数据匹配另外一张表的所有记录，记录结果为：记录数 = 表1记录数 * 表2记录数；字段数 = 表1字段数 + 表2字段数（笛卡尔积） select * from t_27, t_30; 子查询数据源：数据来源是一个查询结果 数据源要求必须是一个表 如果是查询结果必须给起一个表别名 select * from (select * from t_27,t_30) t; # 数据有冲突查不出来 select * from (select * from t_27) as t; 数据表也可以指定别名 表名 as 别名 表名 别名 数据表的别名在负责SQL查询操作时非常有用，而且有些地方是必须使用（如子查询数据源） select * from t_30 as t; select t1.*, t2.stu_name from t_27 as t1, t_30 t2; 4、where 语句 where子句：跟在from数据源之后，对数据进行条件匹配 where是用来匹配条件筛选数据的 where是在磁盘读取后，进入内存之前进行筛选 不符合条件的数据不会进入内存 where筛选的内容因为还没进入内存，所以数据是没有被加工过的 字段别名不能在where中使用 【例1】查询t_35表中学生为lily的成绩信息 select * from t_35 where stu_name = 'Lily'; 因为where是在磁盘取数据时进行条件筛选，此时数据没有进入内存，所以字段别名是无效的 select stu_name name, score from t_35 where name = 'Lily'; 5、运算符 运算符：用来进行字段数据运算，配合where进行条件筛选 比较运算符 >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于） between A and B：A和B之间（A小于B），包括A和B本身（数值比较） in (数据1,数据2,...数据N)：在列举的数据之中 like 'pattern'：像上面样的，用于字符串比较 _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配abc，ab1，但不能匹配abcd） %：匹配当前位置（往后）任意数量任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd） 逻辑运算符 and（逻辑与）、or（逻辑或）、not（逻辑非） null运算符 is null（为空）、is not null（不为空） 【例】查询成绩不及格的所有学生信息 select * from t_35 where score < 60; 【例】查询成绩在60-90之间的学生信息 select * from t_35 where score between 60 and 90; select * from t_35 where score >= 60 and score <= 90; 【例】查询还没有成绩的学生 select * from t_35 where score is null; 6、group by 语句 group by子句：分组统计，根据某个字段将所有的结果分类，并进行数据统计分析 分组统计可以进行统计细分：先分大组，然后大组分小组 分组统计需要使用统计函数 group_concat()：将组里的某个字段全部保留 any_value()：不属于分组字段的任意一个组里的值 count()：求对应分组的记录数量 count(字段名)：统计某个字段值的数量（NULL不统计） count(*)：统计整个记录的数量（较多） sum()：求对应分组中某个字段是和 max()/min()：求对应分组中某个字段的最大/最小值 avg()：求对应分组中某个字段的平均值 group by子句位置：在 where 之后（where可以没有） 【例】查询学生信息 # 创建一张表，存储学生信息 create table t_40( id int primary key auto_increment, name varchar(10) not null, gender enum('男','女','保密'), age tinyint unsigned not null, class_name varchar(10) not null comment '班级名称' )charset utf8; insert into t_40 values(null,'鸣人','男',18,'木叶1班'), (null,'佐助','男',18,'木叶1班'), (null,'佐井','男',19,'木叶2班'), (null,'大蛇丸','男',28,'木叶0班'), (null,'卡卡西','男',29,'木叶0班'), (null,'小樱','女',18,'木叶1班'), (null,'雏田','女',18,'木叶1班'), (null,'我爱罗','男',19,'木叶1班'), (null,'向日葵','女',6,'木叶10班'), (null,'博人','男',8,'木叶10班'), (null,'鼬','男',28,'木叶0班'); 统计每个班的人数 select count(*), class_name from t_40 group by class_name; 统计每个班那女学生数量 select count(*), class_name, gender from t_40 group by class_name, gender; 统计每个班里的人数，并记录班级学生的名字 select count(*),group_concat(name),class_name from t_40 group by class_name; select count(*),any_value(name),class_name from t_40 group by class_name; 7、回溯统计 回溯统计：在进行分组时（通常是多分组），每一次结果的回溯都进行一次汇总统计 【例】统计每个班的男女同学数量，同时要知道班级人数总数 只统计每个班的男女同学数量，没有班级汇总 select count(*), class_name, gender, group_concat(name) from t_40 group by class_name, gender; 汇总统计：回溯 select count(*), class_name, gender, group_concat(name) from t_40 group by class_name, gender with rollup; 回溯原理 graph TB A(统计开始)-->B[大分组:班级名字分组] B-->|木叶1班|C[木叶1班组<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗] B-->|木叶2班|D[木叶2班组<br>佐井] B-->|木叶0班|E[木叶0班组<br>大蛇丸<br>卡卡西<br>鼬] B-->|木叶10班|F[木叶10班组<br>博人<br>向日葵] C-->G[小分组:性别分组] G-->|男|H[木叶1班组:男<br>鸣人<br>佐助<br>我爱罗] G-->|女|I[木叶1班组:女<br>小樱<br>雏田] D-->J[小分组:性别分组] J-->|男|K[木叶2班组:男<br>佐井] E-->L[小分组:性别分组] L-->|男|M[木叶0班组:男<br>大蛇丸<br>卡卡西<br>鼬] F-->N[小分组:性别分组] N-->|男|O[木叶10班组:男<br>博人] N-->|女|P[木叶10班组:女<br>向日葵] H-->Q[性别回溯<br>木叶1班组:NULL<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗] I-->Q K-->R[性别回溯<br>木叶2班组:NULL<br>佐井] M-->S[性别回溯<br>木叶0班组:NULL<br>大蛇丸<br>卡卡西<br>鼬] O-->T[性别回溯<br>木叶10班组:NULL<br>博人<br>向日葵] P-->T Q-->U[班级名字回溯<br>NULL:NULL<br>全部人] R-->U S-->U T-->U U-->V((回溯统计结束)) 8、分组排序 分组排序：在分组后统计结果时可以根据分组字段进行升序或者降序显示数据 默认的系统就会自动对分组结果根据分组字段进行升序排序 可以设定分组结果的排序方式 group by 字段名 [ASC]：升序排序（默认） group by 字段名 DESC：降序排序 【例】对分组结果女性优先显示：gender为枚举，男值为1，女值为2 select count(*), class_name, gender, group_concat(name), any_value(name) from t_40 group by class_name, gender; MySQL8.0开始，group by 默认是没有排序的 除了索引替换排序的优化思路之外的相关排序，我们都必须使用order by来进行 9、having 子句 having子句：类似于where子句，是用来进行条件筛选数据的 having子句本身是针对分组统计结果进行条件筛选的 having子句必须出现在group by子句之后（如果同时存在） having针对的数据是在内存里已经加载的数据 having几乎能做where能做的所有事，但是where却不一定 字段别名（where针对磁盘数据，那时还没有） 统计结果（where在group by之前） 分组统计函数（having通常是针对group by存在的） 能用where解决问题的地方绝不使用 having where 针对磁盘读取数据，源头解决问题 where 能够限制无效数据进入内存，内存利用率较高，而 having 是针对内存数据筛选 【例】获取班级人数小于3的班级 select count(*) as `count`, class_name, group_concat(name) from t_40 group by class_name having `count` < 3; select count(*) as `count`, class_name, group_concat(name) from t_40 group by class_name having count(*) < 3; # 多用了一次函数（效率降低） select class_name, group_concat(name) from t_40 group by class_name having count(*) < 3; 10、order by 子句 order by子句：排序，根据某个指定的字段进行升序或者降序排序 排序的参照物是校对集 order by子句在having子句字后（如果同时存在） 排序分为升序和降序：默认是升序 order by 字段 [ASC]：升序 order by 字段 DESC：降序 多字段排序：在根据某个字段排序好后，可以再细分排序 【例】单字段排序：给所有学生按照年纪大小升序排序 select * from t_40 order by age; select * from t_40 order by age asc; 【例】多字段排序：先性别降序排序，然后按年龄升序排序 select * from t_40 order by gender desc, age; select * from t_40 order by gender desc, age asc; 11、limit 子句 limit子句：限制数据的获取数量（记录数） limit子句必须在order by子句之后（如果同时存在） limit限制数量的方式有两种 limit 数量：限制获取的数量（不保证一定能获取到指定数量） limit 起始位置, 数量：限制数据获取的位置以及数量（分页） 【例】获取前三条数据 select * from t_40 limit 3; 【例】获取t_40表中第3条以后的3条数据 select * from t_40 limit 3, 3; select * from t_40 limit 6, 3; 12、总结 完整语法： select select选项 字段列表[别名]|* from 数据源[别名] where子句 group by子句 having子句 order by子句 limit 子句; 三、数据更新 限制更新：即更新时对更新的记录数进行限制 限制更新通过limit来实现 限制更新其实是局部更新的一种手段，一般更多情况下是依据条件精确更新 实际开发当中，极少出现这类操作，一般都愿意精准操作（利用where条件明确更新条件） 【例】对会员选3个发送10元红包（添加到账户） create table t_41( id int primary key auto_increment, username varchar(50) not null unique, password char(32) not null, account decimal(10,2) default 0.00 )charset utf8; insert into t_41 values(null,'username1','password',default), (null,'username2','password',default), (null,'username3','password',default), (null,'username4','password',default), (null,'username5','password',default); update t_41 set account = account + 10 limit 3; 四、数据删除 1、限制删除 限制删除：限制要删除的记录数 使用limit限制删除数量 一般很少使用限制删除，通常是通过where条件精确删除 delete from t_41 where account = 0 limit 1; 2、清空数据 清空数据：将表中的所有数据清除，并且将表的所有状态回到原始状态 清空数据的本质是先删除表，后创建表 清空数据表是一种比delete更彻底的数据删除方式，所以使用之前必须要慎重 清空数据能够让表的一些变化状态回到原始状态 自增长重新回到初始值 清空语法： truncate 表名 【例】 truncate t_41;

MySQL数据库设计规范 目录： 一、关系型数据库设计范式 1、第一范式 1NF 2、第二范式 2NF 3、第三范式 3NF 4、BCNF 5、逆规范化 二、表关系 1、一对一关系 2、一对多关系 3、多对多关系 一、关系型数据库设计范式 范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度 范式目标是在满足组织和存储的前提下使数据结构冗余最小化，用来减少数据冗余 范式级别越高，表的级别就越标准 各种范式之间的关系：$5NF \subset 4NF \subset BCNF \subset 3NF \subset 2NF \subset 1NF$ 1、第一范式 1NF 第一范式：1NF，关系中每一分量不可再分。数据字段设计时必须满足原子性 满足1NF 的关系称为规范化的关系 如果数据使用的时候需要进行拆分那么就违背1NF 1NF是满足数据表设计的最基础规范 示例 设计一张学生选修课成绩表 学生 性别 课程 教室 成绩 学习时间 张三 男 PHP 101 100 2月1日,2月28日 李四 女 Java 102 90 3月1日,3月31日 张三 男 Java 102 95 3月1日,3月31日 当前表的学习时间在使用的时候肯定是基于开始时间和结束时间的，而这种设计就会存在使用时的数据拆分，不满足原子性也就是1NF 满足1NF的设计：字段颗粒度应用层最小（不需要拆分） 学生 性别 课程 教室 成绩 开始时间 结束时间 张三 男 PHP 101 100 2月1日 2月28日 李四 女 Java 102 90 3月1日 3月31日 张三 男 Java 102 95 3月1日 3月31日 2、第二范式 2NF 第二范式：若R∈1NF，且每个非主属性完全函数依赖于码，则称R∈2NF 2NF的定义要求消除非主属性对码的部分依赖。 部分依赖：首先表存在复合主键，其次有的字段不是依赖整个主键，而只是依赖主键中的一部分 部分依赖解决：让所有非主属性都依赖一个候选关键字 最简单方式：取消复合主键（一般选用逻辑主键替代，但是本质依然是复合主键做主），所有非主属性都依赖主属性（逻辑主键） 正确方式：将部分依赖关系独立成表 示例 学生成绩表中学生和课程应该是决定性关系，因此属于主属性（主键） 学生（P） 性别 课程（P） 教室 成绩 开始时间 结束时间 张三 男 PHP 101 100 2月1日 2月28日 李四 女 Java 102 90 3月1日 3月31日 张三 男 Java 102 95 3月1日 3月31日 成绩是由学生和课程决定的，是完全依赖主属性 性别只依赖学生（部分依赖） 教室、开始时间和结束时间依赖课程（部分依赖） 解决方案：将学生信息维护到一张表，课程信息维护到一张表，成绩表取两个表的主属性即可 学生表 Stu_id（P） 姓名 性别 1 张三 男 2 李四 女 Stu_id是姓名的代指属性（逻辑主键，本质主键是姓名） 性别只依赖主属性 课程表 Class_id（P） 课程 教室 开始时间 结束时间 1 PHP 101 2月1日 2月28日 2 Java 102 3月1日 3月31日 Class_id是课程的代指属性（逻辑主键） 教室、开始时间和结束时间都依赖课程（主属性） 成绩表 Stu_id（P） Class_id（P） 成绩 1 1 100 2 2 90 1 2 95 Stu_id和Class_id共同组成主属性（复合主键） 成绩依赖Stu_id和Class_id本身，不存在部分依赖 3、第三范式 3NF 第三范式：3NF，关系模式 $R<U,F>$ 中，若不存在这样的码 $X$，属性组 $Y$ 及非主属性 $Z(Z \nsubseteq Y)$，使得 $X \to Y(Y \nrightarrow X), Y \to Z$ 成立 3NF的定义要求消除非主属性对码的传递依赖 传递依赖：字段某个非主属性不直接依赖主属性，而是通过依赖某个其他非主属性而传递到主属性之上 传递依赖解决：让依赖非主属性的字段与依赖字段独立成表 示例 学生表：包括所在系信息 学号（P） 姓名 专业编号 专业名字 1 张三 0001001 软件工程 2 李四 0001002 土木工程 姓名和专业编号都依赖于学号（为学号提供信息支持） 专业名字依赖专业编号（为编号提供信息支持） 专业名字间接依赖学号：传递依赖 随着学生增加，专业名字会出现大量数据冗余 解决方案：将存储传递依赖部分的字段（非主属性）独立成表，然后在需要使用相关信息的时候，引入即可 专业表 专业编号（P） 专业名字 0001001 软件工程 0001002 土木工程 即使有更多的信息为专业提供支持也不存在传递关系 学生表 学号（P） 姓名 专业编号 1 张三 0001001 2 李四 0001002 姓名和专业编号都依赖学号（为学号提供信息支持） 没有其他字段是通过非主属性（专业编号）来依赖主属性的：没有传递依赖 学生再多，专业名字信息只需要维护一次，减少数据冗余 4、BCNF BCNF：关系模式 $R<U,F> \in 1NF$ ，对于属性组$X$ 和 $Y$，若 $X \to Y$ 且 $Y \nsubseteq X$ 必含有码，则 $R<U,F> \in BCNF$ 所有非主属性对每一个码都是完全函数依赖 所有的主属性对每一个不包含它的码,也是完全函数依赖 没有任何属性完全函数依赖于非码的任何一组属性 示例 仓库管理关系表：满足一个管理员只在一个仓库工作；一个仓库可以存储多种物品 仓库 管理员 物品名 数量 上海 李华 Huawei 100 上海 李华 iPhone 50 北京 小明 Huawei 70 北京 小明 iPhone 50 (仓库，物品) 和 (管理员，物品) 都是仓库管理关系表的候选码，表中唯一非关键字段为数量，此关系模式已经属于了 3NF，但还存在以下问题： 插入异常：新增加一个仓库，但尚未存放任何物品，无法为该仓库指派管理员——物品名也是主属性，根据实体完整性的要求，主属性不能为空。 删除异常：某仓库被清空后，需要删除所有与这个仓库相关的物品存放记录——仓库本身与管理员的信息也被随之删除。 更新异常：如果某仓库更换了管理员，这个仓库有几条物品存放记录，就要修改多少次管理员信息。 解决方案：把仓库管理关系表分解为两个关系表仓库管理表(仓库，管理员)和仓库表(仓库，物品，数量) 仓库管理表 仓库 管理员 上海 李华 北京 小明 仓库表 仓库 物品 数量 上海 Huawei 100 上海 iPhone 50 北京 Huawei 70 北京 iPhone 50 5、逆规范化 逆规范化：为了提升数据查询的效率而刻意违背范式的规则 逆规范化的目标是为了提升数据访问效率 所谓逆规范化就是减少表之间的关联查询（效率降低），刻意增加数据冗余 示例 1、学生成绩表需要经常查询，而且数据量很大，但是： 成绩表中只有学号，显示的时候需要学生姓名（去学生表中连表查询） 成表表中只有课程号，显示的时候需要显示课程名（去课程表中连表查询） 逆规范化：将学生姓名和课程名在表中冗余维护（不满足2NF） 学号（P） 学生姓名 课程号（P） 课程名字 成绩 1 张三 1 PHP 100 1 张三 2 Java 90 学生姓名部分依赖学号（主属性）：不满足2NF 学生姓名和课程名字会有大量数据冗余存在（不满足2NF导致） 二、表关系 一对一关系 一对多关系（多对一） 多对多关系 1、一对一关系 一对一关系：一张表中的一条记录与另外一张表中有且仅有一条记录有关系 一对一关系通常是用来将一张原本就是一体的表拆分成两张表 频繁使用部分：常用字段 不常使用部分：生僻字段 使用相同的主键对应 一对一关系设计较多使用在优化方面，因为一对一关系表通常有相同信息作为匹配条件，所以查询方式也比较方便 连表操作：利用共有信息进行匹配，一并查出一条完整信息 多次查询：利用共有信息进行多表查询，利用程序组合成一条完整信息 示例 学生信息表 学号（P） 姓名 性别 年龄 身高 体重 籍贯 政治面貌 1 张飞 男 20 178 160 蜀 农民 2 武则天 女 21 168 110 唐 党员 以上数据表信息字段较多 姓名、性别、年龄属于常用字段，频繁查询 2、一对一关系设计 将常用字段取出，与学号组合成一张常用表 将不常用字段取出，与学号组合成一张不常用表 表与表数据对应关系：基于学号（唯一）是一对一关系 常用表 学号（P） 姓名 性别 年龄 1 张飞 男 20 2 武则天 女 21 不常用表 学号（P） 身高 体重 籍贯 政治面貌 1 178 160 蜀 农民 2 168 110 唐 党员 2、一对多关系 一对多关系：也叫多对一关系，一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录 一对多关系是实体中非常常见的一种关系，实体设计时也应用非常多 一对多关系的核心解决方案是如何让记录能够正确匹配到另外表中的数据 一表设计：一表记录在另外一张表中有多条记录，所以无法记录多个字段（违背1NF） 多表设计：多表记录在另外一张表中只有一条记录，可以设置字段记录对应的主属性（通常主键） 示例 1、老师与学科间的关系：一个老师只能教一个学科，但是一个学科有多个老师教授，学科与老师形成的关系就是一对多（反过来老师与学科的关系就是多对一关系） 老师表（多表） 老师ID（P） 姓名 年龄 性别 1 张老师 35 男 2 李老师 34 女 3 王老师 30 男 学科表（一表） 学科ID（P） 名字 课时长度 1 PHP 600 2 Java 800 以上两个实体没有体现彼此之间的关联关系 实际上讲师与学科肯定是有关联的 2、在多表（讲师）中增加字段维护一表（学科）的关系型，形成多对一关系 老师ID（P） 姓名 年龄 性别 学科ID 1 张三 35 男 1 2 李四 34 女 1 3 王五 30 男 2 基于新的讲师表与学科表产生了关联关系（多对一） 基于讲师表可以知道讲师所属学科 基于学科ID可以统计出不同学科的讲师数量 3、多对多关系 多对多关系：一张表中的一条记录对应另外一个表中多条记录，反过来一样 多对多关系在实体中是最常见的关系 多对多关系是无法在自身表中维护对应表关系的（违背1NF），需要通过第三方表来实现将多对多关系变成多个多对一关系 设计一个中间表：记录两张表之间的对应关系（主属性） 中间表与其他表都是多对一的关系 示例 1、老师与学生之间的关系：一个老师会教授多个学生，一个学生也会听多个老师的课，所以实体关系是多对多关系 老师表 老师ID（P） 姓名 年龄 性别 1 张老师 35 男 2 李老师 34 女 3 王老师 30 男 学生表 学生ID（P） 姓名 年龄 性别 1 小明 15 男 2 小红 14 女 3 小萌 14 女 以上实体没有从结构上体现表之间的关系 2、设计一个中间表：老师与学生关系表，将老师与学生的对应关系对应上（多对一） 中间表 ID（P） 学生ID 老师ID 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 2 3 7 3 1 8 3 3 中间表与老师表的对应关系是多对一：通过老师ID可以找到每一个上过课的老师 中间表与学生表的对应关系是多对一：通过学生ID可以找到每一个听过课的学生 老师找学生：老师表 → 中间表（找出老师对应的学生ID） → 学生表（找出学生ID对应的学生信息） 学生找老师：学生表 → 中间表（找出学生对应的老师ID） → 老师表（找出老师ID对应的老师信息）

MySQL数据库字段详解 目录： 一、字段类型 1、整数类型 2、显示宽度 3、小数类型 浮点型 定点型 4、字符串类型 定长型 变长型 文本字符串 枚举 集合 5、时间日期类型 年 时间戳 日期 日期时间 时间 6、总结 二、属性 1、NULL属性 2、Default属性 3、主键 4、自增长属性 5、唯一键 6、comment 属性 三、数据库记录长度 一、字段类型 字段类型：MySQL中用来规定实际存储的数据格式 字段类型在定义表结构的时候设定 设定好字段类型后，插入数据时必须与字段类型对应；否则数据错误 字段类型作用：强制规范录入的数据格式，保证数据的有效性 MySQL有四大数据类型：整数类型、小数类型、字符串类型、时间日期类型 1、整数类型 MySQL中为了数据空间的有效使用，设定了五种整数类型： 迷你整形：tinyint，使用1个字节存储整数（-128~127） 短整型：smallint，使用2个字节存储整数 中整型：mediumint，使用3个字节存储整数 标准整型：int，使用4个字节存储整数 大整型：bigint，使用8个字节存储整数 数值型类型在MySQL中默认是有符号的，即有正负：无符号需要使用unsigned修饰整型，即纯正数 【例1】设计一个表记录个人信息：年龄、头发数量 # 年龄：没有负数，正常年龄也不超过200岁，迷你整型无符号即可 # 头发数量：没有负数，大概在几百万根，所以标准整型无符号即可 create table t1( age tinyint unsigned, # unsigned修饰整数，表示无符号（从0开始） haircount int unsigned )charset utf8mb4; 【例2】设计一个表记录4S店的汽车销量信息：库存数量、销量、采购量 # 4S店经常是先卖后进货，所以库存可能为负数，一个店铺的库存数通常不会太多，那么小整型即可 # 销量通常全国一个月也就几万台，所以基本上小整型就够了，但是为了保证后续可能存在的爆发，那么中整型也是绝对够了的，而且不会是负数 # 采购通常是正向采购，所以不会出现负数，而采购数量与销量持平就好 create table t2( stock smallint, sales mediumint unsigned, purchase mediumint unsigned )charset utf8; 2、显示宽度 显示宽度：int(L)，整数在数据库中显示的符号（数字+符号）个数 显示宽度并不表示存储长度，是用来填充显示位的 例如tinyint(3)，如果实际值是8，如果列指定了zerofill，查询后显示的结果就是008，左侧是用0来填充的。 显示宽度包含符号（如果允许为负数，-负号会增加一个宽度） 显示宽度可以主动控制：创建字段时加括号确定 显示宽度不会影响类型能表示的最大数值 可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill 有符号和无符号对应的宽度不一样 create table t1( a tinyint, b tinyint unsigned )charset utf8; 可以主动控制显示宽度 alter table t1 add c tinyint(2) unsigned; 显示宽度不影响数据的大小 # 小于显示宽度 insert into t1 values(1,1,1); # 大于显示宽度 insert into t1 values(100,100,100); 可以通过zerofill让小于显示宽度的数值前置补充0到显示宽度 # 0填充只能针对正数 alter table t1 add d tinyint(2) zerofill; insert into t1 values(1,1,1,1); 3、小数类型 浮点型 浮点数：float/double，存储不是特别精确的数值数据 浮点数又称之为精度数据，分为两种 单精度：float，使用4个字节存储，精度范围为6-7位有效数字 双精度：double，使用8个字节存储，精度范围为14-15位有效数字 浮点数超过精度范围会自动进行四舍五入 精度可以指定整数和小数部分 默认不指定，整数部分不超过最大值，小数部分保留2位 可以指定：float/double(总长度,小数部分长度) 可以使用科学计数法插入数据：AEB，A * 10 ^ B 【例】记录商品的价格 create table t_11( goods_name varchar(20), goods_price float )charset utf8; insert into t_11 values('Nokia3310',199.99); insert into t_11 values('Nokia6100',1999.9999); 定点型 定点型：decimal，能够保证精度的小数 定点数可以指定长度 decimal：默认 整数部分为10位 小数部分为0 decimal(有效位数, 小数位数) 整数部分为：有效位数 - 小数位数 有效数位不超过65个 定点数的存储模式不是固定长度，所以数据越大占用的存储空间越长，每9个数字使用4个字节存储 【例】记录个人资产情况：资产和负债 create table t1( money decimal(14,2); bet decimal(10,2); )charset uft8mb4; insert into t1 values(1111111111.12,1111111.999); # 错误：进位导致正数部分超过指定范围 insert into t1 values(1111111111.12,99999999.999); 4、字符串类型 定长型 定长型：char(L)，指定固定长度的存储空间存储字符串 定长是指定存储长度 定长的长度是字符而不是字节 L的最大值是255 实际存储空间：L字符数 * 字符集对应字节数 定长里存储的数据不能超过指定长度，但是可以小于指定长度 字符串数据使用单引号或者双引号包裹 定长的访问效率较高，但是空间利用率不高 【例】记录个人信息：身份证信息和手机号码 create table t1( id_number char(18), phone char(11) )charset utf8mb4; insert into t1 values('410000190001011234', '13666666666'); 变长型 变长型：varchar(L)，根据实际存储的数据变化存储空间 变长型的存储空间是由数据的长度实际计算存储空间 变长型的L也是指字符而不是字节 L指定的是最大存储的数据长度 L最大值理论是65535 变长需要额外产生1-2个字节，用来记录实际数据的长度 数据长度小于256个，多1个字节 数据长度大于256个，多2个字节 实际存储空间：实际字符数 * 字符集对应字节数 + 记录长度 变长数据不能超过定义的最大长度 变长字符串在读取时需要进行长度计算，所以效率没有定长字符串高 【例】记录个人信息：用户名、密码、姓名、身份证 create table t1( username varchar(50), password varchar(20), name varchar(10), id_number char(18) )charset utf8mb4; insert into t1 values('user','123456','user1','410000190010101234'); 文本字符串 文本字符串：text/blob，专门用来存储较长的文本 文本字符串通常在超过255个字符时使用 文本字符串包含两大类 text：普通字符 tinytext：迷你文本，不超过 $2 ^ 8 -1$ 个字符 text：普通文本，不超过 $2 ^ {16} - 1$ 个字符 mediumtext：中型文本，不超过 $2 ^ {24} - 1$ 个字符 longtext：长文本，不超过 $2 ^{32} - 1$ 个字符（4G） blob：二进制字符（与text类似） tinyblob、blob、mediumblob、longblob 文本字符串会自动根据文本长度选择适合的具体类型 一般在文本超过255个字符时，都会使用text（blob现在极少使用） 【例】记录新闻信息：标题、作者和内容 create table t1( author varchar(10), title varchar(50), content text )charset utf8mb4; insert into t1 values(''佚名','给联合国的一封信','给联合国的一封信...'); 枚举 枚举：enum，一种映射存储方式，以较小的空间存储较多的数据 枚举在定义后数据只能出现定义时其中的一种 枚举使用1-2个字节存储，最多可以设计65535个选项 枚举实际存储是使用数值，映射对应的元素数据，从1开始 【例】记录人群类型：小朋友、少年、青年、中年、老年，每个人实际只属于一种类别 create table t1( type enum('小朋友','少年','青年','中年','老年') )charset utf8mb4; insert into t_16 values('少年'); insert into t_16 values('仙人'); # 不存在的数据不能插入 enum是建立映射关系，然后实际存储是数字，数值是按照元素顺序从1开始 # 可以使用字段 + 0来判定数据具体的效果（字符串转数值为0） select type, type + 0 from t1; insert into t1 values(5); 枚举定义原理 枚举数据 映射值 数据1 1 数据2 2 ... ... 数据N N（小于65535） 数据存储（读取反过来） graph LR A(指令开始)-->B[插入数据] B-->C[读取映射关系<br>元素==数值<br>数值==数值] C-->D[数值存储到字段] D-->E((结束)) 集合 集合：set，一种映射存储方式，以较小的空间存储较多的数据 集合类似一种多选框，在定义后数据只能出现定义时其中的元素（可以是多个） 集合使用1-8个字节存储数据，最多可以设计64个元素 集合实际存储是使用数值（二进制位），映射对应的元素数据，每个元素对应一个比特位 数据存在：对应位为1 数据不存在：对应位为0 集合语法：set(元素1,元素2,...元素N) 集合定义原理 集合数据 映射位 数据1 00000001 数据2 00000010 ... ... 数据8 10000000 数据存储（读取反过来） graph LR A(指令开始)-->B[插入数据] B-->C[读取映射关系<br>元素选中==位值为1<br>元素未选中==位值为0] C-->D[转化成十进制存储] D-->E((结束)) 【例】记录个人的球类爱好，有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球 create table t1( hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球') )charset utf8; insert into t1 values('足球'); insert into t1 values('冰球,台球,篮球'); 集合建立的也是映射关系，映射方式是每个元素对应一个字节的比特位，从左边开始第一个对应字节从右边开始的第一位 # 可以通过字段 + 0的方式查看存储的具体数值 select hobby, hobby + 0 from t1; # 可以通过插入数值来组合元素，但是需要确定对应的十进制转换成二进制有对应的元素对应 insert into t1 values(194); # 冰球、台球、篮球 # 11000010 => 1 + 2 ^ 1 + 1 * 2 ^ 6 + 1 * 2 ^ 7 = 2 + 64 + 128 = 194 5、时间日期类型 年 年：year，MySQL中用来存储年份的类型 MySQL中使用1个字节存储年份 year能够表示的范围是1901-2155年（256年） year的特殊值是：0000 year允许用户使用两种方式设计（效果一样） year year(4) year数据可以用两种方式插入 直接插入4位年，1901-2155之间都可以 插入2位数，系统会自动匹配对应的年份 69以前：系统加上2000 69以后：系统加上1900 create table t1( y1 year, y2 year(4) )charset utf8mb4; insert into t1 values(1901,2155); insert into t1 values(69,70); insert into t1 values(00,0000); 时间戳 时间戳：timestamp，基于格林威治时间的时间记录 MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式 YYYY-MM-DD HH:II::SS YYYYMMDDHHIISS timestamp使用4个字节存储，表示范围是1971年1月1日0时0分0秒-2155年12月31日23是59分59秒 timestamp的特点是所对应的记录不论哪个字段被更新，该字段都会更新到当前时间 注意：在MySQL8以后，取消了 timestamp 的默认自动更新，如果需要使用，需要额外使用属性： on update current_timestamp 【例】记录商品库存的最后更新时间 create table t_19( goods_name varchar(10), goods_inventory int unsigned, change_time timestamp )charset utf8; insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00'); insert into t_19 values('Nokia7100',100,'19710101000000'); # timestamp会在自己所在的记录任何位置被修改时自动更新时间 update t_19 set goods_inventory = 90; # 在MySQL8中需要主动使用on update current_timestamp才会自动更新 alter table t_19 add c_time timestamp on update current_timestamp; update t_19 set goods_inventory = 80; 日期 日期：date，用来记录年月日信息 使用3个字节存储数据，存储区间是1000 - 9999年，跨度很大 存储日期的格式为：YYYY-MM-DD 存储的范围是：1001-01-01~9999-12-31 【例】记录个人生日 create table t_20( name varchar(10), birth date )charset utf8mb4; insert into t_20 values('A','2020-02-25'); insert into t_20 values('B','20200225'); 日期时间 日期时间：datetime，用来综合存储日期和时间 使用8个字节存储数据 存储格式为：YYYY-MM-DD HH:II:SS 存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59 实际开发中因为编程语言（PHP）的强大，实际存储的时候通常不会使用这种类型 占用较大存储空间 处理不够灵活（固定格式） 使用int unsigned存储时间戳然后利用PHPdate进行格式处理 【例】记录个人具体的出生时间 create table t_21( name varchar(10), birth datetime )charset utf8mb4; insert into t_21 values('Jim','2000-12-12 12:12:12'); insert into t_21 values('Tom','10011212182323'); 时间 时间：time，用来记录时间或者时间段 使用3个字节存储数据 数据范围是 -838:59:59 - 838:59:59 数据插入的格式分为两种 时间格式：[H]HH:II:SS（[]表示可以没有） 时间段格式：D HH:II:SS（D表示天） time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查） 【例】记录用户登录的具体时间 # 具体登录时间可以使用时间戳（包含年月日时分秒信息） # 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用） create table t_22( login_time1 int unsigned, login_time2 datetime, login_date date, login_time3 time )charset utf8mb4; insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12'); insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12'); 6、总结 1、字段类型是用来规范数据的格式的 2、MySQL中有很多类型用来规范数据格式 整数类型（常用） 常用类型：tinyint、int 小数类型（常用） 常用类型：decimal、float 字符串类型（常用） 常用类型：char、varchar、text 时间日期类型（不常用：通常使用真正时间戳存储数据，然后PHP进行灵活解读） 3、实际开发的时候，一定要仔细了解需求，根据需求判定好具体选用那种数据类型 最原始的维护能够具有最大的通用性（选中类型） 最小的消耗能够解决全部的问题（巧妙利用存储空间） 二、属性 属性：建立在字段类型之后，对字段除类型之外的其他约束 属性是在定义表字段的时候针对每个字段进行属性设定 设定好的属性可以通过查看表字段desc进行查看 数据在进行增删改（写）操作时需要在满足字段的要求同时还要满足属性的要求 1、NULL属性 NULL：数据是否允许为空 默认情况下数据是允许为空的 不为空设计：Not Null 一般有效的数据都必须设定为Not Null来保证数据的有效性 【例】用户信息表：用户名、密码、姓名、年龄、注册时间 create table t_23( username varchar(50) not null, password char(30) not null, name varchar(20), age tinyint unsigned, reg_time int unsigned not null )charset utf8mb4; insert into t_23 values('username','password','Jim',20,123456789); # 错误操作：reg_time不能为空，而默认为空，所以系统报错 insert into t_23 (username,password) values('username','password'); 2、Default属性 默认值：default，在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值 默认值在字段设计的时候使用（默认值需要满足数据类型规范） 默认值通常设计的是字段容易出现的数据 一般字段的默认值默认是Null 默认值触发 在系统进行数据插入时自动检测触发 主动使用default关键字触发默认值 【例】用户开户：银行卡账号、身份证号码、姓名、账户余额 create table t_24( account varchar(19) not null, id_card char(18) not null, name varchar(20) not null, money decimal(16,2) default 0.00 not null )charset utf8mb4; # 默认触发 insert into t_24 (account,id_card,name) values('6226000000000001','44011120001212000x','Lily'); # 主动触发 insert into t_24 values('6226000000000002','440111200012120011','Tom',default); insert into t_24 values('6226000000000003','440111200012120022','Jim',100); 3、主键 主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据 主键在一张表中只能有一个 主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引） 主键不能为空：Not Null（默认） 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据 逻辑主键通常是整数：int 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息） 复合主键：多个字段共同组成不能重复的数据 primary key(字段1,字段2,...字段N) 联合主键使用不多，一般也不会超过2个字段 【例】银行账户信息：账户、姓名、余额 # 银行账户具有唯一性，不能重复，也不允许为空 create table t_25( account varchar(17) primary key, name varchar(20) not null, money decimal(16, 2) not null default 0.00 )charset utf8mb4; # 复合主键 create table t_26( account varchar(17), name varchar(20), money decimal(16, 2) not null default 0.00, primary key(account,name) )charset utf8mb4; # 一般使用逻辑主键 create table t_27( id int unsigned primary key, account varchar(17) not null, name varchar(20) not null, money decimal(16, 2) not null default 0.00 )charset utf8mb4; insert into t_27 values(1,'6226000000000001','Lily',default); # 错误：主键1已经存在 insert into t_27 values(1,'6226000000000002','Tom',default); # 联合主键就是联合字段加起来不重复即可 insert into t_26 values('6226000000000001','Tom',default); insert into t_26 values('6226000000000002','Tom',default); 删除主键：主键只有一个，所以删除语法也比较特殊 alter table t_26 drop primary key; 后期新增主键：如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复（一般主键都会在表创建时维护好） alter table t_26 add primary key(account, name); 4、自增长属性 自增长：auto_increment，被修饰的字段在新增时，自动增长数据 自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键） 一张表只能有一个自动增长 自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发 自增长由两个变量控制 初始值：auto_increment_offset，默认是1 步长：auto_increment_increment，默认值也是1 查看自增长控制：show variables like 'auto_increment%'; 【例】记录学生信息：学号和姓名 # 学生信息：学号自动增长 create table t_28( id int primary key auto_increment, stu_no int(8) zerofill not null, stu_name varchar(20) not null )charset utf8mb4; # 使用自增长（可以使用NULL或者default来触发） insert into t_28 values(null, 1, 'Jim'); insert into t_28 values(default, 2, 'Tom'); # 主动控制：自增长的值会从当前最大的值开始自动增长 insert into t_28 values(10, 3, 'Lily'); insert into t_28 values(null, 4, 'Lucy'); 修改表中自增长的值：让下次自增长按照指定值开始 alter talbe t_28 auto_increment = 50; 注意：奇数会保留原值，偶数会自动加1（可能出现的情况） 修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表） # 当前用户当前连接有效（局部） set auto_increment_increment = 2; # 所有用户一直有效（全局） set @@auto_increment_increment = 2; 5、唯一键 唯一键：unique key，用来维护数据的唯一性 一个表中可以有多个唯一键 唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限） 唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引） 复合唯一键：多个字段共同组成 unique key (字段1, 字段2, ...字段N) 一般不会出现，最多2个字段组成 【例】用户表：用户名唯一，而且经常作为查询条件 create table t_29( id int primary key auto_increment, username varchar(50) unique, password char(32) not null )charset utf8mb4; insert into t_29 values(null, 'username', 'password'); insert into t_29 values(null, Null, 'password'); insert into t_29 values(null, Null, 'password'); # 错误 insert into t_29 values(null,'username','password1'); 【例】学生成绩表：一个学生只能有一个学科成绩，但是可以有多个学科 # 学号和学科编号共同组成唯一 create table t_30( id int primary key auto_increment, stu_name varchar(20) not null, course varchar(20) not null, score decimal(5,2), unique key(stu_name, course) )charset utf8; insert into t_30 values(null,'Jim','Math',50); insert into t_30 values(null,'Jim','English',80); 删除唯一键：一张表中不止一个唯一键，所以删除方式是相对麻烦 alter table 表名 drop index 唯一键名字; 例如： alter table t_30 drop index `stu_name`; 新增唯一键 alter table 表名 add unique key(字段列表); 例如： alter table t_30 add unique key `stu_course`(stu_name, course); 6、comment 属性 描述：comment，是用文字描述字段的作用的 描述如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码） 【例】学生成绩表 create table t_31( id int primary key auto_increment, stu_no varchar(10) not null comment '学号', course_no varchar(10) not null comment '课程号', score decimal(5,2) comment '考试成绩', unique key `stu_course` (stu_no,course_no) comment '学号和课程号组成唯一键' )charset utf8; 如果要查看描述信息，需要使用 show create table 查看 三、数据库记录长度 数据库记录长度：MySQL中规定一条记录所占用的存储长度最长不超过65535个字节 记录长度为表中所有字段预计占用的长度之和 所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好） 因为MySQL记录长度的存在，varchar永远达不到理论长度 一般数据长度超过255个字符都会使用text/blob进行存储（数据存储不占用记录长度） 【例】GBK表能存储的最大varchar字符串长度 create table t_32( content varchar(65535) )charset gbk; # 错误 create table t_32( content varchar(32767) )charset gbk; # 错误 create table t_32( content varchar(32766) )charset gbk; 【例】UTF8表能存储的最大varchar字符串长度 create table t_33( content varchar(65535) )charset utf8; # 错误 create table t_33( content varchar(21844) )charset utf8; 【例】Null也要占用一个字节 create table t_34( id tinyint, content varchar(21844) )charset utf8; # 错误 create table t_34( id tinyint not null, content varchar(21844) not null )charset utf8;

MySQL数据库字符集 目录： 一、字符集 1、字符集概念 数据存储 数据读取 2、MySQL字符集 数据库内部对象字符集原理 客户端存储数据原理 客户端读取数据原理 总结 3、乱码问题解决 4、字符集设置原理 二、校对集 1、校对集概念 2、校对集设置 3、校对集应用 一、字符集 1、字符集概念 字符集：charset或者character set，是各种文字和符号在计算机中的总称 字符集根据不同国家的符号不同，有不同的字符集 不同的字符集占用的存储空间不一样，存储的底层也不一样 不同字符集之间可以进行转换 字符集是指定字符的存储和读取的规范 指定的字符集存储需要使用对应的字符集读取 错误的字符集存储或者读取都会产生乱码 常见字符集： ASCII：美国信息交换标准码，一般英文符号，一个字节存储 latin1：拉丁文字符集，一个字节存储，ISO-8859-1的别名 ，能够向下兼容ASCII GB2312：信息交换用汉字编码字符集 ，是中国1981年的一套国标规范，2个字节存储 GBK：汉字内码扩展规范（1995年），两个字节表示表示（汉字很多超过5000个） Unicode：万国码（统一码），使用统一的编码方式来解决传统的局限，1994年出现 UTF-8：8-bit Unicode Transformation Format（万国码） ，针对Unicode的可变长度字符编码，采用1-6个字节编码Unicode字符（目前通用编码规则）。建议使用UTF-8字符集进行数据存储（MySQL8中建议使用 utf8mb4 ） 数据存储 graph LR A(输入符号<br>A)-->B[计算机存储] B-->C{字符集选择} C-->|ASCII|D[转码<br>01000001] C-->|GBK|E[转码<br>0000000001000001] C-->|ASCII 存储中文|G[转码<br>乱码] D-->F((存储)) E-->F G-->F 数据读取 graph LR A(数据读取)-->B{字符集选择} B-->|ASCII|C[读1个字节<br>01000001] B-->|GBK|D[读2个字节<br>0000000001000001] B-->|UTF-8|G[读3个字节<br>错误读取] G-->H[解析<br>乱码] C-->E[解析<br>A] D-->E E-->F((输出)) H-->F 2、MySQL字符集 MySQL字符集：MySQL内部对于数据实际存储的字符集（服务器端） MySQL内部对象可以在各个层级设置字符集 MySQL内部对象存在字符集继承：==字段== → ==表== → ==数据库== → ==DBMS== MySQL内部内嵌支持几乎所有主流字符集 数据存储的最终字符集由字段控制 客户端与服务器进行交互时，需要明确告知服务器客户端自己的字符集（数据格式） 查看MySQL支持的字符集： show charset; 数据库内部对象字符集原理 graph LR A(服务器<br>数据管理)-->B[DBMS系统<br>安装配置字符集] B-->C[数据库<br>设定字符集] C-->|继承<br>DBMS字符集|B C-->D[数据表<br>设定字符集] D-->|继承<br>数据库字符集|C D-->E[数据字段<br>设定字符集] E-->|继承<br>数据表字符集|D 1、服务端存储的数据最终字符集由字段确定 2、字段通常不会设置字符集，继承表的字符集（统一性，真正一般都与数据库一致） 3、数据存储的字符集与客户端的字符集没有直接关系，是由表（字段）决定 客户端存储数据原理 graph LR A(客户端录入数据<br>数据自带字符集)-->B[发送服务端] A-->|提前告知数据字符集<br>否则使用默认设置|C B-->C C[服务端<br>接收数据]-->D[执行<br>存储操作] D-->E[转码为<br>目标字符集] E-->F((存储)) 客户端读取数据原理 graph LR A(客户端录入<br>获取指令)-->B[发送服务端] A-->|提前告知数据字符集<br>否则器使用默认设置|C B-->C[服务器<br>接收请求] C-->D[执行获取指令<br>按照本身字符集获取] D-->E[转成客户端<br>字符集数据] E-->F[返回客户端] F-->G(客户端解析<br>只按照自己的<br>字符集解析) 总结 1、MySQL服务端数据存储的字符集依赖各个对象设置 DBMS：设置最广，一旦设置所有对象都可以依赖，但是优先级最低 DB：针对数据库内的所有表，优先级高于DBMS，可以继承DBMS（一般在数据库层设置） Table：针对当前表的设置，优先级高于DB，可以继承DB Field：针对当前字段设置，优先级高于Table，可以继承Table，优先级最高 2、通常字符集的设置都是围绕数据表（现在都在数据库层），不会到具体字段 3、建议使用UTF8字符集存储数据（MySQL8以后建议使用==UTF8MB4==） 4、MySQL服务端支持各种字符集，并且能够进行各种字符集转换 5、客户端存储数据到服务端原理 客户端告知服务端客户端的字符集 服务端按照客户端指定的字符集接收数据（如果没有指定，使用默认，可能出现乱码） 服务端按照实际存储表对应的字符集进行转换 服务端存储数据 6、客户端读取服务端数据原理 客户端告知服务端客户端的字符集 服务端按照客户指定的指令从数据库读取原始字符集数据 服务端按照客户端的需求将数据进行字符转换 服务端发送目标数据给客户端 客户端按照自己的字符集进行解析 3、乱码问题解决 乱码：指数据不能按照正确的字符集进行存储或者解析 乱码原因1：数据在存储的时候已经变成乱码 客户端字符集与服务端解析字符集不一致 读取时想转成其他字符集均会错误 乱码原因2：数据存储时正确，但是读取时解析成错误字符集 客户端能解析的字符集与服务器提供的字符集不一致 乱码解决方案：不论存储还是读取，都提前告知服务器当前客户端的字符集 set names 客户端字符集; graph LR A(客户端连接<br>认证服务端)-->B[设定字符集<br>告知服务端当前客户端字符集] B-->C[其他操作<br>内部字符集转换不用管] C-->D((结束)) 例如CMD打开MySQL客户端，客户端字符集是固定的GBK set names gbk; 总结 1、乱码的本质原因就是客户端与服务端的字符集不一致导致 客户端存储数据的时候服务端没有正确理解（服务端按照默认的存储，存储的就是乱码） 客户端读取的时候没有正确告知服务端（服务端按照默认的提供） 2、解决乱码问题的方案：保证服务端正确理解客户端的字符集 set names 客户端字符集 在任何数据操作之前（尤其是写数据，包括结构） 4、字符集设置原理 字符集设置原理：服务器端正确保障对客户端的数据识别 MySQL服务端提供了变量来记录客户端的字符集 MySQL对应的存储字符集的变量可以修改 set names 字符集 就是对变量的修改，总共有三个 character_set_client：服务端接收客户端数据 character_set_results：服务端提供数据给客户端 character_set_connection：连接使用的字符集，内部数据操作 通常我们都是使用set names字符集来进行统一设置，而且是在建立连接之后操作数据之前就设置 1、查看系统内部存储这些记录字符集的信息 show variables like 'character_set%'; 2、修改客户端字符集变量，保证数据正常存进服务端 set character_set_client = gbk; 3、修改客户端解析字符集变量，保证数据正常被客户端查看 set character_set_results = gbk; 4、使用set names 字符集批量修改，保证客户端被服务端正确理解，同时客户端也能正确解析 set names gbk; 二、校对集 1、校对集概念 校对集：collate/collation，即数据比较时对应的规则 校对集依赖于字符集 校对集的校对方式分为三种 大小写不敏感：_ci，case insensitive（不区分大小写） 大小写敏感：_cs，case sensitive（区分大小写） 二进制比较：_bin，binnary（区分大小写） 校对集是在进行数据比较的时候触发 2、校对集设置 校对集设置：在创建数据表的时候创建校对规则 校对规则可以在MySQL的四层对象中涉及 DBMS：系统配置 DB：数据库指定（库选项） Table：表指定（表选项） Field：字段指定（字段选项）（一般不用） 校对集从Field到DBMS依次继承，Field优先级最高 校对集依赖字符集，且每个字符集都有默认的校对集（一般情况不需要设置） 语法如下： collate 校对集规则; 查看MySQL支持的校对集： show collation; 在数据库层设计校对集（常用） create database db_1 charset uft8mb4 collate utf8mb4_bin; 在数据表层设计校对集 create table t1( id int, name varchar(10) )charset utf8mb4 collate utf8mb4_bin; 在字段层设计校对集（不常用） create table t1( id int, name varchar(10) collate utf8mb4_bin; )charset utf8mb4; 3、校对集应用 校对集的应用通常是通过数据比较触发：order by 字段，而不是主动触发 数据表中数据一旦产生，那么就不能修改对应的校对集规则 校对集通常使用字符集对应的默认校对集，如果需要进行额外的比较（通常是区分大小写），那么需要在建表的时候设定好目标校对集规则 示例如下： # 创建默认校对规则表（不区分大小写） create table t1( name varchar(5) )charset utf8mb4; insert into t1 values('A'); insert into t1 values('B'); insert into t1 values('a'); insert into t1 values('b'); # 创建二进制校对规则 create table t2( name varchar(5) )charset utf8mb4 collate utf8mb4_bin; insert into t2 values('A'); insert into t2 values('B'); insert into t2 values('a'); insert into t2 values('b'); # 触发校对：排序 order by select * from t1 order by name; # 升序 select * from t2 order by name; # 数据已经存在的表重新修改校对规则无效 alter table t2 collate utf8mb4_general_ci;

MySQL基本操作 目录： 一、MySQL语法规则 二、MySQL库操作 1、创建数据库 2、显示数据库 基本语法 显示部分 显示数据库的创建语句 3、使用数据库 4、修改数据库 5、删除数据库 三、SQL表（字段）操作 1、创建数据表 2、显示数据表 3、查看数据库 4、更改数据表 修改表名 修改表选项 5、更改字段 新增字段 字段位置 更改字段名 修改字段 6、删除字段 四、SQL数据操作 1、新增数据 2、查看数据 3、更新数据 4、删除数据 一、MySQL语法规则 SQL是一种结构化编程语言 基础SQL指令通常是以行为单位 SQL指令需要语句结束符，默认是英文分号：;、\g、\G \G：主要用于查询数据，立体展示结果 SQL指令类似自然语言 编写的SQL中如果用到了关键字或者保留字，需要使用反引号````来包裹，让系统忽略 二、MySQL库操作 1、创建数据库 create database 数据库名 [数据库选项]; 库选项：数据库的相关属性 字符集： charset/charaacter set 字符集 ，代表当前数据库下所有表存储的数据默认指定的字符集（默认为DBMS默认选项） 校对集： collate 校对集 ，字符集和校对规则是一对多的关系，每个字符集至少对应一个校对规则，MySQL 支持 39 种字符集的将近 200 种校对规则。 create database db_1 charset utf8Mb4 collate utf8mb4_general_ci; 数据库名字建议使用 _ 方法，而不是驼峰法命名 2、显示数据库 每当用户通过SQL指令创建一个数据库，那么系统就会产生一个对应的数据库文件夹 Linux下默认路径为： /var/lib/mysql （数据库配置文件： usr/share/mysql ） MySQL8以前每个数据库文件夹下都有一个opt文件，保存的都是数据库选项 基本语法 show databases; information_schema —— 保存数据库所有的结构信息（表、库） mysql —— 核心数据库：负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息 performance_schema —— 效率库：用于收集存放数据库的性能参数 sys —— 帮助DBA和开发人员解释性能模式收集的数据的对象 显示部分 show databases like '匹配模式'; 匹配模式： _ ：匹配当前位置单个字符 % ：匹配指定位置多个字符 -- 获取以my开头的全部数据库 show databases like 'my%'; -- 获取m开头，中间为任意字符，最后为database的数据库 show databases like 'm%database'; 显示数据库的创建语句 show create database 数据库名称; 注：看到的指令并不是原始指令 3、使用数据库 use 数据库名; 4、修改数据库 指修改数据库的相关库选项 不能修改数据库名称（MySQL 5.5以后不能用 rename 指令） 如果想要修改数据库名字：新增 -> 迁移 -> 删除 数据库修改有两个部分：字符集、校对集 alter database [数据库名] [库选项]; 数据库名称可以忽略，此时语句对应于默认数据库 -- 修改数据库字符集 alter database db_1 charset gbk; -- 修改数据库校对集（必须同时改变字符集） alter database db_1 charset gbk collate gbk_chinese_ci; 5、删除数据库 drop database 数据库名字; 三、SQL表（字段）操作 1、创建数据表 create table [数据库名.]表名 ( 字段名 字段类型, ... 字段名 字段类型 ) 表与字段不分家，相辅相成 表的创建需要指定存储的数据库 明确指定数据库：数据库.表名 先使用数据库：use 数据库名字 字段至少需要指定名字、类型 数据库表不限定字段数量 每个字段间使用逗号,分隔 最后一个字段不需要逗号 表可以指定表选项（都有默认值） 存储引擎：engine [=] 具体存储引擎 字符集：[default] charset 具体字符集（继承数据库） 校对集：collate（继承数据库） create table db_1.t1( name varchar(50), age int, gender varchar(10) ); create table t2( name varchar(50) )engine Innodb charset utf8MB4; 存储引擎是指数据存储和管理的方式，MySQL中提供了多种存储引擎，一般使用默认存储引擎 InnoDB 默认存储引擎 支持事务处理和外键 数据统一管理 MyIsam 不支持事务和外键 数据、表结构、索引独立管理 MySQL 5.6 以后不再维护 如果想创建一个与已有表一样的数据表，MySQL提供了一种便捷的复制模式 create table 表名 like 数据库名字.表名 2、显示数据表 在当前数据库下显示所有数据表 show tables; 查看指定数据库下所有数据表 show tables from db_1; 显示部分关联数据表——匹配 show tables like '%like'; 显示具体数据表的创建指令 show create table t1; 显示数据表通常是为了验证数据表是否存在或者验证数据表的创建指令是否正确 在显示数据的时候可以使用不同的语句结束符 \g：与普通分号无区别 \G：纵向显示列数据 3、查看数据库 -- 以下三种一样效果 desc 表名; describe 表名; show columns from 表名; 4、更改数据表 修改表名 rename table 旧表名 to 新表名; 如果有时候想要跨库修改的话，需要使用数据库名.表名 修改表选项 alter table 表名 [库选项]; 5、更改字段 字段操作包含字段名字、类型和属性的操作 字段操作还有位置处理 字段操作通常是在表已经存在数据后进行 新增字段 在表创建好后往里面增加其他字段 字段的新增必须同时存在字段类型 alter table 表名 add [column] 字段名 字段类型 [字段属性] [字段位置] 给已经存在的t1表增加一个字段 age alter table t1 add age int; 给已经存在的t1表增加一个字段 nickname alter table t1 add column nickname varchar(10); 字段的追加默认是在所有字段之后 字段位置 指字段放到某个指定字段之后 字段位置分为两种 第一个字段：first 某个字段后：after 已经存在字段名 字段位置适用于追加字段、修改字段、更改字段名 alter table 表名 字段操作 字段位置; 为t1表增加一个 id 字段，放到最前面 alter table t1 add id int first; 在t1表 name 字段后增加一个身份证字段 card alter table t1 add card varchar(18) after name; 更改字段名 alter table 表名 change 原字段名 新字段名 字段类型 [字段属性] [位置]; 修改字段名 card 为 sfz alter table t1 change card sfz varchar(18); 字段名更改通常只是修改字段名字，但是也必须跟随类型 字段名修改 change 其实也可以修改 字段类型、属性 和 位置，但是通常不使用（专人专事） 修改字段 alter table 表名 modify 字段名 字段类型 [字段属性] [位置]; 修改身份证的类型为 char(18) 并且位置放到 id 后面 alter table t1 modify sfz char(18) after id; 6、删除字段 alter table 表名 drop 字段名; 删除字段会将数据也删除（不可逆） 四、SQL数据操作 1、新增数据 新增数据是根据表的字段顺序和数据类型要求将数据存放到数据表中 数据表中的数据以行（row）为存储单位，实际存储属于字段（field）存储数据 insert into 表名 [(字段列表)] values; 例如： # 插入一段完整数据 insert into t3 values(1,'440111200011111101','Jim','Green'); # 插入部分数据 insert into t1 (id,sfz,name) values(2,'441000200011111211','Tom'); 数据插入分为两种方式插入数据 全字段插入 insert into 表名 values(值列表) 值列表必须与字段列表顺序一致 值列表的每个数据类型必须与字段类型一致 部分字段插入 insert into 表名 (字段列表) values(值列表) 字段列表可以顺序随意 值列表必须与指定的字段列表顺序一致 值列表元素的类型必须与字段列表的类型一致 2、查看数据 查到的数据显示出来是一张二维表 数据显示包含字段名和数据本身 数据查看分两种方式 查看全部字段：使用 * 代替所有字段 查看部分字段：明确字段名，使用逗号分隔 select *|字段列表 from 表名 [where ...]; 例如： # 查看t1表中的所有数据 select * from t1; # 查看t1表中的name和身份证信息; select name, sfz from t1; # 查看t1表中id值为2的信息; select * from t1 where id = 2; 3、更新数据 update 表名 set 字段 = 新值[, 字段 = 新值] [where 条件筛选]; 1、更新数据的针对记录的字段数据进行更新 2、更新通常是限定条件更新（一般不会更新全部） 例如 update t1 set name = 'Cat', sfz = '410101199804011523' where id = 1; 4、删除数据 删除数据：将数据从已有数据表中清除（针对的是记录record） 删除数据是一种不可逆操作 数据删除通常都是有条件删除 delete from 表名 [where 条件]; 示例： # 删除t1表中id为2的数据 delete from t1 where id = 2;