OPEN SOURCE SOFTWARE UNIT-III

IV IT

Introduction to MySQL
The MySQL database system is a networked database system that uses a client-server
architecture that centers around the server , mysqld. The server is the program that actually
manipulates databases.
MySQL Client programs communicate to the server by means of statements written
in Structured Query Language (SQL).

Client programs are installed locally on the

machine from which we want to access MySQL, but the server can be installed anywhere, as
long as clients can connect to it.
One of the clients is the mysql program that is included in MySQL distributions.
When used interactively, mysql prompts us for a statement, sends it to the MySQL server for
execution, and then displays the results. mysql also can be used non interactively; for
example, to read statements from a file or from other programs. This enables us to use mysql
from within scripts or in conjunction with other applications.

Setting Up a MySQL User Account

Problem
We need to create an account to use for connecting to the MySQL server running on a
given host.
Solution
Use the GRANT statement to set up the MySQL user account. Then use that
account's name and password to make connections to the server.
For issuing the GRANT command, we first connect as the MySQL root user, because
GRANT can be used only by root user that has the administrative privileges needed to set up
other user accounts. Once that has been done, we should be able to use the new MySQL
account to connect to the server, create our own database, and proceed from there on our
own.
Explanation
Connecting to a MySQL server requires a username and password. We can also
specify the name of the host where the server is running. If we don't specify connection
parameters explicitly, mysql will take default values. For example, if we specify no hostname,
mysql typically assumes the server is running on the local host.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

The following example shows how to use the mysql program to connect to the server and
issue a GRANT statement that sets up a user account with privileges for accessing a database
named cookbook. The arguments to mysql include -h localhost to connect to the MySQL
server running on the local host, -p to tell mysql to prompt for a password, and -u root to
connect as the MySQL root user.

% mysql -h localhost -p -u root

Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY
'mysqlpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT
Bye

The hostname part of 'cbuser'@'localhost' indicates the host from which we'll be
connecting to the MySQL server to access the cookbook database. To set up an account that
will connect to a server running on the local host, we can use localhost, as the host name. If
we plan to make connections to the server from another host, substitute that host in the
GRANT statement.
For example, if we'll be connecting to the server as cbuser from a host named 127.0.0.1, the
GRANT statement should look like this:
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'127.0.0.1' IDENTIFIED BY
'mysqlpass';

Creating a Database and a Sample Table

Problem
We want to create a database and to set up tables within it.
Solution
We can use a CREATE DATABASE statement to create a database, a CREATE
TABLE statement for each table we want to create, and INSERT to add records to the tables.
Explanation
We need to create the database explicitly before we can use it. For creating the
database, first we have to connect to MySQL with some user name and password.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Once we've connected successfully, using CREATE DATABASE command, the database
can be created.
For example
% mysql -h localhost -p -u cbuser
Enter password: cbpass
mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.08 sec)

Now the cookbook database is created, so we can create tables in it. Issue the following
statements to select cookbook as the default database, create a simple table, and populate it
with a few records:
mysql> USE cookbook;
mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

We can verify that the table contains the inserted rows by issuing a SELECT statement:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Starting and terminating MySQL

Problem
We want to start and stop the mysql program.
Solution
Invoke mysql from the command prompt to start it, specifying any connection
parameters that may be necessary. To leave mysql, use a QUIT statement.
Explanation
To start the mysql program, try just typing its name at the command-line prompt. If
mysql starts up correctly, we'll see a short message, followed by a mysql> prompt that
indicates the program is ready to accept queries. For example
% mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18427 to server version: 3.23.51-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

If mysql tries to start but exits immediately with an "access denied" message, we'll need to
specify connection parameters. The most commonly needed parameters are the host to
connect to (the host where the MySQL server runs), our MySQL username, and a password.
For example:
% mysql -h localhost -p -u cbuser
Enter password: cbpass

The syntax and default values for the connection parameter options are shown in the
following table. These options have both a single-dash short form and a double-dash long
form.
Parameter type Option syntax forms Default value
Hostname -h hostname--host=hostname localhost
Username -u username--user=username Your login name
Password -p--password None
To terminate a mysql session, issue a QUIT statement:
mysql> QUIT
We can also terminate the session by issuing an EXIT statement or (under Unix) by typing
Ctrl-D.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Writing MySQL Based Programs

MySQL-based client programs can be written using several languages such as Perl ,
Ruby, PHP, Python, and Java. Every programming language provides an API for interacting
with database server.
MySQL client APIs provide the capabilities like
Connecting to the MySQL , selecting a database, and disconnecting from the server
Every program that uses MySQL must first establish a connection to the server, and
most programs also select a default database to use. In addition, well-behaved MySQL
programs close the connection to the server when theyre done with it.
Checking for errors
Any database operation can fail and we should detect those errors and we will take
appropriate action such as terminating the program or informing the user of the problem.
Executing SQL statements and retrieving results
The whole point of connecting to a database server is to execute SQL statements.
Each API provides several methods for processing statements results.
Handling special characters and NULL values in statements
One of the methods to write a SQL statement is to embed the data values directly in
the statement string. However, some characters such as quotes and backslashes have special
meaning, and we must take certain precautions when constructing statements containing
them. The same is true for NULL values. If we do not handle these properly, our programs
may generate SQL statements that are erroneous or that yield unexpected results.
Identifying NULL values in result sets
NULL values are special not only when we construct statements, but also in results
returned from statements. Each API provides a convention for recognizing and dealing with
them.

List of APIs provided by programming languages

Several programming languages provide their own APIs for manipulating the
databases located at database server. Some of the APIs are

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

MySQL Client API Architecture

All MySQL client programs that they connect to the server using some kind of
application programming interface (API) that implements a communications protocol.
MySQL APIs provide a standard way to express database operations. Each API translates
database related instructions into something the MySQL server can understand.
The server itself speaks a low-level raw protocol. This is the level at which direct
communication takes place over the network between the server and its clients. A client
establishes a connection to the port on which the server is listening and communicates with it
by sending SQL statements.
The raw protocol is a binary communication stream that is efficient, but not
particularly easy to use. More convenient access to the MySQL server is available through
API that is written at a level above that of the raw protocol. The interface handles and hides
the details of the raw protocol on behalf of our programs. It provides the methods for
operations such as connecting to the server, sending statements, retrieving the results of
statements, and obtaining statement status information.
The MySQL API is implemented as two-level architecture. The top level of this
architecture provides database-independent methods that implement database access in a
portable way. The lower level consists of a set of drivers, each of which implements the
details for a particular database system. The two-level architecture enables application
programs to use an abstract interface that is not tied to the details involved with accessing any
particular database server.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Connecting, Selecting a Database, and Disconnecting

Problem
We need to establish a connection to the server to access a database, and to shut down
the connection when were done.
Solution
Each API provides routines for connecting and disconnecting. The connection
routines require that we provide parameters specifying the hostname that is running the
MySQL server and the MySQL account with the database that we want to use.

Perl Script
To write MySQL scripts in Perl, we should have the DBI module installed, as well as
the MySQL-specific driver module, DBD::mysql. Here is a simple Perl script that connects to
the cookbook database and then disconnects:

#!/usr/bin/perl
# connect.pl - connect to the MySQL server
use strict;
use warnings;
use DBI;
my $dsn = "DBI:mysql:host=localhost;database=cookbook";
my $dbh = DBI->connect ($dsn, "cbuser", "cbpass")
or die "Cannot connect to server\n";
print "Connected\n";
$dbh->disconnect ();
print "Disconnected\n";
% connect.pl
Connected
Disconnected

Ruby
To write MySQL scripts in Ruby, we should have the DBI module installed, as well
as the MySQL-specific driver module. Both are included in the Ruby DBI distribution.
Here is a simple Ruby script that connects to the cookbook database and then disconnects:
#!/usr/bin/ruby
# connect.rb - connect to the MySQL server
require "dbi"
begin
7

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

dsn = "DBI:Mysql:host=localhost;database=cookbook"
dbh = DBI.connect(dsn, "cbuser", "cbpass")
puts "Connected"
rescue
puts "Cannot connect to server"
exit(1)
end
dbh.disconnect
puts "Disconnected"
% connect.rb
Connected
Disconnected
PHP
To write PHP scripts that use MySQL, the PHP interpreter must have MySQL
supporting feature. If it doesnt, our scripts will be unable to connect to our MySQL server.
PHP actually has two extensions that enable the use of MySQL. The first, mysql, is the
original MySQL extension. It provides a set of functions that have names beginning with
mysql_. The second, mysqli, or MySQL improved, provides functions with names that
begin with mysqli_.
<?php
$connect = mysql_connect("localhost", "root", "");
if(!$connect){
die("Cannot connect to MySQL server". mysql_error());
}else{
echo "Sucessfully connected to MySQL server";
}
$db = mysql_select_db("cookbook", $connect);
if(!$db){
die("Cannot select a database". mysql_error());
}else{
echo "Database is sucessfully selected";
} ?>
Python
To write MySQL programs in Python, we need the MySQLdb module that provides
MySQL connectivity.
#!/usr/bin/python
# connect.py - connect to the MySQL server
import sys
import MySQLdb
try:
8

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

conn = MySQLdb.connect (db = "cookbook",host = "localhost",user = "cbuser", passwd = "cbpass")

print "Connected"
except:
print "Cannot connect to server"
sys.exit (1)
conn.close ()
print "Disconnected"
% connect.py
Connected
Disconnected
Java
Database programs in Java are written using the JDBC interface, together with a
driver for the particular database engine we want to access. That is, the JDBC architecture
provides a generic interface used in conjunction with a database-specific driver.
To write MySQL-based Java programs, well also need a MySQL-specific JDBC driver.
// Connect.java - connect to the MySQL server
import java.sql.*;
public class Connect
{
public static void main (String[] args)
{
Connection conn = null;
String url = "jdbc:mysql://localhost/cookbook";
String userName = "cbuser";
String password = "cbpass";
try {
Class.forName ("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Connected");
}
catch (Exception e) {
System.err.println ("Cannot connect to server");
System.exit (1);
}
if (conn != null)
{
try
{
conn.close ();
System.out.println ("Disconnected");
}
catch (Exception e) { /* ignore close errors */ }
}
}
}
9

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Issuing SQL Statements and Retrieving Results

Problem
We want our program to send an SQL statement to the MySQL server and retrieve
whatever result it produces.
Solution
There are two general categories of SQL statements that we can execute. Some
statements retrieve information from the database; others make changes to that information.
These two types of statements are handled differently. Some APIs provide different methods
for issuing each type of statements. Other APIs (such as those for PHP and Python) have a
single call that can be used for any statement.

Statements that do not return a result set (that is, a set of rows). Statements in this

category include INSERT, DELETE, and UPDATE. As a general rule, statements of

this type generally change the database in some way. The example data-modifying
statement is UPDATE
UPDATE profile SET cats = cats+1 WHERE name = 'Fred'
Statements that return a result set, such as SELECT, SHOW, EXPLAIN, and

DESCRIBE. Example statement is

SELECT id, name, cats FROM profile
Perl
The Perl DBI module provides two basic approaches to SQL statement execution,
depending on whether we expect to get back a result set. To issue a statement such as
INSERT or UPDATE that returns no result set, use the database handle do( ) method. It
executes the statement and returns the number of rows affected by it, or undef if an error
occurs.
For example,
my $count = $dbh->do ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'");
if ($count) # print row count if no error occurred
{
print "Number of rows updated: $count\n";
}
The following example executes a SELECT statement and uses the fetch method to retrieve
the rows in a while loop:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");

$sth->execute ();
my $count = 0;
while (my @val = $sth->fetchrow_array ())
{
print "id: $val[0], name: $val[1], cats: $val[2]\n";
++$count;
}
$sth->finish ();
print "Number of rows returned: $count\n";
In addition to these methods for performing the statement execution process, DBI provides
several high-level retrieval methods that issue a statement and return the result set in a single
operation

Ruby
As with Perl DBI, Ruby DBI provides two approaches to SQL statement execution.
With either approach, if a statement-execution method fails with an error, it raises an
exception.
For statements such as INSERT or UPDATE that return no result set, invoke the do( )
database handle method. Its return value indicates the number of rows affected.
For SELECT statement, execute( ) method is provided for returning result set.
For example:

count = dbh.do("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'")

puts "Number of rows updated: #{count}"

The following example executes a SELECT statement and uses the fetch method to
retrieve the rows in a while loop:
count = 0
sth = dbh.execute("SELECT id, name, cats FROM profile")
while row = sth.fetch do
printf "id: %s, name: %s, cats: %s\n", row[0], row[1], row[2]
1

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

count += 1
end
sth.finish
puts "Number of rows returned: #{count}"
PHP
To get PHP to execute the SQL DML statement, we must use the mysql_query()
function. This function is used to send a query or command to a MySQL connection.
For example
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");
mysql_close($con);
?>
To get PHP to execute the DCL statement like SELECT, we must use the
mysql_query() function. This function is used to send a query or command to a MySQL
connection and will get resultset object. Using the resultset object we will retrieve the rows
from the table.
For example
<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$result = mysql_query("SELECT * FROM Persons");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}
mysql_close($con);
?>

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Python
The Python DB-API interface does not have distinct calls for SQL statements that
return a result set and those that do not. To process a statement in Python, use the database
connection object to get a cursor object. Then use the cursors execute( ) method to send the
statement to the server. If the statement fails with an error, execute( ) raises an exception.
Otherwise, if there is no result set, the statement is completed, and we can use the cursors
rowcount attribute to determine how many rows were changed.
For example
cursor = conn.cursor ()
cursor.execute ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'")
print "Number of rows updated: %d" % cursor.rowcount
If the statement does return a result set, fetch its rows, and then close the set. DB-API
provides a couple of methods for retrieving rows
For example
cursor = conn.cursor ()
cursor.execute ("SELECT id, name, cats FROM profile")
rows = cursor.fetchall ()
for row in rows:
print "id: %s, name: %s, cats: %s" % (row[0], row[1], row[2])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close ()
Java
To issue a statement, the first step is to get a Statement object by calling the
createStatement( ) method of our Connection object. Next using this object, we can send
SQL statements to MySQL server. JDBC provides several methods for doing this.
executeUpdate( ) for statements that dont return a result set, executeQuery( ) for statements
that do, and execute( ) when we dont know. Each method raises an exception if the
statement fails with an error.
Example for DML Statement
Statement s = conn.createStatement ();
int count = s.executeUpdate ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'");
s.close (); // close statement
System.out.println ("Number of rows updated: " + count);

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Example for Select statement

Statement s = conn.createStatement ();
s.executeQuery ("SELECT id, name, cats FROM profile");
ResultSet rs = s.getResultSet ();
int count = 0;
while (rs.next ()) // loop through rows of result set
{
int id = rs.getInt (1); // extract columns 1, 2, and 3
String name = rs.getString (2);
int cats = rs.getInt (3);
System.out.println ("id: " + id
+ ", name: " + name
+ ", cats: " + cats);
++count;
}
rs.close (); // close result set
s.close (); // close statement
System.out.println ("Number of rows returned: " + count);
Handling NULL Values in SQL Statements
Problem
We need to construct SQL statements that refer to data values containing special
characters such as quotes or backslashes, or special values such as NULL. Or we are
constructing statements using data obtained from external sources and want to avoid being
subject to SQL injection attacks.
Solution
Use our APIs placeholder mechanism or quoting function to make data safe for
insertion.

Using placeholders
Placeholders enable us to avoid writing data values literally into SQL statements.
Using this approach, we write the statement using placeholders which is a special characters
that indicate where the values go. One common placeholder character is ?.
One of the benefits of using placeholders is that parameter binding operations
automatically handle escaping of characters such as quotes and backslashes.
A second benefit of placeholders is that we can prepare a statement in advance
and then reuse it by binding different values to it each time its executed.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

PERL
To use placeholders in Perl DBI scripts, put a ? in our SQL statement string at each
location where we want to insert a data value, and then bind the values to the statement.
We can bind values by passing them to do( ) or execute( ), or by calling a DBI method
specifically intended for placeholder substitution.
For example
my $sth = $dbh->prepare ("INSERT INTO profile VALUES(?,?,?,?,?)");
my $count = $sth->execute ("De'Mont", "1973-01-12", undef, "eggroll", 4);

RUBY
Ruby DBI uses ? as the placeholder character in SQL statements and nil as the value
to use for binding an SQL NULL value to a placeholder. First we pass the statement string to
prepare to get a statement handle, and then use that handle to invoke execute with the data
values:
sth = dbh.prepare("INSERT INTO profile VALUES(?,?,?,?,?)")
count = sth.execute("De'Mont", "1973-01-12", nil, "eggroll", 4)

PHP
The PEAR DB module allows placeholders to be used with the query( ) method that
executes SQL statements, or we can use prepare( ) to prepare a statement, and execute( ) to
supply the data values and execute the prepared statement. PEAR DB uses ? as the
placeholder marker in SQL statements and the PHP NULL as the value to use when binding
an SQL NULL value to a placeholder.
For example
$stmt =& $conn->prepare ("INSERT INTO profile VALUES(?,?,?,?,?)");
if (PEAR::isError ($stmt))
die ("Oops, statement preparation failed");
$result =& $conn->execute ($stmt, array ("De'Mont","1973-01-12",NULL,"eggroll",4));
if (PEAR::isError ($result))
die ("Oops, statement execution failed");
Python
Pythons MySQLdb module implements placeholders using format specifiers in the
SQL statement string. To use placeholders, invoke the execute( ) method with two

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

arguments: a statement string containing format specifiers and a sequence containing the
values to bind to the statement string.

cursor = conn.cursor ()
cursor.execute ("INSERT INTO profile VALUES(%s,%s,%s,%s,%s)", ("De'Mont", "197301-12", None, "eggroll", 4))

Java
JDBC provides support for placeholders if we use prepared statements. To use a
prepared statement, create a PreparedStatement object by passing a statement string
containing ? place holder characters to our connection objects prepareStatement( ) method.
Then bind our data values to the statement using setXXX ( ) methods. Finally, execute the
statement by calling executeUpdate( ), executeQuery( ), or execute( ) with an empty
argument list.
Here is an example that uses executeUpdate( ) to issue an INSERT statement

PreparedStatement s;
int count;
s = conn.prepareStatement ("INSERT INTO profile VALUES(?,?,?,?,?)");
s.setString (1, "De'Mont"); // bind values to placeholders
s.setString (2, "1973-01-12");
s.setNull (3, java.sql.Types.CHAR);
s.setString (4, "eggroll");
s.setInt (5, 4);
count = s.executeUpdate ();
s.close (); // close statement

Identifying NULL Values in Result Sets

Problem
A query result includes NULL values, but were not sure how to tell where they are.
Solution
The database API probably has some special value that represents NULL by
convention. we just have to know what it is and how to test for it.
Every programming language maps database specific NULL value to the appropriate value in
the program. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

Perl
Perl DBI represents NULL values using undef. Its easy to detect such values using
the defined( ) function. For example

my $sth = $dbh->prepare ("SELECT name, birth, foods FROM profile");

$sth->execute ();
while (my $ref = $sth->fetchrow_hashref ())
{
printf "name: %s, birth: %s, foods: %s\n",
defined ($ref->{name}) ? $ref->{name} : "NULL",
defined ($ref->{birth}) ? $ref->{birth} : "NULL",
defined ($ref->{foods}) ? $ref->{foods} : "NULL";
}
Ruby
Ruby DBI represents NULL values using nil, which can be identified by applying the
nil? method to a value. The following example uses nil? to determine whether to print
result set values as is or as the string "NULL" for NULL values:

sth = dbh.execute("SELECT id, name, cats FROM profile")

while row = sth.fetch do
for i in 0...row.length
row[i] = "NULL" if row[i].nil? # is the column value NULL?
end
printf "id: %s, name: %s, cats: %s\n", row[0], row[1], row[2]
end
end
PHP
PHP represents SQL NULL values in result sets as the PHP NULL value. To
determine whether a value from a result set represents a NULL value, compare it to the PHP
NULL value using the = = = triple equal operator:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT-III

IV IT

$result =& $conn->query ("SELECT name, birth, foods FROM profile");

if (PEAR::isError ($result))
die ("Oops, the statement failed");
while ($row =& $result->fetchRow ())
{
foreach ($row as $key => $value)
{
if ($row[$key] === NULL)
$row[$key] = "NULL";
}
print ("name: $row[0], birth: $row[1], foods: $row[2]\n");
}
$result->free ();
Python
Python DB-API programs represent NULL in result sets using None. The following
example shows how to detect NULL values:
cursor = conn.cursor ()
cursor.execute ("SELECT name, birth, foods FROM profile")
for row in cursor.fetchall ():
for i in range (0, len (row)):
if row[i] == None: # is the column value NULL?
row[i] = "NULL"
print "name: %s, birth: %s, foods: %s" % (row[0], row[1], row[2])
cursor.close ()
Java
For JDBC programs, The way to detect the NULL value is to fetch the value and then
invoke wasNull( ), which returns true if the column is NULL and false otherwise. For
example:
Statement s = conn.createStatement ();
s.executeQuery ("SELECT name, birth, foods FROM profile");
ResultSet rs = s.getResultSet ();
ResultSetMetaData md = rs.getMetaData ();
int ncols = md.getColumnCount ();
while (rs.next ()) // loop through rows of result set {
for (int i = 0; i < ncols; i++) // loop through columns {
String val = rs.getString (i+1);
if (rs.wasNull ())
System.out.print ("NULL");
Else System.out.print (val);
}
System.out.println ();
}
rs.close (); // close result set
s.close (); // close statement
1

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Record Selection Techniques

The records can be selected from the MySQL table using SQL SELECT statement.
There are so many ways to write SELECT statement. The general syntax of the SQL SELECT
statement is
SELECT

[ALL | DISTINCT]

select_expr [, select_expr ...]

[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr }
[HAVING where_condition]
[ORDER BY {col_name | expr }[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Specifying Which Columns to Select

To indicate what kind of information we want to select from a table, name a column or a
list of columns and the table to use. The easiest way to display columns from a table is to use
SELECT * FROM tbl_name. The * specifier is a shortcut for naming all the columns in a table:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Specifying Which Rows to Select

Unless we restrict a SELECT query in some way, it retrieves every row in the table,
which is a lot more than we really want to see. To be more precise about which rows to select,
provide a WHERE clause that specifies one or more conditions that rows must match.
Conditions can perform tests for equality, inequality, or relative ordering. For some types
of data, such as strings, you can use pattern matches. The following statements select columns
from rows from the mail table containing srchost values that are exactly equal to the string
'venus' or that begin with the letter 's':

A WHERE clause can test multiple conditions and different conditions can test different
columns. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Giving Better Names to Query Result Columns Column Aliases

When we retrieve a result set, MySQL gives every output column a name. If an output
column in a result set comes directly from a table, MySQL uses the table column name for the
output column name. For example

If we generate a column by evaluating an expression, the expression itself is the column

name. This can produce rather long and unwieldy names in result sets.

To avoid this problem, we can give an output column a name of our own choosing, use
an AS name clause to specify a column alias. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

We cannot refer to column aliases in a WHERE clause. Thus, the following statement is
Illegal.
mysql> SELECT t, srcuser, dstuser, size/1024 AS kilobytes FROM mail WHERE kilobytes > 500;

ERROR 1054 (42S22): Unknown column 'kilobytes' in 'where clause'

To make the statement legal, replace the alias in the WHERE clause with the column or
expression that the alias represents:
mysql> SELECT t, srcuser, dstuser, size/1024 AS kilobytes FROM mail WHERE size/1024 > 500;

Combining Columns to Construct Composite Values

Column values can be combined to produce composite output values. For this to do, we
have to use concat( ) function in the queries. For example

Removing Duplicate Rows

Some queries produce results containing duplicate rows. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

That result is heavily redundant. Adding DISTINCT to the query removes the duplicate
rows, producing a set of unique values. For example

DISTINCT works with multiple-column output too. For example

Working with NULL Values

Conditions that involve NULL are special. We cannot use comparisons of the form value
= NULL or value != NULL to check whether value is NULL. Such comparisons always produce
a result of NULL because its impossible to tell whether they are true or false. To look for
columns that are or are not NULL, use the IS NULL or IS NOT NULL operator. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Sometimes its useful to map NULL values onto some other distinctive value that has
more meaning in the context of our application. For example If NULL id values in the taxpayer
table mean unknown, we can display that fact by using IF( ) to map NULL onto the string
Unknown.

Sorting a Result Set

When we select rows, the MySQL server is free to return them in any order, unless we
instruct it how to sort the result. There are lots of ways to use sorting techniques. We can sort a
result set by adding an ORDER BY clause that names the column or columns that want to use
for sorting. For example
6

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

This statement names multiple columns in the ORDER BY clause to sort rows by host,
and then by user within each host:

To sort a column in reverse (descending) order, add the keyword DESC after its name in
the ORDER BY clause for example

Using Views to Simplify Table Access

A view is a virtual table that does not contain any data itself. Instead, its defined as the
SELECT statement that retrieves the data of interest.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Selecting Data from More Than One Table

Sometimes we need to retrieve information from multiple tables. Two types of queries
that accomplish this are joins and subqueries.
A join matches rows in one table with rows in another and enables us to retrieve output
rows that contain columns from either or both tables.
A subquery is one query nested within the other. The result is a query that performs a
comparison between values selected by the inner query against values selected by the outer
query. For example

Selecting Rows from the Beginning or End of a Result Set

MySQL supports a LIMIT clause that tells the server to return only part of a result set.
LIMIT is a MySQL-specific extension to SQL that is extremely valuable when our result set
contains more rows than we want to see at a time. It enables us to retrieve just the first part of a
result set or an arbitrary section of the set. Typically, LIMIT is used for the following kinds of
problems:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Answering questions about first or last, largest or smallest, newest or oldest, least or more

expensive, and so forth.

Splitting a result set into sections so that we can process it one piece at a time. This

technique is common in web applications for displaying a large search result across
several pages.

To select the first n rows of a query result, add LIMIT n to the end of your SELECT statement:

A more common technique is to use ORDER BY to sort the result set. Then we can use LIMIT
to find smallest and largest values. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Selecting Rows from the Middle of a Result Set

LIMIT n tells the server to return the first n rows of a result set. LIMIT also has a two
argument form that enables us to pick out any arbitrary section of rows from a result. The
arguments indicate how many rows to skip and how many to return. This means that we can use
LIMIT to do such things as skip two rows and return the next, thus answering questions such as
what is the third-smallest or third-largest value? These are questions that MIN( ) or MAX( )
are not suited for, but are easy with LIMIT.

The two-argument form of LIMIT also makes it possible to partition a result set into smaller
sections. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Working with Strings

String is a data type which represents a collection of characters. Like other data types,
strings can be compared for equality or inequality or relative ordering. Strings have some
additional properties to consider:

Strings can be case sensitive, which can affect the outcome of string operations.

We can compare entire strings or just parts of them by extracting substrings.

We can apply pattern-matching operations to look for strings that have a certain structure.

Types of Strings
MySQL can operate on regular strings or binary strings. "Binary" in this context has little
to do with the presence of non-ASCII values. There are two types of Binary Strings.
Binary data may contain bytes that lie outside the usual range of printable ASCII

characters.
A binary string in MySQL is one that MySQL treats as case sensitive in comparisons.

For binary strings, the characters A and a are considered different. For non-binary strings,
they're considered the same.

A binary column type is one that contains binary strings. Some of MySQL's column types are
binary (case sensitive) and others are not.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Writing Strings That Include Quotes or Special Characters

Generally, the strings are represented in single quotes. But sometimes we need to write a
string that includes a quote character. We can enclose a string containing single quotes within
double Quotes. This works in reverse, too; a string containing double quotes can be enclosed
within single quotes.
For example

Preserving Trailing Spaces in String Columns

If we store a string that contains trailing spaces into the database, we may find that they're
gone when we retrieve the value. This is the normal MySQL behavior for CHAR and
VARCHAR columns; the server returns values from both types of columns without trailing
spaces. If we want to preserve trailing spaces, use one of the TEXT or BLOB column types. The
following example illustrates the difference in behavior for VARCHAR and TEXT columns.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Testing String Equality or Relative Ordering

Strings are subject to the usual equality and inequality comparisons. We can also use
relational operators such as <, <=, >=, and > to test strings for lexical ordering. We can also use
the BETWEEN operator for inclusive-range testing.
For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Decomposing or Combining Strings

We can break apart a string to extract a substring and we can also combine strings to
form a larger string. Parts of strings can be extracted using LEFT( ), MID( ), and RIGHT() to
extract substrings from the left, middle, or right part of a string. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

For LEFT( ) and RIGHT( ), the second argument indicates how many characters to return
from the left or right end of the string. For MID( ), the second argument is the starting position of
the substring and the third argument indicates how many characters to return.
The SUBSTRING( ) function takes a string and a starting position, returning everything
to the right of the position.

To combine strings we can use the CONCAT( ) function. It concatenates all its arguments and
returns the result

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Checking Whether a String Contains a Substring

We can know whether a given string occurs within another string by using locate( )
function.
The LOCATE( ) function takes two arguments representing the substring that we're
looking for and the string in which to look for it. The return value is the position at which the
substring occurs or 0 if it's not present. An optional third argument may be given to indicate the
position within the string at which to start looking.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Pattern Matching with SQL Patterns

SQL pattern matching uses the LIKE and NOT LIKE operators rather than = and != to
perform matching against a pattern string. Patterns may contain two special meta characters.

_ matches any single character

% matches any sequence of characters, including the empty string.

We can use these characters to create patterns that match a wide variety of values.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Pattern Matching with Regular Expressions

We can use REGEXP operator and a regular expression pattern to perform a pattern
match rather than a literal comparison. MySQL supports pattern matching operation based on
regular expressions and the REGEXP operator. REGEXP operator matching uses a different set
of pattern elements than % and _ .

For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Controlling Case Sensitivity in String Comparisons

String comparisons in MySQL are not case sensitive by default. For example

String comparisons are case sensitive only if at least one of the operands is a binary
string. To control case sensitivity in string comparisons, use the following techniques:
To make a string comparison case sensitive that normally would not be, cast (convert)

one of the strings to binary form by using the BINARY keyword.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

To make a string comparison not case sensitive that normally would be, convert both

strings to the same letter case using UPPER( ) or LOWER( ).

Using FULLTEXT Searches

Full-text searching is somewhat similar to a LIKE condition, but is much faster, requiring
a FULLTEXT index to be created for the table columns targeted in the search. After populating
the table, prepare it for use in FULLTEXT searching by adding a FULLTEXT index. This can be
done using an ALTER TABLE statement

ALTER TABLE entries ADD FULLTEXT(title)

To perform a search using the index, use MATCH( ) to name the indexed column and
AGAINST( ) to specify what text to look for. For example
SELECT id, MATCH(title) AGAINST ('python threading') AS score FROM entries

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Working with Date and Time

MySQL has several data types for representing dates and times, and several functions
for operating on them. MySQL stores dates and times in specific formats. MySQL also has
reformatting functions for producing date and time output in formats other than the default.
MySQL provides various different functions to manipulate the Date and time. These
functions are used for performing the following operations on Date and time.

Displaying dates and times.

Determining the current date or time.

Decomposing dates or times into component values.

Synthesizing dates and times from component values.

Converting between dates or times and basic units.

Date and time arithmetic.

Applications for date and time arithmetic.

Selecting records based on temporal constraints.

Using TIMESTAMP values.

MySQL's Date and Time Formats

MySQL provides DATE and TIME column types for representing date and time
values separately, and a DATETIME type for combined date-and-time values. These values
have the following formats:
DATE values are handled as in CCYY-MM-DD format, where CC, YY, MM, and DD

represent the century, year within century, month, and day parts of the date.
TIME values are represented as in hh:mm:ss format, where hh, mm, and ss are the

hours, minutes, and seconds parts of the time.

DATETIME values are represented as combined date-and-time in CCYY-MM-DD

hh:mm:ss format.
TIMESTAMP values also include date and time parts, but are represented as strings in

CCYYMMDDhhmmss format.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Telling MySQL How to Display Dates or Times

MySQL displays dates in ISO format unless you tell it otherwise. To rewrite date
values into other formats, use the DATE_FORMAT( ) function, which takes two arguments:
a DATE, DATETIME, or TIMESTAMP value, and a string describing how to display
the value. Within the formatting string, we indicate what to display using special sequences
of the form %c, where c specifies which part of the date to display. For example

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

Some of the more common list of format sequences are

For example

DEPARTMENT OF INFORMATION TECHNOLGY

IV IT

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Determining the Current Date or Time

This kind of information is also useful for date calculations that are performed in
relation to the current date, such as finding the first (or last) day of the month, or determining
the date for Wednesday of next week. The current date and time are available through three
functions.

NOW( ) returns both the current date and time.

CURDATE( ) and CURTIME( ) return the date and time separately.

CURRENT_TIMESTAMP

and

SYSDATE(

are

synonyms

for

NOW(

).

CURRENT_DATE() and CURRENT_TIME are synonyms for CURDATE( ) and

CURTIME( ).

Decomposing Dates and Times Using Formatting Functions

MySQL provides several options for decomposing dates or times to obtain their
component values. The DATE_FORMAT( ) and TIME_FORMAT( ) functions provide
one way to extract individual parts of temporal values.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Decomposing Dates or Times Using Component-Extraction Functions

We have the functions specifically intended for extracting part of a temporal value,
such as MONTH( ) or MINUTE( ). For obtaining single components of temporal values,
these functions are faster than using DATE_FORMAT( ) for the equivalent operation.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Another way to obtain individual parts of temporal values is to use the EXTRACT( )
function. The keyword indicating what to extract should be a unit specifier such as YEAR,
MONTH, DAY, HOUR, MINUTE, or SECOND.

Synthesizing Dates or Times Using Formatting Functions

Date synthesis often is performed by beginning with a given date, then keeping parts
that we want to use and replacing the rest. For example, to find the first day of the month in
which a date falls, use DATE_FORMAT( ) to extract the year and month parts from the date
and combine them with a day value of 01. TIME_FORMAT( ) can be used in a similar way.

Converting Between Times and Seconds

TIME_TO_SEC( ) converts a TIME value to the equivalent number of seconds, and
SEC_TO_TIME( ) does the opposite. The following query demonstrates a simple
conversion in both directions.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Converting Between Dates and Days

TO_DAYS( ) converts a date to the corresponding number of days, and
FROM_DAYS() does the opposite.

Adding a Temporal Interval to a Time

The primary tools for performing time arithmetic are TIME_TO_SEC( ) and
SEC_TO_TIME( ), which convert between TIME values and seconds. To add an interval
value in seconds to a TIME value, convert the TIME to seconds so that both values are
represented in the same units, add the values together, and convert the result back to a TIME.
For example, two hours is 7200 seconds (2*60*60), so the following query adds two hours to
each t1 value in the
time_val table.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Adding a Temporal Interval to a Date

MySQL provides special functions DATE_ADD( ) and DATE_SUB( ) for adding or
subtracting intervals to or from dates. Each function takes a date value d and an interval. The
syntax for each functions are

DATE_ADD(d,INTERVAL val unit)

DATE_SUB(d,INTERVAL val unit)

Here, unit is the interval unit and val is an expression indicating the number of units. Some of
the common unit specifiers are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
For example

Calculating Intervals between Dates

To calculate an interval in days between date or date-and-time values, convert them to
days using TO_DAYS( ), then take the difference.

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT -III

IV IT

Finding the Day of the Week for a Date

To determine the name of the day of the week for a given date, use DAYNAME( ).

Performing Leap Year Calculations

To determine whether or not a date d falls within a leap year, obtain the year
component using YEAR( ) and test the result. The common rule-of-thumb test for leap years
is "divisible by four,".

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Sorting Query Results

Sorting is performed by adding an ORDER BY clause to a SELECT query. Without
such a clause, MySQL is free to return rows in any order, so sorting helps to bring the rows
in order and make query results easier to examine and understand.

Using ORDER BY to Sort Query Results

When we select records, they're pulled out of the database and returned in whatever
order the server happens to use. Even if the rows are stored in the proper order naturally, a
relational database makes no guarantee about the order in which it returns rows. To arrange
the rows from a query result into a specific order, we can sort them by adding an ORDER BY
clause to the SELECT statement.

ORDER BY has the following general characteristics:

We can sort using a single column of values or multiple columns
We can sort any column in either ascending order (the default) or descending order
We can refer to sort columns by name, by their position within the output column list,
or by using an alias.

Syntax for ORDER BY clause in SELECT statement

ORDER BY { column-Name | ColumnPosition | Expression } [ ASC | DESC ]

column-Name
It refers to the names visible from the SelectItems in the underlying query of the
SELECT statement. The column-Name that we specify in the ORDER BY clause
does not need to be the SELECT list.
ColumnPosition
An integer that identifies the number of the column in the SelectItems in the
underlying query of the SELECT statement. If we want to order by a column position,
that column must be specified in the SELECT list.
Expression
A sort key expression, such as numeric, string, and datetime expressions. Expression
can also be a row value expression such as a scalar sub query or case expression.
1

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

ASC
It specifies that the results should be returned in ascending order. If the order is not
specified, ASC is the default.
DESC
It specifies that the results should be returned in descending order.

For example

The opposite (or reverse) of ascending order is descending order, specified by adding
DESC after the sorted column's name.

To more fully control output order, we can specify a multiple-column sort by listing
each column to use for sorting, separated by commas. The following query sorts in ascending
order by name and by trav_date within the rows for each name
2

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

We can also name the columns by their positions within the output column list or by
using aliases. Positions within the output list begin with 1. The following query sorts results
by the third output column.

If an output column has an alias, we can refer to the alias in the ORDER BY clause

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

The ORDER BY clause also allows us to sort the result set based on an expression.
We have to put the expression that calculates the values in the ORDER BY clause. For
example

Sorting and NULL Values

When we want to sort a column that may contain NULL values, All NULL values
appear at the top of the list, if ORDER BY col ASC is used; they appear at the bottom with
DESC. Despite these differences, if we want NULL values at one end or the other of the sort
order, we can force them to be placed where we want, no matter which version of ORDER
BY we're using.

For example
4

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

DEPARTMENT OF INFORMATION TECHNOLOGY

IV IT

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Sorting in User-Defined Orders

If we want to impose a specific order on all values in a column, use the FIELD( )
function to map them to a list of numeric values and use the numbers for sorting. FIELD( )
compares its first argument to the following arguments and returns a number indicating
which one of them it matches. The following FIELD( ) call compares value to str1, str2, str3,
and str4, and returns 1, 2, 3, or 4, depending on which one of them value is equal to

FIELD(value,str1,str2,str3,str4)

FIELD( ) cal also takes a variable-length argument list. If value is NULL or none of the
values match, FIELD( ) returns 0.

We can use FIELD( ) with column substrings, too. For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Generating Summaries
Database systems are useful for storing and retrieving records, but they also can
generate the summarized information for the data in more concise form. Summaries are
useful when we want the overall picture rather than the details. They're also typically more
readily understood than a long list of records.
The type of summaries we can perform may depend on the kind of data we're working
with. A counting summary can be generated from any kind of values, whether they are
numbers, strings, or dates. For summaries that involve sums or averages, only numeric values
can be used.
Summary operations in MySQL involve the following SQL constructs:
To compute a summary value from a set of individual values, use one of the functions
known as aggregate functions. These are so called because they operate on
aggregates (groups) of values. Aggregate functions include COUNT( ), which counts
records or values in a query result; MIN( ) and MAX( ), which find smallest and
largest values; and SUM( ) and AVG( ), which produce sums and means of values.
These functions can be used to compute a value for the entire result set, or with a
GROUP BY clause to group the rows into subsets and obtain an aggregate value for
each one.
To obtain a list of unique values, use SELECT DISTINCT rather than SELECT.
To count how many distinct values there are, use COUNT(DISTINCT) rather than
COUNT( ).

Summarizing with COUNT( )

MySQL COUNT() function returns a count of number of non-NULL values of a
given expression. If it does not find any matching row, it returns 0.
Syntax
COUNT( [DISTINCT] expr / *);
The COUNT( ) function actually has three forms.
COUNT(*) which will count the rows including NULL values.
The other form, COUNT(expr), takes a column name or expression argument and
counts the number of non-NULL values.
The COUNT(DISTINCT column_name) function returns the number of distinct
values of the specified column
1

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

mysql>SELECT COUNT(*) FROM employee_tbl ;

+----------+
| COUNT(*) |
+----------+
|

13 |

+----------+
1 row in set (0.01 sec)

mysql>SELECT COUNT(commision) count FROM employee_tbl;

+----------+
| COUNT

+----------+
|

5|

+----------+
mysql>SELECT COUNT(DISTINCT DEPTNO) DEPTS FROM employee_tbl;
+----------+
| DEPTS

+----------+
|

4|

+----------+

Summarizing with MIN( ) and MAX( )

MySQL MAX function is used to find out the record with maximum value among a
record set. We can use MIN Function along with MAX function to find out minimum value
as well.
For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

Summarizing with SUM( ) and AVG( )

SUM( ) and AVG( ) produce the total and average (mean) of a set of values.

DEPARTMENT OF INFORMATION TECHNOLOGY

IV IT

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Using DISTINCT to Eliminate Duplicates

A summary operation that doesn't use aggregate functions is to determine which
values or rows are contained in a dataset by eliminating duplicates. It can do with SELECT
DISTINCT.
For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Dividing a Summary into Subgroups

Sometimes it's desirable to break a set of rows into subgroups and summarize each
group. This is done by using aggregate functions in conjunction with a GROUP BY clause.
The MySQL GROUP BY clause is used with the SELECT statement to group rows into
subgroups by the one or more values of columns or expressions. It must appear after the
FROM or WHERE clause. The MySQL GROUP BY clause consists of the GROUP BY
keyword followed by a list of comma-separated columns or expressions.
Syntax
SELECT c1,c2,... cn, aggregate_function(expression)
FROM table
WHERE where_conditions
GROUP BY c1, c2, ... cn

For example

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Selecting Only Groups with Certain Characteristics

The MySQL HAVING clause is used in the SELECT statement to specify filter
conditions for group of rows or aggregates. The MySQL HAVING clause is often used with
the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter
condition to the columns that appear in the GROUP BY clause.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE DATABASE UNIT - III

IV IT

Obtaining and Using MySQL Metadata

There are three kinds of metadata information available from MySQL
Information about the result of queries: This includes number of records affected by any

SELECT, UPDATE or DELETE statement.

Information about tables and databases: This includes information pertaining to the

structure of tables and databases.

Information about the MySQL server: This includes current status of database server,

version number etc.

It is very easy to get all these information from MySQL prompt. But while using
programming APIs then we need to call various APIs methods to obtain all these information.

Obtaining the Number of Rows Affected by a Query

For queries that affect rows (UPDATE, DELETE, INSERT, REPLACE), each API provides a
way to determine the number of rows involved.
PHP Example:
In PHP, we will invoke the mysql_affected_rows( ) function to find out how many rows a
query changed.

$result_id = mysql_query ($query, $conn_id);

$count = ($result_id ? mysql_affected_rows ($conn_id): 0);
print ("$count rows were affected\n");
Java
The Java JDBC interface provides row counts two different ways, depending on the method
we invoke to execute the query. If you use executeUpdate( ), it returns the row count directly.
For example

If we use execute ( ), that method returns true or false to indicate whether or not the statement
produces a result set.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE DATABASE UNIT - III

IV IT

Obtaining Result Set Metadata

For queries that generate a result set, we can get a number of kinds of metadata. For example
in java, JDBC makes result set metadata available through a ResultSetMetaData object, which we
obtain by calling the getMetaData( ) method of the ResultSet object. The metadata object provides
access to several kinds of information.
For example

Listing Tables and Databases

This is very easy to list down all the databases and tables available with database server.
To obtain a list of tables in the current database, we can use the following query.
Syntax:
SHOW TABLES;
However, if no database has been selected, the query will fail. To avoid this problem, we
should either make sure there is a current database or name a database explicitly:

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE DATABASE UNIT - III

IV IT

SHOW TABLES FROM db_name;

Another form of SHOW returns a list of databases hosted by the server.
SHOW DATABASES;
Several programming APIs also provides the methods to obtain the list of tables in the
databases.
PERL Example:
# Get all the tables available in current database.
my @tables = $dbh->tables ( );
print "Table Name $table\n";
}
foreach $table (@tables ){
print "Table Name $table\n";
}

PHP Example for showing the list of databases:

<?php
$con = mysql_connect("localhost",
"userid","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$db_list = mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
{
echo $db->Database . "<br />";
}
mysql_close($con); ?>
Getting Servers Metadata
There are following commands in MySQL which can be executed either at mysql prompt or
using any script like PHP to get various important information about database server.

DEPARTMENT OF INFORMATION TECHNOLOGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

USING MYSQL SEQUENCES

A sequence is a set of integers 1, 2, 3, ... that are generated in order on demand.
Sequences are frequently used in databases because many applications require each row in a
table to contain a unique value, and sequences provide an easy way to generate them.

Using AUTO_INCREMENT column

The simplest way in MySQL to use Sequences is to define a column as
AUTO_INCREMENT and leave rest of the things to MySQL to take care.
Example:
The following example will create table and after that it will insert few rows in this table
where it is not required to give record ID because its auto incremented by MySQL.

Obtain AUTO_INCREMENT Values

LAST_INSERT_ID( ) is a SQL function, so we can use it from within any client that
understands how to issue SQL statements. Otherwise PERL and PHP scripts provide exclusive
functions to retrieve auto incremented value of last record.
PERL Example:

DEPARTMENT OF INFORMATION TECHNOLGY

OPEN SOURCE SOFTWARE UNIT - III

IV IT

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a

query. This attribute is accessed through either a database handle or a statement handle, depending
on how we issue the query. The following example references it through the database handle:

PHP Example:
After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling
mysql_insert_id( ).

Renumbering an Existing Sequence

There may be a case when we have deleted many records from a table and we want to re
sequence all the records. This can be done by using a simple trick but we should be very careful to do
so if our table is having join with other table.
The following example shows how to renumber the id values in the insect table using this
technique:

Starting a Sequence at a Particular Value

By default MySQL will start sequence from 1 but we can specify any other number as
well at the time of table creation. Following is the example where MySQL will start sequence
from 100.

DEPARTMENT OF INFORMATION TECHNOLGY