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;