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_engine

IF 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 conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

WHERE子句

MySQL WHERE

WHERE子句除了用在SELECT中还可以在UPDATE、DELETE中使用

比较运算符

Operator	Description
=	等于
<> or !=	不等于
<	小于
>	大于
<=	小于等于

= | 大于等于

AND 操作符

MySQL AND Operator

语法

WHERE boolean_expression_1 AND boolean_expression_2

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR 操作符

MySQL OR Operator

boolean_expression_1 OR boolean_expression_2

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

需要注意的是AND操作符的优先级大于OR操作符

BETWEEN

MySQL BETWEEN

表示的是区间条件

语法

expr [NOT] BETWEEN begin_expr AND end_expr;

expr在/不在区间begin_expr和end_expr之间

expr、begin_expr和end_expr的数据类型必须相同

例子

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;

LIKE

MySQL LIKE

语法

expr LIKE 'pattern'

LIKE 支持以下通配符

百分号%：匹配任何字符出现的任意次数（0次或多次）
下划线_：匹配当个任意字符

IN

MySQL IN

语法

SELECT 
    column1,column2,...
FROM
    table_name1
WHERE 
 (expr|column_1) [NOT] IN ('value1','value2',...);

如果column_1或表达式的结果在集合中有匹配，将返回1否则返回0

例子

SELECT 
    officeCode, 
    city, 
    phone, 
    country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

IS NULL

A 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
    table
WHERE
    where_conditions
GROUP 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 GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

为了修复这个错误，我们应该把address字段从SELECT中去掉或者在GROUP BY中添加address

SELECT 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子句中指定相同的表达式。不能使用别名。

HAVING

MySQL HAVING

与GROUP BY配合使用，用于过滤分组

语法

HAVING condition

HAVING支持所有WHERE操作符(AND、OR、IN、BETWEEN、LIKE)

ORDER BY

MySQL ORDER BY: Sort a Result Set

语法

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

注意：

先按column1排序再按column2排序，这里只有当column1中的值相同时才按照column2排序
如果未指定ASC或DESC，默认ASC

LIMIT

MySQL LIMIT

语法

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

LIMIT有两个参数：

offset: 指定开始的位置，从0开始
count: 代表要返回的行数

子查询

MySQL Subquery

使用子查询的几种形式：

在WHERE中使用

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

SELECT 
    customerNumber, checkNumber, amount
FROM
    payments
WHERE 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和t2

CREATE 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.id
FROM
    t1
CROSS JOIN t2;

运行结果如下：

Cross join采用笛卡尔积的规则，实际上是将两个表相乘，得到一个组合表（3*3条数据）

Inner join

SELECT 
    t1.id, t2.id
FROM
    t1
        INNER JOIN
    t2 ON t1.pattern = t2.pattern;

Inner join 实际上是对Cross join的条件过滤，它将不满足t1.pattern = t2.pattern的纪录过滤掉：

Left join

获取左表所有记录，即使右表没有对应匹配的记录

SELECT 
    t1.id, t2.id
FROM
    t1
        LEFT JOIN
    t2 ON t1.pattern = t2.pattern
ORDER BY t1.id;

Right join

与Left join相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SELECT 
    t1.id, t2.id
FROM
    t1
        RIGHT JOIN
    t2 on t1.pattern = t2.pattern
ORDER BY t2.id;

组合查询（UNION）

MySQL UNION

用于组合多个SELECT查询

语法

SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
UNION [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,...);

注意：

列和值需要一一对应

UPDATE

MySQL UPDATE

语法

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
[WHERE
    condition];

UPDATE支持两种修饰符：

LOW_PRIORITY：延迟更新操作直到当前表没有读取操作，不过只有部分存储引擎支持该修饰符，比如：MyISAM, MERGE, MEMORY
IGNORE：允许Mysql在发生错误时继续更新操作

DELETE

MySQL DELETE

语法

DELETE FROM table_name
WHERE condition;

如果WHERE条件没有指定，就会删除该表所有纪录

Mysql中的常用sql语句汇总

表（Table）

创建表

数据检索(SELECT)

WHERE子句

比较运算符

AND 操作符

OR 操作符

BETWEEN

LIKE

IN

IS NULL

GROUP BY

HAVING

ORDER BY

LIMIT

子查询

联结（JOIN）

组合查询（UNION）

数据插入和更新

INSERT

UPDATE

DELETE

superTerrorist

引用和评论

解决 videojs 播放视频进度条下方左侧当前播放时长一直为 0 的问题

解决lodash由于依赖错误导致无法启动的问题

阿里云ECS服务器部署Node.js项目全过程详解

浅读TiKV源码：Coprocessor

SQL优化还不会？速来掌握这15个小技巧

SAP Fiori应用的三种部署方式

安装vmware配置ubuntu linux环境上篇

Mysql中的常用sql语句汇总

表（Table）

创建表

数据检索(SELECT)

WHERE子句

比较运算符

AND 操作符

OR 操作符

BETWEEN

LIKE

IN

IS NULL

GROUP BY

HAVING

ORDER BY

LIMIT

子查询

联结（JOIN）

组合查询（UNION）

数据插入和更新

INSERT

UPDATE

DELETE

superTerrorist

引用和评论

解决 videojs 播放视频进度条下方左侧当前播放时长一直为 0 的问题

解决lodash由于依赖错误导致无法启动的问题

阿里云ECS服务器部署Node.js项目全过程详解

浅读TiKV源码：Coprocessor

SQL优化还不会？速来掌握这15个小技巧

SAP Fiori应用的三种部署方式

安装vmware配置ubuntu linux环境 上篇

安装vmware配置ubuntu linux环境上篇