last updated: Monday, July 23, 2018

MYSQL CHEATSHEET

QUICK LINKS

Database Commands

Table Commands

Column Commands

Data Entry Commands

Misc

top
QUICK VIEW PORT
[hide]
NULL

Execute MySql commands from a Bash prompt
NULL:
mysql -u username -p -e "use db_name; source file_with_sql_commands;"   src: NULL
Back up a database:
mysqldump --opt -u username -p db_name > backupfile.sql   src: NULL
Back up only specific table(s) from a database:
mysqldump --opt -u username -p db_name table1 table2 > backupfile.sql   src: NULL
Export table structure for a specific table to a file:
mysqldump --no-data -u username -p db_name table_name > file_name.sql   src: Use mysqldump to get the schema only The Electric Toolbox
To import an sql file from a shell:
mysql -u username -p db_name < file.sql
top

Logging In / Exiting
To log in as 'root' if password has been set:
mysql -u root -p   src: Ubuntu Community Documentation
To log in as 'root' if password has NOT been set:
mysql -u root   src: Ubuntu Community Documentation
To log in as a regular user (non-root):
mysql -u username -p   src: Ubuntu Community Documentation
To exit the MySQL prompt:
\q   src: Ubuntu Community Documentation
top

User Management Commands
To list all users on a MySQL server:
select host, user from mysql.user;   src: devdaily.com - MySQL show users
To create a user with ALL privileges (unsafe) -- this DID NOT WORK the last time I tried it (Sep. 05/17):
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;   src: Ubuntu Community Documentation
To create a user with ALL privileges (unsafe) [** KNOWN TO WORK (tested onSep. 05/17)]:
CREATE USER 'dave'@'localhost' IDENTIFIED BY 'somePassword';
GRANT ALL PRIVILEGES ON * . * TO 'dave'@'localhost';
FLUSH PRIVILEGES;
To create a user with SANE privileges:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database1.* TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword';   src: Ubuntu Community Documentation
To delete a user:
delete from mysql.user WHERE User='user_name';
To reset a user's password:
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password' ;
top

Database Management Commands
To list all databases on a MySQL server:
SHOW DATABASES;
To create a database:
CREATE DATABASE database_name;
To delete a database:
DROP DATABASE database_name;
To select a databse:
USE database_name;
To reset a user's password:
NULL NULL;
top

Table Management Commands
To list all tables in a database:
SHOW TABLES;
To see a table's field formats:
DESCRIBE table_name
To create a simple table with unique index:
CREATE TABLE table_name (
        id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
        first_field_name VARCHAR(20),
        second_field_name VARCHAR(20),
        insertDateTime DATETIME DEFAULT CURRENT_TIMESTAMP
    );
To delete a table:
DROP TABLE table_name;
To rename a table:
RENAME TABLE original_name to new_name;
To make a copy of a table's structure:
CREATE TABLE new_table_name LIKE database_name_of_existing_table.name_of_existing_table;
To make a copy of a table's structure and data:
CREATE TABLE new_table_name LIKE database_name_of_existing_table.name_of_existing_table;
INSERT new_table_name SELECT * FROM database_name_of_existing_table.name_of_existing_table;
To NULL:
NULL NULL;
To NULL:
NULL NULL;
top

Column Commands
To delete an existing column:
ALTER TABLE table_name DROP col_name;
To rename a column:
ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;
To rename a column:
ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition;
To change a column data type:
ALTER TABLE table_name MODIFY column_name data_type;
To add a column to the beginning of an existing table:
ALTER TABLE table_name ADD new_column_name VARCHAR(60) FIRST;   src: Tech-Recipes
To add a column to the end of an existing table:
ALTER TABLE table_name ADD new_column_name VARCHAR(60);   src: Tech-Recipes
To add a column after a specific column in an existing table:
ALTER TABLE table_name ADD new_column_name VARCHAR(60) AFTER column_name;  src: Tech-Recipes
To copy data from one column to a new column:
ALTER TABLE table_name ADD new_column_name data_type;
UPDATE table_name SET new_column_name = old_column_name;   src: GS Design
top

Data Commands
To enter a row of data into a table:
INSERT INTO table_name (column1_name, column2_name) VALUES ('column1_data', 'column2_data');
To display ALL the records for ALL the fields/columns of a table:
SELECT * FROM table_name;
To query table for a specific primary key (id):
SELECT column(s) FROM table WHERE id = 'id_number';
To display the records of a specific field or column:
SELECT * FROM table_name WHERE column_name = 'search_criteria';
To search for a pattern in a column
SELECT * FROM table_name WHERE column_name LIKE '%somepattern%';
To perform a case insensitive query:
SELECT * FROM table_name WHERE lower(column_name) LIKE '%somepattern%';
To display the records of a more than one field or column (multiple WHERE conditions):
SELECT * FROM table_name WHERE column_name = 'search_criteria' AND column_name = 'search_criteria';
To display records that DO NOT contain an empty string (i.e., to only display records with a value in the specified field):
SELECT column_name FROM table_name WHERE column_name<>'';
Show last record:
SELECT * FROM table_name ORDER BY id DESC LIMIT 1
To delete a record from a table:
DELETE FROM table_name WHERE column_name = 'search_criteria';
To delete multiple rows from a table using id:
DELETE FROM table_name WHERE id IN (1,2,3,...,254);
DELETE FROM table_name WHERE id BETWEEN 1 AND 16;
To find the total of a column:
SELECT sum(column_name) FROM table_name;
To change/modify a field/column (i.e., part of a record):
UPDATE tableName SET columnName = "newValue1", columnName = "newValue2" WHERE record_name = "value";
UPDATE medals SET gold = "4", SILVER = "7" WHERE id = 1;
To NULL:
NULL NULL;
top

Reset Auto Increment to Specific Number
STEP 1) Find the highest number in the auto increment field:
SELECT MAX(id) FROM table_name;
STEP 2) To reset the auto increment field, add 1 to the result of the command in step 1 (number):
ALTER TABLE table_name AUTO_INCREMENT number;
top

Reset Auto Increment to Zero
To reset the auto increment field to zero after deleting all fields:
ALTER TABLE table_name AUTO_INCREMENT = 0;
top

Reference Tables
STEP 1) Create the referenced table:
CREATE TABLE products (
   product_no integer PRIMARY KEY,
    name text,
    price numeric
);
STEP 2) Create the referencing table::
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
To NULL:
NULL NULL;
top

Miscellaneous Commands
To save the output of a query to a text file:
select * from watchdog into outfile '/tmp/watchdog.out';
To NULL:
NULL NULL;
To NULL:
NULL NULL;
To NULL:
NULL NULL;
top