mysql-Database Operations

1. Create a table:

1 Create Table table name (2     column name  type  can be empty,3     column name  type  can be empty 4 ) ENGINE=DEFAULT CHARSET=UTF8

Build Table

1         Default value, you can specify a default value when creating a column, and automatically add a default value of 2              when inserting data if it is not actively set Create Table tb1 (3                 int  not NULL 2 , 4                 int  not NULL 5             )

Default Value

1 self-increment, if you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table)2             Create TableTB1 (3Nidint  not NULLAuto_incrementPrimary Key,4Numint NULL5             )6 or7             Create TableTB1 (8Nidint  not NULLAuto_increment,9Numint NULL,Ten                 Index(NID) One             ) AAttention:1, for self-increment columns, must be an index (with a primary key).  -                  2, and for self-increment, you can set the step and start values

Self-increment

primary key, a special unique index that does not allow null values, and if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique. Create Tabletb1 (Nidint  not NULLAuto_incrementPrimary Key, Numint NULL) orCreate Tabletb1 (Nidint  not NULL, Numint  not NULL,                Primary Key(nid,num))

PRIMARY Key

1 foreign key, a special index, can only be the specified content2creatTableColor (3Nidint  not NULL Primary Key,4NameChar( -) not NULL5             )6 7             Create TableFruit (8Nidint  not NULL Primary Key,9SmtChar( +)NULL ,Tencolor_idint  not NULL, One                 constraintFk_ccForeign Key(COLOR_ID)ReferencesColor (NID) A)

FOREIGN Key

2. Delete a table

Drop Table -- Delete a table

Delete Table

3. Clear the Table

Delete  from -- Clear All data truncate Table -- Clear all data and reset the table

Clear

4. Modify the table

1To add a column:Alter TableTable nameAddColumn Name Type2To delete a column:Alter TableTable nameDrop columnColumn Name3 To Modify a column:4         Alter TableTable name ModifycolumnColumn name type;--type5         Alter TableTable name change original column name new column name type;--column name, type6   7 To add a primary key:8         Alter TableTable nameAdd Primary Key(column name);9 To delete a primary key:Ten         Alter TableTable nameDrop Primary Key; One         Alter TableTable name Modify Column nameint,Drop Primary Key; A    -To add a foreign key:Alter TableFrom the tableAdd constraintForeign Key Name (shape: fk_ from Table _ Main Table)Foreign KeyFrom a table (Foreign key field)Referencesprimary table (primary key field); -To delete a foreign key:Alter TableTable nameDrop Foreign KeyFOREIGN Key Name the    -To modify the default value:ALTER TABLETestalter_tblALTERISET DEFAULT  +; -Delete default values:ALTER TABLETestalter_tblALTERIDROP DEFAULT;

Modify Table 6666

mysql-Database Operations