原文： http://zetcode.com/db/apachederbytutorial/sql/

在本章中，我们将使用 Derby 数据库引擎理解的 SQL。 它是 Derby 中存在的最重要的 SQL 语句的快速列表。

SQL（结构化查询语言） 是一种数据库计算机语言，旨在管理关系数据库管理系统中的数据。 Derby 仅支持一组有限的 SQL 语句。 缺少其他数据库系统已知的一些重要语句。 Derby 实现了 SQL-92 核心子集以及一些 SQL-99 函数。

DROP TABLE

DROP TABLE语句从数据库中删除一个表。

  1. ij> DROP TABLE AUTHORS;
  2. 0 rows inserted/updated/deleted
  3. ij> DROP TABLE BOOKS;
  4. 0 rows inserted/updated/deleted

假设我们先前已经创建了AUTHORS和BOOKS表，我们将删除它们并再次创建。 DROP TABLE SQL语句从数据库中删除该表。 请注意，DROP TABLE IF EXISTS语句在 Derby 中不存在。

CREATE TABLE

CREATE TABLE语句创建一个新表。

  1. ij> CREATE TABLE AUTHORS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
  2. > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(25));
  3. 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 
  2. > (START WITH 1, INCREMENT BY 1), AUTHOR_ID BIGINT, TITLE VARCHAR(150), 
  3. > FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID));
  4. 0 rows inserted/updated/deleted

我们创建一个包含三列的BOOKS表。 FOREIGN KEY指定AUTHOR_ID列中的值必须与AUTHORS表的ID列中的值匹配。 外键提供了一种加强数据库参照完整性的方法。 每本书都是由一个或多个作者撰写的。 因此，在AUTHOR_ID列的BOOKS表中，我们只能拥有AUTHORS表中存在的值。

插入行

INSERT语句用于在数据库表中创建一个或多个行。

  1. ij> INSERT INTO AUTHORS(NAME) VALUES('Jack London');
  2. ij> INSERT INTO AUTHORS(NAME) VALUES('Honore de Balzac');
  3. ij> INSERT INTO AUTHORS(NAME) VALUES('Lion Feuchtwanger');
  4. ij> INSERT INTO AUTHORS(NAME) VALUES('Emile Zola');
  5. ij> INSERT INTO AUTHORS(NAME) VALUES('Truman Capote');

我们使用INSERT INTO SQL 语句向AUTHORS表添加五行。

  1. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Call of the Wild');
  2. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Martin Eden');
  3. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Old Goriot');
  4. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Cousin Bette');
  5. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(3, 'Jew Suess');
  6. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'Nana');
  7. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'The Belly of Paris');
  8. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'In Cold blood');
  9. ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'Breakfast at Tiffany');

我们在BOOKS表中插入八行。

  1. ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS
  2. > WHERE AUTHORS.ID = BOOKS.AUTHOR_ID;
  3. NAME                     |TITLE
  4. -------------------------------------------------
  5. Jack London              |Call of the Wild
  6. Jack London              |Martin Eden
  7. Honore de Balzac         |Old Goriot
  8. Honore de Balzac         |Cousin Bette
  9. Lion Feuchtwanger        |Jew Suess
  10. Emile Zola               |Nana
  11. Emile Zola               |The Belly of Paris
  12. Truman Capote            |In Cold blood
  13. Truman Capote            |Breakfast at Tiffany
  15. 9 rows selected

上面的 SQL 查询将两个表连接在一起。 它将每个书名分配给作者。

查询

查询用于从数据库表中查找数据。 SELECT语句是执行查询的主要语句。

限制数据输出

限制数据输出至关重要，因为许多数据库都有成千上万的行。 Derby 不支持其他数据库中已知的LIMIT子句。 Derby 10.7 引入了执行相同函数的FETCH和OFFSET子句。

  1. ij> SELECT * FROM BOOKS FETCH FIRST 4 ROWS ONLY;
  2. ID         |AUTHOR_ID  |TITLE
  3. -------------------------------------------------
  4. 1          |1          |Call of the Wild
  5. 2          |1          |Martin Eden
  6. 3          |2          |Old Goriot
  7. 4          |2          |Cousin Bette

在第一个示例中，我们仅从BOOKS表中提取了前 4 行。

  1. ij> SELECT * FROM BOOKS OFFSET 4 ROWS;
  2. ID         |AUTHOR_ID  |TITLE
  3. -----------------------------------------------
  4. 5          |3          |Jew Suess
  5. 6          |4          |Nana
  6. 7          |4          |The Belly of Paris
  7. 8          |5          |In Cold blood
  8. 9          |5          |Breakfast at Tiffany

使用OFFSET数据包，我们跳过了前四行并显示其余行。

  1. ij> SELECT * FROM BOOKS OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
  2. ID         |AUTHOR_ID  |TITLE
  3. -----------------------------------------------------------------
  4. 5          |3          |Jew Suess
  5. 6          |4          |Nana
  6. 7          |4          |The Belly of Paris
  8. 3 rows selected

我们可以使用OFFSET和FETCH子句的组合选择一部分行。

使用WHERE子句选择特定的行

WHERE 子句可用于过滤结果。 它提供了选择条件，仅从数据中选择特定的行。

  1. ij> SELECT * FROM CARS WHERE PRICE > 40000;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 1          |Audi                          |52642      
  5. 2          |Mercedes                      |57127      
  6. 5          |Bentley                       |350000     
  7. 7          |Hummer                        |41400
  9. 4 rows selected

使用WHERE子句，我们仅选择价格高于 40000 的汽车。

  1. ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en';
  2. NAME                          
  3. ------------------------------
  4. Citroen                       
  5. Volkswagen                    
  7. 2 rows selected

通过LIKE子句，我们选择适合搜索模式的特定汽车名称。 在我们的例子中，汽车以"en"字符结尾。

  1. ij> SELECT * FROM CARS WHERE ID IN (2, 5, 7);
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 2          |Mercedes                      |57127      
  5. 5          |Bentley                       |350000     
  6. 7          |Hummer                        |41400      
  8. 3 rows selected

IN子句可用于从特定值范围中选择行。 上面的 SQL 语句返回 ID 等于 2、5 和 7 的行。

  1. ij> SELECT * FROM CARS WHERE PRICE BETWEEN 20000 AND 50000;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 4          |Volvo                         |29000      
  5. 6          |Citroen                       |21000      
  6. 7          |Hummer                        |41400      
  7. 8          |Volkswagen                    |21600      
  9. 4 rows selected

我们选择价格在 20000 到 50000 之间的汽车。为此，我们在WHERE子句之后使用BETWEEN AND关键字。

排序数据

可以使用ORDER BY子句完成订购数据。

  1. ij> SELECT * FROM CARS ORDER BY PRICE;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 3          |Skoda                         |9000       
  5. 6          |Citroen                       |21000      
  6. 8          |Volkswagen                    |21600      
  7. 4          |Volvo                         |29000      
  8. 7          |Hummer                        |41400      
  9. 1          |Audi                          |52642      
  10. 2          |Mercedes                      |57127      
  11. 5          |Bentley                       |350000     
  13. 8 rows selected

我们按价格订购汽车。 默认订单类型为升序。

  1. ij> SELECT * FROM CARS ORDER BY PRICE DESC;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 5          |Bentley                       |350000     
  5. 2          |Mercedes                      |57127      
  6. 1          |Audi                          |52642      
  7. 7          |Hummer                        |41400      
  8. 4          |Volvo                         |29000      
  9. 8          |Volkswagen                    |21600      
  10. 6          |Citroen                       |21000      
  11. 3          |Skoda                         |9000

为了按降序对数据进行排序，我们添加了DESC关键字。

Derby 函数

Derby 支持一些有用的函数。 这些内置函数是使用 SQL 关键字或特殊运算符执行某些操作的表达式。

汇总函数

聚合函数求值一组行上的表达式。 其他内置函数在单个表达式上运行，而聚合在一组值上运行并将它们缩减为单个标量值。 内置的聚合可以计算一组值以及计数行中表达式的最小值，最大值，总和，计数和平均值。

  1. ij> SELECT COUNT(ID) FROM AUTHORS;
  2. 1          
  3. -----------
  4. 5

COUNT()是一个聚合函数，用于计算在表达式中访问的行数。 AUTHORS表中有五位作者。

  1. ij> SELECT MIN(PRICE) AS "PRICE", MAX(PRICE) AS "MAX",
  2. > AVG(PRICE) AS "AVG", SUM(PRICE) AS "SUM" FROM CARS;
  3. PRICE      |MAX        |AVG        |SUM        
  4. -----------------------------------------------
  5. 9000       |350000     |72721      |581769      
  7. 1 row selected

在上面的查询中，我们使用其他四个函数：MAX()，MIN()，AVG()和SUM()。 AS子句为列提供标签。

日期和时间函数

日期和时间函数与日期和时间一起使用

  1. ij> VALUES CURRENT_DATE;
  2. 1
  3. ----------
  4. 2017-03-15
  6. ij> VALUES CURRENT SCHEMA;
  7. 1
  8. --------------------------
  9. USER12

VALUES CURRENT_DATE返回当前日期。

  1. ij> VALUES CURRENT_TIME;
  2. 1       
  3. --------
  4. 17:22:49

VALUES CURRENT_TIME返回当前时间。

  1. ij> VALUES CURRENT_TIMESTAMP;
  2. 1                            
  3. -----------------------------
  4. 2017-03-15 17:29:49.987

VALUES CURRENT_TIMESTAMP返回当前时间戳，即当前日期和时间作为一个值。

字符串函数

Derby 包含可用于字符串的函数。

  1. ij> VALUES LENGTH('Wonderful day');
  2. 1          
  3. -----------
  4. 13         
  6. 1 row selected

LENGTH()函数返回字符串中的字符数。

  1. ij> VALUES UPPER('derby');
  2. 1    
  3. -----
  4. DERBY
  6. 1 row selected
  7. ij> VALUES LOWER('Derby');
  8. 1    
  9. -----
  10. derby
  12. 1 row selected

UPPER()函数将字符转换为大写字母，LOWER()将字符转换为小写字母。

  1. ij> VALUES SUBSTR('blueberries', 5);        
  2. 1          
  3. -----------
  4. berries

SUBSTR()返回字符串的一部分。 第一个参数是字符串，第二个参数是起始位置。 第一位置的索引为 1。

  1. ij> VALUES SUBSTR('blueberries', 1, 4);
  2. 1   
  3. ----
  4. blue

第三个参数是可选的； 它提供了要返回的子字符串的长度。

数学函数

Derby 包含一些数学函数。

  1. ij> VALUES ABS(-4);
  2. 1          
  3. -----------
  4. 4

ABS()返回数字表达式的绝对值。

  1. ij> VALUES CEIL(3.4), CEIL(3.8);
  2. 1                       
  3. ------------------------
  4. 4.0                     
  5. 4.0

CEIL()函数将指定的数字四舍五入。

  1. ij> VALUES FLOOR(3.4), FLOOR(3.8);
  2. 1                       
  3. ------------------------
  4. 3.0                     
  5. 3.0

FLOOR()函数将指定的数字四舍五入。

  1. ij> VALUES COS(0.6), SIN(0.6);
  2. 1                       
  3. ------------------------
  4. 0.8253356149096783      
  5. 0.5646424733950354

COS()和SIN()是三角余弦和正弦函数。

  1. ij> VALUES RADIANS(180), DEGREES(3.141592653589793);
  2. 1                       
  3. ------------------------
  4. 3.141592653589793       
  5. 180.0

RADIANS()函数将度数转换为弧度，DEGREES()函数将度数转换为弧度。

  1. ij> VALUES SQRT(16.0);
  2. 1                       
  3. ------------------------
  4. 4.0

SQRT()函数返回浮点数的平方根。

更新和删除数据

现在，我们将关注更新和删除CARS表中的数据。

  1. ij> UPDATE CARS SET PRICE=58000 WHERE ID=2;
  2. 1 row inserted/updated/deleted

UPDATE语句用于修改数据库表中的数据。 梅赛德斯汽车的PRICE设置为 58000。

  1. ij> SELECT * FROM CARS WHERE ID=2;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 2          |Mercedes                      |58000      
  6. 1 row selected

随后的SELECT语句确认数据的修改。

  1. ij> CREATE TABLE CARS2(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  2. > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);

对于下一种情况，我们创建一个新的CARS2表。

  1. ij> INSERT INTO CARS2(NAME, PRICE) SELECT NAME, PRICE FROM CARS;
  2. 8 rows inserted/updated/deleted

我们将CARS表中的所有行插入CARS2表中，从而复制所有数据。

  1. ij> SELECT * FROM CARS2;
  2. ID         |NAME                          |PRICE      
  3. ------------------------------------------------------
  4. 1          |Audi                          |52642      
  5. 2          |Mercedes                      |58000      
  6. 3          |Skoda                         |9000       
  7. 4          |Volvo                         |29000      
  8. 5          |Bentley                       |350000     
  9. 6          |Citroen                       |21000      
  10. 7          |Hummer                        |41400      
  11. 8          |Volkswagen                    |21600      
  13. 8 rows selected

我们检查CARS2表，发现所有数据都在复制 OK。

  1. ij> DELETE FROM CARS2 WHERE ID=8;
  2. 1 row inserted/updated/deleted

我们使用DELETE FROM语句删除表中的行。

  1. ij> DELETE FROM CARS2;
  2. 7 rows inserted/updated/deleted

没有WHERE子句的DELETE FROM语句删除表中的所有行。

  1. ij> DROP TABLE CARS2;
  2. 0 rows inserted/updated/deleted

DROP TABLE语句从数据库中完全删除该表。

RENAME声明

RENAME语句属于 SQL 的数据定义语言。

  1. ij> RENAME TABLE CARS TO MYCARS;

RENAME TABLE语句允许我们重命名现有表。 我们将FRIENDS表重命名为MYFRIENDS。

  1. ij> RENAME COLUMN MYCARS.ID TO CID;

RENAME COLUMN语句重命名特定的表列。

在本章中，我们使用了 Derby 中 SQL 语言的基础知识。