sql

• SQL是结构化查询语言的缩写，用来访问和操作数据库系统。
• SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。
• 不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

能力

总的来说，SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language

​ DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

​ DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

​ DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点

• SQL语言关键字不区分大小写！！！
• 但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。
• 同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

数据模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

• 层次模型 (树)
• 网状模型 (图)
• 关系模型 (表)

rdbms

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

关系模型

• 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
• 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

主键

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表的两行记录：

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

• 对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

• 当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：
• 在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。
• 注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN …实现的。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

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

ALTER TABLE studentsADD INDEX idx_score (score);

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

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

ALTER TABLE studentsADD UNIQUE INDEX uni_name (name);

基本查询

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

SELECT * FROM <表名> 	#查询表的所有数据
SELECT 1 #测试数据库连接

条件查询

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

SELECT * FROM <表名> WHERE <条件表达式>

条件表达式可以用

• <条件1> AND <条件2>
• <条件1> OR <条件2>，
• NOT <条件>，
  • 上述NOT条件NOT class_id = 2其实等价于class_id <> 2，因此，NOT查询不是很常用。

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，

• 即NOT优先级最高，
• 其次是AND，
• 最后是OR。
• 加上括号可以改变优先级。

投影查询

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

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用

SELECT 列1, 列2, 列3 FROM …

，让结果集仅包含指定列。这种操作称为投影查询。

使用SELECT 列1, 列2, 列3 FROM …时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是

SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

排序

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序：

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

例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

select id,name,score
form student
where class_id = 1
order by score desc ,id

分页查询

这个查询可以通过LIMIT <M> OFFSET <N>子句实现。

• 现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：
• 类似的，查询第3页的时候，OFFSET应该设定为6:
• 查询第4页的时候，OFFSET应该设定为9:

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

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

select id,name,score
form student
where class_id = 1
order by score desc ,id
limit 3 offset 2
#3条为一页 ，显示第3页

聚合查询

以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

select count(*) num from students
#num 为别名

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

分组

• 如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。
• 如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？
• 对于聚合查询，SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询：

select count(*) num from students group by class_id

执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。

但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：

select class_id, count(*) num from students group by class_id

多表查询

SELECT * FROM <表1> <表2>

别名

select 
	students.id sid
	students.name
	students.gender
	classes.id cid
	classes.name cname
from students,classes

或者

select 
	s.id sid
	s.name
	s.gender
	c.id cid
	c.name cname
from students s,classes c

多表查询也是可以添加WHERE条件的，我们来试试：

select 
	s.id sid
	s.name
	s.gender
	c.id cid
	c.name cname
from students s,classes c
where s.gender = 'M' and c.id =1

使用多表查询可以获取M x N行记录；

多表查询的结果集可能非常巨大，要小心使用。

连接查询

对于这么多种JOIN查询，到底什么使用应该用哪种呢？其实我们用图来表示结果集就一目了然了。

假设查询语句是：

SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

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

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

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

• JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；
• INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT … FROM <表1> INNER JOIN <表2> ON <条件…>；
• JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

连接详解

数据中的连接join分为内连接、自然连接、外连接，外连接又分为左外连接、右外连接、全外连接

表1

表2

当然，这些分类都是在连接的基础上，是从两个表中记录的笛卡尔积中选取满足连接的记录。笛卡尔积简单的说就是一个表里的记录要分别和另外一个表的记录匹配为一条记录，即如果表1有3条记录，表2也有三条记录，经过笛卡尔运算之后就应该有3*3即9条记录。如下表

自然连接（natural join）

自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。 sql语句：Select …… from 表1 natural join 表2 结果：

内连接（inner join）

内连接基本与自然连接相同，不同之处在于自然连接要求是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。 sql语句：Select …… from 表1 inner join 表 2 on 表1.A=表2.E 结果：

自然连接时某些属性值不同则会导致这些元组会被舍弃，那如何保存这些会被丢失的信息呢，外连接就解决了相应的问题。外连接分为左外连接、右外连接、全外连接。外连接必须用using或on指定连接条件。

左外连接（left outer join)

左外连接是在两表进行自然连接，只把左表要舍弃的保留在结果集中，右表对应的列上填null。 sql语句：Select …… from 表1 left outer join 表2 on 表1.C=表2.C 结果：

右外连接(rignt outer join)

右外连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。 Select …… from 表1 rignt outer join 表2 on 表1.C=表2.C

全外连接(full join)

全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。 Select …… from 表1 full join 表2 on 表1.C=表2.C

crud

而对于增、删、改，对应的SQL语句分别是：

• INSERT：插入新记录；
• UPDATE：更新已有记录；
• DELETE：删除已有记录。

INSERT语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

要创建一个新数据库，使用命令：

mysql> CREATE DATABASE test;

要删除一个数据库，使用命令：

mysql> DROP DATABASE test;

对一个数据库进行操作时，要首先将其切换为当前数据库：

mysql> USE test;

插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO … ON DUPLICATE KEY UPDATE …语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO …语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

写入查询结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

CREATE TABLE statistics (    id BIGINT NOT NULL AUTO_INCREMENT,    class_id BIGINT NOT NULL,    average DOUBLE NOT NULL,    PRIMARY KEY (id));

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

事务

特性

数据库事务具有ACID这4个特性：

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

我们会依次介绍4种隔离级别的数据一致性问题。