My SQL

My SQL

This is a cheat sheet of MySQL for easier references. MySQL is an open-source relational database management system. It is a central component of the LAMP open-source web application software stack. MySQL is used by many database-driven web applications, including Drupal, Joomla, phpBB, and WordPress.

Installation

To get started with MySQL, you can download it from here or, simply use xampp, which uses maria-db, a drop in replacement of MySQL. To run MySQL, in linux environment or, docker/ podman container, I've a guide for you!

• Database setup with podman/ Docker containers

User

Login

mysql -u root -p

Show Users

SELECT 
    User, 
    Host 
FROM 
    MYSQL.USER;

Create User

CREATE USER 'someuser'@'localhost' 
IDENTIFIED BY 'somepassword';

Grant All Privileges On All Databases

GRANT ALL PRIVILEGES ON * . * 
TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Show Grants

SHOW GRANTS FOR 'someuser'@'localhost';

Remove Grants

REVOKE ALL PRIVILEGES, GRANT OPTION 
FROM 'someuser'@'localhost';

Delete User

DROP USER 'someuser'@'localhost';

Exit

EXIT;

Database

General Commands

To run SQL files

SOURCE <filename>.sql;

Data Types

Integers

INT

TINYINT

SMALLINT

MEDIUMINT

BIGINT

Float

FLOAT(M,D)

Double

DOUBLE(M,D)

Decimal

DECIMAL(M,D)

Date

DATE -- Format - (YYYY-MM-DD)

Date Time

DATETIME -- Format - (YYYY-MM-DD HH:MM:SS)

Time

TIME -- Format - (HH:MM:SS)

String

CHAR(M)

VARCHAR(M)

BLOB or TEXT

Comments

/* Multi
line
comment */

# Single Line Comment

-- Single Line Comment

Data Definition Language (DDL)

Create Database

CREATE DATABASE cheatsheet;

Show Databases

SHOW DATABASES;

Use Database

USE cheatsheet;

Create Table

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,              -- Setting primary key(1st method)
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    dept_number INT,
    age INT,
    salary REAL
);

CREATE TABLE department (
    dept_number INT,
    dept_name VARCHAR(50),
    dept_location VARCHAR(50),
    emp_id INT,
    PRIMARY KEY(dept_number)                -- Setting primary key(2nd method)
);

-- Create table for classroom
CREATE TABLE classroom (
    building        VARCHAR(15),
    room_number     VARCHAR(7),
    capacity        NUMERIC(4,0) DEFAULT 0,
    PRIMARY KEY (building, room_number)
);

-- Create table for department
CREATE TABLE department (
    dept_name       VARCHAR(20), 
    building        VARCHAR(15), 
    budget          NUMERIC(12,2) CHECK (budget > 0) DEFAULT 0.00,
    PRIMARY KEY (dept_name)
);

-- Create table for course
CREATE TABLE course (
    course_id       VARCHAR(8), 
    title           VARCHAR(50), 
    dept_name       VARCHAR(20),
    credits         NUMERIC(2,0) CHECK (credits > 0) DEFAULT 0,
    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department (dept_name)
        ON DELETE SET NULL
);

-- Create table for instructor
CREATE TABLE instructor (
    ID              VARCHAR(5), 
    name            VARCHAR(20) NOT NULL, 
    dept_name       VARCHAR(20), 
    salary          NUMERIC(8,2) CHECK (salary > 29000) DEFAULT 30000.00,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department (dept_name)
        ON DELETE SET NULL
);

-- Create table for section
CREATE TABLE section (
    course_id       VARCHAR(8), 
    sec_id          VARCHAR(8),
    semester        VARCHAR(6) CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer')), 
    year            NUMERIC(4,0) CHECK (year > 1701 AND year < 2100) DEFAULT 2021, 
    building        VARCHAR(15),
    room_number     VARCHAR(7),
    time_slot_id    VARCHAR(4),
    PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (course_id) REFERENCES course (course_id)
        ON DELETE CASCADE,
    FOREIGN KEY (building, room_number) REFERENCES classroom (building, room_number)
        ON DELETE SET NULL
);

-- Create table for teaches
CREATE TABLE teaches (
    ID              VARCHAR(5), 
    course_id       VARCHAR(8),
    sec_id          VARCHAR(8), 
    semester        VARCHAR(6),
    year            NUMERIC(4,0),
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year)
        ON DELETE CASCADE,
    FOREIGN KEY (ID) REFERENCES instructor (ID)
        ON DELETE CASCADE
);

-- Create table for student
CREATE TABLE student (
    ID              VARCHAR(5), 
    name            VARCHAR(20) NOT NULL, 
    dept_name       VARCHAR(20), 
    tot_cred        NUMERIC(3,0) CHECK (tot_cred >= 0) DEFAULT 0,
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department (dept_name)
        ON DELETE SET NULL
);

-- Create table for takes
CREATE TABLE takes (
    ID              VARCHAR(5), 
    course_id       VARCHAR(8),
    sec_id          VARCHAR(8), 
    semester        VARCHAR(6),
    year            NUMERIC(4,0),
    grade           VARCHAR(2) DEFAULT 'NA',
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year)
        ON DELETE CASCADE,
    FOREIGN KEY (ID) REFERENCES student (ID)
        ON DELETE CASCADE
);

-- Create table for advisor
CREATE TABLE advisor (
    s_ID            VARCHAR(5),
    i_ID            VARCHAR(5),
    PRIMARY KEY (s_ID),
    FOREIGN KEY (i_ID) REFERENCES instructor (ID)
        ON DELETE SET NULL,
    FOREIGN KEY (s_ID) REFERENCES student (ID)
        ON DELETE CASCADE
);

-- Create table for time_slot
CREATE TABLE time_slot (
    time_slot_id    VARCHAR(4),
    day             VARCHAR(1),
    start_hr        NUMERIC(2) CHECK (start_hr >= 0 AND start_hr < 24),
    start_min       NUMERIC(2) CHECK (start_min >= 0 AND start_min < 60),
    end_hr          NUMERIC(2) CHECK (end_hr >= 0 AND end_hr < 24),
    end_min         NUMERIC(2) CHECK (end_min >= 0 AND end_min < 60),
    PRIMARY KEY (time_slot_id, day, start_hr, start_min)
);

-- Create table for prereq
CREATE TABLE prereq (
    course_id       VARCHAR(8), 
    prereq_id       VARCHAR(8),
    PRIMARY KEY (course_id, prereq_id),
    FOREIGN KEY (course_id) REFERENCES course (course_id)
        ON DELETE CASCADE,
    FOREIGN KEY (prereq_id) REFERENCES course (course_id)
);

Show Tables

SHOW TABLES;

Show Table Code

SHOW CREATE TABLE employee;

Describe Table

DESCRIBE employee;
DESC employee;
SHOW COLUMNS IN employee;

Rename Table

RENAME TABLE employee TO employee_table;
ALTER TABLE employee_table RENAME TO employee;

Renaming Column

ALTER TABLE employee 
CHANGE COLUMN employee_id emp_id INT;

Add Constraint to Column

ALTER TABLE employee 
CHANGE COLUMN first_name first_name VARCHAR(50) NOT NULL;

Add Column

ALTER TABLE employee 
ADD COLUMN salary REAL;

Drop Column

ALTER TABLE employee 
DROP COLUMN salary;

Modify the Datatype of column

ALTER TABLE employee 
MODIFY COLUMN salary INT;

Truncate Table

TRUNCATE employee;

Trancute means to delete all the rows from the table but the table structure remains the same.

Drop Table

DROP TABLE department;

Drop Database

DROP DATABASE cheatsheet;

Data Manipulation Language (DML)

Insertion (Complete)

INSERT INTO employee (
    employee_id, 
    first_name, 
    last_name, 
    dept_number, 
    age, 
    salary
) VALUES (
    1, 
    "Anurag", 
    "Peddi", 
    1, 
    20, 
    93425.63
);

INSERT INTO employee VALUES (
    2, 
    "Anuhya", 
    "Peddi", 
    2, 
    20, 
    83425.63
);

Insertion (Partial)

INSERT INTO employee (
    employee_id, 
    first_name
) VALUES (
    3, 
    "Vageesh"
);

Updating all rows

UPDATE employee 
SET salary = 1.1 * salary;

Updating a specified row

UPDATE employee 
SET salary = 1.2 * salary 
WHERE employee_id = 1;

Delete a specified row

DELETE FROM employee 
WHERE employee_id = 2;

Delete all rows

DELETE FROM employee;

Data Query Language (DQL)

Display Table

SELECT * 
FROM employee;

Select only specified columns

SELECT 
    employee_id, 
    first_name 
FROM 
    employee;

Select only few rows

SELECT 
    employee_id, 
    first_name 
FROM 
    employee 
WHERE 
    age > 25;

Select with condition

select ID, 
    case
	when name=score < 40 then "F"
        when name=score < 60 then "C"
        when name=score < 80 then "B"
        else "A"
    end
from marks;

Where Clause

Greater than(>)

SELECT * 
FROM employee 
WHERE salary > 3100;

Greater than equal to(>=)

SELECT * 
FROM employee 
WHERE salary >= 3100;

Less than(<)

SELECT * 
FROM employee 
WHERE salary < 4500;

Less than equal to(<=)

SELECT * 
FROM employee 
WHERE salary <= 4350;

Range

SELECT * 
FROM employee 
WHERE salary > 3000 
AND salary < 4000;

BETWEEN and AND

SELECT * 
FROM employee 
WHERE salary BETWEEN 3000 AND 4000;

OR

SELECT * 
FROM employee 
WHERE salary = 3000 
OR salary = 4000;

Null

SELECT * 
FROM employee 
WHERE salary IS NULL;

Not null

SELECT * 
FROM employee 
WHERE salary IS NOT NULL;

ORDER BY Clause

SELECT * 
FROM employee 
ORDER BY salary DESC;

Like Operator

SELECT * 
FROM employee 
WHERE name LIKE '%Jo%';          -- Similar to *Jo* in regrex

SELECT * 
FROM employee 
WHERE name LIKE 'Jo_';           -- Similar to Jo. in regrex

Views

Create a view

CREATE VIEW personal_info AS 
SELECT 
    first_name, 
    last_name, 
    age 
FROM 
    employees;

Displaying view

SELECT * 
FROM personal_info;

Updating in view

UPDATE personal_info 
SET salary = 1.1 * salary;

Deleting record from view

DELETE FROM personal_info 
WHERE age < 40;

Droping a view

DROP VIEW personal_info;

Joins

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Inner join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
INNER JOIN project AS p 
ON e.eid = p.eid;

-- or

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
JOIN project AS p 
ON e.eid = p.eid;

Full outer join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
LEFT OUTER JOIN project AS p 
ON e.eid = p.eid
UNION
SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
RIGHT OUTER JOIN project AS p 
ON e.eid = p.eid;

Left outer join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
LEFT OUTER JOIN project AS p 
ON e.eid = p.eid;

Right outer join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
RIGHT OUTER JOIN project AS p 
ON e.eid = p.eid;

Left outer join - inner join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
LEFT OUTER JOIN project AS p 
ON e.eid = p.eid 
WHERE p.pname IS NULL;

Right outer join - inner join

SELECT 
    e.fname, 
    p.pname 
FROM 
    employees AS e 
RIGHT OUTER JOIN project AS p 
ON e.eid = p.eid 
WHERE e.fname IS NULL;

Aggregation

Sum function

SELECT 
    SUM(population) 
FROM 
    city 
GROUP BY population;

Average function

SELECT 
    AVG(population) 
FROM 
    city 
GROUP BY population;

Count function

SELECT 
    district, 
    COUNT(district) 
FROM 
    city 
GROUP BY district;

Maximum function

SELECT 
    MAX(population) 
FROM 
    city 
GROUP BY population;

Minimum function

SELECT 
    MIN(population) 
FROM 
    city 
GROUP BY population;

Standard deviation function

SELECT 
    STDDEV(population) 
FROM 
    city 
GROUP BY population;

Group concat function

SELECT 
    GROUP_CONCAT(population) 
FROM 
    city 
GROUP BY population;

Only COUNT function considers NULL values

Procedure

Creating procedure

CREATE PROCEDURE display_dbs()
SHOW DATABASES;

Calling procedure

CALL display_dbs();

Drop procedure

DROP PROCEDURE display_dbs;

Transaction

Begin transaction

START TRANSACTION;

Create savepoint

SAVEPOINT sv_pt;

DELETE FROM city;       -- changing data in table

Rollback

ROLLBACK TO sv_pt;

Releasing savepoint

RELEASE SAVEPOINT sv_pt;

Commiting changes

COMMIT;

Constraints

Not Null

ALTER TABLE Employee
CHANGE
    Age
    Age INT NOT NULL;

Unique

ALTER TABLE Employee
ADD CONSTRAINT u_q UNIQUE(ID);

ALTER TABLE Employee -- drop the constraint
DROP CONSTRAINT u_q;

Primary Key

ALTER TABLE Employee
ADD CONSTRAINT p_k PRIMARY KEY(ID);

ALTER TABLE Employee -- drop the constraint
DROP CONSTRAINT p_k;

Check

ALTER TABLE Employee
ADD CONSTRAINT Age CHECK (age>=30);

ALTER TABLE Employee -- drop the constraint
DROP CHECK Age;

Default

ALTER TABLE Employee
ALTER Age SET DEFAULT 10;

ALTER TABLE Employee -- drop the constraint
ALTER Age DROP DEFAULT;

Cloning

Duplicate a Table Schema

CREATE TABLE emp_dup LIKE employee;

Duplicate a Table

CREATE TABLE emp_dup SELECT * FROM employee;

Access Controls

Creating New User

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

the hostname part is set to localhost, so the user will be able to connect to the MySQL server only from the localhost. To grant access from another host, change the hostname part with the remote machine IP.

CREATE USER 'username'@'172.8.10.5' 
IDENTIFIED BY 'user_password';

To create a user that can connect from any host, '%' is used in the hostname part:

CREATE USER 'username'@'%' 
IDENTIFIED BY 'user_password';

Grant All Permissions

GRANT ALL PRIVILEGES ON * . * 
TO 'username'@'localhost';

Asterisks(*) refers to the database and table names respectively. By using asterisks we can give access of all the databases or tables to the user.

Flush Privileges

FLUSH PRIVILEGES

All the changes won't be in effect unless this query is fired.

Specific User Permissions

GRANT type_of_permission 
ON database_name.table_name 
TO 'username'@'localhost';

type_of_permission may have one of these value:

• ALL PRIVILEGES - Allows user full access to a designated database (or if no database is selected, global access across the system).

• CREATE - allows them to create new tables or databases.

• DROP - allows them to them to delete tables or databases.

• DELETE - allows them to delete rows from tables.

• INSERT - allows them to insert rows into tables.

• SELECT - allows them to use the SELECT command to read through databases.

• UPDATE - allow them to update table rows.

• GRANT OPTION - allows them to grant or remove other users’ privileges. Multiple permissions are given with commas.

Revoking permissions

REVOKE type_of_permission 
ON database_name.table_name 
FROM 'username'@'localhost';

Show User's Current Permissions

SHOW GRANTS FOR 'username'@'localhost';

Delete a User

DROP USER 'username'@'localhost';

Set new password to a user

USE mysql;
UPDATE user 
SET authentication_string=PASSWORD("<new2-password>") 
WHERE User='<user>';
FLUSH PRIVILEGES;

Reset Root Password

Stop MySQL service

sudo systemctl stop mysql

Restart MySQL service without loading grant tables

sudo mysqld_safe --skip-grant-tables &

The apersand (&) will cause the program to run in the background and --skip-grant-tables enables everyone to to connect to the database server without a password and with all privileges granted. Login to shell

mysql -u root

Set new password for root

ALTER USER 'root'@'localhost' 
IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

Stop and start the server once again

mysqladmin -u root -p shutdown
sudo systemctl start mysql

Programming

Declare variables

SET @num = 10;
SET @name = 'Anurag';

Print them

SELECT @name;

For loop

SET @n = 21;
SELECT REPEAT("* ", @n := @n - 1) 
FROM information_schema.tables 
WHERE @n > 0;

Miscellaneous

Enabling foreign key checks

SET foreign_key_checks = 1;

Disabling foreign key checks

SET foreign_key_checks = 0;

Round

SELECT ROUND(3.141596, 3);

Repeated concatenation

SELECT REPEAT("* ", 20);

Random float

SELECT RAND();

Typecast to Int

SELECT CAST(23.01245 AS SIGNED);

Concatenation

SELECT CONCAT("Mahesh", " ", "Chandra", " ", "Duddu", "!");

Extract Month

SELECT MONTH("1998-12-30");

Extract Year

SELECT YEAR("1998-12-30");

Also thanks to

• https://github.com/Cheatsheet-lang/MySQL-cheatsheet

• https://github.com/GunaPalanivel/The-MySQL-Code-Sheet

• https://github.com/AbdGhazall/MySQL-Cheat-Sheet

• https://gist.github.com/bradtraversy/c831baaad44343cc945e76c2e30927b3