数据操纵语言和数据库定义语言，创建基本表

环境：PHPStudy
使用SQL语句创建基本表。建立TPCH数据库，TPCH数据库由地区表Region，国家表Nation，供应商基本表Supplier，零件基本表Part，零件供应联系表PartSupp，顾客表Customer，订单表Orders，订单明细表Lineitem ，这8个基本表组成。TPCH数据模式图如下：

基本表：

create database TPCH;
set names gbk;
use TPCH;
#地区表#
create table Region(
regionkey integer primary key,
name char(25),
comment varchar(152));
describe Region; 
#国家表#
create table Nation(
nationkey integer primary key,
name char(25),
regionkey integer references Region(regionkey),
comment varchar(152));
describe Nation; 
#供应商基本表#
create table Supplier(
suppkey integer primary key,
name char(25),
address varchar(40),
nationkey integer references Nation(nationkey),
phone char(15),
acctbal real,
comment varchar(101));
describe Supplier;
#零件基本表#
create table Part(
partkey integer primary key,
name char(55),
mfgr char(25),
brand char(10),
type varchar(25),
size integer,
container char(10),
retailprice real,
comment varchar(23));
describe Part;
#零件供应联系表#
create table PartSupp(
partkey integer references Part(partkey),
suppkey integer references Supplier(suppkey),
availqty integer,
supplycost real,
comment varchar(199),
primary key(partkey,suppkey));
describe PartSupp;
 #顾客表#
 create table Customer(
 custkey integer primary key,
 name varchar(25),
 address varchar(40),
 nationkey integer references Nation(nationkey),
 phone char(15),
 acctbal real,
 mktsegment char(10),
 comment varchar(23));
 describe Customer;
  #订单表#
  create table Orders(
  orderskey integer primary key,
  custkey integer references Customer(custkey),
  orderstatus char(1),
  totalprice real,
  orderdate date,orderpriority char(15),
  clerk char(15),
  shippriority integer,
  comment varchar(79));
  describe Orders; 
  #订单明细表#
  create table Lineitem(
  orderskey integer references Orders(orderskey),
  partkey integer references Part(partkey),
  suppkey integer references Supplier(suppkey),
  linenumber integer,
  quantity real,
  extendedprice real,
  discount real,
  tax real,
  returnflag char(1),
  linestatus char(1),
  shipdate date,
  commitdate date,
  receiptdata date,
  shipinstruct char(25),
  shipmode char(10),
  comment varchar(44),
  primary key (orderskey,linenumber),
  foreign key (partkey,suppkey) references PartSupp(partkey,suppkey));
  describe Lineitem;

查询：

#单表查询##（实现投影操作）查询供应商的名称，地址和联系电话#
select name,address,phone
from Supplier;
#（实现选择操作）查询最近一周内提交的总价大于1000元的订单的编号，顾客编号等订单的所有信息#
select *
from Orders
where current_date-orderdate<7 and totalprice >1000;
#不带分组过滤条件的分组统计查询##统计每个顾客的订购金额#
select C.custkey,SUM(O.totalprice)
from Customer C,Orders O
where C.custkey=O.custkey
group by C.custkey; 
#带分组过滤条件的分组统计查询##查询订单平均金额超过1000元的顾客编号及其姓名#
select C.custkey,MAX(C.name)
from Customer C,Orders O
where C.custkey=O.custkey
group by C.custkey
having avg(O.totalprice)>1000; 
#单表自身连接查询##查询与“金仓集团”在同一个国家的供应商编号，名称和地址信息#
select F.suppkey,F.name,F.addressfrom Supplier F,Supplier S
where F.nationkey=S.nationkey and S.name='金仓集团'; 
#两表连接查询（普通连接）##查询供应价格大于零售价的零件名，制造商名，零售价格和供应价格#
select P.name,P.mfgr,P.retailprice,Ps.supplycost
from Part P,PartSupp PS
where P.retailprice>PS.supplycost;
#两表连接查询（自然连接）##查询供应价大于零售价格的零件名，制造商名，零售价和供应价#
select P.name,P.mfgr,P.retailprice,Ps.supplycost
from Part P,PartSupp PS
where P.retailprice=PS.supplycost
and P.retailprice>PS.supplycost;
#三表连接查询##查询顾客“苏举库”订购的订单编号，总价极其订购的零件编号，数量和明细价格#
select O.orderskey,O.totalprice,L.partkey,L.quantity,L.extendedprice
from Customer C,Orders O,Lineitem L
where C.custkey=O.custkey 
and O.orderskey=L.orderskey 
and C.name='苏举库';