mysql大作业SQL建库脚本

 

 

DROP DATABASE IF EXISTS order_food;
CREATE DATABASE order_food DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use order_food;


-- 权限表
DROP TABLE IF EXISTS c_quanxian;
CREATE TABLE c_quanxian (
    q_id     int ,
    q_Name   varchar(20),
    PRIMARY KEY (q_id)
);

insert into c_quanxian values(1,'超级管理员');
insert into c_quanxian values(2,'商家');
insert into c_quanxian values(3,'用户');
select * from c_quanxian;

-- 用户表
DROP TABLE IF EXISTS c_user;
CREATE TABLE c_user (
    u_id     int AUTO_INCREMENT,
    u_nickName         varchar(20),
    u_loginName varchar(20),
    u_loginPassword varchar(20),
    u_sex            varchar(20),
    u_phone  varchar(20),
    u_address            varchar(100),
    u_birth    DATE,
    u_quanxian int,
    PRIMARY KEY (u_id)
)AUTO_INCREMENT=1001;

insert into c_user values(null,'admin','admin','admin','男','15671134302','湖北武汉','1999-11-16',1);
insert into c_user values(null,'张三','张三','123','男','15671134302','湖北武汉','1999-11-16',3);
insert into c_user values(null,'李四','李四','123','女','15671134302','湖北武汉','2000-11-01',3);
insert into c_user values(null,'王五','王五','123','男','15671134302','湖北武汉','2006-11-20',2);
insert into c_user values(null,'詹柳','詹柳','123','男','15671134302','湖北武汉','2006-11-20',2);
insert into c_user values(null,'666','666','123','男','15671134302','湖北武汉','2006-11-20',2);
select * from c_user;

-- 
-- -- 菜系表
-- DROP TABLE IF EXISTS caixi;
-- CREATE TABLE caixi (
--     caixi_id     int AUTO_INCREMENT,
--     caixi_Name         varchar(20),
--     caixi_beizhu varchar(200) ,
--     PRIMARY KEY (caixi_id)
-- )AUTO_INCREMENT=1110001;
-- insert into caixi values(null,'湖北菜');
-- insert into caixi values(null,'湖南菜');
-- insert into caixi values(null,'广东菜');
-- insert into caixi values(null,'广西菜');
-- insert into caixi values(null,'上海菜');
-- insert into caixi values(null,'浙江菜');
-- select * from caixi;
-- 
-- 

-- 商户表
DROP TABLE IF EXISTS shanghu;
CREATE TABLE shanghu (
    shanghu_id     int AUTO_INCREMENT,
    shanghu_u_id int,
    shanghu_Name varchar(20),
    shanghu_is_keyong int,  -- 1可用，0不可用
    PRIMARY KEY (shanghu_id),
    FOREIGN KEY (shanghu_u_id) REFERENCES c_user (u_id)on delete CASCADE
)AUTO_INCREMENT=110001;
insert into shanghu values(null,1004,'鸡公煲',1);
insert into shanghu values(null,1005,'襄阳牛肉面',1);
insert into shanghu values(null,1004,'天天香川菜馆',1);
select * from shanghu;
-- DELETE from shanghu where shanghu_Name='天天香川菜馆';




-- 菜品表
DROP TABLE IF EXISTS caiping;
CREATE TABLE caiping (
    caiping_id     int AUTO_INCREMENT,
    caiping_shanghu_id     int ,
    caiping_Name         varchar(20),
    caiping_jiage float ,
    caiping_beizhu varchar(200) ,
    PRIMARY KEY (caiping_id),
    FOREIGN KEY (caiping_shanghu_id) REFERENCES shanghu (shanghu_id)on delete CASCADE
)AUTO_INCREMENT=1220001;

insert into caiping values(null,110003,'鱼香肉丝',20.0,'无');
insert into caiping values(null,110003,'小草鸡',30.0,'无');
insert into caiping values(null,110003,'酸菜鱼',33.0,'无');
insert into caiping values(null,110003,'麻辣虾',50.0,'无');
insert into caiping values(null,110003,'麻婆豆腐',15.0,'无');
insert into caiping values(null,110003,'青椒肉丝',20.0,'无');

insert into caiping values(null,110001,'青椒肉丝',20.5,'无');
insert into caiping values(null,110001,'青椒肉丝',20.0,'无');
insert into caiping values(null,110001,'青椒肉丝',20.0,'无');
insert into caiping values(null,110001,'青椒肉丝',20.5,'无');
select * from caiping;



-- 购物车表
DROP TABLE IF EXISTS gouwuche;
CREATE TABLE gouwuche (
    gouwuche_id     int AUTO_INCREMENT,
    gouwuche_u_id     int ,
    gouwuche_caiping_id int ,
    gouwuche_shuliang int ,
    PRIMARY KEY (gouwuche_id),
    FOREIGN KEY (gouwuche_u_id) REFERENCES c_user (u_id)on delete CASCADE,
    FOREIGN KEY (gouwuche_caiping_id) REFERENCES caiping (caiping_id)on delete CASCADE
)AUTO_INCREMENT=1020001;

insert into gouwuche values(null,1001,1220001,1);
insert into gouwuche values(null,1001,1220002,1);
insert into gouwuche values(null,1001,1220003,1);
insert into gouwuche values(null,1001,1220004,1);
insert into gouwuche values(null,1001,1220005,1);
select * from gouwuche;

-- 账户表

DROP TABLE IF EXISTS zhanghu;
CREATE TABLE zhanghu (
    zhanghu_u_id     int ,
    zhanghu_yu_e FLOAT ,
    zhanghu_is_keyong int,  -- 1可用，0不可用
    PRIMARY KEY (zhanghu_u_id),
    FOREIGN KEY (zhanghu_u_id) REFERENCES c_user (u_id)on delete CASCADE
)AUTO_INCREMENT=1420001;

insert into zhanghu values(1001,1000.5,1);
insert into zhanghu values(1002,3000.5,1);
insert into zhanghu values(1003,2000.5,1);
insert into zhanghu values(1004,4000.5,1);
insert into zhanghu values(1005,6050.5,1);
select * from zhanghu;




-- 订单表

DROP TABLE IF EXISTS dingdan;
CREATE TABLE dingdan (
    dingdan_id     int AUTO_INCREMENT,
    dingdan_u_id     int ,
    dingdan_caiping_id     int ,
    
    dingdan_count_jiage float, 
    
    dingdan_liuyan varchar(200),
    PRIMARY KEY (dingdan_id),
    FOREIGN KEY (dingdan_u_id) REFERENCES c_user (u_id)on delete CASCADE,
    FOREIGN KEY (dingdan_caiping_id) REFERENCES caiping (caiping_id)
)AUTO_INCREMENT=1520001;

insert into dingdan values(null,1001,1220001,20,'微辣');
insert into dingdan values(null,1001,1220001,20,'多加点米饭，米饭往死里压');
insert into dingdan values(null,1001,1220001,20,'');
insert into dingdan values(null,1001,1220001,20,'');
select * from dingdan;


-- 订单流程表

DROP TABLE IF EXISTS liucheng;
CREATE TABLE liucheng (
    liucheng_id     int AUTO_INCREMENT,
    liucheng_u_id int,
    liucheng_dingdan_id     int ,
    
    liucheng_dingdan_start_time    DATETime,-- 下单时间
    liucheng_dingdan_sure_time    DATETime,    -- 商家确认时间
    liucheng_dingdan_end_time    DATETime,    -- 确认收货,订单完成时间
    
    dingdan_is_wancheng int,  -- 1下单，2商家确认，0确认收货完成订单
    PRIMARY KEY (liucheng_id),
    
    FOREIGN KEY (liucheng_u_id) REFERENCES c_user (u_id)on delete CASCADE,
    FOREIGN KEY (liucheng_dingdan_id) REFERENCES dingdan (dingdan_id)on delete CASCADE
)AUTO_INCREMENT=1620001;
insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',0);
insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',1);
insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',2);
select * from liucheng;

-- 留言表

DROP TABLE IF EXISTS liuyan;
CREATE TABLE liuyan (
    liuyan_id     int AUTO_INCREMENT,
    liuyan_u_id int,
    
    liuyan_time    DATETime,-- 留言时间
    liuyan_xinxi varchar(200),
    PRIMARY KEY (liuyan_id),
    FOREIGN KEY (liuyan_u_id) REFERENCES c_user (u_id)on delete CASCADE
)AUTO_INCREMENT=2020001;
insert into liuyan values(null,1001,NOW(),'味道真的不错，朋友都喜欢吃。今天还是比较幸运的，外卖很快就送来了。量也是绝对是够的。');
insert into liuyan values(null,1002,NOW(),'这款产品比较好，色泽比较好，而且价格优惠价格实惠，服务周到，及时，好评！');
insert into liuyan values(null,1003,NOW(),'每一次都服务很好但是这家店铺，还是相对我觉得可以的，有机会大家可以去试试看。');
select * from liuyan;

select * from c_user;
select * from caiping;
select * from shanghu;
select * from c_quanxian;

-- 商品展示查询
select u.u_id,u.u_nickName,u.u_loginName,
sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,
c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu
from caiping as c,shanghu as sh ,c_user as u
where
c.caiping_shanghu_id = sh.shanghu_id and 
u.u_id=sh.shanghu_u_id

-- 5.删除课程名为'数据库系统' 的所有信息 注意级联删除
-- delete from 孙武浩_课程 c where c.课程名称='数据库系统';    

-- 菜品展示视图
drop view if exists caipinShow;
create view caipinShow
AS
(
select u.u_id,u.u_nickName,u.u_loginName,u.u_quanxian,
sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,
c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
qx.q_id
from caiping as c,shanghu as sh ,c_user as u,c_quanxian as qx
where
c.caiping_shanghu_id = sh.shanghu_id and 
u.u_id=sh.shanghu_u_id and 
u_quanxian=qx.q_id
);
select * from caipinShow;

-- 购物车展示视图
drop view if exists gouwucheShow;
create view gouwucheShow
AS
(
select 
u.u_id,u.u_nickName,u.u_loginName,u.u_quanxian,
sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,sh.shanghu_u_id,
c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
qx.q_id,
gwc.gouwuche_caiping_id,gwc.gouwuche_shuliang,gwc.gouwuche_u_id
from caiping as c,shanghu as sh,c_user as u,c_quanxian as qx,gouwuche as gwc
where
c.caiping_shanghu_id = sh.shanghu_id and 
u.u_id=sh.shanghu_u_id and 
u_quanxian=qx.q_id and
gwc.gouwuche_caiping_id=c.caiping_id
);
select * from gouwucheShow;


-- 订单页展示视图

drop view if exists dingdanShow;
create view dingdanShow
AS
(
select 
u.u_id,u.u_nickName,u.u_loginName,
sh.shanghu_id,sh.shanghu_Name,sh.shanghu_u_id,
c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
qx.q_id,
dd.dingdan_id,dd.dingdan_caiping_id,dd.dingdan_u_id,dd.dingdan_liuyan,dd.dingdan_count_jiage,
lc.liucheng_id,lc.liucheng_u_id,lc.liucheng_dingdan_id,lc.liucheng_dingdan_start_time,lc.liucheng_dingdan_sure_time,lc.liucheng_dingdan_end_time,lc.dingdan_is_wancheng
from caiping as c,shanghu as sh,c_user as u,c_quanxian as qx,dingdan as dd,liucheng as lc
where
c.caiping_shanghu_id = sh.shanghu_id and 
u_quanxian=qx.q_id and
dd.dingdan_u_id=u.u_id and
lc.liucheng_u_id=u.u_id
);
select * from dingdanShow;




select * from c_quanxian;
select * from c_user;
select * from caiping;
select * from dingdan;
select * from gouwuche;
select * from liucheng;
select * from liuyan;
select * from shanghu;
select * from zhanghu;