2017.10.20课堂代码

2017.10.20课堂代码

1. 班级表clazz

id code name 1 J96 JAVA96班 2 J93 JAVA93班

2. 学员帐号表account

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

3. 课程表course

id name 1 HTML 2 CSS 3 JS

4. 学员课程关系表student_course

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

5. 学员表student

id name code idcard clazz_id 1 张三 1 123 1 2 李四 2 456 2 3 王五 1 135 2 4 李六 2 246 -

navicat 导出程序代码

/*Navicat MySQL Data TransferSource Server         : LJNSource Server Version : 50556Source Host           : localhost:3306Source Database       : 10.20课堂内容代码Target Server Type    : MYSQLTarget Server Version : 50556File Encoding         : 65001Date: 2017-10-20 16:06:01*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for account-- ----------------------------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`),  UNIQUE KEY `student_id` (`student_id`),  CONSTRAINT `fk_01` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ Records of account-- ----------------------------INSERT INTO `account` VALUES ('1', '1', 'zhangsan', '123456');INSERT INTO `account` VALUES ('2', '2', 'lisi', '123456');INSERT INTO `account` VALUES ('3', '3', 'wangwu', '123456');-- ------------------------------ Table structure for clazz-- ----------------------------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=3 DEFAULT CHARSET=utf8;-- ------------------------------ Records of clazz-- ----------------------------INSERT INTO `clazz` VALUES ('1', 'J96', 'JAVA96班');INSERT INTO `clazz` VALUES ('2', 'J93', 'JAVA93班');-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('1', 'HTML');INSERT INTO `course` VALUES ('2', 'CSS');INSERT INTO `course` VALUES ('3', 'JavaScript');-- ------------------------------ Table structure for student-- ----------------------------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`),  KEY `fk_02` (`clazz_id`),  CONSTRAINT `fk_02` FOREIGN KEY (`clazz_id`) REFERENCES `clazz` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', '张三', '1', '123', '1');INSERT INTO `student` VALUES ('2', '李四', '2', '456', '2');INSERT INTO `student` VALUES ('3', '王五', '1', '135', '2');INSERT INTO `student` VALUES ('4', '李六', '2', '246', null);-- ------------------------------ Table structure for student_course-- ----------------------------DROP TABLE IF EXISTS `student_course`;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`),  KEY `fk_03` (`student_id`),  KEY `fk_04` (`course_id`),  CONSTRAINT `fk_04` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),  CONSTRAINT `fk_03` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student_course-- ----------------------------INSERT INTO `student_course` VALUES ('1', '1', '1');INSERT INTO `student_course` VALUES ('2', '1', '2');INSERT INTO `student_course` VALUES ('3', '1', '3');INSERT INTO `student_course` VALUES ('4', '2', '1');INSERT INTO `student_course` VALUES ('5', '2', '2');

查询数据

自然连接查询

-- 查询学员的学号，姓名，所在班级姓名SELECT s.clazz_id,s.`code`,s.`name`,c.id,c.`name`FROM student AS s,clazz AS cWHERE s.clazz_id=c.id;

内链接查询

-- 内链接查询SELECT s.`code`,s.`name`,c.`name`FROM student AS sINNER JOIN clazz AS cON s.clazz_id=c.id;

左外连接查询

-- 左外连接SELECT s.`code`,s.`name`,c.`name`FROM student AS sLEFT JOIN clazz AS cON s.clazz_id=c.id;

综合查询

-- 查询学员选课信息，要求显示出班级，账号，课程等基础信息SELECT cl.`name` AS '班级名称',s.`code` AS '学号',s.`name` AS '姓名',a.username AS '账号',a.`password` AS '账号密码',c.`name` AS '课程名称'FROM student_course AS scLEFT JOIN student AS s ON sc.student_id=s.idLEFT JOIN course AS c ON sc.course_id=c.idLEFT JOIN clazz AS cl ON s.clazz_id=cl.idLEFT JOIN account AS a ON a.student_id=s.id