Databases

API

Algorithm

Bug

Debugging

Data structure

Cache

Framework

UX Design

Web App

Library

UI Design

Architecture

DBMS

RDBMS

noSQL

SQL

Databases

DBMS

RDBMS

noSQL

SQL

Standardized

Easy to learn

Huge community

Transactional

MySQL - Master class

Getting started with SQL

  • Data Control Language (DCL)
  • DB Engines
  • Data types in SQL
  • Data Definition Language (DDL)

Data Control Language (DCL)

REVOKE

GRANT 

GRANT privilege_name
ON object_name
TO {user_name | PUBLIC | role_name}
[with GRANT option];
  • Data Control Language (DCL)
  • DB Engines
  • Data types in SQL
  • Data Definition Language (DDL)
REVOKE privilege_name
ON object_name
FROM {User_name | PUBLIC | Role_name}

Data Control Language (DCL)

REVOKE

GRANT 

GRANT SELECT
ON users
TO ahmedosama@localhost;
REVOKE privilege_name
ON object_name
FROM {User_name | PUBLIC | Role_name}

Data Control Language (DCL)

REVOKE

GRANT

Privilege levels

Global

Database

Table

Column

Procedure

Proxy 

GRANT SELECT, UPDATE
ON *.*
TO ahmedosama@localhost;
GRANT SELECT, UPDATE
ON sectheater.*
TO ahmedosama@localhost;
GRANT SELECT, UPDATE
ON sectheater.users
TO ahmedosama@localhost;
GRANT
	SELECT(id, first_name, last_name, email),
	UPDATE(is_active)
	ON sectheater.users
TO ahmedosama@localhost;
GRANT EXECUTE
 ON PROCEDURE custom_procedure
TO ahmedosama@localhost;
GRANT PROXY
 ON root
TO ahmedosama@localhost;

Data Control Language (DCL)

REVOKE

GRANT

Roles

Viewer

Moderator

Admin 

CREATE ROLE
	moderator,
	administrator,
	viewer;
GRANT
	SELECT,
	INSERT,
	UPDATE,
	DELETE
ON
	playground.*
TO
	moderator;





GRANT ALL ON playground.* TO adminstrator;








GRANT SELECT ON playground.* TO viewer;

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Transaction rollbacks Yes No
Full text search Yes Yes
Foreign Key Yes No
Data-access method Row-based locks Table-based locks
Optimized for General SQL Heavy data read

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Transaction rollbacks Yes No

Op #1

Op #2

Op #3

Some data

Result

Execution Thread

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Transaction rollbacks Yes No

Op #1

Op #2

Op #3

Some data

Result

Execution Thread

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Foreign Keys Yes No

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Data-access method Row-based locks Table-based locks
Name Balance
Ahmed 1000
Bob 600
Michael 1200

Transaction commited

DB Engines

MyISAM

InnoDB

Engine name InnoDB MyISAM
Data-access method Row-based locks Table-based locks
Name Balance
Ahmed 1200
Bob 600
Michael 1200

Transaction commited

Reading reports

Reading reports

Transferring 300 to Bob

Name Balance
Ahmed 1200
Bob 900
Michael 900

Transferring 300 to Bob

Data types in SQL

Dates

Numeric

Strings

Enum

JSON

Data Definition Language (DDL)

COMMENT

TRUNCATE

DROP

Create

ALTER

Data Definition Language (DDL)

ALTER

COMMENT

TRUNCATE

DROP

Create 

CREATE [
      database,
      table,
      view,
      index,
      trigger,
      procedure
    ]
    item_name [IF NOT EXISTS] [...creation options];

Data Definition Language (DDL)

ALTER

COMMENT

TRUNCATE

DROP

Create 

CREATE DATABASE playground;

CREATE DATABASE playground IF NOT EXISTS;

CREATE DATABASE
	playground
	IF NOT EXISTS
	CHARACTER SET = utf8
	COLLATE utf8_general_ci;

Data Definition Language (DDL)

ALTER

COMMENT

TRUNCATE

DROP

Create 

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}

Data Definition Language (DDL)

ALTER

COMMENT

TRUNCATE

DROP

Create 

ALTER DATABASE playground
	CHARACTER SET utf8
    COLLATE uft8_general_ci;

Data Definition Language (DDL)

ALTER

COMMENT

TRUNCATE

DROP

Create 

DROP DATABASE playground;


















DROP USER ahmedosama@localhost;



















DROP ROLE moderator;

MySQL - Master class

SQL tables, DML and modes

  • Creating tables, altering them
  • Data modification language (DML)
    • Insert
    • Update
    • Delete
  • SQL variables && Modes
  • Creating tables, altering them
  • Data modification language (DML)
    • Insert
    • Update
    • Delete
CREATE TABLE [IF NOT EXISTS] table_name(
   column_name data_type(length)
     [NOT NULL]
     [DEFAULT value]
     [AUTO_INCREMENT]
     column_constraint,
   ...,
   table_constraints
) ENGINE=storage_engine;





CREATE TABLE [IF NOT EXISTS] table_name LIKE another_table;









CREATE TABLE [IF NOT EXISTS] table_name SELECT (selection) FROM original_table;

MySQL - Master class

SQL tables, DML and modes

CREATE DATABASE IF NOT EXISTS playground
CHARACTER SET utf8
COLLATE utf8_general_ci;
















CREATE TABLE IF NOT EXISTS users(
    id INT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    email VARCHAR(64) NOT NULL,
    password VARCHAR(60) NOT NULL,
    gender ENUM('male', 'female', 'unspecified') DEFAULT 'unspecified',
    address VARCHAR(40) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
) ENGINE=InnoDB;

MySQL - Master class

SQL tables, DML and modes

ALTER TABLE table_name
ADD
    new_column_name column_definition
    [FIRST | AFTER column_name]

MySQL - Master class

SQL tables, DML and modes

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name];

MySQL - Master class

SQL tables, DML and modes

ALTER TABLE table_name
    CHANGE COLUMN original_name new_name column_definition
    [FIRST | AFTER column_name];

ALTER TABLE table_name
DROP COLUMN column_name;

MySQL - Master class

SQL tables, DML and modes

INSERT INTO table_name (...columns) VALUES(...values);

INSERT INTO table_name SET column = value;

INSERT INTO table_name (...columns) VALUES(...values) ON DUPLICATE KEY UPDATE (expr);

MySQL - Master class

SQL tables, DML and modes

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

MySQL - Master class

SQL tables, DML and modes

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

MySQL - Master class

SQL tables, DML and modes

  • SELECT [options]
  • Clauses
    • WHERE && Expressions
    • Limit, order by
  • Insert into select
  • Storing select values
  • SQL functions
  • Grouping data
  • Having conditions
  • Roll up clause

MySQL - Master class

Select statement - part 1

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references]
    [JOIN table_reference]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

MySQL - Master class

Select statement - part 1

Where clause is used as a data filter to allow only selection of data to be returned by the SQL server

SELECT [selection]
	FROM {table_name}
	[WHERE {expression}];

MySQL - Master class

Select statement - part 1

Operator precedence

  • Parentheses
  • Multiplication / Division
  • Addition / Subtraction
  • AND
  • NOT
  • OR

MySQL - Master class

Select statement - part 1

Limit and order by clauses are used to control the data returned

SELECT
	*
FROM
	users
WHERE
	id IN (1, 2, 3, 4, 5, 6)
ORDER BY
	id DESC
LIMIT
	2, 5;

MySQL - Master class

Select statement - part 1

Insert into select statement is inserting a data into a table where you fetch this data from another table

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

INSERT INTO ta TABLE tb;

MySQL - Master class

Select statement - part 1

Storing return values from a select statement into variables or files

SELECT
	username,
	email,
	name INTO @username,
	@email,
	@name
FROM
	users
WHERE
	id = 1;

SELECT
	@username;

SELECT
	*
FROM
	users INTO OUTFILE '/home/ahmedosama/test.txt';

MySQL - Master class

Select statement - part 1

SQL Functions

Aggregate

String

Date

Window

Math

MySQL - Master class

Select statement - part 1

Grouping data / Having / Roll up

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
GROUP BY
    group_by_expression WITH ROLLUP
HAVING
    group_condition;

MySQL - Master class

Select statement - part 1

  • Window functions
  • JSON data manipulation
  • Select statement clauses order
  • SubQueries && derived tables
  • Data sets
    • Exists
    • Intersect
    • Union
    • Minus

MySQL - Master class

Select statement - part 2

MySQL - Master class

Select statement - part 2

Window functions

Partitioning data sets and querying against each partition

Aggregate functions

Window functions

-- Window function spec

SELECT function(selection)
  OVER(
      [partition by column]
      [order by column]
      [
      	{ROW|RANGE} {[BETWEEN {lower|upper}]| N}
      	{PRECEDING|FOLLOWING|UNBOUNDED PRECEDING|UNBOUNDED FOLLOWING}
      ]
  )
FROM table_name [WHERE clause] [LIMIT] [ORDER BY];

MySQL - Master class

Select statement - part 2

JSON data

CREATE TABLE roles(
	id int UNSIGNED NOT NULL AUTO_INCREMENT,
	name varchar(16) NOT NULL,
	permissions JSON NOT NULL,

	PRIMARY KEY (id, name)
);
INSERT INTO
	roles(name, permissions)
VALUES
	(
		'user',
		'{"add_post": true, "delete_own_post": true, "delete_others_post": false}'
	),
	(
		'admin',
		'{"add_post": true, "delete_own_post": true, "delete_others_post": true}'
	);

MySQL - Master class

Select statement - part 2

SELECT clauses order

FROM / Joins/ SubQueries

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

DISTINCT clause

ORDER BY

LIMIT

MySQL - Master class

Select statement - part 2

SubQueries && Derived tables

A subquery or known as "Derived table" is the result of performing an inner query within your SELECT statement within any clause (FROM, WHERE)

SELECT
	*
FROM
	users
WHERE
	role_id IN (
		SELECT
			id
		FROM
			roles
		WHERE
			role = 'admin'
	);
  • SQL keys
  • Relational tables
    • One to one
    • One to many
    • Many to many
    • Self-referencing
    • Polymorphic
  • SQL joins
    • Inner
    • Outer (Left, Right, Full)
    • Cross
    • Self

MySQL - Master class

SQL relationships

MySQL - Master class

SQL relationships

One to one

Profile

Head

Workshop

Teacher

Committee

User

MySQL - Master class

SQL relationships

One to many

Projects

Teachers

Users

Plan

School

Student

Many to many

Projects

Courses

Instructors

Courses

Students

Students

MySQL - Master class

SQL relationships

Self referencing

Manager

Tag

Category

Category

Tag

Employee

MySQL - Master class

SQL relationships

Polymorphic

Song

Album

Article

Course

Bookmarkable

Likeable

MySQL - Master class

SQL relationships

Join

MySQL - Master class

Join statements

INNER join

Left/Right join

Cross join

MySQL - Master class

DB Normalization

What, Why, How?

  • Update anomaly
  • Insertion anomaly
  • Deletion anomaly

Elimination of redundant data and waste storage

Normalization forms

MySQL - Master class

DB Normalization

Functional Dependency

MySQL - Master class

DB Normalization

First normal form (1NF)

  • Only atomic values are allowed
  • Non-composite values
  • No repeated values
id first_name last_name phone
1 Ahmed Osama +20123456789, +201018549410
2 Mahmoud Khaled +20123456494, +201018451878

Not in first form

MySQL - Master class

DB Normalization

First normal form (1NF)

  • Only atomic values are allowed
  • Non-composite values
  • No repeated values
id first_name last_name phone
1 Ahmed Osama +20123456789
1 Ahmed Osama +20101854941
2 Mahmoud Khaled +20123456494
2 Mahmoud Khaled +20101845187

MySQL - Master class

DB Normalization

First normal form (1NF)

  • Only atomic values are allowed
  • Non-composite values
  • No repeated values
id first_name last_name country_code phone
1 Ahmed Osama +20 123456789
1 Ahmed Osama +20 101854941
2 Mahmoud Khaled +20 123456494
2 Mahmoud Khaled +20 101845187

MySQL - Master class

DB Normalization

First normal form (1NF)

  • Only atomic values are allowed
  • Non-composite values
  • No repeated values
id first_name last_name
1 Ahmed Osama
2 Mahmoud Khaled
id country_code phone user_id
1 +20 1153820570 1
2 +20 1104578244 1
3 +20 1234567891 2
4 +20 1514177489 2

In first form

MySQL - Master class

DB Normalization

Second normal form (2NF)

  • Must be in 1NF
  • No partial dependency
emp_id project_id hours rph emp_name emp_age
1 2 15 30 ahmed 19
2 1 30 50 mahmoud 23

MySQL - Master class

DB Normalization

Second normal form (2NF)

  • Must be in 1NF
  • No partial dependency
emp_id emp_name emp_age
1 Ahmed Osama 19
2 Mahmoud Magued 30
project_id working_hour rate_per_hour
1 15 40
2 30 50

MySQL - Master class

DB Normalization

Third normal form (3NF)

  • Must be in 2NF
  • No transitive dependency
emp_name ssn address Dnumber Dmngr_ssn
Ahmed 251841 221b baker street 30 221510
Mohamed 184512 8th coding street 50 251841

MySQL - Master class

DB Normalization

Third normal form (3NF)

  • Must be in 2NF
  • No transitive dependency
emp_name ssn address dep_number
Ahmed 251841 221b baker street 30
Mohamed 184512 8th coding street 50
dep_number dep_manager
30 221510
50 251841

MySQL - Master class

DB Normalization

Boyce Codd normal form (BCNF)

  • Must be in 3NF
  • A prime attribute should not depend on a non-prime attribute
student_id subject_id student_name subject_name professor_name
15840 251841 Ahmed Osama EDA Kyle
32801 184512 Mahmoud Khaled DDD Will

MySQL - Master class

DB Normalization

Boyce Codd normal form (BCNF)

  • Must be in 3NF
  • A prime attribute should not depend on a non-prime attribute
student_id subject_id student_name subject_name
15840 251841 Ahmed Osama EDA
32801 184512 Mahmoud Khaled DDD
subject_id professor_name
251841 kyle
184512 will

MySQL - Master class

DB Normalization

Fourth normal form (4NF)

  • Must be in BCNF
  • No multi-valued dependency
student_id course hobby
15840 PHP reading
15840 JavaScript playing chess
15842 Arduino reading
15843 PHP playing chess

MySQL - Master class

DB Normalization

Fourth normal form (4NF)

  • Must be in BCNF
  • No multi-valued dependency
student_id course
15840 PHP
15840 JavaScript
15842 Arduino
15843 PHP
student_id Hobby
15840 reading
15840 playing chess
15842 reading
15843 playing chess

MySQL - Master class

DB Normalization

Fifth normal form (5NF)

  • Must be in 4NF
  • No Join dependency
id course student instructor
15840 PHP ahmed osama khaled ali
15841 JavaScript mahmoud bahaa ismael nassar
15842 Arduino moaaz fadl mina remon
15843 TypeScript mona samir Asmaa osama

MySQL - Master class

DB Normalization

Fifth normal form (5NF)

  • Must be in 4NF
  • No Join dependency
id course
15840 PHP
15841 JavaScript
15842 Arduino
15843 TypeScript
id student
15840 ahmed osama
15841 mahmoud bahaa
15842 moaaz fadl
15843 mona samir
id instructor
15840 khaled ali
15841 ismael nassar
15842 mina remon
15843 asmaa osama
  • Transactions
  • Stored procedures
  • Triggers
  • Indexes
  • Views
  • Data modeling
    • Data flow diagram (DFD)
    • Entity relation diagram (ERD)

MySQL - Master class

Advanced SQL usage

MySQL - Master class

SQL Transactions

A transaction is a controllable set of queries that must go on altogether.

-- 1- start the transction process

START TRANSACTION

-- 2- set of queries to be run

DELETE from users;

-- 3- Submit or revoke changes

{ROLLBACK|COMMIT}

MySQL - Master class

Stored procedures

A procedure is a set of queries that are stored altogether as one executable function later.

CREATE FUNCTION FN_NAME(...{param, defintiion})
RETURNS TYPE [DETERMINISTIC| NOT DETERMINISTIC]
RETURN VALUE

CREATE PROCEDURE NAME(...{param, defintiion})
BEGIN
	STATEMENTS
END;

MySQL - Master class

Triggers

A trigger is like an event that observes an action and acts before or after the event was emitted.

CREATE TRIGGER trigger_name
  {BEFORE|AFTER {INSERT|UPDATE|DELETE}}
  ON table_name [FOR EACH ROW]

BEGIN
  STATEMENTS
END;

MySQL - Master class

Indexes

An index is a special pointer to the data stored in a table that can be accessed faster than any other column

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3)
);

CREATE INDEX index_name ON table_name (column_list)

DROP INDEX index_name ON table_name

ALTER TABLE ADD INDEX index_name ON tablE_name (column_list)

MySQL - Master class

Views

Views give you the ability to store the result from a statement and create a table that stores the outcome which you can query against. 

CREATE VIEW customerPayments
AS
EXECUTION;

MySQL mastery class from basics to advanced

By Security Theater

Made with Slides.com

MySQL mastery class from basics to advanced

If you're new to database management or just got the basics of querying against a database, these slides alongside the corresponding course will take you to a whole next level in managing your databases for your backend applications.

  • 75

More from Security Theater