-- 创建班级表（classs）
CREATE TABLE class(
id INT PRIMARY KEY auto_increment,
cname VARCHAR(20)
);
-- 创建学生表（students）
CREATE TABLE students(
sid INT PRIMARY KEY,
sname VARCHAR(20),
birthday DATE,
gerder VARCHAR(10),
score INT,
class_id INT
);
-- 在班级表添加三条记录
INSERT INTO class (cname) VALUES('一年级'),('二年级'),('三年级');
SELECT * FROM class;
-- 在员工表添加6条记录
INSERT INTO students(sname,birthday,gerder,score,class_id) VALUES
('张三','2010-11-10','男','100','1'),
('李四','2012-11-10','女','75','1'),
('王五','2014-11-10','男','80','2'),
('小明','2013-11-10','女','60','2'),
('小李','2015-11-10','女','30','3'),
('小刘','2008-11-10','男','90','3')
SELECT * FROM students;
-- 查询每个班级平均分
select AVG(s.score),c.cname
FROM students s ,class c
WHERE s.class_id=c.id
GROUP BY s.class_id
-- 修改张三的名字为张五
UPDATE students set sname='张五' WHERE sname='张三';
-- 删除班级名为“三年级”的所有学生
DELETE FROM students WHERE class_id='3';
-- 查询一年级分数最高的人
SELECT sname,MAX(score) AS '最高成绩' FROM students WHERE class_id='1';
-- 查询有学生的年级信息（用子查询实现）
SELECT * FROM class WHERE cname=(SELECT class_id != NULL FROM students);
-- 10 查询以‘小’开头的名字为一个字学生
SELECT * from students where sname LIKE '小%'
-- 查询出生日期在2008-11-10到2012-11-10的学生
select*from students where birthday between '2008-11-10'and '2012-11-10';
--------------------------------------------------------------------------------------------------
-- 创建一个分类表(cate)
CREATE TABLE cate(
id INT PRIMARY KEY auto_increment,
cname VARCHAR(20)
);
-- 创建一个商品表(goods)
CREATE TABLE goods(
sid INT PRIMARY KEY,
gname VARCHAR(20),
number INT,
create_time DATE,
price DOUBLE,
cate_id INT
);
-- 在分类表中添加三条记录
INSERT INTO cate (cname) VALUES('服装'),('玩具'),('家电');
SELECT * FROM cate;
-- 在商品表里添加六条记录
INSERT INTO goods(gname,create_time,number,price,cate_id) VALUES
('羽绒服','2010-11-10','10','1.00','1'),
('轻便服','2012-11-10','30','2.00','1'),
('汽车','2014-11-10','40','2.00','2'),
('公主','2013-11-10','50','6.00','2'),
('橡皮泥','2015-11-10','23','3.50','2'),
('电饭煲','2008-11-10','3','3.00','3')
SELECT * FROM goods;
-- 使用pymysql模块来查询所有的分类及商品
select * from goods g LEFT JOIN cate c ON g.cate_id=c.id
-- 把名字为‘羽绒服’修改成‘棉衣’
UPDATE goods set gname='棉衣' WHERE gname='羽绒服';
-- 删除名字为‘电饭煲’的记录
DELETE FROM goods WHERE gname='电饭煲';
-- 查询‘服装’分类下面库存最多的商品信息
SELECT gname,MAX(number) AS '库存最多' FROM goods WHERE cate_id='1';
-- 查询分类下有商品分类信息
select c.cname,g.gname from cate c,goods g WHERE c.id=g.cate_id
-- 查询以‘服’结束的商品信息
SELECT * from goods where gname LIKE '%服';
-- 查询创建日期在2008-11-10到2012-11-10的之间的商品
select*from goods where create_time between '2008-11-10'and '2012-11-10';
------------------------------------------------------------------------------------------------------
-- 用户表(users):
CREATE TABLE users(
id INT PRIMARY KEY auto_increment,
uname VARCHAR(20),
height DOUBLE
)
-- 购物车表(user_cart):id 商品名称 购买数量 总价 是否删除 用户id
CREATE TABLE user_cart(
id INT PRIMARY KEY auto_increment,
cname VARCHAR(20),
number INT,
price DOUBLE,
deleteu int,
uid INT
)
INSERT into users(uname,height)
VALUES("张三","1.80"),
("李四","1.75"),
("小明","1.34")
-- 在购物车表中加入以下记录（1代表未删除，0代表删除）
INSERT into user_cart(cname,number,price,deleteu,uid)
VALUES("商品一",10,100,1,1),
("商品二",3,89,0,1),
("商品三",2,30,1,1),
("商品四",10,50,1,2),
("商品五",3,10,1,2),
("商品六",1,76,1,2)
-- 7.更新李四的购物车中 商品五的名字为商品5
UPDATE user_cart SET cname="商品5" WHERE cname="商品五"
-- 8.查询身高大于1米并且购物车没删除的用户和购物车信息
select * from users u right JOIN user_cart c ON c.uid=u.id WHERE u.height>1 AND c.deleteu=1
-- 9.查询购物车中删除和没删除的总价和为多少
SELECT sum(price) FROM user_cart
-- 10.查询每个用户没删除的购物车的数量大于1的记录，并按降序排列，取三条记录
SELECT * FROM user_cart c WHERE c.deleteu=1 AND number>1 ORDER BY number DESC LIMIT 0,3
-- 11.按是否删除分组，查询平均总价格大于10的用户和购物车信息
SELECT * FROM user_cart c GROUP BY deleteu HAVING sum(c.price)/count(*)>10
-- 12.查询有购物车的用户和购物车信息
select * from users u right JOIN user_cart c ON c.uid=u.id WHERE c.deleteu=1
-- 13.删除张三购物车中已删除的购物车信息
DELETE user_cart,users from user_cart LEFT JOIN users ON user_cart.uid=users.id WHERE user_cart.deleteu=0 AND users.uname="张三"
