SQL Language Reference
     

Description           General form
----------------  ------------------------------------------------------------
Create a table    CREATE TABLE tblname(columnS) [LIKE tblName] [SELECT ...]; 
                  where a column includes name, dataType[,length, [constraint]]
Create an index   CREATE [UNIQUE] INDEX index_name ON table_name 
                  (index_column_name [(length)] [ASC|DESC], ...)
Insert row        INSERT [INTO] table {[(columnS) VALUES (values)[,(values)]...
                         | SET column1 = expression1[,column2 = expression2]...}
                         
Retrieve data     SELECT columns [FROM table_source]
                  [INTO OUTFILE filePath [FIELDS TERMINATED BY string]] 
                  [WHERE conditions]
                  [GROUP BY groupType] [HAVING where_definition]
                  [ORDER BY orderType]
                  [LIMIT [offset,] row-count];
Change data       UPDATE tableName
                  SET column1 = expression1[,column2 = expression2]...
                  [WHERE condition]
Removing rowS     DELETE FROM tableName [WHERE condition]

Alter table structure
                  ALTER TABLE tableName alteration1 [, alteration2]...
                     with such alterations as:
                     ADD [COLUMN] columnDescription
                     ADD INDEX [index] (column, ...)
                     ADD PRIMARY KEY (column, ...)
                     ADD [CONSTRAINT name] FOREIGN KEY [fkName] 
                         reference definition
                     DROP PRIMARY KEY
                     DROP FOREIGN KEY fkName
                     DROP {INDEX | KEY} indexName
                     MODIFY [COLUMN] columnDescription                    
                     RENAME [TO] newTblName
                     
Removing table    DROP TABLE tblName;

Removing view     DROP VIEW viewName;


Summary Functions(for nonNull values in selected rows)
Description          General form
------------------   ----------------------------------------------------------
Average              AVG(expr)
Count                COUNT(expr)
Floor                FLOOR(expr)
Maximum              MAX(expr)
Minimum              MIN(expr)
Sum                  SUM(expr)

String Functions
Description          General form
-------------------  ---------------------------------------------------------
Concatenate strings  CONCAT((str1,str2,...) 
Extract substring    SUBSTRING(str,pos[,len])
Format number to 
  character string   FORMAT(number,decimal)
Find string position LOCATE(findStr,str,start) 
Pad characters       RPAD (str,length[,padStr] 
String length        LENGTH(str)


Date Functions
Description          General form
-------------------  ---------------------------------------------------------
Current date         CURDATE()
Add specified 
  interval to date   DATE_ADD(date, INTERVAL expression unit)
                     (expression unit: 1 DAY, 1 MONTH ...)
Difference between   DATEDIFF (curdate(), dateStr)

Subtract specified
  interval from date DATE_SUB(date, INTERVAL, expression unit)
  
(Re)format date      DATE_FORMAT(dateStr, formatCodes) where format codes for 
                       numeric day and month name would be '%d %M'
                       
[My]SQL
Description         General form
----------------    ------------------------------------------------------------
Create database     create database dbName;

Create a user       create user userName [identified by 'password'];

Delete a user       drop user userName;
  account & 
  privileges
  
Display users       select user,host[,password] from mysql.user;
  
Export database     mysqldump -u userName -p dbName > pathName; 

Import database     mysql -u userName -p dbName < pathname

Grant privileges    grant {ALL[PRIVILEGES] | privilegeList} on dbName.* to 
                      'userName'@'localhost' identified by 'password'
                      [with grant option];

                   (Privilege list for working with data: SELECT, INSERT, UPDATE,
                                   DELETE, EXECUTE
                       "           for modifying the database structure: CREATE, 
                                   ALTER, DROP, INDEX, CREATE VIEWs, CREATE 
                                   ROUTINE, ALTER ROUTINE, TRIGGER, EVENT)

Load table          load data local infile 'pathName' into table tableName
                    [fields terminated by 'delimiter'];
                       
Revoking privileges revoke {ALL | privilegeList} [, grant option] on 
                       [dbName.]table from userName@localhost 
                       
Retrieve userName   select user();
                       
Retrieve dbName     select database();

Set password        set password for userName[@hostName] = password('new-password');

Use a database      use dbName;

User name syntax    userName[@hostName]

Database Metadata:
Description          General form
-------------------  ---------------------------------------------------------
Display databases    SHOW DATABASES

Display privileges   SHOW GRANTS [FOR user]

Display tables       SHOW TABLES [FROM dbName]

Display table info.  SHOW CREATE TABLE tblName
                     also DESCRIBE tblName

Display view info.   SHOW CREATE VIEW viewName

Display index info.  SHOW INDEX FROM tblName

Transactions:
Description              General form
-----------------------  -------------------------------------------------------
Start a new transaction  START TRANSACTION
Undo a change            ROLLBACK [WORK] [TO SAVEPOINT savepoint_name]
Make change permanent    COMMIT [WORK]
Identify a savepoint     SAVEPOINT savepoint_name