2017/10/20课堂相关代码

创表

• 学生表student

id name code idcard clazz_id 1 张三 001 123 1 2 李四 002 456 1 3 王五 003 789 2

DROP TABLE IF EXISTS student;
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
code varchar(255) DEFAULT NULL,
idcard varchar(255) DEFAULT NULL,
clazz_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

• 账号表account

id student_id username password 1 1 zhangsan 123456 2 2 lisi 456 3 3 wangwu 789

DROP TABLE IF EXISTS account;
CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) DEFAULT NULL,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

*班级表clazz

id code name 1 96 java96 2 93 java93

DROP TABLE IF EXISTS clazz;
CREATE TABLE clazz (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

• 课程表course

id code name diff 1 k1 001 HTML 2 k2 002 CSS 3 k3 003 JS

DROP TABLE IF EXISTS course;
CREATE TABLE course (
id int(11) NOT NULL AUTO_INCREMENT,
code varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
diff int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

• 选修表student_course

id student_id course_id 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2

CREATE TABLE student_course (
id int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) DEFAULT NULL,
course_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

query语句

自然连接查询

使用WHERE 条件将两个表之间进行关联查询

– 查询学员的班级id 学生学号 学生姓名 所在班级 班级学号

SELECT s.clazz_id AS ‘班级id’,
s.code AS ‘学生学号’,s.name AS ‘学生姓名’,
c.name as ‘所在班级’,c.code as ‘班级学号’
FROM student AS s , clazz AS c
WHERE s.clazz_id = c.id

内连接查询

– 内连接查询是可以使用自然连接查询替代的
– 但是效率方面,内连接更高
– 内连接查询

SELECT s.code,s.name,c.name
FROM student as s
INNER JOIN clazz AS c
on s.clazz_id=c.id – 推荐!

左外连接查询

以左边为主表,左表中的数据都会被显示出来,关联的右边中,如果存在符合条件的数据

name被关联出并显示 如果没有,则会显示NULL

SELECT s.code, s.name,c.name
FROM student AS s
LEFT JOIN clazz AS c
ON s.clazz_id = c.id

综合查询

查询学员选课信息,要求显示出班级 账号 课程等基础信息

SELECT c.name as ‘班级’,ac.username AS ‘账户名’,
cs.name AS ‘课程信息’
FROM student_course AS sc
LEFT JOIN student AS s
ON s.id = sc.student_id
LEFT JOIN clazz AS c
ON c.id = s.clazz_id
LEFT JOIN course AS cs
ON cs.id = sc.course_id
LEFT JOIN account AS ac
ON ac.student_id = s.id