DROP TABLE IF EXISTS `classroom`;
/*创建表*/
CREATE TABLE `classroom` (
`building` varchar(15) NOT NULL,
`room_number` varchar(7) NOT NULL,
`capacity` int DEFAULT NULL,
PRIMARY KEY (`building`,`room_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `classroom` VALUES ('Packard','101',500),('Painter','514',10),('Taylor','3128',70),('Watson','100',30),('Watson','120',50);
讲真,上面操作太正式了,我们写个简约版就行
drop table classroom;
/*用drop table if exists classroom更好
*用drop table classroom代表你知道肯定有这么一个表,不然删一个不存在的表会报错*/
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`dept_name` varchar(20) NOT NULL,
`building` varchar(15) DEFAULT NULL,
`budget` decimal(12,2) DEFAULT NULL,
PRIMARY KEY (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `department` VALUES ('Biology','Watson',90000.00),('Comp. Sci.','Taylor',100000.00),('Elec. Eng.','Taylor',85000.00),('Finance','Painter',120000.00),('History','Painter',50000.00),('Music','Packard',80000.00),('Physics','Watson',70000.00);
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`course_id` varchar(8) NOT NULL,
`title` varchar(50) DEFAULT NULL,
`dept_name` varchar(20) NOT NULL,
`credits` int DEFAULT NULL,
PRIMARY KEY (`course_id`),
FOREIGN KEY (`dept_name`) references `department`(`dept_name`) on delete RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `course` VALUES ('BIO-101','Intro. to Biology','Biology',4),('BIO-301','Genetics','Biology',4),('BIO-399','Computational Biology','Biology',3),('CS-101','Intro. to Computer Science','Comp. Sci.',4),('CS-190','Game Design','Comp. Sci.',4),('CS-315','Robotics','Comp. Sci.',3),('CS-319','Image Processing','Comp. Sci.',3),('CS-347','Database System Concepts','Comp. Sci.',3),('EE-181','Intro. to Digital Systems','Elec. Eng.',3),('FIN-201','Investment Banking','Finance',3),('HIS-351','World History','History',3),('MU-199','Music Video Production','Music',3),('PHY-101','Physical Principles','Physics',4);