【SQL】关系模型与查询和更新数据

一、关系模型

1.1 主键

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用。

1.2 外键 FOREIGN KEY

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

1.3 索引

• 可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
• 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
• UNIQUE关键字我们就添加了一个唯一索引。
• 通过对数据库表创建索引，可以提高查询速度。通过创建唯一索引，可以保证某一列的值具有唯一性。数据库索引对于用户和应用程序来说都是透明的。

二 、查询数据

附录：表-students

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95
3	1	小军	M	88
4	1	小米	F	73
5	2	小白	F	81
6	2	小兵	M	55
7	2	小林	M	85
8	3	小新	F	91
9	3	小王	M	89
10	3	小丽	F	88

2.1 基本查询

SELECT * FORM

查询一个表的所有行和所有列的数据，SELECT查询的结果是一个二维表。

2.2 条件查询

• SELECT * FROM WHERE
• 条件表达式可以：AND、OR、NOT
• 优先级：NOT、AND、OR，加括号可以改变优先级。

2.3 投影查询

Select 列1，列2，列3，则可以进返回指定列，这种称为投影。

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

2.4 排序

• ORDER BY：SELECT id,name,FROM studentS ORDER BY score;
• DESC 倒序：SELECT id,name,FROM studentS ORDER BY score DESC;
• ASX 升序，从小到大：

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

使用ORDER BY可以对结果进行排序，可以对多列进行升序、倒序排序。

2.5 分页查询

• 使用LIMIT OFFSET 可以对结果集进行分页，每次查询返回结果集的一部分
• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。这样就能正确查询出第N页的记录集。

案例：

--查询第4页 SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 9;

2.6 聚合查询

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

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

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

-- 使用聚合查询计算男生平均成绩: SELECT AVG(score) average FROM students WHERE gender = 'M';

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

-- WHERE条件gender = 'X'匹配不到任何行: SELECT AVG(score) average FROM students WHERE gender = 'X';

分组

GROUP BY

-- 查出每个班级的平均分，结果集应当有6条记录: SELECT class_id, gender,AVG(score) FROM students GROUP BY gender,class_id;

class_id	gender	AVG(score)
1	M	89
1	F	84
2	F	81
2	M	70
3	F	89.5
3	M	89

2.7 连接查询

• 是一种多表查询，JOIN运算，简单的说就是先确定一个主表作为结果集，然后把其他表的性有选择地“连接”在主表结果集上。
• INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT ... FROM INNER JOIN ON ；
• JOIN查询仍然可以使用WHERE条件和ORDER BY排序
• 其他的语法如下：

三、修改数据

3.1 INSERT 一次向一个表中插入1条或者多条记录

语法： INSERT INTO (字段1，字段2) VALUES (值1，值2，值N...)

添加一条记录

insert into students（class_id,name,gender,score）values (2,'daniu','M',80)

添加多条记录

insert into students（class_id,name,gender,score）values (2,'daniu','M',80),(3,'xxx','M',90)

3.2 UPDATE 一次向一个表中更新1条或者多条记录

语法: update 表名 set 字段1=值1,字段2=值2 where ...；

eg: update students set name=‘大牛’,score=66 where id=6;

• update的where和select的where条件是一样的
• update可以使用表达式，如update students set score=score+10
• where条件没有匹配到任何记录，update也不会报错，也不会有任何记录被更新，如update students set score=1000 where id=999;
• UPDATE后面可以没有WHERE,，如

UPDATE students SET score=60;

这时，整个表的所有记录都会被更新。所以，在执行

UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

• 注意 MYSQL中的更新方式：update语句会返回更新的行数以及where条件匹配行数。eg:mysql> UPDATE students SET name='大宝' WHERE id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

3.3 DELETE 删除一条或多条记录

语法

delete from 表名 where..;

• 删除1条记录：eg:delete from students where id=1;
• 删除多条记录，可以使用条件表达式：delete from students where id>=5 and id
• 如果没有匹配结果，那么也不会报错。
• 最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

DELETE FROM students;

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

• MYSQL中的使用：delete语句可以已返回删除的函数以及where条件匹配的行数。如：mysql> DELETE FROM students WHERE id=1;

Query OK, 1 row affected (0.01 sec)

四、参考资料

关系模型

查询数据

修改数据