MySQL学习足迹记录13--联结表--INNER JOIN...ON_MySQL
bitsCN.com
MySQL学习足迹记录13--联结表--INNER JOIN...ON
学数据库联结表这一节内容几乎吐血,看书上的介绍和百度到的内容,总感到一脸茫然.....也许是我理解能力太差了吧。但我是一个不会轻易放弃的人,研究了半小时,终于搞明白了联结的原理,自我总结如下:关键在于“select语句,如果没有WHERE子句限定,将返回两个表的笛卡尔积.”
Example: 表一: Kemu+-----+--------+| kno | kname |+-----+--------+| b | yuwen || b1 | shuxue |+-----+--------+表二: Chenji;+------+------+---------+| sno | kno | chengji |+------+------+---------+| 1 | a | 10 || 2 | a1 | 20 |+------+------+---------+2 rows in set (0.00 sec)*重点:select语句,如果没有WHERE子句限定,将返回两个表的笛卡尔积.mysql> SELECT * FROM Kemu,Chenji;+-----+--------+------+------+---------+| kno | kname | sno | kno | chengji |+-----+--------+------+------+---------+| b | yuwen | 1 | a | 10 || b1 | shuxue | 1 | a | 10 || b | yuwen | 2 | a1 | 20 || b1 | shuxue | 2 | a1 | 20 |+-----+--------+------+------+---------+4 rows in set (0.00 sec)
Tips:SELECT 语句后面的所有表将会合成一个大表,所以,SELECT后所跟的各种子句就相当于在一个表中筛选数据了。
1.外键:某个表的一列,它包含另一个表的主键值,定义了两个表之间的关系
2. 联结不是物理实体,它在实际的数据库表中不存在,它存在与查询执行当中
3.创建联结
Example: mysql> SELECT vend_name,prod_name,prod_price -> FROM vendors,products -> WHERE vendors.vend_id = products.vend_id #WHERE子句指示MySQL匹配vendors表的 -> ORDER BY vend_name,prod_name; #vend_id和products表中的vend_id.+-------------+----------------+------------+| vend_name | prod_name | prod_price |+-------------+----------------+------------+| ACME | Bird seed | 10.00 || ACME | Carrots | 2.50 || ACME | Detonator | 13.00 || ACME | Safe | 50.00 || ACME | Sling | 4.49 || ACME | TNT (1 stick) | 2.50 || ACME | TNT (5 sticks) | 10.00 || Anvils R Us | .5 ton anvil | 5.99 || Anvils R Us | 1 ton anvil | 9.99 || Anvils R Us | 2 ton anvil | 14.99 || Jet Set | JetPack 1000 | 35.00 || Jet Set | JetPack 2000 | 55.00 || LT Supplies | Fuses | 3.42 || LT Supplies | Oil can | 8.99 |+-------------+----------------+------------+14 rows in set (0.01 sec)
TIPS:
*WHERE联结两个表时,实际是将第一个表中的每一行和第二个表中的每一行配对.并过滤不符合条件的行.
4.内部联结INNER JOIN .... ON
Example: mysql> SELECT vend_name,prod_name,prod_price -> FROM vendors INNER JOIN products #使用明确的联结语法能狗确保不会忘记联结条件 -> ON vendors.vend_id = products.vend_id #联结条件用特定的ON子句, -> ORDER BY vend_name,prod_name; #传递的实际条件与传递给WHERE的相同+-------------+----------------+------------+| vend_name | prod_name | prod_price |+-------------+----------------+------------+| ACME | Bird seed | 10.00 || ACME | Carrots | 2.50 || ACME | Detonator | 13.00 || ACME | Safe | 50.00 || ACME | Sling | 4.49 || ACME | TNT (1 stick) | 2.50 || ACME | TNT (5 sticks) | 10.00 || Anvils R Us | .5 ton anvil | 5.99 || Anvils R Us | 1 ton anvil | 9.99 || Anvils R Us | 2 ton anvil | 14.99 || Jet Set | JetPack 1000 | 35.00 || Jet Set | JetPack 2000 | 55.00 || LT Supplies | Fuses | 3.42 || LT Supplies | Oil can | 8.99 |+-------------+----------------+------------+14 rows in set (0.00 sec)
5.联结多个表
*SQL对一条SELECT语句中可以联结的表的数目没有限制。
Example: mysql> SELECT prod_name,vend_name,prod_price,quantity -> FROM orderitems,products,vendors -> WHERE products.vend_id=vendors.vend_id -> AND orderitems.prod_id=products.prod_id -> AND order_num=20005;+----------------+-------------+------------+----------+| prod_name | vend_name | prod_price | quantity |+----------------+-------------+------------+----------+| .5 ton anvil | Anvils R Us | 5.99 | 10 || 1 ton anvil | Anvils R Us | 9.99 | 3 || TNT (5 sticks) | ACME | 10.00 | 5 || Bird seed | ACME | 10.00 | 1 |+----------------+-------------+------------+----------+4 rows in set (0.00 sec)
bitsCN.com

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

Pinduoduo software provides a lot of good products, you can buy them anytime and anywhere, and the quality of each product is strictly controlled, every product is genuine, and there are many preferential shopping discounts, allowing everyone to shop online Simply can not stop. Enter your mobile phone number to log in online, add multiple delivery addresses and contact information online, and check the latest logistics trends at any time. Product sections of different categories are open, search and swipe up and down to purchase and place orders, and experience convenience without leaving home. With the online shopping service, you can also view all purchase records, including the goods you have purchased, and receive dozens of shopping red envelopes and coupons for free. Now the editor has provided Pinduoduo users with a detailed online way to view purchased product records. method. 1. Open your phone and click on the Pinduoduo icon.

How to skip security warnings in Baidu? There are various security protection services in Baidu APP, but most friends don’t know how to skip security warnings. Next, the editor brings to users pictures and texts on how to skip security warnings in Baidu. Tutorial, interested users come and take a look! How to skip security warnings in Baidu 1. First open Baidu APP, enter the lower right corner of the main page, click [My], and click [Settings] in the upper right corner of the My area; 2. Then on the settings function page, select [Internet Security]; 3. Finally, on the Internet security interface, click [Website Security] to skip the complete warning.

How to use Baidu Advanced Search Baidu search engine is currently one of the most commonly used search engines in China. It provides a wealth of search functions, one of which is advanced search. Advanced search can help users search for the information they need more accurately and improve search efficiency. So, how to use Baidu advanced search? The first step is to open the Baidu search engine homepage. First, we need to open Baidu’s official website, which is www.baidu.com. This is the entrance to Baidu search. In the second step, click the Advanced Search button. On the right side of the Baidu search box, there is

Editor of Machine Power Report: Wu Xin The domestic version of the humanoid robot + large model team completed the operation task of complex flexible materials such as folding clothes for the first time. With the unveiling of Figure01, which integrates OpenAI's multi-modal large model, the related progress of domestic peers has been attracting attention. Just yesterday, UBTECH, China's "number one humanoid robot stock", released the first demo of the humanoid robot WalkerS that is deeply integrated with Baidu Wenxin's large model, showing some interesting new features. Now, WalkerS, blessed by Baidu Wenxin’s large model capabilities, looks like this. Like Figure01, WalkerS does not move around, but stands behind a desk to complete a series of tasks. It can follow human commands and fold clothes

DeepSeek is a powerful intelligent search and analysis tool that provides two access methods: web version and official website. The web version is convenient and efficient, and can be used without installation; the official website provides comprehensive product information, download resources and support services. Whether individuals or corporate users, they can easily obtain and analyze massive data through DeepSeek to improve work efficiency, assist decision-making and promote innovation.

Baidu Incognito Mode is a privacy protection feature that allows users to use Baidu search or other services without leaving any personal information or browsing history. For some users, they may want to turn off incognito mode so that they can keep their search history or browsing history. So how to release Baidu incognito mode? Friends who are still unclear, don’t worry. Next, the editor will bring you a method to remove the incognito browsing mode. You can follow the steps below. How to release Baidu incognito mode 1. Click the [Baidu] icon on the desktop to open the Baidu APP. 2. Search for something and enter the search results page. 3. Click [≡] at the bottom of the page. 4. Find "Multi-Window" in the pop-up window and click to enter. 5. Turn on/off "Incognito"

There are many users in Xirang who don’t know where the shells are and how to get them. Some players have been searching for several hours but still can’t find them. Below, the editor will introduce how to obtain Baidu Xirang shells. Come and take a look. . How to get Baidu Xirang Shell 1. First we need to come to the community, and then come to the location in the picture below. 2. Select the destination here and choose to enter the 188th floor. 3. After entering the 188th floor, you will see this prompt when walking around. Just click I Got It. 4. The location of the shell may be a little difficult to find. Just behind the 188 elevator, there is a small glowing dot that is the shell. 5. You need to use a VR controller to pick up shells. Just click on the shell. Redemption method 1. First click the "Settings" icon in the upper right corner of the page and select "

On May 15, Baidu Apollo held Apollo Day 2024 in Wuhan Baidu Luobo Automobile Robot Zhixing Valley, comprehensively demonstrating Baidu's major progress in autonomous driving over the past ten years, bringing technological leaps based on large models and a new definition of passenger safety. With the world's largest autonomous vehicle operation network, Baidu has made autonomous driving safer than human driving. Thanks to this, safer, more comfortable, green and low-carbon travel methods are turning from ideal to reality. Wang Yunpeng, vice president of Baidu Group and president of the Intelligent Driving Business Group, said on the spot: "Our original intention to build autonomous vehicles is to satisfy people's growing yearning for better travel. People's satisfaction is our driving force. Because safety, So beautiful, we are happy to see
