CS & IT Solutions

To remove a table from a MySQL database, and remove all of its data, use the following SQL command:

drop table if exists recipes;

The command will conditionally delete a table if it exists. The ‘if exists’ syntax is optional, but is useful when using SQL commands in a file, such as when importing data, as it will not display errors.

The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

courtesy http://www.tech-recipes.com/rx/277/drop-or-delete-a-table-in-mysql/

To list the databases that exist in a MySQL server, use the ’show databases’ SQL command:

show databases;
+—————–+
| Database |
+—————–+
| financial |
| mysql |
| test |
+—————–+

The mysql database holds user priviledge information and is required for operation of MySQL. The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

courtesy http://www.tech-recipes.com/rx/274/display-a-list-of-databases-on-a-mysql-server/

A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person’s social security number). When more than one column is used, the combination of column values must be unique.

When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command:

CREATE TABLE `test1` (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);

Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).

courtesy http://www.tech-recipes.com/rx/377/create-a-mysql-table-with-a-primary-key/

For the user molly to change her password to TrckTrt, she would run this command from the shell:

mysqladmin -u molly -p password TrckTrt

The mysqladmin program will prompt for the old password.

courtesy http://www.tech-recipes.com/rx/193/change-a-mysql-user-password/

This slick MySQL syntax allows you to increment or decrement an existing number in a table without first having to read the value. This is a nice way to increment an access counter.

To increment the value ‘counter’ by one for the row in table ‘images’ where ‘image_id’ is ‘15′, use:

UPDATE images SET counter=counter+1 WHERE image_id=15

To decrement the value, use ‘counter=counter-1′ instead. Incrementing or decrementing by other values (or using whatever valid arithmetic arm flexing you need) will work, too.

courtesy http://www.tech-recipes.com/rx/2139/mysql_increment_an_exisitng_value/