Transcript MySQL and The PMA (PhpMyAdmin)

MySQL and phpMyAdmin
1
Navigate to http://webapptst.lasalle.edu/pma
and log on (username: pmadmin)
2
The pma (phpMyAdmin) interface
3
Use the drop-down list to select the test
database
The mysql database is
administrative, and we don’t have
permissions for it. There are even
more databases here that are not
shown to this user.
The (3) after test indicates
that currently the test
database contains three
tables.
4
The test database in the pma interface
We see in the first column above a list of the tables
in the test database, in the second column one finds
buttons allowing particular actions on those tables,
in the third column one can see the number of
records in the table. The remaining columns will be
of less interest to us as we begin.
5
Table Actions
1. The browse button will show that all of the data in the table. (One can also
update and delete the data here.)
2. The search button will set up a query based on that table.
3. The insert button allows one to add new records to the table.
4. The properties button allows one to the table’s structure (the metadata),
i.e. what are the fields, their types, how long can they be, etc.
5. The empty button allows one to delete all of the records in the table. Be
careful – it will give a little warning. (The table structure remains.)
6. The drop button allows one drop the table eliminating both data and
metadata.
6
Browse: Browse shows the data in the
ArtWork table
The arrows show a
few ways to get back
to the test database
page.
7
Search: Search gives one a Query-By-Example
interface to search the ArtWork table
8
Insert: Insert provides a place for a user to
enter data into the ArtWork table.
9
Properties: Properties displays the
design/structure of the ArtWork table.
10
Allows one to write SQL
queries for the test database.
11
12
Export ArtWork as CSV (comma-separated
varaiables)
While the name “comma separated variables” suggested the
fields should be separated (terminated) by a comma, there will
be a problem if the data itself contains commas. Try to choose
a delimiter that would not appear in the data.
13
Export ArtWork as CSV (Cont.)
14
CSV (actually semicolon-separated) file
in Notepad
15
Export ArtWork As XML
16
It does not open the XML
file, but to save it you can
right click, choose View
Source which will show the
XML file in Notepad and it
can be saved from there.
17
18
The result of the
search is a table
which you then chose
to browse.
The search is
accomplished by a
SQL statement, which
can be edited or
turned into PHP code.
19
This view provides a Query-By-Example interface. It
is like the interface we encountered when we clicked
on the search button associated with a table. But
this interface is better suited for doing queries that
involve multiple table (joins).
20
Return to the test database main page, click
on the search button next to the Artist table
Accepting the default settings
yields a simple query that
obtains all of the fields of all
of the records.
21
To choose only a few fields, hold down the control
key while clicking on the field names in the list.
One can “project” out only the
fields one wants to see.
22
One can “select” out records that satisfy a particular
condition, choose a comparison operator and enter a value.
23
The result page shows both the results of
and the SQL for the query.
SQL for query
Result of query
24
1.
2.
3.
We can click the Edit
link and change
aspects of the query.
25
When we start using PHP
pages to interact with the
database, we will need PHP
variables that correspond to
SQL queries.
PMA provides this for us.
26
We can produce a quick report
on the results of the query by
clicking on the Print view link.
The Export link leads to an
interface like that for exporting a
table.
27
Ascending and Descending
We can put the artists
in their birth order by
selecting that field and
choosing ascending (in
this case).
28
Greater than operator
Internally dates correspond to
numbers (not text) and
operators like “greater than or
equal to” make sense. The
difficulty is in knowing how the
particular interface likes to
format dates. Here we used a
year-month-day format.
29
Return to the test database main page and
then click on the Query button.
30
Use the drop-down list to select Artwork.* and Artist.* which means all
of the fields from both tables. Check that they should be displayed.
31
Cartesian Product: The result lists every possible pair of artwork and
artist regardless of whether the artwork was by the artist.
32
We create a “join” by selecting from the Cartesian product records in
which the ArtistID (primary key) from Artist and ArtistID from ArtWork
(foreign key) match.
33
Result of the join.
34
We can refine this
query by choosing
only the fields we want
to see (projection).
35
Result of “join” with projection.
36
We can additional selection conditions.
37
Even though this is a valid query, there is a limited amount of data and
the query produces zero records. (The interface could be a little nicer
at letting you know.)
38
Pre-existing data file
• Suppose we already have a data file, and
we do not want to enter the data using the
Insert feature (which will be shown later)
which allows us to enter data one record
at a time.
• Then we can import data.
• The first step is to examine the data file
and known its format.
39
A file containing data on members of the
House of Representatives in a csv file
CSV files can be opened
in Excel, though they are
just text files (and can be
open in Notepad as well)
40
Create a table (give it a unique name).
41
Enter fields – use order seen in
data file.
42
Table created
43
Scroll to bottom of page to find Insert
link.
44
Click Browse button and find data file.
45
Select delimiter information.
File was comma
delimited. Fields
were not “enclosed.”
46
Scroll down to submit button
47
Result page of import.
48
Click on table and on Browse button.
Slightly problem with first record.
49
Select offending record and click Delete
button.
50
Warning message about Delete, also
shows SQL for delete.
51
Result of Delete
52