数据库相关

数据库：存储结构化数据的仓库

数据库管理软件（DBMS）

数据库管理员（DBA）

SQL:结构化查询语言，绝大部分的关系型数据库都支持

DDL：Data Definition Language

• 作用：用于描述数据库中要存储的现实世界实体的语言。即 创建数据库和表的结构。
• 常用关键字：
• CREATE ALTER DROP TRUNCATE

命令：mysql -uroot -proot           -u后面是用户名 -p后面是密码

命令：sudo service mysql status 出现以下为正确

命令：show databses;

information_schema 元数据

mysql核心库

performance_schema性能相关

sys对DBA  有好处

命令：create database 36th；

命令：使用数据库

use 36th

MySQL常用数据类型

分类	数据类型	说明
数值类型	BIT(M) BOOL，BOOLEAN TINYINT [UNSIGNED] [ZEROFILL] SMALLINT [UNSIGNED] [ZEROFILL] INT [UNSIGNED] [ZEROFILL] BIGINT [UNSIGNED] [ZEROFILL] FLOAT[( M , D )] [UNSIGNED] [ZEROFILL] DOUBLE[( M , D )] [UNSIGNED] [ZEROFILL]	位类型。M指定位数，默认值1，范围1-64 使用0或1表示假或真 带符号的范围是-128到127。无符号0到255。 2的16次方 2的32次方 2的64次方 M指定显示长度，d指定小数位数 （5, 2） 表示比float精度更大的小数
文本、二进制类型	CHAR(size ) VARCHAR(size)    BLOB   LONGBLOB TEXT(clob)          LONGTEXT(longclob)	固定长度字符串（size表示 有多少个字符） 可变长度字符串 （varchar 是可变的） varchar（50）表示最大可以存放的长度是50 BLOB 二进制数据 图片 音乐 TEXT 大文本
时间日期	DATE/DATETIME/ TIMESTAMP TIME	日期类型 DATE (YYYY-MM-DD) 日期时间类型 DATETIME (YYYY-MM-DD HH:MM:SS) 时间戳类型 TIMESTAMP (YYYY-MM-DD HH:MM:SS)

列column=域filed

#消除声音 tab联想

mysql --no-beep -uroot -proot --auto-rehash

#显示数据库

show databases；

#创建数据库

create database 36th；

#使用数据库

use  36th

#显示表

show tables

#删除数据库

drop database XXX；

#创建表

create table member (id int ,name char(20),age int math float);

或者

create table member

——>(id int  auto_increment,  //auto_increment自增

——>name char(20),

——>age int,

——>math float，primary key(id))//primary key(id) 将id作为主键

——>；

#显示表的结构（字段、列、域）

show create table member

#显示某个数据库所有信息

select * from member；

#显示表结构 

desc member

#修改表 增加字段 add

alter table member add enlish float default 60； 

#修改表 修改字段（改类型） modify

alter table member modify name varchar(20);

#修改表 修改字段（改类型） modify

alter table member change name manme varchar(20);

注意：change可以改变列的名字，modify不行

#修改表 删除列

alter table member drop enlish；

#删除整张表

drop table XXX

#调整某一行的位置

alter table member add XXX float id after；

#更改表名

Rename table 表名 to 新表名

#数据库退出

quit或者exit或ctrl+D

#展示表的结构

show create table customer;

数据库管理语言DML

#字符串使用单引号，不是双引号

#插入数值

insert into member（id,name,age,math）values(1,'qiaofeng',30,59);

insert into member（id,math）values(1,59);

#

select *from member；

#插入多个数据

insert into member values(3,'duanyu',20,90),(4,'duanzhengchu',50,98),(8,'azi',18,95);

#修改指定行

update member set age=24 where id=2;

#修改全部行

update member set age=24 

#数据的删除(加where与不加where的区别)

delete from member where id=8;

#插入空值

insert into （name）values(null);

数据库查询语言DQL

 

例如：select id from member;

#选择指定列

select id，name from member；

#选择所有列

select * from member；

#查找范围

select id,name from member where id >1;

#去重 distinct

select distanct e_date from member;

#对字段进行表达式操作

select age ,age+10 from member;

#取别名 

select age，age+10*1000+math*1000 as xxx from member;

#比较运算符

select id ，name from member where id》1；

#查找范围

select age from member where age between 30 and 50;

#in等价枚举，与范围不一样

select * from member where age in（20,60）；

#模糊匹配 like关键字

select *from member where name like '%u'  %指的是0个或任意多个字符u是最后落脚点是字母u _代表一个字符

select *from member where name like '%u__'  %指的是0个或任意多个字符u后面跟两个字符

#判断某个字段是否为空

select * from member where id is NULL；

#排序

select age from member where order by age；

select age from member where order by age asc；//升序排序

select age from member where order by age dasc；//降序排序

select * from member where order by age；

#分页查找

select * from member where order by age limit 1；//限定某一行排序

select * from member where order by age limit 3 offset 1；//从某一行偏移几行

数据完整性：

1.实体完整性：保证每一行是唯一的，通过主键去进行限定

2.域（列、字段）完整性：保证列的数据类型和约束

3.参照完整性：

• 一个表的外键和另一个表的主键关联
• 不允许引用不存在的实体

表和表之间的关系：

1.一对多

2.多对多

3.一对一

#创建外键

create table order_table(order_id int auto_increment primary key,name char(20) not null,price float,user_id int,foreign key(user_id) references customer(id));

#交叉查询(做笛卡尔积)

select * from customer cross join order_table;

#内连接 inner join

select *from customer inner join order_table on customer.id=order_table.user_id;

select *from customer as c inner join order_table as c on c.id=o.user_id;//取别名

select *from customer，order_table where customer.id=order_table.user_id;//隐式内连接

#左外连接，将符合条件的所有行显示出来，然后将左表中的其他列也显示出来，右表不符合条件的列使用NULL

#外连接的时候，必须要加上连接条件on，如果想将不符合条件的行丢掉，可以加上where进行过滤

slect * from customer as c left outer join order_table as o on c.id=o.user_id

where o.order_id is not null;

#右外连接

select * from customer as c right outer join order_table as o on c.id=o.user_id;

总结：

DDL（数据库或表的结构）:

创建： create database/table dbname/tableName；

修改：alter table tableName add/modify/change/drop 列名 类型 [约束条件]

删除：drop database/table dbname/tableName；

清空： truncate table tableName；

DML（针对表中的数据）：

插入：insert into table tableName [指定列] values（对应列的值）

修改/更新：update tableName set=列的值[where 条件]

删除：delete底层是一个for循环 然后一行一行删，只留一个表名（空表）truncate是整个表drop掉 然后重新建一个和表名一模一样的空表

delete from tableName

DQL:

select

复杂查询

select * from left_table join_type right_table [on 条件] [where 条件]

交叉连接 cross join

内连接：inner join

查看当前所属数据库：select database()

#表的复制

create table customer_tmp select * from customer;#结构与数据全部复制过来

create table customer_tmp like customer;#只复制结构不复制数据

#修改主键

alter table member drop primary key；

alter table member modify id int primary key；

#如果一个表有外键约束，此时想修改，必须要先删除外键，然后在添加外键，不能直接在原有的基础上修改

#1修改外键约束

alter table member drop foregin key fk_class_student

#2如果想修改表的外键名字，可以先删除外键约束，然后再新建

alter table order_table_tmp add [CONSTRAINT 'order_table_tmp_ibfk_1'] foreign key(user_id) references customer_tmp(id)

#union 去重

select *from member where id>1 union [all] select *from member where age>22;

#分组

select age from member group by age；

#分组查询

select max(age),count(age),age from member group by age having count(age)>1；

事务：

• 概念：构成单一逻辑工作单元的操作集合。一组操作或者命令的集合
• 事务的性质（ACID）
• 原子性：事务要么全部不发生，要么全部都发生
• 一致性；事务的执行的前后，数据的完整性保持一致，且事务执行前后数据的总和不变
• 隔离性：在A看来   B事务要么在A事务执行之前就已经完成了 要么在A事务完成之后才开始 ，一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰  （两个事物没有交叉，或两个事务串行执行）

        隔离级别有关系，隔离性不能达到100%

• 持久性：指指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

事务的基本操作

START TRANSACTION / BEGIN ：开启一个事务，标记事务的起点

COMMIT：提交事务，表示事务成功被执行。

ROLL BACK ：回滚事务，回滚到初始状态或者回滚点。 roollback to 标签    回到指定回滚点

SAVEPOINT ：回滚点  //savepoint 标签 设置回滚点

RELEASE SAVEPOINT ： 删除回滚点

SET  TRANSACTION:  设置隔离级别 

并发产生的四个问题

1.脏写

脏写是指当多个事务并发写同一数据时，先执行的事务所写的数据会被后写的的 数据覆盖

 

2.脏读

如果一个事务A向数据库写数据，但该事务还没提交或终止，另一个事务B就看到了事务A写入数据库的数据，这个现象我们称为脏读

3.不可重复读

一个事务有对同一个数据项的多次读取，但是在某前后两次读取之间，另一个事务更新该数据项，并且 提交了，在后一次读取时，感知到了提交的更新

4.幻读

一个事务前后两次读数据的时候，凭空多产生了一条，此时就像产生幻觉一样，称为幻读。T2事务前后两次进行读操作的过程中，T1事务新增了一条数据，并且提交了，所以对于T2事务而言，产生幻读。

隔离级别

并行：在同一个时间点，可以有多个事情发生。（点）

并发：在同一个时间段，可以有多个事情发生。（线）

读未提交

脏写可以被避免

可以产生脏读与不可重复读

左右两边开启事务，左边更新某条数据，但是没有提交，在右边会话可以看到左边会话没有提交的数据，产生了脏读，如果接着再将左边会话提交，右边会话继续读，前后两次读的数据不一致，可以产生不可重复读

下面两张图为幻读

读已提交

脏写与脏读是可以避免的。

左右两边都开启会话，左边会话更新某条数据，右边会话仍然更新该条数据，发生右边会话卡住了，就避免了脏写；如果在继续在右边执行select操作，发现左边会话的更新操作没有被右边会话读出来，避免了脏读。

不可重复读与幻读是可以产生的。

可重复读

可以避免脏读，脏写，不可重复读。

幻读演示：

 

可串行化

并发程度越来越低，但串行度是越来越高的。

#查看当前事务的隔离级别

select @@[session|global] transaction_isolation;

select @@tx_isolation;

#查询当前隔离级别

select @@session.transaction_isolation;

#设置当前隔离级别为读未提交

set session transaction isolation level read uncommitted;

#设置当前隔离级别为读已提交

set session transaction isolation level read committed；

#设置当前隔离级别为可重复读

set session transaction isolation level repeatable read；

#设置当前隔离级别为可串行化

set session transaction isolation level serializable;

索引

概念 索引(Index)是帮助MySQL高效获取数据的数据结构

数据结构

空间的连续性、时间复杂度、磁盘IO的次数、范围查找。

顺序查找：遍历操作，时间复杂度是O（N），需要大块连续的空间

    二分查找：时间复杂度O（longN），需要大块连续的空间

二叉树：时间复杂度O（longN），不需要连续的空间。如果数据量比较大的时候（百万级别），二叉树的高度会非常高。磁盘IO的次数会非常高，而磁盘IO的速度是非常慢的，

哈希：时间复杂度是O（1）。哈希会有哈希冲突，会影响查找速度。哈希等价于无序。在SQL里面经常会进行范围查找，而哈希是没有顺序的，所以数据量大的时候，是不能用哈希的。

B树：结点中既存key值，也存value，还要存指向孩子节点的指针，所以一个B树结点存储key值的数量是有限的

B+树：非结点中存key值，不存value，还要存指向孩子节点的指针，所以一个B树结点存储key值的数量相对B树而言，数据量要大，这样树的高度就会降低，树的高度就是进行磁盘IO的次数

#查看结点大小 

 

B+树的特征

 

索引的分类

主键索引：以主键创建的索引

非主键索引（辅助索引）：普通索引，唯一索引，全文索引，组合索引

索引的创建

主键索引

#创建表的时候创建主键

create table test1(id int ,age int ,primary key(id));

#在表创建完成之后，再创建主键

alter table test2 add primary key(id);

普通索引创建

普通列作为的索引

#在创建表的时候，创建普通索引

create table tset3(id int,age int,index idx_age(age));

#在创建表之后，再创建索引

alter table test4 add index idx_age(age);

#在创建表之后，使用create index创建索引

create index idx_id on test4(id);

唯一索引

列中的数据是唯一的

#在创建表的时候，创建普通索引

create table tset3(id int,age int,unique index idx_age(age));

#在创建表之后，再创建索引

alter table test4 add unique index idx_age(age);

#在创建表之后，使用create index创建索引

create unique index idx_id on test4(id);

组合索引

该索引是由多个列一起组成的，称为组合索引

#组合索引在创建完表之后创建

alter table test5 add index idx_name_math_english(name,math,english);

create index idx_name_math_english on test5(name,math,english);

#展示索引

show index from test5\G；\G是纵向展示

索引的删除

#删除表中的索引

alter table test5 drop index idx_name_math_english;

drop index idx_id on test

最左前缀

在组合索引中，如果要进行查询的时候，一定要出现组合索引中最左边的一列，在最左边的一列相同情况下，第二列才可能排好序，然后再第一列，第二列排好序的情况下，第三列才能局部有序，以此类推，所以在进行查询的时候，如果不出现第一列，后面的列是不能保证有序的，就用不到索引。所以在查询的时候一定要出现第一列。

索引优缺点

优点

可以提高查询速率

缺点

1.索引本身也会占用空间

2.索引过多之后，数据更新也会带着更新索引树，所以表的更新比较困难

MySQL逻辑架构

cache：侧重读上

buffer：侧重写上

主要是：MySQL的服务层与存储引擎层

Server层包括连接器，查询缓存，解析器，优化器和执行器等，涵盖了MySQL大多数核心服务功能。

存储引擎，包括常用的：MyISAM,InnoDB，memory。作用是：进行数据的存储与提取。

存储引擎

 

外键依赖

一个表中的某一列是另一个表的主键

MyISAM存储引擎

MySQL5.5之前默认的存储引擎

特点：

a.查询速度快

b.支持表锁

c.支持全文索引（正排索引，倒排索引）

d.不支持事务

InnoDB存储引擎

MySQL 5.5以及以后的版本默认的存储引擎。没有特殊应用，推荐使用InnoDB引擎。

特点：

a.支持事务

b.支持行锁和表锁（默认支持行锁）

c.支持MVCC（多版本并发控制），版本号，事务号

d.支持崩溃恢复

e.支持外键一致性约束

使用innoDB存储表，生成两个文件。

.frm#存储表结构，是任何存储引擎都有的

.ibd #存放数据和索引

 

非聚集 索引与聚集索引

非聚集索引：索引和数据是分开存放的，这样的索引叫非聚集索引（非聚簇索引）；

聚集索引：索引和数据存放在一起，这样的索引叫做聚集索引（聚簇索引）

#查看数据库版本号

select version();

#查看当前表所在的数据库的名字

select database();

memory存储引擎

特点：

a.所有数据都存放在内存中，因此数据库重启会丢失

b.支持表锁

c.支持Hash和BTree索引

d.不支持Blob（大的二进制）和Text（大的文本）字段

#临时表的创建

create temporary table test1(id int,age int,name varchar(20));

#临时表创建完成之后，默认的存储引擎还是innodb，但是show tables看不到表的名字；可以进行正常的SQL语法（可以插入，更新，删除，查找），如果将存在临时表的会话关闭，name临时表就会消失，就不存在了;

#创建存储引擎是memory的表

create table test3(id int,age int,name varchar(20)) ENGINE=memory;

#存储引擎是memory的表创建之后，存储引擎明显就不是innodb，而是memory，show tables的时候是可以看到表的名字的，可以进行正常的SQL语法（可以插入，更新，删除，查找），即使将会话关闭，存储引擎是memory的表，表是不会消失的。断电重启之后，表的结构还在，但是表中的内容消失了。

drop delete truncate之间区别

drop可以删除数据库，也可以删除表，不仅可以删除结构，也可以删除里面的内容，甚至可以删除数据库的名字或者表的名字

delete删除表中的内容，delete删除数据的时候，应该是一条条删除的，表的结构还是存在的。

trucate也是进行删除表的，将表中的内容一次全部删除。先执行了drop，再去执行create

存储引擎的选择

MySQL锁机制

锁的分类

从对数据操作的粒度划分： 

表级锁：开销小，加锁块；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。

从对数据操作的类型划分：

读锁（共享锁）：同一份数据，多个读操作可以同时进行而不互相影响。

写锁（排它锁）：当前操作没有完成之前，它会阻断其他读锁和写锁。

MyISAM的表锁

lock table 表名 read；（加读锁）

lock table 表名 write；（加写锁）

1.读与读之间是共享的，读与写之间是排斥的

2.左边会话对一张表加了读锁，该会话可以对该表进行读操作，但是不能进行写操作。

3.左边会话对test_myisam加了读锁之后，如果还想读其他表，是不行，如果想读其他的表，此时需要对其他表进行先加锁。

4.左边对话对某张表加了读锁之后，右边会话也可以对本张表加读锁（读锁是一个共享锁）。

表的占用情况，show open tables

某个会话可以将某张表多次进行加读锁，但是in_use不会增加

写锁是排它锁，本会话可以进行读写，但是其他会话不能进行读写操作

总结：读锁会阻塞写，但是不会堵塞读（共享）。而写锁则会把读和写都堵塞（排他）

InnoDB的行锁

SELECT ... LOCK IN SHARE MODE;#加读锁

SELECT ... for update；#加写锁

演示的时候，需要使用事务

间隙锁

锁的是一个范围

业务设计（了解）

范式就是规则。

第一范式：每一列是原子的，不能再分

第二范式：确保表中的每列都和主键相关

第三范式：确保每列都和主键列直接相关，而不是间接相关

范式化设计的优缺点：

优点：

可以尽量的减少数据冗余

范式化的表通常比反范式化的表更小

范式化的数据库更新起来更加快

不足：

范式化的表，在查询的时候经常需要很多join关联，增加让查询的代价

更难进行索引优化

反范式设计的优缺点：

优点：

可以减少表的关联

可以更好地进行索引优化

缺点：

存在数据冗余及数据维护异常

对数据的修改需要更多的成本

范式和反范式的选取是根据需求来的

索引再探

主键索引：以主键创建的索引

非主键索引（辅助索引）：以非主键创建的索引，普通索引，唯一索引，组合索引

聚集索引（聚簇索引）：数据与索引一起存放 InnoDB

非聚集索引（非聚簇索引）：数据与索引单独存放 MyISAM

InooDB以主键创建的索引

InnoDB以非主键创建的索引

select age from people where name='Bob'

select age from people where id=xxx;

select name, id from people where name='Bob'

回表：当使用辅助索引进行查询的时候，如果查询的列在辅助索引树上没有，那么就需要通过该条信息对应的主键，在主键索引树上继续查找，此时就会在两个索引树上进行查找，磁盘IO的次数就会增加，查询速度就会比较慢（尽量避免回表）

索引覆盖：当使用辅助索引进行查询的时候，如果查询的列在辅助索引树上命中

慢查询（了解）

概念

就是记录了查询比较慢（执行时间长）SQL的日志

show variables like"%long_query%";

show variables like "%slow%";//会出现下图

set global slow_query_log=on；

set globallong_query_time=0.1

执行计划（重点）

执行命令：explain+SQL

 

 

1.id列

在id相同的时候，会从上向下顺序执行；当id不同的时候，会按照id从大到小进行执行。当id既有相同也有不同的情况，会按照id大的先执行，然后id相同的时候，从上向下顺序执行

2. selec_type列

3.type列

system、const、eq_ref、ref、range、index、ALL.

如果使用的是主键，并且是主键上或唯一索引上的等值扫描的话，可以达到const级别，如果表的数据就只有一条的时候，那么就是system。如果是两个表之间的等值扫描，并且使用的是主键或者唯一索引，且第二个表里面只有一行与前面的表对应，此时使用的是eq_ref。

总结：

system最快：不进行磁盘IO

const：PK或者unique上的等值查询 

er_ref:PK或者unique上的join查询，等值匹配，对于前表的每一行，后表只有一行命中

ref:非唯一索引，等值匹配，可能命中多行

range：索引上的范围扫描，例如：between、in、>

index:索引上的全集扫描，例如：InnoDB的count

ALL最慢：全表扫描

SQL的type只要达到了range级别以上就认为是一个合格的SQL语句

possilbe_keys、key、ken_len三列

possible_keys可能使用的索引、往往是推荐值

key实际使用到的索引

key_len索引使用的最大可能长度

编码格式

utf8mb4:一个字符占用4个字节

utf8：一个字符占用3字节

gbk：一个字符占用2字节

latin：一个字符占用一个字节

为空：额外占用一个字节

varchar：额外占用两个字节

最左前缀结合key_len进行计算

extract额外信息

 

 

主从复制

MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点

负载均衡、读写分离、数据热备份

主机master：binlog，镀膜县城

从机slave：RelayLog、IO线程、SQL线程