SQL数据库的创建实例

create table asp
(
学号 varchar(8),
姓名 varchar(10),
性别 varchar(2),
班级 varchar(10)
)

sp_help asp

alter table asp
add qq varchar(10)null
go

alter table asp
drop column qq

alter table asp
alter column qq varchar(10)not null

insert into asp
values('01','党费','男','软件091','111111')

update asp
set 姓名='党飞'
where 学号='01'

delete asp

create database Sales

alter database Sales
add file
(
name=Sales_dat,
filename='d:\data\Sales_dat2.ndf',
size=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB)

ALTER DATABASE Sales
ADD LOG FILE
(NAME=SalesLog2,
FILENAME='D:\data\Sales_log2.ldf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB)

ALTER DATABASE Sales
ADD FILEGROUP Sales_Group1
GO
ALTER DATABASE Sales
ADD FILE
(NAME=SalesG1F1_dat,
FILENAME='D:\data\SalesG1F1_dat.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB),
(NAME=SalesG1F2_dat,
FILENAME='D:\data\SalesG1F2_dat.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB)
TO FILEGROUP Sales_Group1
GO

ALTER DATABASE Sales
MODIFY FILEGROUP Sales_Group1 DEFAULT
GO

sp_help Sales

ALTER DATABASE Sales
REMOVE FILE SalesG1F2_dat

ALTER DATABASE Sales
MODIFY FILE
(NAME=SalesG1F1_dat,
SIZE=20MB)

USE Sales
CREATE TABLE employee
(
employee_id char(4)not null,
employee_name char(8)not null,
sex char(2)not null,
birth_date datetime not null,
hire_date datetime not null,
address varchar(50)not null,
telphone char(8),
wages money,
department_id char(4)not null,
resume text not null
)

DROP TABLE pubs.dbo.asp

CREATE TABLE Sales.dbo.supplier
(
supplier_id char(5)not null,
supplier_name varchar(50)not null,
linkman_name varchar(10),
address varchar(50),
telephone char(12) not null
)ON [PRIMARY]


CREATE TABLE salarys
(姓名 varchar(10),
基本工资 money,
奖金 money,
总计 AS 基本工资+奖金)

CREATE TABLE autouser
(编号 int identity(1,1)not null,
用户代码 varchar(18),
登陆时间 AS Getdate(),
用户名 AS User_name()
)

ALTER TABLE employee
ADD email varchar(20)null
GO

ALTER TABLE employee
DROP COLUMN email
go

USE Sales
INSERT supplier
values('S001','华中电子有限公司','施宾彬','朝阳路56号','2636565')

INSERT Sales.dbo.supplier
(supplier_id,supplier_name,linkman_name,address,telephone)
values('S001','华科电子有限公司','施宾彬','朝阳路56号','2636565')

INSERT Sales.dbo.supplier
(supplier_name,telephone,supplier_id)
values('韦力电子实业公司','4561681','S003')

INSERT Sales.dbo.supplier
(supplier_id,supplier_name,telephone)
values('S003','韦力电子实业公司''4161681')

CREATE TABLE customer2
(
customer_id bigint not null
identity(0,1),
customer_name varchar(50)not null,
linkman_name char(10),
address varchar(50),
telephone char(12)not null)
go

INSERT customer2
values('东方体育用品公司','刘平','东方市中山路25号','7536802')
insert customer2
(customer_name,linkman_name,address,telepho

ne)
values('北京泛亚实业公司','张伟民','长岭市五一路785号','6850231')
set identity_insert Sales.dbo.customer2 on
insert customer2
(customer_id,customer_name,linkman_name,address,telephone)
values('-100','洞庭强化电器公司','马东','滨海市洞庭大道278号','7605333')


select count(*)as 总数 FROM employee

select AVG(wages)AS 平均收入 FROM employee

select * from employee


create table Goods
(Goods_ID char(6),
Goods_Name char(50),
Classification_ID char(6),
Unit_Price money,
Stock_Quantity float,
Order_Quantity float)

create table Transporter
(Transporter_ID char(6),
Transporter_Name varchar(50),
Linkman_Name char(8),
Address varchar(50),
telephone char(12))


select * from salarys

select * from Department

CREATE TABLE Department
(Department_ID char(4),
Department_Name char(8),
Manager char(8),
Depart_Description varchar(50)
)


CREATE TABLE Sales.dbo.NewCustomer
(CustomerName varchar(50)NOT NULL,
LinkmanName char(8)
)


INSERT NewCustomer
SELECT customer_name,linkman_name
FROM Customer2

CREATE PROCEDURE MySp_Customer
AS
SELECT customer_name,linkman_name FROM Customer2
GO
INSERT NewCustomer
EXECUTE MySp_Customer

INSERT NewCustomer
EXECUTE
('SELECT customer_name,linkman_name FROM Customer2')

UPDATE Customer2
SET linkman_name='佚名',address=NULL,telephone=''

UPDATE Salarys
SET 奖金=奖金*2

UPDATE Customer2
SET telephone='0731-'+telephone
WHERE LEN(telephone)=7

CREATE TABLE Sales.dbo.Sell_Order
(Order_ID1 char(6),
Goods_ID char(6),
Employee_ID char(4),
Customer_ID char(4),
Transporter_ID char(4),
Order_Num float,
Discount float,
Order_Data Datetime,
Send_Date Datetime,
Arrival_Date Datetime,
Cost Money)

UPDATE Sell_Order
SET cost=
Sell_Order.Order_Num*Goods.Unit_Price*(1-Sell_Order.Discount)
FROM Sell_Order ,Goods
WHERE Sell_Order.Goods_id=Goods.Goods_id

UPDATE Goods
SET Unit_Price=Unit_Price*0.9
FROM Goods,
(SELECT TOP 10Goods_ID,SUM(Order_Num)AS Total_Num
FROM Sell_Order
GROUP BY Goods_ID
ORDER BY Total_Num DESC
)AS Total_Sum
WHERE Goods.Goods_id=Total_Sum.Goods_id