Use of SQL ALTER TABLE statements in a project

1. In the actual project development process, previously created entity classes may need to add/delete fields, or change the properties of existing fields, such as the primary key growth strategy from self-increment to UUID type, then it will involve

The use of the ALTER TABLE statement in SQL.

ALTER Table table_name Add column_name datatype   add columns in table

ALTER table table_name  drop column column_name   delete columns in a table

ALTER TABLE table_name ALTER COLUMN COLUMN_NAME datatype   change the data type of the columns in the table

Alter TABLE Persons ALTER COLUMN Birthday year

Code in the actual project:

!--the Customer entity to add the following fields
ALTER TABLE Sys_customer add business_license_no varchar (100);
ALTER TABLE Sys_customer add organization_no varchar (100);
ALTER TABLE Sys_customer add business_scope varchar (100);
ALTER TABLE Sys_customer add legal_representative varchar (100);
ALTER TABLE Sys_customer add legal_representative_card varchar (100);
ALTER TABLE Sys_customer add customer_address varchar (100);
ALTER TABLE Sys_customer add post_code varchar (100);

!--Create a user receipt address record entity, 2016/11/29 11:30
CREATE TABLE ' customer_receive_address ' (
' ID ' varchar (255) is not NULL,
' Address ' varchar (255) DEFAULT NULL,
' Area ' varchar (255) DEFAULT NULL,
' Crt_time ' datetime DEFAULT NULL,
' crt_user_id ' bigint () DEFAULT NULL,
' Crt_user_name ' varchar (255) DEFAULT NULL,
' customer_id ' bigint () DEFAULT NULL,
' Is_main ' bit (1) not NULL,
' Name ' varchar (255) DEFAULT NULL,
' Phone_num ' varchar (255) DEFAULT NULL,
' Postcode ' varchar (255) DEFAULT NULL,
' Upd_time ' datetime DEFAULT NULL,
' upd_user_id ' bigint () DEFAULT NULL,
' Upd_user_name ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' Fk1tulmarjqlerfxuvudw36b2v3 ' (' customer_id '),
CONSTRAINT ' Fk1tulmarjqlerfxuvudw36b2v3 ' FOREIGN KEY (' customer_id ') REFERENCES ' Sys_customer ' (' ID ')
) Engine=innodb DEFAULT Charset=utf8;

2. Actual project, some configuration entity information, publish time, add some data insert statement

-- ----------------------------
--Configuration information table Data 2016-11-11 18:40
-- ----------------------------
INSERT into ' auc_settings ' VALUES (' 1 ', ' smtphost ', ' email ', ' send mail server address ', ' smtp.163.com ');
INSERT into ' auc_settings ' VALUES (' 2 ', ' title ', ' email ', ' email ' headers ', ' email verification ');
INSERT into ' auc_settings ' VALUES (' + ', ' smstplcode ', ' SMS ', ' ali Big Fish ' template number ', ' sms_6740890 ');
INSERT into ' auc_settings ' VALUES (' + ', ' smstype ', ' SMS ', ' Ali Big Fish Message type ', ' normal ');

3. How does this relationship need to be maintained if there is a foreign key relationship between the changed or deleted column and the other table?

--2016-11-29 2016-12-01 Modify the primary key ID type
--Auc_lot
ALTER TABLE ' auc_lot ' MODIFY COLUMN ' id ' varchar (255) not NULL first;

ALTER TABLE auc_brand DROP FOREIGN KEY fk279hokw2vi5cy63b77hg9qxlt
ALTER TABLE auc_price DROP FOREIGN KEY fk4y2qo4m4i8i6axjpc0y44wiy2
ALTER TABLE auc_image DROP FOREIGN KEY FKE3YGRS9UB7PAU5RGL2E17JP8

--Announcement_goods
ALTER TABLE ' announcement_goods ' MODIFY COLUMN ' auc_id ' varchar (255) NULL DEFAULT NULL after ' announcement_id ';
--auc_attention
ALTER TABLE ' auc_attention ' MODIFY COLUMN ' auc_id ' varchar (255) null DEFAULT NULL after ' ID ';
--Auc_brand_no_generator
ALTER TABLE ' auc_brand_no_generator ' MODIFY COLUMN ' auc_id ' varchar (255) null DEFAULT null A fter ' id ';
--Auc_image
ALTER TABLE ' auc_image ' MODIFY COLUMN ' auc_id ' varchar (255) null DEFAULT NULL after ' ID ';
--Settlement_deal
ALTER TABLE ' settlement_deal ' MODIFY COLUMN ' auc_id ' varchar (255) null DEFAULT NULL after ' ID ';
--Settlement_bail
ALTER TABLE ' settlement_bail ' MODIFY COLUMN ' auc_id ' varchar (255) null DEFAULT NULL after ' ID ';

--Announcement_goods
ALTER TABLE announcement_goods DROP FOREIGN KEY fk96cnp94qfcfr3gybrg4x78dh7;
--Announcement
ALTER TABLE ' announcement ' MODIFY COLUMN ' id ' varchar (255) not NULL first;
ALTER TABLE ' announcement_goods ' MODIFY COLUMN ' announcement_id ' varchar (255) null DEFAULT NULL after ' ID ';

Reference links to Literature: http://www.w3school.com.cn/sql/sql_alter.asp

Use of SQL ALTER TABLE statements in a project