Addition, deletion, modification and query of MySQL advanced table

Modify the name of the table

ALTER TABLE student RENAME TO stu;

TO It can be omitted .

ALTER TABLE  The old name of the table  RENAME  The new name of the table ;

This statement modifies the name of the table , In fact, we usually use it in the terminal , There is no need to use commands with visualization tools .

Modify fields

ALTER TABLE stu CHANGE email mail VARCHAR(50) NOT NULL ;
-- ALTER TABLE  Table name  CHANGE  Field name   new field name   data type  [ attribute ( Don't write )] ;

such , I will stu Inside the watch email Changed to mail, The data type is VARCHAR(50).

The way , I'm here DataGrip It will be updated in time after operation , If you use other tools , You may have to refresh manually to respond ！！！

Add fields

ALTER TABLE stu ADD demo VARCHAR(10) NOT NULL ;
-- ALTER TABLE  Table name  ADD  The field name to add   data type  [ attribute ( Don't write )] ;

Delete field

ALTER TABLE stu DROP demo;
-- ALTER TABLE  Table name  DROP  Field name ;

Add primary key constraint

-- ALTER TABLE  Table name  ADD CONSTRAINT  Custom primary key name  PRIMARY KEY  Table name ( Field );
ALTER TABLE stu ADD CONSTRAINT PK_STU PRIMARY KEY stu(studentNo);

Primary key name , It can be named casually , But it's best to follow the rules ,pk+ Underline + Table name , Such as ：PK_STU`.

Add a foreign key constraint

-- ALTER TABLE  Foreign key table name  ADD CONSTRAINT  Foreign key name  FOREIGN KEY ( Foreign key field name ) REFERENCES  Primary key table name ( Primary key field name );
ALTER TABLE result ADD CONSTRAINT FK_RESULT_STU FOREIGN KEY (studentNo) REFERENCES stu(studentNo);

Watch my watch , Primary key in stu Inside the watch , The foreign key table is result, Don't get it wrong . The foreign key name specification is FK_ surface 1_ surface 2.

Little knowledge

We found that , We often use the above sentence ALTER TABLE This keyword .

We can check the help document with common sense .

If English is not good , Just search the browser honestly .