Mysql data table operation

Create table

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

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

##mysql> DROP TABLE emp;

Query OK, 0 rows affected (0.34 sec)

ENGINE=InnoDB

DEFAULT CHARSET=utf8

CREATE TABLE emp (

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