1.初始化脚本
/*
Navicat MySQL Data Transfer
Source Server : 123.207.254.158
Source Server Version : 50718
Source Host : 123.207.254.158:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50718
File Encoding : 65001
Date: 2017-05-12 13:42:48
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
`C` int(11) DEFAULT NULL,
`Cname` varchar(32) DEFAULT NULL,
`T` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('1', '语文', '1');
INSERT INTO `Course` VALUES ('2', '数学', '2');
INSERT INTO `Course` VALUES ('3', '英语', '3');
INSERT INTO `Course` VALUES ('4', '物理', '4');
-- ----------------------------
-- Table structure for Sc
-- ----------------------------
DROP TABLE IF EXISTS `Sc`;
CREATE TABLE `Sc` (
`S` int(11) DEFAULT NULL,
`C` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Sc
-- ----------------------------
INSERT INTO `Sc` VALUES ('1', '1', '56');
INSERT INTO `Sc` VALUES ('1', '2', '78');
INSERT INTO `Sc` VALUES ('1', '3', '67');
INSERT INTO `Sc` VALUES ('1', '4', '58');
INSERT INTO `Sc` VALUES ('2', '1', '79');
INSERT INTO `Sc` VALUES ('2', '2', '81');
INSERT INTO `Sc` VALUES ('2', '3', '92');
INSERT INTO `Sc` VALUES ('2', '4', '68');
INSERT INTO `Sc` VALUES ('3', '1', '91');
INSERT INTO `Sc` VALUES ('3', '2', '47');
INSERT INTO `Sc` VALUES ('3', '3', '88');
INSERT INTO `Sc` VALUES ('3', '4', '56');
INSERT INTO `Sc` VALUES ('4', '2', '88');
INSERT INTO `Sc` VALUES ('4', '3', '90');
INSERT INTO `Sc` VALUES ('4', '4', '93');
INSERT INTO `Sc` VALUES ('5', '1', '46');
INSERT INTO `Sc` VALUES ('5', '3', '78');
INSERT INTO `Sc` VALUES ('5', '4', '53');
INSERT INTO `Sc` VALUES ('6', '1', '35');
INSERT INTO `Sc` VALUES ('6', '2', '68');
INSERT INTO `Sc` VALUES ('6', '4', '71');
-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`S` int(11) DEFAULT NULL,
`Sname` varchar(32) DEFAULT NULL,
`Sage` int(11) DEFAULT NULL,
`Ssex` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `Student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `Student` VALUES ('3', '张三', '17', '男');
INSERT INTO `Student` VALUES ('4', '李四', '18', '女');
INSERT INTO `Student` VALUES ('5', '王五', '17', '男');
INSERT INTO `Student` VALUES ('6', '赵六', '19', '女');
-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
`T` int(11) DEFAULT NULL,
`Tname` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('1', '叶平');
INSERT INTO `Teacher` VALUES ('2', '贺高');
INSERT INTO `Teacher` VALUES ('3', '杨艳');
INSERT INTO `Teacher` VALUES ('4', '周磊');
SELECT s,Sname FROM Student WHERE S NOT IN (
SELECT DISTINCT S FROM Teacher,Course,Sc WHERE Sc.C=Course.C AND Course.T=Teacher.T AND
Teacher.Tname='叶平'
)
- 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
SELECT Sname,Sc.S FROM Sc,Student WHERE C=1 AND Student.S=Sc.S
AND Student.S IN
(SELECT Sc.S FROM Sc,Student WHERE C=2 AND Student.S=Sc.S)
SELECT Sname,Sc.S FROM Sc,Student WHERE C=1 AND Student.S=Sc.S
AND exists
(SELECT * FROM Sc WHERE C=2 AND Student.S=Sc.S)
- 查询学过“叶平”老师所教的所有课的同学的学号、姓名
SELECT Sname,Student.S FROM Student,Sc WHERE Student.S=Sc.S AND Sc.C IN
(SELECT C FROM Teacher ,Course WHERE Teacher.T=Course.T AND Tname='叶平' )
