Home Database Mysql Tutorial MySQL学习足迹记录13--联结表--INNER JOIN...ON_MySQL

MySQL学习足迹记录13--联结表--INNER JOIN...ON_MySQL

Jun 01, 2016 pm 01:31 PM
Baidu ability Record

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)
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

 


bitsCN.com
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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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)

Where can I view the records of things I have purchased on Pinduoduo? How to view the records of purchased products? Where can I view the records of things I have purchased on Pinduoduo? How to view the records of purchased products? Mar 12, 2024 pm 07:20 PM

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 How to skip security warnings in Baidu Feb 23, 2024 pm 10:34 PM

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 How to use Baidu advanced search Feb 22, 2024 am 11:09 AM

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

After 2 months, the humanoid robot Walker S can fold clothes After 2 months, the humanoid robot Walker S can fold clothes Apr 03, 2024 am 08:01 AM

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 web version entrance deepseek official website entrance deepseek web version entrance deepseek official website entrance Feb 19, 2025 pm 04:54 PM

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.

How to disable Baidu incognito mode How to disable Baidu incognito mode Feb 29, 2024 pm 03:40 PM

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"

Introduction to how to obtain Baidu Xiyang Shells Introduction to how to obtain Baidu Xiyang Shells Mar 28, 2024 am 09:11 AM

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 "

Baidu Apollo releases Apollo ADFM, the world's first large model that supports L4 autonomous driving Baidu Apollo releases Apollo ADFM, the world's first large model that supports L4 autonomous driving Jun 04, 2024 pm 08:01 PM

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

See all articles