Enter command line mode for MySQL
# enter without user specified mysql # enter for specific user and prompt for password mysql -u _USERNAME_ -p
NOTE: Substitute the user's MySQL login name for _USERNAME_.
Database level commands
Create database
CREATE DATABASE _DATABASE_NAME_ DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
NOTE: Substitute the name of the database for _DATABASE_NAME_.
Delete database
DROP DATABASE _DATABASE_NAME_;
NOTE: Substitute the name of the database for _DATABASE_NAME_.
Switch to a database
use _DATABASE_NAME_;
User level commands
List of users
SELECT User FROM mysql.user;
NOTE: No substitutions.
Add user
CREATE USER '_USERNAME_'@'localhost' IDENTIFIED BY '_USERPASSWORD_';
NOTE: Substitute the user's MySQL login name for _USERNAME_ and the user's MySQL password for _USERPASSWORD_.
Give user permissions for a specific database
use _DATABASE_NAME_; GRANT ALL ON _DATABASE_NAME_.* TO _USERNAME_@localhost IDENTIFIED BY '_USERPASSWORD_';
NOTE: Substitute the user's MySQL login name for _USERNAME_, the user's MySQL password for _USERPASSWORD_, and the name of the database for _DATABASE_NAME_.
List databases for current user
show databases;
NOTE: No substitutions.
Table level commands
Queries for table structure
List tables in a database
show tables from _DATABASE_NAME_; OR use _DATABASE_NAME_; show tables;
Show row counts for each table
select table_name, table_rows from information_schema.tables where table_schema='_DATABASE_NAME_';
List tables with specified column name
select distinct table_name from information_schema.columns where column_name in ('_COLUMN_NAME_') and table_schema='_DATABASE_NAME_';
List of tables with foreign key IDs -- LIKE ('%_id')
select distinct table_name from information_schema.columns where column_name LIKE ('%_id') and table_schema='exhibits_staging';
List columns in a table
show columns from _TABLE_NAME_ in _DATABASE_NAME_; OR use _DATABASE_NAME_; show columns from _TABLE_NAME_;
Queries for table values
List values for a specific column
select _COLUMN_NAME_ from _DATABASE_NAME_._TABLE_NAME_; OR use _DATABASE_NAME_; select _COLUMN_NAME_ from _TABLE_NAME_;
List values for a range of rows
select _COLUMN_NAMES_ from _DATABASE_NAME_._TABLE_NAME_ limit _START_, _NUM_ROWS_; OR use _DATABASE_NAME_; select _COLUMN_NAMES_ from _TABLE_NAME_ limit _START_, _NUM_ROWS_; OR # sort by id for subset (NOTE: where clause has to come before sorting) select _COLUMN_NAMES_ from _TABLE_NAME_ where _WHERE_CLAUSE_ order by id; # sort by id and list last N rows (NOTE: limit has to come after sorting) select _COLUMN_NAMES_ from _TABLE_NAME_ order by id desc limit _NUM_ROWS_;
NOTE: It starts one row after the _START_ value. If you have O
, it will list rows 21-25.
Where with wildcard using LIKE
# % is wildcard. To force results to start with the query string, eliminate the first % select _COLUMN_NAMES_ from _TABLE_NAME_ where _COLUMN_NAME_ like '%_QUERY_STRING_%';
Get all that don't start with X using <> operator
select id, COL2 from users where substr(COL2,1,5) <> 'guest';
Get all that have one of a set of values using IN
select id, COL2 from users where COL2 IN ('vulue1','value2','value3');
Count and Count Distinct
select count(id) from TABLE_NAME; select count(distinct COLUMN_NAME) from TABLE_NAME;
Modifying values in tables
Add a record
insert into TABLE_NAME (id, COL2, COL3) values(214,2,'three');
Edit a record
replace into TABLE_NAME values(54,2,'new three'); # replaces entire record update TABLE_NAME set COL_NAME = 'new three' where id = 54; # where id = 54 limits update specific record
Search and replace part of a value in a record's column
update TABLE_NAME set COLUMN_NAME = replace(COLUMN_NAME,'OLD_SUBSTRING', 'NEW_SUBSTRING') where id=35; # where id=35 limits update to specific record
Delete a record
delete from TABLE_NAME where id=897;
Joins
INNER
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key
LEFT
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
RIGHT
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key
OUTER
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key