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 ‘\n‘;

数据导出

说明
1.导出的内容由SQL查询语句决定,导出格式可自定义,一般导出.csv文件
2.执行导出命令时路径必须指定在对应的数据库目录下
语法格式:select ... from 库.表名 into outfile "/var/lib/mysql-files/xxx.csv" fields terminated by "分隔符" lines terminated by "\n";

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 * ...;用具体字段代替 * ,不要返回用不到的任何字段

MySQL学习笔记

上一篇:设置最小值 最大值函数 | minmax (Grid Layout) - CSS 中文开发手册 - Break易站


下一篇:【web_for_pentester】XML注入