創(chuàng)建一個學(xué)生表园欣，插入數(shù)據(jù)
create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);
insert into student(id,name,chinese,english,math) values(1, '范建',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'羅況',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'杜子騰',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'范冰',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'申晶冰',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'郝麗海',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'郭迪輝',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'拎壺沖',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'史泰香',94,85,84);
基礎(chǔ)SQL
查詢表中所有學(xué)生的信息
select * from student;
查詢表中所有學(xué)生的姓名和對應(yīng)的英語成績
select name,english from student;
過濾表中重復(fù)數(shù)據(jù)
select english from student;
select DISTINCT english from student; DISTINCT可去過濾english中重復(fù)的值
select DISTINCT english,name from student;DISTINCT可去過濾english和name中同時重復(fù)的數(shù)據(jù)
select english+chinese+math from student; 求總分褪子，顯示的段名為english+chinese+math
select english+chinese+math as 總分 from student;求總分易核，顯示的段名為總分
select name,english+chinese+math as 總分 from student;求總分嗅蔬，顯示的段名為總分蜘渣；同時顯示姓名列
在所有學(xué)生英語分?jǐn)?shù)上加10分特長分济蝉。
select name,english+10 from student;
統(tǒng)計每個學(xué)生的總分
select english+chinese+math from student;
使用別名表示學(xué)生分?jǐn)?shù)
select name,english+chinese+math as 總分 from student;
select name,english+chinese+math 總分 from student;
查詢姓名為范冰的學(xué)生成績
select * from student where name='范冰';
查詢英語成績大于90分的同學(xué)
select * from student where english>90;
查詢總分大于250分的所有同學(xué)
select * from student where english+chinese+math>250;
查詢英語分?jǐn)?shù)在 85－95之間的同學(xué)
select * from student where english>=85 and english<=95;
select * from student where english between 85 and 95;
查詢數(shù)學(xué)分?jǐn)?shù)為84,90,91的同學(xué)杰刽。
select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);
查詢所有姓范的學(xué)生成績。
select * from student where name like '范%';
查詢數(shù)學(xué)分>85王滤，語文分>90的同學(xué)贺嫂。
select * from student where math>85 and chinese>90;
對數(shù)學(xué)成績排序后輸出。
select * from student order by math;
對總分排序后輸出雁乡，然后再按從高到低的順序輸出
select * from student order by math+chinese+english desc;
對姓范的學(xué)生成績排序輸出
select * from student where name like '范%' order by math+chinese+english desc;
select name, math+chinese+english from student where name like '范%' order by math+chinese+english desc;
統(tǒng)計一個班級共有多少學(xué)生第喳？
select count(*) from student;
select count(*) as 總?cè)藬?shù) from student;
統(tǒng)計數(shù)學(xué)成績大于90的學(xué)生有多少個？
select count(*) from student where math>90;
統(tǒng)計總分大于250的人數(shù)有多少踱稍？
select count(*) from student where math+chinese+english>250;
統(tǒng)計一個班級數(shù)學(xué)總成績曲饱？
select sum(math) from student;
統(tǒng)計一個班級語文悠抹、英語、數(shù)學(xué)各科的總成績
select sum(math), sum(chinese), sum(english) from student;
統(tǒng)計一個班級語文扩淀、英語楔敌、數(shù)學(xué)的成績總和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;
求一個班級數(shù)學(xué)平均分？
select avg(math) from student;
求一個班級總分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;
求班級最高分和最低分
select max(math+chinese+english),min(math+chinese+english) from student;
分組數(shù)據(jù)
為學(xué)生表驻谆，增加一個班級列卵凑，練習(xí)分組查詢。
alter table student add column class_id int;
注意語法：Oracle中不能有“column”關(guān)鍵字胜臊，MySQL中有沒有“column”都可以執(zhí)行勺卢。
更新表：
update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;
(同update student set class_id=2 where id between 6 and 10;)
查出各個班的總分，最高分区端。
select class_id,sum(chinese+english+math),max(chinese+english+math) from student group by class_id;
求各個班級 英語的平均分：
select class_id, avg(english) from student group by class_id;
如根據(jù)組函數(shù)的語法要求值漫，將select后增加name列，而不加至group by 之后：
select name, class_id, avg(english) from student group by class_id;
會報錯织盼，因為學(xué)生的name都是各不相同的杨何，理論應(yīng)生成學(xué)生個數(shù)的行，但按班級分組沥邻，只能分兩個班級危虱。
查詢出班級總分大于1300分的班級ID
select class_id from student group by class_id having sum(math+chinese+english)>1300;（正常運行）
select class_id from student where sum(math+chinese+english)>1300 group by class_id ;（會報錯）
對于組函數(shù)的應(yīng)用與Oracle類似，可以應(yīng)用于Having中唐全，但不能用于where子句中埃跷。
