开发必备----SQL备忘录

前言

唉，说是Java开发，其实写的最多的还是SQL，搞得我有点迷啊，我这是SQL工程师吧。。。。不说了，还是写一篇备忘录吧，一方面巩固，一方面方便自己查询，没有的就查漏补缺，后面可能会根据不同数据库，场景，性能来添油加醋。。。

说明一下

这个SQL备忘录主要使用于MySQL，其他数据库可能略有不同，后面会总结一下。。。

DDL（数据定义语言）

创建新数据库

CREATE DATABASE [数据库名]

删除数据库

DROP DATABASE [数据库名]

同理，创建和删除表一样写法，DATABASE 改成 TABLE

创建索引

CREATE INDEX [索引名] ON [表名]

删除索引

DROP INDEX [索引名]

DML（数据操作语言）

insert（插入）

单条插入

insert into students (class_id,name,gender,score) values (2,'大牛','M',80);

多条插入

insert into students (class_id,name,gender,score) values 
(2,'大牛','M',80),
(3,'小牛','S',30);

delete（删除）

单条

delete from students where id = 1;

多条

delete from students where id >= 5 and id <= 7;

更新（update）

单条

update students set name = '大牛',score = 66 where id = 1;

多条

update students set name = '小牛',score = 77 where id >= 5 and id <= 10;

使用表达式

update students set score = score + 10 where id <= 20;

---

咳咳，很快啊，重点来了

查询（select）

select常见完整语句

select [目标列名] 
from [数据表名]
[where 行条件表达式]
[group by [列名]]
[having [组表达式]]
[order by [列名] [asc/desc]]

经典查询

查询所有

select * from students;

实际项目中尽量不要使用，影响性能

查询单行、多行

select name,class_id from students where id = 10;

select name,class_id from students where id > 10 and id < 20;

投影查询

让结果集仅包含指定列
SELECT 列1, 列2, 列3 FROM ...
例子：

SELECT id, score, name FROM students;

起别名
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...;
可以给每一列起个别名，结果集的列名可以与原表的列名不同
例子：

SELECT id stu_id, score points, name stu_name FROM students;

WHERE

SELECT id, score points, name FROM students WHERE gender = 'M';

排序

ORDER BY

ASC升序

SELECT id, name, gender, score FROM students ORDER BY score;

默认的排序规则是ASC：“升序”，即从小到大，ASC可以省略，即 ORDER BY score ASC = ORDER BY score

DECS降序

SELECT id, name, gender, score FROM students ORDER BY score DESC;

多排序

当score列有相同的数据，要进一步排序，可以继续添加列名，例如使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

WHERE
如果有WHERE子句，ORDER BY子句要放到WHERE子句后面,先查出来在排序

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

LIMIT OFFSET

或 LIMIT N,M （例：limit 0,20：第0条记录开始，拿20条数据）

实现：
要实现分页功能，实际上就是从结果集中显示第1到100条记录作为第1页，显示第101到200条记录作为第2页，以此类推。
先进行排序：

SELECT id, name, gender, score FROM students ORDER BY score DESC;

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。
如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

LIMIT 3表示的意思是“最多3条记录”。

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT OFFSET 分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

SELECT COUNT(*) FROM students;

COUNT((星号))表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT((星号))。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

SELECT COUNT(*) num FROM students;

WHERE

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

函数 说明
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

SELECT AVG(score) average FROM students WHERE gender = 'M';

如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

分组聚合

GROUP BY

SELECT COUNT(*) num FROM students GROUP BY class_id;

执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。
但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数。

多表查询

查询多张表

SELECT * FROM students, classes;

students表的每一行与classes表的每一行都两两拼在一起返回。
结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

正确用法

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

表允许设置别名

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

WHERE

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

连接查询是另一种类型的多表查询。
连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

上面的结果集只有class_id列，缺少对应班级的name列。
最常用的一种内连接——INNER JOIN来实现：（内连接）

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

INNER JOIN查询的写法是：

先确定主表，仍然使用FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
可选：加上WHERE子句、ORDER BY等子句。

使用别名不是必须的，但可以更好地简化查询语句。

函数

总数

select count as totalcount 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

高级查询运算词

UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和
TABLE2）并消去表中任何重复行而派生出一个结果表。

当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。

两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

EXCEPT 运算符

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。

当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

INTERSECT 运算符

INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。

当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

使用外连接

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/cross（outer）join

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

分组Group by

一张表，一旦分组 完成后，查询后只能得到组相关的信息。

组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)

在select统计函数中的字段，不能和普通的字段放在一起。