str[bits]

May 25, 2005

Types of SQL Statements Supported by MySQL

Filed under: MySQL

SELECTING, CREATING, DROPPING, AND ALTERING DATABASES

USE
CREATE DATABASE
DROP DATABASE
ALTER DATABASE

CREATING, ALTERING, AND DROPPING TABLES AND INDEXES

CREATE TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
ALTER TABLE

GETTING INFORMATION ABOUT DATABASES AND TABLES

DESCRIBE
SHOW

RETRIEVING INFORMATION FROM TABLES

SELECT
UNION

PERFORMING TRANSACTIONS

SET AUTOCOMMIT
START TRANSACTION
COMMIT
ROLLBACK

MODIFYING INFORMATION IN TABLES

DELETE
INSERT
LOAD DATA
REPLACE
UPDATE

ADMINISTRATIVE STATEMENTS

FLUSH
GRANT
REVOKE

8 Comments »

The URI to TrackBack this entry is: http://str.blogsome.com/2005/05/25/types-of-sql-statements-supported-by-mysql/trackback/

  1. Creating Databases

    To create a database, use a CREATE DATABASE statement:

    CREATE DATABASE db_name;

    Comment by str — May 25, 2005 @ 1:36 pm

  2. In addition:

    CREATE DATABASE supports several optional clauses.

    CREATE DATABASE [IF NOT EXISTS] db_name
    [CHARACTER SET charset] [COLLATE collation];

    Comment by str — May 25, 2005 @ 1:40 pm

  3. Dropping Databases
    DROP DATABASE db_name;

    Comment by str — May 25, 2005 @ 1:41 pm

  4. Altering Databases

    The ALTER DATABASE statement makes changes to a database’s global attributes. Currently, the only such attributes are the default character set and collation:

    ALTER DATABASE db_name [CHARACTER SET charset] [COLLATE collation];

    Comment by str — May 25, 2005 @ 1:43 pm

  5. Checking Which Storage Engines Are Available
    To see a list of available storage engines, use the SHOW ENGINES statement:

    mysql> SHOW ENGINES;

    Comment by str — May 25, 2005 @ 2:02 pm

  6. Temporary Tables
    You can use CREATE TEMPORARY TABLE to create temporary tables that disappear automatically when your connection to the server terminates. This is handy because you don’t have to bother issuing a DROP TABLE statement to get rid of the table, and the table doesn’t hang around if your connection terminates abnormally.

    CREATE TEMPORARY TABLE new_tbl_name LIKE tbl_name;

    Comment by str — May 25, 2005 @ 2:12 pm

  7. Creating Tables from Other Tables or Query Results
    MySQL provides two statements for creating new tables from other tables or from query results. These statements have differing advantages and disadvantages:

    CREATE TABLE … LIKE creates a new table as an empty copy of the original one. It copies the original table structure exactly so that each column is preserved with all of its attributes and the index structure also is copied. However, the new table is empty, so if you want to populate it, a second statement is needed (such as INSERT INTO … SELECT). Also, CREATE TABLE … LIKE cannot create a new table from a subset of the original table’s columns, and it cannot use columns from any other table but the original one.

    CREATE TABLE … SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this statement does not copy all column attributes such as default values or AUTO_INCREMENT. Nor does creating a table by selecting data into it automatically copy any indexes from the original table, because result sets are not themselves indexed. On the other hand, CREATE TABLE … SELECT can both create and populate the new table in a single statement. It also can create a new table using a subset of the original table and include columns from other tables or columns created as the result of expressions.

    Comment by str — May 25, 2005 @ 2:15 pm

  8. Dropping Tables
    DROP TABLE tbl_name;

    Comment by str — May 25, 2005 @ 2:18 pm

RSS feed for comments on this post.

Leave a comment

Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>























Get free blog up and running in minutes with Blogsome | Theme designs available here