INLS 623 Database II
Hemminger
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. 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 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! 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. 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. 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 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 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. 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.Objectives
Creating a Database
Starting Off
The command line interface: mysql
MySQL command line summary
Importing data
mysql
-p -u username -h pearl.ils.unc.edu
dbname < etext.cmdPractice for Class