To operate a table, you must first select a database because the table exists in the database.
Select Database
Mysql> use school;
Database changed
After selecting the database, we can create tables in this database.
Create a table
Mysql> create table student (
-> Id int,
-> Name varchar (20 ),
-> Sex boolean
-> );
Query OK, 0 rows affected (0.11 sec)
Create table is used to create a table, followed by the table name
The field names and types are written in brackets, separated by commas (,). Note that varchar is a variable-length string.
The five lines can also be written as one line. The reason for this writing is to define the definition.
Display table
Copy codeThe Code is as follows:
Mysql> show tables;
+ ------------------ +
| Tables_in_school |
+ ------------------ +
| Student |
+ ------------------ +
Row in set (0.00 sec)
Show tables: displays all tables in the current database.
View basic table structure
Copy codeThe Code is as follows:
Mysql> describe student;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| Name | varchar (20) | YES | NULL |
| Sex | tinyint (1) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
Rows in set (0.00 sec)
The field, data type, whether it is null, primary foreign key, default value, and additional information are displayed.
Describe can also be abbreviated as desc
In fact, most SQL statements can be abbreviated to four characters.
Note that the newly written sex is of the boolean Type and will be automatically converted to the tinyint type.
View detailed table structure
Copy codeThe Code is as follows:
Mysql> show create table student \ G
* *************************** 1. row ***************************
Table: student
Create Table: create table 'student '(
'Id' int (11) default null,
'Name' varchar (20) DEFAULT NULL
) ENGINE = InnoDB default charset = latin1
Row in set (0.00 sec)
Show create table displays the detailed information when creating a table.
The \ G behind the end is to show more beautiful
Tips: The \ G end is particularly valid when long information is displayed.
Delete table
Mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)
The operation for deleting a table is similar to that for deleting a database.
Use the drop command. After deletion, you can use show tables to view the remaining tables.