How to open and use MySQL?

Generally, we use ON in MySQL. In a join, we use ON across a set of columns. USING is useful when two tables share identically named columns to which they are joined.

Open the example.

Create our first table.

mysql> CREATE table ForeignTableDemo
   -> (
   -> Id int,
   -> Name varchar(100),
   - > FK int
   - > );
Query OK, 0 rows affected (0.47 sec)

Create our second table.

mysql> CREATE table PrimaryTableDemo
   - > (
   - >  FK int,
   - > Address varchar(100),
   - > primary key(FK)
   - > );
Query OK, 0 rows affected (0.47 sec)

Now let's add constraints.

mysql> ALTER table ForeignTableDemo add constraint FKConst foreign key(FK) references PrimaryTableDemo(FK);
Query OK, 0 rows affected (1.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

Add records to the second table.

mysql> INSERT into PrimaryTableDemo values(1,'US');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT into PrimaryTableDemo values(2,'UK');
Query OK, 1 row affected (0.14 sec)

mysql>  INSERT into PrimaryTableDemo values(3,'Unknown');
Query OK, 1 row affected (0.08 sec)

Display all records.

mysql> SELECT * from PrimaryTableDemo;

The following is the output.

+----+---------+
| FK | Address |
+----+---------+
|  1 | US      |
|  2 | UK      |
|  3 | Unknown |
+----+---------+
3 rows in set (0.00 sec)

Now, add records to the first table.

mysql>  INSERT into ForeignTableDemo values (1,'John',1);
Query OK, 1 row affected (0.20 sec)

mysql>  INSERT into ForeignTableDemo values (2,'Bob',2);
Query OK, 1 row affected (0.27 sec)

Now let us display all the records in the first table.

mysql> SELECT * from ForeignTableDemo;

This is the output.

+------+------+------+
| Id   | Name | FK   |
+------+------+------+
|    1 | John |    1 |
|    2 | Bob  |    2 |
+------+------+------+
2 rows in set (0.00 sec)

A direct join query that displays only matching rows is shown below. We used ON here.

mysql> SELECT ForeignTableDemo.Id, ForeignTableDemo.Name, PrimaryTableDemo.Address
   - > from ForeignTableDemo
   - > join PrimaryTableDemo
   - > on ForeignTableDemo.FK = PrimaryTableDemo.FK;

The following is the output.

+------+------+---------+
| Id   | Name | Address |
+------+------+---------+
|    1 | John | US      |
|    2 | Bob  | UK      |
+------+------+---------+
2 rows in set (0.14 sec)

Usage examples.

The following is the syntax of USING in MySQL, which displays records with FK = 1.

mysql> select *from ForeignTableDemo join PrimaryTableDemo using(FK) where FK=1;

This is the output.

+------+------+------+---------+
| FK   | Id   | Name | Address |
+------+------+------+---------+
|    1 |    1 | John | US      |
+------+------+------+---------+
1 row in set (0.09 sec)

The above is the detailed content of How to open and use MySQL?. For more information, please follow other related articles on the PHP Chinese website!