Mysql中的常用sql语句汇总

如果你对NodeJs系列感兴趣，欢迎关注微信公众号：前端神盾局或 github NodeJs系列文章本文整理自MySQL Tutorial和SQL必知必会表（Table）创建表MySQL CREATE TABLE Statement By Examples语法CREATE TABLE [IF NOT EXISTS] table_name( column_list) ENGINE=storage_engineIF NOT EXISTS是可选的，但推荐使用，它会先检查是否有有同名表，如果没有则创建。storage_engine MySql 支持多种存储引擎: MyISAM、InnoDB、MERGE、MEMORY (HEAP)、ARCHIVE、CSV、FEDERATED，如果没有指定，默认值是InnoDB在创建表的时候需要给定字段（或列名），格式如下：column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]如果需要使用主键，可以使用以下语法：PRIMARY KEY (col1,col2,…)例子CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, status TINYINT NOT NULL, priority TINYINT NOT NULL, description TEXT, PRIMARY KEY (task_id)) ENGINE=INNODB;数据检索(SELECT)语法SELECT [alias1.]column_1, [alias1.]column_2, …FROM table_1 [AS alias1],table_2 [AS alias2][INNER | LEFT |RIGHT] JOIN table_3 ON conditionsWHERE conditionsGROUP BY column_1HAVING group_conditionsORDER BY column_1LIMIT offset, length;WHERE子句MySQL WHEREWHERE子句除了用在SELECT中还可以在UPDATE、DELETE中使用比较运算符OperatorDescription=等于<> or !=不等于<小于>大于<=小于等于= | 大于等于AND 操作符MySQL AND Operator语法WHERE boolean_expression_1 AND boolean_expression_2ANDTRUEFALSENULLTRUETRUEFALSENULLFALSEFALSEFALSEFALSENULLNULLFALSENULLOR 操作符MySQL OR Operatorboolean_expression_1 OR boolean_expression_2ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULLNULL需要注意的是AND操作符的优先级大于OR操作符BETWEENMySQL BETWEEN表示的是区间条件语法expr [NOT] BETWEEN begin_expr AND end_expr;expr在/不在区间begin_expr和end_expr之间expr、begin_expr和end_expr的数据类型必须相同例子SELECT productCode, productName, buyPriceFROM productsWHERE buyPrice BETWEEN 90 AND 100;LIKEMySQL LIKE语法expr LIKE ‘pattern’LIKE 支持以下通配符百分号%：匹配任何字符出现的任意次数（0次或多次）下划线_：匹配当个任意字符INMySQL IN语法SELECT column1,column2,…FROM table_name1WHERE (expr|column_1) [NOT] IN (‘value1’,‘value2’,…);如果column_1或表达式的结果在集合中有匹配，将返回1否则返回0例子SELECT officeCode, city, phone, countryFROM officesWHERE country IN (‘USA’ , ‘France’);IS NULLA Comprehensive Look at MySQL IS NULL Operator语法value IS [NOT] NULL注意：NULL表示无值，它与0、空字符、false不同GROUP BY[](http://www.mysqltutorial.org/…根据一个或多个列对结果集进行分组，通常而言，GROUP BY 会和SUM、AVG、MAX、MIN等函数使用语法SELECT c1, c2,…, cn, aggregate_function(ci)FROM tableWHERE where_conditionsGROUP BY c1 , c2,…,cn;使用GROUP BY需要注意以下几点：GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。GROUP BY必须在FROM和WHREE之后，ORDER BY之前一般情况下，SELECT中出现的非聚合函数字段，GROUP BY语句中也应该存在，比如SELECT name, address, MAX(age) FROM t GROUP BY name;运行此sql会报错：ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUPBY clause and contains nonaggregated column ‘mydb.t.address’ whichis not functionally dependent on columns in GROUP BY clause; thisis incompatible with sql_mode=only_full_group_by为了修复这个错误，我们应该把address字段从SELECT中去掉或者在GROUP BY中添加addressSELECT name, address, MAX(age) FROM t GROUP BY name,address;不过有一些特例，具体可以参考MySQL Handling of GROUP BY如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。 GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。HAVINGMySQL HAVING与GROUP BY配合使用，用于过滤分组语法HAVING conditionHAVING支持所有WHERE操作符(AND、OR、IN、BETWEEN、LIKE)ORDER BYMySQL ORDER BY: Sort a Result Set语法SELECT column1, column2,…FROM tblORDER BY column1 [ASC|DESC], column2 [ASC|DESC],…注意：先按column1排序再按column2排序，这里只有当column1中的值相同时才按照column2排序如果未指定ASC或DESC，默认ASCLIMITMySQL LIMIT语法SELECT column1,column2,…FROM tableLIMIT offset , count;LIMIT有两个参数：offset: 指定开始的位置，从0开始count: 代表要返回的行数子查询MySQL Subquery使用子查询的几种形式：在WHERE中使用SELECT lastName, firstNameFROM employeesWHERE officeCode IN (SELECT officeCode FROM offices WHERE country = ‘USA’);SELECT customerNumber, checkNumber, amountFROM paymentsWHERE amount = (SELECT MAX(amount) FROM payments);在FROM中使用SELECT MAX(items), MIN(items), FLOOR(AVG(items))FROM (SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS lineitems;联结（JOIN）MySQL Join Made Easy For Beginners在实际业务中，我们经常把数据分表存放，那如何把多个表里的数据用一个SELECT语句查询出来呢？这就需要联结。联结分成以下几种类型：Cross join、Inner join、Left join和Right join下面通过一个例子来讲解它们之间的区别。首先我们创建两个表t1和t2CREATE TABLE t1 ( id INT PRIMARY KEY, pattern VARCHAR(50) NOT NULL); CREATE TABLE t2 ( id VARCHAR(50) PRIMARY KEY, pattern VARCHAR(50) NOT NULL);t1和t2表都有pattern列，现在我们插入一些数据INSERT INTO t1(id, pattern)VALUES(1,‘Divot’), (2,‘Brick’), (3,‘Grid’); INSERT INTO t2(id, pattern)VALUES(‘A’,‘Brick’), (‘B’,‘Grid’), (‘C’,‘Diamond’);Cross join我们先来看一下Cross join的用法：SELECT t1.id, t2.idFROM t1CROSS JOIN t2;运行结果如下：Cross join采用笛卡尔积的规则，实际上是将两个表相乘，得到一个组合表（3*3条数据）Inner joinSELECT t1.id, t2.idFROM t1 INNER JOIN t2 ON t1.pattern = t2.pattern;Inner join 实际上是对Cross join的条件过滤，它将不满足t1.pattern = t2.pattern的纪录过滤掉：Left join获取左表所有记录，即使右表没有对应匹配的记录SELECT t1.id, t2.idFROM t1 LEFT JOIN t2 ON t1.pattern = t2.patternORDER BY t1.id;Right join与Left join相反，用于获取右表所有记录，即使左表没有对应匹配的记录。SELECT t1.id, t2.idFROM t1 RIGHT JOIN t2 on t1.pattern = t2.patternORDER BY t2.id;组合查询（UNION）MySQL UNION用于组合多个SELECT查询语法SELECT column_listUNION [DISTINCT | ALL]SELECT column_listUNION [DISTINCT | ALL]SELECT column_list注意：UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。列数据类型必须兼容：类型不必完全相同，但必须是可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。UNION和JOIN的区别数据插入和更新INSERT语法// 插入单行数据INSERT INTO table(c1,c2,…)VALUES (v11,v12,…);// 插入多行数据INSERT INTO table(c1,c2,…)VALUES (v11,v12,…), (v21,v22,…), … (vnn,vn2,…);注意：列和值需要一一对应UPDATEMySQL UPDATE语法UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, …[WHERE condition];UPDATE支持两种修饰符：LOW_PRIORITY：延迟更新操作直到当前表没有读取操作，不过只有部分存储引擎支持该修饰符，比如：MyISAM, MERGE, MEMORYIGNORE：允许Mysql在发生错误时继续更新操作DELETEMySQL DELETE语法DELETE FROM table_nameWHERE condition;如果WHERE条件没有指定，就会删除该表所有纪录