How to use direct connection in MySQL?-Mysql Tutorial-php.cn

How to use direct connection in MySQL?

王林

Release： 2023-08-29 08:05:08

forward

1418 people have browsed it

How to use direct connection in MySQL?

Direct joins in MySQL work similarly to inner joins or joins. This means it only returns Matching lines. First, we need to understand Straight join in MySQL. For this we need Create two tables and associate them with foreign key constraints.

This is the first table

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

Copy after login

This is the second table-

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

Copy after login

After that we will add the 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

Copy after login

Now the records will be Add to the second table -

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

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

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

Copy after login

Display records

mysql> SELECT * from PrimaryTableDemo;

Copy after login

The following is the output-

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

Copy after login

Now, add the records to the first table

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

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

Copy after login

Display all records with the help of SELECT statement-

mysql> SELECT * from ForeignTableDemo;

Copy after login

The following is the output

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

Copy after login

The query for direct connection is as follows, only matching rows are displayed-

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

Copy after login

The following is the output-

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

Copy after login

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