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 cWHERE 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 scLEFT JOIN student s ON sc.student_id = s.idLEFT JOIN course c on sc.course_id = c.idLEFT JOIN clazz cl on s.clazz_id = cl.idLEFT JOIN account a on a.student_id = s.id