查询数据

一，单表查询

1.查询所有字段

 SELECT 字段1，字段2，字段3...FROM 表名； 
     SELECT  *  FROM 表名；

eg:

	SELECT id,stuName,age,sex,gradeName FROM t_student ;

	SELECT stuName,id,age,sex,gradeName FROM t_student ;

	SELECT * FROM t_student;

2,查询指定字段 

    SELECT 字段1，字段2，字段3...FROM  表名； 

eg:

	SELECT stuName,gradeName FROM t_student;

3, Where条件查询 

SELECT 字段1，字段2，字段3...FROM 表名  WHERE 条件表达式； 

eg:

	SELECT * FROM t_student WHERE id=1;
	SELECT * FROM t_student WHERE age>22;

4,带IN关键字查询 

SELECT 字段1，字段2，字段3...FROM 表名 WHERE 字段 [NOT] IN (元素1，元素2，元素3)； 

eg:

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

5，带BETWEEN  AND 的范围查询 

SELECT 字段1，字段2，字段3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值1AND 取值2； 

eg:

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

6，带LIKE 的模糊查询 

SELECT 字段 1，字段2，字段3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’； 

“%”代表任意字符； 
   “_” 代表单个字符； 

eg:

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

7，空值查询 

SELECT 字段1，字段2，字段3...FROM 表名 WHERE 字段 IS [NOT] NULL； 

eg:

	SELECT * FROM t_student WHERE sex IS NULL;
	SELECT * FROM t_student WHERE sex IS NOT NULL;

8，带AND 的多条件查询 

SELECT 字段 1，字段2...FROM 表名 WHERE 条件表达式1 AND 条件表达式2[...AND 条件表达式n] 

eg:

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

9,带OR 的多条件查询

SELECT 字段 1，字段2...FROM 表名 WHERE 条件表达式1OR 条件表达式2[...OR 条件表达式n] 

eg:

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

10, DISTINCT 去重复查询

SELECT  DISTINCT 字段名 FROM  表名； 

eg:

	SELECT DISTINCT gradeName FROM t_student;

11,对查询结果排序 

SELECT 字段1，字段2...FROM 表名 ORDERBY 属性名 [ASC|DESC] 

eg:

	SELECT * FROM t_student ORDER BY age ASC;
	SELECT * FROM t_student ORDER BY age DESC;

12 GROUPBY 分组查询 

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

1，单独使用(毫无意义)； 
    2，与GROUP_CONCAT()函数一起使用； 
    3，与聚合函数一起使用；
    4，与HAVING 一起使用(限制输出的结果)； 
    5，与WITHROLLUP一起使用(最后加入一个总和行)； 

eg:

	SELECT * FROM t_student GROUP BY gradeName;

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

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

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

	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 分页查询 

    SELECT 字段 1，字段2...FROM 表名 LIMIT 初始位置，记录数； 

eg:

           SELECT * FROM t_student LIMIT 0,5;
           SELECT * FROM t_student LIMIT 5,5;
           SELECT * FROM t_student LIMIT 10,5;