Cheatography

# SQL Cheat Sheet (DRAFT) by elhamsh

This is a draft cheat sheet. It is a work in progress and is not finished yet.

### Functions

 bin(4) 100. Converts a decimal number to a binary number CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is something else" END can be used in SELECT­,DE­LET­E,I­NSE­RT,­UPDATE SELECT CASE city WHEN 'calgary' THEN 'Y' ELSE 'N' END calgary FROM... CAST("1­4:0­6:1­0" AS TIME) CAST(value AS type) SELECT COALES­CE(­NULL, 1, 2, 'W3Sch­ool­s.c­om'); 1. Return the first non-null expression in a list IF(500­<1000, "­YES­", "­NO") IF(con­dition, value_­if_­true, value_­if_­false) IFNULL­(NULL, 500) IFNULL­(ex­pre­ssion, alt_value)

### NUMERIC

 ABS() AVG() CEIL() FLOOR() LOG() LOG10() LOG2() PI POWER(3,2) or POW(3,2) 9 RAND() a value between 0 (inclu­sive) and 1 (exclu­sive) RAND(4) RAND(s­eed), a repeatable sequence of random numbers if a seed value is used ROUND(­135.375, 2) 135.38, ROUND(­number, decima­l_p­laces) ROUND(­135.375) 135 SIGN(n­umber) if >0,1. If = 0, 0. If <0, -1 SQRT(64)

### Join

 SELECT x.a, b, c FROM x INNER JOIN y ON x.id=y.id SELECT o.a, c.b, e.d FROM ((orders o INNER JOIN customers c ON o.id=c.id) INNER JOIN employee e ON e.id=o.eid); LEFT JOIN RIGHT JOIN FULL OUTER JOIN (SELECT a FROM aa) UNION (SELECT a FROM bb) # of columns, and data types the same and in the same order SELECT a,b FROM aa,bb cartesian join, return table of length­(aa*bb) UNION ALL Union all will not eliminate duplicate rows

### String

 ASCII(­cha­racter) number code of the first character CONCAT­("SQL ", "is ", "­fun­!") Concat­enate several expres­sions together CONCAT­_WS­("-", "­SQL­", "­is", "­fun­!") SQL-is­-fun! FIELD(­"­c", "­a", "­b", "­c", "­d", "­e") 3. FIELD(­value, val1, val2, val3, ...). position of the value in the list of values FIND_I­N_S­ET(­"­c", "­a,b­,c,­d,e­") 3. the position of a string in a comma-­sep­arated string list. not found=0 INSERT­("W3­Sch­ool­s.c­om", 1, 9, "­Exa­mpl­e") Exampl­e.com. NSERT(­string, position, number, substring) INSTR(­"­W3S­cho­ols.co­m", "­COM­") 11. return position LEFT(s­tring, number­_of­_chars) extracts a substring from a string (starting from left). LENGTH­(st­ring) LOWER(­string) LPAD("S­QL", 10, "­ABC­") ABCABC­ASQL. LPAD(s­tring, length, pad_st­ring) LTRIM(­" SQL Tutori­al") Remove leading spaces from a string MID("SQL Tutori­al", 5, 3) or SUBSTR­(st­ring, start, length) Tut. MID(st­ring, start, length) POSITI­ON(­"­3" IN "­W3S­cho­ols.co­m") or LOCATE­("3", "­W3S­cho­ols.co­m") 2 REPEAT­(st­ring, number) REPLAC­E("SQL Tutori­al", "­SQL­", "­HTM­L") REPLAC­E(s­tring, from_s­ubs­tring, to_sub­string) REVERS­E(s­tring) RIGHT(­"SQL Tutorial is cool", 4) cool RPAD("SQL Tutori­al", 20, "­ABC­") RTRIM(­"ABC ") SPACE(10) Return a string with 10 spaces UPPER() TRIM() Removes leading and trailing spaces from a string STRCMP­(st­ring1, string2) If string­1=s­tring2, 0 If string­1s­tring2, 1 SUBSTR­ING­_IN­DEX­("ww­w.w­3sc­hoo­ls.c­om­", ".", 2) www.w3­schools, substring of string before number of occurr­ences of delimiter

### Date

 ADDDAT­E("2­017­-06­-15­", INTERVAL -10 DAY) or DATE_A­DD(...) ADDDAT­E(date, INTERVAL value unit) [SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR ...] ADDDAT­E("2­017­-06­-15­", 1) ADDDAT­E(date, days) CURDATE() or CURREN­T_D­ATE() current date as a "­YYY­Y-M­M-D­D" or YYYYMMDD CURDATE() + 1 Tomorrow CURTIME() or CURREN­T_T­IME() current time as a "­HH-­MM-­SS" or HHMMSS CURREN­T_T­IME­STAMP() "­YYY­Y-MM-DD HH-MM-­SS" DATE("2­017­-06-15 09:34:­21") extracts the date value from a date or datetime expression DATEDI­FF(­"­201­7-06-25 09:34:­21", "­201­7-06-15 15:25:­35") the difference in days between two date values DATE_F­ORM­AT(­Bir­thDate, "%W %M %e %Y") %W Weekday name in full, %e Day of the month as a numeric value (0 to 31) DAY("20­17-­06-­15"), DAYOFMONTH 15.DAY­(date) DAYNAM­E(date) DAYOFW­EEK­("20­17-­06-­15") 1=Sunday, ..., 7=Satu­rday. DAYOFY­EAR­("20­17-­06-­15") a number from 1 to 366 EXTRAC­T(WEEK FROM "­201­7-0­6-1­5") EXTRAC­T(unit FROM date) YEAR(date) WEEK(date) SECOND­(date) MONTH(­date) MINUTE­(date) STRFTI­ME(­'%Y', BD) AS year extract part of date-time format data STRFTI­ME(­'%m', BD) AS month DATE('­now') STRFTI­ME('%Y %m %d', 'now') DATE('­now') - BD AS age

### view

 CREATE VIEW x AS SELECT... DROP VIEW x delete view