SQL Cheat Sheet: Commands, Syntax, and Examples

SQL Cheat Sheet

Insert Data

INSERT INTO tabla(col1,col2) VALUES(val2,val2)
INSERT INTO tabla SET col1='va1',col2='val2'
INSERT INTO ON DUPLICATE KEY UPDATE ...
REPLACE INTO tabla VALUES ...

Update Data

UPDATE tabla SET col1='val1' WHERE ...
UPDATE tabla SET col1=(SELECT ...)

Delete Data

DELETE FROM tabla WHERE cond

Transactions

TRANSACTIONS:
START TRANSACTION;
INSERT INTO ...;
SAVEPOINT prueba;
INSERT INTO ...;
ROLLBACK TO SAVEPOINT prueba;
SET autocommit=0;
COMMIT;

Locking

LOCK TABLES tabla1 WRITE, tabla2 WRITE;
UNLOCK TABLES;

Stored Procedures

CREATE PROCEDURE nombre()
BEGIN
DECLARE a INT;
SELECT x INTO a ...;
END;
CALL procedure();

Functions

CREATE FUNCTION nombre(m VARCHAR(255))
RETURNS INT
BEGIN
DECLARE existe INT DEFAULT 1;
DECLARE tot, k INT;
DECLARE cur_1 CURSOR FOR SELECT ... WHERE marca=m;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET existe = 0; -- Or use NOT FOUND
-- Other handlers for different SQLSTATE codes
SET tot = 0;
SET k = 0;
OPEN cur_1;
FETCH cur_1 INTO k;
WHILE existe = 1 DO
SET tot = tot + k;
FETCH cur_1 INTO k;
END WHILE;
CLOSE cur_1;
RETURN tot;
END;

Triggers

DROP TRIGGER IF EXISTS nombre;
CREATE TRIGGER nombre AFTER UPDATE ON tabla FOR EACH ROW
BEGIN
DECLARE ...;
SELECT ...;
END;

Control Flow

IF ... THEN ... ELSEIF ... ELSE ... END IF;

CASE n
WHEN 1 THEN SET dia = 'lunes';
WHEN 2 THEN SET dia = 'martes';
ELSE ...
END CASE;

Date and String Functions

YEAR(NOW())
DATE_ADD(FECHA, INTERVAL VALOR TIPO)
INTERVAL '10-5' YEAR_MONTH
SUBSTRING_INDEX(campo, ',', -1)
UPPER(CONCAT(Apellido, ',', Nombre))
DATE_FORMAT(campo, '%d-%c-%Y')
CONCAT(CASE WHEN fechaalta > DATE_SUB(NOW(), INTERVAL 2 YEAR) THEN 'contrato temporal' ELSE 'contrato fijo' END)

Security and Maintenance

mysqlbinlog ficherolog
SHOW BINLOG EVENTS
mysqlcheck -a -uroot -p db
ANALYZE/OPTIMIZE TABLE tabla
CHECKSUM TABLE tabla
REPAIR TABLE tabla

Backup and Restore

-- Backup
1. LOCK TABLES ...
2. FLUSH TABLES;
3. SELECT * INTO OUTFILE 'c:\backup' FROM actuaciones;
-- Restore
LOAD DATA INFILE 'fichero' INTO TABLE tabla;
BACKUP TABLE tabla TO 'directorio';
mysqldump -uroot -p tabla > backup.sql
mysql -uroot -p tabla < backup.sql

User Management

SELECT CURRENT_USER();
SHOW GRANTS FOR user;

CREATE ROLE 'nombre';
GRANT permisos TO nombre_rol;

GRANT nombre_rol TO usuario;

Server Variables

SET GLOBAL sql_mode = 'modes,modes';

mysqld --initialize --console
mysqld --defaults-file="c:\program..."
SHOW VARIABLES LIKE 'error%';

max_binlog_size
long_query_time
SET DEFAULT ROLE 'rol' TO 'user';