SQL Cheat Sheet: Commands, Syntax, and Examples
Classified in Technology
Written at on English with a size of 3.5 KB.
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';