MySQL Common syntax

Reference Links:http://www.yiibai.com/mysql/First, Sqlect grammar

SELECT   from [INNER | Left | right]JOINin WHEREGROUP by have ORDER by column_1 LIMIT offset, length;

The SELECT statement consists of several clauses as described in the following list:

• The select is followed by a comma-delimited column or a list of asterisks (*), indicating that all columns are to be returned.
• from specifies the table or view for which you want to query data.
• Join fetches data from other tables based on certain join conditions.
• Where filters the rows in the result set.
• Group by combines a set of rows into small groupings and applies aggregate functions to each small group.
• Having filters are based on small groupings defined by the GROUP BY clause.
• Order BY specifies a list of columns to use for sorting.
• Limit limits the number of rows returned.

The select and from statements in the statement are mandatory, and the other parts are optional. Chestnuts:

SELECT  from Employees;

SELECT *  from Employees;

Second, WHERE statementThe WHERE clause allows you to specify the row to select based on the specified filter expression or condition.Chestnuts:Obtain the sales representative in the employee;

SELECT  from WHERE = ' Sales Rep ';

MySQL will first use where to match, in the to select Match.Obtain the sales representative in the employee, and the Office code =1;

SELECT  from WHERE jobtitle='Sales Rep'and=1;

Operator:= equals, almost any data type is available<> or! = is not equal to< Less, usually using numeric and log/time data types> greater than, usually using numbers and log/time data types<= greater than or equal to>= greater than or equal toThere are also useful operators that can be used in the WHERE clause to form complex conditions, such as:

• between Select the value within the given range value.
• like matches a value based on pattern matching.
• inch Specifies whether the value matches any value in the list.
• Is null to check whether the value is null.

three, INSERT statementThe MySQL INSERT statement inserts one or more rows of data into the list.Chestnuts:

INSERT  into VALUES ('learn MySQL INSERT','2017-11-27','  2017-11-27','start learning. ');

Copy all data from the tasks table into the Tasks_bak table

INSERT  into SELECT *  from tasks;

Iv. UPDATE StatementThe UPDATE statement updates the existing data in the table. You can also use the UPDATE statement to change the column values for a single row, a set of rows, or all rows in a table. Chestnuts:update Mary's mailbox.

UPDATE SET Email='[email protected]'WHERE=1056 ;

To update multiple rows of data:

UPDATE SET Email='[email protected]', lastname='Hill  'WHERE=1056;

select data from another table to insert the current table

UPDATE SET = (SELECTfromWHERE jobtitle='Sales Rep'ORDER by RAND1,WHEREisNULL;

Note: A employeenumber data for Jobtitle=sales rep is randomly extracted from the Employees table and inserted into a column with an empty salesrepemployeenumber of customers.v. Delect statements

DELETE  from WHERE task_id=3;

After deletion:vi. Creating and deleting Databases

CREATE DATABASE IF  not EXISTS mytest;

Delete Database MyTest

DROP DATABASE IF EXISTS mytest;

Seven. CREATE TABLE statement

CREATE TABLE [IF not EXISTS] table_name (column_list) engine=table_type;

engine is the designated search engine, does not add the default InnoDB is used, there is myisam,heap,< Span style= "FONT-SIZE:12PX; Font-style:italic ">example,CSV ,archive , mergefederated Ndbcluster optional. column_list detailed semantics:

column_name data_type[size][notnull| NULL][DEFAULT value][auto_increment] PRIMARY KEY (Col1,col2,...)

• column_name The name of the specified column. Each column has a specific data type and size, for example: VARCHAR (255).
• Not NULL or null indicates whether the column accepts NULL values.
• The default value is used to specify the defaults for the column.
• Auto_increment indicates that the value of the column automatically increases whenever a new row is inserted into the table. Each table has one and only one auto_increment column.
• PRIMARY key to set the primary key.

ChestnutSyntax:

CREATE TABLE IF  not EXISTSTasks (task_idINT( One) not NULLAuto_increment, subjectVARCHAR( $)DEFAULT NULL, start_date dateDEFAULT NULL, end_date dateDEFAULT NULL, descriptionVARCHAR( $)DEFAULT NULL,PRIMARY KEY(task_id)) ENGINE=InnoDB;

Eight, ALTER TABLE statementGrammar:

ALTER TABLE table_name action1[, Action2,... ]

• First, specify the name of the table you want to change after the ALTER table clause.
• Second, list the set of actions to apply to the table. Actions can be any operation that adds a new column, adds a primary key , renames a table, and so on. The ALTER Table statement allows multiple operations to be applied in a single ALTER table statement, each separated by a comma (,).

Chestnuts:

ALTER TABLE ADD COLUMN INT (one notNULL;

Other syntax:

ALTER TABLE COLUMN INT (notNULL  auto_increment; ALTER TABLE ADD COLUMN INT (one notNULL; ALTER TABLE  to

MySQL Common syntax