MySQL study Note 3: basic table operations

To operate a table, you must first select a database because the table exists in the database.

Select Database

 
MySQL> UseSchool;DatabaseChanged
 

 

After selecting the database, we can create tables in this database.

Create a table

MySQL> Create TableStudent (->IDInt,->NameVarchar(20),->Sex Boolean->); Query OK,0Rows affected (0.11Sec)
 

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

 
MySQL>Show tables;+------------------ +|Tables_in_school|+------------------ +|Student|+------------------ +1RowIn Set(0.00Sec)
 

Show tables: displays all tables in the current database.

 

View basic table structure

MySQL >  Describe student;  +  --  ----- + ------------- + ------ + ----- + --------- + ------- +  | Field | Type |   Null   |   Key   |   Default   | Extra|  +  --  ----- + ------------- + ------ + ----- + --------- + ------- +  | ID |   Int ( 11 ) | Yes |       |   Null      |         |  | Name|   Varchar ( 20 ) | Yes |       |   Null      |         |  | Sex |   Tinyint ( 1 ) | Yes |      |   Null      |         |  +  --  ----- + ------------- + ------ + ----- + --------- + ------- +  3 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

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  1 RowIn   Set ( 0.00 Sec)

Show create table displays detailed information when creating a table, similar to show create database.

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 TableStudent; query OK,0Rows affected (0.02Sec)
 

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.