SQL – CREATE

測試環境 CentOS 8 (虛擬機)

CREATE 指令的使用方式.

CREATE DATABASE

建立資料庫.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> USE testdb;
Database changed

CREATE TABLE

建立資料表

MariaDB [testdb]> CREATE TABLE employee (Name CHAR(20),ts TIMESTAMP);
Query OK, 0 rows affected (0.004 sec)

MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben1' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben2' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben3' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT * FROM employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2023-09-13 01:11:57 |
| Ben2 | 2023-09-13 01:12:05 |
| Ben3 | 2023-09-13 01:12:11 |
+------+---------------------+
3 rows in set (0.000 sec)

CREATE TABLE LIKE

只複製資料表格式.

MariaDB [testdb]> CREATE TABLE A_employee LIKE employee;
Query OK, 0 rows affected (0.007 sec)

MariaDB [testdb]> SELECT * FROM A_employee;
Empty set (0.000 sec)

CREATE TABLE SELECT FROM

完整複製資料表格式與資料.

MariaDB [testdb]> CREATE TABLE B_employee SELECT * FROM employee;
Query OK, 3 rows affected (0.004 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM B_employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2023-09-13 01:11:57 |
| Ben2 | 2023-09-13 01:12:05 |
| Ben3 | 2023-09-13 01:12:11 |
+------+---------------------+
3 rows in set (0.000 sec)