首页

# 0. 创建数据库

MySQL 终端输入 create database EstateDB charset utf8; 回车

# 1. 创建数据库表

use EstateDB 切换至所建数据库

建立三个表

create table Owner(
	PersonID char(18) not null primary key comment '身份证号_主键',
	Name varchar(20) not null comment '姓名',
	Gender varchar(2) not null comment '性别',
	Occupation varchar(20) not null comment '职业',
	Addr varchar(50) not null comment '身份地址',
	Tel varchar(11) not null comment '电话'
)comment '业主表';
create table Estate(
	EstateID char(15) not null primary key comment '房产编号_主键',
	EstateName varchar(50) not null comment '房产名称',
	EstateBuildName varchar(50) not null comment '房产楼盘',
	EstateAddr varchar(60) not null comment '房产地址',
	EstateCity varchar(60) not null comment '房产城市',
	EstateType char(4) not null comment '房产类型_取值范围:“住宅”“商铺”“车位”“别墅”',
	PropertyArea numeric(5,2) not null comment '产权面积',
	UsableArea numeric(5,2) not null comment '使用面积',
	CompletedDate date not null comment '竣工日期',
	YearLength int not null comment '产权年限_默认值为70',
	Remark varchar(100) null comment '备注'
)comment '房产表';
create table Registration(
	RegisterID int not null primary key comment '登记编号_主键',
	PersonID char(18) not null comment '身份证号_外键',
	EstateID char(15) not null comment '房产编号_外键',
	Price numeric(19,4) not null comment '购买金额',  
    #Mysql 没有 money 类型
	PurchasedDate date not null comment '购买日期',
	DeliverDate date not null comment '交付日期',
	FOREIGN KEY (PersonID) REFERENCES Owner (PersonID),
	FOREIGN KEY (EstateID) REFERENCES Estate (EstateID)
)comment '产权登记表';
# 踩坑：注意中文英文符号，注意空格
image-20220831213508984
image-20220831213554974
image-20220831213613701
成功创建后查询表
QQ截图20220917225412
查询表结构
# 2. 填入数据
# 预处理数据
将 0 开头的数字用 Excel 的分列功能转为文本形式
​ 
分列里面，点两次下一步，然后左上角选文本，点右下角完成
QQ截图20220918110324
参考 https://zhuanlan.zhihu.com/p/100530199
将数据储存为.csv 格式且编码为 utf-8
（这步骤看似简单，但是确实挺费搜索引擎和时间的
# 插入数据
输入以下代码
select * from Owner;
load data local infile 'E:/2022c/cancanword/#9 数据库/2.使用数据库/业主表b.csv' into table Owner
fields terminated by ',';
select * from Estate;
load data local infile 'E:/2022c/cancanword/#9 数据库/2.使用数据库/房产表b.csv' into table Estate
fields terminated by ',';
select * from Registration;
load data local infile 'E:/2022c/cancanword/#9 数据库/2.使用数据库/房产登记表c.csv' into table Registration
fields terminated by ',';
导入后在 Navicat 里面查看各个表
QQ截图20220918112947
QQ截图20220918112932
可见导入已经成功。
# 踩坑
检查发现 Registration 表的 Register 字段名应改为 RegisterID，使用指令
alter table Registration change Register RegisterID int not null primary key comment '登记编号';
报错：有多个主键，遂把表删了重建，重新导入数据。
# 3. 查询数据
# 查询类别为 “商铺” 的房产信息
SELECT
	* 
FROM
	Estate 
WHERE
	EstateType = '商铺';
20220918153510
# 查询竣工日期为 2018 年 12 月 1 日后， 产权面积 90 平方米以上的 “住宅” 的房产信息。
SELECT
	* 
FROM
	Estate 
WHERE
	EstateType = '住宅' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2018-12-01' 
	AND PropertyArea > 90;
QQ截图20220918154313
# 查询个人在各地购买住宅两套以上的业主基本信息
SELECT
	PersonID,
	count(*) AS count 
FROM
	Registration 
GROUP BY
	PersonID 
HAVING
	count > 1;
QQ截图20220918154921
找到购买住宅两套以上的业主的身份证号，接下来查询他们的基本信息
SELECT
	* 
FROM
OWNER 
WHERE
	PersonID = '320283198612130046';
SELECT
	* 
FROM
OWNER 
WHERE
	PersonID = '440111198601063054';
QQ截图20220918155229
# 查询个人在特定城市购买住宅两套以上的业主基本信息
select * 
from Registration
where PersonID in (select PersonID from Registration group by PersonID having count(PersonId)>1);
查出有共主的房产，根据 EstateID 查询房所在城市
SELECT
	* 
FROM
	Estate 
WHERE
	EstateType = '住宅' 
	AND ( EstateID = '00000011' OR EstateID = '00000015' );
* 
FROM
Estate 
WHERE
EstateType = '住宅' 
AND ( EstateID = '00000011' OR EstateID = '00000015' );
QQ截图20220918161927
找不到，说明身份证号 320283198612130046 的人不满足 “在特定城市购买住宅两套 “的条件。
SELECT
	* 
FROM
	Estate 
WHERE
	EstateType = '住宅' 
	AND ( EstateID = '00000007' OR EstateID = '00000009' OR EstateID = '00000016' OR EstateID = '00000017' );
找到了 2 条记录，但是房产城市不同，说明身份证号 440111198601063054 的人也不满足 “在特定城市购买住宅两套 “的条件。
如果某人满足条件，就查询其身份信息。这里用身份证号 440111198601063054 的人演示。
SELECT
	* 
FROM
OWNER 
WHERE
	PersonID = '440111198601063054';
QQ截图20220918155229
# 统计 2018 年度某城市的各类房产销售面积
SELECT
	sum( PropertyArea ) 
FROM
	Estate 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )< '2019-01-01' 
	AND EstateType = '住宅';
QQ截图20220922155343
SELECT
	sum( PropertyArea ) 
FROM
	Estate 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )< '2019-01-01' 
	AND EstateType = '车位';
QQ截图20220922155350
SELECT
	sum( PropertyArea ) 
FROM
	Estate 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )< '2019-01-01' 
	AND EstateType = '别墅';
QQ截图20220922155359
SELECT
	sum( PropertyArea ) 
FROM
	Estate 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )< '2019-01-01' 
	AND EstateType = '商铺';
QQ截图20220922155406
话说数据有点老，基本没有值 233
# 统计 2018 年度某城市的各类房产销售金额
SELECT
	sum( Price ) 
FROM
	Estate,
	registration 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) > '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) < '2019-01-01' 
	AND EstateType = '住宅';
1QQ截图20220922203223
SELECT
	sum( PropertyArea ) 
FROM
	Estate 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )> '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' )< '2019-01-01' 
	AND EstateType = '车位';
SELECT
	sum( Price ) 
FROM
	Estate,
	registration 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) > '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) < '2019-01-01' 
	AND EstateType = '商铺';
Q0Q截图20220922155406
SELECT
	sum( Price ) 
FROM
	Estate,
	registration 
WHERE
	EstateCity = '成都' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) > '2017-12-31' 
	AND DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) < '2019-01-01' 
	AND EstateType = '别墅';
QQ2截图20220922203239
# 通过视图查询
创建 SQL 视图， 通过视图查询指定身份证号 440111198601063054 下， 该业主的购置房产信息（房产编号、 房产名称、 房产类型、 产权面积、 购买金额、 购买日期、 房产楼盘、 房产城市）, 并按日期降序排列。
create view research SELECT
	estate.EstateID, 
	estate.EstateName, 
	estate.EstateType, 
	estate.PropertyArea, 
	registration.Price, 
	registration.PurchasedDate, 
	estate.EstateBuildName, 
	estate.EstateCity
FROM
	estate
	INNER JOIN
	registration
	ON 
		estate.EstateID = registration.EstateID
	INNER JOIN
	`owner`
	ON 
		registration.PersonID = `owner`.PersonID
WHERE
	registration.PersonID = 440111198601063054
ORDER BY
	PurchasedDate DESC
然后使用
DESCRIBE research;
查询表信息
QQ截图20220922221856
查询视图
select * from research;
QQ截图20220922221843
# 视图统计
创建 SQL 视图， 分组统计 2018 年度各城市的住宅销售套数与总销售金额。
create view statistics as SELECT
	EstateCity, 
	count(*) AS count, 
	sum(price)
FROM
	estate
	INNER JOIN
	registration
	ON 
		estate.EstateID = registration.EstateID
WHERE
	EstateCity = '成都' AND
	DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) > '2017-12-31' AND
	DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) < '2019-01-01' AND
	EstateType = '住宅' ;
但是我的数据里并没有符合条件的。。。
换成车位试试吧
create view statistics as SELECT
	EstateCity, 
	count(*) AS count, 
	sum(price)
FROM
	estate
	INNER JOIN
	registration
	ON 
		estate.EstateID = registration.EstateID
WHERE
	EstateCity = '成都' AND
	DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) > '2017-12-31' AND
	DATE_FORMAT( CompletedDate, '%Y-%m-%d' ) < '2019-01-01' AND
	EstateType = '车位' ;
select * from statistics;
QQ截图20220922234113
其他城市和房产类型的查询也同理，只需修改参数即可。