# 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](https://dev.mysql.com/downloads/mysql/) or, simply use [xampp](https://www.apachefriends.org/download.html), 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](https://sharafat.pages.dev/database-containers/)

### User

#### Login

```bash
mysql -u root -p
```

#### Show Users

```sql
SELECT 
    User, 
    Host 
FROM 
    MYSQL.USER;
```

#### Create User

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

#### Grant All Privileges On All Databases

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

#### Show Grants

```sql
SHOW GRANTS FOR 'someuser'@'localhost';
```

#### Remove Grants

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

#### Delete User

```sql
DROP USER 'someuser'@'localhost';
```

#### Exit

```sql
EXIT;
```

## Database

### General Commands

To run SQL files

```sql
SOURCE <filename>.sql;
```

### Data Types

#### Integers

```sql
INT
```

```sql
TINYINT
```

```sql
SMALLINT
```

```sql
MEDIUMINT
```

```sql
BIGINT
```

#### Float

```sql
FLOAT(M,D)
```

#### Double

```sql
DOUBLE(M,D)
```

#### Decimal

```sql
DECIMAL(M,D)
```

#### Date

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

#### Date Time

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

#### Time

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

#### String

```sql
CHAR(M)
```

```sql
VARCHAR(M)
```

```sql
BLOB or TEXT
```

### Comments

```sql
/* Multi
line
comment */
```

```sql
# Single Line Comment
```

```sql
-- Single Line Comment
```

## Data Definition Language (DDL)

### Create Database

```sql
CREATE DATABASE cheatsheet;
```

### Show Databases

```sql
SHOW DATABASES;
```

### Use Database

```sql
USE cheatsheet;
```

### Create Table

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

```sql
SHOW TABLES;
```

## Show Table Code

```sql
SHOW CREATE TABLE employee;
```

## Describe Table

```sql
DESCRIBE employee;
DESC employee;
SHOW COLUMNS IN employee;
```

### Rename Table

```sql
RENAME TABLE employee TO employee_table;
ALTER TABLE employee_table RENAME TO employee;
```

### Renaming Column

```sql
ALTER TABLE employee 
CHANGE COLUMN employee_id emp_id INT;
```

### Add Constraint to Column

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

### Add Column

```sql
ALTER TABLE employee 
ADD COLUMN salary REAL;
```

### Drop Column

```sql
ALTER TABLE employee 
DROP COLUMN salary;
```

### Modify the Datatype of column

```sql
ALTER TABLE employee 
MODIFY COLUMN salary INT;
```

### Truncate Table

```sql
TRUNCATE employee;
```

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

### Drop Table

```sql
DROP TABLE department;
```

### Drop Database

```sql
DROP DATABASE cheatsheet;
```

## Data Manipulation Language (DML)

### Insertion (Complete)

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

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

### Updating all rows

```sql
UPDATE employee 
SET salary = 1.1 * salary;
```

### Updating a specified row

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

### Delete a specified row

```sql
DELETE FROM employee 
WHERE employee_id = 2;
```

### Delete all rows

```sql
DELETE FROM employee;
```

## Data Query Language (DQL)

### Display Table

```sql
SELECT * 
FROM employee;
```

### Select only specified columns

```sql
SELECT 
    employee_id, 
    first_name 
FROM 
    employee;
```

### Select only few rows

```sql
SELECT 
    employee_id, 
    first_name 
FROM 
    employee 
WHERE 
    age > 25;
```

### Select with condition

```sql
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(>)

```sql
SELECT * 
FROM employee 
WHERE salary > 3100;
```

#### Greater than equal to(>=)

```sql
SELECT * 
FROM employee 
WHERE salary >= 3100;
```

#### Less than(<)

```sql
SELECT * 
FROM employee 
WHERE salary < 4500;
```

#### Less than equal to(<=)

```sql
SELECT * 
FROM employee 
WHERE salary <= 4350;
```

#### Range

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

#### BETWEEN and AND

```sql
SELECT * 
FROM employee 
WHERE salary BETWEEN 3000 AND 4000;
```

#### OR

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

#### Null

```sql
SELECT * 
FROM employee 
WHERE salary IS NULL;
```

#### Not null

```sql
SELECT * 
FROM employee 
WHERE salary IS NOT NULL;
```

#### ORDER BY Clause

```sql
SELECT * 
FROM employee 
ORDER BY salary DESC;
```

**Like Operator**

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

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

## Views

### Create a view

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

### Displaying view

```sql
SELECT * 
FROM personal_info;
```

### Updating in view

```sql
UPDATE personal_info 
SET salary = 1.1 * salary;
```

### Deleting record from view

```sql
DELETE FROM personal_info 
WHERE age < 40;
```

### Droping a view

```sql
DROP VIEW personal_info;
```

## Joins

<figure><img src="/files/YDaQ4YjRYLJYN6OXCvDC" alt=""><figcaption></figcaption></figure>

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

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

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

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

### Right outer join

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

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

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

```sql
SELECT 
    SUM(population) 
FROM 
    city 
GROUP BY population;
```

### Average function

```sql
SELECT 
    AVG(population) 
FROM 
    city 
GROUP BY population;
```

### Count function

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

### Maximum function

```sql
SELECT 
    MAX(population) 
FROM 
    city 
GROUP BY population;
```

### Minimum function

```sql
SELECT 
    MIN(population) 
FROM 
    city 
GROUP BY population;
```

### Standard deviation function

```sql
SELECT 
    STDDEV(population) 
FROM 
    city 
GROUP BY population;
```

### Group concat function

```sql
SELECT 
    GROUP_CONCAT(population) 
FROM 
    city 
GROUP BY population;
```

> Only COUNT function considers NULL values

## Procedure

### Creating procedure

```sql
CREATE PROCEDURE display_dbs()
SHOW DATABASES;
```

### Calling procedure

```sql
CALL display_dbs();
```

#### Drop procedure

```sql
DROP PROCEDURE display_dbs;
```

## Transaction

### Begin transaction

```sql
START TRANSACTION;
```

### Create savepoint

```sql
SAVEPOINT sv_pt;
```

```sql
DELETE FROM city;       -- changing data in table
```

### Rollback

```sql
ROLLBACK TO sv_pt;
```

### Releasing savepoint

```sql
RELEASE SAVEPOINT sv_pt;
```

### Commiting changes

```sql
COMMIT;
```

## Constraints

### Not Null

```sql
ALTER TABLE Employee
CHANGE
    Age
    Age INT NOT NULL;
```

### Unique

```sql
ALTER TABLE Employee
ADD CONSTRAINT u_q UNIQUE(ID);
```

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

### Primary Key

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

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

### Check

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

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

### Default

```sql
ALTER TABLE Employee
ALTER Age SET DEFAULT 10;
```

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

### Cloning

### Duplicate a Table Schema

```sql
CREATE TABLE emp_dup LIKE employee;
```

### Duplicate a Table

```sql
CREATE TABLE emp_dup SELECT * FROM employee;
```

## Access Controls

### Creating New User

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

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

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

### Grant All Permissions

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

```sql
FLUSH PRIVILEGES
```

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

### Specific User Permissions

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

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

### Show User's Current Permissions

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

### Delete a User

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

### Set new password to a user

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

```bash
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

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

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

### Print them

```sql
SELECT @name;
```

### For loop

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

## Miscellaneous

### Enabling foreign key checks

```sql
SET foreign_key_checks = 1;
```

### Disabling foreign key checks

```sql
SET foreign_key_checks = 0;
```

### Round

```sql
SELECT ROUND(3.141596, 3);
```

### Repeated concatenation

```sql
SELECT REPEAT("* ", 20);
```

### Random float

```sql
SELECT RAND();
```

### Typecast to Int

```sql
SELECT CAST(23.01245 AS SIGNED);
```

### Concatenation

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

### Extract Month

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

### Extract Year

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://sharafat.gitbook.io/home/docs/my-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.