mysql(保姆级教程）

create table goods(
good_no int comment'商品编号',
goods_name varchar(50) comment'商品名称',
cost double comment'商品成本',
price double comment'商品售价',
count int comment'商品库存',
create_time datetime comment'商品上架时间',
create_no int comment'商品分类编号');

 

create table category(
no int comment'商品分类编号',
name varchar(50) comment'商品类别分类',);

 

 

create table account (
id int comment '用户编号',
    phone varchar(11) comment '用户手机号',
    password varchar(50) comment '用户密码',
    type bit comment '用户类型0管理员1会员',
    name varchar(50) comment '用户名',
    point int comment '用户积分',
    money double comment '用户余额',
    create_time datetime comment '用户创建时间'
);

 

create table cart (
id int comment '购物车编号',
    goods_no int comment '商品编号',
    num int comment '商品数量',
    account_id int comment '用户编号',
    create_time datetime comment '加入购物车时间'
); 

二、添加数据

insert into goods values(1,'火腿肠',0.5,2.5,50,'2023/3/13 19:50',1);
insert into goods values
(2,'薯片',1.5,3.5,30,'2023/3/10 19:50',1),
(3,'鼠标垫',20.5,25.5,20,'2023/3/7 19:50',4),
(4,'萝卜',0.8,1.5,50,'2023/3/8 19:50',2),
(5,'白菜',0.2,0.8,50,'2023/3/9 19:50',2),
(6,'车厘子',30,50,50,'2023/3/10 19:50',3),
(7,'芒果',5,8,50,'2023/3/11 19:50',3),
(8,'五粮液',1350,1700,60,'2023/3/12 19:50',5),
(9,'鼠标',40,50,20,'2023/3/13 19:50',4);


insert into category values(1, '零食'),
    (2,'蔬菜'),
    (3,'水果'),
    (4,'电子产品'),
    (5,'酒水');

insert into cart(id,goods_no,num,create_time,account_id) values
(1,1,2,'2023/3/11 19:50',2),
(2,2,2,'2023/3/12 19:50',3),
(3,4,10,'2023/3/13 19:50',4),
(4,6,20,'2023/3/12 19:50',3),
(5,9,1,'2023/3/12 19:50',2),
(6,2,10,'2023/3/13 19:50',4);

三、作业

1 select good_no,goods_name,price,count from goods where count=50;

2 select good_no,goods_name,price,count from goods where cost=60;

3 update cart set num=num+1 where id=5;

5 alter table cart change id cart_id int;

6 select cart_id,goods_no,num,account_id,create_time from cart where num=10;

7 delete from account where money=9;

8 select * from cart where account_id = 3;

9 select * from goods limit 5,5;

10 select * from goods order by price asc,cost desc limit 6,3;