MariaDB数据库SQL语句实战（大学classroom表、department表、course表

本文介绍了如何使用SQL语句创建并填充两个数据库表：classroom和department。classroom表包含建筑物名称、房间号及容量等字段；department表则记录部门名称、所在建筑物及预算信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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);