Easy to get started with MySQL-learning notes-Chapter 6th create, modify, and delete tables

Document directory

• 6.1.1. syntax for creating a table
• 6.1.2 set the table's primary key
• 6.1.3 set the foreign key of the table
• 6.1.4. Set non-empty table Constraints
• 6.1.5. Set table uniqueness constraints
• 6.1.6. Set the attribute value of the table to automatically increase
• 6.1.7. Set the default values of table attributes
• 6.2.1 view the basic table structure statement describe
• 6.2.2 view detailed table structure statement show CREATE TABLE
• 6.3.1 Modify Table Name
• 6.3.2 modify the Data Type of a field
• 6.3.3 ~ 6.3.6 add, delete, modify, and change the location of fields and Data Types
• 6.3.7. Change the table Storage Engine
• 6.3.8. Foreign key constraint for deleting a table
• 6.4.1 delete a common table that is not associated
• 6.4.2 Delete the parent table associated with another table

6.1 create a table 6.1.1. Create a table in the syntax format

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

Attribute name data type [integrity constraints],

......

Attribute name Data Type

);

 

Integrity constraints table:

Primarykey primary key

Foreignkey

Notnull cannot be blank

Unique unique index

Auto_increment automatically added

Default

 

Create tableexample0 (ID int,

Name varchar (20 ),

Sex Boolean

);

6.1.2 set the table's primary key

Single-field primary key

Attribute name data type primary key

Create tableexample1 (stu_id intPrimary Key,

Stu_name varchar (20 ),

Stu_sexboolean,

);

 

Multi-field primary key

Primary Key (attribute name 1, attribute name 2... Attribute name N)

Create tableexample2 (stu_id int,

Course_idint,

Gradefloat,

Primary Key (stu_id, course_id)

);

 

6.1.3 set the foreign key of the table

Constraint foreign key alias foreign key (attribute 1.1, attribute 1.2 ,..., Attribute 1.n)

References table name (attribute 2.1, attribute 2.2 ,..., Attribute 2.n)

Create tableexample3 (stu_id int,

Course_idint,

Constraintc_fk foreign key (stu_id, course_id)

Referencesexample2 (stu_id, course_id)

);

 

6.1.4. Set non-empty table Constraints

Attribute name data type not null

Create tableexample4 (ID intNot nullPrimarykey,

Name varchar (20)Not null,

Stu_id int,

Constraintd_fk foreign key (stu_id ),

Referencesexample1 (stu_id)

);

 

6.1.5. Set table uniqueness constraints

Attribute name data type unique

Create tableexample5 (ID intprimary key,

Stu_id intUnique,

Name varchar (20) not null

);

 

6.1.6. Set the attribute value of the table to automatically increase

Property Name Data Type auto_increment

Create tableexample6 (ID int primary keyAuto_increment,

Stu_id intunique,

Name varchar (20) not null

);

 

6.1.7. Set the default values of table attributes

Property Name Data Type default Default Value

Create tableexample7 (ID int primary keyAuto_increment,

Stu_id intunique,

Name varchar (20) not null.

English varchar (20) default 'zero ',

Computer float default 0

);

 

6.2 view the table structure 6.2.1. view the basic table structure statement describe

Desc table name

Desc example1

6.2.2 view detailed table structure statement show CREATE TABLE

Show create table table name;

Show createtable example example1 \ G;

6.3 modify table 6.3.1 and table name

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

Desc example0;

Alter table example0 Rename to user;

 

6.3.2 modify the Data Type of a field

Alter table table name modify attribute name data type;

Desc user;

Alter table user modify name varchar (30 );

Desc user;

 

6.3.3 ~ 6.3.6 add, delete, modify, and change the location of fields and Data Types

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

Alter table Table Name drop attribute name;

Alter table table name change old attribute new attribute name new data type;

 

You can use the preceding statement to add, delete, and modify fields. Modify the field name, data type, and location

 

6.3.7. Change the table Storage Engine

Alter table table name engine = InnoDB | MyISAM | memoery;

Show create table user \ G;

Alter table user engine = MyISAM;

 

6.3.8. Foreign key constraint for deleting a table

Alter table Table Name drop foreign key alias;

Show create table example3 \ G;

Alter table example3 drop foreign key c_fk;

Show create table example3 \ G;

 

6.4 delete a table 6.4.1 delete a common table that is not associated

Drop table name;

Desc example5;

Drop table example5;

Desc example5;

 

6.4.2 Delete the parent table associated with another table

Delete the foreign key and then delete the table

Drop table example1; -- Error

Show table example4 \ G;

Alter table example4 drop foreign key d_fk;

Show table example4 \ G;

Drop table example1;

Desc example1;