sql三表查询_TPCH数据库SQL示例

Q0.创建删除用户：

--SQL Server 服务器级别，创建登陆帐户（create login）
create login lina with password='123456', default_database=TCPH
--为登陆账户创建数据库用户（create user）
create user lina for login lina with default_schema=dbo

--彻底删除用户
EXEC sp_dropuser 'lina'
EXEC sp_droplogin 'lina'

Q1.创建删除模式：

--创建一个模式TPCHDEMO，并在模式中创建表和视图
CREATE SCHEMA TPCHDEMO
CREATE TABLE TPCHDEMO.part(p_partkey int,p_name varchar(22),p_category varchar(7));
CREATE VIEW TPCHDEMO.part_view AS select p_name,p_category from part where P_PARTKEY<200;

--删除模式，先删除下属的数据库对象
drop table TPCHDEMO.part
drop view TPCHDEMO.part_view
drop schema TPCHDEMO

Q2.模式转移

--模式转移
CREATE SCHEMA temp
  CREATE TABLE users(id INT,username VARCHAR(30)) ;

--将temp模式下的users表转让给TPCHDEMO模式
CREATE SCHEMA TPCHDEMO
ALTER SCHEMA TPCHDEMO TRANSFER OBJECT::temp.users; 

Q3.修改表

--增加列
ALTER TABLE LINEITEM ADD L_SURRKEY int;
--修改列的数据类型
ALTER TABLE LINEITEM ALTER COLUMN L_QUANTITY SMALLINT;
ALTER TABLE ORDERS ALTER COLUMN O_ORDERPRIORTY varchar(15) NOT NULL;
--增加实体完整性约束，即主键约束
ALTER TABLE SUPPLIER ALTER COLUMN S_SUPPKEY integer NOT NULL;  --非空约束
ALTER TABLE SUPPLIER ADD CONSTRAINT PK_S PRIMARY KEY(S_SUPPKEY); --主键
--增加参照完整性,即外键约束
ALTER TABLE LINEITEM ADD CONSTRAINT FK_S FOREIGN KEY(L_SURRKEY) REFERENCES SUPPLIER(S_SUPPKEY);
--删除外键约束
ALTER TABLE LINEITEM DROP CONSTRAINT FK_S;
--删除列
ALTER TABLE LINEITEM DROP COLUMN L_SURRKEY;

Q4.索引的创建与删除

一个基本表只能创建一个聚集索引，默认对主键建立聚集索引

--对s_name创建唯一索引
CREATE UNIQUE INDEX s_name_Inx ON SUPPLIER(S_NAME);

--对比有无索引的查询执行计划
SELECT * FROM SUPPLIER AS s WHERE s.S_NAME='Supplier#000000728';	
--建立索引后，在索引列中进行查找，然后从原始表定位索引中查找的记录

--删除索引,和创建索引的语法相对应
DROP INDEX s_name_Inx ON SUPPLIER;
SELECT * FROM SUPPLIER AS s WHERE s.S_NAME='Supplier#000000728';	

--s_nation和s_city创建复合索引
CREATE INDEX s_n_c_index ON SUPPLIER(S_NATIONKEY,S_ADDRESS); --非唯一索引
DROP INDEX s_n_c_index ON SUPPLIER;

创建唯一索引后的执行计划

删除唯一索引后的执行计划

SELECT l.L_RETURNFLAG, l.L_LINESTATUS, sum(l.L_EXTENDEDPRICE*(1+l.L_TAX)*(1-l.L_DISCOUNT)) AS total
  FROM LINEITEM AS l
  WHERE l.L_SHIPDATE<='1998-12-01'
  GROUP BY l.L_RETURNFLAG,l.L_LINESTATUS;

--对lineitem表创建列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_lineorder 
ON LINEITEM(L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_LINESTATUS,L_SHIPDATE,L_RETURNFLAG);


--删除列索引
DROP INDEX csindx_lineorder ON LINEITEM;

SELECT l.L_RETURNFLAG, l.L_LINESTATUS, sum(l.L_EXTENDEDPRICE*(1+l.L_TAX)*(1-l.L_DISCOUNT)) AS total
  FROM LINEITEM AS l
  WHERE l.L_SHIPDATE<='1998-12-01'
  GROUP BY l.L_RETURNFLAG,l.L_LINESTATUS;

创建列索引后的执行计划

删除列索引后的执行计划

---

接下来，介绍sql查询

一、单表查询

1.投影操作（简单，略）

• 1.1查询全部的列 *
• 1.2查询指定的列
• 1.3查询表达式列
• 1.4查询列中不同的成员 distinct

2.选择操作（简单，略）

• 2.1比较大小
• 2.2查询指定范围的记录 between and
• 2.3集合判断 in，not in
• 2.4字符匹配 like，%任意字符串，_任意一个字符
• 2.5控制判断 is null，is not null
• 2.6符合条件表达式 and，or

--复合条件查询,1998年4月1日至1998年9月12日之间，交易量大于25的销售利润
SELECT l.L_EXTENDEDPRICE*(1- l.L_DISCOUNT) AS revenue,l.L_SHIPDATE,l.L_QUANTITY
  FROM LINEITEM AS l
WHERE l.L_SHIPDATE BETWEEN '1998-04-01' AND '1998-09-12' 
AND l.L_QUANTITY>25; --197463条记录

3.聚集操作

• 3.1聚集函数 sum，avg，max，min，count

SELECT SUM(CASE WHEN o.O_ORDERPRIORITY='1-urgency' OR o.O_ORDERPRIORITY='2-high' THEN 1 ELSE 0 end) 
as high_line_count , --紧急订单量300091
SUM(CASE WHEN o.O_ORDERPRIORITY<>'1-urgency' OR o.O_ORDERPRIORITY<>'2-high' THEN 1 ELSE 0 end) 
AS low_line_count  --非紧急订单量1500000
FROM ORDERS AS o;

• 3.2分组操作 groupby，having
• 简单分组，ROLLUP分组和CUBE分组的区别可以参考这篇精致的博客

https://blog.csdn.net/make_1998/article/details/82938038​blog.csdn.net

• 3.3排序操作 asc，desc

二、连接查询

1.笛卡尔连接、等值连接、自然连接、非等值连接

• 1.1未设置连接条件时，两表执行笛卡尔连接
• 1.2当两个表存在主外键参照关系时，通常执行等值连接

--连接customer、orders和lineitem表
--筛选bulidng类顾客，下单时间在1995/01/01之前，发货时间在1955/01/01之后
--按订单时间升序并按发货延误时间降序排列
SELECT l.L_ORDERKEY ,o.O_ORDERDATE, l.L_SHIPDATE,DATEDIFF(day,o.O_ORDERDATE,l.L_SHIPDATE) AS delayday
  FROM LINEITEM AS l,ORDERS AS o,CUSTOMER AS c --三表连接
WHERE o.O_ORDERKEY=l.L_ORDERKEY AND o.O_CUSTKEY=c.C_CUSTKEY
AND c.C_MKTSEGMENT='BUILDING'
AND o.O_ORDERDATE<'1995-01-01'
AND l.L_SHIPDATE>'1995-01-01'  --发货时间迟于
GROUP BY l.L_ORDERKEY,o.O_ORDERDATE,l.L_SHIPDATE
ORDER BY o.O_ORDERDATE,delayday DESC;

2.自身连接

--查询同时包含deliver in person和take back return的订单
SELECT L_ORDERKEY,L_SHIPINSTRUCT from lineitem WHERE L_ORDERKEY  
IN(
	SELECT distinct(l.L_ORDERKEY)
    FROM LINEITEM AS l,LINEITEM AS l2
    WHERE l.L_ORDERKEY=l2.L_ORDERKEY
    AND l.L_SHIPINSTRUCT='DELIVER IN PERSON'
    AND l2.L_SHIPINSTRUCT='TAKE BACK RETURN');

#说明：若直接L_SHIPINSTRUCT='DELIVER IN PERSON' or l.L_SHIPINSTRUCT='TAKE BACK RETURN'，会放大查询结果集。

3.外连接

• 3.1左外连接 left outer join
• 3.2右外连接 right outer join
• 3.3全外连接 full outer join

4.多表连接

--partpartsuppsupplier表的星形连接
SELECT p.P_NAME,s.S_NAME,p.P_BRAND,p2.PS_AVAILQTY
  FROM PART AS p, SUPPLIER AS s, PARTSUPP AS p2
WHERE p2.PS_PARTKEY=p.P_PARTKEY
and s.S_SUPPKEY=p2.PS_SUPPKEY;
--耗时4.03

SELECT P_NAME,S_NAME,P_BRAND,PS_AVAILQTY
FROM PARTSUPP AS p
INNER JOIN SUPPLIER AS s ON p.PS_SUPPKEY=s.S_SUPPKEY
INNER JOIN PART AS p2 ON p.PS_PARTKEY=p2.P_PARTKEY;
--耗时4.09

--8个表的雪花形连接
SELECT c.C_NAME, o.O_ORDERDATE, p.P_NAME, r.R_NAME, n.N_NAME,s.S_NAME,
 (l.L_EXTENDEDPRICE*(1-l.L_DISCOUNT)-p2.PS_SUPPLYCOST*l.L_AVAILQTY) AS mount
  FROM CUSTOMER AS c, ORDERS AS o, PART AS p, REGION AS r, NATION AS n,
       LINEITEM AS l, PARTSUPP AS p2, SUPPLIER AS s
       
WHERE l.L_ORDERKEY=o.O_ORDERKEY 

AND l.L_PARTKEY=p.P_PARTKEY 
AND l.L_SUPPKEY=s.S_SUPPKEY

and p2.PS_PARTKEY=l.L_PARTKEY
AND p2.PS_SUPPKEY=l.L_SUPPKEY 

AND n.N_REGIONKEY=r.R_REGIONKEY
AND c.C_NATIONKEY=n.N_NATIONKEY 
AND s.S_NATIONKEY=n.N_NATIONKEY

AND c.C_CUSTKEY=o.O_CUSTKEY ;

三、嵌套查询

注：子查询不能直接使用order by语句，order by只对最终的查询结果排序。但在子查询中，可以和top共同使用。

1.包含IN谓词的子查询（返回有限值）

不相关子查询，子查询可以独立执行

--in子查询
SELECT p.P_BRAND, p.P_SIZE,p.P_TYPE,count(distinct(p2.PS_SUPPKEY)) AS supplier_cnt --配件品牌、大小、类型，卖家数量
  FROM PART AS p, PARTSUPP AS p2
WHERE p2.PS_PARTKEY=p.P_PARTKEY
AND p.P_BRAND<>'Brand#45'  --配件品牌不是45
AND p.P_TYPE NOT LIKE 'MEDIUM POLISHED%'  --配件类型不是中等磨光的
AND p2.PS_SUPPKEY NOT IN (
	SELECT s.S_SUPPKEY FROM SUPPLIER AS s WHERE 
s.S_COMMENT LIKE '%Customer%Complaints%') --不是顾客抱怨的卖家
GROUP BY p.P_BRAND, p.P_SIZE ,p.P_TYPE; 

--等价于
SELECT p.P_BRAND, p.P_SIZE,p.P_TYPE,count(distinct(p2.PS_SUPPKEY)) AS supplier_cnt --配件品牌、大小、类型，卖家数量
  FROM PART AS p, PARTSUPP AS p2,SUPPLIER AS s
WHERE p2.PS_PARTKEY=p.P_PARTKEY
AND p2.PS_SUPPKEY=s.S_SUPPKEY
AND p.P_BRAND<>'Brand#45'  --配件品牌不是45
AND p.P_TYPE NOT LIKE 'MEDIUM POLISHED%'  --配件类型不是中等磨光的
AND s.S_COMMENT NOT LIKE '%Customer%Complaints%' --不是顾客抱怨的卖家
GROUP BY p.P_BRAND, p.P_SIZE ,p.P_TYPE; 

2.带有比较运算符的子查询（返回单个值）

--比较字符子查询
SELECT s.S_ACCTBAL, s.S_NAME,p.PS_SUPPLYCOST  --117767条记录
FROM PARTSUPP AS p,SUPPLIER AS s,PART AS p2
WHERE s.S_SUPPKEY=p.PS_SUPPKEY
AND p2.P_PARTKEY=p.PS_PARTKEY
 
AND p.PS_SUPPLYCOST=(
	SELECT MIN(p.PS_SUPPLYCOST) --最小供应成本
	FROM PARTSUPP AS p,NATION AS n,REGION AS r,SUPPLIER AS s
	WHERE r.R_REGIONKEY=n.N_REGIONKEY
	AND s.S_NATIONKEY=n.N_NATIONKEY
	AND p.PS_SUPPKEY=s.S_SUPPKEY
	AND p2.P_PARTKEY = p.PS_PARTKEY
	AND r.R_NAME='EUROPE') --欧洲供应商
	;

3.带有any或all谓词的子查询（返回多值）

--ANY谓词，巨慢
SELECT * FROM
	LINEITEM AS l
WHERE l.L_EXTENDEDPRICE>ANY(SELECT l2.L_EXTENDEDPRICE FROM NATION AS n,LINEITEM AS l2,SUPPLIER AS s
                            WHERE s.S_NATIONKEY=n.N_NATIONKEY AND l2.L_SUPPKEY=s.S_SUPPKEY
                            AND n.N_NAME='China');

4.带有exist谓词的子查询（不返回任何数据，只返回true 或false）

--没有下过单的顾客数
SELECT c.C_CUSTKEY FROM CUSTOMER AS c
WHERE NOT EXISTS (SELECT o2.O_CUSTKEY FROM ORDERS AS o2,LINEITEM AS l
                  WHERE l.L_ORDERKEY=o2.O_ORDERKEY);

四、集合查询

1.集合并 UNION

--查询lineitem表的shipmode未air或air reg以及shipinstruct为deliver in person的订单号
SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l 
WHERE l.L_SHIPINSTRUCT='DELIVER IN PERSON'

UNION  --去重后，1132580条记录

SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l
WHERE l.L_SHIPMODE='AIR' OR  l.L_SHIPMODE='AIR REG'
;

SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l 
WHERE l.L_SHIPINSTRUCT='DELIVER IN PERSON'

UNION ALL  --不去重，1595549条记录

SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l
WHERE l.L_SHIPMODE IN ('AIR','AIR REG')
;

2.集合交 INTERSECT

--查询container未warp box、med case、jumbo pack，且ps_avalqty低于1000
SELECT p.P_NAME FROM PART AS p
WHERE p.P_CONTAINER IN ('WARP BOX','MED CASE','JUMBO PACK')
INTERSECT
SELECT p2.P_NAME FROM PARTSUPP AS p,PART AS p2
WHERE p2.P_PARTKEY=p.PS_PARTKEY
AND P.PS_AVAILQTY<1000; --3490条记录

SELECT distinct(p.PS_PARTKEY) FROM PARTSUPP AS p,PART AS p2
WHERE p.PS_PARTKEY=p2.P_PARTKEY
and p.PS_AVAILQTY<1000
AND p2.P_CONTAINER IN ('WARP BOX','MED CASE','JUMBO PACK'); --3490条记录

3.集合差 EXCEPT

--查询lineitem表中shipmode模式为air或air reg，但shipinstruct不是deliver in person的订单号
SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l
WHERE l.L_SHIPMODE IN ('AIR','AIR REG')

EXCEPT 

SELECT distinct(l.L_ORDERKEY) FROM LINEITEM AS l 
WHERE l.L_SHIPINSTRUCT='DELIVER IN PERSON'
;

五、派生表查询

--派生表查询
SELECT C_COUNT,COUNT(*) AS custdist
FROM (SELECT c.C_CUSTKEY, count(o.O_ORDERKEY) AS C_COUNT  --顾客号及订单量
  FROM CUSTOMER AS c LEFT OUTER JOIN ORDERS AS o 
  ON c.C_CUSTKEY=o.O_CUSTKEY
AND o.O_COMMENT NOT LIKE '%special%requests%' --没有特殊要求
GROUP BY c.C_CUSTKEY)
AS C_ORDERS(C_CUSTKEY,C_COUNT)
GROUP BY C_COUNT
ORDER BY C_COUNT,custdist desc;

--觉得结构不清晰的话，可以考虑用with表达式
WITH C_ORDERS(C_CUSTKEY,C_COUNT) AS (
SELECT C_CUSTKEY,COUNT(o.O_ORDERKEY ) --统计没有特殊要求顾客的订单数
FROM CUSTOMER AS c	LEFT OUTER JOIN
 ORDERS AS o ON c.C_CUSTKEY=o.O_CUSTKEY
AND o.O_COMMENT  NOT LIKE '%special%requests%' 
GROUP BY c.C_CUSTKEY
)
SELECT C_COUNT,COUNT(*) AS custdist 
FROM C_ORDERS 
GROUP BY C_COUNT
ORDER BY C_COUNT,custdist DESC;

案例

--查询1993年7月起的3个月内没有购买任何东西的顾客数量
--exists谓词
SELECT COUNT(c.C_CUSTKEY) FROM CUSTOMER AS c  --107133条记录
WHERE NOT EXISTS (
SELECT *
FROM ORDERS AS o
WHERE c.C_CUSTKEY=o.O_CUSTKEY
AND o.O_ORDERDATE BETWEEN '1993-07-01' AND DATEADD(MONTH,3,'1993-07-01')
);
--in谓词
SELECT COUNT(c.C_CUSTKEY) FROM CUSTOMER AS c --107133条记录
WHERE c.C_CUSTKEY  not in (
SELECT c.C_CUSTKEY
FROM ORDERS AS o,CUSTOMER AS c
WHERE c.C_CUSTKEY=o.O_CUSTKEY
AND o.O_ORDERDATE BETWEEN '1993-07-01' AND DATEADD(MONTH,3,'1993-07-01') --1993年7月-10月有买东西的顾客id
);
--集合查询
SELECT c.C_CUSTKEY FROM CUSTOMER AS c
EXCEPT
SELECT DISTINCT o.O_CUSTKEY FROM ORDERS AS o,CUSTOMER AS c
WHERE c.C_CUSTKEY=o.O_CUSTKEY
AND o.O_ORDERDATE BETWEEN '1993-07-01' AND DATEADD(MONTH,3,'1993-07-01');
--派生表
SELECT count(distinct(C_CUSTKEY))
FROM 
(SELECT o.O_CUSTKEY FROM ORDERS AS o
WHERE o.O_ORDERDATE BETWEEN '1993-07-01' AND DATEADD(MONTH,3,'1993-07-01')
) 
AS OC(custkey) --派生表OC，新字段custkey 是customer表右外连接指定日期内的orders表 的顾客号
RIGHT OUTER JOIN CUSTOMER ON C_CUSTKEY=CUSTKEY
      
WHERE custkey IS NULL; --107133条记录

终于敲完了，o(*￣▽￣*)ブ

一起学习鸭！