MySQL查询进阶相关sql语句

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;    -- <=
-- 不等于有两种写法: !=  或 <>
select * from students where age != 18;    -- !=
select * from students where age <> 18;    -- <>

-- and
-- 18岁到28岁之间
select * from students where age > 18 and age < 28;  
-- 18岁以上的女性
select * from students where age > 18 and gender = "女"; 

-- or
-- 18岁以上或者身高超过180(包含)
select * from students where age > 18 or height >= 180;    

-- not
-- 不在 18岁以上的女性 这个范围内的信息
select * from students where not (age > 18 and gender = "女");   
-- 不是小于或者等于18岁的女性
select * from students where (not age <= 18) and gender = "女";

-- like 
-- % 替换1个或者多个
-- _ 替换1个
-- 查询姓名中以"小"开始的名字
select name from students where name like "小%";

-- 查询姓名中有"小"的名字
select name from students where name like "%小%";

-- 查询有2个字的名字
select name from students where name like "__";

-- 查询有3个字的名字
select name from students where name like "___";

-- 查询至少有2个字的名字
select name from students where name like "__%";	


-- rlike 正则
-- 查询以"周"开始的名字
select name from students where name rlike "^周.*";

-- 查询以"周"开始, "伦"结尾的名字
select name from students where name rlike "^周.*伦$";

-- in (12, 18, 34)  表示在一个集合内
-- 查询年龄为12, 18, 34的信息
select name, age from students where age = 18 or age = 34 or age = 12;
select name, age from students where age in (12, 18, 34);

-- not in  表示不在一个集合内
-- 查询年龄不是12, 18, 34的信息
select name,age from students where age not in (12, 18, 34);

-- between ... and .. .表示在一个连续的范围内
-- 查询年龄在18到34之间的的信息
select name, age from students where age between 18 and 34;

-- not between ... and ...表示不在一个连续的范围内
-- 查询年龄不在在18到34之间的的信息
select * from students where age not between 18 and 34;

-- is null  判空
-- 查询身高为空的信息
select * from students where height is null;

-- is not null  判非空
-- 查询身高不为空的信息
select * from students where height is not null;

-- order by asc 或 order by desc
-- asc: 升序
-- desc: 降序


-- 查询年龄在18到34岁之间的男性，按照年龄从小到到排序    
select * from students where (age between 18 and 34) and gender = "男" order by age asc;
-- asc可省略, 默认下升序
select * from students where (age between 18 and 34) and gender = "男" order by age;

-- 查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34) and gender = "女" order by height desc;


-- order by 多个字段
-- 按照字段1进行排序，如果某些行字段1的值相同时，则按照字段2排序，以此类推...
-- select * from 表名 order by 字段1 asc | desc [, 字段2 asc | desc, ...]
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender = "女" order by height desc, id desc;

-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 
-- 如果年龄也相同那么按照id从大到小排序
select * from students where (age between 18 and 34) and gender = "女" order by height desc, age asc, id desc;
 
-- 按照年龄从小到大, 身高从高到矮的排序
select * from students order by age asc, height desc;

-- 总数 count(*)  括号中写信号或者字段名, 最终计算结果相同, 都是统计的查询后结果的行数
-- 查询学生总数
select count(*) from students;

-- 最大值 max(字段名)  求此字段的最大值
-- 查询女生的编号最大值
select max(id) from students where gender = "女";

-- 最小值 min(字段名)  求此字段的最小值
-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

-- 求和 sum(字段名)  求此字段值的总和
-- 查询男生的总年龄
select sum(age) from students where gender = "男";

-- 平均值 avg(字段名)  求此字段值的平均值
-- 查询未删除女生的编号平均值
select avg(id) from students where is_delete = 0 and gender = "女";
select sum(id) / count(*) from students where is_delete = 0 and gender = "女";

-- 四舍五入保留小数点n位 round(123.23, 1)   保留1位小数
-- 计算所有学生的平均年龄，保留2位小数
select round(sum(age) / count(*), 2) from students;

-- 按照性别分组, 查询所有的性别
select gender from students group by gender;

-- 计算每种性别中的人数
select gender, count(*) from students group by gender;

-- 计算男性的人数
select gender, count(*) from students where gender = "男" group by gender;


-- group by + group_concat()
-- group_concat(字段名)会作为一个字段输出, 用来显示每一组的某字段的值的集合
-- 查询同种性别中的名字
select gender, group_concat(name, " ", age, " ", id) from students group by gender; 

-- group by + having
-- having 条件表达式: 在分组查询后用特定的条件筛选出符合条件的分组
-- having作用和where一样, 但having只能用于group by, 且放在group by之后
-- 查询平均年龄超过30岁的分组, 显示其性别和该性别中所有名字
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;

-- 查询每种性别中的人数多于2个的信息
select gender, group_concat(name) from students group by gender having count(*) > 2;

-- limit start, count
-- 限制查询出来的数据个数   limit 2 显示查询后的前两条, 等价于 limit 0, 2
select * from students where gender = "男" limit 2;

-- 查询前5个数据
select * from students limit 0, 5;

-- 查询第6 - 10的数据
select * from students limit 5, 5;

-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
-- limit (第n页 - 1) * 每页的个数, 每页的个数
-- 但注意limit不支持表达式, 即这样的 limit 2 * (6-1), 2 是错误的表达式
select * from students order by age asc limit 10, 2;

-- 内连接查询  inner join ... on
-- 使用内连接查询班级表与学生表  
select * from students inner join classes on students.cls_id = classes.id;

-- 左连接查询  left join ... on
-- 使用左连接查询班级表与学生表
select * from students as s left join classes as c on s.cls_id = c.id;

-- 右连接查询  right join ... on
-- 使用右连接查询班级表与学生password表
select * from students as s right join classes as c on s.cls_id = c.id;

-- 查询省的名称为“浙江省”的所有城市
select c.* from areas as c inner join areas as p on c.pid = p.id where p.title = '浙江省';

-- 查询市的名称为“台州市”的所有区县
select dis.* from areas as dis inner join areas as c on c.id = dis.pid where c.title = '台州市';

-- 标量子查询: 子查询返回的结果是一个数据(一行一列)
-- 查询大于平均年龄的学生              (先查询平均年龄)
select * from students where age > (select avg(age) from students);

-- 列级子查询: 子查询返回的结果是一列(一列多行)
-- 查询还有学生在班的所有班级名字         (先在学生表中查找所有班级, 返回的是一列多行的班级id)
select name from classes where id in (select cls_id from students);

-- 行级子查询: 子查询返回的结果是一行(一行多列)
-- 查找班级年龄最大,身高最高的学生               (先查询最大的年龄和最高的身高, 返回的是一行两列: 身高, 年龄)
select * from students where (height, age) = (select max(height), max(age) from students);