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 Transfer

Source Server         : LJN
Source Server Version : 50556
Source Host           : localhost:3306
Source Database       : 10.20课堂内容代码

Target Server Type    : MYSQL
Target Server Version : 50556
File Encoding         : 65001

Date: 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 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;

左外连接查询

-- 左外连接
SELECT s.`code`,s.`name`,c.`name`
FROM student AS s
LEFT JOIN clazz AS c
ON 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 sc
LEFT JOIN student AS s ON sc.student_id=s.id
LEFT JOIN course AS c ON sc.course_id=c.id
LEFT JOIN clazz AS cl ON s.clazz_id=cl.id
LEFT JOIN account AS a ON a.student_id=s.id