You will use the MySQL
database system to implement your TBP App this quarter.
To get a MySQL account, please, e-mail Greg your cs.uchicago.edu login name. Please include "[csppdb] MySQL account request" in you subject line. To request a cs.uchicago.edu account please follow instructions here. Once your MySQL account and password are emailed to you, please log in to change the initial password.
You should use your cs.uchicago.edu account and login to any CS Linux machine. You can find a CS machines to log in to using the following tool: http://tools.cs.uchicago.edu/find_cs_hosts/find.cgi
Now, you can log in to MySQL by typing:
mysql -h cspp53001 -u<yourName> -pHere, mysql is MySQL's command line SQL interface and cspp53001 is the machine on which the MySQL server is running. <yourName> refers to your cs.uchicago.edu login. You can actually omit the -u<yourName> option since MySQL will use the Linux username by default.
You will be prompted for your password. The password initially e-mailed to you should be changed as soon as possible. For security reasons, we suggest that you don't use your regular cs.uchicago.edu password, because as we shall see there are opportunities for this password to become visible under certain circumstances. After you enter the correct password, you should receive the prompt
mysql>
In response to the mysql> prompt, type
set PASSWORD = PASSWORD("<newPassword>");where
<newPassword> is the password you would like to use in the
future. This command, like all other SQL commands, should be terminated with a
semicolon.
Note that MySQL is case-insensitive for keywords and attribute
names but case sensitive for relation and database names. Once
you are in mysql, you can use capitals or not in keywords
like PASSWORD. We tend to capitalize keywords and not other
things.
After you login to MySQL, you can go to your database by typing:
use <yourName>DB;where <yourName> refers to your cs.uchicago.edu username, e.g. use evtimovDB;.
In mysql we can execute any SQL command. One simple type of command creates a table (relation). The form is
CREATE TABLE <tableName> ( <list of attributes and their types> );You may enter text on one line or on several lines. If your command runs over several lines, you will be prompted with -> until you type the semicolon that ends any command. An example table-creation command is:
CREATE TABLE Sells ( bar varchar(50), beer varchar(20), price real );This command creates a table named Sells with three attributes. The first, named bar, is a character string of length up to 50, the second, named beer, is a character string of length (up to) 20, and the third one, named price, is a real number.
Creating a Table With a Primary Key
To create a table that declares attribute a to be a primary key:
CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);To create a table that declares the set of attributes (a,b,c) to be a primary key:
CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));
Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command:
INSERT INTO <tableName> VALUES( <list of values for attributes, in order> );For instance, we can insert the tuple ('Cans', 'Guinness', 5.25) into relation Sells by
INSERT INTO Sells VALUES('Cans', 'Guinness', 5.25);
Getting the Value of a Relation
We can see the tuples in a relation with the command:
SELECT * FROM <tableName>;For instance, after the above create and insert statements, the command
SELECT * FROM Sells;produces the result
+------+----------+-------+ | bar | beer | price | +------+----------+-------+ | Cans | Guinness | 5.25 | +------+----------+-------+
To remove a table from your database, execute
DROP TABLE <tableName>;We suggest you execute
DROP TABLE Sells;after trying out this sequence of commands to avoid leaving a lot of garbage around that will be still there the next time you use the MySQL system.
Getting Information About Your Database
The system keeps information about your own database in certain system tables. You can recall the names of your tables by issuing the command:
SHOW TABLES;You can recall the attributes of a table once you know its name by issuing the command:
DESCRIBE <tableName>;to learn about the attributes of relation <tableName>.
To leave mysql, type
quit;or
exit;in response to the mysql> prompt.
Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.
To run the file thirsty.sql, type:
mysql -h cspp53001 -u<yourName> -p <yourName>DB < thirsty.sql
For more information on using MySQL in batch mode see Section 3.5 of the MySQL Reference Manual "Using mysql in Batch Mode".
script [ -a ] [ filename ]The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type
exitFor more information on how to run the script command, check out its man page. mysql provides the command tee to save query results to a file. At the mysql> prompt, you say:
tee thirsty.lst;and a file called thirsty.lst will appear in your current directory and will record all user input and system output, until you exit mysql or type:
notee;Note that if the file thirsty.lst existed previously, it will be appended.
Finally, if you use Emacs, you can simply run mysql in a shell buffer and save the buffer to a file. To prevent your MySQL password from being echoed in the Emacs buffer, add the following lines to your .emacs file:
(setq-default comint-output-filter-functions '(comint-watch-for-password-prompt)) (setq comint-password-prompt-regexp "\\(\\([Oo]ld \\|[Nn]ew \\|^\\)[Pp]assword\\|Enter password\\):\\s *\\'")
In response to the mysql> prompt, type
help and follow the instructions. You can also browse the complete MySQL Manual online at http://dev.mysql.com/doc/refman/5.1/en/. The MySQL server running on cspp53001 is version 5.1.