csci5211: Computer Networks and Data Communications
Download
Report
Transcript csci5211: Computer Networks and Data Communications
Internet Engineering Course
Web Application Development
Introduction
Company needs to provide various web
services
◦ Hosting intranet applications
◦ Company web site
◦ Various internet applications
Therefore there is a need to develop
applications
◦ We should select web application model to use
◦ Discuss on different types of applications
Content
Application development models:
◦ SSI
◦ CGI with Perl
◦ LAMP/WAMP
◦ J2EE
◦ .Net
Typical web applications in an organization
◦
◦
◦
◦
CMS/DMS
Groupware (Collaboration software)
WIKI
Workflow
SSI
Server Side Includes
This is very simple model, not really an application
development model suitable for middle to large size
applications
Web server processes such instructions and generate
dynamic content
Directives that are placed in HTML pages, and evaluated
on the server while the pages are being served.
Let you add dynamically generated content to an existing
HTML page, without having to serve the entire page via a
CGI program, or other dynamic technology.
SSI
SSI directives have the following syntax:
<!--#element attribute=value attribute=value
... -->
It is formatted like an HTML comment
so if you don't have SSI correctly enabled,
the browser will ignore it
SSI examples
Commonly used to obtain and display environment variables from the
OS:
<!--#echo var="DATE_LOCAL" -->
Modification date of the file
This document last modified
<!--#flastmod file="index.html" -->
Including the results of a CGI program:
<!--#include virtual="/cgi-bin/counter.pl" -->
Including a standard footer:
<!--#include virtual="/footer.html" -->
SSI examples (cont.)
Executing commands:
<pre> <!--#exec cmd="ls" --> </pre>
Conditional expressions:
<!--#if expr="${Mac} &&
${InternetExplorer}" -->
Apologetic text goes here
<!--#else --> Cool JavaScript
code goes here
<!--#endif -->
SSI conclusion
Minimize code duplications
SSI for site management
Creates dynamic content
Can be a security problem
CGI
Common Gateway Interface
Invented in 1993 by NCSA for HTTPd web server
◦ Client requests program to be run on server-side
◦ Web server passes parameters to program through
UNIX shell environment variables
◦ Program spawned as separate process via fork
◦ Program's output => Results
◦ Server passes back results (usually in form of HTML)
Good for interfacing external applications with
information servers
it is language independent
CGI programs are most often written in PERL, C/C++,VB,
Java, or UNIX shell scripts.
CGI
Request service
HEADERS
BODY
Run CGI
program
…
…
…
print $result
CGI with Perl
Write a standard Perl Program
Program's output (to stdout) is sent back
as HTTP Response
You must write out everything
◦ Headers
◦ Blank Space
◦ Body
CGI with Perl
Some CGI programs are in machine code, but Perl programs are
usually kept in source form, so Perl must be run on them
A source file can be made to be “executable” by adding a line at
their beginning that specifies that a language processing program be
run on them first
For Perl programs, if the perl system is stored in /usr/local/bin/perl,
as is often is in UNIX systems, this is:
◦ #!/usr/local/bin/perl
The file extension .cgi is sometimes used for Perl CGI programs
An HTML document specifies a CGI program with the hypertext
reference attribute, href, of an anchor tag, <a>, as in
◦ <a href = “./cgi-bin/reply.cgi>" Click here
to run the CGI program, reply.pl </a>
Perl
Practical Extension and Reporting Language.
Originally developed as a utility language for
UNIX.
Particularly well suited to manipulate patterns,
especially text.
Popular because it is free, available for most
operating systems, and relatively easy to learn
Exceedingly powerful, but noisy, and prone to
errors.
Perl – a simple example
“Hello World” in Perl
#! /usr/bin/perl
print "Content-type: text/html\n\n";
print "<html><body><h1>Hello World!";
print "</h1></body></html>\n";
Simple concept -- the program executes, and the output is
sent to the browser that called it.
Perl – a simple counter
#! /usr/bin/perl
open (INPUT,”count.txt”);
@inline= <INPUT>;
$count = $inline[0] + 1;
close INPUT;
open (OUT,”>count.txt”);
print OUT “$count\n”;
close OUT;
print "Content-type: text/html\n\n";
print "<html><body>”;
print “<h1>Let’s Count! "</h1>";
print “This page accessed $count times<p>”;
print “</body></html>\n";
Perl – Basic syntax
Perl statements end in a semi-colon:
Comments start with a hash symbol and
run to the end of the line
Whitespace is irrelevant:
Perl – Basic syntax (cont.)
Variable types:
◦ Scalars:
◦ Arrays:
Perl – Basic syntax (cont.)
Variable types:
◦ Arrays:
Perl – Basic syntax (cont.)
Variable types:
◦ Hashes:
Perl – Basic syntax (cont.)
Variable scoping:
Conditional constructs:
Perl – Basic syntax (cont.)
Conditional constructs:
While:
Perl – Basic syntax (cont.)
for:
foreach:
Perl – Basic syntax (cont.)
Operators:
◦ Arithmetic:
◦ Numeric comparison:
Perl – Basic syntax (cont.)
Operators:
◦ String comparison:
◦ Boolean logic:
Perl – Basic syntax (cont.)
Files and IO:
Perl – Basic syntax (cont.)
String matching:
Perl – Basic syntax (cont.)
String matching:
Perl – Basic syntax (cont.)
Subroutines:
CGI environment variables (%ENV)
CGI environment variables (%ENV)
example- Printing environment variables:
#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser
fatalsToBrowser);
print header;
print start_html("Environment");
foreach my $key (sort(keys(%ENV))) {
print "$key = $ENV{$key}<br>\n";
}
print end_html;
CGI environment variables (%ENV)
Example- Referrer:
#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser
fatalsToBrowser);
print header;
print start_html("Referring Page");
print "Welcome, I see you've just come
from $ENV{HTTP_REFERER}!<p>\n";
print end_html;
CGI environment variables (%ENV)
Example- Browser detection:
#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
print start_html("Browser Detect");
my($ua) = $ENV{HTTP_USER_AGENT};
print "User-agent: $ua<p>\n";
if (index($ua, "MSIE") > -1) {
print "Your browser is Internet Explorer.<p>\n";
} elsif (index($ua, "Netscape") > -1) {
print "Your browser is Netscape.<p>\n"; }
elsif (index($ua, "Safari") > -1) {
print "Your browser is Safari.<p>\n"; }
elsif (index($ua, "Opera") > -1) {
print "Your browser is Opera.<p>\n";
} elsif (index($ua, "Mozilla") > -1) {
print "Your browser is probably Mozilla.<p>\n";
} else { print "I give up, I can't tell what browser you're using!<p>\n"; }
print end_html;
Form processing
Form processing (cont.)
#!/usr/bin/perl
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser
fatalsToBrowser);
use strict;
print header;
print start_html("Thank You");
print h2("Thank You");
my %form;
foreach my $p (param()) {
$form{$p} = param($p);
print "$p = $form{$p}<br>\n";
}
print end_html;
Form processing (cont.)
#!/usr/bin/perl
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use strict;
print header;
print start_html("Results");
# Set the PATH environment variable to the same path
# where sendmail is located:
$ENV{PATH} = "/usr/sbin";
# open the pipe to sendmail
open (MAIL, "|/usr/sbin/sendmail -oi -t") or
&dienice("Can't fork for sendmail: $!\n");
# change this to your own e-mail address
my $recipient = '[email protected]';
Form processing (cont.)
# Start printing the mail headers
# You must specify who it's to, or it won't be
delivered:
print MAIL "To: $recipient\n";
# From should probably be the webserver.
print MAIL "From: nobody\@cgi101.com\n";
# print a subject line so you know it's from your
form cgi.
print MAIL "Subject: Form Data\n\n";
# Now print the body of your mail message.
foreach my $p (param()) {
print MAIL "$p = ", param($p), "\n";
}
# Be sure to close the MAIL input stream so that the
# message actually gets mailed.
close(MAIL);
Form processing (cont.)
# Now print a thank-you page
print <<EndHTML;
<h2>Thank You</h2>
<p>Thank you for writing!</p>
<p>Return to our <a href="index.html">home
page</a>.</p>
EndHTML
print end_html;
# The dienice subroutine handles errors.
sub dienice {
my($errmsg) = @_;
print "<h2>Error</h2>\n";
print "<p>$errmsg</p>\n";
print end_html; exit;
}
Setting cookies
#!/usr/bin/perl
use strict;
my $cid = int(rand(1000000));
print "Set-Cookie: NAME=$cid\n";
print "Content-type: text/html\n\n";
print <<EndOfHTML;
<html><head><title>Welcome</title></he
ad> <body> <h2>Welcome!</h2> Your
cookie is $cid.<p> </body></html>
EndOfHTML ;
Reading cookies
#!/usr/bin/perl
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser
fatalsToBrowser);
use strict;
print header();
print start_html("Cookie");
print h2("Welcome!");
if (my $cookie = cookie('mycookie')) {
print "Your cookie is $cookie.<br>";
} else {
print "You don't have a cookie named
`mycookie'.<br>";
}
print end_html;
References
http://httpd.apache.org/docs/1.3/howto/ssi
.html
http://learn.perl.org/
http://www.stanford.edu/class/cs193i/hand
outsSum2004/21CGI.pdf
http://www.stanford.edu/class/cs193i/hand
outsSum2004/22CGI2.pdf
http://www.stanford.edu/class/cs193i/hand
outsSum2004/23CGI3.pdf
LAMP
What is LAMP?
LAMP refers to a set of tools:
◦
◦
◦
◦
Linux
Apache
MySQL
PHP
It allows for rapid deployment of software
applications
It can be defined as Open Source platform
We have already discussed on Linux and Apache
We should talk more about PHP and MySQL
PHP overview
Open Source server-side scripting language designed specifically for
the web.
In-line scripting
Conceived in 1994, now used on +10 million web sites. Now in
version 5.0
Outputs not only HTML but can output XML, images (JPG & PNG),
PDF files and even Flash movies (using libswf and Ming) all
generated on the fly. Can write these files to the filesystem.
Supports a wide-range of databases (inherently or via ODBC).
PHP also has support for talking to other services using protocols
such as LDAP, IMAP, SNMP, POP3, HTTP.
Supports OO programming
Perl- and C-like syntax. Relatively easy to learn.
Website @ http://www.php.net/
Why use PHP
If you like free software or need a free solution
If you need a solution that’s portable across multiple platforms
(e.g. Red Hat Linux to Windows 2000)
If you want to add dynamic content to your pages
If you want to make your pages easier to maintain
There are a lot of open source/free packages/libraries available in
PHP.
Many mailing lists/sites are dedicated to it.
Examples of uses of PHP :
◦ Small/Medium Portals
◦ Small/Medium Web-Mails
◦ Content Management
What is in a php file
PHP files may contain text, HTML tags and scripts
PHP files are returned to the browser as plain HTML
PHP files have a file extension of ".php", ".php3", or “.phtml“
Embedding PHP in HTML:
<html>
<body>
<strong>Hello World!</strong><br />
<?
echo ‘This is a PHP introductory course!’;
?>
</body>
</html>
Include mechanism
<?php
include '../includes/header.html';
?>
<center>
content of your web page
</center>
<?php
include 'http://cs.ucy.ac.cy/php/footer.html';
?>
Content can be included from a local or remote
source via such protocols as HTTP, HTTPS, FTP,
and FTPS
Types
Scalar types
◦
◦
◦
◦
Boolean
Integer
Float
String
Compound types
◦ Array
◦ Object
Variables
Variables all start with a $
◦ Case-sensitive
◦ Must start with a letter or underscore, followed by
any number of letters, numbers, or
underscores
◦ Variables are not explicitly typed
Type of value is inferred on operator application
Uninitialized variables have value undef
◦ What undef means depends on context
Numeric context it is 0
String context it is empty string “”
Variables
To assign values to variables:
◦
◦
◦
◦
$foo = ‘bar’; Data Type: String
$foo = 1; Data Type: integer
$foo = 5.34; Data Type: float
$foo = array(“bar”,”united”); Data Type: Array
Data Types are automatically assigned though you can
force a data type by type casting. For example:
◦ $foo = ‘Hello’;
◦ $bar = (int)$foo;
◦ $bar now equals 0
Almost all variables are local (page). Global variables
include $_Session
Example
<html>
<body>
<p> <?php
$temperature = 5;
$conversionFactorC2K = 273;
print("$temperature °C");
echo " is ";
print($temperature+$conversionFactorC2K."°K"); ?>
</p>
</body>
</html>
Associative arrays
Java arrays are indexed by number, e.g. a[0]
PHP arrays can also be indexed by association,
e.g.:
$phone['mark']='3497';
$phone['ian']='3098';
$people=array_keys($phone);
foreach ($people as $person)
echo "$person is on ext
".$phone[$person]."<br />";
Arrays
Actually an ordered map
◦ So you can use it like a vector, hashtable,
dictionary, stack, queue etc.
May contain other arrays
◦ You could build trees with them
In fact, they can contain any PHP type
They are dynamic (their size is changing at
runtime)
Array Syntax
Creation with array()
<?php
$arr = array("foo" => "bar", 12 =>
true);
echo $arr["foo"];
echo $arr[12];
?>
Array of array
<?php
$arr = array(
"somearray" => array(6 => 5, 13 => 9,
"a" =>
42));
echo $arr["somearray"][6]; // 5
echo $arr["somearray"][13]; // 9
echo $arr["somearray"]["a"]; // 42
?>
Parameters
Pass by value is default
<?php
function addNothing($string) {
$string .= ‘with a cherry on top.’;
}
$dessert = ‘Ice cream ’;
addNothing($dessert);
echo $dessert;
?>
Parameters
By reference
<?php
function addTopping(&$string) {
$string .= ‘with a cherry on top.’;
}
$dessert = ‘Ice cream ’;
addTopping($dessert);
echo $dessert;
?>
Servers variable array
$_SERVER is an array containing information
such as
◦ Headers
◦ Paths
◦ Script locations
The entries in this array are created by the
webserver. There is no guarantee that every
webserver will provide any of these; servers
may omit some, or provide others
Servers variable array
Server variables
'argv'
◦ Array of arguments passed to the script.
When the script is run on the command line,
this gives C-style access to the command line
parameters. When called via the GET method,
this will contain the query string.
'argc'
◦ Contains the number of command line
parameters passed to the script (if run on the
command line).
Server variables
'REMOTE_ADDR'
◦ The IP address from which the user is viewing
the current page.
'REMOTE_HOST'
◦ The Host name from which the user is
viewing the current page.
'REMOTE_PORT'
◦ The port being used on the user's machine to
communicate with the web server.
HTML Forms
When a form is submitted to a PHP script, the
information from that form is automatically
made available to the script
◦ There’s a few ways to do this
◦ Example:
<form action="foo.php" method="POST">
Name: <input type="text"
name="username"><br>
Email: <input type="text" name="email"><br>
<input type="submit" name="submit"
value="Submit">
</form>
<html><body><p>
<?php
print $_POST['username'];
?>
</p></body></html>
$_GET
$_REQUEST
$_FILES: An associative array of items uploaded to the current
script via the HTTP POST method.
Session
$_SESSION
◦ An associative array containing session
variables available to the current script.
◦ A way to preserve certain data across
subsequent accesses
Loads of session handling functions
◦ name, lifetime, cache etc.
Session
The idea of a session is to track a user during a single session on a
web site. This enables customized web pages, single login during a
session, shopping cart applications, and tracking users behavior
Cryptographically generated to be a unique session id
Session ID is stored as a cookie on the client box or passed along
through URL's.
Session variable values are stored in the 'superglobal‘ associative
array '$_SESSION.'
The values are actually stored at the server and are accessed via
the session id from your cookie.
On the client side the session ID expires when connection is
broken.
Session handling example
Page 1
<?php
session_start();
$_SESSION[‘FName'] = $_Get[‘FName'];
$_SESSION[‘LName'] = $_Get[‘LName'];
include '../includes/header.html';
?>
Page 2
<?php
session_start();
echo $_SESSION[‘FName'] . “ “ . $_SESSION[‘LName'];
?>
Cookies
Cookies are little text file that a web site stores
in the client’s computer to maintain information
about that client
Cookies are sent along with the rest of the
HTTP headers
Like other headers, cookies must be sent before
any output from your script (this is a protocol
restriction).
This requires that you place calls to this
function prior to any output, including <html>
and <head> tags
Cookies
Setting a cookie
◦ setcookie(“TestCookie", “lng=en”);
Setting a cookie with expiration
◦ setcookie("TestCookie", “lng=en”, time()+3600); /*
expire in 1 hour */
Access and print a cookie
◦ echo $_COOKIE[‘TestCookie’]
Delete a cookie
◦ setcookie ("TestCookie", "", time() - 3600);
◦ set the expiration time to an hour ago
PHP and MySQL
PHP and MySQL are a perfect companion
Largely because they are both free and
they have numerous capabilities
PHP as of version 3 supports inherently
MySQL i.e. specialized build-in functions
handle the database interactions
Same goes with ORACLE but not with
Microsoft databases (Access, SQL Server)
Example
<html>
<body>
<h1>A List of Users Who Have Signed Up For ….</h1>
<?
$dbh = mysql_connect("localhost",“dbusername",“dbpassword")
or die(“Couldn't connect to database.");
$db = mysql_select_db(“dbname", $dbh)
or die(“Couldn't select database.");
$sql = “SELECT username, email FROM userspool”;
$result = mysql_query($sql)
or die(“Something is wrong with your SQL statement.");
while ($row = mysql_fetch_array($result)) {
$username = $row[‘username’];
$email = $row[‘email’];
echo ‘<a href=“mailto:’.$email.’”>’.$username.’</a><br />’;
}
?>
</body>
</html>
MySQL
What is MySQL?
SQL – Structured Query Language
MySQL is a open-source, multithreaded, multi-user, SQL (Structured
Query Language) relational database server
MySQL works on many different platforms—including FreeBSD, UNIX,
Linux, Mac OS X, OS/2 Warp, Solaris, SunOS, SCO, Windows, and
other OS’s.¹
MySQL is used by companies like The Associated Press, Google, NASA,
Sabre Holdings, American Greetings, and Suzuki.²
71
Relational Database
Basically information organized in a structure.
Top level:
Database (Excel File)
-> Tables (Excel Sheet)
-> Columns and Rows
-> Data
Therefore data is organized into categories which can be stored and
retrieved in an efficient manner as long as you know where to look.
72
Programming Languages
Programming languages which can access MySQL
databases include: C, C++, Eiffel, Smalltalk, Java,
Lisp, Perl, PHP, Python, Ruby, and Tcl.²
You need to either compile in MySQL support when
installing these languages or have access to MySQL
libraries respective to the programming language (i.e.
libMySQL.dll)
Installation and Setup
Download installation package or source/rpms from www.mysql.com.
Run setup utility/install rpms/compile from source.
Setup administrator access using mysqladmin. This command allows you
to setup root access and run administrative commands from the
command prompt.
Login to MySQL daemon:
Windows: c:/mysql/bin/mysql –u username –p –h host
*nix: mysql –u username –p –h host
Use root user to add new users with privileges.
Setup Users
Privileges can be granted at four levels:
Global level
Global privileges apply to all databases on a given server. These privileges are
stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON
*.* grant and revoke only global privileges.
Database level
Database privileges apply to all tables in a given database. These privileges are
stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.*
and REVOKE ALL ON db_name.* grant and revoke only database
privileges.²
Table level
Table privileges apply to all columns in a given table. These privileges are
stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name
and REVOKE ALL ON db_name.tbl_name grant and revoke only table
privileges.
Column level
Column privileges apply to single columns in a given table. These privileges are
stored in the mysql.columns_priv table. When using REVOKE, you must
specify the same columns that were granted.
Setup Users
So a command to grant user to database test:
GRANT ALL PRIVILEGES ON test.* TO
‘user’@'localhost' IDENTIFIED BY ‘password’;
Then we need to reload the privileges:
FLUSH PRIVILEGES;
Thus ‘user’ may access the MySQL database from
‘localhost’ (server) on all tables in the ‘test’ database.
mysql –u user –p
password: password
Creating Databases
Syntax:
create database databasename;
create database library;
Then we can see the database:
show databases;
+-------------------+
| library
|
| test
|
+-------------------+
Then we need to access/use the databse:
use library;
Creating Tables
Syntax:
CREATE TABLE tablename (
column1 attributes,
column2 attributes,
column3 attributes);
CREATE TABLE books (
book_id INT(4) PRIMARY KEY NOT NULL
AUTO_INCREMENT UNSIGNED,
title VARCHAR(255),
author VARCHAR(255),
location VARCHAR(32),
ISBN VARCHAR(16),
quantity INT DEFAULT ‘0’);
Creating Tables Cont…
We can then see what tables are in a database:
Show tables;
+-------------------+
| Tables_in_library |
+-------------------+
| books
|
+-------------------+
Then we can see what columns are in a table.
DESCRIBE (DESC) tablename;
DESC TABLE books;
+----------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+----------+------------------+------+-----+---------+----------------+
| book_id
| int(4) unsigned
|
| PRI | NULL
| auto_increment |
| title
| varchar(255)
| YES
|
| NULL
|
|
| author
| varchar(255)
| YES
|
| NULL
|
|
| location | varchar(32)
| YES
|
| NULL
|
|
| ISBN
| YES
|
| NULL
|
|
| YES
|
| 0
|
|
| varchar(16)
| quantity | int(11)
+----------+------------------+------+-----+---------+----------------+
Insert Rows
Syntax:
INSERT (IGNORE) INTO tablename (columns to insert)
VALUES (values to insert);
INSERT INTO books (title, author, location) VALUES ("The
Hobbit", "JRR Tolkien", “F Tolkien, J”);
INSERT INTO books (title, author, location, ISBN,
quantity) VALUES (“Windows XP", “William Gates",
“115.4”, “1282105242142943”, 1);
Notice I did not specify a book_id, this is because it is an auto
incrementing row. Therefore The Hobbit will be book_id 1, and
Windows XP will be book_id 2.
Selecting Rows
Syntax:
SELECT columns FROM tablename WHERE condition
SELECT title, author FROM books WHERE book_id = 1;
+------------+-------------+
| title
| author
|
+------------+-------------+
| The Hobbit | JRR Tolkien |
+------------+-------------+
If you do not specify a WHERE clause, it will select EVERYTHING:
SELECT title, author FROM books;
+------------+---------------+
| title
| author
|
+------------+---------------+
| The Hobbit | JRR Tolkien
|
| Windows XP | William Gates |
+------------+---------------+
Selecting Rows Cont…
If you want to rename the returned column, use an as:
SELECT title as what, author as who FROM books;
+------------+---------------+
| what
| who
|
+------------+---------------+
| The Hobbit | JRR Tolkien
|
| Windows XP | William Gates |
+------------+---------------+
The wildcard ‘*’ can be used to select all columns:
SELECT * FROM books;
+---------+------------+---------------+--------------+------------------+----------+
| book_id | title
| author
| location
| ISBN
| quantity |
+---------+------------+---------------+--------------+------------------+----------+
| 1
| The Hobbit | JRR Tolkien
| F Tolkien, J | NULL
| 2
| Windows XP | William Gates | 115.4
| 0
|
| 1282105242142943 | 1
|
+---------+------------+---------------+--------------+------------------+----------+
ISBN is NULL for The Hobbit since we did not specify, and the quantity is
0 since that is the default value we set.
Selecting Count
Count is a special syntax which returns a numerical amount of a select
statement:
SELECT COUNT(*) FROM books;
+----------+
| COUNT(*) |
+----------+
| 2
|
+----------+
Distinct is another syntax which returns unique values of a select
statement:
SELECT DISTINCT author FROM books;
Updating Rows
Updating rows changes values of data within rows.
Syntax:
UPDATE tablename SET attribute1 = value1, attribue2 =
value2 WHERE conditions;
UPDATE books SET quantity = 5 WHERE book_id = 1;
SELECT * FROM books WHERE book_id = 1;
+---------+------------+-------------+--------------+------+----------+
| book_id | title
| author
| location
| ISBN | quantity |
+---------+------------+-------------+--------------+------+----------+
| 1
| The Hobbit | JRR Tolkien | F Tolkien, J | NULL | 5
|
+---------+------------+-------------+--------------+------+----------+
WARNING! Be careful of update statements and to specify a WHERE
clause. Without a WHERE clause, all rows will be updated with
quantity of 5.
Deleting Rows
Syntax:
DELETE FROM tablename WHERE conditions;
DELETE FROM books WHERE book_id = 2;
SELECT * FROM books;
+---------+------------+-------------+--------------+------+----------+
| book_id | title
| author
| location
| ISBN | quantity |
+---------+------------+-------------+--------------+------+----------+
| 1
| The Hobbit | JRR Tolkien | F Tolkien, J | NULL | 5
|
+---------+------------+-------------+--------------+------+----------+
WARNING! Be careful of delete statements and to specify a WHERE
clause. Without a WHERE clause, all rows will be deleted. If you
need to delete all the contents of a table:
DELETE FROM books;
However, this does not reset the indexes of a table, therefore a better
command is:
TRUNCATE books;
Where Clauses
Where clauses are very important to select, update, and delete
statements.
Where clauses can be in multiple formats and contain multiple
parameters:
WHERE
WHERE
= 0;
WHERE
WHERE
number (NOT) IN (0,1,2);
(book_id = 1 AND ISBN IS (NOT) NULL) OR
location
(date > 10212004) AND (date < 11222004);
(quantity + 2) = 4;
Where clauses can be quite long and complex, as the programmer you
need to know how to read them as well as script them.
Altering Tables
Altering tables enables you to change the structure of a table, whether it
be changing default values to removing or adding columns.
ALTER (IGNORE) TABLE tablename alter_specification;
ALTER TABLE books ADD date timestamp;
ALTER TABLE books RENAME catalog;
ALTER TABLE books MODIFY author INT(11) NOT NULL;
ALTER TABLE books CHANGE ISBN price float;
ALTER TABLE books DROP COLUMN quantity;
Drop Statements
Drop syntax deletes entire tables or databases.
DROP TABLE tablename;
DROP TABLE books;
DROP databasename;
DROP library;
This is for deleting databases and tables, not just clearing the entries!
More on Selects
Select Statements can be used for multiple tables:
Example:
SELECT author_id FROM authors WHERE lastname LIKE
“%Rawling%”;
+-----------+
| author_id |
+-----------+
| 1029
|
+-----------+
SELECT title FROM books WHERE author_id = 1029;
+-----------------------------------------+
| Title
|
+-----------------------------------------+
| Harry Potter and the Sorcerer's Stone
|
| Harry Potter and the Chamber of Secrets |
+-----------------------------------------+
More on Selects
Instead of doing two queries, combine them:
SELECT b.title FROM authors a, books b WHERE
b.author_id = a.author_id AND a.lastname LIKE
“%Rawling%”;
+-----------------------------------------+
| Title
|
+-----------------------------------------+
| Harry Potter and the Sorcerer's Stone
|
| Harry Potter and the Chamber of Secrets |
+-----------------------------------------+
Even better, we can use a ‘left join’:
SELECT b.title FROM books LEFT JOIN authors ON
b.author_id = a.author_id WHERE a.lastname LIKE
“%Rawling%”;
Join’s are generally better.
This can also be applied to multiple UPDATES and DELETES.
Indexes
What are indexes?
When talking about databases, indexing is a technique used by most
current database management systems to speed up particular
kinds of queries (usually by internally generating and storing
redundant information to more quickly locate table entries).
For integers, strings, and text, this is invaluable! Especially if the size of
the table is large!
Creating Indexes
Syntax:
CREATE (UNIQUE|FULLTEXT|SPATIAL) INDEX index_name
(index_type) ON tablename (column1,column2,…);
CREATE INDEX isbn_index ON books (ISBN);
You can specify a certain amount to index on, especially helpful with
strings:
CREATE INDEX title_index ON books (title(11));
Unique indexes help prevent duplicate entries:
CREATE UNIQUE INDEX author_lastname ON author
(lastname(11));
You can assign an index to multiple columns to prevent duplicate entries:
CREATE UNIQUE INDEX author_ref ON books (title,
authorid);
Creating Tables With Indexes
Syntax:
CREATE TABLE tablename (
column1 attributes,…
KEY index_name (column(size))
);
CREATE TABLE books (
book_id INT(4) PRIMARY KEY NOT NULL
AUTO_INCREMENT UNSIGNED,
title VARCHAR(255),
author VARCHAR(255),
KEY title_index (title(11)),
KEY author_index (author(11)),
);
Note: book_id does not need an index since primary key attribute takes
care of it.
Dropping Indexes
Syntax:
DROP INDEX index_name ON tablename;
DROP INDEX author_index ON books;
MySQLdump
MySQLdump is a command which dumps defined contests of a database. This is
handy in terms of backups:
Syntax:
mysqldump –u username –p (databasename) (tablename)
Useful flags:
-A|--all-databases – dumps all databases
-a|--all – same as previous
-c|--complete-insert – use complete insert statements
-e|--extended-insert – use extended insert statements
--add-drop-table – add a drop table before each create table syntax
-h|--host – define a host
-n|--no-create-db – do not specify a create database command
-d|--no-data – do not include the data
-P|--port
--tables
MySQLdump
A method of backing up a entire database in *nix is:
mysqldump –u username –p database > date.sql
Mysqldump exports to the console screen, so it is useful to pipe it into a
file, no idea how to do this in MSDOS.
There are two ways to restore a backup to mysql:
1. Pipe data back into mysql server:
mysql –u username –p databasename < file
mysql –u user –p library < date.sql
2. Source data from within mysql server:
source filename
source date.sql
References
http://php.net
http://mysql.com
http://www.wikipedia.net
http://www.mysql.com
https://ims.ecn.purdue.edu/docu
mentation.html