SQL Cheatsheet

Hereby one can find the basic SQL guide, putting it all together at once place.

SQL Statement types:

• Data Definition Language:

Used to define or modify data structure and objects such as TABLE

CREATE
DROP
ALTER
RENAME
TRUNCATE

• Data Manipulation Language:

INSERT
UPDATE
DELETE
SELECT

• Data Control Language:

Allows the database administrator to manage the rights that users have in a database:

GRANT

GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’

REVOKE

REVOKE type_of_permission ON database_name.table_name TO ‘username’@’localhost’

• Transaction Control Language:

Not all changes are saved automatically in the database, so this allows other users to have access to the modified version of the database.

COMMIT

UPDATE people
SET address = ‘xyz’
WHERE p_id = 5
COMMIT ;

ROLLBACK

UPDATE people
SET address = ‘xyz’
WHERE p_id = 5
ROLLBACK;

SQL Stored Routines

1. SQL Stored Procedures

DELIMITER $$
CREATE PROCEDURE procedure_name (IN in_param data_type, OUT out_param data_type)
BEGIN
SELECT * FROM
table_1 t
JOIN
table_2 e
ON t.id = e.id
WHERE t.id = in_param ;
END$$
DELIMITER ;  

A procedure is invoked using a CALL statement , and can only pass back values using output variables.

SET @out_param =0 ;
CALL database_name.procedure_name (in_param, @out_param) ;
SELECT out_param ;

2. User Defined Functions

DELIMITER $$
CREATE FUNCTION function_name( parameter data_type) RETURNS data_type
DECLARE variable_name data_type
BEGIN
SELECT * 
INTO parameter
FROM table_name t
JOIN 
table_name_2 e
ON t.id = e.id
WHERE t.id = paramter ;
RETURN variable_name ;
END$$
DELIMITER ;

To invoke the user defined function use:

SELECT function_name(parameter) ;

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements.

Stored Routines can be altered with the ALTER PROCEDURE and ALTER FUNCTION statements.

Thank you, to read through this and I hope that I have saved your time so that one doesn’t require to go through several links to find all the basic info about SQL, as I have put altogether.