MySQL study note 3: introduction to the basic operation of tables

To manipulate a table, you first need to select the database, because the table exists in the database
Select database
mysql > use school;
Database changed
With the database selected, we can create tables in the 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 the table, followed by the table name
The field name and type are written in parentheses, separated by commas, and it should be noted that varchar is a variable length string
The 5 rows here can also be written as 1, just for clarity
According to the table

 
mysql> show tables; 
+------------------+ 
| Tables_in_school | 
+------------------+ 
| student | 
+------------------+ 
row in set (0.00 sec) 

show tables displays all the tables in the current database
View the basic structure of the table

 
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) 

This shows the fields, data types, whether they are null, primary foreign keys, default values, and additional information
describe can also be abbreviated as desc
Most sql statements can be reduced to four characters
Note that the sex you just wrote is of type boolean and is automatically converted to type tinyint
View the table detail 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 
row in set (0.00 sec) 

show create table displays details when creating a table
The \G after the end is for a better display
tips: the end of \G is especially useful when displaying long messages
Delete table
mysql > drop table student;
Query OK, 0 rows affected (0.02 sec)
Deleting a table is similar to deleting a database
Both use the drop command, and when the deletion is complete, you can view the remaining tables using show tables