DEV Community: ishanshre

MySql CheatSheet

ishanshre — Sun, 05 Mar 2023 14:29:20 +0000

SQL Commands

Create Database

Create new database or schema
Command - CREATE DATABASE <DATABASE NAME>;
Eg:- CREATE DATABASE myDB;

Drop Database

Delete the database
Command - DROP DATABASE <DATABASE NAME>;
Eg:- DROP DATABASE myDB;

Use Database

-Set the database default

Command- USE <DATABASE NAME>;
Eg:- USE myDB;

Alter Database

We can change the database to read only and vice versa. When the database is read only we can only view its data. We cannot do any operations in database except reading and altering the read only to false or 0
Command- ALTER <DATABASE NAME> READ ONLY=1;
Command- ALTER <DATABASE NAME> READ ONLY=0;

Table

Create Table

Use CREATE to create new table in the database
Command:-

CREATE TABLE <TABLE NAME> (
column 1 DATATYPE,
column 2 DATATYPE,
column 3 DATATYPE,
… 
);

CREATE TABLE user (
    user_id INT,
    username VARCHAR(50),
    password VARCHAR(50),
    joined_date DATE,
    last_login DATE
);

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    created DATE DEFAULT (CURRENT_DATE())
);

Rename Table

Command:- RENAME TABLE <TABLE NAME> TO <NEW TABLE NAME>;
Eg:- RENAME TABLE user TO account;

Alter Table

Alters the table details
Commands:-
Add column

ALTER TABLE account
ADD phone_number VARCHAR(10);

Rename column

ALTER TABLE account
RENAME phone_number to email;

Change column datatype

ALTER TABLE account
MODIFY COLUMN email VARCHAR(255);

Change the column order

ALTER TABLE account
MODIFY email VARCHAR(255)
AFTER password;

Drop Column

ALTER TABLE account
DROP COLUMN email

Insert Rows

Insert single data or single row

INSERT INTO account
VALUES (1,"user1","userPassword1","2021-01-23","2022-12-01");

Insert multiple rows or multiple data

INSERT INTO user
VALUES (3, "admin1","admin1pass","2021-01-02","2022-01-09"),
    (3, "admin2","admin2@pass","2021-01-02","2022-01-09"),
    (4, "admin3","admin3@pass","2021-01-02","2022-01-09"),
    (5, "admin4","admin4@pass","2021-01-02","2022-01-09");

Insert data into selected columns of the row

INSERT INTO user (user_id, username, password)
VALUES (6, "admin5","admin6pass");

Update columns data with where clause

UPDATE accounts
SET password = "hello@123"
WHERE user_id = 2;

Delete row data(where clause is important in DELETE command other wise our whole data will be deleted

DELETE FROM accounts
WHERE user_id = 2;

Enable/Disable safe mode in MySql

SET SQL_SAFE_UPDATES = 0

SET SQL_SAFE_UPDATES = 1

Disable autocommit

SET AUTOCOMMIT = OFF;

Manually create a save point and make changes

COMMIT;

Roll Back Changes(only possible if commit changes

ROLLBACK;

Add UNIQUE Constraints to column when creating a table

CREATE TABLE profile (
    profile_id INT,
    name VARCHAR(50) UNIQUE,
    age INT
);

Add UNIQUE to a column of a table

ALTER TABLE profile
ADD CONSTRAINT
UNIQUE (profile_id);

Set Column NOT NULL when creating a table

CREATE TABLE student (
    student_id INT NOT NULL,
    class INT
);

Set column NOT NULL for existing tables

ALTER TABLE student
MODIFY class INT NOT NULL;

Set a default value new table

CREATE TABLE employees (
   id INT,
   name VARCHAR(255),
   salary DECIMAL(10,2) DEFAULT 0
);

Set a default value for existing table

ALTER TABLE employees 
ALTER salary SET DEFAULT 10;

Set Primary key for new table

CREATE TABLE employees (
     id INT PRIMARY KEY,
     name VARCHAR(50)
);

Set Primary key for existing table

ALTER TABLE employees
ADD CONSTRAINT
PRIMARY KEY(id);

Set Primary key for new table with auto increment

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Set auto_increment value starts from

ALTER TABLE employee 
AUTO_INCREMENT = 1000;

Creating a foriegn key(one to many relationship)

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(5),
);

INSERT INTO customers (name)
VALUES ("ishan"),("binod"),("anil");
SELECT * FROM customers;

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);
SELECT * FROM orders;

Drop the foreign key

ALTER TABLE orders 
DROP FOREIGN KEY orders_ibfk_1

Add foriegn key to existing table with custom name

ALTER TABLE orders 
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id);

Inner Join

Select records from two tables having matching values in both table

SELECT customers.customer_id, customers.name, orders.amount
FROM customers INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Left join

Selects all records from left table
Right side table records are not displayed if record does not matches from the left

SELECT *
FROM customers LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Right join

Selects all the records from the right table
Selects all the records from the left table that matches with the right table

SELECT *
FROM customers RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

COUNT function

Counts the records in that column

SELECT COUNT(amount) AS no_of_amounts
FROM orders;

MAX Function

Max returns the highest order of the record in the column

SELECT MAX(amount) as maximum_amount
FROM orders;

SELECT MAX(name) AS large_name
FROM customers;

MIN function

MIN returns the lowest order of the record in the column

SELECT MIN(amount) as minimum_amount
FROM orders;

SELECT MIN(name) AS small_name 
FROM customers;

AVG function

AVG returns the average of the records in the columns

SELECT AVG(amount) AS average_amount
FROM orders;

SUM function

SUM returns the sum of the records in the columns

SELECT SUM(amount) AS sum_of_amount
FROM orders;

CONCAT function

returns a records concatinating two or more columns

SELECT CONCAT(first_name, " ", last_name) AS "Full Name"
FROM employees;

AND, OR, NOT, BETWEEN AND IN

They are logical keywords for logical operations

SELECT *
FROM employees
WHERE hire_date < "2015-01-15 AND job = "Teacher";

SELECT * 
FROM employees
WHERE job = "Manager" OR job = "Boxer";

SELECT *
FROM employees
WHERE NOT age = 20;

SELECT * 
FROM employees
WHERE NOT age = 23 AND NOT age = 40;

SELECT *
FROM employees
WHERE hire_date BETWEEN "2022-5-20" AND "2023-1-2";

SELECT *
FROM employees
WHERE jobs IN ("Cook","Teacher","Doctor","Manager");

Wildcards (%, _)

% represents number of characters
_ represents only one characters

SELECT *
FROM employees
WHERE first_name LIKE "I%";

SELECT *
FROM employees
WHERE last_name LIKE "%t";

SELECT *
FROM employees
WHERE jobs LIKE "_OO_";

SELECT *
FROM employees
WHERE jobs LIKE "_e_ach_r";

SELECT *
FROM employees
WHERE hire_date LIKE "____-01-__";

SELECT *
FROM employees
WHERE last_name LIKE "_a%";

ORDER BY

Default order : Ascending (ASC keyword)
To order in reverse, add DESC keyword

SELECT * FROM employees
ORDER BY last_name;

SELECT * FROM employees
ORDER BY first_name DESC;

SELECT * FROM employees
ORDER BY hire_date DESC;

LIMIT and OFFSET

LIMIT is used for returning limited records, especially usefull for large datasets
OFFSET is used to display next records to limit. Especially used in pagination

-- Displays next 10 records i.e. record from 11 to 20
SELECT * FROM employees
LIMIT 10
OFFSET 1;

UNION and UNION ALL

Combines the result of two or more tables
Must have same number of columns in the tables
UNION remove the duplicates
UNION ALL allows duplicates

SELECT first_name,  last_name FROM employees
UNION
SELECT first_name, last_name FROM customers

SELECT * FROM incomes
UNION
SELECT * FROM expences

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers

Self Join

Join a table to itself
Comparing rows with other rows in same table

SELECT a.first_name, a.last_name, 
       CONCAT(b.first_name,b.last_name) as "Referred by"
FROM customers as a
INNER JOIN customers as b
ON a.referred_id = b.customer_id;

SELECT a.first_name, a.last_name
       CONCAT(b.first_name, b.last_name) as "Supervised by"
FROM employees as a
LEFT JOIN employees as b
ON a.supervisor_id = b.employee_id;

VIEW

VIEW is a virtual table of a result of a query
Can perform operations of table in view as well
VIEW get updated as corresponding table updates

CREATE VIEW employeee_attendances AS
SELECT first_name, last_name
FROM employees;

CREATE VIEW email_employees_list AS
SELECT email 
FROM employees;

INDEX

INDEX is applied to the column
INDEX is a BTree data structure
It is used for increasing the speed of searching
Disadvantage of Index is that it slows down the update
Longer the column, longer the operations takes

SHOW INDEX FROM customers;

CREATE INDEX last_name_idx
ON customers(last_name);

CREATE INDEX last_name_first_name_idx
ON customers(last_name, first_name);

ALTER TABLE customers
DROP INDEX last_name_idx;

SUBQUERIES

A query within a query
Syntax:- query(subquery)
Result of the subquery is used in the outer query.

SELECT * FROM employees
WHERE hourly_pay > (SELECT AVG(hourly_pay) FROM employees);

SELECT first_name, last_name
FROM customers
WHERE customer_id IN
(SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL);

GROUP BY clause

Allows to group rows based upon one or more column
Most often used with aggregate function such as max, avg, etc.
Use HAVING instead of WHERE keyword when using GROUP BY
Using WHERE results an error with GROUP BY

SELECT COUNT(amount), ordered_date
FROM orders
GROUP BY ordered_date;

SELECT SUM(amount), customer_id
FROM orders
GROUP BY customer_id;

SELECT MAX(amount), customer_id,
FROM orders
GROUP BY customer_id;

SELECT COUNT(amount) AS total_orders, customer_id
FORM orders
GROUP BY customer_id
HAVING total_orders > 1;

ROLLUP clause

Extension of GROUP BY clause
Super aggregate value or grand total in new row

SELECT SUM(amount), ordered_date
FROM orders
GROUP BY ordered_date WITH ROLLUP;

SELECT COUNT(order_id), ordered_date
FROM orders
GROUP BY ordered_date WITH ROLLUP;

SELECT SUM(hourly_pay) AS "Hourly Pay", employee_id
FROM employees
GROUP_BY employee_id WITH ROLLUP;

ON DELETE

Important for tables with relations

-- ON DELETE SET NULL = SET NULL when FK is deleted
-- ON DELETE CASCADE = DELETE record when FK is deleted

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    created DATE DEFAULT (CURRENT_DATE())
);

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    body VARCHAR(1000),
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    );

CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    body VARCHAR(100),
    post_id INT,
    user_id INT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- adding FK and ON DELETE CASCADE to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE

-- adding FK and ON DELETE SET NULL to exisiting table
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL

Stored Procedure

It is same as function or methods in programming.
It is a prepared SQL code that can be used again and again.
Some advantages are reduces network traffic, increase performance, secure and admin can grant permission to use.
The only disadvantage is it increases the memory usuage of every connection.
We also change the delimiter from ; to $$ for the procedure.

DELIMITER $$
CREATE PROCEDURE get_users()
BEGIN
     SELECT * FROM users;
END $$
DELIMITER ;

To call a procedure

CALL get_users();

To drop a procedure

DROP PROCEDURE get_users();

Passing a argument

DELIMITER $$
CREATE PROCEDURE get_user_by_id(IN id INT)
BEGIN
     SELECT * FROM user
     WHERE user_id = id;
END $$
DELIMITER ;

CALL get_user_by_id(1);

Trigger

A special type stored procedure that automatically runs when an event occurs.

CREATE TRIGGER before_hourly_pay_update
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);

CREATE TRIGGER before_hourly_pay_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);

Dockerizing Django and MySql

ishanshre — Wed, 21 Dec 2022 08:55:32 +0000

Dockerizing Django and MySql(Linux)

In this guide, I will show you how to dockerize your django project in one container and MySql Server in another container in Linux Operating System. We will create, manage and run these container using docker-compose. For this I assume you have installed python, docker and docker-compose in your machine. If not, refer to "install python" and "install docker".

First we setup our django project using terminal:

$ mkdir myProject
$ cd myProject
$ python -m venv venv
$ source venv/bin/activate
(venv) $ pip install Django==4.1.4 python-dotenv
(venv) $ django-admin startproject core .
(venv) $ pip freeze > requirements.txt

Lets go line by line:

mkdir myProject create a new directory in your current working directory
cd myProject change directory to myProject
python -m venv venv create a python virtual environment
source venv/bin/activate activate the virtual environment.
pip install Django==4.1.4 python-dotenv install django and python-dotenv package. Python-dotenv package is used for securing our secret keys
django-admin startproject core . create django project in current directory. The dot after core represents the current directory
pip freeze > requirements.txt create a requirements.txt file in current directory with all the package installed in this virtual environment

This is my basic project directory tree:

Configuring Our Project:

Create a .env file in our project directory with touch .env
Setup our python-dotenv in settings.py of myProject/core/settings.py

import os
from dotenv import load_dotenv
load_dotenv(os.path.join(BASE_DIR, ".env"))

Create a new secret key for our project

(venv) $ python manage.py shell
>>> from django.core.management.utils import get_random_secret_key
>>> print(get_random_secret_key())
>>> exit()

Copy the secret key and paste it to .env file
Replace the secret key in settings.pyby pointing it to the .env file

SECRET_KEY = str(os.getenv("DJANGO_SECRET_KEY"))

Configure Django Database in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': str(os.getenv("DATABASE_NAME")),
        'USER': str(os.getenv("DATABASE_USER")),
        'PASSWORD': str(os.getenv("DATABASE_PASSWORD")),
        'HOST': str(os.getenv("DATABASE_HOST")),
        'PORT': 3306,
    }
}

Configure .env for database

DATABASE_NAME = "mysql_new_db"
DATABASE_USER = "myProject"
DATABASE_PASSWORD = "myProject"
DATABASE_ROOT_PASSWORD = "myProject"
DATABASE_HOST = "db"

Setup files for dockerizing our project and MySql: We need to create three files to dockerizing our project and MySql. They are .dockerignore, Dockerfile and docker-compose.yml. A Dockerfile contains all the commands a user could call on the command line to assemble an image. .dockerignore is similar to .gitignore. It allows to specify the list of files and folders to ignore when building a image. And docker-compose.yml a YAML file to define the services and we can also spin everything up or tear it all down.

Setup Dockerfile

Create a Dockerfile in project directory (venv) $ touch Dockerfile

FROM python:3
LABEL maintainer="ishanshrestha"
ENV PYTHONDONTWRITEBYTECODE=1
ENV PYTHONUNBUFFERED 1 
WORKDIR /app
COPY requirements.txt /app/
RUN pip install --upgrade pip
RUN pip install -r requirements.txt
COPY . /app/
EXPOSE 8000

FROM python:3 is the python docker image. It will be your base image your Docker image will be built upon.
LABEL maintainer="ishanshrestha" specifies the maintainer of your docker image. A maintainer can be your name or the website you own. It is a best practice to define a maintainer.
ENV PYTHONDONTWRITEBYTECODE=1 prevents Python from writing out files with extension .pyc .
ENV PYTHONUNBUFFERED 1 tells Python that you don't want to buffer the stdin/stdout. Prints output directly to the console, which prevents any delay of message on the screen.
WORKDIR /app is a default directory in your docker image where the all docker commands runs from.
COPY requirements.txt /app/ copies earlier created requirements.txt to the docker image working directory.
RUN pip3 install -- upgrade pip upgrade the pip in the docker image.
RUN pip install -r requirements.txt installs all the packages defined in the requirements.txt file.
COPY . /app/ copies our project to working directory of your docker image.
EXPOSE 8000 exposes the container to your local machine

Setup docker-compose.yml

Create a docker-compose file in project directory (venv) $ touch docker-compose.yml

version: "3.9"

services:
  db:
    container_name: mysql_new_db
    image: mysql:latest
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_DATABASE=${DATABASE_NAME}
      - MYSQL_USER=${DATABASE_USER}
      - MYSQL_PASSWORD=${DATABASE_PASSWORD}
      - MYSQL_ROOT_PASSWORD=${DATABASE_ROOT_PASSWORD}
      - MYSQL_HOST=${DATABASE_HOST}
    volumes:
      - .:/app
      - mysql_new_data:/var/lib/mysql

  app:
    container_name: app
    build: .
    command: python manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/app
    ports:
      - 8000:8000
    depends_on:
      - db
    restart: always

volumes:
  mysql_new_data:

We define two services int the docker-compose file. These services db and app are the two containers we are going to create. "db" refers to our MySql container and "app" refers to our project container. ### Lets break our "db" section:
container_name: mysql_new_db is the name of the container
image: mysql:latest is the MySql docker image that we pull from docker hub
restart: always sets the restart policy of Docker container to
port: 3306:3306 defines the port for our MySql Server
environment: defines the environment variables for MySql Server
volumes: defines volume mapping of database storage

Now, lets break our "app" section:

build: . points to project directory when building the image
command: runs our django projects
ports: exposes our project image to the local machine
depends_on: sets the order of image for start/stop. In our case, MySql container/image must start before our app image/container.

Setup dokcerignore

git
.gitignore

.docker
code/__pycache__/
code/*/__pycache__/
code/*/*/__pycache__/
code/*/*/*/__pycache__/
.env/
.venv/
venv

Final myProject directory tree:

Final project directory treeAppend mysqlclient==2.1.1 to requirements.txt

Build and run the docker container

To build and run the docker continer:

(venv) $ docker-compose up -d --build

It will take some time to build the image for first time. It uses caching system to speed up the image building for second time.
In command -d tag tells docker to run in the background. To stop the conatiner, run the command

docker-compose down

View the container running

$ docker ps
CONTAINER ID   IMAGE           COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
2303bef6e116   myproject-app   "python manage.py ru…"   5 minutes ago   Up 5 minutes   0.0.0.0:8000->8000/tcp, :::8000->8000/tcp              app
b2ea4a7ba174   mysql:latest    "docker-entrypoint.s…"   5 minutes ago   Up 5 minutes   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql_new_db

Running manage.py commands

$ docker ps
(venv) $ docker-compose run exec app manage.py makemigrations
(venv) $ docker-compose run exec app manage.py migrate
(venv) $ docker-compose run exec app manage.py startapp

Project Link