本文整理自MySQL Tutorial和SQL必知必会

MySQL CREATE TABLE Statement By Examples

CREATE TABLE [IF NOT EXISTS] table_name(

column_list

) ENGINE=storage_engine

1. IF NOT EXISTS是可选的,但推荐使用,它会先检查是否有有同名表,如果没有则创建。

2. 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;

[alias1.]column_1, [alias1.]column_2, ...

table_1 [AS alias1],table_2 [AS alias2]

[INNER | LEFT |RIGHT] JOIN table_3 ON conditions

GROUP BY column_1

HAVING group_conditions

ORDER BY column_1

LIMIT offset, length;

MySQL WHERE

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

= | 大于等于

MySQL AND Operator

WHERE boolean_expression_1 AND boolean_expression_2

MySQL OR Operator

boolean_expression_1 OR boolean_expression_2

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

MySQL BETWEEN

表示的是区间条件

expr [NOT] BETWEEN begin_expr AND end_expr;

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

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

productCode,

productName,

buyPrice BETWEEN 90 AND 100;

expr LIKE 'pattern'

LIKE 支持以下通配符

百分号%:匹配任何字符出现的任意次数(0次或多次)

下划线_:匹配当个任意字符

column1,column2,...

table_name1

(expr|column_1) [NOT] IN ('value1','value2',...);

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

officeCode,

country IN ('USA' , 'France');

A Comprehensive Look at MySQL IS NULL Operator

value IS [NOT] NULL

注意:NULL表示无值,它与0、空字符、false不同

根据一个或多个列对结果集进行分组,通常而言, GROUP BY 会和 SUM、 AVG、 MAX、 MIN等函数使用

c1, c2,..., cn, aggregate_function(ci)

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)

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

SELECT name, address, MAX(age)

GROUP BY name,address;

不过有一些特例,具体可以参考MySQL Handling of GROUP BY

如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。

如果在GROUP BY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。 GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

MySQL HAVING

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

HAVING condition

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

MySQL ORDER BY: Sort a Result Set

SELECT column1, column2,...

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

先按 column1排序再按 column2排序,这里只有当 column1中的值相同时才按照 column2排序

如果未指定 ASC或 DESC,默认 ASC

MySQL LIMIT

column1,column2,...

LIMIT offset , count;

LIMIT有两个参数:

offset: 指定开始的位置,从0开始

count: 代表要返回的行数

MySQL Subquery

使用子查询的几种形式:

在 WHERE中使用

lastName, firstName

officeCode IN (SELECT

country = 'USA');

customerNumber, checkNumber, amount

WHERE amount = (SELECT

MAX(amount)

在 FROM中使用

MAX(items), MIN(items), FLOOR(AVG(items))

orderNumber, COUNT(orderNumber) AS items

orderdetails

GROUP BY orderNumber) AS lineitems;

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的用法:

t1.id, t2.id

CROSS JOIN t2;

运行结果如下:

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

t1.id, t2.id

t2 ON t1.pattern = t2.pattern;

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

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

t1.id, t2.id

t2 ON t1.pattern = t2.pattern

ORDER BY t1.id;

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

t1.id, t2.id

t2 on t1.pattern = t2.pattern

ORDER BY t2.id;

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 INTO table(c1,c2,...)

(v11,v12,...);

// 插入多行数据

INSERT INTO table(c1,c2,...)

(v11,v12,...),

(v21,v22,...),

(vnn,vn2,...);

列和值需要一一对应

MySQL UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_name

column_name1 = expr1,

column_name2 = expr2,

condition];

UPDATE支持两种修饰符:

1. LOW_PRIORITY:延迟更新操作直到当前表没有读取操作,不过只有部分存储引擎支持该修饰符,比如:MyISAM, MERGE, MEMORY

2. IGNORE:允许Mysql在发生错误时继续更新操作

MySQL DELETE

DELETE FROM table_name

WHERE condition;

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