管理租房信息

CREATE DATABASE IF NOT EXISTS county;
#客户信息表
CREATE TABLE IF NOT EXISTS sys_user(
uid INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '客户编号',
uName VARCHAR(155) NOT NULL COMMENT '客户姓名',
uPassWord VARCHAR(6)  COMMENT '客户密码'
)
SELECT * FROM sys_user;
INSERT INTO sys_user(uName,uPassWord)
VALUES ('张三','112233'),('李四','121213'),('王五','122133'),('赵六','133122'),
('孙七','111222'),('武八','112112'),('夏一','113113'),('刘二','122233')


#区县信息表
CREATE TABLE IF NOT EXISTS hos_district(
did INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '区县编号',
dName VARCHAR(255) NOT NULL COMMENT '区县名称'
)
SELECT * FROM hos_district;
INSERT INTO hos_district(dName)
VALUES ('朝阳区'),('海淀区'),('东城区'),('西城区'),('通州区'),('顺义区'),('昌平区'),('丰台区'),('平谷区')


#街道信息表
CREATE TABLE IF NOT EXISTS hos_street(
sid INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '街道编号',
studentName VARCHAR(255) NOT NULL COMMENT '街道名称',
sDid INT(10) NOT NULL COMMENT '区县编号'
)
SELECT * FROM hos_street;


#alter table hos_street
#add constraint FK_sDid foreign key(sDid) references hos_district(did);
#先禁用外键约束 ALTER TABLE 表名 NOCHECK CONSTRAINT 外键约束名
#alter table hos_street no check constraint FK_sDid;


INSERT INTO hos_street(sid,studentName,sDid)
VALUES (1,'中关村',10010),(2,'东湖',10001),(3,'万泉庄',10100),(4,'西道口',11000),
(5,'苏州街',10020),(6,'东四',10110),(7,'西四',10000),(8,'公主坟',11110),(9,'鼓楼',10030)


#然后再启用约束 ALTER TABLE 表名 CHECK CONSTRAINT 外键约束名
#alter table hos_street CHECK constraint FK_sDid;


#房屋类型表
CREATE TABLE IF NOT EXISTS hos_type(
hTid INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '房屋类型编号',
htName VARCHAR(255) NOT NULL COMMENT '房屋类型名称'
)
SELECT * FROM hos_type;


INSERT INTO hos_type(htName)
VALUES ('一室一卫'),('一室一厅'),('两室一厅'),('两室一卫'),('两室一厅一卫'),
('两室二厅一卫'),('两室二厅二卫'),('一室一厅一卫'),('一室二厅二卫')


#出租房屋信息表
CREATE TABLE IF NOT EXISTS hos_house( 
hMid INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '出租房屋编号',
uid INT(4) NOT NULL COMMENT '客户编号',
sid INT(4) NOT NULL COMMENT '街道编号',
hTid INT(4) NOT NULL COMMENT '房屋类型编号',
price DECIMAL(9,2) NOT NULL DEFAULT 0 COMMENT '每月租金',