Derby 中的 SQL 查询
在本章中，我们将使用 Derby 数据库引擎理解的 SQL。 它是 Derby 中存在的最重要的 SQL 语句的快速列表。
SQL（结构化查询语言） 是一种数据库计算机语言，旨在管理关系数据库管理系统中的数据。 Derby 仅支持一组有限的 SQL 语句。 缺少其他数据库系统已知的一些重要语句。 Derby 实现了 SQL-92 核心子集以及一些 SQL-99 函数。
DROP TABLE
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语句从数据库中删除该表。 请注意，DROP TABLE IF EXISTS语句在 Derby 中不存在。
CREATE TABLE
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)创建和标识列。 身份列是存储数字的列，该数字在每次插入时都增加一个。 标识列有时称为自动增量列。
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语句用于在数据库表中创建一个或多个行。
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表，发现所有数据都在复制 OK。
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 语言的基础知识。
