SQL数据库规范化与数据插入

发布于11月09日

№1源表:Sales(来自非 struct 化文件.csv)

id	auto	gasoline_consumption	country
1	Lada Vesta, grey	7.3	Russia
2	BMW F80, red	8.3	Germany
3	Hyundai Elantra, red	5	South Korea
...	...	...	...

CREATE TABLE [sales](
    [id] [nvarchar](50) NOT NULL,
    [auto] [nvarchar](50) NOT NULL,
    [gasoline_consumption] [nvarchar](50) NULL,
    [country] [nvarchar] (50)

) ON [PRIMARY];

INSERT INTO sales values(1,'Lada Vesta, grey', 7.3, 'Russia');
INSERT INTO sales values(2,'BMW F80, red', 8.3, 'Germany');
INSERT INTO sales values(3,'Hyundai Elantra, red', 5, 'South Korea');

2012来源表:品牌

id	name
1	Lada
2	Hyundai
3	BMW
...	...

CREATE TABLE Brand (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
name VARCHAR(50),
);

INSERT INTO Brand values(1, 'Lada');
INSERT INTO Brand values(2,'Hyundai');
INSERT INTO Brand values(3,'BMW');

№3源表:COLOR

id	name
1	grey
2	red
...	...

CREATE TABLE Color (
id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
name VARCHAR(15)
)

INSERT INTO Color values(1, 'grey');
INSERT INTO Color values(2,'red');

№4来源表:国家/地区

id	name
1	Russia
2	Germany
3	South Korea
...	...

CREATE TABLE Country (
id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
name VARCHAR(50)
)

INSERT INTO Country values(1, 'Russia');
INSERT INTO Country values(2,'Germany');
INSERT INTO Country values(2,'South Korea');

最终表格的№5示例:Cars

id	id_brand	model_name	gasoline_consumption	id_color	id_country
1	3	F80	8,3	2	2
2	1	Vesta	7,3	1	1
3	2	Elantra	5	2	3
...	...	...	...	...	...

CREATE TABLE Cars (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
id_brand INT NOT NULL,
model_name varchar (30),
gasoline_consumption DECIMAL(3,1),
id_color INT NOT NULL,
FOREIGN KEY (id_brand) REFERENCES Brand (id),
FOREIGN KEY (id_color) REFERENCES Color (id),
FOREIGN KEY (id_country) REFERENCES Country (id)
)

enter image description here

我能够将 colored颜色、汽车品牌和国家从原来的桌子转移到其他桌子上.但现在我不明白，如果需要考虑汽车品牌、国家、 colored颜色和车型名称之间的id关系，如何将数据输入到Cars表中.

100

INSERT dbo.Cars (id_brand, model_name, gasoline_consumption, id_color, id_country) SELECT id_brand = b.id, model_name = SUBSTRING(s.auto, CHARINDEX(' ', s.auto), CHARINDEX(', ', s.auto) -CHARINDEX(' ', s.auto)), gasoline_consumption = TRY_CONVERT(DECIMAL(3, 1), s.gasoline_consumption), id_color = cr.id, id_country = c.id FROM dbo.Sales AS s LEFT JOIN dbo.Country AS c ON c.name = s.country LEFT JOIN dbo.Brand AS b ON b.name = LEFT(s.auto,CHARINDEX(' ', s.auto)) LEFT JOIN dbo.Color AS cr ON cr.name = TRIM(RIGHT(s.auto, CHARINDEX(' ', REVERSE(s.auto))))

CREATE VIEW dbo.Sales_Parsed AS SELECT model_name = SUBSTRING(s.auto, CHARINDEX(' ', s.auto), CHARINDEX(', ', s.auto) -CHARINDEX(' ', s.auto)), gasoline_consumption = TRY_CONVERT(DECIMAL(3, 1), s.gasoline_consumption), color = TRIM(RIGHT(s.auto, CHARINDEX(' ', REVERSE(s.auto)))), brand = LEFT(s.auto,CHARINDEX(' ', s.auto)) FROM dbo.Sales AS s;

INSERT dbo.Country (name) SELECT DISTINCT sp.Country FROM dbo.Sales_Parsed AS sp EXCEPT SELECT c.name FROM dbo.Country AS c; INSERT dbo.Color (name) SELECT DISTINCT sp.color FROM dbo.Sales_Parsed AS sp EXCEPT SELECT c.name FROM dbo.color AS c; INSERT dbo.Brand (name) SELECT DISTINCT sp.brand FROM dbo.Sales_Parsed AS sp EXCEPT SELECT c.name FROM dbo.Brand AS c;

INSERT dbo.Cars (id_brand, model_name, gasoline_consumption, id_color, id_country) SELECT id_brand = b.id, s.model_name, s.gasoline_consumption, id_color = cr.id, id_country = c.id FROM dbo.Sales_Parsed AS s INNER JOIN dbo.Country AS c ON c.name = s.country INNER JOIN dbo.Brand AS b ON b.name = s.brand INNER JOIN dbo.Color AS cr ON cr.name = s.color;

SQL数据库规范化与数据插入

推荐答案

Sql相关问答推荐

创建每小时重置的序列号

Postgresql：从jsons数组到单个id索引的json

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数？

SQL—如何根据2列填写缺失的值

替换条件中的单元格值

如何优化我的功能以减少花费的时间？

分组多输出访问查询问题

我希望以正确的升序获取SQL结果.怎样才能得到它们？

如何在Presto中将多个列合并到一个数组中

在数据库中搜索列

将日期时间转换为日期格式

将Dense_RANK列为聚合(非解析)函数(&A)

动态组/转置

最小非重复集的SQL查询

在presto sql中解析带有区域的时间格式

如何在 postgres 中旋转某些数据字段

根据行号将列转置为没有任何id或键列的行

在 PostgreSQL 中使用重音敏感排序进行重音不敏感搜索

如何在 PL/SQL 中区分返回的 XML 值？

在 AWS athena 的视图之上创建视图时，如何消除此错误：列别名列表有 1 个条目但t有 4 列可用？