准备数据
- 创建数据表
create table students( id int primary key not null auto_increment, name varchar(20) default '', age tinyint unsigned default 0, height decimal(5,1), gender enum('男', '女', '其他', '保密') default '保密', cls_id int unsigned default 0, is_delete bit default 0 -> );
- 插入数据
insert into students values(0,'小明',18,180.0,2,1,0), (0,'小李',18,180.0,2,2,1), (0,'王杰',29,185.0,1,1,0), (0,'老张',59,175.0,1,2,1), (0,'小黄',38,160.0,1,2,1), (0,'敖缘凤',28,150.0,4,2,1), (0,'王祖贤',18,172.0,2,1,1), (0,'周杰伦',36,NULL,1,1,0), (0,'程坤',27,181.0,1,2,0), (0,'刘亦菲',25,166.0,2,2,0), (0,'金星',33,162.0,3,3,1), (0,'赵丽颖',12,180.0,2,4,0), (0,'郭靖',12,170.0,1,4,0), (0,'周杰',34,176.0,2,5,0);
- 查询时为表取名
select id,name from students as std;
- 查询并去除重复的数据
select distinct 字段 from 数据表;
select distinct gender from students;
- 比较运算符查询
select * from students where age<18;
select * from students where age<=18;
select * from students where age=18;
select * from students where age!=18;
- 逻辑运算符查询
select * from students where age>18 and age<30;
select * from students where age>18 or height>=180;
select * from students where not (age>18 and id>5);
- 模糊查询
- 查询以‘小’开头的
select * from students where name like ‘小%’;- 名字含‘小’的
select * from students where name like ‘%小%’;- 名字是两个字的
select * from students where name like ‘__’;- 名字是三个字的
select * from students where name like ‘___’;- 至少两个字的名字
select * from students where name like ‘__%’;- 正则表达
a. 以刘开头的名字
select * from students where name rlike ‘^刘.*’;
b. 含杰的名字
select * from students where name rlike ‘.杰.’;
c. 以杰结尾的名字
select * from students where name rlike ‘.*杰$’;
- 范围查询
- 非连续范围查询
select * from students where age in (34, 25, 12);
select * from students where age not in (34, 25, 12);- 连续范围查询
select * from students where age between 20 and 30;
select * from students where age not between 20 and 30; 不能使用not (between xx and xx)
- 判断空和非空
select * from students where height is null;
select * from students where height is not null;
- 查询并排序
- 升序(如果省略asc,默认为升序)
select * from students where age between 20 and 30 order by age asc;- 降序
select * from students where age between 20 and 30 order by age desc;- 多字段排序(相同值之间的排序)
按身高降序,当身高相同是按id降序
select * from students where (age between 18 and 34) and gender=‘女’ order by height desc,id desc;