MySQL基础笔记之单表查询

1）基本查询

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

SELECT * FROM t_student;

SELECT stuName,gradeName FROM t_student;

2）当满足某一条件的查询，主要利用WHERE

　　1.基本条件

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

　　2.利用IN,也就是在满足（）内的情况

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

　　3.利用BETWEEN  AND，在两者之间

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

　　4.利用LIKE，进行模糊查询

　　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 ‘张三__‘;#名字只有四个字，前两个是张三

　　5.利用IS，也就是仅在某一条件下才成立

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

　　6.利用AND以及OR表示且以及或关系查询

　　SELECT * FROM t_student WHERE gradeName=‘一年级‘ AND age=23;
　　SELECT * FROM t_student WHERE gradeName=‘一年级‘ OR age=23;

3）去重查询，对于重复的删除

SELECT DISTINCT gradeName FROM t_student;

4）利用ORDER BY进行排序查询，ASC从小到大，DESC从大到小

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

５）利用GROUP BY进行相同类型分组的查询，一般与GROUP_CONCAT，COUNT，以及 HAVING 和 WITH ROLLUP一同使用

SELECT * FROM t_student GROUP BY gradeName;

#相当于找到每个分类的第一个元素，几乎无意义
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

#将每个分类的每一个元素都放在一起，隶属于GROUP_CONCAT(stuName) 这个属性
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

#将每个分类的每一个元素都放在一起，隶属GROUP_CONCAT(stuName) 这个属性，但注意COUNT(stuName)>3
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

#每个属性最下方额外增加一栏，这一栏的内容是该属性下其余元素之和

6）利用LIMIT进行分页查询

SELECT * FROM t_student LIMIT 0,5;#从第0个元素开始（不包括），后面的5个元素列成一张表
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;