ice9web - MySQL Statement Syntax

Ice9web.com

MySQL STATEMENT SYNTAX

To help you find the syntax you need when you need it, this appendix lists the syntax for the most frequently used MySQL operations. Each statement starts with a brief description and then displays the appropriate syntax. For added convenience, you'll also find cross-references to the tutorials where specific statements are taught.

The | symbol is used to indicate one of several options, so NULL|NOT NULL means specify either NULL or NOT NULL.
Keywords or clauses contained within square parentheses [like this] are optional.
Not all MySQL statements are listed, nor is every clause and option listed.

ALTER TABLE

ALTER TABLE is used to update the schema of an existing table. To create a new table, use CREATE TABLE. See Tutorial 26, "Creating and Manipulating Tables," for more information.

• Input

ALTER TABLE tablename
(
    ADD     column          datatype [NULL|NOT NULL] [CONSTRAINTS],
    CHANGE column columns datatype [NULL|NOT NULL] [CONSTRAINTS],
    DROP    column,
    ...
);

COMMIT

COMMIT is used to write a transaction to the database. See Tutorial 31, "Managing Transaction Processing," for more information.

• Input

COMMIT;

CREATE INDEX

CREATE INDEX is used to create an index on one or more columns. See Tutorial 26 for more information.

• Input

CREATE INDEX indexname
ON tablename (column [ASC|DESC], ...);

CREATE PROCEDURE

CREATE PROCEDURE is used to create a stored procedure. See Tutorial 28, "Working with Stored Procedures," for more information.

• Input

CREATE PROCEDURE procedurename( [parameters] )
BEGIN
...
END;

CREATE TABLE

CREATE TABLE is used to create new database tables. To update the schema of an existing table, use ALTER TABLE. See Tutorial 26 for more information.

• Input

CREATE TABLE tablename
(
   column    datatype    [NULL|NOT NULL]    [CONSTRAINTS],
   column    datatype    [NULL|NOT NULL]    [CONSTRAINTS],
   ...
);

CREATE USER

CREATE USER is used to add a new user account to the system. See Tutorial 33, "Managing Security," for more information.

• Input

CREATE USER username[@hostname]
[IDENTIFIED BY [PASSWORD] 'password'];

CREATE VIEW

CREATE VIEW is used to create a new view of one or more tables. See Tutorial 27, "Using Views," for more information.

• Input

CREATE [OR REPLACE] VIEW viewname
AS
SELECT ...;

DELETE

DELETE deletes one or more rows from a table. See Tutorial 25, "Updating and Deleting Data," for more information.

• Input

DELETE FROM tablename
[WHERE ...];

DROP

DROP permanently removes database objects (tables, views, indexes, and so forth). See Tutorials 26; 27; 28; 29, "Using Cursors"; 31; and 33 for more information.

• Input

INSERT

INSERT adds a single row to a table. See Tutorial 24, "Inserting Data," for more information.

• Input

INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);

INSERT SELECT

INSERT SELECT inserts the results of a SELECT into a table. See Tutorial 24 for more information.

• Input

INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];

ROLLBACK

ROLLBACK is used to undo a transaction block. See Tutorial 31 for more information.

• Input

ROLLBACK [ TO savepointname];

SAVEPOINT

SAVEPOINT defines a savepoint for use with a ROLLBACK statement. See Tutorial 31 for more information.

• Input

SAVEPOINT sp1;

SELECT

SELECT is used to retrieve data from one or more tables (or views). See Tutorial 9, "Retrieving Data"; Tutorial 10, "Sorting Retrieved Data"; and Tutorial 11, "Filtering Data," for more basic information. (Tutorials 4-17 all cover aspects of SELECT.)

• Input

SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];