数据库操作题
--1.创建一个班级表(classes)
CREATE table classes(
id int,
name VARCHAR(20)
)
--2创建一个学生表(students)
CREATE table students(
id int,
name varchar(20),
birthday date,
gender char(2),
score int,
class_id int
)
--3.在班级表中添加三条记录
INSERT INTO classes (id,name)VALUES(1,"一年级"),
(2,"二年级"),(3,"三年级")
--4.为students表添加6条数据(多条添加)
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,2),
(6,"小刘","2008-11-10",'男',90,3)
--5.使用pymysql模块来查询每个班级的平均分
select name,score from(select * from students order by score desc) as s group by class_id;
--6.修改张三的名字为张五
UPDATE students set name="张五"where id=1
--删除班级名字为”三年级”的所有学生
DELETE FROM students WHERE class_id=3
--查询一年级分数最高的人
SELECT MAX(score) FROM students WHERE class_id=1
select name,score,class_id from(select * from students order by score desc) as s group by class_id=1;
--查询有学生的年级信息
SELECT classes.name FROM classes WHERE classes.id IN (SELECT students.class_id FROM students GROUP BY students.id)
--查询以‘小’开头的名字为一个字学生
SELECT * FROM students WHERE name LIKE'小%'
--查询出生日期在2008-11-10到2012-11-10的学生
--select 姓名，学号 from student where 出生日期>=1998-10-1 and 出生日期<=1998-11-30
SELECT id,name,birthday,gender,score,class_id FROM students WHERE birthday>="2008-11-10 "AND birthday<="2012-11-10"
--创建一个分类表
CREATE TABLE cate(
id int,
name VARCHAR(20)
)
--创建一个商品表
CREATE TABLE goods(
id int,
name VARCHAR(20),
number int,
create_time date,
price FLOAT(6,2),
cate_id int
)
INSERT INTO cate (id,name)VALUES(1,"服装"),
(2,"玩具"),(3,"家电")
INSERT into goods(id,name,create_time,number,price,cate_id)VALUES
(1,"羽绒服","2010-11-10",10,"3.00",1),
(2,"轻便服","2012-11-10",30,"2.00",1),
(3,"汽车","2014-11-10",40,"2.00",2),
(4,"公主","2013-11-10",50,"6.00",2),
(5,"橡皮泥","2015-11-10",23,"3.50",2),
(6,"电饭煲","2008-11-10",3,"3.00",3)
--使用pymysql模块来查询所有的分类及商品
select id,name,create_time,number,price,cate_id from(select * from goods order by price desc) as s group by cate_id;
--修改把名字为‘羽绒服’修改成‘棉衣’
UPDATE goods set name="棉衣"where id=1
--删除名字为‘电饭煲’的记录
DELETE FROM goods WHERE name="电饭煲"
--8.查询‘服装’分类下面库存最多的商品信息
SELECT * FROM goods WHERE number = (SELECT MAX(number) FROM goods WHERE cate_id=(SELECT id FROM cate WHERE name=“服装”))
--9.查询分类下有商品 分类信息
SELECT cate.name FROM cate WHERE cate.id IN (SELECT goods.cate_id FROM goods GROUP BY goods.id)
--查询以‘服’结束的商品信息
SELECT * FROM goods WHERE name LIKE'%服'
--11查询创建日期在2008-11-10到2012-11-10的之间的商品
SELECT goods.name FROM goods WHERE goods.create_time BETWEEN “2008-11-10” AND “2012-11-10”
CREATE DATABASE cart
use cart
CREATE TABLE users(
id int,
name VARCHAR(20),
height FLOAT(6,2)
)
CREATE TABLE user_cart(
id int,
product VARCHAR(20),
amount int,
price int,
remove int,
user_id int
)
INSERT INTO users (id,name,height)VALUES(1,"张三","1.80"),
(2,"李四","1.75"),(3,"小明","1.34")
INSERT into user_cart(id,product,amount,price,remove,user_id)VALUES
(1,"商品一",10,100,1,1),
(2,"商品二",3,89,0,1),
(3,"商品三",2,30,1,1),
(4,"商品四",10,50,1,2),
(5,"商品五",3,10,1,2),
(6,"商品六",1,76,1,2)
7.更新李四的购物车中 商品五的名字为商品5
UPDATE user_cart SET user_cart.product="商品5" WHERE user_cart.user_id=(SELECT id FROM users WHERE users.name="李四")
UPDATE user_cart SET user_cart.product="商品5" WHERE user_cart.id=5
8.查询身高大于1米并且购物车没删除的用户和购物车信息
SELECT * FROM users INNER JOIN user_cart ON users.id=user_cart.user_id WHERE users.height>1
9.查询购物车中删除和没删除的总价和为多少
SELECT SUM(sum) FROM user_cart
10.查询每个用户没删除的购物车的数量大于1的记录，并按降序排列，取三条记录
SELECT *FROM user_cart WHERE isdelete=1 AND number>1 ORDER BY number DESC LIMIT 0,3
11.按是否删除分组，查询平均总价格大于10的用户和购物车信息
SELECT * FROM users INNER JOIN user_cart ON users.id=user_cart.user_id WHERE sum>10 GROUP BY isdelete
12.查询有购物车的用户和购物车信息
SELECT * FROM users INNER JOIN user_cart ON users.id=user_cart.user_id
13.删除张三购物车中已删除的购物车信息
DELETE FROM user_cart WHERE user_id=1
