MariaDB与MySQL的窗口函数和公共表表达式

PDF文件

下载需积分: 10 | 2.15MB | 更新于2024-07-17 | 29 浏览量 | 举报收藏

立即下载

"本书《MariaDB和MySQL Common Table Expressions 和 Window Functions 揭秘》由Daniel Bartholomew撰写，详细介绍了这两个数据库系统对ANSI SQL标准中的CTE（公共表表达式）和窗口函数的实现。MariaDB在10.2版本中引入了这些功能，而MySQL则在即将发布的8.0版本中加入。尽管实现过程独立，但两者都遵循标准，因此兼容性良好。然而，也存在一些差异，书中会指出这些差异。" 在数据库领域，标准和实施是相互交织的。有时，开发人员会提前引入新特性以推动技术发展，随后这些特性才会被正式标准化。其他时候，标准会先由供应商、开发者和其他相关人员共同制定，然后各数据库系统再根据标准进行实施。CTE（公共表表达式）和窗口函数在ANSI SQL标准中已有很长一段时间，CTE自SQL99版本起，窗口函数则在SQL2003版本中引入。其他如Oracle、SQL Server、PostgreSQL甚至SQLite等数据库系统较早实现了这些特性。 MariaDB和MySQL在相对较晚的时候才加入了这个行列，但它们现在都有符合标准的CTE和窗口函数实现。MariaDB在2017年5月宣布稳定的10.2版本中添加了这些特性，而MySQL则在其8.0版本（处于发布候选阶段）中引入。这两个数据库系统的实现虽然独立，但都尽可能地遵循标准，这使得在MariaDB上运行的查询在大部分情况下也能在MySQL上顺利执行，反之亦然。不过，书中有提到两者之间存在一些细微的差异。 CTE是一种临时的结果集，可以在单个查询中多次引用，常用于复杂的查询结构，如递归查询或避免重复代码。窗口函数则允许在结果集中进行行级别的计算，如计算移动平均、排名或累积总和，它们扩展了SQL的聚合功能，使处理分组数据时更加灵活。本书详细阐述了MariaDB和MySQL如何实现和使用CTE和窗口函数，帮助读者理解这两个数据库系统在这些高级SQL特性上的工作方式，以及如何在实际项目中有效利用它们。对于开发人员和数据库管理员来说，这是一本有价值的参考资料，可以提升他们在处理复杂查询和数据分析时的能力。

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页未读，继续阅读

pm07g101

粉丝: 0

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

mariadb-and-mysql-common-table-expressions-and-window-functions-revealed:丹尼尔·巴塞洛缪（Daniel Bartholomew）的“揭示了MariaDB和MySQL公用表表达式和窗口函数”的源代码-windows source code

mariadb-10.6.8-winx64.zip

mariadb-10.5.5-winx64.zip

mariadb-10.7.5-winx64.zip

mariadb-10.8.4-winx64.zip

mariadb-10.3.7-winx64.zip

mariadb-10.7.4-winx64.zip

最新资源