LPI Linux Certification in a Nutshell (47 page)

Use
UPDATE
to add city and state
values:

mysql>
update families set city = "Chicago", state = "IL" where id = "1";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>
update families set city = "Omaha", state = "NE" where id = "3";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>
select id, city, state, notes from families;
+----+---------+-------+------------------------------------------+
| id | city    | state | notes                                    |
+----+---------+-------+------------------------------------------+
|  1 | Chicago | IL    | This is the Smith family of Chicago, IL  |
|  3 | Omaha   | NE    | This is the Anderson family of Omaha, NE |
+----+---------+-------+------------------------------------------+
2 rows in set (0.00 sec)

Notice that the Anderson family is now
id
“3”
instead of “2”. ID 2 was removed by our
DELETE
command. The autoupdate option of MySQL will never use the number 2
again in this column.

Aggregate functions allow you to group queried data in
meaningful ways. SQL databases are more than just simple data stores;
the complex functionality of SQL allows you to extract meaningful data
very easily.

A common aggregate function is
GROUP BY
. This
function allows you to perform operations on groups of data. Let’s add
some more data to our example database and see what options
GROUP BY
gives us.

mysql>
insert into families set father_name = "Adam White",
-> father_dob = "1969-06-08", mother_name = "Tina White",
-> mother_dob = "1969-01-30", number_of_children = "1",
-> child1_name = "Ed White", child1_dob = "1998-11-17",
-> notes = "This is the White family of Bellevue, NE",
-> city = "Bellevue", state = "NE";
Query OK, 1 row affected, 0 warnings (0.00 sec)
mysql>
insert into families set father_name = "Bill Carpenter",
-> father_dob = "1968-06-01", mother_name = "Linda Carpenter",
-> mother_dob = "1970-02-30", number_of_children = "4",
-> child1_name = "Joe Carpenter", child1_dob = "1998-12-17",
-> child2_name = "Bob Carpenter", child2_dob = "1996-01-01",
-> child3_name = "Luke Carpenter", child3_dob = "2004-08-08",
-> notes = "This is the Carpenter family of Lincoln, NE",
-> city = "Lincoln", state = "NE";
Query OK, 1 row affected, 0 warnings (0.00 sec)
mysql>
select id, father_name, city, state from families;
+----+----------------+----------+-------+
| id | father_name    | city     | state |
+----+----------------+----------+-------+
|  1 | Joe Smith      | Chicago  | IL    |
|  3 | Ken Anderson   | Omaha    | NE    |
|  4 | Adam White     | Bellevue | NE    |
|  5 | Bill Carpenter | Lincoln  | NE    |
+----+----------------+----------+-------+
4 rows in set (0.01 sec)
mysql>
select count(state),state from families GROUP BY state;
+--------------+-------+
| count(state) | state |
+--------------+-------+
|            1 | IL    |
|            3 | NE    |
+--------------+-------+
2 rows in set (0.00 sec)

We’ve added two new families, so our table now contains four rows.
The last query is an example of using the
GROUP BY
syntax. We asked MySQL, “How many different states are represented in
our table?” You also could have achieved the same result with a
combination of the
mysql
command-line program and
some shell scripting knowledge:

#
echo "select state from families" |\
>
mysql –s –uroot –ppassword –hlocalhost community |\
>
sort | uniq –c
1 IL
3 NE

If you are more familiar with the Linux command-line text
processing tools, you can oftentimes depend on them to parse the data
that a SQL query will return. As you can see from this example, the
mysql
command-line program can process queries on
standard input and return results on standard output. The
-s
option tells
mysql
to be
“silent,” meaning not to return any column names and only return data,
which is usually what you want if you’re going to be passing the results
to another program for processing.

Another option MySQL gives you for outputting data is the
ORDER BY
function. This simply changes the sort
order of the results. Let’s sort our families by the father’s date of
birth, from youngest to oldest:

mysql>
select id,father_name,father_dob from families ORDER BY father_dob asc;
+----+----------------+------------+
| id | father_name    | father_dob |
+----+----------------+------------+
|  5 | Bill Carpenter | 1968-06-01 |
|  4 | Adam White     | 1969-06-08 |
|  1 | Joe Smith      | 1970-04-01 |
|  3 | Ken Anderson   | 1971-06-06 |
+----+----------------+------------+
4 rows in set (0.00 sec)

MySQL understands that the
father_dob
column
is type DATE and sorts accordingly. The modifiers to
ORDER
BY
can be
asc
(ascending) or
desc
(descending).

The final concept to describe in our basic SQL overview is
that of
JOIN
. So far, we have concerned ourselves
with querying only one table. This is fine for simple data storage
requirements, but as data complexity grows, so does the need for
multiple tables. As long as there is a relationship between data
elements in the tables, the table values can be
JOIN
ed in a query.

To see an example of this, we need to create another table that
has a relationship with the first table. We will use this second table
to store pet information for each family.

mysql>
CREATE TABLE pets (
->
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
->
family_id INTEGER UNSIGNED NOT NULL,
->
type VARCHAR(45) NOT NULL,
->
name VARCHAR(45) NOT NULL,
->
PRIMARY KEY (id)
->
);
Query OK, 0 rows affected (0.02 sec)
mysql>
show tables;
+---------------------+
| Tables_in_community |
+---------------------+
| families            |
| pets                |
+---------------------+
2 rows in set (0.00 sec)
mysql>
describe pets;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| family_id | int(10) unsigned | NO   |     |         |                |
| type      | varchar(45)      | NO   |     |         |                |
| name      | varchar(45)      | NO   |     |         |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
mysql>
insert into pets (family_id,type,name) VALUES ("1","dog","Max");
Query OK, 1 row affected (0.01 sec)
mysql>
insert into pets (family_id,type,name) VALUES ("3","cat","Paws");
Query OK, 1 row affected (0.01 sec)
mysql>
insert into pets (family_id,type,name) VALUES ("4","cat","Muffy");
Query OK, 1 row affected (0.01 sec)
mysql>
insert into pets (family_id,type,name) VALUES ("4","dog","Rover");
Query OK, 1 row affected (0.00 sec)

The important column in this second table is
family_id
. We need to ensure that as we add data to
this table, we associate the data with the correct ID from the
families
table. This expresses the relationship
between the families and their pets, and allows us to query against
them. We have added four pets to our table: family ID #1 (the Smiths)
have a dog named Max, family ID #3 (the Andersons) have a cat named
Paws, and family ID #4 (the Whites) have a cat named Muffy and a dog
named Rover. Here is a simple example of a join that queries values from
both tables:

mysql>
select a.id, a.father_name, a.mother_name, b.type, b.name
from families a, pets b where a.id = b.family_id;
+----+--------------+---------------+------+-------+
| id | father_name  | mother_name   | type | name  |
+----+--------------+---------------+------+-------+
|  1 | Joe Smith    | Jan Smith     | dog  | Max   |
|  3 | Ken Anderson | Mary Anderson | cat  | Paws  |
|  4 | Adam White   | Tina White    | cat  | Muffy |
|  4 | Adam White   | Tina White    | dog  | Rover |
+----+--------------+---------------+------+-------+
4 rows in set (0.02 sec)

This syntax is slightly different from our previous
SELECT
statements. First, notice that we have to
qualify the column names with a table identifier. In this case, we’re
using
a
and
b
. After the
FROM
statement, we are aliasing the
families
table as
a
and the
pets
table as
b
. This is common shorthand
that makes our SQL statements shorter; otherwise, we’d have to use
families.id
,
families.father_name
, etc. Finally, the
WHERE
clause of
a.id =
b.family_id
expresses the relationship between the tables.
This ensures that we know which pet belongs to which family. As you can
see, the
family_id
column is not a primary key,
because it does not need to be unique. Families can have multiple pets,
as the White family does in our example.

Although this
SELECT
statement did not
actually use the
JOIN
syntax, it is an example of
the simplest kind of join. Notice that we are missing a family from our
results, however: the Carpenter family does not have any pets, so our
SELECT
statement did not select them. If we want
families to display in the output even if they do not have pets, we need
to use the
LEFT JOIN
syntax. This is functionally
very similar to the previous join of two tables, but it differs in two
important ways: the syntax is quite a bit different, and because special
consideration is given to the table on the LEFT, each item present in
the left table will display in the results,
even if
there is not a match with the other joined table. Compare the output of
this
LEFT JOIN
statement with the previous join
output:

mysql>
select families.id, families.father_name, families.mother_name,
->
pets.type, pets.name from families
->
LEFT JOIN pets on families.id = pets.family_id;
+----+----------------+-----------------+------+-------+
| id | father_name    | mother_name     | type | name  |
+----+----------------+-----------------+------+-------+
|  1 | Joe Smith      | Jan Smith       | dog  | Max   |
|  3 | Ken Anderson   | Mary Anderson   | cat  | Paws  |
|  4 | Adam White     | Tina White      | cat  | Muffy |
|  4 | Adam White     | Tina White      | dog  | Rover |
|  5 | Bill Carpenter | Linda Carpenter | NULL | NULL  |
+----+----------------+-----------------+------+-------+
5 rows in set (0.01 sec)

The Carpenter family now appears in the query results, but with
NULL values for
type
and
name
,
since they did not have a corresponding record in the
pets
table. We used the more detailed
SELECT
syntax in this example, keeping the full
table names instead of aliasing them with
a
and
b
but
the queries are the same; the aliasing is just for
readability.

Objective 1: Install and Configure X11

An LPIC 1 candidate should be able to configure and install X
and an X font server. This Objective includes verifying that the video
card and monitor are supported by an X server as well as customizing
and tuning X for the video card and monitor. It also includes
installing an X font server, installing fonts, and configuring X to
use the font server (which may require manually editing
/etc/X11/xorg.conf
). Weight: 2.

Objective 2: Set Up a Display Manager

This Objective states a candidate should be able to set up and
customize a
display manager. This includes turning the display
manager on or off and changing the display manager greetings. It also
includes changing default bitplanes for the display manager and
configuring display managers for use by X stations. This Objective
covers the display managers: X Display Manager
(
xdm
), Gnome Display Manager
(
gdm
), and KDE Display Manager
(
kdm
). Weight: 2.

Objective 3: Accessibility

Demonstrate knowledge and awareness of accessibility
technologies. This objective requires the candidate to be familiar
with the various technologies and how they may be configured in the X
Window System. Topics include keyboard shortcuts, controlling visual
settings and themes, and assistive technologies. Weight: 1.

X is implemented using a client/server model. X servers and
clients can be located on the same computer or separated across a network,
so that computation is handled separately from display rendering. While X
servers manage hardware, they do not define the look of the display, and
they offer no tools to manipulate clients. The X server is responsible for
rendering various shapes and colors on screen. Examples of X Servers
include:

X clients are user programs, such as spreadsheets or CAD tools,
which display graphical output. Examples of X clients are:

A special client program called a
window manager
is responsible for these
functions and provides windows, window sizing, open and close buttons, and
so forth. The window manager controls the other clients running under an X
server. Multiple window managers are available for the X Window System,
allowing you to choose an interface style that suits your needs and
personal taste.

A few complete graphical
desktop environments
are also
available. These packages can include a window manager and additional
applications that work together to create a complete, unified working
environment. Most Linux distributions ship with either the KDE or GNOME,
or both, along with a number of standalone window managers. There is no
standard window manager or environment for Linux. The selection is
entirely up to the user.