MySQL数据库 | 数据表-查询命令详细记录

本篇专门记录数据库增删改查中最常用、花招最多的 查。

【文章结构】

一、数据的准备

二、基本的查询功能

三、条件查询

四、查询排序

五、聚合函数

六、分组查询

七、分页查询

八、连接查询

九、子查询

十、自关联

【正文】

一、数据的准备

首先创建一个数据库，以便后文命令的使用。

注意，我使用高版本时，发现groups 不能用作表名了

-- 创建一个数据库
create database pysql charset=utf8;

-- 使用数据库
use pysql;

-- 查看当前使用的是哪个数据库
select database();

-- 创建数据表groups, heroes
create table groups (  
    id int unsigned auto_increment primary key,
    name varchar(20) not null
);

create table heroes (
    id int unsigned auto_increment primary key not null,
    name varchar(20) default "",
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum("男", "女", "保密") default "保密",
    grp_id int unsigned default 0,
    is_delete bit default 0
);

-- 查看数据库中已有的数据表
show tables;

-- 了解数据表的创建方式
show create table heroes;

-- 插入数据
insert into heroes values
(0,"妮蔻",18,150.00,2,1,0),
(0,"泰隆",50,188.00,1,1,0),
(0,"阿狸",16,179.00,1,1,0),
(0,"赏金",17,175.68,2,3,1),
(0,"盲僧",90,190.00,1,1,0),
(0,"盖伦",30,197.89,1,1,0),
(0,"光辉",18,160.00,2,1,1),
(0,"希维尔",21,167.90,3,2,0),
(0,"劫",70,null,2,1,1),
(0,"派克",34,170.00,1,6,0),
(0,"卡莎",18,180.90,2,1,0),
(0,"塔姆",56,150.00,1,2,0),
(0,"阿木木",90,130.01,1,1,0),
(0,"娜美",18,173.00,2,4,0);

insert into groups values (0, "宗师"), (0, "黑铁"), (0, "黄金");

二、基本的查询功能

-- 查询所有字段(大数据库中慎用)
-- select * from 表名;
select * from heroes;
select * from groups;

-- 查询指定字段
-- select 列1, 列2,...from 表名;
select name, age from heroes;

-- 使用as给字段起别名
-- select 字段 as 别名 from 表名;
select name as "姓名", age as "年龄" from heroes;

-- select 表名.字段... from 表名
select heroes.name, heroes.gender from heroes;

-- 通过as给表起别名
select h.name, h.gender from heroes as h;
-- select heroes.name, heroes.gender from heroes as h; 报错，改了名就要用...

-- 消除重复行(要是京东查手机的时候能有这么个命令，出现过的型号不要再出现就好了)
-- distinct 字段
select distinct gender from heroes;

三、条件查询

-- 比较运算符
  -- select ... from 表名 where 条件
  -- >
  -- 查询大于18岁的信息
  select * from heroes where age>18;

  -- <
  -- 查询id小于5的信息
  select * from heroes where id<5;

  -- >=
  -- <=
  -- 略

  -- =
  --查询性别为男的英雄的id和名字
  select id,name from heroes where gender=1;

  -- != 或者 <> (<>在很多语言中都不用，所以首选 !=)
  select id,name from heroes where gender!=1;

-- 逻辑运算符(与 或 非)
  -- and
  -- 18到50岁之间英雄的信息
  -- 报错 select * from heroes where age>18 and <50; 判断语句 左右两边都要写全
  select * from heroes where age>18 and age<50;

  -- 18岁以上的女性
  select * from heroes where age>18 and gender=2;
  select * from heroes where age>18 and gender="女";
  -- or
  -- 50岁以上或身高180(包含)以上
  select * from heroes where age>50 or height>=180;

  -- not
  -- not 加在谁前面就仅仅否定这一个条件,用()解决优先级的问题，不要死记硬背
  -- 不属于 70岁以上男英雄 的
  select * from heroes where not (age>70 and gender=1);

  -- 年龄不小于或等于18 的女性英雄.用()解决优先级的问题
  select * from heroes where (not age<=18) and gender=2;

-- 模糊查询
  -- like (效率低)
  -- % 替换1个，0个或多个
  -- _替换一个
  --查询姓名中以“赏”开头的名字
  select name from heroes where name like "赏%";

  --查询姓名中有“赏”的名字
  select name from heroes where name like "%赏%";

  --查询两个字的名字
  select name from heroes where name like "__";

  --查询至少两个字的名字
  select name from heroes where name like "__%";

  -- rlike 正则
  -- 查询以“泰”开始的名字
  select name from heroes where name rlike "^泰.*";

  -- 查询以“希”开头，“尔”结尾的名字
  select name from heroes where name rlike "^希.*尔$";


-- 范围查询
  -- in (18, 70, 50) 表示在一个非连续的范围内
  -- 查询年龄为18,70的英雄
  select name, age from heroes where age in (18, 70, 50);

  -- not in (18, 70, 50) 不在某个非连续的范围内
  select name, age from heroes where age not in (18, 70, 50);

  -- between .. and .. 在某个连续的范围内
  select name, age from heroes where age between 18 and 50;
  -- not between .. and .. 不在某个范围中，这是一个整体的语句，同时否定between和and的内容
  -- 报错 select name, age from heroes where age not (between 18 and 50);
  select name, age from heroes where age not between 18 and 50;
  select name, age from heroes where not age between 18 and 50;

-- 判断为空
  -- is null
  -- a = None 表示 a没有指向任何东西，a = "" 表示a指向一个为空的对象
  -- 查询身高为空的信息
  select * from heroes where height is null;
  -- 不为空的
  select * from heroes where height is not null;

四、查询排序

-- order by 字段
-- asc 升序(默认值)
-- desc 降序
-- 先写那个条件，先按照这个条件排序，相同情况下，按第二个排，否则不生效

--查询年龄在20-70的男英雄，按照年龄升序排列
select * from heroes where (age between 20 and 70) and gender=1 order by age;
select * from heroes where (age between 20 and 70) and gender=1 order by age asc;

-- order by 多个字段
-- 查询年龄在16-24之间的女性，按身高降序排列，如相同，按年龄升序排列
select * from heroes where (age between 16 and 20) and gender=2 order by height desc,age asc;

-- 全部人员，按照年龄从小到大排列，身高从高到低
select * from heroes order by age, height desc;

五、聚合函数

-- 函数，带括号那种。

-- 总数
-- count
-- 查询男英雄有多少人
select count(*) as "男英雄个数" from heroes where gender=1;

-- 最大值
-- max
-- 查询最大的年龄
select age from heroes;
select max(age) from heroes;

-- 查询女性最高身高
select max(height) as "最高身高" from heroes where gender=2;

-- 求和
-- sum
-- 所有人身高总和
select sum(height) from heroes;

-- 平均值
-- avg
-- 女性平均年龄，以下两种方式均可，此处目的在于说明select后面可以加运算式，
-- 但此类统计中尽量避免第二种方式，例如在此数据表中，如果是平均身高的话，因为有一个null的存在...
select avg(age) from heroes where gender=2;
select sum(age)/count(*) from heroes where gender=2;

--=====================================

-- 通过下面的命令得到的是女性的个数
select count(*) from heroes where gender=2;
-- 通过下面的命令得到的是女性的总身高
select sum(height) from heroes where gender=2;

-- 以下两种方式得到的女性平均身高不相等
select avg(height) from heroes where gender=2;
select sum(height)/count(*) from heroes where gender=2;

--自然也是不相等的...
select avg(height) from heroes;
select sum(height)/count(*) from heroes;

--=====================================

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

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

六、分组查询

-- 要和聚合搭配使用，才比较有意义

-- group by
-- 按照性别分组，查询所有的性别
-- select 可以唯一标记每个分组的...东西 from heroes group by gender;
select gender from heroes group by gender;

-- 计算每种性别有多少人
select gender, count(*) from heroes group by gender;
-- 此处的count(*) 是对每组的计算结果

-- 计算每组中的最大年龄、平均年龄
select gender, max(age) from heroes group by gender;
select gender, avg(age) from heroes group by gender;

-- group_concat(...)
-- 查询同种性别中的姓名等信息（查看组里的信息用 group_concat(想看的信息)）
-- 统计每种性别都包括哪个英雄(数据多了，就可以统计，地区，部门等等)
select gender, group_concat(name) from heroes group by gender;

-- 计算男性的人数(先写where，再写group by)
select gender,count(*) from heroes where gender=1 group by gender;
-- 计算男性人数，并查看男性都包括谁
select gender,count(*), group_concat(name) from heroes where gender=1 group by gender;
-- 计算男性人数，并查看男性都包括谁,以及每个人的id
select gender,count(*), group_concat(name,id) from heroes where gender=1 group by gender;
select gender,count(*), group_concat(name,"_",id," ",age) from heroes where gender=1 group by gender;

-- having
-- where 是从数据表中过滤数据，而having是从分组结果中过滤数据
-- 查询平均年龄超过40的性别，以及其中包含的人名 having avg(age) > 30
select gender, group_concat(name), avg(age) from heroes group by gender having avg(age) > 30;

-- 查询人数多于2的性别
select gender, group_concat(name), count(*) from heroes group by gender having count(*) > 2;

七、分页查询

-- 例如 网页中选择页数
-- limit start(起始), count(个数)

-- 限制查询出来的数据个数
select * from heroes where gender=1 limit 2;

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

-- 查询id 6-10(包含)的数据(id=1是第0个，id=6是第5个)
select * from heroes limit 5, 5;

-- 每页显示2个，显示第6页的信息，按照年龄升序排序(limit 放在命令末尾)
select * from heroes order by age asc limit 10, 2;

-- 查询所有女性信息，按升高降序，只显示前两个
-- 报错,刚开始想的..select gender, group_concat(name), age from heroes having gender=2 limit 2;
select * from heroes where gender=2 order by height desc limit 2;

八、连接查询

-- 内连接 取多个表的交集，否则不显示

  -- inner join ... on

  -- select * from 表1 inner join 表2; 将两张表对应起来
  -- 表1 一行一行的来对应表2 所有行
  select * from heroes inner join groups;

  -- 查询有能够对应小队的的英雄以及小队信息
  -- select * from 表1 inner join 表2 on 条件;
  select * from heroes inner join groups on heroes.grp_id=groups.id;

  -- 按照要求显示姓名 小队
  select heroes.*, groups.name from heroes inner join groups on heroes.grp_id=groups.id;
  select heroes.name, groups.name from heroes inner join groups on heroes.grp_id=groups.id;

  -- 给数据表起别名
  select h.name, g.name from heroes as h inner join groups as g on h.grp_id=g.id;
  select h.name as "英雄", g.name as "小队" from heroes as h inner join groups as g on h.grp_id=g.id;

  -- 查询 有能够对应小队的英雄以及小队的信息，显示英雄的所有信息，只显示小队名称
  select h.*, g.name from heroes as h inner join groups as g on h.grp_id=g.id;

  -- 在以上的查询中，将小队名字显示在第一列
  select g.name, h.* from heroes as h inner join groups as g on h.grp_id=g.id;

  -- 查询有能够对应小队的的英雄以及小队信息，按照小队进行排序,当小队相同时，按英雄的id 升序排序
  select g.name, h.* from heroes as h inner join groups as g on h.grp_id=g.id order by g.name,h.id;


-- 左连接：以左边的表为基准去从右面的表取东西
  -- left join
  -- 查询每位英雄对应的小组信息
  select h.*, g.name from heroes as h left join groups as g on h.grp_id=g.id order by g.name,h.id;
  select h.*, g.name from heroes as h left join groups as g on h.grp_id=g.id;
  select * from heroes as h left join groups as g on h.grp_id=g.id;

  -- 查询没有对应班级信息的学生
  select * from heroes as h left join groups as g on h.grp_id=g.id having g.id is null;
  select * from heroes as h left join groups as g on h.grp_id=g.id where g.id is null;

-- 右连接
  -- right join...on  用的很少
  -- 将数据表名字互换位置，用left join即可完成

九、子查询

-- select 中套着一个select
-- 查询最高的男英雄的信息
select * from heroes where height = (select max(height) from heroes where gender=1);

十、自关联

-- 一个表通过更改别名，当做两个表使用
-- select * from 表1 as 表A inner join 表1 as 表B on 表A.xxx=表B.yyy having 条件;

# 和时间赛跑