SQL基础(二)

一、JOIN

下文将使用两个数据库表 table_A和 table_B 来进行示例讲解，其结构与数据分别如下：

table_A:

table_B:

其中 pk为 1 的记录在 table_A和 table_B 中都有，2 为 table_A特有，3 为 table_B 特有。

常用的JOIN

1. INNER JOIN

INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
INNER JOIN table_B B
ON A.pk = B.pk;

查询结果为:

2. LEFT JOIN

LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN。左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
LEFT JOIN table_B B
ON A.pk = B.pk;

查询结果为:

3. RIGHT JOIN

RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN。右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
RIGHT JOIN table_B B
ON A.pk = B.pk;

查询结果为：

4. FULL OUTER JOIN

FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
FULL OUTER JOIN table_B B
ON A.pk = B.pk;

查询结果为:

当前示例使用的 MySQL 不支持 FULL OUTER JOIN。

应当返回的结果（使用 UNION 模拟）：

延伸JOIN

1. LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表没有关联数据的记录集。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
LEFT JOIN table_B B
ON A.pk = B.pk
WHERE B.pk IS NULL;

查询结果为:

2. RIGHT JOIN EXCLUDING INNER JOIN

返回右表有但左表没有关联数据的记录集。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
RIGHT JOIN table_B B
ON A.pk = B.pk
WHERE A.pk IS NULL;

查询结果为:

3. FULL OUTER JOIN EXCLUDING INNER JOIN

返回左表和右表里没有相互关联的记录集。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
FULL OUTER JOIN table_B B
ON A.pk = B.pk
WHERE A.pk IS NULL
OR B.pk IS NULL;

因为使用到了 FULL OUTER JOIN，MySQL 在执行该查询时再次报错。

应当返回的结果（用 UNION 模拟):

SQL的七种用法:

其他JOIN

1. CROSS JOIN

返回左表与右表之间符合条件的记录的迪卡尔集。

SELECT A.pk AS A_pk, B.pk AS B_pk,
       A.value AS A_value, B.value AS B_value
FROM table_A A
CROSS JOIN table_B B;

查询结果为:

上面几种 JOIN 查询的结果都可以用 CROSS JOIN 加条件模拟出来，比如 INNER JOIN 对应 CROSS JOIN ... WHERE A.PK = B.PK。

以上参考自https://mazhuang.org/2017/09/11/joins-in-sql/

二、SQL约束(Constraints)

1. 非空 (NOT NULL)

在默认的情况下,表的列接受 NULL 值.

NOT NULL 约束强制列不接受 NULL 值.

NOT NULL 约束强制字段始终包含值,这意味着如果不向字段添加值,就无法插入新纪录或者更新记录.

下面SQL强制id列,name列,age列不接受 NULL 值.

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int
);

添加NOT NULL约束

在已创建的表的scores字段中添加 NOT NULL 约束.

ALTER TABLE student 
MODIFY scores int NOT NULL;

删除NOT NULL约束
在已创建的表的scores字段中删除 NOT NULL 约束.

ALTER TABLE student 
MODIFY scores int NULL;

2. 唯一 (UNIQUE)

UNIQUE 约束唯一标识数据库表中的每条记录.

UNIQUE 和 PRIMARY KEY 约束均为列或者列集合提供了唯一性的保证.

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束.

注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

创建表时添加 UNIQUE 约束

MySQL:

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
UNIQUE (id)
);

SQL Server / Oracle / MS Access：

CREATE TABLE student (
id int NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int
);

如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束

格式:CONSTRAINT 命名 UNIQUE (字段)

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
CONSTRAINT uc_id UNIQUE (id,name)
);

uc_id中,uc就是UNIQUE CONSTRAINT的缩写,意思是唯一约束

表已存在时添加 UNIQUE 约束

当表已被创建时,在id列上创建 UNIQUE 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE student
ADD UNIQUE (id);

如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束

ALTER TABLE student
ADD CONSTRAINT uc_id UNIQUE (id,name);

撤销 UNIQUE 约束

MySQL:

ALTER TABLE student
DROP INDEX uc_id;

SQL Server / Oracle / MS Access：

ALTER TABLE student
DROP CONSTRAINT uc_id;

3. 主键 (PRIMARY KEY)

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

创建表时添加 PRIMARY KEY约束

MySQL:

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
PRIMARY KEY (id)
);

SQL Server / Oracle / MS Access：

CREATE TABLE student (
id int NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int
);

命名 PRIMARY KEY约束

格式:CONSTRAINT 命名 PRIMARY KEY(字段)

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
CONSTRAINT pk_id PRIMARY KEY (id)
);

pk_id中,pk就是PRIMARY KEY的缩写,意思是主键约束

表已存在时添加 PRIMARY KEY约束

当表已被创建时,在id列上创建 PRIMARY KEY约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE student
ADD PRIMARY KEY (id);

命名 PRIMARY KEY约束

ALTER TABLE student
ADD CONSTRAINT pk_id PRIMARY KEY (id);

撤销 PRIMARY KEY约束

MySQL:

ALTER TABLE student
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access：

ALTER TABLE student
DROP CONSTRAINT pk_id;

4. 外键 (FOREIGN KEY)

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

让我们通过一个实例来解释外键,请看下面两个表

student表:

family表:

family表中的s_id列指向student表中的id列.

student表中的id列是student表中的 PRIMARY KEY。

family表中的s_id列是family表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

创建表时添加 FOREIGN KEY约束

MySQL:

CREATE TABLE family (
id int NOT NULL,
father VARCHAR(255) NOT NULL,
mother VARCHAR(255) NOT NULL,
s_id int,
PRIMARY KEY (id),
FOREIGN KEY (s_id) REFERENCES student(id)
);

SQL Server / Oracle / MS Access：

CREATE TABLE family (
id int NOT NULL PRIMARY KEY,
father VARCHAR(255) NOT NULL,
mother VARCHAR(255) NOT NULL,
s_id int FOREIGN KEY REFERENCES student(id)
);

命名 FOREIGN KEY约束

格式:CONSTRAINT 命名 FOREIGN KEY (表1的字段) REFERENCES 表2名 (表2的唯一约束键)

CREATE TABLE family (
id int NOT NULL PRIMARY KEY,
father VARCHAR(255) NOT NULL,
mother VARCHAR(255) NOT NULL,
s_id int,
CONSTRAINT fk_id FOREIGN KEY (s_id) REFERENCES student(id)
);

fk_id中,fk就是FOREIGN KEY的缩写,意思是外键约束

表已存在时添加 FOREIGN KEY约束

当表已被创建时,在id列上创建 FOREIGN KEY约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE family
ADD FOREIGN KEY (s_id)
REFERENCES student(id);

命名 FOREIGN KEY 约束

ALTER TABLE family
ADD CONSTRAINT fk_id 
FOREIGN  KEY (s_id)
REFERENCES student(id);

撤销 UNIQUE 约束

MySQL:

ALTER TABLE family
DROP FOREIGN KEY fk_id;

SQL Server / Oracle / MS Access：

ALTER TABLE family
DROP CONSTRAINT fk_id;

5. 检查 (CHECK)

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

创建表时添加 CHECK约束

下面的 SQL 在 student 表创建时在 id 列上创建 CHECK 约束。CHECK 约束规定 age列必须只包含大于 0 的整数。

MySQL:

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
CHECK (age > 0)
);

SQL Server / Oracle / MS Access：

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL CHECK (age > 0),
scores int
);

如需命名 CHECK 约束,并定义多个列的 CHECK 约束

格式:CONSTRAINT 命名 CHECK (条件)

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int,
CONSTRAINT chk_student CHECK (age>0 AND scores>0)
);

chk_student中,chk就是CHECK的缩写,意思是检查约束

表已存在时添加 CHECK 约束

当表已被创建时,在age列上创建 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE student
ADD UNIQUE (age>0);

如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束

ALTER TABLE student
ADD CONSTRAINT chk_student CHECK (age>0 AND scores>0);

撤销 CHECK约束

MySQL:

ALTER TABLE student
DROP CHECK  chk_student;

SQL Server / Oracle / MS Access：

ALTER TABLE student
DROP CONSTRAINT chk_student;

6. 默认 (DEFAULT)

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

创建表时添加 DEFAULT约束

在 student 表创建时在 “scores” 列上创建 DEFAULT 约束：

MySQL/SQL Server / Oracle / MS Access：

CREATE TABLE student (
id int NOT NULL,
name VARCHAR(255) NOT NULL,
age int NOT NULL,
scores int DEFAULT 0
);

表已存在时添加 DEFAULT约束

当表已被创建时,在scores列上创建 DEFAULT约束

MySQL：

ALTER TABLE student
ADD scores SET DEFAULT 0;

SQL Server / MS Access：

ALTER TABLE student
ADD CONSTRAINT def_scores DEFAULT 0 for scores;

Oracle：

ALTER TABLE student
MODIFY  scores DEFAULT 0;

撤销 DEFAULT约束

MySQL:

ALTER TABLE student
ALTER scores DROP DEFAULT;

SQL Server / Oracle / MS Access：

ALTER TABLE student
ALTER COLUMN scores DROP DEFAULT;

三、 索引

索引介绍

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

官方介绍索引是帮助MySQL高效获取数据的数据结构,打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件

索引类型

主键索引

索引列中的值必须是唯一的，不允许有空值。

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

其他（按照索引列数量分类）

1. 单列索引

2. 组合索引

   组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则)。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

数据结构

mysql索引的数据结构是树，常用的存储引擎InnoDB采用的是B+Tree。这里对B+Tree进行简要介绍。

B+树是B树的升级版，更充分地利用了节点的空间。其结构如下：

和B树的主要区别如下：

• 有 k 个子节点的非叶子节点拥有 k 个关键字，B树是k-1个。这使得非叶子节点能保存的关键字大大增加，因此树高也大大降低。
• 关键字不保存数据，只是用来索引，这样非叶子节点的所占的内存空间就变小了，读到内存中的索引信息就会更多一些，相当于减少了磁盘IO次数
• 数据都是存在叶子节点，这样保证了相近的数据都能存在同一块数据块里。另外也使得B+树的查询次数更稳定，每次查询次数都是相同的，需要查询到叶子节点
• 叶子节点的指针指向下一个数据对应的叶子节点，因此B+树具备了天然排序功能，在排序和范围查找的时候更方便，可以通过叶子节点的指针找到下一个叶子节点的位置
• B+树可以方便地做全表搜索，只需要从第一个叶子节点顺序往后面扫描即可，而B树则需要做树的遍历。

如果感兴趣可以体验一下旧金山大学做的 BPlusTree Visualization 模型来模拟B+Tree

文章到这里就先结束了，后面还会持续更新，希望能帮助到各位大佬。如果文章有需要改进的地方还请各位大佬斧正。