Notes on some commonly used data table operation statements in MySQL and mysql DATA statement notes

Notes on some commonly used data table operation statements in MySQL and mysql DATA statement notes

0. Create a table

Create table Name (attribute name data type [integrity constraints], attribute name data type [integrity constraints], attribute name data type [integrity constraints])

Integrity constraints refer to some special constraints for certain fields.
When using create table to CREATE a TABLE, you must first USE the USE statement to select a database. For example, if you have an EXAMPLE database, USE example to select the database.
Table names cannot use SQL keywords, such as create and update. Letters are case-insensitive.
The following is an example of creating a table:

create table example(id int, name varchar(20), sex boolean);

In the preceding example, a table named example has three fields: id integer, name string, and sex Boolean.
Integrity constraints are imposed on fields. If the integrity constraints are not met, the database system does not perform user operations, protecting the data integrity in the database.
The following are integrity constraints:

• Primary key identifies this attribute as the primary key of the table and can uniquely identify the corresponding tuples
• Foreign key identifies this attribute as the foreign key of the table, which is the primary KEY of a table associated with it
• Not null: This attribute cannot be blank.
• UNIQUE identifies the value of this attribute as UNIQUE
• AUTO_INCREMENT: the value of this attribute is automatically added.
• DEFAULT: Set the DEFAULT value for this property.

1. view the table structure

Describe table name

Describe can be abbreviated as desc.
The describe statement allows you to view basic table definitions. Including field Name field, field data type, whether it is null, whether it is the primary foreign key, default value default and additional information extra.

2. view the detailed table structure

Show create table name \ G

\ G enhanced display readability

show create table

You can view table fields, data types, integrity constraints, storage engines, and character encoding of each field.

3. Modify the table name

Alter table old table name rename [to] new table name;

4. Modify the field data type

Alter table name modify attribute name data type;

5. Modify the field name

Alter table name change old property name new data type;

The old property name parameter refers to the field name before modification, and the new property name parameter refers to the modified field name. If the data type is not specified, the Data Type remains unchanged.

6. Add Fields

Alter table name add attribute name 1 Data Type [integrity constraints] [first] after attribute name 2;

The "first" option indicates that this field is the first field in the table. The "after" attribute name 2 is placed after the specified field. The default value is the last one.

7. delete a field

Alter table Name drop attribute name;

8. Delete the joined table
(1) Delete the foreign key constraint of the table
A foreign key is a special field that associates a table with its parent table. When creating a table, the foreign key constraint has been set. The following statement is required to remove the associations between them.

Alter table Name drop foreign key alias;

The foreign key alias parameter refers to the foreign key code set during table creation.

(2) Delete A common table that is not associated

Drop table name;

When a table is deleted, all data in the table is also deleted. It is best to back up the data in the table before deleting the table.

(3) Delete the parent table associated with another table
When you delete a table with an association relationship, an error is returned when you use drop table example1 because a foreign key is dependent on the table.

For example, an example4 table is created dependent on the example1 table, and the foreign key stu_id of the example4 table is dependent on the primary key of the example1 table. The parent table of example4 in example1.

If you want to delete the example4 table, you must first remove this dependency. The simplest way is to delete the subtable example4 and then the parent table example1. However, this may affect other data in the sub-table.

Another method is to first Delete the foreign key constraint of the sub-table and then delete the parent table. This method does not affect other data in the sub-table and ensures database security.

For example, if the foreign key alias of example4 is d_fk, delete the foreign key constraint of example4.

alter table example4 drop foreign key d_fk; 

.

You can use show create table example4 \ G to check whether the object has been deleted.
Then execute

drop table example1; 

.
If the operation is successful, the operation is successful.

Articles you may be interested in:

• MySQL uses cursor batch processing for table operations
• Mysql database sharding and table sharding methods (commonly used)
• How to copy data from a data table to a new table in MySQL
• How to obtain table information through show table status in Mysql
• How to solve the MYSQL lock table problem
• MySQL uses the left join method when the data in the right table is not unique.
• PHP + MySQL
• How to Learn the data types and table field constraint types supported by MySQL
• MySQL left join table JOIN tutorial
• Basic Learning tutorial for MySQL memory tables