Basic MySQL

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,

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,

PHP Functions Php tutorial - imagemagick php.ini Basics PHP Sessions Cookies Versus Sessions PHP Web-Related Variables PHP ERRORS maximum size of a file uploaded Php Image upload php file_get_contents	MySQL Data on the Web What are GET and POST php and pdf $_ENV and $_SERVER PEAR with php SELECTING DATA PHP prevent hijacking with PHP LAMP PHP MySQL Functions PHP Zip File Functions	Substrings PHP PHP Variable names PHP magic methods How to get current session id Add variables into a session $_GET , $_POST,$_COOKIE different tables present in mysql PHP CURL php Sessions page PHP-sorting an array
PHP-count the elements of array Operators for If/Else Statements PHP file uploading code PHP global variables Testing working using phpinfo PHP Code for a Valid Number PHP-Associative Arrays PHP mvc tutorial PHP get_meta_tags-Extracts difference between print and echo	PHP best tutorial-PHP variables Reading DOC file in PHP PHP interview questions convert time PHP PHP implode array elements header function-PHP PHP-Renaming Files Directories PHP Classes in_array function in PHP keep your session secure PHP	Web Application with PHP What is SQL Injection PHP-extract part of a string PHP urlencode PHP- know browser properties PHP- Extracting Substrings Checking Variable Values /Types PHP-best 20 Open Source cms IP AddressPHP PHP-Scope Resolution Operator
how create new instance of object how eliminate an object PHP- ob_start XML file using the DOM API PHP- MVC PHP- CAPTCHA PHP- Position of a Value in an Array PHP-Mail Functions PHP-difference include vs require calculate the sum of values in an array	PHP-total number of rows Show unique records mysql MySQL Triggers MySQL data directory MySQL Subqueries PHP- Networking Functions PHP- Operators Restore database Conditional Functions mysql PHP-function overloading	Friend function mysql_connect /mysql_pconnect PHP-Error Control Operators what is IMAP Apache-Specific Functions Send Email from a PHP Script SQL inherently WAMP, MAMP, LAMP Php tutorial-SYMBOLS Table Types-MySQL
PHP-Encryption data management PHP Array Running MySQL on Windows Maximum Performance MySQL XML-RPC PHP-static variables Advanced Database Techniques FTP Codeigniter Apache Pool Size	Why NoSQL MySQL Server Performance Database software SQL Interview Answers PHP Redirect	PHP Interview Questions with Answers Advanced PHP