本篇专门记录数据库增删改查中最常用、花招最多的 查。
【文章结构】
一、数据的准备
二、基本的查询功能
三、条件查询
四、查询排序
五、聚合函数
六、分组查询
七、分页查询
八、连接查询
九、子查询
十、自关联
【正文】
一、数据的准备
首先创建一个数据库,以便后文命令的使用。
注意,我使用高版本时,发现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 条件;
# 和时间赛跑