Advanced

Views

Create View

CREATE VIEW view_name AS
SELECT column1, column2
FROM $TABLE_NAME
WHERE condition;

Create or Replace View

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM $TABLE_NAME
WHERE condition;

Select from View

SELECT * FROM view_name;

Show Views

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Drop View

DROP VIEW view_name;

Alter View

ALTER VIEW view_name AS
SELECT column1, column2, column3
FROM $TABLE_NAME
WHERE condition;

View Information

SHOW CREATE VIEW view_name;

---

Stored Procedures and Functions

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(50))
BEGIN
    -- procedure body
    SELECT column1 INTO param2 FROM $TABLE_NAME WHERE id = param1;
END //
DELIMITER ;

Call Stored Procedure

CALL procedure_name(value1, @output_var);

Create Function

DELIMITER //
CREATE FUNCTION function_name(param1 INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE result INT;
    SELECT COUNT(*) INTO result FROM $TABLE_NAME WHERE id = param1;
    RETURN result;
END //
DELIMITER ;

Use Function

SELECT function_name(value) FROM $TABLE_NAME;

Show Procedures

SHOW PROCEDURE STATUS;

Show Functions

SHOW FUNCTION STATUS;

Drop Procedure

DROP PROCEDURE procedure_name;

Drop Function

DROP FUNCTION function_name;

Show Procedure Code

SHOW CREATE PROCEDURE procedure_name;

Show Function Code

SHOW CREATE FUNCTION function_name;

---

Triggers

Create BEFORE INSERT Trigger

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE INSERT ON $TABLE_NAME
FOR EACH ROW
BEGIN
    -- trigger logic
    SET NEW.created_at = NOW();
END //
DELIMITER ;

Create AFTER INSERT Trigger

DELIMITER //
CREATE TRIGGER trigger_name
AFTER INSERT ON $TABLE_NAME
FOR EACH ROW
BEGIN
    -- trigger logic
    INSERT INTO audit_table (action, $TABLE_NAME, record_id) 
    VALUES ('INSERT', '$TABLE_NAME', NEW.id);
END //
DELIMITER ;

Create BEFORE UPDATE Trigger

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE UPDATE ON $TABLE_NAME
FOR EACH ROW
BEGIN
    -- trigger logic
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

Create AFTER UPDATE Trigger

DELIMITER //
CREATE TRIGGER trigger_name
AFTER UPDATE ON $TABLE_NAME
FOR EACH ROW
BEGIN
    -- trigger logic
    INSERT INTO audit_table (action, old_value, new_value) 
    VALUES ('UPDATE', OLD.column_name, NEW.column_name);
END //
DELIMITER ;

Create BEFORE DELETE Trigger

DELIMITER //
CREATE TRIGGER trigger_name
BEFORE DELETE ON $TABLE_NAME
FOR EACH ROW
BEGIN
    -- trigger logic
    INSERT INTO deleted_records (id, deleted_at) 
    VALUES (OLD.id, NOW());
END //
DELIMITER ;

Show Triggers

SHOW TRIGGERS;

Drop Trigger

DROP TRIGGER trigger_name;

---

Transactions

Start Transaction

START TRANSACTION;

Begin Transaction

BEGIN;

Commit Transaction

COMMIT;

Rollback Transaction

ROLLBACK;

Savepoint

SAVEPOINT savepoint_name;

Rollback to Savepoint

ROLLBACK TO savepoint_name;

Release Savepoint

RELEASE SAVEPOINT savepoint_name;

Auto Commit Off

SET autocommit = 0;

Auto Commit On

SET autocommit = 1;

Transaction Example

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

---

User Management and Security

Create User

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Create User with Host

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

Grant Specific Privileges

GRANT SELECT, INSERT, UPDATE ON database_name.$TABLE_NAME TO 'username'@'hostname';

Grant with Grant Option

GRANT SELECT ON database_name.* TO 'username'@'hostname' WITH GRANT OPTION;

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';

Revoke Specific Privileges

REVOKE SELECT, INSERT ON database_name.$TABLE_NAME FROM 'username'@'hostname';

Show Users

SELECT user, host FROM mysql.user;

Show Grants

SHOW GRANTS FOR 'username'@'hostname';

Show Current User

SELECT USER();

Change Password

ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

Drop User

DROP USER 'username'@'hostname';

Flush Privileges

FLUSH PRIVILEGES;

Create Role

CREATE ROLE 'role_name';

Grant Role

GRANT 'role_name' TO 'username'@'hostname';

Set Default Role

SET DEFAULT ROLE 'role_name' TO 'username'@'hostname';

---

Backup & Restore

Backup Database

mysqldump -u username -p database_name > backup.sql

Backup All Databases

mysqldump -u username -p --all-databases > all_backup.sql

Backup Specific Tables

mysqldump -u username -p database_name table1 table2 > tables_backup.sql

Backup with Structure Only

mysqldump -u username -p --no-data database_name > structure_only.sql

Backup with Data Only

mysqldump -u username -p --no-create-info database_name > data_only.sql

Restore Database

mysql -u username -p database_name < backup.sql

Restore from MySQL Console

SOURCE /path/to/backup.sql;

Create Database Before Restore

CREATE DATABASE database_name;
USE database_name;
SOURCE /path/to/backup.sql;

Backup with Compression

mysqldump -u username -p database_name | gzip > backup.sql.gz

Restore from Compressed Backup

gunzip < backup.sql.gz | mysql -u username -p database_name

---

Performance Tuning

Show Current Processes

SHOW PROCESSLIST;

Show Full Processlist

SHOW FULL PROCESSLIST;

Kill Process

KILL process_id;

Explain Query

EXPLAIN SELECT * FROM $TABLE_NAME WHERE condition;

Analyze Query Performance

EXPLAIN FORMAT=JSON SELECT * FROM $TABLE_NAME WHERE condition;

Show Table Status

SHOW TABLE STATUS FROM database_name;

Show Index Usage

SHOW INDEX FROM $TABLE_NAME;

Create Index

CREATE INDEX index_name ON $TABLE_NAME(column_name);

Create Composite Index

CREATE INDEX index_name ON $TABLE_NAME(column1, column2);

Create Unique Index

CREATE UNIQUE INDEX index_name ON $TABLE_NAME(column_name);

Drop Index

DROP INDEX index_name ON $TABLE_NAME;

Show Variables

SHOW VARIABLES LIKE 'variable_name';

Show Status

SHOW STATUS LIKE 'status_name';

Show Engine Status

SHOW ENGINE INNODB STATUS;

Optimize Table

OPTIMIZE TABLE $TABLE_NAME;

Analyze Table

ANALYZE TABLE $TABLE_NAME;

Check Table

CHECK TABLE $TABLE_NAME;

Repair Table

REPAIR TABLE $TABLE_NAME;

Show Query Cache Status

SHOW STATUS LIKE 'Qcache%';

Enable Query Cache

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 1048576;

Show Slow Query Log

SHOW VARIABLES LIKE 'slow_query_log';

Enable Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Show Binary Log

SHOW BINARY LOGS;

Show Master Status

SHOW MASTER STATUS;

Show Slave Status

SHOW SLAVE STATUS;