select 语句查询

示例代码create TABLE Student(    id int,    name varchar(20),    chinese INT,    english INT,    math INT);INSERT into Student(id,name,chinese,english,math)VALUES(1,'欧阳锋','90','80',30);INSERT into Student(id,name,chinese,english,math)VALUES(1,'黄蓉','100','80',30);INSERT into Student(id,name,chinese,english,math)VALUES(1,'郭靖','90','50',30);select* from Student;select name,english from student;select DisTINCT name,english from student;select name,    chinese+10,english+10,math+10 from student;select name,    chinese+english+math sum from student;select * from student where name="欧阳锋";select* from student where  english>50;select *from student where chinese+english+math>200;select * from student where english between 80 and 90;SELECT * from student WHERE chinese in(80,50); INSERT into student(id,name,chinese,english,math)VALUES(10,'李一','60','80','100');INSERT into student(id,name,chinese,english,math)VALUES(10,'李二才','60','80','100');select* from student;SELECT * from student WHERE name like '李%';SELECT * from student WHERE name like '李__';select *from student where math>=30 and chinese>80;drop database student;SELECT * from student;DELETE stduent WHERE name;select *from student where name like '李%' order by english+chinese+math;select count(*) from student;select count(*)from student where english>60;select count(*)from student where english+chinese+math>150;select sum(math) from student;select sum(chinese),SUM(english),SUM(math) from student;select sum(english+chinese+math) from student;select sum(chinese)/count(*) from student;select sum(chinese)/count(chinese) from student;select avg(math)from student;select avg(english+chinese+math)from student;select name from student where english=(select max(english)from student);create table orders(    id int,    product varchar(20),    price float);insert into orders(id,product,price)values(1,'电视',800);insert into orders(id,product,price)values(1,'洗衣机',100);insert into orders(id,product,price)values(1,'橘子',92);insert into orders(id,product,price)values(1,'洗衣粉',90);insert into orders(id,product,price)values(1,'洗衣粉',90);select * from orders group by product;select id,product,sum(price)from orders group by  product;select id,product,sum(price) from orders group by product having SUM(price)>150;

create TABLE Student(

id int,name varchar(20),chinese float,english float,math float

);
INSERT into Student(id,name,chinese,english,math)VALUES(1,’欧阳锋’,’男’,’90’,’80’,30);
INSERT into Student(id,name,chinese,english,math)VALUES(1,’黄蓉’,’男’,’100’,’80’,30);
INSERT into Student(id,name,chinese,english,math)VALUES(1,’郭靖’,’男’,’90’,’50’,30);
INSERT into Student(id,name,chinese,english,math)VALUES(3,’欧阳锋’,’男’,’90’,’80’,30);
INSERT into Student(id,name,chinese,english,math)VALUES(2,’欧阳锋’,’男’,’90’,’80’,30);

查询表中所有学生信息。
select *from student;
查询表中所有学生的姓名和对应的英语成绩
select name,english from student
过滤表中重复数据实际上先查询所有的然后再剔除
select DisTINCT name,english from student;

在所有学生分数上加10份特长份
select name,english+10 from student;
统计每个学生的总分

select name,chinese+english+math from student;
使用别名表示学生分数
select name,chinese+english+math sum from student;

where 子句 过滤数据
查询姓名为欧阳锋的成绩
select * from student where name=”欧阳锋”;
查询英语大于50分的同学
select* from student where english>90;
查询总分大于200分的同学
select *form student where english+chinese+math>200;

查询英语分数在80-90之间的同学 上下都包含
select * from student where english between 80 and 90;
查询数学分数为89，50的同学
SELECT * from student WHERE chinese in(80,50);
查询数学分数大于20，英语大于80的同学
select *from student where math>20 and chinese>80;

// order by 默认asc升序
对数学成绩排序后输出
select name,math from student order by math;
对总分排序后输出，然后再从高到底的顺序输出
select *, english+chinese+math from student order by english+chinese+math;
对姓李姓学生成绩排序输出
select *from student where name like ‘李%’ order by english+chinese+math;

//count 合计函数
统计一个班级共有多少学生？
select count(*) from student;
统计属性英语大于90分的学生有多少个？
select count(*)from student where english>90;
统计总分大于170分的人数有多少？
select count(*)from student where english+chinese+math>150;
统计一个班数学总成绩
select sum(math) from student;
统计一个班英语，语文，数学各科总成绩
select sum(english,chinese,math) from student;
统计一个班英语，语文，数学各科总成绩
select sum(english+chinese+math) from student;
统计一个班语文平均分
select sum(chinese)/count(*) from student;
select sum(chinese)/count(chinese) from student;//语文不算的缺考了
// avg 合计函数
求一个班级数学平均分
select avg(math)from student;
求一个班级总分平均分
select avg(nglish+chinese+math)from student;
求班级的最高分和最低分
select max(english) from student;
select name from student where english=(select max(english)frome student);
*号代表查询所有的列
From指定查询哪剔除重复数据结果时，是否张表。

//group by
creat table orders(
id int,
product varchar(20),
price float
);

insert into orders(id,product,price)values(1,’电视’,800);
insert into orders(id,product,price)values(1,’洗衣机’,100);
insert into orders(id,product,price)values(1,’橘子’,92);
insert into orders(id,product,price)values(1,’洗衣粉’,90);
insert into orders(id,product,price)values(1,’洗衣粉’,90);

//按照商品归类
select * from orders group by prodect;
//让多个重复商品的价格加起来显示
select id,product,sum(price)from orders group by product;

//找出价格大于150的商品 having 用于group by的后面，需要合计函数的情况
select id,product,sum(price) from orders goup by product having sum(price)>150;