Basic mysql table operation summary (3), basic mysql table Summary

Basic mysql table operation summary (3), basic mysql table Summary

The basic operations on mysql tables are as follows:

1. Create a table:
Syntax for creating a table:

Create table table_name (attribute name data type, attribute name data type,... attribute name data type)

Example:

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

Note: before creating a table, you usually need to USE the USE database name. Such a statement selects a database and creates a table in the selected database. Table Name identifiers cannot be Keywords of mysql, such as CREATE or USE. We recommend that you set table name identifiers to t_xxx or tab_xxx. Separate attributes with commas, you do not need to use a comma after the last attribute.

2. view the table structure:
2.1 DESCRIBE statement View table Definitions
USE Database Name; // select a database
DESCRIBE table name; // View table definition information. DESCRIBE can be replaced by DESC or
2.2 show create table statement view detailed TABLE Definitions
USE Database Name; // select a database
Show create table name \ g table name; // View TABLE definition Information
Note: When displaying detailed table definition information, you can use the ";" "\ g" and "\ G" symbols to end the process. To make the results more beautiful and easy to view, it is best to end with \ G.

3. delete a table:
Syntax:
USE Database Name; // select a database
Drop table name; // delete a TABLE with the specified TABLE name

4. Modify the table:
4.1 Modify Table Name
For tables that have been created, some structural modifications are required after a period of time, that is, the modification operation of the table. Why not delete a table directly and create a table based on the definition of the new table? The reason is that if a large amount of data already exists in the table, much additional work is required after reconstruction, such as data overloading. To solve the preceding problem, mysql provides the alter table statement to modify the TABLE structure.
Alter table old_table_name REANME [TO] new_table_name;
4.2 Add a field
4.2.1 Add a field at the last position of the table
Syntax format: alter table table_name ADD attribute name attribute type;
4.2.2 Add a field in the first position of the table
Syntax format: alter table table_name ADD attribute name attribute type FIRST;
4.2.3 Add a field after a specified field in the table
Syntax format: alter table table_name ADD attribute name attribute type AFTER attribute name;
4.3 Delete A Field
Syntax: alter table table_name DROP attribute name;
4.4 MODIFY a field (MODIFY the data type and sequence, and CHANGE the name, name, and attribute)
4.4.1 modify the Data Type of a field
Syntax format: alter table table_name MODIFY attribute name data type; // the data type is the modified data type
4.4.2 modify the field name
Syntax format: alter table table_name CHANGE old property name new property name old data type;
4.4.3 modify the field name and attributes at the same time
Syntax format: alter table table_name CHANGE old property name new data type;
4.4.4 modify the order of fields
Syntax format: alter table table_name MODIFY attribute name 1 Data Type FIRTST | AFTER attribute name 2;
// "Attribute name 1" indicates the field name of the order to be adjusted, and "FITST" indicates that the field is adjusted to the first position of the table, "AFTER attribute name 2" indicates that the field is adjusted to the position of the attribute name 2 field.

5. operation table constraints:
5.1 integrity constraints supported by MySQL
Integrity refers to the accuracy and consistency of the index data, while integrity check refers to checking the data accuracy and consistency. MySQL provides a mechanism to check whether the data in the database table meets the specified conditions to ensure the accuracy and consistency of the data in the database. This mechanism is a constraint. In addition to the integrity constraints of standard SQL, MySQL also adds the AUTO_INCREMENT constraint.
1. not null // The value of the constraint field cannot be blank
2. DEFAULT // set the DEFAULT value of the field
3. The value of the unique key (UK) // constraint field is UNIQUE.
4. primary key (PK) // The constraint field is the primary key of the table, which can be the unique identifier of the table record.
5. AUTO_INCREMENT // The value of the constraint field is automatically increased
5.2 set a non-NULL constraint (not null, NK)
When creating a database table, add the "not null" constraint to some fields to ensure that the field in all records has a value. The syntax format for setting non-null constraints is:

Create table table_name (attribute name data type not null ,);

5.3 set the DEFAULT value of a field (DEFAULT)
When a new record is inserted to a database table, if a field is not assigned a value, the database system automatically inserts the default value for this field. Set the default syntax format of a field in the database table:

Create table table_name (DEFAULT value of attribute name data type ,);

5.4 set UNIQUE constraints (UNIQUE, UK)
When the content of a field in the database table cannot be repeated, you can use the UK constraint to set it. That is, the UK constraint adds the "UNIQUE" constraint to some fields when creating a database table to ensure that the values of this field in all records are not repeated.
Set the unique constraint syntax:

Create table table_name (attribute name data type UNIQUE ,);

For example:

CREATE TABLE t_dept( deptno INT , dname VARCHAR(20) UNIQUE, loc VARCHAR(40) );

If you want to set a name for the uk constraint on the field dname, You can execute the SQL statement CONSTRAINT, for example:

Create table t_dept (deptno INT, dname VARCHAR (20), loc VARCHAR (40), CONSTRAINT uk_dname UNIQUE (dname); // when you set an identifier for a CONSTRAINT, we recommend that you use the "constraint abbreviation _ field name". Therefore, it is set to uk_dname;

5.5 Set primary key constraints (primary key, PK)
When you want to use a field in the database table to uniquely identify all records, you can use the PK constraint for settings. The primary key is set in the database table to facilitate quick query of records in the table. When you set a primary key constraint, the values of the primary key fields must be unique and non-empty. The primary key can be a single field or multiple fields. Therefore, it can be divided into single-field primary keys and multi-field primary keys. A primary key constraint is equivalent to a non-null constraint and a unique constraint.
5.5.1 single-field primary key
Set the PK constraint. The syntax format is as follows:

Create table table_name (attribute name data type primary key ,);

If you want to set a name for the pk constraint on the deptno field, you can use CONSTRAINT, for example:

CREATE TABLE table_name( deptno INT, dname VARCHAR(20), loc VARCHAR(40), CONSTRAINT pk_deptno PRIMARY KEY(deptno));

5.5.2 multi-field primary key
When a primary key is composed of multiple fields, you must use the SQL statement CONSTRAINT. The syntax format is as follows:

Create table table_name (attribute name data type,... [CONSTRAINT name] primary key (attribute name, attribute name ......));

5.6 set the field value to automatically increase (AUTO_INCREMENT)
AUTO_INCREMENT is the unique extension constraint of MySQL. When a new record is inserted into a database table, the value in the field will generate a unique ID. Only one field in the database table can use this constraint, the data type of this field must be an integer. Because an auto-increment field generates a unique ID, this field is often set as a primary key.
Set the auto-increment syntax as follows:

Create table table_name (attribute name data type AUTO_INCREMENT, // The default field value is increased from 1. Add 1 deptno int primary key AUTO_INCREMENT for each added entry based on the previous entry, // It is usually written with the PK );

5.7 set foreign key constraints (foreign key, FK)
The preceding integrity constraints are all set in a single table. The foreign key constraints usually ensure the integrity of the reference between the two tables, that is, the reference relationship between the two fields of the two tables. When you set an FK constraint, the field that sets the FK constraint must depend on the primary key of one of the "one" (one of the many) existing in the database, the foreign key can also be set to null.
Syntax:

Create table table_name (attribute name data type, attribute name data type, [constraint foreign key constraint name] foreign key (attribute name 1) references table name (attribute name 2) // note: "foreign key constraint name" is used to identify the constraint name. "attribute name 1" parameter is the field name set for the foreign key in one table of "multiple", in "attribute 2, the parameter is the field name that the "1" side sets the primary key constraint .)

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.