http://www.apache.org/licenses/LICENSE-2.0
|
•
|
SQL language reference
Reference information
about Derby's SQL language,
including manual pages for statements, functions, and other syntax elements.
| |
|
•
|
SQL reserved words
SQL keywords beyond the standard SQL-92 keywords.
| |
|
•
|
Derby support for SQL-92 features
A list of
SQL-92 features that Derby does
and does not support.
| |
|
•
|
Derby System Tables
Reference
information about the Derby system
catalogs.
| |
|
•
|
Derby exception messages and SQL states
Information
about Derby exception messages.
| |
|
•
|
JDBC Reference
Information
about Derby's implementation
of the JDBC interface include support for JDBC 2.0 features.
| |
|
•
|
Setting attributes for the database connection URL
Information
about the supported attributes to Derby's
JDBC database connection URL.
| |
|
•
|
J2EE Compliance: Java Transaction API and javax.sql Extensions
Information
about the supported attributes to Derby's
support for the Java Transaction API.
| |
|
•
|
Derby API
Notes
about proprietary APIs for Derby.
|
|
•
|
Double quotation marks delimit special identifiers referred to in SQL-92
as delimited identifiers.
| |
|
•
|
Single quotation marks delimit character strings.
| |
|
•
|
Within a character string, to represent a single quotation mark or apostrophe,
use two single quotation marks. (In other words, a single quotation mark is
the escape character for a single quotation mark.)
A double quotation
mark does not need an escape character. To represent a double quotation mark,
simply use a double quotation mark. However, note that in a Java
program, a double quotation mark requires the backslash escape character.
Example:
| |
|
•
|
SQL keywords are case-insensitive. For example, you can type the keyword
SELECT as SELECT, Select, select, or sELECT.
| |
|
•
|
SQL-92-style identifiers are case-insensitive (see
SQL92Identifier
),
unless they are delimited.
| |
|
•
|
Java-style identifiers are always case-sensitive.
| |
|
•
|
* is a wildcard within a
SelectExpression
. See
The * wildcard
. It
can also be the multiplication operator. In all other cases, it is a syntactical
metasymbol that flags items you can repeat 0 or more times.
| |
|
•
|
% and _ are character wildcards when used within character strings following
a LIKE operator (except when escaped with an escape character). See
Boolean expression
.
| |
|
•
|
Two dashes (--) and a newline character delimit a comment, as per the
SQL-92 standard. The two dashes start the comment and the newline character
ends the comment.
|
"A.B"
"A"."B"
|
•
|
creating a table (
CREATE TABLE statement
)
| |
|
•
|
specifying updatable columns in a cursor
| |
|
•
|
in a column's correlation name in a SELECT expression (see
SelectExpression
)
| |
|
•
|
in a column's correlation name in a TableExpression (see
TableExpression
)
|
In this example, the correlation-Name col1 FOR c11 is not permitted because c11 is listed in the FOR UPDATE list of columns. You can use the correlation-Name FOR c12 because it is not in the FOR UPDATE list.SELECT c11 AS col1, c12 AS col2, c13 FROM t1 FOR UPDATE of c11,c13
-- C.Country is a column-Name qualified with a -- correlation-Name . SELECT C.Country FROM APP.Countries C
In this example, the correlation-Name col1 FOR c11 is not permitted because c11 is listed in the FOR UPDATE list of columns. You can use the correlation-Name FOR c12 because it is not in the FOR UPDATE list.SELECT c11 AS col1, c12 AS col2, c13 FROM t1 FOR UPDATE of c11,c13
ij> CREATE TABLE mytable (mycol INT); 0 rows inserted/updated/deleted ij> INSERT INTO mytable VALUES (1), (2), (3); 3 rows inserted/updated/deleted -- this example uses the ij command prepare, -- which prepares a statement ij> prepare p1 AS 'INSERT INTO MyTable VALUES (4)'; -- p1 depends on mytable; ij> execute p1; 1 row inserted/updated/deleted -- Derby executes it without recompiling ij> CREATE INDEX i1 ON mytable(mycol); 0 rows inserted/updated/deleted -- p1 is temporarily invalidated because of new index ij> execute p1; 1 row inserted/updated/deleted -- Derby automatically recompiles p1 and executes it ij> DROP TABLE mytable; 0 rows inserted/updated/deleted -- Derby permits you to drop table -- because result set of p1 is closed -- however, the statement p1 is temporarily invalidated ij> CREATE TABLE mytable (mycol INT); 0 rows inserted/updated/deleted ij> INSERT INTO mytable VALUES (1), (2), (3); 3 rows inserted/updated/deleted ij> execute p1; 1 row inserted/updated/deleted -- Because p1 is invalid, Derby tries to recompile it -- before executing. -- It is successful and executes. ij> DROP TABLE mytable; 0 rows inserted/updated/deleted -- statement p1 is now invalid, -- and this time the attempt to recompile it -- upon execution will fail ij> execute p1; ERROR 42X05: Table 'MYTABLE' does not exist.
|
•
|
add a column to a table
| |
|
•
|
add a constraint to a table
| |
|
•
|
drop an existing constraint from a table
| |
|
•
|
increase the width of a VARCHAR, CHAR VARYING, and CHARACTER VARYING column
| |
|
•
|
override row-level locking for the table (or drop the override)
|
ALTER TABLE table-Name { ADD COLUMN column-definition | ADD CONSTRAINT clause | DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE constraint-name | CHECK constraint-name | CONSTRAINT constraint-name } ALTER column-alteration | LOCKSIZE { ROW | TABLE } }
Simple-column-Name DataType [ Column-level-constraint ]* [ [ WITH ] DEFAULT {ConstantExpression | NULL } ]
column-Name SET DATA TYPE VARCHAR(integer) | column-name SET INCREMENT BY integer-constant
|
•
|
When adding a foreign key or check constraint to an existing table, Derby checks the table to make
sure existing rows satisfy the constraint. If any row is invalid, Derby throws
a statement exception and the constraint is not added.
| |
|
•
|
All columns included in a primary key must contain non null data and be
unique.
ALTER TABLE ADD UNIQUE or PRIMARY KEY provide a shorthand method
of defining a primary key composed of a single column. If PRIMARY KEY is specified
in the definition of column C, the effect is the same as if the PRIMARY KEY(C)
clause were specified as a separate clause. The column cannot contain null
values, so the NOT NULL attribute must also be specified.
|
|
•
|
Increasing the length of an existing VARCHAR column. CHARACTER VARYING
or CHAR VARYING can be used as synonyms for the VARCHAR keyword.
To increase
the width of a column of these types, specify the data type and new size after
the column name.
You are not allowed to decrease the width or to change
the data type. You are not allowed to increase the width of a column that
is part of a primary or unique key referenced by a foreign key constraint
or that is part of a foreign key constraint.
| |
|
•
|
Specifying the interval between consecutive values of the identity column.
To
set an interval between consecutive values of the identity column, specify
the integer-constant. You must previously define the column with the IDENTITY
attribute (SQLSTATE 42837). If there are existing rows in the table, the values
in the column for which the SET INCREMENT default was added do not change.
|
-- Add a new column with a column-level constraint -- to an existing table -- An exception will be thrown if the table -- contains any rows -- since the newcol will be initialized to NULL -- in all existing rows in the table ALTER TABLE CITIES ADD COLUMN REGION VARCHAR(26) CONSTRAINT NEW_CONSTRAINT CHECK (REGION IS NOT NULL); -- Add a new unique constraint to an existing table -- An exception will be thrown if duplicate keys are found ALTER TABLE SAMP.DEPARTMENT ADD CONSTRAINT NEW_UNIQUE UNIQUE (DEPTNO); -- add a new foreign key constraint to the -- Cities table. Each row in Cities is checked -- to make sure it satisfied the constraints. -- if any rows don't satisfy the constraint, the -- constraint is not added ALTER TABLE CITIES ADD CONSTRAINT COUNTRY_FK Foreign Key (COUNTRY) REFERENCES COUNTRIES (COUNTRY); -- Add a primary key constraint to a table -- First, create a new table CREATE TABLE ACTIVITIES (CITY_ID INT NOT NULL, SEASON CHAR(2), ACTIVITY VARCHAR(32) NOT NULL); -- You will not be able to add this constraint if the -- columns you are including in the primary key have -- null data or duplicate values. ALTER TABLE Activities ADD PRIMARY KEY (city_id, activity); -- Drop a primary key constraint from the CITIES table ALTER TABLE Cities DROP CONSTRAINT Cities_PK; -- Drop a foreign key constraint from the CITIES table ALTER TABLE Cities DROP CONSTRAINT COUNTRIES_FK; -- add a DEPTNO column with a default value of 1 ALTER TABLE SAMP.EMP_ACT ADD COLUMN DEPTNO INT DEFAULT 1; -- increase the width of a VARCHAR column ALTER TABLE SAMP.EMP_PHOTO ALTER PHOTO_FORMAT SET DATA TYPE VARCHAR(30); -- change the lock granularity of a table ALTER TABLE SAMP.SALES LOCKSIZE TABLE;
CREATE FUNCTION function-name ( [ FunctionParameter [, FunctionParameter ] ] * ) RETURNS DataType [ FunctionElement ] *
[ schemaName . ] SQL92Identifier
{ | LANGUAGE { JAVA } | EXTERNAL NAME string | PARAMETER STYLE JAVA | { NO SQL | CONTAINS SQL | READS SQL DATA } | { RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT } }
The External Name cannot have any extraneous spaces.class_name.method_name
CREATE [UNIQUE] INDEX index-Name ON table-Name ( Simple-column-Name [ ASC | DESC ] [ , Simple-column-Name [ ASC | DESC ]] * )
SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES, SYS.SYSCONSTRAINTS WHERE SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID AND CONSTRAINTNAME = 'FLIGHTS_PK'
CREATE INDEX OrigIndex ON Flights(orig_airport); -- money is usually ordered from greatest to least, -- so create the index using the descending order CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY); -- use a larger page size for the index call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192'); CREATE INDEX IXSALE ON SAMP.SALES (SALES); call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);
CREATE PROCEDURE procedure-Name ( [ ProcedureParameter [, ProcedureParameter ] ] * ) [ ProcedureElement ] *
{ | [ DYNAMIC ] RESULT SETS INTEGER | LANGUAGE { JAVA } | EXTERNAL NAME string | PARAMETER STYLE JAVA | { NO SQL | MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA } }
The External Name cannot have any extraneous spaces.class_name.method_name
CREATE SCHEMA schemaName
-- Create a schema for employee-related tables CREATE SCHEMA EMP; -- Create a schema for airline-related tables CREATE SCHEMA Flights -- Create a table called "Availability" in each schema CREATE TABLE FLIGHTS.AVAILABILITY (FLIGHT_ID CHAR(6) NOT NULL, SEGMENT_NUMBER INT NOT NULL, FLIGHT_DATE DATE NOT NULL, ECONOMY_SEATS_TAKEN INT, BUSINESS_SEATS_TAKEN INT, FIRSTCLASS_SEATS_TAKEN INT, CONSTRAINT FLT_AVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE)); CREATE TABLE EMP.AVAILABILITY (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT, CONSTRAINT HOTELAVAIL_PK PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
The synonym-Name in the statement represents the synonym name you are giving the target table or view, while the view-Name or table-Name represents the original name of the target table or view.CREATE SYNONYM synonym-Name FOR { view-Name | table-Name }
CREATE TABLE table-Name ( { column-definition | Table-level constraint } [ , { column-definition | Table-level constraint } ] * )
CREATE TABLE HOTELAVAILABILITY (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE)); -- the table-level primary key definition allows you to -- include two columns in the primary key definition PRIMARY KEY (hotel_id, booking_date)) -- assign an identity column attribute to an INTEGER -- column, and also define a primary key constraint -- on the column CREATE TABLE PEOPLE (PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26)); -- assign an identity column attribute to a SMALLINT -- column with an initial value of 5 and an increment value -- of 5. CREATE TABLE GROUPS (GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));
Simple-column-Name DataType [ Column-level-constraint ]* [ [ WITH ] DEFAULT { ConstantExpression | NULL } | generated-column-spec ] [ Column-level-constraint ]*
[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( START WITH IntegerConstant [ ,INCREMENT BY IntegerConstant] ) ] ] ]
|
•
|
SMALLINT
| |
|
•
|
INT
| |
|
•
|
BIGINT
|
Automatically generated values in a GENERATED ALWAYS identity column are unique. Creating an identity column does not create an index on the column.create table greetings (i int generated always as identity, ch char(50)); insert into greetings values (DEFAULT, 'hello'); insert into greetings(ch) values ('bonjour');
Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee uniqueness. Thus, in the above example, the hi and salut rows will both have an identity value of "1", because the generated column starts at "1" and the user-specified value was also "1". To prevent duplication, especially when loading or importing data, create the table using the START WITH value which corresponds to the first identity value that the system should assign. To check for this condition and disallow it, you can use a primary key or unique constraint on the GENERATED BY DEFAULT identity column.create table greetings (i int generated by default as identity, ch char(50)); -- specify value "1": insert into greetings values (1, 'hi'); -- use generated default insert into greetings values (DEFAULT, 'salut'); -- use generated default insert into greetings(ch) values ('bonjour');
|
Data type
|
Maximum Value
|
Minimum Value
|
|
SMALLINT
|
32767 (java.lang.Short.MAX_VALUE)
|
-32768 (java.lang.Short.MIN_VALUE)
|
|
INT
|