Module 03 · Intermediate SQL
SQL Cheat Sheet: Intermediate - LIKE, ORDER BY, GROUP BYSQL 速查表：中级 - LIKE、ORDER BY、GROUP BY
| Command 命令 | Syntax (MySQL/DB2) 语法（MySQL/DB2） | Description 描述 | Example (MySQL/DB2) 示例（MySQL/DB2） |
|---|---|---|---|
| LIKE | SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; |
LIKE operator is used in a WHERE clause to search for a specified pattern in a column.LIKE 运算符用于在 WHERE 子句中在一个列中搜索指定的模式。Two wildcards often used in conjunction with the LIKE operator are percent sign(%) and underscore sign (), depending upon the SQL engine being used. LIKE 运算符通常与两个通配符一起使用，分别是百分号(%)和下划线()，具体取决于所使用的 SQL 引擎。 |
SELECT f_name , l_name FROM employees WHERE address LIKE '%Elgin,IL%';This command will output all entries with Elgin,IL in the Address.该命令将输出在地址中包含 Elgin,IL 的所有条目。 |
| BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.BETWEEN 运算符选择给定范围内的值。这些值可以是数字、文本或日期。 BETWEEN 运算符是包含的：开始值和结束值都包含在内。 |
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;This generates all records of employees with salaries between 40000 and 80000. 这将生成所有工资在 40000 到 80000 之间的员工记录。 |
| ORDER BY | SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; |
ORDER BY keyword is used to sort the result-set in ascending or descending order. The default is ascending. In case of multiple columns in ORDER BY, the sorting will be done in the sequence of the appearance of the arguments.ORDER BY 关键字用于对结果集进行升序或降序排序。默认为升序。在 ORDER BY 中存在多个列时，排序将按照参数出现的顺序进行。 |
SELECT f_name, l_name, dep_id FROM employees ORDER BY dep_id DESC, l_name;This displays the first name, last name, and department ID of employees, first sorted in descending order of department IDs and then sorted alphabetically as per their last names. 这显示了员工的名、姓和部门 ID，首先按部门 ID 降序排序，然后按姓氏字母顺序排序。 |
| GROUP BY | SELECT column_name(s) FROM table_name GROUP BY column_name(s) |
GROUP BY clause is used in collaboration with the SELECT statement to arrange data with identical values into groups.GROUP BY 子句与 SELECT 语句一起使用，用于将具有相同值的数據分组排列。 |
SELECT dep_id, COUNT(*) FROM employees GROUP BY dep_id;This returns the department IDs and the number of employees in them, grouped by the department IDs. 这返回了部门 ID 及其中的员工数量，按部门 ID 分组。 |
| HAVING | SELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING condition |
HAVING clause is used in conjunction with GROUP BY clause in collaboration with the SELECT statement in order to filter the data as per the given condition and then group as per identical values of a specified parameter.HAVING 子句与 GROUP BY 子句协同 SELECT 语句使用，以便根据给定条件过滤数据，然后按指定参数的相同值分组。 |
SELECT DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY" FROM EMPLOYEES GROUP BY DEP_ID HAVING count(*) < 4 ORDER BY AVG_SALARY; |