MariaDB与MySQL：公共表表达式与窗口函数详解

需积分: 9 65 浏览量更新于2024-07-17 收藏 2.17MB PDF 举报

"《MariaDB与MySQL：公共表表达式与窗口函数详解》是一本由Daniel Bartholomew编写的深入指南，它着重介绍了在MariaDB 10.2和MySQL 8.0版本中引入的两种强大的查询工具——公共表表达式(CTEs)和窗口函数。这本书旨在帮助数据库程序员们摆脱传统查询方式的限制，转向更为简洁高效的方法来解答商业智能问题。 CTEs（Common Table Expressions）是一种在SQL查询中创建临时结果集的技术，它允许程序员在单个查询中组织复杂的逻辑，通过分阶段定义和引用数据，简化了复杂的查询结构。通过使用CTEs，开发者可以避免复杂的自连接和冗余代码，使得查询结果更易于理解和维护，尤其在处理递归和分组计算时，CTEs提供了一种清晰的组织框架。另一方面，窗口函数是另一种革命性的特性，它在每个行上执行计算，并且能够访问到当前行以及其之前或之后的数据。这使得在分组查询中实现排名、累计值、百分位数等复杂统计变得易如反掌。窗口函数增强了数据分析的灵活性，特别是在时间序列分析、排名竞赛和移动平均等场景中，它们提供了强大的计算能力。本书详细介绍了如何在实际项目中使用CTEs和窗口函数，包括它们的工作原理、最佳实践和示例。作者强调了这些新特性的优点，比如提高查询性能、增强查询可读性，以及在快速响应业务变化方面的重要性。对于那些希望提升在MariaDB和MySQL环境中查询设计技能的程序员，这本书是一本不可或缺的参考资源。《MariaDB与MySQL：公共表表达式与窗口函数详解》是一本深入浅出的教程，旨在帮助读者掌握这两种高级查询技术，以提升数据处理效率和查询质量，从而更好地服务于商业智能决策。无论是经验丰富的开发者还是刚入门的学习者，都能从中获益匪浅。"

D. Bartholomew, MariaDB and MySQL Common Table Expressions and

Window Functions Revealed, https://doi.org/10.1007/978-1-4842-3120-3_1

CHAPTER 1

Basics of Common Table

Expressions

Common Table Expressions (CTEs) are one of the new SQL features introduced in

MariaDB 10.2 and MySQL 8.0. This chapter will introduce CTEs, describe the two types,

and explain the basic syntax. CTEs are named temporary result sets that only last for the

duration of the query they are in. In some respects, they are similar to derived tables, but

they are more powerful. They can refer to themselves recursively and can be referenced

multiple times in the same query. They also enable column grouping and can be used as

an alternative to views without our needing the CREATE VIEW permission. CTEs were first

introduced as part of the SQL99 standard.

Before We Begin

Before we get into more detail on what CTEs are and what they can do, the examples in

this chapter utilize sample data you can use to follow along with the text and experiment

with CTEs yourself. The table used in this chapter is called employees and it can be

created with the following query:

CREATE TABLE employees (

id serial primary key,

name VARCHAR(150) NOT NULL,

title VARCHAR(100),

office VARCHAR(100)

);

The data itself is in a CSV file called bartholomew-ch01.csv. It can be loaded with a

query similar to the following (assuming the file is on the computer running MariaDB or

MySQL server in the /tmp/ folder):

LOAD DATA INFILE '/tmp/bartholomew-ch01.csv'

INTO TABLE employees

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

CHAPTER 1 ■ BASICS OF COMMON TABLE EXPRESSIONS

The WITH and AS keywords are what distinguish a CTE from a normal query. If you

see a query that begins with WITH ... AS then you are looking at a CTE. The parts in

angle brackets <> are what you provide. Let’s go over the different parts now:

• <cte_name> is the name we will use to refer to the CTE later in

our query; it can be any valid name, i.e., not a reserved word or

function name.

• <cte_body> is just a SELECT statement that produces a result.

This part is wrapped in parentheses ().

• <cte_query> is where we reference the <cte_name> in a SQL

query. For example:

SELECT <select_criteria> FROM <cte_name> [WHERE ...]

• <select _criteria> is your normal SELECT query criteria with

optional WHERE and other clauses.

This might be a little difficult to visualize, so here’s a basic valid CTE where we define

a single <cte_name> and then SELECT from it. This CTE uses the sample data we loaded at

the beginning of the chapter, so feel free to run it on your MariaDB or MySQL server.

WITH emp_raleigh AS (

SELECT * FROM employees

WHERE office='Raleigh'

)

SELECT * FROM emp_raleigh

WHERE title != 'salesperson'

ORDER BY title;

Let’s break down what is happening here. Our <cte_name> is emp_raleigh, and it is

a simple SELECT statement that selects every row in the employees table WHERE the office

is Raleigh. You can think of this CTE as a view or filter of the employees table. Then, in the

<cte_query> section, we use the <cte_name> as part of a simple query that looks for every

entry where the employee is not a salesperson, and lastly it orders the results by their job

title. Because the <cte_query> uses our emp_raleigh <cte_name>, our results will only

come from records in the employees table WHERE office='Raleigh'.

Using our sample data, the result is:

+-----+-----------------+------------+---------+

+-----+-----------------+------------+---------+

| 73 | Mark Hamilton | dba | Raleigh |

| 77 | Nancy Porter | dba | Raleigh |

| 135 | Pauline Neal | dba | Raleigh |

+-----+-----------------+------------+---------+

CHAPTER 1 ■ BASICS OF COMMON TABLE EXPRESSIONS

Unsurprisingly, the output of both queries, using our sample data, is the same.

However, when you compare them, the CTE can be understood by simply reading

it from beginning to end. To understand the derived table, on the other hand, you

need to first read the inner SELECT statement and then jump up to the outer SELECT

statement, and then back down to the end. On a simple example like this, the extra

difficulty compared to the CTE is minimal, but as a derived table query becomes

more complex, the difficulty in reading it goes up exponentially. For a CTE, however,

the difficulty goes up in a more linear fashion because you can always just read from

beginning to end, naturally.

Using in One or Many Places

Following on from the previous section, another reason to use CTEs is if you need

something complex for just one query as opposed to something that will be used many

times in many different queries. For example, if your underlying sales table stores

invoice dates using a Unix timestamp, but several of your applications expect YYYY-

MM-DD whenever they query the table, a view would be an excellent solution; just

define the view and have your applications call that. On the other hand, a complex view

only used once in a single application might be more maintainable if rewritten as an

easier-to-read CTE.

Permissions

When working on your own personal databases on your own workstation or server, your

database user generally has the ALL PRIVILEGES WITH GRANT OPTION permissions, which

means you can do anything you need or want to your tables and databases, including

CREATE, UPDATE, INSERT, DELETE, and so on. Or, you might regularly just log in as the root

database user, which automatically has all permissions. Databases used in production,

however, generally have more granular access defined. Some users are only able to

SELECT from tables in certain databases, while others can make inserts in some tables

but not in others, and yet other users are given more or fewer grants depending on their

various job functions.

You may find yourself in need of something like a view on a table that you do not

have the CREATE VIEW permission on. CTEs only require the SELECT permission, so in this

case using a CTE is a great way to get what you need without having to pester one of the

DBAs to either create the view you need for you or asking them to grant you the CREATE

VIEW permission on the table you need it on, which they may be unable to do because of

company policies.

Nesting

CTEs bring several new tricks to our DBA toolbox, one of which is that in each individual

<cte_body> we can refer to other CTEs. This solves a big problem with nested derived

tables where every level of nesting greatly increases the complexity.

剩余115页未读，继续阅读

iebukes

粉丝: 9
资源: 132

MariaDB与MySQL：公共表表达式与窗口函数详解

MariaDB and MySQL common Table Expressions and Window Functions Revealed

MariaDB and MySQL Common Table Expressions and Window Functions Revealed epub

MariaDB and MySQL Common Table Expressions and Window Functions Revealed

No data sources are configured to run this SQL and provide advanced code assistance. SQL dialect is not configured.MariaDB,MySQL match best.

MariaDB与MySQL的区别是什么

linux如何修改 mariadb / mysql 的 root 密码报错1064

MariaDB和MySQL有什么区别

Neither MariaDB nor MySQL is configured as the default SGDB. See Right-Click -> Help -> MariaDB - MySQL

mariadb 和mysql 的区别

mariaDB怎么链接mysql数据库

最新资源