相关文章推荐
奋斗的皮蛋  ·  ffmpeg停止推流命令 - CSDN文库·  1 年前    · 
面冷心慈的饭盒  ·  C++ Primer学习笔记 - 第16章 ...·  1 年前    · 
满身肌肉的八宝粥  ·  C#中设置ListView的ColumnsH ...·  2 年前    · 
成熟的西装  ·  JSONObject、JSONArray、M ...·  2 年前    · 
Code  ›  MySQL查询这一篇就够了 - skaarl
https://www.cnblogs.com/skaarl/p/13951169.html
有胆有识的煎鸡蛋
2 年前

例1：查询未删除男生信息，按学号降序 

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

例2：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序 

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

3. 聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

  • count(*)表示计算总行数，括号中写星与列名，结果是相同的

    • 例1：查询学生总数 

    select count(*) from students;

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

    例2：查询女生的编号最大值
    

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

  • min(列)表示求此列的最小值
    • 

    例3：查询未删除的学生最小编号
    

    select min(id) from students where is_delete=0;

  • sum(列)表示求此列的和
    • 

    例4：查询男生的总年龄
    

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

  • avg(列)表示求此列的平均值
    • 

    例5：查询未删除女生的编号平均值
    

    select avg(id) from students where is_delete=0 and gender=2;

    

    4. 分组
    

    group by
    

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

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

    group by + group_concat()
    

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

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

    select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+
select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

    group by + 集合函数
    

  • 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
    • 

    select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+
分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+
分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+

    group by + having
    

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

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

    select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

    group by + with rollup
    

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

    select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+
select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+
    

    5. 获取部分行
    

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

    select * from 表名 limit start,count

  • 从start开始，获取count条数据
    • 

    例1：查询前3行男生信息
    

    select * from students where gender=1 limit 0,3;

    示例：分页
    

  • 已知：每页显示m条数据，当前显示第n页
    • 

  • 求总页数：此段逻辑后面会在python中实现

  • 查询总条数p1
    • 

  • 使用p1除以m得到p2
    • 

  • 如果整除则p2为总数页
    • 

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

  • 求第n页的数据
    • 

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

    6. 连接查询
    

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

    mysql支持三种类型的连接查询，分别为：
    

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

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

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

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

    例1：使用内连接查询班级表与学生表
    

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

    例2：使用左连接查询班级表与学生表
    

  • 此处使用了as为表起别名，目的是编写简单
    • 

    select * from students as s left join classes as c on s.cls_id = c.id;

    例3：使用右连接查询班级表与学生表
    

    select * from students as s right join classes as c on s.cls_id = c.id;

    例4：查询学生姓名及班级名称
    

    select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
    

    7. 子查询
    

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

    主要查询的对象,第一条 select 语句
    

    主查询和子查询的关系
    

  • 子查询是嵌入到主查询中
    • 

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

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

    子查询分类
    

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

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

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

    标量子查询
    

  • 查询班级学生平均年龄
    • 

  • 查询大于平均年龄的学生
    • 

    查询班级学生的平均身高
    

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

    列级子查询
    

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

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

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

    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);

    子查询中特定关键字使用
    

  • in 范围

  • 格式: 主查询 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语句
    • 

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

  • 执行顺序为：

  • from 表名
    • 

  • where ....
    • 

  • group by ...
    • 

  • select distinct *
    • 

  • having ...
    • 

  • order by ...
    • 

  • limit start,count
    • 

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

    1. 视图是什么
    

    通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
    

    视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
    

    方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
    

    2. 定义视图
    

    建议以v_开头
    

    create view 视图名称 as select语句;

    3. 查看视图
    

    查看表会将所有的视图也列出来
    

    show tables;

    4. 使用视图
    

    视图的用途就是查询
    

    select * from v_stu_score;

    5. 删除视图
    

    drop view 视图名称;
drop view v_stu_sco;
    

    6. 视图的作用
    

  • 提高了重用性，就像一个函数
    • 

  • 对数据库重构，却不影响程序的运行
    • 

  • 提高了安全性能，可以对不同的用户
    • 

  • 让数据更加清晰
    • 

    所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
    

    事务四大特性(简称ACID)
    

  • 原子性（atomicity）
    • 

    一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
    

  • 一致性（consistency）
    • 

    数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
    

  • 隔离性（isolation）
    • 

    通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
    

  • 持久性（durability）
    • 

    一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）
    

    当数据库中数据量很大时，查找数据会变得很慢
    

    优化方案：索引
    

    1. 索引是什么
    

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
    

    更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
    

    2. 索引的使用
    

    show index from 表名;

  • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
    • 

  • 字段类型如果不是字符串，可以不填写长度部分
    • 

    create index 索引名称 on 表名(字段名称(长度))

  • 删除索引：
    • 

    drop index 索引名称 on 表名;
     
    推荐文章
    奋斗的皮蛋  ·  ffmpeg停止推流命令 - CSDN文库
    1 年前
    面冷心慈的饭盒  ·  C++ Primer学习笔记 - 第16章 模板与泛型编程 - 明明1109 - 博客园
    1 年前
    满身肌肉的八宝粥  ·  C#中设置ListView的ColumnsHeader的字体大小，颜色及背景色_c#listview字体大小_Micro_Ryan的博客-CSDN博客
    2 年前
    成熟的西装  ·  JSONObject、JSONArray、Map、JavaBean相互转换 - 叶语婷 - 博客园
    2 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号