select的执行顺序问题
一张商品表tp_goods:
mysql> select * from tp_goods;
+----+-----------------------+-------------+--------------+--------+
| id | goods_name | goods_price | market_price | cat_id |
+----+-----------------------+-------------+--------------+--------+
| 1 | 锤子手机t1白色版32G | 1998.00 | 2488.00 | 1 |
| 2 | 联想E431笔记本 | 4029.00 | 4998.50 | 2 |
| 3 | mysql数据库深入浅出 | 29.80 | 36.30 | 4 |
| 4 | 苹果6 plus新加坡版 | 2998.00 | 6299.00 | 1 |
| 5 | OSA女装羽绒服2014新款 | 538.50 | 1510.00 | 3 |
+----+-----------------------+-------------+--------------+--------+
根据网上资料
mysql> select cat_id,goods_name,goods_price from tp_goods where cat_id=1;
先执行from操作,得到虚拟表VT1 ??> 然后执行where操作,筛选出cat_id=1的所有行,得到虚拟表VT2。此时的列,还是tp_goods表中所有的列 ??>最后执行 select 操作,此时列是 cat_id, goods_name, goods_price的列,结果返回。
-----------------------------------------------------------------------
mysql> select cat_id,max(goods_price) as max_price from tp_goods group by cat_id;
问题:
1. 这条语句又是如何执行的,group by 和 max函数是同时执行的吗?
2. group by 和 select 又是谁先执行?
回复讨论(解决方案)
1.group by ?先
2.group by ?先
where > group by > 聚合函? > select
1.group by ?先
2.group by ?先
where > group by > 聚合函? > select
那group by执行完成后,产生的虚拟表是什么
行是哪些,列是哪些?
你理解的执行顺序是错误的,至少是不科学的
如果 tp_goods 有 10000000 条记录,而符合 cat_id=1 的只有一条
那么你的第一步的抄写 tp_goods 到 VT1 不就浪费了大量时间和空间了吗?
所以应该是:
建立虚拟表 VT(只有 cat_id,goods_name,goods_price 3列)
遍历 tp_goods 将符合 cat_id=1 的记录追加到 VT 中
输出 VT 的内容
对于 select cat_id,max(goods_price) as max_price from tp_goods group by cat_id
执行的过程应该是:
建立虚拟表 VT(只有 cat_id, max_price 2列,并标识 cat_id 为聚类, max_price 为计算)
遍历 tp_goods 将记录追加到 VT 中,方法是如果 tp_goods.cat_id 的值在 VT.cat_id 中已存在。则修改对应的 max_price 为 max(goods_price,max_price),否则追加
输出 VT 的内容
你理解的执行顺序是错误的,至少是不科学的
如果 tp_goods 有 10000000 条记录,而符合 cat_id=1 的只有一条
那么你的第一步的抄写 tp_goods 到 VT1 不就浪费了大量时间和空间了吗?
所以应该是:
建立虚拟表 VT(只有 cat_id,goods_name,goods_price 3列)
遍历 tp_goods 将符合 cat_id=1 的记录追加到 VT 中
输出 VT 的内容
对于 select cat_id,max(goods_price) as max_price from tp_goods group by cat_id
执行的过程应该是:
建立虚拟表 VT(只有 cat_id, max_price 2列,并标识 cat_id 为聚类, max_price 为计算)
遍历 tp_goods 将记录追加到 VT 中,方法是如果 tp_goods.cat_id 的值在 VT.cat_id 中已存在。则修改对应的 max_price 为 max(goods_price,max_price),否则追加
输出 VT 的内容
版主,有没有书籍推荐,涉及执行过程解释的
书是肯定有的,估计应是1990年代的。不过我没看过(给我看我也不一定去看)
按流程推演,用 php 的关联数组都可以简单的模拟出那个过程
mysql 的开发者们总不至于弱智到用你听说的那个流程来操作的吧
书是肯定有的,估计应是1990年代的。不过我没看过(给我看我也不一定去看)
按流程推演,用 php 的关联数组都可以简单的模拟出那个过程
mysql 的开发者们总不至于弱智到用你听说的那个流程来操作的吧
怎么模拟的?
所以,我想知道mysql开发者们在程序里是如何操作的
按版主的说法,是不是只产生一张虚拟表?如果语句中存在多个 select 呢,会不会有多个虚拟表?如:
select * from tp_goods where cat_id=(select id from tp_cat where cat_name='手机');
版主是否举一个比较复杂的语句,包含where, group by, having, orderby....等等的,来解释一下它第一步的执行过程?
1.from子句用来组装不同数据源的数据2,where子句用于基于指定条件的筛选3.group by 子句用来将数据划分为多个分组4,使用聚集函数进行计算5.使用having子句对分组进行筛选6.计算所有表达式7.使用order by 对所有结果集进行排序
遇到上面括号的情况,当然是按照括号内部再如上述优先级执行。
跑个题:你那表里的 苹果6 是真的么?
特意去翻查了《高性能MySQL》(第二版),没找到楼主说讲的查询顺序的相关描述。希望以下截图能给楼主一些帮助。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Alipay PHP...

JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,

The application of SOLID principle in PHP development includes: 1. Single responsibility principle (SRP): Each class is responsible for only one function. 2. Open and close principle (OCP): Changes are achieved through extension rather than modification. 3. Lisch's Substitution Principle (LSP): Subclasses can replace base classes without affecting program accuracy. 4. Interface isolation principle (ISP): Use fine-grained interfaces to avoid dependencies and unused methods. 5. Dependency inversion principle (DIP): High and low-level modules rely on abstraction and are implemented through dependency injection.

How to automatically set the permissions of unixsocket after the system restarts. Every time the system restarts, we need to execute the following command to modify the permissions of unixsocket: sudo...

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

Sending JSON data using PHP's cURL library In PHP development, it is often necessary to interact with external APIs. One of the common ways is to use cURL library to send POST�...

Article discusses essential security features in frameworks to protect against vulnerabilities, including input validation, authentication, and regular updates.

How to debug CLI mode in PHPStorm? When developing with PHPStorm, sometimes we need to debug PHP in command line interface (CLI) mode...
