InnoDB-DDL 建库

 -- 查看数据库的版本
 select version();
 ​
 -- 查看所有的库
 show databases;
 ​
 -- 删库：主要用于删除结构，隐士提交，不能回滚；delete:主要用于删除数据
 drop database if exists hi_java;
 ​
 -- 建库：collate-数据库的校验规则; utf8_general_ci-大小写不敏感（case insensitive）
 create database if not exists hi_java character set utf8mb4 collate utf8mb4_general_ci;
 ​
 -- 选择数据库
 use hi_java;
 ​
 -- 查看数据库字符集：default-utf8mb4（most bytes 4:一个字符占4个字节）
 show variables like '%character%';

InnoDB-DDL 建表

属性查询

 -- 查看库中所有的表
 show tables;
 ​
 -- 查存储引擎
 show engines;
 ​
 -- 查数据存放位置
 show variables like '%datadir%';
 ​
 -- 查看表详细信息状态
 show table status like 'tb_relation_student';

表备份

 -- 1.只复制表结构
 create table tableName like someTable;
 ​
 -- 2.只复制表数据
 create table tableName select * from someTable;
 ​
 -- 3.复制表结构+数据
 create table tableName like someTable;
 insert into tableName select * from someTable;

建表规范

• 表名

 tb_业务前缀_表作用 [tb_base_user]
 表名不使用复数名词

• 表容量

 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表
 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表

• 字符集

 latin1: 容易出现乱码问题，在实际项目中使用比较少
 GBK: 支持中文，但不支持国际通用字符，在实际项目中使用也不多
 utf-8: 占用3个字节，无法存储emoji表情，因为emoji表情一般需要4个字节
 utf8mb4: 建议设置

• COLLATE排序规则

 排序规则和字符集有关
 字符集utf8mb4对应的排序规则:utf8mb4_general_ci-大小写不敏感；utf8mb4_bin-大小写敏感

• 字段个数

 个数 < 20
 ​
 字段允许冗余，冗余字段遵循
 1 不是频繁修改的字段
 2 不是唯一索引的字段
 3 不是varchar超长字段，更不能是text字段

• 字段名称

 status: 标识状态
 delete_status: 软删除标志位
 关联字段：关联表名_字段名称 [base_user_id]
 必备三字段：id，create_time，update_time

• 字段类型

 id: bigint unsigned
 时间: datetime
 是否：tinyint unsigned [1-是，0-否]
 枚举：tinyint
 金额：decimal(m,n) [n是指小数的长度，m是指整数加小数的总长度]
 人年龄：tinyint unsigned

• not null

 能定义成not null就定义成not null，并设置默认值
 1 innodb中需要额外空间存储null
 2 null只能通过is null / is not null判断，用=号永远返回false

• 唯一索引

 可以给单个字段加，也可以创建联合的唯一索引
 唯一索引失效: 如果是联合的唯一索引，字段值出现null时，则唯一性约束可能会失效
 创建唯一索引时，相关字段一定不能包含null值，否则唯一性会失效

InnoDB-DQL Query

查询SQL模板

 select * （存在聚合函数）
 from t1
 [left | right| inner join t2 on]
 [where ...](不包含聚合函数的过滤条件)
 [group by ...]
 [having ...]（包含聚合函数的过滤条件）
 [order by ...]
 [limint ...]

执行流程

 from t1
 [left | right| inner join t2 on]
 [where ...](不包含聚合函数的过滤条件)
 [group by ...]
 [having ...]（包含聚合函数的过滤条件）
 select * （存在聚合函数）
 [order by ...]
 [limint ...]
 ​
 from > on > left > where > group by > having > select > distinct > order by > limit

SQL技巧

 -- 使用group_concat函数，可以把分组后name相同的数据拼接到一起，组成一个字符串，用逗号分隔
 select name,group_concat(code) from `user` group by name;
 ​
 -- 根据字符的长度进行排序
 select * from brand where name like '%苏三%' order by char_length(name) asc limit 5;
 ​
 -- 先按长度排序，小的排在前面。如果长度相同，则按关键字从左到右进行排序，越靠左的越排在前面
 select * from brand where name like '%苏三%' 
 order by char_length(name) asc, locate('苏三',name) asc limit 5,5;
 ​
 -- 当前时间使用now()函数，
 select now(3) from brand limit 1;

InnoDB-DML Insert

SQL技巧

 -- 如果存在则不插入数据。如果不存在才需要插入数据
 INSERT ignore INTO `brand`(`id`, `code`, `name`) VALUES (123, '108', '苏三');
 ​
 -- 如果不存在则插入数据。如果已存在则不插入数据，而直接返回结果
 INSERT  INTO `brand`(`id`, `code`, `name`) VALUES (123, '108', '苏三')
 on duplicate key update name='苏三',edit_date=now(3);

InnoDB-DML Update

SQL技巧

 -- 字符替换，也能用该函数去掉前后空格
 update brand set name=REPLACE(name,' ','') where name like ' %';

InnoDB-DML Delete

 -- drop：主要用于删除结构，隐士提交，不能回滚
 -- delete：主要用于删除数据
 ​
 -- delete vs truncate：
 相同点：都能删除数据，都不会删除表结构和索引
 delete：
 1.数据删除后是可以实现回滚的；
 2.未重启数据库，计数器不归零；
 3.重启后：InnoDB会从1开始（存内存中，断电即失）；MyISAM继续从上一增量开始（存文件中，不会丢失）
 truncate：
 1.数据删除后是不可以回滚的；
 2.使用的系统和事务的日志资源少，速度快，无事务且不触发trigger，不会进行数据回滚到备份；
 3.重新设置自增列，计数器归零；

InnoDB 视图

• 定义：存储起来的select语句。
• 创建视图

 create view vu_company_department
 as
 select department_name,department_place from tb_company_department;

• 查看视图

 -- 查看数据库的表、视图对象
 show tables ;
 -- 查看视图结构
 desc vu_company_department;
 -- 查看视图的属性信息
 show table status like 'vu_company_department';
 -- 查看视图的详细定义信息
 show create view vu_company_department;

• 更新视图数据

 不可更新视图情况：
 1 基表中进行聚合计算成为视图的列，不能修改
 2 视图表新增数据时，基础表部分字段没有默认值，不能新增
 3 虽然可以更新视图数据，但总的来说视图作为虚拟表，主要用于方便查询，不建议更新视图的数据
 4 对视图数据的更改，都是通过对实际数据表里数据的操作来完成的
 update vu_company_department set department_place = '北京' where department_name = '事业部';

• 修改视图字段

 -- 方式一
 create or replace view vu_company_department
 as
 select department_name,department_number,department_place from tb_company_department;
 ​
 -- 方式二
 alter view vu_company_department
 as
 select department_name,department_place from tb_company_department;

• 删除视图

 drop view if exists vu_company_department;

存储过程

 -- 创建函数时若报错，需设置命令允许创建函数设置(不加global只对当前窗口有效)
 set global log_bin_trust_function_creators = 1;
 ​
 -- 创建随机函数
 delimiter //
 create function rand_num(from_num int,to_num int) returns int(11)
 begin
     declare i int default 0;
     set i = floor(from_num + rand()*(to_num - from_num));
     return i;
 end //
 delimiter ;
 ​
 -- 创建随机字符串函数
 delimiter //
 create function rand_string(n int) returns varchar(255)
 begin
     declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
     DECLARE return_str varchar(255) DEFAULT '';
     DECLARE i INT DEFAULT 0;
     WHILE i < n DO
         SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*52 ),1));
         SET i = i +1;
     END WHILE;
     RETURN return_str;
 end //
 delimiter ;