Create a table in mysql

Use the keyword temporary to create a temporary table. A temporary table can only be used to create user courseware for it. The temporary table can be the same as the persistent table. In this case, the temporary table hides the Permanent Table. Example: Raw table data: mysqlselect * fromstudent; + -------- + ---------- + --------- + ----------- + | stu _

Use the keyword temporary to create a temporary table. A temporary table can only be used to create user courseware for it. The temporary table can be the same as the persistent table. In this case, the temporary table hides the Permanent Table. Example: Raw table data: mysql select * from student; + -------- + ---------- + --------- + ----------- + | stu _

Temporary create temporary table

You can use the keyword temporary to create a temporary table. A temporary table can only be used to create user courseware for it. The temporary table can be the same as the persistent table. In this case, the temporary table hides the Permanent Table.
For example:

Raw table data:

mysql> select * from student;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+----------+---------+-----------+|      1 | a        |     151 |        60 ||      2 | b        |     152 |        61 ||      3 | c        |     153 |        62 ||      4 | d        |     154 |        63 |+--------+----------+---------+-----------+4 rows in set (0.00 sec)

Create a temporary table:

mysql> create temporary table student(    -> stu_id          int,    -> stu_name        varchar(5),    -> stu_tel         int(5),    -> stu_score       int(2)    -> );Query OK, 0 rows affected (0.14 sec)

Query table student:

mysql> select * from student;Empty set (0.00 sec)

There is no data because the temporary table hides the Permanent Table.

If not esists determines that the table exists

If the table already exists, you cannot create a permanent table. You can use if not exists to determine whether the object exists.
You can also use if exists to determine and delete a table.

create table if not exists  student(stu_id          int,stu_name        varchar(5),stu_tel         int(5)，stu_score       int(2));drop table if exists student;

Copy a table

create table student1 like student；

Copy the table structure and change the column name.

mysql> create table student1 as    -> (select stu_id as id,stu_name as name,stu_tel as tel,stu_score as score     -> from student);Query OK, 4 rows affected (0.22 sec)Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student1;+----+------+------+-------+| id | name | tel  | score |+----+------+------+-------+|  1 | a    |  151 |    60 ||  2 | b    |  152 |    61 ||  3 | c    |  153 |    62 ||  4 | d    |  154 |    63 |+----+------+------+-------+4 rows in set (0.00 sec)

When copying a table, you can set new constraints for the new table.
For example, set the id of the new table as the primary key.

mysql> create table student1(stu_id int primary key) as (select * from student);Query OK, 4 rows affected (0.22 sec)Records: 4  Duplicates: 0  Warnings: 0

The Rows not described in the column name table use the constraints in the original table by default.

Other parameters:

Auto_increment automatically allocates an id for the newly added row. You can also set the start id of the incremental allocation id.
Default is the default value for the column.
Comment adds description for columns

Example:

Create table student (stu_id int primary key auto_increment comment 'student id', stu_name varchar (5) comment 'student name', stu_tel int (5) comment 'student phone ', stu_score int (2) default 60 comment 'student score ') auto_increment = 100;

mysql> insert into student values(NULL,'a',150,default);Query OK, 1 row affected (0.03 sec)

mysql> select * from student;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+----------+---------+-----------+|    100 | a        |     150 |        60 |+--------+----------+---------+-----------+1 row in set (0.00 sec)

Mysql> select column_name, column_comment-> from information_schema.columns-> where table_name = 'student '; + ------------- + -------------- + | column_name | column_comment | + ------------- + ---------------- + | stu_id | student ID | stu_name | Student name | stu_tel | stu_score | + ------------- + ---------------- + 4 rows in set, 0 warnings (0.33 sec)