从零开始学SQL：where条件查询与连接

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

文章目录

Python 进阶篇-系列文章全篇
1. 练习强化
2. where之比较运算
3. where之逻辑运算
4. where之模糊查询
5. where之范围查询
6. where之空值判断
7. order排序
8. 聚合函数
9. group分组
10. limit限制记录
11. 连接
13. 自连接
13. 子查询
14. 分页

1. 练习强化

查询

查询所有字段：select * from 表名;
查询部分字段：select 字段1, 字段2… from 表名;
查询字段起别名：

select 字段 as ‘别名‘ from 表名;
select 字段 ‘别名‘ from 表名;

查询，给表起别名：

select 表别名.表字段名,… from 表名 as 别名;
select s.id, s.name from students as s;

去除重复 distinct 去除重复

select distinct 字段名 from 表名;

2. where之比较运算

select * from where age > 18 ; # 所有年龄大于18岁的学生

select * from where age < 18 ; # 所有年龄小于18岁的学生

>=或者 <=

select * from where age >= 18 ; # 所有年龄大于等于18岁的学生

select * from where age = 18 ; # 所有年龄等于18岁的学生

!= 或者 <>

select * from where age != 18 ; # 所有年龄不等于18岁的学生

3. where之逻辑运算

-- and (当有多个条件的时候，表示多个条件必须同时成立)
    -- 18和28之间的所有学生信息
    select * from students where age > 18 and age <=28;
    失败select * from students where age>18 and <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='女';

4. where之模糊查询

like 表示模糊查询

% 表示任意0个或多个
_ 任意一个字符

-- 模糊查询(where name like 要查询的数据)
    -- like 
    -- % 替换任意个
    -- _ 替换1个
    -- 查询姓名中 以 "小" 开始的名字
    select * from students where name like '小%';

    

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

    

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


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


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

5. where之范围查询

in 用于非连续的范围的查询

相等于多个值的 or （或）关系

between 起始 and 终值

连续，包含起始值和终值，是一种 and(与) 关系

-- in (1, 3, 8)表示在一个非连续的范围内
    -- 查询 年龄为18、34的姓名
    select name from students where age = 18 or age = 34;
    select name from students where age in (18,34);

    -- not in 不非连续的范围之内
    -- 年龄不是 18、34岁的信息
    select name from students where age not in (18,34);

    (注意)select name from students where not age in (18,34);

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

6. where之空值判断

is null 表示判断值为空
is not null 判断值非空，错误写法：not is null

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

    
    -- 判非空is not null
    select * from students where height is not null;


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

where 条件判断的总结：

比较运算：　　　> < >= <= = != <>
逻辑运算符： and or not
模糊查询： like

% 任意0个或多个字符串
_ 任意1个字符

范围：

连续 between xxx and xxx
不连续 in (值1，值2，…)

空值判断：

is null 判断为空
is not null 判断非空

7. order排序

排序：order by 排序字段1 排序规则, 字段2 规则2,…
排序规则：

升序

默认， asc

降序

desc

-- order by 字段
  -- asc从小到大排列，即升序
  -- desc从大到小排序，即降序
  
  -- 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序(默认是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 多个字段
  -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从大到小排序
  select * from students where age between 18 and 34 and gender='女' order by height desc, age desc;



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

8. 聚合函数

聚合函数：聚合函数会对当前所在表当做一个组进行统计
常见的聚合函数：

count(*) 统计数量
max(字段) 找出该字段的最大值
min(字段) 找出该字段的最小值
sum(字段) 对该字段求和
avg(字段) 对该字段求平均值

四舍五入的函数:
round(数值,保留的小数位数)

-- 聚合函数
  -- 总数
  -- count
  -- 计算班级学生的总数
  select count(*) from students;
  select count(*) '总人数' from students;

  -- 查询男性有多少人，女性有多少人
  select count(*) from students where gender = '男';
  select count(*) from students where gender = '女';


  -- 最大值
  -- max
  -- 查询最大的年龄
  select max(age) from students;
  select max(age) from students where gender='女';


  -- 查询女性的最高 身高
  select max(height) from students where gender='女';


  -- 最小值
  -- min
  select min(height) from students where gender='女';


  
  -- 求和
  -- sum
  -- 计算所有人的年龄总和
  select sum(age) from students;

  
  -- 平均值
  -- avg
  -- 计算平均年龄
  select avg(age) from students;



  -- 计算平均年龄 sum(age)/count(*)
  select sum(age)/count(*) from students;


  -- 四舍五入 round(123.23 , 1) 保留1位小数
  -- 计算所有人的平均年龄，保留2位小数
  select round(avg(age),2) from students;


  -- 计算男性的平均身高 保留2位小数
  select round(avg(height),2)  from students where gender = '男';

9. group分组

group by 用于分组（按照某个特定的字段进行分类）

group by 字段名

group by + 聚合函数，分组统计/计算
group by + group_concat() 分组 + 内容链接为一个字符串
group by + with rollup 分组 + 小计

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


  
  select name,gender from students group by gender;错误
  -- select name,gender from students group by gender;
  -- 失败select * from students group by gender;

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

  -- 计算每个年龄中的人数
  select age, count(*)  from students group by age;

  -- 查询 男、女性别中年龄的最大值
  select gender, max(age) from students where gender='男' or gender='女' group by gender; 



  -- group_concat(...)
  -- 查询同种性别中的姓名
  select gender, group_concat(name) from students group by gender;

  
  -- 查询每组性别的平均年龄
  select gender, avg(age) from students group by gender;


  
  -- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30(重点)
  select gender, avg(age) from students group by gender having avg(age) > 30;

  select gender, avg(age) av from students group by gender having av > 30;


  
  -- 查询每种性别的平均年龄和名字
  select gender, avg(age), group_concat(name) from students group by gender;

  
  -- 查询每种性别中的人数多于2个的性别和姓名（重点）
  select gender, count(*) c, group_concat(name)  from students group by gender having c > 2;


  -- with rollup 汇总的作用(了解)
  select gender, count(*) from students group by gender with rollup;

注意点：分组的字段要出现在 select 的后面

10. limit限制记录

limit 数据有很多，只取指定数量的数据

表中的数据，位置默认从 0 开始

limit 使用格式： limit 起始位置, 连续取的数目;
limit 一定要写到 SQL 语句的最后面

标准的SQL书写格式：

select 字段1,字段2,...
from  表名
[where 条件]
[group by 字段名]
[order by 字段名 排序规则]
[having 条件]
[limit 起始位置,数量]

11. 连接

内连接：查询的结果为两个表匹配到的数据，默认是笛卡尔积

关键字 inner join
select 字段 from 表1 inner join 表2 where/on 表1.字段 = 表2.字段

外连接

左外连接主表 left join 从表

左外连接另外一个表，在从表中没有找到匹配，右侧补 NULL

右外连接从表 right join 主表

右外连接一个表，在从表中没有找到匹配，左侧补 NULL

-- inner join ... on
  -- select ... from 表A inner join 表B;
  select * from students inner join classes;


  -- 查询 有能够对应班级的学生以及班级信息
  select * from students inner join classes where students.cls_id = classes.id;

  -- 按照要求显示姓名、班级
  select students.name, classes.name  from students inner join classes where students.cls_id = classes.id;


  -- 给数据表起名字
  select s.name, c.name  from students s inner join classes c where s.cls_id = c.id;

  -- 内连接的另外一种写法
  select s.name, c.name  from students s, classes c where s.cls_id = c.id;



  -- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
  select students.*, classes.name from students inner join classes on students.cls_id = classes.id;

    
  -- 在以上的查询中，将班级名称显示在第1列
  select classes.name,students.* from students inner join classes on students.cls_id = classes.id;


  -- 查询 有能够对应班级的学生以及班级信息, 按照班级编号进行排序
  -- select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....;
  select * from students inner join classes on students.cls_id = classes.id order by classes.id ;

  
  -- 当时同一个班级的时候，按照学生的id进行从小到大排序
  select * from students inner join classes on students.cls_id = classes.id order by classes.id, students.id ;



  -- left join ....on 
  -- 查询每位学生对应的班级信息
  select * from students left join classes on students.cls_id = classes.id ;


  
  -- select * from students right join classes on students.cls_id = classes.id;

  -- 查询没有对应班级信息的学生
  -- select ... from xxx as s left join xxx as c on..... where .....
  -- select ... from xxx as s left join xxx as c on..... having .....
  select students.*  from students left join classes on students.cls_id = classes.id where classes.id is null;

  
  (注意)不建议使用 select * from students left join classes on students.cls_id=classes.id having classes.id is null;
  
  -- right join   on
  -- 将数据表名字互换位置，用left join完成
  select * from students right join classes on students.cls_id = classes.id;

13. 自连接

自连接：特殊的内连接

-- 1、查询一共有多少个省
  select count(*) from areas where pid is null;


  -- 2、查询省的名称为“山西省”的所有城市
  select * from areas city inner join areas province on city.pid = province.aid where province.atitle='山西省';



  -- 3、查询省的名称为“广州市”的所有县
  select * from areas city inner join areas province on city.pid = province.aid where province.atitle='广州市';

13. 子查询

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

标量子查询：子查询返回的是 1行1列
列子查询：子查询返回的是1列多行
行子查询：子查询返回 1行多列
表子查询：子查询返回多行多列

-- 子查询
  -- 标量子查询: 子查询返回的结果是一个数据(一行一列)
  -- 列子查询: 返回的结果是一列(一列多行)
  -- 行子查询: 返回的结果是一行(一行多列)
  
  -- 查询出高于平均身高的信息(height)
  -- 1 查出平均身高
  select avg(height) from students;

  

  -- 2 查出高于平均身高的信息
  select * from students where height > (select avg(height) from students);


  
  -- 查询学生的班级号能够对应的 学生名字
  -- select name from students where cls_id in (select id from classes);
  -- 1 查出所有的班级id
  select id from classes;



  -- 2 查出能够对应上班级号的学生信息
  select * from students where id in (select id from classes);