MySQL数据库–单表查询
-
区间查询:
between...and...
执行select * from student1 where age between 20 and 22;
-
模糊查询
like...
%表示任意字符 _表示单个字符。
查找名字第一个字为铁的学生的全部信息:select *from student1 where name like ‘铁%’;
查找名字第二个字为刚的学生的全部信息:select *from student1 where name like ‘_刚’;
-
正则表达式查询
rlike..
查询所有以a或b开头的学生的所有信息 select * from student1 where name rlike ‘1.*$’;
-
过滤重复项
distinct
查询学生的所有年龄select distinct age from student1;
-
有限列表
in... not in...
查询年龄是21或22岁的学生的信息:select * from student1 where age in (21,22);
查询年龄不是21或22岁的学生的信息:select * from student1 where age not in (21,22);
-
排序
order by asc /desc
按年龄升序查询所有学生的信息:select * from student1 order by age asc;
按年龄降序查询所有学生的信息:select * from student1 order by age desc;
-
分组
group by
select age,name,sex from student1 group by sex;
显示
限定查询结果条数
select 列名 from 表名 [where..] [ limit n] [offset m];
lmit n:返回n条记录 offset m:跳过m条记录
offset 不可单独使用不
select 列名 from 表名 【where..】 limit m,n;
limit m,n 相当于 limit n offset m
-
结果别名显示
as
select name as stu_name from student1;
- 过滤
select count(age) as num,age from student1 group by age having num>1;**
ab ↩︎