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 語言的基礎知識。