- CREATE TABLE syntax
- ALTER TABLE syntax
- DROP TABLE syntax
- INSERT syntax
- DELETE syntax
- UPDATE syntax
- SELECT syntax
CREATE TABLE syntaxTop
CREATE TABLE [ IF NOT EXISTS ] table_name ( column_declare1, column_declare2, constraint_declare1, ... )
column_declare ::= column_name type [ DEFAULT expression ] [ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]
type ::= BIT | REAL | CHAR | TEXT | DATE | TIME | FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC | DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR | SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR | LONGVARBINARY
constraint_declare :: = [ CONSTRAINT constraint_name ] PRIMARY KEY ( col1, col2, ... ) | FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ] [ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] | UNIQUE ( col1, col2, ... ) | CHECK ( expression ) [ INITIALLY DEFERRED | INITIALLY | IMMEDIATE ] [ NOT DEFERRABLE | DEFERRABLE ]
triggered_action :: = NO ACTION | SET NULL | SET DEFAULT | CASCADE
When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,
CREATE TABLE Table ( str_col VARCHAR(100) )
When handling strings the database will only allocate as much storage space as the string uses up. If a 10
character string is stored in
str_col then only space for 10 characters will be allocated in the database. So if you need a column that can store a
string of any size, use an arbitrarily large number when declaring the column.
Unique, primary/foreign key and check integrity constraints can be defined in the
CREATE TABLE statement. The following is an example of defining a table with integrity constraints.
CREATE TABLE Customer ( number VARCHAR(40) NOT NULL, name VARCHAR(100) NOT NULL, ssn VARCHAR(50) NOT NULL, age INTEGER NOT NULL, CONSTRAINT cust_pk PRIMARY KEY (number), UNIQUE ( ssn ), // (An anonymous constraint) CONSTRAINT age_check CHECK (age >= 0 AND age < 200) )
ALTER TABLE syntaxTop
ALTER TABLE table_name ADD [COLUMN] column_declare ALTER TABLE table_name ADD constraint_declare ALTER TABLE table_name DROP [COLUMN] column_name ALTER TABLE table_name DROP CONSTRAINT constraint_name ALTER TABLE table_name DROP PRIMARY KEY ALTER TABLE table_name ALTER [COLUMN] column_name SET default_expr ALTER TABLE table_name ALTER [COLUMN] column_name DROP DEFAULT
ALTER CREATE TABLE ....
ALTER is used to add / remove / modify the columns and integrity constraints of a table. The
ADD [COLUMN] form adds a new column definition to the table (using the same column declaration syntax in the
CREATE command). The
DROP [COLUMN] form drops the column with the name from the table.
ALTER [COLUMN] column_name SET default_expr alters the default value for the column.
ALTER [COLUMN] column_name DROP DEFAULT removes the default value set for the column.
The following example adds a new column to a table;
ALTER TABLE Order ADD notes VARCHAR(60000) DEFAULT 'n/a'
ADD constraint_declare is used to define a new integrity constraint on a table (using the same constraint declaration syntax in the
DROP CONSTRAINT is used to drop a named constraint from a table.
The other form of this statement is
ALTER CREATE TABLE ... This alters the table to the specification of the given
CREATE statement. Any columns that are in the original table are not lost provided the column name is in the new
table specification. Any columns that were not in the original table are set to the default value.
The following example demonstrates this form of
ALTER CREATE TABLE table ( col1 INTEGER NOT NULL UNIQUE, col2 NUMERIC, col3 VARCHAR(90000) )
ALTER CREATE TABLE ... syntax is an extension to the SQL-92 standard.
DROP TABLE syntaxTop
DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....
Removes the table(s) from the database. The
clause will drop the table only if it exists. If this clause is not present an error is generated if the table
does not exist. Any data that was in a dropped table is lost so use with care.
INSERT INTO table_name [ ( col_name1, col_name2, .... ) ] VALUES ( expression1_1, expression1_2, .... ), (expression2_1, expression2_2, .... ), ....
INSERT INTO table_name [ ( col_name1, col_name2, .... ) ] SELECT ...
INSERT INTO table_name SET col_name1 = expression1, col_name2 = expression2, ....
This is the SQL command to insert records into a table in the database. This statement comes in three forms.
The first inserts data from a
INSERT INTO table ( col1, col2, col3 ) VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ), ( 11, (28 / 2) - 7, CONCAT(col1, 'c') )
The second form is used to copy information from a
SELECT query into the table specified in the
INSERT statement. For example;
INSERT INTO table ( col1, col2, col3 ) SELECT id, num, description FROM table2 WHERE description LIKE '11%'
The third form uses a list of column
SET assignments. For example;
INSERT INTO table SET col1 = 10, col2 = 4 + 3, col3 = CONCAT(col1, 'c')
If a column of the table is not specified in an
the default value declared for the column is used. If no default value was declared a NULL value is inserted
in the column. If the column is declared as
the insert operation fails.
DELETE FROM table_name [ WHERE expression ]
Deletes all the rows from the table that match the
clause. An example of using the
DELETE FROM table WHERE col3 LIKE '11%' AND col1 < 1000
UPDATE table_name SET col_name1 = expression1, col_name2 = expression2, .... [ WHERE expression ]
Updates information in a table. The
clause is a list of assignments that describe how the columns of the data matched by the
clause are to be updated. Any columns not assigned in the
clause are left unchanged. Examples of using
UPDATE Employee SET salary = salary * 1.25 WHERE name = 'Bob'
UPDATE Order SET id = id + 3, part = CONCAT(part, '-00') WHERE part LIKE 'PO-%'
SELECT [ DISTINCT | ALL ] column_expression1, column_expression2, .... [ FROM from_clause ] [ WHERE where_expression ] [ GROUP BY expression1, expression2, .... ] [ HAVING having_expression ] [ ORDER BY order_column_expr1, order_column_expr2, .... ]
column_expression ::= expression [ AS ] [ column_alias ]
from_clause ::= select_table1, select_table2, ... from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ... from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ... from_clause ::= select_table1 [INNER] JOIN select_table2 ...
select_table ::= table_name [ AS ] [ table_alias ] select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]
order_column_expr ::= expression [ ASC | DESC ]
statement is used to form queries for extracting information out of the database. The following example query
will return the number, quantity and price of all orders for more than 5 items sorted in descending order by
order number. In addition it rounds the order price to two decimal places and applies a dollar ($) sign to the
SELECT number, quantity, CONCAT('$', ROUND(price, 2)) FROM Order WHERE quantity > 5 ORDER BY number DESC
clause may refer to a column, a column alias, or an expression.
HAVING clause is evaluated after the grouping and aggregate columns have been resolved.
For examples of using
SELECT with aggregate functions see the 'Internal SQL Functions' section.