SQL基本操作（2）--单表查询

1、查询所有字段

SELECT * FROM t_student;

2、查询指定字段

SELECT stuName,gradeName FROM t_student;

3、Where条件查询

SELECT * FROM t_student WHERE id=1;

SELECT * FROM t_student WHERE age>22;

4、带IN关键字查询

SELECT * FROM t_student WHERE age IN (21,23);

SELECT * FROM t_student WHERE age NOT IN (21,23);

5、带BETWEEN AND的范围查询

SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;

SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;

6、带LIKE的模糊查询

“%”代表任意字符；

“_” 代表单个字符；

SELECT * FROM t_student WHERE stuName LIKE '张三'; 张三

SELECT * FROM t_student WHERE stuName LIKE '张三%';张三，张三X, 张三XX

SELECT * FROM t_student WHERE stuName LIKE '张三__'; 张三X

SELECT * FROM t_student WHERE stuName LIKE '%张三%'; 只要有张三的

7、空值查询

SELECT * FROM t_student WHERE sex IS NULL;

SELECT * FROM t_student WHERE sex IS NOT NULL;

8、带AND的多条件查询

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23

9、带OR的多条件查询

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23

10、DISTINCT去重复查询

SELECT DISTINCT gradeName FROM t_student;

11、对查询结果排序

SELECT * FROM t_student ORDER BY age ASC;

SELECT * FROM t_student ORDER BY age DESC;

12、GROUP BY分组查询

GROUP BY属性名[HAVING条件表达式][WITH ROLLUP]

（1）单独使用(毫无意义)；

SELECT * FROM t_student GROUP BY gradeName;  false 

（2）与GROUP_CONCAT()函数一起使用；

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

（3）与聚合函数一起使用；

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

（4）与HAVING一起使用(限制输出的结果)；

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

（5）与WITH ROLLUP一起使用(最后加入一个总和行)；

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

13、LIMIT分页查询

LIMIT初始位置，记录数；

SELECT * FROM t_student LIMIT 0,5;

SELECT * FROM t_student LIMIT 5,5;

SELECT * FROM t_student LIMIT 10,5;