PHP and MySQL - Chapter 5 Review questions

What version of MySQL are you using?

My localhost version of MySQL is: Ver 14.14 Distrib 5.5.27, for Win32 (x86)

My webhost provider version is: Server version: 5.1.56-log

What SQL command is used to make a new database? What command is used to make a new table in a database?

A new database is created with CREATE DATABASE databasename

A new table in a database is created with:


USE databasename; (to select the database to create the table in)

CREATE TABLE tablename (

column1name, description,

column2name, description

...);

What SQL command is used to select the database with which you want to work?


USE databasename;

What SQL commands are used for adding records to a table?

INSERT INTO tablename (column1, column2...) VALUES (value1, value2...)
(other variations of INSERT, using no column names, etc.)
REPLACE - can be used instead of INSERT

What types of values must be quoted in queries? What types of values shouldn't be quoted?

Numeric values shouldn't be quoted
String values (for CHAR, VARCHAR, and TEXT column types) must always be quoted
Date and time values must always be quoted
Functions must not be quoted!
The word NULL must not be quoted

What does the asterisk in SELECT * FROM tablename mean? How do you restrict which columns are returned by a query

The asterisk means to retrieve every column.

You can restrict the columns that are returned by a query by specifying the column names in the query:


SELECT column1, column2 FROM tablename

What does the NOW() function do?

The NOW() function returns the current date and time on the server.

How do you restrict which rows are returned by a query?

The first way to restrict the number of rows returned is by using the LIMIT clause.

The other way is to use conditionals to establish criteria for selection of rows. A general illustration of the use of conditionals is:
SELECT which_columns FROM which_table WHERE condition(s)

The conditionals follow the WHERE term in the statement. The conditionals can be extensive and what you use depends on the data that is in the table, and the data that is to be extracted. One simple example is to find all people with a specific birth date:


SELECT name FROM people WHERE birth_date = '2011-01-26'

And the conditionsl can become complex, use mathematical and SQL functions to establish selection criteria, etc.

How do LIKE and NOT LIKE differ from simple equality comparisons? Which type of comparison will be faster? What are the two LIKE and NOT LIKE wildcard characters?

LIKE and NOT LIKE differ from simple equality comparisons in that they are more flexible, utilizing wildcard characters consisting of the underscore (_), which matches a single character, and the percent sign (%), which matches zero or more characters.

Conditionals with LIKE and NOT LIKE are generally slower than simple equality comparisons.

How do you affect the sorting of the returned records? What is the default sorting method? How do you inverse the sort? What is the syntax for sorting by multiple columns?

Sorting is affected by using the ORDER BY clause. Generally:

SELECT * FROM tablename ORDER BY columnname

The default sorting method when using ORDER BY is ascending order.

You can reverse the sorting order by adding the keyword DESC to specify a descending order:

SELECT * FROM tablename ORDER BY column DESC

The syntax for sorting by multiple columns is to specify multiple column names after the ORDER BY clause:


SELECT first_name, last_name FROM users ORDER BY last_name ASC, first_name ASC

What does the LIMIT clause do? How does LIMIT x differ from LIMIT x, y?

The LIMIT clause states how many records to return. A query that is qualified with LIMIT x will only return the initial x records from the query.

A query that is qualified with LIMIT x, y will have y records return, starting at record number x.

What SQL command is used to change the values already stored in a table? How do you change multiple columns at once? How do you restrict to which rows the changes are applied?

The UPDATE command is used to modify data that is already stored in the database. The syntax for using the UPDATE command is:


UPDATE tablename SET column=value [,column1=value1]+

That form of the command will update all rows of the table, so most often a WHERE clause is used to specify which rows should be updated.


UPDATE tablename SET column=value [,column1=value1]+ WHERE columnA=value

The LIMIT clause can also be added to set a hard limit to the number of rows that will be modified.

What SQL command is used to delete rows stored in a table? How do you restrict to which rows the deletions are applied?

The DELETE command is used to delete rows from a table. A command such as:


DELETE FROM tablename

Will delete all rows from the specified table. (This is a non-recoverable operation!) In most cases, deletion of specific rows in the table is what is desired. In this more normal case, use of conditions via the WHERE clause is used.


DELETE FROM tablename WHERE condition

And a further safeguard against excessive deletions is to also add a LIMIT clause.

What is an SQL alias? How do you create one? Why is an alias useful?

An alias is a symbolic renaming of an item used in a query. Aliases are created using the term AS, this is shown in the following example:

SELECT registration_date AS reg FROM users

Aliases are useful in many ways. One is that they can obscure the names of the table columns, which can be useful in preventing SQL insertion attacks. Another is that they can be used to refer to an SQL function result, and use that in other places in the query, as in this example:


SELECT CONCAT(last_name, ', ', first_name) as Name FROM users ORDER BY Name;

Also, if an alias is not used in that example, the column name for the returned data will consist of the entire function call string, which is cumbersome at best, and difficult to deal with in some situations, for example, when using PHP to work with SQL query results.