Một ví dụ về sử dụng terminal tạo cơ sở dữ liệu, show dữ liệu đã tạo :)
MariaDB [(none)]> create database dvd;
MariaDB [(none)]> use dvd;
MariaDB [dvd]> CREATE TABLE `customers` (`customer_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(60) NOT NULL , `address` VARCHAR(60) NOT NULL, `city` VARCHAR(60) NOT NULL, PRIMARY KEY (`customer_id`), INDEX (`customer_id`));
MariaDB [dvd]> describe customers;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| customer_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(60) | NO | | NULL | |
| address | varchar(60) | NO | | NULL | |
| city | varchar(60) | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
MariaDB [dvd]> CREATE TABLE `dvd` ( `dvd_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `dvd_name` VARCHAR(100) NOT NULL, `author` VARCHAR(100) NOT NULL, `price` DECIMAL(6,2) NOT NULL, PRIMARY KEY (`dvd_id`), INDEX (`dvd_id`));
MariaDB [dvd]> describe dvd;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| dvd_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| dvd_name | varchar(100) | NO | | NULL | |
| author | varchar(100) | NO | | NULL | |
| price | decimal(6,2) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
MariaDB [dvd]> CREATE TABLE `orders` ( `order_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `customer_id` INT UNSIGNED NOT NULL, `amount` DECIMAL(6,2) NOT NULL, `payment` VARCHAR(40) NOT NULL,`message` TEXT NOT NULL, `order_date` timestamp, PRIMARY KEY (`order_id`), INDEX (`order_date`));
MariaDB [dvd]> describe orders;
+-------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+-----------------------------+
| order_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | int(10) unsigned | NO | | NULL | |
| amount | decimal(6,2) | NO | | NULL | |
| payment | varchar(40) | NO | | NULL | |
| message | text | NO | | NULL | |
| order_date | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------+
MariaDB [dvd]> CREATE TABLE `order_item` (`order_item` INT UNSIGNED NOT NULL AUTO_INCREMENT, `dvd_id` INT UNSIGNED NOT NULL, `order_id` INT UNSIGNED NOT NULL, `quantity` TINYINT UNSIGNED NOT NULL, PRIMARY KEY (`order_item`), INDEX (`dvd_id`), INDEX (`order_id`));
MariaDB [dvd]> describe order_item;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| order_item | int(10) unsigned | NO | PRI | NULL | auto_increment |
| dvd_id | int(10) unsigned | NO | MUL | NULL | |
| order_id | int(10) unsigned | NO | MUL | NULL | |
| quantity | tinyint(3) unsigned | NO | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
MariaDB [dvd]> INSERT INTO `customers` (`name`,`address`,`city`) VALUES ('Pham Ngoc Hoang','Ha Nam 1','Phu Ly'),('Pham Ngoc Vinh','Ha Nam 2','Ha Noi');
MariaDB [dvd]> select * from customers;
+-------------+-----------------+----------+--------+
| customer_id | name | address | city |
+-------------+-----------------+----------+--------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi |
+-------------+-----------------+----------+--------+
MariaDB [dvd]> INSERT INTO `dvd` (`dvd_name`,`author`,`price`) VALUES ('DVD 1','Demenwarlock 1',1000),('DVD 2','Demenwarlock 2',2000),('DVD 3','Demenwarlock 3',3000);
MariaDB [dvd]> select * from dvd;
+--------+----------+----------------+---------+
| dvd_id | dvd_name | author | price |
+--------+----------+----------------+---------+
| 1 | DVD 1 | Demenwarlock 1 | 1000.00 |
| 2 | DVD 2 | Demenwarlock 2 | 2000.00 |
| 3 | DVD 3 | Demenwarlock 3 | 3000.00 |
+--------+----------+----------------+---------+
MariaDB [dvd]> INSERT INTO `orders` (`customer_id`,`amount`,`payment`,`message`,`order_date`) VALUES (1,1000,'master','Toi rat thic',NOW()),(1,3000,'master','Toi rat thic',NOW()),(2,8000,'payment','Toi dang xem',NOW());
MariaDB [dvd]> select * from orders;
+----------+-------------+---------+---------+--------------+---------------------+
| order_id | customer_id | amount | payment | message | order_date |
+----------+-------------+---------+---------+--------------+---------------------+
| 1 | 1 | 1000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 2 | 1 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 3 | 2 | 8000.00 | payment | Toi dang xem | 2019-06-02 23:01:05 |
+----------+-------------+---------+---------+--------------+---------------------+
MariaDB [dvd]> INSERT INTO `order_item` (`dvd_id`,`order_id`,`quantity`) VALUES (1,1,10),(2,1,4),(1,2,44),(2,3,55);
MariaDB [dvd]> select * from order_item;
+------------+--------+----------+----------+
| order_item | dvd_id | order_id | quantity |
+------------+--------+----------+----------+
| 1 | 1 | 1 | 10 |
| 2 | 2 | 1 | 4 |
| 3 | 1 | 2 | 44 |
| 4 | 2 | 3 | 55 |
+------------+--------+----------+----------+
/* Hai câu lệnh này như nhau: INNER JOIN = ,
MariaDB [dvd]> select customers.name, orders.order_date from customers INNER JOIN orders;
+-----------------+---------------------+
| name | order_date |
+-----------------+---------------------+
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
+-----------------+---------------------+
MariaDB [dvd]> select customers.name, orders.order_date from customers, orders;
+-----------------+---------------------+
| name | order_date |
+-----------------+---------------------+
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Hoang | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Vinh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Ngoc Linh | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
| Pham Vinh Ling | 2019-06-02 23:01:05 |
+-----------------+---------------------+
*/
/* Hai câu lệnh này như nhau: WHERE customers.customer_id = orders.customer_id === USING (`customer_id`);
MariaDB [dvd]> select * from customers INNER JOIN orders WHERE customers.customer_id = orders.customer_id;
+-------------+-----------------+----------+--------+----------+-------------+---------+---------+--------------+---------------------+
| customer_id | name | address | city | order_id | customer_id | amount | payment | message | order_date |
+-------------+-----------------+----------+--------+----------+-------------+---------+---------+--------------+---------------------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1 | 1 | 1000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 2 | 1 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 3 | 2 | 8000.00 | payment | Toi dang xem | 2019-06-02 23:01:05 |
+-------------+-----------------+----------+--------+----------+-------------+---------+---------+--------------+---------------------+
MariaDB [dvd]> select * from customers INNER JOIN orders USING (`customer_id`);
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
| customer_id | name | address | city | order_id | amount | payment | message | order_date |
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1 | 1000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 2 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 3 | 8000.00 | payment | Toi dang xem | 2019-06-02 23:01:05 |
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
*/
Chi tiết về cách sử dụng INNER JOIN sử dụng để lựa chọn 4 bảng
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<style>
body {
font-size: 14px;
}
body span {
background-color: #000000;
color: #ffffff;
}
</style>
</head>
<body>
<pre>
MariaDB [dvd]> SELECT * from customers;
+-------------+-----------------+-----------+----------+
<span>| customer_id | name | address | city |</span>
+-------------+-----------------+-----------+----------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi |
| 3 | Pham Ngoc Linh | Ha Nam 3 | Hung Yen |
| 4 | Pham Vinh Ling | Vinh Phuc | Vinh Yen |
+-------------+-----------------+-----------+----------+
MariaDB [dvd]> SELECT * from orders;
+----------+-------------+---------+---------+--------------+---------------------+
<span>| order_id | customer_id | amount | payment | message | order_date |</span>
+----------+-------------+---------+---------+--------------+---------------------+
| 1 | 1 | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 |
| 2 | 1 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 3 | 2 | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 |
+----------+-------------+---------+---------+--------------+---------------------+
MariaDB [dvd]> select * from order_item;
+------------+--------+----------+----------+
<span>| order_item | dvd_id | order_id | quantity |</span>
+------------+--------+----------+----------+
| 1 | 1 | 1 | 10 |
| 2 | 2 | 1 | 4 |
| 3 | 1 | 2 | 44 |
| 4 | 2 | 3 | 55 |
+------------+--------+----------+----------+
MariaDB [dvd]> SELECT * from customers INNER JOIN orders USING (customer_id);
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
<span>| customer_id | name | address | city | order_id | amount | payment | message | order_date |</span>
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1 | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 |
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 2 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 3 | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 |
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
MariaDB [dvd]> SELECT * from customers INNER JOIN orders USING (customer_id) INNER JOIN order_item USING (order_id);
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
<span>| order_id | customer_id | name | address | city | amount | payment | message | order_date | order_item | dvd_id | quantity |</span>
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
| 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 1 | 1 | 10 |
| 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 2 | 2 | 4 |
| 2 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 | 3 | 1 | 44 |
| 3 | 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 | 4 | 2 | 55 |
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
MariaDB [dvd]> SELECT * from customers INNER JOIN orders USING (customer_id) INNER JOIN order_item USING (order_id) INNER JOIN dvd USING (dvd_id);
+--------+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+----------+----------+----------------+---------+
<span>| dvd_id | order_id | customer_id | name | address | city | amount | payment | message | order_date | order_item | quantity | dvd_name | author | price |</span>
+--------+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+----------+----------+----------------+---------+
| 1 | 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 1 | 10 | DVD 1 | Demenwarlock 1 | 1000.00 |
| 2 | 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 2 | 4 | DVD 2 | Demenwarlock 2 | 2000.00 |
| 1 | 2 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 | 3 | 44 | DVD 1 | Demenwarlock 1 | 1000.00 |
| 2 | 3 | 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 | 4 | 55 | DVD 2 | Demenwarlock 2 | 2000.00 |
+--------+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+----------+----------+----------------+---------+
</pre>
<pre>
Kết quả có thể được giải thích ngắn gọn như sau :)))
Sau câu lệnh: MariaDB [dvd]> SELECT * from customers INNER JOIN orders USING (customer_id); nó được két quả
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
<span>| customer_id | name | address | city | order_id | amount | payment | message | order_date |</span>
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1 | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 |
| 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 2 | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 |
| 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 3 | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 |
+-------------+-----------------+----------+--------+----------+---------+---------+--------------+---------------------+
Sau đó tiếp tục thực hiện lệnh INNER JOIN nó tiếp tục thực hiện dùng kết quả này để INNER JOIN với bảng
MariaDB [dvd]> select * from order_item;
+------------+--------+----------+----------+
<span>| order_item | dvd_id | order_id | quantity |</span>
+------------+--------+----------+----------+
| 1 | 1 | 1 | 10 |
| 2 | 2 | 1 | 4 |
| 3 | 1 | 2 | 44 |
| 4 | 2 | 3 | 55 |
+------------+--------+----------+----------+
Và kết quả sau câu lệnh:
MariaDB [dvd]> SELECT * from customers INNER JOIN orders USING (customer_id) INNER JOIN order_item USING (order_id);
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
<span>| order_id | customer_id | name | address | city | amount | payment | message | order_date | order_item | dvd_id | quantity |</span>
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
| 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 1 | 1 | 10 |
| 1 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 1000.00 | master | Toi rat thic | 2019-06-01 00:00:00 | 2 | 2 | 4 |
| 2 | 1 | Pham Ngoc Hoang | Ha Nam 1 | Phu Ly | 3000.00 | master | Toi rat thic | 2019-06-02 23:01:05 | 3 | 1 | 44 |
| 3 | 2 | Pham Ngoc Vinh | Ha Nam 2 | Ha Noi | 8000.00 | payment | Toi dang xem | 2019-06-03 23:01:05 | 4 | 2 | 55 |
+----------+-------------+-----------------+----------+--------+---------+---------+--------------+---------------------+------------+--------+----------+
</pre>
</body>
</html>Last updated