例:select name,age from students where age in (18,19,25);
IS NULL #取值为空,IS NOT NULL: 取值不为空
like #%任意长度任意字符 _任意单个字符
RLIKE #使用正则表达式
GROUP #根据指定的条件把查询结果进行分组以用于做聚合运算
内置函数:avg() , max() , min() , count() , sum()
order by #根据指定字段对查询结果进行排序
升序:ASC(默认) 降序:DESC
LIMIT [[offset,]row_count] #对查询的结果进行输出行数数量的限制
对查询结果中的数据请求施加‘锁’:
FOR UPDATE :写锁,独占锁,排他锁
LOCK IN SHARE MODE :读锁,共享锁
例:查看男女同学的平均年龄
select avg(age),gender from students group by gender ; 例:查看平均年龄大于20的性别
select avg(age),gender as '年龄' from students group by gender having 年龄>20; 例:查看姓名,年龄以年龄倒序排序
select name,age from students order by age desc; 例:年龄从小到大查看排名11至20的同学的姓名
select name,age from students order by age limit 10,10 ; 练习题:
1. 在students表中,年龄大于25,且为男性的同学的姓名和年龄
select name,age from students where gender='m' and age>25; 2. 以classID为分组依据,显示每组的平均年龄
select avg(age),classID from students where classID is not null group by classID; 3. 显示第二题中平均年龄大于30的分组及平均年龄
select avg(age),classID from students group by classID having avg(age)>30; 4. 显示名字以L开头的同学的相关信息
select * from students where name like 'L%'; 5. 显示teacherID非空的同学的相关信息
select * from students where teacherID is not null; 6. 以年龄排序后显示年龄最大的前10位同学的信息
select * from students order by age DESC limit 10; 7. 查询年龄大于等于20岁,小于等于25岁的同学的信息,用三种方法
select * from students where age>=20 and age(select avg(age) from students); (2)用在IN中的子查询:子查询应该单键查询并返回一个或多个值构成列表
例:查找老师年龄和同学年龄相等的
select name,age from students where age in (select age from teachers); (3)用于EXISTS
用于from子句中的子查询:
例查找平均年龄是30的班级:
select s.aage,s.classID from (select avg(age) as aage,classID from students where classID is not null group by classID) as s where s.aage=30; 联合查询:把两个表查询的结果合并成一个。以前面表的字段为准,后面的表填充内容。
例:
select name,age from students UNION select name,age from teachers;