对查询结果去重:在字段前加distinct
select distinct 字段 from 表;
distinct只能出现在所有字段的最前面,它前面不能出现字段
distinct后面有多个字段的话,那就所有的字段联合起来一起去重
内连接:没有主表副表之分,查不到就不查,不显示
外连接:有主表副表之分,查不到,返回null
{
左外连接
右外连接
}
表级约束:联合多个字段起来一起添加一个约束(多个字段同时满足)
列级约束:只针对于一列的
主键约束:主键字段上的每一个值是每条数据的唯一标识,只要不一样,就不是一条数据(像身份证)
外键约束:用了此约束的字段,它的值只能填入主表中的某个字段的值(被引用的主表中的字段至少具有唯一性unique,不然引用了谁不清楚)
用法:
{
foreign key 字段名 references 主表(字段);
}
## 存储引擎:这个名字只在mysql存在,oracle有对应的机制,叫存储方式
每个存储引擎都有自己的优点,在不同的时机选择不同的引擎
1.myisam:采用三种方式存储一个表,表的格式frm,表的数据myd 表的索引myi。(存在硬盘中的文件)
优缺点:可以被压缩,节省存储空间,可以转换为只读表,不被修改,提高检索效率,不支持事务
2.innodb:mysql默认的存储引擎,表的结构存储在frm文件中,表的数据存储在tablespace的逻辑空间中(数据文件就是索引文件)
优缺点:支持行级锁和表级锁,默认是行级锁、事务,mysql数据库崩溃后可以自动恢复、外键,级联删除,级联更新等,安全得到保障,无法被压缩,
无法转换成只读文件3.memory:
优缺点:表的数据和表的索引存储在内存中,查询速度最快,但是数据容易丢失,不支持事务
## 索引常见模型:相当于一本书的目录,通过目录可以快速地找到想要的东西,是给字段使用的
# 数据库底层存储的核心就是基于以下这些数据模型的
1.哈希表:哈希表这种结构适用于只有等值查询的场景
2.有序数组:有序数组在等值查询和范围查询场景中的性能就都非常优秀,
有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有人口信息,这类不会再修改的数据。
3.二叉树(N叉树):为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N 叉”树。这里,“N 叉”树中的“N”取决于数据块的大小,二叉树每个节点只有两个分支,N叉树就不一定,B+树就是N叉树的一种
## 主键和unique约束的字段会自动添加索引,根据主键的查询效率高,尽量使用主键检索
## MySQL索引的底层数据结构:
B+tree,创建了索引,会在硬盘或内存中生成一个该字段的索引对象文件,并且自动排序
例:where name="luqiqi",查询时,找到该字段的索引对象,然后开始检索,L区,U区...
缩短了扫描的范围, 就是 字段 -》索引 -》 表中数据在硬盘中对应字段(例:luqiqi)的地址值,不走内存,走硬盘
## 查询表的两种方式:1.全表扫描 2.索引检索
## 创建索引和删除索引:
create index 索引名 on 表名(字段)
drop index 索引名 on 表名
索引的分类
单一索引:给单个字段添加索引
联合索引:多个字段联合添加一个索引
主键索引:主键约束自动添加索引
唯一索引:unique约束自动添加索引
索引的失效:使用模糊查询,第一个通配符使用%的时候
## 索引的优化
1.覆盖索引:由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。
需要注意的是,在引擎内部使用覆盖索引在索引 k 上其实读出了三个记录,R3~R5(对应的索引 k 上的记录项),但是对于 MySQL 的 Server 层来说,它最终就只是拿到了引擎的两条记录,因此 MySQL 认为扫描行数是 2。2.联合索引:多使用索引下推或最左前缀索引
索引下推:在第一次遍历索引树之后,在回表之前,就开始对索引中包含的字段先做条件判断,
直接过滤掉不满足条件的记录,减少回表次数。
## DBA命令:在window的dos窗口执行
将数据库中的数据导出:mysqldump 数据库名 >绝对路径
将数据库中的指定数据导出:mysqldump 数据库名 表名>绝对路径
导入数据:create database 数据库名;
use 数据库名;
source 绝对路径;
## 三范式:在实际开发中,还是会以客户的需求为主,有时候会拿冗余换执行的速度
第一范式:任何一张表都要有主键,每一个字段原子性不可以再分
第二范式:在第一范式的基础上,所有非主键的字段要完全依赖主键,不能产生部份依赖(多对多,三张表,关系表,两外键)
第三范式;在第二范式的基础上,所有非主键的字段要直接依赖主键,不能产生传递依赖(一对多,两张表,多的表加外键)
一对一怎么设置:
1.主键共享
2.外键唯一(foreign key,unique)
## MySQL是怎么执行更新语句的
1.Redo Log属于InnoDb存储引擎层,而BinLog属于Mysql Server层。
2.Redo Log记录的是物理数据,直接映射到数据字段的变化
而Bin log属于逻辑日志,有两种格式,一种是sql,记录的就是sql,一种是row格式,直接记录了变更前后的数据内容。
3.Redo Log可以提供crash safe的能力,但因为其循环写无法提供归档能力。
4.Bin Log可以提供归档能力。
## MySQL的事务
1.在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作(回滚段里有多个回滚试图)。
记录上的最新值,通过回滚操作,都可以得到前一个状态的值。
2.不可重复读和幻读的区别在于:不可重复读主要是针对读取某个记录的时候发现某些值被修改了,幻读就是读取某个记录的时候发现记录新增了或者减少了