MySQL（一）

sql思维导图

一、分类

sql：结构化查询语言(Structured Query Language)，执行解析在DBMS

1. DDL 数据定义语言(Data Definition Language)

create、alter、drop(删除表，不支持事务)、truncate(删除一个表并创建一个新表，不支持事务操作，自增不清零)

2. DML 数据操作语言(Data Manipulation Language)

insert、update、delete(删除数据，自增不清零，支持事务)、select(DQL)

3. DQL 数据查询语言(Data Query Language)

select 与事务无关

4. TCL 事务控制语言(Transaction Control Language)

commit、rollback、savepoint

5. DCL 数据控制语言(Data Control Language)

分配权限

二、数据库相关sql

1. 登录 mysql -uroot -p
   登录 “C:\Program Files\MySQL\MySQL Server5.7\bin\mysql.exe” -u root -p12345
   修改密码 set password for root@localhost = password(‘admin’);
2. show databases; 查看所有数据库
3. create database 数据库名; 创建数据库
4. use 数据库名; 切换数据库
5. show create database 数据库名;
6. show char set; 查看数据库支持的字符集
7. alter database 数据库名；
8. character set ‘gbk’; 修改字符集
9. show variables like ‘character_set_database’; 查看当前数据库的字符集
10. drop database 数据库名; 删除数据库

三、表相关sql

1. 表: 数据库中存储数据的单元，创建表时一定要指定字段信息(字段名、字符类型)
2. 表引擎
   InnoDB 支持高级操作，创建时默认InnoDB
   myisam 只支持增删改查，速度快
3. create table 表名(字段1 字段1的值类型,字段2字段2的值类型); 创建表
4. show tables; 查看当前数据库中的表
5. show create table 表名; 查看表的属性
6. desc 表名; 查看表结构
7. create table 表名(…) engine=myisam charset=utf8; 创建表时指定表的引擎和字符集
8. drop table 表名; 删除表
9. 修改表名
   alter table 原表名 rename 新表名;
   rename table 原表名 to 新表名;
10. 插入字段
    alter table 表名 add 字段 字段类型 位置; 插入字段
    alter table 表名 add 字段 字段类型; 在最后插入
    alter table 表名 add 字段 字段类型 first; 在开头插入
11. alter table 表名 add 字段 字段类型 after 字段; 在指定字段后插入
12. alter table 表名 drop 字段; 删除字段(多表时会影响)
13. alter table 表名 change 原字段 新字段 新字段值类型; 修改表字段和字段值类型
14. alter table 表名 modify 字段 字段类型 first/after 指定字段; 修改表字段的位置和类型

四、数据相关sql

1. insert into 表名 values(字段值1,字段值2,字段值3); 全字段插入(批量插入用逗号隔开)
2. insert into 表名 (字段1,字段2) values (字段值1,字段值2); 指定字段插入(批量插入用逗号隔开)
3. select 字段 from 表名; 字段查询
4. select 字段 from 表名 where 条件; 条件查询
5. update 表名 set 字段=字段值; 修改数据(为保证数据安全性，添加条件进行修改)
6. delete from 表名 条件; 删除数据
7. primary key 主键约束(数据唯一且不能为空的字段)
8. primary key auto_increment 主键+自增
   主键值只增不改(指定除外)，主键值有序
   清空表数据后主键值没有清空(没有归零)，持续增长
9. 注释: comment ‘内容’ 在数据类型后面，方便后期二次开发
10. 冗余: 设计表格不合理，大量数据出现重复
11. 乱码问题
    sql在客户端写完，通过网络传出到数据库服务器，这时需要字符集编码，数据库在保存数据库时也需要字符集编码。
    在操作字符之前查看当前的数据库编码集，设置字符集（在Windows终端中默认都是gbk，就需要在终端中设置编码集，告诉数据库以什么形式进行节码，set names gbk）

五、事务

有的时候完成一个业务需要执行两个或多个sql，这时不使用事务可能会造成数据异常，因为我们都自动提交事务，在处理业务时整套sql执行完毕再执行事务会保证数据的完整性。
数据库中执行sql语句的最小工作单元，不能拆分，执行同一业务的的sql语句可以保证全部成功或全部失败。

1. 事务的ACID特性
   Atomicity 原子性: 最小不可拆分，全部成功或全部失败
   Consistency 一致性: 从一个一致状态到另一个一致状态
   Isolation 隔离性: 多个事务之间互不影响
   Durability 持久性: 事务完成后数据提交到数据库中持久保存
2. 事务使用步骤
   step1：关闭事务set autocommit=0;
   step2：执行多次sql（只是在内存中执行，并没有在磁盘中执行）
   step3：手动提交commit
3. 事务相关命令
   查看事务提交状态 show variables like ‘%autocommit%’;
   设置自动提交状态 set autocommit=0/1; 0 关闭 1 打开
   提交 commit;
   回滚 rollback; （将数据回滚到上次事务提交的点）
   保存回滚点 savepoint s1;
   回滚到某个回滚点 rollback to s1;

六、数据库的数据类型

1. 整数 int(m) bigint(m)
   m代表显示长度，如果数据长度不足m，会在数值前补0，但是必须要和zerofill关键字配合使用
   create table t(age int(3) zerofill);
2. 浮点数 double(m,d)
   m代表长度 d代表小数点后的位数
   create table t(salary double(7,2));
   超过小数的位进行四舍五入
   小数点位不足时进行补0
   decimal(m,d) 超高精度
3. 字符串
   char(m) 固定长度(最大255)
   varchar(m) 可变长度，以实际字节量存储(最大65535)
   text() 可变长度
4. 日期
   date 年月日
   time 时分秒
   datetime 默认是null，最大时间9999年12月31日
   timestamp 默认是当前时间(年月日时分秒)，最大时间2038年1月19日

七、查询相关

1. null 空
2. as 别名
3. distinct 去重 (取值字段中重复的数据)
4. !=或<> 不等于
5. and和&&
6. or和||
7. in 包含
8. between 区间的数据查询
9. like 模糊查询
10. 排序 order by
    在where后面写；
    asc 升序 desc 降序；
    默认升序；
11. limit 分页
    limit 参数1,参数2
    参数1：从第几条记录开始
    参数2：读取几条记录
    计算公式：limit (页数-1)*每页数据条数, 读取第几页的几条数据
12. +- */% mod(7,3) 数值计算
13. group by 分组查询
14. having 解决聚合函数的条件过滤问题
    select category_id,sum(num) s from t_item group by category_id having s>1000; 查询每个分类商品的高于1000的库存总量
15. where
    后面写普通字段的过滤条件(不能写聚合函数)

顺序：select * from 表名、where、group by、order by、having、limit

八、日期函数

1. 获取当前日期和时间 now()
2. 获取当前日期 curdate()
3. 获取当前时间 curtime()
4. 获取now中年月日、时分秒 date(now())、time(now())
5. 获取时间结点 extract(year from now()) (year/month/day/hour/minute/second)
6. 日期格式的函数 date_format(date,format)
   select date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’);
   %Y 四位年 %y两位年
   %m 两位月 %c 一位月
   %d 日
   %H 24小时 %h 12小时
   %i 分
   %s 秒
7. 将非标准格式转换 str_to_date(非标准格式字符串,format)
   select str_to_date(‘2019.11.24 14:17:08’,’%Y.%m.%d %H:%i:%s’);

九、聚合函数

1. 求和 sum(字段)
2. 平均值 avg(字段)
3. 最大值 max(字段)
4. 最小值min(字段)
5. 统计数量 count(字段/*)

十、字符串函数

1. 拼接 concat(str1,str2)
2. 获取长度 char_length(str)
3. 获取substr在str中出现的位置(下标从1开始) instr、locate
   instr(str,substr)
   locate(substr,str)
4. 插入 insert(str,start,length,newstr) 将newstr插入到str从第start开始的length长度
   str字符串
   start插入的位置
   length去掉几个字符
   newstr要插入的字符串
5. 替换 replace(str,oldstr,newstr) 将str中的oldstr替换为newstr
6. 大小写转换 upper(str) lower(str)
7. 左右截取字符串 left(str,len) right(str,len) 从左/右截取len个字符
8. 截取字符串 substring(str,start) substring(str,start,len)
9. 去除最左右两边空白 trim(str)
10. 重复 repeat(str,n) 将str重复输出n次
11. 反转 reverse(str)

十一、数学相关函数

1. floor(num) 向下取整
   floor(3.84) // 3
2. round(num) round(num,m) 四舍五入 m代表小数数位
   round(23.8) // 23 round(23.869,2) // 23.87
3. truncate(num,m) 非四舍五入
   truncate(23.869,2) // 23.86
4. rand() 获取0-1的随机数
   floor(rand()*6) 获取0-5的随机数
   floor(rand()*6)+3 获取3-8的随机数
   floor(rand()*3)+8 获取8-10的随机数

十二、分组查询

1. group by 通常和聚合函数结合使用
2. 顺序: select * from emp 、where、group by、order by、having、limit

十三、having

1. 使用having 解决聚合函数的条件过滤问题，写在group by后面
2. where后面写普通字段的过滤条件(不能写聚合函数)，having后面写聚合函数的过滤条件

十四、子查询

1. 嵌套在SQL语句中的查询语句称为子查询
2. 子查询可写的位置
   写在where/having后面作为查询条件的值；
   写在from后面作为一张新表(新表必须起别名)；
   select *from (select *from emp where deptno=10) t_emp_10;
   写在创建表的时候；
   create table t_emp_10 as select *from emp where deptno=10;

十五、关联查询

1. 同时查询多张表的数据称为关联查询
2. 等值连接 select * from A,B where A.x=B.x and 其他条件;
3. 内连接 select * from A join B on A.x=B.x where 其他条件; 查询的是两张表的交集部分
4. 左外连接 select * from A left join B on A.x=B.x where 其他条件;
   以join左边表为主表，左边表显示全部数据，右边表显示交集数据
5. 右外连接 select * from A right join B on A.x=B.x where 其他条件;
   以join右边表为主表，右边表显示全部数据，左边表显示交集数据

十六、表设计- 关联关系

1. 一对一
   两张表A和B表，A表中一条数据对应B表中的一条数据；
   应用场景: 用户表和用户信息扩展表、商品表和商品信息扩展表；
   建立关系: 在从表中添加一个外键字段值指向主表的主键字段。
2. 一对多
   A表中一条数据对应B表中多条数据，B表中一条数据对应A表中一条数据
   应用场景: 部门表和员工表、分类表和商品表
   建立关系: 在多的表里面添加外键记录另外一个表的主键值
3. 多对多
   A表中一条数据对应B表中多条数据，B表中一条数据对应A表中多条数据
   应用场景: 老师和学生、用户表和角色表
   建立关系: 需要创建单独的关系表，关系表中的两个外键字段保存两个表的主键值
4. 自关联
   在表中有一个外键字段指向自己表的主键值
   应用场景: 需要保存层级关系，但是不确定有多少层的时候使用自关联
5. 连接方式和关联关系
   连接方式: 包括内连接和外连接，是关联查询的查询方式。
   关联关系: 包括一对一、一对多、多对多，是指设计表时两个表之间存在的逻辑关系。

十七、视图

1. 什么是视图？
   数据库中表和视图都是其内部的对象，视图可以理解成一个虚拟的表，本质是取代了一段sql查询语句。
2. 为什么使用视图？
   因为有些数据的查询需要使用大量的sql语句，每次书写比较麻烦，使用视图可以起到sql重用的作用，可以隐藏表中的敏感信息。
3. 如何使用？
   格式: create view 视图名 as 子查询
4. 视图分类
   4.1 简单视图
   创建视图的子查询中不包含 去重、函数、分组、关联查询的视图称为简单视图.
   可以进行增删改查操作。
   4.4 复杂视图
   创建视图的子查询中包含 去重、函数、分组、关联查询的视图称为复杂视图。
   只能进行查操作。
5. 视图的增删改查操作

• 插入数据
  如果插入的数据在视图中不显示但是在原表中显示，称为数据污染；
  可以通过with check option 关键字禁止出现数据污染。

数据污染 insert into v_emp_10 (empno,ename) values(10010,‘Tom’);
正确写法 insert into v_emp_10 (empno,ename,deptno) values(10086,‘Jerry’,10);​

create view v_emp_30 as (select *from emp where deptno=30) with check option;

• 修改数据
  只能修改视图中存在的数据。

成功 update v_emp_30 set ename=‘zhangsan’ where empno=10011; 失败 update
v_emp_30 set ename=‘汤姆’ where empno=10010;

• 删除数据
  只能删除视图中存在的数据。

成功 delete from v_emp_30 where empno=10011; 失败 delete from v_emp_30
where empno=10010;

• 修改视图
  格式: create or replace view 视图名 as 子查询;

create or replace view v_emp_10 as (select *from emp);

• 删除视图
  格式:
  drop view v_emp_10;
  drop view if exists v_emp_10;(有就删没有也不会报错)

• 视图别名
  创建视图的时候子查询对字段起了别名，则后期对视图进行操作只能使用别名。

十八、约束

1. 非空约束 not null 字段值不能为空
2. 唯一约束 unique 字段值不能重复
3. 主键约束 primary key 字段值不能重复也不能为null
4. 自增 auto_increment

当字段值为null时，数值自动+1 删除数据数值不减 如果插入数据指定比较大时，下次从最大值基础上+1，删除同上
如果delete删除全表数据，自增值不变 使用truncate删除，自增数值清零

5. 默认约束 default
   当字段的值不赋值时，默认约束的内容生效
6. 检查约束 check
   MySQL语法支持不报错，但是没有效果。
   create table t_check(id int,age int check(age>10));
7. 外键约束

• 作用

外键值可以为null，可以重复，但是不能不存在。
外键指向的表，不能删除 如员工表外键部门号指向的部门表不能先删除。
外键指向的数据不能先删除，如果需要删除先删除外键约束或外键数据(断开关联关系)。

• 使用外键必须要求两张表相同的InnoDB引擎，myisam不支持外键约束。
• 除非特定情况，一般工作中不使用外键约束，使用Java代码通过逻辑对插入和删除的数据进行限制，因为加了外键约束后不方便测试。

十九、索引

1. 什么是索引？
   索引是数据库中用来提高查询效率的技术，类似于目录。
2. 为什么使用？
   如果不使用索引数据会零散的保存在磁盘块中，查询数据需要挨个的遍历每一个磁盘块，直到找到数据为止，使用索引后会在磁盘中以树状结构对数据进行保存，查询数据时会大大降低磁盘块的访问量，从而提高查询效率。
3. 索引是越多越好吗？
   索引会占用磁盘空间，只对常用的查询字段创建索引。
4. 有索引就一定好吗？
   不一定，如果数据量比较小，使用索引反而会降低查询效率。
   尽量不要在频繁修改的表上创建索引。
5. 索引分类
   聚集索引:
   一张表只有一个聚集索引，数据库会自动为添加了主键的表创建聚集索引，一般情况下聚集索引就是通过主键值来创建的索引，聚集索引的树状结构中保存了数据。
   非聚集索引:
   一张表可以有多个非聚集索引，通过非主键字段创建的索引成为非聚集索引，非聚集索引中没有数据，保存的是磁盘块的地址。
6. 创建索引 create index 索引名 on 表名(字段名(长度))

create index index_item2_title on item2(title);

7. 查看索引 show index from item2;
8. 删除索引 drop index index_item2_title on item2;
9. 复合索引
   通过多个字段创建的索引称为复合索引。
   应用场景: 频繁使用多个字段作为查询条件时，可以为这几个字段创建一个复合索引。
   创建格式: create index index_item2_title_price on item2(title,price);