SQL Bible [Electronic resources] نسخه متنی

DDL Statements

This section provides BNF notation for DDL statements.

Tables

The notations below are to create, modify, and drop database tables, respectively:

CREATE TABLE <table_name>
(
column_name <datatype> [<column_constraint>,...]
[DEFAULT <default_value>],...
[<table_constraint>,...]
[physical_options]
)

ALTER TABLE <table_name>
{ <vendor_specific_add_column_clause> |
<vendor_specific_alter_column_clause> |
<vendor_specific_add_constraint_clause> |
<vendor_specific_drop_constraint_clause>
}

DROP TABLE <table_name>

Indexes

The following two notations are to create and drop database indexes:

CREATE [UNIQUE] INDEX <index_name>
ON <table_name> (<column_name> [ASC|DESC],...)

DROP INDEX <index_name>

Views

The notations below are to create, modify, and drop database views, respectively:

CREATE VIEW <view_name> [(column_name,...)]
AS <select_statement>
[WITH CHECK OPTION]

ALTER VIEW <view_name>
<vendor_specific_alter_view_clause>

DROP VIEW <view_name>

Schemas

The following two notations are to create and to drop database schemas:

CREATE SCHEMA <schema_name> 
AUTHORIZATION <authorization_id>
<create_object_statement>,...
<grant_privilege_statement>,...

DROP SCHEMA <schema_name> RESTRICT

Stored procedures

The BNF notation to create a stored procedure follows:

CREATE PROCEDURE <procedure_name> [<parameter_section>]
<procedure_definition>

The following notation is to drop a stored procedure:

DROP PROCEDURE <procedure_name>

User-defined functions

The BNF notation to create a user-defined function follows:

CREATE FUNCTION <function_name>
<function_definition_includes_return_statement>

The following notation is to drop a user-defined function:

DROP FUNCTION <function_name>

Triggers

The BNF notation to create a trigger follows:

CREATE TRIGGER <trigger_name>
[BEFORE | AFTER] 
{INSERT | UPDATE | DELETE}
ON <table_name>
[FOR EACH ROW]
<trigger_body>

The following notation is to drop a trigger:

DROP TRIGGER [qualifier.]<trigger_name>