My SQL

By Priti Dalvi

1
Index
Module Topic Page No
Module 1 : Getting Started with MySQL 4
Module 2 : Editing SQL Commands 17
Module 3 : Data Retrieval & Ordering Output 25
Module 4 : Regular Expressions 45
Module 5 : Creating Tables 48
Module 6: Inserting, Modifying & Deleting Data 57
Module 7 : Modifying Table Structure 65
Module 8 : Integrity Constraints 71
Module 9 : Built-In Functions 86
Module 10 : Indexes 99
Module 11 : Advanced Queries 105

2
Index
Module Topic Page No
Module 12 : Views 119
Module 13 : Transaction Processing 127
Module 14 : Miscellaneous function 133
Module 15: Introduction to programming 139
Module 16 : Temporary tables 151
Module 17 : Cursors 155
Module 18 : Stored Procedure and Functions 169
Module 19 : Triggers 185
Module 20: MySQL Prepared Statement 200
Module 21: Exception Handling 208

3
Module 1. Getting Started with MySQL
• Overview
➢ Introduction to Databases
➢ Introducing MySQL
➢ Main Components of MySQL
➢ Starting MySQL commands
➢ Exiting MySQL

4
Introduction to Databases

Computerized record-keeping system.

EMPLOYEE
COMMISSI
EMPNO ENAME JOB MANAGER HIREDATE SALARY DEPTNO
ON

17-DEC-
7369 SMITH CLERK 7902 800 20
1980
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
01-MAY-
7698 BLAKE MANAGER 7839 2850 30
1981
7782 CLARK MANAGER 7839 09-JUN-1981 2450 0 10
7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

5
Introducing SQL

SQL Statement Statement is sent

Is entered To the Database

Database

Data is displayed

6
What is MySQL?

• MySQL is an open source relational database.

• MySQL is cross platform
• MySQL supports multiple storage engines
• MySQL has high performance
• Cost effective

7
Why MySQL?
• MySQL database has become the word’s most popular open
source database.
• It has high performance, high reliability, and easy to use.
• It runs on more than 20 platforms.
• It offers comprehensive range of database tools.
• Source code is available under the terms of GNU General
public Licenses.

8
The application which uses MySQL

• The application which uses MySQL includes:

➢ TYPO3
➢ Joomla
➢ PHP
➢ Drupal
➢ Wordpress
➢ Other Softwares built on LAMP stack.

9
Features

• Web applications
• Open-source support
• Available large table size
• Stability

10
Comparison

11
Platforms and Interfaces
• Many programming languages with language specific APIs
includes libraries for accessing MySQL database.
• MySQL connector-net is used for integration with Microsoft
Visual Studio.
• MySQL connector-java for Java
• An ODBC interface called MYODBC allows interface with
programming languages such as ASP and Coldfusion.

12
Main Components of MySQL

13
Main Components of MySQL

14
Exiting MySQL
mysql> EXIT

15
MySQL Workbench

• Visual tool for database architects, developers, and DBAs

• A free integrated environment developed by MySQL
• Enables users to graphically administer MySQL databases
• Lets users manage database design & modeling
• Database administration

16
Module 2. Editing SQL Commands
• Overview
➢ Entering SQL commands
➢ Editing SQL commands
➢ Managing SQL files

17
Entering and Editing SQL Commands

• Terminating SQL statement:

➢ a semicolon at the end of a line,
➢ a semicolon on a line by itself,

• Viewing details of SQL buffer:

➢ mysql>SHOW ENGINE INNODB STATUS\G;

• Viewing databases:
➢ mysql>Show databases;
➢ mysql>Show databases like ‘test%’;

18
Entering and Editing SQL Commands
mysql >Show databases(LIKE <wildcard>);
mysql> Show databases;
mysql > Connect databasename;
or
mysql > use databasename
mysql > desc emp;
mysql > desc emp 'e%';
mysql > Show columns from emp;
mysql > Show columns from emp like ‘e%’;
mysql > Show tables;
mysql > Show tables (from<databasename>) (LIKE wildcard>);
mysql > Show table status like ‘emp’;
mysql > Tee c:\[Link];
19
 Entering and Editing SQL Commands
mysql > SHOW CREATE TABLE emp;
mysql > explain select * from emp;
mysql > explain select empno, ename from emp;
mysql > check table emp;

20
Executing SQL files

A file of SQL commands such as [Link] can also be

executed from inside the MySQL client using the source
command
mysql > Source c:\.........\[Link]

Here the full path to [Link] should be used.

21
Show the Structure of the table

Mysql> desc emp;

22
Entering and Editing SQL Commands
mysql>help;
mysql> \c
mysql> Show variables;
mysql > Prompt
mysql > Prompt Priti
mysql > Create database Pragati;
mysql > Create database if not exists Pragati;
mysql > drop database Pragati;
mysql > drop database IF EXISTS Pragati;

23
Entering and Editing SQL Commands
mysql>Select version(), current_date();
mysql> select now();
mysql> select user();

24
Module 3. Data Retrieval & Ordering Output
• Overview
➢ Simple Data Retrieval
➢ Describing Table Structure
➢ Conditional Retrieval using Arithmetic, Relational, Logical
and Special Operators
➢ The ORDER BY clause.
➢ Aggregate functions
➢ The GROUP BY and HAVING clause

25
Data Retrieval

SELECT <select phrase> [ FROM <table(s)> [ WHERE

<where phrase> ]
[ GROUP BY <group-by clause> ] [ORDER BY <order-by
clause> ]
[ LIMIT # of rows ] ]

26
Data Retrieval
mysql> SHOW TABLES;
mysql >SELECT * FROM emp;

mysql > SELECT empno, ename FROM emp;

mysql > SELECT empno as ‘employee NUM’, ename FROM emp;

(heading will be displayed in the same case)

mysql > SELECT distinct deptno FROM emp;
mysql> SELECT * FROM emp limit 2;
mysql > SELECT distinct deptno FROM emp
Limit 2;
mysql > SELECT distinct dept_code FROM emp
Limit 4, 15;
mysql > delete from dept limit 2; (first2 records are deleted) 27
Conditional Retrieval

mysql> SELECT * FROM employee WHERE salary > 3500;

mysql> SELECT emp_code, emp_name FROM employee WHERE

dept_code = 'MKTG';
Or
mysql> SELECT emp_code, emp_name FROM employee WHERE
dept_code = “MKTG”;

mysql> SELECT * FROM employee WHERE hire_date >= '1998-1-1';

28
Description

|| or operator

:= operator-Used to set variables

Binary Field attribute.. Controls case sensitivity

29
Relational Operators
= equal to
!= not equal to
<> not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

30
Relational Operators

mysql >SELECT * FROM employee WHERE salary > 3000;

Mysql>SELECT emp_name FROM employee WHERE dept_code !=
‘MKTG’;
Mysql>SELECT * FROM employee WHERE emp_name = ‘Vijay
Gupta’;
mysql>SELECT * FROM employee WHERE binary emp_name =
‘Vijay Gupta’;

31
Logical Operators
NOT,! not
OR, || or
AND, && and

32
Logical Operators
The AND Operator
mysql > SELECT * FROM employee
WHERE dept_code = 'MKTG' AND sex = 'F';

mysql > SELECT * FROM employee

WHERE salary >= 3000 AND salary <= 4000;

The OR Operator
mysql > SELECT * FROM employee
WHERE dept_code = 'MKTG' OR dept_code = 'FIN';

The NOT Operator

mysql > SELECT * FROM employee WHERE NOT dept_code =
'MKTG‘;
33
Logical Operators

mysql>select * from emp

where sal < 6000 || deptno=20;

mysql>select * from emp

where sal<6000 && deptno=20;

mysql>select * from emp

where not(deptno=20);

34
Special Operators

The BETWEEN operator

mysql > SELECT * FROM employee WHERE salary BETWEEN 3000
and 4000;

mysql > SELECT * FROM employee WHERE date_join BETWEEN

‘2001-01-01’ and ‘2001-12-31’

The IN operator
mysql > SELECT * FROM employee WHERE dept_code IN ('MKTG',
'FIN');

mysql > SELECT * FROM employee

WHERE dept_code NOT IN ('MKTG', 'FIN');
35
Special Operators
The LIKE operator
mysql> SELECT * FROM employee WHERE emp_name LIKE
'P%';
mysql> SELECT * FROM employee WHERE emp_name LIKE
'%Gupta';
mysql> SELECT * FROM employee WHERE emp_name LIKE
'%Gupta%';
mysql> SELECT * FROM employee WHERE emp_name NOT
LIKE '%Gupta%';
mysql> SELECT * FROM employee WHERE grade LIKE '_1';

36
Special Operators

The IS NULL operator

mysql> SELECT * FROM employee WHERE reports_to IS NULL;
Or
mysql> SELECT * FROM employee WHERE reports_to <=> NULL;
mysql> SELECT * FROM employee WHERE reports_to IS NOT
NULL;

37
Arithmetic Operators
+ addition
- subtraction
* multiplication
/ division

mysql> SELECT * FROM product

WHERE direct_sales + indirect_sales > target;

mysql> SELECT prod_code, prod_name, direct_sales + indirect_sales

FROM product;

mysql> SELECT prod_code,

direct_sales + indirect_sales "Total sales"
FROM product;

38
Ordering the SELECT Query Output
Ordering on single column
mysql> SELECT * FROM employee ORDER BY emp_code;
mysql> SELECT * FROM employee ORDER BY 1;

mysql> SELECT * FROM employee WHERE sex = 'M' ORDER BY

emp_name;

mysql> SELECT * FROM employee ORDER BY age DESC;

Ordering on multiple columns

mysql>SELECT * FROM employee ORDER BY dept_code,
emp_name;

mysql>SELECT * FROM employee ORDER BY dept_code, age

DESC; 39
Aggregate Functions
mysql> SELECT COUNT (*) FROM employee;
mysql> SELECT SUM (salary) FROM employee;
mysql> SELECT AVG (age) FROM employee;
mysql> SELECT MAX (salary) FROM employee;
mysql> SELECT MIN (salary) FROM employee;

40
The GROUP BY clause

mysql> SELECT dept_code, sum (salary)

FROM employee
GROUP BY dept_code;
OR
mysql> SELECT dept_code, sum (salary)
FROM employee
GROUP BY 1;
Mysql>SELECT *, sum(sal)
FROM emp
GROUP BY deptno;

41
The HAVING Clause

mysql> SELECT dept_code, sum (salary) FROM employee

GROUP BY dept_code
HAVING sum (salary) > 10000;

mysql> SELECT dept_code, sum (salary)

FROM employee
WHERE age > 30
GROUP BY dept_code
HAVING sum (salary) > 10000
ORDER BY sum (salary) desc;

42
Counting and Identifying Duplicates

SELECT COUNT(*) as repetitions, last_name, first_name

FROM person
GROUP BY last_name, first_name
HAVING repetitions > 1;

43
Eliminating Duplicates

SELECT last_name, first_name

FROM person
GROUP BY last_name, first_name;

44
Module 4. Regular Expressions

• Overview
➢ using regular expressions

45
Regular expressions

• “.” matches any single character

• “*” matches zero or more

mysql>SELECT * FROM employee WHERE name REGEXP '^b';

mysql>SELECT * FROM employee WHERE name REGEXP ‘[abc]';
mysql>SELECT * FROM employee WHERE name REGEXP ‘[a-c]';
mysql>SELECT * FROM employee WHERE name REGEXP ‘^A';
mysql>SELECT * FROM employee WHERE name REGEXP ‘T$’;

46
Regular expressions

mysql> SELECT * FROM employee WHERE name REGEXP BINARY

‘^K‘;

mysql> SELECT * FROM pet WHERE name REGEXP

'^.....$';

47
Module 5. Creating Tables

• Overview
➢ Creating a Table
➢ Data Types

48
Creating Tables

CREATE [ temporary ] TABLE [ if not exists ] tablename (

column-name data-type [other clauses]... );

mysql> CREATE TABLE dept (

dept_code varchar (4),
dept_name varchar(20) );

49
Creating Tables
mysql> CREATE TABLE dept (
dept_code varchar (4),
dept_name varchar (20) );

mysql> CREATE TABLE IF NOT EXISTS dept (

dept_code varchar (4),
dept_name varchar (20) );

50
Creating Tables

Mysql>CREATE TABLE CUSTOMER(

id int not null auto_increment primary key,
Name varchar(20),
City enum(‘mumbai’,’delhi’) default ‘mumbai’
);

51
Numeric Data Types:
Data Type Range Stores

TINYINT If signed -128 to 127, If unsigned from 0 to Integer Data

255,Storage (Bytes): 1B

SMALL If signed -32768 to 32767, from 0 to 65535, Integer Data

INT width up 5 digits, to Storage (Bytes): 2B

MEDIUMI If signed -8388608 to 8388607, if unsigned Integer Data

NT from 0 to 16777215, Storage (Bytes): 3B

INT If signed -2147483648 to 2147483647, If Integer Data

unsigned range is from 0 to 4294967295. width of
up to 11 digits.
Storage (Bytes): 4B
BIGINT If signed -9223372036854775808 to Integer Data
9223372036854775807, if unsiged from 0 to
18446744073709551615, Storage (Bytes): 8B
52
Numeric Data Types:
Data Type Range Stores

FLOAT(M,D) A precision from 0 to 23 results in a 4-byte, floating-point number

will default to 10,2. Decimal precision can go to
24 places
DOUBLE(M. precision from 24 to 53 results in an 8-byte floating-point number
D) double-precision, default to 16,4. Decimal
precision can go to 53 places.
DECIMAL(M, NUMERIC is a synonym for DECIMAL floating-point number
D)

53
Numeric Data Types:
Data Type Range Stores

DATE Between 1000-01-01 and 9999-12-31, Date data

YYYY-MM-DD default format
DATETIME between 1000-01-01 00:00:00 and 9999-12-31 Date and Time data
23:59:59, in YYYY-MM-DD HH:MM:SS format
TIMESTAMP DATETIME format, only without the hyphens eg: Date and Time data
as 19731230153000 ( YYYYMMDDHHMMSS ).

TIME Stores the time in HH:MM:SS format.

YEAR(M) Stores a year in 2-digit or 4-digit format, The

default length is 4.

54
String Types:
Data Type Range Stores
CHAR(M) - n characters where n can be 1 to 255 Fixed-length character
data.

VARCHAR(M) 1 to 255 characters in length variable-length data

TEXT 1 to 65535 characters, used to store large Store text
amounts of data
BLOB Binary Large Objects store large amounts of
binary data like
images or other types
of files
TINYBLOB or 255 characters
TINYTEXT
MEDIUMBLOB length of 16777215 characters
or
MEDIUMTEXT
55
String Types:

Data Type Range Stores

LONGBLOB length of 4294967295 characters

or
LONGTEXT
ENUM Enumeration, creating a list of items

56
Module 6. Inserting, Modifying & Deleting Data

• Overview
➢ Inserting Data into a Table
➢ Inserting Data into a Table using Sub query
➢ Modifying Data in a Table
➢ Deleting Data from a Table

57
Insert records

Syntax:
INSERT [ low_priority | delayed ] [ ignore ] [ into ]
<tablename>
[ ( <columnname>, ...) ] VALUES (<insert expression>,)

INSERT INTO table-name VALUES (value1, value2, ...);

Insert into customer values(null,’priti’,’mumbai’);
mysql> INSERT INTO dept VALUES ('MKTG', 'Marketing');
mysql> INSERT INTO dept VALUES ('FIN', 'Finance');
mysql> INSERT INTO dept VALUES ('TRNG', 'Training');

58
Inserting Data into Table

mysql> INSERT INTO employee (emp_code, age, emp_name)

VALUES(101, 33, 'Sunil');

59
Insert records

Inserting the records using another table

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO]

tbl_name[(col_name,...)] SELECT …
insert into emp (empno,ename) select roll, name from t6;

mysql> INSERT INTO senior

SELECT * FROM employee WHERE age > 50;

mysql> INSERT INTO senior (empno, ename)

SELECT empno, ename FROM employee WHERE age > 50;

60
Inserting Data into a Table
Mysql> desc dept;
Field Type Null? Key Default Extra
------------- ------ ----- ----- --------- -------
DEPT_CODE varchar(4) yes null
DEPT_NAME varchar(20)yes null

61
Counting and Identifying Duplicates:

CREATE TABLE person

(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
INSERT IGNORE INTO person (last_name, first_name)
VALUES( 'Rampal', 'Arjun');
REPLACE INTO person (last_name, first_name)
VALUES( 'Rampal', 'Arjun');
REPLACE INTO person (last_name, first_name)
VALUES( 'Rampal', 'Arjun');

62
Modifying and Deleting Data

UPDATE <tablename> SET <columnname> = <value> ( ,

<columnname>
= <value> ) [ WHERE <where phrase> ] [ LIMIT <# of rows> ];

mysql> UPDATE employee SET salary = salary + 100;

mysql> UPDATE employee SET salary = salary + 200 WHERE sex = 'F';

63
Modifying and Deleting Data

DELETE [LOW PRIORITY or QUICK] FROM table_name

[WHERE where_definition]
[ORDER BY order_definition]
[LIMIT row_value]

mysql> DELETE FROM employee;

mysql> DELETE FROM employee WHERE dept_code = 'MKTG';
Mysql> TRUNCATE employee;

64
Modifying and Deleting Data

DELETE [LOW_PRIORITY | QUICK] FROM <tablename>

[WHERE where_clause] [ORDER BY ...] [LIMIT #]

mysql> DELETE FROM employee;

mysql> DELETE FROM employee WHERE dept_code = 'MKTG';
mysql> DELETE quick from emp order by empno limit 2;

65
Module 7. Modifying Table Structure

• Overview
➢ Altering Table structure
➢ Dropping Column from a Table
➢ Dropping a Table

66
Modifying a Table Structure

ALTER (IGNORE) TABLE <tablename> <alter phrase> [ , <alter

phrase> ]
Common <alter phrase> variables to use with the ALTER TABLE statement
include the following:

ADD <columnname> <create specification>

CHANGE (COLUMN) <oldcolumnname> <create specification>
MODIFY (COLUMN) <create specification>
DROP (COLUMN) <columnname>
RENAME (TO) <newtablename>

67
Modifying a Table Structure
Mysql> ALTER table emp
drop comm;
Mysql> ALTER table emp
Add email varchar(20);
or
Mysql> ALTER table emp
Add email varchar(20) first;
or
Mysql> ALTER table emp
Add email varchar(20) after sal;
Mysql>ALTER table emp
modify ename varchar(40);

68
Modifying a Table Structure

Mysql>alter table emp

change sal sal1 float(8,2);
Mysql>alter table emp
alter comm set default 1000;
Mysql>alter table emp
alter comm drop default;
Mysql>alter table emp
rename to newemp;

69
Dropping a Table

DROP TABLE table-name;

MYSQL> DROP table dept;

MYSQL> DROP table IF EXISTS dept;

70
Module 8. Integrity Constraints

• Overview
➢ Understanding Table and Column Constraints
➢ Creating, Modifying and Dropping Column level constraints
➢ Creating, Modifying and Dropping Table level constraints

➢ Adding Constraints to Columns of an existing table

➢ Enabling and Disabling Constraints
➢ Dropping Columns and Tables having constraints

71
Integrity Constraints

• Not Null

• Unique

• Check

• Primary Key

• Foreign Key

72
Column Constraints

mysql> CREATE TABLE employee (

emp_code number (5) NOT NULL,
emp_name varchar (25) NOT NULL,
dept_code varchar (4) );

73
Column Constraints

mysql> CREATE TABLE employee (

emp_code int (5) UNIQUE,
emp_name varchar2 (25) NOT NULL);

74
The UNIQUE Constraint
mysql> CREATE TABLE supplier (
supp_code int(4) PRIMARY KEY,
supp_name varchar (30) UNIQUE);

mysql > CREATE TABLE supplier (

supp_code int(4) PRIMARY KEY,
supp_name varchar (30) UNIQUE
NOT NULL);

75
The CHECK Constraint

mysql> CREATE TABLE employee (

emp_code int(5) PRIMARY KEY,
emp_name varchar (25) NOT NULL,
dept_code varchar (4) CHECK (dept_code = upper
(dept_code) ));

76
The PRIMARY KEY Constraint

mysql> CREATE TABLE employee (

emp_code int(5) PRIMARY KEY,
emp_name varchar (25) NOT NULL,
dept_code varchar (4));

77
The REFERENCES Constraint

mysql> CREATE TABLE employee (

emp_code int(5)
PRIMARY KEY,
emp_name varchar (25) NOT NULL,
dept_code varchar (4)
REFERENCES dept (dept_code));

78
The REFERENCES Constraint

mysql> CREATE TABLE employee (

emp_code int(5) primary key,
emp_name varchar (25) not null,
dept_code varchar (4)
REFERENCES dept(dept_code)
ON DELETE CASCADE | ON DELETE SET NULL
ON UPDATE RESTRICT);

79
Table Constraints

mysql> CREATE TABLE employee(

emp_code int(4)not null,
emp_name varchar(40)not null,
dept_code varchar(4),
UNIQUE (emp_code,emp_name) );

80
The PRIMARY KEY and CHECK Constraint

mysql> CREATE TABLE orders (

order_year int (4),
order_number int(5),
order_date date,
PRIMARY KEY (order_year, order_number));

mysql> CREATE TABLE employee (emp_code int(4),

emp_name varchar(20),
date_birth date,date_join date,
CHECK (date_join > date_birth));

81
The FOREIGN KEY Constraint

mysql> CREATE TABLE ship (

ship_code varchar (5),
ship_name varchar (20) not null,
PRIMARY KEY (ship_code) );

mysql> CREATE TABLE voyage (

ship_code varchar (5),
voyage_number int(3),
date_arrival date,
PRIMARY KEY
(ship_code,voyage_number),
FOREIGN KEY (ship_code)
REFERENCES ship (ship_code) );
82
The FOREIGN KEY Constraint

mysql> CREATE TABLE docket (

docket_number int(5),
docket_date date,
ship_code varchar (5),
voyage_number int (3),
PRIMARY KEY (docket_number),
FOREIGN KEY (ship_code, voyage_number)
REFERENCES voyage (ship_code, voyage_number)
ON DELETE CASCADE);

83
Removing Duplicates, Table Replacement:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex

FROM person
GROUP BY last_name, first_name;
mysql> DROP TABLE person;
mysql> ALTER TABLE tmp RENAME TO person;
Or
ALTER IGNORE TABLE person
ADD PRIMARY KEY (last_name, first_name);

Mysql>ALTER TABLE emp

ADD unique KEY (ename);

mysql>alter table emp

modify comm decimal(7,2) not null default 1000;
84
Renumbering an Existing Sequence

Mysql>alter table emp

drop empno;

mysql>alter table emp

add empno int not null auto_increment first,
add primary key (empno);

85
Module 9. Built-In Functions

• Overview
➢String functions
➢Numeric functions
➢Date functions
➢Special formats with Date data types
➢Conversion functions

86
String functions
Function Returns Example Result
lcase (x) Converts the entire string to SELECT lcase inder kumar
lowercase. ( 'Inder Kumar Gujral' ) gujral
FROM dual;
ucase(x) Converts the entire string to SELECT ucase INDER
uppercase. ( 'Inder' ) FROM dual;
ascii(c) ASCII value of the given SELECT ascii(‘ARTI’); 65
character, C

Concat(s1,s2.. Concatenates (joins) the SELECT concat(‘bina Bina gore

) strings, S1, S2 ’,’gore’);

replace (char, Every occurrence of str1 in char is SELECT replace( ‘Cap' , 'C', Map
str1, str2) replaced with str2. 'M' ) ;

length (char) Length of char. SELECT length (‘MySQL’); 5

rtrim(str) Trims whitespace from the SELECT rtrim(‘ priti’); priti

right of string
87
String functions
Function Returns Example Result
Substr() Return the substring as specified SELECT ra
SUBSTRING('Quadratically',5)
;
CHAR_LENGT Returns the length of the string str SELECT 7
H(str) CHAR_LENGTH(“Pragati");

INSERT(str,pos, Returns the string str, with the SELECT QuWhattic

len,newstr) substring beginning at position INSERT('Quadratic', 3, 4,
pos and len characters long 'What');
replaced by the string newstr
INSTR(str,subst Returns the position of the first SELECT 4
r) occurrence of substring substr INSTR('foobarbar', 'bar');
in string str.
LENGTH(str) Returns the length of the string str SELECT LENGTH(‘priti'); 5

LPAD(str,len,pa Returns the string str, left-padded SELECT LPAD('hi',4,'??'); ??hi

dstr) with the string padstr to a length of
len characters.
88
Numeric functions
Function Returns Example Result

SQRT (n) square root of any number select SQRT(16); 4

RAND () random numbers between 0 SELECT RAND();
and 1
ABS(N) absolute value Select abs(-5); 5

Sign(n) Return 1 or -1 or 0 Select sign(-5); -1

POW(n1,n2) Power of a number Select POW(5,3); 125

Round(n,d) Round the number Select round(4.789,1); 4.8

Format(x,d) rounded to D decimal places Select 12,332.1235

format(12332.123456, 4);

IFNULL() replace the null value column select empno, comm,

with a specific value ifnull (comm, 1200) from
emp;
89
Numeric functions
Function Returns Example Result

CEILING(X) return the smallest integer SELECT CEILING(3.46); 4

value that is not smaller than
X.
FLOOR(X) returns the largest integer SELECT FLOOR(7.55); 7
value that is not greater than X.
FORMAT(X, to format the number X in the SELECT 423,423,234
D) following given format. FORMAT(423423234.6543 .65
4453,2);
GREATEST(n returns the greatest value in the SELECT 99
1,n2,n3,.......... set of input parameters (n1, n2, GREATEST(3,5,1,8,33,99,
) n3, a nd so on). 34,55,67,43);

LEAST(N1,N to return the least-valued item SELECT 1

2,N3,N4,......) from the value list LEAST(3,5,1,8,33,99,34,5
5,67,43);
LOG10(X) returns the base-10 logarithm SELECT LOG10(100); 2.000000
of X
90
Date and Time Functions
Function Returns Example Result
ADDDATE() Add dates SELECT ADDDATE('1998-01- 1998-02-02
02',31);

ADDTIME(expr1 adds expr2 to expr1 and returns the SELECT ADDTIME('1997-12-31 1998-01-02
,expr2) result 23:59:59.999999','1 01:01:01.00000
1:1:1.000002'); 1
CURDATE() current date as a value in 'YYYY-MM- SELECT CURDATE(); 2013-03-30
DD'

CURTIME() Returns the current time as a value in SELECT CURTIME(); | 23:50:26

'HH:MM:SS'
DATE(expr) Extracts the date part of the date or SELECT DATE('2003-12-31 2003-12-31
datetime 01:02:03');

DATEDIFF(expr1 Difference in days SELECT DATEDIFF('1997-12-31 1

,expr2) 23:59:59','1997-12-30')

DATE_FORMAT Formats the date value SELECT DATE_FORMAT('1997- Saturday

(date,format) 10-04 22:23:00', '%W %M %Y'); October 1997

91
Format specifier characters
Specifier Description
%a Abbreviated weekday name (Sun..Sat)

%b Abbreviated month name (Jan..Dec)

%c Month, numeric (0..12)

%D Day of the month with English suffix (0th, 1st,

2nd, 3rd, .)
%d Day of the month, numeric (00..31)

%f Microseconds (000000..999999)

%H Hour (00..23)

%h Hour (01..12)

92
Format specifier characters
Specifier Description
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of
the week
%u Week (00..53), where Monday is the first day of
the week

93
Format specifier characters
Specifier Description
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of
the week, numeric, four digits; used with %V

%x Year for the week, where Monday is the first day of

the week, numeric, four digits; used with %v

%Y Year, numeric, four digits

%y Year, numeric (two digits)

94
Date Functions
Function Returns Example Result

DAYNAME(dat name of the weekday SELECT DAYNAME(‘2013- SATURDAY

e) 03-30');

DAYOFMONT day of the month for date, in the SELECT DAYOFMONTH('1998-02- 3

03');
H(date) range 0 to 31.

DAYOFWEEK( weekday index for date (1 = SELECT 3

date) Sunday, 2 = Monday, ., 7 = DAYOFWEEK('1998-02-03');
Saturday).
DAYOFYEAR( the day of the year for date, in the SELECT 34
date) range 1 to 366. DAYOFYEAR('1998-02-03');

EXTRACT(unit extracts parts from the date SELECT EXTRACT(YEAR | 1999

FROM date) FROM '1999-07-02');

HOUR(time) the hour for time. The range of the SELECT HOUR('10:05:03'); 10
return value is 0 to 23

LAST_DAY(dat value for the last day of the month. SELECT LAST_DAY('2003- 2003-02-28
e) 02-05');

95
Date Functions
Function Returns Example Result
MONTH(date the month for date, in the SELECT MONTH('1998- 2

) range 0 to 12 02-03')

MONTHNA the full name of the month for SELECT February

ME(date) date. MONTHNAME('1998-02-
05');
NOW() Returns the current date and SELECT NOW(); | 1997-12-15
time 23:50:26
|
SYSDATE() the current date and time as a SELECT SYSDATE(); 2006-04-12
value in 'YYYY-MM-DD 13:47:44
HH:MM:SS'
TIME(expr) Extracts the time SELECT TIME('2003-12- | 01:02:03
31 01:02:03');
TIMEDIFF(e Difference in time SELECT 46:58:57.99
xpr1,expr2) TIMEDIFF('1997-12-31 9999
23:59:59.000001’,'1997-
12-30 01:01:01.000002');
96
Date Functions

Function Returns Example Result

7
WEEK(date[, This function returns the week SELECT WEEK('1998-02-
mode]) number for date 20');

1
WEEKDAY(d weekday index for date (0 = SELECT
ate) Monday, 1 = Tuesday, . 6 = WEEKDAY('1998-02-03
Sunday). 22:23:00');

8
WEEKOFYE calendar week of the date as a SELECT
AR(date) number in the range from 1 to WEEKOFYEAR('1998-02-
53. 20');
YEAR(date) year for date, in the range 1000 SELECT YEAR('98-02- 1998
to 9999 03');

97
Conversion Functions

• The conversion functions are:

➢ str_to_date()
➢ Binary()
➢ Cast()
➢ Convert()

98
Module 10. Indexes

• Overview

➢ Understanding Indexes
➢ Unique , Simple Indexes, Partial index

➢ Creating and dropping Indexes

➢ Querying the STATISTICS table

99
Indexes
• Are database objects used to improve the performance of the
database.

• Uses the ROWID for search operations.

• There are two types of index:

➢UNIQUE
➢SIMPLE

CREATE [UNIQUE] INDEX index_name ON table_name

(column_name, column_name );
mysql> CREATE UNIQUE INDEX idx_dept_code ON
employee(dept_code);

100
Simple Indexes

Mysql>CREATE INDEX idx_dept_code ON employee (dept_code);

Mysql>CREATE INDEX emp_idx ON employee (dept_code, emp_code);

mysql>DROP INDEX name_index on table_name;

101
Partial index

Mysql> create index name_idx1

on person (name(7));

or

Mysql>alter table person

add key name_idx (name(7));

102
ALTER command to add INDEX:

mysql> alter table emp

add primary key (eno);
mysql> alter table dept
add unique u_dname (dname);
mysql> alter table emp
add index ind_job(job);

Droping the index

mysql >DROP INDEX name_index on table_name;
Mysql>ALTER TABLE emp DROP PRIMARY KEY;

103
Querying the Data Dictionary

mysql> show index from emp;

• Mysql> SELECT DISTINCT TABLE_NAME,

INDEX_NAME FROM
INFORMATION_SCHEMA.STATISTICS WHERE
TABLE_SCHEMA = ‘pragati'

104
Module 11. Advanced Queries

• Overview

➢ Table joins

➢ Sub queries

➢ Set operators

➢ MERGE statement

105
JOINS

Cross Join

mysql> SELECT emp_name, dept_name

FROM EMPLOYEE
CROSS JOIN DEPT

106
JOINS

Inner Join

mysql> SELECT emp_code, dept_name

FROM employee a JOIN dept b
ON (a.dept_code=b.dept_code)

107
JOINS

Equi Join

mysql> SELECT emp_name, dept_name

FROM EMPLOYEE, DEPT
WHERE dept.dept_code = employee.dept_code;

108
JOINS

Left Outer Join

mysql> SELECT A.emp_name, B.emp_name

FROM employee A
left join dept B
on A.reports_to = B.emp_code ;

109
JOINS

Right Outer Join

Mysql>SELECT A.emp_name, B.emp_name

FROM employee A
right join dept B
on A.reports_to = B.emp_code ;

110
JOINS

mysql> SELECT [Link], [Link]

FROM emp e
left join dept d using (deptno)

mysql> SELECT emp_code, dept_name

FROM employee a JOIN dept b
ON (a.dept_code=b.dept_code
And a.emp_code<20);

111
JOINS
Self Join

mysql> SELECT a.emp_name, b.emp_name

FROM employee A, employee B
WHERE A.reports_to = B.emp_code;

112
SUBQUERIES

mysql> SELECT * FROM emp

WHERE deptno =(SELECT FROM dept
WHERE dname = ‘operations');

mysql> SELECT * FROM orders

WHERE cust_code IN (SELECT cust_code FROM customer
WHERE city_code = 'PUNE');

mysql > SELECT * FROM dept WHERE EXISTS (

SELECT * FROM employee WHERE employee.dept_code =
dept.dept_code);

mysql > SELECT * FROM dept WHERE NOT EXISTS (

SELECT * FROM employee WHERE employee.dept_code =
dept.dept_code);
113
SUBQUERIES

➢Checking the existence of the inner query.

Mysql>select deptno from emp where EXISTS (select deptno

from dept);

mysql> SELECT * FROM employee WHERE salary = (SELECT MIN

(salary) FROM employee);

114
SET Operators
mysql> SELECT prod_code, prod_name FROM product
UNION
SELECT prod_code, prod_name FROM old_products;

Mysql>SELECT prod_code, prod_name FROM product

UNION distinct
SELECT prod_code, prod_name FROM old_product;

Mysql>SELECT prod_code, prod_name FROM product

UNION all
SELECT prod_code, prod_name FROM old_product;

115
SET Operators
The result wise UNIO, UNION ALL, DISTINCT is same.

Use UNION ALL instead of simple UNION, and DISTINCT the

output will be same but for the optimization of the query can be
increased. It is 3.5 times faster.

116
MERGE statement

• Meaning

➢ Mysql Merge Statement is used to merge the two sql statement using
UNION clause..

➢ The UNION clause is used to combine the result set of any two sql
queries.

117
MERGE statement

• Example

➢ Mysql>create table e_merge(

empno decimal(4,0),
ename varchar(10) ) engine=merge union=(e_t1,e_t2);

118
Module 12. Views

• Overview

➢ Understanding Views
➢ Creating views
➢ Altering & dropping views

➢ Manipulating data using views

➢ Viewing the Details

119
Views

• View is a virtual table

• Doesn’t store any data

• Used the extract the data from multiple tables

Restrictions on View Definitions

• Can’t have triggers on views

• cannot contain a subquery in the FROM clause of the SQL

statement.
120
Views
Restrictions on View Definitions

• User, system, or local variables are not allowed in the SQL

SELECT statement.

• Views can't point at temporary tables.

• Views created within stored procedures can't reference

parameters in the stored procedure.

121
Views

CREATE [OR REPLACE] [<algorithm attributes>] VIEW

[database.]< name> [(<columns>)]
AS <SELECT statement> [ WITH CHECK OPTION ]

122
Views
mysql> CREATE VIEW fin_emp AS
SELECT * FROM employee WHERE dept_code = 'FIN';

mysql > SELECT * FROM fin_emp;

Mysql> CREATE VIEW emp_view(emp_name,employee_no)

as select ename, empno from emp where deptno=10
With check option;

mysql> DELETE from fin_emp;

123
Views

mysql> insert into fin_emp (emp_code, emp_name, dept_code)

values (111, 'Sunil', 'FIN');

mysql> CREATE OR REPLACE VIEW fin_emp AS

SELECT * FROM employee;

124
Views

mysql> DROP VIEW fin_emp;

➢Restriction:
We can not use order by clause

125
Viewing the Details

mysql> show create view fin_emp \g;

mysql>explain select * from fin_emp;

126
Module 13. Transaction Processing

• Overview

➢ ACID

➢ Transaction Processing commands

➢ Locking table

127
Properties of Transactions(ACID):

• Atomicity
• Consistency
• Isolation
• Durability

128
Transaction Processing
• COMMIT

• ROLLBACK

129
Commit

• COMMIT is used to write the changes to the database.

• MySQL automatically commit the changes to the database.
• To force MySQL not to commit the changes automatically
following statement is used:
➢ Set autocommit = 0;

130
Transaction Processing
mysql> SAVEPOINT savepointname;
mysql> SAVEPOINT stage1;
mysql> ROLLBACK TO savepointname;
mysql> ROLLBACK TO stage1;

131
Transaction Processing
lock table table_reference_list lock_type ;

mysql> LOCK TABLES gadgets [[AS] alias] read;

Mysql> UNLOCK TABLES;

Lock_type:
write or read

132
Module 14. Miscellaneous function

• Overview

➢ if()

➢ isnull()

➢ nullif()

➢ case

➢ Loading the data from text file

133
Logical and Conditional Functions

If() function

mysql>select sal, if (sal> 1200, 1,0) from emp;

134
Logical and Conditional Functions

Isnull() function
To determine whether the value of the argument given in
parentheses is NULL.
It returns
➢ 1 if the value is NULL
➢ 0 if it is not NULL.
mysql> select ename, comm from emp
where isnull(comm);

135
Logical and Conditional Functions

null()if function
This MySQL function returns NULL if the two arguments given
are equal. Otherwise, it returns the value or results of the first
argument.
Syntax
NULLIF(condition1, condition2)

select nullif(null,null); null

select nullif(500,null); 500
select nullif(500,300); 500

136
Cases

mysql> SELECT ename,

( case deptno
when 10 then ‘ACCOUNTS’
when 20 then ‘RESEARCH’
when 30 then ‘SALES’
when 40 then ‘OPERATIONS’
else ‘UNASSIGNED’
end
) as Department
FROM emp;

137
Loading the data from text file

• Create a text file eg: c:\[Link]

• Command:
Mysql>Load data local infile ‘c:/[Link]’ into table salary;

For inserting the null values,

Priti Ght 7000

Bina \N 9000
Rita \N 8000

138
Module 15. Introduction to programming

• Overview

➢ creating local variables

➢ storing the data from the table

➢ Looping and Conditional constructs

139
User-Defined variables

Mysql>set @name=‘Anuradha’;
Mysql>set @tel_no=5432487;
Mysql>select @name, @tel_no;

140
User-Defined variables

Mysql>select @max_sal:=max(sal)
From emp
Mysql>select * from emp
Where sal=@max_sal;

141
Compound-Statement

➢used for writing compound statements

➢can contain multiple statements

➢can appear within stored programs (stored procedures and

functions, and triggers).

begin
update employee
set salary = salary + 111
where dept_code = 'MKTG';
delete from employee
where dept_code = ‘operation’;
end;
142
Example

Mysql>Create procedure GetProducts()

BEGIN
Select * from Products
END ;

143
Repeat loop

delimiter //
CREATE PROCEDURE dorepeat1(p1 INT)
BEGIN
SET @x = 0;
REPEAT
SET @x = @x + 1;
select @x;
UNTIL @x > p1
END REPEAT;
end
//
delimiter ;
call dorepeat1(20);

144
IF-ELSE Statements

if nValue > 40 then

set nCount = nCount + 1;
end if;

if nValue between 40 and 50 then

set nCount = nCount + 1;
if nValue > 40 then end if;
if nValue < 50 then
set nCount = nCount + 1;
end if;
end if;

145
IF-ELSE Statements

if nValue > 40 then if nSalary < 2000 then

set nCount1 = nCount1 + 1; set nProfTax = 0;
Else Elseif nSalary < 3500 then
set nCount2 = nCount2 + 1; set nProfTax= 15;
end if; elseif nSalary < 5000 then
set nProfTax = 30;
else
set nProfTax := 50;
end if;

146
Example
Mysql>create function total (a int, b int) returns varchar(50)
begin
declare c int;
declare s varchar(50);
set c = a + b;
if c < 10 THEN
set s = 'add is less then 10';
ELSE
set s = 'add is equal or greater then 10';
END IF;
RETURN s;
END $$
147
Example else-if
Mysql>create function check1(a int, b int) returns varchar(50)
begin
declare c int;
declare s varchar(50);
if a < b THEN
set s = 'a is less than b';
ELSEif a > b THEN
set s ='add is greater than b';
else
set s='a is equal to b';
END IF;
RETURN s;
END $$
148
Example else-if

➢To execute function

DELIMITER ;
select check1 (10, 20);

149
Example loop … end loop
Mysql> create procedure proce2()
BEGIN
DECLARE count INT default 0;
DECLARE in_count int;
set in_count =20;
increment: LOOP
SET count = count + 1;
select count;
IF count < 20 THEN ITERATE increment; END IF;
IF count > in_count THEN LEAVE increment;
END IF;
END LOOP increment;
SELECT count;
END

150
Module 16. Temporary tables

• Overview

➢ Creating temporary tables

➢ Table Cloning

➢ Getting Server Metadata

151
Usage of temporary table

mysql >CREATE TEMPORARY TABLE SalesSummary (

product_name VARCHAR(50) NOT NULL,
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

152
Dropping Temporary Tables:

Mysql>DROP TABLE SalesSummary;

153
MySQL Clone Tables

Step 1
mysql > SHOW CREATE TABLE emp \G;
Step 2
Rename this table and create another table
mysql >CREATE TABLE clone_EMP
(

EMPNO NUMERIC(4) not null,

ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2) REFERENCES DEPT(DEPTNO)
) ENGINE =InnoDB;

154
MySQL Clone Tables

Step 3
Mysql>insert into
clone_emp1(empno,ename,job,mgr,hiredate,sal,
comm, deptno)
select empno,ename,job,mgr,hiredate,sal,
comm, deptno from emp;

155
Getting Server Metadata( in MySQL)
Command Description

SELECT VERSION( ) Server version string

SELECT DATABASE( ) Current database name

(empty if none)

SELECT USER( ) Current username

SHOW STATUS Server status indicators

SHOW VARIABLES Server configuration

variables

156
Module 17. Cursors

• Overview

➢ Understanding Cursors

➢ Types of cursors

➢ Cursor operations

157
Cursors

• Allow you to manipulate a row -by-row processing of the

resultsets.

• The cursor allows you to iterate through the result set

• Can perform certain operations on each row.

• A cursor enables a programmer or DBA to perform complex

processing on the server within the database.

• Operate on all the rows in the returned by a query at one time.

•Cursor is convenient to use when you are performing on a

complex resultset
158
Cursors

• Main purpose of a cursor is when you want to perform operation

in multiple tables for each row with the results of a query
operations.

• Another reason to use cursor is to use when there is some steps

in process are optional and you want to perform those steps on
certain rows of query.

• so with cursor you can fetch the result set and then perform the
additional processing only on the rows that require it.

159
Types of Cursors

1. Asensitive

2. Read Only

3. Non_Scrollable (or forward-only)

➢ employee_cur CURSOR For select * from employee;
➢ OPEN employee_cur;
➢ FETCH employee_cur INTO var_name;
➢ CLOSE employee_cur;

160
Types of Cursors

1. Asensitive

2. Read Only

3. Non_Scrollable (or forward-only)

161
Steps of Cursors

1. Declare a cursor
2. Open a cursor statement
3. Fetch the cursor
4. Close the cursor
5. Handler

162
Steps of Cursors

1. Declare..
➢ To declare a cursor

2. Open a cursor statement

➢ This process actually retrieves the data using the previously
defined SELECT statement. With the cursor populated with
data, individual rows can be fetched

3. Fetch the cursor

➢ When we have to retrieve the next row from the cursor

4. Close the cursor

➢ CLOSE frees up any internal memory and resources used
163
Disadvantage of Cursors

➢ The major disadvantage to using a cursor is the performance

hit.

➢ Cursors take quite a few resources to accomplish their tasks.

164
Cursors

Mysql> declare
sales cursor for
SELECT
a.salesman_id,a.salesman_name, [Link], [Link]
FROM salesman a, target b, sales c
WHERE a.salesman_id=b.salesman_id
and [Link]=[Link];
begin
..............
end;

165
Cursors
mysql>CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_roll int(11) DEFAULT 0;
DECLARE t1_cur CURSOR FOR SELECT * from t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN t1_cur;
read_loop: LOOP
FETCH t1_cur INTO v_roll;
IF done THEN
LEAVE read_loop;
END IF;
select concat('the roll number is ', v_roll);
END LOOP;
CLOSE t1_cur;
END;

166
Cursors

DECLARE ... HANDLER Syntax

DECLARE handler_action HANDLER FOR condition_value [, condition_value]

... Statement

➢The DECLARE ... HANDLER statement specifies a handler that deals with
one or more conditions.

➢If one of these conditions occurs, the specified statement executes.

➢statement can be a simple statement such as SET var_name = value, or a

compound statement written using BEGIN and END

➢Handler declarations must appear after variable or condition declarations.

167
Cursors

➢Disable DATA NOT FOUND handlers from calling

functions

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

➢ Setting the value

DECLARE CONTINUE HANDLER FOR NOT FOUND SET
flag=1;

168
Cursors
Mysql>CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_deptno decimal(2,0);
DECLARE v_dname varchar(14);
DECLARE v_loc varchar(13);
DECLARE cur1 CURSOR FOR SELECT * from dept;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO v_deptno,v_dname,v_loc;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO dept1 VALUES (v_deptno,v_dname,v_loc);
END LOOP;
CLOSE cur1;
END;
169
Cursors
Advantages of cursor:
➢Best used when you want each row or more than one row one by
one.
➢Its efficient because with Cursor we are doing operations so there is
no need to write complex queries(like joins)

Disadvantages:
➢Cursor is faster than a while loop but it create more overhead in
database.
➢Cursor fetching one by one data from database so if data is more,
its take more execution time.

170
Module 18. Stored Procedure and Functions

• Overview

➢ Understanding Stored Procedures

➢ Creating Stored Procedures

➢ Parameter modes

➢ Understanding Stored Functions

➢ Creating Stored Functions

➢ Transaction

➢ Function

171
Stored Procedures

• Stored Procedures are much faster, reusable

• It avoids the overhead of network communication

• Avoid parsing, optimizing

172
Stored Procedures

CREATE [DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

173
Stored procedure

• Since MySQL version 5.0 stored procedure feature have been

added to MySQL.
• For eg:
Create procedure GetProducts()
BEGIN
Select * from Products
END ;
• Calling stored procedure
Call GetProducts();

174
Stored Procedures

• Procedure parameter modes

➢ The IN parameter mode is used to pass values to the procedure when

invoked.
➢ The OUT parameter mode is used to return a values to the caller of
the procedure.
➢ The INOUT parameter is used to pass initial values to the procedure
when invoked and it also returns updated values to the caller.

175
Stored Procedure

• Creating a Single-Statement Procedure

mysql> create procedure myProc ()

SELECT id,first_name FROM employee;

mysql> call myProc ();

mysql> drop procedure myProc;

176
Stored Procedure

Creating procedure with multiple statements:

Mysql>delimiter //

Mysql>CREATE procedure myProc ()

BEGIN
SELECT ename, sal FROM emp;
SELECT deptno, dname FROM dept;
END;
//

Mysql>delimiter ;
Mysql>CALL myProc ();

177
Stored Procedure : in parameter
mysql> DELIMITER //
mysql> CREATE PROCEDURE myProc (IN in_count INT)
BEGIN
DECLARE count INT default 0;
increment: LOOP
SET count = count + 1;
IF count < 20 THEN ITERATE increment; END IF;
IF count > in_count THEN LEAVE increment;
END IF;
END LOOP increment;
SELECT count;
END
//
mysql> DELIMITER ;
mysql> call myProc(5);
178
Stored Procedure : out parameter
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE procout (OUT p_tot_sal
INT)
BEGIN
SELECT sum(sal) INTO p_tot_sal FROM emp;
END;
//
mysql> delimiter ;
mysql>
mysql> CALL procout(@sal);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sal;

179
Procedure

Creating procedure:

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN
SELECT COUNT(*) INTO param1 FROM emp;
END;

CALL simpleproc (@a);

select @a;

drop procedure simpleproc;

180
Stored Procedure : in out parameter
mysql> delimiter //
mysql> CREATE PROCEDURE update_sal (INOUT
in_increment INT)
BEGIN
update emp
set sal= sal+ in_increment where empno=700;
SELECT sal INTO in_increment FROM emp
where empno=700;
END;
//

mysql> delimiter ;
Mysql>set @up_sal=1000;
mysql> CALL update_sal(@up_sal);
mysql> SELECT @up_sal;
181
Stored Procedure :
To see information about Stored Procedure like:
• name of database
• type of procedure
• language
• code writtern
• date of creation

mysql>SELECT * FROM [Link] WHERE name

= 'myProc'\G;

182
Stored Procedure :
In case you would want to view all the stored procedures in a
Database then we can use :

mysql>SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="dbname";

183
Stored Functions

• Stored Functions
➢ Is similar to stored procedures except that a function returns
only a single values.

Syntax:
CREATE FUNCTION f_name ([parameter(s)])
RETURNS data type
DETERMINISTIC
STATEMENTS

184
Stored Functions

mysql>delimiter //
mysql>create function hello (s char(20))
returns char(50)
deterministic
return CONCAT('Hello',space(1),s);
//

mysql>delimiter ;
mysql>select hello('world');

185
Stored Functions

Mysql>DELIMITER //
Mysql>CREATE FUNCTION join_date_ck(return_date DATE)
RETURNS VARCHAR(3)
deterministic
BEGIN
DECLARE sf_value VARCHAR(3);
IF curdate() > return_date
THEN SET sf_value = 'Yes';
ELSEIF curdate() <= return_date
THEN SET sf_value = 'No';
END IF;
RETURN sf_value;
END//

186
Stored Functions
To run the function:

Mysql>DELIMITER ;
Mysql>select empno, hiredate, curdate(), join_date_ck(hiredate)
from emp;

187
Module 19. Triggers

• Overview

➢ Understanding Triggers
➢ Applying Triggers
➢ Types of Triggers
➢ Dropping Triggers

188
Database Triggers

• A trigger defines an action the database should take when some

database related event occurs.
• It may be used to supplement declarative referential integrity, to
enforce complex business rules or to audit changes of data.
• Uses of Triggers are:
➢ Audit Data Modifications
➢ Log Events Transparently
➢ Enforce Complex Business Rules
➢ Derive Column Values Automatically
➢ Implement Complex Security Authorizations
➢ Maintain Replicate Tables

189
Database Triggers

It has the following syntax:

CREATE TRIGGER <name> <time> <event>
ON <table>
FOR EACH ROW
<body statements>

190
Database Triggers
Triggername Is the name of the trigger to be created
BEFORE engine fires the trigger before executing the triggering statement.
AFTER engine fires the trigger after executing the triggering statement.
DELETE engine fires the trigger whenever a DELETE statement removes a row
FROM the table.
INSERT Indicates that the engine fires the trigger whenever a INSERT statement
adds a row to table.
UPDATE Indicates that the engine fires the trigger whenever an UPDATE statement
changes a value in one of the columns specified in the OF clause. If the OF
clause is omitted, the engine fires the trigger whenever a UPDATE statement
changes a value in any column of the table.

191
Database Triggers
ON Specifies the schema and name of the table, which the trigger is to be created.
If schema is omitted, the engine assumes the table is in the user's own schema.
A trigger cannot be created on a table in the schema SYS.

REFERENCI- Specifies correlation names. Correlation names can be used in the block and
NG WHEN clause of a row trigger to refer specifically to old and new values of the
current row. The default correlation names are OLD and NEW. If the row
trigger is associated with a table named OLD or NEW, this clause can be
used to specify different correlation names to avoid confusion between
table name and he correlation name.
FOR EACH Designates the trigger to be a row trigger. The engine fires a row trigger once
ROW for each row that is affected by the triggering statement and meets the optional
trigger constraint defined in the when clause. If this clause is omitted the
trigger is a statement trigger
WHEN Specifies the trigger restriction. The trigger restriction contains a SQL
condition that must be satisfied for the engine to fir the trigger. This condition
must contain correlation names and cannot contain a query. Trigger restriction
can be specified only for the row triggers. The engine evaluates this
condition for each row affected by the triggering statement.

192
Database Triggers
MySQL triggers cannot:

•Use SHOW, LOAD DATA, LOAD TABLE, BACKUP

DATABASE, RESTORE, FLUSH and RETURN statements.

•Use statements that commit or rollback implicitly or explicitly

such as COMMIT, ROLLBACK, START TRANSACTION,
LOCK/UNLOCK TABLES, ALTER, CREATE, DROP,
RENAME… etc.

•Use prepared statement such as PREPARE, EXECUTE…

•Use dynamic SQL.

•Call a stored procedure or stored function.

193
Database Triggers

• Applying Triggers
➢ Triggering Event
➢It can be Insert, Update or Delete statement for a table
➢ Trigger Constraint (Optional)
➢A boolean expression for each row trigger specified
using a WHEN clause
➢ Trigger Action
➢code to be executed when a triggering statement is
encountered

194
Database Triggers

• Types of Triggers
➢ The 'time' when the trigger fires
• BEFORE trigger (before the triggering action).
• AFTER trigger (after the triggering action)

➢The 'item' the trigger fires on

• Row trigger: once for each row affected by the triggering statement
• Statement trigger: once for the triggering statement, regardless of the
number rows affected

195
Database Triggers

BEFORE triggers are used when the trigger action should

determine whether or not the triggering statement should be
allowed to complete.

By using a BEFORE trigger, you can eliminate unnecessary

processing of the triggering statement.

BEFORE triggers are used to derive specific column values before

completing a triggering INSERT or UPDATE statement.

196
Database Triggers

• Expressions in Triggers

➢ Help in referring to values in row triggers.

➢ Need to use :OLD and :NEW prefixes.

If NEW.column_name < OLD.column_name……

197
Database Triggers

• Conditional Predicates

➢ Useful when the trigger fires more than one type of DML operation.
➢ Need to use the INSERTING, UPDATING or DELETING clause.
➢ These are pre – defined PL/SQL Boolean type variables which
evaluate to either true or false.

IF DELETING (‘column_name’) THEN……

198
Database Triggers

Mysql>delimiter $$
Mysql>CREATE TRIGGER myTrigger
BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
INSERT into transaction_log (user_id, description)
VALUES (user(), 'Employee deleted ');
END$$
Mysql>delimiter ;

199
Database Triggers

To execute the trigger:

Mysql>Delete from emp

Where empno=7566;

To check ;
Mysql> select * from transaction_log

200
Database Triggers

mysql> delimiter $$
mysql> CREATE TRIGGER myTrigger
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF [Link] < 600 THEN
SET [Link]='Y';
ELSE
SET [Link]='N';
END IF;
END$$

201
Database Triggers

Mysql>Delimiter //
mysql> create trigger tr1
-> before insert on dept
-> for each row
-> begin
-> set @name=‘insert trigger ';
-> end;
-> //

202
Database Triggers

To execute

Mysql>delimiter ;
Insert into dept values(50,’new,’new’);
Select @name;

203
Dropping Triggers, View Details

• Dropping a trigger

➢ delimiter //
➢ drop trigger if exists trigger name ;

• View Details of triggers

➢ SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

204
Module 20. MySQL Prepared Statement

• Overview

➢ Transactions
➢ use of Prepared Statements
➢ Benefits of Prepared Statements
➢ Syntax

205
Transactions

• MySQL support transactions.

• Storage engine type is used InnoDB.
• For eg:
➢ delimiter $$
➢ start transaction;
➢ select @catid := max(categoryid) From categories;
➢ Set @catid=@catid+1;
➢ Insert into categories (categoryid, categoryname) values (@catid,
‘Chocolates’);
➢ Insert into products (productname, unitprice, unitsinstock, categoryid)
values (‘Dairy Milk Silk’, 50, 5, @catid);
➢ Commit;
➢ $$

206
MySQL prepared statement

• By using Prepared statement server does not have to parse the

query fully.

• It uses client/server binary protocol.

• It is faster when the query is executed multiple times.

• It uses placeholder to save statement.

207
MySQL prepare statement

➢ MySQL 4.1 and newer support server-side prepared

statements.

➢ client library can execute the query repeatedly

by specifying the statement handle

➢ Prepared statements can have parameters

➢ Parameters can given as question-mark, called as

placeholders

➢ You can repeat this as many times as desired.

208
MySQL prepare statement

❑ Benefits of prepared statement

➢ more efficient than executing a query repeatedly

➢ The server has to parse the query only once, which saves
some parsing
➢ The server has to perform some query optimization steps
only once, as it caches a partial query execution plan
➢ Sending parameters via the binary protocol is more efficient
than sending them as ASCII text.
➢ The binary protocol therefore helps save memory on the
client,
➢ Also reduces network traffic

209
MySQL prepare statement

❑ Benefits of prepared statement

➢ Only the parameters—not the entire query text—need to be

sent for each execution, which reduces network traffic.

➢ Prepared statements can also help with security.

➢ A prepared statement is specific to the session

210
MySQL prepare statement

• Prepare statement is based on other three MySQL statements

as follow:
➢ PREPARE :- to prepare statement for execution.
➢ EXECUTE :- to execute a prepare statement
➢ DEALLOCATE PREPARE :- to release a prepare statement.

211
Prepare statement …

mysql> SET @sql := 'SELECT actor_id, first_name, last_name

-> FROM [Link] WHERE first_name = ?';
mysql> PREPARE stmt_fetch_actor FROM @sql;
mysql> SET @actor_name := 'Penelope';
mysql> EXECUTE stmt_fetch_actor USING @actor_name;
mysql> DEALLOCATE PREPARE stmt_fetch_actor;

212
Module 21. Exception Handling

• Overview

➢ Understanding Exception Handling

➢ Using HANDLER
➢ List of Exceptions

213
Exception Handling

➢Usually handled by try .. catch block in .NET environment

➢Handled by using
➢DECLARE CONTINUE HANDLER
➢Need to declare the handler

➢should not use MySQL error code 0 or SQLSTATE values that

begin with '00'

214
Exception Handling

Syntax
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement

handler_action:
CONTINUE
| EXIT
| UNDO

215
Exception Handling

mysql> CREATE PROCEDURE handlerdemo ()

BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO test.t VALUES (1);
SET @x = 2;
INSERT INTO test.t VALUES (1);
SET @x = 3;
END;

mysql> CALL handlerdemo()//

mysql> SELECT @x//

216
Exception Handling
Error code Message
1240 Key reference and table
reference don't match
1062 Duplicate key in index
1005 Can't create table
1006 Can't create database
1008 Can't drop database
1016 Can't open file
1036 Table is read only

1037 Out of memory

1044 Access denied for user
1046 No database selected

1068 Multiple primary key defined

217