SQL典范实习36题-1建表及数据拔出
奇闻趣事 2023-05-04 17:38www.bnfh.cn奇闻趣事
SQL经典练习36题-1建表及数据插入
创建表的语句如下
-- 创建学生表
CREATE TABLE STUDENT (
SD INT PRIMARY KEY,-- 学号
SNAME VARCHAR ( 50 ) NOT NULL,-- 学生姓名，不能为空
SAGE INT,-- 学生年龄
SSEX CHAR ( 1 ) CHECK (
SSEX IN ( 'F', 'M' )) -- 学生性别，只能填写F或M
);
-- 创建教师表
CREATE TABLE TEACHER (
TD INT PRIMARY KEY, -- 教师编号
TNAME VARCHAR ( 50 ) NOT NULL -- 教师姓名，不能为空
);
-- 创建课程表
CREATE TABLE COURSE (
CD INT PRIMARY KEY,-- 课程编号
CNAME VARCHAR ( 50 ) NOT NULL,-- 课程名称，不能为空
TD INT,-- 教师编号
FOREIGN KEY ( TD ) REFERENCES TEACHER ( TD ) -- 外键，指向教师表的教师编号字段
);
-- 创建成绩表
CREATE TABLE SC (
SD INT,-- 学号
CD INT,-- 课程编号
SCORE INT,-- 成绩
PRIMARY KEY ( SD, CD ),-- 主键，学号和课程编号的组合
FOREIGN KEY ( SD ) REFERENCES STUDENT ( SD ),-- 外键，指向学生表的学号字段
FOREIGN KEY ( CD ) REFERENCES COURSE ( CD ) -- 外键，指向课程表的课程编号字段
);插入数据的语句。sc表数据较多，这里限于篇幅就不展示了。
-- 向student表插入数据
INSERT INTO `student` VALUES (2301, '李红', 22, 'F');
INSERT INTO `student` VALUES (2302, '王敏', 23, 'F');
INSERT INTO `student` VALUES (2303, '张红', 25, 'M');
INSERT INTO `student` VALUES (2304, '周丹', 18, 'F');
INSERT INTO `student` VALUES (2305, '刘伟', 24, 'M');
INSERT INTO `student` VALUES (2306, '陈丽', 21, 'F');
INSERT INTO `student` VALUES (2307, '张海燕', 19, 'F');
INSERT INTO `student` VALUES (2308, '张红', 23, 'M');
INSERT INTO `student` VALUES (2309, '李莉', 25, 'F');
INSERT INTO `student` VALUES (2310, '周磊', 18, 'M');
INSERT INTO `student` VALUES (2311, '张敏', 24, 'F');
INSERT INTO `student` VALUES (2312, '王伟', 20, 'M');
INSERT INTO `student` VALUES (2313, '陈华', 19, 'M');
INSERT INTO `student` VALUES (2314, '刘海燕', 23, 'F');
INSERT INTO `student` VALUES (2315, '李伟', 20, 'M');
INSERT INTO `student` VALUES (2316, '张丽', 21, 'F');
INSERT INTO `student` VALUES (2317, '王鹏', 25, 'M');
INSERT INTO `student` VALUES (2318, '陈丹丹', 22, 'F');
INSERT INTO `student` VALUES (2319, '刘华', 18, 'M');
INSERT INTO `student` VALUES (2320, '李玲', 19, 'F');
INSERT INTO `student` VALUES (2321, '张涛', 25, 'M');
INSERT INTO `student` VALUES (2322, '王丹', 22, 'F');
INSERT INTO `student` VALUES (2323, '张红', 20, 'M');
INSERT INTO `student` VALUES (2324, '刘磊', 19, 'M');
INSERT INTO `student` VALUES (2325, '李艳', 25, 'F');
INSERT INTO `student` VALUES (2326, '张红', 18, 'F');
INSERT INTO `student` VALUES (2327, '王梅', 20, 'F');
INSERT INTO `student` VALUES (2328, '陈勇', 23, 'M');
INSERT INTO `student` VALUES (2329, '刘鹏', 25, 'M');
INSERT INTO `student` VALUES (2330, '李娜', 22, 'F');
INSERT INTO `student` VALUES (2331, '周娜', 18, 'F');
INSERT INTO `student` VALUES (2332, '张涵', 20, 'F');
INSERT INTO `student` VALUES (2333, '王飞', 19, 'M');
INSERT INTO `student` VALUES (2334, '陈建华', 23, 'M');
INSERT INTO `student` VALUES (2335, '刘超', 21, 'M');
INSERT INTO `student` VALUES (2336, '李丹', 19, 'F');
INSERT INTO `student` VALUES (2337, '张刚', 25, 'M');
INSERT INTO `student` VALUES (2338, '王琳', 22, 'F');
INSERT INTO `student` VALUES (2339, '陈娜', 21, 'F');
INSERT INTO `student` VALUES (2340, '刘涛', 18, 'M');
INSERT INTO `student` VALUES (2341, '李琴', 19, 'F');
INSERT INTO `student` VALUES (2342, '周超', 25, 'M');
INSERT INTO `student` VALUES (2343, '张敏', 23, 'F');
INSERT INTO `student` VALUES (2344, '王芳', 20, 'F');
INSERT INTO `student` VALUES (2345, '陈辉', 21, 'M');
INSERT INTO `student` VALUES (2346, '刘明', 22, 'M');
INSERT INTO `student` VALUES (2347, '李雪', 18, 'F');
INSERT INTO `student` VALUES (2348, '张宇', 24, 'M');
INSERT INTO `student` VALUES (2349, '王莉莉', 22, 'F');
INSERT INTO `student` VALUES (2350, '陈志刚', 25, 'M');
INSERT INTO `student` VALUES (2351, '刘琳', 23, 'F');
INSERT INTO `student` VALUES (2352, '李建', 19, 'M');
INSERT INTO `student` VALUES (2353, '周丽丽', 18, 'F');
INSERT INTO `student` VALUES (2354, '张峰', 20, 'M');
INSERT INTO `student` VALUES (2355, '刘超', 24, 'M');
INSERT INTO `student` VALUES (2356, '陈燕', 22, 'F');
INSERT INTO `student` VALUES (2357, '刘建军', 23, 'M');
INSERT INTO `student` VALUES (2358, '李林', 25, 'M');
INSERT INTO `student` VALUES (2359, '周明', 21, 'M');
INSERT INTO `student` VALUES (2360, '张晨', 19, 'M');
INSERT INTO `student` VALUES (2361, '王云', 20, 'F');
INSERT INTO `student` VALUES (2362, '陈红', 24, 'F');
INSERT INTO `student` VALUES (2363, '刘军', 21, 'M');
INSERT INTO `student` VALUES (2364, '刘超', 19, 'M');
INSERT INTO `student` VALUES (2365, '周峰', 23, 'M');
INSERT INTO `student` VALUES (2366, '张琳', 20, 'F');
INSERT INTO `student` VALUES (2367, '王建华', 18, 'F');
INSERT INTO `student` VALUES (2368, '陈梅', 22, 'F');
INSERT INTO `student` VALUES (2369, '刘洋', 21, 'M');
INSERT INTO `student` VALUES (2370, '李涛', 25, 'M');
INSERT INTO `student` VALUES (2371, '周敏', 22, 'F');
INSERT INTO `student` VALUES (2372, '张静', 20, 'F');
INSERT INTO `student` VALUES (2373, '王峰', 19, 'M');
INSERT INTO `student` VALUES (2374, '陈军', 23, 'M');
INSERT INTO `student` VALUES (2375, '刘红梅', 24, 'F');
INSERT INTO `student` VALUES (2376, '李丽华', 18, 'F');
INSERT INTO `student` VALUES (2377, '周华', 21, 'M');
INSERT INTO `student` VALUES (2378, '刘超', 19, 'M');
INSERT INTO `student` VALUES (2379, '王晓', 25, 'F');
INSERT INTO `student` VALUES (2380, '陈明华', 22, 'M');
INSERT INTO `student` VALUES (2381, '刘燕', 23, 'F');
INSERT INTO `student` VALUES (2382, '李明', 20, 'M');
INSERT INTO `student` VALUES (2383, '周静', 18, 'F');
INSERT INTO `student` VALUES (2384, '张飞', 19, 'M');
INSERT INTO `student` VALUES (2385, '王霞', 25, 'F');
INSERT INTO `student` VALUES (2386, '陈辉华', 22, 'M');
INSERT INTO `student` VALUES (2387, '刘勇', 23, 'M');
INSERT INTO `student` VALUES (2388, '李小华', 19, 'F');
INSERT INTO `student` VALUES (2389, '周建军', 24, 'M');
INSERT INTO `student` VALUES (2390, '张秀兰', 21, 'M');
INSERT INTO `student` VALUES (2391, '王桂花', 20, 'F');
INSERT INTO `student` VALUES (2392, '陈飞', 25, 'M');
INSERT INTO `student` VALUES (2393, '刘秀英', 22, 'F');
INSERT INTO `student` VALUES (2394, '李俊', 23, 'M');
INSERT INTO `student` VALUES (2395, '周鹏', 18, 'M');
INSERT INTO `student` VALUES (2396, '张玉', 19, 'F');
INSERT INTO `student` VALUES (2397, '王建', 25, 'M');
INSERT INTO `student` VALUES (2398, '陈娟', 21, 'F');
INSERT INTO `student` VALUES (2399, '刘杰', 20, 'M');
INSERT INTO `student` VALUES (2400, '李勇', 24, 'M');
INSERT INTO `student` VALUES (2401, '刘超', 19, 'F');
INSERT INTO `student` VALUES (2402, '张云', 22, 'M');
INSERT INTO `student` VALUES (2403, '王玉兰', 23, 'F');
INSERT INTO `student` VALUES (2404, '陈丹', 25, 'F');
INSERT INTO `student` VALUES (2405, '刘红霞', 20, 'F');
INSERT INTO `student` VALUES (2406, '李建军', 18, 'M');
INSERT INTO `student` VALUES (2407, '周敏华', 19, 'F');
INSERT INTO `student` VALUES (2408, '张俊', 22, 'M');
INSERT INTO `student` VALUES (2409, '王丽华', 23, 'F');
INSERT INTO `student` VALUES (2410, '陈明', 20, 'M');
INSERT INTO `student` VALUES (2411, '刘娜', 18, 'F');
INSERT INTO `student` VALUES (2412, '李刚', 21, 'M');
-- 向teacher表插入数据
INSERT INTO `teacher` VALUES (5101, '蔡勇');
INSERT INTO `teacher` VALUES (5102, '陈磊');
INSERT INTO `teacher` VALUES (5103, '李梅');
INSERT INTO `teacher` VALUES (5104, '王超');
INSERT INTO `teacher` VALUES (5105, '张建华');
INSERT INTO `teacher` VALUES (5106, '刘凯');
INSERT INTO `teacher` VALUES (5107, '赵红');
INSERT INTO `teacher` VALUES (5108, '黄勇');
INSERT INTO `teacher` VALUES (5109, '周秀兰');
INSERT INTO `teacher` VALUES (5110, '徐建国');
INSERT INTO `teacher` VALUES (5111, '朱芳');
INSERT INTO `teacher` VALUES (5112, '陈伟华');
INSERT INTO `teacher` VALUES (5113, '刘敏');
INSERT INTO `teacher` VALUES (5114, '李伟');
INSERT INTO `teacher` VALUES (5115, '王红');
-- 向course表插入数据
INSERT INTO `course` VALUES (1201, '计算机组成原理', 5113);
INSERT INTO `course` VALUES (1202, '数据结构与算法', 5109);
INSERT INTO `course` VALUES (1203, '操作系统原理', 5106);
INSERT INTO `course` VALUES (1204, '计算机网络', 5101);
INSERT INTO `course` VALUES (1205, '数据库原理', 5102);
INSERT INTO `course` VALUES (1206, '软件工程', 5107);
INSERT INTO `course` VALUES (1207, '编译原理', 5106);
INSERT INTO `course` VALUES (1208, '计算机图形学', 5110);
INSERT INTO `course` VALUES (1209, '计算机系统结构', 5113);
INSERT INTO `course` VALUES (1210, '计算机视觉', 5110);
INSERT INTO `course` VALUES (1211, '人工智能原理', 5112);
INSERT INTO `course` VALUES (1212, '大数据处理技术', 5102);
INSERT INTO `course` VALUES (1213, '信息安全原理', 5101);
INSERT INTO `course` VALUES (1214, '网络安全与防护', 5101);
INSERT INTO `course` VALUES (1215, '云计算技术', 5102);
INSERT INTO `course` VALUES (1216, '嵌入式系统原理', 5108);
INSERT INTO `course` VALUES (1217, '计算机体系结构', 5113);
INSERT INTO `course` VALUES (1218, '计算机音视频处理技术', 5110);
INSERT INTO `course` VALUES (1219, '计算机游戏开发技术', 5107);
INSERT INTO `course` VALUES (1220, '计算机人机交互设计', 5111);
-- 向sc表插入数据 部分数据
INSERT INTO `sc` VALUES (2301, 1201, 66);
INSERT INTO `sc` VALUES (2301, 1202, 91);
INSERT INTO `sc` VALUES (2301, 1203, 62);
INSERT INTO `sc` VALUES (2301, 1204, 97);
INSERT INTO `sc` VALUES (2301, 1205, 65);
INSERT INTO `sc` VALUES (2301, 1206, 67);
INSERT INTO `sc` VALUES (2301, 1207, 85);
INSERT INTO `sc` VALUES (2301, 1208, 90);
INSERT INTO `sc` VALUES (2301, 1209, 89);
INSERT INTO `sc` VALUES (2301, 1210, 75);
INSERT INTO `sc` VALUES (2301, 1211, 70);
INSERT INTO `sc` VALUES (2301, 1212, 66);
INSERT INTO `sc` VALUES (2301, 1213, 74);
INSERT INTO `sc` VALUES (2301, 1214, 100);
INSERT INTO `sc` VALUES (2301, 1215, 74);
INSERT INTO `sc` VALUES (2301, 1216, 68);
INSERT INTO `sc` VALUES (2301, 1217, 69);
INSERT INTO `sc` VALUES (2301, 1218, 66);
INSERT INTO `sc` VALUES (2301, 1219, 86);
INSERT INTO `sc` VALUES (2301, 1220, 65);
INSERT INTO `sc` VALUES (2302, 1201, 90);
INSERT INTO `sc` VALUES (2302, 1202, 84);
INSERT INTO `sc` VALUES (2302, 1203, 77);
INSERT INTO `sc` VALUES (2302, 1204, 93);
INSERT INTO `sc` VALUES (2302, 1205, 94);
INSERT INTO `sc` VALUES (2302, 1206, 37);
INSERT INTO `sc` VALUES (2302, 1207, 63);
INSERT INTO `sc` VALUES (2302, 1208, 56);
INSERT INTO `sc` VALUES (2302, 1209, 68);
INSERT INTO `sc` VALUES (2302, 1210, 50);
INSERT INTO `sc` VALUES (2302, 1212, 64);
...