MySQL学习三基础----最最常用查询操作-别名、模糊查询、范围查询、排序查询

本文链接：https://blog.csdn.net/qq_40433737/article/details/102878262

1）给表名起别名（as可省略）

select c.name from students as c;

在这里插入图片描述
2）给字段起别名（as可省略）

select name as "名字" from students as c;

在这里插入图片描述

注意：distinct后有多个字段时，多列数据完全相同时才会去重

select distinct name from students;

在这里插入图片描述

select * from students where age_age >= 18;

运算符<,>,<=,>=,!=或<>
逻辑运算符and,or

后面跟的是字符串, 带有特殊的字符(%: 表示任意字符可有可无(0个或者多个), _表示任意一个字符)
注意：%和_的区别：% 表示任意字符可代表字符可以不代表字符；
_表示占位符，即_就代表一个字符

select * from students where name like "__";
#查询结果是所有2个字符的名字

在这里插入图片描述

select * from students where name like "%%";
#查询结果是所有名字

在这里插入图片描述

1）in 表示在一个非连续的范围内

select * from students where age = 18 or age = 34 or xx or xx;
select * from students where age in (18, 34);

2）not in 不在非连续的范围之内

select * from students where age not in (18, 34);
select * from students where not age in (18, 34);

3）连续范围

select * from students where age > 18 and age < 34;

between … and …表示在一个连续的范围内两边都会包含
– 查询年龄在18到34之间的的信息

select * from students where age between 18 and 34;

4）为空判断
注意：不能使用=null，使用了不会报错，返回空

select * from students where height is null;

5）不为空判断

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

select * from students where height not is null; – 错误的，语法错误

1）升序

select *
from students
where age between 18 and 34 and gender = '男'
order by age asc;

2）降序

select *
from students
where age between 18 and 34 and gender = '女'
order by height desc;

3）两个条件排序

select * from students 
where age between 1 and 20 and gender = 1 
order by age,id desc;

4）三个条件排序，同理
–查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序

select * from students
where age between 18 and 34 and genser =2
order by height desc,age asc,id desc;