IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
SQL home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

MySQL COMMANDS

An Introduction

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:

the army database

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
 

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.