mysql面试题总结

一.概念

1.事务

(1)什么是事物？

将一组增删改查看成一个执行单元，要么全成功，要么有一个失败，数据库就会回滚，所有操作都不生效

(2)事务的特性？

• ①原子性：将一组增删改看成一个整体，要不全成功，要不有一个失败，数据库就回滚，所有操作都不生效

• ②一致性：事务开始前的状态和事务执行后的状态保持一致

• ③隔离性：当多个用户并发访问数据库时，线程会为用户开启一个事务，事务与事务相互隔离，互不干扰

• ④持久性：一旦事务执行生效会永久运用到数据库，即使设备故障也不影响

(3)不考虑隔离性导致的问题

• ①脏读：当事务A访问了事务B未提交的数据，并使用了它
• ②幻读：当事务A访问了数据表，事务B修改或删除了数据，导致事务A读取与实际不符合，类似出现幻觉
• ③不可重复读：当事务A多次访问同一条数据，事务B修改了它，导致事务A两次读取的数据不一样

(4)四个隔离级别

• ①Read uncommitted：读未提交，最低隔离级别，可能会出现所有情况
• ②Read committed：读提交，可以避免幻读
• ③Repeatable read：可重复读，可以避免幻读和不可重复读
• ④Serialzable：串行化，可以避免脏读、幻读和不可重复读。

(5)事务的操作

• 开启事务：begin
• 提交事务：commit
• 回滚事务：rollback

2.重要的MySQL引擎

①MyISAM：不支持事务，查询速度相对较慢，将数据储存在三个文件，一个放索引，一个放数据，一个放表结构
②InnoDB：支持事务，查询速度相对较快，将数据储存在两个文件中，一个放表结构，一个放数据和索引

3.常用的数据类型

int	存储整数类型，默认存储十一位
char	存储定长字符串，无论字符串长度是否够长，都占用规定大小
varchar	存储变长字符串，大小随字符串长度变化
datetime	存储日期类型 H:H:H S:S:S
decimal	存储小数，两个参数，第一个参数为长度，第二个参数为几位小数

4.字段约束

unsigned	约定字符是有符号还是无符号
primary	主键约束
foreign	外键约束
unique	唯一约束
int(4）	最大宽度
not null	不能为空
auto_increment	自增

5.常见的索引

primary key	唯一索引，不可重复且不能为空
unique	唯一索引，不可重复可以为空
index	普通索引，可以为空可以重复
多列索引	可以为空可以重复

6.视图

（1）定义：是一张虚拟的表，储存着复杂的查询语句

（2）优点：

• ①简单：存储着复杂的sql语句，使用时可以直接调用视图
• ②安全:数据具有对库和表的权限管理，但是没有对字段的管理权限，使用视图可以实现权限管理
• ③数据独立性：视图基于源表，源表的结构改变不会影响视图

（3）缺点：

• ①维护和管理成本增大，对数据迁移有很大影响
• ②如果存储简单的查询语句，视图会按照复杂的查询语句执行，会降低执行效率

（4）添加视图：

crate view 视图名 as(查询语句);

（5）查看所有视图：

show table status where comment='view';

（6）查看视图表：

select * from 视图名;

（7）删除视图：

drop view 视图名;

7.触发器

（1）定义：

触发器(trigger)也叫触发程序，触发器是一种特殊的存储过程，但是触发器不需要语句调用，也不需要手动启动，通过有增删改事件触发，当指定表发生指定动作，将**触发器。

（2）添加触发器语句：

create trigger 触发器名字 触发时间 触发事件 on 表名 for each row begin 触发后的事件 end;

（3）查看所有触发器：

select * from information_schema.triggers/G;

（4）删除触发器：

drop trigger 触发器名字;

8.Mysql优化:

为什么要优化？

一个应用吞吐量瓶颈往往出现在数据库的处理速度上，随着应用程序的使用和业务的拓展，数据库数据量逐渐增多，数据库处理压力逐渐增大，关系型数据库数据存放在磁盘上的，读写速度较慢（与内存中的数据相比）

优化方式：

• 设计表的时候严格根据数据库的设计范式来设计数据库;
• select 后尽量不使用*
• 尽量不使用嵌套查询，使用连接查询或者where查询
• sql关键词尽量使用大写
• 尽量使用逻辑外交不使用物理外键
• 给查询频繁的字段添加索引，并且遵循最左原则（尽量将首选关键字段放在最前边）
• 垂直分库分表：把一些不经常读的数据或者结果复杂的表拆分成多张表，较少磁盘I/O操作;
• 水平分库分表：于数据量庞大的表，使用水平分库分表
• 使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘IO;
• 优化硬件;
• 主从分离读写;采用主从复制把数据库的读操作和写入操作分离开来;

9.索引：

（1）定义：一种高效获取数据的存储结构，类似于子字典的目录
（2）优点：提升数据的查询速度，减少io操作
（3）缺点：维护成本高，会降低其他操作的执行速度

（4）添加索引：

alter table 表名 add 索引类型 索引名(字段名);

（5）查看所有：

show index from 表名;

（6）删除主键索引：

alter table 表名 drop primary key;

（7）删除索引：

drop index 索引的名字 on 表名;

---

二.库的操作

（1)进入数据库：

mysql -u用户名 -p密码 -h主机地址 -P端口号

（2)创数据库：

create database 库名;

（3)使用数据库：

use 库名;

(4)查看所有数据库：

show databases;

(5)删除数据库：

drop database 库名;

三.数据库的表

(1)创建表：

create table 表名(
	字段名1 约束，
	字段名2 约束，
	)charset=utf8;

(2)查看所有表：

show tables;

(3)查看建表语句：

show create table 表名\G;

(4)删表:

drop table 表名;

(5)表结构的操作：

①添加字段:

alter table 表名 add 字段名 约束;

②删除字段:

alter table 表名 drop 字段名;

③修改字段:

alter table 表名 modify 字段名 约束;
alter table 表名 change 旧字段名 新字段名 约束;(不改名则写两次旧字段名)

④修改表名

alter table 旧表名 rename as 新表名;

⑤修改编码类型：

alter table 表名 charset=utf8;

⑥修改自增值：

alter table students auto_increment=新值;（修改的自增值只能比原数据最大的自增值大）	

四.数据的操作

（1）添加数据

insert into 表名 values(按字段顺序依次写数据1),(按字段顺序依次写数据2);
insert into 表名(指定字段) values(按指定字段顺序依次写数据1),(按指定字段顺序依次写数据2);

（2）更新数据

update 表名 set 字段名=新数据 where 条件;

（3）删除数据

delete from 表名 where 条件;

（4）基本查询数据

------------------------------------where条件查询
select * from 表名 where 条件;

--------------------------------------分组 group by having
例如：select id from 表名 group by id;
select id from 表名 group by id having id>5;(分组和再筛选最后返回数据）
select id from 表名 where id>5 group by id having id>10;(先where筛选再分组再having筛选最后返回数据)

------------------------------------排序 desc（降序） asc（默认，升序）
select * from 表名 order by 排序条件 desc;

-------------------------------------------limit 分页
select * from 表名 limit 3;(只显示三个数据)
select * from 表名 limit 3,4;(跳过三个查看四个数据)

--------------------------------------------聚合函数
max最大值:select max（age） from 表名;
min最小值:select min（age） from 表名;
avg平均值:select avg（age） from 表名;
count计数:select age,count(age) from 表名 group by age;

（5） 运算符：

①and:select * from 表名 where 条件一 and 条件二;
②or :select * from 表名 where 条件一 and 条件二;
③in :select * from 表名 where 字段 in （选项1，选项2，选项3..）;
④not:select * from 表名 where 字段 not in （选项1，选项2，选项3..）;
⑤=  :select * from 表名 where 字段=某值;	

---

五.关系查询

（1）嵌套查询

select * from 表1 where id in（select id from 表2）;

（2）where关联

select * from 表1,表2 where 表1.id=表2.id;

（3）连接查询

内连接
select * from 表1 inner join 表2 on 表1.id=表2.id;（以共同条件为准）

左连接
select * from 表1 left join 表2 on 表1.id=表2.id;（已左表为准）

右链接
select * from 表1 right join 表2 on 表1.id=表2.id;（已左表为准）

​

六.表的关系

（1）一对一

​ 一张表的一条数据对应另一张表的一条数据

（2）一对多

​ 一张表的一条数据对应另一个表的多条数据（例如班级学生）

（3）多对多

​ 一张表的多条数据对应另一个表的多条数据

七.数据的备份

（1）备份库:

mysqldump -u root -p 库名 > 备份的库名.sql

（2）备份表:

mysqldump -u root -p 库名 表名 > 备份的表名.sql

（3）导入表/库:

mysql -uroot -p 库名 < 备份的文件.sql

（导入库需要先创建输入库，因为实际是导入表，必须重新创入个新的数据库，然后再导入）

八.创建mysql用户，添加权限

（1）创建用户：

create user '用户名'@'ip地址' identified by '密码';

（2）授权：

grant all/select/insert/delete on 库名.表名 to 用户名@主机地址;

（3）删除用户：

drop user 用户名@主机地址;

（4）修改密码：

update user set authentication_string=password('新密码') where user=‘用户名’ and host=‘主机地址’;

九.pymysql连接数据库

步骤：

• 1.导入pymysql包
• 2.连接数据库
• 3.创建游标
• 4.创建sql语句，并且执行
• 5.若是增删改需要提交
• 6.关闭游标和数据库

import pymysql
#1.连接数据库
db=pymysql.connect(host='localhost',port='3306',user='root',password='123456',database='news')
#2.创建游标
cursor=db.cursor()

#3.写sql语句
sql1='sql增删改语句'

#4.执行sql1语句
cursor.execute(sql)

##5.若是增删改都需要保存命令
db.commit()
sql2='sql查询语句'
cursor.execute(sql2)#执行sql2语句
print(cursor.fetchone())  # 只获取一个数据，并且还是个生成器
print(cursor.fetchall())  # 会获取所有数据

#6.关闭游标和数据库
cursor.close()
db.close()

十.orm 了解概念

​ ORM，全称Object Relational Mapping，意为对象关系映射。

• ①ORM是一个操作数据库的框架

• ②ORM会将python代码翻译成对应数据库的sql语句

• ③ORM会将数据库中的表，映射成python中的类

• ④一张表就是一个类，将表中的字段映射成对象的属性。

优点：可以降低数据库和程序之间的耦合，专注于业务逻辑即可

缺点：ORM生成的sql语句，不是最优的sql语句，执行效率会比较低。