2017.10.20笔记：多表数据操作

一.创建表

1.学员表

ID	学号	姓名	身份证号	所属班级
1	111	张三	123	J96
2	222	李四	456	J93
3	333	王五	236	J93

-- 创建学员表
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student(
    id              INT PRIMARY KEY AUTO_INCREMENT,
    code            VARCHAR(255),
    name            VARCHAR(255),
    id_card_number  VARCHAR(255),
    clazz_id        VARCHAR(255),
    CONSTRAINT fk_student_clazz FOREIGN KEY (clazz_id) REFERENCES t_clazz(id)
);

2.班级表

ID	名称
1	JAVA96班
2	JAVA93班

-- 创建班级表
DROP TABLE IF EXISTS t_clazz;
CREATE TABLE t_clazz(
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(255)
);

3.课程表

ID	课程名称
1	HTML
2	CSS
3	JavaScript

DROP TABLE IF EXISTS t_course;
CREATE TABLE t_course(
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(255)
);

4.学员账号表

ID	学员ID	账号	密码
1	s1	zhangsan	123456
2	s2	lisi	123456
3	s3	wangwu	123456

-- 创建学员账号表
DROP TABLE IF EXISTS t_account;
CREATE TABLE t_account(
    id              INT PRIMARY KEY AUTO_INCREMENT,
    student_id      INT UNIQUE,
    userName        VARCHAR(255),
    password        VARCHAR(255),
    CONSTRAINT fk_account_student FOREIGN KEY (student_id) REFERENCES t_student(id)
)

5.学员课程关系表

ID	学员ID	课程ID
1	s1	1
2	s1	2
3	s1	3
4	s2	1
5	s2	2

-- 创建学员课程关系表
DROP TABLE IF EXISTS t_student_course;
CREATE TABLE t_student_course(
    id              INT PRIMARY KEY AUTO_INCREMENT,
    student_id      INT,
    course_id       INT,
    CONSTRAINT fk_middle_student FOREIGN KEY (student_id) REFERENCES t_student(id),
    CONSTRAINT fk_middle_course FOREIGN KEY (course_id) REFERENCES t_course(id)
);

二.表关联查询

1.自然连接查询
使用 WHERE 条件将两个表之间进行关联查询

-- 查询学员的学号、姓名、所在班级名称
SELECT s.clazz_id,s.code,s.`name`,c.id,c.`name`
FROM student as s, clazz as c
WHERE S.clazz_id = C.id

2.内连接查询

-- 内连接查询
SELECT s.`code`, s.`name`, c.`name` FROM student s 
INNER JOIN clazz c ON s.clazz_id = c.id

3.左外连接查询
以左表为主表，左表中的数据都会被显示出来，关联的右表中，如果存在符合条件的数据，那么会被关联出并显示，如果没有，则会显示 NULL。

-- 左外连接
SELECT s.`code`, s.`name`, c.`name` FROM student s 
LEFT JOIN clazz c ON s.clazz_id = c.id

4.综合查询

-- 查询学员选课信息，要求显示出班级、账号、课程等基础信息
SELECT 
cl.name as '班级名称',
s.`code` as '学号',
s.`name` as '姓名', 
a.username as '账号',
a.password as '账号密码',
c.`name` as '课程名称'
FROM student_course sc
LEFT JOIN student s ON sc.student_id = s.id
LEFT JOIN course c on sc.course_id = c.id
LEFT JOIN clazz cl on s.clazz_id = cl.id
LEFT JOIN account a on a.student_id = s.id