员工信息

表Waiter用于保存餐厅服务人员的一些基本信息，表的结构如图所示。

图3.29

四、查询

（1）创建单表查询 USE CANYIN_db

SELECT Food.FoodName 名称,Food.FoodPrice 价格 FROM Food

WHERE Food.Foodtype='肉';

图 4.1

21

USE CANYIN_db

SELECT Food.Foodtype,Food.FoodName FROM Food

WHERE Food.Foodnum='2001';

图4.2

USE CANYIN_db

SELECT Guestfood.FoodPrice,Guestfood.G_Foodtype FROM Guestfood

WHERE Guestfood.G_FoodNum='2001';

图4.3

（2）创建多表查询 USE CANYIN_db SELECT

Food.FoodName

菜

名

称

格,Guestfood.G_WaiterName 服务员

22

,Food.FoodPrice

价

FROM Food,Guestfood

WHERE Food.Foodtype='肉' AND Food.FoodName=Guestfood.G_FoodName

图4.4

USE CANYIN_db

SELECT Food.Foodtype,Food.FoodName,Guestfood.G_WaiterName 服务员 FROM Food,Guestfood

WHERE Food.Foodnum='2001' AND Food.FoodName=Guestfood.G_FoodName

图4.5

USE CANYIN_db

SELECT Guestfood.FoodPrice,Guestfood.G_Foodtype,Food.FoodName FROM Food,Guestfood

WHERE Food.Foodnum='2001' AND Food.FoodName=Guestfood.G_FoodName

23

图4.6 ( 3 ) 创建视图

CREATE VIEW Purchase2 AS SELECT P_Time,P_Num FROM Purchase GO

USE CANYIN_db; SELECT*FROM Purchase;

图4.7

五、数据库代码

建库：

CREATE DATABASE [CANYIN_db] ON PRIMARY (

NAME='CANYIN_db',

FILENAME='E:\\数据库\\CANYIN.mdf',

24