Catalog

  One 、join

Two 、on

3、 ... and 、left join and right join

left join

right join 

Four 、MySQL Workbench Graphic software

  One 、join

join have Connect effect . When two or more tables are related , You can use jion To connect these related tables to process or analyze data .

join The relevant connection mechanism of is as follows ：

Will find jion The column name after is the result of adding the two tables （ The same may happen , As in the above example ）.S1 jion S2 after ,S1 The columns in will be associated with S2 The columns in are merged one by one to form a new table . So in the above example, the last generated new table is 3 * 2 = 6 Columns .

If you are not satisfied with the newly generated table at this time , Want the corresponding options together , We can choose to use it on.

Two 、on

on be used for Adding constraints . For example, in use S1 join S2 When merging tables , We want to make S1 Medium l1 Column and S2 Medium l2 When the columns match equally , We can change the sentence to ：S1 join S2 on S1.l1 = S2.l2

On the premise that the column names are not repeated , We can also directly do without . The operator .

Or the example above ：

You can see that the merger is no longer the same as before “ The fool ” Amalgamated , It's going to be classid and id A new table formed by merging equal columns into one . 

3、 ... and 、left join and right join

The two statements are of the same nature , and join comparison ,left / right join The main difference is left / right  join There must be on.

• left join

left join Statement will check whether the data of the left table is included in the new table , If you lack it, use NULL Fill in ：

One thing to note ： about left join The position of the left and right watches is very important . stay join You can exchange positions in , The resulting new tables are the same ; But use left join Words , Exchange the positions of the left and right tables, and the resulting new table is completely different . So let's take this example , If you will stu and class The exchange of positions ：

  So when writing, you must pay attention to .

• right join 

and left join The same property is opposite ：

A right join B= B left join A --> It's all about B Benchmarking

Four 、MySQL Workbench Graphic software

Found by accident , After downloading, connect to your own library and find it very convenient , You can create and manage your database in it . Mainly can be graphical , Macros are easy to watch when designing . But in the final analysis, it is better to use more terminal operations , This can be an auxiliary .

MySQL Workbench Use the tutorial - How to use Workbench operation MySQL / MariaDB Database Chinese guide - Karayun Speaking of terminal operation , Today, I also installed a plug-in that can automatically complete sql sentence , It's so convenient （ Roar , Yesterday, I was still working for SQL The sentence is always wrong and annoyed .

It's called mycli, After installation, you can directly open the relevant library from the command line ：

It can also mark the names of commands and tables in green , It looks much more comfortable , I love it .