Python 学习笔记：Intermediate SQL (1)

这里主要是整理 SQL 一些进阶的语法，方便自己不记得的时候可以快速查找。

String Patterns

---

/* LIKE */
SELECT column1, column2, ... FROM table1 WHERE column1 LIKE '%pattern%';

/* BETWEEN ... AND */
SELECT column1, column2, ... FROM table1 WHERE (column1 BETWEEN condition1 AND condition2);

Sorting

---

/* in ascending order */
SELECT column1, column2, ... FROM table1 ORDER BY column1;

/* in descending order */
SELECT column1, column2, ... FROM table1 ORDER BY column1 DESC, column2 DESC;

/* multiple tables */
SELECT t1.column1, t2.column2, ... FROM table1 as t1, table2 as t2 ORDER BY t1.column1, t2.column2 DESC;

Grouping

---

/* general syntax */
SELECT * FROM table1 GROUP BY column1;

/* includes SQL aggregate functions */
SELECT column1, COUNT(*) as "Number", AVG(column2) as "Average" FROM table1 GROUP BY column1;

/* limit to the groups that count is greater than n */
SELECT column1, COUNT(*) as "Number", AVG(column2) as "Average" FROM table1 GROUP BY column1 HAVING count(*) > n

Aggregate Functions

---

/* get the total value */
SELECT SUM(column1) FROM table1;

/* get the average value */
SELECT AVG(column1) FROM table1;

/* get the maximum value */
SELECT MAX(column1) as "Max" FROM table1;

Scalar and String Functions

---

/* display the rounded value of a column */
SELECT ROUND(column1) FROM table1;

/* display the length of every row of a column */
SELECT LENGTH(column1) FROM table1;

/* select the row that its uppercase value of column1 is equal to "STR" */
SELECT * FROM table1 WHERE UCASE(column1) == "STR";

Date and Time Functions

---

/* display the day of monthe */
SELECT DAYAY(column_date) FROM table1;

/* displays row that the date is on the 6th month */
SELECT * FROM table1 WHERE MONTH(column_date) == '06';

/* displays the third day from the date */
SELECT (column_date + 3 DAYSDAYS) FROM table1;

/* displays the day between today's date and the date */
SELECT (CURRENT DATE - column_date) FROM table1;