Derby 中的 SQL 查询 · ZetCode 中文系列教程

# Derby 中的 SQL 查询 > 原文： [http://zetcode.com/db/apachederbytutorial/sql/](http://zetcode.com/db/apachederbytutorial/sql/) 在本章中，我们将使用 Derby 数据库引擎理解的 SQL。它是 Derby 中存在的最重要的 SQL 语句的快速列表。 _SQL（结构化查询语言）_ 是一种数据库计算机语言，旨在管理关系数据库管理系统中的数据。 Derby 仅支持一组有限的 SQL 语句。缺少其他数据库系统已知的一些重要语句。 Derby 实现了 SQL-92 核心子集以及一些 SQL-99 函数。 ## `DROP TABLE` `DROP TABLE`语句从数据库中删除一个表。 ```java ij> DROP TABLE AUTHORS; 0 rows inserted/updated/deleted ij> DROP TABLE BOOKS; 0 rows inserted/updated/deleted ``` 假设我们先前已经创建了`AUTHORS`和`BOOKS`表，我们将删除它们并再次创建。 `DROP TABLE SQL`语句从数据库中删除该表。请注意，`DROP TABLE IF EXISTS`语句在 Derby 中不存在。 ## `CREATE TABLE` `CREATE TABLE`语句创建一个新表。 ```java 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)`创建和标识列。身份列是存储数字的列，该数字在每次插入时都增加一个。标识列有时称为自动增量列。 ```java 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`列中的值匹配。外键提供了一种加强数据库参照完整性的方法。每本书都是由一个或多个作者撰写的。因此，在`AUTHOR_ID`列的`BOOKS`表中，我们只能拥有`AUTHORS`表中存在的值。 ## 插入行 `INSERT`语句用于在数据库表中创建一个或多个行。 ```java 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`表添加五行。 ```java 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`表中插入八行。 ```java 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`子句。 ```java 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 行。 ```java 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`数据包，我们跳过了前四行并显示其余行。 ```java 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 子句可用于过滤结果。它提供了选择条件，仅从数据中选择特定的行。 ```java 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 的汽车。 ```java ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en'; NAME ------------------------------ Citroen Volkswagen 2 rows selected ``` 通过`LIKE`子句，我们选择适合搜索模式的特定汽车名称。在我们的例子中，汽车以`"en"`字符结尾。 ```java 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 的行。 ```java 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`子句完成订购数据。 ```java 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 ``` 我们按价格订购汽车。默认订单类型为升序。 ```java 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 关键字或特殊运算符执行某些操作的表达式。 ### 汇总函数聚合函数求值一组行上的表达式。其他内置函数在单个表达式上运行，而聚合在一组值上运行并将它们缩减为单个标量值。内置的聚合可以计算一组值以及计数行中表达式的最小值，最大值，总和，计数和平均值。 ```java ij> SELECT COUNT(ID) FROM AUTHORS; 1 ----------- 5 ``` `COUNT()`是一个聚合函数，用于计算在表达式中访问的行数。 `AUTHORS`表中有五位作者。 ```java 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`子句为列提供标签。 ### 日期和时间函数日期和时间函数与日期和时间一起使用 ```java ij> VALUES CURRENT_DATE; 1 ---------- 2017-03-15 ij> VALUES CURRENT SCHEMA; 1 -------------------------- USER12 ``` `VALUES CURRENT_DATE`返回当前日期。 ```java ij> VALUES CURRENT_TIME; 1 -------- 17:22:49 ``` `VALUES CURRENT_TIME`返回当前时间。 ```java ij> VALUES CURRENT_TIMESTAMP; 1 ----------------------------- 2017-03-15 17:29:49.987 ``` `VALUES CURRENT_TIMESTAMP`返回当前时间戳，即当前日期和时间作为一个值。 ### 字符串函数 Derby 包含可用于字符串的函数。 ```java ij> VALUES LENGTH('Wonderful day'); 1 ----------- 13 1 row selected ``` `LENGTH()`函数返回字符串中的字符数。 ```java ij> VALUES UPPER('derby'); 1 ----- DERBY 1 row selected ij> VALUES LOWER('Derby'); 1 ----- derby 1 row selected ``` `UPPER()`函数将字符转换为大写字母，`LOWER()`将字符转换为小写字母。 ```java ij> VALUES SUBSTR('blueberries', 5); 1 ----------- berries ``` `SUBSTR()`返回字符串的一部分。第一个参数是字符串，第二个参数是起始位置。第一位置的索引为 1。 ```java ij> VALUES SUBSTR('blueberries', 1, 4); 1 ---- blue ``` 第三个参数是可选的；它提供了要返回的子字符串的长度。 ### 数学函数 Derby 包含一些数学函数。 ```java ij> VALUES ABS(-4); 1 ----------- 4 ``` `ABS()`返回数字表达式的绝对值。 ```java ij> VALUES CEIL(3.4), CEIL(3.8); 1 ------------------------ 4.0 4.0 ``` `CEIL()`函数将指定的数字四舍五入。 ```java ij> VALUES FLOOR(3.4), FLOOR(3.8); 1 ------------------------ 3.0 3.0 ``` `FLOOR()`函数将指定的数字四舍五入。 ```java ij> VALUES COS(0.6), SIN(0.6); 1 ------------------------ 0.8253356149096783 0.5646424733950354 ``` `COS()`和`SIN()`是三角余弦和正弦函数。 ```java ij> VALUES RADIANS(180), DEGREES(3.141592653589793); 1 ------------------------ 3.141592653589793 180.0 ``` `RADIANS()`函数将度数转换为弧度，`DEGREES()`函数将度数转换为弧度。 ```java ij> VALUES SQRT(16.0); 1 ------------------------ 4.0 ``` `SQRT()`函数返回浮点数的平方根。 ## 更新和删除数据现在，我们将关注更新和删除`CARS`表中的数据。 ```java ij> UPDATE CARS SET PRICE=58000 WHERE ID=2; 1 row inserted/updated/deleted ``` `UPDATE`语句用于修改数据库表中的数据。梅赛德斯汽车的`PRICE`设置为 58000。 ```java ij> SELECT * FROM CARS WHERE ID=2; ID |NAME |PRICE ------------------------------------------------------ 2 |Mercedes |58000 1 row selected ``` 随后的`SELECT`语句确认数据的修改。 ```java 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`表。 ```java ij> INSERT INTO CARS2(NAME, PRICE) SELECT NAME, PRICE FROM CARS; 8 rows inserted/updated/deleted ``` 我们将`CARS`表中的所有行插入`CARS2`表中，从而复制所有数据。 ```java 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`表，发现所有数据都在复制 OK。 ```java ij> DELETE FROM CARS2 WHERE ID=8; 1 row inserted/updated/deleted ``` 我们使用`DELETE FROM`语句删除表中的行。 ```java ij> DELETE FROM CARS2; 7 rows inserted/updated/deleted ``` 没有`WHERE`子句的`DELETE FROM`语句删除表中的所有行。 ```java ij> DROP TABLE CARS2; 0 rows inserted/updated/deleted ``` `DROP TABLE`语句从数据库中完全删除该表。 ## `RENAME`声明 `RENAME`语句属于 SQL 的数据定义语言。 ```java ij> RENAME TABLE CARS TO MYCARS; ``` `RENAME TABLE`语句允许我们重命名现有表。我们将`FRIENDS`表重命名为`MYFRIENDS`。 ```java ij> RENAME COLUMN MYCARS.ID TO CID; ``` `RENAME COLUMN`语句重命名特定的表列。在本章中，我们使用了 Derby 中 SQL 语言的基础知识。