一些SQL作业

二、创建数据库

use master 

go

if exists(select * from sysdatabases where name='BillingSys')

drop database BillingSys

create database BillingSys

on primary

(

name='BillingSys.mdf',

filename='E:\BillingSystem\Data\BillingSys.mdf',

size=5mb,

maxsize=unlimited,

filegrowth=2mb

),

(

name='BillingSys.ndf',

filename='E:\BillingSystem\Data\BillingSys.ndf',

size=5mb,

maxsize=100mb,

filegrowth=1mb

)

log on

(

name='BillingSys_log.ldf',

filename='E:\BillingSystem\Data_Log\BillingSys_log.ldf',

size=1mb,

maxsize=20mb,

filegrowth=10%

)

三、创建表

use ExpressTest

go

IF EXISTS (SELECT * FROM sysobjects WHERE name='T_Customer')

DROP TABLE T_Customer

create table T_Customer

(

PKID int IDENTITY(1,1),

CustomerCode varchar(20) PRIMARY KEY,

Customer varchar(50) NOT NULL,

Phone varchar(20) CHECK(LEN(Phone)<=12),

Fax varchar(50) CHECK(LEN(Fax)<=12),

CustomerAddress varchar(50),

PostCode varchar(10) CHECK(LEN(PostCode)=6),

Linkman varchar(20),

LinkmanMoblie varchar(20) CHECK(LEN(LinkmanMoblie)=11),

CustomerType varchar(50) DEFAULT 4 CHECK(CustomerType in(1,2,3,4,5)),

EnterpriseProperty smallint CHECK(EnterpriseProperty in(1,2,3)),

EnterpriseSize int,

LinkManEmail varchar(50) Check(LinkManEmail like '%@%')

)

IF EXISTS (SELECT * FROM sysobjects WHERE name='T_Employee')

DROP TABLE T_Employee

create table T_Employee

(

PKID int IDENTITY(1,1),

EmployeeCode Varchar(20) PRIMARY KEY,

Name Varchar(20) NOT NULL,

Gender smallint CHECK(Gender in(0,1)),

BirthDate Smalldatetime,

NativeProvince Varchar(20) CHECK(NativeProvince like '%省%' OR NativeProvince like '%自治区%' OR NativeProvince like '%市%'),

NativeCity Varchar(20) CHECK(NativeCity like '%市%'),

EntryDate Smalldatetime,

PositionType Smallint CHECK(PositionType in(1,2,3,4,5)),

Salary Numeric(6,0)

)

IF EXISTS (SELECT * FROM sysobjects WHERE name='D_ShipList_Main')

DROP TABLE D_ShipList_Main

create table D_ShipList_Main

(

PKID int IDENTITY(1,1),

GoodsBillCode varchar(50) PRIMARY KEY,

SendGoodsCustomerNO varchar(20),

FOREIGN KEY(SendGoodsCustomerNO) REFERENCES T_Customer(CustomerCode),

SendGoodsCustomer varchar(50) NOT NULL,

SendGoodsCustomerTel varchar(50) CHECK(LEN(SendGoodsCustomerTel)<=12),

SendGoodsCustomerAddr varchar(50),

ReceiveGoodsCustomerCode varchar(20),

FOREIGN KEY(ReceiveGoodsCustomerCode) REFERENCES T_Customer(CustomerCode),

ReceiveGoodsCustomer varchar(50) NOT NULL,

ReceiveGoodsCustomerTel varchar(50) CHECK(LEN(ReceiveGoodsCustomerTel)<=12),

ReceiveGoodsCustomerAdder varchar(50) CHECK(ReceiveGoodsCustomerAdder like '%市%' OR ReceiveGoodsCustomerAdder like '%省%'),

SendGoodsDate datetime NOT NULL,

SendGoodsAddr varchar(100) NOT NULL CHECK(SendGoodsAddr like '%市%' OR SendGoodsAddr like '%省%'),

ReceiveGoodsAddr varchar(50) NOT NULL CHECK(ReceiveGoodsAddr like '%市%'),

PredeliveryDate datetime,

FactDealDate datetime,

HelpAcceptprocedureRate numeric(8,2),

AcceptprocedureRate numeric(3,1) DEFAULT 0,

PayMode varchar(50) CHECK(PayMode in(1,2,3,4)),

FetchGoodsMode smallint CHECK(FetchGoodsMode in(1,2,3)),

FetchEmployeeCode varchar(20) NOT NULL,

FOREIGN KEY(FetchEmployeeCode) REFERENCES T_Employee(EmployeeCode),

DistributeGoodsMode smallint CHECK(DistributeGoodsMode in(1,2,3)'),

DistributeEmployeeCode varchar(20),

FOREIGN KEY(DistributeEmployeeCode) REFERENCES T_Employee(EmployeeCode),

WriteBillPerson varchar(50) NOT NULL,

WriteDate datetime NOT NULL,

Validity smallint NOT NULL DEFAULT 1 CHECK(Validity (0,1)),

IfAudit smallint NOT NULL DEFAULT 1 CHECK(IfAudit in(0,1)),

IfSettleAccounts smallint NOT NULL DEFAULT 0 CHECK(IfSettleAccounts in(0,1)),

CarriageFee numeric(8,2) NOT NULL,

DiscountRate numeric(2,2) CHECK(DiscountRate<=1),

ReduceFund numeric(8,2),

PayKickback numeric(8,2),

CarryGoodsFee numeric(8,2),

InsuranceFee numeric(8,2),

Remark varchar(100)

)

四、数据表的基础操作

--查询所有运费大于100元的货运单

Select * from D_ShipList_Main where CarriageFee>100

--查询所有运费大于50元并且小于100元的货运单

Selec t * from D_ShipList_Main

where CarriageFee between 50 and 100

或者

Selec t * from D_ShipList_Main

where CarriageFee > 50 and CarriageFee <100

--查询所有折后价格大于100元的货运单

select GoodsBillCode,SendGoodsCustomer,SendGoodsAddr,ReceiveGoodsAddr,

SendGoodsDate,CarriageFee as '原始运费',DiscountRate as '折扣率',CarriageFee*DiscountRate as '折后运费'

from D_ShipList_Main

where CarriageFee*DiscountRate>100

-- 查询所有“张”姓发件人的货运单

Select * from D_ShipList_Main

where SendGoodsCustomer like '张%'

-- 查询所有未上保险的货运单

Select * from D_ShipList_Main

where InsuranceFee is null  or InsuranceFee=0

-- 查询所有日期在2018年的货运单

Select * from D_ShipList_Main

where SendGoodsDate between '2018-1-1 00:00:00' and '2018-12-31 23:59:59'

--查询已完成审核，但尚未付款的货运单

Select * from D_ShipList_Main

where IfAudit=1 and IfSettleAccounts=0

--查询运费最高的前20条货运单记录

select TOP 20 * from D_ShipList_Main 

order by Carriagefee desc

--查找实际运费（原始运费*折扣率+保险费）在50-100元的所有货运单

select*from D_ShipList_Main

where CarriageFee*DiscountRate+InsuranceFee between 50 and 100

或者

select*from D_ShipList_Main

where CarriageFee*DiscountRate+InsuranceFee>= 50 and CarriageFee*DiscountRate+InsuranceFee<=100

--利用Between查询所有发件时间在2018年1月至6月的货运单

select*from D_ShipList_Main

where SendGoodsDate between '2018-1-1 00:00:00' and '2018-6-30 23:59:59'

-- 利用Like语句查询所有济南或青岛发往山东省外的货运单

select*from D_ShipList_Main

where (SendGoodsAddr  like '%济南%' or SendGoodsAddr like '%青岛%' )

  and (ReceiveGoodsAddr not like '山东%')

--利用In语句查询所有付款方式是现金或微信支付的货运单

select*from D_ShipList_Main

where Paymode in ('1','3')

--查询所有实际运价在100元以内的货运单，要求按运费和保险费进行排序

select*from D_ShipList_Main

where CarriageFee*DiscountRate+InsuranceFee<100

order by CarriageFee,InsuranceFee

五、分组和联合查询

1.分组统计2018年各快递员的总配送次数、单次配送平均重量、配送总重量。

SELECT DistributeEmployeePKID as '配送员',count(DistributeEmployeePKID) AS '总配送次数',AVG(PackageWeight) AS '单次配送平均重量',SUM(PackageWeight) AS '配送总重量'

FROM D_Shiplist_Main

where year(FactDealDate)='2018'

GROUP BY DistributeEmployeePKID

2.分组统计各取件快递员所取的不同包装类型的货物总件数、总重量。

SELECT  FetchEmployeePKID as ‘取件快递员’, FetchGoodsMode as ‘包装类型’, count(*) as ‘货物总件数’, sum(PackageWeight) as ‘总重量’

FROM D_Shiplist_Main

GROUP BY FetchEmployeePKID, FetchGoodsMode

3．联合D_ShipList_Main表和T_Customer表，使用左外联接，查询所有货运单的货运单号、发运时间、到达时间、发货客户姓名

SELECT a.GoodsBillCode AS'货运单号'

        ,a.SendGoodsDate AS'发运时间'

        ,a.FactDealDate AS'到达时间'

        ,b.Customer AS'发货客户姓名'

FROM D_Shiplist_Main a left join T_Customer b

ON a.SendGoodsCustomerNO= b.CustomerCode

4．联合D_ShipList_Main表和T_Customer表、T_Employee表，使用内连接，查询2018年所有运费大于50元货运单的货运单号、发运时间、到达时间、发货客户姓名、取件快递员姓名、送件快递员姓名。

SELECT a.GoodsBillCode AS'货运单号'

       ,a.SendGoodsDate AS'发运时间'

       ,a.FactDealDate AS'到达时间'

       ,b.Customer AS'发货客户姓名'

    ,C.Name AS'取件快递员姓名'

    ,D.Name AS'送件快递员姓名'

FROM D_Shiplist_Main a inner join T_Customer b

ON a.SendGoodsCustomerNO=b.CustomerCode

inner join T_Employee c

ON a.FetchEmployeePKID=c.EmployeeCode

 inner join T_Employee d

ON a.DistributeEmployeePKID=d.EmployeeCode

WHERE year(SendGoodsDate)=’2018’ and (DiscountRate*CarriageFee-ReduceFund+InsuranceFee)>50

六、子查询

1. 查找非注册客户发运的所有运单并按日期和费用排序，查询结果包括：运单号、发运人姓名、发件时间、到达时间、货运费用。

SELECT GoodsBillCode AS '运单号'

       ,SendGoodsCustomer AS '发运人姓名'

,SendGoodsDate AS '发件时间'

    ,FactDealDate AS '到达时间'

    ,CarriageFee AS '货运费用'

FROM D_ShipList_Main

WHERE SendGoodsCustomerNO NOT IN (SELECT CustomerCode FROM T_Customer)

ORDER BY SendGoodsDate,CarriageFee

1. 查找所有运价比从山东到北京最高运价还要高的货运单，查询结果包括：运单号、发件日期、收件日期、取件员工姓名、配送员工姓名。

SELECT a.GoodsBillCode AS '运单号'

       ,a.SendGoodsDate AS '发件日期'

,a.FactDealDate AS '收件日期'

    ,b.Name AS '取件员工姓名'

    ,c.Name AS '配送员工姓名'

FROM D_ShipList_Main a

INNER JOIN T_Employee b

ON a.FetchEmployeePKID=b.EmployeeCode

INNER JOIN T_Employee c

ON a.DistributeEmployeePKID=c.EmployeeCode

WHERE CarriageFee>

(

   SELECT MAX(CarriageFee)

   FROM D_ShipList_Main

   WHERE SendGoodsAddr LIKE '山东%'

    AND

    ReceiveGoodsAddr LIKE '北京%'

)

3．利用ALL子查询查找所有实付费用（运费*折扣率-扣减费用+保险费）大于客户“李大和” 所有运单费用的货运单信息，查询结果包括：运单号、发货日期、到达日期、实付费用、客户姓名）

SELECT a.GoodsBillCode AS '运单号'

       ,a.SendGoodsDate AS '发货日期',a.FactDealDate AS '到达日期',a.CarriageFee*DiscountRate-ReduceFund+InsurenceFEE AS '实付费用', b.Customer AS '客户姓名'

FROM D_ShipList_Main a

INNER JOIN T_Customer b

ON a.SendGoodsCustomerNO=b.CustomerCode

WHERE (CarriageFee*DiscountRate-ReduceFund+InsurenceFEE)>ALL

(

  SELECT CarriageFee

  FROM D_ShipList_Main

  WHERE SendGoodsCustomerNO=

  (

    SELECT CustomerCode FROM T_Customer WHERE Customer LIKE '李大和'

  )

)

补充内容：

1. 找出配送间隔在72小时内的所有货运记录（DateDiff）；

SELECT*

FROM D_ShipList_Main

WHERE DATEDIFF(HOUR,SendGoodsDate,FactDealDate)<=72

1. 查询所有延误（比预定时间晚）12小时以上的货运单（DateDiff）;

SELECT*

FROM D_ShipList_Main

WHERE DATEDIFF(HH,PredeliveryDate,FactDealDate)>=12

3.找出所有实际运费在100元以上，并且配送间隔在72小时内的货运记录，按实际运费倒序排列

SELECT*

FROM D_ShipList_Main

WHERE (DATEDIFF(HOUR,SendGoodsDate,FactDealDate)<=72)

AND(CarriageFee>100)

ORDER BY CarriageFee DESC

视图

1.创建视图，内容为2018年从山东发往外省的货运单信息（包括：运单号、发运日期、实际到达日期、包裹重量、客户姓名、取件员姓名、标准运费、实付费用）；

use ExpressTest

if Exists(select * from sysobjects where name='V_2018')

    drop view V_2018

go

CREATE VIEW V_2018

AS

(

    SELECT s.GoodsBillCode, s.SendGoodsDate, s.FactDealDate,s.PackageWeight,s.SendGoodsCustomer,s.ReceiveGoodsCustomer,e.Name,s.SendGoodsAddr,s.ReceiveGoodsAddr

from D_ShipList_Main s join T_Employee e on s.FetchEmployeePKID=e.EmployeeCode

where year(SendGoodsDate)='2018' and left(SendGoodsAddr,2)='山东' and left(ReceiveGoodsAddr,2)<>'山东'

)

2.创建视图，内容为2018年所有实付运费在30元以上的货运单（包括：运单号、发运日期、实际到达日期、包裹重量、客户姓名、标准运费、实付费用）

CREATE VIEW V_ActualFee2018

AS

(

SELECT a.GoodsBillCode  as '运单号'

            ,a.SendGoodsDate as '发运时间'

            ,a.FactDealDate as '实际到达日期'

            ,a.PackageWeight as '包裹重量'

            ,b.Customer as '客户姓名'

            ,a.CarriageFee as '标准运费'

            ,a.CarriageFee*DiscountRate-ReduceFund+InsuranceFee as '实付费用'

FROM D_ShipList_Main a

INNER JOIN T_Customer b

ON a.SendGoodsCustomerNO = b.CustomerCode

WHERE YEAR(SendGoodsDate)='2018'

            AND CarriageFee*DiscountRate-ReduceFund+InsuranceFee > 30

)

3．创建视图，内容为所有客户2018年的收件量统计结果，视图中包括：客户编码、客户姓名，收件总数量、收件总重量、总运费、单件平均运费。

CREATE VIEW V_RGQuantity2018

AS

(

SELECT a.ReceiveGoodsCustomerCode as '客户编码'

            ,b.Customer as '客户姓名'

            ,COUNT(a.ReceiveGoodsCustomerCode) as '收件总数量'

            ,SUM(a.PackageWeight) as '收件总重量'

            ,SUM(a.CarriageFee) as '总运费'

            ,AVG(a.CarriageFee) as '单件平均运费'

FROM D_ShipList_Main a

INNER JOIN T_Customer b

ON a.ReceiveGoodsCustomerCode = b.CustomerCode

WHERE YEAR(SendGoodsDate)='2018'

GROUP BY a.ReceiveGoodsCustomerCode

)

存储过程

编写存储过程，查询某个客户某一年的收件总数量，并写出查询客户号是‘201011756’ 2018年的收件总数量执行存储过程的语句

CREATE PROCEDURE USP_ReceiveGoodAmount

(

@cus_ID varchar(20),

@year char(4),

)

AS

Begin

select ReceiveGoodsCustomerCode as '客户编码', COUNT(a.ReceiveGoodsCustomerCode) as '收件总数量'

FROM D_ShipList_Main

WHERE YEAR(SendGoodsDate)=year and ReceiveGoodsCustomerCode=cus_ID

GROUP BY a.ReceiveGoodsCustomerCode

End

exec USP_ReceiveGoodAmount ‘201011756’, ‘2018’