MySQL Clone and Create Table

Create a table

• Create a simple table
• NOT NULL (This is never null)
• AUTO_INCREMENT (use for INT, and not character, automatic generate the number)

CREATE TABLE mytbl
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

Insert Records

When the column is auto_increment, we can assign null to it, and it will automatically generate the number.

mysql> INSERT into mytbl(id,name,age) 
    -> values(null,'Fish',30);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from mytbl;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Fish |  30 | 
+----+------+-----+
1 row in set (0.00 sec)

Clone a table

• You want to clone a table. Here how you can do that.

Table we want to clone

mysql> select * from user;
+---------+------------------------------+---------------+
| user_id | loginEmail                   | password      |
+---------+------------------------------+---------------+
|       1 | admin@w3cyberlearning        | d4Z6Jm1L5KLY6 | 
|       2 | user@w3cyberlearning         | d4yOXqnMb09iw | 
|       3 | sophal@w3cyberlearning       | d4elKQV.j6LcQ | 
|       4 | yiling@w3cyberlearning       | d4TBKG4h5SUkY | 
|       5 | yiling1@w3cyberlearning      | d4J/AM81hPu5. | 
|       6 | kwan@w3cyberlearning         | d4Z.cnO8IUAoA | 
|       7 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       8 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       9 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|      10 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      11 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      12 |                              | d4rBwtAKocqwA | 
|      13 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      14 | [email protected]     | d4D9O7zfTlRZE | 
|      15 |                              | d4rBwtAKocqwA | 
|      16 | [email protected] | 82hngl1f0jsxA | 
|      17 | [email protected]                  | 82VgBddnErejI | 
|      18 |                              | d4rBwtAKocqwA | 
+---------+------------------------------+---------------+
18 rows in set (0.02 sec)

mysql> DESC user;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| user_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| loginEmail | varchar(50)  | NO   |     | NULL    |                | 
| password   | varchar(100) | NO   |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Clone table without copy from the existing record

We clone only the table structure.

mysql> CREATE TABLE myuser AS (SELECT * FROM user WHERE 1=2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc myuser;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user_id    | int(11)      | NO   |     | 0       |       | 
| loginEmail | varchar(50)  | NO   |     | NULL    |       | 
| password   | varchar(100) | NO   |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Clone table by copy a specific record

You can specific a specific record to be copied to a new table.

mysql> CREATE TABLE user2 AS (SELECT * FROM user WHERE loginEmail LIKE '%@%');
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> select * from user2;
+---------+------------------------------+---------------+
| user_id | loginEmail                   | password      |
+---------+------------------------------+---------------+
|       1 | admin@w3cyberlearning        | d4Z6Jm1L5KLY6 | 
|       2 | user@w3cyberlearning         | d4yOXqnMb09iw | 
|       3 | sophal@w3cyberlearning       | d4elKQV.j6LcQ | 
|       4 | yiling@w3cyberlearning       | d4TBKG4h5SUkY | 
|       5 | yiling1@w3cyberlearning      | d4J/AM81hPu5. | 
|       6 | kwan@w3cyberlearning         | d4Z.cnO8IUAoA | 
|       7 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       8 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       9 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|      10 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      11 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      13 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      14 | [email protected]     | d4D9O7zfTlRZE | 
|      16 | [email protected] | 82hngl1f0jsxA | 
|      17 | [email protected]                  | 82VgBddnErejI | 
+---------+------------------------------+---------------+
15 rows in set (0.00 sec)

Create table from file

File Content (file.txt)

CREATE TABLE myscore(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 score int NOT NULL,
 student_name VARCHAR(200) NOT NULL
);

Create table from file

• User:root
• Password:caojiang
• Database Name: school
• File: file.txt

sophal@sophal-desktop:~$ mysql -uroot -pcaojiang school < file.txt

Insert record from file

File Content (insert.txt)

INSERT INTO myscore(id,score,student_name)
VALUES(null,30,'Bob'),
(null,40,'Jing'),
(null,34,'Kim');

Insert From File

sophal@sophal-desktop:~$ mysql -uroot -pcaojiang school < insert.txt

Check the result

sophal@sophal-desktop:~$ mysql -uroot -pcaojiang school 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.51a-3ubuntu5.8 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from myscore;
+----+-------+--------------+
| id | score | student_name |
+----+-------+--------------+
|  1 |    30 | Bob          | 
|  2 |    40 | Jing         | 
|  3 |    34 | Kim          | 
+----+-------+--------------+
3 rows in set (0.00 sec)