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

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值