MySQL Intro

 

 

 

MySQL is a popular, open source, DBMS that is free for individual use and at low cost for commercial applications.  PHP is a popular, open source, server-side scripting language. ebus202.info has MySQL & PHP installed and provides the EBUS202 database for an introduction.

Here is the web page with the examples used in class to introduce DBMSs that use SQL, Structured Query Language.  The fictitious records are for a fictitious organization, the Southeast Society for the Preservation of Preservation.

This introduction first uses MySQL at the command line to get a feel for how SQL works.  It will be followed by exercises using MySQL in PHP scripts for the command line.  Then, the same EBUS202 database is used to introduce PHP used for 'web programming' with HTML GET data and POST data in FORMS. 

Here are the web pages for SESPoP.  They demonstrate using HTTP FORMS with GET & POST data from a web page that provides 'active content' from the EBUS202 database.  The scripts for this exercises are located in /home/SeSPoP/Web and they can be copied to your /home/yourname/web/ directory, edited into your website and styled using your external css. 

Stick to the 'GET Data Demo' for the time-being, using the two files /home/SeSPoP/Web/LinkToReports.html and /home/SeSPoP/Web/DonationReports.php.  

The presentation on the board and introductory lab exercise covers these topics:

database, table, columns, rows, & primary key
data types: integer, varchar, decimal, and autoincremented key
the select statement and its clauses: from, where, group by, order by, desc, & limit
the sum & count operators return the sum and count for a column, or can be used with group by to make 'subtotals'.
the like operator in the where clause is one way to make substring comparisons --  where Amount like '%.95'  will get Amounts ending with .95, the '%' is a 'wildcard', matches anything before the .95.
joining tables to get RegionName and VolunteerName from their tables using the foreign keys stored in Donations: where Regions.Id = RegionId   -- this is a simpler way to get the 'default join', INNER JOIN, used in the text in Figure 22.22...

After the short lecture/demo, the lab assignment is to log into mysql and make queries to answer these questions:

Which are the top Regions in amount donated?
Who are the top 10 Donors?
Who are the top Volunteers in amount they collected?
Who gets a tee shirt?  (These donors gave an amount greater than $20, ending with 95 cents)