More MySQL

...in no particular order.

NOTE: All actions on this page assume you are already logged into the MySQL console and have the proper permissions.

How do I select which database to work on?

You use it.

USE database_name;

How do I create a table in a database?

1) make sure you are USE-ing the correct database!

2) CREATE TABLE table_name(column1 type(size), column2 type(size), ...etc. );

EXAMPLE:

CREATE TABLE products(part_num char(6), description char(25), price decimal(5));

How do I see what the 'columns' are in a table?

1) make sure you are USE-ing the correct database!

2) DESCRIBE table_name

This will show you the column information including the data type and length.

How do I show the records in a table?

Make sure you are USE-ing the correct database!

1) Create a temporary table from a SELECT statement-

SELECT CREATE TABLE table_name;

2) Show ENTIRE contents of a database including record names:

SELECT * FROM table_name;

WARNING: Don't use on a large table!!

3) Show selected contents from a table:

SELECT * FROM table_name WHERE condition;

How do I clear the primary key on a table?

1) make sure you are USE-ing the correct database!

2) ALTER TABLE table_name DROP PRIMARY KEY;

How do I add a primary key to a table?

1) make sure you are USE-ing the correct database!

2) ALTER TABLE table_name ADD PRIMARY KEY (column_name);

There's a blank space in a column name and now I can't fix it

To get MySQL to recognize a column name with a blank space in it, you must put the column name in back-ticks (`) NOT apostrophes (').

EXAMPLE: ALTER TABLE inventory ADD PRIMARY KEY (`Part Number`);

How do I list all the users?

A) To list all the users (regardless of database or table):

SELECT User,Host FROM mysql.user;

B) To list all the users on a database.table:

Don't know this one... working on it.

C) To list all the databases & tables granted to a user:

SHOW GRANTS FOR user@host;