insertinto student(id,name,chinese,english,math) values(6,'丁香',92,85,87);
insertinto student(id,name,chinese,english,math) values(7,'何北',75,81,80);
insertinto student(id,name,chinese,english,math) values(8,'唐娇',77,80,79);
insertinto student(id,name,chinese,english,math) values(9,'任知了',95,85,85);
insertinto student(id,name,chinese,english,math) values(10,'王越',94,85,84);
查询表中所有学生的信息。
select* from student;
查询表中所有学生的姓名和对应的英语成绩。
selectname,english from student;
过滤表中重复数据。
selectenglish from student;
selectDISTINCT english from student;
selectDISTINCT english,name from student;
selectenglish+chinese+math from student;
selectenglish+chinese+math as 总分 from student;
selectname,english+chinese+math as 总分 from student;
在所有学生英语分数上加10分特长分。
selectname,english+10 from student;
统计每个学生的总分。
selectenglish+chinese+math from student;
使用别名表示学生分数
selectname,english+chinese+math as 总分 from student;
selectname,english+chinese+math 总分 from student;
查询姓名为何东的学生成绩
select* from student where name='何东';
查询英语成绩大于90分的同学
select* from student where english>90;
查询总分大于250分的所有同学
select* from student where english+chinese+math>250;
查询英语分数在 85－95之间的同学。
select* from student where english>=85 and english<=95;
select* from student where english between 85 and 95;
查询数学分数为84,90,91的同学。
select* from student where math=84 or math=90 or math=91;
select* from student where math in(84,90,91);
查询所有姓何的学生成绩。
select* from student where name like '何%';
查询数学分>85，语文分>90的同学。
select* from student where math>85 and chinese>90;
对数学成绩排序后输出。
select* from student order by math;
对总分排序后输出，然后再按从高到低的顺序输出
select* from student order by math+chinese+english desc;
对姓何的学生成绩排序输出
select* from student where name like '何%' order by math+chinese+english desc;
selectname, math+chinese+english from student where name like '何%' order bymath+chinese+english desc;
统计一个班级共有多少学生？
selectcount(*) from student;
统计数学成绩大于90的学生有多少个？
selectcount(*) from student where math>90;
统计总分大于250的人数有多少？
selectcount(*) from student where math+chinese+english>250;
统计一个班级数学总成绩？
selectsum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
selectsum(math), sum(chinese), sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
selectsum(math+chinese+english)from student;
selectsum(math)+sum(chinese)+sum(english) from student;
求一个班级数学平均分？
selectavg(math) from student;
求一个班级总分平均分
selectavg(math+chinese+english)from student;
selectavg(math)+avg(chinese)+avg(english) from student;
求班级最高分和最低分
selectmax(math+chinese+english),min(math+chinese+english) from student;
7 综合性练习：为学生表，增加一个班级列，然后训练分组查询
查出各个班的总分，最高分
准备环境
给表添加一个字段：altertable student add column class_id int;
更新表：
updatestudent set class_id=1 where id<=5;
updatestudent set class_id=2 where id>5;
selectsum(math+chinese+english),max(math+chinese+english) from student group byclass_id;
查询出班级总分大于1300分的班级ID
selectclass_id from student group by class_id havingsum(math+chinese+english)>1300;
selectclass_id from student where sum(math+chinese+english)>1300 group by class_id;
note:where和group区别: 在wehre子句中不能使用分组函数
8 时间和日期
mysql>select year (now()), month(now()), day(now()) , date(now());
+--------------+--------------+------------+-------------+
|year (now()) | month(now()) | day(now()) | date(now()) |
+--------------+--------------+------------+-------------+
| 2014 | 9 | 7 | 2014-09-07 |
+--------------+--------------+-