--1 切换到master数据库中
use master
go
--2 判断cpms数据库是否存在，若存在则删除
if exists(select * from sys.sysdatabases where name='cpms')
drop database cpms
go
--3 创建cpms数据库
create database cpms
on
(name=cpms_data,
filename='c:\cpms\cpms_data.mdf')
log on
(name=cpms_log,
filename='c:\cpms\cpms_log.ldf')
go
--4 切换到cpms数据库中
use cpms
go
--5 创建用户表
create table users
(UserName NVARCHAR(20) not null primary key, --用户名
Pwd varchar(20) , --密码
UserType nvarchar(5) default('普通用户') ) --用户类型
go
--5 创建职员表
create table worker
(work_id nvarchar(6) not null primary key, --职员编号
Work_name nvarchar(8) not null, --职员姓名
sex bit not null, --性别
Birth smalldatetime , --出生日期
telephone nvarchar(15), --联系电话
Address nvarchar(50), --家庭住址
Position nvarchar(10)) --职位
go
--7 创建供货商表
create table Supplier
(Sup_name nvarchar(20) not null primary key, --供货商名称
Sup_Address nvarchar(24), --供货商地址
Sup_Tel nvarchar(15) , --供货商联系电话
Supplier nvarchar(8)) --供货人名称
go
--8 创建货物表
create table Ware
(Ware_id nvarchar(4) not null primary key, --货号
Ware_name nvarchar(16), --货名
Spec nvarchar(12), --规格
Unit nvarchar(2)) --单位
go
--9 创建库存表
create table Stock
(Stock_id int identity(1,1) not null primary key,--库存序列号
Ware_id nvarchar(4) references ware, --货号
Buy_Num smallint, --买入数量
Sale_Num smallint, --卖出数量
Stock_Num smallint ) --库存数量
go
--10 创建销售表
create table Sell
(Sell_id int identity(1,1) not null primary key,--销售序列号
Ware_id nvarchar(4) references ware, --货号
Sell_Price decimal(18,0), --销售单价
Sell_Date smalldatetime , --销售日期
Sell_Num smallint, --销售数量
Work_Id nvarchar(6) references worker) --销售职工编号
go
--11 创建进货表
create table Restock
(Res_Id int identity(1,1) not null primary key,--进货序列号
Ware_Id nvarchar(4) references ware, --货号
Res_Price decimal(18,0), --进货单价
Res_Number smallint, --进货数量
Res_Date smalldatetime, --进货日期
Work_id nvarchar(6) references worker, --进货人
Sup_Name nvarchar(20)references supplier)--供货商名称
go
--12 添加user表数据
insert into users(username,pwd,usertype)
select 'admin','admin','管理员'union
select'guest','123455', '普通用户'union
select'赵本山','123456','普通用户'
go
select * from users
--13 添加WORKER表数据
insert into Worker(work_id,Work_name,Sex,birth,telephone,address,position)
select '9601','刘伟','0','1978-12-14 0:00','020-555666333','大庆路456号','副经理' union
select '9701','羊向天','1','1975-6-6 0:00','010-56987857','紫阳路56号','经理' union
select '9702','王文彬','1','1978-5-22 0:00','010-56987858','紫阳路47号','业务员' union
select '9703','张梦露','1','1983-8-6 0:00','010-159458793','三环路106号','副经理' union
select '9704','罗兰','1','1984-4-5 0:00','010-23541123','紫阳路8号','业务员' union
select '9801','王泽方','0','1982-2-3 0:00','010-22365478','三环路98号','业务员' union
select '9802','易扬','0','1985-1-14 0:00','010-98654123','紫禁城98号','业务员' union
select '9803','兰利','0','1984-1-1 0:00','010-156984555','紫禁城97号','业务员' union
select '9821','张平','0','1986-5-4 0:00','010-158322331','紫禁城54号','业务员'
go
select * from worker
--14 添加供货商表数据
insert into Supplier(sup_name,sup_address,sup_tel,supplier)
select '华强电子公司','上海路45号','027123456789111','吴国立' union
select '京华电子公司','北京西路31号','027123456789666','刘为东' union
select '兰光电子公司','向阳大道888号','024123456789222','陈一红' union
select '赛格电子公司','南京紫光苍444号',null,'赵天晨' union
select '桑达电子公司','红星三路78号',null,'李三利'
go
select * from supplier
--15 添加货物表数据
insert into Ware(ware_id,Ware_name,spec,unit)
select'1001','cpu','intel p4 2.4','片' union
select'1002','cpu','intel p4 3.0','片' union
select'1003','cpu','intel c4 2.0','片' union
select'1006','cpu','奔腾 p4 845G','片' union
select'2101','主板','华硕 p4 B533','个' union
select'2102','主板','华硕 p4 B266','个' union
select'2104','主板','华硕 intel845G','个' union
select'3101','软驱','三星1.44M','个' union
select'3103','软驱','NEC','个' union
select'3104','软驱','SONY 1.44M','个' union
select'4201','光驱','三星52X','个' union
select'4203','光驱','SONY48X','个' union
select'4204','光驱','LG16X','个' union
select'4301','硬盘','希捷60G','个' union
select'4303','硬盘','IBM40G','个' union
select'5101','声卡','AC97','个' union
select'5103','声卡','创新PCI128','个' union
select'5104','声卡','集成AC97','个' union
select'5201','网卡','intel PCLA','个' union
select'6101','内存','HY128M','个' union
select'6201','内存','HY256M','个' union
select'7101','机箱','东方城211A','套' union
select'7103','机箱','银梭IV号','套' union
select'7201','音箱','漫步者R351T5.1','对' union
select'7202','音箱','漫步者R8NT','对' union
select'7301','鼠标','极光旋貂','个' union
select'7401','键盘','Acer 52TW','个' union
select'7402','键盘','多彩DL-K9810','个' union
select'7403','键盘','网际键盘','个' union
select'7501','优盘','朗科64M','只' union
select'7503','优盘','朗科32M','只'
go
select * from ware
--16 添加库存表数据
insert into Stock
select '1001','12','7','5' union
select '3104','6','0','6' union
select '4201','6','5','1' union
select '4203','3','1','2' union
select '4204','6','3','3' union
select '4301','7','4','3' union
select '4303','6','5','1' union
select '5101','4','2','2' union
select '5103','8','5','3' union
select '5104','5','4','1' union
select '5201','5','0','5' union
select '1002','9','2','7' union
select '6101','5','4','1' union
select '6201','10','9','1' union
select '7101','11','1','10' union
select '7103','7','5','2' union
select '7201','13','10','3' union
select '7202','2','1','1' union
select '7301','3','2','1' union
select '7401','8','7','1' union
select '7402','1','1','0' union
select '7403','4','3','1' union
select '1003','5','3','2' union
select '7501','20','15','5' union
select '7503','15','13','2' union
select '1006','9','4','5' union
select '2101','5','3','2' union
select '2102','10','3','7' union
select '2104','8','5','3' union
select '3101','16','6','10' union
select '3103','30','2', '28'
go
select * from Stock
--17 添加销售表数据
insert into Sell
select '1001','1550','2003-2-28 0:00','7','9702' union
select '1002','2040','2003-3-16 0:00','2','9801' union
select '1003','690','2003-3-28 0:00','3','9802' union
select '1006','730','2003-4-3 0:00','4','9703' union
select '2101','970','2003-4-19 0:00','3','9701' union
select '2102','780','2003-4-19 0:00','3','9702' union
select '3103','85','2003-4-28 0:00','2','9801' union
select '2104','1100','2003-7-19 0:00','5','9601' union
select '3101','100','2003-6-5 0:00','6','9704' union
select '4201','250','2003-6-5 0:00','5','9803' union
select '4203','780','2003-6-5 0:00','1','9821' union
select '4204','580','2003-6-8 0:00','3','9821' union
select '4301','800','2003-6-8 0:00','4','9703' union
select '4303','700','2003-6-28 0:00','5','9801' union
select '5101','420','2003-6-8 0:00','2','9801' union
select '5103','260','2003-7-28 0:00','5','9702' union
select '5104','300','2003-7-28 0:00','4','9601' union
select '6101','200','2003-7-28 0:00','4','9703' union
select '6201','300','2003-7-29 0:00','9','9801' union
select '7101','260','2003-7-29 0:00','1','9802' union
select '7103','450','2003-7-18 0:00','5','9601' union
select '7201','600','2003-7-18 0:00','10','9702' union
select '7202','150','2003-6-28 0:00','1','9704' union
select '7301','340','2003-8-28 0:00','2','9803' union
select '7401','120','2003-8-28 0:00','7','9703' union
select '7402','200','2003-8-28 0:00','1','9704' union
select '7403','180','2003-2-25 0:00','3','9601' union
select '7501','460','2003-8-28 0:00','15','9601' union
select '7503','240','2003-9-5 0:00','13','9703'
go
select * from sell
--18 添加进货表数据
insert into Restock
select '3101','70','8','2003-4-27 0:00','9702','华强电子公司' union
select '2101','890','5','2003-5-14 0:00','9801','华强电子公司' union
select '3103','70','13','2003-5-14 0:00','9801','华强电子公司' union
select '3104','70','2','2003-5-18 0:00','9801','华强电子公司' union
select '4201','200','6','2003-5-18 0:00','9801','华强电子公司' union
select '3101','70','8','2003-5-19 0:00','9801','华强电子公司' union
select '4203','699','3','2003-5-26 0:00','9701','华强电子公司' union
select '4204','399','6','2003-5-26 0:00','9701','华强电子公司' union
select '4301','695','7','2003-5-26 0:00','9702','华强电子公司' union
select '3103','70','17','2003-5-26 0:00','9702','华强电子公司' union
select '4303','625','3','2003-5-28 0:00','9801','京华电子公司' union
select '5101','360','4','2003-5-29 0:00','9801','京华电子公司' union
select '1001','1330','12','2003-3-29 0:00','9801','京华电子公司' union
select '1002','1950','9','2003-3-30 0:00','9701','京华电子公司' union
select '1003','650','5','2003-3-1 0:00','9801','京华电子公司' union
select '1006','700','9','2003-3-26 0:00','9702','京华电子公司' union
select '3104','70','6','2003-6-4 0:00','9701','京华电子公司' union
select '6101','135','5','2003-6-5 0:00','9701','京华电子公司' union
select '6201','225','10','2003-6-5 0:00','9701','京华电子公司' union
select '7101','180','2','2003-6-5 0:00','9801','兰光电子公司' union
select '4303','625','3','2003-6-12 0:00','9801','兰光电子公司' union
select '7103','320','2','2003-6-13 0:00','9801','兰光电子公司' union
select '7201','490','13','2003-6-16 0:00','9701','兰光电子公司' union
select '7202','100','2','2003-6-16 0:00','9701','兰光电子公司' union
select '5103','190','4','2003-7-20 0:00','9702','兰光电子公司' union
select '7301','220','3','2003-7-22 0:00','9801','兰光电子公司' union
select '5103','190','4','2003-7-20 0:00','9801','赛格电子公司' union
select '7103','320','5','2003-7-27 0:00','9801','赛格电子公司' union
select '7401','70','8','2003-7-27 0:00','9702','赛格电子公司' union
select '7402','120','1','2003-8-10 0:00','9702','赛格电子公司' union
select '7403','100','4','2003-8-16 0:00','9702','桑达电子公司' union
select '7501','300','20','2003-8-25 0:00','9701','桑达电子公司' union
select '7101','180','9','2003-8-28 0:00','9701','桑达电子公司' union
select '7503','150','15','2003-8-28 0:00','9702','桑达电子公司' union
select '2102','1000','10','2003-8-28 0:00','9702','赛格电子公司' union
select '2104','980','8','2003-6-12 0:00','9701','京华电子公司' union
select '5104','220','5','2003-6-12 0:00','9701','京华电子公司' union
select '5201','150','5','2003-6-5 0:00','9701','京华电子公司'
go
select * from restock
-------------------------------------------------------
--1、简单查询
--任务一：查询Worker表中的所有职员信息
select * from worker
--任务二：查询Worker表的职工编号、姓名及性别
select work_id,work_name,sex
from worker
--查询货号货名销售编号
select a.Ware_id,Ware_name,Sell_id
from Sell as a,ware as b--给表设置别名
go
--任务三：将任务二查询到的Worker表的职工编号、姓名及性别字段，用中文标题显示。
select work_id'职工编号',Work_name as '姓名','性别'=sex
from worker
--任务四：显示Sell表中的销售序列号、货号及销售单价，在结果中将销售单价在原有价格上+5元,字段名不变
select sell_id,Ware_id,Sell_price+5'sell_price'
from Sell
--1-1 查询所有货物信息；
select * from Ware
--1-2 查询所有的销售信息；
select * from Sell
--1-3 查询所有职工的姓名、性别及出生年月；
select work_id,sex,birth
from worker
go--两个表都有的字段要在select字段名中公共字段前加表名
--1-4 将第3题中查询到的Worker表的姓名、性别及出生年月，用中文标题显示；
--字段名‘别名’
--字段名 as ‘别名’
--‘别名’=字段名，，，以数字开头必须要加单引号
select work_name '姓名',sex as '性别','出生年月'=birth
from worker
--1-5 查询所有供货商的名称、地址及联系电话，并将字段标题，分别用“供货商名称”、“供货商地址”、“联系电话”表示；
select Sup_name'供货商名称',Sup_Address as '供货商地址','联系方式'=Sup_Tel
from Supplier
--1-6 显示Sell表中的销售序列号、货号及销售单价，在结果中将销售单价在原有价格上+10元，字段名不变；
select Sell_id,Ware_id,Sell_Price+10'sell_price'
from Sell
--1-7 显示Restock表中的进货序列号、货号、进货单价及进货数量，在查询结果中将进货单价在原有价格上减去8元，要求：字段标题均采用中文标题
select Res_Id'进货序列号',Ware_Id'货号',Res_Price-8'进货单价',Res_Number'进货数量'
from Restock
--1-8：在SELL（销售）表中完成如下查询：要求能显示所有信息，并在最后一列显示一新列名为“10月份销售数量”，列的内容=‘销售数量+10’
select * ,'10月份的销售数量'=Sell_Num+10
from sell
--查询sell表ware表所有信息
select c.*,d.*
from Sell as c,Ware as d
--1-9 检索职员表中“Work_id”字段的全部数据：
----select *(所有列) all（所有行不输入则默认所有行）top+5（数字是几就是前几条记录top+n percent表示前百分之几条记录） distinct 关键字检索部分字段
----from 表名
--任务五：查询Worker表中的前3条记录。
--1-10 查询Restock表中的前10条记录。
select top 10 * from Restock
--1-11 显示Sell表中的前10%条记录
select top 10 percent * from Sell
--1-12 查询Worker表中的后三条记录
select top 3 * from worker
order by work_id desc--默认情况下是主键由小到大升序asc，需要倒序查询的时候才输入description
--1-13 显示Sell表中销售单价最高的三条记录
select top 3 * from Sell
Order by Sell_Price desc
--1-14 查询Ware表前10%条记录的货号、货名及规格；
select top 10 percent Ware_id,Ware_name,Spec
from Ware
--任务六:查询当前用户类型有哪些种类
--select distinct字段列表from表名。表示：去掉重复记录
select distinct usertype from users
--1-15 在本公司货物表中货物的种类有哪些
select distinct ware_name from Ware
--1-16 查询进货表中，本公司所进货物来自于哪几个供货商；
select distinct Supplier from Supplier
--1-17 查询进货表中，本公司的货物主要由哪几个职员进货；
select distinct Work_id from Restock
-------------------------------------------------------------------
--2、条件查询
--2-1 在“Worker”表中检索性别为‘女’的职员记录：
--2-2 在Sell表中，查询销售价格大于1000元的货物销售信息
--2-3 在Sell表中，查询销售价格小于800元的货物销售信息
--2-4 在Sell表中，查询销售价格不大于1000元的货物销售信息。
--2-5 在Sell表中，查询销售价格在1000～2000元的货物销售信息。
--2-6 在Sell表中，查询销售价格不在1000～2000元之间的货物销售信息
--2-7 查询性别为男，且职位是业务员的职员信息
--2-8 查询库存数量在5以上的库存货物的库存序列号、货号及库存数量，要求：列标题用中文表示
--2-9 查询出生日期在1980年至1983年之间的所有女性职员信息
--2-10 已知供货人姓刘，查询其所在公司的信息。
--2-任务八：在Sell表中，查询销售价格在1000～2000元的货物销售信息
--2-11 在Sell表中，查询销售价格不在1000～2000元的货物销售信息
--2-12 查询出生日期在1980年至1983年之间的所有女性职员信息
--2-13 查询库存数量在3-5之间的所有库存信息
------------------------------------------------------- --1、简单查询 --任务一：查询Worker表中的所有职员信息 select * from worker --任务二：查询Worker表的职工编号、姓名及性别 select work_id,work_name,sex from worker --任务三：将任务二查询到的Worker表的职工编号、姓名及性别字段，用中文标题显示。 select work_id 职工编号,work_name as 职工姓名,性别=sex from worker --任务四：显示Sell表中的销售序列号、货号及销售单价，在结果中将销售单价在原有价格上+5元,字段名不变 select Sell_id ,Ware_id ,Sell_Price+5 Sell_Price from sell --1-1 查询所有货物信息； select * from ware --1-2 查询所有的销售信息； select * from sell --1-3 查询所有职工的姓名、性别及出生年月； select work_name,sex,birth from worker --1-4 将第3题中查询到的Worker表的姓名、性别及出生年月，用中文标题显示； select work_name 姓名,sex 性别,birth 出生年月 from worker --1-5 查询所有供货商的名称、地址及联系电话，并将字段标题，分别用“供货商名称”、“供货商地址”、“联系电话”表示； select sup_name 供货商名称,Sup_Address 供货商地址,Sup_Tel 联系电话 from supplier --1-6 显示Sell表中的销售序列号、货号及销售单价，在结果中将销售单价在原有价格上+10元，字段名不变； select sell_id,ware_id,sell_price+10 sell_price from sell --1-7 显示Restock表中的进货序列号、货号、进货单价及进货数量，在查询结果中将进货单价在原有价格上减去8元，要求：字段标题均采用中文标题 select Res_Id 进货序列号,Ware_Id 货号,Res_Price 进货单价,Res_Number-8 进货数量 from Restock --1-8：在SELL（销售）表中完成如下查询：要求能显示所有信息，并在最后一列显示一新列名为“10月份销售数量”，列的内容=‘销售数量+10’ select *,Sell_Num+10 '10月份销售数量' from sell --1-9 检索职员表中“Work_id”字段的全部数据： select all work_id from worker --任务五：查询Worker表中的前3条记录。 select top 3 * from worker --1-10 查询Restock表中的前10条记录。 select top 10 * from restock --1-11 显示Sell表中的前10%条记录 select top 10 percent * from Sell --1-12 查询Worker表中的后三条记录 select top 3 * from worker order by work_id desc --1-13 显示Sell表中销售单价最高的三条记录 select top 3 * from Sell order by Sell_Price desc --1-14 查询Ware表前10%条记录的货号、货名及规格； select top 10 percent ware_id,ware_name,spec from ware --任务六:查询当前用户类型有哪些种类 select distinct usertype from users --1-15 在本公司货物表中货物的种类有哪些 select distinct ware_name from Ware --1-16 查询进货表中，本公司所进货物来自于哪几个供货商； select distinct sup_name from restock --1-17 查询进货表中，本公司的货物主要由哪几个职员进货； select distinct work_id from restock
----------------------1.条件查询---------------------
--1-1 在“Worker”表中检索性别为‘女’的职员记录：
select * from worker where sex=1
--1-2 在Sell表中，查询销售价格大于1000元的货物销售信息
select * from Sell where Sell_Price>1000
--1-3 在Sell表中，查询销售价格不大于1000元的货物销售信息。
select * from Sell where Sell_Price !>1000
select * from Sell where Sell_Price <=1000
--1-4 在Sell表中，查询销售价格在1000～2000元的货物销售信息。
select * from Sell where Sell_Price >=1000 and Sell_Price <=2000
--1-5 在Sell表中，查询销售价格不在1000～2000元之间的货物销售信息
select * from Sell where not Sell_Price>=1000 and Sell_Price<=2000
select * from Sell where Sell_Price<1000 or Sell_Price>2000
--1-6 查询性别为男，且职位是业务员的职员信息
select * from worker where sex=0 and Position='业务员'
--1-7 查询库存数量在5以上的库存货物的库存序列号、货号及库存数量，要求：列标题用中文表示
select stock_id '库存序列号',ware_id '货号',stock_num '数量' from Stock where Stock_Num>5
-----------between关键字--------------------
--格式：select 字段列表 from 表名 where 字段 between 低值 and 高值
--1-8 查询出生日期在1980年至1983年之间的所有女性职员信息
select * from worker where sex=1 and Birth between '1980-1-1' and '1983-12-31'
--1-9 查询库存数量在3-5之间的所有库存信息
select * from Stock where Stock_Num between 3 and 5
-----------------2、in关键字-------------
--格式：select 字段列表 from 表名 where 字段 in (值列表)
--说明：in 表示字段值与值列表中的某个值匹配，值列表之间用,做分隔符
--2-1在Worker表中，查询职工编号为9601、9702的职工信息 。
select * from worker where work_id in ('9601','9702')
--2-2 在Worker表中，查询职工编号不是9601、9702的职工信息
select * from worker where work_id not in ('9601','9702')
---------------------3、Like关键字-------------------------
--格式：select 字段列表 from 表名 where 字段名 [not] like ''
--%:占多个位 _:占1个位 []:占1个位，可以是多个字符
--[^]:不在这些字符范围中
--3-1 从“WORKER”中查找姓名中第二个字是“文”的职员信息。
select * from worker where Work_name like '_文%'
--3-2 查询worker表中所有姓王的职员信息
select * from worker where Work_name like '王%'
--3-3 显示姓刘和姓王的职员信息
select * from worker where Work_name like '[刘王]%'
--3-4 显示职员编号末尾数字为1和3的职员信息
select * from worker where work_id like '%[13]'
--3-5 查询Worker表中姓王的且名字第三个字为彬的职工信息
select * from worker where Work_name like '王_彬%'
--3-6 查询职工名字中包含“向”或“兰”的职员信息
select * from worker where Work_name like '%[向兰]%'
--3-7 查询家庭住址在三环路的所有职员信息
select * from worker where Address like '%三环路%'
--3-8 查询规格中包含“三星”的所有货物信息
select * from ware where spec like '%三星%'
标签：Sell,00,2003,--,查询,union,select From： https://www.cnblogs.com/bky-wang/p/18124961