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