Mysql Table Base Operation Summary (iii) _mysql

For the MySQL table based on the operation, the specific contents are as follows

1. Create a table:
To create a table's syntax form:

CREATE TABLE table_name (
 property name data type,
 property name data type,
 ...
 ) Property name Data type
)

Example:

CREATE TABLE t_dept (
 deptno int,
 dname varchar (),
 Loc varchar (20)

Note: Before you create a table, you usually need to use the name of the using, such a statement to select a library to create a table in the selected library. For a table name identifier, you cannot be a MySQL keyword, such as CREATE, use, and suggest that the table name identifier is t_xxx or tab_xxx; the attributes are separated by commas, and the last property does not need to use commas.

2. View Table structure:
2.1 Describe statements view the definition of a table
use library name; Select a library
DESCRIBE table name; To view the table's definition information, describe can use DESC instead
2.2 Show CREATE Table statement View the detailed definition of a table
use library name; Select a library
Show CREATE table name \g table names; View definition information for a table
Note: When displaying detailed definition information for a table, you can use the ";" "\g" "\g" symbol to end, in order to make the results show more beautiful, user-friendly view, it is best to use \g to end.

3. Delete Table:
to delete a table's syntax form:
Use library name; Select a library
The DROP table table name; Delete a table with the specified table name

4. Modify the table:
4.1 Modifying table names
For a table that has already been created, the use of a period of time requires some structural modifications, that is, table modification operations. Why not delete the table directly and then create the table according to the new table definition? The reason is that if a large amount of data already exists in the table, a lot of extra work needs to be done after the rebuild, such as overloading the data. To address the above problems, MySQL provides ALTER TABLE statements to implement the Modify tables structure.
Modifies the syntactic form of the table name: ALTER TABLE Old_table_name REANME [to] new_table_name;
4.2 Add Field
4.2.1 Add a field to the last position in the table
The syntactic form is: ALTER TABLE table_name ADD property Name property type;
4.2.2 Add a field to the first position in the table
The syntactic form is: ALTER TABLE table_name ADD Property Name property Type A;
4.2.3 Add fields after a specified field in a table
The syntactic form is: ALTER TABLE table_name ADD property Name property type after property name;
4.3 delete fields
Delete the syntax form of the field: ALTER TABLE table_name DROP property name;
4.4 Modify the field (modify data type and order must be modify, modify first name or name and attribute must be change)
4.4.1 Modify the data type of a field
The syntactic form is: ALTER TABLE table_name MODIFY property name data type; Data type is the modified data type
4.4.2 Modify the name of the field
The syntactic form is: ALTER TABLE table_name Change old property name new property name old data type;
4.4.3 to modify the name and properties of a field at the same time
The syntactic form is: ALTER TABLE table_name Change old property name new property name new data type;
4.4.4 Modify the order of fields
The syntactic form is: ALTER TABLE table_name MODIFY property name 1 data type firtst| After property name 2;
The property name 1 parameter represents the field name to adjust the order, and the "fitst" parameter indicates that the field is resized to the first position in the table, and after property name 2 indicates that the field is adjusted to the property name 2 field.

5. Constraint of the action table:
5.1 mysql-Supported integrity constraints
integrity refers to the accuracy and consistency of data, while integrity checking means checking the accuracy and consistency of data. MySQL provides a mechanism to check whether the data in a database table meets the specified conditions to ensure the accuracy and consistency of the data in the database, which is the constraint. In addition to supporting the integrity constraints of standard SQL, MySQL also extends auto_increment constraints.
1. The value of NOT NULL//constraint field cannot be null
2. Default//Set field defaults
3. The value of the unique KEY (UK)/constraint field is the only
4. The PRIMARY key (PK)//constraint field is the primary key of the table and can be used as a unique identifier for the table record
5. The value of the auto_increment//constraint field is automatic growth
5.2 Set non-null constraints (NOT NULL, NK)
When you create a database table, add a "not NULL" constraint to some fields to ensure that the field in all records has a value. The syntax for setting a Non-empty constraint is:

 CREATE TABLE table_name ( 
  property name data type not NULL, 
 ); 

5.3 Setting the default value for a field
When you insert a new record into a database table, if you do not assign a value to a field, the database system automatically inserts a default value for that field. The default value syntax for setting a field in a database table is:

 CREATE TABLE table_name ( 
  property name data type default value, 
 );

5.4 Setting UNIQUE constraints (unique, UK)
When the contents of a field in a database table are not allowed to be duplicated, you can set it by using the UK constraint. That is, the UK constraint adds a "UNIQUE" constraint to certain fields when creating a database table, guaranteeing that the values on that field in all records are not duplicated.
Set a unique constraint syntax form:

CREATE TABLE table_name (
 property name data type UNIQUE,
);

For example:

CREATE TABLE t_dept (
 deptno INT,
 dname VARCHAR () UNIQUE,
 Loc VARCHAR ()) 
;

If you want to set a name for the UK constraint on the field dname, you can execute the SQL statement constraint, as shown in the following example:

CREATE TABLE t_dept (
 deptno INT,
 dname VARCHAR (),
 Loc VARCHAR (), CONSTRAINT uk_dname
 UNIQUE ( dname)
);
When you set an identifier for a constraint, it is recommended that you use constraint abbreviation _ field name, so set to Uk_dname;

5.5 Set PRIMARY KEY constraint (PRIMARY key, PK)
When you want to uniquely identify all records with a field in a database table, you can use the PK constraint to set it. The primary key is set in a database table to facilitate quick lookup of records in a table. When you specify a PRIMARY KEY constraint, you must satisfy that the value of the primary key field is unique, non-empty. A primary key can make a single field or multiple fields, so it is divided into Word Chini and multiple field primary keys. Primary KEY constraints are equivalent to non-null constraints plus unique constraints.
5.5.1 single Field primary key
Set the PK constraint, the syntax is as follows:

CREATE TABLE table_name (
 property name data type PRIMARY KEY
);

If you want to set a name for the PK constraint on the field deptno, you can use constraint, as shown in the following example:

CREATE TABLE table_name (
 deptno INT,
 dname VARCHAR (),
 Loc VARCHAR (),
 CONSTRAINT Pk_deptno PRIMARY KEY (DEPTNO)
);

5.5.2-Field Primary key
When a primary key has multiple fields combined, it needs to be implemented through the SQL statement constraint, in the form of the following syntax:

CREATE TABLE table_name (
 property name data type,
 ...
 ) [CONSTRAINT constraint name] PRIMARY KEY (property name, property name ...)
);

5.6 Set field value automatically increase (auto_increment)
auto_increment is the unique constraint of MySQL's unique extension, when a new record is inserted into a database table, the value on the field generates a unique ID, and only one field in the database table can use the constraint, and the data type of the field must be of an integer type. Because the set from the Grow field generates a unique ID, the field is often set to the PK primary key.
The format of the self-growth syntax is as follows:

CREATE TABLE table_name (
 property name data type auto_increment,///The value of the default field is increased starting from 1, each additional is based on the previous one plus 1
 deptno INT PRIMARY KEY auto_ INCREMENT,//usually write with PK
);

5.7 Set FOREIGN KEY constraint (FOREIGN key, FK)
The previous integrity constraints are set in a single table, and the outer key constraint usually guarantees referential integrity between two tables, that is, the reference relationship between two fields built on two tables. When you set up the FK constraint specifically, the fields that set the FK constraint must depend on the primary key of one of the first (One-to-many) one that already exists in the database, while the foreign key can be set to null.
The syntax for setting FK constraints is as follows:

CREATE Table table_name (
 property name data type,
 property name data type,
 [CONSTRAINT FOREIGN KEY constraint name] FOREIGN key (property name 1) REFERENCES table name (property name 2) 
   //Note: The FOREIGN KEY constraint name is used to identify the constraint name, and the property name 1 argument is the field name for the foreign key set in the side table of "many", and in property 2, the parameter is the one side of "a" setting the field name of the primary KEY constraint.
)

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.