数据库系统 第6节 数据操作语言

2. 数据操作语言 (DML)

数据操作语言 (DML) 是一组用于操作数据库中数据的SQL命令。DML主要包括以下四种操作：

SELECT

定义: SELECT 语句用于从一个或多个表中检索数据。它是SQL中最常用的操作之一，可以用来提取满足特定条件的数据。

语法:

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC|DESC]];

• column1, column2, ...: 要从表中选择的列名。
• table_name: 包含所需数据的表的名称。
• [WHERE condition]: 可选子句，用于指定要选择哪些行。
• [ORDER BY column_name [ASC|DESC]]: 可选子句，用于按特定列对结果进行排序。

案例: 从 Books 表中选择所有价格超过10美元的书籍。

SELECT * FROM Books WHERE Price > 10;

INSERT

定义: INSERT 语句用于向表中插入新的记录。它可以插入一行或多行数据。

语法:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• table_name: 要插入数据的表的名称。
• (column1, column2, ...): 要插入数据的列名。
• (value1, value2, ...): 对应列的值。

案例: 向 Books 表中插入一本新书。

INSERT INTO Books (BookID, Title, Author, Publisher, PublicationDate, Price)
VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Charles Scribner''s Sons', '1925-04-10', 12.99);

UPDATE

定义: UPDATE 语句用于修改表中的现有记录。它允许更改一个或多个列的值。

语法:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: 包含要更新的记录的表的名称。
• SET column1 = value1, column2 = value2, ...: 要更新的列名及其新值。
• WHERE condition: 指定要更新哪些行的条件。

案例: 更新 Books 表中书籍的价格，将价格低于10美元的书籍价格调整为10美元。

UPDATE Books
SET Price = 10
WHERE Price < 10;

DELETE

定义: DELETE 语句用于删除表中的记录。它可以删除一行或多行数据。

语法:

DELETE FROM table_name
WHERE condition;

• table_name: 包含要删除的记录的表的名称。
• WHERE condition: 指定要删除哪些行的条件。

案例: 从 Books 表中删除价格低于5美元的所有书籍。

DELETE FROM Books
WHERE Price < 5;

示例结果

SELECT 示例

• 选择所有价格超过10美元的书籍

SELECT * FROM Books WHERE Price > 10;

BookID	Title	Author	Publisher	PublicationDate	Price
1	The Catcher in the Rye	J.D. Salinger	Little, Brown and Co.	1951-07-16	10.99
2	To Kill a Mockingbird	Harper Lee	J.B. Lippincott & Co.	1960-07-11	9.99

INSERT 示例

• 向 Books 表中插入一本新书

INSERT INTO Books (BookID, Title, Author, Publisher, PublicationDate, Price)
VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Charles Scribner''s Sons', '1925-04-10', 12.99);

UPDATE 示例

• 更新 Books 表中书籍的价格

UPDATE Books
SET Price = 10
WHERE Price < 10;

DELETE 示例

• 从 Books 表中删除价格低于5美元的所有书籍

DELETE FROM Books
WHERE Price < 5;

总结

通过这些基本的DML操作，你可以有效地管理和操作数据库中的数据。SELECT 语句用于检索数据，INSERT 语句用于插入新数据，UPDATE 语句用于更新现有数据，而 DELETE 语句用于删除不需要的数据。这些操作构成了数据库管理的基础，也是开发人员在日常工作中最常用的SQL语句之一。

如果你需要更深入的解释或者有其他具体的需求，请随时告诉我！

这次我们将创建一个简单的在线健身俱乐部会员管理系统。

案例：在线健身俱乐部会员管理系统

实体和属性

• 会员 (Members)

  • 会员ID (MemberID)
  • 姓名 (Name)
  • 年龄 (Age)
  • 会籍类型 (MembershipType)
  • 加入日期 (JoinDate)

• 会籍类型 (MembershipTypes)

  • 类型ID (TypeID)
  • 类型名称 (TypeName)
  • 月费 (MonthlyFee)

• 支付记录 (Payments)

  • 支付ID (PaymentID)
  • 会员ID (MemberID)
  • 支付日期 (PaymentDate)
  • 金额 (Amount)

关系表 (Tables)

会员表 (Members)

MemberID	Name	Age	MembershipType	JoinDate
1	Alice	25	Basic	2024-01-01
2	Bob	32	Premium	2024-02-15
…	…	…	…	…

会籍类型表 (MembershipTypes)

TypeID	TypeName	MonthlyFee
1	Basic	50
2	Premium	100
…	…	…

支付记录表 (Payments)

PaymentID	MemberID	PaymentDate	Amount
101	1	2024-01-01	50
102	2	2024-02-15	100
…	…	…	…

数据操作语言 (DML)

SELECT

• 选择所有年龄大于30岁的会员

SELECT * FROM Members WHERE Age > 30;

INSERT

• 向 Members 表中插入一位新会员

INSERT INTO Members (MemberID, Name, Age, MembershipType, JoinDate)
VALUES (3, 'Charlie', 28, 'Basic', '2024-03-01');

UPDATE

• 更新 Members 表中会员的会籍类型

UPDATE Members
SET MembershipType = 'Premium'
WHERE MemberID = 1;

DELETE

• 从 Members 表中删除年龄小于20岁的会员

DELETE FROM Members
WHERE Age < 20;

示例结果

SELECT 示例

• 选择所有年龄大于30岁的会员

SELECT * FROM Members WHERE Age > 30;

MemberID	Name	Age	MembershipType	JoinDate
2	Bob	32	Premium	2024-02-15

INSERT 示例

• 向 Members 表中插入一位新会员

INSERT INTO Members (MemberID, Name, Age, MembershipType, JoinDate)
VALUES (3, 'Charlie', 28, 'Basic', '2024-03-01');

UPDATE 示例

• 更新 Members 表中会员的会籍类型

UPDATE Members
SET MembershipType = 'Premium'
WHERE MemberID = 1;

DELETE 示例

• 从 Members 表中删除年龄小于20岁的会员

DELETE FROM Members
WHERE Age < 20;

SQL 查询示例

1. 选择所有年龄大于30岁的会员

SELECT * FROM Members WHERE Age > 30;

2. 向 Members 表中插入一位新会员

INSERT INTO Members (MemberID, Name, Age, MembershipType, JoinDate)
VALUES (3, 'Charlie', 28, 'Basic', '2024-03-01');

3. 更新 Members 表中会员的会籍类型

UPDATE Members
SET MembershipType = 'Premium'
WHERE MemberID = 1;

4. 从 Members 表中删除年龄小于20岁的会员

DELETE FROM Members
WHERE Age < 20;

5. 计算每个会籍类型的会员人数

SELECT MembershipType, COUNT(*) AS NumberOfMembers
FROM Members
GROUP BY MembershipType;

示例结果

1. 选择所有年龄大于30岁的会员

MemberID	Name	Age	MembershipType	JoinDate
2	Bob	32	Premium	2024-02-15

2. 向 Members 表中插入一位新会员

• 插入后的新会员信息如下：

MemberID	Name	Age	MembershipType	JoinDate
3	Charlie	28	Basic	2024-03-01

3. 更新 Members 表中会员的会籍类型

• 更新后的会员信息如下：

MemberID	Name	Age	MembershipType	JoinDate
1	Alice	25	Premium	2024-01-01

4. 从 Members 表中删除年龄小于20岁的会员

• 删除后的会员表如下：

MemberID	Name	Age	MembershipType	JoinDate
1	Alice	25	Premium	2024-01-01
2	Bob	32	Premium	2024-02-15
3	Charlie	28	Basic	2024-03-01

5. 计算每个会籍类型的会员人数

MembershipType	NumberOfMembers
Premium	2
Basic	1

通过这些概念和查询，我们可以看到如何利用数据操作语言 (DML) 来组织和操作数据。如果你需要更深入的解释或者有其他具体的需求，请随时告诉我！

当然，我们可以构建一个新的案例来进一步说明数据操作语言 (DML) 的使用。这次我们将创建一个简单的在线餐厅预订系统。

案例：在线餐厅预订系统

实体和属性

• 餐厅 (Restaurants)

  • 餐厅ID (RestaurantID)
  • 名称 (Name)
  • 地址 (Address)
  • 电话 (Phone)

• 餐桌 (Tables)

  • 餐桌ID (TableID)
  • 餐厅ID (RestaurantID)
  • 容纳人数 (Capacity)
  • 是否吸烟区 (IsSmokingArea)

• 预订 (Reservations)

  • 预订ID (ReservationID)
  • 餐桌ID (TableID)
  • 预订人姓名 (CustomerName)
  • 预订日期时间 (ReservationDateTime)
  • 人数 (NumberOfPeople)

关系表 (Tables)

餐厅表 (Restaurants)

RestaurantID	Name	Address	Phone
1	Italiano	123 Pasta Blvd, Cityville	(123) 456-7890
2	Sushi Palace	456 Sushi Ave, Townsville	(987) 654-3210
…	…	…	…

餐桌表 (Tables)

TableID	RestaurantID	Capacity	IsSmokingArea
101	1	2	No
102	1	4	Yes
201	2	2	No
202	2	4	Yes
…	…	…	…

预订表 (Reservations)

ReservationID	TableID	CustomerName	ReservationDateTime	NumberOfPeople
3001	101	John Doe	2024-08-10 19:00	2
3002	201	Jane Doe	2024-08-10 20:00	2
…	…	…	…	…

数据操作语言 (DML)

SELECT

• 选择所有容纳人数为2的餐桌

SELECT * FROM Tables WHERE Capacity = 2;

INSERT

• 向 Tables 表中插入一张新餐桌

INSERT INTO Tables (TableID, RestaurantID, Capacity, IsSmokingArea)
VALUES (103, 1, 4, 'No');

UPDATE

• 更新 Tables 表中餐桌的容纳人数

UPDATE Tables
SET Capacity = 3
WHERE TableID = 101;

DELETE

• 从 Tables 表中删除容纳人数为4且位于吸烟区的餐桌

DELETE FROM Tables
WHERE Capacity = 4 AND IsSmokingArea = 'Yes';

示例结果

SELECT 示例

• 选择所有容纳人数为2的餐桌

SELECT * FROM Tables WHERE Capacity = 2;

TableID	RestaurantID	Capacity	IsSmokingArea
101	1	2	No
201	2	2	No

INSERT 示例

• 向 Tables 表中插入一张新餐桌

INSERT INTO Tables (TableID, RestaurantID, Capacity, IsSmokingArea)
VALUES (103, 1, 4, 'No');

UPDATE 示例

• 更新 Tables 表中餐桌的容纳人数

UPDATE Tables
SET Capacity = 3
WHERE TableID = 101;

DELETE 示例

• 从 Tables 表中删除容纳人数为4且位于吸烟区的餐桌

DELETE FROM Tables
WHERE Capacity = 4 AND IsSmokingArea = 'Yes';

SQL 查询示例

1. 选择所有容纳人数为2的餐桌

SELECT * FROM Tables WHERE Capacity = 2;

2. 向 Tables 表中插入一张新餐桌

INSERT INTO Tables (TableID, RestaurantID, Capacity, IsSmokingArea)
VALUES (103, 1, 4, 'No');

3. 更新 Tables 表中餐桌的容纳人数

UPDATE Tables
SET Capacity = 3
WHERE TableID = 101;

4. 从 Tables 表中删除容纳人数为4且位于吸烟区的餐桌

DELETE FROM Tables
WHERE Capacity = 4 AND IsSmokingArea = 'Yes';

5. 列出所有预订日期为2024-08-10的预订

SELECT R.Name, T.Capacity, Res.CustomerName, Res.ReservationDateTime
FROM Reservations Res
JOIN Tables T ON Res.TableID = T.TableID
JOIN Restaurants R ON T.RestaurantID = R.RestaurantID
WHERE Res.ReservationDateTime >= '2024-08-10' AND Res.ReservationDateTime < '2024-08-11';

示例结果

1. 选择所有容纳人数为2的餐桌

TableID	RestaurantID	Capacity	IsSmokingArea
101	1	2	No
201	2	2	No

2. 向 Tables 表中插入一张新餐桌

• 插入后的新餐桌信息如下：

TableID	RestaurantID	Capacity	IsSmokingArea
103	1	4	No

3. 更新 Tables 表中餐桌的容纳人数

• 更新后的餐桌信息如下：

TableID	RestaurantID	Capacity	IsSmokingArea
101	1	3	No

4. 从 Tables 表中删除容纳人数为4且位于吸烟区的餐桌

• 删除后的餐桌表如下：

TableID	RestaurantID	Capacity	IsSmokingArea
101	1	3	No
103	1	4	No
201	2	2	No

5. 列出所有预订日期为2024-08-10的预订

Name	Capacity	CustomerName	ReservationDateTime
Italiano	3	John Doe	2024-08-10 19:00
Sushi Palace	2	Jane Doe	2024-08-10 20:00

通过这些概念和查询，我们可以看到如何利用数据操作语言 (DML) 来组织和操作数据。如果你需要更深入的解释或者有其他具体的需求，请随时告诉我！