More MySQL

From SlackWiki
Jump to: navigation, search
...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;