2020-10-31

–1.创建一个数据库sale
–主数据文件容量为4MB，事务日志文件容量为10MB，
–数据文件和日志文件的最大容量为20MB，文件增量为1MB
create database saleon
(name = sale,
filename = ‘C:\Users\admin\Desktop\sale.mdf’,
size = 4,
maxsize = 20,
filegrowth = 1)
log on
(name = sale_log,
filename = ‘C:\Users\admin\Desktop\sale_log.ldf’,
size = 10,
maxsize = 20,
filegrowth = 1)
–2.使用数据库
use sale
–创建客户信息表customer
create table customer
(cusno nvarchar(20) not null,
cusname nvarchar(10) not null,
cusaddress nvarchar(30) null,
cusphone int null)
–创建入库信息entry
create table entry(eno nvarchar(20) not null,
inputdata datetime not null,
amount decimal(6,0) not null --数量)

–创建一个产品表product
create table product
(pno nvarchar(20) not null,
pname nvarchar(20) not null,
pprice decimal(8,2) not null,
–价格pstocks decimal(8,0) not null --库存量)
–创建customer表中的数据内容insert into customer
select 1001,‘杨洋’,‘北京’,0233-11119999 union
select 1002,‘拟好’,‘深圳’,0233-11118888 union
select 1003,‘张三’,‘河南’,0233-22221111 union
select 1004,‘李四’,‘湖南’,0233-33338888 union
select 1004,‘李四’,‘湖南’,0233-33338888 union
select 1005,‘小明’,‘河北’,0233-77772222
–查询customer表中所有的数据
select all*from customer
–过滤掉重复的数据
select distinct *from customer
–获取前3行的数据
select top 3 *from customer
–获取前百分30的百分比
select top 30 percent *from customer
–修改列中的内容update customer set cusname = ‘古斯’ where cusname = ‘张三’
–创建entry表中的数据内容insert into entry
select 1001-01-01,00001,10
select 1001-01-02,00002,7 union
select 1001-01-03,00001,5 union
select 1001-01-04,00001,9 union
select 1001-02-01,00003,30 union
select 1001-02-02,00004,30 union
select 1001-02-02,00005,10 union
select 1001-02-02,00007,40
–创建并完善product表中的信息insert into product
select 00001,‘电视’,3000.00,800 union
select 00002,‘洗衣机’,5000.00,800 union
select 00003,‘床’,700.00,900 union
select 00004,‘空调’,9000.00,500 union
select 00005,‘沙发’,3500.00,600 union
select 00006,‘餐桌’,300.00,800
–查询product表
select pno as 物品号,pname as 物品,pprice as 物品价格,pstocks as 库存货量 from product