Unit- 05

Database
Operation

256
MySQL Introduction:-
• MySQL is an open-source • MySQL is one of the best RDBMS
relational database management being used for developing various
web based software application.
system (RDBMS). It is the most • MySQL is main component of web
popular database system used application like XAMPP, LAMP and
with PHP. MySQL is developed, WAMP.
distributed, and supported by • MySQL is used by many popular
Oracle Corporation. websites which include Facebook,
Flickr, Mediawiki, Twitter and
• MySQL is named after co-founder YouTube
Monty Widenius's daughter: My • It is designed to allow simple
• SQL the abbreviation for request from a database via
command.
Structured Query Language.

257
MySQL Introduction:-
• MySQL database stores data into • +----+------------+-----------+----------------------+
tables like other relational | id | first_name | last_name | email|
database. A table is a collection of +----+------------+-----------+----------------------+
| 1 | Ram | Maharaj | [email protected] |
related data, and it is divided into | 2 | Laxma | Maharaj | [email protected]
rows and columns
• The data in a MySQL database are • Each row in a table represents a
stored in tables which consists of data record that are inherently
columns and rows. connected to each other such as
information related to a particular
• MySQL operate using client/server person, whereas each column
architecture in which the server represents a specific field such as
run on the machine the database id, first_name, last_name, email,
and client connect to the server etc
over network.
258
Feature of MySQL :-
• Easy to use:- • No License Fee:-
• Free to download:- • Security:-
• Ease of Management:- • High Availability:-
• Client/Server Architecture:- • Scalability:-
• Compatible on many OS:- • High Flexibility:-
• Robust Transaction support:- • High Productivity:-
• Compressive application • Speed:-
Development:-
• High Performance:-

259
Data type in MySQL :-
• Data type is specifies a • We can determine the data type in
MySQL with the following
particular type of data, like characteristics:
integer, floating point, Boolean
etc. • The type of values (fixed or
• MySQL support a number if SQL variable) it represents.
standard data types in various • The storage space it takes is based
on whether the values are a fixed-
category. length or variable length.
• It use many different data type • Its values can be indexed or not.
broken into mainly 3 category:- • How MySQL performs a
numeric, date ant time and comparison of values of a
particular data type.
string type.
260
String Data Types:-

261
Numeric Data Types:-

262
Date and Time Types:-

263
Binary Large Object Data Types (BLOB):-

264
Accessing MySQL via the Command Line:-
• There are three main way can • Starting the command-line
interact with MySQL: interface:-
• After installing XAMPP, you will be
1. Command Line able to access the MySQL
2. Web interface such as executable from given directory
phpMyAdmin C:\xampp\mysql\bin
3. Programming language like • MySQL default user will be root
PHP and will not have had password
set.
• By using CMD select->Run, enter
CMD into the run box.

265
Accessing MySQL via the Command Line:-
• In window command prompt • MySQL Commands:-
enter below command • SQL command and Keyword are
C:\xampp\mysql\bin\mysql -u case-insensitive.
root • It is recommended to use
uppercase for all database
• You can check everything is operation.
working as it should be.
• Table name are case-sensitive on
SHOW databases; Linux, case insensitive on
For Linux:- window.
mysql -u root -p • It is recommended to use
lowercase for tables.

266
MySQL Commands:-

267
Database Related Command :-
• Creating Database:- USE <database name>;
• A database is a collection of Example:-
data. MySQL allows us to store
and retrieve the data from MariDB [none]> CREATE
database in a efficient way. DATABASE GPG;
• Syntax:- Query OK, 1 row affected(0.01
CREATE DATABASE <database sec)
name>; MariDB [none]> USE GPG;
• We can use/select created Database Changed
database use using MySQL USE
Command:- MariDB [GPG]>

268
Database Related Command :-
• Drop Database:- • Table Related Command:-
• We can drop/delete/remove a • In order to create a table we
MySQL database easily with the have to choose an appropriate
MySQL DROP DATABASE database to operation using USE
command. command.
• It delete all the table of the • Table can created using CREATE
database along with database TABLE command into the
permanently. database and by mentioning the
• Syntax:- field with its type.
DROP DATABASE database name;
269
Creating a Table:-
• In order to create a table we • Syntax:-
have to choose an appropriate CREATE TABLE [IF NOT EXISTS]
database for operation using <table name> (<field name> data
USE command. type [optional parameter]0
• Table can be created using ENGINE = storage engine;
CREATE TABLE command into • Example:-
the database and by mentioning
the field with its type. • CREATE TABLE students(rolln
VARCHAR(16), name
VARCHAR(120), percent
float(5,2)) ENGINE MyISAM;

270
Table Related Command :-
• Describe Command:- • Example:-
• For checking whether your new INSERT INTO student(rollno,
table has been created we can name, percent) VALUES (1, ‘Ram’,
use DESCRIBE command. 75.4);
• Syntax:-
DESCRIBE<table name>; • Deleting Table:-
• Adding Data to a Table:- • Syntax:-
INSERT INTO <table name> DROP Table<table name>;
(colomn_1, column_2..) VALUES
(value_1, value_2..);
271
MySQL storage Engine:-
• A storage engine is a software • List of storage engines
module that a database MySQL supported storage engines:
management system use to InnoDB
create, read, update data from a MyISAM
database. Memory
• There are two types of storage CSV
engine in MySQL:- transection Merge
and non- transection. Archive
Federated
Blackhole
Example

272
Connecting to MySQL Database:-
• Creating a Login files:- • Example:- login.php
• When a web site is developed
with PHP it contains multiple <?
program file files that will
require access to MySQL and $hn = ‘localhost’;
will thus need the login and $db = ‘college’;
password details.
$un = ‘root’;
• It is good to create a single file
to store login credential and $pw = ‘’;
then include that file whenever ?>
it is needed.
273
Connecting to MySQL Database:-
• All login credentials that are
used to log MySQL server are <?
saved in the file login.php, we
can include it in any PHP file to require_once ‘login.php’;
access the database by using $conn = new mysql($hn,
require_once statement. $un, $pw, $db);
• It will generate a fatal error if if($conn->connect_error)
the file is not found.
die ($conn->connect_error);
?>

274
Connecting to MySQL Database:-
• Building and Executing a Query: Example:-
<?php
require_once ‘login.php’;
<?php $conn = new mysql($hn, $un,
$query = “SELECT * FROM $pw, $db);
STUDENT”; if($conn->connect_error) die
($conn->connect_error);
$result = $conn->query($query);
$query = “SELECT * FROM
if(!$result) die($conn->error) STUDENT”;
?> $result = $conn->query($query);
if(!$result) die($conn->error)

275
Connecting to MySQL Database:-
$result->data_seek($j);
$row = $result->num_row; echo ‘Percentage’ .$result-
for($j=0; $j<row; ++$j) >fetch_assoc()*‘percent’+.<br/>’;
{ }
$result->data_seek($j); $result->close();
echo ‘Roll No.: ’ .$result- $conn->close();
>fetch_assoc()*‘rollno’+.<br/>’;
$result->data_seek($j); ?>
echo ‘Name: ’ .$result-
>fetch_assoc()*‘name’+.<br/>’;

276
Connecting to MySQL Database:-
• Fetching a Row:- Example:-
<?php
• To fetch one row at a time
fetch_array() method is used to require_once ‘login.php’;
fetched each row entirely at a $conn = new mysql($hn, $un,
$pw, $db);
time.
if($conn->connect_error) die
• This return single row of data as ($conn->connect_error);
an array, which is then assigned $query = “SELECT * FROM
to the array $row. STUDENT”;
$result = $conn->query($query);
if(!$result) die($conn->error)

277
Connecting to MySQL Database:-
echo ‘Name: ’ .$row*‘name’+.
$row = $result->num_row; ‘br/>’;
for($j=0; $j<row; ++$j) echo ‘Percentage: ’
.$row*‘percent’+. ‘br/>’;
{
}
$result->data_seek($j);
$result->close();
$row = $result-
>fetch_array(MYSQLI_ASSOC); $conn->close();
echo ‘Roll No.: ’ .$row*‘rollno’+. ?>
‘br/>’;

278
Connecting to MySQL Database:-
• Fetching a Row:- • Closing a Connection:-

• MySQL_NUM:- • Sresult->close();

• MSYQL_ASSOC:- • $result->close();

• MySQL_BOTH:-

279
Connecting to MySQL Database:-
• myslqli_fetch_object:- $result= mysqli_query($conn,
<?php $sql);
$conn=mysqli_connect(“localhos while($row=
t”, “root”, “college”); myslqli_fetch_object($result));
if(mysqli_connect_error()) {
{ echo $row->rollno;
echo “Failed to connect MySQL”; echo $row->name;
}
echo $row->percent;
$sql= “SELECT rollno,nane,
percent FROM student”; }

280