Erik: Copy from old, had no category, placed in Tips

Erik — Thu, 04 Jun 2009 05:22:07 GMT

Copy from old, had no category, placed in Tips

New page

[[Category:Tips]]
::...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 <code>use</code> it.

::<code>USE database_name;

==How do I create a table in a database?==
::1) make sure you are <code>USE</code>-ing the correct database!
::2) <code>CREATE TABLE table_name(column1 type(size), column2 type(size), ...etc. );</code>

:::EXAMPLE:
::::<code>CREATE TABLE products(part_num char(6), description char(25), price decimal(5));</code>

==How do I see what the 'columns' are in a table?==
::1) make sure you are <code>USE</code>-ing the correct database!
::2) <code>DESCRIBE table_name</code>

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 <code>USE</code>-ing the correct database!

1) Create a temporary table from a SELECT statement-
:: <code>SELECT CREATE TABLE table_name;</code>

2) Show ENTIRE contents of a database including record names:
::<code>SELECT * FROM table_name;</code>
::WARNING: Don't use on a large table!!

3) Show selected contents from a table:
::<code>SELECT * FROM table_name WHERE condition;</code>

==How do I clear the primary key on a table?==
::1) make sure you are <code>USE</code>-ing the correct database!
::2) <code>ALTER TABLE table_name DROP PRIMARY KEY;</code>

==How do I add a primary key to a table?==
::1) make sure you are <code>USE</code>-ing the correct database!
::2) <code>ALTER TABLE table_name ADD PRIMARY KEY (column_name);</code>

==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: <code>ALTER TABLE inventory ADD PRIMARY KEY (`Part Number`);</code>

==How do I list all the users?==
::A) To list all the users (regardless of database or table):
::::<code>SELECT User,Host FROM mysql.user;</code>

::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:
::::<code>SHOW GRANTS FOR user@host;</code>

More MySQL - Revision history

Erik: Copy from old, had no category, placed in Tips