SQL Commands, Functions, Procedures, and Triggers
SQL Commands
User Management
- Create user nombre_user@localhost;
- SELECT user FROM user;
- GRANT ALL ON nombre_BD.* TO nombre_user@localhost;
- GRANT UPDATE, SELECT, INSERT ON nombre_BD.* TO nombre_user@localhost;
- REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘nombre_user’@’localhost’;
- DELETE FROM mysql.user WHERE user = ‘nombre_user’;
Database Operations
- SHOW DATABASES;
- CREATE DATABASE nombre_BD;
- USE nombre_BD;
- DROP DATABASE nombre_BD;
Table Operations
- SHOW TABLES;
- DESCRIBE nombre; //Details of the table
- DROP TABLE nombre_tabla;
- CREATE TABLE IF NOT EXISTS nombre_tabla(id INT KEY, nombre VARCHAR(20) NOT NULL) ENGINE=InnoDB;
Data Types: DATE, YEAR, DOUBLE, VARCHAR(), etc.
Attribute Types: AUTO_INCREMENT, DEFAULT ‘valor_defecto’, UNIQUE NOT NULL [alternative key], PRIMARY KEY(atributo1, atributo2), atributo1 NOT NULL, UNIQUE(atributo1, atributo2), INDEX(atributo) [orders that attribute for faster search], FOREIGN KEY(atributo_tabla) REFERENCES nombre_TABLA_perteneciente(nombre_original)
- […] ON DELETE RESTRICT: Does not allow deleting the field if it is in use.
- […] ON UPDATE CASCADE: Deletes the field and its saved attributes.
- […] ON DELETE SET NULL: Deletes the field and sets the value to null.
- […] ON UPDATE NO ACTION: Same effect as restrict.
- […] ON DELETE SET DEFAULT: Sets a default value.
- SHOW ENGINES;
- ALTER TABLE nombre_tabla ENGINE=MyISAM;
- ALTER TABLE nombre_tabla ADD FOREIGN KEY (clave_ajena_local) REFERENCES tabla_clave_ajena(clave_ajena);
- ALTER TABLE nombre_tabla ADD COLUMN atributo INTEGER NOT NULL;
Data Manipulation
- INSERT INTO nombre_tabla VALUES(85, “pepe”), (86, “miguel”);
- INSERT INTO nombre_tabla(id, nombre) VALUES(85, “pepe”), (86, “miguel”);
- DELETE FROM nombre_tabla WHERE atributo=’xxx’;
- DELETE FROM nombre_tabla WHERE atributo LIKE ‘%xxx%’ OR … AND …;
- UPDATE nombre_tabla SET atributo=’xxx’;
- UPDATE nombre_tabla SET atributo1=’xxx’ WHERE atributo2 LIKE ‘%yyy%’;
Views
- CREATE VIEW nombre_vista AS SELECT * FROM nombre_tabla;
- SELECT * FROM nombre_vista;
- ALTER VIEW nombre_vista AS SELECT * FROM nombre_tabla;
- DROP VIEW nombre_vista1, nombre_vista2;
- SHOW CREATE VIEW nombre_vista;
Functions, Procedures, and Triggers
SET @nombre_variable:=x; //Assign value
SET @v1=x, @v2=y; //Assign value
SELECT @v1; //Show value
SELECT @v1:=(@v2:=1)+(@+3:=4); //Assign calculated value
Functions
DROP FUNCTION IF EXISTS mayor;
DELIMITER //
CREATE FUNCTION mayor (num1 INT, num2 INT)
RETURNS INT
BEGIN
DECLARE max INTEGER;
SET max=num2;
IF num1>num2 THEN SET max=num1;
END IF;
RETURN max;
END//
DELIMITER ;
Procedure
DROP PROCEDURE IF EXISTS tipo;
DELIMITER //
CREATE PROCEDURE tipo(IN co VARCHAR(9), OUT tip VARCHAR(10))
BEGIN
SELECT ‘MEMORIA’ FROM memoria WHERE co=cod UNION
SELECT ‘CAMARA’ FROM camara WHERE co=cod UNION
SELECT ‘OBJETIVO’ FROM objetivo WHERE co=cod UNION
SELECT ‘TELEVISION’ FROM tv WHERE co=cod UNION
SELECT ‘PAQUETE’ FROM pack WHERE co=cod INTO tip;
END//
DELIMITER ;
Trigger
1. Create a trigger that, when adding a new line to the order, decrements the stock of the item.
DROP TRIGGER IF EXISTS existencias;
DELIMITER //
CREATE TRIGGER existencias BEFORE INSERT ON linped
FOR EACH ROW
BEGIN
DECLARE cant INT;
SELECT s.disponible FROM stock s WHERE s.articulo=NEW.articulo INTO cant;
IF cant>NEW.cantidad THEN
UPDATE stock SET disponible=disponible-NEW.cantidad WHERE articulo=NEW.articulo;
ELSE
DECLARE msg VARCHAR(250);
SET msg=”The requested quantity of the item is not available.”;
SIGNAL SQLSTATE ‘4500’ SET MESSAGE_TEXT=msg;
END IF;
END//
DELIMITER ;
2. Create a trigger that, when modifying an order line, updates the stock.
DROP TRIGGER IF EXISTS existencias;
DELIMITER //
CREATE TRIGGER existencias BEFORE UPDATE ON linped
FOR EACH ROW
BEGIN
DECLARE dif INT;
DECLARE cant INT;
SET dif=OLD.cantidad-NEW.cantidad;
SELECT disponible FROM stock s WHERE NEW.articulo=s.articulo INTO cant;
IF cant<dif THEN
DECLARE msg VARCHAR(250);
SET msg=”The requested quantity of the item is not available.”;
SIGNAL SQLSTATE ‘4500’ SET MESSAGE_TEXT=msg;
ELSEIF cant <> 0 THEN
UPDATE stock SET disponible=disp+dif WHERE articulo=NEW.articulo;
END IF;
END IF;
END//
DELIMITER ;
Transaction
DROP PROCEDURE IF EXISTS marcas;
DELIMITER //
CREATE PROCEDURE marcas(IN m VARCHAR(15))
BEGIN
DECLARE existe INT;
DECLARE error INT;
START TRANSACTION;
INSERT INTO articulo VALUES (‘Z0001′,’PowerShot SX30 IS’,’415.00′,m,NULL,’5291p.jpg’,NULL);
SET error=@error;
SELECT EXISTS(SELECT 1 FROM marca WHERE marca=m) INTO existe;
IF existe=0 OR error>0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END//
DELIMITER ;
SELECT Queries
– Find out if any teacher teaches all subjects:
SELECT nombre FROM profesores p WHERE NOT EXISTS(SELECT codigo FROM asignaturas a WHERE NOT EXISTS(SELECT asignatura FROM imparte i WHERE i.asignatura=a.codigo AND p.dni=i.dni));
– Users who have requested orders worth more than 10000 (per order) or who have requested more than 5 different items among all their orders:
SELECT usuario
FROM linped l, pedido p WHERE l.numpedido=p.numpedido
GROUP BY p.numpedido, usuario
HAVING SUM(cantidad*precio)>10000
UNION
SELECT usuario
FROM linped l, pedido p WHERE l.numpedido=p.numpedido
GROUP BY usuario
HAVING COUNT(DISTINCT articulo)>5;
– Email, first name, and last name of users who have requested televisions but have never requested cameras:
SELECT email, nombre, apellidos
FROM usuario
WHERE email IN
(SELECT usuario FROM pedido p, linped l, tv
WHERE p.numpedido=l.numpedido AND articulo=cod)
AND email NOT IN
(SELECT usuario FROM pedido p, linped l, camara
WHERE p.numpedido=l.numpedido AND articulo=cod);
– All items, even if they are repeated, that appear in a pack or a basket:
SELECT p.articulo, a.nombre
FROM ptienea p
JOIN articulo a ON (p.articulo = a.cod)
UNION ALL
SELECT c.articulo, a.nombre
FROM cesta c
JOIN articulo a ON (c.articulo = a.cod);