Logo

SQL Database

SQL stand for Structured Query Language. To understand SQL Database, You must know what is database. If you say, collection of meaning full words/information is called data then we can say that systematic collection of data is called database. We can storage and manipulate of this data using query language. So Structured Query Language is used for storing, manipulating and retrieving data in databases.

SQL is a Relational SQL Database Management System. There are a lots of many Relational databases like MS SQL Server, MySQL, IBM DB2, Oracle, and Microsoft Access. SQL comes up with many versions. Each new version comes up with new features.

If want to check databases, run following command.

EXEC sp_databases;
SHOW DATABASES;
.database;

SQL storage engines

Database storage engine is uses to create, read & update data from a database. In Mysql, there are two types of storage engines. transactional and non-transactional. There are many storage engine but most common storage engines are

  • InnoDB
  • MyISAM

In SQLite database, there are two types of storage engines

  • A log-structured merge-tree storage engine
  • In-memory binary tree storage engine

If you want to get to know, in which database engine you are working on, just write following command.

SHOW ENGINES;

Installation

You can download MySQL from www.mysql.com. There are many other softwares comes up with different packages like XAMPP, WAMPP, MySQL Workbanch and Navicate. I am going to install XAMPP. After installation XAMPP, open browser then open url http://localhost:phpmyadmin. It'll automatically log-in to database. You may log-off and log-in again. If you want to log-in user command, Run following command.

C:/PATH TO INSTALLED MYSQL> mysql -u root -p;


It'll ask for password. Just follow the instructions. if you want to log-off or quite from MySQL. Use following command.

exit;

Create, Select & Drop Database

As we already have explained that systematic collection of data is called database. You may create database using GUI wizard. But here we'll do using SQL command. First we'll check how many databases already have created using following command.

View Databases

To check how many & which databases do we have, we'll use following command.

SHOW DATABASES;
Database
information_schema
mysql
performance_schema
phpmyadmin

Create Databases

To create database we use keyword CREATE DATABASE. let's create two new databases. one is testdb and other is schooldb

CREATE DATABASE testdb;


and creating another database.

CREATE DATABASE schooldb;



Now run query show databases; again to see your created databases. It'll list all created databases like this.

Database
information_schema
mysql
performance_schema
phpmyadmin
testdb
schooldb

Each database have different Character Set and Collation. There are lots of Character Sets & Collation types for different purposes. We do not need to discuss them here as its basic level of tutorial.

Now we need to work on either testdb or schooldb. Let's remove/delete testdb and start work on other database.

Delete Databases

To remove/delete database, use following command.
DROP DATABASE testdb;
DROP DATABASE testdb;
DROP DATABASE testdb;
Let's select schooldb and start work on it.

Select Database

To select specific database, run following command.
USE schooldb;


Now you have selected schooldb to work on it. Its time to create database table. As database may have many tables and each table may have many fields. You have to tell, which is for what purpose? Means, if you want to store datetime into database then choose datatime data type. If you want to store decimal values then select float data type etc. Here is list of data type you may select.

TABLES

Database table has many components. Some of them are mandatory & some are optional. We have to tell system what type of database we are going to create. what should be its data collation. What attribute should it have & what index value it should be. Detail is as follow

Data Types

Data types defines the type of data is being store. Following are the data type we have

  • INT
  • VARCHAR
  • TEXT
  • DATE
  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • BIGINT
  • DECIMAL
  • FLOAT
  • DOUBLE
  • REAL
  • BIT
  • BOOLEAN
  • SERIAL
  • DATETIME
  • TIMESTAMP
  • TIME
  • YEAR
  • CHAR
  • TINYTEXT
  • MEDIUMTEXT
  • LONGTEXT
  • BINARY
  • VARBINARY
  • TINYBLOB
  • MEDIUMBLOB
  • BLOB
  • LONGBLOB
  • ENUM
  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON
  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

After selecting data type you have to give length/value for specific data type (Some data types are required). Then select collation which tells what form of data you are going to store. Let's suppose you want to store URDU, ARABIC, HINDI or RASSIAN language then you've to select utf-08 collation. e.g: utf8_croatian_ci, utf8_general_ci, utf8_german2_ci etc. Here is list of some collations.

Data Collations

  • armscii8_bin
  • armscii8_general_ci
  • ascii_bin
  • ascii_general_ci
  • big5_bin
  • big5_chinese_ci
  • cp1250_bin
  • cp1250_croatian_ci
  • cp1251_bin
  • cp1256_bin
  • cp1257_bin
  • cp850_bin
  • cp852_bin
  • cp866_bin
  • cp932_bin
  • dec8_bin
  • eucjpms_bin
  • euckr_bin
  • gb2312_bin
  • gbk_bin
  • geostd8_bin
  • greek_bin
  • hebrew_bin
  • hp8_bin
  • keybcs2_bin
  • koi8r_bin
  • latin1_bin
  • latin2_croatian_ci
  • latin5_bin
  • latin7_general_ci
  • macce_bin
  • macroman_bin
  • sjis_bin
  • swe7_bin
  • tis620_bin
  • ucs2_bin
  • utf16_bin
  • utf16le_bin
  • utf32_bin
  • utf8_bin
  • utf8mb4_bin

Attributes

An attribute specifies the attribute's syntax and how attributes of that type are compared and sorted. Following are some attributes we have

  • BINARY
  • UNSIGNED
  • UNSIGNED ZEROFILL
  • on update CURRENT_TIMESTAMP
Some fields are required to define indexes. Which are

Index Value

Index value is used to increase the performance of efficiently retrieving data from a database. Here are some index keys we use

  • PRIMARY
  • UNIQUE
  • INDEX
  • FULLTEXT
  • SPATIAL

Create, Select & Drop Tables

There are a lots of other options while creating a table. But we'll focus on some of them. Let's create a table tbl_school with field name of school in to two languages English & Urdu, EMIS Code of school, sort order, school status, saving date and saving datetime,

Syntax to create a table would be

CREATE TABLE TABLE_NAME (
				FIELD_NAME FIELD_TYPE (FIELD_LENGTH) COLLATION_TYPE,
			) ENGINE=DATABASE_ENGIN_NAME DEFAULT CHARSET=DATABASE_COLLATION_TYPE;
Here you can see, we have to provide table name and it's all fields with their fields type, length and collation type.

Create Table

To create database we shall use keyword CREATE TABLE.Let's create tbl_school now.

CREATE TABLE tbl_school (
  school_id int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  school_name varchar(255) NOT NULL,
  school_name_urdu varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  school_emis_code varchar(255) DEFAULT NULL,
  school_sort_order int(11) NOT NULL,
  school_status enum('Active','Deactive') NOT NULL,
  school_saving_date date NOT NULL,
  school_saving_datetime datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


To check how many record are there in created table, we have a command.

SELECT

A keyword SELECT is used fetch record from database table. We use either table field name separated by comma or use * with SELECT to fetch all table fields. e.g:

SELECT * FROM tbl_school;


It'll show following output:

Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
No record found.

It's time to insert some record into table as this table is empty at the moment.

INSERT

To insert record into table. We'll use following command.
INSERT INTO tbl_school(
			school_id, 
			school_name,
			school_name_urdu,
			school_emis_code,
			school_sort_order,
			school_status,
			school_saving_date,
			school_saving_datetime
			) 
		VALUES (NULL,
			"Sani school",
			"سنی",
			"SC335563",
			"1",
			"Active",
			"2018-11-29",
			"2018-11-29 12:00:00"
                       );

Now run following command to check your inserted record.

SELECT * FROM tbl_school;
It'll show you following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00

If you notice here. Field Id has some values even we have not assign any value while insertion. This is because This field is a primary key and is auto incremented. Check out the create table query again and you'll find where we have defined this field as primary key and auto incremented.
CREATE TABLE tbl_school (
  school_id int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
  school_name varchar(255) NOT NULL,
  school_name_urdu varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  school_emis_code varchar(255) DEFAULT NULL,
  school_sort_order int(11) NOT NULL,
  school_status enum('Active','Deactive') NOT NULL,
  school_saving_date date NOT NULL,
  school_saving_datetime datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
                

Update

The UPDATE statement is used to modify the existing records in a table. Let put urdu name for school Umer school. To do that we'll use following query.
UPDATE tbl_school
SET school_name_urdu = 'عمر'
WHERE school_id = 5;


Now check current table record.
SELECT * FROM tbl_school;
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00
4 Rizwan school NULL SC335566 4 Active 2018-11-30 2018-11-30 09:00:00
5 Umer school عمر SC335567 5 Active 2018-12-01 2018-12-01 09:00:00

Let's remove last two record from table. To do that we'll use DELETE keyword.

DELETE

The DELETE statement is used to delete existing records in a table. So to delete last two record separately. We'll use following two queries.
DELETE FROM tbl_school WHERE school_id = 5;


DELETE FROM tbl_school WHERE school_id = 4;
Now check how many record remains by using following query.
SELECT * FROM tbl_school;
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

Sometimes we have to sort the record either in ascending order or descending order. We'll do it using ORDER BY Clause.

ALTER

Alter key word is used to change/rename/modify table/field name etc. Let's add primary key into tbl_student.
ALTER TABLE tbl_student
ADD PRIMARY KEY (id);


Let's make this primary key AUTO_INCREMENTED so that it'll generate unique number on every insertion. To make changes in already added field you have to use modify keyword with Alter
ALTER TABLE tbl_student
MODIFY id int(11) NOT NULL AUTO_INCREMENT;


We have auto incremented field now. Let's insert some data in it.
INSERT INTO tbl_student(id,
                        name,
                        cnic,
                        phone,
                        school_id_fk,
                        roll_number,
                        status, 
                        saving_date,
                        saving_datetime
                       ) 
               VALUES (NULL,
                       "Alas",
                       "00000-0000000-0",
                       "0092 000 0000000",
                       "1",
                       "10",
                       "Active",
                       "2018-11-29",
                       "2018-11-29 11:00:00"

                      );
Get record from tbl_student
SELECT * FROM tbl_student
It'll show following output:
id name cnic phone school_id_fk roll_number status saving_date saving_datetime
1 Alas 00000-0000000-0 0092 000 0000000 1 10 Active 2018-11-29 2018-11-29 11:00:00

Here we have field value 1 for field school_id_fk. Where it comes from? It's comes from tbl_school as foreign key. Let's make relation between tbl_school & tbl_student so that you cannot delete school unless you delete its id from tbl_student. It's called refrential integration. Before creating relation. We have to index this field.

Primary Key

A primary key is a unique column that will identify each row in a table. It can be used as reference in other tables as foreign key.
Let's insert two more records.
INSERT INTO tbl_school(
			school_id, 
			school_name,
			school_name_urdu,
			school_emis_code,
			school_sort_order,
			school_status,
			school_saving_date,
			school_saving_datetime
			) 
		VALUES (NULL,
			"Hyne school",
			"ہا این",
			"SC335564",
			"2",
			"Deactive",
			"2018-11-29",
			"2018-11-29 12:01:00"
                       );
                       
                       INSERT INTO tbl_school(
			school_id, 
			school_name,
			school_name_urdu,
			school_emis_code,
			school_sort_order,
			school_status,
			school_saving_date,
			school_saving_datetime
			) 
		VALUES (NULL,
			"Delickate school",
			"ڈیلیکیٹ",
			"SC335565",
			"3",
			"Deactive",
			"2018-11-29",
			"2018-11-29 12:02:00"
                       );
                       


Now run following command to check your inserted record.

SELECT * FROM tbl_school;
It'll show you following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

If you have noticed in our query. We have used SELECT * FROM TABLE_NAME; What does * means?
* means all.
Which means select all fields from table. If you do not want to select All fields and you want to select only Id and name of the school. You may use following query.
SELECT school_id, school_name 
FROM tbl_school
It'll show following output.
Id Name
1 Sani school
2 Hyne school
3 Delickate school

You may show more columns by adding them in query separated by comma.
We have started playing with data now. Sometimes we have to fetch specific record only. As we have following record in our table now.
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

Let's suppose we want to get data of Sani school school only. For that we'll use WHERE clause.

INDEX

Index are used to speed up queries. Let's add index on school_id_fk
ALTER TABLE tbl_student ADD INDEX(school_id_fk);


It'll allow table field to relate to its origin table. It's time to create relation between tables.

FOREIGN KEY

When a primary key is used as reference in another table then this key will called foreign key. Foreign key shows that this key come from some other table. It is referring to primary key of some table. It linked up two tables. Let's tell system the value in school_id_fk field in tbl_student table comes as foreign key from tbl_school. which is a primary key of tbl_school.
ALTER TABLE tbl_student ADD FOREIGN KEY (school_id_fk) REFERENCES tbl_school(school_id);


You have linked up both tables which means you cannot delete any school from tbl_school table unless you have deleted all its references from other tables. As we know that we have Sani school id value 1 in school_id_fk field of tbl_student now. It's means you cannot delete sani school from tbl_school unless you delete it from tbl_student. Confused? no worries. Let try to delete school from school table.
DELETE FROM tbl_school WHERE school_id = 1;
oops!!, We got an error
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`tbl_student`, CONSTRAINT `tbl_student_ibfk_1` FOREIGN KEY (`school_id_fk`) REFERENCES `tbl_school` (`school_id`))
Now you get it, why we cannot delete it. hmmm... We have to remove reference first. Let's empty the student table. To do that we have to use TRUNCATE keyword.

TRUNCATE

Truncate is use to empty the table. It'll reset primary key counter back to zero as well. To empty table, we'll use following query.
TRUNCATE tbl_school;


Reference have removed now. You may delete school now.
Let's insert same data again in tbl_student.
INSERT INTO tbl_student(id,
                        name,
                        cnic,
                        phone,
                        school_id_fk,
                        roll_number,
                        status, 
                        saving_date,
                        saving_datetime
                       ) 
               VALUES (1,
                       "Alas",
                       "00000-0000000-0",
                       "0092 000 0000000",
                       "1",
                       "10",
                       "Active",
                       "2018-11-29",
                       "2018-11-29 11:00:00"

                      );
Get record from tbl_student
SELECT * FROM tbl_student
It'll show following output:
id name cnic phone school_id_fk roll_number status saving_date saving_datetime
1 Alas 00000-0000000-0 0092 000 0000000 1 10 Active 2018-11-29 2018-11-29 11:00:00

Sometimes we have to identify a unique value in a table. For example. Two students cannot have same roll number in same class. So roll number should be a unique number. To make it unique, we have to use keyword UNIQUE

UNIQUE KEY

Unique key is a unique column that identifying each row in a table.
ALTER TABLE tbl_student ADD UNIQUE(roll_number)


You are done. You cannot insert same roll number now. Let's try
INSERT INTO tbl_student(id,
                        name,
                        cnic,
                        phone,
                        school_id_fk,
                        roll_number,
                        status, 
                        saving_date,
                        saving_datetime
                       ) 
               VALUES (2,
                       "Nathan",
                       "75356-7865434-9",
                       "0092 333 54365254",
                       "1",
                       "10",
                       "Active",
                       "2018-11-29",
                       "2018-11-29 10:00:00"

                      );
oops!!, We got an error
#1062 - Duplicate entry '10' for key 'roll_number'
Now question is. Why do we need of Unique key in the presence of primary key which is also a unique key. Or in other words you can ask, what is the difference between PRIMARY KEY & UNIQUE KEY?
Hmmm... so answer is.
  • Unique Constraint may have a NULL value.
  • Each table can have more than one Unique Constraint.
  • Unique Constraint cannot be related with another table's as a Foreign Key.
  • One table can only have one primary key.

COMPOSITE KEY

Composite key is a combination of multiple columns of tables that can be used to uniquely identify each row in the table. In our table tbl_fee we are going to put check that a student cannot pay same month fee again. To create a composite key, use following query.
ALTER TABLE tbl_results ADD UNIQUE KEY unique_combination (school_id,student_id,year,month);


We are done here. Let's insert some record now.

INSERT INTO `tbl_fee` (id, school_id, student_id, year, month, amount_paid, paid_datetime) VALUES
(NULL, 1, 1, 2018, 1, 2500, '2018-01-01 00:00:00');

Let's try to insert same record.

INSERT INTO `tbl_fee` (id, school_id, student_id, year, month, amount_paid, paid_datetime) VALUES
(NULL, 1, 1, 2018, 1, 2500, '2018-01-01 00:00:00');

Ooops... We have gotten an error:

#1062 - Duplicate entry '1-1-2018-1' for key 'unique_combination'

Which means we are secure now. No one can enter duplicate record now. Let's add more records

INSERT INTO tbl_results (id, school_id, student_id, year, month, amount_paid, paid_datetime) VALUES
(2, 1, 1, 2018, 2, 2500, '2018-02-01 00:00:00'),
(3, 1, 1, 2018, 3, 2500, '2018-03-01 00:00:00'),
(4, 1, 1, 2018, 4, 2500, '2018-04-01 00:00:00'),
(5, 1, 1, 2018, 5, 2500, '2018-05-01 00:00:00'),
(6, 1, 2, 2018, 2, 2500, '2018-02-01 00:00:00'),
(7, 1, 2, 2018, 3, 2500, '2018-03-01 00:00:00'),
(8, 1, 2, 2018, 4, 2500, '2018-04-01 00:00:00'),
(9, 1, 2, 2018, 5, 2500, '2018-05-01 00:00:00'),
(10, 2, 1, 2018, 1, 2500, '2018-01-01 00:00:00'),
(11, 2, 1, 2018, 2, 2500, '2018-02-01 00:00:00'),
(12, 2, 1, 2018, 3, 2500, '2018-03-01 00:00:00'),
(13, 2, 1, 2018, 4, 2500, '2018-04-01 00:00:00');

Let's check out put:

idschool_idstudent_idyearmonthamount_paidpaid_datetime
1 1 1 2018 1 2500 2018-01-01 00:00:00
2 1 1 2018 2 2500 2018-02-01 00:00:00
3 1 1 2018 3 2500 2018-03-01 00:00:00
4 1 1 2018 4 2500 2018-04-01 00:00:00
5 1 1 2018 5 2500 2018-05-01 00:00:00
6 1 2 2018 2 2500 2018-02-01 00:00:00
7 1 2 2018 3 2500 2018-03-01 00:00:00
8 1 2 2018 4 2500 2018-04-01 00:00:00
9 1 2 2018 5 2500 2018-05-01 00:00:00
102 1 2018 1 2500 2018-01-01 00:00:00
112 1 2018 2 2500 2018-02-01 00:00:00
122 1 2018 3 2500 2018-03-01 00:00:00
132 1 2018 4 2500 2018-04-01 00:00:00

Let's play with this table now. First we'll check how many entries in this table. We'll use COUNT function.

WHERE Clause

Where clause is used when we've condition in our query. It allows us to search specific data.
So to get record for school Sani school. We'll use following query.

SELECT * 
FROM tbl_school
WHERE school_name = 'Sani school'


It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00

If you want to fetch record which have school Id greater than 1. You'll use following query.

SELECT * 
FROM tbl_school
WHERE school_id > 1
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

Similarly if you want to fetch record which have school Id less than 3. You'll use following query.

SELECT * 
FROM tbl_school
WHERE school_id < 3
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00

We may use other operators to fulfill our requirements. Such as
Operator Use Detail
= (A = B) A is equals to B
<> (A <> B) A is not equals to B
> (A > B) A is greater than B
< (A < B) A is less than B
>= (A >= B) A is greater than or equals to B
<= (A <= B) A is less than or equals to B

Sometimes we are ask to get all school which name starts with letter S. To do that we'll use Like operator.

Like Operator

Wildcard is used to search for partial matches. We use character '%' with like operator to get desired output. Suppose we want to get record of all schools which name starts with letter S, we'll use following query.
SELECT * 
FROM tbl_school
WHERE school_name like 'S%' 
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00

If you want to get all school which name end with letter e. We'll use following query.
SELECT * 
FROM tbl_school
WHERE school_name like '%e'
                     
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
2 Hyne ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

Sometime we required to find record which contains string ck. Irrespective of what is postfix or prefix. To obtain such records we'll use following query.
SELECT * 
FROM tbl_school
WHERE school_name like '%ck%'
                     
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
3 Delickate ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

Sometime we asked to get two school data. Let suppose we asked to get data of school Sani school & Hyne school. To get this record we'll use OR Operator.

Or Operator

We use OR operator when we required one of the conditions must be fulfilled in two or more conditions. Here we're requiring to get school either Sani school or Hyne school. To get that record, we'll use following query.
SELECT * 
FROM tbl_school
WHERE school_name = 'Sani school'
OR school_name = 'Hyne school'
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00

let's insert two more records.
INSERT INTO tbl_school (
			school_name,
			school_name_urdu,
			school_emis_code,
			school_sort_order,
			school_status,
			school_saving_date,
			school_saving_datetime
			) 
		VALUES ("Rizwan school",
			"NULL",
			"SC335566",
			"4",
			"Active",
			"2018-11-30",
			"2018-11-30 09:00:00"
                       ),
            ("Umer school",
			"NULL",
			"SC335567",
			"5",
			"Active",
			"2018-12-01",
			"2018-12-01 09:00:00"
                       );          
Check how many record we have now using following query.
SELECT * FROM tbl_school
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00
4 Rizwan school NULL SC335566 4 Active 2018-11-30 2018-11-30 09:00:00
5 Umer school NULL SC335567 5 Active 2018-12-01 2018-12-01 09:00:00

Sometimes we are required to get fetch data between two dates. To do that we use BETWEN Operator.

Between Operator

Between operator is used to retrieve values from an expression within a specific range. Let get record from 29 November to 30 November. We'll use following query.
SELECT * 
FROM tbl_school
WHERE school_saving_date BETWEEN '2018-11-29' AND '2018-11-30'


It'll show you following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00
4 Rizwan school NULL SC335566 4 Active 2018-11-30 2018-11-30 09:00:00

Let's get all those school which are active and saved on 29 November, 2018. To do this we have to use AND Operator.

AND Operator

We use AND operator when we required all of the conditions must be fulfilled in two or more conditions. Here we're requiring to get school which are ative and saved on 29 November, 2018. To do that we'll use following query.
SELECT * 
FROM tbl_school
WHERE school_saving_date = '2018-11-29'
AND school_status = 'Active'
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00

Sometimes we are required to get by choice. Suppose we want to get school id 1,3 & 5. To do this, we'll use IN operator.

IN Operator

In Operator is used to specify multiple possible values for a column. So to get school id of 1,3 & 5, we'll following query.
SELECT * 
FROM tbl_school
WHERE school_id IN (1,3,5)
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00
5 Umer school NULL SC335567 5 Active 2018-12-01 2018-12-01 09:00:00

Sometimes we are required to get record which are having null values. To do this we'll use IS NULL operator.

IS NULL

IS NULL condition is used to check if there is a NULL value in the field. Let's get all school which have their urdu name is null. Or they do not have their urdu name. To do that, We'll use run following query.
SELECT * 
FROM tbl_school
WHERE school_name_urdu IS NULL
It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
4 Rizwan school NULL SC335566 4 Active 2018-11-30 2018-11-30 09:00:00
5 Umer school NULL SC335567 5 Active 2018-12-01 2018-12-01 09:00:00

Sometimes we are required to get record which do not having null values. To do this we'll use IS NOT NULL operator.

IS NOT NULL

IS NOT NULL condition is used to check if there is a NOT a NULL value in the field. Let's get all school which have their urdu name is not null. Or they do have their urdu name. To do that, We'll use run following query.
SELECT * 
FROM tbl_school
WHERE school_name_urdu IS NOT NULL


It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00

ORDER BY

Order by is used to sort the record either in ascending order or descending order. Let's sort our record in descending order using following query.
SELECT * 
FROM tbl_school
ORDER BY school_id DESC


It'll show following output:
Id Name Name urdu Emis code Sort order Status Saving date Saving datetime
3 Delickate school ڈیلیکیٹ SC335565 3 Deactive 2018-11-29 2018-11-29 12:02:00
2 Hyne school ہا این SC335564 2 Active 2018-11-29 2018-11-29 12:01:00
1 Sani school سنی SC335563 1 Active 2018-11-29 2018-11-29 12:00:00

Sometimes we have to use Alias for field name using AS keyword.

Alias

Alias is an alternate word for table field name. Let's get only 3 fields from table with their alias name and sorting by id ascending.
SELECT school_id as Id, school_name as name, school_status as status, school_saving_date as date
FROM tbl_school
ORDER BY school_id ASC


It'll show you following output:
Id Name Status Date
1 Sani school Active 2018-11-29
2 Hyne school Active 2018-11-29
3 Delickate school Deactive 2018-11-29

Let's create another table tbl_student.
CREATE TABLE tbl_student (
  id int(11) AUTO_INCREMENT NOT NULL,
  name varchar(255) DEFAULT NULL,
  cnic varchar(255) DEFAULT NULL,
  phone varchar(255) DEFAULT NULL,
  school_id_fk int(11) NOT NULL,
  roll_number varchar(255) DEFAULT NULL,
  status enum('Active','Deactive') DEFAULT NULL,
  saving_date date DEFAULT NULL,
  saving_datetime datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Output result would be:
id name cnic phone school_id_fk roll_number status saving_date saving_datetime
No. Record Found.

If you check out this query. We have not defined AUTO_INCREMENT and PRIMARY KEY to this table. We have to modify the table now as most often table needs AUTO_INCREMENTED field and PRIMARY KEY field. To modify table we have to use ALTER keyword with ADD keyword.

Regular Expression

SQL provides regular expression as well in query. Let's suppose we have following table tbl_person.

Id Name
1 Butcher
2 Buckner
3 Lily
4 Durmat

and we want to get all the names starting with BU. To do that we can use regular expression in query.

      	SELECT name FROM tbl_person WHERE name REGEXP '^bu';
      

It'll show following output.

Id Name
1 Butcher
2 Buckner


If you noticed here. We use ^ (carrot sign) in query. This is called pattern. Patterns are used to help REGEX to filter record. There are many patterns. Some of them are as follow

Pattern Definition
^ Starts with
$ End at
. Any single character
* Zero or more
+ One or more
| Or

JOINS

JOINS are used to combine the record of multiple tables.

There are only 3 joins:

  • Cross Join (Also known as Cartesian Join E.g: Table A, Table B)
  • Inner Join (Also known as JOIN E.g: Table A Join/Inner Join Table B)
  • Outer join

    There are three types of outer join.
    • Left Outer Join (Also known as Left Join)
    • Right Outer Join (Also known as Right Join)
    • Full Outer Join (Also known as Full Join)
      Note: In Mysql, there is no concept of full join. They have UNION / UNION All instead.
Let's get only school school id and name from school table
SELECT school_id, school_name FROM tbl_school;
AND get student id, name & school id from student table
SELECT id, name, school_id_fk FROM tbl_school;

and then show their results side by side.

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Let's do the cross join to these two tables.

CROSS JOIN / CARTESIAN JOIN

In cross join number of rows in the first table will be multiplied by the number of rows in the second table. Suppose we have following two tables.

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Checkout this query.

SELECT school_id, school_name, id, name, school_id_fk 
FROM tbl_school, tbl_student


Checkout the output:
school_id school_name id name school_id_fk
1Sani1 Nathan 1
1Sani2 Mark 2
2Hyne1 Nathan 1
2Hyne2 Mark 2
3Delickate1 Nathan 1
3Delickate2 Mark 2

Here you can clearly see that he pick First row from first table and join with all rows of second table then picked second row from first table and again join with all rows of second table and so on...


Let's do the inner join to these two tables.

INNER JOIN / JOIN / STRAIGHT_JOIN

Inner join returns only matched entries of both tables. Let's suppose we have following two tables.

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Let's join them now. (All queries will return the same result.)

SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)
OR
SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
INNER JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)
OR
SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
STRAIGHT_JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)


Here you can see, we defined each column name with their table name. It is because, sometime we have same column name in both table. To differentiate which filed is belongs to which table, we have to use table name with field name.

Also noticed that to join tables we have to tell which fields are going to match. Here you can clearly see that school_id field of table school is going to join with school_id_fk of student table.

Checkout the output of that query:

school_id school_name id name school_id_fk
1Sani1 Nathan1
2Hyne2 Mark 2

You can see that it returns only those rows which are matched. Rest of the rows are eliminated.

LEFT JOIN / LEFT OUTER JOIN

Left join returns all rows from first table and only matched record from second table. Let's suppose we have following two tables.

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Let's join them now. (Both queries will return the same result.)

SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
LEFT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)
OR
SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
LEFT OUT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)


Her you can see that to join tables we have to tell which fields are going to match. Here you can clearly see that school_id field of table school is going to join with school_id_fk of student table.

Checkout the output of that query:

school_id school_name id name school_id_fk
1Sani1 Nathan1
2Hyne2 Mark 2
3DelickateNULL NULL

You can see that it returns all rows of table school and only matched rows from table student. It return NULL on none matched record.

RIGHT JOIN / RIGHT OUTER JOIN

Right join returns all rows from second table and only matched record from first table. Let's suppose we have following two tables.

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Let's join them now. (Both queries will return the same result.)

SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
RIGHT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)
OR
SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
RIGHT OUT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)


Her you can see that to join tables we have to tell which fields are going to match. Here you can clearly see that school_id field of table school is going to join with school_id_fk of student table.

Checkout the output of that query:

school_id school_name id name school_id_fk
1Sani1 Nathan1
2Hyne2 Mark 2

You can see that it returns all rows of table student and only matched rows from table school. It might return NULL on none matched record if there will be any.

UNION ALL

UNION ALL combine all record of both tables. keep in mind that number of fields should be same in both tables. Let's suppose we have following two tables.

tbl_student_grade_A

Student id Student name
1 John
2 Julie

tbl_student_grade_B

Student id Student name
1 Mack
2 Lee
3 Butcher
SELECT tbl_student_grade_A.id, tbl_student_grade_A.name 
FROM tbl_student_grade_A 
UNION ALL
SELECT tbl_student_grade_B.id, tbl_student_grade_B.name 
FROM tbl_student_grade_B


Output of this query will be:

Id Name
1 John
2 Julie
1 Mack
2 Lee
3 Butcher

Here you can see that It shows all record of tbl_student_grade_A & tbl_student_grade_B.

UNION

UNION combine all record of both tables except duplicates. keep in mind that number of fields should be same in both tables. Let's suppose we have following two tables.

tbl_student_grade_A

Student id Student name
1 John
2 Julie

tbl_student_grade_B

Student id Student name
1 Mack
2 Lee
3 Butcher
SELECT tbl_student_grade_A.id, tbl_student_grade_A.name 
FROM tbl_student_grade_A 
UNION
SELECT tbl_student_grade_B.id, tbl_student_grade_B.name 
FROM tbl_student_grade_B


Output of this query will be:

Id Name
1 John
2 Julie
3 Butcher
1 Mack
2 Lee

Here you can see that It shows all record of tbl_student_grade_A & tbl_student_grade_B and remove duplicates.

Here question arises that what is difference between UNION & UNION ALL. So answer is:

Both combines the record of both tables but UNION removes the duplicates.

Sometimes we listen the term SELF JOIN

SELF JOIN

If you join table to itself then this would be called SELF JOIN. Let's create a new table.
CREATE TABLE tbl_category (
  category_id int(11) NOT NULL,
  category_name varchar(255) NOT NULL,
  category_parent_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE tbl_category  ADD PRIMARY KEY (category_id);

ALTER TABLE tbl_category  MODIFY category_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

And insert some record in it.

INSERT INTO tbl_category (category_id, category_name, category_parent_id) VALUES
(1, 'Users', 0),
(2, 'Users list', 1),
(3, 'Users Add', 1),
(4, 'Users Edit', 1),
(5, 'Users Delete', 1);

Let's join this table to itself now.

SELECT a.category_id, a.category_name, b.category_name as parent_category  
FROM tbl_category as a
LEFT JOIN tbl_category as b ON (a.category_parent_id = b.category_id)



Output if this query will be:

category_id category_name category_name
1 Users NULL
2 Users List Users
3 Users Add Users
4 Users Edit Users
5 Users Delete Users
 

INSERT JOIN

Sometimes we have to insert record into 3rd table by joining two tables record. Let's suppose we have following three tables.

tbl_profile
prof_idprof_name
1Lee
2Mark
3John
tbl_passports
passport_idpasspost_profile_idfkpassport_number
11BA1234
22US6434
33AU2344
 

tbl_info
info_idprof_idinfo_personpassport_idinfo_passport
No record found.


 

Let's insert the record into tbl_info by joining tbl_profile & tbl_passports

INSERT INTO tbl_info(info_person,info_passport)
SELECT prof_name, passport_number
FROM tbl_profile
LEFT JOIN tbl_passports ON (prof_id = passport_profile_idfk)


Here will be the output

info_idprof_idinfo_personpassport_idinfo_passport
11Lee1BA1234
22Mark2US6434
33John3AU2344
 

UPDATE JOIN

Sometimes we have to update record by joining two tables record. Let's suppose we have following three tables.

tbl_profile
prof_idprof_name
1Lee
2Nathan
3John
tbl_passports
passport_idpasspost_profile_idfkpassport_number
11BA1234
22CA1423
33AU2344
 

tbl_info
info_idprof_idinfo_personpassport_idinfo_passport
11Lee1BA1234
22Mark2US6434
33John3AU2344
 

Let's suppose someone has changed Mark name to Nathan & their passport respectively, as showing in above table but tbl_info having the older data. Now we have to update the record into tbl_info by joining tbl_profile & tbl_passports tables

UPDATE tbl_info 
LEFT JOIN tbl_profile ON (tbl_info.prof_id = tbl_profile.prof_id)
LEFT JOIN tbl_passports ON (tbl_info.passport_id = tbl_passports.passport_id)
SET tbl_info.info_person = tbl_profile.prof_name, tbl_info.info_person = tbl_passports.passport_number
WHERE tbl_profile.prof_id = 2



Here will be the output

info_idprof_idinfo_personpassport_idinfo_passport
11Lee1BA1234
22Nathan2CA1423
33John3AU2344

If you do not use WHERE clause then it'll update whole table record.

DELETE JOIN

Sometimes we have to delete record by joining two tables record. Let's suppose we have following three tables.

tbl_profile
prof_idprof_name
1Lee
2Nathan
3John
tbl_passports
passport_idpasspost_profile_idfkpassport_number
11BA1234
22CA1423
33AU2344
 

tbl_info
info_idprof_idinfo_personpassport_idinfo_passport
11Lee1BA1234
22Nathan2CA1423
33John3AU2344
 

Now we want to delete last record from tbl_info. To that we'll use following query.

DELETE FROM tbl_info 
LEFT JOIN tbl_profile ON (tbl_info.prof_id = tbl_profile.prof_id)
LEFT JOIN tbl_passports ON (tbl_info.passport_id = tbl_passports.passport_id)
WHERE tbl_profile.prof_id = 3
AND tbl_passports.passport_id = 3



Here will be the output

info_idprof_idinfo_personpassport_idinfo_passport
11Lee1BA1234
22Nathan2CA1423

If you do not use WHERE clause then it'll delete whole table record.

 

AGGREGATE FUNTIONS

Aggregation function is a function that perform calculation on a set of values and returns a single value.

Let's create a table tbl_results

CREATE TABLE tbl_fee (
  id int(11) NOT NULL,
  school_id int(11) NOT NULL,
  student_id int(11) NOT NULL,
  year year(4) NOT NULL,
  month int(11) NOT NULL,
  amount_paid float NOT NULL,
  paid_datetime datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Let's add primary key to the table

ALTER TABLE tbl_fee  ADD PRIMARY KEY (id);

Make a table auto-incremented

ALTER TABLE tbl_fee  MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

As the table names shows that we are going to save fee of students and we should make sure that we are not getting same month fee again from particular student. To do that we create composite key. in table.

COUNT

COUNT calculate the total records. Let's try following query.
SELECT COUNT(*) FROM tbl_fee;


It'll show following output:

COUNT(*)
13

You may put alias for count title.

SELECT COUNT(*) as total 
FROM tbl_fee;

Now we know how many record we have into our table fee. Let's get how much amount we have received. We'll use SUM function.

SUM

SUM function sum up of a set of values of specific field. Let's get total fee we have received till now using following query.

SELECT SUM(amount_paid) total_fee 
FROM tbl_fee;


It'll show following output:

total_fee
32500

Now we know how much amount we have received till now. Let's get average amount we have received. We'll use AVG function.

AVG

AVG function calculates the average value of a set of values of specific field. Let's get average fee we have received till now using following query.

SELECT AVG(amount_paid)  average_amount
FROM tbl_fee;


It'll show following output:

average_amount
2500

Now we know how much average amount we have received. Let's get minimum amount we have received. We'll use MIN function.

MIN

MIN function gets the minimum value of specific field. Let's get minimum fee we have received using following query.

SELECT MIN(amount_paid)  minimum_amount
FROM tbl_fee;


It'll show following output:

minimum_amount
2500

Now we know minimum amount we have received. Let's get maximum amount we have received. We'll use MAX function.

MAX

MAX function gets the maximum value of specific field. Let's get maximum fee we have received using following query.

SELECT MAX(amount_paid)  maximum_amount
FROM tbl_fee;


It'll show following output:

maximum_amount
2500

Let's see how many unique school fee we have in our table tbl_fee. To do that we'll use a keyword DISTINCT

DISTINCT

DISTINCT function returns unique value of provided field. Let's get how many unique we have in tbl_fee using following query.

SELECT DISTINCT(school_id) as unique_school
FROM tbl_fee;


It'll show following output:

unique_school
1
2
It shows. two unique school we have. Let's get which school get how much amount. To do that we'll use GROUP BY.

GROUP BY

GROUP BY function group the record as per existing values separately. Let's get how much amount we have in tbl_fee for each school using following query.

SELECT school_id, SUM(amount_paid) as total_amount
FROM tbl_fee
GROUP BY school_id


It'll show following output:

school_id total_amount
1 22500
2 10000

It show total amount for each school separately. Sometimes we are asked you get specific records in specific range of amount. To do that we'll use HAVING statement.

HAVING

HAVING clause always used with aggregate function to specify filter conditions.

Let's get those school whose total received amount is less than 15000.

SELECT school_id, SUM(amount_paid) as total_amount
FROM tbl_fee
GROUP BY school_id
HAVING total_amount < 15000


It'll show following output:

school_id total_amount
2 10000

Let's get those school whose total received amount is more than 15000.

SELECT school_id, SUM(amount_paid) as total_amount
FROM tbl_fee
GROUP BY school_id
HAVING total_amount > 15000


It'll show following output:

school_id total_amount
1 22500

Built-in functions

There are lots of many built-in functions like

String Functions Numeric Functions Date Functions Advanced Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

We can create our own function as well.

Custom Manipulations

STORED FUNCTIONS / ROUTINES

Stored functions are the program that manipulate on input parameters (which could be one or more) and returns a single result.

Let's create a function AVERAGE. Which will take two inputs OBTAIN & TOTAL and will return the average.

Syntax

CREATE FUNCTION FUNCTION_NAME(PARAMETER_1 DATA_TYPE, PARAMETERS_2 DATA_TYPE, ......) RETURN DATA_TYPE
	SQL_DATA_ACCESS
BEGIN
	RETURN (Manipulated input values);
END;     

Creating PERCENTAGE function.

DELIMITER ||
CREATE FUNCTION PERCENTAGE(OBTAIN INT, TOTAL INT) RETURNS float
    NO SQL
BEGIN

	RETURN (OBTAIN/TOTAL)*100;

END;	


How to call it?

SELECT PERCENTAGE(42,100);
Output:
PERCENTAGE(42,100)
42

If you want to delete function, use following query.

DROP FUNCTION

DROP keyword is used to delete function. Here is a query.

DROP FUNCTION PERCENTAGE;

STORED PROCEDURE / ROUTINES

Stored procedure are the program that manipulate on input parameters (which could be one or more) and returns a multiple rows.

Let's stored function DISTRICT_USERS. Which will take one inputsDISTRICT_ID and will returns all the users belongs to that districts. But before creating a stored function. let's create table and some entries in it.

Creating a table.

CREATE TABLE tbl_users (
  user_id int(11) NOT NULL,
  user_name varchar(255) NOT NULL,
  user_district_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE tbl_users ADD PRIMARY KEY (user_id);

ALTER TABLE tbl_users MODIFY user_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Inserting some record.

INSERT INTO tbl_users (user_id, user_name, user_district_id) VALUES
(1, 'Sani', 1),
(2, 'Hyne', 1),
(3, 'Tony', 2),
(4, 'Micheal', 2),
(5, 'Shawn', 2);

Now we have table and some record to manipulate. Let's create stored procedure now.

Syntax

CREATE PROCEDURE PROCEDURE_NAME(IN PARAMETER_1 DATA_TYPE,OUT PARAMETERS_2 DATA_TYPE, INPUT PARAMETERS_2, ......)
	SQL_DATA_ACCESS
BEGIN
	QUERY WITH DATA MANIPULATION;
END;     

Creating DISTRICT_USERS procedure.

DELIMITER ||
CREATE PROCEDURE DISTRICT_USERS(IN DISTRICT_ID INT)
    NO SQL
BEGIN

SELECT * FROM tbl_users WHERE user_district_id = DISTRICT_ID;

END;


There are three types of input parameters for stored procedures.

  • IN
  • OUT
  • IN OUT

IN: The parameter can be referenced by the procedure. The value of the parameter cannot be overwritten by the procedure.

OUT: The parameter cannot be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.

IN OUT: The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

Let's get all user of district 2.

How to call it?

CALL DISTRICT_USERS(2);
Output:
user_id user_name user_district_id
3 Tony 2
4 Micheal 2
5 Shawn 2

If you want to delete procedure, use following query.

DROP PROCEDURE

DROP keyword is used to delete procedure. Here is a query.

DROP PROCEDURE DISTRICT_USERS;

Views

Sometimes we have to join some tables and get the result again and again. To avoid writing queries again and again we create a virtual table which is called Views.

Let's assume that we have following two tables

school_id school_name
1 Sani
1 Hyne
1 Delickate
id name school_id_fk
1 Nathan 1
2 Mark 2

Let's join them now.

SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
LEFT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)

Output:

school_id school_name id name school_id_fk
1Sani1 Nathan1
2Hyne2 Mark 2
3DelickateNULL NULL

We assume that this query is very important and we have to use it many times Let save this query by making it as views.

Syntax

CREATE VIEW VIEW_NAME AS JUERY;

Creating VIEWS

DELIMITERS ||
CREATE VIEW student_data as SELECT tbl_school.school_id, tbl_school.school_name, 
       tbl_student.id, tbl_student.name, tbl_student.school_id_fk 
FROM tbl_school 
LEFT JOIN tbl_student ON (tbl_school.school_id = tbl_student.school_id_fk)


How to call it?

SELECT * FROM student_data;

Output:

school_id school_name id name school_id_fk
1Sani1 Nathan1
2Hyne2 Mark 2
3DelickateNULL NULL

If you want to delete VIEW, use following query.

DROP VIEW

DROP keyword is used to delete view. Here is a query.

DROP VIEW student_data;

Trigger

Sometimes we need to manipulate other tables while insertion, updation or deletion on one table. To do this we create triggers.

Keep in mind that trigger would always be apply on table level only.

Trigger can be apply either BEFORE or AFTER on INSERT/UPDATE/DELETE.

BEFORE: If you want to manipulate data before insertion/updation/deletion. Mostly for same table.

AFTER: If you want to manipulate data after insertion/updation/deletion. Mostly for other tables.

Let's suppose we have following three tables.

tbl_user
IdName
1Micheal
2Alax
tbl_rights
IdName
1View
2Add
3Edit
4Delete
tbl_assigned_rights
IdUsersRights
111
212
321
422

As you can see we have users, some privileges rights and assigned rights for these users. Here you can see. Both users have rights of view & add pages

Now we want, on adding every new user, his rights for view pages should be automatically privilege. To make it happen, we create trigger.

Syntax

CREATE TRIGGER TRIGGER_NAME BEFORE/AFTER INSERT ON TABLE_NAME
 FOR EACH ROW BEGIN

-- MANIPULATION GOES HERE

END
DELIMITERS ||
CREATE TRIGGER add_user_rights AFTER INSERT ON tbl_users
 FOR EACH ROW BEGIN

	INSERT INTO tbl_assigned_rights(user_id,right_id) VALUES(((SELECT MAX(user_id) FROM tbl_users)+1),1);

END


DROP TRIGGER

DROP keyword is used to delete trigger. Here is a query.

DROP TRIGGER add_user_rights;

EVENTS

Event is a cronjob. We schedule it what time it should be execute and what should it do.

Keep in mind that events works on database level. Let's suppose we have a table tbl_activity and its log table tbl_ativity_log (having same fields)

tbl_activity
IdUsersactivity_date
112019-01-01 01:12:15
212019-01-01 02:22:45
322019-01-01 03:32:25
422019-01-01 04:52:35

And we want to save every day record to other table as well at 23:59:59 daily. To do that we create event.

Syntax

CREATE EVENT EVENT_NAME ON SCHEDULE EVERY 1/2/3... DAY/MONTH/YEAR... STARTS STARTING_DATE_TIME ON COMPLETION NOT PRESERVE ENABLE DO BEGIN

-- MANIPULATION WILL GOES HERE

END

Let's create an event now.

DELIMITERS ||
CREATE EVENT log_todays_record ON SCHEDULE EVERY 1 DAY STARTS '2019-01-01 23:59:59' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN

INSERT INTO tbl_activity_log(user_id,activity_date)
SELECT user_id,activity_date 
FROM tbl_activity
WHERE DATE(activity_date) = CURDATE();

END


DELETE EVENTS

DROP keyword is used to delete event. Here is a query.

DROP EVENT log_todays_record;

IF Statement

SWITCH Statement

Frequently Asked Questions

is there any function in mysql 8 to find specific key with its specific value in JSON string?
      
SELECT user_name, LOWER(TRIM(BOTH 0x22 FROM TRIM(BOTH 0x20 
FROM SUBSTRING(SUBSTRING(user_name,LOCATE('\"20\"',user_name)+LENGTH('\"20\"'),LOCATE(0x2C,SUBSTRING(user_name,LOCATE('\"20\"',user_name)+LENGTH('\"20\"')+1,LENGTH(user_name)))),LOCATE(0x22,SUBSTRING(user_name,LOCATE('\"20\"',user_name)+LENGTH('\"20\"'),LOCATE(0x2C,SUBSTRING(user_name,LOCATE('\"20\"',user_name)+LENGTH('\"20\"')+1,LENGTH(user_name))))),LENGTH(user_name))))) 
FROM tbl_users 
By: Atharva Jawalkar on 24 Jan, 2019
How to copy table data from one database to another database table?
      
INSERT INTO DATABASE_B.tbl_activity_log(user_id,activity_date)
SELECT user_id,activity_date 
FROM DATABASE_A.tbl_activity
WHERE DATE(activity_date) = CURDATE();
By: Scott Rehm on 08 Jan, 2019
How to copy one table data to another table?
      
INSERT INTO tbl_activity_log(user_id,activity_date)
SELECT user_id,activity_date 
FROM tbl_activity
WHERE DATE(activity_date) = CURDATE();
By: Ravin Nash on 29 Dec, 2018