"MySQL notes" data definition language DDL

1. Create basic table   creation table < table name > (< column name >< data type >[column-level integrity constraints]                                                         [,< Column name >< data type >[column-level integrity constraints]                                                         .........                                                         [,< Table-level integrity constraints;]);  Example:

  
 

   
  

  
create table student(
   
  

  
 Sno char(5) not null unique,
   
  

  
 Sname char(20) unique,
   
  

  
 Ssex char(1),
   
  

  
 Sage int,
   
  

  
 Sdept char(15)
   
  

  
);
  
 

2. Modify Basic Table Alert table < table name > [Add < New column name > < data type > [integrity constraint]]                                   [Drop < integrity constraint name;] [Modify < Column name >< data type >]; 1) Add: Used to add new columns and new columns related integrity constraints to the table, the new column increases successfully without any data
         Alert Table student add scome date;
2) Drop: Used to delete the specified integrity constraint
     Alert Table student drop unique (Sname);
3) Modify: Used to modify the original column definition, including modifying the column name and data type, the column data type modification may lose the original data     Alert Table student Modify Sage smallint;
        Note: SQL does not provide the ability to delete attribute columns, if you need to delete columns, you must indirectly through the SELECT statement, copy the contents of the required columns to another table, delete the original table, and then name the new table as the original table name3. Delete base table drop table < table name > 4, Build and delete index 5.1 create [unique] [cluster] Index < index name > on < Table name > (< column name > [< order][,< Column name >[< order] ...); Cases:              Create unique index studentindex on student (SNO);Note: 1) Unique: Indicates that each index value for this index is only one record for Chong 2)cluster: The index to be established when the table is clustered, that is, after a column is indexed, the data storage order on the hard disk is also adjusted to be stored in the order of the column, and the order of the indexes is consistent with the order of storage.
5.2 Delete Drop Index < index name >; example:              DROP index studentindex;
Links:http://blog.csdn.net/zdwzzu2006/article/details/2298777


From for notes (Wiz)

"MySQL notes" data definition language DDL