【SQL】一文速通SQL
SQL知识概念介绍
1. Relation Schema vs Relation Instance
简单而言,Relation Schema 是一个表,有变量还有数据类型
R = (A1, A2, … , An)
e.g. Student (sid: integer, name: string, login: string, addr: string, gender: char)
Relation instance 表中的一行(row)
Note: 不是所有的表都可以被当作Relation关系表
-
每个表都需要有一个独一无二的名
-
列名都要是独一无二的名
-
每一行的结构都要一样: All tuples in a relation have the same structure
-
所有变量的数值都需要是单一的(atomic原子性原则),不能是多个值multivalued或者复合值composite
-
多值:
2 | Jane | Smith | HTML, CSS, JavaScript
-
复合值
1 | John | Doe | 123 Main St, New York, NY, 10001
-
-
每一列都是独一无二的,不能有两列数据一模一样
-
列的排序是不重要的 (immaterial)
2. RDMBS table extends mathematical relation
RDMBS 允许重复的列,支持空值,支持排序(行列)
3. The Structured Query Language (SQL) 介绍
- SQL 是一个标准的 用于与RDBMS交互的声明性语言 ( declarative language for interacting with RDBMS)
- SQL支持的语法大致分成2个类别:
- DDL (Data Definition Language)
- Create, drop, alter
- DML (Data Manipulation Language)
- INSERT, DELETE, UPDATE, SELECT … FROM … WHERE
- DDL (Data Definition Language)
4. 数据类型
a. 数值类型
- INT / INTEGER:整数类型,存储正负整数(例如
-2147483648
到2147483647
)。 - SMALLINT:较小范围的整数,适合小整数值。
- BIGINT:更大范围的整数,适合超大整数值。
- DECIMAL(p, s) 或 NUMERIC(p, s):定点数,用于存储精确的十进制数,其中
p
表示总位数,s
表示小数位数。 - FLOAT / REAL:浮点数,用于存储近似的小数,适合需要较大范围但不要求精确度的数据。
b. 字符串类型
-
CHAR(n):固定长度的字符串,
n
表示字符数。不足n
位会自动填充空格。使用
CHAR
数据类型而不指定长度(不加括号),通常会被数据库视为CHAR(1)
-
VARCHAR(n):可变长度的字符串,
n
表示最大字符数。适合存储长度不固定的字符串。不足会填充空值
-
TEXT:用于存储大文本数据,如长篇描述等。
c. 日期和时间类型
- DATE:只存储日期(年、月、日),格式通常为
YYYY-MM-DD
。 - TIME:只存储时间(小时、分钟、秒),格式为
HH:MM:SS
。 - DATETIME:同时存储日期和时间。
- TIMESTAMP:包含日期和时间,通常用于记录数据的创建或更新时间。
- INTERVAL: 5 DAY
d. 布尔类型
- BOOLEAN:用于存储布尔值,通常是
TRUE
或FALSE
。
e. 二进制数据类型
- BLOB(Binary Large Object):用于存储二进制数据,例如图像、音频、视频文件等。
5. 完整性约束(Integrity Constraints)
完整性约束(Integrity Constraints)是为了确保数据的准确性、一致性和完整性。通过这些约束,我们可以在创建或修改表时定义规则,以防止不符合要求的数据被插入表中。常见的完整性约束有以下几种:
1. NOT NULL
- 确保列中不能存储空值(NULL)。
- 应用于必须有值的列。例如,用户的用户名不能为空。
CREATE TABLE Users (UserID INT,UserName VARCHAR(50) NOT NULL
);
2. UNIQUE
- 确保列中的所有值都是唯一的,不能有重复值。
- 可以应用于一个或多个列。应用在多列时,会要求这些列的组合唯一。
CREATE TABLE Users (UserID INT UNIQUE,Email VARCHAR(100) UNIQUE
);
3. PRIMARY KEY
- 用于唯一标识表中的每一行数据。
- 主键列必须是唯一的,且不能为NULL。一个表中只能有一个主键。
- 主键可以由一列或多列组合而成。
CREATE TABLE Users (UserID INT PRIMARY KEY,UserName VARCHAR(50)
);
4. FOREIGN KEY
- 用于在表之间建立关联关系。
- 外键引用另一个表的主键或唯一键,用于确保该列的值必须在被引用的表中存在,保证数据的引用完整性。
CREATE TABLE Orders (OrderID INT PRIMARY KEY,UserID INT,FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
5. CHECK
- 用于确保列中的值满足特定条件。
- 可以用在一列或多列上,用于自定义数据的约束规则。
CREATE TABLE Products (ProductID INT PRIMARY KEY,Price DECIMAL(10, 2) CHECK (Price > 0)
);
使用
CHECK
约束的效果
- 如果插入一个负数的
Price
,会报错。- 如果插入一个大于0的价格,会成功。
测试插入数据
插入一个合法的价格:
INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Laptop', 1000.00);
输出:插入成功。表中将有一个
ProductID
为 1,ProductName
为 ‘Laptop’,价格为 1000.00 的记录。插入一个无效的价格(负数):
INSERT INTO Products (ProductID, ProductName, Price) VALUES (2, 'Smartphone', -500.00);
输出:报错(假设使用的是 MySQL,错误消息会类似于):
ERROR CHECK constraint failed: Products
这条插入语句会失败,因为违反了
CHECK (Price > 0)
的约束条件,价格不能为负数。
6. DEFAULT
- 为列设置一个默认值,当插入数据时没有指定该列的值时,会使用默认值。
- 确保某些列总有一个预定义的值,避免出现意外的NULL值。
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Status VARCHAR(20) DEFAULT 'Active',Level INTERGER DEFAULT 1 CHECK (Level BETWEEN 1 AND 5)
);
6. Star Schema(星型模式) 和 Snowflake Schema(雪花模式)
在数据仓库设计中,Star Schema(星型模式) 和 Snowflake Schema(雪花模式) 是两种常见的架构,它们都用于组织和存储数据,使得数据可以高效地查询和分析。这两种模式在结构上有所不同,适用于不同的分析需求。
1. Star Schema(星型模式)
Star Schema 是最简单的维度建模模式,表结构就像一颗星星,中心是事实表(fact table),而各个维度表(dimension table)则像星星的“光点”围绕在事实表周围。
特点:
- 事实表(Fact Table):存储业务数据的度量,如销售额、订单数量、收入等。
- 维度表(Dimension Table):存储描述事实表中数据的背景信息,如产品、客户、时间、地区等。每个维度表通常都有一个主键。
- 事实表和维度表之间通过外键关联。
- 维度表通常是去归一化的,即它们包含冗余的数据。
结构:
+------------+| Time |+------------+|+--------+---------+| |
+-----------+ +-----------+
| Product | | Customer |
+-----------+ +-----------+|+-----------+| Fact Table |+-----------+
优点:
- 查询性能好:由于维度表通常是去归一化的(包含冗余数据),查询时通常可以减少联接操作,提高性能。
- 简单易懂:结构直观,易于理解和使用。
- 适合OLAP查询:快速响应多维分析。
缺点:
- 冗余数据:去归一化可能会导致数据冗余,这会增加存储需求和维护成本。
- 数据更新复杂:当维度数据发生变化时,需要更新所有相关的记录。
2. Snowflake Schema(雪花模式)
Snowflake Schema 是对星型模式的扩展,它通过对维度表进行归一化来减少冗余数据,形成多级的、像雪花一样的层次结构。每个维度表都可以进一步分解成多个子维度表。
特点:
- 事实表:同样存储度量数据。
- 维度表:维度表会进一步被拆分成多个子表,这样可以消除冗余数据。
- 每个维度表的主键可以通过与其他子表的外键关联来建立关系。
结构:
+------------+| Time |+------------+|+-------+--------+| |
+------------+ +------------+
| Product | | Customer |
+------------+ +------------+| |
+-----------+ +-------------+
| ProductCategory| | CustomerRegion |
+-----------+ +-------------+|+-----------+| Fact Table |+-----------+
优点:
- 减少冗余数据:通过归一化维度表,减少了冗余数据,因此更节省存储空间。
- 更新更容易:由于数据是规范化的,更新维度数据时不需要修改多个表。
- 更好的数据一致性:减少了数据冗余后,可以保证数据一致性。
缺点:
- 查询性能较差:由于维度表是规范化的,查询时需要更多的联接操作,这可能影响查询性能。
- 结构复杂:比星型模式更加复杂,开发和维护难度较大。
- 适合ETL处理:数据仓库中的数据处理更为高效,但查询响应可能较慢。
3. Star Schema 和 Snowflake Schema 的对比
特性 | Star Schema | Snowflake Schema |
---|---|---|
结构复杂性 | 简单,中心是事实表,周围是维度表 | 复杂,维度表进一步被拆分为子表 |
数据冗余 | 存在冗余(去归一化的维度表) | 减少冗余(维度表是归一化的) |
查询性能 | 通常较好,减少联接操作 | 查询性能较差,更多的联接操作 |
存储需求 | 存储需求较高(冗余数据) | 存储需求较低(通过归一化减少冗余) |
数据更新 | 更新维度数据时较为复杂,需要更新冗余数据 | 更新更容易,只需要修改子表中的数据 |
适用场景 | 适用于查询密集型应用(OLAP) | 适用于需要高数据一致性且处理复杂数据的场景 |
ETL 处理 | 简单,处理时较少需要联接 | 更复杂,需要联接多个子表 |
4. 总结
- Star Schema(星型模式):适用于查询响应速度要求较高的场景,结构简单,易于理解,但会存在数据冗余和存储开销。
- Snowflake Schema(雪花模式):适用于数据更新频繁、需要减少冗余数据的场景,结构复杂,查询性能较差,但可以更好地保证数据一致性。
选择使用哪种模式取决于你的需求。如果查询性能最重要,可以使用星型模式;如果存储空间和数据一致性更重要,雪花模式可能更合适。
SQL 核心命令介绍
1. 键(Relational Keys)
在关系型数据库中,键(Key) 是用来唯一标识每一行数据的字段或字段组合。通过键,可以确保数据表中的每一行都有唯一的标识符。根据不同的用途和功能,数据库中有多种类型的键。以下是常见的键的类型和概念:
1. 候选键(Candidate Key)
-
定义
:候选键是一个能够唯一标识表中每一行数据的字段或字段组合。候选键有以下特征:
- 唯一性:候选键的值在表中是唯一的。
- 最小性:候选键不能包含多余的字段(即,去除其中任何一个字段后,它就无法再唯一标识数据行了)。
-
举例:如果我们有一个
Users
表,其中有UserID
和Email
两个字段,并且它们都能唯一标识每一个用户,那么UserID
和Email
都是候选键。
2. 主键(Primary Key)
-
定义:主键是从候选键中选出的一个,用于唯一标识表中的每一行数据。每个表只能有一个主键。
-
特征
- 唯一性:主键值在表中必须唯一。
- 非空性:主键字段不能为空(不能为
NULL
)。 - 主键是候选键中的一种。
-
举例:在
Users
表中,UserID
可以是主键,而Email
可能是候选键,但是只有一个可以作为主键。
CREATE TABLE Users (UserID INT PRIMARY KEY, -- 主键UserName VARCHAR(50),Email VARCHAR(100)
);
3. 外键(Foreign Key)
-
定义
-
外键是一个表中的字段,它引用另一个表的主键或唯一键。外键用于建立和维护表与表之间的关系。
-
外键确保引用的列(外键)中的值必须在被引用的表的主键中存在,从而保持数据的一致性。
-
-
举例:如果我们有一个
Orders
表,UserID
列可能是外键,引用Users
表的UserID
列。
CREATE TABLE Orders (OrderID INT PRIMARY KEY,OrderDate DATE,UserID INT,FOREIGN KEY (UserID) REFERENCES Users(UserID) -- 外键,引用 Users 表的主键
);
在上述示例中,Orders
表的 UserID
是外键,指向 Users
表的 UserID
主键,确保每个订单都对应一个有效的用户。
4. 唯一键(Unique Key)
-
定义
:唯一键类似于主键,它也要求列中的值是唯一的,但与主键不同的是:
- 唯一键的列可以包含
NULL
值(但每个NULL
只会出现一次)。 - 一个表可以有多个唯一键。
- 唯一键的列可以包含
-
举例:在
Users
表中,Email
列可以是唯一键,确保没有两个用户拥有相同的邮箱地址。
CREATE TABLE Users (UserID INT PRIMARY KEY, UserName VARCHAR(50),Email VARCHAR(100) UNIQUE -- 唯一键
);
5. 复合键(Composite Key)
- 定义:复合键是由多个字段组合而成的键,用来唯一标识一行数据。复合键的每个单独字段可能不能唯一标识数据,但它们的组合能够唯一标识一行数据。
- 举例:假设我们有一个
CourseRegistrations
表,其中StudentID
和CourseID
的组合能够唯一标识每个学生的选课记录,因此可以将这两个字段作为复合主键。
CREATE TABLE CourseRegistrations (StudentID INT,CourseID INT,RegistrationDate DATE,PRIMARY KEY (StudentID, CourseID) -- 复合主键
);
在这个例子中,单独的 StudentID
或 CourseID
都不能唯一标识一行记录,但它们的组合可以。
候选键不一定是主键,主键一定是候选键.
2.Key 和 Foreign Key in SQL
在 SQL 中,PRIMARY KEY
和 FOREIGN KEY
是用来维护数据完整性和表之间关系的关键概念。你可以在 SQL CREATE TABLE
语句中定义这些约束。以下是关于 Primary Keys 和 Foreign Keys 的详细说明。
1. PRIMARY KEY (主键)
- 主键是用来唯一标识表中每一行数据的。每个表只能有一个主键。
- 主键可以是一个或多个列(也叫复合主键),它们组合起来确保每一行数据的唯一性。
- 主键列不允许
NULL
值。
语法:
eCREATE TABLE table_name (column1 datatype,column2 datatype,PRIMARY KEY (column1, column2) -- 指定主键
);
例子:
CREATE TABLE Students (sid INT,name VARCHAR(50),age INT,CONSTRAINT Student_PK PRIMARY KEY (sid) -- sid 是主键
);
在这个例子中,sid
列被指定为 PRIMARY KEY
,确保每个学生的 sid
是唯一的,并且不能为空。
2. UNIQUE (唯一约束)
- 唯一约束确保列中的每个值都是唯一的,但不同于主键,它允许
NULL
值。 - 一个表中可以有多个唯一约束。
语法:
CREATE TABLE table_name (column1 datatype,column2 datatype,UNIQUE (column1) -- 列设置为唯一
);
例子:
CREATE TABLE Users (user_id INT,email VARCHAR(100) UNIQUE, -- email 是唯一的,可以为空username VARCHAR(50) UNIQUE -- username 也是唯一的
);
3. FOREIGN KEY (外键)
- 外键用于在两个表之间建立关系,它引用另一个表的主键或唯一键。
- 外键用于维护数据的一致性和完整性,确保在一个表中的某个列的值存在于另一个表中。
- 默认情况下,外键引用的是父表(被引用表)的主键。
语法:
CREATE TABLE table_name (column1 datatype,column2 datatype,FOREIGN KEY (column1) REFERENCES referenced_table (referenced_column) -- 外键约束
);
例子:
CREATE TABLE Orders (order_id INT PRIMARY KEY,order_date DATE,sid INT,FOREIGN KEY (sid) REFERENCES Students(sid) -- sid 外键,引用 Students 表的 sid 列
);
在这个例子中,Orders
表中的 sid
列是外键,引用 Students
表中的 sid
列。这样,每个订单都必须与一个已存在的学生 ID 关联。
4. 外键引用的父表列
- 外键引用的列通常是另一个表中的主键或唯一键。
- 可以显式地指定外键列引用的列名。例如:
FOREIGN KEY (lecturer) REFERENCES Lecturer(empid)
这里,lecturer
列引用 Lecturer
表的 empid
列。
5. 指定外键约束的名称
你可以在创建表时,使用 CONSTRAINT
关键字为外键约束命名。这有助于在之后修改或删除约束时方便引用。
语法:
CREATE TABLE table_name (column1 datatype,column2 datatype,CONSTRAINT fk_name FOREIGN KEY (column1) REFERENCES referenced_table (referenced_column)
);
例子:
CREATE TABLE Orders (order_id INT PRIMARY KEY,sid INT,CONSTRAINT fk_student FOREIGN KEY (sid) REFERENCES Students(sid) -- 给外键约束命名
);
6. 外键的删除和更新规则
你可以使用外键约束来指定父表数据被删除或更新时,子表数据应该如何处理。例如,可以使用 ON DELETE CASCADE
来定义删除父表数据时,自动删除子表数据。
例子:
CREATE TABLE Orders (order_id INT PRIMARY KEY,sid INT,CONSTRAINT fk_student FOREIGN KEY (sid) REFERENCES Students(sid) ON DELETE CASCADE -- 删除学生记录时,自动删除与该学生相关的订单
);
7. 总结
- 主键 (
PRIMARY KEY
) 确保表中的每行数据唯一并且不为空,通常用于标识每一行数据。 - 外键 (
FOREIGN KEY
) 用于建立表与表之间的关系,确保在子表中的数据在父表中存在。 - 唯一约束 (
UNIQUE
) 确保列中的值唯一,但允许NULL
值。 - 可以使用
CONSTRAINT
关键字为主键、外键和其他约束命名。 - 外键可以指定级联操作(如
ON DELETE CASCADE
和ON UPDATE CASCADE
),在父表的数据变化时自动影响子表数据。
CREATE TABLE Student(sid INTEGERCONSTRAINT STUDENT_PK PRIMARY KEY (sid)
);CREATE TABLE UoS (ucode CHAR(8),CONSTRAINT UoS_PK PRIMARY KEY (ucode)
);CREATE TABLE Enrolled (sid INTEGER,ucode CHAR(8),semester VVARCHAR,CONSTRAINT Enrolled_FK1 FOREIGN KEY (sid) REFERENCES Student,CONSTRAINT Enrolled_FK2 FOREIGN KEY (ucode) REFERENCES UoS,CONSTRAINT Enrolled_PK PRIMARY KEY (sid, ucode)
)
3. CONSTRAINT 关键词
在 SQL 中,CONSTRAINT
是可选的,但它对于定义和管理约束(如主键、外键、唯一性约束等)有一些重要的优点和功能。
1. 使用 CONSTRAINT
定义约束
当你使用 CONSTRAINT
来定义约束时,你可以为该约束指定一个名称。这样做的主要优点是:
- 命名约束:你可以为每个约束指定一个名称,方便在后续修改或删除时引用这个约束。
- 更清晰的语法:你可以明确标识每个约束的作用和目标列。
- 更好的可维护性:约束的名称使得数据库的设计和管理更清晰,尤其是在复杂的数据库中。
2. 不使用 CONSTRAINT
定义约束
如果不使用 CONSTRAINT
,约束会依赖于默认的规则(如自动生成的名称)。这种方式更简洁,但也存在一些缺点:
- 自动生成名称:如果你没有显式指定约束名称,数据库系统会自动生成一个默认的名称,这个名称通常是随机的、难以理解的,难以维护。
- 不易修改:没有明确的约束名称时,删除或修改约束时就无法直接引用该约束。
- 难以阅读:约束的定义可能不如显式命名清晰,尤其是在查看数据库架构时。
3. 例子
3.1 使用 CONSTRAINT
定义约束
CREATE TABLE Students (sid INT,name VARCHAR(50),age INT,CONSTRAINT STUDENT_PK PRIMARY KEY (sid) -- 显式命名主键约束
);
在这个例子中,CONSTRAINT STUDENT_PK PRIMARY KEY (sid)
显式地为主键约束命名为 STUDENT_PK
,这样你就可以在以后的数据库操作中使用该名称引用该约束(例如在删除约束时)。
3.2 不使用 CONSTRAINT
定义约束
CREATE TABLE Students (sid INT PRIMARY KEY, -- 直接在列定义时指定约束name VARCHAR(50),age INT
);
这里没有使用 CONSTRAINT
关键字,而是直接在列定义时定义了 PRIMARY KEY
约束。这样,sid
列被设置为主键,但没有显式指定约束名称。数据库系统会自动为主键生成一个默认的名称(例如 PRIMARY
)。
CREATE Movie Details(
Movie_ID INT PRIMARY KEY,
Movie_Title VARCHAR(20),
Year_of_Release INT
);
CREATE Sales Order(
Order_ID CHAR(4) PRIMARY KEY
Date_Of_Purchase DATE,
STORE_ID VARCHAR(6),
Movie_ID INT,
FOREIGN KEY (Movie_ID) REFERENCES Movie_Details(Movie_ID)
);
4. SELECT 读取数据
- 读取数据库 USE Egg_database;
SELECT
是 SQL 中最常用的语句,用于从数据库中查询数据。它的基本语法是:
sqlCopy codeSELECT column1, column2, ...
FROM table_name
WHERE condition;
常见的 SELECT
语句例子及解释
1. 查询所有记录
SELECT * FROM T;
含义:
- 这个查询语句返回表
T
中的所有列和所有行(记录)。 *
表示选择所有列。
示例: 假设有一个名为 Employees
的表,SELECT * FROM Employees;
会返回 Employees
表中的所有数据。
2. 查询记录的数量
SELECT COUNT(*) FROM T;
含义:
COUNT(*)
是一个聚合函数,返回表T
中的行数。*
表示计算所有的行。- 这个查询会返回表
T
中的总记录数。
示例: 假设表 Employees
有 1000 条记录,SELECT COUNT(*) FROM Employees;
会返回 1000
。
3. 查询前 n 条记录
SELECT * FROM T LIMIT n;
含义:
LIMIT n
限制查询结果只返回前n
行数据。n
是一个数字,指定返回多少行。
示例:
SELECT * FROM Employees LIMIT 5;
- 返回
Employees
表的前 5 行记录。
4. 按列排序
SELECT * FROM T ORDER BY a;
含义:
ORDER BY
用来对结果进行排序,默认是按升序排序(从小到大)。a
是你希望排序的列名。
示例:
SELECT * FROM Employees ORDER BY age;
- 这个查询将返回
Employees
表中的所有记录,并按age
(年龄)列升序排序。
5. 按列降序排序
SELECT * FROM T ORDER BY a DESC;
含义:
ORDER BY a DESC
用来对结果按列a
进行降序排序(从大到小)。DESC
是降序排序的关键字。
示例:
SELECT * FROM Employees ORDER BY salary DESC;
- 这个查询将返回
Employees
表的所有记录,并按salary
(薪资)列的降序排序。
6. 使用 DISTINCT 排除重复值
SELECT DISTINCT a FROM T;
含义:
DISTINCT
用来去掉列a
中的重复值,返回唯一的结果。
示例:
SELECT DISTINCT department FROM Employees;
- 这个查询会返回
Employees
表中所有不同的department
(部门)值,去除重复的部门。
7. 使用 COUNT() 计算唯一值
SELECT COUNT(DISTINCT a) FROM T;
含义:
COUNT(DISTINCT a)
会计算列a
中不重复的值的数量。
示例:
SELECT COUNT(DISTINCT department) FROM Employees;
- 这个查询会返回
Employees
表中不同department
(部门)的数量。
8. 计算某列的总和、平均值、最小值和最大值
sqlCopy code
SELECT SUM(a), AVG(a), MIN(a), MAX(a) FROM T;
含义:
SUM(a)
:返回列a
中所有值的总和。AVG(a)
:返回列a
中所有值的平均值。MIN(a)
:返回列a
中的最小值。MAX(a)
:返回列a
中的最大值。
示例:
SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM Employees;
- 这个查询会返回
Employees
表中所有salary
(薪资)值的总和、平均值、最小值和最大值。
9. 使用 WHERE 进行条件过滤
SELECT * FROM T WHERE condition;
含义:
WHERE
子句用于过滤结果集,只返回符合指定条件的记录。
示例:
SELECT * FROM Employees WHERE department = 'HR';
- 这个查询会返回
Employees
表中所有部门为'HR'
的记录。
COUNT() 函数的不同用法
-
COUNT(*)
:计算所有行的数量,不考虑行中的 NULL值。
SELECT COUNT(*) FROM Employees;
-
COUNT(ALL column_name)
:计算列中非 NULL的行数,ALL是默认的,通常不需要显式声明。
SELECT COUNT(ALL salary) FROM Employees;
-
COUNT(DISTINCT column_name)
:计算列中唯一值的数量,忽略重复的值。
SELECT COUNT(DISTINCT department) FROM Employees;
总结
SELECT
语句用于从数据库中查询数据。COUNT()
是一个聚合函数,用于计算符合条件的记录数。- 使用
LIMIT
来限制返回的记录数,ORDER BY
来排序结果。 DISTINCT
用于去重,WHERE
用于筛选条件。
5. VARCHAR的match操作和合并操作(%,_,||)
Pattern Matching with LIKE Operator and %
Placeholders
在 SQL 中,LIKE
操作符用于执行模式匹配查询,通常用来搜索列值是否符合特定的模式。常常与通配符(placeholders)结合使用,这使得你可以灵活地搜索包含特定字符串的记录。
基本语法:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
通配符(Placeholders)
%
:代表零个或多个字符(包括空字符串)。%
可以放在模式的任何位置,用于匹配多个字符。_
:代表一个单独的字符。_
替代一个位置的字符。
示例:
假设有一个名为 Employees
的表,包含字段 name
(员工姓名)。
1. %
通配符
-
%
代表零个或多个字符:SELECT * FROM Employees WHERE name LIKE 'Jo%';
含义:查询所有名字以
'Jo'
开头的员工,比如'John'
,'Joan'
,'Joe'
等。SELECT * FROM Employees WHERE name LIKE '%n';
含义:查询所有名字以
'n'
结尾的员工,如'John'
,'Ann'
等。SELECT * FROM Employees WHERE name LIKE '%an%';
含义:查询所有名字包含
'an'
的员工,如'Joan'
,'Ann'
,'Nathan'
等。
2. _
通配符
-
_
代表单个字符:SELECT * FROM Employees WHERE name LIKE '_o_';
含义:查询所有名字由三个字符组成,中间字符为
'o'
,例如'Jon'
,'Tom'
等。SELECT * FROM Employees WHERE name LIKE 'Jo_';
含义:查询所有名字由三个字符组成,以
'Jo'
开头的员工,如'Jon'
,'Joe'
。
3. 结合 %
和 _
使用
SELECT * FROM Employees
WHERE name LIKE 'J_o%';
含义:查询所有名字以 'J'
开头,第三个字符为 'o'
,后面可以跟任意数量的字符的员工,例如 'John'
, 'Joan'
。
String Concatenation: ||
Operator
在 SQL 中,字符串连接(Concatenation) 用于将两个或多个字符串连接在一起。不同的数据库管理系统(DBMS)可能有不同的语法来实现这一操作,但 ||
是 ANSI SQL 标准中用于字符串连接的操作符。
基本语法:
SELECT string1 || string2 || string3;
示例:
假设你有一个 Employees
表,其中有 first_name
和 last_name
字段,你想将这两个字段的值合并为一个全名:
SELECT first_name || ' ' || last_name AS full_name
FROM Employees;
含义:
- 这个查询将
first_name
和last_name
字段的值连接起来,中间加一个空格,并命名为full_name
。
另一个例子:
SELECT 'Employee: ' || first_name || ' ' || last_name
FROM Employees;
含义:
- 在每个员工的名字前加上
'Employee: '
字符串,输出格式如'Employee: John Doe'
。
注意:
-
在一些数据库系统(例如 MySQL)中,字符串连接的操作符是
CONCAT()
函数,而不是||
。例如:SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Employees;
总结:
-
LIKE
操作符:用于在查询中进行模式匹配,通过使用 % 和 _
通配符,可以灵活地查找符合条件的记录。
%
用于匹配零个或多个字符。_
用于匹配单个字符。
-
||
操作符:用于将多个字符串连接成一个字符串。
6. SQL 中的比较操作符
在 SQL 中,比较操作符 用于在查询中对数据进行条件比较。比较操作符可以帮助你筛选符合条件的记录,支持多种类型的比较,如数值比较、字符串比较、范围比较等。
常见的比较操作符
-
=
:等于- 用于比较两个值是否相等。
SELECT * FROM Employees WHERE age = 30;
- 返回
age
为 30 的员工。
-
>
:大于- 用于比较左边的值是否大于右边的值。
SELECT * FROM Employees WHERE salary > 50000;
- 返回薪资大于 50000 的员工。
-
> =
:大于或等于- 用于比较左边的值是否大于或等于右边的值。
SELECT * FROM Employees WHERE salary > = 50000;
- 返回薪资大于或等于 50000 的员工。
-
<
:小于- 用于比较左边的值是否小于右边的值。
SELECT * FROM Employees WHERE salary < 50000;
- 返回薪资小于 50000 的员工。
-
< =
:小于或等于- 用于比较左边的值是否小于或等于右边的值。
SELECT * FROM Employees WHERE salary < = 50000;
- 返回薪资小于或等于 50000 的员工。
-
!=
或<>
:不等于- 用于比较两个值是否不相等。在某些数据库系统中,
!=
和<>
都可以表示不等于。
SELECT * FROM Employees WHERE salary != 50000;
- 返回薪资不等于 50000 的员工。
- 用于比较两个值是否不相等。在某些数据库系统中,
-
BETWEEN
:在范围内- 用于检查一个值是否在两个给定的值之间(包括边界值)。
SELECT * FROM Employees WHERE age BETWEEN 30 AND 40;
- 返回年龄在 30 到 40 之间(包含 30 和 40)的员工。
结合逻辑运算符
在 SQL 中,可以使用 逻辑连接词(logical connectives)组合多个比较操作符来形成更复杂的查询条件:
-
AND
:与- 用于连接多个条件,只有当所有条件都为真时,查询才会返回结果。
SELECT * FROM Employees WHERE age > 30 AND salary > 50000;
- 返回年龄大于 30 且薪资大于 50000 的员工。
-
OR
:或- 用于连接多个条件,只要其中一个条件为真,查询就会返回结果。
SELECT * FROM Employees WHERE age > 30 OR salary > 50000;
- 返回年龄大于 30 或薪资大于 50000 的员工。
-
NOT
:非- 用于对条件取反,返回不符合条件的记录。
SELECT * FROM Employees WHERE NOT (age > 30);
- 返回年龄不大于 30 的员工。
示例 1: 使用 BETWEEN
和 AND
连接多个条件
SELECT * FROM TelescopeConfig
WHERE (mindec BETWEEN -90 AND -50)
AND (maxdec >= -45)
AND (tele_array = 'H168');
含义:
-
这个查询返回
TelescopeConfig
表中,满足以下条件的记录:
mindec
在 -90 到 -50 之间(包括 -90 和 -50)。maxdec
大于或等于 -45。tele_array
的值为'H168'
。
工作流程:
- 第一个条件
(mindec BETWEEN -90 AND -50)
使用BETWEEN
来筛选出mindec
值在 -90 到 -50 之间的记录。 - 第二个条件
(maxdec >= -45)
筛选出maxdec
值大于或等于 -45 的记录。 - 第三个条件
(tele_array = 'H168')
筛选出tele_array
为'H168'
的记录。
这些条件通过 AND
运算符结合,表示只有满足所有条件的记录会被选中。
示例 2: 使用 LIKE
进行模糊匹配
SELECT *
FROM TelescopeConfig
WHERE tele_array LIKE 'H%';
含义:
- 这个查询返回
TelescopeConfig
表中,tele_array
字段以'H'
开头的所有记录。 %
是 SQL 中的通配符,表示任意数量的字符,因此'H%'
可以匹配所有以'H'
开头的字符串。
工作流程:
- 通过
LIKE 'H%'
,查询会返回所有以'H'
开头的tele_array
值的记录。
总结
- 比较操作符:用来在 SQL 中对数据进行比较,如
=
,>
,<
,BETWEEN
等。 - 逻辑运算符:用于组合多个条件,如
AND
,OR
,NOT
。 BETWEEN
:检查值是否在指定范围内。LIKE
:进行模糊匹配,常用于字符串的查询。
7. SQL 中的日期和时间
在 SQL 中,日期和时间是非常重要的数据类型,特别是在涉及到时间范围和历史记录时。SQL 支持多种日期和时间操作,包括日期比较、日期格式化以及时间间隔的计算。不同的数据库系统(如 MySQL、PostgreSQL、Oracle 和 SQL Server)对日期时间的支持有所不同,但大多数提供了类似的功能。
常见的日期时间数据类型
DATE
:表示日期(年-月-日)- 例如:
DATE '2012-03-26'
,表示2012年3月26日。 - 一些系统可能还包括时间部分(如
DATE
类型会包含时间信息)。
- 例如:
TIME
:表示时间(时:分:秒),通常精确到纳秒级。- 例如:
TIME '16:12:05'
,表示时间16:12:05。
- 例如:
TIMESTAMP
:表示一个日期和时间,通常用来表示一个时间点。- 例如:
TIMESTAMP '2012-03-26 16:12:05'
,表示2012年3月26日16时12分05秒。
- 例如:
INTERVAL
:表示时间的持续时长(例如几天、几小时)。- 例如:
INTERVAL '5 DAY'
表示5天。
- 例如:
DATETIME
:在 SQL Server 中使用的数据类型,包含日期和时间。
常见的日期时间比较操作
SQL 支持对日期和时间进行比较,常见的操作符包括 =
, >
, <
, < =
, > =
等。这些操作符可以用于日期和时间的字段之间进行比较。
SELECT *
FROM Epoch
WHERE startDate < CURRENT_DATE;
解释:返回 startDate
小于当前日期的所有记录。CURRENT_DATE
是数据库系统的当前日期。
日期时间常量
-
CURRENT_DATE
:返回数据库系统的当前日期(不包括时间)。SELECT CURRENT_DATE;
- 返回当前日期,例如
2024-11-12
。
- 返回当前日期,例如
-
CURRENT_TIME
:返回当前时间戳(包括日期和时间)。SELECT CURRENT_TIME;
- 返回当前时间和日期,例如
2024-11-12 14:32:55
。
- 返回当前时间和日期,例如
日期和时间的常见操作
-
日期时间的提取(EXTRACT)
使用
EXTRACT
函数可以从日期或时间中提取特定的部分,例如年、月、日等。SELECT EXTRACT(YEAR FROM startDate) AS startYear FROM Epoch;
解释:从
startDate
字段中提取年份。 -
日期字符串转换(DATE string)
- 通过
DATE
常量,可以将字符串转换为日期格式。 - 例如:
DATE '2012-03-01'
会转换为日期2012-03-01
。
Oracle 特有语法:
TO_DATE
函数用于将字符串转换为日期。SELECT TO_DATE('01-03-2012', 'DD-Mon-YYYY') FROM DUAL;
解释:将
'01-03-2012'
按'DD-Mon-YYYY'
格式转换为日期2012-03-01
。 - 通过
-
日期与时间间隔的加减(
+/- INTERVAL
)SQL 支持对日期和时间进行加减操作,可以使用
INTERVAL
来表示时间间隔。SELECT '2012-04-01' + INTERVAL '36 HOUR' AS newDate;
解释:将
'2012-04-01'
日期加上36小时,返回新的日期和时间。SELECT '2012-04-01' - INTERVAL '5 DAY' AS newDate;
解释:将
'2012-04-01'
日期减去5天,返回新的日期。 -
一些数据库特有的日期操作
-
MySQL:使用
DATE_ADD()
和DATE_SUB()
来加减日期时间。SELECT DATE_ADD('2012-03-26', INTERVAL 5 DAY);
-
PostgreSQL:使用
+ INTERVAL
或- INTERVAL
进行日期加减。SELECT '2012-03-26' + INTERVAL '5 DAY';
-
常见的 SQL 日期时间函数
SQL 类型 | 示例 | 描述 |
---|---|---|
DATE | DATE '2012-03-26' | 仅日期部分 |
TIME | TIME '16:12:05' | 仅时间部分 |
TIMESTAMP | TIMESTAMP '2012-03-26 16:12:05' | 日期和时间 |
INTERVAL | INTERVAL '5 DAY' | 时间间隔(如 5 天) |
CURRENT_DATE | CURRENT_DATE | 当前日期(不包括时间) |
CURRENT_TIME | CURRENT_TIME | 当前时间戳(包括日期和时间) |
8. SQL 中的 JOIN 操作
在 SQL 中,当数据分布在多个表(关系)中时,我们通常需要将这些表的数据结合起来进行查询,这个过程称为 JOIN。
JOIN 基本概念
- FROM 子句列出了查询中涉及的所有表(关系)。
- JOIN 谓词 可以通过 WHERE 子句明确指定,确保正确地组合数据。
- 数据表的组合方式有多种,常见的有 自然连接(Natural Join)、等值连接(Equi-Join) 和 任意连接(Theta Join)。
交叉连接 (Cross Join)
交叉连接 会返回两个表的 笛卡尔积,即两个表中所有行的所有组合。常用于需要生成所有可能组合的情况。
SELECT *
FROM Station, Organisation;
解释:这个查询会返回 Station
表和 Organisation
表的笛卡尔积。每个 Station
表中的行与 Organisation
表中的每一行都进行组合。
使用 WHERE
子句进行连接
可以使用 WHERE
子句指定连接条件,这通常是实现 等值连接(Equi-Join) 或 Theta 连接(Theta Join) 的方式之一。
查询示例:查找所有站点的名称、开始日期和组织名称
SELECT sitename, commence, organisation
FROM Station, Organisation
WHERE orgcode = code;
解释:
WHERE orgcode = code
指定了Station
表的orgcode
列与Organisation
表的code
列相等。- 该查询返回了站点名称、开始日期和组织名称,符合连接条件的记录。
SQL Join 操作符
SQL 提供了多种 JOIN 操作符 来实现不同类型的连接:自然连接(NATURAL JOIN)、内连接(INNER JOIN)、外连接(OUTER JOIN)等。
常见的 JOIN 操作
-
NATURAL JOIN(自然连接)
自然连接 是根据两个表中具有相同列名的列进行连接,自动排除重复的列。
SELECT * FROM Measurement NATURAL JOIN Sensor;
解释:
- 这个查询会根据
Measurement
表和Sensor
表中相同名称的列(通常是sensor
)自动进行连接。
- 这个查询会根据
-
INNER JOIN(内连接)
内连接 返回两个表中符合连接条件的记录。
SELECT sitename, commence, organisation FROM Station INNER JOIN Organisation ON orgcode = code;
解释:
INNER JOIN
连接Station
和Organisation
表,条件是orgcode
与code
相等。
-
USING 关键字(简化的连接)
USING 用于指定要用于连接的列名(当两个表中列名相同时)。
SELECT * FROM Measurement JOIN Sensor USING (sensor);
解释:
USING (sensor)
表示连接Measurement
和Sensor
表时,基于共同的sensor
列。
JOIN 的语义
在 SQL 查询中,JOIN 操作的语义可以按以下步骤进行解释:
- 笛卡尔积:首先计算所有参与连接的表的 笛卡尔积(即所有记录的组合)。
- 应用选择条件:接着应用 WHERE 子句中的条件,从笛卡尔积中筛选出符合条件的记录。
- 应用投影:最后,选择并返回查询中指定的列。
示例:SELECT-FROM-WHERE 查询的执行顺序
SELECT A1, A2, ..., An
FROM R1, R2, ..., Rm
WHERE condition;
解释:
- 笛卡尔积:首先计算
R1
,R2
, …,Rm
表的笛卡尔积(所有行的组合)。 - 应用选择条件:根据
WHERE
子句中的条件筛选出符合要求的记录。 - 应用投影:从符合条件的记录中选择列
A1, A2, ..., An
。
这个过程描述了 SQL 查询的基本执行流程,但并不具体描述数据库管理系统(DBMS)是如何执行这个过程的。
示例 1: 查找所有的测量数据,包含水质数据
SELECT *
FROM Measurement JOIN Sensor USING (sensor) LIMIT 3;
解释:
- 这个查询会返回
Measurement
表和Sensor
表的连接结果,并且限制结果只返回前三行。
示例 2: 查找所有站点的名称、开始日期和组织名称
SELECT sitename, commence, organisation
FROM Station JOIN Organisation ON orgcode = code;
解释:
- 该查询使用
INNER JOIN
将Station
和Organisation
表连接起来,并返回站点名称、开始日期和组织名称。
总结
- JOIN 操作:可以将多个表的数据结合起来,常见的操作符包括
NATURAL JOIN
,INNER JOIN
,OUTER JOIN
等。 - 连接条件:连接通常通过
ON
子句(指定连接条件)或USING
子句(简化的连接)来指定。 - 执行顺序:SQL 查询的执行顺序是:笛卡尔积 → 应用选择条件 → 应用投影。
- JOIN 类型:
NATURAL JOIN
:自动根据相同的列名进行连接。INNER JOIN
:返回符合条件的记录。USING
:简化的连接,自动根据共享的列名进行连接。
INNER JOIN | LEFT JOIN | RIGHT JOIN | UNION
INNER JOIN
只返回两张表中符合条件的匹配记录。若某条记录在任意一张表中找不到匹配项,该记录不会出现在结果中。
语法:
SELECT *
FROM Covid_month
INNER JOIN Covid_total
ON Covid_month.Country = Covid_total.Country;
在这个例子中,Covid_month
和 Covid_total
表中 Country
列相等的记录会被合并在一起。
2. LEFT JOIN(左连接)
LEFT JOIN
会返回左表的所有记录,即使右表中没有匹配项。- 如果右表没有匹配项,则这些列的结果显示为
NULL
。
示例:
SELECT *
FROM Covid_month AS mm
LEFT JOIN Covid_total AS tt
ON mm.Country = tt.Country;
该查询会返回 Covid_month
中所有国家的数据。若 Covid_total
中有与之匹配的国家,则会显示相应的记录;若没有,则相关列显示为 NULL
。
3. RIGHT JOIN(右连接)
RIGHT JOIN
会返回右表的所有记录,即使左表中没有匹配项。- 如果左表没有匹配项,则这些列的结果显示为
NULL
。
示例:
SELECT *
FROM Covid_month AS mm
RIGHT JOIN Covid_total AS tt
ON mm.Country = tt.Country;
该查询会返回 Covid_total
中所有国家的数据,若 Covid_month
中有匹配的国家则会合并显示,没有匹配的部分将显示为 NULL
。
4. UNION 与 UNION ALL
UNION
用于将两张表上下合并,去除重复的记录。UNION ALL
同样合并两张表的数据,但不去重,显示所有记录。
示例:
SELECT Country
FROM Covid_month
UNION ALL
SELECT Country
FROM Covid_total;
在这个查询中,UNION ALL
合并 Covid_month
和 Covid_total
中的 Country
列,保留所有记录,包括重复的国家名。如果使用 UNION
,则会自动去除重复项。
9.一个非直观的 SQL 查询分析
让我们详细分析这个查询,特别是在 S
表为空的情况下,为什么查询结果可能是空的。
查询:
SELECT DISTINCT R.A
FROM R, S, T
WHERE R.A = S.A OR R.A = T.A;
假设我们有三个表:R
、S
和 T
,每个表都包含一个整数类型的属性 A
。
查询执行步骤
-
笛卡尔积(Cross Product):
- SQL 首先计算
R
、S
和T
三个表的 笛卡尔积。即每个表中的所有行会与其他表的所有行进行组合。 - 如果
R
、S
和T
表中分别有n
、m
和k
行数据,那么笛卡尔积的结果将是n * m * k
行。
- SQL 首先计算
-
选择条件(Selection Condition):
-
在笛卡尔积的基础上,查询会应用选择条件
WHERE R.A = S.A OR R.A = T.A
,意味着只有满足以下条件的行会被保留:
- 要么
R.A
和S.A
相等 - 要么
R.A
和T.A
相等
- 要么
-
这意味着查询会选择
R.A
与S.A
或R.A
与T.A
具有相同值的所有组合。
-
-
投影(Projection):
- 最后,查询使用
SELECT DISTINCT R.A
从符合条件的记录中选择R.A
并去除重复项。 - 这就意味着查询最终会返回满足条件的所有
R.A
的唯一值。
- 最后,查询使用
查询结果:R ∩ (S ∪ T)
根据查询的条件,查询实际上计算的是 R
与 (S ∪ T)
的交集。也就是说,查询返回的是 R.A
中与 S.A
或 T.A
相匹配的值。
特殊情况:如果 S = ∅
如果 S
表为空(S = f
),那么笛卡尔积 R x S x T
的结果会是空集。这是因为空表与任何表的笛卡尔积都为无结果集。所以这个案例的结果也会为空
9. SQL 中的 NULL 值与三值逻辑
在 SQL 中,NULL
代表一个 未知 或 缺失 的值。它并不是一个空字符串或数字 0,而是一个特殊的值,表示数据不存在或不可用。SQL 使用三值逻辑(True、False、Unknown)来处理包含 NULL
值的表达式和条件。
1. NULL 与比较操作
任何与 NULL
进行的比较都会返回 Unknown
(未知),这是 SQL 三值逻辑的关键点之一。例如:
5 < NULL
会返回Unknown
NULL <> NULL
会返回Unknown
NULL = NULL
也会返回Unknown
NULL
不等同于自身,因为它表示未知,未知与未知比较无法确定结果。因此,与 NULL
进行的任何比较都会得到 Unknown
。
2. WHERE 子句中的 NULL 处理
在 SQL 的 WHERE
子句中,如果条件的结果是 Unknown
,SQL 会将其视为 False
,从而忽略该行。例如:
SELECT sid
FROM enrolled
WHERE grade = 'unknown';
假设有些学生还没有成绩(grade
为 NULL
)。在这个查询中,条件 grade = 'unknown'
对这些学生的 NULL
成绩进行比较会返回 Unknown
,所以查询会忽略这些学生,没有成绩的学生不会出现在结果中。
3. NULL 值的处理方法
-
IS NULL 和 IS NOT NULL:可以使用这些运算符来专门检查
NULL
值。例如,grade IS NULL
可以用于查找没有成绩的学生。SELECT sid FROM enrolled WHERE grade IS NULL;
-
三值逻辑总结:SQL 的三值逻辑包括:
- True:条件成立。
- False:条件不成立。
- Unknown:包含
NULL
时的结果。
这种逻辑在 WHERE
子句中非常重要,因为 Unknown
会导致该行被忽略。
10. SQL 聚合函数和分组查询 (Aggregate Functions and Grouping)
SQL 提供多种聚合函数,用于对数据进行统计和计算,如求和、计数、平均值等。聚合函数通常与 GROUP BY
和 HAVING
子句一起使用,以便将数据分组并对每个组执行计算。
1. SQL 聚合函数
聚合函数 | 作用 |
---|---|
COUNT(attr) | 统计非空属性 attr 的数量或使用 COUNT(*) 统计所有行 |
MIN(attr) | 返回 attr 的最小值 |
MAX(attr) | 返回 attr 的最大值 |
AVG(attr) | 计算 attr 的平均值 |
MODE() WITHIN GROUP (ORDER BY attr) | 计算 attr 的众数(最频繁值) |
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY attr) | 返回 attr 值的中位数 |
2. SQL 分组 (GROUP BY)
当我们想要针对不同的分组数据应用聚合函数时,可以使用 GROUP BY
子句。例如,假设我们有一个 Sales
表,我们想按公司名统计每个公司的销售总额:
SELECT Company, SUM(Amount)
FROM Sales
GROUP BY Company;
3. 过滤分组结果 (HAVING 子句)
- HAVING 子句 允许在分组后的结果上应用条件。与
WHERE
不同,HAVING
主要用于聚合后的条件。 - 例如,如果我们想查询平均分数超过 10 的学习单元,可以使用
HAVING
:
SELECT uos_code AS unit_of_study, AVG(mark)
FROM Assessment
GROUP BY uos_code
HAVING AVG(mark) > 10;
4. 示例:带有 GROUP BY 和 HAVING 的查询
假设我们想要找到平均分数超过 2 的学习单元中的 6 个学分的课程单元:
SELECT uos_code AS unit_of_study, AVG(mark)
FROM Assessment NATURAL JOIN UnitOfStudy
WHERE credit_points = 6
GROUP BY uos_code
HAVING COUNT(*) > 2;
在这个查询中:
WHERE
子句过滤出所有 6 学分的课程单元。GROUP BY
将结果按uos_code
(课程单元编号)分组。HAVING COUNT(*) > 2
确保只选择结果超过两项的课程单元。
11. **SQL 插入数据 (INSERT INTO) **
在 SQL 中,INSERT INTO
语句用于向表中插入新记录。常见的 INSERT INTO
语句格式如下:
INSERT INTO 表格名 (列名1, 列名2, …)
VALUES (值1, 值2, …);
INSERT INTO 的使用方法
1. 插入特定字段的值
可以选择性地向指定的字段插入数据,其他字段可以使用默认值。
INSERT INTO egg.eggs_record (id, egg_name, sold)
VALUES (1, '鸡蛋', '2020-02-02');
在此例中,将向 egg.eggs_record
表的 id
、egg_name
和 sold
列插入数据。
2. 插入多个列的值
在插入数据时,不一定要指定每个列。如果希望插入所有列的数据,可以直接省略列名:
INSERT INTO egg.eggs_record
VALUES (2, '鸭蛋', '2020-02-02');
注意:必须确保 VALUES
部分提供的值数量与表格列数一致。
3. 插入默认值
如果某些列有默认值,可以使用 DEFAULT
关键字,或将值设为 NULL
(前提是该字段允许 NULL
)。
INSERT INTO egg.eggs_record
VALUES (DEFAULT, '龟蛋', NULL);
在此例中,id
列会自动生成或使用默认值,sold
列插入 NULL
。
12. SQL 修改表结构和数据 (ALTER 和 UPDATE)
在 SQL 中,ALTER
语句用于修改表的结构(例如添加、删除列等),而 UPDATE
语句用于修改表中的具体数据。下面是这两个语句的使用教程。
1. ALTER TABLE 修改表结构
添加一列
使用 ALTER TABLE
和 ADD
关键字可以在已有表中添加新列。
语法:
ALTER TABLE 数据库名.表格名
ADD 列名 数据类型 [DEFAULT 默认值];
示例:
ALTER TABLE egg.eggs_record
ADD stock INT NULL;
该语句会在 egg.eggs_record
表中添加一个名为 stock
的 INT
类型列,且允许为空。
2. UPDATE 修改表中具体数据
修改数据
使用 UPDATE
语句可以更新表中某些特定行的值。需要指定更新的表名、要修改的值以及满足的条件(WHERE
子句)。
语法:
UPDATE 数据库名.表格名
SET 列名 = 新值
WHERE 条件;
示例:
UPDATE egg.eggs_record
SET sold = '2022-06-06'
WHERE id = 3;
在此示例中,将 egg.eggs_record
表中 id
为 3 的记录的 sold
列更新为 '2022-06-06'
。
注意事项
ALTER TABLE
只能用来修改表结构,不能用来修改表中已有数据。UPDATE
语句的WHERE
条件非常重要,否则可能会误修改整张表的所有行。
13. SQL 删除操作 (DELETE 和 DROP)
在 SQL 中,删除操作分为删除表中的数据和删除整个表或数据库。DELETE
语句用于删除表中满足条件的特定数据,而 DROP
语句用于删除表或整个数据库。
1. 删除数据:DELETE FROM
DELETE FROM
语句用于删除表中的特定行,可以通过 WHERE
子句指定条件,以控制删除哪些记录。
语法:
DELETE FROM 数据库名.表格名
WHERE 条件;
示例:
DELETE FROM egg.eggs_record
WHERE id = 3;
此语句会删除 egg.eggs_record
表中 id
为 3 的记录。
注意:
WHERE
子句在DELETE
中非常重要,如果省略WHERE
,将会删除表中的所有数据。
2. 删除表:DROP TABLE
DROP TABLE
语句用于删除整个表以及其中的所有数据。删除表后,表结构和数据都会永久丢失,无法恢复。
语法:
DROP TABLE 数据库名.表格名;
示例:
DROP TABLE egg.eggs_record;
此语句会删除 egg
数据库中的 eggs_record
表,包括其中的所有数据和结构。
3. 删除数据库:DROP DATABASE
DROP DATABASE
语句用于删除整个数据库,包括其中的所有表和数据。删除数据库是一个不可逆的操作,请小心使用。
语法:
DROP DATABASE 数据库名;
示例:
DROP DATABASE egg;
此语句会删除 egg
数据库以及其中的所有表和数据。
注意事项
DELETE FROM
用于删除表中部分数据;DROP TABLE
和DROP DATABASE
则会永久删除表或数据库本身。DELETE FROM
需要WHERE
子句以避免删除所有数据;DROP
操作会彻底删除表或数据库,无法恢复。
14. 顺序
- SELECT
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- (SELECT开始读取)
- ORDER BY
- LIMIT
15. Null
Null 如果影响最后的结果,那么输出是UNKNOWN