数据库基础知识

数据库 : 数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。

数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。

数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。

数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统。

数据库基本概念

元组：元组是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每一列就是一个属性。在二维表里，元组也称为行。

主码：也称为主键。主码是从候选码中选出来的。一个实体集中只能有一个主码，但是可以有多个候选码。不能重复，不允许有空。一个表只能有一个主键。

候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。

外码：也称为外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。用来和其它表建立联系用，外键是另一表的主键，外键是可以有重复的，可以有空值。一个表可以有多个外键。

ER图

E-R 图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。

数据库范式

第一范式 1NF

属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求，也就是说关系型数据库中创建的表一定满足第一范式。

2 NF

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。

函数依赖（functional dependency）：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
部分函数依赖（partial functional dependency）：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；　　也就是说学生姓名通过学号和身份证号都可以查到唯一的姓名，不唯一了，所以姓名部分函数依赖于学号和身份证号。
完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；部分函数依赖是姓名通过学号，身份证号都能单独查出，而完全依赖就是姓名的查询要依赖于学号和身份证号一起才能够查出。
传递函数依赖 ：在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

3 NF

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求。

drop、delete与truncate区别

用法不同

drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

属于不同的数据库语言

truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。

DML 语句和 DDL 语句区别：

DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作。
DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

MySQL代码

数据库操作

/* 数据库操作 */ ------------------
-- 查看当前数据库
    SELECT DATABASE();
-- 显示当前时间、用户名、数据库版本
    SELECT now(), user(), version();
-- 创建库
    CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
    数据库选项：
        CHARACTER SET charset_name
        COLLATE collation_name
-- 查看已有库
    SHOW DATABASES[ LIKE 'PATTERN']
-- 查看当前库信息
    SHOW CREATE DATABASE 数据库名
-- 修改库的选项信息
    ALTER DATABASE 库名 选项信息
-- 删除库
    DROP DATABASE[ IF EXISTS] 数据库名
        同时删除该数据库相关的目录及其目录内容

表的操作

-- 创建表
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
        每个字段必须有数据类型
        最后一个字段后不能有逗号
        TEMPORARY 临时表，会话结束时表自动消失
        对于字段的定义：
            字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']-- 查看所有表
    SHOW TABLES[ LIKE 'pattern']
    SHOW TABLES FROM  库名
-- 查看表结构
    SHOW CREATE TABLE 表名 （信息更详细）
    DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
    SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
-- 修改表
    -- 修改表本身的选项
        ALTER TABLE 表名 表的选项
        eg: ALTER TABLE 表名 ENGINE=MYISAM;
    -- 对表进行重命名
        RENAME TABLE 原表名 TO 新表名
        RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
        -- RENAME可以交换两个表名
    -- 修改表的字段机构（13.1.2. ALTER TABLE语法）
        ALTER TABLE 表名 操作名
        -- 操作名
            ADD[ COLUMN] 字段定义       -- 增加字段
                AFTER 字段名          -- 表示增加在该字段名后面
                FIRST               -- 表示增加在第一个
            ADD PRIMARY KEY(字段名)   -- 创建主键
            ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
            ADD INDEX [索引名] (字段名) -- 创建普通索引
            DROP[ COLUMN] 字段名      -- 删除字段
            MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
            CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
            DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
            DROP INDEX 索引名 -- 删除索引
            DROP FOREIGN KEY 外键    -- 删除外键
-- 删除表
    DROP TABLE[ IF EXISTS] 表名 ...
-- 清空表数据
    TRUNCATE [TABLE] 表名
-- 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名
-- 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
-- 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 优化表
    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修复表
    REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

数据操作

/* 数据操作 */ ------------------
-- 增
    INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
        -- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
        -- 可同时插入多条数据记录！
        REPLACE与INSERT类似，唯一的区别是对于匹配的行，现有行（与主键/唯一键比较）的数据会被替换，如果没有现有行，则插入新行。
    INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]
-- 查
    SELECT 字段列表 FROM 表名[ 其他子句]
        -- 可来自多个表的多个字段
        -- 其他子句可以不使用
        -- 字段列表可以用*代替，表示所有字段
-- 删
    DELETE FROM 表名[ 删除条件子句]
        没有条件子句，则会删除全部
-- 改
    UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

列属性

/* 列属性（列约束） */ ------------------
1. PRIMARY 主键
    - 能唯一标识记录的字段，可以作为主键。
    - 一个表只能有一个主键。
    - 主键具有唯一性。
    - 声明字段时，用 primary key 标识。
        也可以在字段列表之后声明
            例：create table tab ( id int, stu varchar(10), primary key (id));
    - 主键字段的值不能为null。
    - 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
        例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));
2. UNIQUE 唯一索引（唯一约束）
    使得某字段的值也不能重复。
3. NULL 约束
    null不是数据类型，是列的一个属性。
    表示当前列是否可以为null，表示什么都没有。
    null, 允许为空。默认。
    not null, 不允许为空。
    insert into tab values (null, 'val');
        -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
4. DEFAULT 默认值属性
    当前字段的默认值。
    insert into tab values (default, 'val');    -- 此时表示强制使用默认值。
    create table tab ( add_time timestamp default current_timestamp );
        -- 表示将当前时间的时间戳设为默认值。
        current_date, current_time
5. AUTO_INCREMENT 自动增长约束
    自动增长必须为索引（主键或unique）
    只能存在一个字段为自动增长。
    默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;
6. COMMENT 注释
    例：create table tab ( id int ) comment '注释内容';
7. FOREIGN KEY 外键约束
    用于限制主表与从表数据完整性。
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
        -- 将表t1的t1_id外键关联到表t2的id字段。
        -- 每个外键都有一个名字，可以通过 constraint 指定
    存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
    作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
    MySQL中，可以对InnoDB引擎使用外键约束：
    语法：
    foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
    此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。
    可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。
    如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：
    1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
    2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
    3. restrict，拒绝父表删除和更新。
    注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

MySQL面试题

关系型数据库介绍

关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

MySQL是一种关系型数据库，主要用于持久化存储我们系统中的一些数据。

MySQL基础架构

MySQL存储引擎

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

MySQL存储引擎采用的是插件式架构，支持多种存储引擎。存储引擎是基于表的，而不是数据库。

而且，还可以根据MySQL定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。

MyISAM和InnoDB的区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎。

1.是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。也就说，MyISAM 一锁就是锁住了整张表。

2.是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

4.是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

5.索引实现不一样。

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

MySQL事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

并发事务带来的问题

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。读到了修改中还未提交的数据。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。同一个数据被修改了两次，第一次修改的内容丢失。
不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。第一个事务还未完成，第二个事务修改数据，第一个事务前后读取到的内容不一致（同一个数据进行了修改）。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。第一个事务还未完成，第二个事务修改数据，第一个事务前后读取到的数据量不一致。（同一个表进行了增删）

SQL标准定义的事务隔离级别

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

MySQL锁

表级锁： MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

表级锁和行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

意向锁用来快速判断是否可以对某个表使用表锁。

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

数据库理论20题

char、varchar的区别是什么

平时使用char类型定义字段时，往往会指定其长度M，即char（M）。其实M指的是字符数，即这个字段最多存储多少个字符，M可不指定，默认为1，范围是[0，255]，单个字母、数字、中文等是占用一个字符。utf8字符集下一个中文字符占3个字节。

VARCHAR用于存储可变字符串，是最常见的字符串数据类型。它比char更节省空间，因为它仅使用必要的空间。varchar需要1或2个额外字节记录字符串长度，如果列的最大长度不大于255字节则需要1个字节。

char是定长的，根据定义的字符串长度分配足够的空间。CHAR会删除末尾空格。假如申请了char（10）的空间，无论实际存储多少内容。该字段都占用了10个字符，而varchar是变长的，也就是说申请的只是最大长度，占用的空间为实际字符长度+1。

char适合存储很短的字符串，或所有值都接近同一个长度。对于经常变更的数据，char比varchar更好，因为定长的char来存储只有一个字节，但是varchar需要两个字节，因为还有一个记录长度的额外字节。

在检索效率上来讲，char>varchar，因此在使用中，如果确定某个字段的值的长度，可以使用char，否则尽量使用varchar。

什么是关系型数据库？

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和表被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。

什么是SQL：

结构化查询语言（Structured Query Language）简称SQL。是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

什么是MySQL：

MySQL是一个关系型数据库管理系统，MySQL是最流行的关系型数据管理系统之一，常见的关系型数据库还有Oracle、SQL server、Access等等。

FOLAT和BOUBLE的区别是什么？

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

超键、候选键、主键、外键分别是什么？

超键：在关系能唯一标识元组属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称次表的外键。

超键：

在关系中能唯一标识元组的属性集称为关系模式的超键。于是我们从例子中可以发现学号是标识学生实体的唯一标识。那么该元组的超键就为学号。
除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)(学号，年龄)，这样也是超键.

候选键：

不含多余属性的超键为候选键。根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如（学号，年龄）是超键，但是它不是候选键。因为它还有了额外的属性。

主键：

用户选择的候选键作为该元组的唯一标识，那么它就为主键。简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。

外键：

外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。所以，主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

SQL约束有哪几种

NOT NULL：用于控制字段内容一定不能为空。

UNIQUE：控件字段内容不能重复，一个表允许有多个unique约束。

PRIMARY KEY：也是用于控件字段内容不能重复，但它一个表只允许出现一个。

FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表的值之一。

MySQL的四种隔离级别

未提交读（READ UNCOMMITTED）：

这个隔离级别下，其他事务可以看到本事务没有提交的部分修改，因此会造成脏读的问题（读取到了其他事务未提交的部分，而之后该事务进行了回滚）。

这个级别的性能没有足够大的优势，而且又有很多的问题，因此很少使用。

已提交读（READ COMMITTED）:

其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读问题，在同一个事务内的两次读取，拿到的结果不一样，因为另外一个事务对数据进行了修改。

可重复读（REPEATABLE READ）：

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读,当你读取 id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条 id=11 的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题。

可串行化（SERIALIZABLE）：

这是最高的隔离级别，可以解决上面提到的所有问题，因为它强制所有的操作串行执行，这会导致并发性能极速下降，因此也不是很常用。

什么是子查询

条件：一条SQL语句的查询结果作为另一条查询语句的条件或查询结果。

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

连接分为内连接、左连接、右连接

内连接是根据某个条件连接两个表共有的数据；

左连接是根据某个条件已经左边的表连接数据，右边的表没有数据的话则填null；

右连接是根据某个条件以及右边的表连接数据，左边的表没有数据的话则填null；

内连接：

内连接使用关键字Inner join on，关联条件a.a_id=b.b_id

select * from a_table a inner join b_table b on a.a_id = b.b_id;

这就说明了，内连接是组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

左连接：

左连接使用的是 left join on，关联条件和上方一样 a.a_Id=b.b_id。

select * from a_table a left join b_table b on a.a_id = b.b_id;

left join是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左（外）连接，左表的记录会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右连接：

select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

存储过程（procedure）和函数（function）区别

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

相同点：

（1）存储过程和函数都是为了可重复的执行操作数据库的SQL语句的集合。

（2）存储过程和函数都是一次编译后缓存起来，下次使用就直接命中已经编译好的sql语句，减少网络交互提高了效率。

不同点：

（1）标识符不同，函数的标识符是function，存储过程时procedure。

（2）函数返回单个值或者表对象，而存储过程没有返回值，但是可以通过OUT参数返回多个值

（3）函数限制比较多，比如不能使用临时表，只能使用表变量，一些函数都不可用等，而存储过程的限制相对就比较少；

（4）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强；

（5）函数的参数只能是IN类型，存储过程的参数可以是IN OUT INOUT三种类型；

（6）存储函数使用select调用，存储过程需要使用call调用。

关系型数据库具有哪些特点？

1.数据集中控制。在文件管理方法中，文件是分散的，每个用户或每种处理都有各自的文件，这些文件之间一般是没有联系的，因此，不能按照统一的方法来控制、维护和管理。而数据库则很好地克服了这一缺点，可以集中控制、维护和管理有关数据。

2.数据独立性高。数据库中的数据独立于应用程序，包括数据的物理独立性和逻辑独立性，给数据库的使用、调整、优化和进一步扩充提供了方便，提高了数据库应用系统的稳定性。

3.数据共享性好。数据库中的数据可以供多个用户使用，每个用户只与库中的一部分数据发生联系，用户数据可重叠，用户可以同时存取数据而互不影响，大大提高了数据库的使用效率。

4.数据冗余度小。数据库中的数据不是面向应用，而是面向系统。数据统一定义、组织和存储，集中管理，避免了不必要的数据冗余，也提高了数据的一致性。

5.数据结构化，整个数据库按一定的结构形式构成，数据在记录内部和记录类型之间相互关联，用户可通过不同的路径存取数据。

6.统一的数据保护功能，在多用户共享数据资源的情况下，对用户使用数据有严格的检查，对数据库规定密码或存取权限，拒接非法用户进入数据库，以确保数据的安全性、一致性和并发控制。

什么是存储过程以及如何调用

存储过程和视图一样，也是对SQL代码进行了封装，可以反复利用。优点同样是清晰且安全，同时还可以减少网络传输量。

和视图不一样的点在于，视图是虚拟表，不会对底层数据表直接操作，而存储过程时程序化的SQL，可以直接操作底层数据表，能够实现一些更加复杂的处理。

简单的说，存储过程是SQL语句和控制语句构成的语句结合，也可以将其理解为函数，可以接收参数，也可以返回输出参数给调用者。

CREATE PROCEDURE `add_num`(IN N INT)
BEGIN
    DECLARE i INT;--- 使用DECLARE 声明变量
    DECLARE sum INT;
    SET i = 1;--- 使用 SET 赋值语句
    SET sum = 0;
    while i<=n do
        SET sum = sum + i;
        SET i = i+1;
    END WHILE;
    SELECT sum;
END

在工作都会怎么样使用存储过程，优点是什么?

一个非常明显的优点即存储过程可以一次编译多次使用，存储过程只是在创造的时候进行编译，之后的使用就不再需要编译，这直接提升了SQL的执行效率。

另外，将代码封装成模块，这种模块化的思想可以减少很多重复的工作，减少开发工作量的同时，还能保障代码的结构清晰。

最后，存储过程的安全性较高，在设定存储过程的时候可以设置用户的使用权限。

缺点：

它的可移植性差，存储过程不能跨数据库移植，比如MySQL，Oracle和SQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。其次调试困难，只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。此外，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能导致存储过程失效。最后它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时候数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

存储过程和函数的区别

drop、truncate、delete区别

相同点：truncate和不带where子句的delete，以及drop都删除表内的数据。

不同点：

truncate会清除表数据并重置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放
truncate和delete只删除数据不删除表的结构。drop语句将删除表的结构被依赖的约(constrain),触发器(trigger),依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
速度上一般来说: drop> truncate > delete
使用上,想删除部分数据行用 delete，想删除表用 drop,想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

视图的作用

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据;视图创建后，可以使用与表相同的方式利用它们。

优点：

1.安全性。虚拟表是基于底层数据表的，我们在使用视图时，一般不会轻易通过视图对底层数据进行修改，即使是使用单表的视图，也会受到限制，比如计算字段，类型转换等是无法通过来对底层数据进行修改的，这也在一定程度上保障了数据表的安全性。同时，我们还可以针对不同用户开放不同的数据查询权限，比如人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则是不提供这个字段的。

2.简单清晰。视图是对 SQL 查询的封装，它可以将原本复杂的 SQL 查询简化，在编写好查询之后，我们就可以直接重用它而不必要知道基本的查询细节。同时我们还可以在视图之上再嵌套视图。这样就好比我们在进行模块化编程一样，不仅结构清晰，还提升了代码的复用率。

非关系型数据库和关系型数据库区别，优势比较？

（1）关系型数据库

优点：二维表格，容易理解。容易操作。易于维护。支持SQL。

缺点：读写性能比较差。固定的表结构，不够灵活。应对高并发场景，磁盘I/O存在瓶颈。海量数据读写性能差。

（2）非关系型数据库

优点：不需要SQL解析，读写性能高。可以使用内存或硬盘作为载体，速度快。基于键值对，数据没有耦合性，方便扩展。部署简单。

缺点：不支持SQL，增加了学习成本。没有事务。

MySQL中in和exists的区别

mysql中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率高，这种说法其实是不准确的。这是要区分环境的。

1.如果查询的两个表大小相当，那么用in和exists差别不大。

2.如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询小的用in。

3.not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快

触发器

什么是触发器：

触发器是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表时间相关的特殊存储过程，它的执行不是有程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。简单理解为：你执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句。

作用：

1.可以写入数据表前，强制检验或转换数据；

2.触发器发生错误时，异动的结果会被撤销；

3.部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器

4.可依照特定的情况，替换异动的指令（INSTEAD OF）

触发器四要素：

1.监视地点 table

2.监视事件 insert、update、delete

3.触发时间 after、before

4.触发事件 insert、update、delete

基本语法：

before/after： 触发器是在增删改之前执行，还是之后执行
delete/insert/update： 触发器由哪些行为触发（增、删、改）
on 表名： 触发器监视哪张表的（增、删、改）操作
触发SQL代码块： 执行触发器包含的SQL语句

注意： 触发器也是存储过程程序的一种，而触发器内部的执行SQL语句是可以多行操作的，所以在MySQL的存储过程程序中，要定义结束符

标签：存储,--,数据库,事务,MySQL,数据,主键
From： https://www.cnblogs.com/baifeili/p/16468734.html

MySQL