SQL 入门

学习笔记 of 廖雪峰SQL教程

关系模型

Record: 每一行称为记录

Column: 每一列称为字段

NULL: 表示数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

主键: 通过某个字段唯一区分出不同的记录，这个字段被称为主键。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均***不可***用作主键。

id字段：

1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；

2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

   如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

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

如无必要，勿用联合主键，复杂度上升

外键: 在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表的主键）。

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

基本操作

查询

SELECT * FROM <table_name>

判断当前到数据库的链接是否有效

SELECT 1

条件查询

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

1. 查询分数在80分以上的学生记录

SELECT * FROM students WHERE score >= 80;

2. 查询分数在80分及以上的男生

SELECT * FROM students WHERE score >= 80 AND gender = 'M';

3. 查询分数在80分及以上的学生或者男生

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

4. 查询不是2班的学生

SELECT * FROM students WHERE NOT class_id = 2;

5. 分数在80以下或者90以上，并且是男生

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

条件	表达式举例1	表达式举例2	说明
使用=判断相等	score = 80	name = ‘abc’	字符串需要用单引号括起来
使用>判断大于	score > 80	name > ‘abc’	字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等	score >= 80	name >= ‘abc’
使用<判断小于	score < 80	name <= ‘abc’
使用<=判断小于或相等	score <= 80	name <= ‘abc’
使用<>判断不相等	score <> 80	name <> ‘abc’
使用LIKE判断相似	name LIKE ‘ab%’	name LIKE ‘%bc%’	%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’

投影查询

1. 从students表中返回id, score 和name这三列：

   SELECT id, score, name FROM students;

2. 取列同时给每一列起个别名SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...：

   SELECT id, score pints, name FROM students;

3. 加上WHERE实现复杂查询：

   SELECT id, score points, name FROM students WHERE gender = 'M';

排序

关键字：ORDER BY

1. 按成绩祷告进行排序（从低到高）

   SELECT id, name, gender, score FROM students ORRDER BY score;

2. 加上DESC表示倒序：

   SELECT id, name, gender, score FROM students ORDER BY score DESC;

3. 按多列排序：

   SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

4. 带WHERE条件的ORDER BY：

   SELECT id, name, gender, score
   FROM students
   WHERE class_id = 1
   ORDER BY score DESC;

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

1. 给students排序并把结果而烦恼页，每页3条记录，获取第1页记录：

   SELECT id, name, gender, score
   FROM students
   ORDER BY score DESC 
   LIMIT 3 OFFSET 0;

   LIMIT 3 OFFSET 0表示对结果从0记录开始，最多取3条

2. 查询第2页就是跳过头三条记录，从3好记录开始查询：

   SELECT id, name, gender, score
   FROM students
   ORDER BY score DESC
   LIMIT 3 OFFSET 3;

如果OFFSET超过了查询的最大数量并不会报错。而是得到一个空的结果集。

聚合查询

统计数量

1. 查询students表一共有多少记录

   SELECT COUNT(*) FROM students;
   SELECT COUNT(*) num FROM students;

   COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

2. 查询有多少男生：

   SELECT COUNT(*) boys FROM students WHERE gender = 'M';

平均值

SELECT AVG(score) average FROM students WHERE gender = 'M';

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

分组聚合

分组统计各个班男生和女生的人数：

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

从多张表同时查询数据：SELECT * FROM <表1> <表2>

1. 得到students表和classes表的 乘积:

   SELECT * FROM students, classes;

2. 实用表名来给列名做区分：

   SELECT
   	s.id sid,
   	s.name,
   	s.gender,
   	s.score,
   	c.id cid,
   	c.name cname
   FROM students s, classes c;

连接查询

通过INNER JOIN将两张表连接起来

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等子句。

选出所有学生，同时返回班级的名称:

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;

JOIN type	Function
INNER JOIN	只返回同时存在于两张表里的行数据
RIGHT OUTER JOIN	只返回右表存在的行
LEFT OUTER JOIN	只返回左表存在的行
FULL OUTER JOIN	把两张表的所有记录全部选择出来，并且自动在缺失的列填充为NULL

插入

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

一次性增加多条新纪录：

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

更新

UPDATE <form_name> SET column1=value1, column2=value2, ... WHERE ...;

1. 更新students表id=1的记录的name和score这两个字段:

   UPDATE students SET name='大牛', score=66 WHERE id=1;

2. 把所有80分以下的同学的成绩加10分:

   UPDATE students SET score=score+10 WHERE score<80;

如果UPDATE语句没有WHERE条件，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出期望的记录集，然后再用UPDATE更新。

避免生产环境执行更新删除语句忘记加where条件的解决方案

删除

DELETE FROM <表名> WHERE ...;

要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据。

MySQL

安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。

打开命令提示符，输入命令mysql -u root -p，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>：输入exit断开与MySQL Server的连接并返回到命令提示符。

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server。默认端口号是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1:3306。

也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

mysql -h 10.0.1.99 -u root -p

数据库

在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shici              |
| sys                |
| test               |
| school             |
+--------------------+

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。

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

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

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

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

注意：删除一个数据库将导致该数据库的所有表全部被删除。

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

mysql> USE test;
Database changed

表

列出当前数据库的所有表，使用命令：

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| classes             |
| statistics          |
| students            |
| students_of_class1  |
+---------------------+

要查看一个表的结构，使用命令：

mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| class_id | bigint(20)   | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int(11)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

还可以使用以下命令查看创建表的SQL语句：

mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` (                             |
|          |   `id` bigint(20) NOT NULL AUTO_INCREMENT,            |
|          |   `class_id` bigint(20) NOT NULL,                     |
|          |   `name` varchar(100) NOT NULL,                       |
|          |   `gender` varchar(1) NOT NULL,                       |
|          |   `score` int(11) NOT NULL,                           |
|          |   PRIMARY KEY (`id`)                                  |
|          | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

修改表就比较复杂。如果要给students表新增一列birth，使用：

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

要删除列，使用：

ALTER TABLE students DROP COLUMN birthday;

退出MySQL

使用EXIT命令退出MySQL：

mysql> EXIT
Bye

注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

实用SQL语句

插入或替换

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

插入或更新

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

插入或忽略

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

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

快照

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

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

查询结果写入表

创建一个统计成绩的表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必须存在。

事务

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：

-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

事务的ACID特性

• 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;

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

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

Read Uncommitted

Read Uncommitted是隔离级别最低的一种事务级别。

1. 脏读（Dirty Read）

在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读。

2. 不可重复读（Non Repeatable Read）

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

Repeatable Read

1. 幻读（Phantom Read）：

   幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。