这一部分主要使用SQL中的DML,数据库操作语言(data manipulation language),对数据库数据进行增、删、改、查操作,作为前提,先介绍关系型数据库中的表关系。
关系型数据库(RDBMS)
MySQL作为关系型数据库,数据是存储在表中的,假如设计一个订单系统,可能包含以下信息:
- 供应商(Vendors)
- 产品目录(Products)
- 客户列表(Customers)
- 订单(Orders)
- 可能还会为产品打上一些标签(比如,新品,进口商品,本地特产等)
每个表中都有个特殊的列(字段),称为主键(Primary Key),其值用来唯一标识表中的一行数据(记录)。表中还可以有称为外键(Foreign Key)的列,引用同一个表或不同表中某行的主键。因此,通过外键,表与表之间是有关联关系的,这也是建立关系型数据库模型的基础。
下面我们试着设计一下这个订单系统的表结构,就拿其中的供应商(Vendors)和产品目录(Products)来说:
供应商表(Vendors):
列 | 说明 | 主键/外键 |
---|---|---|
vendor_id | 供应商唯一ID, | PK |
vend_name | 供应商名字 | |
vend_address | 供应商地址 |
产品目录表(Products):
列 | 说明 | 主键/外键 |
---|---|---|
prod_id | 产品唯一ID | PK |
prod_name | 产品名字 | |
prod_price | 产品价格 | |
vendor_id | 供应商ID(关联Vendors表的vendor_id列) | FK |
Products表存放产品记录,每个产品都有prod_name, prod_price,并且有唯一的prod_id作为主键。除此之外,还有一个外键vendor_id,引用Vendors表中的vend_id,通过这种方式,为每个产品指定了供应商。我们不需要在产品表中再添加更多的列来存储其供应商的具体信息,以后直接根据这个外键,去Vendors表中查询即可。
从中我们可以看出,关系型数据库在存储数据时是很高效的,避免了数据冗余。而且,关系型数据库的可伸缩性远比非关系型数据库要好。比如,对数据库中供应商名字,地址等信息的修改,可以只更新Vendors表中的记录,相关表中的记录不用改动。
但是另一方方面,将数据分别存放在不同的表中有时是很复杂的,我们需要查询多个关联的表,才能拿到我们想要的数据。因此,在设计表结构,以及编写SQL语句时,需要很好地理这种关联关系,知道什么信息存在什么表中。
表关系种类
一对多
比如上面的供应商和产品,一个供应商可能提供多个产品,而一个产品只能属于一个供应商,在多的一边(产品表)通过Foreign Key 描述这种关系。
一对一
比如,一张订单对应一张订单详情;一篇文章摘要,对应一篇具体的文章内容。一对一关系的Foreign Key建在关系的哪边都行。
多对多
这个是相对复杂的一种,但是也很好理解。还是上面的例子,比如给产品打标签,一个产品可能有多个标签(”进口“, ”新品“),而一个标签下可以对应多个产品(进口商品可能有很多)。
特别要注意的是,如果要描述多对多关系,需要借助第三张表,这个表称为关系表,这张表实际存储时,看起来是这样的:
id (PK) | prod_id (FK) | tag_id (FK) |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 2 | 1 |
4 | 3 | 1 |
除了自增的主键id,它还有两个外键,一个prod_id关联产品表,一个tag_id关联标签表。
插入数据(INSERT INTO)
这里将以教学管理为例,来创建数据库,创建表,插入数据。最后一部分的综合练习,将基于接下来要创建的数据。
创建表并定义约束关系
首先,创建一个数据库,就命名为school
吧
CREATE DATABASE school CHARSET utf8;
关于表创建和约束的更多详细,请参考MySQL基础(二)数据库、表的创建及操作
下面我们我们将创建5张表,分别是:
- 班级表(Class)
- 课程表(Course)
- 成绩表(Score)
- 学生表(Student)
- 教师表(Teacher)
其中班级表和课程表是一对多关系;班级表和学生表是一对多关系;教师表和课程表是一对多关系;学生表和课程表是多对多关系。
-- -------------------------
-- 创建班级表
-- -------------------------
CREATE TABLE Class
(
cls_id int NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
caption char(30) NOT NULL
);
-- -------------------------
-- 创建课程表
-- -------------------------
CREATE TABLE Course
(
crs_id int NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
crs_name char(30) NOT NULL ,
teacher_id int NOT NULL -- FK
);
-- -------------------------
-- 创建成绩表
-- -------------------------
CREATE TABLE Score
(
scr_id int NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
num tinyint NOT NULL ,
student_id int NOT NULL , -- FK
course_id int NOT NULL -- FK
);
-- -------------------------
-- 创建学生表
-- -------------------------
CREATE TABLE Student
(
std_id int NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
gender enum('女', '男') NOT NULL ,
std_name char(30) NOT NULL ,
class_id int NOT NULL -- FK
);
-- -------------------------
-- 创建教师表
-- -------------------------
CREATE TABLE Teacher
(
tea_id int NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
tea_name char(30) NOT NULL
);
-- -------------------------
-- 定义外键
-- -------------------------
ALTER TABLE Course
ADD CONSTRAINT FK_Course_Teacher
FOREIGN KEY (teacher_id) REFERENCES Teacher (tea_id);
ALTER TABLE Score
ADD CONSTRAINT FK_Score_Student
FOREIGN KEY (student_id) REFERENCES Student (std_id);
ALTER TABLE Score
ADD CONSTRAINT FK_Score_Course
FOREIGN KEY (course_id) REFERENCES Course (crs_id);
ALTER TABLE Student
ADD CONSTRAINT FK_Student_Class
FOREIGN KEY (class_id) REFERENCES Class (cls_id);
定义外键的方式
注意:定义外键的数据类型一定要和关联表主键的数据类型一致!
建表时定义
CREATE TABLE Course
(
crs_id int PRIMARY KEY AUTO_INCREMENT ,
crs_name char(30) NOT NULL ,
teacher_id int NOT NULL ,
CONSTRAINT FK_Course_Teacher -- 外键名
FOREIGN KEY (teacher_id) -- 外键字段
REFERENCES Teacher (tea_id) -- 关联表主键字段
);
建表后定义
CREATE TABLE Course
(
crs_id int PRIMARY KEY AUTO_INCREMENT ,
crs_name char(30) NOT NULL ,
teacher_id int NOT NULL
);
ALTER TABLE Course
ADD CONSTRAINT FK_Course_Teacher -- 外键名
Foreign KEY (teacher_id) REFERENCES Teacher (tea_id); -- 外键字段和关联表主键字段
删除外键
格式
ALTER TABLE 表名
DROP FOREIGN KEY 外键名;
示例
ALTER TABLE Course
DROP FOREIGN KEY FK_Course_Teacher;
插入数据
插入单条数据
格式
INSERT INTO 表名 [(fld1, fld2, ..., fldn)]
VALUES (val1, val2, ..., valn);
注意:表名后的字段名可以省略,但建议加上,VALUES
的值将依次匹配字段名,而不一定非要按照表中字段的实际次序;有默认值的字段或自增字段可不提供值。另外,对于自增id,如果插入数据时指定了非连续的值,比如表记录最后一行id是3,插入数据时指定id是9,那么以后再插入数据时,id将从9开始自增。
插入多条数据
格式
INSERT INTO 表名 [(fld1, fld2, ..., fldn)]
VALUES (val1, val2, ..., valn),
(val1, val2, ..., valn),
-- ...
(val1, val2, ..., valn);
示例
下面,我们将为上面创建的那5张表填充数据:
-- --------------------------
-- 插入班级数据
-- --------------------------
INSERT INTO Class (caption)
VALUES ('三年二班'), ('三年三班'), ('一年二班'), ('二年九班');
-- --------------------------
-- 插入教师数据
-- --------------------------
INSERT INTO Teacher (tea_name)
VALUES ('张磊老师'), ('李平老师'), ('刘海燕老师'), ('朱云海老师'), ('李杰老师');
-- -------------------------
-- 插入课程数据
-- -------------------------
INSERT INTO Course (crs_name, teacher_id)
VALUES ('生物', 1), ('物理', 2), ('体育', 3), ('美术', 2);
-- -------------------------
-- 插入学生数据
-- -------------------------
INSERT INTO Student (gender, std_name, class_id)
VALUES ('男', '理解', 1), ('女', '钢蛋', 1), ('男', '张三', 1), ('男', '张一', 1),
('女', '张二', 1), ('男', '张四', 1), ('女', '铁锤', 2), ('男', '李三', 2),
('男', '李一', 2), ('女