mysql问题总结，面试复习神器

Q1：主键，外键，超键，候选键；

主键 对表中数据进行唯一标识的数据列的组合；不能缺失；不能空值；

外键 该列为另一表的主键；

超键 关系中能唯一标识数据的属性；

候选键 不含多余属性的超键；

 

Q2：数据库事务的四个特征及含义；

原子性 要么全部完成，要么不完成，若发生错误会进行回滚操作；

一致性 开始到结束后，数据库完整性约束没收到破坏；（实体完整性，参照完整性，用户定义的完整性）

隔离性 事务与事务之间相隔离，串行化执行；

持久性 事务完成对数据的影响是永久的；

 

Q3：视图的作用，可以更改吗；

 

视图是虚拟的表；只包含动态检索数据的查询，不包含数据；简化操作，隐藏细节，保护数据；对视图的更新会作用于基表，一般不更新；

 

Q4：drop,delete和truncate ;

drop 表级的删除；不能回滚；

truncate 清空表；不记录单行删除日志；无法恢复；只能对于TABLE操作；不能在带FOREIGN KEY约束的表（被引用的表）中使用；计数从头开始；

delete 配合where删除数据；会记录日志用于回归；会触发触发器；不减少索引和表的空间；

 

Q5：索引的工作原理和其种类；

 

索引的实现通常采用B树或B+树，加快查询速度也消耗更多空间；

唯一索引 不允许任何两行具相同值

主键索引 唯一索引的一种

聚集索引 行的物理顺序和键值的索引顺序相同

 

普通索引 无限制

全文索引 针对较大的数据生成全文索引很耗时间空间

组合索引 最左前缀原则：若对多列建立组合索引，若第二列未使用索引，则第三列也不会使用

 

InnoDB 主索引：InnoDB的数据文件本身；辅助索引：相应记录主键的值

MyISAM 索引与数据分离；辅助索引：与主索引无区别；

 

Q6：连接的种类

内部联接（等值联接） INNER JOIN

外部联接（包含在相关表中没有关联行的行） LEFT OUTER JOIN

  RIGHT OUTER JOIN

 

Q7：数据库范式

1NF 每一列都是不可分割的基本数据项，同一列无二值；无重复的域；

2NF 实例依赖于主键部分；

3NF 属性不依赖于其他非主属性；

 

Q8：存储过程与触发器的区别

 

存储过程和触发器都是SQL语句集；触发器不可用CALL调用，而是在用户执行某些语句后自动调用；

 

Q9：分表与分区

分表 真正的分表，每张表对应三个文件；提高MYSQL的并发能力；

分区 表中的数据分成多个区块；突破磁盘的读写能力；

 

Q10：数据库隔离级别

脏读 一个事务读取了另一个事务未提交的数据

不可重复读 在一次事务范围内，读取同一数据产生了不同的值

虚读 读取整体的数据后，因其他事务对数据的更新，再次查询时结果不同

串行化 3种均可避免

可重复读（默认） 避免1,2

读已提交 1

读未提交 无

 

Q11：MYSQL的两种存储引擎

MYISAM 不支持事务，不支持外键，表锁；插入数据时锁定整个表，查行数时无需整表扫描

INNODB 支持事务，外键，行锁，查表总行数时，全表扫描；

 

Q12：MYSQL索引算法

HASH 适合等值查找，不适合范围，不能排序

BTREE 适合范围查找，无hash冲突

 

Q13：聚集索引和非聚集索引

聚集索引 数据按索引顺序存储，节点存储的是真实数据

非聚集索引 节点存储的是指向真正数据的指针

 

Q14：索引的优缺点

优点 提高查询效率

缺点 降低了更新效率

 

Q15：两种存储引擎索引的区别

Innodb 主索引的数据文件本身就是索引文件；辅助索引记录主键的值；

MyISAM 主索引数据文件和索引文件分离；与主索引无区别；

 

Q16：数据库的主从复制

 

一个服务器作为主服务器，一个或多个服务器作为从服务器，主服务器将更新写到二进制日志，当一个从服务器连接到主服务器时，通知主服务器读取日志，接收从那时起发生的所有更新。解决：数据分布，负载平衡，备份，高可用性和容错性

 

基于语句 在主服务器上执行的语句，在从服务器上也执行

基于行 将改变的内容复制过去

混合类型 语句复制失败时采用行的形式

 

Q17：数据库连接池

 

为数据库连接建立一个缓冲池，防止过于大量的连接的建立与管理；

 

Q18：存储过程

 

存储过程是一些预编译的SQL语句，执行效率较高

 

Q19：乐观锁和悲观锁

乐观锁 假定不会发生并发冲突，只在提交时检查，若有其他数据更新了数据，则回滚；使用数据版本标示数据（时间戳，版本号）

悲观锁 假定会发生并发冲突，屏蔽一切破坏数据库一致性的操作，主要用于数据争用激烈的环境，以及锁成本低于回滚成本时；排他锁；

25、 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

a)、索引的目的是什么？

 

快速访问数据表中的特定信息，提高检索速度

 

创建唯一性索引，保证数据库表中每一行数据的唯一性。

 

加速表和表之间的连接

 

使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

b)、索引对数据库系统的负面影响是什么？

 

负面影响：

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

c)、为数据表建立索引的原则有哪些？

 

在最频繁使用的、用以缩小查询范围的字段上建立索引。

 

在频繁使用的、需要排序的字段上建立索引

d)、 什么情况下不宜建立索引？

 

对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

 

对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

26、 简述在MySQL数据库中MyISAM和InnoDB的区别

 

区别于其他数据库的最重要的特点就是其插件式的表存储引擎。切记：存储引擎是基于表的，而不是数据库。

 

InnoDB与MyISAM的区别：

 

InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用，是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎，已经停止开发)。

 

特点：

 

· 行锁设计、支持外键,支持事务，支持并发，锁粒度是支持mvcc得行级锁；

 

 MyISAM存储引擎: 是MySQL官方提供的存储引擎，主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。

特点：

 

不支持事务，锁粒度是支持并发插入得表级锁，支持表所和全文索引。操作速度快，不能读写操作太频繁；

 

 

SQL常用的一些关键字

1、distinct关键字

显示没有重复记录的商品名称，商品价格和商品类别列表

select distinct ware_name,price from t_ware;

2、使用计算列

查询所有商品价格提高20%后的价格

select ware_id,ware_name,price*1.2 from t_ware'

3、列的别名

a) 不使用as

select ware_id,ware_name,price*1.2 price_raise from t_ware;

b)使用as

select ware_id,ware_name,price*1.2 price_raise from t_ware;

4、使用逻辑表达式

• a)not 显示商品价格不大于100的商品

select ware_id,ware_name,price,category_id from t_ware where not price>100;

• b)or 显示商品类别编号为5或6或7的商品

select ware_id,ware_name,price,category_id from t_ware where category_id or category_id=6 or category_id=7;

• c)and 显示商品价格大于100且商品类别编号为5的商品

select ware_id,ware_name,price,category_id from t_ware where not price>100 and category_id = 5;

5、使用between关键字

显示商品价格在200元至1000元之间的商品（留心一下，是半开区间还是封闭区间？）

select ware_id,ware_name,price,category_id from t_ware where price between 200 and 1000;

6、使用in关键字

显示商品类别为5,6,7且价格不小于200元的商品

select ware_id,ware_name,price,category_id from t_ware where category_id in (5,6,7) and price>=200;

7、使用like子句进行模糊查询

• a)%（百分号）表示0到n个任意字符

select ware_id,ware_name,price,category_id from t_ware where ware_name like '%纯棉%';

• b)_（下划线）表示单个的任意字符

select ware_id,ware_name,price,category_id from t_ware where ware_name like  '%长袖_恤%';

8、转义字符escape的使用

select ware_id,ware_name,price,category_id from t_ware where ware_name like '%\%%' escape '\';

9、使用order by给数据排序

select * from t_ware_category where parent_id = 0 order by ware_id ;

select * from t_ware_category where parent_id = 0 order by ware_id asc;

select * from t_ware_category where parent_id = 0 order by ware_id desc ;

rownum

• a)查询前20条商品记录

select ware_id,ware_name,price from t_ware where rownum <= 20;

• b)查询第11条至第20条记录

select ware_id,ware_name,price from t_ware where rownum<=10 and ware_id not in(select ware_id from t_ware where rownum<=10);

10、常用统计函数

• a)sum()返回一个数字列或计算列的总和 select sum(price) from t_ware;
• b)avg()对一个数字列或计算列球平均值
• c)min()返回一个数字列或一个数字表达式的最小值
• d)max()返回一个数字列或一个数字表达式的最大值
• e)count()返回满足select语句中指定的条件的记录值

11、多表查询和笛卡尔乘积

查询商品编号，商品名称，商品价格和商品类别名称

select t_ware.ware_id, t_ware.ware_name, t_ware.price ,t_ware_category_name from t_ware, t_ware_category  where t_ware.category_id=t_ware_category.category_id;

使用join

• a)左连接

    select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware left join t_ware_category on t_ware.category_id=t_ware_category.category_id;

 

    select w.ware_id,w.ware_name,w.price,wc.category_name from t_ware w left join t_ware_category wc on w.category_id=wc.category_id;

• b) 右连接

    select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware left join t_ware_category on t_ware.category_id=t_ware_category.category_id;

12、使用union

select ware_id,ware_name from t_ware where ware_name like '%T恤%' union select ware_id,ware_name from t_ware where ware_name like '%手提包%'

13、使用group by

• a)统计每个二级类别下有多少商品，以及商品总价值

select w.category_id,wc.category_name,count(w.ware_id),sum(w.price) from t_ware w left join t_ware_category wc on w.category_id=wc.category_id group by w.category_id,wc.category_name;

• b) 统计每个一级类别下有多少商品，以及商品总价值

select wc2.category_id,wc2.category_name,sum(w.price) from t_ware w left join t_ware_category wc on w.category_id=wc.category_id left join t_ware_category wc2 on wc.parent_id=wc2.category_id group by wc2.category_id,wc2.category_name;

14、使用having对结果进行筛选

–举例子说明：查询table表查询每一个班级中年龄大于20，性别为男的人数

select COUNT(*)as '>20岁人数',classid  from Table1 where sex='男' group by classid,age having age>20 

• 需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
• 执行where子句查找符合条件的数据；
• 使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
• having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
• having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
• having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以