为何业务这么喜欢“宽表”? | 相对而言的“窄表”,并非单指字段比较少,而是严格按照数据库设计范式,尽量减少数据冗余。 |
宽表就像个自助餐厅,可以提供给你绝大多数想要的食材需求,相对于之前还要自己去找数、加工、验证,省下了很多功夫 | |
create table oders_analyse as (select o.orderid, o.amount, o.discount, p.proname, p.price, b.typename, s.supname, s.linkname, k.stockamount from orders o left join product p on o.proid = p.proid left join proctype b on p.protype = b.typeid left join supplier s on p.supid = s.supid left join stock k on o.proid = k.proid ) comment '订单分析宽表';
报错,分两次,建表+加表注释
-------------------------------------------------------------------
SHOW TABLE STATUS WHERE name='orders_analyse'\G;
select * from table ·orders_analyse·\G;
select * from table orders_analyse\G;
\g,\G不区分大小写
数列显示
-------------------------------------------------------------------
sql代码写在代码框里,粘贴在正文里多了换行符。
建表同时建表注释报错。comment 用单引号修饰。字符都是单引号,字段名是反单引号,表名是反单引号。
CREATE TABLE oders_analyse AS (
SELECT o.orderid, o.amount, o.discount, p.proname, p.price, b.typename, s.supname, s.linkname, k.stockamount
FROM orders o
LEFT JOIN product p ON o.proid = p.proid
LEFT JOIN proctype b ON p.protype = b.typeid
LEFT JOIN supplier s ON p.supid = s.supid
LEFT JOIN stock k ON o.proid = k.proid);
alter table oders_analyse comment = '修改后的表注释信息(用户信息表)';
ALTER TABLE oders_analyse RENAME orders_analyse;
alter table orders_analyse comment = '宽表';
/*
Since the tables already exist, we only need to show their schema using the following commands:
*/
SHOW CREATE TABLE orders;
SHOW CREATE TABLE product;
SHOW CREATE TABLE proctype;
SHOW CREATE TABLE supplier;
SHOW CREATE TABLE stock;