数据库

sql是控制数据库的语言，数据库是存储数据的中心。每一种数据都有自己的格式，例如一张班级的表，里面有描述学生的属性以及学生的学习成绩之类。当然在计算机中，我们可以使用不同的定义模式来归纳自己的数据。

于是为了统一，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心。

概念

数据模型

数据需要一定的格式，这决定了存储难度和查询难度。数据库一般有三种模型：

• 层次模型

• 网状模型

• 关系模型

数据类型

数据类型是数据的单位，决定了数据的属性。

关系数据库

关系数据库中的关系是数据库中表之间的关系，表是在数据库下创建的存储数据的一种单元，这样可以按照一定的逻辑在不同的表中查询自己需要的信息。例如一个学校的管理表：

班级表中每一行表示一个班级的信息：

创建的学生表，表示一个班级的人员信息，这个学生表和班级表存在一定的关系

可以看到一个班级中有许多学生，班级与学生的关系就是一对多。也就是说可以在一个班级中查询到许多学生。如果是一对一，也就是一个班级中只有一个学生，这样班级和学生就是一对一的关系。但是一般而言，数据表是不会这样设计的。

主键

记住一个概念，键是一种特殊的列

主键的作用是标注一个独立的行，这个行中包括所有列在当前行的信息。确定了一个主键，就可以确定一个行的信息。所以，对于主键的要求其必须是一种完全不重复的字段。

一般来说在本地主机中可以使用**BIGINT NOT NULL AUTO_INCREMENT类型**(一种自增的数据)来创建主键。

在这里使用一个数据表来描述主键，如果使用name这个字段来作为主键，通过检索小明，我们可以得到这个小明行的所有信息，但是这显然不是一个好的主键字段选择，因为名称通常会重复。

此外，主键也支持联合主键，就是通过两个列位的信息来确定唯一的一个行。举一个例子。在这个表中，我们可以结合id_num，id_type。来创建一个联合主键，可以发现1A 2A 2B都是独立唯一的一个字段，这样结合也可以成为一种主键。

主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

外键*

当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：

在另一个classes表中，也可以通过主键来确定任意一个班级记录：

如果需要联合两个表来确定students表的一条新的记录，例如，id=1的小明，属于classes表中的哪一个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为一对多，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

例如：

• 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

构造多对多

对于外键用法来说，可以使用外键构造多对多关系。多对多关系需要构造一个中间表，下面用三个表演示如何构造多对多关系：

teachers表：

classes表：

中间表teacher_class关联两个一对多关系：

通过中间表teacher_class可知teachers到classes的关系：

• id=1的张老师对应id=1,2的一班和二班；
• id=2的王老师对应id=1,2的一班和二班；
• id=3的李老师对应id=1的一班；
• id=4的赵老师对应id=2的二班。

同理可知classes到teachers的关系：

• id=1的一班对应id=1,2,3的张老师、王老师和李老师；
• id=2的二班对应id=1,2,4的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系。

构造一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系：

构造一对一关系的目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

例如，对于students表：

如果要经常根据score列进行查询，就可以对score列创建索引：

ALTER TABLE students
ADD INDEX idx_score (score);

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

给一些独特的业务数据添加标记，例如身份证，邮箱这种类似于唯一的数据。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

索引只是优化查找速度，即使没有索引也不影响查找数据。

查询

要查询数据库表的数据，我们使用如下的SQL语句：

# 这个语句的意思是从一个表中查询其的所有行
SELECT * FROM <表名>

使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*中填写你需要查询的列字段。如果是填写*，则会查询当前表中的所有列，将这些列全部析出。

SELECT语句其实并不要求一定要有FROM子句。不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

条件查询

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件分数在80分或以上的学生，写成WHERE条件就是

SELECT * FROM students WHERE score >= 80

其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩，因此，score >= 80就筛选出了指定条件的记录。

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。需要查出一个成绩大于80且性别为男的

就可以写出WHERE条件：score >= 80 AND gender = '男'：

当然其中的AND可以替换为or，and，NOT，这些需要针对于需求进行修改。

投影查询

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

例如，从students表中返回id、score和name这三列：

-- 使用投影查询
SELECT id, score, name FROM students;

例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变

-- 使用投影查询，并将列名重命名：
SELECT id, score as points, name FROM students;

查询排序

我们使用SELECT查询时，查询结果集通常是按照id排序的，也就是根据主键排序。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序：

-- 按score从低到高:
SELECT id, name, gender, score FROM students ORDER BY score;

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”

-- 按score从高到低:
SELECT id, name, gender, score FROM students ORDER BY score DESC;

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

-- 按score, gender排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

-- 带WHERE条件的ORDER BY:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一 条子句。如果它不是最后的子句，将会出错。

分页查询

分页实际上就是从结果集中截取出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。我们先把所有学生按照成绩从高到低进行排序：

-- 按score从高到低:
SELECT id, name, gender, score FROM students ORDER BY score DESC;

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

-- 查询第1页:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要跳过头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

-- 查询第2页:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集。

聚合查询

简单来说就是将某一块数据进行求和计算

聚合函数也就是COUNT() 函数，用于返回表中由 WHERE 子句中指定的条件过滤的行数或列数。也就是说这个函数是返回一个数字。需要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。

-- 使用聚合查询并设置结果集的列名为num:
SELECT COUNT(*) num FROM students;

-- 等价于
COUNT( [ALL|DISTINCT] expression )

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生数量：

-- 使用聚合查询并设置WHERE条件:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

多表查询

在查询一个表时可以使用SELECT函数来进行查询操作。也可以使用SELECT从多个表进行查询，查询的语法是：SELECT * FROM <表1> <表2>。

-- FROM students, classes:
SELECT * FROM students, classes;

例如将下面两个表进行JOIN

• 表一：students

• 表二：classes

拼接完成的数据表是:

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理

要解决这个问题，我们仍然可以利用投影查询的设置列的别名来给两个表各自的id和name列起别名：

-- set alias:
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。

但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以 SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

-- set table alias:
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

注意到FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>。这样我们用别名s和c分别表示students表和classes表。

多表查询也是可以添加WHERE条件的

-- set where clause:
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

连接查询属于多表查询的一种。简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地连接在主表结果集上。

以最常使用的内连接为例，要求选出所有学生，同时返回班级名称：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score -- .之后的是表的某个列，这被称作全限定列名
FROM students s
INNER JOIN classes c
ON s.class_id = c.id; -- 指定了需要的列

注意INNER JOIN查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

接下来展示外连接，将上述的内连接更改为外连接

-- 使用OUTER JOIN:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

执行上述RIGHT OUTER JOIN可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多出来的一行是四班，但是，学生相关的列如name、gender、score都为NULL。

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

• INNER JOIN：只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

• RIGHT OUTER JOIN：返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

• LEFT OUTER JOIN：则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：

当使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

-- 使用FULL OUTER JOIN:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

使用韦恩图来举例。

INNER JOIN是选出两张表都存在的记录：

LEFT OUTER JOIN是选出左表存在的记录：

RIGHT OUTER JOIN是选出右表存在的记录：

FULL OUTER JOIN则是选出左右表都存在的记录：

常见

这里记录补充sql中的常见语法以及常见功能

注释

很多 DBMS 都支持各种形式的注释语法。我们先来看行内注释：

SELECT prod_name -- 这是一条注释
FROM Products;

注释使用--两个连字符嵌在行内。--之后的文本就是注释，下面是另一种形式的行内注释（但这种形式有些 DBMS 不支持 ）。

# 这是一条注释
SELECT prod_name
FROM Products;

在一行的开始处使用#，这一整行都将作为注释。