Basic MySQL
Create or Drop a Database
Starting with something simple, you can use the CREATE command to create a new database. The syntax is
CREATE DATABASE IF NOT EXISTS [yourDBName];
When you create a database with this command, you're really just creating a directory to hold the files that make up the tables in the database.
To delete an entire database from the system, use the DROP command:
DROP DATABASE IF EXISTS [yourDBName];
Be extremely careful when using the DROP command,
because once you drop the database,
You can also use the CREATE command to create a table within the current database. The syntax is
CREATE TABLE [yourTableName] ([fieldName1] (type], [fieldName2]
[type], ...) [options]
To delete a table from the current database, use the DROP command:
DROP TABLE [yourTableName];
Be extremely careful when using the DROP command, because once you drop the tables,
CREATE DATABASE IF NOT EXISTS [yourDBName];
When you create a database with this command, you're really just creating a directory to hold the files that make up the tables in the database.
To delete an entire database from the system, use the DROP command:
DROP DATABASE IF EXISTS [yourDBName];
Be extremely careful when using the DROP command,
because once you drop the database,
You can also use the CREATE command to create a table within the current database. The syntax is
CREATE TABLE [yourTableName] ([fieldName1] (type], [fieldName2]
[type], ...) [options]
To delete a table from the current database, use the DROP command:
DROP TABLE [yourTableName];
Be extremely careful when using the DROP command, because once you drop the tables,
Altering a Table
To add a column to a table, use this:
ALTER TABLE [yourTableName] ADD [newColumn] [fieldDefinition];
To delete a column from a table, use this:
ALTER TABLE [yourTableName] DROP [columnName];
To change a column from one type to another, use this:
ALTER TABLE [yourTableName] CHANGE [columnName]
[newfieldDefinition];
To make a unique column in your table, use this:
ALTER TABLE [yourTableName] ADD UNIQUE [columnName]
([columnName]);
To index a column in your table, use this:
ALTER TABLE [yourTableName] ADD INDEX [columnName]
([columnName]);
Using the ALTER command alleviates the need to delete an entire
table and re-create it .
Insert, Update, or Replace tables record
The INSERT and REPLACE commands populate your tables one record at a time. The syntax of INSERT is
INSERT INTO [yourTableName] ([fieldName1], [fieldName2], ...)
VALUES ('[value of fieldName1]', '[value of fieldName2]'...);
When inserting records, be sure to separate your strings with single quotes or double quotes. If you use single quotes around your strings and the data you are adding contains apostrophes, avoid errors by escaping the apostrophe (\ ') within the INSERT statement. Similarly, if you use double quotes around your strings and you want to include double quotes as part of the data, escape them (\ ") within your INSERT statement.
The REPLACE command has the same syntax and requirements as the INSERT command. The only difference is that you use REPLACE to overwrite a record in a table, based on a unique value:
REPLACE INTO [yourTableName] ([fieldName1], [fieldName2], ...)
VALUES ('[value of fieldName1]', '[value of fieldName2]'...);
The UPDATE command modifies parts of a record without replacing the entire record. To update an entire column in a table with the same new value, use this:
UPDATE [yourTableName] SET [fieldName] = '[new value]';
If you want to update only specific rows, use a WHERE clause:
UPDATE [yourTableName] SET [fieldName] = '[new value]' WHERE [some
expression];
UPDATE can be a very powerful SQL command.
Deleting from a Table
Like the DROP command, using DELETE without paying attention to what you're doing can have horrible consequences in a production environment. Once you drop a table or delete a record, it's gone forever. Don't be afraid; just be careful. To delete all the contents of a table, use the following:
DELETE FROM [yourTableName];
If you want to delete only specific rows, use a WHERE clause:
DELETE FROM [yourTableName] WHERE [some expression];
If you're going to start deleting records, be sure you have a backup.
Selecting from a Table
When creating database-driven Web sites, the SELECT command will likely be the most often-used command in your arsenal. The SELECT command causes certain records in your table to be chosen, based on criteria that you define. Here is the basic syntax of SELECT:
SELECT [field names] FROM [table name]
WHERE [some expression]
ORDER BY [field names];
To select all the records in a table, use this:
SELECT * FROM [yourTableName];
To select just the entries in a given column of a table, use this:
SELECT [columnName] FROM [yourTableName];
To select all the records in a table and have them returned in a particular order, use an expression for ORDER BY. For example, if you have a date field for record entries and you want to see all the record entries ordered by newest to oldest, use this:
SELECT * FROM [yourTableName] ORDER BY [dateField] DESC;
DESC stands for "descending." To view from oldest to newest, use ASC for "ascending." ASC is the default order.
You can also perform mathematical and string functions within SQL statements,