1. SQL操作符

1.1 ALL、ANY和SOME子查询

ALL和ANY操作符的常见用法是结合一个相对比较操作符对一个数据列子查询的结果进行测试。它们测试比较值是否与子查询所返回的全部或一部分值匹配。
比如说，如果比较值小于或等于子查询所返回的每一个值，<= ALL将是true；
只要比较值小于或等于子查询所返回的任何一个值，<= ANY将是true。
SOME是ANY的一个同义词。

下面这条语句用来检索最早出生的总统，具体做法是选取出生日期小于或等于president数据表里的所有出生日期（只有最早的出生日期满足这一条件）的那个数据行：

SELECT last_name, first_name, birth FROM president WHERE birth <= ALL (SELECT birth FROM president);

下面这条语句的用处就不大了，它将返回所有的数据行，因为对于每个日期，至少有一个日期（它本身）大于或等于它：

SELECT last_name, first_name, birth FROM president WHERE birth <= ANY (SELECT birth FROM president);

当ALL、ANY或SOME操作符与”=”比较操作符配合使用时，子查询可以是一个数据表子查询。此时，你需要使用一个数据行构造器来提供与子查询所返回的数据行进行比较的比较值。

SELECT last_name, first_name, city, state FROM president 
 -> WHERE (city, state) = ANY 
 -> (SELECT city, state FROM president 
 -> WHERE last_name = 'Roosevelt');

1.2 IN和NOT IN

IN和NOT IN操作符是= ANY和< > ALL的简写。
也就是说，IN操作符的含义是”等于子查询所返回的某个数据行”，
NOT IN操作符的含义是”不等于子查询所返回的任何数据行”。

select * from table1 where a [not] in (‘值1','值2','值4','值6');

1.3 BETWEEN

BETWEEN了限制查询数据范围时包括了边界值,NOT BETWEEN则是不包括。
示例：

select * from table1 where time between time1 and time2 
select a,b,c, from table1 where a not between 数值1 and 数值2

1.4 EXISTS和NOT EXISTS

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。
EXISTS 指定一个子查询，检测行的存在。语法：EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句（不允许有 COMPUTE 子句和 INTO 关键字）。结果类型为 Boolean，如果子查询包含行，则返回 TRUE。

1.4.1 在子查询中使用 NULL

在子查询中指定 NULL，并返回结果集，通过使用 EXISTS 仍取值为 TRUE。

SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC

1.4.2 比较使用 EXISTS 和 IN 的查询

本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名。注意两个查询返回相同的信息。

SELECT title
FROM titles
WHERE EXISTS
 (SELECT *
FROM publishers
WHERE pub_id = titles.pub_id
AND city LIKE 'B%')

SELECT title
FROM titles
WHERE pub_id IN
 (SELECT pub_id
FROM publishers
WHERE city LIKE 'B%')

1.4.3 比较使用 EXISTS 和 = ANY 的查询

本示例显示查找与出版商住在同一城市中的作者的两种查询方法：第一种方法使用 = ANY，第二种方法使用 EXISTS。注意这两种方法返回相同的信息。

SELECT au_lname, au_fname
FROM authors
WHERE exists
 (SELECT *
FROM publishers
WHERE authors.city = publishers.city)

SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
 (SELECT city
FROM publishers)

1.4.4 NOT EXISTS

作用与 EXISTS 正相反。如果子查询没有返回行，则满足 NOT EXISTS 中的 WHERE 子句，返回TRUE。本示例查找不出版商业书籍的出版商的名称：

SELECT pub_name
FROM publishers
WHERE NOT EXISTS
 (SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name

1.5 GROUP BY

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

1.5.1 单组分类

某个员工信息表结构和数据如下：

id  name  dept  salary  edlevel  hiredate 
1 张三 开发部 2000 3 2009-10-11
2 李四 开发部 2500 3 2009-10-01
3 王五 设计部 2600 5 2010-10-02
4 王六 设计部 2300 4 2010-10-03
5 马七 设计部 2100 4 2010-10-06
6 赵八 销售部 3000 5 2010-10-05
7 钱九 销售部 3100 7 2010-10-07
8 孙十 销售部 3500 7 2010-10-06

例如，我想列出每个部门最高薪水的结果，sql语句如下：

SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
查询结果如下：
 DEPT MAXIMUM 
 开发部 2500
 设计部 2600
 销售部 3500

1.5.2 多组分类

例如，查询公司2010年入职的各个部门每个级别里的最高薪水：

SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL
查询结果如下：
 DEPT EDLEVEL MAXIMUM 
 设计部 4 2300
 设计部 5 2600
 销售部 5 3000
 销售部 7 3500

注意：在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对DEPT和EDLEVEL的每个唯一组合各返回一行。

1.6 HAVING

having字句用于组，常用于和 group by 配合使用，可以让我们筛选成组后的各种数据。
where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而 having子句在聚合后对组记录进行筛选。

例如：
显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区

SELECT region, SUM(population), SUM(area)FROM bbc
GROUP BY region HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。相反，having子句可以让我们筛选成组后的各组数据。

又如：
用group by和having子句联合来查出重复的记录,sql如下:

select uid,email,count(*) as ct from edm_user081217 GROUP BY email HAVING ct > 1

先用group by 对email进行分组,在用having来过滤大于1的,这样查找出来的就是重复的记录了。

1.7 IF和CASE

IF语句

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

case when语句

用于计算条件列表并返回多个可能结果表达式之一。

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

又如：

SELECT CASE 1 WHEN 1 THEN 'one'
WHEN 2 THEN 'two' 
ELSE 'more' END
as testCol

将输出one

2. SQL管理命令

2.1 USE 数据库名

选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

2.2 SHOW DATABASES

列出 MySQL 数据库管理系统的数据库列表。

2.3 SHOW TABLES

显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

use new_schema;
show tables;

2.4 SHOW COLUMNS FROM 数据表

显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

use new_schema;
show columns from students;

2.5 SHOW INDEX FROM 数据表

显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

3. 基本SQL语句

3.1 创建数据库

create DATABASE dbname;

3.2 删除数据库

drop database dbname;

3.3 创建新表

create table students
 (
 id int unsigned not null auto_increment primary key,
 name char(8) not null,
 sex char(4) not null,
 age tinyint unsigned not null,
 tel char(13) null default "-"
 );

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表) 
B：create table tab_new as select col1,col2… from tab_old definition only

3.4 删除新表

drop table tabname;

3.5 增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。

3.6 添加/删除主键

Alter table tabname add primary key(col)
Alter table tabname drop primary key(col)

3.7 创建/删除索引

create [unique] index idxname on tabname(col….)
drop index idxname

注：索引是不可更改的，想更改必须先删除再重建。

3.8 创建/删除视图

create view viewname as select statement
drop view viewname

3.9 选择、插入、删除等简单语句

选择

select * from table1 where 范围

插入

insert into table1(field1,field2) values(value1,value2)
例：
insert into students values (3,'Candy','女',18,'876');

删除

delete from table1 where 范围 
例：
delete from new_schema.students where id=3;

更新

update table1 set field1=value1 where 范围
例：
update students set age=20 where id=3;

查找

select * from table1 where field1 like '%value1%'

其中like的用法类似正则表达式。

排序

select * from table1 order by field1,field2 [desc/asc]

其中desc是降序，asc是升序。

总数

select count(*) as ctname from table1

求和

select sum(field1) as sumvalue from table1

平均

select avg(field1) as avgvalue from table1

最大

select max(field1) as maxvalue from table1

最小

select min(field1) as minvalue from table1

3.10 高级查询运算词

UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。
两种情况下，派生表的每一行不是来自 TABLE1 就是来自TABLE2。

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。
当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。
当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。

3.11 外连接

内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现。
外连接: 包括
- 左外连接(左边的表不加限制)
- 右外连接(右边的表不加限制)
- 全外连接(左右两表都不加限制)

left outer join

左外连接：结果集里既包括连接表的匹配行，也包括左连接表的所有行。

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

right outer join

右外连接：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

full outer join

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

3.12 复制表结构

只复制表结构：

create table s2 select * from s1 where 1=2;

这里，因为1不等于2，所以就只是复制s1的表结构到s2。

3.13 复制表数据

复制整个表的数据(假设两个表结构一样) ：

insert into s2 select * from s1;

3.14 复制表结构和数据

create table s2 select * from s1;

3.15 子查询

子查询分为如下几类：

标量子查询：返回单一值的标量，最简单的形式。
列子查询：返回的结果集是 N 行一列。
行子查询：返回的结果集是一行 N 列。
表子查询：返回的结果集是 N 行 N 列。

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

标量子查询

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。
可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧
示例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1) 
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2) 
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

列子查询

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询的返回。
可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧。
示例：

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1) 
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2) 
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

行子查询

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。
例子：

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)

注：(1,2) 等同于 row(1,2)

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

表子查询

指子查询返回的结果集是 N 行 N 列的一个表数据。
例子：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

4. SQL实现功能举例

4.1 同步删除主表中的已经在副表中删除的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

4.2 四表联查

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

4.3 提前五分钟提醒日程安排

select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

其中datediff()函数是计算时间差的函数。

4.4 一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 
desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

4.5 前10条记录

select top 10 * form table1 where 范围

4.6 选择所有在 A 中但不在 B 和 C 中的行，并消除所有重复行

(select a from tableA ) except (select a from tableB) except (select a from tableC)

4.7 随机取出10条数据

select top 10 * from tablename order by newid()

4.8 随机选择记录

select newid()

4.9 删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

4.10 选择从10到15的记录

select * from students order by id limit 9,15;

其中，limit操作符用于限制显示结果的范围。

limit n,m

表示显示第（n+1）到m条数据。

4.11 b值相同时，对应的a最大的记录

类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等。

select * from students s1 where age=(select max(age) from students s2 where s1.sex=s2.sex);

MySQL：常用操作符、管理命令、基本SQL语法等