面试准备（数据库）

基础语句

1、数据库

1. 创建数据库：CREATE DATABASE 数据库名；
2. 删除数据库：drop database <数据库名>；
3. 选择数据库：use 数据库名

2、数据类型

大致可以分为三类：数值、日期/时间和字符串(字符)类型

1. 数值：严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)
2. 日期和时间：DATETIME、DATE、TIMESTAMP、TIME和YEAR
3. 字符串类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

3、数据库表及命令

1. 创建表： CREATE TABLE table_name (column_name column_type);
2. 删除表：DROP TABLE table_name ;
3. 创建索引：CREATE INDEX indexName ON table_name (column_name)
4. 插入数据：INSERT INTO table_name ( field1, field2,…fieldN ) VALUES
   ( value1, value2,…valueN );
5. 查询数据：SELECT column_name,column_name FROM table_name [WHERE Clause]
6. 删除数据：DELETE column_name from table_name where
7. 更新数据：UPDATE table_name SET field1=new-value1, field2=new-value2
   [WHERE Clause]
8. LIKE 子句: LIKE 子句中使用百分号 %字符来表示任意字符,_代表一个字符。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
9. 排序： SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据，ASC ， DESC 来表示升序或降序排列。 默认情况下，它是按升序排列
10. INNER JOIN（内连接）：获取两个表中字段匹配关系的记录
11. LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
12. RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

左连接：

关键字：left join on / left outer join on

语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

12. group by:分组，having：对分组后的再进行筛选
    查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
    select workaddress,count() from emp where age < 45 group by workaddress having count() >= 3;
13. MySQL UPDATE多表关联更新：
    一张表为 product 表，用来存放产品信息，其中有产品价格字段 price；另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%
    1、（Update语句方式）UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId；
    2、（通过INNER JOIN）UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
    3、（通过子查询）UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);

常见函数：

1. distinct：去重
2. sum：求和
3. avg：平均值
4. max：最大值
5. min：最小值
6. count：计数
7. FLOOR(x) 返回小于或等于 x 的最大整数
8. CEIL(x)/CEILING(x) 返回大于或等于 x 的最小整数
9. EXP(x) 返回 e 的 x 次方

1、事务

1. 事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务
2. 原子性：对于其数据修改，要么全部执行，要么全部不执行
3. 一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B树索引或双向链表）都必须是正确的
4. 隔离性：多个事务并发执行时，相互之间无影响
5. 持久性：事务完成之后，它对于系统的影响是永久性的。修改后即使出现系统故障也将一直保持
6. 数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态

2、索引

1. 索引是单独的、物理的对数据库表中一列或多列的值进行排序的一种数据结构，相当于图书上的目录，使用索引可以快速访问数据库表中的特定信息，但会使修改、插入、删除操作变慢，同时还会占用较大的磁盘空间
2. 普通索引：create index index_name on table(column)；或者创建表的时候直接指定。create table(…, index index_name column);
3. 唯一索引：类似普通索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
4. 主键索引：特殊的唯一索引，不允许为空，只能有一个，一般是在建表时指定primary key(column)
5. 组合索引：在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);
6. 什么时候需要创建索引：
   1、主键自动建立唯一索引
   2、频繁作为查询条件的字段应该创建索引
   3、查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）
   4、查询中统计或者分组的字段；
7. 什么时候不需要创建索引
   1、频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
   2、where条件里用不到的字段，不创建索引
   3、表记录太少，不需要创建索引
   4、经常增删改的表不适合创建索引
   5、数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引

3、锁

1. 锁机制是数据库一个比较重要的机制，在处理事务的并发性方面起着至关重要的作用。为了保证数据的一致性，MySQL数据库存在多种数据引擎，MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定

2. 数据库的锁有：行锁、表锁、页锁。乐观锁、悲观锁

3. 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

4. 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

5. 页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

6. 悲观锁：就是考虑问题很悲观，每次去拿数据时都认为别人会修改， 所以每次拿数据的时候都会上锁。
   实现：sql语句后边加上for update 例如：select id，name from table for update

7. 乐观锁：
   就是考虑问题很乐观，每次去拿数据的时候都认为别人不会修改， 所以不会上锁.假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。不能解决脏读问题
   实现：在表里边加一个vesion 例子：select max(nub) ,version from table
   Update table set nub=nub+1,version=version+1 where id=id and version =version

8. 什么时候会释放锁？ 提交事务或回滚事务就会释放锁