Python MySQL数据库2：数据库查询

总体内容

• 一、数据准备、基本的查询
• 二、条件查询
• 三、排序
• 四、聚合、分组
• 五、分页
• 六、连接查询(多表的时候有用，单表的时候几乎没有用)
• 七、自关联
• 八、子查询
• 九、总结

一、数据准备、基本的查询

• 1.1、创建数据库 (pythonTestDataBase: 数据库名)

  create database pythonTestDataBase charset=utf8;
  

创建数据库 (pythonTestDataBase: 数据库名)

• 1.2、 使用数据库（pythonTestDataBase）

  use pythonTestDataBase;
  

• 1.3、创建两个数据表 animalTable 与 personTable

  create table animalTable(
      id int unsigned primary key auto_increment not null,
      name varchar(20) default '',
      age tinyint unsigned default 0,
      gender enum('雄','雌','保密') default "保密",
      is_delete bit default 0
  );
  
  create table personTable(
      id int unsigned primary key auto_increment not null,
      name varchar(20) default '',
      gender enum('男','女','保密') default "保密",
      skinColor varchar(20) default '',
      is_delete bit default 0
  );
  

  创建两个表

• 1.4、查看表结构

  desc animalTable;
  desc personTable;
  

• 1.5、插入一些数据

  insert into animalTable values(0, "梅花鹿", 3,"保密",0),(0, "熊猫", 2, "雌",0),(0, "东北虎", 6,"雄",0);
  insert into personTable values(0, "小王","保密","黄",0),(0, "小李","男","黑",0),(0, "小杜","女","白",0);
  

创建好的数据

• 1.6、基本的查询

  • (1)、查询所有字段

    select * from 表名;
    

    例如：

    select * from animalTable;
    select * from personTable;
    select id, name from animalTable;
    

  • (2)、查询指定字段

    select 列1,列2,... from 表名;
    

    例如：

    select name,gender from personTable;
    

  • (3)、使用 as 给字段起别名

    select 字段 as 名字.... from 表名;
    

    例如：

    select name as 姓名, gender as 性别 from personTable;
    

    使用 as 给字段起别名

  • (4)、select 表名.字段 .... from 表名;

    select 表名.name, 表名.age from 表名;
    

    例如：

    select personTable.name, personTable.gender from personTable;
    

  • (5)、可以通过 as 给表起别名

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

    例如

    select s.name, s.gender from personTable as s;
    

  • (6)、消除重复行(distinct 字段)

    select distinct gender from personTable;
    

二、条件查询

personTable表
animalTable 表

• 2.1、比较运算符（使用 animalTable 表）

  • (1)、>

    // 查看大于 6 岁的动物
    select * from animalTable where age>6;
    select id,name,gender from animalTable where age>6;
    

  • (2)、<

    // 查看小于 6 岁的动物
    select * from animalTable where age<6;
    

  • (3)、=、>=、<=

     // 查看 等于、等于等于、小于等于 6 岁的动物
    select * from animalTable where age=6;
    select * from animalTable where age>=6;
    select * from animalTable where age<=6;
    

  • (4)、!=

    // 查看不等于 6 岁的动物
    select * from animalTable where age!=6;
    

• 2.2、逻辑运算符（使用 animalTable 表）

  • (1)、and

    // 3到12之间的动物信息
    select * from animalTable where age>3 and age<12;
    

  `and`

  • (2)、or

    // 6以上或者性别是保密的
    select * from animalTable where age>6 or gender="保密";
    

  • (3)、not

    // 不在 7岁以上的雄性 这个范围内的信息 
    select * from animalTable where not (age>7 and gender = "雄");
    

    不在 7岁以上的雄性 这个范围内的信息

    // 年龄不是小于或者等于7 并且是雄性
    select * from animalTable where  (not age <=7) and gender = "雄"；
    

    年龄不是小于或者等于7 并且是雄性

• 2.3、模糊查询（使用 personTable 表），效率比较低
  

  personTable表

  • (1)、like：% 替换1个或者多个、_ 替换1个、查询姓名中 以 "关键字名" 开始的名字

    • % 替换1个或者多个

      // 查询姓名中 有 "杰" 所有的名字
      select * from personTable where name like "%杰%";
      

      查询姓名中 有 "杰" 所有的名字

    • _ 替换1个

      // 查询姓名中 有姓 "周" 所有2个字的名字
      select * from personTable where name like "周_";
      // 查询姓名中 有姓 "周" 所有3个字的名字
      select * from personTable where name like "周__";
      // 查询至少有2个字的名字
      select name from personTable where name like "__%";
      

    • 查询姓名中 以 "关键字名" 开始的名字

      // 查询姓名中 以 "小" 开始的名字
      select name from personTable where name like "小%";
      

  • (2)、rlike 正则

    // 查询以 周开始的姓名
    select * from personTable where name rlike "^周.*";
    // 查询以 周开始、伦结尾的姓名
    select name from personTable where name rlike "^周.*伦$";
    

    查询以 周开始、伦结尾的姓名

• 2.4、范围查询、null（使用 personTable 表）
  

  personTable内容更新

  • in (1, 3, 8)表示在一个非连续的范围内

    // 查询 身高为为172、178 的姓名
    select * from personTable where height in (172, 178);
    

    查询 身高为为172、178 的姓名

  • not in 不 非连续 的范围之内

    // 查询 不是 身高为为172、178 的姓名
    select * from personTable where height not in (172, 178);
    

  • between ... and ...表示在一个连续的范围内

    查询 身高在 在172到180之间的的信息
    select * from personTable where height betwen 172 in 180;
    

    查询 身高在 在172到180之间的的信息

  • not between ... and ... 表示 不在一个连续的范围内

    查询 身高在 不 在172到180之间的的信息
    select * from personTable where height not betwen 172 in 180;
    或者 （使用上面的即可）
    select * from personTable where not height betwen 172 in 180;
    

  • 空(null)判断: 判空is null
    理解一个概念： name = null 与 name = "" 的区别,前者是 name没有指向任何地址，后者是指向一个空的地址

    // 查询身高为 空(null) 的信息
    select * from personTable where height is null;
    

    查询身高为 空(null) 的信息

  • 判非空 is not null

     // 查询身高 不为 空(null) 的信息
     select * from personTable where height is not null;
    

三、排序 order by 字段，使用 animalTable 表

animalTable表

• 3.1、order by` 字段

  • asc 从 小到大 排列，即 升序
  • desc 从 大到小 排序，即 降序

• 3.2、默认是 升序(第2句与第3句一个意思)

  // 查询年龄在3到12岁之间的雄性动物(默认按照 id 排序 )
  select * from animalTable where (age between 3 and 12) and gender = "雄";
  查询年龄在3到12岁之间的雄性动物，按照年龄从小到大排序
  select * from animalTable where (age between 3 and 12) and gender = "雄" order by age;
  select * from animalTable where (age between 3 and 12) and gender = "雄" order by age asc;
  

查询年龄在3到12岁之间的雄性动物，按照年龄从小到到排序

• 3.3、order by 多个字段

  查询年龄在3到12岁之间的雄性动物，按照年龄从小到大排序，如果年龄相同的情况下按照 id 从大到小排序( id 默认是从小到大的)

  select * from animalTable where (age between 3 and 12) and gender = "雄" order by age asc,id desc;
  

查询年龄在3到12岁之间的雄性动物，按照年龄从小到大排序，如果年龄相同的情况下按照 id 从大到小排序( id 默认是从小到大的)

• 3.4、不需要 where 约束

  按照年龄从小到大、id 从大到小的排序

  select * from animalTable order by age asc,id desc;
  

  不需要 where 约束,按照年龄从小到大、id 从大到小的排序

四、聚合、分组

animalTable表

• 4.1、聚合

  • (1)、count 计算个数

    查询雄性有多少人

    select count(*) as 雄性数量 from animalTable where gender = "雄";
    

    查询雄性有多少人

    查询雌性有多少

    select count(*) as 女性人数 from personTable where gender = "女";
    

  • (2)、最大值 max 与 最小值 min
    查询最大的年龄

    select max(age) from animalTable;
    

    查询最大的年龄

  • (3)、求和 sum
    求年龄的和

    select sum(age) from animalTable;
    

  • (4)、平均值 avg
    求年龄的平均值

    select avg(age) from animalTable;
    

    还可以用 sum(age)/count(*) 来计算平均年龄

    select sum(age)/count(*) from animalTable;
    

  • (5)、四舍五入 round(值 , 保留小数的位数) 小数
    计算所有动物的平均年龄，保留2位小数

    select round(avg(age),2) from animalTable;
    

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

    计算雄性的平均年龄 保留2位小数

    select round(avg(age),2) from animalTable where gender = "雄";
    

• 4.2、分组 group by

  • group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组

  • group by可用于单个字段分组，也可用于多个字段分组

  • (1)、按照 性别 分组,查询所有的性别
    查询所有性别的动物组

    select gender from animalTable group by gender;
    

  • (2)、计算每种 性别 中的人数

    select gender,count(*) from animalTable group by gender;
    

    计算每种 性别 中的人数

  • (3)、计算雄性的数量(取出分组中 雄 性的组)，根据条件取出分组中的某一个分组

    select gender,count(*) from animalTable where gender = "雄" group by gender;
    

    计算雄性的数量

  • (4)、group by + group_concat() 显示分组中的 指定字段
    显示分组中的 name

    • group_concat(字段名)可以作为一个输出字段来使用，
    • 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

    select gender,group_concat(name) from animalTable where gender="雄" group by gender;
    

    group_concat(...) 显示分组中的 指定字段
    

    显示分组中的 name, age, id

    select gender,group_concat(name, age, id) from animalTable where gender=1 group by gender;
    

    显示分组中的 name, age, id
    

    由于上面的 name, age, id 混在了一起，我们需要分开他们，看的更直观一些

    select gender,group_concat(name,"_",age, "_",id) from animalTable where gender=1 group by gender;
    

    由于上面的 name, age, id 混在了一起，我们需要分开他们，看的更直观一些

  • (5)、group by + having

    • having 条件表达式：用来分组查询后指定一些条件来输出查询结果
    • having作用和where一样，但having只能用于group by

    查询平均年龄超过7岁的性别，以及姓名

    select gender, group_concat(name),avg(age) from animalTable group by gender having avg(age)>7;
    

    查询平均年龄超过30岁的性别，以及姓名
    

    查询每种性别中的数量多于2个的信息

    select gender, group_concat(name) from animalTable group by gender having count(*)>2;
    

  • (6)、group by + with rollup
    with rollup 的作用是：在最后新增一行，来记录当前列里所有记录的总和

    select gender,count(*) from animalTable group by gender with rollup;
    

    **with rollup** 的作用是：在最后新增一行，来记录当前列里所有记录的总和

五、分页

personTable表

• 5.1、当数据量过大时，在一页中查看数据是一件非常麻烦的事情
  语法：select * from 表名 limit start,count,解释：start 是页码，count是一页显示的数量

• 5.2、查询前2行男生信息

  select * from personTable where gender = "男" limit 0,2;
  

  查询前2行男生信息

• 5.3、求第n页的数据，每页 m 条数据（不足m条，有多少显示多少条）

  select * from personTable limit (n-1)*m,m;
  

  举例： 如果展示：第三页的数据,每页2个数据：n=3,m=2

  select * from personTable limit 4,2;
  

  错误写法：

  select * from personTable limit (3-2)*2,2;
  

  提示：limit 要放在其他约束之后，最后面

六、连接查询 (多表的时候有用，单表的时候几乎没有用)，当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回，mysql支持三种类型的连接查询，分别为：内连接查询、左连接查询、右连接查询(有了左连接，一般不使用右连接)
语法： inner join ... on...

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

• 6.1、准备

  • 给 personTable 添加 class_id 字段，并赋值，效果如下

    alter table personTable add class_id int unsigned;

    
    给 personTable 添加 class_id 字段，并赋值，效果如下

  • 创建一个班级的数据表，并赋值

    mysql> create table classTable(
        ->  id int unsigned primary key auto_increment not null,
        -> class_name varchar(20) default ''
    );
    
    mysql> insert into classTable values(0,"一班"),(0,"二班"),(0,"三班")
    

    classTable表

• 6.2、内连接查询：inner join ... on (取交集)：查询的结果为两个表匹配到的数据
  

  内连接查询：查询的结果为两个表匹配到的数据

  • select ... from 表A inner join 表B;

    select * from personTable inner join classTable;
    

  • (1)、查询 有能够对应班级的学生以及班级信息

    select * from personTable inner join classTable on personTable.class_id=classTable.id;
    

    查询 有能够对应班级的学生以及班级信息

  • (2)、在上面查询的基础上 按照要求显示姓名、班级:不再使用 *,展示什么: 表名.字段

    select personTable.*,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;
    // 只显示两个组的名字
    select personTable.name,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;
    

  • (3)、给数据表 起别名（取表名的首字母）

    select p.name,c.class_name from personTable as p inner join classTable as c on p.class_id=c.id;
    

    5161545485353_.pic_hd.jpg

    • (4)、在以上的查询中，将班级姓名显示在第1列（将上面的p.name,c.class_name调换一下顺序）

      select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id;
      

    • (5)、查询 有能够对应班级的学生以及班级信息, 按照班级降序进行排序

      select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id order by c.class_name desc;
      

    • (6)、当时同一个班级的时候，按照学生的id进行从大到小排序

      select p.name,p.id,c.class_name from personTable as p inner join classTable as c on p.class_id=c.id order by p.id desc;
      

      当时同一个班级的时候，按照学生的id进行从大到小排序

• 6.3、左连接查询:left join (取左边)：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
  

  image.png

  • (1)、查询每位学生对应的班级信息

    select * from personTable left join classTable on personTable.class_id= classTable.id;
    

    查询每位学生对应的班级信息

  • (2)、查询没有对应班级信息的学生(用 having 就好，对结果进行处理)
    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 * from personTable left join classTable on personTable.class_id= classTable.id having classTable.class_name is null; 
    或者 where 来替换 having
    select * from personTable left join classTable on personTable.class_id= classTable.id where classTable.class_name is null;
    

    查询没有对应班级信息的学生(用 `having` 就好，对结果进行处理)

• 6.4、右连接查询(取右边)：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

  
  image.png

  提示： 将数据表名字互换位置，用left join完成 right join...on...

七、自关联

• 7.1、自关联的引用

  • 设计省信息的表结构provinces

    id
    ptitle
    

  • 设计市信息的表结构citys

    id
    ctitle
    proid
    

  • citys表的proid表示城市所属的省，对应着provinces表的id值

  • 问题：能不能将两个表合成一张表呢？

  • 思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的

  • 意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

  • 答案：定义表areas，结构如下

    id
    atitle
    pid
    

  • 说明:

    • 因为省没有所属的省份，所以可以填写为null
    • 城市所属的省份pid，填写省所对应的编号id
    • 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
    • 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

• 7.1、准备工作
  把本地的 city.sql(地区文件内容) 拷贝到 服务器root家目录下，下面代码：前面的city.sql 是本地的路径，后面的是服务器的路径，我是直接放到了 root的家目下

  scp -P 22 city.sql root@47.93.240.8: city.sql 
  

  在 pythonTestDataBase 库下建立表 city
  创建city表的语句如下：

  create table city(
        id int unsigned primary key auto_increment not null,
        pid int unsigned default 0,
        cityname varchar(20) default ' ',
        type int unsigned default null
  );
  

  导入数据

  source  city;
  

• 7.2、查询出山东省有哪些市

  select * from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山东省";
  select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山东省";
  

• 7.3、 查询出青岛市有哪些县城

  select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="青岛市";
  select * from city where pid=(select aid from city where atitle="青岛市")
  

八、子查询

animalTable表

• 8.1、 子查询 与 主查询

  • 子查询语句：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句。
  • 主查询：主要查询的对象,第一条 select 语句。
  • 主查询 和 子查询 的关系
    • 子查询是嵌入到主查询中
    • 子查询是辅助主查询的,要么充当条件,要么充当数据源
    • 子查询是可以独立存在的语句,是一条完整的 select 语句

• 8.2、子查询分类

  • 标量子查询: 子查询返回的结果是一个数据(一行一列)
  • 列子查询: 返回的结果是一列(一列多行)
  • 行子查询: 返回的结果是一行(一行多列)

• 8.3、标量子查询
  查询动物的平均年龄

  select avg(age) from animalTable;
  

  查询大于平均年龄的动物

  select * from animalTable where age > (select avg(age) from animalTable);
  

  查询动物的平均年龄与 查询大于平均年龄的动物

• 8.4、列级子查询

  
  列级子查询
  • 查询还有学生在班的所有班级名字

    • 找出学生表中所有的班级 id

    • 找出班级表中对应的名字

      select class_name from classTable where id in (select class_id from personTable);

      
      查询还有学生在班的所有班级名字

• 8.5、行级子查询

  • 需求: 查找班级年龄最大,身高最高的学生

  • 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素

    select * from students where (height,age) = (select max(height),max(age) from students);
    

• 8.6、子查询中特定关键字使用

  • in 范围
    格式: 主查询 where 条件 in (列子查询)

九、总结

• 9.1、查询的完整格式

  SELECT select_expr [,select_expr,...] [      
     FROM tb_name
     [WHERE 条件判断]
     [GROUP BY {col_name | postion} [ASC | DESC], ...] 
     [HAVING WHERE 条件判断]
     [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
     [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
  ]
  

• 9.2、完整的select语句

  select distinct *
  from 表名
  where ....
  group by ... having ...
  order by ...
  limit start,count
  

• 9.3、执行顺序为：

  • from 表名
  • where ....
  • group by ...
  • select distinct *
  • having ...
  • order by ...
  • limit start,count

• 9.4、实际使用中，只是语句中某些部分的组合，而不是全部