insert into table_name (column_name, ..., column_name)
values (value, ..., value);

insert into musicians (musician_id, last_name, first_name, nickname)
values (2,'Lydon','John','Johnny Rotten');

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

update table_name
set column_name= value,
...,
column_name=value
where column_name=value;

update albums
set year=1994
where album_id=4;

update albums
set category='old music'
where year < 1980;

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
      

or:

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

delete from table_name
where column_name=value

delete from albums
where albums_id=4;

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]
                  

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]
                    

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]
                    

select column_name, ..., column_name
from table_name
where column_name=value;

select title
from albums
where category='industrial';

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula}
        [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula}
        [ASC | DESC] ,...]
      [LIMIT [offset,] row_count | row_count OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]
      

table_name [[AS] alias]
    [[USE INDEX (key_list)]
  | [IGNORE INDEX (key_list)]
  | [FORCE INDEX (key_list)]]

select bands.band_name
from bands,albums
where albums.category='alternative'
and bands.band_id=albums.band_id;

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference
    ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
  [UNION [ALL | DISTINCT]
   SELECT ...]

select title
from albums,
where band_id in
(select bands.band_id
from bands, band_musician
where band_musician.musician_id=2
and bands.band_id=band_musician.band_id);

CREATE [UNIQUE|FULLTEXT] INDEX index_name [index_type]
       ON tbl_name (index_col_name,...)

index_col_name:
        col_name [(length)] [ASC | DESC]

SHOW INDEX FROM tbl_name


DROP INDEX index_name ON tbl_name

create table table_name (
column_name column_type column_modifiers,
...,
column_name column_type column_modifiers);

create table musicians(
musician_id INT,
last_name CHAR(40),
first_name CHAR(40),
nickname CHAR(40));

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
      

or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];

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

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

index_col_name:
        col_name [(length)] [ASC | DESC]

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

table_options: table_option [table_option] ...

table_option:
    {ENGINE | TYPE} = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}
  | AUTO_INCREMENT = #
  | AVG_ROW_LENGTH = #
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | MAX_ROWS = #
  | MIN_ROWS = #
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }
  | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
  | UNION = (table_name,[table_name...])
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path to directory'
  | INDEX DIRECTORY = 'absolute path to directory'
  | DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...     (Some legal select statement)

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) 
REFERENCES tbl_name (index_col_name, ...) 
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] 
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

 CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB;

 CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), 
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB; 

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
   price DECIMAL,
   PRIMARY KEY(category, id)) ENGINE=INNODB;
   
CREATE TABLE customer (id INT NOT NULL,
   PRIMARY KEY (id)) ENGINE=INNODB;
  
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
   product_category INT NOT NULL,
   product_id INT NOT NULL,
   customer_id INT NOT NULL,
   PRIMARY KEY(no),
   INDEX (product_category, product_id),
   FOREIGN KEY (product_category, product_id)
   REFERENCES product(category, id)
   ON UPDATE CASCADE ON DELETE RESTRICT,
   INDEX (customer_id),
   FOREIGN KEY (customer_id)
   REFERENCES customer(id)) ENGINE=INNODB;

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

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

alter_specification:
    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  | ADD [COLUMN] (create_definition, create_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD FULLTEXT [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name create_definition
           [FIRST | AFTER column_name]
  | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col
  | CHARACTER SET character_set_name [COLLATE collation_name]
  | table_options
      

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

DROP DATABASE db_name 

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]
      

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user_name [, user_name ...]
 

Ejemplos en Postgresql:

Grant insert privilege to all users on table films :

GRANT INSERT ON films TO PUBLIC;

Grant all privileges to user manuel on view kinds :

GRANT ALL PRIVILEGES ON kinds TO manuel;

Ejemplos en Mysql

To revoke the GRANT OPTION privilege from a user, use a priv_type value of GRANT OPTION:

mysql> REVOKE GRANT OPTION ON ... FROM ...;

priv_type

ALL [PRIVILEGES]	Sets all simple privileges except GRANT OPTION
ALTER	Allows use of ALTER TABLE
CREATE	Allows use of CREATE TABLE
CREATE TEMPORARY TABLES	Allows use of CREATE TEMPORARY TABLE
DELETE	Allows use of DELETE
DROP	Allows use of DROP TABLE
EXECUTE	Allows the user to run stored procedures (MySQL 5.0)
FILE	Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX	Allows use of CREATE INDEX and DROP INDEX
INSERT	Allows use of INSERT
LOCK TABLES	Allows use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS	Allows use of SHOW FULL PROCESSLIST
REFERENCES	Not yet implemented
RELOAD	Allows use of FLUSH
REPLICATION CLIENT	Gives the right to the user to ask where the slave or master servers are
REPLICATION SLAVE	Needed for replication slaves (to read binary log events from the master)
SELECT	Allows use of SELECT
SHOW DATABASES	SHOW DATABASES shows all databases
SHUTDOWN	Allows use of mysqladmin shutdown
SUPER	Allows use of CHANGE MASTER, KILL thread, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE	Allows use of UPDATE
USAGE	Synonym for ``no privileges''
GRANT OPTION	Allows privileges to be granted

MySQL has four privilege levels:

Global level

Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* will grant and revoke only global privileges.

Database level

Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db.* and REVOKE ALL ON db.* will grant and revoke only database privileges.

Table level

Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db.table and REVOKE ALL ON db.table will grant and revoke only table privileges.

Column level

Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE you must specify the same columns that were granted.

 

 

 

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query 

CREATE VIEW comedies AS
           SELECT * FROM films WHERE kind = 'Comedy'; 

CREATE      

[DEFINER = { ser | CURRENT_USER }]      

TRIGGER trigger_nametrigger_timetrigger_event     

ON tbl_name FOR EACH ROW trigger_stmt 

DROP TRIGGER [schema_name.]trigger_name          

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); 

Query OK, 0 rows affected (0.03 sec) 

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account 

-> FOR EACH ROW SET @sum = @sum + NEW.amount; 

Query OK, 0 rows affected (0.06 sec)