(
CustomerID int IDENTITY(1,1) PRIMARY KEY, CName varchar(8) NOT NULL, Address varchar(50), City varchar(10), Tel varchar(20) UNIQUE, Company varchar(50), Birthday datetime, Type tinyint DEFAULT 1 );
CREATE TABLE Goods (
GoodsID int CONSTRAINT C1 PRIMARY KEY, GoodsName varchar(20) NOT NULL, Price money,
Decription varchar(200), Storage int,
Provider varchar(50), Status tinyint DEFAULT(0) );
CREATE TABLE Orders (
OrderID int IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY,
GoodsID int NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE,
/*表级完整性约束条件，参照约束为Goods(GoodID)，删除时进行级联删除*/
CustomerID int NOT NULL FOREIGN KEY(CustomerID)
REFERENCES Customers(CustomerID) ON DELETE NO ACTION, /*表级完整性约束条件，参照约束为Customers(CustomerID)，删除时不删除参照表中的被引用行*/
Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity > 0), Ordersum money NOT NULL,
OrderData datetime DEFAULT(getdate()) );
/*在Customers表中已有数据的基础上向各个表中插入数据*/
INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday) VALUES('王伟','长安区','西安','029005','邮电大学','')
INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday) VALUES('惠敏','海淀区','北京','010001','中国农业大学','') INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday) VALUES('王二小','浦东新区','上海','021001','上海商学院','') INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday) VALUES('周颖','临潼区','西安','029006','西安科技大学','') INSERT INTO Customers(CName,Address,City,Tel,Company,Birthday) VALUES('吴起','槐荫区','山东','058436','山东师范','') /*向Goods表中插入数据*/
INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status) VALUES('0001','书包',45.00,'双肩包','20','舒适','1')
INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status) VALUES('0002','水杯',32.00,'塑料','322','特百惠','1')
INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status) VALUES('0003','上衣',100.22,'长款','18','春季适用','1')
INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status) VALUES('0004','风衣',128.22,'长款','28','秋季适用','1')
INSERT INTO Goods(GoodsID,GoodsName,Price,Decription,Storage,Provider,Status) VALUES('0005','短裤',56.88,'超短','50','夏季适用','1') /*向Orders表中插入数据，其中有错误数据的测试*/
INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData) VALUES('0003','1','4',100.22,'')
INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)
VALUES('0001','2','3',45.00,'')
INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData) VALUES('0002','5','10',32.00,'')
INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData) VALUES('0004','3','2',128.22,'')
INSERT INTO Orders(GoodsID,CustomerID,Quantity,Ordersum,OrderData)/*该语句错误，应为赋给OrderID的值没有参照表Orders*/ VALUES('0006','3','1',32.00,'') /*修改表中的数据*/
UPDATE Goods /*将Goods表中的所有价钱都增加10*/ SET Price=Price+10; UPDATE Orders SET Quantity=5
WHERE CustomerID=2;/*将Orders表中CustomerID为2的客户订单数量修改为5*/ UPDATE Customers
SET Tel= , Address='长安区' WHERE CustomerID=3; UPDATE Orders SET CustomerID=10
WHERE GoodsID=0001; /*该语句错误，没有参考完整性*/ /*删除表中的一些数据*/ DELETE FROM Orders
WHERE CustomerID=2;/*删除了CustomerID为2的订单记录*/ /*查询*/ SELECT * FROM Customers
WHERE City='西安'; /*查找所有西安客户的信息*/ SELECT GoodsName,Storage,Price,0.25*Price Discount From Goods
ORDER BY Price ASC; /*查找所有商品的名称，库存，价格以及表示打折价，按升序排列*/
SELECT GoodsID,GoodsName,Price From Goods
WHERE GoodsName LIKE '%衣%'; /*查找商品名中包含“衣”的商品编号，名称及价格*/ SELECT GoodsName,Storage,Price From Goods
WHERE Storage>100 AND Storage<500; /*查询库存量大于100小于500的商品名称，库存和单价*/ /*连接查询*/
SELECT Goodsname,Cname,Price,Quantity,Ordersum FROM Customers,Goods,Orders
WHERE Customers.CustomerID=Orders.CustomerID and Goods.GoodsID=Orders.OrderID and
Orderdata between ' 'and '' and Quantity>2
/*复杂查询*/
/*查找订单编号，商品编号，客户编号，按日期对订单分组，显示订货数量不超30的订单信息*/
SELECT OrderID,GoodsID,CustomerID FROM Orders WHERE Quantity<=30 (SELECT Orderdata FROM Orders
group by Orderdata); /*查找北京客户的订单信息*/ SELECT * FROM Orders
WHERE CustomerID in
(SELECT CustomerID FROM Customers WHERE City='西安');
/*查找不是上海客户的订单信息*/ SELECT * FROM Orders
WHERE CustomerID in (SELECT CustomerID FROM Customers WHERE City<>'上海');
/*北京客户的相关信息及订单情况*/ SELECT *
FROM Customers,Orders
WHERE Customers.CustomerID=Orders.CustomerID and City='西安'; CREATE VIEW XIAN_ORDER(OrderID,GoodsName,Quantity,CustomerID,Cname) AS
SELECT OrderID,GoodsName,Quantity,Orders.CustomerID,Cname FROM Orders,Goods,Customers
WHERE Customers.CustomerID=Orders.CustomerID AND Goods.GoodsID=Orders.OrderID AND City='西安' /*创建西安客户订单的视图*/ SELECT *
FROM XIAN_ORDER /*查找西安客户的订单信息*/ /*存储过程*/
/*为上海客户建立存储过程*/ CREATE procedure sp_shanghai AS SELECT * FROM customers WHERE city='上海'; CREATE procedure sp_Goods