INLS 623 Database II

Hemminger

What Is MySQL?

To quote the creators and maintainers of MySQL:

MySQL, the most popular Open Source SQL database management system, was originally developed, distributed, and supported by MySQL AB. It was then purchased by Sun, which supported MySQL developers, and offered services around the MySQL database management system. Recently Sun MicroSystems was purchased by Oracle, who now manages MySQL. So far it has continued to support MySQL. See section 1.3 Overview of MySQL.

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

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 5.1 – the manual is for 5.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 practice creating tables, and when you're ready load in the table for our class work (see below).

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 a set of tables we'll use for all our SQL work.

/fs1/htdocs/courses/2014_fall/inls623_001/mysql/chapter5

To load the SQL commands from this file you have three choices:
1. cut and paste from one window to another. While this works fine for short queries, DO NOT use it to move large files as it often fails (or encounters problems interpreting end of line characters etc).

2. Pipe the input from source file into the mysql startup command
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).

3. 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;
I suggest doing the 3rd. See our resources page for specifics.

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.