SQL Wiki

执行顺序

SQL 语句的书写顺序和实际执行顺序并不完全一致，先理清执行顺序，后续排查查询结果会更容易。

select id,
name,
subject,
avg(score) as avg_score
from students_grade
where pt >= '2019-01-01'
group by id,name,subject
having avg_score >= 60
order by avg_score desc

这些关键字的执行顺序是：FROM -> WHERE -> GROUP BY -> SELECT -> DISTINCT -> HAVING -> ORDER BY -> LIMIT/OFFSET。

在 FROM、WHERE、GROUP BY 执行完成后，才开始执行 SELECT。 HAVING 通常配合 GROUP BY 使用，用于筛选聚合计算后的结果。

SELECT 
    E.DepartmentID,
    E.Name,
    MAX(E.Salary) as MaxSalary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
WHERE 
    E.Status = 'Active'
GROUP BY 
    E.DepartmentID
HAVING 
    MAX(E.Salary) > 50000
ORDER BY 
    MaxSalary DESC;

当然，我可以通过一个具体的例子来帮助你理解SQL语句的执行顺序。假设我们有两个表：一个是员工表（Employees），另一个是部门表（Departments）。我们想找出每个部门薪资最高的员工信息以及他们的薪资。以下是相应的SQL查询和每个步骤的解释：

SELECT 
    E.DepartmentID,
    E.Name,
    MAX(E.Salary) as MaxSalary
FROM 
    Employees E
JOIN 
    Departments D ON E.DepartmentID = D.DepartmentID
WHERE 
    E.Status = 'Active'
GROUP BY 
    E.DepartmentID
HAVING 
    MAX(E.Salary) > 50000
ORDER BY 
    MaxSalary DESC;

执行顺序及解释：

FROM Employees E: 首先，SQL引擎会从Employees表中获取数据。
JOIN Departments D ON E.DepartmentID = D.DepartmentID: 然后，根据DepartmentID将Employees表和Departments表进行连接。
WHERE E.Status = 'Active': 接下来，只选择状态为'Active'的员工。
GROUP BY E.DepartmentID: 将结果按DepartmentID进行分组，为接下来的聚合函数（MAX）做准备。
SELECT E.DepartmentID, E.Name, MAX(E.Salary) as MaxSalary: 选取DepartmentID、员工姓名和每个部门最高的薪资。
HAVING MAX(E.Salary) > 50000: 筛选出平均薪资超过50000的那些部门。
ORDER BY MaxSalary DESC: 最后，按照最高薪资降序排序结果。

在这个例子中，你可以看到SQL语句的各个部分是如何按照特定的执行顺序组合在一起，以达到我们想要的查询结果。通过理解这个顺序，你可以更好地构建和优化你的SQL查询。

在 SQL 中，查询的关键词执行顺序如下：

FROM/JOIN：首先执行FROM和/或JOIN子句，以确定感兴趣的数据。
WHERE：执行WHERE子句，以过滤不符合约束条件的记录。
GROUP BY：执行GROUP BY子句，根据一个或多个列中的值对数据进行分组。
HAVING：执行HAVING子句，删除不符合约束条件的已创建分组记录。
SELECT：执行SELECT子句，获取所有所需的列和表达式。
ORDER BY：执行ORDER BY子句，对派生值按升序或降序排序。
LIMIT/OFFSET：最后执行LIMIT和/或OFFSET子句，以保留或跳过指定数量的行。

这些步骤按顺序执行，以便从数据库中找到所需的数据，并将其过滤和处理为最终结果。

名词解释

外键

外键：可以通过特定的指标，实现与其他表关联，这种列就称为外键。外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称 fk_class_id 可以任意指定；FOREIGN KEY (class_id) 指定了 class_id 作为外键；REFERENCES classes (id) 指定这个外键关联到 classes 表的 id 列，也就是 classes 表的主键。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果 classes 表不存在 id = 99 的记录，students 表就无法插入 class_id = 99 的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id 仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

如果要经常根据 score 列进行查询，就需要对score 列创建索引。

ALTER TABLE students
ADD INDEX idx_score (score);

使用 ADD INDEX idx_score (score) 就创建了一个名称为 idx_score、使用 score 列的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

-- 在duration列创建普通索引idx_duration
CREATE INDEX idx_duration ON examination_info(duration);

-- 在exam_id列创建唯一性索引uniq_idx_exam_id
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);

-- 在tag列创建全文索引full_idx_tag
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

数据库

保存有组织的数据的容器（通常是一个文件或者一组文件）。

表

在数据库领域中，表是一种结构化的文件，可以用来存储某种特定类型的数据。表可以保存顾客清单、产品目录、或者其他信息清单。

表是某种特定类型数据的结构化清单。

主键

表中每一行都应该有一列（或几列）可以唯一标识自己。一列（或一组列），其值能够唯一标识表中每一行。

主键需要满足以下条件：

任意两行都不具有相同的主键值。
每一行都必须具有一个主键值，主键列不允许 NULL 值。
主键列中的值不允许修改或者更新。
主键值不能重用；如果某行从表中删除，它的主键不能赋给以后的新行。

外键

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

让我们通过一个实例来解释外键。请看下面两个表：

创建数据库

create database samp_db character set gbk;
drop database samp_db; --删除库名为samp_db
show databases;
use samp_db ;--选择创建的数据库samp_db
show tables;--显示samp_db下面所有的表名字
describe

CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建数据库，该命令的作用：

如果数据库不存在则创建，存在则不创建。
创建RUNOOB数据库，并设定编码集为utf8

选择数据库

use course; -- course是数据库名

查看列：desc 表名;
修改表名：alter table t_book rename to bbb;
添加列：alter table 表名 add column 列名 varchar(30);
删除列：alter table 表名 drop column 列名;
修改列名MySQL： alter table bbb change nnnnn hh int;
修改列名SQLServer：exec sp_rename't_student.name','nn','column';
修改列名Oracle：lter table bbb rename column nnnnn to hh int;
修改列属性：alter table t_book modify name varchar(22);
删除行: delete from course where Cname = ''

数据类型

数值类型

类型	用途
TINYINT	小整数值
SMALLINT	大整数值
MEDIUMINT	大整数值
INT或INTEGER	大整数值
BIGINT	极大整数值
FLOAT	单精度浮点数值
DOUBLE	双精度浮点数值
DECIMAL	小数值

日期和时间

类型	用途
DATE	日期值
TIME	时间值或持续时间
YEAR	年份值
DATETIME	混合日期和时间值
TIMESTAMP	混合日期和时间值，时间戳

通过用 timestampdiff 函数可以实现两个时间戳的时间差。

delete
from
exam_record
where TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5 and score <60

字符串

类型	用途
CHAR	定长字符串
VARCHAR	变长字符串
TEXT	长文本数据
LONGTEXT	极大文本数据

char(n) 和 varchar(n) 中括号里n 代表字符的个数，并不代表字节的个数，比如CHAR(30) 就可以存储30个字符。

创建数据表

创建数据表需要包括表名、字段名，以及每个字段的定义。创建 MySQL 数据表的 SQL 通用语法是：

create table table_name (column_name column_type);

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5));

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果不想字段为 NULL 可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为 NULL，就会报错。
AUTO_INCREMENT 定义列为自增属性，一般用于主键，数值会自动加 1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

insert into grade values(90,100,'A')

insert into grade values(80,89,'B')

insert into grade values(70,79,'C')

insert into grade values(60,69,'D')

insert into grade values(0,59,'E')

删除数据库

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

drop database samp_db; --删除库名为samp_db

插入数据

例如，我们向students表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

insert into table_name (field1, field2,...,fieldN)
                       Values
                       (value1, value2,...,valueN);

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

SQL 别名

通过使用 SQL，可以为表名称或列名称指定别名。SQL 别名分为列别名和表别名。

select column_name AS alias_name from table_name;

表的 SQL 别名用法：

select column_name from table_name AS alias_name

为了插入ID为9003的高难度SQL试卷，且不管试卷ID是否已存在，都要插入成功，我们可以使用INSERT IGNORE或REPLACE语句。这里我们使用REPLACE语句来实现：

REPLACE INTO examination_info (exam_id, tag, difficulty, duration, release_time)
VALUES (9003, 'SQL', 'High', 90, '2021-01-01 00:00:00');

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号（,）分隔，并使用 WHERE 子句设定查询条件。 SELECT 命令可以读取一条或者多条记录。你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据。你可以使用 WHERE 语句来包含任何条件。你可以使用 LIMIT 属性来设定返回的记录数。你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

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

因此，分页实际上就是从结果集中“截取”出第M~N条记录。 这个查询可以通过LIMIT <M> OFFSET <N>子句实现。 我们先把所有学生按照成绩从高到低进行排序：

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;

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize； OFFSET计算公式为pageSize * (pageIndex - 1)。这样就能正确查询出第N页的记录集。

聚合查询

count 用于查看数据行数。

SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students;

分组

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

多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：

SELECT *
FROM <表1>, <表2>;

例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写：

SELECT * FROM students, classes;

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

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

你可能还注意到了，上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理

要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

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

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

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

连接查询

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 s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

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

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

LEFT JOIN

注意观察 LEFT JOIN 中两表的连接方式。

select a.device_id, b.question_id,b.result from
(select id,device_id from user_profile where university = '浙江大学') a left join question_practice_detail b
on a.device_id = b.device_id order by b.question_id asc

自链接

当然可以！让我用简单的语言来解释一下如何使用自连接（也叫自联结）来实现滚动统计。

什么是滚动统计？

滚动统计是一种计算方法，它会在数据序列中滑动一个窗口，对窗口内的数据进行统计。例如，计算每个月的销售额及其前两个月的总和，这样你可以看到一个三个月的滚动总销售额。

什么是自连接？

自连接是指在数据库查询中，将一个表与它自己连接起来。这在处理需要比较或组合同一表中不同记录的数据时非常有用。

如何用自连接实现滚动统计？

让我们通过一个简单的例子来说明。

假设你有一个销售记录的表，叫做 Sales，结构如下：

SaleID SaleDate Amount 1 2024-01-01 100 2 2024-01-02 150 3 2024-01-03 200 4 2024-01-04 250 5 2024-01-05 300

现在，你想计算每一天及其前两天的销售总额（即一个3天的滚动总和）。

步骤：

给表起别名：为了区分同一个表中的不同实例，我们给表起不同的别名，比如 s1 和 s2。
设定连接条件：我们希望将每一行 s1 与它前两天的行 s2 连接起来。假设 SaleDate 是连续的，可以用日期来进行匹配。
计算总和：在连接后，对 s1 和匹配的 s2 的 Amount 进行求和。

示例SQL查询：

SELECT s1.SaleDate,
       (s1.Amount + COALESCE(s2.Amount, 0) + COALESCE(s3.Amount, 0)) AS RollingTotal
FROM Sales s1
LEFT JOIN Sales s2 ON s1.SaleDate = DATEADD(day, 1, s2.SaleDate)
LEFT JOIN Sales s3 ON s1.SaleDate = DATEADD(day, 2, s3.SaleDate)
ORDER BY s1.SaleDate;

解释：

s1 是当前的销售记录。
s2 是当前记录的前一天的销售记录。
s3 是当前记录的前两天的销售记录。
COALESCE 函数用于处理可能没有前一天或前两天数据的情况，避免出现 NULL。

结果：

SaleDate RollingTotal 2024-01-01 100 2024-01-02 250 (100 + 150) 2024-01-03 450 (100 + 150 + 200) 2024-01-04 600 (150 + 200 + 250) 2024-01-05 750 (200 + 250 + 300)

总结

通过自连接，我们将同一个表中的不同时间点的数据关联起来，这样就可以在一个查询中同时访问当前行及其前面的几行数据。然后，通过对这些关联的数据进行计算，就实现了滚动统计。这种方法在需要基于时间序列进行分析时非常有用，尤其是在不支持窗口函数的数据库中。

希望这个解释对你有帮助！

更新表（UPDATE）

UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …；可以用 UPDATE 命令更新数据库。

update STUDENT SET SSEX = "女" where SNAME = '曾华'

例如，我们想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name='大牛', score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1：

UPDATE students SET name='大牛', score=66 WHERE id=1;

最后，要特别小心的是，UPDATE语句可以没有WHERE条件，例如：

UPDATE students SET score=60;

这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

update
examination_info
set tag = "Python"
where tag = "PYTHON"

注释

在 SQL 中，使用 -- 可以写行内注释；也可以写多行注释，从 /* 开始，到 */ 结束。

删除数据

delete from STUDENT where  SNAME = '曾华'

限制返回行数

R 中 head 通常用于展示前 6 行数据；在 SQL 中可以用 LIMIT 实现。

a1s <- sqldf("select * from iris limit 6")

正则匹配

之前学习过like %进行模糊匹配，MySQL 同样也支持其他正则表达式的匹配， MySQL 中使用 REGEXP 操作符进行正则表达式匹配。

模式	描述
^
$
.	匹配除
[…]
[^…]
p1	p2	p3	匹配p1或p2或p3
*	匹配前面的子表达一次或多次
+	匹配前面的子表达式一次或多次
{n}	匹配确定的n次

select * from STUDENT where SNAME regexp '王'

WHERE 条件

SQL 中的 WHERE 类似于数据处理语境里的过滤条件，本节属于条件查询。

like

在 MySQL 中，可以使用 SELECT 命令读取数据，并通过 WHERE 子句筛选记录。WHERE 子句可以使用等号设定精确条件；如果需要匹配包含特定字符的记录，则可以使用 LIKE。

#从iris 数据集中筛选出Species 开头是"set" 的记录
sqldf("select * from iris where Species like 'set%'")

select * from STUDENT where SNAME like "匡%"

like 通常紧跟通配符 "%",代表匹配0个以上的字符。找到所有电影名为“WALL-” 开头的电影。

SELECT * FROM movies where title like "%WALL-%"

R 语句可以这么写

library(data.table)
iris %>%
    filter(Species %like%  'set')

in

在 SQL 中，IN操作符用于检查一个值是否包含在指定的一组值中。它通常用在WHERE子句中，以过滤出那些列值等于列表中任一值的行。

IN操作符的语法为：SELECT column_name FROM table_name WHERE column_name IN (value1, value2,…, valuen)。其中：

column_name：要检查的值所在的列名；
value1, value2,…, valuen：要检查的值的列表，可以是常量、子查询或从其他表中选取的值。

例如，sqldf("select * from iris where Species in ('setosa','versicolor')")这条语句的作用是从iris表中选择Species列的值为'setosa'或'versicolor'的所有行。

使用IN操作符可以简化多个OR条件的情况，使查询更加清晰和简洁。例如，不用IN的话，上述查询可能需要写成：SELECT * FROM iris WHERE Species = 'etosa' OR Species = 'ersicolor'。

IN操作符还支持与子查询结合使用，例如：SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM subquery)，其中子查询返回一个值的集合。

此外，需要注意的是IN操作符不包括NULL值，即使列表中包含NULL，使用IN (NULL)的查询不会返回任何行。但可以使用IS NULL来专门检查NULL值。同时，IN操作符的性能可能会受到值列表长度的影响，对于较长的列表，可以考虑使用其他方法来提高性能。

sqldf("select * from iris where Species in ('setosa','versicolor')")

当然还有更直接的等号语句：

sqldf("select * from iris where Species = 'setosa'")

not in

Number does not exist in a list，表示目标值不在列表中。

SELECT * FROM movies where id not in ("1","2");

这段代码是一个 SQL 查询语句，其作用是从名为 movies 的表中选择出所有 id 不在指定列表（"1" 和 "2"）中的记录。

具体解释如下：

SELECT *：表示选择所有列的数据。* 是通配符，表示选择所有列。
FROM movies：指定要从哪个表中查询数据，这里是 movies 表。
WHERE id NOT IN ("1","2")：这是一个筛选条件。WHERE 子句用于指定筛选条件，只有满足条件的记录才会被选中。id NOT IN ("1","2") 表示筛选出 id 列的值不在指定列表（"1" 和 "2"）中的记录。NOT IN 操作符用于排除列表中的值。

需要注意的是，如果在实际应用中表 movies 中的 id 列可能存在 NULL 值，并且子查询或其他方式生成的排除列表中也可能包含 NULL，那么使用 NOT IN 可能会导致意外的结果。在这种情况下，更好的做法可能是使用其他方式来表达这种条件，例如使用 NOT EXISTS 或者对 NULL 值进行特殊处理，具体方法取决于数据库管理系统以及实际需求。

NOT IN 和 NULL：当使用 NOT IN 子句时，如果子查询结果中包含NULL，整个NOT IN条件会返回NULL，而不是TRUE或FALSE。

例如，如果使用 SQL Server，可以将查询修改为：

SELECT * FROM movies WHERE id!= ALL (SELECT ISNULL(id, '') FROM movies WHERE id IN ("1","2"));

上述查询中，使用 ISNULL(id, '') 处理了 id 可能为 NULL 的情况。如果子查询中存在 NULL 值，它将被转换为空字符串，而不会影响最终的筛选结果。不同的数据库系统可能有不同的函数来处理 NULL 值，比如在 Oracle 中可以使用 NVL 函数等。在实际使用时，需要根据所使用的具体数据库进行相应的调整。

not exists

SELECT * FROM movies m
WHERE NOT EXISTS (
    SELECT 1 FROM ratings r 
    WHERE r.movie_id = m.id AND r.rating < 5
)

这里面 select 1 的意思是我们可以通过一个简单的示例来说明这段 SQL 代码的含义。假设有两个表：students 表和 enrollments 表。

示例数据

students 表：

id	name
1	Alice
2	Bob
3	Charlie

enrollments 表：

student_id	course
1	Math
1	Physics
2	Chemistry

SQL 查询解释

SQL 语句如下：

SELECT *
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.id
);

这段代码的逻辑是：从 students 表中选出那些在 enrollments 表中没有对应记录的学生。

步骤解析

针对每个学生，子查询会执行：

对于 students 表中的每一行（每个学生），子查询会执行：

SELECT 1
FROM enrollments e
WHERE e.student_id = s.id;

这个子查询的目的是查找 enrollments 表中是否存在与当前学生 s.id 相匹配的记录。

子查询返回的结果：

对于 Alice (id = 1)：子查询会查找 enrollments 表中 student_id = 1 的记录。由于存在两条记录（Math 和 Physics），EXISTS 为真，NOT EXISTS 为假，Alice 不会被选中。
对于 Bob (id = 2)：子查询会查找 student_id = 2 的记录。由于存在一条记录（Chemistry），Bob 不会被选中。
对于 Charlie (id = 3)：子查询会查找 student_id = 3 的记录。由于 enrollments 表中没有匹配记录，EXISTS 为假，NOT EXISTS 为真，Charlie 会被选中。

为什么写 SELECT 1？

在 EXISTS 或 NOT EXISTS 子句中，数据库只关心子查询是否返回了任何记录，而不在乎返回的具体内容。

使用 SELECT 1 表示只返回一个常量，这种写法简洁且明确表达了“只关心存在性”的意图。
同样效果的写法还有 SELECT * 或 SELECT NULL，但习惯上更推荐使用 SELECT 1。

最终结果

执行这条 SQL 查询后，返回的结果只有 Charlie 这条记录，因为只有 Charlie 没有在 enrollments 表中找到对应的记录。

between and

上面的语句是针对的是离散变量，如果变量是连续变量，那么就需要用到 between and 了。

sqldf("select * from mtcars where wt between 3 and 4")

还能进一步显示行名，

sqldf("select * from mtcars where wt between 3 and 4", row.names = T)

Python 的 SQL 语句中 between 是左闭右开。

R 语句为

mtcars %>% filter(wt %>% between(3,4))

sqldf('select Abbr, avg("Sepal.Length")  from iris where Species in ('','') by Species')

not between and

Number is not within range of two values (inclusive) 不在2个数之间。

sqldf("select * from mtcars where wt not between 3 and 4", row.names = T)

union/union all

数据集的合并，类似于 R 中的 rbind.

sqldf("select * from a1 union all select * from a2")

必须注意 union 与 union all 之间的区别。union all 是不去重，union 是去重。

sqldf("select count(*) from (
select * from iris
union all
select * from iris
)")

NULL

空值，这块需要注意的点就是 is not null/ is null

null 不被匹配！

Note

在 SQL 中，LIKE 操作符用于模式匹配，而通配符 % 表示匹配任意长度的任意字符组合。例如，prod_name LIKE '%' 意味着匹配所有包含任意字符的非空字符串。但是，注意这里的“匹配”只针对有实际字符串值的记录，而不是 NULL。

为什么 NULL 不被匹配？

NULL 的含义

在 SQL 中，NULL 表示“未知”或“缺失”的值，并不是一个空字符串。它代表数据不存在，而不是一个可比较的字符值。

比较结果为 UNKNOWN

当你使用 LIKE 进行比较时，如果某个列的值是 NULL，则任何与 NULL 的比较都会返回 NULL（也就是逻辑上的 UNKNOWN），而不是 TRUE。例如：

NULL LIKE '%'

结果不是 TRUE，而是 NULL。在 WHERE 子句中，只有返回 TRUE 的记录会被选中，因此 NULL 的记录会被忽略。

简单数据示例说明

假设有一个表 products 如下：

prod_id	prod_name
1	Apple
2	Banana
3	NULL

当你执行如下查询：

SELECT *
FROM products
WHERE prod_name LIKE '%';

对于 prod_id 为 1 的记录，'Apple' LIKE '%' 返回 TRUE，因此这行会被选中。
对于 prod_id 为 2 的记录，'Banana' LIKE '%' 返回 TRUE，因此这行也会被选中。
对于 prod_id 为 3 的记录，NULL LIKE '%' 返回 NULL（即 UNKNOWN），因此这行不会被选中。

总结

虽然 % 通配符在字符串匹配中能匹配任意字符，但它无法“匹配” NULL。如果你想专门查找 NULL 值，需要使用 IS NULL 条件，而不是 LIKE。

通配符

在 SQL 中，方括号 [] 通配符用于定义一个字符集，这个字符集指定了可以匹配的位置字符。使用 [] 可以指定一个范围或一组字符，只有当某个字符匹配该集合中的字符时，条件才会成立。

方括号 ([]) 的基本用法：

字符集匹配

方括号 [] 中可以列出一个或多个字符，表示可以匹配的字符范围或集合。例如，[abc] 会匹配 ‘a’、‘b’ 或 ‘c’ 中的任何一个字符。

字符范围

如果在方括号中使用连字符 -，可以表示一个字符的范围。例如，[a-z] 表示小写字母中的任何一个字符，[0-9] 表示数字中的任何一个。

否定字符集

如果使用 ^ 或 ! 在方括号的开头，它表示一个否定字符集，即匹配不在指定集合中的字符。例如，[^0-9] 会匹配非数字字符。

举例说明

以下是一些常见的例子：

示例 1：匹配以字母 M 或 ] 开头的名字

假设我们有一个 contacts 表，包含 name 字段，查询所有名字以 M 或 ] 开头的联系人。

SELECT *
FROM contacts
WHERE name LIKE '[M]%' OR name LIKE '[}]%';

在这个查询中，[M] 匹配以 M 开头的名字，[}] 匹配以 ] 开头的名字。这里，方括号的作用是指定我们要匹配的字符集——要么是 M，要么是 ]。

示例 2：匹配以数字或字母开头的名字

如果我们希望查找名字以数字或字母开头的联系人：

SELECT *
FROM contacts
WHERE name LIKE '[0-9]%' OR name LIKE '[a-zA-Z]%';

[0-9] 匹配任何数字（0 到 9）。
[a-zA-Z] 匹配任何字母（大小写都可以）。

这个查询将返回所有名字以数字或字母开头的联系人。

示例 3：匹配不以数字开头的名字

如果我们想排除那些名字以数字开头的联系人，可以使用否定字符集：

SELECT *
FROM contacts
WHERE name LIKE '[^0-9]%';

这个查询使用 [^0-9] 来排除以数字开头的名字，匹配所有名字以非数字字符开头的联系人。

注意：并不是所有 DBMS 都支持方括号 [] 作为字符集

如你所提到的，方括号 [] 用来定义字符集并不是所有数据库管理系统（DBMS）都支持的，特别是在一些数据库中，这种用法可能不可用。通常，微软的 SQL Server 和 Access 是支持这种语法的。

总结

方括号 [] 用来定义字符集，并要求匹配某个位置上的字符。
它可以用来表示一个范围（如 [a-z]）、列举多个字符（如 [abc]），或否定某些字符（如 [^0-9]）。
在一些 DBMS（如 SQL Server 和 Access）中，方括号用法是支持的，但其他一些数据库可能不支持这一特性。

字符串拼接

字符串拼接可以使用 CONCAT。

select name, concat(url,',',alexa,',',country) AS site_info from websites

排序与限制

数据排序。order by var desc 降序排列；order by var asc 升序排列。

library(datasets)
library(tidyverse)
library(sqldf)
#> Loading required package: gsubfn
#> Loading required package: proto
#> Loading required package: RSQLite
sqldf('select * from iris order by "Sepal.Length" desc limit 3')
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1          7.9         3.8          6.4         2.0 virginica
#> 2          7.7         3.8          6.7         2.2 virginica
#> 3          7.7         2.6          6.9         2.3 virginica
iris %>%
    arrange(desc(Sepal.Length)) %>%
    head(3)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1          7.9         3.8          6.4         2.0 virginica
#> 2          7.7         3.8          6.7         2.2 virginica
#> 3          7.7         2.6          6.9         2.3 virginica

聚合与分组

这一部分主要对应聚合统计。

sqldf('select Species, avg("Sepal.Length") from iris group by Species')
#>      Species avg("Sepal.Length")
#> 1     setosa               5.006
#> 2 versicolor               5.936
#> 3  virginica               6.588

iris %>%
      select(Species, Sepal.Length) %>%
      group_by(Species) %>%
      summarise(mean(Sepal.Length))
#> # A tibble: 3 x 2
#>   Species    `mean(Sepal.Length)`
#>   <fct>                     <dbl>
#> 1 setosa                     5.01
#> 2 versicolor                 5.94
#> 3 virginica                  6.59

嵌套查询

这一节关注嵌套查询：对每个 Species，先计算该组的平均 Sepal Length，再筛选出高于该组均值的记录并继续统计。

sqldf("select iris.Species '[Species]',
avg(\"Sepal.Length\") '[Avg of SLs > avg SL]'
from iris,
(select Species, avg(\"Sepal.Length\") SLavg
from iris group by Species) SLavg
where iris.Species = SLavg.Species
and \"Sepal.Length\" > SLavg
group by iris.Species")

上面的 SQL 语句等价于下面的写法：

aggregate(Sepal.Length ~ Species, iris, function(x) mean(x[x > mean(x)]))

请过滤出每组记录大于该组均值的记录。

Emp <- data.frame(emp = letters[1:24], salary = 1:24, dept = rep(c("A", "B", "C"), each = 8))
sqldf("SELECT *
FROM Emp AS e1
WHERE salary > (SELECT avg(salary)
FROM Emp
WHERE dept = e1.dept)")

等价于以下 R 语言

Emp$ave_salary = ave(Emp$salary, Emp$dept)
Emp %>%
    filter(salary > ave_salary)

连接查询

下面对照 R 和 SQL 中常见的数据连接方式。

R	SQL
内连接()	SELECT * FROM x JOIN y ON x.a = y.a
left_join()	SELECT * FROM x LEFT JOIN y ON x.a = y.a
right_join()	SELECT * FROM x RIGHT JOIN y ON x.a = y.a
full_join()	SELECT * FROM x FULL JOIN y ON x.a = y.a
semi_join()	SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WHERE x.a = y.a)
anti_join()	SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a)
intersect(x, y)	SELECT * FROM x INTERSECT SELECT * FROM y
union(x, y)	SELECT * FROM x UNION SELECT * FROM y
setdiff(x, y)	SELECT * FROM x EXCEPT SELECT * FROM y

Abbr <- data.frame(Species = levels(iris$Species),
                   Abbr = c("S", "Ve", "Vi"))

sqldf('select Abbr, avg("Sepal.Length") from iris natural join Abbr group by Species')
#>   Abbr avg("Sepal.Length")
#> 1    S               5.006
#> 2   Ve               5.936
#> 3   Vi               6.588

iris %>%
      inner_join(Abbr, by = "Species") %>%
      group_by(Species) %>%
      summarise(mean(Sepal.Length))
                                     #> Warning: Column `Species` joining factor and character vector, coercing
                                     #> into character vector
                                     #> # A tibble: 3 x 2
                                     #>   Species    `mean(Sepal.Length)`
                                     #>   <chr>                     <dbl>
                                     #> 1 setosa                     5.01
                                     #> 2 versicolor                 5.94
                                     #> 3 virginica                  6.59

SNP1x <-
  structure(list(Animal = c(194073197L, 194073197L, 194073197L,
                            194073197L, 194073197L), Marker = structure(1:5, .Label = c("P1001",
                                                                                        "P1002", "P1004", "P1005", "P1006", "P1007"), class = "factor"),
                 x = c(2L, 1L, 2L, 0L, 2L)), .Names = c("Animal", "Marker",
                                                        "x"), row.names = c("3213", "1295", "915", "2833", "1487"), class = "data.frame")

SNP4 <-
  structure(list(Animal = c(194073197L, 194073197L, 194073197L,
                            194073197L, 194073197L, 194073197L), Marker = structure(1:6, .Label = c("P1001",
                                                                                                    "P1002", "P1004", "P1005", "P1006", "P1007"), class = "factor"),
                 Y = c(0.021088, 0.021088, 0.021088, 0.021088, 0.021088, 0.021088
                 )), .Names = c("Animal", "Marker", "Y"), class = "data.frame", row.names = c("3213",
                                                                                              "1295", "915", "2833", "1487", "1885"))
sqldf("select * from SNP4 LEFT JOIN SNP1x using (Animal, Marker) limit 5")
#>      Animal Marker       Y x
#> 1 194073197  P1001 0.02109 2
#> 2 194073197  P1002 0.02109 1
#> 3 194073197  P1004 0.02109 2
#> 4 194073197  P1005 0.02109 0
#> 5 194073197  P1006 0.02109 2

内连接

在 flights 数据集中，对 year,group 进行分组，筛选出大于均值 dep_time 的记录。

flights %>%
    group_by(year,month) %>%
    select(dep_time) %>%
    inner_join(flights %>%
               group_by(year,month) %>%
               summarise(mean(dep_time))) %>%
    filter(dep_time > `mean(dep_time)`)

<SQL>
SELECT *
FROM (SELECT `LHS`.`year` AS `year`, `LHS`.`month` AS `month`, `LHS`.`dep_time` AS `dep_time`, `RHS`.`mean(dep_time)` AS `mean(dep_time)`
FROM (SELECT `year`, `month`, `dep_time`
FROM `nycflights13::flights`) AS `LHS`
INNER JOIN (SELECT `year`, `month`, AVG(`dep_time`) AS `mean(dep_time)`
FROM `nycflights13::flights`
GROUP BY `year`, `month`) AS `RHS`
ON (`LHS`.`year` = `RHS`.`year` AND `LHS`.`month` = `RHS`.`month`)
)
WHERE (`dep_time` > `mean(dep_time)`)

查询所有学生的 sname，cno，degree 列

select SNAME,CNO,DEGREE from (select SNAME, SNO from STUDENT) as A
inner join (select SNO, CNO, DEGREE from  SCORE) as B
on A.SNO = B.SNO

select SNO,CNAME,DEGREE from
(select CNO, CNAME from COURSE) AS A
inner join (select * from SCORE) AS B
on A.CNO = B.CNO

select SNAME, CNO, DEGREE from (select  SNAME,SNO from STUDENT) as A
join (select * from SCORE) as B
on A.SNO = B.SNO

在 SQL 中，默认的 JOIN 是 INNER JOIN。也就是说，如果只写 JOIN 而不指定类型，SQL 引擎会自动将其解释为 INNER JOIN。

INNER JOIN

INNER JOIN 会返回两个表中所有匹配的行。如果某一行在其中一个表中没有匹配项，那么该行不会出现在结果集中。

LEFT JOIN

LEFT JOIN（或 LEFT OUTER JOIN）会返回左表中的所有行，即使在右表中没有匹配项。对于没有匹配项的右表行，会返回 NULL。

示例解释

使用 INNER JOIN

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
JOIN table2 ON table1.common_column = table2.common_column;

这个查询等同于：

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

使用 LEFT JOIN

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

例子

假设有两个表，employees 和 departments。

表 employees

employee_id	name	department_id
1	John	10
2	Jane	20
3	Alice	30

表 departments

department_id	department_name
10	HR
20	Finance

INNER JOIN 查询

SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

结果集：

name	department_name
John	HR
Jane	Finance

LEFT JOIN 查询

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

结果集：

name	department_name
John	HR
Jane	Finance
Alice	NULL

在这个例子中，INNER JOIN 仅返回两个表中都有匹配项的行，而 LEFT JOIN 返回左表中所有行，即使右表中没有匹配项，也会显示这些行并在右表列中填充 NULL。

去重

选取出唯一的结果的语法.

插入变量

用$ 可以插入变量。

minSL <- 7
limit <- 3
species <- "virginica"
fn$sqldf("select * from iris where \"Sepal.Length\" > $minSL and species = '$species' limit $limit")
                                   #>   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
                                   #> 1          7.1         3.0          5.9         2.1 virginica
                                   #> 2          7.6         3.0          6.6         2.1 virginica
                                   #> 3          7.3         2.9          6.3         1.8 virginica

CASE WHEN

计算25岁以上和以下的用户数量。

select 
age_cut, count(age_cut) as number
from
(select
case when 
(age >= 25) then ('25岁及以上')
when not (age >= 25) or (age is NULL) then ('25岁以下')
end as  age_cut
from user_profile) a
group by
age_cut

COALESCE

COALESCE 和 NVL 在功能上非常相似，但有一些细微差别：

NVL 是 Oracle 特有的函数，而 COALESCE 是 SQL 标准函数，在多种数据库系统中都可以使用。
NVL 只接受两个参数，而 COALESCE 可以接受两个或更多参数。
NVL 的语法是：
```
NVL(expr1, expr2)
```
如果 expr1 为 NULL，则返回 expr2，否则返回 expr1。
COALESCE 的语法是：
```
COALESCE(expr1, expr2, ..., exprn)
```
它返回参数列表中第一个非 NULL 的表达式。如果所有参数都为 NULL，则返回 NULL。

在大多数情况下，当只需要处理两个表达式时，NVL 和 COALESCE 可以互换使用。例如：

NVL(expr1, expr2)

等同于

COALESCE(expr1, expr2)

不过，COALESCE 更灵活，因为它可以处理多个表达式：

COALESCE(expr1, expr2, expr3, expr4)

这相当于嵌套的 NVL 函数：

NVL(NVL(NVL(expr1, expr2), expr3), expr4)

因此，在处理多个可能的 NULL 值时，使用 COALESCE 通常更具可读性和可移植性。

聚合统计

count

计数 select count(class) as ct from STUDENT where class = "abc"

sqldf("select count(*) from iris")

mean/var

sqldf("select avg(demand) mean, variance(demand) var from BOD")
 #>    mean   var
                                   #> 1 14.83 21.44

select SNAME, DEGREE from
(select SNO,SNAME,CLASS from STUDENT) as A
join (select * from SCORE) as B
on A.SNO = B.SNO where CLASS = "95033"

flights %>%
    group_by(year,month) %>%
    summarise(mean(dep_time)) %>%
    show_query()

上面 dplyr 语句 sql 写法是：

    <SQL>
SELECT `year`, `month`, AVG(`dep_time`) AS `mean(dep_time)`
FROM `nycflights13::flights`
GROUP BY `year`, `month`

having

在 SQL 语句中，HAVING 通常用于过滤聚合后的结果。

#现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

select university, avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt from user_profile group by university having avg(question_cnt) < 5 or avg(answer_cnt) < 20

SQLite

这个 Python 包可以处理 SQL 语句。

import sqlite3
conn = sqlite3.connect('/home/tonylu/learn/sql/test.db')
c = conn.cursor()
c.execute('''CREATE TABLE SCORE
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       COURSE            TEXT     NOT NULL,
       SCORE        INT);''')
print ("数据表创建成功")
conn.commit()
conn.close()


conn = sqlite3.connect('/home/tonylu/learn/sql/test.db')
c = conn.cursor()
print ("数据库打开成功")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (1, 'Paul', 'Math', 98)")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (2, 'Allen', 'MATH', 88)")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (3, 'Teddy', 'Computer', 91)")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (4, 'Mark', 'English', 71)")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (5, 'Mark', 'Computer', 87)")

c.execute("INSERT INTO SCORE (ID,NAME,COURSE,SCORE) \
      VALUES (6, 'Allen', 'English', 73)")

conn.commit()
print ("数据插入成功")
conn.close()

cursor = c.execute("select name from (select row_number() over(partition by name order by score) as rank, * from score) where rank=1 and score>80")

# cursor = c.execute("select sum(score), name from score where name in (select name from (select row_number() over(partition by name order by score) as rank, * from score) where rank=1 and score>80)")

cursor = c.execute("select sum(score) from score")

cursor = c.execute("select name from (select row_number() over(partition by name order by score) as rank, * from score) where rank=1 and score>80")

[i for i in cursor]
# => [('Paul',), ('Teddy',)]

字符串截取

从左截取字符串。

select left(SBIRTHDAY, 4) from STUDENT where SNO = "108"