基于oracle数据库进行sql基础练习

2018-06-11 - 6,642 Views - 0 Goods - Nothing

数据库脚本如下直接导入即可


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级ID',
`className` varchar(32) DEFAULT NULL COMMENT '班级名称',
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '一年级');
INSERT INTO `class` VALUES ('2', '二年级');
INSERT INTO `class` VALUES ('3', '三年级');
INSERT INTO `class` VALUES ('4', '四年级');
INSERT INTO `class` VALUES ('5', '五年级');
INSERT INTO `class` VALUES ('6', '六年级');

-- ----------------------------
-- Table structure for `class_student`
-- ----------------------------
DROP TABLE IF EXISTS `class_student`;
CREATE TABLE `class_student` (
`id` int(11) NOT NULL COMMENT '班级与学生关联表ID',
`cid` int(11) NOT NULL COMMENT '班级ID',
`sid` int(11) NOT NULL COMMENT '学生ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class_student
-- ----------------------------
INSERT INTO `class_student` VALUES ('1', '1', '1');
INSERT INTO `class_student` VALUES ('2', '1', '2');
INSERT INTO `class_student` VALUES ('3', '2', '3');
INSERT INTO `class_student` VALUES ('4', '2', '4');
INSERT INTO `class_student` VALUES ('5', '3', '5');
INSERT INTO `class_student` VALUES ('6', '4', '6');
INSERT INTO `class_student` VALUES ('7', '5', '7');
INSERT INTO `class_student` VALUES ('8', '6', '8');
INSERT INTO `class_student` VALUES ('9', '6', '9');
INSERT INTO `class_student` VALUES ('10', '5', '10');

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`courseId` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程表ID',
`courseName` varchar(32) DEFAULT NULL COMMENT '课程名称',
PRIMARY KEY (`courseId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生表ID',
`stuName` varchar(32) DEFAULT NULL COMMENT '学生名称',
`age` varchar(11) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '刘一', '15');
INSERT INTO `student` VALUES ('2', '陈二', '16');
INSERT INTO `student` VALUES ('3', '张三', '14');
INSERT INTO `student` VALUES ('4', '李四', '15');
INSERT INTO `student` VALUES ('5', '王五', '16');
INSERT INTO `student` VALUES ('6', '赵六', '13');
INSERT INTO `student` VALUES ('7', '田七', '14');
INSERT INTO `student` VALUES ('8', '周八', '15');
INSERT INTO `student` VALUES ('9', '吴九', '14');
INSERT INTO `student` VALUES ('10', '郑十', '13');
INSERT INTO `student` VALUES ('11', '小红', '16');
INSERT INTO `student` VALUES ('12', '小明', '15');

-- ----------------------------
-- Table structure for `student_score`
-- ----------------------------
DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生课程成绩表ID',
`sid` int(11) NOT NULL COMMENT '学生ID',
`courseid` int(11) NOT NULL COMMENT '课程ID',
`score` varchar(11) NOT NULL COMMENT '学生成绩分数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_score
-- ----------------------------
INSERT INTO `student_score` VALUES ('1', '1', '1', '90');
INSERT INTO `student_score` VALUES ('2', '1', '2', '100');
INSERT INTO `student_score` VALUES ('3', '1', '3', '99');
INSERT INTO `student_score` VALUES ('4', '2', '1', '88');
INSERT INTO `student_score` VALUES ('5', '2', '2', '99');
INSERT INTO `student_score` VALUES ('6', '2', '3', '100');
INSERT INTO `student_score` VALUES ('7', '3', '1', '92');
INSERT INTO `student_score` VALUES ('8', '3', '2', '75');
INSERT INTO `student_score` VALUES ('9', '3', '3', '89');
INSERT INTO `student_score` VALUES ('10', '4', '1', '100');
INSERT INTO `student_score` VALUES ('11', '4', '2', '78');
INSERT INTO `student_score` VALUES ('12', '4', '3', '97');
INSERT INTO `student_score` VALUES ('13', '5', '1', '87');
INSERT INTO `student_score` VALUES ('14', '5', '2', '99');
INSERT INTO `student_score` VALUES ('15', '5', '3', '100');
INSERT INTO `student_score` VALUES ('16', '6', '1', '88');
INSERT INTO `student_score` VALUES ('17', '6', '2', '98');
INSERT INTO `student_score` VALUES ('18', '6', '3', '99');
INSERT INTO `student_score` VALUES ('19', '7', '1', '98');
INSERT INTO `student_score` VALUES ('20', '7', '2', '60');
INSERT INTO `student_score` VALUES ('21', '7', '3', '58');
INSERT INTO `student_score` VALUES ('22', '8', '1', '65');
INSERT INTO `student_score` VALUES ('23', '8', '2', '45');
INSERT INTO `student_score` VALUES ('24', '8', '3', '78');
INSERT INTO `student_score` VALUES ('25', '9', '1', '99');
INSERT INTO `student_score` VALUES ('26', '9', '2', '100');
INSERT INTO `student_score` VALUES ('27', '9', '3', '87');
INSERT INTO `student_score` VALUES ('28', '10', '1', '86');
INSERT INTO `student_score` VALUES ('29', '10', '2', '88');
INSERT INTO `student_score` VALUES ('30', '10', '3', '68');

-- ----------------------------
-- Table structure for `teachers`
-- ----------------------------
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
`tid` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师ID',
`teacherName` varchar(32) DEFAULT NULL COMMENT '教师姓名',
`sex` varchar(11) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teachers
-- ----------------------------
INSERT INTO `teachers` VALUES ('1', '刘老师', '男');
INSERT INTO `teachers` VALUES ('2', '陈老师', '男');
INSERT INTO `teachers` VALUES ('3', '张老师', '女');
INSERT INTO `teachers` VALUES ('4', '李老师', '女');
INSERT INTO `teachers` VALUES ('5', '王老师', '男');
INSERT INTO `teachers` VALUES ('6', '赵老师', '女');
INSERT INTO `teachers` VALUES ('7', '孙老师', '女');

-- ----------------------------
-- Table structure for `teachers_class`
-- ----------------------------
DROP TABLE IF EXISTS `teachers_class`;
CREATE TABLE `teachers_class` (
`id` int(11) NOT NULL COMMENT '教师班级关联表ID',
`tid` int(11) NOT NULL COMMENT '教师表ID',
`cid` int(11) NOT NULL COMMENT '班级表ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teachers_class
-- ----------------------------
INSERT INTO `teachers_class` VALUES ('1', '1', '1');
INSERT INTO `teachers_class` VALUES ('2', '2', '2');
INSERT INTO `teachers_class` VALUES ('3', '3', '3');
INSERT INTO `teachers_class` VALUES ('4', '4', '4');
INSERT INTO `teachers_class` VALUES ('5', '5', '5');
INSERT INTO `teachers_class` VALUES ('6', '6', '6');
INSERT INTO `teachers_class` VALUES ('7', '7', '7');

-- ----------------------------
-- Table structure for `teachers_course`
-- ----------------------------
DROP TABLE IF EXISTS `teachers_course`;
CREATE TABLE `teachers_course` (
`id` int(11) NOT NULL,
`tid` int(11) NOT NULL COMMENT '教师ID',
`courseid` int(11) NOT NULL COMMENT '课程ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teachers_course
-- ----------------------------
INSERT INTO `teachers_course` VALUES ('1', '1', '1');
INSERT INTO `teachers_course` VALUES ('2', '2', '2');
INSERT INTO `teachers_course` VALUES ('3', '3', '3');

下面进行基础sql练习


-- 查询一年级有多少个学生 
SELECT s.sid, s.stuName, s.age, c.className FROM student s 
LEFT JOIN class_student cs ON s.sid = cs.sid
LEFT JOIN class c ON c.cid = cs.cid 
WHERE c.cid=1;


-- 查询年龄大于等于15岁的学生并且查询出来班级和授课老师
SELECT s.age, s.stuName, c.className,t.teacherName FROM student s
LEFT JOIN class_student cs ON cs.sid = s.sid
LEFT JOIN class c ON c.cid = cs.cid
LEFT JOIN teachers_class tc ON tc.cid = c.cid
LEFT JOIN teachers t ON t.tid = tc.tid
WHERE
s.age >=15;

-- 查询所有学生的各科成绩信息,并且查询出班级,课程名称,分数,以及授课教师名字
SELECT s.sid, s.age, s.stuName, sce.score ,sce.courseid, c.courseName, cl.className, t.teacherName
FROM student s
LEFT JOIN student_score sce ON sce.sid = s.sid
LEFT JOIN course c ON c.courseId = sce.courseid
LEFT JOIN class_student cs ON cs.sid = s.sid
LEFT JOIN class cl ON cl.cid = cs.cid
LEFT JOIN teachers_course te ON te.courseid = c.courseId
LEFT JOIN teachers_class tc ON tc.cid = cl.cid
LEFT JOIN teachers t ON t.tid = tc.tid
order by sid;

-- 查询某个学生的班级信息(LEFT JOIN)
SELECT c.className, s.stuName, s.age FROM class c 
LEFT JOIN class_student cs ON cs.cid = c.cid
LEFT JOIN student s ON s.sid = cs.sid
WHERE s.stuName = '张三';

-- 查询某个学生的班级信息(子查询进行)
SELECT * FROM class c WHERE c.cid = (SELECT cs.cid FROM class_student cs WHERE cs.sid = (SELECT s.sid FROM student s WHERE s.stuName = '张三'));

SELECT * FROM class c WHERE c.cid IN(
	SELECT cs.cid FROM class_student cs, student s WHERE cs.sid = s.sid AND s.stuName = '张三'
)

 
-- 查询某个老师的班级信息(LEFT JOIN)
SELECT c.className, t.teacherName, t.sex FROM class c
LEFT JOIN teachers_class tc ON tc.cid = c.cid
LEFT JOIN teachers t ON t.tid = tc.tid
WHERE t.teacherName = '刘老师';
-- 查询刘老师的班级信息(子查询进行)
SELECT * FROM class c WHERE c.cid IN(
SELECT tc.cid FROM teachers_class tc, teachers t WHERE tc.tid = t.tid AND t.teacherName = "刘老师"
);


-- 查询分配了班级的所有学生信息(学生有2人没有进行班级分配返回结果一定是少2条记录才对)
-- 基于左连接实现
SELECT * FROM student s 
LEFT JOIN class_student cs ON cs.sid = s.sid
LEFT JOIN class c ON c.cid = cs.cid
WHERE cs.cid IS NOT NULL;

-- 基于内连接实现
SELECT * FROM student s 
INNER JOIN class_student cs ON cs.sid = s.sid
INNER JOIN class c ON c.cid = cs.cid;
转载请注明原文链接:首页 -> 技术交流 -> JAVA开发 -> 基于oracle数据库进行sql基础练习
  • 支付宝打赏
  • 微信打赏

China.BeiJing

如果说人生是自我编写的程序,那么青春就是其中意味深长的代码