準(zhǔn)備數(shù)據(jù)：
create table student(
?id int,
?name varchar(20),
?chinese float,
?english float,
?math float
);
insert into student (id ,name ,chinese,english,math)values(1,`張小明`,89,78,90);
insert into student (id ,name ,chinese,english,math)values(2,`李進(jìn)`,67,98,56);
insert into student (id ,name ,chinese,english,math)values(3,`王五`,87,78,77);
insert into student (id ,name ,chinese,english,math)values(4,`李一`,88,98,90);
insert into student (id ,name ,chinese,english,math)values(5,`李來財`,82,84,67);
insert into student (id ,name ,chinese,english,math)values(6,`張進(jìn)寶`,55,85,45);
insert into student (id ,name ,chinese,english,math)values(7,`張小明`,75,65,30);

?

查詢學(xué)生的總成績
select s.chinese+s.english+s.math,s.name from student s

?

使用別名
select chinese as 語文,english as 英語,math as 數(shù)學(xué),name from student
與下面語句等價
select chinese 語文,english 英語,math 數(shù)學(xué),name from student

?

查詢總分大于200分的所有用戶
select * from student s where (s.chinese+s.english+s.math)>250

不等于：<> 或者 !=

1?使用order by 排序

order by使用語法：

select column1,column2 from table_name? order by 列名 asc[decs]

?

對數(shù)學(xué)成績排序后輸出
select math,name from student order by math;

對總分排序后輸出
select (s.chinese+s.english+s.math) as sum_score,s.name from student s order by sum_score desc
與下面句子等價
select (s.chinese+s.english+s.math) ,s.name from student s order by (s.chinese+s.english+s.math) desc

?

對姓李的同學(xué)成績排序
select (s.chinese+s.english+s.math) as sum_score,s.name from student s where s.name like '李%' ORDER BY sum_score desc

2?統(tǒng)計函數(shù)

統(tǒng)計函數(shù)count語法：

select count(*) | count(列名) from tablename where where_definition

?

統(tǒng)計數(shù)學(xué)成績大于90分的同學(xué)
select count(*) from student? where math>=90

?

統(tǒng)計函數(shù)sum 和 avg語法

select sum(列名){,sum(列名)…} from table where where_definition

select avg(列名){,avg(列名)…} from table where where_definition
強調(diào)：avg不會統(tǒng)計（為空）的現(xiàn)象

?

統(tǒng)計函數(shù)max 和 min語法

select max(列名) from table where where_definition
select min(列名) from table where where_definition
備注：如果數(shù)據(jù)類型不是“數(shù)值”類型，也能進(jìn)行排序查詢

3?group by 分組
create table orders(
?id int,
?product varchar(20),
?price float
);

insert into orders(id,product,price) values(1,'電視',900);
insert into orders(id,product,price) values(2,'洗衣粉',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',10);
insert into orders(id,product,price) values(5,'洗衣粉',90);

?

根據(jù)商品分類
select product,sum(price) from orders group by product;

?

having用法
查詢購買了幾類商品，并且每類總價大于100的商品
select product,sum(price) as sumprice from orders? group by product HAVING sumprice >100;

?