sql语句 多表查询

多表查询：

学生表、班级表、课程表、班级课程表

因为 banji表 的字段属性是独立的要先建立 banji表

班级和学生是一对多的关系，一个班级对应多个学生，多的一方 student 表要建立一个外键banji_id 来和一的一方 banji表 的 id 建立关系

班级和课程是多对多的关系，一个班级可以学习多门课程，一门课程也可以被多个班级学习，需要创建第三个表 banji_course表，分别把 banji_id 和 course_id 作为外键，又称为联合主键

关系图如下：

---

sql语句实现

-- 班级表

CREATE TABLE banji(

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL

);

INSERT INTO banji(`name`) VALUES('java1807'),('java1812');

SELECT * FROM banji;

 这里涉及到一个外键的知识

数据库SQL语言中，foreign key和references的区别是什么？

假设两张表,表1(学号,姓名,性别),学号为主键. 表2(学号,课程,成绩). 可以为表2的学号定义外键(FOREIGN KEY),该外键的取值范围参照(REFERENCES)表1的学号

CONSTRAINT是对某列定义约束, 如上表1中的"性别",可以定义约束,将取值限定为不是"男",就是"女". CHECK(性别 IN (‘男’,‘女’))

-- 学生表

CREATE TABLE student(

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,

    age INT,

    gender CHAR(1),

    banji_id INT,

    FOREIGN KEY(banji_id) REFERENCES banji(id)

);

INSERT INTO student(`name`,age,gender,banji_id) 

VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);

INSERT INTO student(`name`,age,gender,banji_id) 

VALUES('张三',20,'男',3); banji_id为外键，范围要参照REFERENCES banji(id)的班级表的id；

SELECT * FROM student;

-- 课程表

CREATE TABLE course(

    id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,

    credit INT COMMENT '学分'

); COMMENT 注释

INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);

SELECT * FROM course;

-- 班级课程表

CREATE TABLE banji_course(

    -- id int PRIMARY KEY AUTO_INCREMENT,

    banji_id INT,

    course_id INT,

    PRIMARY KEY(banji_id,course_id), -- 联合主键

    FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键

    FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键

);

INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);

SELECT * FROM banji_course;

-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件

-- 查询班级是java1812班所有学生信息

SELECT * FROM student WHERE banji_id=2;

SELECT id FROM banji WHERE `name`='java1812';

SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='java1812');

-- 班级是java1807班或者java1812班所有学生信息

SELECT * FROM student WHERE banji_id=1 OR banji_id=2;

SELECT * FROM student WHERE banji_id IN(1,2);

SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812'; -- 1,2

SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812');

-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果

关联查询：inner join

SELECT student.id,student.`name`,student.age,student.gender,banji.name AS '班级名称' FROM

student INNER JOIN banji ON student.banji_id=banji.id;

AS 是一次性的，只在当前语句成立。

-- 列出所有学生学习的课程名称

-- 学生姓名   班级名称   课程名称    学分

 

总结：多表查询主要是注意以下两点

1、整个查询涉及到几张表，涉及到几张表就连接这几张表。

2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）