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); ...