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.nameFROM t_student AS s,t_clazz AS cWHERE s.clazz_id = c.id;

2.内连接查询

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

3.左连接查询

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