My SQL Cheatsheet
Connecting to a database using the command-line client:
mysql -u [username] -p [database];
To exit, type exit.
To clear the console on Linux, type system clear.
Database Commands:
- To create a database, use: CREATE DATABASE [IF NOT EXISTS] db_name;
- To use a database, use: USE db_name;
- To drop a database, use: DROP DATABASE [IF EXISTS] db_name;
- To show available databases, use: SHOW DATABASE;
- To show tables in a database, use: SHOW TABLES;
DDL Commands:
- To create a new table, use: CREATE TABLE table_name (column1 datatype,
column2 datatype, …);
- To add, delete, or modify columns in an existing table, use: ALTER TABLE
Table_name ADD column_name datatype;
- To remove all rows from a table, use: TRUNCATE TABLE table_name;
- To destroy an object (database, table, index, or view), use: DROP TABLE
table_name;
- To rename a table, use: RENAME TABLE table_name1 to new_table_name1;
- To add a single-line comment, use: —Line1;
- To add a multi-line comment, use: /* Line1, Line2 */
DML Commands:
- To insert data into a table, use: INSERT INTO table_name (column1, column2,
column3, …) VALUES (value1, value2, value3, …);
- To select data from a table, use: SELECT column1, column2, … FROM table_name
[where condition];
- To update data in a table, use: UPDATE table_name SET column1 = value1,
column2 = value2, … WHERE condition;
- To delete data from a table, use: DELETE FROM table_name WHERE condition;
DELETE FROM table_name WHERE condition;
DCL Commands:
- GRANT: Gives access to users to the database.
- REVOKE: Removes granted permissions.
TCL Commands:
- COMMIT: Stores changes made by the user.
- ROLLBACK: Reverts changes of a transaction.
- SAVEPOINT: Marks a point of transaction for easy rollback.
How to delete a savepoint:
- RELEASE SAVEPOINT savepoint_name;
Indexes:
- CREATE INDEX: Creates an index on a table.
- DROP INDEX: Drops an index from a table.
Views:
- CREATE VIEW: Creates a view based on a query.
- SELECT FROM View: Retrieves data from a view.
- ALTER VIEW: Alters a view’s query.
4. Deleting a View:
DROP VIEW View_name;
1. Creating a Trigger:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
2. Dropping a Trigger:
DROP TRIGGER [IF EXISTS] trigger_name;
3. Showing a Trigger:
SHOW TRIGGERS;
4. Showing a Trigger using Pattern Matching:
SHOW TRIGGERS LIKE pattern;
Stored Procedures:
1. Creating a Stored Procedure:
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
2. Calling a Stored Procedure:
CALL sp_name;
3. Deleting a Stored Procedure:
DROP PROCEDURE sp_name;
Joins:
1. INNER JOIN:
SELECT * FROM TABLE1 INNER JOIN TABLE2 WHERE condition;
2. LEFT JOIN:
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
3. RIGHT JOIN:
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
4. CROSS JOIN:
SELECT select_list FROM TABLE1 CROSS JOIN TABLE2;
When a MySQL statement is processed, a memory area called a context area is
created, and a cursor is a pointer to it.
There are two types of cursors: implicit and explicit.
Explicit cursors are used when executing a SELECT statement that returns more
than one row.
The cursor works in four stages:
1. Declaration: DECLARE cursor_name CURSOR FOR SELECT_statement;
2. Open: OPEN cursor_name;
3. Fetch: FETCH cursor_name INTO variables list;
4. Close: CLOSE cursor_name;
