MySQL的操作（三）：数据查询基础

准备数据

1. 创建数据表
   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 -> );
2. 插入数据
   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);

3. 查询时为表取名

select id,name from students as std;

4. 查询并去除重复的数据

select distinct 字段 from 数据表;
select distinct gender from students;

5. 比较运算符查询

select * from students where age<18;
select * from students where age<=18;
select * from students where age=18;
select * from students where age!=18;

6. 逻辑运算符查询

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);

7. 模糊查询

1. 查询以‘小’开头的
   select * from students where name like ‘小%’;
2. 名字含‘小’的
   select * from students where name like ‘%小%’;
3. 名字是两个字的
   select * from students where name like ‘__’;
4. 名字是三个字的
   select * from students where name like ‘___’;
5. 至少两个字的名字
   select * from students where name like ‘__%’;
6. 正则表达
   a. 以刘开头的名字
   select * from students where name rlike ‘^刘.*’;
   b. 含杰的名字
   select * from students where name rlike ‘.杰.’;
   c. 以杰结尾的名字
   select * from students where name rlike ‘.*杰$’;

8. 范围查询

1. 非连续范围查询
   select * from students where age in (34, 25, 12);
   select * from students where age not in (34, 25, 12);
2. 连续范围查询
   select * from students where age between 20 and 30;
   select * from students where age not between 20 and 30; 不能使用not (between xx and xx)

9. 判断空和非空

select * from students where height is null;
select * from students where height is not null;

10. 查询并排序

1. 升序（如果省略asc，默认为升序）
   select * from students where age between 20 and 30 order by age asc;
2. 降序
   select * from students where age between 20 and 30 order by age desc;
3. 多字段排序（相同值之间的排序）
   按身高降序，当身高相同是按id降序
   select * from students where (age between 18 and 34) and gender=‘女’ order by height desc,id desc;