5数据库操作代码部分
5.1 数据库创建代码
5.2 创建表代码
CREATE DATABASE Dorm;
USE Dorm;
CREATE TABLE `department` (
`院系编号` INT(10) NOT NULL,
`院系名称` VARCHAR(20) NOT NULL,
`院系主管` VARCHAR(20) NOT NULL,
`地址` VARCHAR(100) NOT NULL,
`院系人数` INT(10) NOT NULL,
PRIMARY KEY (`院系编号`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `dorm` (
`区号` VARCHAR(10) NOT NULL ,
`栋号` VARCHAR(10) NOT NULL,
`房间号` INT(5) NOT NULL,
`床位数` INT(5) NOT NULL,
`租金(元/月/床)` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`区号`,`栋号`,`房间号`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`院系编号` INT(10) NOT NULL,
`学号` INT(20) NOT NULL,
`姓名` VARCHAR(20) NOT NULL,
`性别` VARCHAR(2) CHECK (`性别` IN ('男','女')),
`学生类型` VARCHAR(10) CHECK (`学生类型` IN ('大专生','本科生','研究生','博士生')),
`入学日期` DATE NOT NULL,
`籍贯` VARCHAR(100) NOT NULL,
`身份证号` VARCHAR(18) NOT NULL,
`详细地址` VARCHAR(100) NOT NULL,
`出生日期` DATE NOT NULL,
`所学专业` VARCHAR(50) NOT NULL,
`民族` VARCHAR(20) DEFAULT '汉' NOT NULL,
`电话(家)` VARCHAR(20) NOT NULL,
`缴费总计` DECIMAL(10,2) NOT NULL,
`所需房费` DECIMAL(10,2) NOT NULL,
`备注` TEXT,
PRIMARY KEY (`学号`),
FOREIGN KEY(`院系编号`) REFERENCES department(`院系编号`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `accommodation` (
`学号` INT(20) NOT NULL,
`区号` VARCHAR(10) NOT NULL,
`栋号` VARCHAR(10) NOT NULL,
`房间号` INT(5) NOT NULL,
`床号` INT(5) NOT NULL,
`入住日期` DATE NOT NULL,
`备注` TEXT,
FOREIGN KEY(`学号`) REFERENCES student(`学号`),
FOREIGN KEY(`区号`,`栋号`,`房间号`) REFERENCES dorm(`区号`,`栋号`,`房间号`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
数据表截图:
Accommodation
Department
Dorm
Student