Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use join and on in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Usage: 1, join is used to obtain the data that exists in different tables according to the relationship between the columns in two or more tables, with the function of joining, the syntax is "Table 1 join Table 2"; 2, on is used to add constraints between the two tables connected, the syntax is "Table 1 join Table 2 on conditions".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

The use of join in MySQL: join has the function of joining, that is, when two or more tables are related, you need to use join to join these related tables to process or analyze data:

Usage of join: take a look at the connection example first, and you will understand:

For example, there is a table called stu, and a table called class,stu join class generates a new table.

We execute: after stu join class, the new table becomes:

Sum up with the above example:

The column name after join is the sum of two table column names, which may result in the same column name, such as id and name

First, a row of data in table stu is continuously spliced with each row in table class to generate a new row.

Then use the second row of table stu to splice with each row of data in table class, and so on.

The table stu is 3 rows, and the table class is 2 rows, so according to the above rule, a new table with 3 rows 2 = 6 rows will be produced.

Usage of on: add constraints

Generally speaking, the table after our join is not what we want. At this time, we can add some conditions with ON:

For example: stu join class on classid = class.id, on is followed by the condition we add. We want the data in the classid column to be equal to the data in the id column. Note here that the list name after join is duplicated, so we need to add the original table name to the conditional statement after ON. For example, here: classid = class.id, because id has two columns, here we want the id in the table class, which is class.id.

So: after executing: stu join class on classid = class.id, the table changes to:

Tip: when there is only join, the on behind it can be replaced with where

Verify in the database: mysql-u root-p # log in to the database and enter the password CREATE DATABASE new; # to create the database newuse new; # use this database CREATE TABLE stu (id int primary key,name char (10), classid int) default charset = utf8mb4 # create table stuINSERT INTO stu (id,name,classid) VALUES (1), (2) insert data SELET * FROM stu

CREATE TABLE class (id int primary key,name char (10), teacher char (10)) default charset = utf8mb4; # create table classINSERT INTO class (id,name,teacher) VALUES (1 'experimental class', "Xiao Hong"), (2) "ordinary class", "small blue"); # insert data SELECT * FROM class

SELECT * FROM stu join class; # join two tables with join

SELECT * FROM stu join class ON classid = class.id; # use on to add conditions

The above is all the contents of the article "how to use join and on in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.