SQL -- Structured Query Language

Syntaxe

Créer une base de données

CREATE DATABASE db_name

Sélectionner une base de donné

MySQl : USE db_name

Détruir une base de données

SQL : DROP DATABASE db_name

MySQL : DROP DATABASE [IF EXISTS] db_name

Créer une table

MySQL :

CREATE TABLE tbl_name (create_definition,...)

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] KEY(index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Modifier une table

MySQL :

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX key_name
  or    RENAME [AS] new_tbl_name

Optimiser une table

MySQl : OPTIMISE TABLE tbl_name

Détruire une table

SQL1 ne permet pas de supprimer des tables.

SQL2 : DROP TABLE tbl_name

MySQL : DROP TABLE [IF EXISTS] tbl_name [, tbl_name...]

Insérer des données dans une table

MySQL :

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  INSERT [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY] [INTO] tbl_name SET col_name=expression,
        col_name=expression,...

Insérer des données dans une table depuis un fichier externe

MySQL :

LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY "]
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [(col_name,...)]

Modifier des données dans une table

MySQL :

REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...)
or  REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY] [INTO] tbl_name SET col_name=expression,
        col_name=expression,...

Mettre à jour les données d'une table

MySQL :

UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
    [WHERE where_definition]

Effacer des donnés d'une table

MySQL : DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]

Effectuer une requète

MySQL :

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
    select_expression,...
    [INTO OUTFILE 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]

Effectuer une jointure

MySQL :

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

Obtenir les informations d'une base de données

MySQL :

SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW PROCESSLIST

Obtenir les informations d'une requètes

MySQL : EXPLAIN SELECT select_options

Documents de référence

Autres documents

Site de référence

Forum de discussions

Logiciels


Christophe Merlet redfox@redfoxcenter.org	©Tous droits réservés 9 août 1999