MySQL cheat sheet

source: MySQL Cheat Sheet

Access mysql server

mysql -u [username] -p;nmysql -u [username] -p [database] nmysqldump -u [username] -p [database] > data_backup.sql;nshow grantsn

Working with database

CREATE DATABASE [IF NOT EXISTS] database_name;nUSE database_name;nDROP DATABASE [IF EXISTS] database_name;nshow databases;n

Working with table

show tables;nDESCRIBE table [column]nnCREATE [TEMPORARY] TABLE [IF NOT EXISTS] table(n key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,n c1 type(size) NOT NULL,n c2 type(size) NULL,n ...n);nninsert into table_name values (),();ninsert into table_name values(columns) select (same columns) from other_table_name --將數據一次性導入表，避免逐條insertnnALTER TABLE table ADD|DROP [COLUMN];nALTER TABLE table ADD PRIMARY KEY (column,...)nALTER TABLE table ADD INDEX [name](column, ...);nnUPDATE table_name SET column = new value WHERE id = xxx;nDELETE from table_name WHERE id = ;n

CONCATENATE and Trim

CONCATENATE: select concat(id,(,info,)) from test;nRTrim: select concat(RTrim(id),(,RTrim(info),)) from test; （RTrim刪除數據右側空格）nselect concat(id,(,info,)) AS id_info from test; (use AS to create Alias)nselect id, quantity, price, quantity*price AS amount from products;n

Querying Data

show columns from table_name；= describe table_name;nselect distinct vendor_id from products;nselect distinct column_name1, column_name2, column_name3 from table_name;(1*2*3)nselect * from table_name limit 50,50 nselect * from table_name order by column_name1, column_name2(先按1排序，再按2排序，對應的場景比如對姓名相同，名字不同的員工姓名排序)nselect * from table_name order by product_price DESC, product_name;(price降序排序，name還是默認的升序排序)n最大值 select product_price from products order by product_price DESC LIMIT 1;n最小值 select product_price from products order by product_price LIMIT 1;n

Filtering Data

select * from table_name where conditions;nselect * from products where product_price between 5 and 10;nselect * from products where id = 1003 and price >10;nselect * from products (where id = 1003 or id = 1004) and price>10;nIN operator: select * from products where product_price NOT IN (1003,1004);n

Wildcard and Regular Expression

LIKE word% start with word （% 表示任意字元出現任意次數）nLIKE %word end with wordnLIKE %word% contain wordnLIKE s%b start with s and end with bnLIKE _word (_ only match single char)nREGEXP ^...n n匹配特殊字元 . - n r (轉義 escaping)n匹配字元類 [[:digit:]]{4}nselect hello REGEXP [a-z]n

Function

length() 返回串的長度nlocate() 找出串的一個子串nsubstring() 返回子串的字元nselect * from table_name where Date(order_date) BETWEEN 2016-08-01 AND 2016-08-30 -- 8月的訂單nselect * from table_name where YEAR(order_data)=2016 AND MONTH(order_data)=9nAVG(DISTICT column_name)nCOUNT()nSUM()n

Group by and Having

select id,count(*) from table_name WHERE price>10 GOURP BY id HAVING count(*) >2 ORDER BY count(*) LIMIT 5nselect id from table_name GROUP BY id 相當於對id去重nselect id from orders where order_num IN (select order_num from table_name where ...)nselect name, (select count(*) from orders where orders.id = customers.id) from customers n

JOINing tables

select vendor_name,product_name, product_price from vendors, productsnwhere vendors.id = products.id (等值聯結)nselect vendor_name,product_name, product_price from vendors INNER JOIN productsnON vendors.id = products.id (內部聯結)nselect p1.product_id, p1.product_name （自聯結）nLEFT | RIGHT JOINn

UNION

組合查詢，每個查詢都要包含相同的列，表達式，聚集函數

select ... UNION (ALL) select... ORDER BYn

VIEW

視圖是一段SQL，不是數據。優點是方便重用SQL，保護數據

CREATE VIEW view_name AS (select ...)n

STORED PROCEDURES

mysql> DELIMITER // nmysql> CREATE PROCEDURE proc1(OUT s int) n -> BEGIN n -> SELECT COUNT(*) INTO s FROM user; n -> END n -> // nmysql> DELIMITER ;nnCALL proc1();//nCURSORS游標只能用於存儲過程n

Trigger

CREATE TRIGGER printMessage AFTER INSERT ON products nFOR EACH ROW SELECT product added n每個表最多支持6個觸發器（INSERT,DELETE,UPDATE前後）n

TRANSACTION PROCESS

ROLLBACK and COMMIT, SAVEPOINT

start transaction;nselect * from table_name;ndelete from table_name;nselect * from table_name;nrollback;nselect * from table_name;n

--set autocommit=0 nstart transaction;ndelete * from table_name;ncommit;n

Managing Security

-- show users

use mysql;nselect * from usern

create user xavizhao identified by password;ngrant select on database.* to user_name;nset password for user_name = Password(xxx)n

1

推薦閱讀：