MySQL queries cheat sheet

Connect to host 123.45.6.789 with username ken and enter password when prompted

mysql -h 123.45.6.789 -u ken -p

Backup and restore mysql database

backup: # mysqldump -u root -p -h localhost myDatabase > mysqlDBdump.sql
backup a table: mysqldump -u root -p -h localhost myDatabase t1 t2 t3 > myTables.sql 
restore:# mysql -u root -p -h localhost myDatabase < mysqlDBdump.sql

If each of the above command will take hours to finish, we can do the below to run it in the background. Another thing we can do to have the above command to run successful is to run them with nohup, just put “nohup” before the each of the above command.

ctrl+z #To pause the program and get backt to the shell window.
bg #To run it in the background.
jobs #To display the current jobs running.
disown -h %[jobnumber] #To disown the job, so it will still be running even if you logged out.

Backup a mysql table by copy the table data directly using mysql query

CREATE TABLE myblog.post_backup LIKE myblog.post;
INSERT myblog.post_backup SELECT * FROM myblog.post;

Export data with SELECT … INTO OUTFILE

SELECT * FROM students INTO OUTFILE "/tmp/student.csv"
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Run a mysql query in the background

nohup mysql -u root -p ecomDB -e “INSERT INTO customer_entity_bk select * from customer_entity” &

Show existing databases on the mysql server.

show databases;

Choose a database to work on, in this case choose the database named webdata. This query has to be executed before doing queries against a particular database.

use webdata;

Show existing tables in the database you are working on.

show tables;

Find tables whose table name has the string “name”.

show tables like "%name%";

Show the query that created the table transactions.

show create table transactions;

Show columns of the table transactions.

show columns from transactions;

Show the columns whose column name has the string “id” in it in the table sales_order.

show columns from sales_order like "%id%";

Show the number of columns of the table sales_order in the database web_store.

SELECT count(*) FROM information_schema.`COLUMNS` C
WHERE table_name = 'sales_order'
AND TABLE_SCHEMA = "web_store";

Show table status.

show table status;

Create a database webdata

CREATE DATABASE IF NOT EXISTS webdata;

Create a table named transactions. Assume there is a table named categories already exist for the foreign key creation.

CREATE TABLE IF NOT EXISTS transactions(
   transaction_id int NOT NULL AUTO_INCREMENT,
   date date NOT NULL,
   category varchar(50) NOT NULL,
   item_description varchar(255),
   cost double NOT NULL,
   last_edit timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
   PRIMARY KEY (transaction_id ),
   FOREIGN KEY(category) REFERENCES categories(category)on update cascade
)ENGINE=InnoDB;

Insert data into the table transactions.

INSERT INTO transactions(date, category, item_description, cost)
VALUES
("2013/2/1","groceries","beef","9.99"),
("2013/3/1","alcohol","wine","20");

Update a row in the transactions table.

UPDATE transactions
SET item_description="meat"
WHERE transaction_id=1;

Drop a foreign key.

ALTER table transactions DROP foreign key category

Delete a table named transactions.

DROP TABLE transactions;

Change the column name from category to categories and it’s data type in the transactions table.

ALTER TABLE `transactions` change category categories char(20);

Delete the column item_description in the transactions table.

ALTER TABLE transactions DROP COLUMN item_description;

Add a new column after the column category.

ALTER TABLE transactions ADD type varchar(50) AFTER category;

Get the year to date sum of total sales from orders table.

SELECT sum(grand_total) FROM orders 
WHERE created_at >= '2013/1/1' and created_at < curdate();

Retrieve records within one month

SELECT * FROM transactions 
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

Select records between two dates

SELECT * FROM transactions
WHERE date >= '2013/1/1' and date < '2013/2/1';

MySQL if example. This example select book name and cover type of the books, if the cover type is default, set it to softcover, otherwise use the value as it is.

SELECT book_name,  
IF(type='default',"softcover", type)   
AS coverType   
FROM books;

Add a trigger that modifies the timestamp value for last_edit when a row is edited. This is just for demonstration. The last_edit in transactions table will auto update even without this trigger because it was create with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

DELIMITER |
CREATE
    TRIGGER last_edit BEFORE UPDATE ON transactions FOR EACH ROW 
BEGIN
    SET NEW.last_edit = NOW();
END;
|

Search within Codexpedia

Custom Search

Search the entire web

Custom Search