Getting started with MySQL
by Russell J.T. Dyer
Over the past few years, MySQL has become one of the most popular database
systems. The company which develops the software, MySQL AB of Sweden, is
experiencing about 35,000 downloads a day and estimates over 5 million
installations worldwide.
Since MySQL can be selected from installation as part of Red Hat� Enterprise
Linux� and Fedora Core, I won't explain how to install the software. However,
if you don't have MySQL installed on your server, you can either get it from
your Linux installation CDs or you can go to MySQL's download page to get the
latest RPMs. Just be sure to use the latest, recommended Generally Available
release and install the Standard distribution of each RPM for your type of
system.
Connecting to MySQL
MySQL is a database system, a database server. To interface with the MySQL
server, you can use a client program or you can write a program or script with
one of the popular programming languages (such as Perl) using an API
(Application Programming Interface) to interface with the MySQL server. This
article focuses on using the default client that comes with MySQL called mysql.
With this client, you can either enter queries from the command line or you can
switch to a terminal, that is to say, monitor mode. We'll use the latter.
>From the command line, enter the following to log in as the root user and to
>enter monitor mode:
mysql -u root -p -h localhostThe -u option is for specifying the user name.
Replace root if you want to use a different user name. This is the MySQL user
name, not the Linux user name. The password for the MySQL user root is probably
different from the Linux user root. Incidentally, it's not a good security
practice to use the root user unless you have a specific administrative task to
perform for which only root has the needed privileges. The -p option above
instructs the mysql client to prompt for the password. If the password for the
root user hasn't been set yet, then the password is blank and you would just
hit Enter when prompted. The -h option is for specifying the host name or the
IP address of the server. This would be necessary if the client is running on a
different machine than the server. If you've secure-shelled into the server
machine, you probably won't need to use the host option. In fact, if you're
logged into Linux as root, you won't need the user option-the -p is all you'll
need. Once you've entered the line above along with the password when prompted,
you are logged into MySQL through the client. To exit, type quit or exit and
press Enter.
Creating a structure
To be able to add and manipulate data, first you have to create a database
structure. Creating a database is simple. Enter something like the following
from within the mysql client:
CREATE DATABASE bookstore;
USE bookstore;This very minimal, first SQL statement creates a sub-directory
called bookstore on the Linux file system in the directory which holds your
MySQL data files. It won't create any data, obviously. It just sets up a place
to add tables, which in turn hold data. The second SQL statement above sets
this new database as the default database. It will remain your default until
you change it or until you log out of MySQL.
The next step is to begin creating tables. This is only a little more
complicated. To create a simple table that holds basic data on books, enter the
following:
CREATE TABLE books (
isbn CHAR(20) PRIMARY KEY,
title VARCHAR(50),
author_id INT,
publisher_id INT,
year_pub CHAR(4),
description TEXT
);This SQL statement creates the table books with six fields, or rather
columns. The first column (isbn) is an identification number for each row-this
name relates to the unique identifier used in the book publishing business. It
has a fixed width character type of 20 characters. It will be the primary key
column on which data will be indexed. The column data type for the book title
is a variable width character column of fifty characters at most. The third and
fourth columns will be used for identification numbers for the author and the
publisher. They are integer data types. The fifth column is used for the
publication year of each book. The last column is for entering a description of
each book. It's a TEXT data type, which means that it's a variable width column
and it can hold up 65535 bytes of data for each row. There are several other
data types that may be used for columns, but this gives you a good sampling.
To see how the table we created looks, enter the following SQL statement:
DESCRIBE books;which produces the following output:
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| isbn | varchar(20) | | PRI | | |
| title | varchar(50) | YES | | NULL | |
| author_id | int(11) | YES | | NULL | |
| publisher_id | int(11) | YES | | NULL | |
| year_pub | char(4) | YES | | NULL | |
| description | text | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+To change the
settings of a table, you can use the ALTER TABLE statement. I'll cover that
statement in another article. To delete a table completely (including its
data), you can use the DROP TABLE statement, followed by the table name. Be
careful with this statement since it's not reversible.
The next table we'll create for our examples is the authors table to hold
author information. This table saves us from having to enter the author's name
and other related data for each book written by each author. It also helps to
ensure consistency of data: there's less chance of inadvertent spelling
deviations.
CREATE TABLE authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
name_last VARCHAR(50),
name_first VARCHAR(50),
country VARCHAR(50)
);We'll join this table to the books table as needed. For instance, we would
use it when we want a list of books along with their corresponding authors'
names. For a real bookstore's database, both of these tables would probably
have more columns. There would also be several more tables. For the examples
that follow, these two tables are enough.
Minor items
Before moving on to the next step of adding data to the tables, let me point
out a few minor items that I've omitted mentioning. SQL statements end with a
semi-colon (or a \G). You can spread an SQL statement over multiple lines.
However, it won't be passed to the server by the client until you terminate it
with a semi-colon and hit Enter. To cancel an SQL statement once you've started
typing it, enter \c and press Enter.
As a basic convention, reserved words are printed in all capital letters. This
isn't necessary, though. MySQL is case-insensitive with regards to reserved
words. Database and table names, however, are case-sensitive on Linux. This is
because they reference the related directories and files on the file system.
Column names aren't case sensitive since they're not affected by the file
system, per se. As another convention, I use lower-case letters for structural
names (such as table names). It's a matter of preference for deciding on names.
Entering data
The primary method for entering data into a table is to use the INSERT
statement. As an example, let's enter some information about an author into the
authors table:
INSERT INTO authors
(name_last, name_first, country)
VALUES('Kafka', 'Franz', 'Czech Republic');This adds the name and country of
the author Franz Kafka to the authors table. We don't need to give a value for
the author_id since that column was created with the AUTO_INCREMENT flag. MySQL
automatically assigns an identification number. You can manually assign one,
especially if you want to start the count at a higher number than 1 (such as
1000). Since we are not providing data for all of the columns in the table, we
have to list the columns for which we are giving data and in the order that the
data is given in the set following the VALUES keyword. This means that we could
give the data in a different order.
For an actual database, we would probably enter data for many authors. We'll
assume that we've done that and move on to entering data for some books. Below
is an entry for one of Kafka's books:
INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Castle', '1', '0805211063', '1998');This adds a record for
Kafka's book, The Castle. Notice that I mixed up the order of the columns, but
it still works because both sets agree. We indicate that the author is Kafka by
giving a value of 1 for the author_id. This is the value that was assigned by
MySQL when we entered the row for Kafka earlier. Let's enter a few more books
for Kafka, but by a different method:
INSERT INTO books
(title, author_id, isbn, year_pub)
VALUES('The Trial', '1', '0805210407', '1995'),
('The Metamorphosis', '1', '0553213695', '1995'),
('America', '1', '0805210644', '1995');In this example, we've added
three books in one statement. This allows us to give the list of column names
once. We also give the keyword VALUES only once, followed by a separate set of
values for each book, each contained in parentheses and separated by commas.
This cuts down on typing and speeds up the process. Either method is fine, and
both have their advantages. To be able to continue with our examples, let's
assume that data on thousands of books has been entered. With that behind us,
let's look at how to retrieve data from tables.
Retrieving data
The primary method of retrieving data from tables is to use a SELECT statement.
There are many options available with the SELECT statement, but you can start
simply. As an example, let's retrieve a list of book titles from the books
table:
SELECT title
FROM books;This displays all of the rows of books in the table. If the
table has thousands of rows, MySQL displays thousands. To limit the number of
rows retrieved, add a LIMIT clause to the SELECT statement:
SELECT title
FROM books
LIMIT 5;This limits the number of rows displayed to five. To be able to
list the author's name for each book along with the title, you have to join the
books table with the authors table. To do this, use the JOIN clause:
SELECT title, name_last
FROM books
JOIN authors USING (author_id);Notice that the primary table from which
we're drawing data is given in the FROM clause. The table to which we're
joining is given in the JOIN clause along with the commonly named column
(author_id) that we're using for the join. For older versions of MySQL, you may
have to join the tables differently.
To retrieve the titles of only books written by Kafka based on his name (not
the author_id), we would use the WHERE clause with the SELECT statement:
SELECT title AS 'Kafka Books'
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Kafka';
+-------------------+
| Kafka Books |
+-------------------+
| The Castle |
| The Trial |
| The Metamorphosis |
| America |
+-------------------+This statement lists the titles of Kafka books stored in
the database. Notice that I've added the AS parameter next to the column name
title to change the column heading in the results set to Kafka Books. This is
known as an alias. Looking at the results here, we can see that the title for
one of Kafka's books is incorrect. His book Amerika is spelled above with a "c"
in the table instead of a "k". This leads to the next section on changing data.
Changing and deleting data
To change existing data, a common method is to use the UPDATE statement. When
changing data, though, we need to be sure that we change the correct rows. In
our example, there could be another book with the title America written by a
different author. Since the key column isbn has only unique numbers and we know
the ISBN number for the book that we want to change, we can use it to specify
the row.
UPDATE books
SET title = 'Amerika'
WHERE isbn = '0805210644';This changes the value of the title column for
the row specified. We could change the value of other columns for the same row
by giving the column = value for each, separated by commas.
If we want to delete a row of data, we can use the DELETE statement. For
instance, suppose that our fictitious bookstore has decided no longer to carry
books by John Grisham. By first running a SELECT statement, we determine the
identification number for the author to be 2034. Using this author
identification number, we could enter the following:
DELETE FROM books
WHERE author_id = '2034';This statement deletes all rows from the table
books for the author_id given. To do a clean job of it, we'll have to do the
same for the authors table. We would just replace the table name in the
statement above; everything else would be the same.
Conclusion
This is a very basic primer for using MySQL. Hopefully, it gives you the idea
of how to get started with MySQL. Each of the SQL statements mentioned here
have several more options and clauses each. You can learn more about them from
experimenting and by reading the on-line documentation provided by MySQL AB.
About the author
Russell Dyer has written on MySQL and other open sources topics for several
magazines. He is the author of MySQL in a Nutshell (O'Reilly 2005).
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Indo-Linux/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
