數(shù)據(jù)庫原理及應(yīng)用課程設(shè)計餐飲管理系統(tǒng)-文庫吧
2025-03-28 06:51 本頁面
【正文】 null, YG_Age int null, YG_Sex char(20) null default 39。男39。, YG_Money decimal(8,2) null, YG_Zhiwei varchar(60) null, YG_phone varchar(20) null, constraint PK_YG_TABLE primary key (YG_id))Go/*=======================================================*//* Table: 訂單信息表 *//*=======================================================*/create table DingDan_table ( DD_xuhao int not null, DD_id char(20) not null, CZ_id char(20) null, CD_id char(20) null, YG_id char(20) null, DD_time datetime null, DD_zhuangtai char(60) not null, constraint PK_DINGDAN_TABLE primary key (DD_id, DD_xuhao))go通過查詢菜單與訂單實現(xiàn)包含有訂單號和總價格的視圖。/*==========================================================*//* View: View_ZhangDan *//*==========================================================*/create view View_ZhangDan asselect DD_id as 賬單號,sum(CD_price) as 總價格from DingDan_table,CaiDan_tablewhere =group by DD_id Go以菜單表為基礎(chǔ)實現(xiàn)菜單細分為粵菜、川菜、魯菜等不同特色的菜單。/*==========================================================*//* View: View_chuancai *//*==========================================================*/create view View_chuancai asselect CD_id,CD_name,CD_pricefrom CaiDan_tablewhere CD_id like 39。C%39。go/*=========================================================*//* View: View_lucai *//*=========================================================*/create view View_lucai asselect CD_id,CD_name,CD_pricefrom CaiDan_tablewhere CD_id like 39。L%39。go/*=========================================================*//* View: View_yuecai *//*=========================================================*/create view View_yuecai asselect CD_id,CD_name,CD_pricefrom CaiDan_tablewhere CD_id like 39。Y%39。go ：賬單視圖SELECT *FROM View_ZhangDan 賬單視圖結(jié)果：菜單視圖SELECT *FROM View_yuecaiSELECT *FROM View_chuancai 川菜視圖結(jié)果SELECT *FROM View_lucai 魯菜視圖結(jié)果/*=======存儲過程=========*/ 局部變量@CZ_id為要查詢的餐桌號USE 餐飲管理GOCREATE PROCEDURE shifoujiezhang (@CZ_id char(10))ASSELECT CZ_id as 桌號,KR_zhifu as 是否結(jié)賬from KeRen_table where CZ_id=@CZ_id。GO 局部變量@name為要查詢的員工姓名USE 餐飲管理GOCREATE PROCEDURE XiaoShouE @name char(10) ASSELECT DISTINCT(YG_Name),總價格from YG_table inner join DingDan_table on = inner join View_ZhangDan on DD_id=賬單號where YG_Name=@nameGO例：EXEC XiaoShouE 李四 銷售額存儲過程結(jié)果alter table DingDan_table add constraint FK_DINGDAN__DIANCAI_CANZHUO_ foreign key (CZ_id) references CanZhuo_table (CZ_id)goalter table DingDan_table add constraint FK_DINGDAN__FUZE_YG_TABLE foreign key (YG_id) references YG_table (YG_id)goalter table JinHuo_table add constraint FK_JINHUO_T_FUZE_YG_TABLE foreign key (YG_id) references YG_table (YG_id)
公司管理相關(guān)推薦
鄂ICP備17016276號-1