MySQL Summary reference: PHP an MySQL for Dummies by Janet Valade, published by Wiley 2002
MySQL Advantages:
  • Fast
  • Cheap (like free) and widely used
  • Works on many OSs
  • Secure, Easy to use (standard SQL advantages)
  • Supports large dbs (50 mega-rows, 4-8Gb file sizes)
  • Customizable (open source GPS licenses available)
  • Memory efficient
How to start a database application:
  • Identify why you are doing this application and what you want from it
  • List exactly what the application is to do
  • Detail each of the tasks
  • Design the database: list all types of information to be used in application
  • Name the database
  • Identify the objects - which will become the tables; name the tables (use singular)
  • Organize the tables so that all needed fields (attributes) are included, but there will be no duplication of data between all tables. Name the fields (columns).
  • determine the unique primary key (combination of fields) for each table
  • define the field defaults, field type (like what are allowable values)
INSTALLING MySQL
in Windowsin Linux/UNIX
first check if it is currently running:
would be on system tray at bottom of screen, possibly as traffic signal with a green light. type "ps -ax | more", and look for a program called "mysqld".
If not running, check to see if it has been installed:
Look for a program called "WinMySQLadmin" somewhere on the Start/Programs menu. If not found, look for it in /mysql/bin. type "find / -name "mysql*".
Starting MySQL
- Start WinMySQLadmin
- right-click in the WinMySQLadmin window
- select the menu item for your OS
- start the server 		- go to the mysql/bin directory
- type "safe-mysqld &"
- check that MySQL server is started (by typing "ps -ax | more")
CREATING/MODIFYING DATABASE STRUCTURE
First connect to MySQL using the following php code in a php page:
mysql_connect("name of host computer", "user account name", "password");
Then use SQL commands. This can be done in php with:
$result = mysql_query("the actual query");
if ($result == 0) echo("<b>Error ".mysql_errno().": ".mysql_error()."</b>");
elsif (@mysql_num_rows($result) == 0) echo("no results found message")
else ... output table using following php/mysql functions:

mysql_num_fields($result)

mysql_field_name($result, field-number)

$row = mysql_fetch_row($result)

$row(field-number)

To create a database use the following SQL query:
CREATE DATABASE db-name
To create a table use the following SQL query:
CREATE TABLE table-name (
field-name field-type [field-characteristics, such as NOT NULL]
...
PRIMARY KEY(field-name)
)
To see what tables are in a database use the following SQL query:
SHOW TABLES
To see what columns are in a table:
SHOW COLUMNS FROM table-name
To eliminate a table:
DROP TABLE table-name
To modify a table structure, append any of the following to the query "ALTER TABLE table-name":
ADD column-name field-type [field-characteristics]
ALTER column-name SET DEFAULT value
ALTER column-name DROP DEFAULT
CHANGE column-name new-column-name field-type [field-characteristics]
DROP column-name
MODIFY column-name field-type [field-characteristics]
RENAME newtable-name
ADDING NEW ACCOUNTS/CHANGING PERMISSIONS/PASSWORD
After connecting to MySQL with an account, such as root, with sufficient permissions, the GRANT query both adds accounts and modifies them:
GRANT [permission [(columns)] ON tablename TO account-name@hostname [IDENTIFIED BY 'password']
where permission can be any of: [ALL, ALTER(ability to alter table structure), CREATE (databases or tables), DELETE (rows in tables), DROP (databases or tables), GRANT (permissions on a MySQL account), INSERT (new rows into tables), SELECT (read data from tables), SHUTDOWN (MySQL server), UPDATE (data in table), USAGE (no permissions at all).

table-name can be a comma separated list of table-names; at least one table-name is required; for all tables in all databases, use *; for tables in non-current database, use database.table-name; *.* is all tables in all databases The account account-name is created, if it doesn't already exist, and the password is the new password, overriding the old one, if an account already existed. To remove permissions:

REVOKE permission (columns) ON table-name FROM account-name@host-name
Note: to disable account, use ALL in the above query in place of permission
ADDING/MODIFYING/DELETING rows to a table with SQL
To add a row:
INSERT INTO table-name [(col-name, col-name,...)] VALUES (val1, val2,...)
To add from a text file:
LOAD DATA INFILE "file-name" INTO TABLE table-name [options]
where options can include:
FIELDS TERMINATED BY "character" -- default is newline
FIELDS ENCLOSED BY "character"
LINES TERMINATED BY "character" -- default is the tab char
MySQL BACKUP/RESTORE PROCEDURES
BACKUP: Store one backup on server, other computer(s), and other media.

To make a backup, in the mysql/bin directory run the mysqldump program. This generates a text file which is a sequence of SQL queries that can completely regenerate a database. For example, in Linux type:

cd /usr/local/mysql/bin
mysqldump --user=account-name --password=password database-name > path-spec/backupfile-name

Only SELECT permission is required by the account doing the backup.

REPAIR: if the database gets damaged, for example when an error message of the form "incorrect key file for table: 'glarf'" occurs, first try repairing it with the repair program provided: myisamchk:

Go to the mysql/bin directory, shutdown the server, and run myisamchk. For example in Linux type:

cd /usr/local/mysql/bin
mysqladmin - u account-name -p shutdown
myisamchk -r path-spec/database-name/table-name.MYI
Note: *.MYI can be used for tables to repair all tables in a database. Then just start the MySQL server by typing: "mysqladmin -u account-name -p start".

If, after the preceding steps, the table is still broken, then retry it, only using the -o option instead of the -r option.

RESTORE:

If the database is to be moved elsewhere, or the database can't be repaired, then (after DROPping the corrupt table) run a PHP SQL query-submitting page, cut each CREATE/INSERT query that is used to build the table from the backup file, paste it in the query input field, and submit them one at a time..... unless there are too many.

In the case of restoring a large table or entire database:

If entire database, prepend to the backup-file:
CREATE DATABASE database-name;
use database-name;
Then, in the mysql/bin directory, type:
mysql -u account-name -p database-name < path-spec/backup-file-name
Complete MySQL Documentation: http://www.mysql.com/documentation