6,navicat使用!!!
通过上面的学习,我们发现在命令行中写sql语句特别不方便,尤其是编写创建表的语句,我们只能在记事本上写好后直接复制到命令行进行执行。那么有没有刚好的工具提供给我们进行使用呢? 有。
6.1 navicat概述
Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。
这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。
6.2 navicat安装
看最上面资源分享位置。
navicat
http://www.downcc.com/soft/430673.html
6.3 navicat使用
6.3.1 建立和mysql服务的连接
第一步: 点击连接,选择MySQL
第二步:填写连接数据库必要的信息,点击右下角连接测试。
以上操作没有问题就会出现如下图所示界面:
如果想连接服务器上的数据库可以看我的博客,我当时弄就遇到了很多坑:
navicat连接服务器数据库
https://blog.csdn.net/weixin_47343544/article/details/124066058?spm=1001.2014.3001.5501
6.3.2 操作
连接成功后就能看到如下图界面:
修改表结构
通过下图操作修改表结构:
点击了设计表后即出现如下图所示界面,在图中红框中直接修改字段名,类型等信息:
注:双击表,就能查看表中数据。一定要在双击数据库之后,在新建查询,
新建查询,写sql,可以使用Ctrl + R 快捷键运行,也可以选中sql语句进行右键运行,一个查询可以写很多条语句,选中执行就行,不用删除。写完sql可以点击上面美化Sql进行格式化。
编写SQL语句并执行
按照如下图所示进行操作即可书写SQL语句并执行sql语句。
7,DML
DML主要是对数据进行增(insert)删(delete)改(update)操作。
7.1 添加数据
给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
批量添加数据
-- 推荐使用第一种,在公司中需要sql更清晰
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
-- 省略字段名
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
练习
为了演示以下的增删改操作是否操作成功,故先将查询所有数据的语句介绍给大家:
select * from stu;
-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据,列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
-- 插入一条数据
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
-- 批量添加数据,用逗号分割
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1), (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1), (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
7.2 修改数据
修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
注意:
修改语句中如果不加条件,则将所有数据都修改!
像上面的语句中的中括号,表示在写sql语句中可以省略这部分
练习
将张三的性别改为女
update stu set sex = '女' where name = '张三';
将张三的生日改为 1999-12-12 分数改为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';
注意:如果update语句没有加where条件,则会将表中所有数据全部修改!
update stu set sex = '女';
上面语句的执行完后查询到的结果是:
7.3 删除数据
删除数据
DELETE FROM 表名 [WHERE 条件] ;
练习
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;
8,DQL
下面是黑马程序员展示试题库数据的页面
页面上展示的数据肯定是在数据库中的试题库表中进行存储,而我们需要将数据库中的数据查询出来并展示在页面给用户看。上图中的是最基本的查询效果,那么数据库其实是很多的,不可能在将所有的数据在一页进行全部展示,而页面上会有分页展示的效果,如下:
当然上图中的难度字段当我们点击也可以实现排序查询操作。从这个例子我们就可以看出,对于数据库的查询时灵活多变的,需要根据具体的需求来实现,而数据库查询操作也是最重要的操作,所以此部分需要大家重点掌握。
接下来我们先介绍查询的完整语法:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
为了给大家演示查询的语句,我们需要先准备表及一些数据:
删除stu表
drop table if exists stu; -- 创建stu表 CREATE TABLE stu ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math double(5,2), -- 数学成绩 english double(5,2), -- 英语成绩 hire_date date -- 入学时间 );
添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) VALUES (1,'马运',55,'男','杭州',66,78,'1995-09-01'), (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'), (3,'马斯克',55,'男','香港',56,77,'1999-09-02'), (4,'柳白',20,'女','湖南',76,65,'1997-09-05'), (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'), (6,'刘德花',57,'男','香港',99,99,'1998-09-01'), (7,'张学右',22,'女','香港',99,99,'1998-09-01'), (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
接下来咱们从最基本的查询语句开始学起。
8.1 基础查询
8.1.1 语法
查询多个字段
SELECT 字段列表 FROM 表名;
-- 查询所有数据 SELECT * FROM 表名;
去除重复记录(这个很常用 distinct 关键字)
SELECT DISTINCT 字段列表 FROM 表名;
起别名
AS: AS 也可以省略
8.1.2 练习
查询name、age两列
select name,age from stu;
查询所有列的数据,列名的列表可以使用*替代
select * from stu;
上面语句中的*不建议大家使用,因为在这写*不方便我们阅读sql语句。我们写字段列表的话,可以添加注释对每一个字段进行说明,这个是点击上面的美化SQL。
而在上课期间为了简约课程的时间,老师很多地方都会写*。
查询地址信息
select address from stu;
执行上面语句结果如下:
从上面的结果我们可以看到有重复的数据,我们也可以使用 distinct 关键字去重重复数据。
去除重复记录
select distinct address from stu;
查询姓名、数学成绩、英语成绩。并通过as给math和english起别名(as关键字可以省略)
select name,math as 数学成绩,english as 英文成绩 from stu; select name,math 数学成绩,english 英文成绩 from stu;
8.2 条件查询
8.2.1 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
条件列表可以使用以下运算符
8.2.2 条件查询练习
查询年龄大于20岁的学员信息
select * from stu where age > 20;
查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;
查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
select * from stu where age >= 20 && age <= 30; select * from stu where age >= 20 and age <= 30;
上面语句中 && 和 and 都表示并且的意思。建议使用 and 。
也可以使用 between ... and 来实现上面需求
select * from stu where age BETWEEN 20 and 30;
查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
查询年龄等于18岁的学员信息
select * from stu where age = 18;
查询年龄不等于18岁的学员信息
select * from stu where age != 18; select * from stu where age <> 18;
查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from stu where age = 18 or age = 20 or age = 22; select * from stu where age in (18,20 ,22);
查询英语成绩为 null的学员信息
null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not
select * from stu where english = null; -- 这个语句是不行的 select * from stu where english is null; select * from stu where english is not null;
8.2.3 模糊查询练习
模糊查询使用like关键字,可以使用通配符进行占位:
(1)_ : 代表单个任意字符
(2)% : 代表任意个数字符
查询姓'马'的学员信息
select * from stu where name like '马%';
查询第二个字是'花'的学员信息
select * from stu where name like '_花%';
查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';
8.3 排序查询
8.3.1 语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
上述语句中的排序方式有两种,分别是:
ASC : 升序排列 (默认值)
DESC : 降序排列
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
8.3.2 练习
查询学生信息,按照年龄升序排列
select * from stu order by age ;
查询学生信息,按照数学成绩降序排列
select * from stu order by math desc ;
查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
select * from stu order by math desc , english asc ;
8.4 聚合函数
8.4.1 概念
将一列数据作为一个整体,进行纵向计算。
如何理解呢?假设有如下表
现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。
8.4.2 聚合函数分类
函数名 功能
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
8.4.3 聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
注意:null 值不参与所有聚合函数运算
8.4.4 练习
统计班级一共有多少个学生
select count(id) from stu; select count(english) from stu;
上面语句根据某个字段进行统计,如果该字段某一行的值为null的话,将不会被统计。所以可以在count(*) 来实现。* 表示所有字段数据,一行中也不可能所有的数据都为null,所以建议使用 count(*)
select count(*) from stu;
查询数学成绩的最高分
select max(math) from stu;
查询数学成绩的最低分
select min(math) from stu;
查询数学成绩的总分
select sum(math) from stu;
查询数学成绩的平均分
select avg(math) from stu;
查询英语成绩的最低分
select min(english) from stu;
8.5 分组查询
8.5.1 语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
8.5.2 练习
查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex; -- 这里查询name字段就没有任何意义
查询男同学和女同学各自的数学平均分,以及各自人数
select sex, avg(math),count(*) from stu group by sex;
查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math > 70 group by sex;
查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;
where 和 having 区别:
执行时机不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
可判断的条件不一样:where 不能对聚合函数进行判断,having 可以。
8.6 分页查询
如下图所示,大家在很多网站都见过类似的效果,如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看,用户也可以通过点击查看下一页的数据。
接下来我们先说分页查询的语法。
8.6.1 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
注意: 上述语句中的起始索引是从0开始
8.6.2 练习
从0开始查询,查询3条数据
select * from stu limit 0 , 3;
每页显示3条数据,查询第1页数据
select * from stu limit 0 , 3;
每页显示3条数据,查询第2页数据
select * from stu limit 3 , 3;
每页显示3条数据,查询第3页数据
select * from stu limit 6 , 3;
从上面的练习推导出起始索引计算公式(重中之重):
起始索引 = (当前页码 - 1) * 每页显示的条数
二、MySQL高级
1,约束
上面表中可以看到表中数据存在一些问题:
id 列一般是用标示数据的唯一性的,而上述表中的id为1的有三条数据,并且 马花疼 没有id进行标示
柳白 这条数据的age列的数据是3000,而人也不可能活到3000岁
马运 这条数据的math数学成绩是-5,而数学学得再不好也不可能出现负分
柳青 这条数据的english列(英文成绩)值为null,而成绩即使没考也得是0分
针对上述数据问题,我们就可以从数据库层面在添加数据的时候进行限制,这个就是约束。
1.1 概念
约束是作用于表中列上的规则,用于限制加入表的数据
例如:我们可以给id列加约束,让其值不能重复,不能为null值。
约束的存在保证了数据库中数据的正确性、有效性和完整性
添加约束可以在添加数据的时候就限制不正确的数据,年龄是3000,数学成绩是-5分这样无效的数据,继而保障数据的完整性。
1.2 分类
非空约束: 关键字是 NOT NULL
保证列中所有的数据不能有null值。
例如:id列在添加 马花疼 这条数据时就不能添加成功。
唯一约束:关键字是 UNIQUE
保证列中所有数据各不相同。
例如:id列中三条数据的值都是1,这样的数据在添加时是绝对不允许的。
主键约束: 关键字是 PRIMARY KEY
主键是一行数据的唯一标识,要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
例如:上图表中id就可以作为主键,来标识每条数据。那么这样就要求数据中id的值不能重复,不能为null值。
检查约束: 关键字是 CHECK
保证列中的值满足某一条件。
例如:我们可以给age列添加一个范围,最低年龄可以设置为1,最大年龄就可以设置为300,这样的数据才更合理些。
注意:MySQL不支持检查约束。
这样是不是就没办法保证年龄在指定的范围内了?从数据库层面不能保证,以后可以在java代码中进行限制,一样也可以实现要求。
默认约束: 关键字是 DEFAULT
保存数据时,未指定值则采用默认值。
例如:我们在给english列添加该约束,指定默认值是0,这样在添加数据时没有指定具体值时就会采用默认给定的0。
外键约束: 关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。
外键约束现在可能还不太好理解,后面我们会重点进行讲解。
1.3 非空约束
概念
非空约束用于保证列中所有数据不能有NULL值
语法
添加约束
-- 创建表时添加非空约束 CREATE TABLE 表名( 列名 数据类型 NOT NULL, … ); -- 建完表后添加非空约束 ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL; 删除约束 ALTER TABLE 表名 MODIFY 字段名 数据类型;
1.4 唯一约束
概念
唯一约束用于保证列中所有数据各不相同
语法
添加约束
-- 创建表时添加唯一约束 CREATE TABLE 表名( 列名 数据类型 UNIQUE [AUTO_INCREMENT], -- AUTO_INCREMENT: 当不指定值时自动增长 … ); CREATE TABLE 表名( 列名 数据类型, … [CONSTRAINT] [约束名称] UNIQUE(列名) ); -- 建完表后添加唯一约束 ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE; 删除约束 ALTER TABLE 表名 DROP INDEX 字段名;
1.5 主键约束
概念
主键是一行数据的唯一标识,要求非空且唯一
一张表只能有一个主键
语法
添加约束
-- 创建表时添加主键约束 CREATE TABLE 表名( 列名 数据类型 PRIMARY KEY [AUTO_INCREMENT], … ); CREATE TABLE 表名( 列名 数据类型, [CONSTRAINT] [约束名称] PRIMARY KEY(列名) ); -- 建完表后添加主键约束 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); 删除约束 ALTER TABLE 表名 DROP PRIMARY KEY;
1.6 默认约束
概念
保存数据时,未指定值则采用默认值
语法
添加约束
-- 创建表时添加默认约束 CREATE TABLE 表名( 列名 数据类型 DEFAULT 默认值, … ); -- 建完表后添加默认约束 ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值; 删除约束 ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
1.7 约束练习
根据需求,为表添加合适的约束
-- 员工表 CREATE TABLE emp ( id INT, -- 员工id,主键且自增长 ename VARCHAR(50), -- 员工姓名,非空且唯一 joindate DATE, -- 入职日期,非空 salary DOUBLE(7,2), -- 工资,非空 bonus DOUBLE(7,2) -- 奖金,如果没有将近默认为0 );
上面一定给出了具体的要求,我们可以根据要求创建这张表,并为每一列添加对应的约束。建表语句如下:
DROP TABLE IF EXISTS emp; -- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id,主键且自增长 ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名,非空并且唯一 joindate DATE NOT NULL , -- 入职日期,非空 salary DOUBLE(7,2) NOT NULL , -- 工资,非空 bonus DOUBLE(7,2) DEFAULT 0 -- 奖金,如果没有奖金默认为0 );
通过上面语句可以创建带有约束的 emp 表,约束能不能发挥作用呢。接下来我们一一进行验证,先添加一条没有问题的数据
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
验证主键约束,非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);
执行结果如下:
从上面的结果可以看到,字段 id 不能为null。那我们重新添加一条数据,如下:
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
执行结果如下:
从上面结果可以看到,1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据
INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);
执行结果如下:
验证非空约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);
执行结果如下:
从上面结果可以看到,ename 字段的非空约束生效了。
验证唯一约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);
执行结果如下:
从上面结果可以看到,ename 字段的唯一约束生效了。
验证默认约束
执行完上面语句后查询表中数据,如下图可以看到王五这条数据的bonus列就有了默认值0。
注意:默认约束只有在不给值时才会采用默认值。如果给了null,那值就是null值。
如下:
INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);
执行完上面语句后查询表中数据,如下图可以看到赵六这条数据的bonus列的值是null。
验证自动增长: auto_increment 当列是数字类型 并且唯一约束
重新创建 emp 表,并给id列添加自动增长
-- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY auto_increment, -- 员工id,主键且自增长 ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名,非空并且唯一 joindate DATE NOT NULL , -- 入职日期,非空 salary DOUBLE(7,2) NOT NULL , -- 工资,非空 bonus DOUBLE(7,2) DEFAULT 0 -- 奖金,如果没有奖金默认为0 );
接下来给emp添加数据,分别验证不给id列添加值以及给id列添加null值,id列的值会不会自动增长:
INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null); INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null); INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);
1.8 外键约束
1.8.1 概述
外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性。
如何理解上面的概念呢?如下图有两张表,员工表和部门表:
员工表中的dep_id字段是部门表的id字段关联,也就是说1号学生张三属于1号部门研发部的员工。现在我要删除1号部门,就会出现错误的数据(员工表中属于1号部门的数据)。而我们上面说的两张表的关系只是我们认为它们有关系,此时需要通过外键让这两张表产生数据库层面的关系,这样你要删除部门表中的1号部门的数据将无法删除。
1.8.2 语法
添加外键约束
-- 创建表时添加外键约束 CREATE TABLE 表名( 列名 数据类型, … [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) ); -- 建完表后添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称); 删除外键约束 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
1.8.3 练习
根据上述语法创建员工表和部门表,并添加上外键约束:
-- 删除表 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; -- 部门表 CREATE TABLE dept( id int primary key auto_increment, dep_name varchar(20), addr varchar(20) ); -- 员工表 CREATE TABLE emp( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 添加外键 dep_id,关联 dept 表的id主键 CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id) );
添加数据
-- 添加 2 个部门 insert into dept(dep_name,addr) values ('研发部','广州'),('销售部', '深圳'); -- 添加员工,dep_id 表示员工所在的部门 INSERT INTO emp (NAME, age, dep_id) VALUES ('张三', 20, 1), ('李四', 20, 1), ('王五', 20, 1), ('赵六', 20, 2), ('孙七', 22, 2), ('周八', 18, 2);
此时删除 研发部 这条数据,会发现无法删除。
删除外键
alter table emp drop FOREIGN key fk_emp_dept;
重新添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);
2,数据库设计
2.1 数据库设计简介
软件的研发步骤
数据库设计概念
数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型。
建立数据库中的表结构以及表与表之间的关联关系的过程。
有哪些表?表里有哪些字段?表和表之间有什么关系?
数据库设计的步骤
需求分析(数据是什么? 数据具有哪些属性? 数据与属性的特点是什么)
逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
如下图就是ER(Entity/Relation)图:
物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
维护设计(1.对新的需求进行建表;2.表优化)
表关系
一对一
如:用户 和 用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
上图左边是用户的详细信息,而我们真正在展示用户信息时最长用的则是上图右边红框所示,所以我们会将详细信息查分成两周那个表。
一对多
如:部门 和 员工
一个部门对应多个员工,一个员工对应一个部门。如下图:
多对多
如:商品 和 订单
一个商品对应多个订单,一个订单包含多个商品。如下图:
2.2 表关系(一对多)
一对多
如:部门 和 员工
一个部门对应多个员工,一个员工对应一个部门。
实现方式
在多的一方建立外键,指向一的一方的主键
案例
我们还是以 员工表 和 部门表 举例:
经过分析发现,员工表属于多的一方,而部门表属于一的一方,此时我们会在员工表中添加一列(dep_id),指向于部门表的主键(id):
建表语句如下:
-- 删除表 DROP TABLE IF EXISTS tb_emp; DROP TABLE IF EXISTS tb_dept; -- 部门表 CREATE TABLE tb_dept( id int primary key auto_increment, dep_name varchar(20), addr varchar(20) ); -- 员工表 CREATE TABLE tb_emp( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 添加外键 dep_id,关联 dept 表的id主键 CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id) );
查看表结构模型图:
2.3 表关系(多对多)
多对多
如:商品 和 订单
一个商品对应多个订单,一个订单包含多个商品
实现方式
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
案例
我们以 订单表 和 商品表 举例:
经过分析发现,订单表和商品表都属于多的一方,此时需要创建一个中间表,在中间表中添加订单表的外键和商品表的外键指向两张表的主键:
建表语句如下:
-- 删除表 DROP TABLE IF EXISTS tb_order_goods; DROP TABLE IF EXISTS tb_order; DROP TABLE IF EXISTS tb_goods; -- 订单表 CREATE TABLE tb_order( id int primary key auto_increment, payment double(10,2), payment_type TINYINT, status TINYINT ); -- 商品表 CREATE TABLE tb_goods( id int primary key auto_increment, title varchar(100), price double(10,2) ); -- 订单商品中间表 CREATE TABLE tb_order_goods( id int primary key auto_increment, order_id int, goods_id int, count int ); -- 建完表后,添加外键 alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id); alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
查看表结构模型图:
2.4 表关系(一对一)
一对一
如:用户 和 用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
实现方式
在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)
案例
我们以 用户表 举例:
而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用,此时就可以将这张表查分成两张表。
建表语句如下:
create table tb_user_desc ( id int primary key auto_increment, city varchar(20), edu varchar(10), income int, status char(2), des varchar(100) ); create table tb_user ( id int primary key auto_increment, photo varchar(100), nickname varchar(50), age int, gender char(1), desc_id int unique, -- 添加外键 CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id) );
查看表结构模型图:
2.5 数据库设计案例
根据下图设计表及表和表之间的关系:
经过分析,我们分为 专辑表 曲目表 短评表 用户表 4张表。
一个专辑可以有多个曲目,一个曲目只能属于某一张专辑,所以专辑表和曲目表的关系是一对多。
一个专辑可以被多个用户进行评论,一个用户可以对多个专辑进行评论,所以专辑表和用户表的关系是 多对多。
一个用户可以发多个短评,一个短评只能是某一个人发的,所以用户表和短评表的关系是 一对多。