1

Lecture 9 – MYSQL and PHP

(Part1)SFDV3011 – Advanced Web

Development

2

SQL SQL (Structured Query Language) is the language most

commonly used to define, manipulate, and query RDBMSs

It’s origins trace back to SEQUEL designed for IBM’s “System R” in 1970s

Currently there is an ANSI (American National Standards Institute) standard for SQL

The standard does not adhere in many respects to the relational model described by Codd (e.g. it explicitly breaks several important relational concepts - particularly by allowing duplicate rows, and columns with the same names)

3

RDBMSs based on SQL There are a number of choices for an RDBMS based on

SQL:

Oracle - developed by Oracle Corporation (whose origins date back to 70s, when Ellis spotted potential in concept of relational theory and IBM’s SEQUEL)

Microsoft SQL ServerMySQL - extremely popular, open source RDBMSPostgreSQL, SQLite, ...and many others

Each of these RDBMSs uses it’s own implementation of SQL. In this course we will cover MySQL.

4

MYSQL Open source RDBMS

Popular, because it’s free, lightweight (not all the features of Oracle) and relatively fast

Named after the daughter (My) of Michael Widenius, who was the main author of the first version

Used by Wikipedia, Facebook and many other websites

Managed and developed by MySQL AB until 2008 when it was acquired by SUN Microsystems, which in turn was bought by Oracle Corporation in 2010.

5

Example of database

OrganisationID Name Address Notes

1 ACME Inc New York Very friendly crew

2 Abcde.com Bathgate No comment

VisitID VisitorID OrganisationID Date1 1 2 21/03/01

2 3 2 23/07/05

3 2 1 05/02/06

VisitorID Name Email Affiliation

1 John Doe J.d@napier.ac.uk Napier University

2 Jim Doe Ji.d@napier.ac.uk Napier University

3 John Doe Jo.d@napier.ac.uk Napier University

Visitors

Organisation

Visits

6

Numeric data types

• BIT[(n)] 1 to 2n

• TINYINT -128 to 127• SMALLINT -32768 to 32767• INT -2147483648 to 2147483647• FLOAT -3.4E+38 to -1.18e-38, 0, and 1.18E-38 to

3.4E+38• BOOL, BOOLEAN

– 0 = FALSE – other non null values = TRUE

7

String data types

• CHAR(n), 0< n <255– [BINARY | ASCII | UNICODE]

• TINYBLOB up to 255 bytes• TINYTEXT up to 255 characters• BLOB up to 65535 bytes• TEXT up to 65535 characters• ENUM('value1', 'value2', ...) up to 65535

values

8

Date and Time data types

• DATE '1000-01-01' to '9999-12-31'• TIME '-838:59:59' to '838:59:59'• DATETIME '1000-01-01 00:00:00' to '9999-12-31

23:59:59'• TIMESTAMP used to record update or insert times• YEAR [(2 | 4)] 1901 to 2155,

9

Log in mysql• Type command:

mysql -h hostname -u username -p

• Where:hostname = localhostdefault username = root

• Once logged in, the following mysql prompt appears to type mysql commands:

mysql>

10

Create a database

Type the command:

create database dbname;

View tables created (at first there won't be any, because you haven't made any yet!) :

show tables;

11

Create a tablemysql> create table tablename(

column1 type,column2 type,...columnn type);

mysql> create table visitors(visitorID int unsigned not null auto_increment primary

key,name char(50) not null,email char(50),Affiliation char(200));

mysql> create table organisations(organisationID int unsigned not null auto_increment

primary key,name char(50) not null,address char(200),notes text);

mysql> create table visits(visitID int unsigned not null auto_increment primary

key,visitorID int unsigned not null,organisationID int unsigned not null,date date);

12

Looking at the database• View all tables:

– show tables;• Describe a table:

– describe tablename;

• List table content:– Query the

database …

13

Inserting datamysql> insert into tablename

[(col1, col2, ...)] values(val1, val2, ...);

mysql> insert into visitors (name, email) values('John Doe', 'j.d@napier.ac.uk'),('Jim Doe', 'ji.d@napier.ac.uk'),('Jon Doe', 'jo.d@napier.ac.uk');mysql> insert into organisations (name,

address, notes) values('ACME', 'New York', NULL),('abcde.com', ‘Bathgate’, 'No comment!');

mysql> insert into visits values(NULL, 1, 2, '2000-12-20'),(NULL, 2, 2, '2004-04-21'),(NULL, 1, 2, '2006-05-06');

14

Retrieving data

SELECT [options] items [INTO file_details]FROM tables[WHERE conditions][GROUP BY group_type][HAVING where_definitions][ORDER BY order_type][LIMIT limit_criteria][PROCEDURE

procedure_name(args)][lock_options];

15

Retrieving data• Select whole table:

– select * from table_name;• Select one column:

– select col_name from table_name;• Select two columns:

– select col1, col2 from table_name;• Selection with criterion:

– select * from table_name where criterion;

– Note: criterion is a boolean.

16

Selection from multiple tables (1)

• Selection condition(s) may involve more than one table

Example:“Show the dates of all visits made by Jim Doe.”

select visits.datefrom visits, visitorswhere visitors.name = 'Jim Doe'and visitors.visitorID = visits.visitorID;

17

Selection from multiple tables (2)

• Selection from more than two tables:– Follow relations between tables

Example:“Show the dates of all visits made by Jim Doe to Acme

Inc.”You do it, using SELECT, FROM, WHERE, ANDAnswer:

select visits.datefrom visits, visitors, organisationswhere visitors.name = 'Jim Doe'and visitors.visitorID = visits.visitorIDand organisation.name = 'Acme Inc.'and organisation.organisationID =

visits.organisationID;

18

Updating recordsUPDATE [options] tablenameSET col1=expr1, col2=expr2 …[WHERE conditions][ORDER BY order_type][LIMIT number]

Example - Update ACME’s address:

update organisationsset address = 'Boston'where organisationID = 1;

19

Deleting records

DELETE [options] FROM tablename[WHERE conditions][ORDER BY order_type][LIMIT number]

Example - Delete ACME from organisation table:

delete from organisationswhere organisationID = 1;

20

Deleting tables

DROP [options] TABLE tablename;

Example – Delete organisations:

drop table organisations;

21

Tips

SQL commands can be stored in a text file.

Commands stored in the text file can be executed using source

Example:mysql> source filename.sql

In command windows (DOS/UNIX), use the up arrow to navigate to previously typed commands.

22

MYSQL storage engines

MySQL supports multiple storage engines Each table can be configured to use a different storage engine – you

can mix storage engines in one database When you create a new table, you can specify which storage engine to

use as follows:

create table visits(visitID int unsigned not null

auto_increment primary key,visitorID int unsigned not null,organisationID int unsigned not null,date date) ENGINE=’MyISAM ’;

It is possible to migrate a table to a different storage engine (this is not always a trivial operation, since some features might not be supported in both engines)

ALTER TABLE visits ENGINE=’InnoDB ’; To find out which storage engine a table uses:

SHOW CREATE TABLE visits \G ;

23

MYSQL storage engines

InnoDB (default for MySQL 5.5 and later) - transaction safe (ACID compliant), data stored in clustered indexes, foreign-key support

MyISAM (default prior to MySQL 5.5) - no-transaction, full-text search index

Archive - supports only INSERT and SELECT queries; un-indexed tables for storing data that is not accessed too often

Memory - all data stored in RAM (does not survive reboot), very fast, good for intermediate results ...and more

24

Schema optimization

Choosing data types

The smaller the data type (in terms of bytes it uses to represent information) the faster its manipulation

Simpler data types are easier to handle (e.g. storing date and time as integers, not strings)

Define fields as NOT NULL whenever you can - possibility of variable being null makes queries and indexing more complicated

Using VARCHAR instead of CHAR trades storage space for speed

It is best to use INTEGER type for primary keys

25

Indexing An index is a map of your data that orders it and allows for quick

search (like index at the end of a book with references to pages where a particular term can be found)

Indexes help a database retrieve data efficiently

For small databases indexing can be neglected, but as the size of the database grows, indexing becomes critical

Indexes slow down inserts and updates but speed up WHERE clauses and ORDER BY.

Failure to properly index data is the leading cause of SQL performance problems

26

Query performance

Poorly designed queries may unnecessarily retrieve more data than needed (not only too many rows but also too many columns) that is eventually thrown away before returning the final result

Do you need so many rows?

SELECT * FROM organisation, visits WHERE organisation.OrganisationID = visits.OrganisationID LIMIT 1;

Do you need all the columns?

The EXPLAIN statement can be used as a way to obtain information about how MySQL executes a SELECT statement

EXPLAIN SELECT * FROM organisation, visits WHERE organisation.OrganisationID = visits.OrganisationID;