SQL Server命令示例
1.访问及判断数据库的存在
use master --访问主数据库
go
if exists(select * from sysdatabases where name='Fresh') --判断Fresh是否存在于系统数据库
drop database Fresh
go
2.创建数据库
create database Fresh
on primary
(
name='Fresh_date', --主文件逻辑名
Filename='D:\Fresh_date.mdf', --主文件操作系统文件名
size=10Mb, --主文件开始大小
Filegrowth=15% --主数据库增长速率
)
log on
(
name='Fresh_log', --日志文件逻辑名
Filename='D:\Fresh_log.ldf', --日志文件操作系统文件名
size=5Mb, --日志文件开始大小
maxsize=20Mb, --日志文件最大增长尺寸
Filegrowth=1Mb --日志文件增长速率
)
3.创建数据库表
use Fresh
go
create table Administrators --管理员表
(
userid int identity(1,1) not null primary key,
username varchar(20) unique not null,
password varchar(50) not null,
PWproblem varchar(100) not null,
answer varchar(100) not null,
type int default(1) not null
)
use Fresh
go
create table Customer --用户表
(
userid int identity(1,1) not null primary key,
username varchar(20) not null,
name varchar(20) not null,
password varchar(20) not null,
PWproblem varchar(100) not null,
answer varchar(100) not null,
sex varchar(5) not null,
age varchar(5) not null,
address varchar(100) not null,
telephone varchar(20) not null,
moneys money default(0) not null,
type int default(0) not null
)
use Fresh
go
create table Stock --库存表
(
commodityID int identity(1,1) not null primary key,
commodityName varchar(20) not null,
CDcategory varchar(20) not null,
CDlabel varchar(30) not null,
CDchart varchar(80) null,
CDintroduce varchar(100) not null,
Price decimal(12,2) not null
)
use Fresh
go
create table S_Details --库存详情表
(
CDnumber int identity(1,1) not null primary key,
commodityID int not null,
CDcode varchar(5) not null,
CDout varchar(20) not null,
CDstock varchar(10) not null,
constraint FK_commodityID foreign key (commodityID) references Stock (commodityID)
)
use Fresh
go
create table Orde --订单表
(
OrderID int identity(1,1) not null primary key,
CDnumber int not null,
commodityName varchar(20) not null,
userid int not null,
name varchar(20) not null,
address varchar(100) not null,
state varchar(10) default('未发货') not null,
constraint FK_CDnumber foreign key (CDnumber) references S_Details (CDnumber),
constraint FK_userid foreign key (userid) references Customer (userid)
)
use Fresh
go
create table Operation --操作表
(
operatID int identity(1,1) not null primary key,
userid int not null,
username varchar(20) not null,
OtTable varchar(20) not null,
BeoperatID int not null,
Beoperatname varchar(20) not null,
operation varchar(20) not null,
OtTime datetime default(getdate()) not null
constraint FK_Otuserid foreign key (userid) references Administrators (userid)
)
4.修改表信息
alter table Administrators add constraint UQ_username unique(username) --为Administrators（管理员表）的列username添加唯一约束
alter table Customer add constraint UQ_C_username unique(username) --为Customer（用户表）的列username添加唯一约束
5.向数据表插入数据
use Fresh
go
insert Administrators(username,password,PWproblem,answer) values --为Administrators表插入信息
('龙','123','所在学校?','湖南软件')
use Fresh
go
insert Customer(username,name,password,PWproblem,answer,sex,age,address,telephone) values --为Customer表插入信息
('zhangsan','张三','123','我叫什么?','张三','男','20','湖南软件职业学院','1008611')
--use Fresh
--go
--insert Customer(username,name,password,PWproblem,answer,sex,age,address,telephone) values('lisi','李四','123','我叫什么？','李四','男','18','湖南软件','1008611')
use Fresh
go
insert Stock(commodityName,CDcategory,CDlabel,CDchart,CDintroduce,Price) values --为Stock表插入信息
('金丝雨衣','衬衫','衣服','img/01.jpg','穿上之后彰显金钱魅力!',66.66)
use Fresh
go
insert S_Details(commodityID,CDcode,CDout,CDstock) values --为S_Details表插入信息
(1,'38','6','88')
use Fresh
go
insert Orde(CDnumber,commodityName,userid,name,address) values --为Orde表插入信息
(1,'金丝雨衣',1,'张三','湖南软件职业学院')
--use Fresh
--go
--insert Stock(commodityName,CDcategory,CDlabel,CDchart,CDintroduce,Price) values --插入商品
--('凉夏','休闲裤','裤子','img/1.png','魅力夏天~',66.00),
--('凉夏','休闲裤','裤子','img/Clothing/female/Casual_pants/1.png','魅力夏天~',66.00),
--('暖冬','羽绒服','衣服','img/Clothing/female/Down_Jackets/1.png','温暖冬天~',88.00),
--('霸颜','牛仔裤','裤子','img/Clothing/female/Jeans/1.png','霸气颜值~',66.00),
--('蓝调','衬衫','衣服','img/Clothing/female/shirt/01.png','简约蓝调~',66.00),
--('紫萱','裙子','衣服','img/Clothing/female/skirt/01.jpg','气质紫萱~',88.00),
--('绿透','T恤','衣服','img/Clothing/female/T_shirt/01.png','纯澈绿透~',66.00),--女装
--('迷彩工装裤','休闲裤','裤子','img/Clothing/male/Casual_pants/01.png','除了帅还是帅！',66.00),
--('破洞牛仔','牛仔裤','裤子','img/Clothing/male/Jeans/1.png','独特潮流！',66.00),
--('西服','西服','衣服','img/Clothing/male/Mans_suit/1.png','气质正装！',266.00),
--('灰色格调','衬衫','衣服','img/Clothing/male/shirt/1.png','文艺清新！',88.00),
--('黄白相伴','T恤','衣服','img/Clothing/male/T_shirt/1.png','相伴有你！',99.00),
--('西裤','西裤','裤子','img/Clothing/male/Western_style_trousers/1.png','气质正装！',166.00),--男装
--('灰原','男潮','衣服','img/Clothing/Trend/Male_fashion/1.png','小哀！小哀！',100.00),
--('女神','女潮','套装','img/Clothing/Trend/Trend_of_women/1.png','女神！女神！',199.00),--潮流
--('小呆萌','男童','衣服','img/Clothing/baby/Boys_wear/1.png','呆萌可爱~',77.00),
--('小紫萱','女童','衣服','img/Clothing/baby/Girls_wear/1.png','气质从小培养~',88.00) --童装
use Fresh
go
insert Stock(commodityName,CDcategory,CDlabel,CDchart,CDintroduce,Price) values --插入商品
('凉夏','休闲裤','女装','img/1.png','魅力夏天~',66.00)
--('凉夏','休闲裤','女装','img/Clothing/female/Casual_pants/1.png','魅力夏天~',66.00),
--('暖冬','羽绒服','女装','img/Clothing/female/Down_Jackets/1.png','温暖冬天~',88.00),
--('霸颜','牛仔裤','女装','img/Clothing/female/Jeans/1.png','霸气颜值~',66.00),
--('蓝调','衬衫','女装','img/Clothing/female/shirt/01.png','简约蓝调~',66.00),
--('紫萱','裙子','女装','img/Clothing/female/skirt/01.jpg','气质紫萱~',88.00),
--('绿透','T恤','女装','img/Clothing/female/T_shirt/01.png','纯澈绿透~',66.00),--女装
--('迷彩工装裤','休闲裤','男装','img/Clothing/male/Casual_pants/01.png','除了帅还是帅！',66.00),
--('破洞牛仔','牛仔裤','男装','img/Clothing/male/Jeans/1.png','独特潮流！',66.00),
--('西服','西服','男装','img/Clothing/male/Mans_suit/1.png','气质正装！',266.00),
--('灰色格调','衬衫','男装','img/Clothing/male/shirt/1.png','文艺清新！',88.00),
--('黄白相伴','T恤','男装','img/Clothing/male/T_shirt/1.png','相伴有你！',99.00),
--('西裤','西裤','男装','img/Clothing/male/Western_style_trousers/1.png','气质正装！',166.00),--男装
--('灰原','男潮','潮流','img/Clothing/Trend/Male_fashion/1.png','小哀！小哀！',100.00),
--('女神','女潮','潮流','img/Clothing/Trend/Trend_of_women/1.png','女神！女神！',199.00),--潮流
--('小呆萌','男童','童装','img/Clothing/baby/Boys_wear/1.png','呆萌可爱~',77.00),
--('小紫萱','女童','童装','img/Clothing/baby/Girls_wear/1.png','气质从小培养~',88.00) --童装
--('白雪','裙子','推荐','img/Clothing/female/skirt/01.png','洁如白雪~',88.00),
--('庄焰','T恤','推荐','img/Clothing/female/T_shirt/1.png','热情不失端庄~',88.00) --女推
--('瞧你吗','T恤','推荐','img/Clothing/male/T_shirt/2.png','是的，我在看你！',88.00),
--('韩风','衬衫','推荐','img/Clothing/male/shirt/2.png','潮流韩风',88.00) --男推
--('军旅','男潮','推荐','img/Clothing/Trend/Male_fashion/2.png','军人色，军人颜!',88.00),
--('粉色回忆','女潮','推荐','img/Clothing/Trend/Trend_of_women/2.png','回忆有你才甜~',88.00) --潮推
--('小白小绿','男童','推荐','img/Clothing/baby/Boys_wear/2.png','小帅萌！',88.00),
--('红白公主','女童','推荐','img/Clothing/baby/Girls_wear/2.png','小公主~',88.00) --童推
insert Stock(commodityName,CDcategory,CDlabel,CDchart,CDintroduce,Price) values --插入商品
('宠萌韵娘','套装','童装','img/Adorable/baby/萌推-童1.png','萌~',222.22),
('灰兔奈乃','套装','童装','img/Adorable/baby/萌推-童2.png','萌~',222.22), --童萌推
('淡忘轩笙','套装','女装','img/Adorable/female/萌推-女1.png','美~',222.22),
('白雪寻妮','套装','女装','img/Adorable/female/萌推-女2.png','美~',222.22), --女萌推
('寄情双笙','套装','男装','img/Adorable/male/萌推-男1.png','帅~',222.22),
('草帽小子','套装','男装','img/Adorable/male/萌推-男2.png','帅~',222.22), --男萌推
('仙气环绕','套装','潮流','img/Adorable/Trend/萌推-潮3.png','帅~',222.22),
('连帽卫衣','套装','潮流','img/Adorable/Trend/萌推-潮4.png','帅~',222.22) --潮萌推
6.查询示例
select * from Customer where userid='1' --查询语句1
7.删除示例
delete from Stock where commodityID=7
