Transcript Unit-8

Unit-8
Introduction Of
MySql
Types of table in PHP
• MySQL supports various of table types or
storage engines to allow you to optimize your
database.
• The table types are available in MySQL are:
1. ISAM
2. MyISAM
3. InnoDB
4. BerkeleyDB (BDB)
5. MERGE
6. HEAP
• only MyISAM tables support indexing and
searching feature.
• MyISAM is also the default table type
when you create table without declaring
which storage engine to use.
ISAM
• The original storage engine in MySQL was the
ISAM engine. It was the only storage engine
available until MySQL 3.23, when the improved
MyISAM engine was introduced as the default.
• It is not available in MySQL 5.0. Embedded
MySQL server versions do not support ISAM
tables by default.
• Each ISAM table is stored on disk in three files.
The files have names that begin with the table
name and have an extension to indicate the file
type.
• An .frm file stores the table definition. The data
file has an .ISD extension. The index file has an
.ISM extension.
MyISAM
• MyISAM table type is default when you
create table.
• MyISAM table work very fast.
• The size of MyISAM table depends on the
operating system and the data file are
portable from system to system.
• With MyISAM table type, you can have 64
keys per table and maximum key length of
1024 bytes.
• Each MyISAM table is stored on disk in
three files.
• The files have names that begin with the
table name and have an extension to
indicate the file type.
• An .frm file stores the table format. The
data file has an .MYD (MYData)
extension. The index file has an .MYI
(MYIndex) extension.
InnoDB
• Different from MyISAM table type, InnoDB table
are transaction safe and supports row-level
locking.
• Foreign keys are supported in InnoDB tables.
• The data file of InnoDB table can be stored in
more than one file so the size of table depends
on the disk space.
• Like the MyISAM table type, data file of InnoDB
is portable from system to system.
• The disadvantage of InnoDB in comparison with
MyISAM is it take more disk space.
BerkeleyDB (BDB)
• BDB is similar to InnoDB in transaction
safe.
• It supports page level locking but data file
are not portable.
MERGE
• Merge table type is added to treat multiple
MyISAM tables as a single table so it remove the
size limitation from MyISAM tables.
• When you create a MERGE table, MySQL
creates two files on disk.
• The files have names that begin with the table
name and have an extension to indicate the file
type.
• An .frm file stores the table format, and an .MRG
file contains the names of the underlying
MyISAM tables that should be used as one.
HEAP
• Heap table is stored in memory so it is the
fastest one.
• Because of storage mechanism, the data will be
lost when the power failure and sometime it can
reason the server run out of memory.
• Heap tables do not support columns with
AUTO_INCREMENT and TEXT characteristics.
• The MEMORY storage engine associates each
table with one disk file.
• The file name begins with the table name and
has an extension of .frm to indicate that it stores
the table definition.
QUERY IN MYSQL
1. SELECT
• SELECT selects records from the database.
When this command is executed from the
command line, MySQL prints all the records that
match the query.
• The simplest use of SELECT is shown in this
example:
• SELECT * FROM age information;
• The * means “show values for all fields in the
table”; FROM specifies the
• table from which to extract the information.
2. INSERT
• For the table to be useful, we need to add
information to it. We do so with the INSERT
command:
• INSERT INTO age information
(lastname, firstname, age) VALUES (´PATEL´,
´RAM´, 46);
• The syntax of the command is INSERT INTO,
followed by the table in which to insert, a list
within parentheses of the fields into which
information is to be inserted, and the qualifier
VALUES followed by the list of values in
parentheses in the same order as the respective
fields.
3.UPDATE
• Since the database is about people, information
in it can change For instance, although a
person’s birthday is static, their age changes. To
change the value in an existing record, we can
UPDATE the table. Let’s say the fictional Larry
Wall has turned 47:
• UPDATE age information SET age = 47
WHERE lastname = ´PATEL´;
• Be sure to use that WHERE clause; otherwise, if
we had only entered UPDATE
• age information SET age = 47, all the records in
the database would have
• been given the age of 47!
4. DELETE
• Sometimes we need to delete a record
from the table This is done with the
DELETE command.
• DELETE FROM age information
WHERE lastname = ´PATEL´;
TRUNCATE
• TRUNCATE tbl_name
• TRUNCATE TABLE empties a table completely.
• Logically, this is equivalent to a DELETE
statement that deletes all rows, but there are
practical differences under some circumstances.
• TRUNCATE `student`
• That is, we want to keep the layout and structure
of the table, but just empty the data.
• MySQL has a handy command for emptying a
table known as TRUNCATE.
Alias
• To save typing and add additional functionality,
table aliases are sometimes used in queries.
• Consider an example that finds all inventory
details of wine #183:
• SELECT * FROM inventory i, wine w WHERE
i.wine_id = 183 AND i.wine_id = w.wine_id;
• In this query, the FROM clause specifies aliases
for the table names. The alias inventory i means
than the inventory table can be referred to as i
elsewhere in the query. For example, i.wine_id
is the same as inventory.wine_id. This saves
typing in this query.
Order By
• Suppose we have a large amount of data in a
database.
• When we perform a SELECT query on the data,
we get a whole bunch of data.
• Now suppose we wanted to sort that data, that
is, we wanted to arrange the data in alphabetical
order before showing it to a user in their
browser.
• Would we sort the data using PHP? Of course
not. MySQL has its own built-in sorting engine
and we can invoke it using order by in our SQL.
• If we had the table "employees" and we
wanted to output the entire table sorting in
ascending order by last name we would
use the following code:
• SELECT * FROM employees ORDER BY
last_name ASC;
• If we wanted to sort in descending order
we would use:
• SELECT * FROM employees ORDER BY
last_name DESC;
• If we do not specify "ASC" or "DESC",
MySQL will default to "ASC" or ascending.
Difference Between Truncate and
Delete
• TRUNCATE is a DDL (data definition language)
command whereas DELETE is a DML (data
manipulation language) command.
• You can use WHERE clause(conditions) with DELETE
but you can't use WHERE clause with TRUNCATE .
• You cann't rollback data in TRUNCATE but in DELETE
you can rollback data.TRUNCATE removes(delete) the
record permanently.
• A trigger doesn’t get fired in case of TRUNCATE
whereas Triggers get fired in DELETE command.
• If tables which are referenced by one or more FOREIGN
KEY constraints then TRUNCATE will not work.
• Delete and Truncate both are logged operation. But
DELETE is a logged operation on a per row basis and
TRUNCATE logs the deallocation of the data pages in
which the data exists.
• TRUNCATE is faster than DELETE.