MySQL练习1_建表

create database school; 2 3 create table Student #学生表 4 ( 5 SNO char(3) not null primary key, #学号（主码） 6 Sname char(8) not null , #学生姓名 7 Ssex char(2) not null, #学生性别 8 Sbirthday datetime, #学生出生年月 9 Class char(5) #学生所在班级 10 ); 11 create table Course #课程表 12 ( 13 Cno char(5) not null primary key, #课程号（主码） 14 Cname varchar(10) not null, #课程名称 15 Tno char(3) not null #教工编号（外码） 16 ) ; 17 create table Scorse #成绩表 18 ( 19 20 Sno char(3) not null, #学号（外码） 21 Cno char(5) not null, #课程号（外码） 22 Degree decimal(4,1) #成绩 23 ); 24 create table Teacher #教师表 25 ( 26 Tno char(3) not null primary key, #教工编号（主码） 27 Tname char(4) not null, #教工姓名 28 Tsex char(2) not null, #教工性别 29 Tbirthday datetime, #教工出生年月 30 Prof char(6), #职称 31 Depart varchar(10) not null #教工所在部门 32 ); 33 insert into Student values(‘108‘, ‘曾华‘, ‘男‘, ‘1977-09-01‘, ‘95033‘); 34 insert into Student values(‘105‘, ‘匡明‘, ‘男‘, ‘1975-10-02‘, ‘95031‘); 35 insert into Student VALUES(‘107‘, ‘王丽‘, ‘女‘, ‘1976-01-23‘, ‘95033‘); 36 insert into Student VALUES(‘101‘, ‘李军‘, ‘男‘, ‘1976-02-20‘, ‘95033‘); 37 insert into Student VALUES(‘109‘, ‘王芳‘, ‘女‘, ‘1975-02-10‘, ‘95031‘); 38 insert into Student VALUES(‘103‘, ‘陆君‘, ‘男‘, ‘1974-06-03‘, ‘95031‘); 39 insert into Course VALUES(‘3-105‘, ‘计算机导论‘, ‘825‘); 40 insert into Course VALUES(‘3-245‘, ‘操作系统‘, ‘804‘); 41 insert into Course VALUES(‘6-166‘, ‘数字电路‘, ‘856‘); 42 insert into Course VALUES(‘9-888‘, ‘高等数学‘, ‘831‘); 43 44 insert into Score values(‘103‘, ‘3-245‘,‘86‘); 45 insert into Score values(‘105‘, ‘3-245‘,‘75‘); 46 insert into Score values(‘109‘, ‘3-245‘ ‘68‘); 47 insert into Score values(‘103‘, ‘3-105‘,‘92‘); 48 insert into Score values(‘105‘, ‘3-105‘,‘88‘); 49 insert into Score values(‘109‘, ‘3-105‘,‘76‘); 50 insert into Score values(‘101‘, ‘3-105‘,‘64‘); 51 insert into Score values(‘107‘, ‘3-105‘ ‘91‘); 52 insert into Score values(‘108‘, ‘3-105‘,‘78‘); 53 insert into Score values(‘101‘, ‘6-166‘,‘85‘); 54 insert into Score values(‘107‘, ‘6-166‘,‘79‘); 55 insert into Score values(‘108‘, ‘6-166‘,‘81‘); 56 57 insert into Teacher values(‘804‘,‘李诚‘,‘男‘,‘1958-12-02‘,‘副教授‘,‘计算机系‘); 58 insert into Teacher values(‘856‘,‘张旭‘,‘男‘,‘1969-03-12‘,‘讲师‘,‘电子工程系‘); 59 insert into Teacher values(‘825‘,‘王萍‘,‘女‘,‘1972-05-05‘,‘助教‘,‘计算机系‘); 60 insert into Teacher values(‘831‘,‘刘冰‘,‘女‘,‘1977-08-14‘,‘助教‘,‘电子工程系‘); 61 62 insert into Score values(‘109‘, ‘3-245‘, ‘68‘); 63 insert into Score values(‘103‘, ‘3-105‘, ‘92‘); 64 insert into Score values(‘105‘, ‘3-105‘, ‘88‘); 65 insert into Score values(‘109‘, ‘3-105‘, ‘76‘); 66 insert into Score values(‘101‘, ‘3-105‘, ‘64‘); 67 insert into Score values(‘107‘, ‘3-105‘, ‘91‘); 68 insert into Score values(‘108‘, ‘3-105‘, ‘78‘); 69 insert into Score values(‘101‘, ‘6-166‘, ‘85‘); 70 insert into Score values(‘107‘, ‘6-166‘, ‘79‘); 71 insert into Score values(‘108‘, ‘6-166‘, ‘81‘); 72 73 insert into Teacher VALUES(‘804‘, ‘李诚‘, ‘男‘, ‘1958-12-02‘, ‘副教授‘,‘计算机系‘); 74 insert into Teacher VALUES(‘856‘, ‘张旭‘, ‘男‘, ‘1969-03-12‘, ‘讲师‘, ‘电子工程系‘); 75 insert into Teacher VALUES(‘825‘, ‘王萍‘, ‘女‘, ‘1972-05-05‘, ‘助教‘, ‘计算机系‘); 76 insert into Teacher VALUES(‘831‘, ‘刘冰‘, ‘女‘, ‘1977-08-14‘, ‘助教‘, ‘电子工程系‘)

                                   

                                 

 

1 create table grade(low  int(3),upp  int(3),rank  char(1));
2 insert into grade values(90,100,‘A‘);
3 insert into grade values(80,89,‘B‘);
4 insert into grade values(70,79,‘C‘);
5 insert into grade values(60,69,‘D‘);
6 insert into grade values(0,59,‘E‘)

MySQL练习1_建表

标签：