COMP519: Web Programming Fall 2005
Download
Report
Transcript COMP519: Web Programming Fall 2005
CSC WEB PROGRAMMING
IN THE NEXT LECTURES YOU WILL LEARN
W H AT I S S Q L
H O W T O A C C E S S M Y S Q L D ATA B A S E
H O W T O C R E AT E A B A S I C M Y S Q L D ATA B A S E
HOW TO USE SOME BASIC QUERIES
HOW TO USE PHP AND MYSQL
INTRODUCTION TO SQL
SQL is an ANSI (American National Standards Institute) standard computer
language
for accessing and manipulating databases.
SQL stands for Structured Query Language
using SQL you can
access a database
execute queries, and retrieve data
insert, delete and update records
SQL works with database programs like MS Access, DB2, Informix, MS SQL
Server, Oracle, Sybase, mySQL, etc.
Unfortunately, there are many different versions. But, they must support the same major
keywords in a similar manner such as SELECT, UPDATE, DELETE, INSERT, WHERE, etc.
Most of the SQL database programs also have their own proprietary extensions!
APPLICATION STRUCTURE
End
User
Applicatio
n
Software
SQL
Database
Data Model
SQL
Develop
er
DBA
Database
Tools
DATABASE ADMINISTRATOR (DBA)
A database administrator (DBA) is a person
responsible for the design, implementation,
maintenance and repair of an organization's
database.
The role includes the development and design
of database strategies, monitoring and
improving database performance and capacity,
and planning for future expansion
requirements.
They may also plan, co-ordinate and
implement security measures to safeguard the
database.
DATABASE MODEL
A database model or database schema is the
structure or format of a database, described
in a formal language supported by the
database management system, In other
words, a "database model" is the application
of a data model when used in conjunction
with a database management system.
SQL
Structured Query Language is the language we use to issue commands to the database
Create a table
Retrieve some data
Insert data
Delete data
COMMON DATABASE SYSTEMS
Three Major Database Management Systems in wide use
MySQL - Simple fast and scalable - commercial open source
Oracle - Large, commercial, enterprise-scale, very very tweakable
SqlServer - Very nice - from Microsoft (also Access)
Many other smaller projects, free and open source
HSQL, SQLite, Postgress, ...
BASIC SQL SYNTAX
➔ Data Definition Language (DDL)
• CREATE TABLE / DATABASE / VIEW / etc.....
• ALTER ...
• DROP ...
➔ Data Manipulation Language (DML)
•
•
•
•
SELECT ... FROM / INTO … WHERE ...
INSERT INTO ... VALUES ...
UPDATE … SET … WHERE ...
DELETE FROM … WHERE ...
MYSQL – KEY COMMANDS
Command
ALTER
BACKUP
CREATE
DELETE
DESCRIBE
DROP
INSERT
RENAME
SHOW
UPDATE
USE
Parameter(s)
Meaning
DATABASE, TABLE
Alter DATABASE or TABLE
TABLE
Back up TABLE
DATABASE, TABLE,
Create DATABASE or TABLE
(expression with TABLE & ROW)
Delete ROW from TABLE
TABLE
Describe the TABLE'S columns
DATABASE,TABLE
Delete DATABASE or TABLE
(expression with data)
Insert data
TABLE
Rename TABLE
(too many items to list)
List item's details
(expression with data)
Update an existing record
database
Use database
MySQL Resource: http://dev.mysql.com/doc/refman/5.5/en/index.html
SQL DATABASE TABLES
A database most often contains one or more tables. Each table is identified by a
name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.
For example, a table called "Persons":
LastName
FirstName
Address
City
Hansen
Ola
34 PeachApple
Atlanta
Stepheson
Love
23 PeachApple
Atlanta
Pettersen
Kari
20PeachApple
Atlanta
The table above contains three records (one for each person) and four columns (LastName,
FirstName, Address, and City).
SQL QUERIES
With SQL, you can query a database and have a result set returned.
A query like this:
SELECT LastName FROM Persons;
gives a result set like this:
LastName
Hansen
Stepheson
Pettersen
The mySQL database system requires a semicolon at the end of
the SQL statement!
SQL DATA LANGUAGES
The query and update commands together form the Data Manipulation Language
(DML) part of SQL:
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table
The Data Definition Language (DDL) part of SQL permits database tables to be created
or deleted:
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
*Here we will use some of them in mySQL
LOGGING INTO MYSQL SERVER
You can log into our mySQL server from Linux by typing in the prompt
bash-2.05b$ -u myname -p 1db23
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 209201 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
From here you can create, modify, and drop tables, and modify the data in your tables.
But first, you must specify which database on the server you want to use (you have only
one, however).
mysql> use db;
Database changed
TECHNICAL NOTE
You probably don’t need to worry about this, but thought I would mention it
here…
Most books and on-line tutorials assume the database server is running
on the same machine as everything else, and that the user is "root".
Neither of these are true here. Wherever you see "localhost", replace it by
"mysql" Wherever you see "root", replace it with your username.
(Ignore this if you don’t understand it for now, or are not consulting other
references.)
MYSQL & PHP: PROCESS STEPS
The process of using MySQL with PHP:
1.
Connect to MySQL
mysql_connect
2.
Select the database to use
3.
Build a query string $query = "SELECT * FROM tableName";
4.
Perform the query $result = mysql_query($query);
5.
Retrieve the results and output it to a web page
mysql_select_db
$rows = mysql_num_rows($result);
$row = mysql_fetch_row($result);
6.
Repeat Steps 3 to 5 until all desired data retrieved.
7.
Disconnect from MySQL (usually done automatically)
MYSQL & PHP: LOGIN.PHP FOR YOUR DATABASE – STEP
0
Create login_lastname.php file
<?php // login_lastname.php
make sure to place in personal folder
$db_hostname = 'localhost';
$db_database = 'yourMySQLusername';
$db_username = 'yourMySQLusername';
$db_password = 'yourMySQLpassword';
?>
MYSQL & PHP: CONNECT TO DATABASE – STEPS 1-4
<?php // query.php
require_once 'login_lastname.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());
$query = "SELECT * FROM classics";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);
for ($j = 0 ; $j < $rows ; ++$j){
$row = mysql_fetch_row($result);
echo 'Author: ' . $row[0] . '<br />';
echo 'Title: ' . $row[1] . '<br />';
echo 'Year: ' . $row[3] . '<br />';
echo 'ISBN: ' . $row[5] . '<br /><br />';
?>
YOUR FIRST MYSQL COMMAND
Kind of like "print 'hello world'"
show databases;
If this does not work, stop
and figure out why.
Some of these are part of
MySQL and store internal data
- don't mess with them.
CREATING A TABLE
You can create a table you might use for the upcoming project. For example,
mysql> CREATE TABLE students(
-> num INT NOT NULL AUTO_INCREMENT,
-> f_name VARCHAR(48),
-> l_name VARCHAR(48),
-> student_id INT,
-> email VARCHAR(48),
-> PRIMARY KEY(num));
Hit Enter after each line
(if you want). MySQL
doesn’t try to interpret the
command itself until it
sees a semicolon (;)
(The “->” characters you
see are not typed by you.)
Query OK, 0 rows affected (0.02 sec)
*If the server gives you a big ERROR, just try again from the top!
CREATING A TABLE
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16), year CHAR(4))
ENGINE MyISAM;
DESCRIBE classics;
VIEWING THE TABLE STRUCTURE
Use DESCRIBE to see the structure of a table
mysql> DESCRIBE students;
+------------+-------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+-------------+------+-----+---------+----------------+
| num
| int(11)
| NO
| PRI | NULL
| auto_increment |
| f_name
| varchar(48) | YES |
| NULL
|
|
| l_name
| varchar(48) | YES |
| NULL
|
|
| student_id | int(11)
| YES |
| NULL
|
|
| email
| varchar(48) | YES |
| NULL
|
|
+------------+-------------+------+-----+---------+----------------+
TEXT FIELDS
Understand character sets and indexable for searching
CHAR allocates entire space (faster for small strings where length is known)
VARCHAR allocates variable amount of space depending on the data length (less space)
BINARY TYPES (RARELY USED)
Character = 8 - 32 bits of information depending on character set
Byte = 8 bits of information
Small Images - data
TEXT FIELDS
Have a character set
BINARY LARGE OBJECT (BLOB)
Large raw data, files, images, word documents, PDF, Movies, etc etc..
No translation, indexing or character set
NUMBERS
Numbers are very efficient,
take little storage and are
easy to process because
CPU's can compare them
often with a single
instruction
32 bit
64 bi
DATES
TIMESTAMP is very efficient but only can handle dates from 1970 through 2037
32 bit
AUTO_INCREMENT
Often as we make multiple tables and
need to JOIN them together we
need an integer, primary key for
each row so we can efficiently add a
reference to a row in a table, in
some other table as a foreign key
DROP TABLE classics;
CREATE TABLE classics (
id INT UNSIGNED NOT NULL
AUTO_INCREMENT KEY,
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16), year CHAR(4))
ENGINE MyISAM;
DESCRIBE classics;
DATA TYPES
Text fields (small and large)
Binary fields (small and large)
Numeric fields
AUTO_INCREMENT fields
INSERTING DATA
Using INSERT INTO you can insert a new row into your table. For example,
mysql> INSERT INTO students
->
VALUES(NULL,’Russell’,’abc’,396640,‘[email protected]');
Query OK, 1 row affected (0.00 sec)
Using SELECT FROM you select some data from a table.
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+
| num | f_name | l_name | student_id | email
|
+-----+---------+--------+------------+----------------------+
|
1 | Russell | abc |
396640 | [email protected] |
+-----+---------+--------+------------+----------------------+
1 row in set (0.00 sec)
INSERTING SOME MORE DATA
You can repeat inserting until all data is entered into the table.
mysql> INSERT INTO students
-> VALUES(‘James',‘Bond',007,'[email protected]');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+
| num | f_name | l_name | student_id | email
|
+-----+---------+--------+------------+----------------------+
|
1 | Russell | abc |
396640 | [email protected]
|
|
2 | James
| Bond
|
7 | [email protected]
|
+-----+---------+--------+------------+----------------------+
2 rows in set (0.00 sec)
Note: The value “NULL” in the “num” field is automatically replaced by
the SQL interpreter as the “auto_increment” option was selected when
the table was defined.
INSERTING...
INSERT INTO classics(author, title, type, year)
VALUES('Mark Twain','The Adventures of Tom
Sawyer','Fiction','1876');INSERT INTO classics(author, title, type,
year)
VALUES('Jane Austen','Pride and
Prejudice','Fiction','1811');INSERT INTO classics(author, title,
type, year)
VALUES('Charles Darwin','The Origin of Species','NonFiction','1856');INSERT INTO classics(author, title, type, year)
VALUES('Charles Dickens','The Old Curiosity
Shop','Fiction','1841');INSERT INTO classics(author, title, type,
year)
VALUES('William Shakespeare','Romeo and Juliet','Play','1594');
INSERTED...
mysql> select * from classics;+----+---------------------+-----------------------------+-------------+------+| id | author
| title
| type
| year |+----+---------------------+-----------------------------+-------------+------+| 1 | Mark Twain
| The Adventures
of Tom Sawyer | Fiction
| 1876 || 2 | Jane Austen
| Pride
and Prejudice
| Fiction
| 1811 || 3 | Charles Darwin
|
The Origin of Species
| Non-Fiction | 1856 || 4 | Charles Dickens
| The Old Curiosity Shop
| Fiction
| 1841 || 5 | William
Shakespeare | Romeo and Juliet
| Play
| 1594 |+----+--------------------+------------------------------+-------------+------+5
rows in set (0.00 sec)mysql>
mysql> describe classics;+--------+------------------+------+-----+--------+----------------+| Field | Type
| Null | Key | Default |
Extra
|+--------+------------------+------+-----+---------+---------------+| id
| int(10) unsigned | NO
| PRI | NULL
|
auto_increment || author | varchar(128)
| YES |
| NULL
|
|| title | varchar(128)
| YES |
| NULL
|
||
type
| varchar(16)
| YES |
| NULL
|
|| year
| char(4)
| YES |
| NULL
|
|+--------+-----------------+------+-----+---------+----------------+5 rows in set
(0.00 sec)mysql> ALTER TABLE classics MODIFY year SMALLINT;Query OK, 5
rows affected (0.08 sec)Records: 5 Duplicates: 0 Warnings: 0mysql>
describe classics;+--------+------------------+------+-----+---------+---------------+| Field | Type
| Null | Key | Default | Extra
|+--------+------------------+------+-----+---------+----------------+|
id
| int(10) unsigned | NO
| PRI | NULL
| auto_increment ||
author | varchar(128)
| YES |
| NULL
|
||
title | varchar(128)
| YES |
| NULL
|
|| type
| varchar(16)
| YES |
| NULL
|
|| year
|
smallint(6)
| YES |
| NULL
|
|+--------+-----------------+------+-----+---------+----------------+5 rows in set (0.00
sec)
mysql> ALTER TABLE classics ADD pages SMALLINT;Query OK, 5 rows affected
(0.12 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> describe
classics;+--------+------------------+------+-----+---------+---------------+| Field | Type
| Null | Key | Default | Extra
|+--------+------------------+------+-----+---------+----------------+|
id
| int(10) unsigned | NO
| PRI | NULL
| auto_increment ||
author | varchar(128)
| YES |
| NULL
|
||
title | varchar(128)
| YES |
| NULL
|
|| type
| varchar(16)
| YES |
| NULL
|
|| year
|
smallint(6)
| YES |
| NULL
|
|| pages |
smallint(6)
| YES |
| NULL
|
|+--------+-----------------+------+-----+---------+----------------+6 rows in set (0.08
sec)mysql>
GETTING DATA OUT OF THE TABLE
The SELECT command is the main way of getting data out of a table, or set of tables.
SELECT * FROM students;
Here the asterisk means to select (i.e. return the information in) all columns.
You can specify one or more columns of data that you want, such as
SELECT f_name,l_name FROM students;
+---------+--------+
| f_name
| l_name |
+---------+--------+
| Russell | Martin |
| James
| Bond
|
+---------+--------+
2 rows in set (0.00 sec)
GETTING DATA OUT OF THE TABLE (CONT.)
You can specify other information that you want in the query using the WHERE clause.
SELECT * FROM students WHERE l_name=‘Bond’;
+-----+---------+--------+------------+----------------------+
| num | f_name
| l_name | student_id | email
|
+-----+---------+--------+------------+----------------------+
|
2 | James
| Bond
|
7 | [email protected]
|
+-----+---------+--------+------------+----------------------+
1 row in set (0.00 sec)
SELECT student_id, email FROM students WHERE l_name=‘Bond’;
+------------+----------------------+
| student_id | email
|
+------------+----------------------+
|
7 | [email protected]
|
+------------+----------------------+
1 row in set (0.00 sec)
ALTERING THE TABLE
The ALTER TABLE statement is used to add or drop columns in an existing table.
mysql> ALTER TABLE students ADD date DATE;
Query OK, 2 rows affected (0.00 sec)
Records: 2
Duplicates: 0
Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------+
| num | f_name | l_name | student_id | email
| date |
+-----+---------+--------+------------+----------------------+------+
|
1 | Russell | Martin |
396640 | [email protected] | NULL |
|
2 | James
| Bond
|
7 | [email protected]
| NULL |
+-----+---------+--------+------------+----------------------+------+
2 rows in set (0.00 sec)
UPDATING THE TABLE
The UPDATE statement is used to modify data in a table.
mysql> UPDATE students SET date='2007-11-15' WHERE num=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+
| num | f_name | l_name | student_id | email
| date
|
+-----+---------+--------+------------+----------------------+------------+
|
1 | Russell | Martin |
396310 | [email protected]
| 2012-08-16
|
2 | James
| Bond
|
7 | [email protected]
| NULL
|
+-----+---------+--------+------------+----------------------+------------+
2 rows in set (0.00 sec)
Note that the default date format is “YYYY-MM-DD” and I don’t believe this
default setting can be changed.
DELETING SOME DATA
The DELETE statement is used to delete rows in a table.
mysql> DELETE FROM students WHERE l_name='Bond';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+
| num | f_name | l_name | student_id | email
| date
|
+-----+---------+--------+------------+----------------------+------------+
|
1 | Russell | Martin |
396310 | [email protected] | 2006-11-15 |
+-----+---------+--------+------------+----------------------+------------+
1 row in set (0.00 sec)
THE FINAL TABLE
We’ll first add another column, update the (only) record, then insert more data.
mysql> ALTER TABLE students ADD gr INT;
Query OK, 1 row affected (0.01 sec)
Records: 1
Duplicates: 0
Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+------+
| num | f_name | l_name | student_id | email
| date
| gr
|
+-----+---------+--------+------------+----------------------+------------+------+
|
1 | Russell | Martin |
396310 | [email protected]
| 2007-11-15 | NULL |
+-----+---------+--------+------------+----------------------+------------+------+
1 row in set (0.00 sec)
mysql> UPDATE students SET gr=3 WHERE num=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> SELECT * FROM students;
+-----+---------+--------+------------+----------------------+------------+------+
| num | f_name | l_name | student_id | email
| date
| gr
|
+-----+---------+--------+------------+----------------------+------------+------+
|
1 | Russell | Martin |
396310 | [email protected]
| 2007-11-15 |
3 |
+-----+---------+--------+------------+----------------------+------------+------+
1 row in set (0.00 sec)
mysql> INSERT INTO students VALUES(NULL,‘James',‘Bond',007,'[email protected]‘,‘200711-15’, 1);
. . .
. . .
THE FINAL TABLE (CONT.)
. . .
. . .
mysql> INSERT INTO students VALUES(NULL,‘Hugh,‘Milner',75849789,‘[email protected]‘,
CURRENT_DATE, 2);
Note:
CURRENT_DATE is a built-in SQL command which (as expected)
gives the current (local) date.
mysql> SELECT * FROM students;
+-----+---------+----------+------------+----------------------------+------------+------+
| num | f_name
| l_name
| student_id | email
| date
| gr
|
+-----+---------+----------+------------+----------------------------+------------+------+
|
1 | Russell | Martin
|
396310 | [email protected]
| 2007-11-15 |
3 |
|
5 | Kate
| Ash
|
124309 | [email protected]
| 2007-11-16 |
3 |
|
3 | James
| Bond
|
7 | [email protected]
| 2007-11-15 |
1|
|
4 | Bob
| Jones
|
|
6 | Pete
| Lofton
|
|
7 | Polly
| Crackers |
|
8 | Hugh
| Milner
|
12190 | [email protected]
| 2007-11-16 |
3 |
76 | [email protected] | 2007-11-17 |
2 |
1717 | [email protected]
75849789 | [email protected]
| 2007-11-17 |
1|
| 2007-11-17 |
2 |
+-----+---------+----------+------------+----------------------------+------------+------+
7 rows in set (0.00 sec)
mysql> exit
Bye
OTHER SQL COMMANDS
SHOW tables; gives a list of tables that have been defined in the database
ALTER TABLE students DROP email; would drop the “email” column from all records
DROP TABLE students; deletes the entire “students” table, and its definition (use the
DROP command with extreme care!!)
DELETE FROM students; removes all rows from the “students” table (so once again, use
the DELETE command with great caution), the table definition remains to be used
again
A more useful command is something like
DELETE FROM students WHERE (num > 5) AND (num <= 10);
which selectively deletes students based on their “num” values (for example).
HELP; gives the SQL help
HELP DROP; gives help on the DROP command, etc.
BACKING UP/RESTORING A MYSQL DATABASE
You can back up an entire database with a command such as
mysqldump –h mysql –u martin martin > backup.sql
(Run from the Unix command line.)
This gives a script containing SQL commands to reconstruct the table structure (of all tables) and all of
the data in the table(s).
To restore the database (from scratch) you can use this type of Unix command:
mysql –h mysql –u martin martin < backup.sql
(Use with caution, as this can overwrite your database.)
Other commands are possible to backup/restore only certain tables or items in tables, etc. if that is what
you desire. For example
mysqldump –h mysql –u martin martin books clients> backup.sql
stores information about the “books” and “clients” tables in the “martin” database.
PUTTING CONTENT INTO YOUR DATABASE WITH PHP
We can simply use PHP functions and mySQL queries together:
• Connect to the database server and login (this is the PHP command to do so)
mysql_connect("host","username","password");
• Choose the database
mysql_select_db("database");
• Send SQL queries to the server to add, delete, and modify data
mysql_query("query"); (use the exact same query string as you would
normally use in SQL, without the trailing semi-colon)
• Close the connection to the database server (to ensure the information is stored properly)
mysql_close();
STUDENT DATABASE: DATA_IN.PHP
<html>
<head>
<title>Putting Data in the DB</title>
</head>
<body>
<?php
/*insert students into DB*/
if(isset($_POST["submit"])) {
$db = mysql_connect("mysql”, “user“, “password”);
mysql_select_db(“db");
$date=date("Y-m-d");
/*
Get the current date in the right SQL format
*/
$sql="INSERT INTO students VALUES(NULL,'“ . $_POST[“f_name"] . "','“ .
$_POST["l_name"] . "',“ . $_POST["student_id"] . ",'“ . $_POST["email"] . "','“ .
$date . "',“ . $_POST["gr"] . ")";
/* construct the query */
mysql_query($sql);
mysql_close();
/*
execute the query
*/
echo"<h3>Thank you. The data has been entered.</h3> \n";
echo'<p><a href="data_in.php">Back to registration</a></p>‘ . “\n”;
echo'<p><a href="data_out.php">View the student lists</a></p>‘ .”\n”;
}
STUDENT DATABASE: DATA_IN.PHP
else {
?>
<h3>Enter your items into the database</h3>
<form action="data_in.php" method="POST">
First Name: <input type="text" name=“f_name“ /> <br/>
Last Name: <input type="text" name=“l_name“ /> <br/>
ID: <input type="text" name=“student_id“ /> <br/>
email: <input type="text" name=“email“ /> <br/>
Group: <select name="gr">
<option value ="1">1</option>
<option value ="2">2</option>
<option value ="3">3</option>
</select><br/><br/>
<input type="submit" name="submit“ /> <input type="reset“ />
</form>
<?php
}
?>
</body>
</html>
GETTING CONTENT OUT OF YOUR DATABASE WITH
PHP
Similarly, we can get some information from a database:
• Connect to the server and login, choose a database
mysql_connect("host","username","password");
mysql_select_db("database");
• Send an SQL query to the server to select data from the database into an array
$result=mysql_query("query");
• Either, look into a row and a fieldname
$num=mysql_numrows($result);
$variable=mysql_result($result,$i,"fieldname");
• Or, fetch rows one by one
$row=mysql_fetch_array($result);
• Close the connection to the database server
mysql_close();
STUDENT DATABASE: DATA_OUT.PHP
<html>
<head>
<title>Getting Data out of the DB</title>
</head>
<body>
<h1> Student Database </h1>
<p> Order the full list of students by
<a href="data_out.php?order=date">date</a>,
<href="data_out.php?order=student_id">id</a>, or
by <a href="data_out.php?order=l_name">surname</a>.
</p>
<p>
<form action="data_out.php" method="POST">
Or only see the list of students in group
<select name="gr">
<option value ="1">1</option>
<option value ="2">2</option>
<option value ="3">3</option>
</select>
<br/>
<input type="submit" name="submit“ />
</form>
</p>
STUDENT DATABASE: DATA_OUT.PHP
<?php
/*get students from the DB */
$db = mysql_connect("mysql",“martin");
mysql_select_db(“martin", $db);
switch($_GET["order"]){
case 'date':
$sql = "SELECT * FROM students ORDER BY date"; break;
case ‘student_id':
$sql = "SELECT * FROM students ORDER BY student_id";
break;
case ‘l_name': $sql = "SELECT * FROM students ORDER BY l_name"; break;
default: $sql = “SELECT * FROM students”; break;
}
if(isset($_POST["submit"])){
$sql = “SELECT * FROM students WHERE gr=“ . $_POST["gr"];
}
$result=mysql_query($sql);
/* execute the query */
while($row=mysql_fetch_array($result)){
echo "<h4> Name: “ . $row["l_name"] . ', ‘ . $row["f_name"] . "</h4> \n";
echo "<h5> ID: “ . $row[“student_id"] . "<br/> Email: “ . $row["email"] .
"<br/> Group: “ . $row["gr"] . "<br/> Posted: “ . $row["date"] . "</h5> \n";
}
mysql_close();
?>
</body>
</html>
CAN DO EVEN MORE WITH PHP
Can create tables in PHP
Can delete rows and columns
Can make updates
Can make queries to several tables
Can get connected to several databases
* Find more information on PHP/mySQL
MYSQL INDEX TYPES
INDEX - Good for individual row lookup and sorting / grouping results - works best
with exact matches or prefix lookups
PRIMARY KEY - Very little space, very very fast, exact match, requires no duplicates,
extremely fast for integer fields
FULLTEXT - Costly to maintain on insert of new data, can handle substrings and the
LIKE clause
FULLTEXT DETAILS
FULLTEXT indexes can be used only with tables that use the MyISAM engine. (ALTER
TABLE tablename ENGINE = MyISAM;)
FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only.
For large data sets, it is much faster to load your data into a table that has no
FULLTEXT index and then create the index than to load data into a table that has
an existing FULLTEXT index.
ADDING INDEXES
You can add an index as part of a CREATE TABLE sequence or separately using an
ALTER TABLE
ALTER TABLE classics ADD INDEX(author);
ALTER TABLE classics ADD FULLTEXT(title);
RETRIEVING ROWS WITH SELECT
mysql> select * from classics;+----+---------------------+-----------------------------+-------------+------+-------+| id | author
| title
| type
| year | pages |+----+---------------------+-----------------------------+-------------+------+-------+| 1 | Mark Twain
| The
Adventures of Tom Sawyer | Fiction
| 1876 | NULL || 2 | Jane Austen
| Pride and Prejudice
| Fiction
| 1811 | NULL || 3 | Charles
Darwin
| The Origin of Species
| Non-Fiction | 1856 | NULL || 4 |
Charles Dickens
| The Old Curiosity Shop
| Fiction
| 1841 | NULL
|| 5 | William Shakespeare | Romeo and Juliet
| Play
| 1594
| NULL |+----+---------------------+------------------------------+------------+------+-------+5 rows in set (0.00 sec)mysql>
RETRIEVING ROWS WITH SELECT
mysql> select title, year from classics;+-----------------------------+------+| title
| year |+-----------------------------+------+| The Adventures of Tom Sawyer |
1876 || Pride and Prejudice
| 1811 || The Origin of
Species
| 1856 || The Old Curiosity Shop
| 1841 ||
Romeo and Juliet
| 1594 |+-----------------------------+------+5 rows in set (0.00 sec)
mysql>
COUNTING WITH SELECT
mysql> select count(*) from classics;+----------+| count(*) |+---------+|
5 |+----------+1 row in set (0.00 sec)mysql>
WHERE CLAUSE
We can limit the rows we retrieve on a SELECT using a WHERE clause
mysql> SELECT title, author, year FROM classics WHERE year >
1850;+------------------------------+----------------+-----+| title
| author
| year |+-----------------------------+----------------+------+| The
Adventures of Tom Sawyer | Mark Twain
| 1876 || The
Origin of Species
| Charles Darwin | 1856 |+-----------------------------+----------------+------+2 rows in set
(0.00 sec)mysql>
THE LIKE CLAUSE
We can do wildcard matching in a WHERE clause using the LIKE operator
mysql> SELECT title, author FROM classics WHERE title LIKE
"%and%";+---------------------+---------------------+| title
| author
|+---------------------+--------------------+| Pride and Prejudice | Jane Austen
|| Romeo
and Juliet
| William Shakespeare |+---------------------+--------------------+2 rows in set (0.00 sec)mysql>
ORDER BY
mysql> SELECT title, author, year FROM classics ORDER BY
year;+------------------------------+---------------------+-----+| title
| author
|
year |+------------------------------+---------------------+-----+| Romeo and Juliet
| William Shakespeare |
1594 || Pride and Prejudice
| Jane Austen
|
1811 || The Old Curiosity Shop
| Charles Dickens
|
1841 || The Origin of Species
| Charles Darwin
|
1856 || The Adventures of Tom Sawyer | Mark Twain
|
1876 |+------------------------------+---------------------+-----+5 rows in set (0.00 sec)mysql>
ORDER BY
mysql> SELECT title, author, year FROM classics ORDER BY year
DESC;+------------------------------+---------------------+-----+| title
| author
|
year |+------------------------------+---------------------+-----+| The Adventures of Tom Sawyer | Mark Twain
|
1876 || The Origin of Species
| Charles Darwin
|
1856 || The Old Curiosity Shop
| Charles Dickens
|
1841 || Pride and Prejudice
| Jane Austen
|
1811 || Romeo and Juliet
| William Shakespeare |
1594 |+------------------------------+---------------------+-----+5 rows in set (0.00 sec)mysql>
Indexes improve ORDER BY per
THE LIMIT CLAUSE
The LIMIT clause can request the first "n" rows, or the first "n" rows after some starting
row. Note: the first row is zero, not one
WHERE and ORDER BY clauses happen *before* the LIMIT is applied
mysql> SELECT title, author, year FROM classics ORDER BY year DESC
LIMIT 2;+------------------------------+----------------+------+|
title
| author
| year |+-----------------------------+----------------+------+| The Adventures of Tom
Sawyer | Mark Twain
| 1876 || The Origin of Species
|
Charles Darwin | 1856 |+------------------------------+---------------+------+2 rows in set (0.01 sec)
THE LIMIT CLAUSE
The LIMIT clause can request the first "n" rows, or the first "n" rows after some starting
row. Note: the first row is zero, not one
WHERE and ORDER BY clauses happen *before* the LIMIT is applied
mysql> SELECT title, author, year FROM classics ORDER BY year DESC
LIMIT 1,2;+------------------------+-----------------+------+| title
| author
| year |+------------------------+----------------+------+| The Origin of Species | Charles Darwin | 1856 || The Old
Curiosity Shop | Charles Dickens | 1841 |+------------------------+----------------+------+2 rows in set (0.00 sec)
GROUP BY AND COUNT
We can get sub-counts of items where there are duplicate values in a column
CREATE TABLE tracks ( id INT
UNSIGNED NOT NULL
AUTO_INCREMENT KEY,
title
VARCHAR(128),
rating INTEGER);
INSERT INTO tracks (title, rating)
VALUES ('Who Made Who', 5);INSERT
INTO tracks (title, rating) VALUES
('Stray Cat Strut', 4);INSERT INTO
tracks (title, rating) VALUES ('The
Twist', 2);INSERT INTO tracks
(title, rating) VALUES ('Down With
The Sickness', 4);INSERT INTO tracks
(title, rating) VALUES ('Iron Man',
5);
UPDATE .. SET
Allows the updating of one or more columns in a row or set of rows
Usually used with a WHERE clause
mysql> SELECT * FROM tweet;+---------------+-------+| text
| zip
|+----------------+------+| Had pizza
| 48842 || Had
Chi Dog
| 48109 || Went to 572
| 48109 || Coding at home | 48842
|+----------------+-------+
mysql> UPDATE tweet SET zip='49277' WHERE text = 'Had
pizza';Query OK, 1 row affected (0.00 sec)Rows matched:
1 Changed: 1 Warnings: 0mysql> SELECT * FROM tweet;+---------------+-------+| text
| zip
|+---------------+-------+| Had pizza
| 49277 || Had Chi
Dog
| 48109 || Went to 572
| 48109 || Coding at
home | 48842 |+----------------+-------+
JOIN
We can pull "composite" rows from multiple tables as long as we have fields that can
link rows from one table to the corresponding rows in another table.
mysql> select * from
tweet;+----------------+------+| text
| zip
|+----------------+------+| Had pizza
| 48842
|| Had Chi Dog
| 48109
|| Went to 572
| 48109
|| Coding at home | 48842
|+----------------+-------+
mysql> select * from
zips;+-------+-----------+------+| zip
| city
| state |+-------+----------+-------+| 48842 | Holt
| MI
|| 48109 | Ann
Arbor | MI
|+-------+----------+-------+
mysql> SELECT * FROM tweet JOIN zips ON tweet.zip =
zips.zip;+----------------+-------+-------+-----------+------+| text
| zip
| zip
| city
|
state |+----------------+-------+-------+-----------+------+| Had pizza
| 48842 | 48842 | Holt
| MI
|| Had Chi Dog
| 48109 | 48109 | Ann Arbor | MI
||
Went to 572
| 48109 | 48109 | Ann Arbor | MI
||
Coding at home | 48842 | 48842 | Holt
| MI
|+---------------+-------+-------+-----------+-------+
mysql> SELECT * FROM tweet JOIN zips ON tweet.zip =
zips.zip;+----------------+-------+-------+-----------+------+| text
| zip
| zip
| city
|
state |+----------------+-------+-------+-----------+------+| Had pizza
| 48842 | 48842 | Holt
| MI
|| Had Chi Dog
| 48109 | 48109 | Ann Arbor | MI
||
Went to 572
| 48109 | 48109 | Ann Arbor | MI
||
Coding at home | 48842 | 48842 | Holt
| MI
|+---------------+-------+-------+-----------+-------+
mysql> SELECT text, city, state FROM tweet JOIN zips
ON tweet.zip = zips.zip;+---------------+-----------+-------+| text
| city
|
state |+----------------+-----------+-------+| Had pizza
| Holt
| MI
|| Had Chi Dog
| Ann Arbor | MI
|| Went to 572
| Ann Arbor | MI
|| Coding at home
| Holt
| MI
|+----------------+-----------+------+
MYSQL FUNCTIONS
Many operations in MySQL need to use the built-in functions
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
LEARNING OUTCOMES
In this lecture you have learned
What is SQL
How to access mySQL database
How to create a basic mySQL database
How to use some basic queries
How to use PHP and mySQL