关系模型
主键
对于关系表,有个很重要的约束,就是任意两条记录不能重复。不能重复不是指两条记录不完全相同,而是指能够通过某个字段唯一区分出两条不同的记录,这个字段被称为主键。
选取主键的一个基本原则是:不使用任何业务相关的字段作为主键。
因此,身份证号、手机号、邮箱号这些看上去可以唯一的字段,均不可用作主键。作为主键最好是完全与业务无关的字段,我们一般把这个字段命名为ID。
常见的可作为ID字段的类型有:
- 自增整数类型
- 全局唯一GUID类型
联合主键
顾名思义,联合主键即通过多个字段均设置为主键来唯一标识记录。
在没有必要的情况下,尽量不使用联合主键,因为它给关系表带来了复杂度的上升。
外键
关系数据库可以通过外键实现一对多、多对多和一对一的关系。外间既可以通过数据库来约束,也可以不设置约束,仅仅靠应用程序的逻辑来保证。
索引
索引是关系数据库中对某一列或者多个列的值进行预排列的数据结构。通过使用索引,可以让数据库系统不必扫描整个表,而是直接定位到符合条件的记录,大大加快查询速度。
索引的效率取决于索引列的值是否为散列,即该列的值越互不相同,索引的效率越高。反之,如果记录的列存在大量相同的值,对该列的索引就没太多意义。
索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,速度越慢。
查询数据
基本查询
1
2
3
4
5
-- 切换到test数据库
USE test;
-- 查询students表
SELECT * FROM students;
条件查询
1
2
3
4
5
-- 查询students表中score大于等于80的数据
SELECT * FROM students WHRER score >= 80;
-- 查询students表中score在60到90(均包含)的数据
select * from students where score between 60 and 90;
投影查询
1
2
-- 查询students表中score,id和name三列
select score, id,name from students;
排序
1
2
3
4
5
6
7
8
9
10
11
-- 查询students表,按score升序排列
select * from students order by score;
-- 查询students表,按score降序排列
select * from students order by score desc,gender;
-- 带where条件的排序查询
select id,name,gender,score
from students
where class_id = 1
order by score desc;
分页查询
实现分页功能,例如从结果中显示第1-100条记录为第一页,第101-200条记录为第二页…
实际上就是从结果中截取第M-N条记录。可以同果limit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查询第一页
select id, name, gender,score
from students
order by score desc
limit 3 offset 0;
-- 查询第二页
select id, name, gender,score
from students
order by score desc
limit 3 offset 3;
-- 查询第三页
select id, name, gender,score
from students
order by score desc
limit 3 offset 6;
聚合查询
对于统计总数、平均数这类计算,SQL提供了专门的聚合函数进行查询,快速获得结果。
1
2
-- 使用Count
select count(*) from students;
使用Count()表示查询所有列的行数,注意计算的结果虽然是一个数字,但查询的结果仍然是一个二维表,只是这个二维表只有一行一列,并且列名是COUNT().
1
2
-- 使用聚合查询并设置结果集的列名为num
select count(*) num from students;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 使用聚合查询并设置where条件
select count(*) num from students where gender = 'M';
-- 使用聚合查询计算男生平均成绩
select AVG(score) average from students where gender = 'M';
-- 使用聚合查询计算男生总成绩
select SUM(score) sum from students where gender = 'M';
-- 使用聚合查询计算男生最低成绩
select MIN(score) min from students where gender = 'M';
-- 使用聚合查询计算男生最高成绩
select MAX(score) max from students where gender = 'M';
分组
1
2
3
4
5
-- 按class_id分组
select class_id,count(*) from students group by class_id;
-- 按class_id,gender分组
select class_id,gender,count(*) num from students group by class_id,gender;
多表查询
SELECT不仅可以从一张表查询数据,还可以从多张表同时查询数据,语法是:SELECT * FROM <表1><表2>.表2>表1>
1
2
-- 查询students,classes的“乘积”
select * from students,classes;
如上述这种一次查询两个表的乘积,查询结果也是一个二维表,又称作笛卡尔查询,其结果集是目标表的行数的乘积。
为了简化操作,我们还可以设置别名
1
2
3
4
5
6
7
8
9
-- set alias
select
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
from students s,classes c;
1
2
3
4
5
6
7
8
9
10
-- 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;
连接查询
1
2
3
4
5
6
7
8
9
10
11
-- 选出所有学生,同时返回班级名称
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;
1
2
3
4
5
6
7
8
9
10
11
-- Right 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;
1
2
3
4
5
6
7
8
9
10
11
-- Left outer join
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;
1
2
3
4
5
6
7
8
9
10
11
-- 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;