MySQL系统化学习(中)

本文书接上文https://www.tianchenyi.com/index.php/archives/416/

where条件查询

从这篇开始,正式进入到查询阶段,加油!
下面这个图片是本次使用的数据,可以作为参考

数据

as和distinct关键字

as关键字

在使用SQL语句的时候,往往屏幕显示的字段不具有良好的可读性,此时可以使用as给字段起一个别名

1.使用as给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;
2.可以通过as给表起别名
 -- 如果是单表查询可以省略表名
select id,name,gender from students; 
 -- 表名.字段名
select students.id, students.name,students.gender from students;
 -- 可以通过as给表起别名
select s.id,s.name,s.gender from students as s;

现在看起来只是方便了一点,但是在后期自连接的时候,必须要对表起别名

distinct关键字

distinct可以去除重复数据行,两个字段里要是有相同的,则只保留一份。

select distinct 列1,... from 表名;
例: 查询班级中学生的性别
select name, gender from students;
-- 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct
select distinct name, gender from students;

比较运算符

  • 1.where条件查询的介绍

where运算支持语句

  1. 比较运算符
  2. 逻辑运算符
  3. 模糊查询
  4. 范围查询
  5. 空判断

where条件查询语法格式如下

select * from 表名 where 条件;
例:
select * from students where id = 1;
  • 2.比较运算符查询

运算符

  1. 等于: =
  2. 大于: >
  3. 大于等于: >=
  4. 小于: <
  5. 小于等于: <=
  6. 不等于: != 或 <>

下面是一些案例

例1:查询编号大于3的学生:
select * from students where id > 3;

例2:查询编号不大于4的学生:
select * from students where id <= 4;

例3:查询姓名不是“黄蓉”的学生:
select * from students where name != '黄蓉';

例4:查询没被删除的学生:
select * from students where is_delete=0;

逻辑运算符

  1. and

  2. or

  3. not

    例1:查询编号大于3的女同学:
    select * from students where id > 3 and gender=0;

    例2:查询编号小于4或没被删除的学生:
    select * from students where id < 4 or is_delete=0;

    例3:查询年龄不在10岁到15岁之间的学生:
    select * from students where not (age >= 10 and age <= 15);

说明: 多个条件判断想要作为一个整体,可以结合‘()’,现在发现一般在取反的时候用括号。

模糊查询

  1. like是模糊查询关键字

  2. %表示任意多个任意字符

  3. _表示一个任意字符

    例1:查询姓黄的学生:
    select * from students where name like ‘黄%’;

    例2:查询姓黄并且“名”是一个字的学生:
    select * from students where name like ‘黄_’;

    例3:查询姓黄或叫靖的学生:
    select * from students where name like ‘黄%’ or name like ‘%靖’;

范围查询

  1. between .. and .. 表示在一个连续的范围内查询

  2. in 表示在一个非连续的范围内查询

    例1:查询编号为3至8的学生:
    select * from students where id between 3 and 8;

    例2:查询编号不是3至8的男生:
    select * from students where (not id between 3 and 8) and gender=’男’;

    例3:查询编号为3,5,7的学生
    slect * from students where id in (3,5,7);

空判断查询

  1. 判断为空使用: is null

  2. 判断非空使用: is not null

    例1:查询没有填写身高的学生:
    select * from students where height is null;

注意:

  1. 不能使用 where height = null 判断为空
  2. 不能使用 where height != null 判断非空
  3. null 不等于 ‘’ 空字符串

MySQL高级查询

这才是查询的精华,加油!!!

排序

排序查询语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

语法说明:

  1. 先按照列1进行排序,如果列1的值相同时,则按照 列2 排序,以此类推

  2. asc从小到大排列,即升序

  3. desc从大到小排序,即降序

  4. 默认按照列值从小到大排列(即asc关键字)

    例1:查询未删除男生信息,按学号降序:
    select * from students where gender=1 and is_delete=0 order by id desc;

    例2:显示所有的学生信息,先按照年龄从大–>小排序,当年龄相同时 按照身高从高–>矮排序:
    select * from students order by age desc,height desc;

可以给多个字段排序,前方相同时,后方再排序。

分页查询

  • 分页查询的介绍

当我们在京东购物,浏览商品列表的时候,由于数据特别多,一页显示不完,一页一页的进行显示,这就是分页查询

  • 分页查询的语法

    select * from 表名 limit start,count

说明:

  • limit是分页查询关键字

  • start表示开始行索引,默认是0

  • count表示查询条数

    例1:查询前3行男生信息:
    select * from students where gender=1 limit 0,3;
    简写
    select * from students where gender=1 limit 3;

  • 分页查询案例

    已知每页显示m条数据,求第n页显示的数据
    提示: 关键是求每页的开始行索引
    查询学生表,获取第n页数据的SQL语句:
    select * from students limit (n-1)*m,m;

聚合函数

聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计计算分组数据。
常用的聚合函数:

  • count(col): 表示求指定列的总行数

  • max(col): 表示求指定列的最大值

  • min(col): 表示求指定列的最小值

  • sum(col): 表示求指定列的

  • avg(col): 表示求指定列的平均值

    求总行数
    – 返回非NULL数据的总行数.
    select count(height) from students;

    – 返回总行数,包含null值记录;
    select count(*) from students;

    求最大值
    – 查询女生的编号最大值
    select max(id) from students where gender = 2;

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

    求和
    – 查询男生的总身高
    select sum(height) from students where gender = 1;

    – 平均身高
    select sum(height) / count(*) from students where gender = 1;

    求平均值
    – 求男生的平均身高, 聚合函数不统计null值,平均身高有误
    select avg(height) from students where gender = 1;

    – 求男生的平均身高, 包含身高是null的
    select avg(ifnull(height,0)) from students where gender = 1;
    说明
    ifnull函数: 表示判断指定字段的值是否为null,如果为空使用自己提供的值。

  • 聚合函数的特点

聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换。
聚合函数默认不对空值进行统计,就是空值不算。

分组查询

  • 分组查询介绍

分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组。
分组查询基本的语法格式如下:
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP:在所有记录的最后加上一条记录,显示select查询时聚合函数的统计和计算结果

PS:这里的思路可以灵活一些,比如说我们不知道性别一共有几项,可以select distinct gender from students;,可以通过这种方式看一下。

  • group by的使用

group by可用于单个字段分组,也可用于多个字段分组
指定了分组字段,查询的时候也只能用指定分组的字段。

-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;
  • group by + group_concat()的使用

group_concat(字段名):统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割

-- 根据gender字段进行分组, 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

group_concat(name)长这样

  • group by + 聚合函数的使用

    – 统计不同性别的人的平均年龄
    select gender,avg(age) from students group by gender;
    – 统计不同性别的人的个数
    select gender,count(*) from students group by gender;

  • group by + having的使用

having作用和where类似都是过滤数据的,但having是过滤分组数据的,只能用于group by。

-- 根据gender字段进行分组,统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;
  • group by + with rollup的使用

with rollup的作用是:在最后记录后面新增一行,显示select查询时聚合函数的统计和计算结果

-- 根据gender字段进行分组,汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组,汇总所有人的年龄(这个不是求和)
select gender,group_concat(age) from students group by gender with rollup;

效果是这样滴

  • 小结

group by 根据指定的一个或者多个字段对数据进行分组
group_concat(字段名)函数是统计每个分组指定字段的信息集合
聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
having 是对分组数据进行条件过滤
with rollup在最后记录后面新增一行,显示select查询时聚合函数的统计和计算结果

内连接查询

  • 连接查询的介绍

连接查询可以实现多个表的查询,当查询的字段数据来自不同的表就可以使用连接查询来完成。
连接查询可以分为:内连接查询左连接查询右连接查询自连接查询

  • 内连接查询

查询两个表中符合条件的共有记录
内连接查询效果图:

内连接查询

内连接查询语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

说明:
inner join 就是内连接查询关键字
on 就是连接查询条件

例1:使用内连接查询学生表与班级表:
select * from students as s inner join classes as c on s.cls_id = c.id;
  • 小结

内连接使用inner join .. on .., on 表示两个表的连接查询条件。
内连接根据连接查询条件取出两个表的 “交集”。

左连接查询

  • 左连接查询

左表为主根据条件查询右表数据,如果根据条件查询右表数据不存在使用null值填充。
左连接查询效果图:

左连接查询

左连接查询语法格式:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2;

说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表,表2 是右表

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

效果图

  • 小结

左连接使用left join .. on .., on 表示两个表的连接查询条件。
左连接以左表为主根据条件查询右表数据,右表数据不存在使用null值填充。
左表会把所有左表的数据展示出来。

右连接查询

  • 右连接查询

以右表为主根据条件查询左表数据,如果根据条件查询左表数据不存在使用null值填充
右连接查询效果图:

右连接查询

右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

说明:
right join 就是右连接查询关键字
on 就是连接查询条件
表1 是左表,表2 是右表

例1:使用右连接查询学生表与班级表:
select * from students as s right join classes as c on s.cls_id = c.id;
  • 小结

右连接使用right join .. on .., on 表示两个表的连接查询条件
右连接以右表为主根据条件查询左表数据,左表数据不存在使用null值填充。
相当于就是把左连接给反过来了。

自连接查询

  • 自连接查询

左表和右表是同一个表,根据连接查询条件查询两个表中的数据。

区域表效果图

区域表连表查询分析

例1:查询省的名称为“山西省”的所有城市
创建areas表:
create table areas(
    id varchar(30) not null primary key, 
    title varchar(30), 
    pid varchar(30)
);

执行sql文件给areas表导入数据:
source areas.sql;

说明:
source 表示执行的sql文件
自连接查询必须对表起别名

自连接查询的用法
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';
  • 小结

自连接查询就是把一张表模拟成左右两张表,然后进行连表查询。
自连接就是一种特殊的连接方式,连接的表还是本身这张表
这个相当于inner join on 自己一下,然后可以适用于一些特殊的情况。

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,外部那个select语句则称为主查询.

主查询和子查询的关系:

  1. 子查询是嵌入到主查询中

  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源

  3. 子查询是可以独立存在的语句,是一条完整的 select 语句

    例1. 查询大于平均年龄的学生:
    select * from students where age > (select avg(age) from students);

    例2. 查询学生在班的所有班级名字:
    select name from classes where id in (select cls_id from students where cls_id is not null);

    例3. 查找年龄最大,身高最高的学生:
    select * from students where (age, height) = (select max(age), max(height) from students);

子查询是一个完整的SQL语句,子查询被嵌入到一对小括号里面


小技巧:清屏,在mysql状态下输入system clear
当前进度:7-5-1-00:00