Derby 中的 SQL 查询
最后修改于 2020 年 7 月 6 日
在本章中，我们将使用 Derby 数据库引擎理解的 SQL。 这是一个 Derby 中存在的最重要的 SQL 语句的快速列表。
SQL（结构化查询语言） 是一种数据库计算机语言，用于管理关系数据库管理系统中的数据。 Derby 仅支持有限的一组 SQL 语句。 其他数据库系统中存在的一些重要语句缺失。 Derby 实现了一个 SQL-92 核心子集，以及一些 SQL-99 功能。
删除表
DROP TABLE 语句从数据库中删除一个表。
ij> DROP TABLE AUTHORS; 0 rows inserted/updated/deleted ij> DROP TABLE BOOKS; 0 rows inserted/updated/deleted
假设我们之前创建了 AUTHORS 和 BOOKS 表，我们将删除它们并重新创建。 DROP TABLE SQL 语句从数据库中删除表。 请注意， Derby 中不存在 DROP TABLE IF EXISTS 语句。
创建表
CREATE TABLE 语句创建一个新表。
ij> CREATE TABLE AUTHORS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(25)); 0 rows inserted/updated/deleted
我们创建一个 AUTHORS 表，其中包含两列：ID 和 NAME。 在 ID 列中，我们将放置大整数，在 NAME 列中，我们将放置长度不超过 25 个字符的字符串。 PRIMARY KEY 唯一标识表中的每条记录。 每个作者都是一个独特的个体。 即使有同名的作者，他们每个人也在 AUTHORS 表中的单独行中。 一个表中只能有一个列具有此约束。
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) 创建一个标识列。 标识列是一个列，它存储在每次插入时递增 1 的数字。 标识列有时被称为自动递增列。
ij> CREATE TABLE BOOKS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), AUTHOR_ID BIGINT, TITLE VARCHAR(150), > FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID)); 0 rows inserted/updated/deleted
我们创建一个 BOOKS 表，其中包含三列。 FOREIGN KEY 指定 AUTHOR_ID 列中的值必须与 AUTHORS 表的 ID 列中的值匹配。 外键提供了一种强制执行数据库的引用完整性的方法。 每本书都由一个或多个作者撰写。 因此，在 BOOKS 表的 AUTHOR_ID 列中，我们只能使用 AUTHORS 表中存在的值。
插入行
INSERT 语句用于在数据库表中创建一行或多行。
ij> INSERT INTO AUTHORS(NAME) VALUES('Jack London');
ij> INSERT INTO AUTHORS(NAME) VALUES('Honore de Balzac');
ij> INSERT INTO AUTHORS(NAME) VALUES('Lion Feuchtwanger');
ij> INSERT INTO AUTHORS(NAME) VALUES('Emile Zola');
ij> INSERT INTO AUTHORS(NAME) VALUES('Truman Capote');
我们使用 INSERT INTO SQL 语句向 AUTHORS 表中添加了五行。
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Call of the Wild'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Martin Eden'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Old Goriot'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Cousin Bette'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(3, 'Jew Suess'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'Nana'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'The Belly of Paris'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'In Cold blood'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'Breakfast at Tiffany');
我们向 BOOKS 表中插入了八行。
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS > WHERE AUTHORS.ID = BOOKS.AUTHOR_ID; NAME |TITLE ------------------------------------------------- Jack London |Call of the Wild Jack London |Martin Eden Honore de Balzac |Old Goriot Honore de Balzac |Cousin Bette Lion Feuchtwanger |Jew Suess Emile Zola |Nana Emile Zola |The Belly of Paris Truman Capote |In Cold blood Truman Capote |Breakfast at Tiffany 9 rows selected
上面的 SQL 查询连接了这两个表。 它将每个书名分配给一个作者。
查询
查询用于从数据库表中查找数据。 SELECT 语句是执行查询的主要语句。
限制数据输出
限制数据输出至关重要，因为许多数据库有成千上万甚至数百万行。 Derby 不支持其他数据库中已知的 LIMIT 子句。 Derby 10.7 引入了 FETCH 和 OFFSET 子句，它们的作用相同。
ij> SELECT * FROM BOOKS FETCH FIRST 4 ROWS ONLY; ID |AUTHOR_ID |TITLE ------------------------------------------------- 1 |1 |Call of the Wild 2 |1 |Martin Eden 3 |2 |Old Goriot 4 |2 |Cousin Bette
在第一个示例中，我们仅从 BOOKS 表中提取了前 4 行。
ij> SELECT * FROM BOOKS OFFSET 4 ROWS; ID |AUTHOR_ID |TITLE ----------------------------------------------- 5 |3 |Jew Suess 6 |4 |Nana 7 |4 |The Belly of Paris 8 |5 |In Cold blood 9 |5 |Breakfast at Tiffany
使用 OFFSET 子句，我们跳过前四行并显示其余行。
ij> SELECT * FROM BOOKS OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY; ID |AUTHOR_ID |TITLE ----------------------------------------------------------------- 5 |3 |Jew Suess 6 |4 |Nana 7 |4 |The Belly of Paris 3 rows selected
我们可以使用 OFFSET 和 FETCH 子句的组合来选择部分行。
使用 WHERE 子句选择特定行
WHERE 子句可用于过滤结果。 它提供了一个选择条件，以仅从数据中选择特定的行。
ij> SELECT * FROM CARS WHERE PRICE > 40000; ID |NAME |PRICE ------------------------------------------------------ 1 |Audi |52642 2 |Mercedes |57127 5 |Bentley |350000 7 |Hummer |41400 4 rows selected
使用 WHERE 子句，我们仅选择价格高于 40000 的汽车。
ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en'; NAME ------------------------------ Citroen Volkswagen 2 rows selected
使用 LIKE 子句，我们选择符合搜索模式的特定汽车名称。 在我们的例子中，它以“en”字符结尾的汽车。
ij> SELECT * FROM CARS WHERE ID IN (2, 5, 7); ID |NAME |PRICE ------------------------------------------------------ 2 |Mercedes |57127 5 |Bentley |350000 7 |Hummer |41400 3 rows selected
IN 子句可用于从特定值范围中选择行。 上面的 SQL 语句返回 ID 等于 2、5 和 7 的行。
ij> SELECT * FROM CARS WHERE PRICE BETWEEN 20000 AND 50000; ID |NAME |PRICE ------------------------------------------------------ 4 |Volvo |29000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600 4 rows selected
我们选择价格在 20000 到 50000 之间的汽车。 为此，我们使用 WHERE 子句后的 BETWEEN AND 关键字。
排序数据
可以使用 ORDER BY 子句对数据进行排序。
ij> SELECT * FROM CARS ORDER BY PRICE; ID |NAME |PRICE ------------------------------------------------------ 3 |Skoda |9000 6 |Citroen |21000 8 |Volkswagen |21600 4 |Volvo |29000 7 |Hummer |41400 1 |Audi |52642 2 |Mercedes |57127 5 |Bentley |350000 8 rows selected
我们按价格对汽车进行排序。 默认排序类型是升序。
ij> SELECT * FROM CARS ORDER BY PRICE DESC; ID |NAME |PRICE ------------------------------------------------------ 5 |Bentley |350000 2 |Mercedes |57127 1 |Audi |52642 7 |Hummer |41400 4 |Volvo |29000 8 |Volkswagen |21600 6 |Citroen |21000 3 |Skoda |9000
要按降序对数据进行排序，我们添加 DESC 关键字。
Derby 函数
Derby 支持一些有用的函数。 这些内置函数是 SQL 关键字或特殊运算符执行某些操作的表达式。
聚合函数
聚合函数对一组行上的表达式求值。 而其他内置函数对单个表达式进行运算，聚合函数对一组值进行运算，并将它们简化为单个标量值。 内置聚合函数可以计算一组值上的表达式的最小值、最大值、总和、计数和平均值，以及计算行数。
ij> SELECT COUNT(ID) FROM AUTHORS; 1 ----------- 5
COUNT 是一个聚合函数，它计算表达式中访问的行数。 在 AUTHORS 表中有五个作者。
ij> SELECT MIN(PRICE) AS "PRICE", MAX(PRICE) AS "MAX", > AVG(PRICE) AS "AVG", SUM(PRICE) AS "SUM" FROM CARS; PRICE |MAX |AVG |SUM ----------------------------------------------- 9000 |350000 |72721 |581769 1 row selected
在上面的查询中，我们使用了另外四个函数：MAX、MIN、AVG 和 SUM。 AS 子句为列提供标签。
日期和时间函数
日期和时间函数用于处理日期和时间
ij> VALUES CURRENT_DATE; 1 ---------- 2017-03-15 ij> VALUES CURRENT SCHEMA; 1 -------------------------- USER12
VALUES CURRENT_DATE 返回当前日期。
ij> VALUES CURRENT_TIME; 1 -------- 17:22:49
VALUES CURRENT_TIME 返回当前时间。
ij> VALUES CURRENT_TIMESTAMP; 1 ----------------------------- 2017-03-15 17:29:49.987
VALUES CURRENT_TIMESTAMP 返回当前时间戳，即当前日期和时间作为一个值。
字符串函数
Derby 包含处理字符串的函数。
ij> VALUES LENGTH('Wonderful day');
1
-----------
13
1 row selected
LENGTH 函数返回字符串中的字符数。
ij> VALUES UPPER('derby');
1
-----
DERBY
1 row selected
ij> VALUES LOWER('Derby');
1
-----
derby
1 row selected
UPPER 函数将字符转换为大写，LOWER 函数将字符转换为小写。
ij> VALUES SUBSTR('blueberries', 5);
1
-----------
berries
SUBSTR 返回字符串的一部分。 第一个参数是字符串，第二个参数是起始位置。 第一个位置的索引是 1。
ij> VALUES SUBSTR('blueberries', 1, 4);
1
----
blue
第三个参数是可选的；它提供要返回的子字符串的长度。
数学函数
Derby 包含一些数学函数。
ij> VALUES ABS(-4); 1 ----------- 4
ABS 返回数值表达式的绝对值。
ij> VALUES CEIL(3.4), CEIL(3.8); 1 ------------------------ 4.0 4.0
CEIL 函数将指定的数字向上取整。
ij> VALUES FLOOR(3.4), FLOOR(3.8); 1 ------------------------ 3.0 3.0
FLOOR 函数将指定的数字向下取整。
ij> VALUES COS(0.6), SIN(0.6); 1 ------------------------ 0.8253356149096783 0.5646424733950354
COS 和 SIN 是三角余弦和正弦函数。
ij> VALUES RADIANS(180), DEGREES(3.141592653589793); 1 ------------------------ 3.141592653589793 180.0
RADIANS 函数将角度转换为弧度，DEGREES 函数将弧度转换为角度。
ij> VALUES SQRT(16.0); 1 ------------------------ 4.0
SQRT 函数返回浮点数的平方根。
更新和删除数据
现在我们将关注在 CARS 表中更新和删除数据。
ij> UPDATE CARS SET PRICE=58000 WHERE ID=2; 1 row inserted/updated/deleted
UPDATE 语句用于修改数据库表中的数据。 梅赛德斯汽车的 PRICE 设置为 58000。
ij> SELECT * FROM CARS WHERE ID=2; ID |NAME |PRICE ------------------------------------------------------ 2 |Mercedes |58000 1 row selected
随后的 SELECT 语句确认了对数据的修改。
ij> CREATE TABLE CARS2(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
对于下一个例子，我们创建一个新的 CARS2 表。
ij> INSERT INTO CARS2(NAME, PRICE) SELECT NAME, PRICE FROM CARS; 8 rows inserted/updated/deleted
我们将 CARS 表中的所有行插入到 CARS2 表中，从而复制所有数据。
ij> SELECT * FROM CARS2; ID |NAME |PRICE ------------------------------------------------------ 1 |Audi |52642 2 |Mercedes |58000 3 |Skoda |9000 4 |Volvo |29000 5 |Bentley |350000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600 8 rows selected
我们检查 CARS2 表，看到所有数据都复制正常。
ij> DELETE FROM CARS2 WHERE ID=8; 1 row inserted/updated/deleted
我们使用 DELETE FROM 语句删除表中的一行。
ij> DELETE FROM CARS2; 7 rows inserted/updated/deleted
没有 WHERE 子句的 DELETE FROM 语句删除表中的所有行。
ij> DROP TABLE CARS2; 0 rows inserted/updated/deleted
DROP TABLE 语句从数据库中完全删除该表。
RENAME 语句
RENAME 语句属于 SQL 的数据定义语言。
ij> RENAME TABLE CARS TO MYCARS;
RENAME TABLE 语句允许我们重命名现有表。 我们将 FRIENDS 表重命名为 MYFRIENDS。
ij> RENAME COLUMN MYCARS.ID TO CID;
RENAME COLUMN 语句重命名特定的表列。
在本章中，我们使用了 Derby 中 SQL 语言的基础知识。