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