表关系操作

一对一
一对多或多对一
多对多

一、一对一关系

两个表中字段关系是一一对应的。例如：人和身份证。一般在数据库中如果两个表是一一对应那么可以考虑将两张表合成一个表

二、一对多（多对一）

1. 创建

示例：班级和学生，一个班级对应多个学生，一个学生对应一个班级

在实现的时候一般使用外键实现

示例代码SQL:


-- 创建班级表


# 多表查询练习

-- 创建表 班级表
CREATE TABLE class (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10);
)

-- 创建课目

CREATE TABLE course(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 创建学生表
CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    sex CHAR(1),
    age INT,
    class_id INT,
    -- constraint [外键名称] foreign key ([外键列名称]) references [主表名称]([ 主表列名称 ])
    CONSTRAINT foreign_class_id FOREIGN KEY (class_id) REFERENCES class(cid)
);

-- 创建专业与学生关系表

CREATE TABLE correlation_student_course(
    student_id INT,
    course_id INT,
    CONSTRAINT foreign_student_id FOREIGN KEY (student_id) REFERENCES student(sid),
    CONSTRAINT foreign_course_id FOREIGN KEY (course_id) REFERENCES course(cid)
);

-- 插入年纪数据

INSERT  INTO class(name) values 
(
    '大一'
),
(
    '大二'
),
(
    '大三'
)
,(
    '大四'
);
/*
+-----+--------+
| cid | name   |
+-----+--------+
|   1 | 大一   |
|   2 | 大二   |
|   3 | 大三   |
|   4 | 大四   |
+-----+--------+
*/



-- 插入专业数据

INSERT course(name) values('C语言'),('数据结构'),('计算机组成原理'),('高数一');
 
 /*
+-----+-----------------------+
| cid | name                  |
+-----+-----------------------+
|   1 | C语言                 |
|   2 | 数据结构              |
|   3 | 计算机组成原理        |
|   4 | 高数一                |
+-----+-----------------------+
*/

-- 插入学生

INSERT student(name,sex,age,class_id) values
(
    '张三','男' ,18,(SELECT cid FROM  class WHERE name='大一')
),(
    '王五','男' ,21,(SELECT cid FROM  class WHERE name='大一')
),
(
    '赵四','男' ,21,(SELECT cid FROM  class WHERE name='大二')
),(
    '钟无艳','女' ,22,(SELECT cid FROM  class WHERE name='大三') 
),(
    '黄蓉','女' ,18,(SELECT cid FROM  class WHERE name='大一')
),(
    '郭靖','女' ,24,(SELECT cid FROM  class WHERE name='大四')
),(
    '小龙女','女' ,24,(SELECT cid FROM  class WHERE name='大四') 
);


--追加学号
update student set student_number='21001' where name='张三';
update student set student_number='21002' where name='王五';
update student set student_number='21003' where name='赵四';
update student set student_number='21004' where name='钟无艳';
update student set student_number='21005' where name='黄蓉';
update student set student_number='21006' where name='郭靖';
update student set student_number='21007' where name='小龙女';

alter table student modify student_number char(5) not null unique; 

-- 修改外键关联关系

ALTER TABLE STUDENT 

-- 插入关系表数据
-- 同理插入多个数据
INSERT INTO correlation_student_course(
    student_id,course_id
)
VALUES
(
    (SELECT sid FROM student WHERE student_number='21007') , -- 注意这里需要使用括号括起来
    (SELECT cid FROM course WHERE name ='计算机组成原理')
);

2. 查询

查询以上表为例


student 表中数据
+-----+-----------+------+------+----------+----------------+
| sid | name      | sex  | age  | class_id | student_number |
+-----+-----------+------+------+----------+----------------+
|   2 | 张三      | 男   |   18 |        9 | 21001          |
|   3 | 王五      | 男   |   21 |        9 | 21002          |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |
+-----+-----------+------+------+----------+----------------+

course 表中数据
+-----+-----------------------+
| cid | name                  |
+-----+-----------------------+
|   1 | C语言                 |
|   2 | 数据结构              |
|   3 | 计算机组成原理        |
|   4 | 高数一                |
+-----+-----------------------+

class 表中数据
+-----+--------+
| cid | name   |
+-----+--------+
|   9 | 大一   |
|  10 | 大二   |
|  11 | 大三   |
|  12 | 大四   |
+-----+--------+

correlation_student_course 表中数据

+------------+-----------+
| student_id | COURSE_id |
+------------+-----------+
|          2 |         3 |
|          2 |         4 |
|          4 |         1 |
|          4 |         2 |
|          5 |         4 |
|          6 |         4 |
|          3 |         4 |
|          3 |         1 |
|          3 |         2 |
|          7 |         2 |
|          7 |         1 |
|          8 |         1 |
|          8 |         3 |
+------------+-----------+

1>笛卡尔积。

如果直接查询不加限制条件查出来的结果将是笛卡尔积，即两个表的乘积

示例：


SELECT * FROM student ,class;

+-----+-----------+------+------+----------+----------------+-----+--------+
|   2 | 张三      | 男   |   18 |        9 | 21001          |  12 | 大四   |
|   2 | 张三      | 男   |   18 |        9 | 21001          |  11 | 大三   |
|   2 | 张三      | 男   |   18 |        9 | 21001          |  10 | 大二   |
|   2 | 张三      | 男   |   18 |        9 | 21001          |   9 | 大一   |
|   3 | 王五      | 男   |   21 |        9 | 21002          |  12 | 大四   |
|   3 | 王五      | 男   |   21 |        9 | 21002          |  11 | 大三   |
|   3 | 王五      | 男   |   21 |        9 | 21002          |  10 | 大二   |
|   3 | 王五      | 男   |   21 |        9 | 21002          |   9 | 大一   |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |  12 | 大四   |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |  11 | 大三   |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |  10 | 大二   |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |   9 | 大一   |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |  12 | 大四   |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |  11 | 大三   |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |  10 | 大二   |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |   9 | 大一   |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |  12 | 大四   |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |  11 | 大三   |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |  10 | 大二   |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |   9 | 大一   |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |  12 | 大四   |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |  11 | 大三   |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |  10 | 大二   |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |   9 | 大一   |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |  12 | 大四   |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |  11 | 大三   |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |  10 | 大二   |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |   9 | 大一   |
+-----+-----------+------+------+----------+----------------+-----+--------+

--这样的查询是没有什么意义的

2>内联查询

一般求的是两个表的交集。

显示内联接语法
select [字段列表] from [表名1] inner join[ 表名2] on [条件] --inner 可选字段

隐式内连接：使用where条件消除无用数据。


SELECT * FROM student,class WHERE student.class_id=class.cid;

+-----+-----------+------+------+----------+----------------+-----+--------+
| sid | name      | sex  | age  | class_id | student_number | cid | name   |
+-----+-----------+------+------+----------+----------------+-----+--------+
|   2 | 张三      | 男   |   18 |        9 | 21001          |   9 | 大一   |
|   3 | 王五      | 男   |   21 |        9 | 21002          |   9 | 大一   |
|   6 | 黄蓉      | 女   |   18 |        9 | 21005          |   9 | 大一   |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |  10 | 大二   |
|   5 | 钟无艳    | 女   |   22 |       11 | 21004          |  11 | 大三   |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |  12 | 大四   |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |  12 | 大四   |
+-----+-----------+------+------+----------+----------------+-----+--------+

显示内连接

-- select [字段列表] from [表名1] inner join [表名2] on [条件]

SELECT s.name 姓名,s.sex 性别,c.name 年级 FROM student s INNER JOIN class c on s.class_id=c.cid;

+-----------+--------+--------+
| 姓名      | 性别   | 年级   |
+-----------+--------+--------+
| 张三      | 男     | 大一   |
| 王五      | 男     | 大一   |
| 黄蓉      | 女     | 大一   |
| 赵四      | 男     | 大二   |
| 钟无艳    | 女     | 大三   |
| 郭靖      | 女     | 大四   |
| 小龙女    | 女     | 大四   |
+-----------+--------+--------+

2>外连接查询

左连接
select [字段列表] from [表1] left outer join [表2] on [条件];-- outer可以不写
表一的全集与表一表二的交集的和
select [字段列表] from [表1] right outer join [表2] on [条件];-- -- outer可以不写
表二的全集与表一表二的交集的和


SELECT 
    s.student_number 学号, s.name 姓名,c.name 年级
FROM
student s 
LEFT OUTER JOIN 
class c 
ON s.class_id=c.cid;

+--------+-----------+--------+
| 学号   | 姓名      | 年级   |
+--------+-----------+--------+
| 21001  | 张三      | 大一   |
| 21002  | 王五      | 大一   |
| 21003  | 赵四      | 大二   |
| 21004  | 钟无艳    | 大三   |
| 21005  | 黄蓉      | 大一   |
| 21006  | 郭靖      | 大四   |
| 21007  | 小龙女    | 大四   |
+--------+-----------+--------+

-- 右连接
SELECT 
     s.student_number 学号, s.name 姓名,c.name 年级
FROM 
    class c 
RIGHT OUTER JOIN
    student s
ON
c.cid=s.class_id;

+--------+-----------+--------+
| 学号   | 姓名      | 年级   |
+--------+-----------+--------+
| 21001  | 张三      | 大一   |
| 21002  | 王五      | 大一   |
| 21003  | 赵四      | 大二   |
| 21004  | 钟无艳    | 大三   |
| 21005  | 黄蓉      | 大一   |
| 21006  | 郭靖      | 大四   |
| 21007  | 小龙女    | 大四   |
+--------+-----------+--------+

3>子查询

查询中嵌套查询，称嵌套查询为子查询。

子查询不同情况:

子查询的结果是单行单列的]

-- 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
1. 查询年龄大于平均年龄的人的姓名年龄
SELECT  name,age FROM student WHERE age>(SELECT AVG(age) FROM student);
+-----------+------+
| name      | age  |
+-----------+------+
| 钟无艳    |   22 |
| 郭靖      |   24 |
| 小龙女    |   24 |
+-----------+------+

子查询的结果是多行单列的

-- 子查询可以作为条件，使用运算符in来判断
-- 查询有C语言课程，与数据结构课程的人的信息
SELECT 
   * 
FROM 
    student 
WHERE 

sid IN
    (
    SELECT 
        student_id
    FROM 
        correlation_student_course
    WHERE
        course_id 
    IN
        (
        SELECT 
            cid 
        FROM 
            course
        WHERE name= "C语言" OR name='数据结构'
        )
    );

+-----+-----------+------+------+----------+----------------+
| sid | name      | sex  | age  | class_id | student_number |
+-----+-----------+------+------+----------+----------------+
|   3 | 王五      | 男   |   21 |        9 | 21002          |
|   4 | 赵四      | 男   |   21 |       10 | 21003          |
|   7 | 郭靖      | 女   |   24 |       12 | 21006          |
|   8 | 小龙女    | 女   |   24 |       12 | 21007          |
+-----+-----------+------+------+----------+----------------+

子查询的结果是多行多列的

--  子查询可以作为一张虚拟表参与查询
-- 查询 大于20岁 对学生信息

SELECT 
    s.name 姓名,s.sex 性别,s.age 年龄,c.name 年级
FROM 
    class c,
    (
    SELECT
        *
    FROM
        student
    WHERE 
        age>20
    ) s
WHERE
    c.cid=s.class_id;

+-----------+--------+--------+--------+
| 姓名      | 性别   | 年龄   | 年级   |
+-----------+--------+--------+--------+
| 王五      | 男     |     21 | 大一   |
| 赵四      | 男     |     21 | 大二   |
| 钟无艳    | 女     |     22 | 大三   |
| 郭靖      | 女     |     24 | 大四   |
| 小龙女    | 女     |     24 | 大四   |
+-----------+--------+--------+--------+

目录CONTENT

mysql-表关系操作

表关系操作

一、一对一关系

二、一对多（多对一）

1. 创建

2. 查询

1>笛卡尔积。

2>内联查询

2>外连接查询

3>子查询

评论区

mysql-表关系操作

表关系操作

一、一对一关系

二、 一对多（多对一）

1. 创建

2. 查询

1>笛卡尔积。

2>内联查询

2>外连接查询

3>子查询

评论区

二、一对多（多对一）