Common MySQL syntax and mysql syntax

Common MySQL syntax and mysql syntax
Reference: http://www.yiibai.com/mysql/ 1, SQLECT syntax

SELECT  column_1, column_2, ... 
FROM table_1 
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions 
WHERE 
　　conditions 
GROUP BY column_1 
HAVING group_conditions 
ORDER BY column_1 
LIMIT offset, length;

A select statement consists of the following clauses:

• SELECT is followed by a comma-separated column or an asterisk (*) List, indicating that all columns will be returned.
• FROM specifies the table or view of the data to be queried.
• JOIN obtains data from other tables based on certain JOIN conditions.
• WHERE filters the rows in the result set.
• Group by combines a GROUP of rows into small groups and applies an aggregate function to each small GROUP.
• The HAVING filter is based on the small GROUP defined BY the group by clause.
• Order by specifies the list of columns used for sorting.
• LIMIT limits the number of returned rows.

The SELECT and FROM statements in the statement are mandatory, and the other parts are optional. Chestnuts:

SELECT 
　　lastname,firstname,jobtitle 
FROM　
　　employees;

SELECT * FROM employees;

2. The WHERE clause of the WHERE statement allows you to specify the row to be selected based on the specified filter expression or condition. Chestnut: Get sales representatives from employees;

SELECT 
　　lastname,firstname,jobtitle 
FROM 
　　employees 
WHERE 
　　jobtitle = 'Sales Rep';

MySQL will first use where for matching and then select for matching. Obtain the sales representatives of employees and the office code is 1;

SELECT 
　　lastname,firstname,jobtitle 
FROM 
　　employees 
WHERE 
　　jobtitle='Sales Rep' AND officeCode = 1;

Operator: = equals. Almost any data type is available <> or! = Not equal to <less than, usually using numbers and log/time data types> greater, generally, numeric and log/time data types <= greater than or equal to> = greater than or equal to some useful operators can be used in the WHERE clause to form complex conditions. For example:

• BETWEEN specifies the value in the specified range.
• LIKE matches the value based on the pattern match.
• IN specifies whether the value matches any value IN the list.
• Is null checks whether the value is null.

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

INSERT INTO 
　　tasks(subject,start_date,end_date,description) 
VALUES
　　('Learn MySQL INSERT','2017-11-27','2017-11-27','start learning..');

Insert all data in the tasks table to the tasks_bak table.

 INSERT INTO tasks_bak SELECT * FROM tasks;

4. UPDATE statement to UPDATE existing data in the table. You can also use the UPDATE statement to change the column values of a single row, a group of rows, or all rows in the table. Chestnut: Update Mary's mailbox.

UPDATE 
　　employees 
SET 
　　email='mary.new@yiibai.com' 
WHERE 
　　employeeNumber = 1056;

Update multiline data:

UPDATE 
　　employees 
SET 
　　email='mary@yiibai.com',lastname='Hill' 
WHERE 
　　employeeNumber = 1056;

Insert data from another table to the current table

UPDATE customers 
SET 
　　salesRepEmployeeNumber = (SELECT 
　　　　　　employeeNumber 
　　　　FROM 
　　　　　　employees 
　　　　WHERE 
　　　　　　jobtitle='Sales Rep' 
　　　　ORDER BY RAND() LIMIT 1) 
WHERE 
　　salesRepEmployeeNumber IS NULL;

Note: A employeeNumber data of jobtitle = Sales Rep is randomly extracted from the employees table and inserted into the column where the salesRepEmployeeNumber of customers is null. V. DELETE statement

DELETE FROM tasks WHERE task_id=3;

After deletion: 6. Create and delete a database

CREATE DATABASE IF NOT EXISTS mytest;

Delete database mytest

DROP DATABASE IF EXISTS mytest;

VII. create table statement

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

The engine specifies the search engine. If no search engine is added, InnoDB is used by default. Optional values include MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE, FEDERATED, and NDBCLUSTER. Detailed Syntax of column_list:

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]PRIMARY KEY (col1,col2,...)

• Column_name specifies the column name. 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 DEFAULT value of a column.
• AUTO_INCREMENT indicates that the column value is automatically increased whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.
• Set the primary key for primary key.

Chestnut Syntax:

CREATE TABLE IF NOT EXISTS tasks ( 
　　　　task_id INT(11) NOT NULL AUTO_INCREMENT, 
　　　　subject VARCHAR(45) DEFAULT NULL, 
　　　　start_date DATE DEFAULT NULL, 
　　　　end_date DATE DEFAULT NULL, 
　　　　description VARCHAR(200) DEFAULT NULL, 
　　　　PRIMARY KEY (task_id) 
) ENGINE=InnoDB; 

8. alter table statement Syntax:

ALTER TABLE table_name action1[,action2,…]

• First, specify the name of the TABLE to be changed after the alter table clause.
• Next, list the operations that you want to apply to the table. You can add new columns, add primary keys, and rename tables. The alter table statement allows multiple operations to be applied in a single alter table statement. Each operation is separated by commas.

Chestnuts:

ALTER TABLE 
　　mytest 
ADD COLUMN 
　　task_id INT(11) NOT NULL;

Other Syntax:

ALTER TABLE mytest CHANGE COLUMN task_id task_id INT(10) NOT NULL AUTO_INCREMENT;ALTER TABLE mytest ADD COLUMN task_id INT(11) NOT NULL;ALTER TABLE tasks RENAME TO work_items;