订单管理系统对 SQL SEVER数据库进行操作

mortal616

已于 2024-06-18 23:15:21 修改

阅读量1k

点赞数 26

文章标签：数据库 sql

于 2024-06-18 22:52:29 首次发布

本文链接：https://blog.csdn.net/qq_56437115/article/details/139785609

版权

创建“订单管理系统”数据库，数据库名称marketing。

USE master

IF EXISTS(SELECT * FROM sysdatabases WHERE name ='marketing')

DROP DATABASE marketing

CREATE DATABASE marketing

ON PRIMARY

( NAME ='marketing_data',

FILENAME='D:\marketing_data.mdf',

SIZE=3MB,

FILEGROWTH = 15%)

LOG ON

( NAME='marketing_log',

FILENAME='D:\marketing.ldf',

SIZE=1MB,

FILEGROWTH = 15%

)

（二）创建Marketing数据库中各表，并为各表设置主键、约束、创建关联，最后向表中录入数据。

表1 销售人员表

说明：

“工号”设置为主键。

“姓名”不允许为空。

“电话”设置唯一约束，设置为不超过11位数字的检查约束。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 销售人员 (

工号 INT PRIMARY KEY,

部门号 INT,

姓名 VARCHAR(50) NOT NULL,

地址 VARCHAR(100),

电话 INT UNIQUE CHECK (len(电话)<=11),

性别 VARCHAR(2)

);

INSERT INTO 销售人员(工号, 部门号, 姓名, 地址, 电话, 性别)

VALUES (1, 1, '李求一', '北京市阳区', '25152454', '男'),

(2, 2, '王巧敏', '北京市海淀区', '25345656', '女'),

(3, 3, '张零七', '深圳市南山区', '25152342', '男'),

(4, 2, '钱守空', '深圳市罗湖区', '27352659', '男'),

(5, 3, '周运', '北京市魏公村', '83695245', '男'),

(6, 4, '鹏迎夏', '北京市天坛', '81265987', '女');

表2 部门信息表

说明：

“编号”设置为主键。

“名称”不允许为空。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 部门信息 (

编号 INT PRIMARY KEY,

名称 VARCHAR(50) NOT NULL,

经理 INT,

人数 INT

);

INSERT INTO 部门信息 (编号, 名称,经理,人数)

VALUES (1,'计算机销售部',1,10 ),

(2, '手机销售部',2,200 ),

(3,'打印机销售部',3,300);

表3 客户信息表

说明：

“编号”设置为主键。

“姓名”不允许为空。

“电话”设置唯一约束，设置为不超过11位数字的检查约束。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 客户信息 (

编号 INT PRIMARY KEY NOT NULL,

姓名 VARCHAR(50) NOT NULL,

地址 VARCHAR(100),

电话 VARCHAR(13) UNIQUE CHECK (电话 like '[0-9]'

OR 电话 like '[0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

);

表4 货品信息表

说明：

“编码”为主键，并设置为自动产生序号的自动编号字段-----“标识列”。

“名称”非空约束。

“状态”默认约束true表示可出售。

“售价”设置检查约束，必须大于“成本价”。

“库存量”设置检查约束，必须大于0。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 货品信息 (

编码 INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

名称 VARCHAR(50) NOT NULL,

库存量 INT CHECK (库存量 > 0),

供应商编码 INT,

状态 BIT DEFAULT 'true',

成本价 MONEY,

售价 MONEY

);

ALTER TABLE 货品信息

ADD CONSTRAINT chk_售价 CHECK (售价 > 成本价);

表5 订单信息表

说明：

“订单号”设置为主键。

“名称”不允许为空。

“订货日期”设置系统当前日期为默认值约束。

“客户编号”设置外键约束引用“客户信息”的“编号”值并与该表建立关联。

“货品编号”设置外键约束引用“货品信息”的“编码”值并与该表建立关联。

“销售工号”设置外键约束引用“销售人员”的“工号”值并与该表建立关联。

“数量”设置检查约束大于0。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 订单信息 (

订单号 INT PRIMARY KEY NOT NULL,

销售工号 INT FOREIGN KEY REFERENCES 销售人员(工号),

货品编号 INT FOREIGN KEY REFERENCES 货品信息(编码),

客户编号 INT FOREIGN KEY REFERENCES 客户信息(编号),

数量 INT CHECK (数量>0) NOT NULL,

总金额 MONEY,

订货日期 DATETIME DEFAULT GETDATE(),

交货日期 DATETIME DEFAULT GETDATE(),

);

表6供应商信息

说明：

“编码”为主键，

“名称”非空约束。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 供应商信息 (

编码 INT PRIMARY KEY NOT NULL,

名称 VARCHAR(50) NOT NULL,

联系人 VARCHAR(10),

地址 VARCHAR(50),

电话 VARCHAR(13)

);

根据表的结构，插入数据并进行测试。
根据表的结构，插入数据并进行测试。
查询marketing数据库的“销售人员”表，列出表中的所有记录，给出销售人员的姓名和电话。

USE Marketing; -- 切换到Marketing数据库

SELECT 姓名,电话 FROM 销售人员;
查询marketing数据库的“货品信息”表，列出表中的所有记录，每个记录包含货品的编码、货品名称和库存量，显示的字段名分别为货品编码、货品名称和货品库存量。
USE Marketing; -- 切换到Marketing数据库

SELECT 编码 AS 货品编码,名称 AS 货品名称,库存量 AS 货品库存量 FROM 货品信息;
查询marketing数据库的“订单信息”表，列出表中的所有记录，每个记录包含货品编码和数量，同时要求货品编码不重复。
USE Marketing; -- 切换到Marketing数据库

SELECT DISTINCT 货品编号, 数量 FROM 订单信息;
查询marketing数据库的“订单信息”表，给出查询结果的前3条记录，每个记录包含货品编码和数量
SELECT TOP 3 货品编号, 数量 FROM 订单信息
将“客户信息”表中深圳地区的客户信息插入到“深圳客户”表中。
USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 深圳客户 (

    编号 INT PRIMARY KEY NOT NULL,

    姓名 VARCHAR(50) NOT NULL,

    地址 VARCHAR(100),

    电话 VARCHAR(13)  UNIQUE CHECK (电话 like '[0-9]'

    OR 电话 like '[0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    ),

);

INSERT INTO 深圳客户 (编号, 姓名,  地址,电话)

SELECT 编号, 姓名,  地址,电话

FROM 客户信息

WHERE 地址 LIKE '深圳%';

创建和“客户信息”表结构相同的“上海客户”表，该表无记录。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 上海客户 (

    编号 INT PRIMARY KEY NOT NULL,

    姓名 VARCHAR(50) NOT NULL,

    地址 VARCHAR(100),

    电话 VARCHAR(13)  UNIQUE CHECK (电话 like '[0-9]'

    OR 电话 like '[0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR 电话 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    ),

);
由“订单信息”表中找出订货量大于等于10的订单信息。
SELECT * FROM 订单信息 WHERE 数量 >= 10;
由“订单信息”表中找出订货时间是2008年的订单。
SELECT * FROM 订单信息 WHERE YEAR(订货日期) = 2008;
由“销售人员”表中找出下列人员的信息：周运，张零七，李求一。
SELECT * FROM 销售人员 WHERE 姓名 IN ('周运', '张零七', '李求一');
由“客户信息”表中找出所有深圳区域的客户信息。
SELECT * FROM 客户信息 WHERE 地址 like '%深圳%';
由“订单信息”表中找出所有交货日期为空的订单。
SELECT * FROM 订单信息 WHERE 交货日期 IS NULL;
由“订单信息”表中找出订货量在10和20之间的订单信息。
SELECT * FROM 订单信息 WHERE 数量 BETWEEN 10 AND 20;
查询货品信息，要求检索结果按照销售价格的升序排列。
SELECT * FROM 货品信息 ORDER BY 售价 ASC;
求货品信息表中，所有货品的总售价和平均售价。
SELECT SUM(售价) AS 总售价, AVG(售价) AS 平均售价 FROM 货品信息;

15订单信息表中，找出最早订单和最晚订单的日期。

SELECT MIN(订货日期) AS 最早订单日期, MAX(订货日期) AS 最晚订单日期 FROM 订单信息;
货品信息表中，找出有售价的货品个数、有成本价的货品个数。
SELECT COUNT(CASE WHEN 售价 IS NOT NULL THEN 1 END) AS 有售价的货品个数, COUNT(CASE WHEN 成本价 IS NOT NULL THEN 1 END) AS 有成本价的货品个数 FROM 货品信息;
对订单信息表，按照货品编码进行分组统计，即求出每种货品的销售数量，统计的结果按照货品编码进行排序。
SELECT 货品编号, COUNT(*) AS 销售数量 FROM 订单信息 GROUP BY 货品编号 ORDER BY 货品编号;
求出2008年以来每种货品的销售数量，统计的结果按照货品编码进行排序。
USE marketing

SELECT 货品编号,SUM(数量) AS 销售数量

FROM 订单信息

WHERE 订货日期 >= '2008-01-01'

GROUP BY 货品编号

ORDER BY 货品编号;
求出2008年以来，有两个订单以上货品的每种货品的销售数量，统计的结果按照货品编码进行排序。
SELECT 货品编号, SUM(数量) AS 销售数量

FROM 订单信息

WHERE 订货日期 >= '2008-01-01'

GROUP BY 货品编号

HAVING COUNT(货品编号) >= 2

ORDER BY 货品编号;
求出2008年以来，每种货品的销售数量，统计的结果按照货品编码进行排序，并显示统计的明细。
SELECT 货品编号, SUM(数量) AS 销售数量

FROM 订单信息

WHERE 订货日期 >= '2008-01-01'

GROUP BY 货品编号

ORDER BY 货品编号;

21. 生成多表链接，需要使用“销售名单”和“销售业绩”表，并添加数据。

“销售名单”表及其内容由“销售人员”表产生，“销售业绩”表单独构成。

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 销售名单 (

    工号 INT PRIMARY KEY,

    部门号 INT,

    姓名 VARCHAR(50) NOT NULL,

    地址 VARCHAR(100),

    电话 INT UNIQUE  CHECK (len(电话)<=11),

    性别 VARCHAR(2)

);

INSERT INTO 销售名单(工号, 部门号, 姓名, 地址, 电话, 性别)

VALUES (1, 1, '李求一', '北京市阳区', '25152454', '男'),

       (2, 2, '王巧敏', '北京市海淀区', '25345656', '女'),

       (3, 3, '张零七', '深圳市南山区', '25152342', '男'),

       (4, 2, '钱守空', '深圳市罗湖区', '27352659', '男'),

       (5, 3, '周运', '北京市魏公村', '83695245', '男'),

       (6, 4, '鹏迎夏', '北京市天坛', '81265987', '女');

USE Marketing; -- 切换到Marketing数据库

CREATE TABLE 销售业绩 (

    工号 INT PRIMARY KEY,

    部门号 INT,

    姓名 VARCHAR(50) NOT NULL,

    地址 VARCHAR(100),

    电话 INT UNIQUE  CHECK (len(电话)<=11),

    性别 VARCHAR(2),

    业绩 money

);

INSERT INTO 销售业绩 (工号,部门号,姓名,地址,电话 ,性别,业绩 )

SELECT 销售名单.*, SUM(o.总金额) AS performance

FROM 订单信息 o

JOIN 销售名单  ON o.销售工号 = 销售名单.工号

GROUP BY o.销售工号,销售名单.工号,销售名单.部门号,销售名单.姓名,销售名单.地址,销售名单.电话,销售名单.性别;
由“销售名单”表和“销售业绩”表，查询有名单并且登记了业绩的每个销售的销售业绩。
SELECT *

FROM 销售业绩

23. 列出所有销售的名单并对已有销售额的销售人员给出其销售额。

SELECT 销售人员.姓名, COALESCE(SUM(订单信息.总金额), 0) AS 销售额

FROM 销售人员

LEFT JOIN 订单信息 ON 销售人员.工号 = 订单信息.销售工号

GROUP BY 销售人员.姓名;
列出所有销售额并对有销售人员的给出其工号和姓名。
SELECT 销售人员.工号, 销售人员.姓名, COALESCE(SUM(订单信息.总金额), 0) AS 销售额

FROM 销售人员

LEFT JOIN 订单信息 ON 销售人员.工号 = 订单信息.销售工号

GROUP BY 销售人员.工号, 销售人员.姓名

HAVING COALESCE(SUM(订单信息.总金额), 0) != 0;
列出所有销售人员名单和所有的销售额，销售额与销售人员能对应的则对应给出，无对应的则在对应的列值填充NULL。
SELECT 销售人员.工号, 销售人员.姓名, CASE WHEN COALESCE(SUM(订单信息.总金额), 0) = 0 THEN NULL ELSE COALESCE(SUM(订单信息.总金额), 0) END AS 销售额

FROM 销售人员

LEFT JOIN 订单信息 ON 销售人员.工号 = 订单信息.销售工号

GROUP BY 销售人员.工号, 销售人员.姓名;
给出“货品信息”表中货品的销售情况，所谓销售情况就是给出每个货品的销售数量、订货日期或没有销售。可见对于没有销售的货品也要列出。
SELECT 货品信息.编码, 货品信息.名称, COALESCE(SUM(订单信息.数量), 0) AS 销售数量

FROM 货品信息

LEFT JOIN 订单信息 ON 货品信息.编码 = 订单信息.货品编号

GROUP BY 货品信息.编码, 货品信息.名称;

27. 由订单信息表中，找出最早订单和最晚订单，并按早晚进行排序。

USE marketing

SELECT *

FROM 订单信息

WHERE 订货日期 = (SELECT MIN(订货日期) FROM 订单信息)

UNION ALL

SELECT *

FROM 订单信息

WHERE 订货日期 = (SELECT MAX(订货日期) FROM 订单信息)

ORDER BY 订货日期 ASC;

28. 找出订货数量大于10的货品信息。

SELECT * FROM 订单信息 WHERE 数量>10;
找出有销售业绩的销售人员信息。
USE marketing

SELECT DISTINCT 销售人员.*

FROM 销售人员 JOIN 订单信息 ON 订单信息.销售工号=销售人员.工号

WHERE 订单信息.数量>0
查询每种货品订货量大于最小一次订货量的订单信息。
SELECT 货品信息.名称, 订单信息.订单号, 订单信息.数量

FROM 货品信息

JOIN 订单信息 ON 货品信息.编码 = 订单信息.货品编号

WHERE 订单信息.数量 > (

    SELECT MIN(数量)

    FROM 订单信息

);

查询每种货品订货量最大的订单信息。

SELECT 货品信息.名称, 订单信息.订单号, MAX(订单信息.数量) as 最大订货量

FROM 货品信息

JOIN 订单信息 ON 货品信息.编码= 订单信息.货品编号

GROUP BY 货品信息.名称,订单信息.订单号;

由“订单信息”表中找出总订货金额大于5000元的客户编号和客户姓名及电话。

SELECT 客户信息.*

FROM 客户信息 JOIN 订单信息 ON  客户信息.编号=订单信息.客户编号

GROUP BY 客户信息.编号 ,客户信息.姓名,客户信息.地址,客户信息.电话

HAVING SUM(订单信息.总金额) > 5000;

使用带子查询的查询语句，查找“货品信息”表，给出货品价格大于货品的平均价格的货品信息。SELECT 编码,名称, 售价

FROM 货品信息

WHERE 售价 > (SELECT AVG(售价) FROM 货品信息);

修改“货品信息”表中的数据，将所有价格超过2000元的货品价格降低10%。

UPDATE 货品信息

SET 售价 = 售价 * 0.9

WHERE 售价 > 2000;

（五）变量使用及流控语句

1．给出服务器当前的系统日期和时间，给出系统当前的月份和月份名字。

SELECT

    GETDATE() AS 系统日期和时间,

    MONTH(GETDATE()) AS 当前月份,

    DATENAME(MONTH, GETDATE()) AS 月份名称;
由“订单信息”表和“销售人员”表，给出每个销售人员的订单个数的统计。
SELECT

    o.姓名 AS 姓名,

    COUNT(s.数量) AS 订单个数

FROM

    订单信息 s

JOIN

    销售人员 o ON s.销售工号 = o.工号

GROUP BY

o.姓名;
使用CASE语句，查询客户信息，根据地址中的城市名确定其所属城市。
SELECT

    姓名,

    地址,

    CASE

        WHEN 地址 LIKE '%北京%' THEN '北京'

        WHEN 地址 LIKE '%上海%' THEN '上海'

        WHEN 地址 LIKE '%深圳%' THEN '深圳'

        ELSE '其他城市'

    END AS 所属城市

FROM

    客户信息;
由“订单信息”表和“销售人员”表，给出每个销售人员的订单个数的统计。
SELECT

    o.姓名 AS 姓名,

    COUNT(s.数量) AS 订单个数

FROM

    订单信息 s

JOIN

    销售人员 o ON s.销售工号 = o.工号

GROUP BY

o.姓名;
在marketing数据库中，创建一个多语句表值函数，它可以查询指定部门每个销售人员的订单数，该函数接收输入的部门号，通过查询“订单信息”表返回销售的订单数。
CREATE FUNCTION dbo.GetOrderCountByDepartment (@department_id INT)

RETURNS @result TABLE (

    salesperson_id INT,

    order_count INT

)

AS

BEGIN

    INSERT INTO @result (salesperson_id, order_count)

    SELECT

        S.部门号,



        COUNT(O.数量) AS order_count

    FROM

        销售人员 S

  join 部门信息 D  ON S.部门号 =D.编号

  JOIN 订单信息 O ON O.销售工号=s.工号

    WHERE

        编号 = @department_id

    GROUP BY

        S.部门号

    RETURN;

END;

（六）存储过程

1．建立一个名为“simpleorder”的存储过程，该存储过程返回客户的订购信息。

CREATE PROCEDURE simpleorder

AS

BEGIN

    SELECT

        订单号,

        c.姓名,

        P.名称,

        o.数量

    FROM

        订单信息 o

JOIN

    客户信息 c ON o.客户编号=c.编号

JOIN 货品信息 P ON P.编码=o.货品编号



END;
建立一个批处理，它能根据指定的表名关键字显示相应的表信息。
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION

REM 检查参数数量

IF "%~1"=="" OR "%~2"=="" (

    ECHO 请提供表名关键字和字段关键字作为参数。

    ECHO 用法: %0 表名关键字字段关键字

    EXIT /B 1

)

REM 设置表名关键字和字段关键字变量

SET "TABLE_KEYWORD=%~1"

SET "FIELD_KEYWORD=%~2"

REM 设置SQLCMD的其他参数

SET "SERVER=."

SET "DATABASE=marketing"

SET "USERNAME=ROOT"

SET "PASSWORD=ROOT"

REM 使用SQLCMD执行查询

sqlcmd -S %SERVER% -d %DATABASE% -U %USERNAME% -P %PASSWORD% -Q

"

USE [%marketing%];

SET NOCOUNT ON;

DECLARE @Query NVARCHAR(MAX);

SELECT @Query = STRING_AGG(CAST('SELECT * FROM ' + QUOTENAME(t.name) +

    ' WHERE ' + QUOTENAME(c.name) + ' LIKE ''%%%FIELD_KEYWORD%%%'' FOR XML PATH('''')), ' UNION ALL ')

FROM sys.tables t

INNER JOIN sys.columns c ON t.object_id = c.object_id

WHERE t.name LIKE '%%%TABLE_KEYWORD%%%' AND c.name LIKE '%%%FIELD_KEYWORD%%%';

EXEC sp_executesql @Query;

"

ENDLOCAL

3. 建立一个名为“proc_货品”的存储过程，带有一个参数接收指定的货品名称，在“货品信息表”、“供应商表”中查询该产品的供货商信息。

CREATE PROCEDURE proc_货品

    @product_name NVARCHAR(50) -- 输入参数，表示要查询的货品名称

AS

BEGIN

    SELECT

        G.*,

        P.名称

    FROM

        货品信息 P

    JOIN

        供应商信息 G ON P.供应商编码 = G.编码

    WHERE

        P.名称 = @product_name;

END;

4. 建立有一个输入参数并返回两个输出参数的存储过程“proc_货品1”，根据“货品表”统计公司某一类货品的库存总数量和成本。

CREATE PROCEDURE proc_货品

    @category VARCHAR(50), -- 输入参数，表示要统计的货品类别

    @total_quantity INT OUTPUT, -- 输出参数，表示库存总数量

    @total_cost DECIMAL(10, 2) OUTPUT -- 输出参数，表示成本总金额

AS

BEGIN

    SELECT

        @total_quantity = SUM(库存量),

        @total_cost = SUM(成本价)

    FROM

        货品信息

    WHERE

        名称 LIKE '%@category%';

END;