数据库知识整理

常见面试题

• sql:count(1)和count(*)的区别
• 增删改查：从表中筛选id 1-10的数字倒序排列
• 怎么检查主从库数据不一致
• 怎么锁表，备份， 怎么查master状态
• 怎么恢复备份文件
• 怎么做主从同步
• 怎么终止主从同步中阻塞的进程
• 前端一次查询的数据量太大怎么做
• 海量文件中查找有关键词的文件
  －－－－－－－－－－－－－－－－－－－－－－－

1 请你写一下mysql删除语句

delete from table_name where;
删除表中所有记录，但保留结构
delete * from table_name;（每次删除一行，并在事务日志中为所删除的每行记录一项）
truncate table table_name; (更快，释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放)
删除数据库\删除表
drop table table_name;
drop database database_name;

2 请你写一些基本的SQL语句

1. 选择

select * from table;
select col1,col2,.. from table;

2. 去重

select distinct col1,.. from table;

3. where提取满足条件的记录

select col1 from table where col2 operator value;
operator有：
=,>,<,<>(不等于),between,like(模式),in(多值)
SELECT * FROM Websites 
WHERE alexa > 15 AND (country='CN' OR country='USA');

4. 排序
   order by　对一个或多个列进行排序，默认升序，可用desc降序

select * from table order by col1,[col2,..] [asc/desc]

5. 插入

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
insert into table_name values (val1,val2,...);
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

6. 更新

update table_name set col1=value1, col2=value2 where 限制条件;

7. 删除

delete from table where 限制条件;

3 请你写两个sql语句:统计XX人数、选出课程编号不为XX的学生学号

select sClass 班级,count(*) 班级学生总人数,

SELECT 学号,
FROM S
WHERE NOT EXISTS 
(SELECT * FROM SC
WHERE SC.课程号 = 'XX' AND S.学号 = SC.学号)；

4 某个表格中有10条一模一样的数据，现在要删掉其中的9条，请你写一下sql语句

delete * from table_name limit 9；

5 某个表格存着s_name subject score 三个字段，比如某一行是 张三 数学 76，现在要选取出所有科目成绩都大于80分的学生名字，请写出sql语句

反着推

select s_name 
from table_name 
where s_name not in 
(select s_name from table_name where score <＝80)

6 写出sql语句：数据库统计总成绩取前十名的学生

select student_name.student_grade 
from table_name 
order by student_grade desc limit 10;

SELECT * FROM (
select T.*,ROW_NUMBER()OVER(PARTITION BY 班级 order by 成绩 desc) RN
FROM T
)WHERE RN<=10

7 请问count和sum的区别，以及count(*)和count(列名)的区别

• COUNT() 函数返回匹配指定条件的行数; SUM() 函数返回数值列的总和。
• COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：
• COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) AS nums FROM access_log;
SELECT SUM(column_name) FROM table_name;

8 join作用, SQL左连接以及使用场景

• JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。使用场景：可以保持左表完整加入另一表中的数据
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行。如果右表的某行在左表中没有匹配行，则在相关联的结果集行中左表的所有选择列表列均为空值。
• FULL JOIN：只要其中一个表中存在匹配，则返回行

内连接
SELECT column_name(s)
FROM table1
INNER JOIN（JOIN） table2
ON table1.column_name=table2.column_name;

9. 什么是数据库事务, 事务的隔离级别, 以及你一般使用的事务是哪种

• 数据库事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的：
  1.为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  2.当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
• 当事务被提交给了DBMS（数据库管理系统），则DBMS（数据库管理系统）需要确保该事务中的**所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态;**同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。
• 数据库事务拥有以下四个特性，被称之为ACID特性：
  原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
  数据库的事务：

事务的隔离性及时同一时间只允许一个事务请求同一数据，不同事物之间彼此没有任何干扰，
事务隔离级别如下：

10. 数据库mysql中CHAR和VCHAR的区别

1、char(n)类型
char类型是定长的类型，即当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字节，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL是自动删除输入字符串末尾的空格。 char是适合存储很短的，一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要。

2、varchar(n)类型

varchar(n)类型用于存储可变长的，长度为n个字节的可变长度且非Unicode的字符数据。n必须是介于1和65535之间的数值，存储大小为输入数据的字节的实际长度+1/2. 比如varchar(10), 然后输入abc三个字符，那么实际存储大小为3个字节。除此之外，varchar还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。取数据的时候，不需要去掉多余的空格。

11. 主键与外键的区别？

• 主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。
  身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
• 外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

12 请问什么是脏读、幻读、不重复读

• 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，但是还没有来得及提交到数据库中，这时，另一个事务也访问这个数据，然后使用了这个数据。A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
• 不可重复读（前后多次读取，数据内容不一致）。事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
• **幻读（前后多次读取，数据总量不一致）**事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

13 请你说说redis

redis是一个key-value存储系统。和Memcached类似，它支持存储的value类型相对更多，包括string(字符串)、list(链表)、set(集合)、zset(sorted set --有序集合)和hash（哈希类型）。这些数据类型都支持push/pop、add/remove及取交集并集和差集及更丰富的操作，而且这些操作都是原子性的。在此基础上，redis支持各种不同方式的排序。与memcached一样，为了保证效率，数据都是缓存在内存中。区别的是redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了master-slave(主从)同步。

Redis是一个开源的使用ANSI C语言编写、遵守BSD协议、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库，并提供多种语言的API。

它通常被称为数据结构服务器，因为值（value）可以是 字符串(String), 哈希(Hash), 列表(list), 集合(sets) 和 有序集合(sorted sets)等类型。

14 请你说一说数据库中的聚类查询

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。

15 请问如何对数据库作优化

1、调整数据结构的设计，对于经常访问的数据库表建立索引
2、调整SQL语句， ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。
3、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。
4、调整硬盘Ｉ／Ｏ，DBA可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间Ｉ／Ｏ负载均衡。

16 请你说一下数据库连接泄露的含义

数据库连接泄露指的是如果在某次使用或者某段程序中没有正确地关闭Connection、Statement和ResultSet资源，那么每次执行都会留下一些没有关闭的连接，这些连接失去了引用而不能得到重新使用，因此就造成了数据库连接的泄漏。数据库连接的资源是宝贵而且是有限的，如果在某段使用频率很高的代码中出现这种泄漏，那么数据库连接资源将被耗尽，影响系统的正常运转。

17 请问如果mysql中用户密码丢了怎么办；建一个数据库表，授权命令是什么

• 对于普通用户的密码丢失，直接用root超级管理员登录修改密码即可
• 若是root密码丢失，可通过mysqlld_saft方式找回

1、停止mysql：service mysqld stop
2、安全模式启动：mysql_safe-skip-grant-tables&
3、无密码回车键登录：mysql –uroot –p;
4、重置密码：use mysql update user set password=password(“)
where user=’root’ and host=’localhost’;flush privileges
5、正常启动：service mysql restart
6、再使用mysqladmin：mysqladmin password ‘123456’

Create database demodb default character set utf8 collate utf8_general_ci;
授权
Grant all privileges on demodb. * [用户名称]@’%’
立即启动修改
Flush privileges

18 请你写一下SQL语句的多表查询

按照department_id 查询 employees(员工表)和 departments(部门表) 的信息。

SELECT e.last_name,e.department_id,d.department_name 
FROM employees e,departments d 
where e.department_id = d.department_id