Now this example introduces you to the WHERE clause, but also to the = operator. That is only one operator you can use with the WHERE clause. There are others:

The first few work much like the = sign. If you want to use !, >, <=, and so on, they work much like the = symbol. You might have an example like this:

SELECT * FROM Employees WHERE AGE > 21

But the LIKE qualifier is a bit different. The LIKE statement enables you to get back records that are similar to the criteria you put in. Here is an example:

SELECT * FROM Employees WHERE LastName LIKE 'B%'

The % symbol is a wild card. This statement is saying “get all the records in the employees table where the last name starts with a B.” That means Brown, Benson, Bartholomew, etc. The LIKE keyword is pretty useful.

There are also cases where you may have more than one criteria. In these cases, you will want to use a modifier with your SELECT statement.

The AND operator displays a record if both the first condition and the second condition are true.

The OR operator displays a record if either the first condition or the second condition is true.

SELECT * FROM Employees WHERE City='Chicago' AND LastName='Johnson'
SELECT * FROM Employees WHERE City='San Francisco' OR City='New York'

Although our examples only show one AND and one OR, you can chain together as many of these as needed to create the database logic your search requires.

Another important keyword is DISTINCT. This keyword is telling the database to ignore duplicate records. The basic syntax is like this:

SELECT DISTINCT column_name(s) FROM table_name

If, for example, you wanted a list of cities found in your Employees table, but wanted to omit duplicates, then this code would work like this:

SELECT DISTINCT City FROM Employees

The ORDER BY clause is used to sort the data retrieved. You simply select what column you want to use to sort the records. The general syntax is like this:

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

A specific example would be:

SELECT * FROM Employees ORDER BY LastName

The BETWEEN operator selects a range of data between two values. The general syntax is:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

A specific example would be:

SELECT * FROM Employees
WHERE LastName
BETWEEN 'Jones' AND 'McMurray'

These basic select options are enough to allow you to perform a wide range of queries on any given database. The SELECT statement and the various modifiers we have just explored are the most commonly used SQL statements.

In addition to these query options, SQL offers a number of mathematical functions you may find useful. Here are some of the most commonly used. You should notice that some of these (like AVG, SUM, etc.) require that the column being used contain a mathematical data type, such as an integer. Using a string as the argument to these functions will not work. However, others, like COUNT, can take any data type as an argument.

The AVG function returns the mathematical mean for a given column. Its general syntax is:

SELECT AVG(column_name) FROM table_name

A specific example would be:

SELECT AVG(Salary) FROM Employees

The COUNT function literally counts the number of entries and returns that value. The general syntax is:

SELECT COUNT(column_name) FROM table_name

A specific example would look like this:

SELECT COUNT(LastName) FROM Employees

The FIRST function returns the very first entry in the table for that column. The general syntax looks like this.

SELECT FIRST(column_name) FROM table_name

A specific example would look like this:

SELECT FIRST(LastName) FROM Employees

The LAST function does the exact opposite of the FIRST function and has the same syntax.

The MAX function returns the maximum value found in that particular column. The minimum does the exact opposite. The general syntax looks like this.

SELECT MAX(column_name) FROM table_name

Here is a specific example:

SELECT MAX(Salary) FROM Employees

The SUM function adds up all the values for a given column and returns the sum. Here is the basic syntax.

SELECT SUM(column_name) FROM table_name

A specific example might look like this:

SELECT SUM(Salary) FROM Employees

So far all the examples we have looked at retrieved data from just one table, but it is not at all uncommon to need records from more than one table. That brings us to the join commands. While there are several types of joins, the most basic is the inner join. Here is the basic syntax:

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=
table_name2.column_name

Here is a more specific example:

SELECT Employees.LastName, Employees.FirstName, Orders.OrderNo FROM Employees INNER
JOIN Orders ON Employees.P_Id=Orders.P_Id ORDER BY Employees.LastName

What this statement is saying is to get certain columns from the Employees table and also to get certain records from the Orders table. It joins them where Employees.P_Id equals Orders.P_Id. This is the most basic type of join.

Now in this section, we have not covered all the various nuances of SQL; however, we have gone over the basics, and you should be able to write simple SQL queries effectively.

The LIMIT command enables you to retrieve rows within a specified range. The LIMIT count determines the number of rows returned; however, this command may return fewer rows than designated if the query itself yields fewer rows.

SELECT * FROM mytable LIMIT 5

This command basically states that you want all the columns in mytable, but only shows the first five records returned.

This command checks if a value is NULL and allows you to return a different value if it is. The format is as follows:

ISNULL(check-expression,replace-expression)

Here is an example:

SELECT Description, ISNULL(Qty, 0.00) AS 'Max Quantity' FROM Sales;

You can create, change, or even delete entire tables with SQL commands. These commands may be more important to a network administrator than the queries we covered previously in this chapter. The reason is that it is much more likely that a network administrator will be creating databases or tables, rather than retrieving data from them.

First, creating an entire database is quite easy. Here is the basic syntax:

CREATE DATABASE database_name

Here is an example:

CREATE DATABASE my_db

Creating a table is just a little more complicated. You have to identify the column names and data types. Here is the basic syntax:

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type,
 column_ name3 data_type, .... )

Now let’s look at an actual example:

CREATE TABLE Employees
( P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255) )

The ALTER TABLE statement is used to add, drop, or modify columns in an existing table. The basic syntax looks like this:

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype

Here are some examples:

ALTER TABLE Employees ADD DateOfHire date
ALTER TABLE Employees DROP COLUMN DateOfHire

The SQL UNION operator combines the results of two or more SELECT statements. Here is the basic syntax:

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

Here is an example:

SELECT LastName FROM Employees
UNION
SELECT LastName FROM Customers

The SELECT INTO statement retrieves data from one table and inserts it into another table. Here is the basic syntax:

SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name

Here is an example:

SELECT * INTO Employees_Backup FROM Employees

Database administrators utilize triggers to establish relationships among different databases. For example, when new records are added to one table, new records are subsequently added to related tables. Triggers are essentially stored procedures that automatically execute in response to certain events. Associated with a specific table, a trigger is configured to execute when records are modified (inserted, updated, or deleted). We will see how to create triggers when we examine Postgres Admin.

In this section, we will provide you with a brief tutorial of the basic functions in MySQL Administrator, along with screenshots where appropriate. It should be noted that this is just a brief tutorial designed to assist the MySQL novice in getting started. If you intend to work with MySQL on a professional level, at a minimum, you should carefully study all documentation available at www.mysql.org. You may also consider reviewing the help file that comes with MySQL Administrator.

When you first launch the administrator, you will be prompted to log in (see Figure 18.4).

Figure 18.4. MySQL Administrator login.

This will take you to the main screen (see Figure 18.5), which begins with a summary of the server status, including the version of MySQL currently running.

Figure 18.5. MySQL Administrator main screen.

You also have a number of options on the left-hand side, which are used to manage this MySQL server. We will look briefly at each of these.

The first option, Service Control (see Figure 18.6), is the easiest. It simply allows you to stop or start the service, and it provides you with any log messages from the service startup.

Figure 18.6. MySQL Administrator Service Control.

The next option, Startup Parameters, is considerably more complex (see Figure 18.7), but don’t worry. You can leave all these settings with their default values. In fact, unless you have a compelling reason to do otherwise, you should leave them with default values.

Figure 18.7. MySQL Administrator Startup Parameters.

Frankly, the first tab should absolutely be left with default settings. Changing the port you use or the directories for critical files can render your database server unusable.

The User Administration screen comes next (see Figure 18.8). This will allow you to add or edit users. This screen is very easy to follow.

Figure 18.8. MySQL Administrator User Administration.

The next screen may be the simplest of all. It simply shows you current threads running and any current user connections (see Figure 18.9).

Figure 18.9. MySQL Administrator Server Connections.

This brings us to the Server Health screen (see Figure 18.10).

Figure 18.10. MySQL Administrator Server Health.

This may be one of the most critical screens. The first few tabs give you information on CPU and memory usage, as well as statistics such as uptime. The Server Variables tab allows you to view a host of server variables, including the default character set and the port. The final tab, Administration & Security Advisors, gives you tips on securing your MySQL installation.

The Server Logs tab provides a very easy and convenient way for you to view the MySQL Server Logs (see Figure 18.11).

Figure 18.11. MySQL Administrator Server Logs.

The Backup and Restore Backup options provide a very convenient and user-friendly way to create database backups and to restore them (see Figure 18.12).

Figure 18.12. MySQL Administrator Backup and Restore the server.

There are a number of backup options you can set; you can even schedule the backup to occur at specific times. It is critical that your database be backed up frequently. How frequently is dependent on your environment. For example, a high school might only need to back up once per day. But an ecommerce site might get backed up every hour.

The other two options on the left are not critical for you at this point. For example, Replication Status lets you know the status of replicating your server with another server. In situations where high availability is critical, it is common practice to replicate a server so there is a backup server running and ready to go if there is a problem. While this is an important issue, it is really beyond the scope of our basic introduction to MySQL.

For those who insist on working with Postgres via shell commands, let’s look at a few basic commands

To access a database, use:

psql mydatabasename

If you leave off the database name, then it will default to your user account name and your default database. When using psql, you will be greeted with the following message:

Welcome to psql 9.0.1 (or whatever your version number is)

Postgres expects you to use the SELECT command (just like with SQL), even to get information about Postgres, for example:

SELECT version();
SELECT current_date;

To exit psql, just type:

q

You can create a user with this command:

createuser chuck --pwprompt

To access a specific database as a specific user:

psql -U chuck -W mytestdb

Beyond these commands, you can execute SQL statements right at the shell. For more details, go to www.postgresql.org/docs/9.0/interactive/index.html.

Fortunately, it also has a nice, easy-to-use GUI, called pgAdmin. You can install it by using the Add/Remove programs portion of your desktop (see Figure 18.14).

Figure 18.14. Install Postgres Admin.

The pgAdmin screen is laid out a bit differently than MySQL Admin, but it has many similar functions (see Figure 18.15).

Figure 18.15. pgAdmin main screen.

To create a new database, you first create a new server. You start by selecting File > Add Server (see Figure 18.16) and simply fill in the details.

Figure 18.16. pgAdmin connects to a new server.

Note that it is not uncommon for people to experience issues at this point. Those issues will be specific to your Linux distribution, so if you experience any you will need to refer to your distributions documentation. For example, Debian frequently has a permissions issue at this point.

After you have connected to a database (or created a new one), your pgAdmin screen will display elements of that database (see Figure 18.17).

Figure 18.17. pgAdmin viewing a database.

Creating a new table (or any other element of a database) is a very easy matter in pgAdmin. You simply choose a new table and fill in the data (see Figure 18.18).

Figure 18.18. pgAdmin adding a table.

You can also expand any server to see its databases, and you can expand the database to see its tables, triggers, functions, and all its elements (see Figure 18.19).

Figure 18.19. pgAdmin viewing database elements.

pgAdmin makes administering your Postgres database remarkably easy. I recommend you spend a little time familiarizing yourself with this tool. As you have already seen, much of it is self-explanatory.

Note that Postgres does not support all the standard SQL commands. Whether you are using the shell commands or pgAdmin, there are several functions that Postgres does not support, which are listed here: