• Overview
• Logging In to mySQL
• Changing Your Password
• Creating a Table
• Inserting Tuples
• Getting the Value of a Relation
• Getting Rid of Your Tables
• Getting Information About Your Database
• Quitting mySQL
• Executing SQL Commands From a File
• Recording Your Session

Overview

We shall be using an mySQL database system to implement our PDA this quarter.

Logging In to mySQL

Log in to mySQL by typing:

     ./mysql -u root -p 

You will be prompted for your password. By default there is no root password so just hit return. After hitting return you will see the prompt:

 mysql< 

Creating a Table

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 line numbers until you type the semicolon that ends any command. Warning: an empty line terminates the command but does not execute it. An example table-creation command is:

     create table test (
         i int,
         s char(10)
     );

Note that SQL is case insensitive, so CREATE TABLE and create table are the same. This command creates a table named test with two attributes. The first, named i, is an integer, and the second, named s, is a character string of length (up to) 10.

Inserting Tuples

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 (10, ``foobar'') into relation test by

     INSERT INTO test VALUES(10, 'foobar');

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 test;

produces the result

              I S
     ---------- ----------
             10 foobar

Getting Rid of Your Tables

To remove a table from your database, execute

     DROP TABLE <tableName> ;

We suggest you execute

     DROP TABLE test;

after trying out this sequence of commands to avoid leaving a lot of garbage around that will be there the next time you use the mySQL system.

Getting Information About Your Database

     show tables ;

     DESCRIBE MyTable;

Quitting mySQL

To leave mySQL, type

     quit;

in response to the SQL> prompt.

Executing SQL Commands From a File

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. This way you can use what ever editor you like best for creating the sql commands. For example, if I create a file called "foo" and put in three statements:

create table dogs (name varchar(10), age int) ;
insert into dogs values ('spot',9) ;
insert into dogs values ('fido',2) ;

One way to do so is to startup mysql as usual, give the "use databases" command to connect to the correct database, and then read in the file of commands using the source command:

     source foo;

and the file foo's contents will be executed.

Recording Your Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is

script [ -a ] [ filename ]

exit