MySQL commands can
be issued in a variety of ways
- interactively
- from keyboard in 'real-time' - the public
database portals you have used as part of this unit are examples
of ad-hoc querying.
- read in from
a text file - like batch processing
- embedded
in another language (eg. COBOL or Pascal) and accessed via ODBC
- scripted
in a server-side webpage. This is the most common form of access for
MySQL commands and typically combines standard queries that
are modified by values called parameters gleaned from the website
you are working with at the time like the current topic, point in
an index, key word and so on.
Commands that make
changes to structure and/or table content in database tables are made
permanent by COMMITting them. By default, MySQL databases are autocommitted
- changes are automatically written direct to the database. It is possible
to ROLLBACK to a previous database state like before a major change,
but this is messy. It is also possible and most often desireable to
LOCK tables or currently accessed rows in tables. Locking of records
or tables makes exclusive access possible, so when one person is making
changes another cannot edit the same record.
All table and database
details are stored in a repository called the Data Dictionary. MySQL
can be used to manage MANY relational databases. Each database can contain
MANY tables. The management information (or metadata) for ALL
databases is also stored in tables.
Convention - commands
are issued ONE CLAUSE PER LINE in the following form:
select alb_name, p_date
from albums
where artist = 'the cure'
and p_date = 1988
NOTE: each part
(called a clause) of the query is ON A NEW LINE - I have used LOWERCASE
for all/most of my querys throughout this section - don't get too creative
with CASE as it does matter in some instances (like table names etc)
Creating Things
In SQL, tables live
in databases. To access the tables by query, you must load the database.
When starting from scratch, you create the DB first (empty), then load
it, then create tables in the loaded DB space
eg. CREATE DATABASE army
Once a database
is created, you have to connect to it as a valid user with a password
(for the purpose of the exercises in this section, this has usually
been done for you). Any MySQL databases you have access to live on the
school server and so there are some necessary restrictions on what you
are able to do with them.
In the following
section, we will refer to an imagined ARMY database, as a collection
of 3 related tables:
Once inside the
database space, you are free to create tables.
The CREATE
TABLE syntax (do not be afraid) from the MySQL Reference
documentation is as follows (note, this has been simplified for you):
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(definition {, definition},...)]
[table_options] [select_statement] |
where:
definition
means:
col_name type [NOT
NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [REFERENCES tbl_name [(col_name {, col_name})]]
or PRIMARY KEY (col_name {, col_name})
or KEY [index_name] (col_name {, col_name})
or INDEX [index_name] (col_name {, col_name})
or UNIQUE [INDEX] [index_name] (col_name {, col_name})
or FULLTEXT [INDEX] [index_name] (col_name {, col_name})
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (col_name {, col_name})
[REFERENCES tbl_name [(col_name {, col_name})]]
or CHECK (expr)
and type means:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...) |
This looks unnecessarily
complicated merely because it contains most available table creation
options. Many of those options are not used regularly. Simplified examples
of valid create tables commands for the ARMY database are as follows:
create table soldiers
(name VARCHAR(30) NOT NULL,
rank CHAR(3) NOT NULL,
serialnumber INTEGER UNSIGNED NOT NULL,
primary key(serialnumber))
create table platoons
(platoonid INTEGER NOT NULL,
platoonname VARCHAR(10),
barracks CHAR(1),
primary key (platoonid))
create table members
(soldier INTEGER NOT NULL,
platoonid INTEGER NOT NULL,
primary key (platoonid, soldier),
foreign key (soldier) references soldiers(serialnumber),
foreign key (platoonid) references platoons(platoonid))
This sequence of
commands will make three table containers that will allow us to store
information about soldiers and the platoons they are in. Common elements
featured in these commands include column definitions, data types, primary
and foreign key references. These terms will be explained further. Notice
that these tables relate to each other - they contain storage facilities
for information related to the same topic. More specifically, the commands
make explicit reference in one table to columns in another table.
Populating tables
is achieved with variations of the following commands:
INSERT INTO tableName (columnName {, columnName})
VALUES (const {, const} )
Like:
insert into soldiers
values ('PAARTS, Olivia','Pri',001)
This command inserts
ONE row into the soldiers table, containing name,
rank and serialnumber.
Notice that the TEXT data is enclosed in quotes and the
NUMERIC data is not.
As an extension
of this command, it is possible to insert many rows at one time, (say
3 rows into a 2 col table) using a command similar to:
INSERT INTO tableName
VALUES (col1value1, col2value1),
(col1value2, col2value2),
(col1value3, col2value3)
Like:
insert into soldiers
values ('MONROE, Marvin','Maj',002),
('GRABLE, Gary','Pri',003),
('AARDVARK, Anthea','Cap',004),
('ROBERTS, Millicent','Pri',005)
and:
insert into platoons
values (1,'Recruits','D'),
(2,'Admin','A')
Notice ROWS of data
are enclosed in brackets and separated by commas.
To import large
amounts of data from external applications (like spreadsheets, wordprocessors
and other databases), it is possible to use a COMMA DELIMETED TEXT FILE
full of data, so long as each ROW of the intending table is separated
in the file with an ENTER, and field values are separated with commas
- the command for this is called LOAD DATA INFILE. Most applications
can output text in this form.
It is also possible
to take data from another table and use it as the basis of an insertion
- using a select statement as part of the row source:
INSERT INTO tableName (columnName {, columnName})
selectStatement
Like:
insert into members
select serialnumber, 1
from soldiers
where rank = 'Pri'
or
insert into members
select serialnumber, 2
from soldiers
where rank <> 'Pri'
The statement can
optionally contain column names only if the order of the inputted columns
matches the table definition. If there is deviation from the field order
as defined in the table definition, then the column names should be
considered mandatory.
Remember, if inserting
from a text file, there must be a comma (,) separating each field on
a row, with an [ENTER] separating rows. The data format (order and type)
in the file must match the defined column structure.
If you would like
to try these commands out, go to the PUBLIC
DATABASE PORTAL and paste each command directly into the command
window from here. Remember, if someone else has done this before you,
you will need to use your own table names as creating a table that already
exists causes an understandible error.
Selecting Rows
The SELECT command
is powerful and versatile, used for column and row subsetting. A Simplified
syntax for the Select statement appears below:
SELECT
[DISTINCT | DISTINCTROW | ALL] select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM tableref
[WHERE condition { AND | OR condition} ]
[GROUP BY colnum | colname [ASC | DESC] { ,colnum | colname
[ASC | DESC]}
[HAVING condition ]]
[ORDER BY colnum | colname [ASC | DESC] { , colnum | colname}
[ASC | DESC] ] |
where tableref
=
table_reference
{, table_reference}
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr
}
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
Where table_reference
is defined as:
table_name
[[AS] alias]
and join_condition
is defined as:
ON conditional_expr
| USING (column_list)
|
The select_expression
used inthe first line of the select command can be delightfully complex,
including table data, derived information, system information and textual
values arranged and diplayed together.
The first part of
the course will concentrate on SINGLE TABLE queries (ie. those with
only one table mentioned in the FROM clause)
MULTI-TABLE queries
take many forms - JOINS and UNIONS in MySQL. Other SQLs also allow SUB-QUERIES
- we shall cover SUBQUERIES in Access when a working proficiency of
other multi-table processes has been gained.
A Projection
is a result table resulting from an unconditional select statement
select albname, artist
from albums
This performs column
subsetting - result table will have the same number of rows as the base
table
A Selection-Projection
combination uses column and row subsetting:
select song
from tracks
where media = 'cd'
This will result
in an answer table of arity 1 (that is, the answer table will have ONE
column).
Deleting Table
Rows
DELETE FROM
table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
DELETE table_name[.*] [,table_name[.*] ...]
FROM table-references
[WHERE where_definition]
or
DELETE FROM
table_name[.*], [table_name[.*] ...]
USING table-references
[WHERE where_definition]
|
The first version
of this command is the most commonly used. It should be noted that the
WHERE clause is OPTIONAL in all versions of this command - leave it
out and some devastating changes result. It should also be noted that
the where clause in this command can be as complex as necessary to isolate
the unwanted rows. If it is left out, the table is EMPTIED totally!
The ORDER BY and
LIMIT clauses are interesting MySQL-specific additions
to this command (over other flavours of SQL) - if you arrange by a column
and then limit to 1, you effectively remove the biggest or smallest
(depending on whether you ordered ASCending or DESCending)
As an example, using
the MUSIC database, suppose I lost the album with ser_num 'CDVE39'.
To remove it from the database, the following commands would be needed
to maintain the integrity of the DB:
delete from albums
where sernum = 'CDVE39'
delete from tracks
where sernum = 'CDVE39'
delete from performers
where sernum = 'CDVE39'
Due to CASCADE effects
associated with the foreign keys, however, it should only be necessary
to delete it from the Albums table, the RDMS should remove related rows
from both the Tracks and Performers tables automatically. What would
be the consequences of leaving the Tracks and Performers tables unaltered??
To empty the tracks
table, the command delete from tracks is all that is needed
Altering Row Instances
eg. Suppose I now
have FLYING LIZARDS' album 'THE FLYING LIZARDS' on CD, and not LP as
previously stored:
update albums
set media = 'cd'
where artist = 'The Flying Lizards'
and albname = 'The Flying Lizards'
The general syntax
is:
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2, ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT #]
|
Global (whole table)
updates are performed if the where clause is omitted!
The limit clause is a useful MySQL addition
as it allows you to determine how many records should be updated.
MySQL Metadata
metadata
= data about the data (all of which is also stored in tables maintained
by the system)
The Public Database
Portal has a table and database browser that allows you to examine the
table structure as well as it's data. In Access, metadata is
displayed graphically.
Commands can be
issued to extract and examine meta data in most comand-line driven SQLs. Some of the commands that MySQL offers are:
- the show tables command lists tables in the current database
- show columns from <tablename> lists column defs from the named table
- describe <tablename> does the same job as show columns from <tablename>
- show index from <tablename> displays the current index information used in the nominated table
