-- CREATE TABLE students (
id INT,name VARCHAR(20),birthday date,gender VARCHAR(20),score VARCHAR(20),
class_id VARCHAR(20)
);
-- 创建一个学生表(students),包含字段id,name,birthday,gender,score,class_id
SHOW TABLES
-- 查看所有表
CREATE TABLE classrs (
id INT,name VARCHAR(20)
);
-- 创建一个班级表(classes)，包含字段id,name
INSERT into classrs(id,name)VALUES(1,"one"),(2,"two"),(3,"three");
-- 在班级表中添加三条记录 id:1 name:一年级，id:2 name:二年级，id:3 name:三年级
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES
(1,'张三','2010-11-10','男','100','1'),
(2,'李四','2012-11-10','女','75','1'),
(3,'王五','2014-11-10','男','80','2'),
(4,'小明','2013-11-10','女','60','2'),
(5,'小李','2015-11-10','女','30','3'),
(6,'小刘','2008-11-10','男','90','3');
-- 在员工表里添加六条记录，
UPDATE students SET name="张五" WHERE name="张三" ;
-- 修改张三的名字为张五
DELETE FROM classrs WHERE id=3;
-- 删除班级名字为”三年级”的所有学生
SELECT name,MAX(score) AS '最高成绩' FROM students WHERE class_id='1';
-- 查询一年级分数最高的人
select * from students;
-- 查询有学生的年级信息
select * FROM students where name LIKE "小";
-- 查询以‘小’开头的名字为一个字学生
SELECT * FROM students WHERE birthday BETWEEN "2008-11-10" AND "2012-11-10";
-- 查询出生日期在2008-11-10到2012-11-10的学生
-- 创建一个分类表(cate)，包含字段id,name
CREATE TABLE cate(
id INT PRIMARY KEY auto_increment,
cname VARCHAR(20)
)
-- 创建一个商品表(goods),包含字段id,name,number,create_time,price,cate_id
CREATE TABLE goods(
id INT PRIMARY KEY auto_increment,
gname VARCHAR(10),
number INT,
create_time date,
price DOUBLE,
cate_id INT
)
-- 在分类表中添加三条记录 id:1 name:服装，id:2 name:玩具，id:3 name:家电
INSERT into cate(cname)
VALUES("服装"),
("玩具"),
("家电")
-- 在商品表里添加六条记录
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')
-- 使用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 g.gname,MAX(g.number) as "库存最多"
FROM goods g,cate c
WHERE c.cname="服装"
-- 查询分类下有商品分类信息
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 g.gname,g.create_time
from goods g
where g.create_time>'2008-11-10' and g.create_time<'2012-11-10'
-- 创建一个数据库 cart
CREATE DATABASE cart;
-- 使用新创建的数据库,在数据库下建立两张表
-- 用户表(users): id 姓名 身高
-- 购物车表(user_cart):id 商品名称 购买数量 总价 是否删除 用户id
-- 创建一个分类表(cate)，包含字段id,name
CREATE TABLE users(
id INT PRIMARY KEY auto_increment,
uname VARCHAR(20),
height DOUBLE
)
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 FROM user_cart c, users u WHERE c.deleteu=0 AND u.uname="张三"
