【mysql】-常见面试题汇总

面试问题

排序的规则？

• 排序采用order by子句，order by后面跟上排序字段，排序字段后面可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句，那么order by必须放在where 语句后面。
• 升序 asc
• 降序 desc

函数的分类？经常使用的函数？

• 数据处理函数

1. lower
2. upper
3. substr
4. length
5. trim(去首尾空格，不会去除中间的空格)
6. str_to_date(%Y-%m-%d)
7. date_format
8. format(保留小数)
9. round
10. rand()随机数
11. ifnull（如果为空，则替换为0）

• 聚合函数/分组函数
• 分组函数自动忽略空值

12. count
13. sum
14. avg
15. min
16. max

分组查询注意条件？

• 如果使用了order by，order by 必须放到group by后面。
• 在sql语句中，select语句后面只能跟分组函数+参与分组的字段。
• 如果想要对分组数据再进行过滤需要使用having子句。

limit使用方法？

select * from emp limit m,n;

• 从m+1开始，展示n条数据

创建表时有什么约束条件？

• 字段名
• 字段数据类型
• 字段长度限制
• 字段约束

mysql常见数据类型？

• char:定长字符串，适合做主键或者外键
• varchar：可变长字符串
• double\float
• int\bigint
• date

增加删除修改表结构

• 使用alter table，不影响表中的数据。

 alter table 表名 add 字段名 数据类型(长度) --添加字段
 alter table 表名 modify 字段名 数据类型(长度) --修改字段长度
 alter table 表名 change 原字段名 现在字段名 数据类型(长度) --修改字段名称
 alter table 表名 drop 字段名 --删除字段

常见约束？constraint

• 非空：not null
• 唯一约束：unique
• 主键约束： primary key
• 外键约束： foreign key

constraint	外键名称 foreign key(当前表字段) references 连接表(连接表字段)	  

主键、外键的区别？

• 主键：唯一标识一条记录，不能有重复，不能为空。
• 外键：表的外键是另一个表的主键，外键可以为空，可以重复。

mysql和oracle的区别？

• oralce的语法更严谨
• mysql 特有的存储引擎，oracle等其他数据库没有。

• 什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

char和varchar的区别？

• char是一种固定长度的类型：

1. 身份证号、手机号、MD5值
2. 长度较小的

• varchar则是一种可变长度的类型：

3. 文本框
4. 字符串很少被更新的，容易产生存储碎片

存储时期？

• date：（生日）占用得字节数比使用字符串.datatime.int储存要少，使用date只需要3个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算
• datetime：以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用8个字节得存储空间，datatime类型与时区无关
• Timestamp:以时间戳格式存储，占用4个字节，范围小1970-1-1到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp列得值

drop,delete与truncate的区别？

• drop直接删掉表和表的结构。
• truncate删除表中数据，再插入时自增长id又从1开始 。不删除表结构。
• delete删除表中数据，可以加where字句。
• 速度，一般来说: drop> truncate > delete
• delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

左连接和右连接的区别。

• 左连接能查寻到的数据，右连接同样也可以完成。
• 左连接是显示左表的所有数据，没有匹配的以null值显示。
• 右连接相反。

数据库设计三范式？

• 第一范式：每个字段不可再分，主键不能重复。
• 第二范式：非主键字段完全依赖主键字段。主键可以完全代表整行数据。
• 第三范式：非主键字段不能传递依赖主键字段，一对多时在多个一方添加外键，不能将部门表的字段添加到员工表中，应该用外键关联部门表，消除数据冗余。

数据库安全

了解XSS攻击吗？如何防止？

• XSS攻击全称跨站脚本攻击，XSS是一种在web应用中的计算机安全漏洞，它允许恶意web用户将代码植入到提供给其它用户使用的页面中，扫描到javascript的内容，进行脚本添加攻击。
• XSS攻击的危害包括
  1、盗取各类用户帐号，如机器登录帐号、用户网银帐号、各类管理员帐号
  2、控制企业数据，包括读取、篡改、添加、删除企业敏感数据的能力
• 基于代码修改的防御

1. 对用户提交的内容进行可靠的输入验证，限制提交长度或者格式。其他一律过滤。
2. session标记。
3. 去掉css和JavaScript的引用内容。

SQL注入漏洞产生的原因？如何防止？

• 原因：通过设定固定的参数来传参给服务器，查询出一些重要信息，主要原因是没有细致的过滤用户输入的主要内容，导致非法数据侵入系统。
• 产生的方式：

1. 通过拼接or 1=1 等语句获取信息。
2. 通过表单动态拼接sql

• 如何预防：

3. 对表单进行验证。
4. 对单引号和双引号进行格式转换。
5. 对用户名密码进行加密。
6. 不使用管理员权限来连接系统，每个应用复制的表建立自己的权限。
7. 项目上线前进行sql注入检测，通过检测软件或者平台。

事务（Transaction）

什么是事务？

• 一个最小的不可再分的工作单元
• 通常一个事务对应一个完整的业务
• 而一个完整的业务需要批量的DML语句共同完成
• 事务只和DML语句有关

数据库事务的特性。

• 原子性：整个数据库的操作，要么全部完成，要么全部不完成。不可能停留在中间某个环节，事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有被执行过的状态。
• 一致性：在事务的开始之前，和事务的结束之后，数据库的完整性约束没有发生破坏。
• 隔离性：同一时间只有一个请求请求同一数据库。
• 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务的重点

1. 在事务进行过程中，未结束之前，DML语句是不会修改底层数据库文件中的数据的。
2. 只是将历史纪录一下，在内存中完成记录。
3. 只有在事务结束的，而且是成功结束的时候才会修改底层硬盘文件中的数据。

设置手动提交

默认自动提交是no，将autocommit的值设置为off。

索引

索引的目的是什么？

1. 快速访问数据表中的特定信息，提高检索速度
2. 创建唯一性索引，保证数据库表中每一行数据的唯一性。
3. 加速表和表之间的连接
4. 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

什么时候使用索引？

• 表中该字段中的数据量庞大
• 经常被检索，经常出现在where子句中的字段
• 经常被DML操作的字段不建议添加索引

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

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

索引的使用

8. 主键和外键自动添加索引，所以能通过主键查询的尽量通过主键查询。

create index 索引名 on 表名 (字段名);//创建索引
show index from 表名;//查看索引

sql使用过程和优化小方法。

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择，因为in不走索引。

什么是锁？

答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

基本锁类型：锁包括行级锁和表级锁

存储引擎

mysql的九种存储引擎

• 其中三种比较重要
• 查看当前存储引擎为： show engings\G
• MyISAM：可压缩，设置为只读，节省空间。
• INnoDB（默认使用）：支持事务，外键的完整性，级联删除，级联更新。
• MEMORY(记忆)：数据存储在内存中，时临时的，数据查询非常快。

如何选择存储引擎？

• 大量的读少量更新：Myisam。
• InnoDB:支持事务和并发。
• Memory：非永久需要的数据。

视图（view）

如何创建一个视图？

create view 视图名 as 查询语句

视图有什么作用？

1. 隐藏表的细节。（限制功能，保证数据安全，防止程序员对数据胡乱操作）
2. 提高检索效率。（将经常查询的语句建立视图，下次查询时，直接查询视图）