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:
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.
Category | Detailed explanation |
---|---|
Basic syntax | DROP TABLE table name; |
Example | DROP TABLE emp; |
Example Description | Delete table emp |
##mysql> DROP TABLE emp;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 setAt the end of creating the table, we often use the MyISAM or InnoDB engine. When specifying the engine, we can use:Query OK, 0 rows affected (0.34 sec)
ENGINE=InnoDBSpecify the table default character set:
DEFAULT CHARSET=utf8The effect is as follows:
CREATE TABLE emp (Next Sectionuseraname varchar(10) DEFAULT NULL,
Password date DEFAULT NULL,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;