August Rush

一个还在努力成长的小火汁!

游龙当归海,海不迎我自来也。

We create our own demons.

You can reach me at augustrush0923@gmail.com
MySQL高级查询
发布:2020年10月26日 | 作者:augustrush | 阅读量: 354

条件


使用where子句对表中的数据筛选,结果为true的行会出现在结果集中


  • 语法如下:
select * from 表名 where 条件;

select * from students where id=1;


where 后面支持多种运算符,进行条件的处理


  • 比较运算符
    • 等于: =
    • 大于: >
    • 大于等于: >=
    • 小于: <
    • 小于等于: <=
    • 不等于: != 或 <>
select * from students where id > 3;

select * from students where id <= 4;

select * from students where name != '小明';

select * from students where is_delete=0;


  • 逻辑运算符
    • and
    • or
    • not
select * from students where id >3 and gender=0;

select * from students where id <4 or is_delete=0;

select * from students where not id = 15;


  • 模糊查询
    • like
    • %表示任意多个任意字符
    • _表示一个任意字符
select * from students where name like '黄%';

select * from students where name like '黄_';

select * from students where name like '黄%' or name like '%靖';


  • 范围查询
    • in表示在一个非连续的范围内
    • between ... and ...表示在一个连续的范围内
select * from studnets where id in(1,3,8);

select * from students where id between 3 and 8;

select * from students where (id between 3 and 8) and gender=1;


  • 空判断
    • is null 判空
    • is not null 判非空
    • null''是不同的
select * from students where height is null;

select * from students where height is not null;

select * from students where height is not null and gender=1;


优先级


  • 优先级由高到低的顺序为: 小括号--not--比较运算符--逻辑运算符

  • and 比 or 先运算,如果同时出现并希望先算or,需要结合()使用


排序


语法:

select * from 表名 order by 列1 asc|desc [, 列2 asc|desc, ...]


说明:

  • 将每行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推

  • 默认按照列值从小到大排序(asc)

  • asc从小到大排序,即升序

  • desc从大到小排序,即降序

select * from students where gender=1 and is_delete=0 order by id desc;

select * from students where is_delete=0 order by name;

select * from students order by age desc, height desc;


聚合函数


总数


  • count(*) 表示计算总行数,括号中写*与列名,结果是相同的
select count(*) from studnets;


最大值


  • max(列) 表示求此列的最大值


select max(id) from students where gender=2;


最小值


  • min(列) 表示求此列的最小值
select min(id) from students where is_delete=0;


求和


  • sum(列) 表示求此列的和
select sum(age) from students where gender=1;

select sum(age)/count(*) from students where gender=1; # 平均年龄


平均值


  • avg(列) 表示求此列的平均值
select avg(height) from students where is_delete=0 and gender=2;


分组


group by

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

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

select gender from students group by gender;


group by + group_concat()


  • group_concat(字段名)可以作为一个输出字段来使用,

  • 表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合

select gender, group_concat(name) from students group by gender;


select gender, group_concat(id) from students group by gender;


group by + 聚合函数


select gender, avg(age) from students group by gender;

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


group by + having


  • having 条件表达式: 用来分组查询后指定一些条件来输出查询结果

  • having作用和where一样,但having只能用于group by

select gender, count(*) from students group by gender having count(*)>2;


group by + with rollup

  • with rollup的作用是: 在最后新增一行,来记录当前列里所有记录的总和
select gender,count(*) from students group by gender with rollup;

select gender, group_concat(age) from students group by gender with rollup;


分页


当数据量过大时,在一页中查看数据是一件非常麻烦的事情


语法:

select * from 表名 limit start,count;


  • 从start开始,获取count条数据
select * from students where gender=1 limit 0,3;


已知:每页显示m条数据,当前显示第n页

求总页数:

  1. 查询总条数p1

  2. 使用p1除以m得到p2

  3. 如果整除则p2为总页数

  4. 如果不整除则p2+1为总页数

求第n页的数据


select * from students where is_delete=0 limit (n-1)*m,m;


连接查询


当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回

MySQL支持三种类型的连接查询

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

  • 右连接查询: 查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充

  • 左连接查询: 查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充


语法:

select * from 表1 [inner | left | right] join 表2 on 表1.列 = 表2.列


# 使用内连接查询班级表与学生表
select * from students inner join classes on students.class_id = classes.id;

# 使用左连接查询班级表与学生表
select * from students as s left join classes as c on s.class_id = c.id;

# 使用右连接查询班级表与学生表
select * from students as s right join classes as c on s.class_id = c.id;

# 查询学生姓名及班级名次
select s.name,c.name from students as s inner join classes as c on s.class_id = c.id;


自关联

表中的某一列,关联了这个表中的另外一列,但是它们的业务逻辑含义是不一样的。这就是自关联

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

select count(*) from areas where pid is null;

select city.* from areas as city 
inner join areas as province on city.pid=province.aid 
where provice.atitle='河南省';

select dis.* from areas as dis 
inner join areas as city on city.aid=dis.pid 
where city.atitle='广州市';


子查询


子查询

在一个select语句中,嵌入了另外一个select语句,那么被嵌入的select语句称之为子查询语句


主查询

主要查询的对象,第一条select语句被称之为主查询语句


主查询和子查询的关系

子查询是嵌入到主查询中

子查询是辅助主查询的,要么充当条件,要么充当数据源

子查询是可以独立存在的语句,是一条完整的select语句


子查询分类

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

列子查询:返回的结果是一列(一列多行)

行子查询: 返回的结果是一行(一行多列)

标量子查询


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


列级子查询


select name from classes where id in (select cls_id from students);


行级子查询


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


子查询中特定关键字使用

主查询 where 条件 in (列子查询)


查询的完整格式

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}] ]


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


  • 执行顺序为:

    1. from 表名
    2. where...
    3. group by ...
    4. select distinct *
    5. having ...
    6. order by ...
    7. limit start,count


外键

在创建数据表的时候设置外键约束

create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);


对于已经存在的数据表更新外键约束

alter table 表名 add foreign key (外键字段名) references 表名(字段名)

alter table goods add foreign key (brand_id) references goods_brands(id);

alter table goods add foreign key (cate_id) references goods_cates(id);


取消外键约束

alter table 表名 drop foreign key 外键名称;


  • 标签云

  • 支付宝扫码支持一下

  • 微信扫码支持一下



基于Nginx+Supervisord+uWSGI+Django1.11.1+Python3.6.5构建

版权所有 © 2020-2021 August Rush

京ICP备20007446号-1 & 豫公网安备 41100202000460号

网站地图 & RSS | Feed