"MYSQL" Grammar Review

First, the data type

Source: http://www.runoob.com/mysql/mysql-data-types.html

Second, the basic statement

1. Create a data table

//PRIMARY key auto-increment, T_user
CREATE TABLE IF  not EXISTS' T_user ' (' Id ')INTUNSIGNED auto_increment, ' Name 'VARCHAR( -) not NULL, ' Cardno 'VARCHAR( +) not NULL, ' Birthday ' DATE,PRIMARY KEY(' Id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

2. Delete Data Sheet

DROP TABLE T_school;

3. New data

INSERTVALUES(' king ', ' 0001 ', now ());

4. Delete data

Conditional delete
WHERE Id=3;

Empty table, self-increment primary key does not reset
DELETE from T_user;
Or, self-increment primary key reset
TRUNCATE TABLE T_user;

5. Update data

UPDATE SET Name=' queen ', Cardno='nx_01'WHERE Id=4;

6. Query data

Querying all data in a table
SELECT* from T_user;

Conditional query
SELECT * from T_user WHERE id>2 and Name are not NULL;

Group
SELECT Name from T_user GROUP by Name;

Sort, default ASC, order
SELECT * from T_user ORDER by Id DESC;

INNER join (inner JOIN, or equivalent connection: Gets a record of the field matching relationship in two tables.
Left join: Gets all the records of the left table, even if the right table does not have a matching record.
Right join: The opposite of the left join, which is used to get all the records of the right table, even if there are no matching records for the table.

INNRT JOIN t_school ts on TS. Id = TU. SchoolID
WHERE TS. ' Level ' = 1;

UNION All: Returns all result sets that contain duplicate data
UNION DISTINCT: Deletes duplicate data in the result set, defaults to reset
SELECT Id, Name from T_user WHERE classname= ' Three second class '
UNION All
SELECT Id, Name from T_user WHERE classname= ' three years five class '

Statistics
T_user;

7. Table field Modification

//new FieldALTER TABLET_userADDClassNameVARCHAR( -) not NULL;//Delete a fieldALTER TABLET_userDROPCardno;//Modify field Data PropertiesALTER TABLET_user MODIFY NameBIGINT  not NULL DEFAULT  -;//Modify Field namesALTER TABLET_user Change Name realnameVARCHAR( -);

Third, other functions

1. Judgment

SELECT     IF = 1 ' male ' ' female ' ) SEX from T_user;

2, comma splicing

SELECT     Group_concat (Name)fromten;

3. Empty sentence

SELECT   ' 0000 ') Cardno from T_user       WHERE Id>ten;

4. Convert data type

// Type Conversions SELECT CAST  as CHAR  from  // stitching An empty string, implicitly converting SELECT CONCAT (Id,' from T_user;

5, pagination and statistics of the number of rows

//Method OneSELECTTAB.*,        @i Total from        (SELECT                (@i:= @i + 1) asRowNum, B.*              fromBook B, (SELECT @i:= 0) asit) TABWHERETAB. RowNumbetween 5  and Ten//Method two:SET @i=0;SELECTTAB.*,        @i Total from        (SELECT                (@i:= @i + 1) asRowNum, B.*              fromBook B) TABWHERETAB. RowNumbetween 5  and Ten;SET @i:=NULL;

6. Time Format Conversion

//yyyy-MM-'%y-%m-%d%h:%i:%s')

"MYSQL" Grammar Review