Newbie's Linux Manual: Introduction to MySQL

Newbie's Linux Manual

Introduction to MySQL

] [ Download*] [ Previous] [ Next] Downloadthe latest stable MySQL server RPM (4.5Mb) and MYSQL client RPM (2.0Mb) and as root, install both.

- 2 -

Whilst it's not a good example to set, setting-up a MySQL user account is a complicated process, warranting an entire chapter to describe how to do it, so we'll be using the MySQL root account for the rest of this tutorial. To start with, the root account doesn't even have a password. Fortunately it's set-up so people can't log-in remotely so it's not a major security risk.

To make a connection to the MySQL server with mysql (the MySQL client), enter:

mysql -u root

4.7 million books, CDs, videos, and DVDs available to buy! Webmaster| Manual's Copyright Terms

[

* In Linux enter: unzip nlm.zip

It's All About SQL

MySQL is the arguably the best and most widely used database for Linux. The SQL part of its name, stands for Structured Query Language. Databases are all about storing and retrieving data. You ask the database a question, and it displays the answer. You ask the question using SQL. All professional databases use SQL, making it important to learn.

Installation & Running

- 1 -

Note:

To quit MySQL, either press Ctrl+D or enter exit.

The MySQL Prompt

An SQL statement can be entered on one line, or multiple lines. Here's an example:

mysql> SHOW DATABASES;

...or:

mysql> SHOW
    -> DATABASES;

...with the semi-colon (;) telling MySQL that the statement is complete and that it can now process it.

Creating Your First Database

- 1 -

Enter:

mysql> CREATE DATABASE company;

Note:

Case does not apply in SQL, but it's common practice to document keywords in uppercase, even though you'll likely enter them in lowercase.

- 2 -

MySQL can hold more than one database. To display a list of all the existing databases, enter:

mysql> SHOW DATABASES;

...to display:

+----------+
| Database |
+----------+
| company  |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

Note:

Usually, when you ask MySQL a question, it displays the answer in the format shown above. The '(0.00 sec)' part is how long MySQL took to calculate the answer.

- 3 -

Enter:

mysql> USE company

...to tell MySQL that you want to use this database.

- 4 -

Enter:

mysql> CREATE TABLE customer (
    -> First_Name VARCHAR,
    -> Surname VARCHAR,
    -> E-mail VARCHAR);

Some Basic Database Jargon

A database consists of one or more tables. A database might have a table for customer details, a table for products, a table for orders and so on. A table is made up of columns. The table you just created (customer) has three columns: First_Name, Surname, and E-mail. In MySQL, table names, and column names can not have spaces. Each entry you store in a database is called a record, and a record consists of fields. [John|Smith|] is an example record, containing 3 fields.

Note:

Each of the columns you created is of type VARCHAR, meaning each column can store up to 255 characters (the characters can be anything), and that because each field is of variable length, you save on disk space. MySQL offers an impressive assortment of datatypes. For details consult the MySQL reference manual.

- 5 -

Either switch to another virtual terminal or open another XTerm window and enter:

pico -w ~/customer.txt

- 6 -

Now enter the following, making sure every entry on each line is seperated by one and only one tab (I've indicated each tab with a »):

Laurence»Hunter»
Bill»Turner»
Steve»Coe»

...and save, then exit Pico.

- 7 -

Back in the virtual terminal or XTerm window running MySQL, enter:

mysql> LOAD DATA LOCAL INFILE "customer.txt" INTO TABLE customer;

- 8 -

To display the entire contents of the customer table, enter:

mysql> SELECT * FROM customer;

...to be continued.

[