CSCI 479 Using MySQL

Computer Science Department, Bucknell University

Using MySQL
CSCI 479 Fall 2009
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 ------------------------
[hyde@linuxremote1 ~]$  mysql -h db.eg.bucknell.edu -u hyde -p hydedb
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4654765
Server version: 5.0.75 Source distribution

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

mysql> help
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Network Support, training, or other products, visit:
   https://shop.mysql.com/

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 statement delimiter. NOTE: Takes the rest of the line as new 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.
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.
end 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
[hyde@linuxremote1 ~]$

Page maintained by Dan Hyde, hyde at bucknell.edu Last update September 30, 2009

Back to CSCI 479's home page.

Using MySQL CSCI 479 Fall 2009 Professor Dan Hyde

Using MySQL
CSCI 479 Fall 2009
Professor Dan Hyde