MySQL单表查询

MySQL单表查询

1、查询所有字段

SELECT * FROM grade;

2、查询grade表中的id,firstname,lastname字段

SELECT id,firstname,lastname FROM grade;

3、查询grade表中id大于4的学生姓名

 SELECT firstname FROM grade WHERE id >4;

4、查询grade表中女生的记录

SELECT * FROM grade WHERE sex = 'f';

5、查询grade表中id值为2,4,6的学生记录

SELECT * FROM grade WHERE id = 2 OR id = 4 OR id = 6;

6、查询grade表中math成绩在85到94之间的记录

SELECT * FROM grade WHERE math > 85 AND math <94;

7、查询grade表中firstname以A开头的记录

SELECT * FROM grade WHERE firstname LIKE 'a%';

8、查询grade表中firstname以A开头以e结尾的记录

SELECT * FROM grade WHERE firstname LIKE '%e';

9、查询grade表中firstname包含l的记录

SELECT * FROM grade WHERE firstname LIKE '%l%';

10、查询grade表中firstname以A开头后面有4个字符的记录

SELECT * FROM grade WHERE firstname LIKE 'a';

11、查询grade表中english在80到90之间的记录

 SELECT * FROM grade WHERE english > 80 AND english < 90;

12、查询grade表中math大于90 或者 chinese大于90的记录

SELECT * FROM grade WHERE math > 90 or chinese > 90;

13、查询grade表中id不是1、3、5、7的记录

SELECT * FROM grade WHERE NOT (id = 1 or id = 3 or id = 5 or id = 7);

14、查询grade表中的性别有哪些

SELECT DISTINCT(sex) FROM grade;

15、查询grade表中的lastname有哪几种

SELECT DISTINCT(lastname) FROM grade;