Wednesday, January 17, 2007


NELINET course: Introduction to MySQL , taught by Ed Sperr

Most steps and examples on handout. Commands should work in all versions of SQL, including MS-SQL (i.e., in Access).

Starting with MySQL 5.0 command line client, enter password, then:

show databases; [all commands end with semi-colons]

Then, start Apache 2 (here, activate dormant icon on system tray.)

Then, browser address bar: localhost/pma to get php myadmin, allows us (like command line) to talk with backend database. This serves as GUI interface, almost OPAC-like, i.e. "using Web browser to make request to apache server".

USE databases; (or Use wikidb;, etc.)

[here: plus sign denotes mysql command prompt]

UP ARROW show earlier commands
+show databases;
+drop ....; [actually deletes the thing from harddrive]

Next step: create our own db:
+create database mylibrary;
+use mylibrary;
+create table characters (name varchar(30),occupation varchar(20));

+show tables;
+describe characters;

Or use phpmyadmin, refresh, select mothergoos database on left column. Data dictionary shows schema of table.

So far table is defined, but still empty.

+INSERT INTO table_name (col_name,...) VALUES (value,...)

+INSERT INTO characters (name, occupation) VALUES ("Mary", "shepherdess");

Computer response: Query OK, one row affected.

To retrieve data from table ("if there's one thing you should take away from this class ...") :

+SELECT select_expr FROM table_name

New example
+create database mylibrary;
+use mylibrary;
We're going to store:
Title (as "title")'
Publication Information (as "pubinfo")
Author's last name (as "lname")
first name (as "fname")
Authors Dates (as "adates")
Publication Date (as "pdate")

Hint: CREATE TABLE table_name(field_name data_type(length),...)

+CREATE TABLE books (title VARCHAR(255), pubinfo VARCHAR(255), lname VARCHAR(255), fname VARCHAR(255), adates CHAR(9), pdate CHAR(4));

If mysql prompt disappears, try several carriage returns, but may need to close and reopen command line client.

+insert into books (title, pubinfo, lname, fname, adates, pdate) values ("Moby Dick, or, the Whale", "New York; London: Penguin, 2003.", "Melville", "Herman", "1819-1891", "2003");

Remember that double quotes escapes all the characters within, including single quote. Backslash is another escape character

Inserting records from comma and tab delimited text files.-->
+load data infile 'T:/classfiles/mysql/books.txt' into table books;

+select * from books; [to retrieve all data]

MySQL 5.0 manual

Make sure fields are put in the right order, so: "select title [etc.] from books;" ("'Select' is bread and butter command") Much better display in phpadmin, selecting db, then sql tab, then "sel * from books" [this produces cool OPAC-like display]

New topic: "Aggregate functions", math-oriented transactions,

e.g., SELECT select_expr, agg_func(expr) FROM table_name GROUP BY field

GROUP BY means: grouping like things together, again easier to see in phpmyadmin. What if we wanted to count number of books by each author. E.g., everything written by S. Clemens ...

SELECT fname, lname, COUNT (title) FROM books GROUP BY lname;

Put in alphabetical order?

  • ORDER by expr

  • SELECT fname, lname, COUNT (title) FROM books GROUP BY lname ORDER by lname;

WHERE Clauses

  • SELECT select_expr

  • FROM table

  • ex.: SELECT lname, fname, title FROM books WHERE lname = "Twain";

  • What copies/versions of Scarlet Letter to we have?.: SELECT * FROM books WHERE title LIKE "%Scarlet Letter%"; will pull up all titles with the phrase "Scarlet Letter" in title field. % = wildcard. Backslash before would make the % literally read as a percentage sign.

  • Which nathaniel Hawthorne books were published after 1960? SELECT title FROM books ...

Relational Database

  • Common problem with single table db, is waste of space, redundancy of data values, increase risk of data entry error. E.g., address book, where almost everyone has "Main Library" and "567 Campus Rd.". So table one would have "Last name, First name, and Extension and Table Two would have Library and Library address. Need to add keys to relate them together, e.g., sequentially numbered rows.

How would you set somthing like this up if you're a collection deve librarian keeping track of vendor gifts and contacts?

  • CREATE TABLE vendors (vendor VARCHAR (100), ADDRESS varchar(255), phone CHAR(14));

  • And another table for reps: CREATE TABLE reps (lname VARCHAR(50), fname VARCHAR(50), phone ChAR(14), gift VARCHAR(255));

  • Then, to save time: LOAD DATA infile 'T:/classfiles/mysql/vendors.txt' INTO TABLE vendors; LOAD DATA infile 'T:/classfiles/mysql/reps.txt' INTO TABLE reps;

  • To see all gifts brought by reps: Select * from reps;

  • To see all addresses of vendors: Select * from vendors;
  • To add field to tables: ALTER TABLE table_name [ADD/CHANGE/DROP] column_name column_definition;

  • to add numeric keys: e.g. ALTER TABLE vendors ADD id int; [int=integer, no need to specify field length]

  • To make it auto-increment, first remove previous: ALTER TABLE vendors DROP id; then ALTER TABLE vendors ADD id int not null auto_increment primary key FIRST;
  • SELECT * [everything] from vendors;

  • Another table: ALTER TABLE reps ADD vendor_id int;

  • DESCRIBE reps;

  • So Tom Stevens works for Ingram. Need to link tables. Ingram is record 2. So put 2 in vendor id slot for Stevens. Need to update table manually.: UPDATE table = name SET column_name-new_value_or_expression WHERE where_condition;
  • SO: UPDATE reps SET vendor_id = 2 WHERE lname = 'Stevens';

  • Then: SELECT * from reps; (now Rep Stevens has vendor ID of 2).

  • Add these: Jane Smith works for Baker and Taylor (1) Joan Drake works for YBBP (4); Vera Miles works for Rittenhouse (3)

  • Digression: If you need to change data type use ALTER TABLE: e.g., pubdate should have been data type 'year': ALTER TABLE books CHANGE pubdate pupdate year;

  • SELECT * FROM reps;

  • We now have relationship between two tables.

  • Which rep brought the pens? SELECT fname, lname FROM reps WHERE gift LIKE "%pens%";


SELECT select_expr FROM table 1 JOIN table2 ON (table1_field-table2_field) WHERE where_condition


SELECT vendor FROM vendors JOIN reps ON (id = vendor_id) WHERE gift LIKE "%pens%";


SELECT vendor FROM vendors, peps WHERE (id - vendor_id And gift LIKE '%pens%');

Add more data ...

INSERT INTO reps (lname, fname, phone, gift) VALUES ("Delor", "Jacques", "(617)525-8412", "Big wheel of stinky brie"); INSERT INTO reps (lname, fname, phone, gift, vendor_id) VALUES ("Sneed", "Shanna", "(508)522-1567", "Old ALA \"Read\" Posters", 3);

If I want to see all information about people who work at Rittenhouse: SELECT * FROM reps JOIN vendors ON (id = vendor_id) WHERE vendor = "Rittenhouse";

To see everybody: SELECT * FROM reps JOIN vendors ON (id = vendor_id);


But what about Jacques (who's currently unemployed)? SELECT * FROM reps LEFT JOIN vendors ON (id = vendor_id);

What happens with SELECT * FROM reps RIGHT JOIN vendors ON (id = vendor_id); Jacques doesn't show up because he doesn't appear in second table.

With authors spelled differently, another table for authority control.

To do this, get rid of Books table: DROP TABLE table_name;

New one:

CREATE TABLE books(id int primary key, title VARCHAR(255), pubinfo VARCHAR(100), author_id int);

CREATE TABLE authors (id int primary key, lname VARCHAR(255), fname VARCHAR(100), author_id int);

LOAD DATA INFILE 'T:/classfiles/mysql/authors.txt' INTO TABLE authors;


select * from authors, books where = author_id;

MySQL more secure than MS Access? Tools exist to convert one from the other. Another option: export as tab-delimited text file. Keep in mind that Access designed to be personal, part of MS Office, whereas MySQL built from bottom up for Web environment.

Grab apache, php, etc., files from XAMPP, not for production, but good for sandbox playing around. MySQL is backend silo where data live. Scripting langauges allow us to communicate with them e.g., PHP, PERL, Python, ColdFusion to auomate tcomman language,

PHP 11/15/06

"Hypertext pre-processor"

Helper applications PHP MyAdmin and MySQL Command line client. These are ways of communicating with black-box MySQL running in background. Today, more sophisticated way to do the same thing. Scripting language PHP instead of command line and MyAdmin. Why? Relatively simple. OSS. Best way to learn: look at code other people post on message boards. Designed from ground up specifically for building Web applications. Interpretated language, i.e., does not need to be compiled (e.g., C++), so changes show up immediately, instant gratification. Also: Iterative.

Static Web page = simple file request. PHP (installed, along with Apache, on Web server) mediates the request. documentation "is fabulous".

"Follow the bouncing ball" metaphor: "Hello World" [?php echo 'Hello World!; ?]

C:-->Program Files-->Apache Group-->Apache Group-->htdocs, where Apache installs itself.

Basic php command is "Echo". Similar to "Print" command. Followed by argument: 'Hello World' . Result is no longer static text, but rather output of a compmand. "You have just gone through the rabbit hole (or looking glass)."

Variables (Or, PHP Madlibs). "Jack and [] went up the [] to fetch a [] of water ..." Could use this technique to collect input in Web form, then sent it to SQL as structured query, and return results as HTML output. Single quotes are literal, double quotes or no quotes are variable. Period stitches things together.

FYI: Scintilla seems like good text editor.

Loop command: Keep doing operation until given condition has been met. For loops establish set of starting conditions, est., what is to change, est. assessment for when conditions are met; this concept common to all programming languages. E.g.: ($i = 0; $i while ... uses same concept but structured differently, with php commands in separated code-blocks, says "Do this WHILE this is the case".

If statement: If variable favorite color is blue do what's in brackets, otherwise "else", i.e., do other bracketed echo (very similar to PERL command). Quotation marks around html attributes require backslash escape characters.

Date function: asks the system "What's the time?" There are lots of built-in PHP gfunctions; and one can create or call other functions.

multiplier.php file shows mathematical functions, and multiplier2.php shows for loop through 13. "Leveraging power of 'for-loop' to save ourselves time." See form: "multiform.html", interesting part is "form action=" in this case "from action 'multiback.php' method= 'get' "


Similar to variables, but convenient structure for storing multiple related values, require keys and values, where keys can be integers ("0", "1", "2", etc.) or names (e.g., "Title", "Author") ... Result of simple array looks like table with key in column one and value in column 2.

Easiest way to creat new database is to drop new data folder into MySQLData folder.

In order to use PHP to communicate with SQL db, need to do the following:
(1) connect to DB,
a. use built-in function called mysql connect: myql_connect(host, user name, password
b. mysql_select_db

(2) run query (pass sql query to a function),
a. mysql_query(sqlquery)

(3) retrieve the results,
a. mysql_fetch_assoc(resource_result) [fetches set of arrays reprenting rows of a database)]

(4) then format (parse) them for display (at which PHP really excels).


search_step1.php ...

beware sql injection attacks. Final example iteration shows safety feature.

No comments:

Post a Comment