SQL 基础教程

主键原则

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

外键原则

• 不用外键约束，用程序逻辑做外键关联

关系

一对多

class 表

student 表

多对多

class 表

teacher 表

class_student 表

一对一

• 也可以用一张大表
• 拆成两张表，可以把不经常查的内容区分开来

索引

定义

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。

建立方式

建立一个索引，索引名为 idx_score, 列名为 score

ALTER TABLE students
ADD INDEX idx_score (score);

建立前提

建立索引的列数据尽量互不相同

• 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高

注意点

• 索引越多，插入/更新/删除的速度越慢
• 数据库会自动给主键做索引

约束

可以对身份证这类唯一的列做唯一索引：可以保证某一列的值具有唯一性

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

或唯一约束

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查询

基本查询

使用 SELECT 查询的基本语句 SELECT * FROM <表名> 可以查询一个表的所有行和所有列的数据。

SELECT 查询的结果是一个二维表。

SELECT * FROM table_name

投影查询

使用 SELECT * 表示查询表的所有列，使用 SELECT 列 1, 列 2, 列 3 则可以仅返回指定列，这种操作称为投影。

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

条件查询

通过 WHERE 条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。

SELECT * FROM table_name WHERE 条件表达式

关系运算符：(如果不加小括号，则按照如下顺序为优先级计算)

• NOT
• AND
• OR

排序

SELECT * FROM table_name ORDER BY score;
SELECT * FROM table_name ORDER BY score DESC;

• 降序：DESC
• 升序：ASC（默认，可省略）

PS: 先 where 再 order by

分页

LIMIT n OFFSET m 从第 m 条记录开始，取 n 条记录

SELECT * FROM table_name LIMIT 10 OFFSET 40

注意：

• offset 是可选的，不写则为 offset 0
• 在 MySQL 中，LIMIT 15 OFFSET 30 还可以简写成 LIMIT 30, 15
• 随着 offset n 中，n 增大，效率会降低

分组

根据 column_a, column_b 分组，注意这里是 , 分隔

SELECT * FROM table_name GROUP BY column_a, column_b

分组条件：

SELECT * FROM table_name GROUP BY column_a, column_b HAVING count(name) > 1

聚合

count

查询行数，且查询到的结果是二维表，只是这个表是一行一列。列名为 count(*)

SELECT count(*) FROM table_name

功能同上，但列名为 num

SELECT count(*) num FROM table_name

SUM, AVG, MAX, MIN

注意：如果聚合查询的 WHERE 条件没有匹配到任何行，COUNT() 会返回 0，而 SUM()、AVG()、MAX() 和 MIN() 会返回 NULL

多表查询

就硬查

查询方式👇；如下的查询，会形成笛卡尔积，即 a 表里的每条数据，都和 b 表里的每条数据匹配一次。得到的结果集，列数是 a 表和 b 表的列数之和，行数是 a，b 表的行数乘积

SELECT * FROM table_a, table_b

连接查询 - INNER JOIN

SELECT * FROM table_a a INNER JOIN table_b b ON a.id = b.aid

使用步骤：

1. 确认主表，仍然是 FROM table_name 的写法
2. 确认要连接的表，使用 INNER JOIN table_name 语法
3. 确认连接条件，使用 ON a.id = b.aid 语法
4. 可选，加上 where 和 order by 等子句

⚠️注意：

• INNER JOIN 只返回同时存在于两张表的行数据
• RIGHT OUTER JOIN 返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以 NULL 填充剩下的字段
• LEFT OUTER JOIN 则返回左表都存在的行。如果某一行仅在左表存在，那么结果集就会用 NULL 填充剩下的字段
• FULL OUTER JOIN 会返回两表全部记录，不存在则给 NULL

💡Tip:

• FROM 后面的主表即为左表。JOIN 后面的副表即为右表
• ON 和 WHERE 的区别：
  • on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录
  • where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

连接图示

当有如下语句时：

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

• INNER JOIN

• LEFT OUTER JOIN

• RIGHT OUTER JOIN

• FULL OUTER JOIN

查询条件

• 验证为空 IS NULL

DELETE

DELETE FROM table_name WHERE ...

DELETE p.* FROM Person p, Person p1
	WHERE p.email = p1.email AND p.id > p1.id

INSERT

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

插入多条记录，一定要带小括号！！！

INSERT INTO Activity (player_id, device_id, event_date, games_played)
VALUES
	(1, 2, '2016-03-01', 5),
	(1, 2, '2016-05-02', 6),
	(2, 3, '2017-06-25', 1),
	(3, 1, '2016-03-02', 0),
	(3, 4, '2018-07-03', 5)

UPDATE

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