一篇文章带你弄懂数据库的有哪几种高效查询的方法

于 2024-06-27 13:14:15 发布
阅读量538 收藏 7
点赞数 14
分类专栏： 数据库详细基础教程 文章标签： 数据库 sql 后端 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_80263861/article/details/140011017
版权

高效查询数据的方法

阅读指南：
本文章讲述了对于在数据库中高效查询数据的方法，如果读者感兴趣，后续我们会更新高级的操作在我们的对于数据库教程的合集中，大家可以来很我们一起学习讨论
合集链接：
数据库详细基础教程

高效查询处理

1.分组查询

概念：

先将数据行，按照某一或者多特性列进行分组，最后查询每组的特性。

分组查询的结果只能是分组特性列或者聚合函数。

语法：
SELECT 分组行, 分组列, 聚合函数 FROM table_name [WHERE condition] [GROUP BY 分组列, 分组列, …… HAVING 分组后的条件]

示例：

# 查询每种性别的员工数量和性别平均工资
SELECT gender, COUNT(*), AVG(salary) FROM t_employee GROUP BY gender;

# 查询生日年份，性别相同的人数和平均工资
SELECT YEAR(birthday), COUNT(*), AVG(salary) FROM t_employee GROUP BY YEAR(birthday);

# 查询工资高于5000，每种性别的员工数量和平均工资
SELECT gender, COUNT(*), AVG(salary) FROM t_employee WHERE salary > 5000 GROUP BY gender;

# 查询按照性别分组后平均工资高于11500的人数和性别人数
SELECT gender, COUNT(*), AVG(salary) FROM t_employee GROUP BY gender HAVING AVG(salary) > 11500;
SELECT gender, COUNT(*), AVG(salary) AS ave FROM t_employee GROUP BY gender HAVING ave > 11500;

注：

  1. 分组查询只能查询的是分组字段（列）和聚合函数
  2. HAVING是分组后的条件，WHERE是分组前的条件
  3. HAVING只能在 group by 后面出现，WHERE随时可以出现
  4. HAVING一般比较的都是聚合函数

2.排序查询

概念：

按照某一或者多特性列进行数据排序，不会影响结果条数，只是改变数据的展示顺序

# 语法：
SELECT 列,列,函数, FROM table_name [WHERE condition] [ORDER BY 排序列 ASC|DESC, 排序列 ASC|DESC……]

示例：

# 按照年龄正序排序
SELECT MIN(eid) AS eid, birthday FROM t_employee GROUP BY birthday ORDER BY birthday DESC;
# 按照工资倒序，如果工资相同，按照年龄正序排序查询员工信息
SELECT * FROM t_employee ORDER BY salary DESC, birthday DESC;
# 查询有奖金的员工，最终按照工资倒序显示员工信息
SELECT * FROM t_employee WHERE commission_pct IS NOT NULL ORDER BY salary DESC;

注：ASC为正序（默认值），DESC为倒序。

多列排序，只有上一列相同，第二列才会生效，以此类推。

3.数据切割（分页查询）

概念：
将结果进行分页切割，按照指定区域一段一段的进行展示。

例如：商品的分页展示

SELECT 列, 列, 函数 FROM table_name [WHERE condition] [LIMIT [位置偏移,] 行数];
# 位置偏移量：可选参数，默认是0
# 行数：指的是返回的记录条数

示例：

# 查询工资最高的员工信息
SELECT * FROM t_employee ORDER BY salary DESC LIMIT 0,1;
SELECT * FROM t_employee ORDER BY salary DESC LIMIT 1;
# 查询第二高的
SELECT * FROM t_employee ORDER BY salary DESC LIMIT 1,1;
# 查询工资最高的女性
SELECT * FROM t_employee WHERE gender = "女" ORDER BY salary DESC LIMIT 1;

注：

  1. 当行数对于数据库的实际数量时，则会返回数据库的实际数据，而不会造别的数据。
  2. limit关键字会影响数据，所以一般放置最后
  3. 分页条数的计算公式：limit (page - 1) * size, size; ！！！！！

4.开发经验

  1. 关键字的执行顺序
# 单表查询的结构：
SELECT
from
WHERE
AND
GROUP
HAVING
ORDER BY……ASC|DESC
LIMIT
# 关键字的摆放顺序是不能颠倒的
SELECT...FROM..WHERE...GROUP BY...HAVING...ORDER BY...LIMIT...
# SELECT语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT的字段 -> ORDER BY -> LIMIT

注：

  1. WHERE后面依然无法使用SELECT产生的列，这时候，确实没有列的生成，同时在标准的sql中也是如此
  2. 经过优化和中间的虚拟表，GROUP BY 和 HAVING 后面可以使用 SELECT 产生的列

示例：

#1、创建数据库test01_library#
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

#指定使用哪个数据库
USE test01_library;

#2、创建表 books
CREATE TABLE books(
	id INT,
	`name` VARCHAR(50),
	`authors` VARCHAR(100) ,
	price FLOAT,
	pubdate YEAR ,
	note VARCHAR(100),
	num INT
);

#3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books 
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
# 2）指定所有字段名称，插入第二记录`t_employee``books`
INSERT INTO books (id,NAME,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id,NAME,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

# 4、将小说类型(novel)的书的价格都增加5。(update)
# update 表名 set 列名 = 值 , 列名 = 值 , 列名 = 列名 运算 数值 where
UPDATE books SET price = price + 5 WHERE note = 'novel';

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books SET price = 40, note = 'drama' WHERE NAME = 'EmmaT';

# 6、删除库存为0的记录。
# delete from 表名 [where ...]
DELETE FROM books WHERE num = 0;

# 7、统计书名中包含a字母的书 'name不知道全部的名称一部分,模糊查看 like'
SELECT * FROM books WHERE NAME LIKE '%a%';

# 8、统计书名中包含a字母的书的数量和库存总量 [多行函数 | 聚合函数 count sum]
SELECT COUNT(*), SUM(num) FROM books WHERE NAME LIKE '%a%';

# 9、找出“novel”类型的书，按照价格降序排列
# order by | where 
# 书写的时候: select 列名 from where group by having order by asc desc limit 
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC, note;
UPDATE books SET num = 40 WHERE NAME LIKE '%o%';

# 11、按照note分类统计书的数量 [分组问题]
# 分组 select 分组字段,聚合函数
SELECT COUNT(*) FROM books GROUP BY note;
# 12、按照note分类统计书的库存量，显示库存量超过30本的 [分组,统计每一组的num的和 和>30 having]
SELECT COUNT(*),SUM(num) AS sm FROM books GROUP BY note HAVING sm > 30;

# 13、查询所有图书，每页显示4本，显示第二页 分页查询
# limit offset , number; 分页: page = 2 | size = 5 ==> limit (page-1)*size , size; => limit 5,5;
SELECT * FROM books LIMIT 4, 4;

# 14、按照note分类统计书的库存量，显示库存量最多的  [nm库存和排序,倒序+limit 1]
SELECT note, SUM(num)  FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 1;


# 15、查询书名达到10个字符的书，不包括里面的空格 (函数 | string的函数 char_length(column|字符串) | replace(字符串,'目标','替换的值') )
SELECT * FROM books WHERE  CHAR_LENGTH(REPLACE(NAME,' ','')) > 5;

/*
16、查询书名和类型，
 其中note值为 novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话 不在这个内,成为其他类型
*/

# case 流程控制 | 两个结果 if()
# 用法1: case when 运算表达式 then 值  when 运算表达式 then 值  else 默认值 end as 别名
# 用法2: case 列名 | 表达式 when 值 then 结果  when 值 then 结果  else 结果 end as 别名
SELECT NAME,note,
         CASE 
            WHEN note = 'novel' THEN '小说'
            WHEN note = 'law' THEN '法律'
            WHEN note = 'medicine' THEN '医药'
            WHEN note = 'cartoon' THEN '卡通'
            WHEN note = 'joke' THEN '笑话'
            ELSE '其他'
         END AS TYPE  FROM books ;    

SELECT NAME,note,
         CASE note 
            WHEN 'novel' THEN '小说'
            WHEN 'law' THEN '法律'
            WHEN 'medicine' THEN '医药'
            WHEN 'cartoon' THEN '卡通'
            WHEN 'joke' THEN '笑话'
            ELSE '其他'
         END AS TYPE  FROM books ;   

# 17、查询书名、库存状态，其中num值超过30本的，显示滞销，30-10代表正常, 大于0并低于10的，显示畅销，为0的显示需要无货
 SELECT NAME , num , 
         CASE 
            WHEN num > 30 THEN '滞销'
            WHEN num > 10 AND num <= 30  THEN '正常'
            WHEN num > 0 AND num <= 10  THEN  '畅销'
            ELSE '无库存'
         END AS 库存状态
            FROM books;   

# 18、统计每一种note的库存量，[并合计总量](分组)
# with rollup sql查询的子句,用于生成数据汇总行!
SELECT IFNULL(note,'总量'),SUM(num) FROM books GROUP BY note WITH ROLLUP;

# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'总量'),COUNT(*) FROM books GROUP BY note WITH ROLLUP;

# 20、统计库存量前三名的图书 [排序 倒序, limit 0 3 | limit 3]
SELECT * FROM books ORDER BY num DESC LIMIT 3;

# 21、找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 1;

# 22、找出novel中价格最高的一本书 [where order limit ]
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 1;

# 23、找出书名中字数最多的一本书，不含空格     
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC LIMIT 1;
Alfredorw
关注
  • 14
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值