mysql数据库实验题
- 格式：doc
- 大小：271.50 KB
- 文档页数：6
《数据库第二次作业——上机实验题》
实验测试环境：Windows10专业版 / SQL 2012 Developer Edition X64
实验一：交互式SQL的使用
实验要求：
1,创建Student数据库，包括Students,Courses,SC表，表结构如下：
Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)
SC(SNO,CNO,GRADE)
(注：下划线表示主键，斜体表示外键)，并插入一定数据。
1）创建Students表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for Students
-- ----------------------------
DROP TABLE IF EXISTS `Students`;
CREATE TABLE `Students` (
`SNO` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT
NULL,
`SNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
DEFAULT NULL,
`SEX` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
DEFAULT NULL,
`BDATE` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`HEIGHT` double DEFAULT NULL,
`DEPARTMENT` varchar(255) CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
2）创建Courses表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for Courses
-- ----------------------------
DROP TABLE IF EXISTS `Courses`;
CREATE TABLE `Courses` (
`CNO` varchar(255) NOT NULL,
`CNAME` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`LHOUR` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`CREDIT` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`SEMESTER` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
3）创建SC表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for SC
-- ----------------------------
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC` (
`SNO` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`CNO` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`GRADE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
DEFAULT NULL,
KEY `SNO` (`SNO`),
KEY `CNO` (`CNO`),
CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `Students` (`SNO`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `Courses` (`CNO`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
4)插入数据到Students表
INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
VALUES('1001','李小','男','2019-05-31',1.80,'软件系');
SELECT * FROM Students;
5）插入Courses
INSERT INTO Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER) VALUES('cs_1001','起飞技术','6','99','春季');
SELECT * FROM Courses;
6）插入SC
INSERT INTO SC(SNO,CNO,GRADE) VALUES('1001','cs_1001','80');
SELECT * FROM SC;
2．完成如下的查询要求及更新的要求。
（1）查询身高大于1.70m的男生的学号和姓名；
select SNO,SNAME from Students where HEIGHT>1.7 AND SEX='男'
（2）查询计算机系秋季所开课程的课程号和学分数；
select CNO,CREDIT from Courses where substring(CNO, 4, 5) >1000 AND SEMESTER='春季';
