Introduction to MySQL

This guide is intended to jog the memory about syntax of the basic commands, and not to act as a comprehensive reference.

The full documentation and information about MySQL can be found at http://www.mysql.com/documentation/index.html, Alternatively, MySQL by Paul DuBois is a good reference (ISBN 0-7357-0921-1)

Connecting

Log into the server using SSH and then enter the command

mysql -u 'username' -p -h 'hostname'

where username and hostname are as provided on your account details letter. This will start a basic client that allows you to enter commands to manulipulate the database. To execute each command, type in the command and a terminating ';', for example'select * from test;'

Creating Tables

Basic Form

CREATE TABLE
(
 Item 1,
 Item 2,
 Item 3
)

Example Items would be

aunsignednumber INT UNSIGNED NOT NULL,

adate DATE NOT NULL,

astring VARCHAR(200) NOT NULL,

anenumeration ENUM('T','F') NOT NULL,

anindex INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

Adding Values

Basic Form

INSERT INTO tablename VALUES (
columsitems
);

Example

INSERT INTO tablename VALUES (
'string', 345, 'T'
);

Selection

Basic Form:

SELECT * FROM tablename
SELECT col1, col2,col3 FROM tablename
SELECT * FROM tablename WHERE expression
SELECT * FROM tablename ORDER BY col1 DESC;
SELECT * FROM tablename WHERE col2 LIKE 'W%'

Updating

UPDATE tablename SET col1='string'
UPDATE tablename SET col1 ='string' WHERE expression

Deleting Items (Rows)

Basic Form:

DELETE FROM tablename WHERE col1=expression

Adding Columns

ALTER TABLE tablename ADD col INT NOT NULL,