數(shù)據(jù)庫(kù)原理及應(yīng)用課程設(shè)計(jì)餐飲管理系統(tǒng)-文庫(kù)吧
2025-03-28 06:51 本頁(yè)面
【正文】 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通過(guò)查詢(xún)菜單與訂單實(shí)現(xiàn)包含有訂單號(hào)和總價(jià)格的視圖。/*==========================================================*//* View: View_ZhangDan *//*==========================================================*/create view View_ZhangDan asselect DD_id as 賬單號(hào),sum(CD_price) as 總價(jià)格from DingDan_table,CaiDan_tablewhere =group by DD_id Go以菜單表為基礎(chǔ)實(shí)現(xiàn)菜單細(xì)分為粵菜、川菜、魯菜等不同特色的菜單。/*==========================================================*//* 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é)果/*=======存儲(chǔ)過(guò)程=========*/ 局部變量@CZ_id為要查詢(xún)的餐桌號(hào)USE 餐飲管理GOCREATE PROCEDURE shifoujiezhang (@CZ_id char(10))ASSELECT CZ_id as 桌號(hào),KR_zhifu as 是否結(jié)賬from KeRen_table where CZ_id=@CZ_id。GO 局部變量@name為要查詢(xún)的員工姓名USE 餐飲管理GOCREATE PROCEDURE XiaoShouE @name char(10) ASSELECT DISTINCT(YG_Name),總價(jià)格from YG_table inner join DingDan_table on = inner join View_ZhangDan on DD_id=賬單號(hào)where YG_Name=@nameGO例：EXEC XiaoShouE 李四 銷(xiāo)售額存儲(chǔ)過(guò)程結(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號(hào)-1