Data table operations

Data table operations

#Note:

1. In order to make it easier for everyone to 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.. The field type You only need to learn int, which represents the integrity. 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),  hiredate 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 table creation SQL statement

View table creation statement

Category detailed explanation Basic Syntax show create table table name\G;
Example show create table emp \G;
Example description View the creation statement of table emp
Execute the complete example:

mysql> show create table emp \G
 Table: emp 
 Create Table: CREATE TABLE emp ( 
 ename varchar(10) DEFAULT NULL, 
 hiredate 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)

The above table In the create 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.

\G cannot be followed by a semicolon;, because \G is functionally equivalent to;, if a semicolon is added, it will be;; (2 semicolons), SQL syntax error

ERROR: 
 No query specified

Delete table

##

mysql>drop table emp:
Query ok,0 rows affected(0.34 sec)

Note: Delete 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;