PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

Book V: MySQL

MySQL Account Privileges

Chapter 1: Introducing MySQL

In This Chapter

Discovering how MySQL works

Communicating with MySQL

Securing data stored in MySQL

Many dynamic websites require a backend database. The database can contain information that the web pages display to the user, or the purpose of the database might be to store information provided by the user. In some applications, the database both provides available information and stores new information.

MySQL, the most popular database for use in websites, was developed to be fast and small, specifically for websites. MySQL is particularly popular for use with websites that are written in PHP, and PHP and MySQL work well together.

This chapter provides an introduction to MySQL, and explains how it works and how you can communicate with it. As discussed in Book IV, Chapter 3, much of this information also applies to the SQLite database introduced in that chapter.

Examining How MySQL Works

The MySQL software consists of the MySQL server, several utility programs that assist in the administration of MySQL databases, and some supporting software that the MySQL server needs (but you don’t need to know about). The heart of the system is the MySQL server.

The MySQL server is the manager of the database system. It handles all your database instructions. For instance, if you want to create a new database, you send a message to the MySQL server that says, for instance, "create a new database and call it newdata." The MySQL server then creates a subdirectory in its data directory, names the new subdirectory newdata, and puts the necessary files with the required format into the newdata subdirectory. In the same manner, to add data to that database, you send a message to the MySQL server, giving it the data and telling it where you want the data to be added.

Before you can pass instructions to the MySQL server, it must be running and waiting for requests. The MySQL server is usually set up so that it starts when the computer starts and continues running all the time. This is the usual setup for a website. However, it isn’t necessary to set it up to start when the computer starts. If you need to, you can start it manually whenever you want to access a database. When it’s running, the MySQL server listens continuously for messages that are directed to it. Installing and starting the MySQL server are discussed in Book I, Chapter 4.

Understanding Database Structure

MySQL is a Relational Database Management System (RDBMS). Your MySQL server can manage many databases at the same time. In fact, many people might have different databases managed by a single MySQL server. Each database consists of a structure to hold the data and the data itself. A database can exist without data, only a structure, be totally empty, twiddling its thumbs and waiting for data to be stored in it.

Data in a database is stored in one or more tables. You must create the database and the tables before you can add any data to the database. First you create the empty database. Then you add empty tables to the database.

Database tables are organized like other tables that you’re used to — in rows and columns. Each row represents an entity in the database, such as a customer, a book, or a project. Each column contains an item of information about the entity, such as a customer name, a book name, or a project start date. The place where a particular row and column intersect, the individual cell of the table, is called a field.

Tables in databases can be related. Often a row in one table is related to several rows in another table. For instance, you might have a database containing data about books you own. You would have a book table and an author table. One row in the author table might contain information about the author of several books in the book table. When tables are related, you include a column in one table to hold data that matches data in the column of another table.

Only after you’ve created the database structure can you add data. More information on database structure and instructions for creating the structure is provided in Chapter 3 of this minibook.

Communicating with MySQL

All your interaction with the database is accomplished by passing messages to the MySQL server. The MySQL server must be able to understand the instructions that you send it. You communicate using Structured Query Language (SQL), which is a standard computer language understood, at least in some form, by most database management systems.

To make a request that MySQL can understand, you build a SQL statement and send it to the MySQL server. The following sections tell you how to do that.

Building SQL queries

SQL is almost English; it’s made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work.

The first word of each statement is its name, which is an action word (a verb) that tells MySQL what you want to do. The statements that we discuss in this minibook are CREATE, DROP, ALTER, SHOW, INSERT, LOAD, SELECT, UPDATE, and DELETE. This basic vocabulary is sufficient to create — and interact with — databases on websites.

The statement name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.

The following is a typical SQL statement. As you can see, it uses English words:

SELECT lastName FROM Member

When a statement uses SELECT, it's known as a query, because you're querying the database for information. This query retrieves all the last names stored in the table named Member. More complicated queries, such as the following, are less English-like:

SELECT lastName,firstName FROM Member WHERE state="CA" AND

city="Fresno" ORDER BY lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. Although this query is less English-like, it’s still pretty clear.

Here are some general points to keep in mind when constructing a SQL statement, as illustrated in the preceding sample queries:

Capitalization: In this book, we put SQL language words in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. We did this to make it easier for you to read — not because MySQL needs this format. The case of the SQL words doesn't matter; for example, select is the same as SELECT, and from is the same as FROM, as far as MySQL is concerned.

On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix or Linux. When you're using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — for example, lastname isn't the same as lastName. Windows, however, isn't as picky as Unix and Linux; from its point of view, lastname and lastName are the same.

Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.

Quotes: Notice that CA and Fresno are enclosed in double quotes (") in the preceding query. CA and Fresno are a series of characters called text strings, or character strings. You're asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don't enclose the numbers in quotes. (In Chapter 3 of this minibook, we explain the types of data that you can store in a MySQL database.)

We discuss the details of specific SQL queries in the sections of the book where we discuss their uses. For instance, in Chapter 3 in this minibook, we discuss the CREATE query in detail when we cover the details of creating the database structure; we also discuss the INSERT query when we tell you how to add data to the database.

Sending SQL queries

You can send a SQL query to MySQL several ways. In this book, we cover the following two methods of sending queries:

The mysql client: When you install MySQL, a text-based mysql client is automatically installed. This simple client can be used to send queries.

PHP built-in functions: You communicate with a MySQL database from PHP scripts by using PHP built-in functions designed specifically for this purpose. The functions connect to the MySQL server and send the SQL query. Accessing MySQL databases from PHP scripts is discussed in detail in Chapter 5 of this minibook.

Using the mysql client

When MySQL is installed, a simple, text-based program called mysql (or sometimes the command line interface or the CLI) is also installed. Programs that communicate with servers are client software; because this program communicates with the MySQL server, it’s a client. When you enter SQL queries in this client, the response is returned to the client and displayed onscreen. The monitor program can send queries across a network; it doesn’t have to be running on the machine where the database is stored.

This client is always installed when MySQL is installed, so it’s always available. It’s quite simple and quick if you know SQL and can type your queries without mistakes.

To send SQL queries to MySQL from the mysql client, follow these steps:

1. Locate the mysql client.

By default, the mysql client program is installed in the subdirectory bin, under the directory where MySQL is installed. In Unix and Linux, the default is /usr/local/mysql/bin or /usr/local/bin. In Windows, the default is c:\Program Files\MySQL\MySQL Server 5.0\bin. However, the client might be installed in a different directory. Or, if you aren't the MySQL administrator, you might not have access to the mysql client.

If you don’t know where MySQL is installed or can’t run the client, ask the MySQL administrator to put the client somewhere where you can run it or to give you a copy that you can put on your own computer.

2. Start the client.

In Unix and Linux, type the path/filename (for example, /usr/local/mysql/bin/mysql). In Windows, open a command prompt window and then type the path\filename (for example, c:\ Program Files\MySQL\MySQL Server 5.0\bin\mysql). This command starts the client if you don't need to use an account name or a password. If you need to enter an account or a password or both, use the following parameters:

• -u user: user is your MySQL account name.

• -p: This parameter prompts you for the password for your MySQL account.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:

mysql -u root -p

3. If you're starting the mysql client to access a database across the network, use the following parameter after the mysql command:

-h host, where host is the name of the machine where MySQL is located.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:

mysql -h mysqlhost.mycompany.com -u root -p

Press Enter after typing the command.

4. Enter your password when prompted for it.

The mysql client starts, and you see something similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 459 to server version: 5.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

5. Select the database that you want to use.

At the mysql prompt, type the following:

use databasename

Use the name of the database that you want to query.

Some SQL statements, such as SHOW DATABASES, don't require that you select a database. For those statements, you can skip Step 5.

6. At the mysql prompt, type your SQL statement followed by a semicolon (;) and then press Enter.

If you forget to type the semicolon (;) at the end of the query, the mysql client doesn't execute the statement. Instead, it continues to display the prompt (mysq>) until you enter a semicolon.

The response to the statement is displayed onscreen.

7. To leave the mysql client, type quit at the prompt and then press Enter.

You can use the mysql client to send a SQL statement that you type yourself, and it returns the response to the statement.

Protecting Your MySQL Databases

You need to control access to the information in your database. You need to decide who can see the data and who can change it. If a bad guy gets a list of your customers’ private information (such as credit card numbers), you clearly have a problem. You need to guard your data.

MySQL provides a security system for protecting your data. The system includes the following:

MySQL accounts: No one can access the data in your database without an account. The account has a name the user must use. The account can also have a password that users must provide before they access the account. In addition, each account specifies where you can access the data from, such as only from the current computer or only from a specific domain.

Permissions: MySQL uses account permissions to specify who can do what. Anyone using a valid account can connect to the MySQL server, but he or she can do only those things that are allowed by the permissions for the account. For example, an account might be set up so that users can select data but cannot insert or update data. Or, an account might be set up so that it can change the data in a specific table, but can only look at the data in another table.

You can create and delete accounts, add and change passwords, and add and remove permissions with SQL queries. You can send the SQL queries with either of the methods described in the preceding section. You can also manage your MySQL accounts with features provided by phpMyAdmin. We describe administering your MySQL databases in Chapter 2 of this minibook.