正常SQL语句练习

1.插入数据
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES(2,“李四”,“2012-11-10”,“女”,“75”,1)
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES(3,“王五”,“2014-11-10”,“男”,“80”,2)
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES(4,“小明”,“2013-11-10”,“女”,“60”,2)
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES(5,“小李”,“2015-11-10”,“女”,“30”,2)
INSERT INTO students(id,name,birthday,gender,score,class_id) VALUES(6,“小刘”,“2008-11-10”,“男”,“90”,3)

2.查询三个班级的平均数
SELECT AVG(score) FROM students GROUP BY class_id

3.修改张三的名字为张五
UPDATE students SET name=“张五” WHERE id=1

4.删除班级名字为”三年级”的所有学生
DELETE FROM students WHERE class_id=3

5.查询一年级分数最高的人
SELECT MAX(score) FROM students WHERE class_id=1

6.查询有学生的年级信息（用子查询实现）
SELECT classes.name FROM classes WHERE classes.id IN (SELECT students.class_id FROM students GROUP BY students.id)

7.查询以‘小’开头的名字为一个字学生
SELECT students.name FROM students WHERE students.name LIKE “小_”

11查询出生日期在2008-11-10到2012-11-10的学生
SELECT st.name FROM students st WHERE st.birthday BETWEEN “2008-11-10” AND “2012-11-10”

5 使用pymysql模块来查询所有的分类及商品
SELECT *FROM cate c,goods g WHERE c.id = g.cate_id

SELECT * FROM cate INNER JOIN goods ON cate.id=goods.cate_id

6 把名字为‘羽绒服’修改成‘棉衣’
UPDATE goods SET goods.name=“棉服” WHERE goods.name=“羽绒服”

7 删除名字为‘电饭煲’的记录
DELETE FROM goods WHERE goods.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)

10 查询以‘服’结束的商品信息
SELECT * FROM goods WHERE goods.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”

7.更新李四的购物车中 商品五的名字为商品5
UPDATE user_cart SET user_cart.goodsname=“商品5” WHERE user_cart.user_id=(SELECT id FROM users WHERE users.username=“李四”)

8.查询身高大于1米并且购物车没删除的用户和购物车信息
SELECT * FROM users INNER JOIN user_cart ON users.id=user_cart.user_id WHERE users.height>1 AND user_cart.isdelete=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 AND isdelete=0