In order to manage data on the MySQL server, you must connect to it using some type of SQL client. There is a plethora of different clients you can choose from. However, for your Linux+/LPIC-1 exam, it’s best that you be familiar with the command-line–based MySQL client. If you can manipulate SQL server data with this utility, all of the other clients will be a piece of cake for you to use.
The command-line MySQL client is run by entering mysql at the shell prompt. The
syntax is mysql –h host_name –u user_name –p. For example, to connect to the MySQL
service running on the local Linux system as root, you would enter mysql –h localhost –u root –p. This is shown in Figure 16-16.
At this point you are connected to the database and can execute any supported SQL command construct. Let’s practice doing so in the following exercise.
Figure 16-16
Connecting to the MySQL database from the command line
Exercise 16-3: Managing SQL Data
VIDEO For more information watch Exercise 16-3.
In the scenario for this exercise, you will create a new database called customers and populate it with data. To do this, complete the following (the semi-colons must remain in the commands.):
1. Install the mysql packages on your system along with any required dependencies. Depending upon your distribution, you may need to install the following: • mysql
• mysql-client • mysql-shared
2. Switch to root using the su– command.
3. Set up your MySQL server’s grant tables by changing to the /usr/bin directory and running the mysql_install_db command at the shell prompt.
4. Start the MySQL service using the mysql init script in your distribution’s init directory.
5. Verify that the server is running by entering the mysqladmin version
command at the shell prompt.
6. Assign a password to your MySQL root user account by entering mysqladmin –u root password 'your_new_password' at the shell prompt.
7. Connect to the MySQL server from the command line by entering mysql –h localhost –u root –p at the shell prompt.
8. At the mysql prompt, enter CREATE DATABASE customers;. You should be
prompted that the database was created.
9. To use the new database, enter USE customers; at the mysql prompt. 10. To view the tables that were defined by default in the new database, enter
SHOW TABLES; at the mysql prompt. You should see that there are no tables
defined. At this point, things get a little more complex. You need to use the
CREATE TABLE command to create the table, but you will also need to define
your table’s columns in the command. Therefore, you first need to define the fields that you want to include in each record in the table. You also need to decide what kind of data will be stored in each field as well as how long the table will be. In the table we’re building here, we’ll need four columns:
first
last
phone
We know that the first and last fields will contain alphabetical characters of varying length. The phone field will contain ten digits with two hyphens (area_code-prefix-number). The lastpurchase column will contain the date of each customer’s last purchase.
11. Create a new table in the database by entering CREATE TABLE active (first VARCHAR(15), last VARCHAR(15), phone CHAR(12), lastcontact DATE);
at the mysql prompt.
12. Verify that the table and columns were created correctly by entering DESCRIBE active; at the mysql prompt. You should see that the active table and its fields
were created, as shown in Figure 16-17.
With the table defined, you can now insert data into it.
13. To add a record for a customer named Robb Tracy, enter INSERT INTO active VALUES ('Robb','Tracy','801-756-5555','2010-07-01');.
14. To view the data just added to the table, you use a syntax of
SELECT field(s) FROM table [WHERE conditions] [ORDER BY field]
For example, to view everything in the active table, you would enter SELECT * FROM active; at the mysql prompt. You should see the data shown in Figure
16-18.
You could also enter SELECT first,phone FROM active; to view just certain
fields from each record. Another example would be SELECT * FROM active WHERE last='Tracy'; to view just records containing the string Tracy in the
last field.
You can also delete data from the table. This is the syntax:
DELETE FROM table WHERE conditions
For example, to delete the Robb Tracy record from the table, you could enter DELETE FROM active WHERE last='Tracy'; at the mysql prompt.
Before deleting, however, you should use the equivalent SELECT command first to see what exactly you’ll be deleting! For example, first run SELECT
Figure 16-17
Creating a new table in a database
FROM active WHERE last='Tracy';, then run DELETE FROM active WHERE last='Tracy';.
You can also modify existing data using the UPDATE command. The syntax is
UPDATE table_name SET column = new_value WHERE condition
TIP You can also use the JOIN command to merge fields from two different
tables.
EXAM TIP You can use the GROUP BY statement to consolidate the
results from a SELECT statement by one or more columns. It is commonly used with the SUM() statement. For example, suppose the active table contains two additional columns named quantity and custid and you use this information to record individual orders. In this situation, each customer may have multiple records in the table; one for each order placed. The SELECT custid,SUM(quantity)AS “Total” FROM active GROUP BY custid; would aggregate a single result for each unique customer ID displaying the total quantity purchased for each (in a new column named “Total”).