SQL语句 ：查询、条件查询、排序、聚合、分组

CREATE TABLE stuscore(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    SUBJECT VARCHAR(20),
    score VARCHAR(20)    
)
SELECT *from stuscore
insert into stuscore VALUES(null,'张三','数学','89');
INSERT INTO stuscore VALUES(NULL,'张三','语文','80');
INSERT INTO stuscore VALUES(NULL,'张三','英语','70');
INSERT INTO stuscore VALUES(NULL,'李四','数学','90');
INSERT INTO stuscore VALUES(NULL,'李四','语文','82');
INSERT INTO stuscore VALUES(NULL,'李四','英语','88');
#计算每个人的总成绩并从高到低排名（姓名，总成绩）
SELECT name,SUM(score) from stuscore GROUP BY name ORDER BY SUM(score) DESC
SELECT NAME ,SUM(score) total FROM  stuscore t GROUP BY NAME ORDER BY total DESC
#查询出每门课都大于80分的学生姓名
select name,min(score) from stuscore group BY name ASC HAVING MIN(score)>80
select name from (select name,min(score) from stuscore group BY name ASC HAVING MIN(score)>80)stu
 #列出各门课程的平均成绩（课程，平均成绩）
 select SUBJECT,AVG(score) from stuscore GROUP BY SUBJECT
 
 create table product(
    pid int primary key auto_increment,
    pname varchar(20),
    price double,
    pdate timestamp
)
insert into product values (null,'谭妮平',0.01,null);
insert into product values (null,'李士雪',38,null);
insert into product values (null,'左慈',-998,null);
insert into product values (null,'黄迎',99999,null);
insert into product values (null,'南国强',99998,null);
insert into product values (null,'士兵',1,null);
insert into product values (null,'李士兵',698,null);
--   1.查询所有商品
SELECT * from product
--     2.查询商品名和商品价格
SELECT pname,price from product 

--     3.查询所有商品信息使用表别名

SELECT p.pid,p.pname,p.price,p.pdate from product as p
--     4.查询商品名，使用列别名
SELECT p.pid,p.pname as 姓名,p.price,p.pdate from product as p
--     5.去掉重复值(按照价格)
SELECT DISTINCT price from product
--     6.将所有的商品的价格+10进行显示
SELECT *,price+10 as 增加后的值 from product

--  条件查询
--     1.查询商品名称为"左慈"的商品信息
select *from product where pname='左慈';
--     2.查询价格>60元的所有商品信息
SELECT *FROM product WHERE price>60;
--     3.查询商品名称含有"士"字的商品信息
SELECT *FROM product WHERE pname like '%士%';
--     4.查询商品id在(3,6,9)范围内的所有商品信息
SELECT *FROM product WHERE pid IN(3,6,9);
SELECT *FROM product WHERE pname LIKE '%士%' AND pid=6 OR pid=9;

-- 排序(asc/desc)
 1.查询所有的商品，按价格进行排序(升序asc、降序desc)
select *from product ORDER BY price ASC
 2.查询名称有"士"的商品信息并且按照价格降序排序
  SELECT *FROM product WHERE pname LIKE '%士%'  ORDER BY price DESC;

--     聚合
select * from product
    1.获得所有商品的价格的总和
    select sum(price) from product
    2.获得所有商品的平均价格
    select avg(price) from product 
    3.获得所有商品的个数
    select count(*) from product
    4.升级一下
    SELECT AVG(price) ,SUM(price),COUNT(*)FROM product;
    
-- 分组
 1.添加分类id 
alter table product add cid varchar(32);
     2.初始化数据
    update product set cid='1';
    update product set cid='2' where  pid in (5,6,7);
    查询：
    1.根据cid字段分组，分组后统计商品的个数。
    select *,count(*) as个数 from product GROUP BY cid
    2.根据cid分组，分组统计每组商品的平均价格，并且平均价格大于30000元。
    select cid,avg(price) from product GROUP BY cid HAVING avg(price)>30000;