MySQL基础内容的总结

1.数据库类型

关系型数据库 —— mysql oracle sqlite
非关系型数据库 —— mongodb redis

2.存储引擎

InnoDB
- 数据存储持久化
- 数据和索引存储在一起
- 支持事务行级锁表级锁外键
  事务：保证数据的完整性, 原子性操作
  行级锁：修改少量行的时候, 针对修改频繁的表
  表级锁：批量修改行的时候, 针对要大量修改数据的表
  外键：约束两张表中的关联字段不能随意的添加删除，降低数据删改的出错率
- mysql 5.6 之后的默认存储引擎
Mysiam
- 数据存储持久化
- 数据和索引不存储在一起
- 只支持表级锁
- mysql 5.5 之前的默认存储引擎
Memory
- 存储在内存中,断电消失
查看存储引擎的操作
查看所有存储引擎：show engines;
查看当前默认存储引擎：show variables like 'default_storage_engine';

3.MySQL数据类型

数据类型主要有四种：数字、时间、字符串和单多选行为，这里只总结了比较常用的部分^ ^

数字
- 整数 int
- 浮点数 float(a,b) 一共a位,小数点后占b位
时间
- 年月日 date
- 时分秒 time
- 年月日时分秒 datetime
字符串
- char
  - 定长存储, 节省时间
  - 最多存储255个字符
  - 适合手机号,身份证件号,银行卡号,用户名密码......
- varchar
  - 变长存储, 节省空间
  - 最多存储65535个字符
  - 适合评论......
- enum / set
  - enum 单选行为
  - set 多选行为（会自动去重）

4.操作

1.启动/关闭服务

cmd管理员模式

启动 —— net start mysql
关闭 —— net stop mysql
启动客户端
- 连自己的 —— mysql -uroot -p
- 连别人的 —— mysql -uroot -p 密码 -h ip
  - eg. mysql -uroot -p123 -h192.168.14.12

2.和用户相关的操作

查看当前用户：select user();
创建一个用户：create user 用户名@ip identified by 密码
- eg. create user 'guest'@'192.168.0.%' identified by '123456';
给当前用户设置密码：set password = password('...');
给用户授权
- 权限类型: all / select / select, insert（所有权限/查看/查和增）
- grand 权限类型 on 数据库名.* to 用户名@ip;

3.库相关的操作

查看所有数据库：show databases;
创建库：create database 库名;
切换到某个库：use 库名;
查看当前库下的表：show tables;
删除库：drop database 库名;（删库需谨慎！！！）

4.表相关的操作

创建表：

在建表之前前还有一件重要的东西要提，那就是完整性约束，分为以下七种
- 无符号：unsigned（只适用数字类型)
- 唯一约束：unique
  - 联合唯一：unique(字段1,字段2)
- 非空约束：not null
- 主键约束：primary key
  - 联合主键：primary key(字段1,字段2)
  - 主键自带 unique + not null
  - 一张表只能有一个主键
- 自增约束：auto_increment
  - 至少是unique约束才能用
  - 常见用于id字段
- 外键约束：foreign key(字段) references 外表(字段)
  - 外表字段至少是unique约束
- 默认值约束：default 'xxx'

建表：

create table 表名(
id int,
name char(18),
字段名3 类型[(宽度) 约束条件]
);
# 放在中括号里的内容可以不写

查看表结构
- desc 表名;（结构清晰，但信息没有那么全）
- show create table 表名;（信息更全，但显示没那么清晰）
修改表结构
- 改表名：alter table 旧表名 rename 新表名;
- 增加字段：alter table 表名 add 字段名数据类型约束 (first / after 某字段);
- 修改字段：
  - alter table 表名 modify 字段名数据类型约束;
  - alter table 表名 change 旧字段名新字段名数据类型约束
- 删除字段
  alter table 表名 drop 字段名;
查整张表的数据：select * from 表名;

5.数据相关的操作

1.增

insert into 表名 values();
insert into 表名(字段1，字段2) values();

2.删

删除表中的某行数据：delete from 表名 where 条件;
清空表中数据
- delete from 表名（保留自增偏移量）
- truncate 表名;（清空自增偏移量）

3.改

update 表名 set 字段=新值 where 条件;

4.查（重点）

单表查询

基础查询：
- 查看表中全部数据：select * from 表名;
- 查看表中某个字段数据：select 字段 from 表名;
重命名字段：
- select 字段新字段名 from 表名;（仅显示数据的时候改变，并不会真的修改表结构）
去重：
- select distinct 字段名 from 表名;
拼接：
- concat()：select concat(emp_name,':',salary) from employee;
- concat_ws(), 以第一个参数为分隔符：concat_ws('|',cid,cname);
where条件语句：
- 比较运算符：> < = <= >= !=
- 范围：
  - between...and...(顾头顾尾)
  - in(a,b,c)
- 模糊查询like：
  - % 任意长度任意内容
  - _ 一个字符长度任意内容
- 逻辑运算：not > and > or

分组和聚合函数

分组：group by

聚合

求个数 count()
求最大最小值 max(), min()
求和 sum()
求平均值 avg()

# 在成绩表中，按学生学号分组，并他们的平均分
mysql> select student_id,avg(num)  from score group by student_id;
+------------+----------+
| student_id | avg(num) |
+------------+----------+
|          1 |  10.0000 |
|          2 |  38.0000 |
|          3 |  82.0000 |
|          4 |  73.0000 |
|          5 |  73.0000 |
|          6 |  38.0000 |
|          7 |  38.0000 |
|          8 |  38.0000 |
|          9 |  79.0000 |
|         10 |  66.5000 |
|         11 |  66.5000 |
|         12 |  66.5000 |
|         13 |  87.0000 |
+------------+----------+

过滤语句 having：

筛选符合条件的某一组数据,而不是某一行数据

# 在成绩表中，按学生学号分组，并找出平均分大于60的数据
mysql> select student_id,avg(num)  from score group by student_id having avg(num)>60;
+------------+----------+
| student_id | avg(num) |
+------------+----------+
|          3 |  82.0000 |
|          4 |  73.0000 |
|          5 |  73.0000 |
|          9 |  79.0000 |
|         10 |  66.5000 |
|         11 |  66.5000 |
|         12 |  66.5000 |
|         13 |  87.0000 |
+------------+----------+

查询排列：order by
- 默认升序asc, 可以修改desc为降序
数量限制：limit m,n
- 从m+1起取n个

多表联查
- 连表查询
  - 1.select * from 表1,表2 where 条件(了解)
  - 2.内连接：select * from 表1 inner join 表2 on 连接条件;
    - 保留双方都有的内容
  - 3.外连接
    - 左外连接：select * from 表1 left join 表2 on 连接条件;
      - 左表信息全部保留
    - 右外连接：select * from 表1 right join 表2 on 连接条件;
    - 全外连接
      - mysql不直接支持
      - 左外连接 union 右外连接
- 子查询 -- 嵌套
```
# 查看开发部员工姓名
# 先查询技术部的部门id
    select id from department where name = '开发';
#  再根据这个部门id找到对应的员工名
    select name from employee where dep_id =(select id from department where name = '技术');
```

5.索引与存储数据的方式

索引：
- 创建：create index 索引名 on 表名(字段名);
  - 联合索引：create index 索引名 on 表名(字段1, 字段2);
- 删除：drop index 索引名 on 表名;
- 作用：加快查找速度
存储数据方式：
- b+树
- 聚集索引 / 聚簇索引
  - 叶子节点存储具体数据
  - Innodb中的主键
- 非集聚索引 / 非聚簇索引 / 辅助索引
  - 叶子节点不存具体的数据, 存储主键和索引列
  - Innodb Myisam

6.正确使用MySQL

操作库的角度
- 搭建集群
- 读写分离
- 分库
操作表的角度
- 合理安排表与表之间的关系
- 固定长度的字段放前面
- 尽量用char代替varchar
操作数据的角度
- 在where就把范围缩小
- 尽量用连表查询代替子查询
- 删改数据尽量用主键
- 正确使用索引
  - 创建索引
    - 选择区分度较大的字段, 重复率低于1/10
    - 选择短字段作为索引
    - 不要创建不必要的索引, 不用的索引及时删除
  - 使用索引
    - 查询的字段为索引字段
    - 条件范围越小越快
    - 条件列不参与计算和使用函数
    - 模糊查询 like 'a%' 而不是 like '%a'
    - and/or
      - and连接有一个有索引就能名中
      - or连接全部有索引才能命中
    - 联合查询（最左前缀原则）
      - create index ind_mix on t1(id,name,email);
      - 条件中带最左字段的都能命中, 否则不能
    - 条件中的数据类型和实际字段的类型必须一致
    - select 字段中应该包含 order by 中的字段

7.补充

覆盖索引
- 查询过程不用回表 eg. select id from 表 where id > 10000;
索引合并：分别创建的两个索引在某一次查询中临时合并成一条索引
执行计划：explain select 语句
- 查看sql语句有无按预期执行
- 查看type等级
- 查看索引使用情况
慢查询优化
- 首先优化sql语句
  了解业务场景, 适当创建索引
  连表查询代替子查询
  确认索引命中情况
- 修改表的结构
  拆表
  固定长度字段往前移
- 使用执行计划观察type等级
慢日志
- mysql的配置中开启, 并设置时间，在超过设定时间之后,这句sql总是会被记录下来，对被记录下来的 sql语句定期优化。

自学两个半月了，希望通过写博客来复习回顾之前学的内容，同时记录自己的学习经历……