More MySQL

 

For Advanced & Intrepid Atudents:

The MySQL Manual is available on-line.  Get your eyes on it and use it to look up the commands, data types, and other details about MySQL we cover in class.

The last two topics introduced some MySQL basics using the select statement.  For this topic, each student has their own database set up at ebus202 and the instructor will give you your mysql login, which is set the same as the linux login, and your mysql password, which is _not_ the same as the linux password.

If you want to add 'database administration' to your bag of tricks, plan to get MySQL on your own computer, there are versions for both Windoze & Linux. There you can practice with the administrative commands. Your mysql account at ebus202.info only grants access to your database and won't allow you to CREATE DATABASE, GRANT access privileges, or otherwise do 'database administration'. 

There will be a brief demo of 'MySQL Administration',  creating a new database and granting access to it for a new user.  And, an 'administrative script' will be demo'd as another example of structured programming, using PHP to create a database and MySQL login for each name on the roster.

Today's exercises are for each student to design create a table with several columns, not more than five or six, and use MySQL at the command line to insert and update records and make changes to the table structure.   

You _could_ start at the mysql prompt in your database and type in a long create table statement. But that is an error-prone approach and multi-lined SQL statements are clumsy. 

A pro edits the create table statement into a 'mysql script' and executes it from the command line.  That way, if an error has been keyed in it's easy to find & fix it without having to retype the whole, long command.  And, it's easy to copy a script to another file and edit it to make another table.

 

Make up a simple table to use later in this exercise.  You can use the following script, CreateSampleTable, as a guide.  Don't make more than three or four columns in the table since we'll be working at the command line to insert and update records and that will be burdensome with a larger table.

Some tables I've seen from the early starters are: States & sports teams; a record collection; SomeStuff; and other 'name and address' tables similar to the SampleTable.

Show your new table to the instructor for approval.

 

Here are the steps for the first part of the exercise, creating a new table in your database using a script:

/home/gschool/CreateSampleTable is provided for use as a 'template' that can be edited to make a 'table creating script'.  Here are its contents:

# MySQL dump 8.14
# Table structure for table 'Sample'
#
CREATE TABLE Sample (
  SampleId mediumint unsigned NOT NULL auto_increment,
  EmailAddress varchar(60) default NULL,
  FirstName varchar(25) default NULL,
  LastName varchar(45) default NULL,
  Notes text default NULL,
  DateTimeCreated timestamp,
  PRIMARY KEY  (SampleId)
) TYPE=MyISAM;

Erratta: for MariaDB make the last line

) engine=Aria;

It demonstrates MySQL's CREATE TABLE command, where the columns to create and information about the records' KEY are enclosed in parentheses after the table name. 

Id is an auto-incremented integer that is incremented (by one) each time a new row is added to the table.  It will be used as the records' key so NOT NULL is appropriate.

The other rows use 'default NULL' to store NULL (nothing, never been defined) if no value is assigned for the row when a new record is entered.

The varchar data type allows a column to hold a string as long as the number in parentheses. 

A text field can hold 'free text' up to a length of 65,536 characters. 

A PRIMARY KEY must be nominated, and the auto-incremented Id is built for use as a primary key.

Notice that this is _one_ long SQL statement that ends with ;.

 

Use mc or otherwise copy /home/gschool/CreateSampleTable to your home directory.

The script _can_ be run from the MySQL command line, but here is syntax for running it from the linux command line.  This script doesn't need to be in your bin directory since it's not 'executable by linux'.  Instead, the script is 'passed into mysql' using the < operator on a linux command line, like this:

mysql -ugschool -pmypassword gschool < CreateSampleTable

If the command executes and gives an error message you'll have to debug the script. 

(It might be helpful to open two putty sessions, one for the linux command line and the other for mysql.  That way it's easier to _see_ what's going on.)

After you've made CreateSampleTable work there will be a new table in your database.  Get into your mysql database and use show tables;... SampleTable should appear.

Get rid of SampleTable using drop table SampleTable.

 

Next, copy CreateSampleTable to another file in your home directory and edit it to create the small, new table your instructor has approved. 

Use the above syntax at the linux command line to create the new table in your database. 

Get into mysql and make sure show tables; shows your new table.

 

Add some records to your new table using mysql's insert into statement.

Using SampleTable as an example, a statement like this will insert a new record:

insert into SampleTable set EmailAddress = 'gsx@some.net', FirstName = 'G', LastName = 'Student'; 

Since Id is an 'auto incremented' field it should not be included in the insert statement.  MySQL will automatically assign

Un-mentioned fields are left with NULL data.

The DateTimeCreated field will contain the data and time the new record was inserted.

You can quickly add a few records by using the up arrow to recall the prior insert, then use the right and left arrows to move around in the statement to change the data.  Hit Enter when you're done editing the next new record.

Use select * from NewTable (using your new table's name) to see the records that have been inserted.

 

Delete a record or two using mysql's delete from statement.  Be careful using this statement since typing 'delete from NewTable' without a where clause will delete _all_ the records!  So make sure to include the where clause before you hit Enter!

This statement will delete one record with an Id of 3:

delete from NewTable where Id=3;

 

Changing columns' names or data type, 'dropping' a column, or adding new columns are done using MySQL's alter table statement.

To add a new column named Street after the LastName column in the Sample table above:

    alter table Sample add column Street varchar(50) after LastName;

To drop a column:

    alter table Sample drop column EmailAddress;

To change a column's name:

    alter table Sample change column Street StreetAddress

To change a column's data type:

    alter table Sample change column StreetAddress StreetAddress(60);

 

Make some select statements with your new table and sample data.

 

Design a PHP program, similar to SESPoPRpts.php, that will use your new table instead of the tables in the EBUS202 database.  It should allow the User to select from at least two reports.

Sketch structured notation for this new program.  Have the instructor approve your design.

 

Edit the new PHP script.  You're welcome to use Midnight Commander's F9-File copy to File & Insert file functions to get parts from your copy of SESPoPRpts into the new file.  

Debug the new script and show it to the instructor when it's running.

 

Hit Counter