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, MEMORYIGNORE
:允许Mysql在发生错误时继续更新操作
DELETE
MySQL DELETE
- 语法
DELETE FROM table_name WHERE condition;
- 如果
WHERE
条件没有指定,就会删除该表所有纪录