MySql

The MySQL database is a very fast, multi-threaded, multi-

user, and robust Structured Query Language database server.
MySQL is the most popular Open Source SQL database
management system, is developed, distributed, and supported
by MySQL AB. MySQL AB is a commercial company, founded by
the MySQL developers. MySql database is open source
database. Open Source means that it is possible for anyone
to use and modify the software. Anybody can download the
MySQL software from the Internet and use it without paying
anything. It is Written in C and C++. It Works on many
different platforms. We use MySQL Server with databases that
can contain 50 million records, 60,000 tables and about
5,000,000,000 rows.
Some Important commands in My Sql :
(i) mysql> create database ranjit;  It creates a new database in the
current user.
(ii) mysql> staus; : It displays the status of the current user.
(iii) mysql >use ranjit; : This command is used to entered in the given
database name.
(iv) mysql> show tables; : This command is used to displays the list of
all tables in the current database.
(v) mysql> drop database ranjit; : This command is used to delete the
given database name.
(vi) Mysql>show databases;  This command is used to display all
databases in the mysql.

SQL can be divided into two parts:

(i) The Data Manipulation Language (DML)

(ii) The Data Definition Language (DDL).

(i) The query and update commands are the DML part of SQL:
 SELECT - extracts data from a database
 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database

(ii) The DDL part of SQL permits database tables to be created or

deleted. We can also define constraints between tables. The most
important DDL statements in SQL are:
  CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 DROP DATABASE- Delete the database

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

Syntax:
CREATE TABLE table_name (column_name1 data_type,column_name2
data_type,column_name3 data_type);

Data Types in MySQL

In MySQL there are three main types : text, number, and Date/Time
types.

(a) Text types:

Data type Description

CHAR(size) It Holds a fixed length string (can contain letters, numbers, and special
characters). The fixed size is specified in parenthesis. Can store up to
255 characters
VARCHAR(size) It Holds a variable length string (can contain letters, numbers, and
special characters). The maximum size is specified in parenthesis. Can
store up to 255 characters. Note: If we put a greater value than 255, it
will be converted to a TEXT type
TINYTEXT It Holds a string with a maximum length of 255 characters
TEXT It Holds a string with a maximum length of 65,535 characters

(b) Number types:

Data type Description

TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED. The maximum number of digits
may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED. The maximum number of
digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED. The
maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED. The maximum number of digits may
be specified in parenthesis
FLOAT(size , d) A small number with a floating decimal point. The maximum number of
digits may be specified in the size parameter. The maximum number of
digits after decimal point is specified in the second parameter
DOUBLE(size , d) A large number with a floating decimal point. The maximum number of
 digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the d parameter

(c) Date types:

Data type Description

DATE A date. Format: YYYY-MM-DD

Note: The supported range is from '1000-01-01' to '9999-12-31'

DATETIME *A date and time combination. Format: YYYY-MM-DD HH:MM:SS

Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31

23:59:59'
TIME A time. Format: HH:MM:SS

Note: The supported range is from '-838:59:59' to '838:59:59'

CREATE TABLE Example

CREATE TABLE Persons(P_Id int,LastName varchar(255),
FirstName varchar(255),Address varchar(255),City
varchar(255));
Deleting the tables :
(i) Drop table table_name
(ii) Drop table table1 , table2 , …….. , table n;

SQL Constraints
Constraints are used to limit the type of data that can go into a table.

There are following types of constraints in MySql

(i) NOT NULL

(ii) PRIMARY KEY
(iii) UNIQUE KEY
(iv) FOREIGN KEY
(v) DEFAULT

(i) NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. It
means that we cannot insert a new record, or update a record without
adding a value to this field.

For Example:
CREATE TABLE Persons(P_Id int NOT NULL,LastName
varchar(255) NOT NULL,FirstName varchar(255),Address
varchar(255),City varchar(255));
(ii) PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database
table. Primary keys must contain unique values. Each table can have
maximum only one primary key column.

For Example :
CREATE TABLE Persons(P_Id int NOT NULL,LastName
Larchar(255) NOT NULL,FirstName varchar(255),Address
varchar(255),City varchar(255),PRIMARY KEY (P_Id));

Note : We can also create composite primary key in table.

CREATE TABLE Persons(P_Id int ,LastName Varchar(255),
FirstName varchar(255),Address varchar(255),City
varchar(255),PRIMARY KEY (P_Id,LastNane,City));

Note : We can also insert blank value in primary key columns

only one times.
PRIMARY KEY Constraint on ALTER TABLE
ALTER TABLE Persons ADD PRIMARY KEY (P_Id);
ALTER TABLE Persons ADD PRIMARY KEY (P_Id,city);
To DROP the PRIMARY KEY Constraint on a table
ALTER TABLE Persons DROP PRIMARY KEY
(iii) UNIQUE key Constraint
The UNIQUE KEY constraint is uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness
for a column or set of columns. A PRIMARY KEY constraint automatically has a
UNIQUE constraint defined on it.

Note: We can have many UNIQUE constraints per column, but only one
PRIMARY KEY constraint per table.

Note: We can also insert the NULL value in unique key

columns one or more times.

For Example :
CREATE TABLE Persons(P_Id int NOT NULL,LastName
varchar(255) NOT NULL,FirstName varchar(255),
Address varchar(255),City varchar(255),UNIQUE (P_Id));

Note: We can also create composite unique key.

 CREATE TABLE Persons(P_Id int NOT NULL,LastName
varchar(255) NOT NULL,FirstName varchar(255),
Address varchar(255),City varchar(255),UNIQUE (P_Id,
LastName , City));

Note: We can also create unique key on multiple columns.

CREATE TABLE Persons(P_Id int NOT NULL,LastName
varchar(255) NOT NULL,FirstName varchar(255),
Address varchar(255),City varchar(255),UNIQUE(P_Id),
UNIQUE(LastName) , UNIQUE(City));
UNIQUE Constraint on ALTER TABLE
(a) ALTER TABLE Persons ADD UNIQUE (P_Id);
(b) ALTER TABLE Persons ADD UNIQUE (P_Id,city);
DROP the UNIQUE Constraint
(a) ALTER TABLE Persons DROP INDEX p_ID;
(c) ALTER TABLE Persons DROP INDEX city;

(iv) FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Create table city_table(city_code int , city_name varchar(20),primary

key(city_code));
CREATE TABLE emp_info (emp_code varchar(20) NOT NULL,name
varchar(50) NOT NULL,city_code int,
PRIMARY KEY (emp_code), CONSTRAINT fk_emp_city FOREIGN KEY
(city_code) REFERENCES city_table(city_code));
FOREIGN KEY Constraint on ALTER TABLE
ALTER TABLE emp_info ADD CONSTRAINT fk_emp_city FOREIGN KEY
(city_code) REFERENCES city_table(city_code);

To DROP a FOREIGN KEY Constraint

ALTER TABLE emp_info DROP FOREIGN KEY fk_emp_city;

(v) DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a
column. The default value will be added to all new records, if no
other value is specified.

For Example :
CREATE TABLE stud_info(roll varchar(10), Name varchar(25), gender
varchar(15) default ‘Male’, City varchar(25) DEFAULT 'Patna');
DEFAULT Constraint on ALTER TABLE
ALTER TABLE stud_info ALTER gender set default ‘Male’, ALTER
City SET DEFAULT 'Patna';

To DROP a DEFAULT Constraint

ALTER TABLE stud_info ALTER City DROP DEFAULT;

(i) INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.

It is possible to write the INSERT INTO statement in three forms:

(a) The first form doesn't specify the column names where the data will be
inserted, only their values:

INSERT INTO table_name VALUES (value1, value2, value3,...);

Eg.
Insert into stud_info values(‘101’, ’ranjit’, ’patna’);

(b) The second form specifies both the column names and the values to be
inserted:

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...);
Eg.
Insert into stud_info(roll,name,address) values(‘101’,
’ranjit’, ’patna’);

(c) Insert Multiple rows at a time :

insert into table_name(list of columns) values(list of 1st rows), (list of 2nd
rows), (list of 3rd rows);
Eg.
Insert into stud_info(roll,name,address) values(‘101’,
’ranjit’, ’patna’), (‘102’,’pankaj’,’siwan’), (‘103’,
’vijay’,’gaya’);
Note : If the value is varchar or date type value then we must write the value
within single quotes or double quotes.
(iii) UPDATE Statement
The UPDATE statement is used to update the existing records in a table.

The general Syntax is :

UPDATE table_name SET column1 = value1, column2 =
value2,...
WHERE some_column = some_value;
Note: The WHERE clause specifies which record or records
that should be updated. If we omit the WHERE clause then all
records will be updated.
For Example :
UPDATE stud_info SET Address = 'Boring Road 800023', City='Patna'
WHERE roll=’101’;

(iii) The DELETE Statement

The DELETE statement is used to delete rows in a table.
The general Syntax is :
DELETE FROM table_name WHERE some_column=some_value;
For Example :
DELETE FROM stud_info WHERE roll=’101';
Delete All Rows
DELETE FROM table_name;
Eg. Delete from stud_info;
(iv) The SQL “SELECT” Statement
The SELECT statement is used to select/display data from a database
table.
The general syntax is :
SELECT column_names FROM table_name;
“Or”
SELECT * FROM table_name;
For Example :
(i) Select roll, first_name, city from stud_info;
(ii) Select * from stud_info;
(iii) Select * from stud_info\G; (It Displays
the record in Row wise.);
(iv) Insert into stud1(roll,name,address) select
roll,name,address from stud2;

Creating AUTO INCREMENT Field

Auto-increment field allows a unique number to be generated when
a new record is inserted into a table.
For Example :
CREATE TABLE stud_info ( SL int AUTO_INCREMENT,
Name varchar(25) ,Address varchar(50), PRIMARY KEY
(SL));
Note : The Auto_Increment Field Must be the
Primary Key. By default, the starting value of
AUTO_INCREMENT is 1, and it will increment by 1 for
each new record. If we want to change the
AUTO_INCREMENT starting value with another value
then we have to use the following SQL statement:

ALTER TABLE stud_info AUTO_INCREMENT = 100;

Display Schema of table :
(i) Desc table_name;
(ii) Describe table_name;
(iii) Show columns from table_name;
(iv) Explain table_name;

Copying Table :
(i) Create table new_table_name select * from
old_table_name;
(ii) Create table database_name.new_table_name select *
from database_name.old_table_name;
Alter the Structure of table :
(i) alter table table_name add column column_name

datatype(size); : Using this command ,the

new column is added at the last
position of the table by default.
Eg. Alter table stud_info add column marks int(3);

(ii) alter table table_name add column column_name

datatype after existing_column_name;
Eg. Alter table stud_info add column marks int after address;
 (iii) alter table table_name add column column_name
datatype first; : It adds one column at the first position of the
table;

Eg. Alter table stud_info add column id int(3) first ;

(iv) alter table table_name drop column column_name;

Eg. Alter table stud_info drop column marks;

(v) alter table old_table_name rename new_table_name; :

This command is used for rename the table name;
Eg. Alter table stud_info rename stud1;

(vi) alter table table_name change column

old_column_name new_column_name datatype; : This
command is used for rename the column name;
Eg. Alter table stud_info change column marks tot_marks int;

Select Command
Use of alias name in select statement:
(i) Select stud_roll ‘Roll’,FirstName,
LastName,city from stud_info;
(ii) Select stud_roll AS ‘Roll’ ,FirstName,
LastName ,city from stud_info;
(iii) Select roll, concat(FirstName,’ ‘,
LastName) As ‘Name’ , City from stud_info;
(iv) Select * from Stud_info \G ; (It Displays
the record in Row wise.);
WHERE Clause
The WHERE clause is used to filter the records. The WHERE clause is used to
extract only those records that fulfill a specified criteria.

Syntax:
SELECT column_name(s) FROM table_name WHERE
column_name operator value;
For Example :
SELECT * FROM stud_info WHERE City='Ara'

Operators Allowed in the WHERE Clause

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Note: In some versions of SQL the <> operator may be written as !=

Example:
Select * from stud_info where roll>103;
Select * from stud_info where roll<103;
Select * from stud_info where roll <= 103;
Select * from stud_info where roll <>104;
Select * from stud_info where roll != 104;
Select * from stud_info where roll between 104 and 107;
Select * from stud_info where name like ‘ r % ‘;
Select * from stud_info where name like ‘_a % ‘;
Select * from stud_info where name like ‘ _a__i %‘;
Select * from stud_info where course in (‘mca’,’mba’,’bca’);

The LIKE Operator

The LIKE operator is used to search for a specified
pattern in a column.
Syntax :->
SELECT column_name(s) FROM
table_name WHERE column_name LIKE
pattern;
For Example :
SELECT * FROM emp1 WHERE City LIKE
's%';
Note : The "%" and “_”(underscore) sign can be
used to define wildcards.

Wildcard Description
% A substitute for zero or more
characters
_ (underscore) A substitute for exactly one character
For Example :
(i) SELECT * FROM Persons WHERE City LIKE
'%nes%';
(ii) SELECT * FROM Persons WHERE FirstName
LIKE '_la';
(iii) SELECT * FROM Persons WHERE LastName
LIKE 'Choo_ da_ Sh_am_L';
(v) select * from stud_info where roll
like ‘%2%’;

The IN Operator
The IN operator allows us to specify multiple values in a
WHERE clause.

Syntax
SELECT column_name(s) FROM table_name WHERE
column_name IN (value1,value2,...)
Example1:
SELECT * FROM Persons WHERE LastName IN
('Jha','Mishra',’Pathak’,’Tiwari’,’pandey’);
Example2(Example just like intersection) :
Select * from stud_info1 where rol1,course,session in (select
roll,course,session from stud_info2);
Example3(Example just like minus between two
tables) :
Select * from stud_info1 where rol1,course,session not in (select
roll,course,session from stud_info2);

The BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to select a
range of data between two values. The values can be numbers,
text, or dates.
Syntax
SELECT column_name(s) FROM table_name WHERE
column_name BETWEEN value1 AND value2;
Example :
SELECT * FROM stud_info WHERE Name BETWEEN
'arun' AND 'ranjit';
SELECT DISTINCT Statement
Some of the columns may contain duplicate
values. Sometimes we want to display the list
of only the different (distinct) values in a
table. The DISTINCT keyword can be used to
return only distinct (different) values.
Syntax :
SELECT DISTINCT column_name(s) FROM
table_name;
For Example :
SELECT DISTINCT City FROM Persons;
Select distinct roll,name from stud_info;
The ORDER BY Keyword
(i) The ORDER BY keyword is used to sort the result-set by a
specified column.
(ii) The ORDER BY keyword sorts the records in ascending
order by default.
(iii) If we want to sort the records in a descending order then
we have to use the DESC keyword.
Syntax :->
SELECT column_name(s) FROM table_name ORDER
BY column_name(s) ASC/DESC;
For Example :
SELECT * FROM stud_info ORDER BY
LastName DESC;
The LIMIT CLAUSE
The LIMIT clause is used to specify the number of records to
return.
Syntax :->
SELECT column_name(s) FROM table_name LIMIT
number_of_records;
“Or”
Select column_name(s) from table_name limit
index_no,no_of_records;
For Example :
SELECT * FROM Persons LIMIT 5;
SELECT * FROM Persons order by name
desc LIMIT 2,4;

The UNION Operator

The SQL UNION operator combines two or more SELECT statements.
Each SELECT statement within the UNION must have the same
number of columns. The columns must also have similar data types,
and the columns in each SELECT statement must be in the same
order.

Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by

default. If we want to display duplicate values also then we have to
use UNION ALL operator.

Syntax :
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Example :
(a) SELECT roll FROM stud_info UNION SELECT
roll FROM stud
(b) SELECT roll FROM stud_info UNION ALL
SELECT roll FROM stud

Table JOINING
SQL Table joins are used to query data from two or
more tables, based on a relationship between certain
columns in these tables. The JOIN keyword is used in an
SQL statement to query data from two or more tables, based
on a relationship between certain columns in these tables.

SQL INNER JOIN:

It Returns rows when there is at least one
match in both tables.

Syntax :
SELECT column_name(s) FROM table1 INNER
JOIN table2 ON table1.column_name =
table2.column_name;
Example :
SELECT stud_info.roll,
stud_info.Name, emp_info.emp_code
FROM stud_info INNER JOIN emp_info
ON stud_info.name = emp_info.name
ORDER BY stud_info.Name;
Note : The INNER JOIN keyword returns rows
when there is at least one match in both tables. If
there are rows in "Stud_info" that do not have
matches in "emp_info", those rows will NOT be
displayed.
MySQL Date Functions
The following table lists the most important built-in
date functions in MySQL:
Function Description
NOW() It Returns the current date and time.
Example: Select Now() as ‘AAj KA Date Aur Time’;
CURDATE() It Returns the current date only.
Example: Select Curdate() as ‘AAj KA Tarikh’;
CURTIME() It Returns the current time only.
Example: Select curtime() as ‘Abhi KA Time’;
DATE() It Extracts the date part of a date or date/time
expression.
Example : SELECT roll, DATE(dob) AS DOB FROM
Stud_info;
Note: Here dob is column name of stud_info table.
EXTRACT() This function is used to return a single part of a
date/time, such as year, month, day, hour, minute,
etc.
Syntax: EXTRACT(unit FROM date);
Note: Where date is a valid date expression and
unit can be one of the following: MICROSECOND,
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH,
 QUARTER, YEAR, SECOND_MICROSECOND,
MINUTE_MICROSECOND, MINUTE_SECOND,
HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE,
DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE,
DAY_HOUR, YEAR_MONTH.

SELECT EXTRACT(YEAR FROM dob)

Example :
AS DobYear, EXTRACT(MONTH FROM dob) AS
DobMonth, EXTRACT(DAY FROM dob) AS
DobDay, FROM stud_info WHERE roll=19;

DATE_ADD() This function Adds a specified time interval to a given

date.
Syntax: DATE_ADD(date,INTERVAL expr type)

Where “date” is a valid date expression and “expr” is

the number of interval we want to add.

type can be one of the following: MICROSECOND,

SECOND, MINUTE, HOUR,DAY, WEEK, MONTH,
QUARTER, YEAR, SECOND_MICROSECOND,
MINUTE_MICROSECOND, MINUTE_SECOND,
HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE,
DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE,
DAY_HOUR, YEAR_MONTH.

Example : SELECT roll,DATE_ADD(dob,INTERVAL 90

DAY) AS ‘Date Of Birth’ FROM stud_info;
DATE_SUB() This function Subtracts a specified time interval from
a given date.
Syntax : DATE_SUB(date,INTERVAL expr type);
Example : SELECT roll,DATE_SUB(dob,INTERVAL 25
DAY) AS ‘Date Of Birth’ FROM stud_info;
DATEDIFF() This function Returns the number of days between
two dates.
Syntax : DATEDIFF(date1,date2);
Note : Where date1 and date2 are valid date or
date/time expressions. Only the date parts of the
values are used in the calculation.
Example : SELECT DATEDIFF('2008-11-30','2008-11-
29') AS ‘DiffDate’;
DATE_FORMAT() This function Displays date/time data in different
formats.
Syntax : DATE_FORMAT(date,format);

Note : Where “date” is a valid datetime and

“format” is specifies the output format for the
date/time. The formats that can be used are:

Eg.: Select date_format(now() , ‘%a’);

Format Description
%a Abbreviated weekday name
%b Abbreviated month name
%c Month, numeric
%D Day of month with English suffix
%d Day of month, numeric (00-31)
%e Day of month, numeric (0-31)
%f Microseconds
%H Hour (00-23)
%h Hour (01-12)
%I Hour (01-12)
%i Minutes, numeric (00-59)
%j Day of year (001-366)
%k Hour (0-23)
%l Hour (1-12)
%M Month name
%m Month, numeric (00-12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss 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 week
%u Week (00-53) where Monday is the first day of week
%V Week (01-53) where Sunday is the first day of week,
used with %X
%v Week (01-53) where Monday is the first day of week,
used with %x
%W Weekday name
%w Day of the week (0=Sunday, 6=Saturday)
%X Year of the week where Sunday is the first day of
week, four digits, used with %V
 %x Year of the week where Monday is the first day of
week, four digits, used with %v
%Y Year, four digits
%y Year, two digits

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from
values in a column. Some Useful aggregate functions:

1. AVG() : It Returns the average value.

Example : Select AVG(Age) As ‘Average Age’ from

stud_info;

2. COUNT()  It Returns the number of rows.

Example : Select Count(Age) As ‘Total No Of

Records ’ from stud_info;

Select count(*) from stud_info;

3. MAX()  It Returns the largest value.

Example : Select MAX(Age) As ‘Maximum Age’

from stud_info;

4. MIN()  It Returns the smallest value.

Example : Select MIN(Age) As ‘Minimum Age’

from stud_info;

5. SUM()  It Returns the sum of all values.

Example : Select SUM(Age) As ‘Total Age’ from

stud_info;

6. UCASE()  This function converts the value of a

field to uppercase.
 Example : Select UCASE(name) As ‘Name’ from
stud_info;

7. LCASE()  This function converts the value of a

field to lowercase.

Example : Select LCASE(name) As ‘Name’ from

stud_info;

8. MID()  This function is used to extract

characters from a text field.

SELECT MID(column_name,
Syntax :
start[,length]) FROM table_name;
Example : Select MID(name,2,4) As ‘Name’ from
stud_info;

Select MID(name,2) As ‘Name’ from stud_info;

9. ROUND()  This function is used to round a

numeric field to the number of decimals
specified.

SELECT ROUND(column_name,
Syntax :
decimals) FROM table_name;
Example : Select ROUND(per_marks,1) As
‘Percentage Marks’ from stud_info;

GROUP BY Statement
The GROUP BY statement is used to conjunction with the
aggregate functions to group the result-set by one or more
columns.
Syntax: SELECT column_name,
aggregate_function (column_name) FROM
table_name WHERE column_name operator value
GROUP BY column_name;
Example1:select roll, name,
sum(phy), sum(chem), sum(math),
sum(bio) from marks_details where
class=’VI’ and section=’A’ group by
roll;
Example 2 : select roll, name,
Class , Section, sum(phy),
sum(chem), sum(math), sum(bio) from
marks_details group by
Class,Section, roll;
HAVING Clause
The HAVING clause is added to SQL because
the WHERE keyword could not be used with
aggregate functions.

Syntax: SELECT column_name,

aggregate_function(column_name) FROM
table_name WHERE column_name
operator value GROUP BY column_name
HAVING
aggregate_function( column_name)
operator value;
Example : select * from
marks_details group by roll having
count(roll)>=2;
SubQuery in MySql
A subquery is a query within a query. It is also
called an inner query or a nested query. A
subquery can be used anywhere is allowed. It
is a query expression enclosed in parentheses.
Subqueries can be used with SELECT,
INSERT, UPDATE or DELETE statements.

There is more than one way to execute an

SQL task. Many subqueries can be
replaced by SQL joins. SQL joins are
usually faster.

Subquery with the INSERT statement:

If We want to create a copy of some field from the “stud_info” table

Into another table called “stud” then We have to create a subquery
as:

Note :First of all we have to create a new table “stud1” and

we have exists table “stud2” with records then :-

Insert into stud1 select roll, now(),age from stud2;

Note: In above table the schema of “stud1” table is :

Roll varchar(10)
Curdate date
Age int
Insert into stud_info values(‘101’,’Ranjit’, (select dist_name
from dist_table where dist_code=’10’));

Subquery with the Update

statement:

Update stud1 set roll = (select roll

from stud2 limit 4,1), name =
(select name from stud3 where
roll=101 limit 1) where dist=(select
dist from dist_table where
dist_code=’5’);
Subquery with the Delete statement:

Delete from stud1 where roll =

(select roll from stud2 where
dob >= ‘2012-3-20’ limit 1);
Subquery with the Select
statement:

Select * from stud_info where

dist_name = (select dist_name from
dist_table where city_name =(select
city_name from city_table where
city_code=’15’));
Trigger
A Trigger is a named database object which defines some action that the
database should take when some databases related event occurs. Triggers
are executed when we issues a data manipulation command like INSERT,
DELETE, UPDATE on a table for which the trigger has been created. They
are automatically executed and also transparent to the user.

CREATE TRIGGER
The general syntax of CREATE TRIGGER is :

CREATE TRIGGER trigger_name trigger_time

trigger_event ON tbl_name FOR EACH ROW
trigger_statement;

The trigger can associate only with the table name and that must be refer to
a permanent table. Trigger_time means trigger action time. It can be
BEFORE or AFTER. It is used to define that the trigger fires before or after
the statement that executed it. Trigger_event specifies the statement that
executes the trigger. The trigger_event can be of any DML Statement:
INSERT, UPDATE, DELETE.

We can not have the two triggers for a given table, which have the same
trigger action time and event. We cannot have two BEFORE INSERT
triggers for the same table. But we can write one BEFORE INSERT and one
BEFORE UPDATE trigger for a same table.
Trigger_statement have the statement that executes when the trigger
fires but if we want to execute multiple statement then we have to use the
BEGIN and END compound statement. A trigger only can fire with one
event. To define trigger which are fired by multiple events, we have to
define multiple triggers. One trigger for each event.

We can refer the columns of the table that associated with trigger by
using the OLD and NEW keyword. OLD.column_name is used to refer the
column of an existing row before it is deleted or updated and
NEW.column_name is used to refer the column of a new row that is
inserted or after updated existing row.
 In INSERT trigger we can use only NEW.column_name because there
is no old row and in a DELETE trigger we can use
only OLD.column_name because there is no new row. But in UPDATE
trigger we can use both, OLD.column_name is used to refer the columns of
a row before it is updated and NEW.Column_name is used to refer the
column of the row after it is updated.

For Example:
(i) First of all we have to create a
table “stud_info” as :--
Mysql> create table stud_info(roll int,
name varchar(30), address varchar(50));
(ii) Now change the delimiter
type ; to // as :--
Mysql> delimiter //
(iii) Now create a trigger as :--
Mysql> create trigger mytrigger BEFORE
insert on stud_info for each row begin
if (new.roll>=1 and new.roll<=50) then
set new.roll=new.roll+1;
elseif(new.roll>=50 and new.roll<100)
then new.roll=new.roll+5; end if ;
end //

Note : If we want to delete the

trigger then we have to write as :
Drop trigger trigger_name ;
Example2 of trigger :
Create trigger tot_amt_calculate after insert
on fee_table for each row begin update
stud_info set tot_pmt_amt=(select
sum(fee_table.amount) from fee_table where
stud_info.roll= fee_table.roll); end //
Example3 of trigger :
(i) First of all we have to create two tables as :

Create table stud_info(id varchar(15), name

varchar(25), total_fee int, tot_pmt_fee int,
dues_fee int);
Create table payment_table(id varchar(15),
pmt_date date, pmt_amt int);
(ii) After creating tables we write a trigger as :--
Create trigger trg1 after insert on payment_table
for each row update stud_info set
dues_fee=total_fee-(select sum(pmt_amt) from
payment_table where stud_info.id =
payment_table.id), tot_pmt_fee = (select
sum(pmt_amt) from payment_table where stud_info.id
= payment_table.id);

“Or”
Mysql> Delimiter //

Mysql> Create trigger trg1 after insert on

payment_table for each row begin update stud_info
set dues_fee=total_fee-(select sum(pmt_amt) from
payment_table where stud_info.id =
payment_table.id); update stud_info set
tot_pmt_fee = (select sum(pmt_amt) from
payment_table where stud_info.id =
payment_table.id); end //

Creating function in mysql :

(i) Sum Of two numbers :
Mysql> create function fun1() returns
int
Begin
Declare a,b,c int;
 Set a=50;
Set b = 60 ;
Set c = a + b;
Return c;
End //
Execute the above function as :
Mysql> select fun1() //
(ii) Calculate area of any
circle:
Mysql> Create function area(r double)
returns double
Begin
Declare a double;
Set a = r * r * pi();
return a ;
end //
Execute the above function :
Mysql> select area(5) //

Creating Procedure in mysql :

(i) Calculate area of any
circle:
<Mysql> create procedure area(in r
double,out a double)
Begin
Set a = r * r * pi();
End //
Execute the procedure as :
Mysql> call area(5 , @ar) //
Mysql> select @ar //
Call the procedure in PHP
(i)First of all we have to create an
procedure as :
Mysql> Create procedure save_record(in roll
int , in nm varchar(20), in add1 varchar(25))
Begin
Insert into stud_info(roll,name,
address) values(roll,nm,add1);
End //
(ii) Write the PHP Code as :--

<?php
$con=mysql_connect("localhost","root","kumar");
mysql_select_db("ranjit",$con);
mysql_query("call save_record(25,'vinay',’patna’)");
mysql_close($con);
?>

Connectivity With PHP and SQLServer:

$con1=array(“database” => ”database_name”);
$con = sqlsrv_connect(“server_name”,$con1);

The sqlsrv_query() and sqlsrv_prepare() functions each accept four

parameters i.e $con, $tsql, $params(optional), and $options (optional, not
shown).

· (i) $con : This is required parameter in PHP connection resource

created with the sqlsrv_connect() function.

(ii) $tsql : This is required parameter in a string that defines a

Transact-SQL query. Question marks (?) are used as placeholders for
parameters.

· (iii) $params : This is optional parameter in an array of values that

correspond (in order) to the parameter placeholders (?) in the query
defined by the $tsql parameter. Each value in the $params array can be a
literal value (such as 5), a PHP variable (such as $myVar), or an array with
the following structure:

array($value [, $direction [, $phpType [, $sqlType]]]);

This array is used to specify the parameter value, the parameter direction
(in case of stored procedure), the PHP type of the parameter and the SQL
Server type of a value sent to the server.
 (iv) $options : This is also optional parameter in an associative
array that sets properties on the query. There are Two keys supported i.e
QueryTimeout and SendStreamParamsAtExec. The QueryTimeout key sets
the maximum time in seconds that a query is allowed to run. The
SendStreamParamsAtExec key determines if all stream data is sent at the
time of query execution or if subsequent calls
to sqlsrv_send_stream_data are necessary to send all stream data.

Retrieving Data from the SqlServer

The general pattern for retrieving data with the SQL Server 2005 for PHP
involves defining and executing a query and then using one of the following
three options to retrieve data from the result set.

· (i) The sqlsrv_fetch_array() function (retrieves a row of data as an

array).

· (ii) The sqlsrv_fetch_object() function (retrieves a row of data as a

PHP object).

· (iii) The combination of the sqlsrv_fetch()

and sqlsrv_get_field() functions (retrieves a single field from a row of data).

Note: These functions provide forward-only access to the rows of a

result set.

When choosing which option to use, consider the following:

The sqlsrv_fetch_array and sqlsrv_fetch_object functions pull an

entire row of data into script memory. This may not be desirable for rows
that contain large amounts of data.

Data returned by the sqlsrv_fetch_array() and sqlsrv_fetch_object()

functions will be typed according to the defaults PHP data types. For more
information.

· Using the combination of sqlsrv_fetch() and sqlsrv_get_field() allows

us to specify the PHP data type of the returned data, including specification
of the data as a stream.

Configure the Sqlserver Driver for PHP :

(i) First of all Download the “php_sqlsrv_ts.dll” file.
(ii) Copy above file into “c:\php5\ext” location.
(iii) Add the following line in “c:\php5\php.ini” file:--
extension=php_sqlsrv_ts.dll
(iv) Restart the apache server.
(v) Now we can write the program as :---

<?php
$ser = "india";
$db = array("Database"=>"ranjit");
 $con = sqlsrv_connect( $ser, $db);
$rs = sqlsrv_query( $con,"select * from stud_info");
while($row=sqlsrv_fetch_array($rs,SQLSRV_FETCH_ASSOC))
{
echo($row['roll']." ".$row["name"]." <br/>");
}
“or”
while($row=sqlsrv_fetch_array($rs,SQLSRV_FETCH_NUMERIC))
{
echo($row[0]." ".$row[1]." <br/>");
}

sqlsrv_close( $conn);

?>

Function and Operator

Name Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
AES_DECRYPT() Decrypt using AES
AES_ENCRYPT() Encrypt using AES
AND, && Logical AND
ASCII() Return numeric value of left-most character
ASIN() Return the arc sine
Assign a value (as part of a SET statement, or
as part of the SET clause in
= an UPDATE statement)
:= Assign a value
ATAN2(), ATAN() Return the arc tangent of the two arguments
ATAN() Return the arc tangent
AVG() Return the average value of the argument
BENCHMARK() Repeatedly execute an expression
Check whether a value is within a range of
BETWEEN ... AND ... values
Return a string representation of the
BIN() argument
BINARY Cast a string to a binary string
BIT_AND() Return bitwise and
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
 Name Description
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
& Bitwise AND
~ Invert bits
| Bitwise OR
^ Bitwise XOR
CASE Case operator
CAST() Cast a value as a certain type
Return the smallest integer value not less
CEIL() than the argument
Return the smallest integer value not less
CEILING() than the argument
CHAR_LENGTH() Return number of characters in argument
CHAR() Return the character for each integer passed
CHARACTER_LENGTH() A synonym for CHAR_LENGTH()
CHARSET() Return the character set of the argument
COALESCE() Return the first non-NULL argument
Return the collation coercibility value of the
COERCIBILITY() string argument
COLLATION() Return the collation of the string argument
COMPRESS() Return result as a binary string
CONCAT_WS() Return concatenate with separator
CONCAT() Return concatenated string
Return the connection ID (thread ID) for the
CONNECTION_ID() connection
Convert numbers between different number
CONV() bases
CONVERT_TZ() Convert from one timezone to another
CONVERT() Cast a value as a certain type
COS() Return the cosine
COT() Return the cotangent
Return the count of a number of different
COUNT(DISTINCT) values
Return a count of the number of rows
COUNT() returned
CRC32() Compute a cyclic redundancy check value
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(),CURRENT_TIMES
TAMP Synonyms for NOW()
CURRENT_USER(), CURRENT_USER The authenticated user name and host name
CURTIME() Return the current time
 Name Description
DATABASE() Return the default (current) database name
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
Extract the date part of a date or datetime
DATE() expression
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
DECODE() Decodes a string encrypted using ENCODE()
DEFAULT() Return the default value for a table column
DEGREES() Convert radians to degrees
DES_DECRYPT() Decrypt a string
DES_ENCRYPT() Encrypt a string
DIV Integer division
/ Division operator
ELT() Return string at index number
ENCODE() Encode a string
ENCRYPT() Encrypt a string
<=> NULL-safe equal to operator
= Equal operator
EXP() Raise to the power of
Return a string such that for every bit set in
the value bits, you get an on string and for
EXPORT_SET() every unset bit, you get an off string
EXTRACT() Extract part of a date
Return the index (position) of the first
FIELD() argument in the subsequent arguments
Return the index position of the first
FIND_IN_SET() argument within the second argument
Return the largest integer value not greater
FLOOR() than the argument
Return a number formatted to specified
FORMAT() number of decimal places
For a SELECT with a LIMIT clause, the
number of rows that would be returned were
FOUND_ROWS() there no LIMIT clause
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format UNIX timestamp as a date
GET_FORMAT() Return a date format string
 Name Description
GET_LOCK() Get a named lock
>= Greater than or equal operator
> Greater than operator
GREATEST() Return the largest argument
GROUP_CONCAT() Return a concatenated string
Return a hexadecimal representation of a
HEX() decimal or string value
HOUR() Extract the hour
IF() If/else construct
IFNULL() Null if/else construct
Check whether a value is within a set of
IN() values
INET_ATON() Return the numeric value of an IP address
INET_NTOA() Return the IP address from a numeric value
Insert a substring at the specified position up
INSERT() to the specified number of characters
Return the index of the first occurrence of
INSTR() substring
Return the index of the argument that is less
INTERVAL() than the first argument
IS_FREE_LOCK() Checks whether the named lock is free
IS NOT NULL NOT NULL value test
IS NOT Test a value against a boolean
IS NULL NULL value test
Checks whether the named lock is in use.
IS_USED_LOCK() Return connection identifier if true.
IS Test a value against a boolean
ISNULL() Test whether the argument is NULL
Return the last day of the month for the
LAST_DAY argument
Value of the AUTOINCREMENT column for
LAST_INSERT_ID() the last INSERT
LCASE() Synonym for LOWER()
LEAST() Return the smallest argument
<< Left shift
Return the leftmost number of characters as
LEFT() specified
LENGTH() Return the length of a string in bytes
<= Less than or equal operator
< Less than operator
LIKE Simple pattern matching
LN() Return the natural logarithm of the argument
LOAD_FILE() Load the named file
 Name Description
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP,LOCALTIMESTAMP() Synonym for NOW()
Return the position of the first occurrence of
LOCATE() substring
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
Return the natural logarithm of the first
LOG() argument
LOWER() Return the argument in lowercase
Return the string argument, left-padded with
LPAD() the specified string
LTRIM() Remove leading spaces
Return a set of comma-separated strings that
MAKE_SET() have the corresponding bit in bits set
MAKEDATE() Create a date from the year and day of year
MAKETIME MAKETIME()
Block until the slave has read and applied all
MASTER_POS_WAIT() updates up to the specified position
MATCH Perform full-text search
MAX() Return the maximum value
MD5() Calculate MD5 checksum
MICROSECOND() Return the microseconds from argument
Return a substring starting from the specified
MID() position
MIN() Return the minimum value
- Minus operator
MINUTE() Return the minute from the argument
MOD() Return the remainder
% or MOD Modulo operator
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NAME_CONST() Causes the column to have the given name
Check whether a value is not within a range
NOT BETWEEN ... AND ... of values
!=, <> Not equal operator
Check whether a value is not within a set of
NOT IN() values
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
NOT, ! Negates value
NOW() Return the current date and time
NULLIF() Return NULL if expr1 = expr2
OCT() Return an octal representation of a decimal
 Name Description
number
OCTET_LENGTH() A synonym for LENGTH()
Return the value of the pre-4.1
OLD_PASSWORD() implementation of PASSWORD
||, OR Logical OR
Return character code for leftmost character
ORD() of the argument
PASSWORD() Calculate and return a password string
PERIOD_ADD() Add a period to a year-month
Return the number of months between
PERIOD_DIFF() periods
PI() Return the value of pi
+ Addition operator
POSITION() A synonym for LOCATE()
Return the argument raised to the specified
POW() power
Return the argument raised to the specified
POWER() power
PROCEDURE ANALYSE() Analyze the results of a query
QUARTER() Return the quarter from a date argument
Escape the argument for use in an SQL
QUOTE() statement
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
REGEXP Pattern matching using regular expressions
RELEASE_LOCK() Releases the named lock
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
>> Right shift
Return the specified rightmost number of
RIGHT() characters
RLIKE Synonym for REGEXP
ROUND() Round the argument
ROW_COUNT() The number of rows updated
RPAD() Append string the specified number of times
RTRIM() Remove trailing spaces
SCHEMA() A synonym for DATABASE()
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Return the second (0-59)
SESSION_USER() Synonym for USER()
SHA1(), SHA() Calculate an SHA-1 160-bit checksum
SIGN() Return the sign of the argument
 Name Description
SIN() Return the sine of the argument
SLEEP() Sleep for a number of seconds
SOUNDEX() Return a soundex string
SOUNDS LIKE Compare sounds
Return a string of the specified number of
SPACE() spaces
SQRT() Return the square root of the argument
STD() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
STDDEV() Return the population standard deviation
STR_TO_DATE() Convert a string to a date
STRCMP() Compare two strings
A synonym for DATE_SUB() when invoked
SUBDATE() with three arguments
SUBSTR() Return the substring as specified
Return a substring from a string before the
specified number of occurrences of the
SUBSTRING_INDEX() delimiter
SUBSTRING() Return the substring as specified
SUBTIME() Subtract times
SUM() Return the sum
Return the time at which the function
SYSDATE() executes
SYSTEM_USER() Synonym for USER()
TAN() Return the tangent of the argument
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
Extract the time portion of the expression
TIME() passed
TIMEDIFF() Subtract time
* Multiplication operator
With a single argument, this function returns
the date or datetime expression; with two
TIMESTAMP() arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
Subtract an interval from a datetime
TIMESTAMPDIFF() expression
TO_DAYS() Return the date argument converted to days
TRIM() Remove leading and trailing spaces
Truncate to specified number of decimal
TRUNCATE() places
UCASE() Synonym for UPPER()
 Name Description
- Change the sign of the argument
UNCOMPRESS() Uncompress a string compressed
Return the length of a string before
UNCOMPRESSED_LENGTH() compression
Convert each pair of hexadecimal digits to a
UNHEX() character
UNIX_TIMESTAMP() Return a UNIX timestamp
UPPER() Convert to uppercase
The user name and host name provided by
USER() the client
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
UUID() Return a Universal Unique Identifier (UUID)
Defines the values to be used during an
VALUES() INSERT
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance
Returns a string that indicates the MySQL
VERSION() server version
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (0-53)
XOR Logical XOR
YEAR() Return the year
YEARWEEK() Return the year and week

Backup the record in text file:

Select * into outfile ‘c:/test.txt’ from
stud_info;
“Or”
Select * into outfile ‘c:/test.txt’
lines terminated by ‘\n’ from stud_info;
“Or”
Select * into outfile ‘c:/test.txt’ from
stud_info where roll=101;
Restore the Data from text File into
Table:
Load Data Infile ‘c:/test.txt’ into
table stud_info;
Backup Data using mysqldump
command:
(i) First of all we have to open “Dos
Command Prompt”.
(ii) Set the path for “mysqldump.exe”
file as :
C:\>path=%path%;c:\program files\
mysql\mysql server 5.1\bin;
(iii) Now take the backup using
mysqldump command as :-
c:\>mysqldump –u root –p ranjit>”c:\mybackupfile.sql”
Note: not use ;
Restoring Data :
C:\>mysql –u root –p ranjit< “c:\mybackupfile.sql”
Note : Here “root” is current user,
“ranjit” is database name and “c:\
mybackupfile.sql” is the filename
where we want to save the backup file.
Note : If we want to execute the above function in php then we
have to write the code as :--
<? php
$rs_area = mysql_query(“select Area(22)”);
$area = mysql_result($rs_area,0,0);
echo “The area of the circle with radius 22cm is ”.$area.”
sq.cm”;
?>