CSCI 479 Using MySQL

Computer Science Department, Bucknell University

Using MySQL
CSCI 479 Fall 2008
Professor Dan Hyde

In order to use MySQL on the Linux machines, a System Administrator (Mike Harvey or Jeremy Dreese <ecst@bucknell.edu>) must install a MySQL database for you. For each Team, I have requested a MySQL database that is associated with your team account.

After you have your database login and password, you need to create some tables. One way to create the original tables is to use the tool mysql on Linux. The command-line tool mysql allows you to connect to a database and enter SQL (Structured Query Language) commands. You must be in the account for which the database was created.

My convention is to type SQL commands in caps but that is not necessary. SQL commands are case in-sensitive. However, user-defined names are case sensitive in MySQL! "Id" and "ID" are not the same.

You may need a SQL reference book. I recommend MySQL Pocket Reference by George Reese, published by O'Reilly, $10.

Note semi-colons are at the end of SQL commands and strings are in single quotes. You may spread a SQL command across several lines. The command will be sent to the database server when you type ";" and Return. In the first command below, after the -h option is the host name; after -u is the user account; -p means must have password; and the last item is the database name.

------------------- mysql Sample Session ------------------------
castor{130}% mysql -h db.eg.bucknell.edu -u hyde -p hydedb
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3510 to server version: 5.0.24a

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> help

For the complete MySQL Manual online, visit:
   http://www.mysql.com/documentation

For info on technical support from MySQL developers, visit:
   http://www.mysql.com/support

For info on MySQL books, utilities, consultants, etc., visit:
   http://www.mysql.com/portal

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set query delimiter. 
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute a SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.

For server side help, type 'help contents'

mysql> #this is a comment;
mysql> CREATE TABLE nameTable( id INTEGER, name CHAR(20) );
Query OK, 0 rows affected (0.00 sec)

mysql> DESCRIBE nameTable; #another comment to end of line
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe nameTable; # SQL commands are NOT case sensitive
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe nametable; # user-defined names are case sensitive
ERROR 1146 (42S02): Table 'hydedb.nametable' doesn't exist
mysql> INSERT INTO nameTable VALUES(123, 'Darth Vader');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nameTable VALUES( # splitting command over two lines
    -> 345, 'Luke Skywalker');
Query OK, 1 row affected (0.00 sec)

mysql> DESCRIBE nameTable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM nameTable;
+------+----------------+
| id   | name           |
+------+----------------+
|  123 | Darth Vader    |
|  345 | Luke Skywalker |
+------+----------------+
2 rows in set (0.00 sec)

mysql> QUIT;
Bye
castor{131}%

Page maintained by Dan Hyde, hyde at bucknell.edu Last update October 7, 2007

Back to CSCI 479's home page.

Using MySQL CSCI 479 Fall 2008 Professor Dan Hyde

Using MySQL
CSCI 479 Fall 2008
Professor Dan Hyde