MySQL queries cheat sheet
Connect to host 126.96.36.1999 with username ken and enter password when prompted
mysql -h 188.8.131.529 -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.
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.
Show existing tables in the database you are working on.
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
Search the entire web