PHP and MySQL - Chapter 4 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 characters can be used in database, table, and column names?

Databases and table identifiers should only contain letters, numbers, and the underscore (no spaces should be used!)

Should you treat database, table and column names as case-sensitive or case-insensitive?

They should be treated as case-sensitive.

What are the three general column types?

What are the differences between CHAR and VARCHAR?

Both of these types store strings and can be set with a maximum length. The primary difference between them is that anything stored as a CHAR will always be stored as a string the length of the column, using spaces to pad it. (The spaces are removed when you retrieve the stored value from the database. )
In a VARCHAR column, the strings will only requre as much space as the string itself, plus one byte for a terminator. This requires less storage space.
Larry mentions that databases are normally faster with fixed-size columns. This is an argument in favor of CHAR.

How do you determine what size (in terms of subtype or length) a column should be?

The general categories of text, number of date/time are the first order determination. The subtypes are selected according to the anticipated requirements of the data to be represented. For example: integer values, subtypes range from TINYINT, which occupies 1 byte, limiting it to a range of -128 to 127 for signed values, or 0 to 255 for unsigned values, then increasing in size with SMALLINT, MEDIUMINT, INT, which are 2, 3, and 4 bytes respectively, and finally BIGINT, which is 8 bytes. His guideline is that the size of any field should be restricted to the smallest possible value, based upon the largest possible input.

What are some of the other properties that can be assigned to columns?

Some properties that are available: NOT NULL, to require a value for a field. You can specify a default value, as in this example:
gender ENUM('M', 'F') default 'F'
Number types can be marked as UNSIGNED, and ZEROFILL. AUTO_INCREMENT is used to indicate that the next highest value is used for a row that is added to the table.

What is a primary key?

A primary key is a unique way to refer to a particular record. It is almost always a number value, it must always have a value, the value must never change, and it must be unique for each record in the table.

If you're using the command-line mysql client to connect to MySQL, what username and password combination is required?

The username and password vary, but they must be values that are established in MySQL as a valid user during the setup procedure, or as an administrative action.