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 多选行为(会自动去重)
- char
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 右外连接
- 左外连接:select * from 表1 left join 表2 on 连接条件;
-
子查询 -- 嵌套
# 查看开发部员工姓名 # 先查询技术部的部门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 表名;
- 作用:加快查找速度
- 创建:create 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等级
- 首先优化sql语句
- 慢日志
- mysql的配置中开启, 并设置时间,在超过设定时间之后,这句sql总是会被记录下来,对被记录下来的 sql语句定期优化。
自学两个半月了,希望通过写博客来复习回顾之前学的内容,同时记录自己的学习经历……