PU Coding Syntax and Examples of MySQL

SQL Statements

The statements that work with the data in a database are called the data manipulation language (DML).

The statements that create databases and work with the objects within a database are called the data definition language (DDL).

On large systems, these statements are used exclusively by database administratiors (DBAs).

DML:

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

DDL:

1. CREATE DATABASE
2. DROP DATABASE
3. CREATE TABLE
4. ALTER TABLE
5. DROP TABLE
6. CREATE INDEX
7. ALTER INDEX
8. DROP INDEX

SELECT

The basic syntax for the SELECT statement (including five clauses.)

SELECT select_list
[FROM table_source]
[WHERE search_condition]
[ORDER BY order_by_list]
[LIMIT row_limit]

The syntax of the SELECT clause.

SELECT [ALL|DISTINCT]
       column_specification [[AS] result_column]
    [, column_specification [[AS] result_column] ...

column_specification could be *, column_name, arithmetic expressions and functions.

ORDER BY

The syntax of the ORDER BY clause:

ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]]...

• ASC is default.
• The ORDER BY clause can include a column alias that's specified in the SELECT clause if the column alias does not include spaces.
• The ORDER BY clause can include any valid expression. The expression can refer to any column in the base table, even if it isn't included in the result set.
• The ORDER BY clause can use numbers to specify the columns to use for sorting.
  • In that case, 1 represents the first column in the result set, 2 represents the second column, and son on.

LIMIT

The syntax of the LIMIT clause:

LIMIT [offset,] row_count

• If you code both arguments, the offset specifies the first row to return, where the offset of the first row is 0.
• If you want to retrieve all of the rows from a certain offset to the end of the result set, code -1 for the row count.

JOIN

A join lets you combine columns from two or more tables into a single result set.

Tables are typically joined on the relationship between the primary key in one table and a foreign key in the other table. However, you can also join tables based on relationships not defined in the database. These are called hac relationships.

Explicit syntax for an inner join:

SELECT select_list
FROM table_1
    [INNER] JOIN table_2 ON join_condition_1
    [[INNER] JOIN table_3 ON join_condition_2]...

Implicit syntax for an inner join:

SELECT select_list
FROM table_1, table_2 [, table_3]...
WHERE table_1.column_name operator table_2.column_name
    [AND table_2.column_name operator table_3.column_name]...

• THE INNER keyword is optional and is seldom used.

Explicit syntax for an outer join:

SELECT select_list
FROM table_1
    {LEFT|RIGHT} [OUTER] JOIN table_2 ON join_condition_1
    [{{LEFT|RIGHT} [OUTER] JOIN table_3 ON join_condition_2]...

• The OUTER keyword is optional and typically omitted.
• An outer join retrieves all rows that satisfy the join condition, plus unmatched rows in the left or right table.
• When a row with unmatched columns is retrieved, any columns from the other table that are included in the result set are given null values.

The syntax for a join that uses the USING keyword:

SELECT select_list
FROM table_1
    [{LEFT|RIGHT} [OUTER]] JOIN table_2 USING (join_column_1 [, join_column_2]...)
    [[{LEFT|RIGHT} [OUTER]] JOIN table_3 USING (join_column_1 [, join_column_2]...)]...

• The join can be an inter join or an outer join.

The syntax for a join that uses the NATURAL keyword

SELECT select_list
FROM table_1
    NATURAL JOIN table_2
    [NATURAL JOIN table_3]...

• You can use NATURAL keyword to create a natural join that joins two tables based on all columns in the two tables that have the same name.

Explicit syntax for a cross join:

SELECT select_list
FROM table_1 CROSS JOIN table_2

Implicit syntax for a cross join:

SELECT select_list
FROM table_1, table_2

• A cross join joins each row from the first table with each row from the second table. The result set returned by a cross join is known as a Cartesian product.

UNION

The syntax for a union operation:

    SELECT_statement_1
UNION [ALL] SELECT_statement_2
[UNION [ALL] SELECT_statement_3]...
[ORDER BY order_by_list]

A union combines rows from two or more result sets, while a join combines columns from two or more result sets.

Each result set must return the same number of columns, and the corresponding columns in each result set must have compatible data types.

By default, a union eliminates duplicate rows. If you want to include duplicate rows, code the ALL keyword.

The column names in the final result set are taken from the first SELECT clause. Column aliases assigned by the other SELECT clauses have no effect on the final result set.

If you want to sort the result of a union operation, you can code an ORDER BY clause after the last SELECT statement. In an ORDER BY clause, you must use the column names that are specified in the first SELECT statement.

INSERT, UPDATE and DELETE

Syntax of the INSERT statement

INSERT [INTO] table_name [(column_list)]
VALUES (expression_1[, expression_2]...)
       [, (expression_1[, expression_2]...)]...

• INTO keyword is optional
• If you don't include a column list in the INSERT clause, you must specify the column values in the same order as in the table, and you must code a value for each column.
• If you include a column list, you must specify the column values in the same order as in the column list, and you can omit columns that have default values, accept null values, or are automatically generated.
• To insert a null value into a column, you can use the NULL keyword.
• To insert a default value or to have MySQL generate a value for an auto increment column, you can use the DEFAULT keyword.

Syntax of the UPDATE statement

UPDATE table_name 
SET column_name_1 = expression_1[, column_name_2 = expression_2]...
[WHERE search_condition]

• If you turn off safe update mode and omit the WHERE clasuse, all rows in the table will be updated.

Syntax of the DELETE statement

DELEtE FROM table_name
[WHERE search_conditon]

• If you turn safe update mode off and omit the WHERE clause from a DELETE statement, all the rows in the table will be deleted.

CREATE, ALTER and DROP

Syntax of the CREATE TABLE AS statement:

CREATE TABLE table_name AS select_statement

• When you use the CREATE TABLE AS statement to create a table, only the column definitions and data are copied.
• Definitions of primary keys, foreign keys, indexes and so on are not included in the new table.