Crud Insert
INSERT INTO employees (id, name, position, salary) VALUES (1, 'Alice', 'Engineer', 75000.00);
Read (Find)
SELECT name, salary FROM employees WHERE position = 'Engineer';
Update
UPDATE employees SET salary = salary + 5000 WHERE position = 'Engineer';
Delete
DELETE FROM employees WHERE id = 1;
Dates CURDATE(),CURTIME() - YEAR(date),MONTH(date),DAY(date) - DATEDIFF(‘2025-07-01’,NOW())
Blocks Procedure DELIMITER //
CREATE PROCEDURE GetHighEarners(IN min_salary DECIMAL(10,2), OUT out_val INT)
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Error occurred during insert.';
END;
SELECT * FROM employees WHERE salary > min_salary;
SET @session_val := “some value”;
SET out_val = “some value”;
END //
DELIMITER ;
CALL GetHighEarners(60000);
SELECT @session_va , out_val
Function DELIMITER //
CREATE FUNCTION GetAnnualSalary(monthly DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC // or READS SQL DATA
BEGIN
RETURN monthly * 12;
END //
DELIMITER ;
SELECT name, GetAnnualSalary(salary / 12) AS annual FROM employees;
Cursor DELIMITER //
CREATE PROCEDURE ListAllNames()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE emp_name VARCHAR(100);
DECLARE cur CURSOR FOR SELECT name FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_name;
IF done THEN
LEAVE read_loop;
END IF;
SELECT emp_name;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
Trigger DELIMITER //
CREATE TRIGGER after_salary_update AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END IF;
END //
DELIMITER ;
View CREATE VIEW engineer_view AS SELECT name, salary FROM employees WHERE position = 'Engineer';
Index CREATE INDEX idx_position ON employees(position);
Users CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'secure123';
GRANT SELECT ON company.* TO 'report_user'@'localhost';
Exp/Import mysqldump -u root -p company > backup.sql
mysql -u root -p company < backup.sql
