If you ever had to deal with MySQL in the past, I'm sure you had to spend lots of time searching on Google (as I did..).

To save you some searching, I'll post some commands I "learned while doing".

• I'll assume all you know about MySQL is "mysql -u root -p"

The above means:

1. You are logged-in to a MySQL Box as "root".
2. You know the MySQL root's password (which might be different from the root local user...).
3. There is at least 1 database with a couple of tables you can safely play with.

Let's get dirty, let's say you have one MySQL table as follows:

• Table name "login" - Table Structure (aka schema) as follows:

Field	Type	Null	Key	Default	Extra
id	tinyint(3)	NO	PRI	NULL	auto_increment
password	varchar(150)	YES		NULL
username	varchar(150)	YES		NULL

• Table name "login" - Table Content as follows:

id	password	username
1	pantera	phil

 

 

select * from TABLE-NAME;

Select lists a table content.

On a MySQL prompt, type:

mysql> select * from login;

 

delete from TABLE-NAME where id=1;

delete wipes rows based on some condition (always pay attention b/c there's no such thing as a "Recycle Bin").

Example:

mysql> delete from login where id=1;

 

insert into TABLE-NAME (id, password, username) VALUES (1, "usr", "pwd");

insert adds data into rows.

Example:

mysql> insert into login (id, password, username) VALUES (1, "metallica", "lars");

 

describe TABLE-NAME;

describe shows you the schema (the columns definitions).

Example:

mysql> describe login;

Output:

Field	Type	Null	Key	Default	Extra
id	tinyint(3)	NO	PRI	NULL	auto_increment
password	varchar(150)	YES		NULL
username	varchar(150)	YES		NULL

 

ALTER TABLE TABLE-NAME ADD COLUMN new-col-name bool;

Alter allows you to add (& remove) new column(s):

Example:

mysql> ALTER TABLE login ADD COLUMN status bool;

 

select * from mysql.user;

When you run a select against the mysql.user, you will get a list of all the authorised MySQL Users.

Example:

select * from mysql.user;

 

SHOW GRANTS;

Show GRANTS reports your [logged-in] user's permissions.

Example:

mysql> SHOW GRANTS;

Output:

+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost|
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '1e5515b01d57bf15' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

SHOW GRANTS FOR phil;

When you wish to know a PARTICULAR User Permissions.

mysql> SHOW GRANTS FOR phil;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for phil@%|
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, FILE, SHOW VIEW ON *.* TO 'phil'@'%' IDENTIFIED BY PASSWORD '794c6f050dff6713' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';

Grant can also be used by itself To assign (additional) permissions to a particular user.

Example:

mysql> GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.03 sec)

 

REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';

Revoke is the opposite of Grant and it is used to deny permissions to a user.

Example:

mysql> REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.00 sec)