MariaDB

User

-- create a database named newdb
CREATE DATABASE newdb

-- create new user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- grant all permissions to newuser connect from localhost
GRANT ALL PRIVILEGES ON `database`.[*|table] TO 'newuser'@'localhost';

-- grant specific permission
GRANT SELECT, INSERT, UPDATE, DELETE ON `database`.[*|table] TO `newuser`@`localhost`

-- allow user to execute stored procedure
GRANT EXECUTE ON PROCEDURE <procedure name> TO `newuser`@`localhost`;

-- reload permission after permission(s) granted
FLUSH PRIVILEGES;

Common commands

-- list all databases
SHOW DATABASES

-- switch database
USE <database>

-- list all tables
SHOW TABLES

-- set time zone
SET GLOBAL time_zone = 'Asia/Kuala_Lumpur';

Stored procedure

Example

DELIMITER //
CREATE PROCEDURE sp_next_no (IN seq_type VARCHAR(10), IN prefix VARCHAR(10)) 
BEGIN
	DECLARE next_no DECIMAL(15,0);
	UPDATE seq SET curr_no = curr_no + 1 WHERE `type` = seq_type;
	SELECT curr_no INTO next_no FROM seq WHERE `type` = seq_type;
	IF next_no IS NULL THEN
		INSERT INTO seq(`type`, curr_no, prefix) VALUES (seq_type, 1, prefix);
		SELECT 1 INTO next_no;
	END IF;
	SELECT next_no;
END//
DELIMITER ;