Internet Engineering Course - PowerPoint PPT Presentation

1 / 97
About This Presentation
Title:

Internet Engineering Course

Description:

use CGI::Carp qw(warningsToBrowser fatalsToBrowser); print header; ... use CGI::Carp qw(warningsToBrowser fatalsToBrowser); use strict; print header; ... – PowerPoint PPT presentation

Number of Views:170
Avg rating:3.0/5.0
Slides: 98
Provided by: ZhiLi7
Category:

less

Transcript and Presenter's Notes

Title: Internet Engineering Course


1
Internet Engineering Course
  • Web Application Development

2
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

3
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

4
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.

5
SSI
  • SSI directives have the following syntax
  • lt!--element attributevalue attributevalue ...
    --gt
  • It is formatted like an HTML comment
  • so if you don't have SSI correctly enabled, the
    browser will ignore it

6
SSI examples
  • Commonly used to obtain and display environment
    variables from the OS
  • lt!--echo var"DATE_LOCAL" --gt
  • Modification date of the file
  • This document last modified
  • lt!--flastmod file"index.html" --gt
  • Including the results of a CGI program
  • lt!--include virtual"/cgi-bin/counter.pl" --gt
  • Including a standard footer
  • lt!--include virtual"/footer.html" --gt

7
SSI examples (cont.)
  • Executing commands
  • ltpregt lt!--exec cmd"ls" --gt lt/pregt
  • Conditional expressions
  • lt!--if expr"Mac InternetExplorer" --gt
  • Apologetic text goes here
  • lt!--else --gt Cool JavaScript code goes here
  • lt!--endif --gt

8
SSI conclusion
  • Minimize code duplications
  • SSI for site management
  • Creates dynamic content
  • Can be a security problem

9
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 gt 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.

10
CGI
Request service
Run CGI program print result
HEADERS BODY
11
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

12
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, ltagt, as in
  • lta href ./cgi-bin/reply.cgigt" Click here to
    run the CGI program, reply.pl lt/agt

13
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.

14
Perl a simple example
  • Hello World in Perl
  • ! /usr/bin/perl
  • print "Content-type text/html\n\n"
  • print "lthtmlgtltbodygtlth1gtHello World!"
  • print "lt/h1gtlt/bodygtlt/htmlgt\n"
  • Simple concept -- the program executes, and the
    output is sent to the browser that called it.

15
Perl a simple counter
  • ! /usr/bin/perl
  • open (INPUT,count.txt)
  • _at_inline ltINPUTgt
  • count inline0 1
  • close INPUT
  • open (OUT,gtcount.txt)
  • print OUT count\n
  • close OUT
  • print "Content-type text/html\n\n"
  • print "lthtmlgtltbodygt
  • print lth1gtLets Count! "lt/h1gt"
  • print This page accessed count timesltpgt
  • print lt/bodygtlt/htmlgt\n"

16
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

17
Perl Basic syntax (cont.)
  • Variable types
  • Scalars
  • Arrays

18
Perl Basic syntax (cont.)
  • Variable types
  • Arrays

19
Perl Basic syntax (cont.)
  • Variable types
  • Hashes

20
Perl Basic syntax (cont.)
  • Variable scoping
  • Conditional constructs

21
Perl Basic syntax (cont.)
  • Conditional constructs
  • While

22
Perl Basic syntax (cont.)
  • for
  • foreach

23
Perl Basic syntax (cont.)
  • Operators
  • Arithmetic
  • Numeric comparison

24
Perl Basic syntax (cont.)
  • Operators
  • String comparison
  • Boolean logic

25
Perl Basic syntax (cont.)
  • Files and IO

26
Perl Basic syntax (cont.)
  • String matching

27
Perl Basic syntax (cont.)
  • String matching

28
Perl Basic syntax (cont.)
  • Subroutines

29
CGI environment variables (ENV)
30
CGI environment variables (ENV)
  • example- Printing environment variables
  • !/usr/bin/perl
  • use strict
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • print header
  • print start_html("Environment")
  • foreach my key (sort(keys(ENV)))
  • print "key ENVkeyltbrgt\n"
  • print end_html

31
CGI environment variables (ENV)
  • Example- Referrer
  • !/usr/bin/perl
  • use strict
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • print header
  • print start_html("Referring Page")
  • print "Welcome, I see you've just come from
    ENVHTTP_REFERER!ltpgt\n"
  • print end_html

32
CGI environment variables (ENV)
  • Example- Browser detection
  • !/usr/bin/perl
  • use strict
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • print start_html("Browser Detect")
  • my(ua) ENVHTTP_USER_AGENT
  • print "User-agent ualtpgt\n"
  • if (index(ua, "MSIE") gt -1)
  • print "Your browser is Internet Explorer.ltpgt\n"
  • elsif (index(ua, "Netscape") gt -1)
  • print "Your browser is Netscape.ltpgt\n"
  • elsif (index(ua, "Safari") gt -1)
  • print "Your browser is Safari.ltpgt\n"
  • elsif (index(ua, "Opera") gt -1)
  • print "Your browser is Opera.ltpgt\n"
  • elsif (index(ua, "Mozilla") gt -1)

33
Form processing
34
Form processing (cont.)
  • !/usr/bin/perl
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • use strict
  • print header
  • print start_html("Thank You")
  • print h2("Thank You")
  • my form
  • foreach my p (param())
  • formp param(p)
  • print "p formpltbrgt\n"
  • print end_html

35
Form processing (cont.)
  • !/usr/bin/perl
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • use strict
  • print header
  • print start_html("Results")
  • Set the PATH environment variable to the same
    path
  • where sendmail is located
  • ENVPATH "/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 'nullbox_at_cgi101.com'

36
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\_at_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)

37
Form processing (cont.)
  • Now print a thank-you page
  • print ltltEndHTML
  • lth2gtThank Yoult/h2gt
  • ltpgtThank you for writing!lt/pgt
  • ltpgtReturn to our lta href"index.html"gthome
    pagelt/agt.lt/pgt
  • EndHTML
  • print end_html
  • The dienice subroutine handles errors.
  • sub dienice
  • my(errmsg) _at__
  • print "lth2gtErrorlt/h2gt\n"
  • print "ltpgterrmsglt/pgt\n"
  • print end_html exit

38
Setting cookies
  • !/usr/bin/perl
  • use strict
  • my cid int(rand(1000000))
  • print "Set-Cookie NAMEcid\n"
  • print "Content-type text/html\n\n"
  • print ltltEndOfHTML lthtmlgtltheadgtlttitlegtWelcomelt/tit
    legtlt/headgt ltbodygt lth2gtWelcome!lt/h2gt Your cookie
    is cid.ltpgt lt/bodygtlt/htmlgt
  • EndOfHTML

39
Reading cookies
  • !/usr/bin/perl
  • use CGI qw(standard)
  • use CGICarp qw(warningsToBrowser
    fatalsToBrowser)
  • use strict
  • print header()
  • print start_html("Cookie")
  • print h2("Welcome!")
  • if (my cookie cookie('mycookie'))
  • print "Your cookie is cookie.ltbrgt"
  • else
  • print "You don't have a cookie named
    mycookie'.ltbrgt"
  • print end_html

40
References
  • http//httpd.apache.org/docs/1.3/howto/ssi.html
  • http//learn.perl.org/
  • http//www.stanford.edu/class/cs193i/handoutsSum20
    04/21CGI.pdf
  • http//www.stanford.edu/class/cs193i/handoutsSum20
    04/22CGI2.pdf
  • http//www.stanford.edu/class/cs193i/handoutsSum20
    04/23CGI3.pdf

41
LAMP
42
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

43
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 _at_ http//www.php.net/

44
Why use PHP
  • If you like free software or need a free solution
  • If you need a solution thats 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

45
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
  • lthtmlgt
  • ltbodygt
  • ltstronggtHello World!lt/stronggtltbr /gt
  • lt?
  • echo This is a PHP introductory course!
  • ?gt
  • lt/bodygt
  • lt/htmlgt

46
Include mechanism
  • lt?php
  • include '../includes/header.html'
  • ?gt
  • ltcentergt
  • content of your web page
  • lt/centergt
  • lt?php
  • include 'http//cs.ucy.ac.cy/php/footer.html'
  • ?gt
  • Content can be included from a local or remote
    source via such protocols as HTTP, HTTPS, FTP,
    and FTPS

47
Types
  • Scalar types
  • Boolean
  • Integer
  • Float
  • String
  • Compound types
  • Array
  • Object

48
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

49
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

50
Example
  • lthtmlgt
  • ltbodygt
  • ltpgt lt?php
  • temperature 5
  • conversionFactorC2K 273
  • print("temperature degC")
  • echo " is "
  • print(temperatureconversionFactorC2K."degK")
    ?gt
  • lt/pgt
  • lt/bodygt
  • lt/htmlgt

51
Associative arrays
  • Java arrays are indexed by number, e.g. a0
  • PHP arrays can also be indexed by association,
  • e.g.
  • phone'mark''3497'
  • phone'ian''3098'
  • peoplearray_keys(phone)
  • foreach (people as person)
  • echo "person is on ext ".phoneperson."ltbr
    /gt"

52
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)

53
Array Syntax
  • Creation with array()
  • lt?php
  • arr array("foo" gt "bar", 12 gt true)
  • echo arr"foo"
  • echo arr12
  • ?gt

54
Array of array
  • lt?php
  • arr array(
  • "somearray" gt array(6 gt 5, 13 gt 9, "a" gt
  • 42))
  • echo arr"somearray"6 // 5
  • echo arr"somearray"13 // 9
  • echo arr"somearray""a" // 42
  • ?gt

55
Parameters
  • Pass by value is default
  • lt?php
  • function addNothing(string)
  • string . with a cherry on top.
  • dessert Ice cream
  • addNothing(dessert)
  • echo dessert
  • ?gt

56
Parameters
  • By reference
  • lt?php
  • function addTopping(string)
  • string . with a cherry on top.
  • dessert Ice cream
  • addTopping(dessert)
  • echo dessert
  • ?gt

57
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

58
Servers variable array
59
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).

60
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.

61
HTML Forms
  • When a form is submitted to a PHP script, the
    information from that form is automatically made
    available to the script
  • Theres a few ways to do this
  • Example
  • ltform action"foo.php" method"POST"gt
  • Name ltinput type"text" name"username"gtltbrgt
  • Email ltinput type"text" name"email"gtltbrgt
  • ltinput type"submit" name"submit"
    value"Submit"gt
  • lt/formgt

62
  • lthtmlgtltbodygtltpgt
  • lt?php
  • print _POST'username'
  • ?gt
  • lt/pgtlt/bodygtlt/htmlgt
  • _GET
  • _REQUEST
  • _FILES An associative array of items uploaded
    to the current script via the HTTP POST method.

63
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.

64
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.

65
Session handling example
  • Page 1
  • lt?php
  • session_start()
  • _SESSIONFName' _GetFName'
  • _SESSIONLName' _GetLName'
  • include '../includes/header.html'
  • ?gt
  • Page 2
  • lt?php
  • session_start()
  • echo _SESSIONFName' . .
    _SESSIONLName'
  • ?gt

66
Cookies
  • Cookies are little text file that a web site
    stores in the clients 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 lthtmlgt
    and ltheadgt tags

67
Cookies
  • Setting a cookie
  • setcookie(TestCookie", lngen)
  • Setting a cookie with expiration
  • setcookie("TestCookie", lngen, time()3600)
    / expire in 1 hour /
  • Access and print a cookie
  • echo _COOKIETestCookie
  • Delete a cookie
  • setcookie ("TestCookie", "", time() - 3600)
  • set the expiration time to an hour ago

68
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)

69
Example
  • lthtmlgt
  • ltbodygt
  • lth1gtA List of Users Who Have Signed Up For
    .lt/h1gt
  • lt?
  • dbh mysql_connect("localhost",dbusername",dbp
    assword")
  • 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 rowusername
  • email rowemail
  • echo lta hrefmailto.email.gt.username.lt/
    agtltbr /gt
  • ?gt
  • lt/bodygt
  • lt/htmlgt

70
MySQL
71
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 platformsincluding
    FreeBSD, UNIX, Linux, Mac OS X, OS/2 Warp,
    Solaris, SunOS, SCO, Windows, and other OSs.¹
  • MySQL is used by companies like The Associated
    Press, Google, NASA, Sabre Holdings, American
    Greetings, and Suzuki.²

72
Relational Database
  • Basically information organized in a structure.
  • Top level
  • Database (Excel File)
  • -gt Tables (Excel Sheet)
  • -gt Columns and Rows
  • -gt 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.

73
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)

74
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.

75
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.

76
Setup Users
  • So a command to grant user to database test
  • GRANT ALL PRIVILEGES ON test. TO
    user_at_'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

77
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

78
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)

79
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 varchar(16) YES NULL

80
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.

81
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
  • ---------------------------

82
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 0
  • 2 Windows XP William Gates 115.4
    1282105242142943 1
  • ----------------------------------------------
    --------------------------------

83
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

84
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.

85
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

86
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 number (NOT) IN (0,1,2)
  • WHERE (book_id 1 AND ISBN IS (NOT) NULL) OR
    location 0
  • WHERE (date gt 10212004) AND (date lt 11222004)
  • WHERE (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.

87
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

88
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!

89
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
  • -----------------------------------------

90
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
  • Joins are generally better.
  • This can also be applied to multiple UPDATES and
    DELETES.

91
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!

92
Creating Indexes
  • Syntax
  • CREATE (UNIQUEFULLTEXTSPATIAL) 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)

93
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.

94
Dropping Indexes
  • Syntax
  • DROP INDEX index_name ON tablename
  • DROP INDEX author_index ON books

95
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

96
MySQLdump
  • A method of backing up a entire database in nix
    is
  • mysqldump u username p database gt 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 lt file
  • mysql u user p library lt date.sql
  • 2. Source data from within mysql server
  • source filename
  • source date.sql

97
References
  • http//php.net
  • http//mysql.com
  • http//www.wikipedia.net
  • http//www.mysql.com
  • https//ims.ecn.purdue.edu/documentation.html
Write a Comment
User Comments (0)
About PowerShow.com