MariaDB与MySQL：共用表表达式与窗口函数深入解析 - CSDN文库

需积分: 10 131 浏览量更新于2024-07-19 收藏 2.32MB PDF 举报

"MariaDB and MySQL Common Table Expressions and Window Functions 无水印原版pdf" 本书《MariaDB and MySQL Common Table Expressions and Window Functions Revealed》由Daniel Bartholomew撰写，详细介绍了MariaDB和MySQL数据库系统中的两种高级查询功能：Common Table Expressions（CTEs）和Window Functions。CTEs是SQL中的一种临时结果集，可以在单个查询中多次引用，提高了复杂查询的可读性和维护性。而Window Functions则允许在数据集上进行计算时考虑当前行的上下文，如计算排名、移动平均等，极大地增强了分析和聚合能力。在MariaDB和MySQL中，这两项特性都极大地扩展了数据库的查询能力，使得开发者可以编写更高效、更灵活的SQL语句。书中可能涵盖了如何创建和使用CTEs进行多步骤查询，以及如何利用Window Functions执行复杂的行级计算。此外，作者Daniel Bartholomew可能会详细讲解这两种技术的工作原理，提供实例来演示它们在实际问题中的应用，并给出最佳实践建议。这本书对于数据库管理员、开发人员以及对数据库性能优化感兴趣的读者来说，是一份宝贵的参考资料。通过学习，读者将能够更好地理解和掌握如何在MariaDB和MySQL环境中利用CTEs和Window Functions提升查询效率，从而优化数据处理和分析流程。该资源的ISBN号分别为：pbk版本的978-1-4842-3119-7和electronic版本的978-1-4842-3120-3。如果想了解更多关于本书的信息，可以在美国亚马逊官网进行搜索。需要注意的是，此资源的版权受法律保护，未经授权不得复制或以其他形式使用其中的材料。这本书深入浅出地探讨了MariaDB和MySQL中的CTEs和Window Functions，是提升数据库操作和查询能力的重要学习资料。通过阅读，读者不仅可以了解到这些高级查询技术，还能了解到如何将它们有效地应用于实际的数据库管理和开发工作中。

3

© Daniel Bartholomew 2017

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

5

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:

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

| id | name | title | office |

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

| 73 | Mark Hamilton | dba | Raleigh |

| 77 | Nancy Porter | dba | Raleigh |

| 135 | Pauline Neal | dba | Raleigh |

| 68 | Edmund Hines | manager | Raleigh |

| 28 | Marc Greene | programmer | Raleigh |

| 96 | Mary Walker | programmer | Raleigh |

| 100 | Freida Duchesne | programmer | Raleigh |

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

CHAPTER 1 ■ BASICS OF COMMON TABLE EXPRESSIONS

7

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

yinkaisheng-nj

粉丝: 762
资源: 6231

最新资源