Sql Server 中常用语句

1.创建用户数据库

--创建数据库
use master --切换到master数据库
go

-- 终止所有与SaleManagerDB数据库的连接
alter database SaleManagerDB set single_user with rollback immediate
go

if exists (select * from sysdatabases where name='SaleManagerDB') 
drop database SaleManagerDB
go

create database SaleManagerDB
on primary
(
    name='SaleManagerDB_data',
    filename='D:\DB\SaleManagerDB_data.mdf',
    size=10MB,
    filegrowth=1MB
)
log on
(
    name='SaleManagerDB_log',
    filename='D:\DB\SaleManagerDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go

2.在数据库中创建表

use SaleManagerDB
go
--商品分类表
if exists (select * from sysobjects where name='ProductCategory')
drop table ProductCategory
go
create table ProductCategory
(
	CategoryId int identity(1,1) primary key ,--商品分类编号
	CategoryName varchar(20) not null--商品分类名称
)
go
--商品计量单位表
if exists (select * from sysobjects where name='ProductUnit')
drop table ProductUnit
go
create table ProductUnit
(
	Id int identity(1,1) primary key ,
	Unit varchar(20) not null--商品计量单位
)
go
--商品信息表
if exists (select * from sysobjects where name='Products')
drop table Products
go
create table Products
(
	ProductId varchar(50) primary key,--商品编号（商品条码）
	ProductName varchar(50) not null, 
	UnitPrice numeric(5,2) not null,
	Unit varchar(50) not null,--计量单位（为了提高效率，该字段并没有使用外键）
	Discount int,--折扣
	CategoryId int  references ProductCategory (CategoryId) not null --（商品分类）外键
)
go
--商品库存状态
if exists (select * from sysobjects where name='InventoryStatus')
drop table InventoryStatus
go
create table InventoryStatus
(	
    StatusId int primary key,--库存状态
    StatusDesc varchar(50) not null--（1：正常，-1：低于库存，2：高于库存；-2：已清仓）
)
go
--商品库存信息
if exists (select * from sysobjects where name='ProductInventory')
drop table ProductInventory
go
create table ProductInventory
(
	ProductId varchar(50) primary key,--商品编号
    TotalCount int not null,--总数量
    MinCount int not null,--最小库存
    MaxCount int not null,--最大库存
    StatusId int references InventoryStatus (StatusId) --库存状态（1：正常，-1：低于库存，2：高于库存；-2：已清仓）
)
go
--销售员表
if exists (select * from sysobjects where name='SalesPerson')
drop table SalesPerson
go
create table SalesPerson
(
	SalesPersonId int identity(10000,1) primary key,-- 自动标识
	SPName varchar(50) not null,
	LoginPwd varchar(50)  not null --最少6位  
)
go
--销售流水账
if exists (select * from sysobjects where name='SalesList')
drop table SalesList
go
create table SalesList
(  
	SerialNum varchar(50) primary key not null, --流水号(系统自动生成)
	TotalMoney numeric(10,2) not null,--购物总价钱
	RealReceive numeric(10,2) not null,--实际收款
	ReturnMoney  numeric(10,2) not null,--找零
	SalesPersonId int references SalesPerson (SalesPersonId), --销售员（外键）
	SaleDate smalldatetime  default(getdate()) not null --默认数据库服务器时间
)
go
--销售流水账明细
if exists (select * from sysobjects where name='SalesListDetail')
drop table SalesListDetail
go
create table SalesListDetail
(
    Id int identity(1000000,1) primary key not  null,--自动标识列
    SerialNum varchar(50) references SalesList (SerialNum), --流水号（外键）
	ProductId varchar(50) not null, --商品编号（不需要外键）
	ProductName varchar(50) not null,
	UnitPrice numeric(10,2) not null,
	Discount int,--折扣
	Quantity int not null,--销售数量	
    SubTotalMoney numeric(10,2)--小计金额
)
go
--商品入库表
if exists (select * from sysobjects where name='ProductStorage')
drop table ProductStorage
go
create table ProductStorage
(
	StorageId int identity(100000,1) primary key,--标识列
	ProductId varchar(50) references Products (ProductId),--外键
	AddedCount int not null,--入库数量
	CurrentTime smalldatetime default(getdate())  not null --默认数据库服务器时间
)
go
--登录日志
if exists (select * from sysobjects where name='LoginLogs')
drop table LoginLogs
go
create table LoginLogs
(
    LogId int identity(1,1) primary key,
	LoginId  int not null,
	SPName varchar(50),--登录人员姓名
	ServerName varchar(100),--登录的服务器名称
	LoginTime datetime default(getdate()) not null, --默认数据库服务器时间
	ExitTime datetime --退出时间
)
go
--超市会员表
if exists (select * from sysobjects where name='SMMembers')
drop table SMMembers
go
create table SMMembers
(
	MemberId int identity(100200300,1) primary key,--会员卡号
	MemberName varchar(50) not null,--会员姓名	
	Points int default(0) not null,--会员积分（消费10元，获得1个积分）
	PhoneNumber varchar(200) not null,--联系电话
	MemberAddress text not null,--联系地址
	OpenTime datetime default(getdate()),--开户时间
	MemberStatus int default(1) not null--会员卡状态（1：正常使用；0：冻结；-1：注销）
)
go
--管理员表
if exists (select * from sysobjects where name='SysAdmins')
drop table SysAdmins
go
create table SysAdmins
(
	LoginId int identity(2000,1) primary key,--登录账号
	LoginPwd varchar(20),--登录密码
	AdminName varchar(20),--管理员姓名
	AdminStatus bit, --当前状态（1：启用；0：禁用）
	RoleId int --角色编号（1：超级管理员；2：一般管理员）
)
go

3.往表中插入数据

use SaleManagerDB
go
--管理员信息
insert into SysAdmins(LoginPwd,AdminName,AdminStatus,RoleId)
values('11223344','王永利',1,1)
insert into SysAdmins(LoginPwd,AdminName,AdminStatus,RoleId)
values('11223344','张红梅',1,2)
insert into SysAdmins(LoginPwd,AdminName,AdminStatus,RoleId)
values('11223344','刘丽娜',1,2)
insert into SysAdmins(LoginPwd,AdminName,AdminStatus,RoleId)
values('11223344','王惠惠',0,2)
--销售员信息
insert into  SalesPerson(SPName,LoginPwd) values('王丽丽','123456')
insert into  SalesPerson(SPName,LoginPwd) values('王小刚','123456')
insert into  SalesPerson(SPName,LoginPwd) values('王大力','123456')
--超市会员信息
insert into SMMembers(MemberName,Points,PhoneNumber,MemberAddress,OpenTime,MemberStatus)
values('王晓敏',default,'13590856789','天津南开区',default,default)
insert into SMMembers(MemberName,Points,PhoneNumber,MemberAddress,OpenTime,MemberStatus)
values('刘全明',default,'13590856788','天津河北区',default,default)
insert into SMMembers(MemberName,Points,PhoneNumber,MemberAddress,OpenTime,MemberStatus)
values('赵大力',default,'13590856785','天津红桥区',default,default)
insert into SMMembers(MemberName,Points,PhoneNumber,MemberAddress,OpenTime,MemberStatus)
values('王文才',default,'13590856782','天津东丽区',default,default)
insert into SMMembers(MemberName,Points,PhoneNumber,MemberAddress,OpenTime,MemberStatus)
values('李兆新',default,'13590856781','天津河西区',default,default)
--商品分类数据
insert into ProductCategory(CategoryName) values('饮料')--1
insert into ProductCategory(CategoryName) values('副食')--2
insert into ProductCategory(CategoryName) values('面食')--3
insert into ProductCategory(CategoryName) values('肉类')--4
insert into ProductCategory(CategoryName) values('米类')--5
insert into ProductCategory(CategoryName) values('酒类')--6
insert into ProductCategory(CategoryName) values('烟类')--7
insert into ProductCategory(CategoryName) values('文具')--8
insert into ProductCategory(CategoryName) values('玩具')--9
insert into ProductCategory(CategoryName) values('日用品')--10
--商品计量单位
insert into ProductUnit values('箱')
insert into ProductUnit values('瓶')
insert into ProductUnit values('盒')
insert into ProductUnit values('本')
insert into ProductUnit values('袋')
insert into ProductUnit values('只')
insert into ProductUnit values('条')
insert into ProductUnit values('桶')
insert into ProductUnit values('打')
insert into ProductUnit values('听')
insert into ProductUnit values('罐')
insert into ProductUnit values('张')
insert into ProductUnit values('块')
insert into ProductUnit values('床')
insert into ProductUnit values('把')
insert into ProductUnit values('台')
insert into ProductUnit values('个')
--商品信息
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003001','康师傅牛肉面',40.00,'箱',0,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003002','康师傅打卤面',35.00,'箱',0,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003003','康师傅三鲜面',38.00,'箱',0,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003004','统一牛肉面',36.00,'箱',8,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003005','统一酸菜面',42.00,'箱',9,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003006','雪花啤酒',60.50,'箱',0,6)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003007','燕京啤酒',60.00,'箱',0,6)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003008','可口可乐',6.80,'瓶',0,1)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003009','百事可乐',5.80,'瓶',0,1)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003010','统一鲜橙多',5.80,'瓶',0,1)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003011','茉莉花茶',3.50,'瓶',0,1)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003012','自制蛋糕',19.80,'盒',0,2)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003013','中型碳素笔',10.00,'盒',0,9)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003014','黑妹牙膏',6.80,'盒',0,10)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003015','东北大米',80.00,'袋',0,5)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003016','天津小站大米',100.00,'袋',0,5)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003017','利达面粉',68.50,'袋',0,3)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003018','大豆油',68.80,'桶',0,2)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003019','纯棉毛巾',8.80,'条',0,10)
insert into Products (ProductId,ProductName,UnitPrice,Unit,Discount,categoryId)
values('6005004003020','金龙鱼食用油',55.80,'桶',9,2)
--商品库存状态
insert into InventoryStatus(StatusId,StatusDesc)values(1,'正常')
insert into InventoryStatus(StatusId,StatusDesc)values(-1,'低于库存')
insert into InventoryStatus(StatusId,StatusDesc)values(2,'高于库存')
insert into InventoryStatus(StatusId,StatusDesc)values(-2,'已清仓')
--商品库存数据
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003001',190,200,500,1)--方便面
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003002',350,200,500,1)--方便面
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003003',230,200,500,1)--方便面
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003004',300,200,400,1)--方便面
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003005',190,100,300,1)--方便面
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003006',1000,200,500,1)--啤酒
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003007',1000,200,300,1)--啤酒
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003008',180,200,300,1)--饮料
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003009',210,200,300,1)--饮料
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003010',150,100,200,1)--饮料
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003011',150,100,200,1)--饮料
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003012',200,100,150,1)--盒
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003013',80,100,150,1)--盒
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003014',50,100,150,1)--盒
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003015',180,100,200,1)--袋
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003016',160,100,200,1)--袋
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003017',1000,100,200,1)--袋
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003018',230,100,200,1)--桶
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003019',150,100,200,1)--条
insert into ProductInventory(ProductId,TotalCount,MinCount,MaxCount,StatusId)
values('6005004003020',120,100,200,1)--桶