1 单表查询语法结构
SELECT 字段1,字段2,... FROM 表名
where 条件
GROUP BY 字段名
HAVING 筛选条件
ORDER BY 字段名
LIMIT 查询条数
2 where 条件查询
单条件查询
SELECT * FROM student where name='jack';
AND多条件查询
SELECT id,name,age,sex FROM student where age=14 AND sex='male';
BETWEENT AND关键字查询
SELECT name,age,sex FROM student where age BETWEEN 12 AND 16; 查询年龄12至16岁的学生信息。包含12和16岁。
IN关键字查询
SELECT * FROM student where age IN (12,14,16); 查询年龄为12或14或16的学生信息
空与非空查询
SELECT * FROM student where sex IS NOT NULL;
SELECT * FROM student where sex IS NULL;
SELECT * FROM student where sex='';
注意:判断某个字段是否为NULL要用关键字IS,不能用=,''空字符串不是NULL
关键字LIKE模糊查询
SELECT * FROM student where name like '%j%';
SELECT * FROM student where name like '_j_';
通配符%表示多个字符可以是0个,
通配符_表示1个字符
3 GROUP BY分组
分组是在where之后,对where的查询结果按照某个相同的字段进行分组。默认返回每一组的第一条记录。
SELECT * FROM student GROUP BY sex;
4 HAVING筛选
having主要是用来对分组后的记录进行筛选 ;
例如:有一个全国人民的工资表country,现在要查询平均工资大于5000的省份。
SELECT province,avg(salary) FROM country GROUP BY province HAVING avg(salary) >5000;
5 ORDER BY 排序
单列排序
SELECT * FROM student ORDER BY id; 按照id升序排列
SELECT * FROM student ORDER BY id ASC; 按照id升序排列
SELECT * FROM student ORDER BY id DESC; 按照id降序排列
多列排序
SELECT * FROM student ORDER BY name,id; 先按name排序,name相同则按照id排序
6 LIMIT限制查询的记录
SELECT * FROM student ORDER BY id LIMIT 10; 从第一条记录开始,查10条记录
SELECT * FROM student ORDER BY id LIMIT 0,10; 从第一条记录开始,查10条记录。第一条记录从0开始,
SELECT * FROM student ORDER BY id LIMIT 3,10; 从第4条记录开始,查10条记录。