mysql第四章单表查询笔记_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;