INLS 623 Database II

Hemminger

What Is MySQL?

MySQL, one of the most popular Open Source SQL database management system, is currently operated by Oracle.

The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL software.

Objectives

The primary objective of today's exercise is to familiarize you with MySQL and help you get through the basics of creating and populating tables in MySQL. So, by the end of this Lab Exercise you should be able to:

·         Create tables in MySQL

·         Insert records into tables in at least two ways (command line, batch mode)

·         Submit SQL queries

Creating a Database

The administrative utilities of MySQL allow the creation of databases with various levels of access privileges. We have already created a set of databases for use in this class. Note that there are two names of interest – your login name and your database name. You will be assigned both of these, and given a password for your login. The databases have been created with a restricted set of access permissions. You will be able to have complete create, read, update, delete functionality from the MySQL monitor. From the web, you will be able to read from anywhere, but will only have modify,delete from SILS domains.

 

I will let you know your database name and password at the beginning of this lab.  Please change your password!

Starting Off

First, login to your account on ruby.  As mentioned in class, you will be running the MySQL client from ruby, and connecting to the MySQL server running on pearl.

 

If you have not used MySQL before, then you should do the MySQL tutorial at

 

http://dev.mysql.com/doc/mysql/en/Tutorial.html

 

Do through Section 3.6 but you can skim or skip 3.6 if your SQL is pretty good. Note however that this section does describe some MySQL idiosyncracies. With respect to this tutorial, you should know the following things:

·         We are using MySQL server version 4.1.12   the manual is for 4.x so it should be mostly accurate, although there are some variations and inconsistencies.

·         To connect, you do need to specify the host (-h option) because the database server is running on pearl.ils.unc.edu (and you will be running the client on ruby.ils.unc.edu),

·         your database has already been created for you – it is the same as your userid

·         The LOAD DATA command does not work on our installation.

 

Running through the tutorial will take a little while, but it is well worth your while.

The command line interface: mysql

Here at SILS, the MySQL client on ruby is a command line client (although if you're feeling up to it, both a Windows GUI and an X windows interface are available for download from mysql.com), so if you want to create tables and add records you’ll need to use SQL statements or other command line utilities, or insert data using one of the many languages that have MySQL libraries. From the Unix command line, we use the MySQL monitor called "mysql". It can be invoked as follows:

 

mysql -p –u <username> -h pearl.ils.unc.edu <database name>

 

or, for instance if you were assigned db2_10 as your ID, then

 

mysql -p –u db2_10 -h pearl.ils.unc.edu db2_10

 

 

 

1.  First, invoke the monitor using the above command.  You will know you are "in" the client, when your prompt changes from the standard ruby prompt to

            mysql>

 

2.  If you have not yet changed your password, please do so now.  The command for this is:

 

mysql> SET PASSWORD for user_name = PASSWORD(‘new_password’);

 

3.  Then create the following tables using standard SQL "CREATE TABLE …" commands:

 

Table

Attribute

Format

Description

etext

textid

int

Primary key (not null)

 

title

varchar (200)

Title of the text

 

author

varchar (200)

Author of the text

 

 

 

 

file_info

fileid

int

Primary key (not null)

 

textid

int

FK to etext

 

path

varchar(128)

Full path to file (not null)

 

filetype

char(4)

Type of file e.g. HTML, GIF, JPG, SGML

 

 

 

 

access_info

fileid

int

File accessed

 

adate

date

Date of access

 

domain

varchar(128)

Accessing domain

 

Note: the SQL command to create the first table is:


create table etext (

      textid int not null,

      title varchar(200),

      author varchar(200),

      primary key (textid));

 

There are several ways to populate your tables including, 1) using SQL insert commands from MySQL (you can script this); 2) using SQL from an API (Perl etc); or 3) use the LOAD DATA facility. The latter is very helpful if you are populating databases with lots of data, but this is a security hole and has been disabled on pearl. Thus loading, data is restricted to insert statements and APIs.

MySQL command line summary

All commands can span multiple lines and they must end with a semi-colon “;”. Of course, all SQL commands are supported from MySQL monitor

 

show databases                          lists all databases

show tables                                   lists all tables in the current database

describe tablename                    gives the schema for the named table

use databasename                                    changes to the named database

drop table tablename      drops the named table

 

Importing data

I have provided you example data to create and populate the three tables described above.

 

http://ils.unc.edu/courses/2005_spring/inls157_001/mysql/etext.cmd

http://ils.unc.edu/courses/2005_spring/inls157_001/mysql/file_info.cmd

http://ils.unc.edu/courses/2005_spring/inls157_001/mysql/access_info.cmd

 

 

Each of these files contains SQL commands to insert data in the appropriate tables. To do the insertion:

 

1.       Make copies of the files in your local directory on ruby (save from the web page to local file, or use the Unix “cp” command, i.e.

% cp /htdocs/inls157_f04/mysql/etext.cmd .

2.       From the ruby Unix command prompt do

 

mysql -p -u username -h pearl.ils.unc.edu dbname < etext.cmd

 

      where username is your MySQL user name (e.g. db2_10). This should perform the insertions into the etext table, after you supply the password for your database.  Note that you do have to create the table before you can do the inserts. It will disconnect you after successfully completing the commands (or it will report an error if it encounters one processing your commands).

      Alternatively, you can use the “source” command within mysql on ruby to process a file directly.  So if you are in your home directory on ruby and you have the file etext.cmd you could do

mysql> source etext.cmd;

 

3.       Do the same thing for the other two tables.

4.       Verify that the insertions have worked by invoking the MySQL monitor and doing “show tables;” and some SELECT statements.

5.       There should be 3 rows in etext, 30 rows in file_info, and 107 rows in access_info.

 

Practice for Class

To drive this all home, do the following:

1.       Log in to ruby and MySQL and change your password.

2.       Create the necessary tables and complete the insertions specified in the “Importing Data” section above.

3.       Create a new table in your database called ‘project’. Attributes should be name (varchar(128)), sponsor (varchar(128)), startdate (date), and stopdate (date).  Add several records to your project table.

 

 

Thanks to Kristin Chaffin, who created the bulk of this material for the INLS 723 (formerly 258) course.  I've adapted it and incorporated it into 623 when creating this course.