SQL笔记
用sqlbolt尝试快速入门sql的笔记
学完之后基本能用于简单的CRUD了，就是可能细节不够多
并不简洁并且头轻脚重的思维导图总结，想着多插点代码结果看起来好像没怎么总结只是按照目录排了一下
基本概念
它将数据以表格的形式展现，行存储数据内容，列存储数据属性(个人理解，严谨的学习等之后看书补上)
大概就像下面这样子罢(确信)
| id | name | age |
|---|---|---|
| 1 | 田所浩二 | 24 |
| 14 | 李田所 | 24 |
| 514 | 王道往 | 24 |
基本语法
sql不区分大小写，每句以分号结尾。建议是关键字全大写以区分列表名与关键字
Select查询
从指定的表中查询列
SELECT col, another_col,... FROM mytable;
检索所有列使用通配符*可代替列出所有列
SELECT * FROM mytable;
对应练习答案
SELECT title FROM movies;
SELECT director FROM movies;
SELECT title, director FROM movies;
SELECT title, year FROM movies;
SELECT * FROM movies;
Where约束查询
Where约束了select的查询条件
复杂的判断字句可以通过链接多个AND 或 OR关键词构建
常见数值判断语句如下
| 操作符 | 描述 | 例句 |
|---|---|---|
| =,!=,<,<=,>,>= | 基本数值运算符 | col_name!=4 |
| BETWEEN…AND… | 数值包括在两值闭区间内 | col_name BETWEEN 2 AND 4 |
| NOT BETWEEN…AND… | 数值不包括在两值闭区间内 | col_name NOT BETWEEN 2 AND 4 |
| IN (…) | 数值存在于给定列表 | col_name IN (2,4,6) |
| NOT IN (…) | 数值不存在于给定列表 | col_name NOT IN (2,4,6) |
对应练习答案
SELECT * FROM movies WHERE id=6;
SELECT * FROM movies WHERE year BETWEEN 2000 AND 2010;
SELECT * FROM movies WHERE year NOT BETWEEN 2000 AND 2010;
SELECT * FROM movies WHERE year id<6;
常见文本判断语句如下
| 操作符 | 描述 | 例句 |
|---|---|---|
| = | 区分大小写字符相等精确匹配 | col_name=“abc” |
| !=, <,> | 区分大小写字符不相等精确匹配,<,>是比较首字母字典序 | col_name!=“abcd” |
| LIKE | 忽略大小写相等精确匹配字符 | col_name LIKE “ABC” |
| NOT LIKE | 忽略大小写不相等精确匹配字符 | col_name NOT LIKE “ABC” |
| % | 匹配一个或多个字符,用于LIKE or NOT LIKE | col_name LIKE “%AT%” |
| _ | 至少匹配一个字符,用于LIKE or NOT LIKE | col_name LIKE “AN_” |
| IN(…) | 字符存在给定列表 | col_name IN (“A”,“B”,“C”) |
| NOT IN(…) | 字符不存在给定列表 | col_name NOT IN (“A”,“B”) |
对应练习答案
SELECT title FROM movies WHERE title LIKE "Toy Story%";
SELECT title FROM movies WHERE director = "John Lasseter";
SELECT title FROM movies WHERE director != "John Lasseter";
SELECT title FROM movies WHERE title LIKE "WALL-%"
过滤和排序搜索结果 DISTINCT ,ORDER BY,LIMIT…OFFSET
过滤重复列值 DISTINCT
SELECT DISTINCT col, another_col... FROM mytable;
会盲目地删除搜索结果中的重复行
排序搜索结果 ORDER BY
使搜索结果按照升序(ASC)或降序(DESC)排序
SELECT col, another_col... FROM mytable ORDER BY col ASC;
限制搜索结果
使用LIMIT设置最大搜索结果数，OFFEST指定LIMIT开始处偏移
SELECT col, another_col... FROM mytable LIMIT num_limit OFFSET num_offset;
对应练习答案
SELECT DISTINCT director FROM movies ORDER BY director;
SELECT DISTINCT title FROM movies ORDER BY year DESC LIMIT 4;
SELECT title FROM movies ORDER BY title LIMIT 5;
SELECT title FROM movies ORDER BY title LIMIT 5 OFFSET 5;
-- 复习内容
SELECT city, population
FROM north_american_cities
WHERE country = "Canada";
SELECT city
FROM north_american_cities
WHERE country = "United States"
ORDER BY latitude DESC;
SELECT city
FROM north_american_cities
WHERE longitude < -87.629798
ORDER BY longitude;
SELECT city
FROM north_american_cities
WHERE country = "Mexico"
ORDER BY population DESC
LIMIT 2;
SELECT city
FROM north_american_cities
WHERE country = "United States"
ORDER BY population DESC
LIMIT 2 OFFSET 2;
多表查询JOIN
查询一个实体的问题可能需要在多个拥有有关该实体数据的表中查询才能回答，而共享有关单个实体信息的表需要一个在整个数据库中唯一不为空的主键来标识该主体以用于多个表中查询实体相关数据，一种常见的主键类型是自增整数，也可以是字符串，散列值，只要是唯一的。在查询中使用JOIN语句的时候，可以使用主键横跨两个不同的表组合行数据
INNER JOIN
SELECT col, another_table_column,...
FROM mytable
INNER JOIN another_table
ON mytable.id = another_table.id
WHERE conditions(s);
上面的例子中INNER JOIN以ON条件约束了匹配another_table表中数据的条件(即id相等)，创建了包含来自两个表的合并列的结果行，链接表后才是WHERE约束,ORDER BY 排序等语句，这里INNER JOIN 可以直接写成JOIN，但为了不与其它类型JOIN混淆一般还是写成INNER JOIN
对应练习答案
SELECT title, domestic_sales, international_sales
FROM movies
INNER JOIN boxoffice
ON movies.id = boxoffice.movie_id;
SELECT title, domestic_sales, international_sales
FROM movies
INNER JOIN boxoffice
ON movies.id = boxoffice.movie_id
WHERE international_sales > domestic_sales;
SELECT title, rating
FROM movies
INNER JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;
OTHER JOIN
如果两个表数据不对称，或者在不同阶段输入数据时发生数据不对称导致数据查询失败。这种情况将不得不使用LEFT JOIN ， RIGHT JOIN 或者 FULL JOIN
SELECT col, another_col, ...
FRPM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE conditions(s)
ORDER BY col, ... ASC/DESC
LIMIT num_limit OFFSET num_offset;
当将表A链接到表B时，LEFT JOIN 不管在B中是否找到匹配的行都只返回表A的行，RIGHT JOIN 则相反只返回表B的行，而FULL JOIN意味着保持两个表中的行，而不管另一个表中是否存在匹配的行
这些JOIN语句也有写成
LEFT OUTER JOIN,RIGHT OUTER JOIN或FULL OUTER JOIN，这些关键字是为了兼容性保留的，直接简单的简写就可以了
对应练习答案
SELECT DISTINCT building FROM employees;
SELECT * FROM buildings;
SELECT DISTINCT building_name, role
FROM buildings
LEFT JOIN employees
ON building_name = employees.building;
NULL 空值约束
在构造查询，约束以及处理结果时需要特别注意NULL空值，所以尽量减少数据库中的NULL值，替代NULL值得一种方法是使用数据类型默认值如0或空字符，但是如果需要存储空值，如需要计算平均值时就要避免使用默认值而是使用NULL。
而在例如链接具有非对称数据的表时可以使用IS NULL 或者 IS NOT NULL 来测试WHERE 字句中列的NULL值
SELECT col, another_col,...
FROM mytable
WHERE mytable
WHERE col IS/IS NOT NULL
AND/OR another_condition
AND/OR ...;
对应练习答案
SELECT role,name FROM employees
WHERE building IS NULL;
SELECT DISTINCT building_name FROM buildings
LEFT JOIN employees
ON building_name = employees.building
WHERE employees.building IS NULL
AS别名 | 表达式查询
表达式用于编写更复杂的逻辑以查询列值，这些表达式可以在查询时使用数学和字符串函数以及基本算法来在执行查询时转换值。
SELECT particle_speed / 2.0 AS half_partice_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;
每个数据库都有自己支持的数学，字符串，日期函数。当表达式在查询SELECT语句中使用时，可以使用AS关键字给出一个描述性的别名
SELECT col_exps AS exps_desc,...
FROM mytable
除了表达式外，列和表也可以有别名
SELECT col AS pretty_col_name, ...
FROM long_name_table AS mytable;
对应练习答案
SELECT id,title,
(domestic_sales + international_sales) / 1000000 AS combined_sales
FROM movies
INNER JOIN boxoffice
ON id = boxoffice.movie_id;
SELECT id,title,
(rating)* 10 AS percent
FROM movies
INNER JOIN boxoffice
ON id = boxoffice.movie_id
SELECT title FROM movies WHERE year % 2 = 0
聚合搜索1 | 分组
聚合搜索语法
SQL支持使用聚合表达式(或函数)来汇总关于一组数据行的信息，可用于回答综合问题。如果没指定分组，聚合函数会在所有输入行上运行并返回一个值。与普通表达式一样可以给结果一个别名
SELECT AGG_FUNC(col_or_exps) AS aggregate_desc,...
FROM mytable
WHERE constraint_exps;
聚合搜索常见函数
| 函数 | 描述 |
|---|---|
| COUNT(*),COUNT(col) | 未指定列名时计算分组中的行数，否则计算指定列的分组内非NULL行数 |
| MIN(col) | 查找分组内指定列的最小值 |
| MAX(col) | 查找分组内指定列的最大值 |
| AVG(col) | 查找分组内指定列的平均值 |
| SUM(col) | 查找分组内指定列的总和 |
分组
还可以将聚合函数应用于分组得到与GROUP BY 子句定义的组的唯一结果。GROUP BY 子句通过对指定列中具有相同值的行进行分组
SELECT AGG_FUNC(col_or_exps) AS aggregate_desc,...
FROM mytable
WHERE constraint_exps
GROUP BY col;
对应练习答案
SELECT MAX(years_employed) AS longest FROM employees;
SELECT role,AVG(years_employed)
FROM employees
GROUP BY role;
SELECT building,SUM(years_employed)
FROM employees
GROUP BY building;
聚合搜索2
为了约束过滤分组后的行，SQL使用一个专门与GROUP BY子句一起使用的HAVING子句来从分组结果中筛选约束结果行
SELECT group_by_col,AGG_FUNC(col_or_exps) AS aggregate_desc,...
FROM mytable
WHERE constraint_exps
GROUP BY col
HAVING group_condition;
在不使用GROUP BY子句时过滤约束数据直接用WHERE就可以了，HAVING子句的约束方法编写和WHERE一样
对应练习答案
SELECT COUNT(*) FROM employees WHERE role='Artist';
SELECT role,COUNT(role) FROM employees GROUP BY role;
SELECT sum(years_employed) FROM employees WHERE role='Engineer';
执行顺序
-
FROM 和 JOIN
- FROM子句和JOIN字句首先执行确认查询的数据集，包括字句中的子查询，并可能导致在后台创建临时表以包含正在来凝结的表的所有列和行。
-
WHERE
- WHERE在确定数据集后对各行应用约束丢弃掉不满足约束条件的行，约束只能从FROM中请求的表中访问列。且在大多数数据库中，查询的 SELECT 部分中的别名不可访问，因为它们可能包含依赖于尚未执行的查询部分的表达式。
-
GROUP BY
- GROUP BY 指定列公共值对WHERE约束后数据进行分组，配合聚合函数搜索使用。
-
HAVING
- 将分组后结果进行约束筛选数据，类似WHERE，别名在大多数数据库中也不能从此步骤访问。
-
SELECT
- 查询部分的任何表达式都是最后再计算
-
DISTINCT
- 查询部分中列被标记为DISTINCT时将剩余的行中重复的行丢弃过滤掉
-
ORDER BY
- 将行根据制定数据进行升序或降序排序，因为SELECT中表达式已经计算完成所以这部分可以使用别名。
-
LIMIT/OFFSET
- 最后将数据根据
最大显示条数LIMIT和偏移值LIMIT外的数据丢弃过滤掉，将后续数据结果返回。
- 最后将数据根据
对应练习答案
SELECT director,COUNT(director) FROM movies
GROUP BY director;
SELECT director,SUM(Domestic_sales+International_sales) FROM boxoffice
INNER JOIN movies
ON movie_id=movies.id
GROUP BY director;
数据库模式 Schema
数据库模式描述数据库每个表的结构，每一列使用的数据类型和表能包含什么。
插入值 INSERT INTO … VALUES(…,…) ,…
INSERT语句描述要插入的表，哪一列插入什么值，可以插入多行，一般插入值每个参数对应列的数据列行。
INSERT INTO mytable
VALUES(value_or_expr, another_value_or_expr, …),
(value_or_expr_2,another_value_or_expr_2,
…),
…;
如果列支持默认值可以在插入值时显示指定插入哪些列忽略带有默认值的列。
INSERT INTO mytable
(col, another_col,...)
VALUES(value_or_expr, another_value_or_expr, …),
(value_or_expr_2,another_value_or_expr_2,
…),
…;
尽管指定插入行显得冗长，但是这样可以有效向前兼容。例如如果插入一个没有默认值的新行，没有硬编码的插入语句就必须改变相应值以响应表的改变。(否则可能该行全为空值)
插入语句中还可以使用数字运算和字符串表达式以格式化插入数据。
INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000);
对应练习答案
INSERT INTO movies
(title,director,year,length_minutes)
VALUES('Toy Story 4','Anyway',1919,81);
INSERT INTO movies
VALUES(4,'Toy Story 4','Anyway',1919,81);
INSERT INTO boxoffice
VALUES(4,8.7,340000000,270000000)
更新值 UPDATE … SET col = … WHERE…
用于更新存在数据，类似插入语句，需要指明插入的表，列和行。更新的数据类型还需要匹配表模式中的列的数据类型。更新语句通过SET获取需要修改的所有列，然后通过WHERE过滤出需要修改的行。一般修改之前应该使用SELECT语句检查一下WHERE语句获取到的是不是要修改的行，避免修改了所有行
UPDATE mytable
SET column = value_or_expr,
other_column = another_value_or_expr,
…
WHERE condition;
对应练习答案
UPDATE movies
SET director="John Lasseter"
WHERE title="A Bug's Life";
UPDATE movies
SET year=1999
WHERE title='Toy Story 2';
UPDATE movies
SET title='Toy Story 3',
director='Lee Unkrich'
WHERE title='Toy Story 8';
删除值 DELETE FROM … WHERE …
指明WHERE条件既可以指定删除哪一行，如果不指定则是删除所有行。一般删除之前应该检查一遍并且使用SELECT语句检查一下WHERE语句获取到的是不是要删除的行，避免删除了所有行
DELETE FROM mytable
WHERE condition;
对应练习答案
DELETE FROM movies
WHERE year<2005;
DELETE FROM movies
WHERE director='Andrew Stanton';
建表 CREATE TABLE
用于需要在数据库添加新实体和关系时使用，通过schema模式指定数据库的列的数据类型，可选的插入前约束和可选的默认值。为了避免插入重名表报错可以添加IF NOT EXISTS语句放置创建重复表导致报错
CREATE TABLE IF NOT EXISTS mytable(
col DataType TableConstraint DEFAULT default_val,
another_column DataType TableConstraint DEFAULT default_value,
…
);
数据类型
不同的数据库支持不同的数据类型，但是包括数值类型，字符串类型，以及其他包括日期，布尔值，二进制类型都是通用支持的。以下时常用的数值类型表
| 数据类型 | 描述 |
|---|---|
| INTEGER,BOOLEAN | INTEGER存放类似计数或年龄等整数，BOOLEAN布尔值存放0或1代表假或真 |
| FLOAT,DOUBLE,REAL | 三种存放小数/商的浮点数类型，根据小数位数的精度要求选择 |
| CHARACTER(num_chars), VARCHAR(num_chars), TEXT | 都是存放字符串文本类型，CHARACTER和VARCHAR通过指定能容纳的字符数量(num_chars)以提升在大型表的使用时的存储和查询效率 |
| DATE，DATETIME | 存储日期和时间戳，以跟踪时间序列和时间数据。因为跨时区等问题可能数据很难处理。 |
| BLOB | 存放二进制数据，这些数据对数据库来说通常是不透明的，因此通常必须使用在正确的元数据存储它们才能查询到 |
表约束
每个列可以有额外的表约束以约束向该列插入的值，以下是常用的约束
| 约束 | 描述 |
|---|---|
| PRIMARY KEY | 一位该列的值是唯一的，并且每个值都可以用来在表中标识单独一行，类似id等数据 |
| AUTOINCREMENT | 对于整型值，意味着这里的值会在插入数据时自增并填充，并不是所有数据库都支持(Not supported in all databases.) |
| UNIQUE | 表明这列的值是唯一的，不能在该列其他行中插入相同值，跟PRIMARY KEY主键不同，这个值不需要用于在表中作为区分行的键值 |
| NOT NULL | 表明插入值不能是空值NULL |
| CHECK (expression) | 可以在插入值前通过expression表达式验证值是否合法，例如检查是否负数，包含什么前缀，大于指定值等 |
| FOREIGN KEY | 一致性检查约束，用于确保此列中的每个值对应另一个表中相应列的另一个值 例如有列出所有雇员id的雇员表，列出它们工资的工资表。FOREIGN KEY可以确保工资表中每一行都对应雇员表中的一个有效雇员行 |
因为不确定Not supported in all databases.的翻译对比了多个引擎(
CREATE TABLE movies(
id INTEGER PRIMARY KEY,
title TEXT,
director TEXT,
year INTEGER,
length_minutes INTEGER
);
对应练习答案
CREATE TABLE Database(
Name TEXT,
Version FLOAT,
Download_count INTEGER
);
改表ALTER TABLE
有一系列用于更新指定表和数据库模式的方法，包括添加，删除和修改列以及表的约束。
添加列
添加列的语句类似于建表，需要指定列的数据类型和可选的表约束，默认值应用与已存在和新插入的行。在像MySQL这样的数据库中还可以使用FIRST 或 AFTER字句指定新列的插入位置。
ALTER TABLE mytable
ADD col DataType OptionalTableConstraint
DEFAULT default_value;
删除列
删除列和指定要删除的列一样简单，但是包括SQLite之类的一些数据库不支持这个特性而是替代的需要创建一个新表并迁移数据。
ALTER TABLE mytable
DROP col_to_be_deleted;
重命名表
使用RENAME TO 字句来为表重命名
ALTER TABLE mytable
RENAME TO new_table_name;
其他操作
每个数据库实现都支持不同的方法来修改它们的表，在使用之间最好先查询使用的数据库文档:MySQL, Postgres, SQLite, Microsoft SQL Server.
对应练习答案
ALTER TABLE movies
ADD Aspect_ratio FLOAT;
ALTER TABLE movies
ADD Language TEXT
DEFAULT 'English';
删表 DROP TABLE IF EXISTS …
需要删除表的情况下使用，和DELETE语句不同在于它会从数据库中删除表模式，为了避免删除不存在的表导致报错可以使用IF EXISTS语句避免，如果有另一个表依赖当前要删除的表，那还必须为所有依赖这个表的依赖表做相应的更新或者完全删除这些表。
DROP TABLE IF EXISTS mytable;
对应练习答案
DROP TABLE movies;
DROP TABLE boxoffice;
最近在牛客网的在线编程那里练了练，发现sql相对确实不怎么难。只不过光学网站上的操作还不够，相对太精炼了。遇到一些需要日期函数，正则匹配，字符串函数就瞎眼了。不过这种细枝末节还是找完善一点的工具书学习比较好，这个网站能大概一星期让我大概掌握SQL已经很强了，接下来准备继续刷刷题加深一下记忆然后学php去
137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
