目录
- mysql常见操作命令
- SQL语句规范
- 列举常见的关系型数据库和非关系型都有那些？
- MySQL常见数据库引擎及比较？
- 简述数据库三大范式？
- 存储引擎
- 数据库的数据类型
- 数据库五大约束
- 什么是事务？MySQL如何支持事务？
- 如何判断表与表关系
- 简述数据库设计中一对多和多对多的应用场景？
- 如何基于数据库实现商城商品计数器？
- 单表的增删改查 与查询顺序
- 常见SQL（必备）
- 简述触发器、函数、视图、存储过程？
- 左连接 ，右连接，内连接和全外连接的4者区别
- MySQL索引种类
- 索引在什么情况下遵循最左前缀的规则？
- 主键和外键的区别？
- MySQL常见的函数？
- 列举 创建索引但是无法命中索引的8种情况。
- 如何开启慢日志查询？
- 数据库导入导出命令（结构+数据）？
- 数据库优化方案？
- char和varchar的区别？
- 简述MySQL的执行计划？
- 1000w条数据，使用limit offset 分页时，为什么越往后翻越慢？如何解决？
- 什么是索引合并？
- 什么是覆盖索引？
- 简述数据库读写分离？
- 简述数据库分库分表？（水平、垂直）
- sq注入
- python操作mysql
mysql常见操作命令
select user(); #查看当前用户名
select database();#查看当前库
show databases;#查看当前所有库
show tables;#查看当前库所有表
desc 表名 #查看表结构
show create table d1;
查看字符集编码的指令:
show variables like "%char%";
SQL语句规范
1.SQL关键字、对象名、和列名不区分大小写。
2.字符值和日期值要区分大小写。
3.在应用程序中如果SQL语句文本很长，可以将语句分布到多行上，并且可以通过使用跳格和缩进提高代码的可读性
4.SQL*Plus中的SQL语句以分号（;）结束。
列举常见的关系型数据库和非关系型都有那些？
MySQL、Oracle、SQLite、Access、MS SQL Server
#数据库分类:
#关系型数据库:mysql oracle. sglserver. sgllite accesse 表的形式 关联性强 模糊查询 查询稍慢
#非关系型数据库:redis. mongodb. memcache(内存级别) 键值对形式 利用键查询快 利用值查询慢
关系型数据库(需要有表结构)
mysql、oracle 、 spl、server、db2、sybase
非关系型数据库（是以key-value存储的，没有表结构 键值对形式 利用键查询快 利用值查询慢）（NoSQL）
MongoDB
MongoDB 是一个高性能，开源，无模式的文档型数据库，开发语言是C++。它在许多场景下可用于替代传统的关系型数据库或键/值存储方式。
Redis
Redis 是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库，并提供多种语言的API。目前由VMware主持开发工作。
MySQL常见数据库引擎及比较？
Innodb存储引擎 mysql5.6之后的默认的存储引擎
数据和索引存储在一起 2个文件
数据索引表结构
数据持久化
支持事务
为了保证数据的完整性,将多个操作变成原子性操作
保持数据安全
支持行级锁
修改的行少的时候使用
修改数据频繁的操作
支持表级锁
批量修改多行的时候使用
对于大量数据的同时修改 加锁
支持外键
约束两张表中的关联字段不能随意的添加删除
够降低数据增删改的出错率
Myisam存储引擎 mysql5.5之前的默认的存储引擎
数据和索引不存储在一起 3个文件
数据索引表结构
数据持久化
只支持表锁
Memory存储引擎
数据存储在内存中, 1个文件
表结构
数据断电消失
简述数据库三大范式？
数据库的三大特性：
'实体':表
'属性'：表中的数据(字段)
'关系'：表与表之间的关系
----------------------------------------------------
# 数据库设计三大范式：
1：确保每列保持原子性（即数据库表中的所有字段值是不可分解的原子值）
2：确保表中的每列都是和主键相关（表中只能保存一种数据，不可以把多种数据保存在同一张表中）--->完全属于当前表的数据
3：确保每列都和主键直接相关，而不是间接相关（在一个数据库表中保存的数据只能与主键相关）----> 消除传递依赖（间接）
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。
而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
存储引擎
存储数据的的方式
# 存储引擎 -- 存储数据的方式
# 一张表
# 数据
# 表的结构
# 索引(查询的时候使用的一个目录结构)
# 面试题
# 你了解mysql的存储引擎么?2
# 你的项目用了什么存储引擎,为什么?
# innodb
# 多个用户操作的过程中对同一张表的数据同时做修改
# innodb支持行级锁,所以我们使用了这个存储引擎
# 为了适应程序未来的扩展性,扩展新功能的时候可能会用到...,涉及到要维护数据的完整性
# 项目中有一两张xx xx表,之间的外键关系是什么,一张表的修改或者删除比较频繁,怕出错所以做了外键约束
数据的存储方式 -- 存储引擎engines innodb常用 myisam memory
使用不同的存储引擎,数据是以不同的方式存储的
show engines; 查看存储引擎
Innodb存储引擎 mysql5.6之后的默认的存储引擎
数据和索引存储在一起 2个文件
数据索引表结构
数据持久化
支持事务
为了保证数据的完整性,将多个操作变成原子性操作
保持数据安全
支持行级锁
修改的行少的时候使用
修改数据频繁的操作
支持表级锁
批量修改多行的时候使用
对于大量数据的同时修改 加锁
支持外键
约束两张表中的关联字段不能随意的添加删除
够降低数据增删改的出错率
Myisam存储引擎
Myisam存储引擎 mysql5.5之前的默认的存储引擎
数据和索引不存储在一起 3个文件
数据索引表结构
数据持久化
只支持表锁
Memory存储引擎
数据存储在内存中, 1个文件
表结构
数据断电消失
数据库的数据类型
数值类型
int 范围大
tinyint 范围小
float浮点数 精度小 常用
decimal小数值 精度大 不常用
时间日期类型
datetime年月日时分秒
date年月日
time时间
year年
字符串类型
char定长速度快占用空间大 存储范围小
varchar变长速度相对慢占用空间小 存储范围大
选择类型
enum单选
set多选
ps 时间添加方式
#添加方式 全数字或字符串(20180705112222);('2018-07-05 11:22:22');
单选题,只能从有限的条件中选择
多选题,从有限的条件中选
乱写为空 如果要乱写报错 就设置约束
多写多次只显示一次
数据库五大约束
unsigned 无符号与int等搭配
not null ：非空约束，指定某列不能为空；
unique : 唯一约束，指定某列或者几列组合不能重复
default：默认约束
primary key ：主键， 第一个 非空加唯一
foreign key ：外键，主要用于参照完整性
auto_increment:自增必须设置最少(unique)自带非空只能对数字有效
设置了自增必须以一一对应的格式添加数据
如果删除了某个数据 其他不变 自带记录
delete from t1;如果有自增id,新增的数据,仍然是以删除前的最后 id 作为起始。
truncate table tl;数据量大，删除速度比上一条快，且直接从零开始,
什么是事务？MySQL如何支持事务？
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
事务的特性：
原子性
其对数据的修改，要么全部成功，要么全部都不成功。
一致性
事务开始到结束的时间段内，数据都必须保持一致状态。
隔离性
数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。
持久性
事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
Mysql实现事务
InnoDB支持事务，MyISAM不支持
# 启动事务：
# start transaction；
# update from account set money=money-100 where name='a';
# update from account set money=money+100 where name='b';
# commit；
'start transaction 手动开启事务，commit 手动关闭事务'
如何判断表与表关系
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
简述数据库设计中一对多和多对多的应用场景？
FK(一对多)
下拉框里面的数据就需要用FK关联另一张表
M2M（多对多）
多选的下拉框，或者checkbox
如何基于数据库实现商城商品计数器？
create table product
(id primary key auto_increment,
pname varchar(64),
pcount int);
单表的增删改查 与查询顺序
增
insert into 表名 (字段1,字段2) values
(内容1,内容2);
删
delete from 表名 where 条件;
改
update 表名 set 修改内容 where 查找条件;
查
where:
范围
> < = <= >= !=/<>
between and 举例 between 80 and 100
in (a,b,c) not in
模糊查询
like
%任意长度任意内容 _一个长度任意内容
regexp 正则
放正则
身份运算符 -->null
is , is not
逻辑运算符
and or not
查询步骤
select distinct 字段1 as a,字段2... from 库名.表名 (如果没有use连接到某个库,需要写上库名)
where 条件
group by 分组依据字段 与函数结合
count(id) / count(*) 计数 :每个组对应几条数据
max 求最大值: 这个组中某字段的最大值
min 求最大值: 这个组中某字段的最小值
avg 求平均值
sum 求和值
having 分组后再过滤
order by 排序 order by 字段 asc,字段 desc;
limit limit 3 limit 5,5 从索引为5的记录(第六条)开始算上这一条取5条
常见SQL（必备）
group by 分组对聚合的条件进行筛选需要通过havhing
SQL的left join 、right join、inner join之间的区别
left join (左连接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接) 返回包括右表中的所有记录1和左表中联结字段相等的记录
inner join（内连接）： 只返回两个表中联结字段相等的行
简述触发器、函数、视图、存储过程？
触发器：
对数据库某张表的增加、删除，修改前后定义一些操作
函数：(触发函数是通过select)
聚合函数：max/sum/min/avg
时间格式化：date_format
字符串拼接：concat
存储过程：
将SQL语句保存到数据库中，并命名，以后在代码调用时，直接调用名称即可
参数类型：
in 只将参数传进去
out 只拿结果
inout 既可以传，可以取
函数与存储过程区别：
本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。
视图：
视图是一个虚拟表，不是真实存在的（只能查，不能改）
左连接 ，右连接，内连接和全外连接的4者区别
基本定义：
left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。 以左表为基准 右表没的用none填充
right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join（等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。
子查询
一个查询结果集作为另一个查询的条件
MySQL索引种类
MySQL目前主要有以下几种索引类型：
1.普通索引
加速查找
2.唯一索引
加速查找 + 约束：不能重复（只能有一个空，不然就重复了）
3.主键索引
主键（primay key）：加速查找 + 约束：不能重复 + 不能为空
4.联合索引
ps：联合索引的特点：遵循最左前缀的规则
5.全文索引
将存储于数据库中的整本书或整篇文章中任意内容信息查找出来，如大量级的文字中如like %关键字%，普通索引的效率与全文索引相比是非常低的。
按存储结构分类：
1.聚簇索引(聚集索引)
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
2.辅助索引
1.表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
2.叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
3.每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
索引不是越多越好，索引越多修改时越慢，文件空间占比越大，在现实中查询多 修改少 但是我们还是一个建立一个好的习惯 不要盲目建立索引
索引在什么情况下遵循最左前缀的规则？
联合索引
主键和外键的区别？
主键是能确定一条记录的唯一标示。例如，身份证证号
外键：用于与另一张表的关联，是能确定另一张表记录的字段，用于保持数据的一致性
| 主键 | 外键 | |
|---|---|---|
| 定义 | 唯一标识一条记录，不能有重复的，不允许为空 | 表的外键是另一张表的主键，外键可以有重复的，可以为空 |
| 作用 | 用来保证数据完整性 | 用来与其他表建立联系的 |
| 个数 | 主键只能有一个 | 一个表可以有多个外键 |
MySQL常见的函数？
聚合函数
max/sum/min/avg
时间格式化
date_format
字符串拼接
concat（当拼接了null，则返回null）
截取字符串
substring
返回字节个数
length
列举 创建索引但是无法命中索引的8种情况。
1.- like '%xx'
select * from tb1 where name like '%cn';
2.- 使用函数
select * from tb1 where reverse(name) = 'wupeiqi';
3.- or
select * from tb1 where nid = 1 or email = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
4.- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where name = 999;
5.- !=
select * from tb1 where name != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
6.- >
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
7.- order by
select email from tb1 order by name desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
8.- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
如何开启慢日志查询？
修改配置文件
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
下面是开启
slow_query_log = ON
long_query_time = 2
log_queries_not_using_indexes = OFF
log_queries_not_using_indexes = ON
注：查看当前配置信息：
show variables like '%query%'
修改当前配置：
set global 变量名 = 值
数据库导入导出命令（结构+数据）？
导出现有数据库数据：（当有提示出入密码。-p就不用加密码）
mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件 路径 # 结构
导入现有数据库数据：
mysqldump -uroot -p密码 数据库名称 < 文件路径
数据库优化方案？
1.计算机硬件调优
利用数据库分区技术，均匀地把数据分布在系统的磁盘中，平衡I/O 访问，避免I/O 瓶 颈：
2.应用程序优化
确保功能的同时，尽可能少地动用数据库,简化对数据库的访问；
3.基本表设计优化
1、创建数据表时把固定长度的放在前面（）
2、将固定数据放入内存： 例如：choice字段 （django中有用到，数字1、2、3…… 对应相应内容）
3.不要把数据放在一张表上
4.数据库索引优化
1.合理建立索引
5.SQL 语句优化
1.应避免使用相关子查询。把子查询转换成联结来实现。
2.择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语 句放在前面；较弱的选择条件写在后面，
可以查看执行计划优化SQL语句
6.事务处理调优
消除不必要的封锁。
根据事务的内容将事务切分成较小的事务。事务所要求的锁越多
7、读写分离
- 实现：两台服务器同步数据
- 利用数据库的主从分离：主，用于删除、修改、更新；从，用于查；
读写分离:利用数据库的主从进行分离：主，用于删除、修改更新；从，用于查
char和varchar的区别？
char 和 varchar 的区别(char可变, varchar不可变 )
简述MySQL的执行计划？
执行计划通常是开发者优化SQL语句的第一步。MySQL在解析SQL语句时，会生成多套执行方案，然后内部会进行一个成本的计算，然后通过优化器选择一个最优的方案执行，然后根据这个方案会生成一个执行计划。开发者通过查看SQL语句的执行计划，可以直观的了解到MySQL是如何解析执行这条SQL语句的，然后再针对性的进行优化。
1000w条数据，使用limit offset 分页时，为什么越往后翻越慢？如何解决？
答案一：
先查主键，在分页。
select * from tb where id in (
select id from tb where limit 10 offset 30
)
答案二：
按照也无需求是否可以设置只让用户看200页
答案三：
记录当前页 数据ID最大值和最小值
在翻页时，根据条件先进行筛选；筛选完毕之后，再根据limit offset 查询。
select * from (select * from tb where id > 22222222) as B limit 10 offset 0
如果用户自己修改页码，也可能导致慢；此时对url种的页码进行加密（rest framework ）
什么是索引合并？
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
简单的说，索引合并，让一条sql可以使用多个索引。对这些索引取交集，并集，或者先取交集再取并集。
从而减少从数据表中取数据的次数，提高查询效率。
什么是覆盖索引？
在索引表中就能将想要的数据查询到
简述数据库读写分离？
实现：两台服务器同步数据
利用数据库的主从分离：主，用于删除、修改、更新；从，用于查；
简述数据库分库分表？（水平、垂直）
1、分库
当数据库中的表太多，将某些表分到不同数据库，例如：1W张表时
代价：连表查询跨数据库，代码变多
2、分表
水平分表：将某些列拆分到另一张表，例如：博客+博客详情
垂直分表：将某些历史信息，分到另外一张表中，例如：支付宝账单
sq注入
mysq注入
SQL注入简介：SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句 使判断条件永远为真，实现无帐号登录，甚至篡改数据库。
解决方案 让mysql帮我们拼接
python操作mysql
查询
# 光标会记录位置 取得那就会记录此时的位置
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='',
database='daycs',
charset='utf8',
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "select * from book;"
ret = cursor.execute(sql)#ret 受影响的行数
print(ret)#ret 受影响的行数
cursor.scroll(1,'absolute') #绝对移动,按照数据最开始位置往下移动1条
print(cursor.fetchmany()) #取出多条 默认取出1条
# print(cursor.fetchone()) #取出单条
# print(cursor.fetchall()) #取出所有的
cursor.scroll(3,'absolute') #绝对移动,按照数据最开始位置往下移动3条
cursor.scroll(3,'relative') #相对移动,按照当前光标位置往下移动3条
cursor.close()#关闭游标
conn.close()#关闭连接
增
# 光标会记录位置 取得那就会记录此时的位置
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='',
database='daycs',
charset='utf8',
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "insert into book values(%s,'eg','知识没有用出版社',200,'2047-7-5');"
ret = cursor.execute(sql,('独孤九'))
print(ret)#ret 受影响的行数
conn.commit()# 增删改都必须进行提交操作(commit)