# Advanced

## Views

*Create View*

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

*Create or Replace View*

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

*Select from View*

```sql
SELECT * FROM view_name;
```

*Show Views*

```sql
SHOW FULL TABLES WHERE Table_type = 'VIEW';
```

*Drop View*

```sql
DROP VIEW view_name;
```

*Alter View*

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

*View Information*

```sql
SHOW CREATE VIEW view_name;
```

***

## Stored Procedures and Functions

*Create Stored Procedure*

```sql
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*

```sql
CALL procedure_name(value1, @output_var);
```

*Create Function*

```sql
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*

```sql
SELECT function_name(value) FROM $TABLE_NAME;
```

*Show Procedures*

```sql
SHOW PROCEDURE STATUS;
```

*Show Functions*

```sql
SHOW FUNCTION STATUS;
```

*Drop Procedure*

```sql
DROP PROCEDURE procedure_name;
```

*Drop Function*

```sql
DROP FUNCTION function_name;
```

*Show Procedure Code*

```sql
SHOW CREATE PROCEDURE procedure_name;
```

*Show Function Code*

```sql
SHOW CREATE FUNCTION function_name;
```

***

## Triggers

*Create BEFORE INSERT Trigger*

```sql
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*

```sql
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*

```sql
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*

```sql
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*

```sql
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*

```sql
SHOW TRIGGERS;
```

*Drop Trigger*

```sql
DROP TRIGGER trigger_name;
```

***

## Transactions

*Start Transaction*

```sql
START TRANSACTION;
```

*Begin Transaction*

```sql
BEGIN;
```

*Commit Transaction*

```sql
COMMIT;
```

*Rollback Transaction*

```sql
ROLLBACK;
```

*Savepoint*

```sql
SAVEPOINT savepoint_name;
```

*Rollback to Savepoint*

```sql
ROLLBACK TO savepoint_name;
```

*Release Savepoint*

```sql
RELEASE SAVEPOINT savepoint_name;
```

*Auto Commit Off*

```sql
SET autocommit = 0;
```

*Auto Commit On*

```sql
SET autocommit = 1;
```

*Transaction Example*

```sql
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*

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

*Create User with Host*

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

*Grant Privileges*

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

*Grant Specific Privileges*

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

*Grant with Grant Option*

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

*Revoke Privileges*

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

*Revoke Specific Privileges*

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

*Show Users*

```sql
SELECT user, host FROM mysql.user;
```

*Show Grants*

```sql
SHOW GRANTS FOR 'username'@'hostname';
```

*Show Current User*

```sql
SELECT USER();
```

*Change Password*

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

*Drop User*

```sql
DROP USER 'username'@'hostname';
```

*Flush Privileges*

```sql
FLUSH PRIVILEGES;
```

*Create Role*

```sql
CREATE ROLE 'role_name';
```

*Grant Role*

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

*Set Default Role*

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

***

## Backup & Restore

*Backup Database*

```sql
mysqldump -u username -p database_name > backup.sql
```

*Backup All Databases*

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

*Backup Specific Tables*

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

*Backup with Structure Only*

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

*Backup with Data Only*

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

*Restore Database*

```sql
mysql -u username -p database_name < backup.sql
```

*Restore from MySQL Console*

```sql
SOURCE /path/to/backup.sql;
```

*Create Database Before Restore*

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

*Backup with Compression*

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

*Restore from Compressed Backup*

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

***

## Performance Tuning

*Show Current Processes*

```sql
SHOW PROCESSLIST;
```

*Show Full Processlist*

```sql
SHOW FULL PROCESSLIST;
```

*Kill Process*

```sql
KILL process_id;
```

*Explain Query*

```sql
EXPLAIN SELECT * FROM $TABLE_NAME WHERE condition;
```

*Analyze Query Performance*

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

*Show Table Status*

```sql
SHOW TABLE STATUS FROM database_name;
```

*Show Index Usage*

```sql
SHOW INDEX FROM $TABLE_NAME;
```

*Create Index*

```sql
CREATE INDEX index_name ON $TABLE_NAME(column_name);
```

*Create Composite Index*

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

*Create Unique Index*

```sql
CREATE UNIQUE INDEX index_name ON $TABLE_NAME(column_name);
```

*Drop Index*

```sql
DROP INDEX index_name ON $TABLE_NAME;
```

*Show Variables*

```sql
SHOW VARIABLES LIKE 'variable_name';
```

*Show Status*

```sql
SHOW STATUS LIKE 'status_name';
```

*Show Engine Status*

```sql
SHOW ENGINE INNODB STATUS;
```

*Optimize Table*

```sql
OPTIMIZE TABLE $TABLE_NAME;
```

*Analyze Table*

```sql
ANALYZE TABLE $TABLE_NAME;
```

*Check Table*

```sql
CHECK TABLE $TABLE_NAME;
```

*Repair Table*

```sql
REPAIR TABLE $TABLE_NAME;
```

*Show Query Cache Status*

```sql
SHOW STATUS LIKE 'Qcache%';
```

*Enable Query Cache*

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

*Show Slow Query Log*

```sql
SHOW VARIABLES LIKE 'slow_query_log';
```

*Enable Slow Query Log*

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

*Show Binary Log*

```sql
SHOW BINARY LOGS;
```

*Show Master Status*

```sql
SHOW MASTER STATUS;
```

*Show Slave Status*

```sql
SHOW SLAVE STATUS;
```