Part 01

Create New Table


            CREATE DATABASE NAME_DATABASE;
        
            CREATE DATABASE company;

SHOW DATABASES

            SHOW DATABASE;
{ or } 
            SHOW SCHEMAS;

DROP DATABASE

            DROP DATABASE NAME_DATABASE;
        
            DROP DATABASE company;

SHOW COLUMN FROM TABLE

            USE NAME_DATABASE
            SHOW COLUMNS FROM NAME_TABLE
{ or } 
            USE company;
            SHOW COLUMNS FROM Employee;
            

                

                    id
                    int
                

                

                    Employee_name
                    varchar(255)
                

                

                    Employee_salary
                    int
                

                

                    Employee_bonus
                    int

ORDER BY

            USE NAME_DATABASE;
            SELECT * FROM NAME_TABLE ORDER BY COLUMN_NAME ASC;
            or
            SELECT * FROM NAME_TABLE ORDER BY COLUMN_NAME DESC;
        
            USE company;
            SELECT * FROM employee ORDER BY Employee_name ASC;        
            

                

                    id
                    Employee_name
                    Employee_salary
                    Employee_bonus
                

                

                    6
                    Abdullah
                    6000
                    1000
                

                

                    1
                    Ahmad
                    5000
                    15000
                

                

                    2
                    Ali
                    5500
                    1500
                

                

                    5
                    Nasser
                    5500
                    1500
                

                

                    4
                    Nora
                    5000
                    2000
                

                

                    3
                    Sarah
                    5500
                    null

Part 02

Create Table

            USE NAME_DATABASE;
            CREATE TABLE NAME_TABLE(
                NAME_COLUMNS Type_Column,
                NAME_COLUMNS Type_Column(SIZE VARCHAR),
                NAME_COLUMNS Type_Column,
            );
        
            CREATE TABLE employee (
                id INT(255),
                Employee_name VARCHAR(255),
                Employee_Salary INT,
                Employee_bonus INT
            );

Show Columns From Table

            USE NAME_DATABASE;
            SELECT NAME_COLUMN , NAME_COLUMN FROM NAME_TABLE;
        
            USE company;
            SELECT id, employee_name, employee_salary, employee_bonus FROM NAME_TABLE;
        
            

                

                    id
                    Employee_name
                    Employee_salary
                    Employee_bonus
                

                

                    null
                    null
                    null
                    null

Add Column

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE ADD NAME_COLUMN(TYPE_COLUMN)
        
            ALTER TABLE employee ADD employee_mail VARCHAR(255);

Modify column type

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE MODIFY COLUMN NAME_COLUMN(TYPE);
        
            ALTER TABLE employee MODIFY COLUMN id VARCHAR( 255 );

Drop column

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE DROP COLUMN NAME_COLUMN;
        
            ALTER TABLE employee DROP COLUMN idTow;

Show Tables

            USE NAME_DATABASE;
            SHOW TABLES; 
        
            USE company;
            SHOW TABLES;

Rename table

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE RENAME NEW_NAME_TABLE;
        
            USE company;
            ALTER TABLE employee RENAME users;

Drop table

            USE NAME_DATABASE;
            DROP TABLE NAME_TABLE;
        
            USE company;
            DROP TABLE employee;

Part 03

insertValue

            USE NAME_DATABASE;
            INSERT INTO NAME_TABLE(NAME_COLUMN, NAME_COLUMN, NAME_COLUMN, NAME_COLUMN) 
                            VALUES (VALUE, VALUE, VALUE, VALUE);
        
            INSERT INTO employee (id, employee_name, employee_mail, employee_salary, employee_bonus) 
                    VALUES (1, "Abdullah", "example@example.com", 5000, 1000);

insertMult

            USE NAME_DATABASE;
            INSERT INTO NAME_TABLE
                            VALUES (VALUE, VALUE, VALUE, VALUE);
        
            INSERT INTO employee 
                    VALUES(1, "Abdullah", "example@example.com", 5000, 1000),
                            (2, "Ali", "example@example", 3000, 1000);

Show Where

            USE NAME_DATABASE;
            SELECT * FROM NAME_TABLE WHERE NAME_COLUMN = "VALUE";
        
            USE NAME_DATABASE;
            SELECT * FROM employee WHERE employee_name = "Ali";

Update Row Value

            USE NAME_DATABASE;
            UPDATE NAME_TABLE
            SET NAME_COLUMN = VALUE
            WHERE NAME_PRIMARY_kEY="VALUE";
        
            USE company;
            UPDATE employee SET employee_mail = "test@test.com" WHERE id="3"

Delete Row Value

            USE NAME_DATABASE;
            DELETE FROM NAME_TABLE WHERE NAME_COLUMN="VALUE";
        
            USE company;
            DELETE FROM employee WHERE id="6";

Group Value From Select Row

            USE NAME_DATABASE;
            SELECT NAME_COLUMN FROM NAME_TABLE GROUP BY NAME_COLUMN;
        
            USE company;
            SELECT employee_salary FROM employee GROUP BY employee_salary;

Part 04

Add Primary Key

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE ADD PRIMARY KEY (NAME_COLUMN);
        
            USE company;
            ALTER TABLE employee ADD PRIMARY KEY (id);

Search Value From Column

            USE NAME_DATABASE;
            SELECT NAME_COLUMN FROM NAME_TABLE WHERE NAME_COLUMN LIKE(" VALUE % " || "VALUE -")
        
            for multi character use %
            USE company;
            SELECT employee_name FROM employee WHERE employee_name LIKE("A%" || "%A" || "A%__")
{ or } 
            for one character use -
            USE company;
            
            SELECT employee_name FROM employee WHERE employee_name LIKE("A-" || -A);

Get Distinct Data From Column

            USE NAME_DATABASE;
            SELECT DISTINCT NAME_COLUMN FROM NAME_TABLE;
        
            USE company;
            SELECT DISTINCT employee_name FROM employee;

AS

            USE NAME_DATABASE;
            SELECT NAME_COLUMN AS NEW_NAME FROM NAME_TABLE;
        
            USE company;
            SELECT employee_name as name FROM employee;

Copy Table

            USE NAME_DATABASE;
            CREATE TABLE NAME_COPY_TABLE SELECT NAME_COLUMNS FROM NAME_ORIGINAL_TABLE;
        
            USE company;
            CREATE TABLE employee_copy SELECT * FROM employee;

Part 05

Aggregate Function | Syntax :

            USE NAME_DATABASE;

            SELECT Aggregate_Functions(NAME_COLUMN)
            FROM NAME_TABLE 
            WHERE conditions;

AVG()Aggregate Function

            USE NAME_DATABASE;

            SELECT AVG(NAME_COLUMN)
            FROM NAME_TABLE;
        
            USE NAME_DATABASE;

            SELECT AVG(employee_salary)
            FROM employee;

MAX() Aggregate Function

            USE NAME_DATABASE;

            SELECT MAX(NAME_COLUMN)
            FROM NAME_TABLE;
        
            USE NAME_DATABASE;

            SELECT MAX(employee_salary)
            FROM employee;

MIN() Aggregate Function

            USE NAME_DATABASE;

            SELECT MIN(NAME_COLUMN)
            FROM NAME_TABLE;
        
            USE NAME_DATABASE;

            SELECT MIN(employee_salary)
            FROM employee;

SUM() Aggregate Function

            USE NAME_DATABASE;

            SELECT SUM(NAME_COLUMN)
            FROM NAME_TABLE;
        
            USE NAME_DATABASE;

            SELECT SUM(employee_salary)
            FROM employee;

Part 06

Numeric Functions

            

                

                    Function
                    Description
                

                

                    POW()
                    ترجع قيمة رقم مرفوع إلى أس رقم آخر
                

                

                    MOD()
                    ترجع باقي القسمة للعددين
                

                

                    ABS()
                    ترجع القيمة المطلقة للعدد
                

                

                    DIV()
                    ترجع قسمة عددين

POW() Numeric Functions

            SELECT POW(NUM, NUM);
        
            SELECT POW(3, 2);

ABS() Numeric Functions

            SELECT ABS( -NUM);
        
            SELECT ABS(-3);

DIV Numeric Functions

            SELECT NUM DIV NUM;
        
            SELECT 5 DIV 2;

Part 07

Foreign Key

            USE NAME_DATABASE;

            CREATE TABLE NAME_TABLE(
                NAME_COLUMN TYPE_COLUMN NOT NULL,
                NAME_COLUMN TYPE_COLUMN NOT NULL,
                NAME_COLUMN TYPE_COLUMN NOT NULL,
                PRIMARY KEY(NAME_COLUMN),
                FOREIGN KEY (NAME_COLUMN) REFERENCES (NAME_TABLE(NAME_COLUMN))
            )
        
            use company;
            CREATE TABLE product(
                product_id INT NOT Null,
                product_name INT NOT NULL,
                company_id INT,
                PRIMARY KEY (product_id),
                FOREIGN KEY (company_id)REFERENCES company(company_id)
            );

Foreign Key By Alter Table

            USE NAME_DATABASE;
            ALTER TABLE NAME_TABLE ADD FOREIGN KEY (NAME_COLUMN) REFERENCES NAME_TABLE(NAME_COLUMN);
        
            USE company;
            ALTER TABLE product ADD FOREIGN KEY (company_id) REFERENCES NAME_TABLE(company_id);

Part 08

Relations

Part 09

Inner join | Syntax :

            SELECT NAME_COLUMNS FROM INNER JOIN NAME_TABLE_02 ON NAME_TABLE_01.COLUMN_NAME = NAME_TABLE_02.COLUMN_NAME;
        
            SELECT product_id, company_name from product INNER JOIN company ON product.company_id = company.company_id

LEFT OUTER JOINS | Syntax :

            SELECT NAME_COLUMNS FROM NAME_TABLE_01 LEFT OUTER JOIN NAME_TABLE_02 ON NAME_TABLE_01.NAME_COLUMN = NAME_TABLE_02.NAME_COLUMN;
        
            SELECT employee_name, employee_id, project_id FROM employee LEFT OUTER JOIN project ON employee_name = project_manager;

RIGHT OUTER JOINS | Syntax :

            SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column = name_table2.column_name;            
        
            SELECT product_id, company_name FROM product INNER JOIN company ON product.company_id = company.company_id;

CROSS JOINS | Syntax :

            USE NAME_DATABASE;
            SELECT column1, column2,... FROM table1 CROSS JOIN table2;
        
            USE company;
            SELECT * FROM employee CROSS JOIN department;

NATURAL JOINS | SYNTAX

            USE NAME_DATABASE;
            SELECT [column_name | *]
            FROM table_name1
            NATURAL JOIN table_name2;
        
            USE company;
            SELECT company_name, product_name
            FROM company
            NATURAL JOIN product;

Part 10

CREATE VIEW | SYNTAX

            USE NAME_DATABASE;
            CREATE VIEW NAME_VIEW
            AS SELECT NAME_COLUMN... FROM NAME_TABLE
            WHERE YOUR_STATEMENT;     
        
            USE company;
            CREATE VIEW 
            passed_students 
            AS SELECT 
            name, course, exam_result
            FROM students
            WHERE exam_result = "Passed";

DROP VIEW | SYNTAX

            USE NAME_DATABASE;
            DROP VIEW NAME_VIEW;
        
            USE company;
            DROP VIEW passed_students;

Part 11

CREATE INDEX | SYNTAX

            USE NAME_DATABASE;
            CREATE INDEX ON INDEX_NAME
            ON TABLE_NAME(COLUMN_NAME);     
        
            USE company;
            CREATE INDEX ON employeeName
            ON employee(employee_name);

SHOW INDEX | SYNTAX

            USE NAME_DATABASE;
            SHOW INDEX FROM TABLE_NAME;     
        
            USE company;
            SHOW INDEX FROM employee;

DROP INDEX | SYNTAX

            USE NAME_DATABASE;
            DROP INDEX INDEX_NAME ON TABLE_NAME;     
        
            USE company;
            DORP INDEX employeeName On employee;

procedure | SYNTAX

            USE NAME_DATABASE;
            DELIMITER // 
            CREATE PROCEDURE procedure_name(parameter_list)
            BEGIN
            statements;
            END
        
            USE company;
            DELIMITER // 
            CREATE PROCEDURE display_employee()
            BEGIN
            SELECT * FROM employee;
            END

call procedure | SYNTAX

            USE NAME_DATABASE;
            CALL stored_procedure_name(argument_list);
        
            USE company;
            CALL display_employee;