跳转至

4. 查询

匹配空和非空

空: is null
非空: is not null

练习

1. 查找姓名为NULL的蜀国男英雄的信息
     select * from sanguo
     where name is null and country='蜀国' and sex='男';

2. 查找姓名为''的英雄的信息
     select * from sanguo
     where name='';

3. 在所有蜀国英雄中查找攻击力大于150的并且名字不为NULL的英雄的姓名、攻击值和国家
    select name,gongji,country from sanguo
    where country='蜀国' and gongji>150 and name is not null;

4. 查找魏蜀两国英雄中攻击力小于200并且防御力小于80的英雄信息
    select * from sanguo
    where country in ('蜀国', '魏国') and gongji<200 and fangyu<80;

空值和空字符串的区别

  1. null:空值, 只能用is, is not 去匹配
  2. '': 空字符串, 只能用= != 去匹配

模糊查询(like)

  1. where 字段名 like 表达式
  2. 表达式
    _ : 匹配单个字符
    % : 匹配0到多个字符
  3. 练习
    select name from sanguo where name like '_%_';  # 匹配 name 有两个字符以上的
    select name from sanguo where name like '%';    # 匹配所有不包括null
    select name from sanguo where name like '___';  # 匹配 name 有三个字符的
    select name from sanguo where name like '赵%';  # 匹配name 赵开头的
    

SQL高级查询

select ...聚合函数 from 表名
where ...
group by...
having ...
order by...
limit ...

排序 order by

作用
给查询结果进行排序
语法

order by 字段名 ASC/DESC  
ASC(升序)  
DESC(降序)  

示例

1. 将所有英雄按防御值从高到低排序
    select * from sanguo
    order by fangyu DESC;

2. 将蜀国英雄按攻击力从高到低排序
    select * from sanguo
    where country='蜀国'
    order by gongji DESC;

3. 将魏蜀两国英雄中名字为3个字的,按防御力升序排序
    select * from sanguo
    where country in ('魏国', '蜀国') and name like '___'
    order by fangyu ASC;

限制显示查询的条数 limit

(永远放在SQL命令的最后写)
1. 显示查询的条数
2. 用法

limit n;  -->显示n条记录
limit m, n; --> 从第m+1条开始显示, n条
limit 2, 3: 显示3 4 5 三条记录
3. 练习
1. 在蜀国英雄中,查找防御值倒数第二名到倒数第四名英雄的姓名,防御值,国家
    select name, fangyu, country from sanguo
    where country='蜀国'
    order by fangyu ASC
    limit 1,3;

2. 在所有蜀国名字不为null英雄中,查找攻击值前3名的英雄的姓名,攻击值和国家
    select name,gongji,country from sanguo
    where country='蜀国' and name is not null
    order by gongji DESC
    limit 3;

MySQL里的分页
每页显示5条记录,显示第四页的记录
limit 15, 5;

第一页: limit 0,5;  # 1, 2, 3, 4, 5
第二页: limit 5,5;  # 6, 7, 8, 9, 10
第三页: limit 10,5;  # 11, 12, 13, 14, 15
第四页: limit 15,5;  # 16, 17, 18, 19, 20
...
每页显示n条记录 显示第m页的内容
limit (m-1)*n, n;

聚合查询

聚合函数

分类

avg(字段名)  # 求该字段所有值的平均值
sum(字段名)  # 求该字段所有值的和
max(字段名)  # 求该字段所有值的最大值
min(字段名)  # 求该字段所有值的最小值
count(字段名)  # 求该字段所有记录条数
练习
1. 所有英雄中攻击力最大值
    select max(gongji) [as '最大攻击'] from sanguo;

2. 统计id, name 两个字段分别有多少条记录
    select count(id), count(name) from sanguo;

group by分组

给查询的结果分组

1. 查询表中都有哪些国家
    select country from sanguo
    group by country;

2. 计算每个国家的平均攻击力
    select country,avg(gongji) from sanguo
    group by country;

    分组  聚合  去重
    注意: select 之后的字段名如果没有在group by之后出现,则必须要对该字段进行聚合处理(聚合函数)

3. 查找所有国家中英雄数量最多的前两名国家名称和英雄数量
    select country as '国家',count(id) as '英雄数量' from sanguo
    group by country
    order by count(country) DESC
    limit 2;

having语句

作用: 对查询的结果进行进一步的筛选
练习

找出平均攻击力大于105的国家的前两名显示国家名和平均攻击力
    select country,avg(gongji) from sanguo
    group by country
    having avg(gongji) > 105
    order by avg(gongji) DESC
    limit 2;
总结
1. having 语句通常和group by 语句联合使用,过滤又group by 语句返回的记录集
2. where 关键字只能操作表中实际存在的字段, having 语句可操作由聚合函数生成的显示列

distinct

不显示字段的重复值

select distinct 字段1,字段2 from 表名
练习
表中有哪些国家
    select ditinct country from sanguo;

查询表中一共有几个国家
    select count(distinct country) as '国家总数' from sanguo;
注意:
    distinct  from 之间的所有字段打值都相同才会去重

查询表记录时可以做数学运算

运算符
+ - * / %
练习

查询时显示所有英雄攻击力翻倍
    select id, name, gongji*2,country from sanguo;
问??   只让蜀国的战力翻倍

嵌套查询

(子查询)

定义: 把内层的查询结果作为外层的查询条件
语法

select ...from 表名 where 字段名 运算符 
                                        (select ...from 表名 where 条件)

练习

1/ 把攻击值小于平均攻击值的名字和攻击值显示出来
 select name,gongji from sanguo
 where gongji <
 (select avg(gongji) from sanguo);

2/ 找出每个国家中攻击力最高的英雄的名字和攻击值
    select name, gongji from sanguo
    where (country, gongji) in
    (select country, max(gongji) from sanguo
    group by country);

多表联合查询

  1. 迪卡尔积: 不加where 条件
    select ...from 表1, 表2;
    记录多的表的每一条记录去匹配另一张表的所有记录,
    如果两张表记录相同则后表的每一条记录去匹配前表的所有记录

  2. 加where 条件
    select ... from 表1, 表2 where 条件;

练习

1. 显示省和市的详细信息
   select sheng.s_name, city.c_name from sheng, city
   where sheng.s_id = city.cfather_id;

2. 显示省市县的详细信息
    select sheng.s_name, city.c_name, xian.x_name from sheng,city,xian
    where xian.xfather_id = city.c_id and city.cfather_id = sheng.s_id;

连接查询

内连接(inner join)

语法格式

select 字段名1,... from 1
inner join 2 on 条件
inner join 3 on 条件;
练习
1. 显示省和市的详细信息(只显示匹配到的)
    select sheng.s_name, city.c_name from sheng
    inner join city on sheng.s_id = city.cfather_id;

2. 显示省市县的详细信息
    select sheng.s_name, city.c_name, xian.x_name from sheng
    inner join city on city.cfather_id = sheng.s_id
    inner join xian on xian.xfather_id = city.c_id;

外连接

  1. 左连接(left join) 特点: 以左表为主,显示查询结果
    语法格式
    select 字段名1... from 1
    left join 2 on 条件
    left join 3 on 条件;
    
    练习
    1. 显示省/市的详细信息, 要求省全部显示
        select sheng.s_name, city.c_name from sheng
        left join city on city.cfather_id = sheng.s_id;
    2. 显示省//县的详细信息, 要求省全部显示
        select sheng.s_name, city.c_name, xian.x_name from sheng
        left join city on sheng.s_id = city.cfather_id
        left join xian on city.c_id = xian.xfather_id;
    
        select sheng.s_name, city.c_name, xian.x_name from sheng
        right join city on sheng.s_id = city.cfather_id
        right join xian on city.c_id = xian.xfather_id;
    
  2. 右连接(right join)
    特点: 以右表为主,显示查询结果
    用法: 同左连接类似