MySQL Common statements:

SQL statements

To 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;

1 Create TableSchool (2Nidint  not NULL Primary Key,3Namevarchar( -)4) engine=InnoDBdefaultCharSet=UTF8;5 6 Create TableStudent (7Nidint  not NULL Primary Key,8Namevarchar( -),9Ageint,Tenschool_idint  not NULL, One     constraintFkForeign Key(school_id)ReferencesSchool (NID) A) engine=InnoDBdefaultCharSet=UTF8;

FOREIGN Key

To modify a table structure:

1To add a column:Alter TableTable nameAddcolumn name type;2To delete a column:Alter TableTable nameDrop columncolumn name;3 4 To Modify a column:5         Alter TableTable name Modifycolumncolumn name type;6         Alter Tabletable name change original column name new column name type;7 8To add a primary key:Alter TableTable nameAdd Primary Key(column name);9         TenTo delete a primary key:Alter TableTable nameDrop Primary Key; One         ATo add a foreign key:Alter TableFrom the tableAdd constraintFOREIGN Key NameForeign KeyFrom a table (Foreign key field)Referencesprimary table (primary key field); -To delete a foreign key:Alter TableTable nameDrop Foreign Keyforeign key name;

1 1. Increase:2     Insert  intoTable (column name 1, column Name 2 ...)Values(value 1, value 2 ...), (value 1, value 2 ...);3     Insert  intoTable 1 (Column name 1, column Name 2 ...)SelectColumn name 1, column name 2,... fromtable 2;4 5 2. By deleting:6     Delete  fromTablewhereId=1  andName='Yan';7 8 3. Change:9     UpdateTableSetName ='Yan' whereId>1;Ten  One 4. Check: A     SelectNid,name,age asAlias fromTablewhereId>1；

3. Conditional query:

1     Select *  fromTablewhereId>1  andName!='Yan'  andAge= +;2     Select *  fromTablewhereIdbetween 1  and 9;3     Select *  fromTablewhereIdinch(1,2,3);4     Select *  fromTablewhereId not inch(1,2,3)5     Select *  fromTablewhereIdinch(SelectNid fromtable);

Wildcard query:

1     Select *  from where  like  ' yan% '  ; 2     Select *  from where  like  ' Yan_ '

Limit query:

1     Select *  fromTable limit5;-Top 5 rows2     Select *  fromTable limit4,5;-5 lines starting from line 4th3     Select *  fromTable limit5Offset4    -5 lines starting from line 4th

Sort query:

1     Select *  fromTableOrder  byColumnASC;-arrange from small to large according to "column"2     Select *  fromTableOrder  byColumndesc;-arrange from large to small according to columns3     Select *  fromTableOrder  byColumn 1desc, column 2ASC;-Rank from large to small according to "column 1", if same, sort by column 2 small to large

Group query:

1     Select  from Group  by num; 2     Select  from Group  by  having Max (ID) > ten;

Even table query:

1     Select A.num, A.name, B.name 2      from  3left     join  B4on     = B.nid

MySQL Common statements: