MySQL学习笔记

MySQL

MySQL1

1.MySQL基础3

1.1.数据库概述3

1.1.1.基础概述3

1.1.2.数据库分类3

1.1.3.关系型数据库3

1.2.SQL语句5

1.2.1.定义：结构化查询语言(Structured Query Language)，用于数据库增、删、改、查（所有关系型数据库都使用SQL操作数据）5

1.2.2.特点：每条语句以；结尾，命令关键字不区分大小写，所有符号必须是英文半角符号5

1.3.数据库操作5

1.3.1.1.查看已有库：mysql>show databases;5

1.3.2.2.创建库（指定字符集）：mysql>create database 库名 charset=utf8;5

1.3.3.3.查看某个库的结构：mysql>show create database 库名;5

1.3.4.4.查看当前所在库是哪个：mysql>select database();5

1.3.5.5.切换当前库：mysql>use 库名;5

1.3.6.6.删除库：mysql>drop database 库名;5

1.3.7.7.库名的命名规则5

1.4.数据表操作6

1.4.1.数据表的设计思路6

1.4.2.字段类型6

1.4.3.字段属性9

1.4.4.数据表操作10

1.5.数据操作10

1.5.1.表记录（一行）的增、删、改、查11

1.5.2.表字段（一列）的增、删、改、查14

1.6.数据库备份14

1.6.1.终端操作14

1.6.2.MySQL操作15

1.7.Python操作MySQL数据库操作16

1.7.1.pymysql安装：sudo pip3 install pymysql16

1.7.2.数据库读写概念16

1.7.3.pymysql操作流程16

2.MySQL高级17

2.1.MySQL普通查询-单表17

2.1.1.3.select ...聚合函数 from 表名18

2.1.2.1.where ...筛选18

2.1.3.2.group by ...分组18

2.1.4.4.having ...18

2.1.5.5.order by ...排序(一定排倒数第二)19

2.1.6.6.limit ...;限定结果显示个数(一定排最后)19

2.1.7.distinct关键字19

2.1.8.对查询结果的数学运算19

2.1.9.嵌套查询19

2.2.MySQL索引查询20

2.2.1.索引概述20

2.2.2.索引类型22

2.2.3.SQL命令运行时间监测24

2.3.MySQL多表操作24

2.3.1.外键24

2.3.2.多表查询25

2.3.3.表的复制27

2.4.MySQL存储引擎27

2.4.1.锁的概念27

2.4.2.mysql事物28

2.4.3.存储引擎29

2.5.E-R模型31

2.5.1.定义：实体-关系 数据模型,用于数据库设计31

2.5.2.E-R图31

2.6.MySQL调优32

2.6.1.1.选择存储引擎32

2.6.2.2.在select、where、order by常涉及字段设置索引32

2.6.3.3.SQL语句优化32

 

1. MySQL基础

1.1. 数据库概述

1.1.1. 基础概述

数据库：在数据库管理系统管理和控制下，在一定介质（硬盘）上的数据集合、数据仓库

数据库管理系统：管理数据库的软件（mysql），用于管理和维护数据库

MySQL数据库：由MySQL软件建立的数据库

1.1.2. 数据库分类

关系型：采用关系模型（行列组成的二维表）来组织数据结构的数据库，MySQL、Oracle（目前最大的，收购了msql）、SQLite、SQL_server（微软开发的）

非关系型：不采用关系模型组织数据结构的数据库

1.1.3. 关系型数据库

数据库结构：数据元素-->记录（行），字段（列）-->数据表-->数据库

数据表：存放数据的二维表格

字段：二维表的每一列，用来表示该列数据的含义

记录：二维表的一行，表示一组完整的数据，可以说数据表是有每一条记录组成的

MySQL特点

关系型数据库

可跨平台

支持多种编程语言（python、java、php）

基于磁盘存储，数据是以文件形式存放在数据库目录/var/lib/mysql下

MySQL安装

Ubuntu

安装服务端：sudo apt-get install mysql-server

安装客户端：sudo apt-get install mysql-client

Windows

MySQL官网

MySQL启动和连接

服务端启动：sudo etc/init.d/mysql status|start|stop|restart
    或sudo service mysql start|stop|restart|status

status查看状态

start启动

stop关闭

restart重启

客户端连接：mysql -h主机地址 -u用户名 -p密码

本地连接：mysql -uroot -p123456

断开连接：ctrl+d或exit

1.2. SQL语句

1.2.1. 定义：结构化查询语言(Structured Query Language)，用于数据库增、删、改、查（所有关系型数据库都使用SQL操作数据）

1.2.2. 特点：每条语句以；结尾，命令关键字不区分大小写，所有符号必须是英文半角符号

1.3. 数据库操作

1.3.1. 1.查看已有库：mysql>show databases;

1.3.2. 2.创建库（指定字符集）：mysql>create database 库名 charset=utf8;

1.3.3. 3.查看某个库的结构：mysql>show create database 库名;

1.3.4. 4.查看当前所在库是哪个：mysql>select database();

1.3.5. 5.切换当前库：mysql>use 库名;

1.3.6. 6.删除库：mysql>drop database 库名;

1.3.7. 7.库名的命名规则

字母、数字、下划线组成，不能纯数字，不能有除了_外的其他符号

区分大小写，stu和Stu是两个库

不能以MySQL关键字命名

1.4. 数据表操作

1.4.1. 数据表的设计思路

分析存储内容

确定字段构成

选择字段类型

1.4.2. 字段类型

数字型（包含bit比特型）

整数型：int[4],smallint[2],tinyint[1],bigint[8]

浮点型：float[4],double[8]

定点型：decimal（m，d）有m-d位整数和d位小数组成

比特值型：值为0或1，表达两种情况，如真，假

字符型

char()定长字符串:char默认表示1字符，效率高，char(32)字符长度不足32填充空格，注意：mysql取值时会将末尾空格去掉

varchar()变长字符串：无默认值,参数必填，节省空间，varchar(32)

text文本类型

blob二进制字节串

可用于把隐藏文件（图片、视频等）存在数据库中

enum枚举型，单选类型：enum('male','female')

set可复选类型:set('sing','dance','draw')

时间类型

类型层面

date年月日，数据插入格式：'yyyy-mm-dd'

time时分秒，数据插入格式：'hh:mm:ss'

datetime年月日时分秒，数据插入格式：'yyyy-mm-dd hh:mm:ss'

timestamp与datetime显示一致，内部存储是时间戳

year年

数据值层面

MySQL中的时间函数

now()返回系统当前年月日时分秒，可用于设置datetime类型字段的默认值（default now()）

curdate()返回系统当前年月日，可用于设置date类型字段的默认值（default curdate()）

curtime()返回系统当前时分秒，可用于设置time类型字段的默认值（default curtime()）

time('2:30:25')将字符串'2:30:25'转换为时间

date('2019-9-22')将字符串'2019-9-22'转换为日期

时间运算

时间类型数据的作用：以字符串类型的数据插入，以时间类型进行运算，从而对数据进行筛选操作

时间间隔 interval 3 year|month|day|hour|minute|second

举例：

建表：create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime default now(),performance time);

插入数据：insert into marathon(athlete,birthday,performance) values('lennie','1992-11-23','2:46:36'),('ginger','1994-8-24','2:37:24')...;

查询

00后运动员的信息：select * from marathon where birthday>='2000-01-01';

近7天报名的运动员的信息：select * from marathon where registration_time > (now()-interval 7 day);

成绩差10分钟就能到两个半小时以内的运动员有哪些：select althlete from marathon where performance - time('2:30:00') < interval 10 minute;

1.4.3. 字段属性

unsigned：数字类型设置无符号

not null：设置字段不能为空（不写表示默认可以为空），插入数据时为空就报错

default：设置字段默认值，插入数据时为空就自动填充默认值

auto_increment：设置字段为自增，一般用于主键，自带属性默认值每次自动加1

primary key：设置该字段为主键，主键自带属性不能重复，不能为空

1.4.4. 数据表操作

1.创建表：create table 表名（字段1 类型 属性，字段2 类型 属性，...）charset=utf8;

e.g.：create table interest(id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned,hobby set('sing','dance','draw'),price decimal(6,2),content text) default charset=utf8;

2.查看已有数据表：show tables;

3.查看已有表字符集（创建表的sql语句）：show create table 表名；

4.查看数据表结构(表头)：desc 表名;

5.删除表：drop table 表名；

6.修改表名：alter table 表名 rename 新表名；

1.5. 数据操作

1.5.1. 表记录（一行）的增、删、改、查

1.插入行

1.insert into 表名 values(记录1),(记录2)...;每条记录中的值必须和所有字段完全对应

2.insert into 表名(字段2,字段3) values(值2.1，值3.1),(值2.2，值3.2)...;可指定任意不能为空的字段插入数据（不推荐）

2.删除行：delete from 表名 where 条件;如果不加where所有记录全部清空

3.更改数据：update 表名 set 字段1=值1，字段2=值2... where 条件；如果不加where，字段1那一列全部改为值1...

4.查询数据：

1.select * from 表名 where 条件；不加where显示全表

2.select 字段1,字段2... from 表名 where 条件；不加where显示全列

5.where子句

通过一定的运算表达式对数据进行筛选、定位

MySQL运算符

算数运算：+,-,*,/,%

比较运算：=,!=,<,>,<=,>=,in,not in,between,not between,is null,is not null,like,regexp,<=>

between 5 and 8：闭区间[5,8]

in (5,8)：集合(5,8)

is null：为空

like：模糊查询

regexp：正则查询

字段1<=>字段2：字段1，字段2都为null为真否则为假

逻辑运算：and,or,not或！,xor(异或：相同为0，不同为1)

位运算

6.高级查询语句

like，模糊查询

运算符（关键字）like

%表示任意0个或多个字符

_表示任意单个字符

举例

查询名字以A开头的记录：select * from class_1 where name like 'A%';

查询名字为3个字母的记录：select * from class_1 where name like '___';

regexp，正则查询不区分大小写

格式：where 字段名 regexp 'regex'

举例，查询名字以B开头的记录：select * from class_1 where name regexp '^B.+';

order by 字段名1 [desc]，排序

使查询结果按照字段1，默认升序排列，desc倒序排列

可复合排序，如：oder by 字段1，字段2 desc；表示按字段1升序，字段1有相同值时按照字段2降序排列，--->排序嵌套

limit，限制查询结果显示数量

limit n：显示结果的前n条

limit m,n：从m+1条开始，显示n条

limit (b-1)a,a：查询结果分页显示，每页显示a条，显示第b页的内容

union distinct[all]，联合查询（类似or）

用于连接两个select语句，使两个查询结果拼接在一起展示，默认distinct去重复，all不去重

注意：两个查询语句所查询的字段必须一致，否则报错

举例：查询class_1表中女生，或者成绩大于90分的学生信息(女生成绩小于90的也展示出来)
mysql>select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

1.5.2. 表字段（一列）的增、删、改、查

1.添加字段：alter table 表名  add 新字段名 类型 属性 first|after 旧字段；添加到第一列，添加到某字段后面，不写默认添加到最后

2.删除字段：alter table 表名 drop 字段名；

3.修改字段：

1.修改字段数据类型：alter table 表名 modify 字段名 新数据类型；

2.修改字段名：alter table 表名 change 旧字段名 新字段名 新类型；

1.6. 数据库备份

1.6.1. 终端操作

说明：

1.操作对象是.sql文件，即把库导出为.sql文件和把.sql文件导入数据库

2.操作命令不是sql语句，无需启动mysql，直接在终端操作即可

数据库导出命令：mysqldump -uroot -p 需要导出的库名 > 路径/xxx.sql

库名：导出单个库

--all-databases：导出所有库

-B 库1 库2 库3 ：导出指定的多个库

库名 表1 表2 表3：导出指定库的指定表

数据库导入命令：mysql -uroot -p 存储导入数据的库名 < xxx.sql

--one-database：恢复备份数据中的某一个库

e.g.：mysql -uroot -p --one-database 目标库名 < all.sql

1.6.2. MySQL操作

数据导入

标准.sql文件的导入：mysql>source xxx.sql;

结构化文件（如.csv文件，内容是按分隔符分开的，一行一行的）的导入

1.将xxx.csv放到数据库搜索路径中

1.找到本机中mysql的安全路径：mysql>show variables like 'secure_file_priv';-->/var/lib/mysql-files/

2.将目标文件复制到安全路径下：Linux: sudo cp /home/用户名/xxx.csv /var/lib/mysql-files/

2.在数据库中创建对应的表

3.执行数据导入语句：mysql>load data infile '/var/lib/mysql-files/xxx.csv' into table 表名 fields terminated by ',' lines terminated by ' ';

数据导出

说明

1.导出的内容由SQL查询语句决定，导出格式可自定义，一般导出.csv文件

2.执行导出命令时路径必须指定在对应的数据库目录下

语法格式：select ... from 库.表名 into outfile "/var/lib/mysql-files/xxx.csv" fields terminated by "分隔符" lines terminated by " ";

1.7. Python操作MySQL数据库操作

1.7.1. pymysql安装：sudo pip3 install pymysql

1.7.2. 数据库读写概念

读操作：数据查询

写操作：数据增、删、改

1.7.3. pymysql操作流程

1.建立数据库连接,创建数据库连接对象：db=pymysql.connect(参数信息)

host:主机地址，本地localhost

port ：端口号,mysql服务器默认监听端口号3306，除了端口号，其他参数都要加引号

user ：用户名

password ：密码

database ：库名

charset ：编码方式,推荐使用 utf8

2. 创建游标对象(cur = db.cursor())

3. 游标方法: cur.execute("insert ....")

1.cur.execute(sql命令,[列表]) 执行SQL命令  --SQL语句是字符串，[]列表可用于格式化字符串传值

2.cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None

3.cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))。

4.cur.fetchall() 获取所有查找到的记录，返回结果形式同上。

5.cur.close() 关闭游标对象

4. 提交到数据库或者获取数据（数据的增、删、改需要同步才能成功） : db.commit()/cur.fetchall()

1.cur = db.cursor() 返回游标对象,用于执行具体SQL命令

2.db.commit() 提交到数据库执行

3.db.rollback() 回滚，用于当commit()出错是回复到原来的数据形态

4.db.close() 关闭连接

5. 关闭游标对象 ：cur.close()

6. 断开数据库连接 ：db.close()

2. MySQL高级

2.1. MySQL普通查询-单表

2.1.1. 3.select ...聚合函数 from 表名

规律：聚合函数输出结果为一个

聚合函数

avg(字段名) ：该字段的平均值

max(字段名)：该字段的最大值

min(字段名)：该字段的最小值

sum(字段名)：该字段所有记录的和

count(字段名)：统计该字段记录的个数 (null不能被统计)

2.1.2. 1.where ...筛选

where只能操作表中实际存在的字段

2.1.3. 2.group by ...分组

作用：对查询的结果进行分组（去重复）显示，通常与聚合函数联合使用

规律：

1.group by 后面的字段必须是select后面的字段之一，

2.查询字段和group by后面的字段不一致，则必须对该字段进行聚合处理（聚合函数）

2.1.4. 4.having ...

作用：对分组或者聚合后的结果进一步的筛选，类似where子句，但是弥补了where关键字不能与聚合函数联合使用的不足，通常与group by 联合使用

与where的区别：where只能操作表中实际存在的字段,having操作的是聚合函数生成的显示字段

2.1.5. 5.order by ...排序(一定排倒数第二)

2.1.6. 6.limit ...;限定结果显示个数(一定排最后)

2.1.7. distinct关键字

语法：select distinct 字段1 from 表名

作用：去重复，效果和group by一致,e.g. : sanguo表中都有哪些国家:

select country from sanguo group by country;

select distinct country from sanguo;

规律：

1.distinct对多个字段去重复时，由该几个字段构成的每一行必须是完全一样的才能去重复

2.distinct不能对任何字段做聚合处理，即注意区分去重复与聚合概念的区别

2.1.8. 对查询结果的数学运算

作用：对查询结果翻倍、加1等显示，对表中原数据没有做修改

运算符：+,-,*,/,%,**

举例：查询时显示攻击力翻倍--->select attack*2 from sanguo;

2.1.9. 嵌套查询

定义：把内层（括号内）的查询结果作为外层的查询条件

语法：select ... from 表名 where 条件(select ....);

2.2. MySQL索引查询

2.2.1. 索引概述

定义：索引是对数据库表的一列或者多列进行排序的一种数据结构（B+树）

二叉查找树

特点：一个结点只存储一个数据，左子树一定比右子树小

举例：查找数字6，需要经过四次磁盘IO（9->5->7->6）

B树

1.每个节点能存储多个索引和数据(键值对),存储海量数据时相对二叉树可以降低树的高度,从而减少磁盘IO次数.

2.但由于每个节点存储了数据,根据索引进行范围查询时效率并不高

B+树

1.除叶子节点外，节点内只存索引（主键）,不储数据（记录），从而单个节点能存储的索引数量远远大于B树，有大量索引重复存储;

2.所有数据均存储在叶子节点中,并且有序的相连,范围查询时效果最棒!

mysql数据库索引采用B（B+）树模型的原因？

1.叶子结点存数据，其他节点存索引，可降低树的高度，减少磁盘IO次数

2.叶子节点有序相连，适合范围查询

优缺点

优点：查询速度极快，千万级的数据查询，只需要经历3-4次磁盘IO

缺点

1.索引需要额外占用物理存储空间（虽然不多）

2.对数据库写操作（增删改）时，索引需要动态维护，降低数据维护速度（对于存储海量数据的数据仓库，侧重数据分析和数据挖掘，供企业决策分析之用，主要是数据查询，修改和删除很少）

2.2.2. 索引类型

说明：

1.索引可设置多个字段

2.哪些字段创建索引:经常用来查询的字段、where条件判断字段、order by排序字段

索引分类

1.普通索引(index) ：字段值无约束,KEY标志为 MUL

2.唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

3.主键（primary key）:字段值不允许重复,且不能为NULL，只能有一个主键字段，KEY标志为 PRI，通常设置记录编号字段id,能唯一锁定一条记录

索引创建和删除

MUL和UNI

创建

创建表时

作为字段属性：字段名 index/unique

作为字段：index/unique(字段名)

已有表中创建：create index/unique 索引名 on 表名（字段名）;索引名默认与字段名一致

查看索引

desc 表名;-->KEY标志:MUL,UNI

show index from 表名G;

删除:drop index 索引名 on 表名;只能一个一个删

PRI和自增属性

创建

创建表时

作为字段属性：字段名 primary key auto_increment

作为字段：create table 表名（id int auto_increment，...，primary key(id))charset=utf8,auto_increment=1000;设置自增起始值

已有表中创建主键：alter table 表名 add primary key(id)

已有表中设置自增起始值：alter table 表名 auto_increment=2000;

删除

删除主键：alter table 表名 drop primary key;

删除自增属性：alter table 表名 modify id int;

2.2.3. SQL命令运行时间监测

​mysql>show variables like '%pro%';

​    1.开启 ：mysql> set profiling=1;

​    2.查看 ：mysql> show profiles;

​    3.关闭 ：mysql> set profiling=0;

2.3. MySQL多表操作

2.3.1. 外键

定义：让当前表字段的值只能在另一个表字段值的范围内选择，表之间的一种关联关系

使用规则：

1.主表、从表(设置外键的表)字段数据类型要一致

2.主表被参考字段必须设置了KEY（索引）的一种，一般为主键

添加外键（类似于创建字段）

创建表时：create table 从表名（。。。，foreign key(参考字段名) references 主表名(被参考字段名) on delete 级联动作 on update 级联动作）charset=utf8;

已有表中：alter table 从表名 add foreign key(从表已有字段) references 主表名(被参考字段) on delete 级联动作 on update 级联动作

删除外键（类似于删除字段）：alter table 表名 drop foreign key 外键名;外键名默认与字段名一致

查看外键：show create table 表名；

级联动作

cascade：从表中的数据随着主表中的数据变化而变化（删除、更新），

restrict(默认)：从表有相关联记录,不允许主表操作

set null：主表删除、更新,从表对应关联字段值为NULL

2.3.2. 多表查询

前提：表之间有关联关系。如：有三个表，省表，市表，县表，县表字段co_cid关联市表字段cid，市表ci_pid关联省表字段pid，但并非完全关联

笛卡尔积

select 表1.字段1，表2.字段1 from 表1，表2;

说明：显示行数为两个字段值数量的乘积，当数据量庞大时，会非常耗费计算机资源，查询时应尽量避免此类查询

多表查询

语法格式：select 字段名列表 from 表名列表 where 条件;

举例：查询省市县详细信息（省有哪些市，市有哪些县）:select province.pame,city.cname,county.coname from province,city,county where province.pid=city.cp_id and city.cid=county.copid;

连接查询

内连接，适用于多表之间完全关联（外键）的情形

功能与多表查询一致，mysql官方推荐使用此命令进行多表查询

语法格式：select 字段名列表 from  表1（主表） inner join 表2（从表） on 条件 inner join 表3（从从表） on 条件;

举例：查询省市县详细信息（省有哪些市，市有哪些县）：select province.pname,city.cname,county.coname from province inner join city on province.pid=city.cp_id inner join county on city.cid=county.copid;

外连接，适用于多表之间并非完全关联的情形

左外链接

以 左表 为主显示查询结果，右表没有对应显示null

语法格式：select 字段名列表 from 表1 left join 表2 on 条件 left join 表3 on 条件;

举例：查询省、市详细信息（要求省全部显示）：select province.pname,city.cname from province left join city on province.pid=city.cp_id;

右外连接

以 右表 为主显示查询结果，左表没有对应显示null

语法格式：select 字段名列表 from 表1 right join 表2 on 条件 right join 表3 on 条件;

举例：查询省、市详细信息（要求市全部显示）：select province.pname,city.cname from province right join city on province.pid=city.cp_id;

2.3.3. 表的复制

复制表数据

说明

1.表能根据实际需求复制数据

2.复制表时不会把KEY属性复制过来

语法格式：create table 副本表名 select ... from 库.主表名 [where 条件];

复制表结构（表头）：create table 副本表名 select ... from 库.主表名 where faulse;

2.4. MySQL存储引擎

2.4.1. 锁的概念

数据库加锁的目的：解决客户端并发访问的冲突问题

锁类型

读锁(共享锁)：加读锁之后可以进行查（读）操作，不能进行增删改（写）操作

写锁(互斥锁、排他锁)：加写锁之后，不能读，不能写

锁粒度

行及锁：只锁定一行

表及锁：锁定整个表

注意：手动加锁需谨慎，尽量使用MySQL内部功能（如存储引擎）自动加锁和释放锁，从而避免死锁

2.4.2. mysql事物

定义：一次表记录操作（删、改、查）从开始到结束的过程（所有sql语句的集合）

作用：一条或多条sql语句同时执行时，维护数据的一致性、准确性、有效性

说明：

1.在MySQL命令行的默认设置下，事务都是自动提交的，因此要显式地开启一个事务来禁止当前自动提交。

2.事务只针对于表记录操作有效,对于库和表的操作无效

3.事务一旦提交结束，对数据库中数据的更改是永久性的

4.事物操作的前提：数据库采用的InnoDB存储引擎

一次事物的操作过程

1.开启事务

方式1：mysql>begin;

方式2：mysql>start transaction;

2.执行1条或多条sql语句

3.关闭事物

方式1：mysql>commit; # 提交并结束事务，并使已对表记录进行的所有修改成为永久性的

方式2：mysql>rollback; # 回滚并结束事务，并撤销正在进行的所有未提交的修改

事物的四大特性ACID

原子性atomicity：事物是最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚

一致性consistency：数据库总是从一个一致性的状态转换到另一个一致性的状态

隔离性isolation：一个事务所做的修改在最终提交以前，对其他事务是不可见的

持久性durability：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失

2.4.3. 存储引擎

定义：处理数据库操作的处理器,表存储数据的方式(技术),也叫表类型.

基本操作

1.查看所有存储引擎：mysql> show engines;

2.查看已有表的存储引擎：mysql> show create table 表名;

3.指定引擎--默认InnoDB

1.创建表时：create table 表名(...)engine=MyISAM,charset=utf8,auto_increment=10000;

2.已有表中：alter table 表名 engine=InnoDB;

三种常用的存储引擎

InnoDB

1.支持行级锁(锁了一行,不影响其他行的操作)

2.支持外键、事务、事务回滚(如银行转账失败回滚,账户充值失败回滚)

3.表数据和索引同存储在一个文件中

表名.frm ：表结构

表名.ibd : 表记录及索引文件  ##B树

4.适用场景:要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制--执行写操作多的表

MyISAM

1.支持表级锁

2.表数据和索引分开存储

表名.frm ：表结构

表名.MYI : 索引文件(my index)

表名.MYD : 表记录(my data)  ##B+树

3.适用场景:用来插入和查询记录时,能提供较高的处理效率--执行读操作多的表

MEMORY

1.表记录存储在内存中，效率高

2.服务或主机重启，表记录清除，容易丢失数据

3.适用场景:作为临时表，存放查询的中间结果。

2.5. E-R模型

2.5.1. 定义：实体-关系 数据模型,用于数据库设计

2.5.2. E-R图

实体：矩形框（记录）

属性：椭圆形框（字段）

关系：菱形框（实体之间的关联）

一对一关联：两表任选其一，设置外键关联主表主键，且从表外键字段设置唯一索引

一对多关联：两表任选其一，设置外键关联主表主键

多对多关联：创建中间表，设置两个外键字段，分别关联两表的主键

2.6. MySQL调优

2.6.1. 1.选择存储引擎

读操作多：MyISAM

写操作多：InnoDB

2.6.2. 2.在select、where、order by常涉及字段设置索引

2.6.3. 3.SQL语句优化

1.单条查询最后添加 LIMIT 1，停止全表扫描

2.where子句中不使用 != ,否则放弃索引全表扫描

3.尽量避免 NULL 值判断,否则放弃索引全表扫描

优化前：select number from t1 where number is null;

优化后：select number from t1 where number=0;# 在number列上设置默认值0,确保number列无NULL值

4.尽量避免 or 连接条件,否则放弃索引全表扫描

优化前：select id from t1 where id=10 or id=20;

优化后：select id from t1 where id=10 union all select id from t1 where id=20;

5.模糊查询尽量避免使用前置 % ,否则全表扫描

6.尽量避免使用 in 和 not in,否则全表扫描  

优化前：select id from t1 where id in(1,2,3,4);

优化后：select id from t1 where id between 1 and 4;

7.尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的任何字段