MySQL Common syntax

Source: Internet
Author: User
Tags mysql insert

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.