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.
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
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
|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,
PRIMARY KEY (SampleId)
Erratta: for MariaDB make the last line
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
|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.|
SampleTable as an example, a statement like this will insert a new
insert into SampleTable set
EmailAddress = 'firstname.lastname@example.org', FirstName = 'G', LastName =
Since Id is an 'auto incremented' field it should
not be included in the insert statement. MySQL will
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
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
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
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
Debug the new script and show it to the instructor
when it's running.