MySQL Common statement Summary

1. Create a statement

  CREATE DATABASE database_name//Creating databases

Delete Table

DROP TABLE IF EXISTS ' t_social_user_extend ';

//Build table
CREATE TABLE ' t_social_user_extend ' (' id ' bigint (20) not NULL auto_increment, ' user_id ' bigint () Not NULL COMMENT ' user ID ', ' birthday ' date DEFAULT NULL COMMENT' Date of birth ', ' research_field ' varchar (DEFAULT NULL COMMENT ' research field ', ' interest_field ' varchar (DEFAULT NULL COMMENT ' areas of interest ', ' photo_url ' varchar (DEFAULT NULL COMMENT ' Avatar ', ' sex ' varchar (DEFAULT NULL COMMENT ' gender ', ' QQ ' varchar (DEFAULT NULL COMMENT ' QQ ', ' WeChat ' varchar (DEFAULT NULL COMMENT ", ' contact_address ' varchar (DEFAULT NULL COMMENT ' contact address ', ' user_name ' varchar () DEFAULT NULL COMMENT ' user name ', ' password ' varchar (DEFAULT NULL COMMENT ' password ', ' phone ' varchar (DEFAULT NULL COMMENT ' phone ', ' real_name ' varchar ((+) DEFAULT NULL COMMENT ' real name ', PRIMARY KEY (' id ')) ENGINE=innodb DEFAULT Charset=utf8 comment= ' utf8_general_ci '

2. Common statements

　　Select:select column name from table name and: SELECT * from table name

Where:select column name from table name WHERE column operator value

Distinct:select DISTINCT column name from table name

Insert:insert into table_name (column 1, column 2,...) Values (value 1, value 2,....)

Update:update table name SET column name = new value WHERE Column name = value

DELETE from table name WHERE column name = value

3. Advanced Phrases

Limit:select column_name (s) from table_name limit start number, number of bars//select * from T_social_user limit 10, 10; 10 per page, second page

Like:select column_name (s) from table_name WHERE column_name like '%pattern% '

In:select column_name (s) from table_name WHERE column_name in (value1,value2,...)

Between:select column_name (s) from table_name WHERE column_name between value1 and value2

　As:select column_name as Alias_name from table_name

　　JOIN: SELECT table_name1.column_name1, table_name1.column_name2, Table_name2.column_name3 from Table_name1, table_name2 where table_name1.column_name5 = table_name2.column_name6//According to the relationship between tables get data from two or more tables

JOIN: Returns a row if there is at least one match in the table

Left JOIN: Returns all rows from the table, even if there is no match in the right table

Right JOIN: Returns all rows from the correct table even if there is no match in the left table

Full JOIN: Returns a row if there is a match in one of the tables

Union:select column_name (s) from table_name1 UNION SELECT column_name (s) from table_name2//combine result sets of two or more SELECT statements

Create select:create Table new_table_name SELECT * FROM Old_tablename//Select data from one table, then insert the data into another table or use it to create a backup copy of the table or to archive the records. SELECT Into:select * to New_table_name [in externaldatabase] of SQL equivalent _tablename

SQL Constraints (constraints), which mainly include the following constraints: Not NULL, UNIQUE, PRIMARY key, FOREIGN key, CHECK, DEFAULT

CHECK: used to limit the range of values in a column, such as when a table is builtCHECK (id>0)

　　CREATE INDEX : CREATE INDEXIndex_name on table_name (COLUMN_NAME)//Create an index in a table where the index enables database applications to find data faster without reading the entire table.

Drop:alter TABLE table_name DROP INDEX index_name

　　　　   DROP Table Name

DROP database name

Alter:alter TABLE table_name Add COLUMN_NAME datatype//Add column

ALTER TABLE table_name DROP column column_name  //Delete columns

ALTER TABLE table_name ALTER COLUMN COLUMN_NAME datatype//Change columns data type

INCREMENT:

CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT,   //自增长，在新记录插入表中时生成一个唯一的数字 LastName varchar (255) not NULL, FirstName varchar (255), Address varchar (255), City varchar (255), PRIMARY KEY (P_Id) )

VIEW: Create VIEW view_name as SELECT column_name (s) from table_name WHERE condition//creates an attempt to

Date:

MySQL Common statement Summary