search
HomeDatabaseMysql TutorialMysql技术内幕-笔记-第三章 查询处理_MySQL

第三章 查询处理

逻辑查询处理:(8) SELECT (9) DISTINCT

                   (1) FROM

                   (3) JOIN

       (2) ON

       (4) WHERE

       (5) GROUP BY

       (6) WITH {CUBE|ROLLUP}

       (7) HAVING

     (10) ORDER BY

     (11) LIMIT

SELECT一共有3个过滤过程,WHERE,ON,HAVING, ON是最先执行的过滤过程。

WHERE过滤器中:

  1.由于数据还没有分组,因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对攻击的过滤

  2.由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如SELECT city AS c FROM t WHERE c='ShangHai'是不允许出现的。

在WHERE过滤器中进行的过滤和在ON过滤器中进行的过滤是有所不同的。对于OUTERJOIN中的过滤,在ON过滤器过滤完之后还会添加保留表中被ON条件过滤掉的记录,而WHERE条件中被过滤掉的记录则是永久的过滤。在INNER JOIN中两者是没有差别的,因为没有添加外部行的操作。

  3. HAVING是对分组条件进行过滤的筛选器,子查询不能用作分组的聚合函数,如HAVING COUNT(SELECT ...)

  4. SELECT:列的别名不能在SELECT中的其他别名表达式中使用,如:

  mysql>SELECT order_id AS o, o+1 AS n FROM orders;

  

  5. DISTINCT: 如果在查询中指定了DISTINCT子句,则会创建一张内存临时表(如果内存中存放不下就放到磁盘上)。这张内存临时表的表结构和上一步产生的虚拟表一样,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来去除重复数据。另外,对于使用了GROUP BY的查询,在使用DISTINCT是多余的,因为已经进行分组了,不会移除任何行。

  6. 大多数DBA和开发人员都错误的认为在选取表中的数据时,记录会按照表中主键的大小顺序的取出,即结果像进行了ORDER BY一样。导致这个经典错误的原因主要是没有理解什么才是真正的关系数据库。数据库中常见的查询操作其实对应的是集合的某些运算:选择、投影、连接、并、交、差、除。最终的结果虽然是以一张二维表的方式呈现在用户面前,但是从数据库内部来看是一系列的集合操作。因此,对于表中的记录,用户需要以集合的思想来理解。没有ORDER BY子句的查询只代表从集合中查询数据,而集合是没有顺序概念的。因此要牢记,不用为表中的行假设任何特定的顺序。

  7. LIMIT:从上一步骤的虚拟表中选出从指定位置开始的指定行数据。对应没有应用ORDER BY的LIMIT字句,结果同样可能是无序的,因此LIMIT子句通常和ORDER BY一起使用。

  LIMIT n,m

      表示从第n条记录开始选择m条记录。而大多数开发人员喜欢使用这类语句来解决web中经典的分页问题。对于小规模的数据,这并不会有太大的问题。但是对于大规模数据来说,LIMIT n,m效率是十分低的。因为每次都需要对数据进行选取。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

How can you monitor MySQL server health and performance?How can you monitor MySQL server health and performance?Apr 26, 2025 am 12:15 AM

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

Compare and contrast MySQL and MariaDB.Compare and contrast MySQL and MariaDB.Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),