SQL学习笔记

maiaimei 2024/4/23 SQL

SQL 对大小写不敏感。
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。
分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的语句。
SQL将涉及空值的任何比较运算的结果视为unknown（既不是谓词 is null，也不是is not null）。
- true and unknown的结果是unknown
- false and unknown的结果是false
- unknown and unknown的结果是unknown
- true or unknown的结果是true
- false or unknown的结果是unknown
- unknown or unknown的结果是unknown
- not unknown的结果是unknown
- 如果where子句谓词对一个元组计算出false或者unknown，那么该元组不能被加入到结果集中。

在线 SQL 数据库环境：

# DDL

数据定义语言（Data Definition Language）

# CREATE DATABASE

CREATE DATABASE dbname;

# ALTER DATABASE

修改数据库

# DROP DATABASE

DROP DATABASE 语句用于删除数据库，包括其中的所有表、视图、存储过程等数据库对象。

DROP DATABASE 是一个非常强大和危险的操作，因为它会永久删除整个数据库及其所有相关数据，因此在执行之前务必要慎重考虑并确保你真的希望执行此操作。

DROP DATABASE [IF EXISTS] database_name;

参数说明：

DROP DATABASE：表示删除数据库的操作。
IF EXISTS：是一个可选的子句，用于检查数据库是否存在。如果存在，执行删除操作；如果不存在，不会报错。
database_name：要删除的数据库的名称。

# CREATE TABLE

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

1
2
3
4
5
6
7

# ALTER TABLE

# 添加列
ALTER TABLE table_name
ADD column_name datatype

# SQL Server / MS Access 修改列的数据类型：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

# My SQL / Oracle 修改列的数据类型：
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

# Oracle 10G 之后版本修改列的数据类型:
ALTER TABLE table_name
MODIFY column_name datatype;

# 删除列
ALTER TABLE table_name
DROP COLUMN column_name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# DROP TABLE

DROP TABLE 语句用于删除表。

删除表将同时删除表的结构以及存储在其中的所有数据。因此，在执行DROP TABLE语句之前，请确保您真的希望永久删除表及其所有数据，因为此操作是不可逆的。

DROP TABLE [IF EXISTS] TABLE_NAME;

参数说明：

DROP TABLE：表示删除表的操作。
IF EXISTS：是一个可选的子句，用于检查表是否存在。如果存在，执行删除操作；如果不存在，不会报错。
table_name：要删除的表的名称。

# TRUNCATE TABLE

在 SQL 中，TRUNCATE TABLE语句用于快速删除表中的所有数据，但保留表的结构（列、约束等），与 DELETE 语句相比，TRUNCATE TABLE 通常更快，因为它是通过删除表中的所有行而不是逐行删除实现的。

然而，需要注意的是，TRUNCATE TABLE不会触发触发器，而且无法在事务中进行回滚。

TRUNCATE TABLE TABLE_NAME;

# CREATE INDEX

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

**注释：**更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

在表上创建一个普通索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

1
2

在表上创建一个唯一索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

1
2

**注释：**用于创建索引的语法在不同的数据库中不一样。

# DROP INDEX

索引是一种优化数据库查询性能的结构，但有时候可能需要删除某个索引，例如当索引不再需要或需要替换为新的索引时。

DROP INDEX 语句用于删除表中的索引。

DROP INDEX [IF EXISTS] index_name
ON table_name;

1
2

参数说明：

DROP INDEX：表示要删除索引的操作。
IF EXISTS：是一个可选的子句，用于检查索引是否存在。如果存在，就执行删除操作；如果不存在，不会报错。
index_name：要删除的索引的名称。
ON table_name：指定包含要删除索引的表的名称。

# Constraints

SQL 约束（Constraints）用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

创建时间：1.约束可以在创建表时创建（通过CREATE TABLE语句），2.也可以在表创建后创建（通过ALTER TABLE语句）。

约束范围：1.约束一个列，直接在列后使用。2.可以为约束命名，或约束多个列时，使用constraint语法。

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

1
2
3
4
5
6
7

在 SQL 中，我们有如下约束：

NOT NULL (opens new window) - 指示某列不能存储 NULL 值。
UNIQUE (opens new window) - 唯一索引，保证某列的每行必须有唯一的值。

命名：???
PRIMARY KEY (opens new window) - 主键索引，NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

一般主键命名：PK_表名

复合主键命名：PK_???
FOREIGN KEY (opens new window) - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键命名：FK_从表名_主表名
CHECK (opens new window) - 保证列中的值符合指定的条件。

命名：CK_表名_列名
DEFAULT (opens new window) - 规定没有给列赋值时的默认值。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。

# DML

数据操纵语言（Data Manipulation Language）

# SELECT

从数据库表中获取数据

# DISTINCT

# ORDER BY

ORDER BY，默认升序，可以指定ASC或DESC关键字对结果集进行排序

# GROUP BY

GROUP BY 语句，可以与聚集函数一起使用，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

1
2
3
4

# HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚集函数一起使用。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

1
2
3
4
5

WHERE 和 HAVING 关键字存在以下几点差异： 1、一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。 2、WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。 3、WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤。 4、WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。 5、WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。 6、执行顺序：WHERE 早于 GROUP BY 早于 HAVING

# TOP

TOP 子句用于规定要返回的记录的数目。

对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。

注释：并非所有的数据库系统都支持 TOP 子句。

Oracle 的语法：

SELECT * FROM (
    SELECT rownum as rn,t.*
    FROM table_name t
    WHERE rownum <= end
) tt
WHERE tt.rn >= start;

1
2
3
4
5
6

SQL Server 的语法：

SELECT TOP number|percent column_name(s)
FROM table_name;

1
2

# 分页

MySQL 的语法：

SELECT column_name(s)
FROM table_name
LIMIT start, end;

1
2
3

Oracle 的语法：

select tt.*
from (
	select rownum as rn,t.*
	from (
	  select * 
	  from table_name
	  order by column_name
	) t 
    where rownum <= end
) tt
where tt.rn >= start;

1
2
3
4
5
6
7
8
9
10
11

SQL Server 的语法：

# 别名

可以为列名称和表名称指定别名（Alias）。

表的 SQL Alias 语法

SELECT column_name(s)
FROM table_name AS alias_name;

1
2

列的 SQL Alias 语法

SELECT column_name AS alias_name
FROM table_name;

1
2

# 连接

# JOIN

# INNER JOIN

# LEFT JOIN

# RIGHT JOIN

# FULL JOIN

# 集合运算

# 并运算

使用 union 自动去除重复数据。

(SELECT column_name(s)
FROM table_name)
union
(SELECT column_name(s)
FROM table_name);

1
2
3
4
5

使用 union all 可以保留所有重复数据

(SELECT column_name(s)
FROM table_name)
union all
(SELECT column_name(s)
FROM table_name);

1
2
3
4
5

# 交运算

使用 intersect 自动去除重复数据。

(SELECT column_name(s)
FROM table_name)
intersect
(SELECT column_name(s)
FROM table_name);

1
2
3
4
5

使用 intersect all 可以保留所有重复数据

(SELECT column_name(s)
FROM table_name)
intersect all
(SELECT column_name(s)
FROM table_name);

1
2
3
4
5

# 差运算

使用 except 自动去除重复数据。

(SELECT column_name(s)
FROM table_name)
except
(SELECT column_name(s)
FROM table_name)

1
2
3
4
5

使用 except all 可以保留所有重复数据

(SELECT column_name(s)
FROM table_name)
except all
(SELECT column_name(s)
FROM table_name)

1
2
3
4
5

# INSERT INTO

向数据库表中插入数据，语法：

INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

1
2

# UPDATE

更新数据库表中的数据，语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

# DELETE

从数据库表中删除数据，语法：

DELETE FROM 表名称 WHERE 列名称 = 某值

# WHERE

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。语法：

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

# SQL 操作符

下面的操作符可在 WHERE 子句中使用：

操作符	描述
=	等于
<>	不等于在某些版本的 SQL 中，操作符 <> 可以写为 !=。
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
IN	在某个范围内
LIKE	搜索某种模式

# SQL LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SQL LIKE 操作符语法

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

1
2
3

SQL 通配符必须与 LIKE 运算符一起使用。

通配符	描述
%	代表零个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

# SQL IN 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

SQL IN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

1
2
3

# SQL BETWEEN 操作符

BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SQL BETWEEN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2

1
2
3

# 函数

# 聚合函数

聚合函数同时可以对多行数据进行操作，并返回一个结果。

# AVG 平均值

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

SELECT AVG(column_name) FROM table_name

# MAX 最大值

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

SELECT MAX(column_name) FROM table_name

# MIN 最小值

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

SELECT MIN(column_name) FROM table_name

# SUM 总和

SUM 函数返回数值列的总数（总额）。

SELECT SUM(column_name) FROM table_name

# COUNT 计数

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目，

**注释：**COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。

SELECT COUNT(DISTINCT column_name) FROM table_name

# Oracle 函数

# 单行函数

# 字符串函数

# 数值函数

# 日期函数

# 转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。

TO_CHAR(d|n[,fmt])，把日期和数字转换为指定格式的字符串。

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual

# 其它函数

NVL(xvalue)，如果x为空，返回value，否则返回X

NVL2(x,value1,value2)，如果x非空，返回value1，否则返回value2

# 聚合函数

PostgreSQL学习笔记树

初冬十月的笔记