Logging in

$ mysql -h hostname -u root -p

If you omit the password value following the -password or -p option on the command line, mysql prompts for one.

Example of password typed in clear text on the commandline

$ mysql -h hostname -u root -psecret

Create a MySQL database

mysql> create database [database_name];

List all databases on the MySQL server

mysql> show databases;

Use a database

mysql> use [db_name];

The USE db_name statement tells MySQL to use the db_name database as
the default (current) database for subsequent statements. The database
remains the default until the end of the session or another USE
statement is issued.

See all the tables in a database

mysql> show tables;

See database field formats

mysql> describe [table name];

DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. These statements also display
information for views.

Deleting a database

mysql> drop database [database name];

DROP DATABASE drops all tables in the database and deletes the
database.

Deleting a table

mysql> drop table [table_name];

DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed.

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a

mysql> SELECT * FROM [table name] WHERE [column] RLIKE "^a";

RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

Change a users password

$ mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Delete a user

mysql> DROP USER 'jeffrey'@'localhost';

The statement removes privilege rows for the account from all grant tables.

View privileges/grants/rights for a user

show grants for 'user'@'localhost';

Set a root password if there is on root password

# mysqladmin -u root password newpassword

Update database permissions/privileges.

mysql> flush privileges;

Delete a column

mysql> alter table [table name] drop column [column name];

Add a new column to db

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name

mysql> alter table [table name] change column old_col_name new_col_name;

// CrashMAG