The Basics of 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.
Build 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 discussed here 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:
Capitalization: In this example, SQL language words are in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. This makes it easier for you to read —MySQL doesn’t need 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.
Send SQL queries
You can send a SQL query to MySQL several ways. Here are 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.