1
主键
对于关系表,有个很重要的约束,就是任意两条记录不能重复。不能重复不是指两条记录不完全相同,而是指能够通过某个字段唯一区分出不同的记录,这个字段被称为主键。
2
联合主键
关系数据库实际上还允许通过多个字段唯一标识记录,即两个或更多的字段都设置为主键,这种主键被称为联合主键。
3
定义外键
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
表的主键)。
删除外键
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
要删除一个外键约束,也是通过ALTER TABLE
实现的
4
索引
如果要经常根据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);
索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。
对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。
5
唯一索引
在设计关系数据表的时候,看上去唯一的列,例如身份证号、邮箱地址等,因为他们具有业务含义,因此不宜作为主键。
但是,这些列根据业务要求,又具有唯一性约束:即不能出现两条记录存储了同一个身份证号。这个时候,就可以给该列添加一个唯一索引。例如,我们假设students
表的name
不能重复:
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
通过UNIQUE
关键字我们就添加了一个唯一索引。
也可以只对某一列添加一个唯一约束而不创建唯一索引:
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
这种情况下,name
列没有索引,但仍然具有唯一性保证。
无论是否创建索引,对于用户和应用程序来说,使用关系数据库不会有任何区别。这里的意思是说,当我们在数据库中查询时,如果有相应的索引可用,数据库系统就会自动使用索引来提高查询效率,如果没有索引,查询也能正常执行,只是速度会变慢。因此,索引可以在使用数据库的过程中逐步优化。
6
分页查询
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:
在MySQL中,LIMIT 3 OFFSET 6
还可以简写成 LIMIT 6, 3
。
7
聚合函数
除了COUNT()
函数外,SQL还提供了如下聚合函数:
函数 | 说明 |
---|---|
SUM | 计算某一列的合计值,该列必须为数值类型 |
AVG | 计算某一列的平均值,该列必须为数值类型 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
注意,MAX()
和MIN()
函数并不限于数值类型。如果是字符类型,MAX()
和MIN()
会返回排序最后和排序最前的字符。
8
多列分组
也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
class_id | gender | num |
---|---|---|
1 | M | 2 |
1 | F | 2 |
2 | F | 1 |
2 | M | 2 |
3 | F | 2 |
3 | M | 1 |
9
多表查询
SELECT * FROM students, classes;
这种一次查询两个表的数据,查询的结果也是一个二维表,它是students
表和classes
表的“乘积”,即students
表的每一行与classes
表的每一行都两两拼在一起返回。结果集的列数是students
表和classes
表的列数之和,行数是students
表和classes
表的行数之积。
10
连接查询
内连接
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;
右链接
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;
左连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;
全连接
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;
有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返回左表都存在的行。如果某一行仅在左表存在,那么结果集就会以NULL
填充剩下的字段。
对于这么多种JOIN查询,到底什么使用应该用哪种呢?其实我们用图来表示结果集就一目了然了。
假设查询语句是:
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
我们把tableA看作左表,把tableB看成右表,那么INNER JOIN是选出两张表都存在的记录:
LEFT OUTER JOIN是选出左表存在的记录:
RIGHT OUTER JOIN是选出右表存在的记录:
FULL OUTER JOIN则是选出左右表都存在的记录: