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语句定期优化。
自学两个半月了，希望通过写博客来复习回顾之前学的内容，同时记录自己的学习经历……