Operation of msyql data table

MySQL Add Columns, Modify Columns, Delete Columns

ALTER TABLE: Add, modify, delete columns, constraints, etc. Table definitions.

View table structure: desc table name;

Modify table name: alter table name rename to new table name;

add column: alter table table name add column name data type;

Delete column: alter table name drop column name;

Modify column name: alter table name change column name new column name new data type; --column must be followed by data type

Modify column attributes: alter table table name modify column name data type; --data type must be added after column

modify column subset:alter table table name modify column name data type character set character set name; --column must be followed by data type

Copy table structure:

mysql>create table new table name like table name to copy; --Copy structure only, not content

mysql>insert into table name a select * from table name b; --copy all the internal parts of table name b into table name a, and cooperate with the above sentence to copy the whole data table

mysql>create table td (select * from name); --Create a new table and copy the contents of the name table to the new table, excluding the primary key, index

mysql>insert into name select * from id where sex != '1'; --Copy qualified partial data to name table

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MySQL View Constraints, Add Constraints, Delete Constraints Add Columns, Modify Columns, Delete Columns

View field information of table: desc table name;

View all information about the table: show create table name;

Add primary key constraint: alter table table name add constraint primary key (such as PK_table name) primary key table name (primary key field);

Add foreign key constraint: alter table slave table add constraint foreign key (such as: FK_slave table_primary table) foreign key slave table (foreign key field) references primary table (primary key field);

Delete primary key constraint: alter table table name drop primary key;

Remove foreign key constraint: alter table table name drop foreign key (case-sensitive);

alter table t_book rename to bbb;

add column: alter table table name add column name varchar(30);

Delete column: alter table name drop column name;

MySQL: alter table bbb change nnnn hh int;

alter table t_book modify name varchar(22);

sp_rename: SQL Server built-in stored procedure for modifying table definitions.

--------------------------------------------------------------------------------------------------------

Joint query:

union: The number of query fields in the query table should be consistent, and duplicate data will be deleted automatically.

union all : The number of query fields in the query table should be consistent, and all qualified data should be displayed.

select id1,name1 from table1 union select * id2,name2 from table2;

-----------------------------------------------------------------------------------------------------