增删改查

--1 切换到master数据库中
use master
go
--2 判断cpms数据库是否存在，若存在则删除,begin和end可以相当于c#中的大括号{}
if exists(select * from sys.sysdatabases where name='cpms')-- 存在ture不存在false
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  ,    --用户名
Pwd varchar(20) ,                    --密码
UserType nvarchar(5)  )                --用户类型
go 
--5 创建职员表
create table worker
(work_id nvarchar(6) not null  ,    --职员编号
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  ,    --供货商名称
Sup_Address nvarchar(24),            --供货商地址
Sup_Tel nvarchar(15) ,                --供货商联系电话
Supplier nvarchar(8))                --供货人名称
go
--8 创建货物表
create table Ware
(Ware_id nvarchar(4) not null  ,    --货号
Ware_name nvarchar(16),                --货名
Spec nvarchar(12),                    --规格
Unit nvarchar(2))                    --单位
go 
--9 创建库存表
create table Stock
(Stock_id int identity(1,1) not null,    --库存序列号
Ware_id nvarchar(4) ,                    --货号
Buy_Num smallint,                        --买入数量
Sale_Num smallint,                        --卖出数量
Stock_Num smallint )                    --库存数量
go
--10 创建销售表
create table Sell
(Sell_id int identity(1,1) not null,    --销售序列号
Ware_id nvarchar(4)  ,                    --货号
Sell_Price decimal(18,0),                --销售单价
Sell_Date smalldatetime ,                --销售日期
Sell_Num smallint,                        --销售数量
Work_Id nvarchar(6)  )                    --销售职工编号
go 
--11 创建进货表Restock表
create table restock
(res_id int identity(1,1)not null primary key,--进货序列号
ware_id nvarchar(4),--货号
res_price decimal(18,0) ,--进货单价
res_number smallint ,--进货数量
res_sate smalldatetime ,--进货日期
woek_id nvarchar ,--进货人
sup_name nvarchar(20),--供货商名称
) 
go
--12 添加user表数据，一次添加一行记录
insert into users (UserName,Pwd,UserType) values('admin','admin','管理员')
insert into users values ('guest','12345','管理员')
insert into users values('赵本山','12345','普通用户')
insert into users(UserName,UserType) values('abc','普通用户')
go
----insert into 表名，添加多行记录
----select 值列表 union 添加第一行
----select 值列表 union 添加第二行
----select 值列表 添加最后一行，select后面不加union表示最后一行
insert into users(UserName,Pwd,UserType)
select 'abcd','admin','管理员' union
select 'defg','12345','管理员' union
select 'zhao','12345','普通用户'
go
insert into worker(work_id,Work_name,sex,Birth,telephone,Address,Position)--数量和值还有顺序一样类型可以省略不写
select '9601','刘伟','0','1978/12/14','020-555666333','大庆路456号','副经理' union
select '9701','羊向天','1','1975/6/6','010-56987857','紫阳路56号','经理' union
select '9702','王文彬','1','1978/5/22','010-56987858','紫阳路47号','副经理' 
go
select * from worker--星号两边空格
---一、数据的插入和维护-----------------
--1 采用Insert语句完成USERS表数据的插入
--2 在users表中添加一个新用户，用户名abc，密码为空，用户类型为普通用户
--3 采用insert...select语句完成对worker表数据的插入
--4 根据客户要求，将admin用户的密码修改为‘12345’。
update users
set Pwd='12345'--修改的值得字段=新值
where UserName='admin'--查找修改的记录行
go
--要求：将guest用户的用户类型修改为管理员
update users
set UserType='管理员'
where UserType='guest'
go
select * from users
--5 将所有人的密码均改为‘12345’
update users
set Pwd='123'
go--没有where的时候将修改所有的记录！！！
--6 将worker表中羊向天的职位改为“总经理”，出生日期改为‘1974-3-6'.同时修改多个值
update worker
set Position='总经理',Birth='1974-3-6'--（1900-2073年smalldatetime）
where Work_name='羊向天'
go
select * from worker
--7 将abc用户密码改为‘abcde’，用户类型改为管理员
update users
set Pwd='abcde',UserType='管理员'
where UserName='abc'
go
select * from users
--8 删除用户名为’赵本山’的记录信息
delete from users
where UserName='赵本山'--省略则删库，删除的条件
go
select * from users
--9 删除在1980年以前出生的所有职员信息。
--条件中的关系运算符>大于  <小于  >=大于等于  <=小于等于  =等于  <>不等于
delete from worker
where Birth<'1980-1-1'--或者是where year(birth)<1980用年份函数
--如month（取月份）
--day（取天）
go
select * from worker
--练习-----------------------------
--10 在users表中插入一行记录，要求：用户名为自己姓名的拼音，密码自定，用户类型管理员
insert into users(UserName,Pwd,UserType)values('汪敏','123','管理员')

--11 将users表中用户名为姓名拼音的用户密码改为’123’，类型为“普通用户”。
update users set Pwd=123,UserType='普通用户'
where UserName='wangmin'
--12 删除用户名为姓名拼音的用户记录信息。
delete from users where UserName='wangmin'
select * from users
------------------------------------------------------------------------------
--二、数据表的管理------------

--1 打开新的查询窗口，将cpms数据中的users表重命名为users1；sp_rename 旧表名，新表名
--sp_rename users,users1
--go
----2 删除该表；drop table表名
--drop table users1
--3 完成后，注释上述两条代码,重新执行一遍数据库创建代码。

---三、约束的操作-------------------------------------------------------------
--1 为数据库CPMS中表Worker的SEX字段创建一个缺省约束，缺省值为0（男） 。
--2 为Restock(进货表)中的进货日期设置默认值为系统当前的日期 
--3 设置默认Users表中的用户类型默认为“普通用户”
--4 为Restock(进货表)中的进货数量（Res_number）设置必须>=1 。
--5 设置职员的出生日期范围在1950年1月1日至2020年12月31日。
--6 为users表的username字段设置主键约束。
--7 找到书34-41页，完成7个表的主键及外键约束的创建。
--8 设置供货商的联系电话不允许产生重复值。




--增删改查
--增
--1一次添加一条记录，一般用于编程，添加少量记录
--insert into 表名（字段名列表）values（值列表）
--一次添加多条记录
--insert into表名（字段名列表）
--select 第n行记录union
--select最后一行记录
--删
--delete from表名
--where 删除的条件
--省略where则删表
--改
--update表名
--set
--where条件