SQL Example 4--alter

<title>SQL Example 4–alter</title> SQL Example 4–alter

• SQL Create
  
• /etc/my.cnf
  

• character
  

  show create  database Test;alter  Database Test character  set  UTF8; alter  database Test set  GBK; 

  show create  table  user1; alter  table  user1  character  set  GBK; alter  table  user1  character  set  UTF8; 

• Add
  

  Alter TableUser1AddAgeint  not NULL default Ten; # Add to last columnAlter TableUser1AddAge1int  not NULL default Ten  First; # Add to first columnAlter TableUser1AddAge2int  not NULL default Ten  AfterAgeAlter Table User1 AddAge3int  not NULL,AddAge4int  not NULL;

  Alter Table User1 Drop age; Alter Table User1 Drop Drop Drop drop age4;

• Primary key and foreign key
  

  Create Table test1 int varchar  not null); Create Table test2 int  not NULL varchar  not null);

  Primary key:
  

  Alter Table test2 Add constraint Primary Key  from Test2; Alter Table test2 Drop Primary Key  from Test2;

  Create FOREIGN Key:
  

  Alter Table test2 Add Foreign Key(ID2)ReferencesTest1 (ID); # type to the same can' t create table 'test.#SQL-158e_11' (errno:150) # type differentALTER TABLE test2 modify ID2 int not null;ALTER TABLE test1 modify ID int not null;ALTER TABLE Test1 add foreign key (ID) references test2 (ID2);Can 'TCreate Table ' test. #sql -158e_11 '(errno:150)Alter Table test2 Add constraintpk_test2_idPrimary Key(ID2); # parent table must be primary keyAlter Table test1 Add Foreign Key(ID)ReferencesTest2 (ID2); Show columns fromTest2;

  Go to modify
  

  Delete foreign key:
  

   from  from Create table test1;                #  Test1_ibfk_1altertabletest2dropforeignkey Test1_ibfk_1;

• Default
  

  alter  table  Span style= "color: #87cefa;" >test2  int  not  null ; alter  table  test2 set  default  15 ; # add default  property table  test2  alter  age drop  default ; # delete default  Property 

  Alter Table test2 Add Unique  from Test2;

• Modify Go back
  

  Alter Table test2 ModifyIdsmallint  not NULL  First;Alter Table test2 ModifyID tinyint not NULL  First; # switch * Big integer * to * small integer * Note to prevent data lossAlter Table test2Change PID p_id tinyint;Alter Table test2Change p_id PIDint;

• Rename, general can not change, the data may be wrong
  

  Table  to Test3; Alter Table test3  to Test1; Drop Table test1, test2;

SQL Example 4--alter