Apache Derby - 語法

---

---

本節介紹了所有 Apache Derby SQL 語句的語法。

所有語句都以下列任一關鍵字開頭，例如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP、CREATE、USE、SHOW，並且所有語句都以分號 (;) 結尾。

Apache Derby 的 SQL 語句區分大小寫，包括表名。

CREATE 語句

CREATE TABLE table_name (
   column_name1 column_data_type1 constraint (optional),
   column_name2 column_data_type2 constraint (optional),
   column_name3 column_data_type3 constraint (optional)
);

DROP TABLE

DROP TABLE table_name;

INSERT 語句

INSERT INTO table_name VALUES (column_name1, column_name2, ...);

SELECT 語句

SELECT column_name, column_name, ... FROM table_name;

UPDATE 語句

UPDATE table_name
   SET column_name = value, column_name = value, ...
   WHERE conditions;

DELETE 語句

DELETE FROM table_name WHERE condition;

DESCRIBE 語句

Describe table_name

SQL TRUNCATE TABLE 語句

TRUNCATE TABLE table_name;

ALTER 語句 - 新增列

ALTER TABLE table_name ADD COLUMN column_name column_type;

ALTER 語句 - 新增約束

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);

ALTER 語句 - 刪除列

ALTER TABLE table_name DROP COLUMN column_name;

ALTER 語句 - 刪除約束

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

WHERE 子句

SELECT * from table_name WHERE condition;
or,
DELETE from table_name WHERE condition;
or,
UPDATE table_name SET column_name = value WHERE condition;

GROUP BY 子句

SELECT column1, column2, . . . table_name GROUP BY column1, column2, . . .;

ORDER BY 子句

SELECT * FROM table_name ORDER BY column_name ASC|DESC.

Having 子句

SELECT column1, column2 . . . from table_name GROUP BY column having
condition;

建立索引

CTREATE INDEX index_name on table_name (column_name);

建立一個 UNIQUE 索引

CREATE UNIQUE INDEX index_name on table_name (column_name);

建立一個 COMPOSITE 索引

CREATE INDEX index_name on table_name (column_name1, column_name2);

顯示索引

SHOW INDEXES FROM table_name;

刪除索引

DROP INDEX index_name;