MySQL经典练习题-数据准备

 1 -- 1、数据表准备:
 2 -- --学生表---
 3 CREATE TABLE STUDENT
 4 (SNO VARCHAR(3) NOT NULL PRIMARY KEY, 
 5 SNAME VARCHAR(4) NOT NULL,
 6 SSEX VARCHAR(2) NOT NULL, 
 7 SBIRTHDAY DATETIME,
 8 CLASS VARCHAR(5));
 9 -- ---课程表---
10 CREATE TABLE COURSE (CNO VARCHAR(5) NOT NULL,CNAME VARCHAR(10) NOT NULL,TNO VARCHAR(10) NOT NULL);
11 -- ---成绩表---
12 CREATE TABLE SCORE (SNO VARCHAR(3) NOT NULL, CNO VARCHAR(5) NOT NULL, DEGREE NUMERIC(10, 1) NOT NULL);
13 -- ---教师表---
14 CREATE TABLE TEACHER (TNO VARCHAR(3) NOT NULL, 
15 TNAME VARCHAR(4) NOT NULL, 
16 TSEX VARCHAR(2) NOT NULL, 
17 TBIRTHDAY DATETIME NOT NULL, 
18 PROF VARCHAR(6), DEPART VARCHAR(10) NOT NULL);
19 -- 2、插入数据:
20 -- --学生表---
21 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,'曾华','','1977-09-01 00:00:00',95033);
22 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,'匡名','','1975-10-02 00:00:00',95031); 
23 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,'王力','','1976-01-23 00:00:00',95033); 
24 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,'李君','','1976-02-20 00:00:00',95033); 
25 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,'王方','','1975-02-10 00:00:00',95031); 
26 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,'陆钧','','1974-06-03 00:00:00',95031); 
27 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('','陆仪','','1984-04-03 00:00:00',95031); 
28 insert into Student values('01' , '赵雷' ,'' ,'1990-01-01' , '95033');
29 insert into Student values('02' , '钱电' ,'', '1990-12-21' , '95032');
30 insert into Student values('03' , '孙风' ,'', '1990-05-20' , '95032');
31 insert into Student values('04' , '李云' , '','1990-08-06' , '95033');
32 insert into Student values('05' , '周梅' , '','1991-12-01' , '95033');
33 insert into Student values('06' , '吴兰' , '','1992-03-01' , '95034');
34 insert into Student values('07' , '郑竹' , '','1989-07-01' , '95035');
35 insert into Student values('08' , '王菊' , '','1990-01-20' , '95036');
36 -- --课程表---
37 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105','计算机导论',825);
38 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245','操作系统',804);
39 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166','数据电路',856);
40 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888','高等数学',100);
41 insert into Course values('01' , '语文' , '02');
42 insert into Course values('02' , '数学' , '01');
43 insert into Course values('03' , '英语' , '03');
44 -- --成绩表---
45 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
46 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
47 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
48 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
49 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
50 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
51 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
52 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
53 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
54 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
55 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
56 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
57 insert into Score values('01' , '01' , 80);
58 insert into Score values('01' , '02' , 90);
59 insert into Score values('01' , '03' , 99);
60 insert into Score values('02' , '01' , 70);
61 insert into Score values('02' , '02' , 60);
62 insert into Score values('02' , '03' , 80);
63 insert into Score values('03' , '01' , 80);
64 insert into Score values('03' , '02' , 80);
65 insert into Score values('03' , '03' , 80);
66 insert into Score values('04' , '01' , 50);
67 insert into Score values('04' , '02' , 30);
68 insert into Score values('04' , '03' , 20);
69 insert into Score values('05' , '01' , 76);
70 insert into Score values('05' , '02' , 87);
71 insert into Score values('06' , '01' , 31);
72 insert into Score values('06' , '03' , 34);
73 insert into Score values('07' , '02' , 89);
74 insert into Score values('07' , '03' , 98);
75 -- --教师表---
76 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','','1958-12-02','副教授','计算机系');
77 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','','1969-03-12','讲师','电子工程系');
78 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','','1972-05-05','助教','计算机系');
79 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','','1977-08-14','助教','电子工程系');
80 insert into Teacher values('01', '张三','','1978-02-10','副教授','数学');
81 insert into Teacher values('02', '李四','','1968-11-02','副教授','语文');
82 insert into Teacher values('03' , '王五','','1970-12-18','副教授','英语');

 

posted @ 2021-06-26 10:59  Eleanor123  阅读(67)  评论(0编辑  收藏  举报