Home Database Mysql Tutorial MySQL中join语句的基本使用教程及其字段对性能的影响_MySQL

MySQL中join语句的基本使用教程及其字段对性能的影响_MySQL

May 27, 2016 pm 01:45 PM

join语句的基本使用

SQL(MySQL) JOIN 用于根据两个或多个表中的字段之间的关系,从这些表中得到数据。

JOIN 通常与 ON 关键字搭配使用,基本语法如下:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1 通常称为左表,table2 称为右表。ON 关键字用于设定匹配条件,用于限定在结果集合中想要哪些行。如果需要指定其他条件,后面可以加上 WHERE 条件 或者 LIMIT 以限制记录返回数目等。

下面以最常见的两表连接来说明 MySQL JOIN 的用法,关于多表 JOIN 请参见《MySQL JOIN 多表》。

MySQL JOIN 分类

JOIN 按照功能大致分为如下三类:

  1. INNER JOIN(内连接):取得两个表中存在连接匹配关系的记录。

  2. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。

  3. RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。

关于 MySQL FULL JOIN 全连接

MySQL 没有提供 SQL 标准中的 FULL JOIN(全连接):两个表记录都取出,而不管彼此是否有对应记录。要解决此问题,可以使用 UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN,达到模拟 FULL JOIN 的目的。

MySQL INNER JOIN

INNER JOIN 用于取得两个表中存在连接匹配关系的记录。下面是两个原始数据表:

20151216104417019.png (366×317)

article 表中文章的所属用户是通过 uid 这个字段与 user 表关联起来的。通过观察数据不难发现,对于 uid=3 的用户,并没有发表任何文章;而文章中 aid=4 却无法在 uid 表中找到对应记录(可能是该用户被删除而其所属的文章却被保留了下来)。

我们列出所用文章与用户一一对应的数据。

SELECT … INNER JOIN … ON 语句如下:

SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.uid = user.uid
Copy after login

返回查询结果如下:

20151216104447947.png (273×126)

对于 INNER JOIN,等同与下面的 SQL 语句:

SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid
Copy after login

CROSS JOIN

CROSS JOIN 即交叉连接,在不指定 ON 条件下:

SELECT article.aid,article.title,user.username FROM article CROSS JOIN user
Copy after login

得到的结果是被连接的两个数据表的乘积,即笛卡尔积。

实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。

INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:

平板视图打印?

... FROM table1 INNER JOIN table2

... FROM table1 CROSS JOIN table2

... FROM table1 JOIN table2
Copy after login


join的字段字符集编码对性能的影响

先来看一下示例代码:

建utf-8编码的表 t1:

CREATE TABLE IF NOT EXISTS `t1` (
 `name` varchar(50) NOT NULL DEFAULT '',
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Copy after login

随便插入些数据,数量大一点,后面实验结果更清晰,偷个懒,构造随机字符串插入语句

insert into t1(name) 
select concat(
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65)
)
Copy after login

每次执行插入一条记录,用你熟悉的脚本(python,php,shell等都行)写个循环,执行一万次以上。

将该表复制成一个新表t2,删除一部分数据,1000条左右即可。(推荐使用phpMyAdmin)

再将t2复制为t3,并将字段改为gb2312编码。

使用一个left join语句,写一个语句,查出t2/t3比t1少了哪些记录。

语句很简单,如下:

SELECT SQL_NO_CACHE t1.name, t2.name
FROM t1
LEFT JOIN t2 ON t1.name = t2.name
WHERE t2.name IS NULL 
LIMIT 0 , 30
Copy after login

注意加入 SQL_NO_CACHE ,禁用mysql缓存。

先看编码一致的t2表,phpMyAdmin里执行结果:

显示行 0 - 29 ( 1,129 总计, 查询花费 0.0010 秒)
平均耗时大概为0.0010秒

SELECT SQL_NO_CACHE t1.name, t3.name
FROM t1
LEFT JOIN t3 ON t1.name = t3.name
WHERE t2.name IS NULL 
LIMIT 0 , 30
Copy after login

phpMyAdmin执行结果:

显示行 0 - 29 ( 30 总计, 查询花费 0.1871 秒)
差两个数量级!

查询语句解释:

20151216104614308.png (697×497)

以上就是MySQL中join语句的基本使用教程及其字段对性能的影响_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Statement of this Website
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

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

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to connect to the database of apache How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to start mysql by docker How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Laravel Introduction Example Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

How to install mysql in centos7 How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles