MySQL Helper

Mysql queries examples

-- MySQL Database and User Management Examples

-- CREATE DATABASES
CREATE DATABASE myapp_db;
CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CREATE USERS
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'securepass';
CREATE USER 'specifichost'@'192.168.1.100' IDENTIFIED BY 'hostpass';

-- GRANT PERMISSIONS
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'remoteuser'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- CREATE TABLES
CREATE TABLE users (
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(50) NOT NULL UNIQUE,
   email VARCHAR(100) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER TABLES
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) NOT NULL;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO app_users;

-- ALTER USERS
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'newpassword';
RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';

-- ALTER DATABASES
ALTER DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- DELETE/DROP OPERATIONS
DROP TABLE IF EXISTS temp_table;
DROP DATABASE IF EXISTS old_db;
DROP USER 'unwanteduser'@'localhost';
REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'appuser'@'localhost';

-- VIEW PERMISSIONS
SHOW GRANTS FOR 'appuser'@'localhost';
SELECT user, host FROM mysql.user;
SHOW DATABASES;

-- Create simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `students` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated` timestamp NULL DEFAULT NULL,
    `first_name` varchar(255) NOT NULL,
    `last_name` varchar(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Create another simple table
-- Status values: 0=deleted, 1=inactive, 2=active, >=2 activate and different use cases
CREATE TABLE `courses` (
    `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `status` tinyint(1) NOT NULL DEFAULT 2,
    `code` VARCHAR(32) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL,
    `description` text
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Create ManyToMany connection between the tables
CREATE TABLE `course_membership` (
`id_student` INT UNSIGNED NOT NULL,
`id_course` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id_student`, `id_course`),
CONSTRAINT `constr_course_membership_student_fk`
    FOREIGN KEY `student_fk` (`id_student`) REFERENCES `students` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `constr_course_membership_course_fk`
    FOREIGN KEY `course_fk` (`id_course`) REFERENCES `courses` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;


-- Create a test database
CREATE DATABASE IF NOT EXISTS all_types_demo;
USE all_types_demo;

-- Drop the table if it exists
DROP TABLE IF EXISTS all_data_types;

-- Create a table with all MySQL 8.0 data types
CREATE TABLE all_data_types (
    -- Numeric Types
    id                INT AUTO_INCREMENT PRIMARY KEY,
    tiny_int_col      TINYINT,                    -- 1 byte signed integer (-128 to 127)
    small_int_col     SMALLINT,                   -- 2 bytes
    medium_int_col    MEDIUMINT,                  -- 3 bytes
    int_col           INT,                        -- 4 bytes
    big_int_col       BIGINT,                     -- 8 bytes
    decimal_col       DECIMAL(10,2),              -- Exact fixed-point number
    numeric_col       NUMERIC(8,3),               -- Alias for DECIMAL
    float_col         FLOAT(7,4),                 -- Approximate single precision
    double_col        DOUBLE(16,8),               -- Approximate double precision
    real_col          REAL,                       -- Alias for DOUBLE
    bit_col           BIT(8),                     -- Bit field

    -- Date and Time Types
    date_col          DATE,                       -- 'YYYY-MM-DD'
    datetime_col      DATETIME(6),                -- 'YYYY-MM-DD HH:MM:SS[.fraction]'
    timestamp_col     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    time_col          TIME(3),                    -- 'HH:MM:SS[.fraction]'
    year_col          YEAR,                       -- 'YYYY'

    -- String Types
    char_col          CHAR(10),                   -- Fixed-length string (0–255)
    varchar_col       VARCHAR(255),               -- Variable-length string
    binary_col        BINARY(16),                 -- Fixed-length binary
    varbinary_col     VARBINARY(255),             -- Variable-length binary
    tiny_blob_col     TINYBLOB,                   -- Binary large object (max 255 bytes)
    blob_col          BLOB,                       -- Max 65,535 bytes
    medium_blob_col   MEDIUMBLOB,                 -- Max 16,777,215 bytes
    long_blob_col     LONGBLOB,                   -- Max 4GB
    tiny_text_col     TINYTEXT,                   -- Max 255 chars
    text_col          TEXT,                       -- Max 65,535 chars
    medium_text_col   MEDIUMTEXT,                 -- Max 16,777,215 chars
    long_text_col     LONGTEXT,                   -- Max 4GB
    enum_col          ENUM('small','medium','large'),
    set_col           SET('a','b','c','d'),       -- Multiple values

    -- JSON Type
    json_col          JSON,                       -- Native JSON storage

    -- Spatial/Geometry Types (require spatial support)
    geom_col          GEOMETRY,                   -- Any geometry
    point_col         POINT,                      -- Single coordinate pair
    linestring_col    LINESTRING,
    polygon_col       POLYGON
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

-- INSERT
INSERT INTO all_data_types (
    tiny_int_col, small_int_col, medium_int_col, int_col, big_int_col,
    decimal_col, numeric_col, float_col, double_col, real_col, bit_col,
    date_col, datetime_col, time_col, year_col,
    char_col, varchar_col, binary_col, varbinary_col,
    tiny_blob_col, blob_col, medium_blob_col, long_blob_col,
    tiny_text_col, text_col, medium_text_col, long_text_col,
    enum_col, set_col, json_col, geom_col, point_col, linestring_col, polygon_col
) VALUES (
    1, 32000, 8000000, 2147483647, 9223372036854775807,
    12345.67, 9876.543, 123.4567, 987654.12345678, 111.11, b'10101010',
    '2025-09-27', '2025-09-27 12:34:56.123456', '12:34:56.789', 2025,
    'fixed', 'variable string', x'00112233445566778899AABBCCDDEEFF', x'DEADBEEF',
    'a', 'binary blob', 'medium blob', 'long blob',
    'short text', 'normal text', 'medium text', 'long text',
    'medium', 'a,b,c', '{"key":"value"}',
    ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('POINT(1 1)'),
    ST_GeomFromText('LINESTRING(0 0,1 1,2 2)'),
    ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))')
);

-- Insert row into table
INSERT INTO checklists (todo_id, task_id, is_completed, todo)
VALUES (1, 1, 0, 'My first task');

-- Insert multiple rows into table
INSERT INTO checklists (todo_id, task_id, is_completed, todo)
VALUES (2, 1, 0, 'My second task'),
(2, 2, 0, 'My third task');

-- Insert using update syntax
INSERT INTO checklists SET
todo_id = 3,
task_id = 1,
is_completed = 0,
todo = 'My forth task';

-- Get active students from course 1
SELECT cm.*, c.title, CONCAT(s.first_name, ' ', s.last_name) AS student, s.status
FROM course_membership AS cm
    INNER JOIN courses AS c ON (cm.id_course = c.id)
    INNER JOIN students AS s ON (cm.id_student = s.id AND s.status = 2)
WHERE cm.id_course = 1

        
CREATE DATABASE IF NOT EXISTS {database_name};

CREATE USER IF NOT EXISTS '{db_username}'@'%' IDENTIFIED BY '{db_user_pass}';

GRANT ALL PRIVILEGES ON {database_name}.* TO '{db_username}'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;