超级会员免费看
SQL 数据库操作全解析
1. SQL 简介
SQL（Structured Query Language）即结构化查询语言，由 IBM 在 20 世纪 70 年代发明，用于访问关系型数据库中的数据。从 Java 程序员的角度来看，只要可以使用 SQL 访问的数据库就是关系型数据库。
SQL 是一种查询语言，旨在提取、组织和更新关系型数据库中的信息。最初，它被设想为一种类似英语的查询语言，让未经训练的终端用户无需编程就能访问和更新关系型数据库数据，但实际上它过于复杂，普通用户难以掌握，不过却深受程序员喜爱。
关于 SQL 的发音，有两种常见方式：
- 逐个字母拼读：Es – Que – El。
- 发音类似“sequel”。后者会让你听起来不像数据库新手。
2. SQL 语句
与 Java 不同，SQL 不是面向对象的语言，但和 Java 一样，它通过语句来完成任务。常见的 SQL 语句如下表所示：
| SQL 语句 | 描述 |
| — | — |
|
数据操作
| |
| select | 从一个或多个表中检索数据，是最常用的语句。 |
| insert | 向表中插入一行或多行数据。 |
| delete | 从表中删除一行或多行数据。 |
| update | 更新表中现有的行。 |
|
数据定义
| |
| create | 创建表和其他数据库对象。 |
| alter | 更改表或其他数据库对象的定义。 |
| drop | 删除表或其他数据库对象。 |
| use | 在脚本中用于指定后续语句应用的数据库。 |
需要注意的是，SQL 语句不区分大小写，例如“select”“Select”“SELECT”甚至“sElEcT”都是等效的。
3. 创建 SQL 数据库
在将数据存储到关系型数据库之前，需要先创建数据库。通常不通过 Java 程序来创建，而是编写包含创建表所需的 Create 语句的脚本文件，然后通过数据库服务器的管理程序运行该脚本。
以下是一个创建名为“movies”的数据库的示例脚本：
drop database movies;
create database movies;
use movies;
create table movie (
id int not null auto_increment,
title varchar(50),
year int,
price decimal(8,2),
primary key(id)
);
insert into movie (title, year, price)
values ("It's a Wonderful Life", 1946, 14.95);
insert into movie (title, year, price)
values ("The Great Race", 1965, 12.95);
insert into movie (title, year, price)
values ("Young Frankenstein", 1974, 16.95);
insert into movie (title, year, price)
values ("The Return of the Pink Panther", 1975, 11.95);
insert into movie (title, year, price)
values ("Star Wars", 1977, 17.95);
insert into movie (title, year, price)
values ("The Princess Bride", 1987, 16.95);
insert into movie (title, year, price)
values ("Glory", 1989, 14.95);
insert into movie (title, year, price)
values ("Apollo 13", 1995, 19.95);
insert into movie (title, year, price)
values ("The Game", 1997, 14.95);
insert into movie (title, year, price)
values ("The Lord of the Rings: The Fellowship of the Ring", 2001, 19.95);
该脚本的重要行解释如下：
1.
drop database movies;
：删除任何同名的现有数据库，在测试时经常需要删除和重新创建数据库，所以脚本中通常包含此语句。
2.
create database movies;
：创建一个名为“movies”的新数据库。
3.
use movies;
：指定后续脚本语句应用于新创建的“movies”数据库。
4.
create table movie (...)
：创建一个名为“movie”的表，包含“id”“title”“year”和“price”列，并指定“id”列为表的主键。
5.
id int not null auto_increment
：“id”列的数据类型为 int，对应 Java 的 int 类型，“not null”表示该列每行必须有值，“auto_increment”表示数据库服务器会自动为该列提供值，每次添加新行时，“id”列的值会自动递增。
6.
title varchar(50)
：“title”列的数据类型为 varchar，类似于 Java 的 String。
7.
year int
：“year”列的数据类型为 int。
8.
price decimal(8,2)
：“price”列的数据类型为 decimal，Java 没有 decimal 类型，该列的值会转换为 double。
9.
primary key(id)
：指定“id”列为表的主键，主键是表中每行包含唯一值的列（或列的组合），每个表都应该有主键。
10.
insert into movie (...)
：向数据库中添加数据，每个 insert 语句向“movie”表中添加一行。
要在 MySQL 中运行此脚本，可从开始菜单启动 MySQL Command Line Client，然后使用 source 命令指定脚本名称，例如：
mysql> source c:\data\create.sql
4. 查询数据库
查询是 SQL 的核心功能。查询是对一个或多个 SQL 表执行的操作，用于从表中提取数据并创建包含所选行和列的结果集。结果集本身也是一个由行和列组成的表，从 Java 程序查询数据库时，结果集会以 ResultSet 类的对象形式返回给程序，该类有方法可提取结果集中每行每列的数据。
4.1 使用基本的 select 语句
查询数据库使用 select 语句，在该语句中，需要列出要从中检索数据的表、要检索的具体表列，以及其他指定检索哪些行、以什么顺序呈现行等的子句。
例如，以下简单的 select 语句列出“movie”表中的所有电影：
select title, year
from movie
order by year;
该语句的各部分解释如下：
-
select title, year
：指定查询结果中要包含的列。
-
from movie
：指定要从哪个表中检索行。
-
order by year
：表示结果按“year”列排序，最古老的电影排在前面。
在 MySQL Command Line Client 中运行该查询，结果如下：
mysql> select title, year from movie order by year;
+---------------------------------------------------+------+
| title | year |
+---------------------------------------------------+------+
| It's a Wonderful Life | 1946 |
| The Great Race | 1965 |
| Young Frankenstein | 1974 |
| The Return of the Pink Panther | 1975 |
| Star Wars | 1977 |
| The Princess Bride | 1987 |
| Glory | 1989 |
| Apollo 13 | 1995 |
| The Game | 1997 |
| The Lord of the Rings: The Fellowship of the Ring | 2001 |
+---------------------------------------------------+------+
10 rows in set (0.09 sec)
如果想检索表中每行的所有列，可以使用星号（*）代替具体列名，但在程序中这样做不是一个好主意，因为表的列结构可能会改变，使用星号会使程序无法处理这种变化。
在 SQL 数据库中，表中存储的行不假定有特定顺序，因此如果要按顺序显示查询结果，必须在 select 语句中包含 order by 子句。
4.2 缩小查询范围
如果想查找特定视频标题的信息，可以在 select 语句中使用 where 子句选择特定的行。例如：
mysql> select title, year from movie
-> where year <= 1980
-> order by year;
+--------------------------------+------+
| title | year |
+--------------------------------+------+
| It's a Wonderful Life | 1946 |
| The Great Race | 1965 |
| Young Frankenstein | 1974 |
| The Return of the Pink Panther | 1975 |
| Star Wars | 1977 |
+--------------------------------+------+
5 rows in set (0.00 sec)
该 select 语句选择“year”列小于或等于 1980 的所有行，并按“year”列排序结果。
4.3 排除行
如果想检索除符合某些条件之外的所有行，可以使用 where 子句排除特定行。例如，以下查询忽略 20 世纪 70 年代制作的电影：
mysql> select title, year from movie
-> where year < 1970 or year > 1979
-> order by year;
+---------------------------------------------------+------+
| title | year |
+---------------------------------------------------+------+
| It's a Wonderful Life | 1946 |
| The Great Race | 1965 |
| The Princess Bride | 1987 |
| Glory | 1989 |
| Apollo 13 | 1995 |
| The Game | 1997 |
| The Lord of the Rings: The Fellowship of the Ring | 2001 |
+---------------------------------------------------+------+
7 rows in set (0.41 sec)
4.4 单例查询
当想检索特定行的信息时，可以在 where 子句中指定主键列。例如：
mysql> select title, year from movie where id = 7;
+-------+------+
| title | year |
+-------+------+
| Glory | 1989 |
+-------+------+
1 row in set (0.49 sec)
这种类型的 select 语句称为单例查询，因为它只检索一行，在 Java 程序中常用于让用户访问或更新特定数据库行。
4.5 使用 like 关键字
如果想检索关于某部电影的信息，但记不清具体名称，只知道包含某个关键词，可以使用 where 子句中的 like 关键字进行模糊查询。例如，使用百分号（%）作为通配符：
mysql> select title, year from movie
-> where title like "%princess%";
+--------------------+------+
| title | year |
+--------------------+------+
| The Princess Bride | 1987 |
+--------------------+------+
1 row in set (0.00 sec)
4.6 列函数
如果想统计“movie”表中电影的总数，或统计 1970 年之前制作的电影数量，可以使用列函数。SQL 的列函数可对列进行计算，如计算总和、平均值、最大值、最小值或统计列中的值的数量。常见的列函数如下表所示：
| 函数 | 描述 |
| — | — |
| sum(column - name) | 计算列中值的总和。 |
| avg(column - name) | 计算列的平均值，计算时不考虑 null 值。 |
| min(column - name) | 确定列中的最小值。 |
| max(column - name) | 确定列中的最大值。 |
| count(column - name) | 统计列中有数据值的行数。 |
| countDistinct(column - name) | 统计列中不同值的数量。 |
| count(*) | 统计结果集中的行数。 |
例如，以下 select 语句计算表中的行数和最古老电影的年份：
mysql> select count(*), min(year) from movie;
+----------+-----------+
| count(*) | min(year) |
+----------+-----------+
| 10 | 1946 |
+----------+-----------+
1 row in set (0.00 sec)
如果 select 语句包含 where 子句，只有符合条件的行才会参与计算。例如，统计 1970 年之前制作的电影数量：
mysql> select count(*) from movie where year < 1970;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
4.7 从多个表中选择
在实际应用中，大多数 select 语句会从两个或多个表中检索数据。例如，要列出当前借给朋友的所有电影，需要在数据库中创建另一个表，列出朋友的姓名和他们借的电影的 id。
以下是创建“friend”表的语句：
create table friend (
lastname varchar(50),
firstname varchar(50),
movieid int
);
向“friend”表中添加数据：
insert into friend (lastname, firstname, movieid)
values ("Haskell", "Eddie", 3);
insert into friend (lastname, firstname, movieid)
values ("Haskell", "Eddie", 5);
insert into friend (lastname, firstname, movieid)
values ("Cleaver", "Wally", 9);
insert into friend (lastname, firstname, movieid)
values ("Mondello", "Lumpy", 2);
insert into friend (lastname, firstname, movieid)
values ("Cleaver", "Wally", 3);
要同时使用“friend”和“movie”表进行查询，只需在 from 子句中列出两个表，并在 where 子句中提供关联表的条件。例如：
mysql> select lastname, firstname, title
-> from movie, friend
-> where movie.id = friend.movieid;
+----------+-----------+--------------------+
| lastname | firstname | title |
+----------+-----------+--------------------+
| Haskell | Eddie | Young Frankenstein |
| Haskell | Eddie | Star Wars |
| Cleaver | Wally | The Game |
| Mondello | Lumpy | The Great Race |
| Cleaver | Wally | Young Frankenstein |
+----------+-----------+--------------------+
5 rows in set (0.00 sec)
要列出 Eddie Haskell 借的所有电影：
mysql> select title from movie, friend
-> where movie.id = friend.movieid
-> and lastname = "Haskell";
+--------------------+
| title |
+--------------------+
| Young Frankenstein |
| Star Wars |
+--------------------+
2 rows in set (0.00 sec)
4.8 消除重复行
如果只想知道借了电影的所有人的姓名，可以简单地从“friend”表中进行查询：
mysql> select lastname, firstname from friend;
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| Haskell | Eddie |
| Haskell | Eddie |
| Cleaver | Wally |
| Mondello | Lumpy |
| Cleaver | Wally |
+----------+-----------+
5 rows in set (0.00 sec)
但这个结果集存在问题，Eddie Haskel 和 Wally Cleaver 被列出了两次。可以在 select 语句中添加 distinct 关键字来消除重复行：
mysql> select distinct lastname, firstname from friend;
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| Haskell | Eddie |
| Cleaver | Wally |
| Mondello | Lumpy |
+----------+-----------+
3 rows in set (0.07 sec)
5. 更新和删除行
前面已经介绍了如何创建数据库、插入行和检索结果集，接下来介绍如何更新和删除表中的数据，这需要使用 update 和 delete 语句。
5.1 删除语句
delete 语句的基本语法如下：
delete from table - name where condition;
例如，删除“id”为 10 的电影：
mysql> delete from movie where id = 10;
Query OK, 1 row affected (0.44 sec)
可以通过后续的 select 语句确认电影已被删除：
mysql> select * from movie;
+----+--------------------------------+------+-------+
| id | title | year | price |
+----+--------------------------------+------+-------+
| 1 | It's a Wonderful Life | 1946 | 14.95 |
| 2 | The Great Race | 1965 | 12.95 |
| 3 | Young Frankenstein | 1974 | 16.95 |
| 4 | The Return of the Pink Panther | 1975 | 11.95 |
| 5 | Star Wars | 1977 | 17.95 |
| 6 | The Princess Bride | 1987 | 16.95 |
| 7 | Glory | 1989 | 14.95 |
| 8 | Apollo 13 | 1995 | 19.95 |
| 9 | The Game | 1997 | 14.95 |
+----+--------------------------------+------+-------+
9 rows in set (0.00 sec)
如果 where 子句选择了多行，所有选中的行都会被删除。例如，删除所有姓“Haskell”的记录：
mysql> delete from friend where lastname = "Haskell";
Query OK, 2 rows affected (0.45 sec)
如果不包含 where 子句，整个表都会被删除。例如，删除“movie”表中的所有行：
mysql> delete from movie;
Query OK, 9 rows affected (0.44 sec)
可以通过 select 语句确认表已为空：
mysql> select * from movie;
Empty set (0.00 sec)
此时，可以重新运行 create.sql 脚本来重新创建表。
5.2 更新语句
update 语句用于选择表中的一行或多行，并修改所选行中一个或多个列的值。其语法如下：
update table - name
set expressions...
where condition;
例如，将“id”为 8 的电影的价格改为 18.95：
mysql> update movie set price = 18.95 where id = 8;
Query OK, 1 row affected (0.44 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以使用 select 语句验证价格是否已更改：
mysql> select id, price from movie;
+----+-------+
| id | price |
+----+-------+
| 1 | 14.95 |
| 2 | 12.95 |
| 3 | 16.95 |
| 4 | 11.95 |
| 5 | 17.95 |
| 6 | 16.95 |
| 7 | 14.95 |
| 8 | 18.95 |
| 9 | 14.95 |
| 10 | 19.95 |
+----+-------+
10 rows in set (0.01 sec)
要更新多个列，可以用逗号分隔表达式。例如，更改“friend”表中 Eddie Haskell 的姓名：
mysql> update friend set lastname = "Bully",
-> firstname = "Big"
-> where lastname = "Haskell";
Query OK, 2 rows affected (0.46 sec)
Rows matched: 2 Changed: 2 Warnings: 0
通过 select 语句确认行已正确更新：
mysql> select firstname, lastname from friend;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Big | Bully |
| Big | Bully |
| Wally | Cleaver |
| Lumpy | Mondello |
| Wally | Cleaver |
+-----------+----------+
5 rows in set (0.00 sec)
update 语句的 set 表达式还可以包含计算。例如，将所有电影的价格提高 10%：
mysql> update movie set price = price * 1.1;
Query OK, 10 rows affected (0.46 sec)
Rows matched: 10 Changed: 10 Warnings: 0
使用 select 语句验证更新是否成功：
mysql> select id, price from movie;
+----+-------+
| id | price |
+----+-------+
| 1 | 16.45 |
| 2 | 14.25 |
| 3 | 18.65 |
| 4 | 13.15 |
| 5 | 19.75 |
| 6 | 18.65 |
| 7 | 16.45 |
| 8 | 20.85 |
| 9 | 16.45 |
| 10 | 21.95 |
+----+-------+
10 rows in set (0.01 sec)
综上所述，SQL 提供了丰富的功能来创建、查询、更新和删除数据库中的数据，掌握这些操作对于管理和处理关系型数据库至关重要。通过合理运用各种 SQL 语句和技巧，可以高效地完成数据库的各种任务。
6. SQL 操作流程总结
为了更清晰地理解 SQL 的各种操作，我们可以通过以下 mermaid 流程图来展示从创建数据库到查询、更新和删除数据的整个流程：
graph LR
classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px
A([开始]):::startend --> B(创建数据库):::process
B --> C(创建表):::process
C --> D(插入数据):::process
D --> E{是否需要查询数据?}:::decision
E -- 是 --> F(使用 select 语句查询):::process
F --> G{是否需要更新数据?}:::decision
E -- 否 --> G
G -- 是 --> H(使用 update 语句更新):::process
H --> I{是否需要删除数据?}:::decision
G -- 否 --> I
I -- 是 --> J(使用 delete 语句删除):::process
I -- 否 --> K([结束]):::startend
J --> K
从这个流程图可以看出，整个 SQL 操作过程是一个循序渐进的过程，每个步骤都依赖于前面的步骤。例如，在查询数据之前，需要先创建数据库和表，并插入数据。
7. SQL 操作注意事项
7.1 主键的重要性
在创建表时，每个表都应该有一个主键。主键是表中每行包含唯一值的列（或列的组合），它的作用是确保表中每行数据的唯一性，便于快速定位和操作特定的行。例如，在“movie”表中，“id”列被指定为主键，这使得我们可以通过“id”快速找到特定的电影记录。如果没有主键，在进行单例查询或更新、删除特定行时会变得非常困难。
7.2 数据类型的匹配
在创建表时，需要注意数据类型的选择和匹配。不同的数据库系统可能对数据类型有不同的实现，但基本的原则是要确保数据类型与存储的数据相匹配。例如，“title”列使用 varchar 类型来存储字符串，“year”列使用 int 类型来存储整数。同时，要注意不同编程语言与数据库数据类型的转换，如 Java 没有 decimal 类型，“price”列的 decimal 类型值会转换为 double 类型。
7.3 脚本的备份和测试
在进行数据库操作时，特别是删除和更新操作，一定要谨慎。在删除整个表或大量数据之前，最好先备份数据，以防误操作导致数据丢失。同时，在测试环境中进行充分的测试，确保脚本的正确性和稳定性。例如，在运行删除表或数据库的脚本之前，先在测试数据库中运行，观察结果是否符合预期。
7.4 性能优化
在进行复杂查询时，特别是从多个表中查询数据时，要注意性能问题。可以通过创建索引、优化查询语句等方式来提高查询性能。例如，在经常用于查询条件的列上创建索引，可以加快查询速度。另外，合理使用列函数和避免不必要的子查询也可以提高性能。
8. SQL 常见错误及解决方法
8.1 语法错误
SQL 语句对语法要求非常严格，一个小的语法错误可能导致整个语句无法执行。常见的语法错误包括拼写错误、缺少关键字、括号不匹配等。例如，将“select”写成“selec”，或者忘记在语句末尾添加分号。解决方法是仔细检查语句，使用数据库管理工具的语法检查功能，或者参考 SQL 语法文档。
8.2 数据类型不匹配错误
当插入或更新数据时，如果提供的数据类型与表中列的数据类型不匹配，会导致数据类型不匹配错误。例如，将字符串插入到 int 类型的列中。解决方法是确保提供的数据类型与列的数据类型一致，或者进行必要的类型转换。
8.3 主键冲突错误
在插入数据时，如果插入的主键值已经存在于表中，会导致主键冲突错误。解决方法是确保插入的主键值是唯一的，或者使用自动递增的主键。
8.4 权限不足错误
如果用户没有足够的权限执行某些 SQL 操作，会出现权限不足错误。例如，尝试删除数据库或表，但用户没有相应的删除权限。解决方法是联系数据库管理员，请求授予相应的权限。
9. 总结
通过前面的介绍，我们全面了解了 SQL 的基本概念、常见语句和操作方法。SQL 作为一种强大的数据库查询和操作语言，在关系型数据库管理中起着至关重要的作用。以下是对 SQL 操作的关键要点总结：
-
创建数据库和表
：使用 create database 和 create table 语句来创建数据库和表，注意指定合适的数据类型和主键。
-
插入数据
：使用 insert into 语句向表中插入数据。
-
查询数据
：使用 select 语句进行各种查询操作，包括基本查询、缩小查询范围、排除行、单例查询、模糊查询等，还可以使用列函数进行计算。
-
更新数据
：使用 update 语句更新表中的数据，可以更新单个列或多个列，还可以进行计算更新。
-
删除数据
：使用 delete 语句删除表中的数据，可以删除特定行或整个表。
掌握这些 SQL 操作，可以帮助我们高效地管理和处理关系型数据库中的数据。同时，要注意操作过程中的注意事项和常见错误的解决方法，确保数据库操作的正确性和稳定性。
希望通过本文的介绍，你对 SQL 有了更深入的理解和掌握，能够在实际项目中灵活运用 SQL 进行数据库操作。
扫一扫
3132
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
