2017-10-20课堂代码

2017-10-20课堂代码-多表数据操作

创建五张表

1. 学生表t_student

id	code	name	id_card_number	clazz_id
1	01	张三	123	1
2	02	李四	456	1
3	01	王五	236	2
4	03	赵六	249	null

-- 创建学生表
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        INT,
    CONSTRAINT fk_student_clazz FOREIGN KEY (clazz_id) REFERENCES t_clazz(id)
);

2.班级表t_clazz

id	name
1	Java96班
2	Java93班

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

3.课程表t_course

id	name
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.账号表t_account

id	student_id	userName	password
1	1	zhangsan	123456
2	2	lisi	123456
3	3	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.学生课程表t_student_course

id	student_id	course_id
1	1	1
2	1	2
3	1	3
4	2	1
5	2	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.自然连接查询，查询学员学号，姓名，所在班级名称

-- 自然连接查询,查询学员学号，姓名，所在班级名称
SELECT s.clazz_id,s.code,s.name,c.id,c.name
FROM t_student AS s,t_clazz AS c
WHERE s.clazz_id = c.id;

2.内连接查询

-- 内连接查询
SELECT s.code AS '学号',s.name AS '姓名',c.name AS '班级名称' FROM t_student AS s
INNER JOIN t_clazz AS c ON s.clazz_id = c.id;

3.左连接查询

-- 左外连接，符合条件的被显示，没有的显示null
SELECT s.code AS '学号',s.name AS '姓名',c.name AS '班级名称' FROM t_student AS s
LEFT JOIN t_clazz AS 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 t_student_course AS sc
LEFT JOIN t_student AS s ON sc.student_id = s.id
LEFT JOIN t_course AS c ON sc.course_id = c.id
LEFT JOIN t_clazz AS cl ON s.clazz_id = cl.id
LEFT JOIN t_account AS a ON a.student_id = s.id;