MySQL – cheat table | davejingtian.org

The first book I have read about SQL is Ben Forta’s ‘MySQL Crash Course’. I like Ben’s writing style where each chapter only contains few but important things worth doing hands-on trial and remembering. Generally, below is some notes after my reading this excellent book. Thanks Ben and his fabulous book!

# Installation
rpm -i MySQL-server-5.5.24-1.rhel5.i386.rpm
rpm -i MySQL-client-5.5.24-1.rhel5.i386.rpm

# MySQL Crash Courses
http://forta.com/books/0672327120/

# Password
/usr/bin/mysqladmin -u root password ‘new-password’

# Startup
mysql -u root -p

# MySQL Commands
CREATE DATABASE ‘dbName’;
SHOW DATABASES;
USE ‘dbName’;
SHOW TABLES;
SHOW COLUMNS FROM ‘tableName’;
DESCRIBE ‘tableName’;
help SHOW;
SELECT ‘columnName’ FROM ‘tableName’;
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName’;
SELECT * FROM ‘tableName’;
SELECT DISTINCT ‘columnName’ FROM ‘tableName’;
SELECT ‘columnName’ FROM ‘tableName’ LIMIT ‘num’;
SELECT ‘columnName’ FROM ‘tableName’ LIMIT ‘lineIndex’, ‘num’;
SELECT ‘columnName’ FROM ‘tableName’ LIMIT ‘num’ OFFSET ‘lineIndex’;
SELECT ‘tableName’.’columnName’ FROM ‘dbName’.’tableName’;
SELECT ‘columnName’ FROM ‘tableName’ ORDER BY ‘columnName’;
SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ ORDER BY ‘columnName2’, ‘columnName3’;
SELECT ‘columnName1’columnName2’, ‘columnName3’ FROM ‘tableName’ ORDER BY ‘columnName2’ DESC, ‘columnName3’;
SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ ORDER BY ‘columnName2’ DESC, ‘columnName3’ DESC;
SELECT ‘columnName’ FROM ‘tableName’ ORDER BY ‘columnName’ DESC LIMIT ‘num’;
SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ WHERE ‘condition’ ORDER BY ‘columnName3’;
SELECT ‘columnName1’ FROM ‘tableName’ WHERE ‘columnName2’ IS NULL;
SELECT ‘columnName1’, ‘columnName2’, ‘columenName3’ FROM ‘tableName’ WHERE ‘condition1’ AND ‘condition2’;
SELECT ‘columnName1’, ‘columnName2’, ‘columenName3’ FROM ‘tableName’ WHERE ‘condition1’ OR ‘condition2’;
SELECT ‘columnName1’, ‘columnName2’, ‘columenName3’ FROM ‘tableName’ WHERE ‘columnName1’ IN (‘value1’, ‘value2’);
SELECT ‘columnName1’, ‘columnName2’, ‘columenName3’ FROM ‘tableName’ WHERE ‘columnName1’ NOT IN (‘value1’, ‘value2’);
SELECT ‘columnName’ FROM ‘tableName’ WHERE ‘columnName’ LIKE ‘pattern’;
SELECT ‘columnName’ FROM ‘tableName’ WHERE ‘columnName’ REGEXP ‘pattern’;
SELECT Func(‘columnName1’, ‘columnName2’, …) AS ‘columnNameNew’ FROM ‘tableName’ ORDER BY ‘columnName1’;
SELECT ‘columnName’, Func() AS ‘columnNameNew’ FROM ‘tableName’ GROUP BY ‘columnName’;
SELECT ‘columnName’, Func() AS ‘columnNameNew’ FROM ‘tableName’ GROUP BY ‘columnName’ WITH ROLLUP;
SELECT ‘columnName’, COUNT(*) FROM ‘tableName’ GROUP BY ‘columnName’ HAVING COUNT(*) >= 2;
SELECT ‘columnName’, COUNT(*) FROM ‘tableName’ WHERE ‘condition’ GROUP BY ‘columnName’ HAVING COUNT(*) >= 2;
SELECT ‘columnName’, COUNT(*) FROM ‘tableName’ WHERE ‘condition’ GROUP BY ‘columnName’ HAVING COUNT(*) >= 2 ORDER BY ‘columnName’;
SELECT ‘columnName1’ FROM ‘tableName1’ WHERE ‘columnName2’ IN (SELECT ‘columnName2’ FROM ‘tableName2’ WHERE ‘columnName3’ IN (
        SELECT ‘columnName3’ FROM ‘tableName3’ WHERE ‘condition’);
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName1’, ‘tableName2’ WHERE ‘tableName1’.’columnName3′ = ‘tableName2’.’columnName3′;
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName1’ INNER JOIN ‘tableName2’ ON ‘tableName1’.’columnName3′ = ‘tableName2’.’columnName3′;
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName1’ AS ‘name1’, ‘tableName2’ AS ‘name2’ WHERE ‘name1’.’columnName3′ = ‘name2’.’columnName3′;
SELECT ‘name1’.’columnName1′, ‘name1’.’columnName2′ FROM ‘tableName’ AS ‘name1’, ‘tableName’ AS ‘name2’ WHERE
        ‘name1’.’columnName3′ = ‘name2’.’columnName3′ AND ‘name2’.’columnName4′ = ‘daveti’;
SELECT ‘name1’.*, ‘name2’.’columnName2′ FROM ‘tableName1’ AS ‘name1’, ‘tableName2’ AS ‘name2’ WHERE ‘name1’.’columnName3′ = ‘name2’.’columnNam3′;
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName1’ LEFT OUTER JOIN ‘tableName2’ ON ‘tableName1’.’columnName3′ = ‘tableName2’.’columnName3′;
SELECT ‘columnName1’, ‘columnName2’ FROM ‘tableName1’ RIGHT OUTER JOIN ‘tableName2’ ON ‘tableName1’.’columnName3′ = ‘tableName2’.’columnName3′;
SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ WHERE ‘condition1’ UNION
        SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ WHERE ‘condition2’ ORDER BY ‘columnName1’, ‘columnName2’;
SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ WHERE ‘condition1’ UNION ALL
        SELECT ‘columnName1’, ‘columnName2’, ‘columnName3’ FROM ‘tableName’ WHERE ‘condition2’;
SELECT ‘columnName’ FROM ‘tableName’ WHERE Match(‘columanName’) Against(‘matchingString’);
SELECT ‘columnName’, Match(‘columnName’ Against(‘matchingString) AS ‘columnNameNew’ FROM ‘tableName’;
SELECT ‘columnName’ FROM ‘tableName’ WHERE Match(‘columnName’) Against(‘matchingString’ WITH QUERY EXPANSION);
SELECT ‘columnName’ FROM ‘tableName’ WHERE Match(‘columnName’) Against(‘matchingString’ IN BOOLEAN MODE);
SELECT ‘columnName’ FROM ‘tableName’ WHERE Match(‘columnName’) Against(‘booleanOperator_matchingString’ IN BOOLEAN MODE);
INSERT INTO ‘tableName’ VALUES(‘value1’, ‘value2’, …, ‘valueN’);
INSERT INTO ‘tableName'(‘columnName1’, ‘columnName2’, …, ‘columnNameN’) VALUES (‘value1’, ‘value2’, …, ‘valueN’);
INSERT LOW_PRIORITY INTO ‘tableName'(‘columnNameN’, ‘columnNameN-1’, …, ‘columnName1’) VALUES (‘valueN’, ‘valueN-1’, …, ‘value1’);
INSERT INTO ‘tableName'(‘columnName1’, ‘columnName2’, …, ‘columnNameN’)
        VALUES (‘value1’, ‘value2’, …, ‘valueN’), (‘value11’, ‘value22’, …, ‘valueNN’);
INSERT INTO ‘tableName1′(‘columnName1’, ‘columnName2’, …, ‘columnNameN’)
        SELECT(‘columnName11’, ‘columnName22’, …, ‘columnNameNN’) FROM ‘tableName2’ WHERE ‘condition’;
UPDATE ‘tableName’ SET ‘columnName1’ = ‘value1’, ‘columnName2’ = ‘value2’ WHERE ‘condition’;
UPDATE IGNORE ‘tableName’ SET ‘columnName1’ = NULL, ‘columnName2’ = ‘value2’ WHERE ‘condition’;
DELETE FROM ‘tableName’ WHERE ‘condition’;
CREATE TABLE tableName
{
        columnName1     type1   NOT NULL        AUTO_INCREMENT,
        columnName2     type2   NULL,
        columnName3     type3   NULL,
        columnName4     type4   NOT NULL        DEFAULT 1,
        …
        PRIMARY KEY (columnName1)
}ENGINE=engineName;
ALTER TABLE tableName ADD columnName type;
ALTER TABLE tableName DROP COLUMN columnName;
ALTEE TABLE tableName1 ADD CONSTRINT fk_tableName1_tableName2 FOREIGN KEY (columnName1) REFERENCES tableName2 (columnName2);
DROP TABLE tableName;
RENAME TABLE tableName1 TO tableName2;
CREATE VIEW viewName AS SELECT …;
SHOW CREATE VIEW viewName;
DROP VIEW viewName;
DELIMITER //
CREATE PROCEDURE procedureName( args…)
BEGIN
        SQL statements…
END //
DELIMITER ;
CALL procedureName( args…);
DELIMITER //
CREATE PROCEDURE productpricing2(
        OUT pl DECIMAL(8, 2),
        OUT ph DECIMAL(8, 2),
        OUT pa DECIMAL(8, 2))
BEGIN
        SELECT Min(prod_price)
        INTO pl
        FROM products;
        SELECT Max(prod_price)
        INTO ph
        FROM products;
        SELECT Avg(prod_price)
        INTO pa
        FROM products;
END //
DELIMITER ;
CALL productpricing2(@pl, @ph, @pa);
SELECT @pl, @ph, @pa;
DELIMITER //
CREATE PROCEDURE ordertotal(
        IN onumber INT,
        OUT ototal DECIMAL(8, 2))
BEGIN
        SELECT Sum(item_price*quantity)
        FROM orderitems
        WHERE order_num = onumber
        INTO ototal;
END //
DELIMITER ;
CALL ordertotal(20005, @ot);
SELECT @ot;
DROP PROCEDURE procedureName;
DROP PROCEDURE IF EXISTS procedureName;
SHOW CREATE PROCEDURE procedureName;
SHOW PROCEDURE STATUS;
DELIMITER //
CREATE TRIGGER triggerName BEFORE|AFTER DELETE|UPDATE|INSERT ON tableName
        FOR EACH ROW
        BEGIN
                SET|INSERT|UPDATE|…;
        END //
DELIMITER ;
DROP TRIGGER triggerName;
START TRANSACTION;
ROLLBACK|COMMIT|SAVEPOINT;
SHOW CHARACTER SET;
SHOW COLLATION;
SHOW VARIABLES;
CREATE USER userName IDENTIFIED BY ‘password’;
RENAME USER userName TO userName2;
DROP USER userName;
SHOW GRANTS FOR userName;
GRANT SELECT ON dbName.* TO userName;
REVOKE SELECT ON dbName.* FROM userName;
SET PASSWORD (FOR userName) = Password(‘new password’);
FLUSH TABLES;
ANALYZE TABLE tableName;
CHECK TABLE tableName;
REPAIR TABLE tableName;
OPTIMIZE TABLE tableName;
SHOW STATUS;
SHOW PROCESSLIST;

# SQL Syntax
help SELECT;
Name: ‘SELECT’
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr …]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], … [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE ‘file_name’
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE ‘file_name’
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]