本文共 2114 字，大约阅读时间需要 7 分钟。

一、select的使用

#创建学生表CREATE TABLE student(  id INT NOT NULL DEFAULT 1,  `name` VARCHAR(32) NOT NULL DEFAULT'',  chinese FLOAT NOT NULL DEFAULT 0.0,  english FLOAT NOT NULL DEFAULT 0.0,  math FLOAT NOT NULL DEFAULT 0.0);#添加数据INSERT INTO student(id,`name`,chinese,english,math)VALUES(1,'小王',65,64,89);INSERT INTO student(id,`name`,chinese,english,math)VALUES(2,'小李',55,66,77);INSERT INTO student(id,`name`,chinese,english,math)VALUES(3,'小丽',86,88,78);INSERT INTO student(id,`name`,chinese,english,math)VALUES(4,'小明',64,76,77);INSERT INTO student(id,`name`,chinese,english,math)VALUES(5,'小红',88,99,32);INSERT INTO student(id,`name`,chinese,english,math)VALUES(6,'小亮',58,60,20);INSERT INTO student(id,`name`,chinese,english,math)VALUES(7,'小凡',63,23,78);INSERT INTO student(id,`name`,chinese,english,math)VALUES(8,'小美',55,20,78);#查询表中所有的学生的信息SELECT*FROM student#查询表中所有学生名字和数学成绩SELECT `name`,math FROM student;#过滤掉表中重复数据SELECT DISTINCT math FROM student;#查询记录，每个字段相同才去重SELECT `name`,math FROM student

#统计每个学生的总分SELECT `name`,(chinese+english+math)FROM student;#统计每个学生的总分并加50分SELECT `name`,(chinese+english+math+50)FROM student;#使用别名表示SELECT `name`AS'名字',(chinese+english+math+50)AS '总成绩'FROM student;

#查询小王的成绩SELECT*FROM student WHERE `name`='小王';#查询数学成绩大于70分的SELECT*FROM student WHERE math>70;#查询总成绩大于210分的SELECT*FROM student WHERE (chinese+english+math)>210#查询英语成绩大于70，id大于3的同学SELECT*FROM student WHERE english>70 AND id>3#查询语文成绩大于数学成绩的SELECT*FROM student WHERE chinese>math#查询总成绩大于200 ，语文成绩小于数学成绩的，姓小的同学SELECT*FROM student WHERE (chinese+english+math)>200 AND chinese
   
    = 60 AND math<=80SELECT*FROM student WHERE math BETWEEN 60 AND 80#查询语文成绩为55，63，64的SELECT*FROM student WHERE chinese IN(55,63,64)#查询姓小的同学SELECT*FROM student WHERE `name` LIKE '小%'#查询英语成绩>60,数学成绩>60SELECT*FROM student WHERE english>60 AND math>60
   

# order by使用#对英语成绩排序后输出（升序）SELECT*FROM student ORDER BY english#对总分按从高到低的顺序输出SELECT `name`,(chinese+english+math)AS total_score FROM student ORDER BY total_score DESC;#对总分按从高到低的顺序输出,姓小的同学SELECT  `name`,(chinese+english+math) AS total_score FROM student WHERE `name` LIKE '小%'ORDER BY total_score ;