mySQL建表及练习题（上）

 

 

 

create table student(
sno varchar(20)not null primary key,
sname varchar(20)not null,
ssex varchar(20)not null,
sbirthday datetime null,
class varchar(20)null
);
insert into student values('108','曾华','男','1977-09-01',95033);
insert into student values('105','匡明','男','1975-10-02',95031);
insert into student values('107','王丽','女','1976-01-23',95033);
insert into student values('101','李军','男','1976-02-20',95033);
insert into student values('109','王芳','女','1975-02-10',95031);
insert into student values('103','陆军','男','1974-09-03',95031);
create table course(
cno varchar(20)null primary key,
cname varchar(20)null,
tno varchar(20)null
);
insert into course values('3-105','计算机导论',825);
insert into course values('3-245','操作系统',804);
insert into course values('6-166','数字电路',856);
insert into course values('9-888','高等数学',831);

create table score(
sno varchar(20)null,
cno varchar(20)null,
degree decimal(4,1)null,
primary key(sno,cno)
);
insert into score values('103','3-245',86);
insert into score values('105','3-245',75);
insert into score values('109','3-245',68);
insert into score values('103','3-105',92);
insert into score values('105','3-105',88);
insert into score values('109','3-105',76);
insert into score values('101','3-105',64);
insert into score values('107','3-105',91);
insert into score values('108','3-105',78);
insert into score values('101','6-166',85);
insert into score values('107','6-166',79);
insert into score values('108','6-166',81);
create table teacher(
tno varchar(20) not null primary key,
tname varchar(20)not null,
tsex varchar(20)not null,
tbirthday datetime null,
prof varchar(20)null,
depart varchar(20)not null
);
insert into teacher values('804','李成','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');