createtable student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
english int,
math int);
INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(1,'张明','男',89,78,90);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(2,'李进','男',67,53,95);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(3,'王五','女',87,78,77);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(4,'李一','女',88,98,92);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(5,'李财','男',82,84,67);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(6,'张宝','男',55,85,45);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(7,'黄蓉','女',75,65,30);INSERTINTO student(id,NAME,gender,chinese,english,math)VALUES(7,'黄蓉','女',75,65,30);-- 查询表中所有信息select*from student;-- 查询姓名对应的英语成绩select name,english from student;-- 过滤表中重复信息selectdistinct*from student;-- 统计每个学生的总分selectdistinct name,chinese+english+math sum_score from student;-- 给学生加10分特长分,并使用别名表示学生分数selectdistinct name,chinese+english+math+10 student_sum_score from student;-- 查询英语成绩大于90分的同学select name,english from student where english >90;-- 查询总分大于200的同学selectdistinct name,chinese + english + math student_score from student where chinese + english + math >200;-- 查询英语分数在80-90的同学。selectdistinct name,english from student where english >=80and english <=90;-- 查询英语分数不在80-90的同学selectdistinct name,english from student wherenot(english >=80and english <=90);-- 查询数学分数位89,90,91的同学selectdistinct name,math from student where math =89or math =90or math =91;-- 查询所有姓李的学生英语成绩selectdistinct name,english from student where name like'李%';-- 查询所数学语文都为80分的同学select name,math,chinese from student where math =80and chinese =80;-- 查询所英语为80分或总分为200的同学select name from student where english =80or(chinese + english + math)=200;-- 对数学成绩降序后输出select*from student orderby math desc;-- 对总分排序后输出从高到低select*from student orderby(chinese + english + math)desc;-- 对姓李的学生成绩排序输出select*from student where name like'李%'orderby(chinese + english + math)desc;-- 查询男生女生分别于多少人,并将人数降序输出,select gender,count(*)as num from student GROUPBY gender orderbycount(*)desc;