MYSQL

mysql tutorial





DQL(Data Query Language): To fetch the data from the database
Example: SELECT
 
DML(Data Manipulation Language): To modify the database objects
Example: INSERT, UPDATE, DELETE

DDL(Data Definition Language): To create & modify database objects
Example: CREATE, DROP, ALTER, TRUNCATE



MYSQL COMMANDS

  1. FUNDAMENTALS OF SQL
  2. FILTERING COMMANDS
  3. ORDERING COMMANDS
  4. ALIAS
  5. AGGREGATE COMMANDS
  6. GROUP BY COMMANDS
  7. CONDITIONAL STATEMENT
  8. JOINS
  9. SUBQUERY
  10. VIEW & INDEX
  11. STRING FUNCTIONS
  12. MATHEMATICAL FUNCTIONS
  13. DATE-TIME FUNCTIONS
  14. PATTERN MATCHING(regex)
  15. DATA TYPE CONVERSION FUNCTIONS

1. Fundamentals of SQL


CREATE

CREATE statement is used to create a table 
Syntax: 

CREATE TABLE ‘’TABLE_NAME’’
(
    ‘’COLUMN1’’ ‘’DATA_TYPE’’ CONSTRAINTS,
    ’COLUMN2’’ ‘’DATA_TYPE’’ CONSTRAINTS,
    ‘’COLUMN3’’ ‘’DATA_TYPE’’ CONSTRAINTS,
    --------------------------------------
    ‘’COLUMN N’’ ‘’DATA_TYPE’’ CONSTRAINTS
 );

CREATE TABLE if not exists user
(
    id int unsigned,   /* id is unsigned because id will never be negative */
    name varchar(20),   /* name will no longer than 20 characters */
    email varchar (30),
    password varchar(120),      /* 120 length because it will be in encrpted form*/
    contact varchar(14),     /* String type because of + sign */
    address varchar(30),
    date_of_birth date,
    gender enum("m", "f", "o"),
    status bool
)



INSERT 

INSERT statement is used to insert new data into the table 
Syntax: 
INSERT INTO “TABLE_NAME”
(
    COL1,
    COL2,
    ……..
    COL_N
)
VALUES
(
    Col_val_1,
    Col_val_2,
    …….
    Col_val_N
);

-- First Method of passing values
-- In this Method we will give colums which we have in our tabel
INSERT INTO user(id, name, email, password, contact, address, date_of_birth, gender, status)
VALUES
(
        2025,
        "Muhammad Tayyab",
        "muhammadtayyab@gmail.com",
        "1234455",
        '49238974',
        'koi bhi',
        '1999-09-10',
        'm',
        1
);

-- Second Method of Passing Multiple Rows values
INSERT INTO user(id, name, email, password, contact, address, date_of_birth, gender, status)
VALUES
(
        2025,
        "Ali",
        "ali@gmail.com",
        "1200455",
        '49118974',
        'koi bhi',
        '2012-05-01',
        'm',
        1
),
(
        2025,
        "Sanam",
        "sanam@gmail.com",
        "1234355",
        '492343974',
        'koi bhi',
        '1999-09-10',
        'f',
        1
),
(
        2025,
        "Sanam",
        "sanam@gmail.com",
        "1234355",
        '492343974',
        'koi bhi',
        '2000-09-10',
        'f',
        1
);
   
-- Third Method of passing values
-- In this method we will not give the give the colums
INSERT INTO user
VALUES
(
        2025,
        "Arslan",
        "arslan@gmail.com",
        "1211455",
        '+049118974',
        'koi bhi',
        '1998-09-10',
        'm',
        1
),
(
        2025,
        "Ahmed",
        "ahmed@gmail.com",
        "1234300",
        '492343974',
        'koi bhi',
        '2001-01-10',
        'm',
        1
),
(
        2025,
        "Hania",
        "hania@gmail.com",
        "1277355",
        '+92343974',
        'koi bhi',
        '2000-09-10',
        'f',
        1
)

Import data from the file(PostgreSQL)

For CSV file 
COPY TABLE_NAME(
    column1, column2,
)
FROM FILE_PATH
    DELIMITER ‘,
CSV
    HEADER;

For txt file 

COPY TABLE_NAME
(
    column1, column2,
)

FROM FILE_PATH
    DELIMITER ‘,;

SELECT 

SELECT statement is used to retrieve data from the table 

Syntax: 

SELECT * FROM “TABLE_NAME”;

For select one column 

SELECT “COLUMN_NAME” FROM “TABLE_NAME”; 

For select multiple columns 

SELECT “COLUMN1, COLUMN2,…” FROM “TABLE_NAME”;

For select all columns 

SELECT * FROM “TABLE_NAME”;


DISTINCT 

DISTINCT keyword is used to eliminate all duplicate records & fetch only unique records 

Syntax: 


SELECT DISTINCT(*) FROM “TABLE_NAME”;


WHERE 

WHERE clause is used to filter a records 

Syntax: 

SELECT “COLUMN_NAME(S)” FROM “TABLE_NAME “ WHERE CONDITION;

AND/OR 

The AND/OR is used to combine multiple conditions 

Syntax: 

SELECT “COLUMN_NAMES(s)
FROM
“TABLE_NAME”
WHERE CONDITION AND/OR CONDITION;

UPDATE 

It is used to modify the existing data in the table 

Syntax: 

UPDATE “TABLE_NAME”
SET
    COL_1=VAL_1, COL_2=VAL_2,
WHERE
    CONDITION;

DELETE 

It is used to delete existing records in the table 

Syntax: 

For delete, all rows 

DELETE FROM “TABLE_NAME”;


For delete, single/multiple row(s) 

DELETE FROM “TABLE_NAME “ WHERE CONDITION;


ALTER 

It is used to change the definition or structure of the table 

Syntax: 

ADD COLUMN 

ALTER TABLE “ TABLE_NAME” ADD “COLUMN_NAME “ “DATA_TYPE”;


DROP COLUMN 

ALETR
    TABLE “TABLE_NAME”
DROP
    “COLUMN_NAME”;

MODIFY DATA TYPE 

ALTER TABLE “TABLE_NAME”
ALTER
    COLUMN “COL_NAME”
TYPE
    NEW_DATA_TYPE;



RENAME COLUMN 

ALTER TABLE “TABLE_NAME”
RENAME
    COLUMN “COL_NAME” TO “NEW_NAME”;


ADD CONSTRAINTS 

ALTER TABLE “TABLE_NAME”
ADD CONSTRAINT COL_NAME CHECK CONDITION;


2.Filtering Commands 

IN 

Used to reduce multiple OR logical operators in SELECT, DELETE, INSERT & UPDATE statements 

Syntax: 

SELECT “COL_NAME”
FROM
    “TABLE_NAME”
WHERE
    “COL_NAME” IN (‘VAL1’, ’VAL2’,);

BETWEEN 

Used to retrieve data within a given range 

Syntax: 

SELECT “COL_NAME(S)
FROM
    “TABLE_NAME”
WHERE
    “COL_NAME” BETWEEN “VAL1” AND “VAL2”;

LIKE 

Used to perform pattern matching/regex using wildcards(%, _) % - match any string of any length _ - match on a single character 

Syntax: 

SELECT
    “COL_NAME”
FROM
“TABLE_NAME”
WHERE
    “COL_NAME” LIKE ‘PATTERN’;

3. Ordering Commands 


ORDER BY 

Used to sort the data & it is only used in SELECT statement 

Syntax: 

SELECT
    “COL_NAME(s)
FROM
    “TABLE_NAME”
ORDER BY
    “COL_NAME” ASC/DESC;

LIMIT 

Used to limit the number of records based on a given limit 

Syntax: 

SELECT
    “COL_NAME(S)
FROM
    “TABLE_NAME”
[WHERE & ORDER BY – Optional]
    LIMIT “LIMIT_VALUE”;

4.ALIAS 


AS 

Used to assign an alias to the column 

Syntax: 


SELECT
    “COL_NAME” as “COL_ALIAS”
FROM
    “TABLE_NAME”;

5.AGGREGATE COMMANDS 


COUNT 

Used to count the expression 

Syntax: 

SELECT COUNT(COL_NAME) FROM “TABLE_NAME”;

SUM 

Used to sum the expression 

Syntax: 

SELECT SUM(COL_NAME) FROM “TABLE_NAME”;

AVG 

Used to average the expression 

Syntax: 

SELECT AVG(COL_NAME) FROM “TABLE_NAME”;

MIN 

Used to retrieve the minimum value 

Syntax: 

SELECT MIN(COL_NAME) FROM “TABLE_NAME”;

MAX 

Used to retrieve the maximum value 

Syntax: 

SELECT MAX(COL_NAME) FROM “TABLE_NAME”;

6.GROUP BY COMMANDS 


GROUP BY 

GROUP BY clause is used to group the results by one or more columns 

Syntax: 

SELECT
    “COL_1”, “COL_2”,……
FROM
    “TABLE_NAME”
GROUP BY
    “COL_NAME”;

HAVING 

HAVING clause is added to SQL because the WHERE keyword cannot be used with aggregate functions 

Syntax: 

SELECT
    “COL_1”, “COL_2”,……
FROM
    “TABLE_NAME”
GROUP BY
    “COL_NAME”
HAVING
    ‘CONDITION’;

7.CONDITIONAL STATEMENT 


CASE 

CASE expression is a conditional expression 

Syntax: 

CASE
    WHEN CONDITION THEN RESULT
    [WHEN CONDITION THEN RESULT]
    [WHEN CONDITION THEN RESULT]
    ELSE RESULT
END

8. JOINS 


JOINS used to fetch data from multiple tables 

TYPES OF JOINS: 

INNER JOIN 

INNER JOIN produces only the set of records that match in table A and table B 

Syntax

SELECT
    COL1,COL2,.. FROM “TABLE_1”
INNER
    JOIN “TABLE_2”
ON
    TABLE_1. COMMON_COL = TABLE_2. COMMON_COL;

LEFT JOIN 

LEFT JOIN returns all the rows in table A(Left), even if there are no matches in table B(Right) 

Syntax: 

SELECT
    COL_1,COL_2,… FROM “TABLE_1”
LEFT JOIN
    “TABLE_2”
ON
    TABLE_1. COMMON_COL = TABLE_2. COMMON_COL;

RIGHT JOIN 

RIGHT JOIN returns all the rows in table B(Right), even if there is no matches in table A(left) 

Syntax: 

SELECT
    COL_1, COL_2,
FROM
    “TABLE_1”
RIGHT JOIN
    “TABLE_2”
ON
    TABLE_1.COMMON_COL = TABLE_2. COMMON_COL;

FULL JOIN 

FULL JOIN combines the results of both right & left join 

Syntax: 


SELECT
    COL_1,COL_2,
FROM
    “TABLE_1”
FULL JOIN
    “TABLE_2”
ON
    TABLE_1.COMMON_COL = TABLE_2. COMMON_COL;


CROSS JOIN 

CROSS JOIN creates a Cartesian product between two sets 

Syntax: 

SELECT
    TAB1.COL,TAB2.COL,..
FROM
    “TABLE_1”, “TABLE_2”,……..


EXCEPT 


EXCEPT is used to fetch all the data from table A except that matches with table B 
Syntax: 
SELECT
COL1,COL2,……..
FROM TABLE_A [WHERE]
EXCEPT
SELECT COL_1,COL_2,……
FROM TABLE_B [WHERE];


UNION

UNION is used to combine two or more SELECT statements 

Syntax: 

SELECT
COL1,COL2,……..
FROM TABLE_A [WHERE]
UNION
SELECT COL_1,COL_2,……
FROM TABLE_B [WHERE];

SUBQUERY 

SUBQUERY is a query within a query 

Syntax: 

SUBQUERY is in WHERE clause
SELECT
    “COL_1” FROM “TABLE_NAME_1”
WHERE
    “COL_2” [operator] (SELECT “COL_3” FROM “TABLE_NAME_2” WHERE CONDITION);

VIEW 

VIEW is a virtual table created by a query joining one or more tables 

Syntax: 

CREATE[OR RESPONSE] view_name AS
SELECT
COL_NAME(S)” FROM “TABLE_NAME”

INDEX 

An INDEX creates an entry for each value that appears in the indexed column 

Syntax: 

CREATE[UNIQUE]
INDEX
“index_name” ON “TABLE_NAME” (index_col1 [ASC/DESC],………..

11.STRING FUNCTIONS 


LENGTH: 

LENGTH function retrieves the length of the specified string 

Syntax: 


LENGTH(‘string’)

UPPER/LOWER 

UPPER/LOWER function converts all the characters in the specified string to uppercase/lowercase 

Syntax: 

upper(‘string’) lower(‘string’)

REPLACE 

REPLACE function replaces all the occurrences of the specified string 

Syntax: 

REPLACE(‘string’, ’from string’, to string’)

TRIM 

TRIM function removes all specified characters either from beginning or end of the string or both 

Syntax:
 
TRIM( [Leading|Trailing|Both] [trim char] from string)

RTRIM 

RTRIM function removes all specified characters from RHS of the string 

Syntax: 

RTRIM(‘string’, trim char)

LTRIM 

LTRIM function removes all specified characters from LHS of the string 

Syntax:
 
LTRIM(‘string’, trim char)

CONCATENATION 

|| operator used to concatenate two or more strings 

Syntax: 

‘string_1’ || ‘string_2’ || ‘string_3’

SUBSTRING 

SUBSTRING function used to extract substring from a string 

Syntax:

SUBSTRING(‘string’ [start position] [substring length]);

STRING_AGG 

String aggregate function concatenates input values into a string, separated by a delimiter 

Syntax:

STRING_AGG(‘expression’, delimiter)

12.MATHEMATICAL FUNCTIONS 


CEIL 

CEIL function returns the smallest integer value which is greater than or equal to a number 

Syntax: 

CEIL(number)

FLOOR 

FLOOR function returns the largest integer value which is less than or equal to a number 

Syntax: 

FLOOR(number)

RANDOM 

RANDOM function used to generate a random number between 0 & 1 (1 will be excluded) 

Syntax:

RANDOM( );

SETSEED 

SETSEED function used to set a seed for the next time that we call the RANDOM function 

Syntax

SETSEED(seed) [seed can have a value between 1 and -1(both are inclusive]

ROUND 

ROUND function rounds a number to a specified number of decimal places 

Syntax: 

ROUND(number)

POWER 

POWER function returns m raised to the nth power 

Syntax: 

POWER(m,n)

13. DATE-TIME FUNCTIONS 

CURRENT_DATE 

CURRENT_DATE function returns the current date 

Syntax: 

CURRENT_DATE( )

CURRENT_TIME 

CURRENT_TIME function returns the current time with the time zone 

Syntax
 
CURRENT_TIME( )

CURRENT_TIMESTAMP 

CURRENT_ TIMESTAMP function returns the current date & current time with the time zone 
Syntax: 
CURRENT_ TIMESTAMP ( )

AGE 

AGE function returns the difference between two dates 
Syntax: 
AGE(date1,date2)

EXTRACT 

EXTRACT function extract specified parts from date 
Syntax: 

EXTRACT(‘unit’ FROM ‘date’)
[unit will be the day, month, year,day, decade, hour, minute, second, etc.,]

14.PATTERN MATCHING


There are three different approaches to pattern matching 
• Using LIKE 
• Using SIMILAR TO 
• Using Regular Expression 
• | denotes alternation (either of two alternatives). 
• * denotes repetition of the previous item zero or more times. 
• + denotes repetition of the previous item one or more times. 
• ? denotes repetition of the previous item zero or one time. 
• {m} denotes the repetition of the previous item exactly m times. 
• {m,} denotes repetition of the previous item m or more times. 
• {m,n} denotes repetition of the previous item at least m and not more than n times. 
• Parentheses () can be used to group items into a single logical item. 
• A bracket expression [...] specifies a character class, 

15.DATA TYPE CONVERSION FUNCTIONS 

TO_CHAR 

TO_CHAR function converts number/date to String 

Syntax: 

TO_CHAR(value,format-mask)

TO_DATE 

TO_DATE function converts a string to date 
Syntax: 

TO_DATE(string,format-mask)

T0_NUMBER 

T0_NUMBER function converts a string to date 

Syntax: 

TO_NUMBER(string,format-mask)