SQL cheetsheet(MySQL)(to be continued)

本文最后更新于:July 21, 2020 am

SQL cheetsheet

select

In MySQL, every statement must be terminated with a semicolon

select * --(all cols)
select colname1,colname2
select distinct colname -- remove duplicate
select expression as name --using expressions
SELECT * FROM table --(all cols)
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...
SELECT DISTINCT colname -- remove duplicate
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition

-- REGEXP(RLIKE|REGEXP)
SELECT * FROM table WHERE column REGEXP '^(A|B|C)'
SELECT * FROM table WHERE rec RLIKE "^b$";  
-- RETURN THE INFORMATION
DESCRIBE table [column]

--TIME AND DATE
SELECT CURRENT_DATE, (YEAR(CURRENT_DATEYEAR(date_col)) AS time_diff [FROM table];

WHERE Conditionals

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IN (value1, value2)
field1 NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2
BETWEEN ... AND ...
fieldname REGEXP '^a'