1. CREATE TABLE syntax
2. ALTER TABLE syntax
3. DROP TABLE syntax
4. INSERT syntax
5. DELETE syntax
6. UPDATE syntax
7. SELECT syntax

CREATE TABLE syntax

        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 syntax

        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 CREATE command). 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 statement;

            ALTER CREATE TABLE table ( col1 INTEGER NOT NULL UNIQUE, col2 NUMERIC, col3 VARCHAR(90000) )
          

The ALTER CREATE TABLE ... syntax is an extension to the SQL-92 standard.

DROP TABLE syntax

        DROP TABLE [ IF EXISTS ] table_name1, table_name2, ....

Removes the table(s) from the database. The IF EXISTS 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 syntax

        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 VALUES clause;

            
              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 INSERT 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 NOT NULL the insert operation fails.

DELETE syntax

        DELETE FROM table_name [ WHERE expression ]

Deletes all the rows from the table that match the WHERE clause. An example of using the DELETE statement;

            DELETE FROM table WHERE col3 LIKE '11%' AND col1 < 1000      
          

UPDATE syntax

        UPDATE table_name SET col_name1 = expression1, col_name2 = expression2, .... [ WHERE expression ]

Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated. Any columns not assigned in the SET clause are left unchanged. Examples of using UPDATE ;

            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 syntax

        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 ]

The SELECT 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 output.

            
              SELECT number, quantity, CONCAT('$', ROUND(price, 2)) FROM Order WHERE quantity > 5 ORDER BY number
              DESC
                 
          

The ORDER BY and GROUP BY clause may refer to a column, a column alias, or an expression. The 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.