More MySQL

From SlackWiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
...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;