Mysql data table operation

Create table

Category	Detailed explanation
Basic syntax	CREATE TABLE table name (field name 1 field type,....field name n field type n);
Example	CREATE TABLE user(username varchar(20),password varchar(32));
Example description	Create a table named user, the first The field is username, the field type of the table is varchar, and the length is 32 characters. The second field is password, the type is also varchar, and the length is also 32 characters.

Note:

1. In order to better let everyone get started, data types are temporarily outside the scope of our explanation in this chapter. I’m afraid that everyone will focus on one thing and lose another. It is very important to quickly learn the management and operation statements of the database. Data types, fields, character sets, and engines are all knowledge points to understand.
2. . As for field types, you only need to learn int now, which represents integer type. float represents floating point. char and varchar can represent strings.
3. We can add the length after the type, such as: varchar(20).

Other examples:

mysql> CREATE TABLE emp(
ename varchar(10),
hired date,
sal float(10,2),
deptno int(2)
);
Query OK, 0 rows affected (0.63 sec)

mysql> create table dept( deptno int(4), deptname varchar(20));
Query OK, 0 rows affected (0.12 sec)
View table field structure information

Category	Detailed explanation
Basic syntax	desc table name;
Example	desc emp
Example description	View the table structure of the emp table

mysql>DESC emp;
+----------+---------------+------+-----+--------- +-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+--------- +-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+--------- +-------+
4 rows in set (0.39 sec)
View the creation SQL statement of the table
View table creation statement

Category Detailed explanation
Basic syntax SHOW CREATE TABLE table name\G;
Example SHOW CREATE TABLE emp \G;
Example description View table emp Create statement
Execute complete example:

mysql> SHOW CREATE TABLE emp \G;
Table: emp
CREATE TABLE: CREATE TABLE emp (
ename varchar(10) DEFAULT NULL,
hired date DEFAULT NULL,
sal decimal(10,2) DEFAULT NULL,
deptno int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1 row in set (0.00 sec)
ERROR:
No query specified

In the above table creation SQL statement, in addition to the table definition, you can also see the table's engine (storage engine) and charset (character set) and other information. The meaning of the "\G" option is to enable records to be arranged vertically according to fields, making it easier to display records with relatively long content.

Delete table

Category	Detailed explanation
Basic syntax	DROP TABLE table name;
Example	DROP TABLE emp;
Example Description	Delete table emp

##mysql> DROP TABLE emp;

Query OK, 0 rows affected (0.34 sec)

Note: Delete the table. Both tables and data will be lost. Please do not back up data before deleting important tables.

Specify the table engine and character set

At the end of creating the table, we often use the MyISAM or InnoDB engine. When specifying the engine, we can use:

ENGINE=InnoDB

Specify the table default character set:

DEFAULT CHARSET=utf8

The effect is as follows:

CREATE TABLE emp (

useraname varchar(10) DEFAULT NULL,
Password date DEFAULT NULL,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;