数据库实验2

CREATE TABLE  `student` (
Sno INT NOT NULL ,
Sname VARCHAR( 20 ) NOT NULL ,
Ssex VARCHAR( 3 ) ,
Sage INT( 2 ) NOT NULL ,
Sdept VARCHAR( 20 ) ,
Sclass VARCHAR( 30 ) ,
Intime VARCHAR( 20 ) ,
PRIMARY KEY ( Sno )
) ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO  `data`.`student` (
`Sno` ,`Sname` ,`Ssex` ,`Sage` ,`Sdept` ,`Sclass` ,`Intime`)
VALUES (

), (

), (

), (

), (

), (

), (

);

CREATE TABLE  `course` (
Cno VARCHAR( 10 ) NOT NULL ,
Cname VARCHAR( 20 ) NOT NULL ,
Cpno VARCHAR( 10 ) ,
Ccredit FLOAT( 1 ) NOT NULL ,
PRIMARY KEY ( Cno )
) ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `data`.`course` (`Cno`, `Cname`, `Cpno`, `Ccredit`) VALUES 
(‘C801‘, ‘高等数学‘, NULL, ‘4‘), 
(‘C802‘, ‘C++语言‘, ‘C807‘, ‘3‘), 
(‘C803‘, ‘数据结构‘, ‘C802‘, ‘4‘), 
(‘C804‘, ‘数据库原理‘, ‘C803‘, ‘4‘),
(‘C805‘, ‘操作系统‘, ‘C807‘, ‘4‘), 
(‘C806‘, ‘编译原理‘, ‘C803‘, ‘4‘),
(‘C807‘, ‘离散数学‘, NULL, ‘4‘);

CREATE TABLE  `grade` (
Sno INT NOT NULL ,
Cno VARCHAR( 10 ) ,
Ggrade INT( 3 ) DEFAULT  ‘0‘,
Ccredit FLOAT( 1 ) NOT NULL ,
PRIMARY KEY ( Sno, Cno )
) ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO  `data`.`grade` (
`Sno` ,
`Cno` ,
`Ggrade` ,
`Ccredit`
)
VALUES (
‘20023001‘,  ‘C801‘,  ‘98‘,  ‘4‘
), (
‘20023002‘,  ‘C804‘,  ‘70‘,  ‘4‘
), (
‘20026001‘,  ‘C801‘,  ‘85‘,  ‘4‘
), (
‘20023001‘,  ‘C802‘,  ‘99‘,  ‘3‘
), (
‘20026002‘,  ‘C803‘,  ‘82‘,  ‘4‘
);

CREATE TABLE  `teach` (
Tname VARCHAR( 10 ) NOT NULL ,
Cno VARCHAR( 10 ) ,
Ttime INT( 3 ) DEFAULT  ‘0‘,
Tclass VARCHAR( 20 ) ,
PRIMARY KEY ( Cno, Tclass )
) ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO  `data`.`teach` (
`Tname` ,`Cno` ,`Ttime` ,`Tclass`)
VALUES (
‘苏亚步‘,  ‘C801‘,  ‘72‘,  ‘软件1班‘
), (
‘苏亚步‘,  ‘C801‘,  ‘72‘,  ‘软件2班‘
), (
‘王文山‘,  ‘C802‘,  ‘64‘,  ‘软件2班‘
), (
‘张珊‘,  ‘C803‘,  ‘72‘,  ‘软件2班‘
), (
‘王文山‘,  ‘C804‘,  ‘64‘,  ‘软件2班‘
);