Coursera 笔记

Module 03 · Intermediate SQL

SQL Cheat Sheet: FUNCTIONS and Implicit JOINSQL 速查表：函数与隐式连接

Coursera ↗

Command   命令 Syntax (MySQL/DB2)   语法（MySQL/DB2） Description   描述 Example (MySQL/DB2)   示例（MySQL/DB2）
COUNT SELECT COUNT(column_name) FROM table_name WHERE condition; COUNT function returns the number of rows that match a specified criterion.
COUNT 函数返回符合指定标准的数据行数。		 SELECT COUNT(dep_id) FROM employees;
AVG SELECT AVG(column_name) FROM table_name WHERE condition; AVG function returns the average value of a numeric column.
AVG 函数返回数值列的平均值。		 SELECT AVG(salary) FROM employees;
SUM SELECT SUM(column_name) FROM table_name WHERE condition; SUM function returns the total sum of a numeric column.
SUM 函数返回数值列的总和。		 SELECT SUM(salary) FROM employees;
MIN SELECT MIN(column_name) FROM table_name WHERE condition; MIN function returns the smallest value of the SELECTED column.
MIN 函数返回选定列的最小值。		 SELECT MIN(salary) FROM employees;
MAX SELECT MAX(column_name) FROM table_name WHERE condition; MAX function returns the largest value of the SELECTED column.
MAX 函数返回 SELECTED 列的最大值。		 SELECT MAX(salary) FROM employees;
ROUND SELECT ROUND(2number, decimals, operation) AS RoundValue; ROUND function rounds a number to a specified number of decimal places.
ROUND 函数将数字四舍五入到指定的十进制位数。		 SELECT ROUND(salary) FROM employees;
LENGTH SELECT LENGTH(column_name) FROM table; LENGTH function returns the length of a string (in bytes).
LENGTH 函数返回字符串的长度（以字节为单位）。		 SELECT LENGTH(f_name) FROM employees;
UCASE SELECT UCASE(column_name) FROM table; UCASE function displays the column name in each table in uppercase.
UCASE 函数以大写形式显示每个表中的列名。		 SELECT UCASE(f_name) FROM employees;
LCASE SELECT LCASE(column_name) FROM table; LCASE function displays the column name in each table in lowercase.
LCASE 函数用于在每张表中以小写形式显示列名。		 SELECT LCASE(f_name) FROM employees;
DISTINCT SELECT DISTINCT column_name FROM table; DISTINCT function is used to display data without duplicates.
DISTINCT 函数用于显示不重复的数据。		 SELECT DISTINCT UCASE(f_name) FROM employees;
DAY SELECT DAY(column_name) FROM table DAY function returns the day of the month for a given date.
DAY 函数用于返回给定日期的月份中的日。		 SELECT DAY(b_date) FROM employees where emp_id = 'E1002';
CURRENT_DATE SELECT CURRENT_DATE; CURRENT_DATE is used to display the current date.
CURRENT_DATE 用于显示当前日期。		 SELECT CURRENT_DATE;
DATEDIFF() SELECT DATEDIFF(date1, date2); DATEDIFF() is used to calculate the difference between two dates or time stamps. The default value generated is the difference in number of days.
DATEDIFF() 用于计算两个日期或时间戳之间的差异。默认生成的值是相差的天数。		 SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;
FROM_DAYS() SELECT FROM_DAYS(number_of_days); FROM_DAYS() is used to convert a given number of days to YYYY-MM-DD format.
FROM_DAYS() 用于将给定的天数转换为 YYYY-MM-DD 格式。		 SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;
DATE_ADD() SELECT DATE_ADD(date, INTERVAL n type); DATE_ADD() is used to calculate the date after lapse of mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days after what is mentioned in date column. The type valiable can also be months or years.
DATE_ADD() 用于计算在指定日期类型单位数之后的时间，即如果 n=3 且 type=DAY，结果将是日期列中指定日期之后的 3 天。type 变量也可以是月份或年份。		 SELECT DATE_ADD(date, INTERVAL 3 DAY);;
DATE_SUB() SELECT DATE_SUB(date, INTERVAL n type); DATE_SUB() is used to calculate the date prior to the record date by mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days before what is mentioned in date column. The type valiable can also be months or years.
DATE_SUB() 用于计算在记录日期之前指定日期类型单位数的时间，即如果 n=3 且 type=DAY，结果将是日期列中指定日期之前的 3 天。type 变量也可以是月份或年份。		 SELECT DATE_SUB(date, INTERVAL 3 DAY);;
Subquery   子查询 SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) Subquery is a query within another SQL query and embedded within the WHERE clause.
Subquery 是嵌套在另一个 SQL 查询中的查询，并嵌入在 WHERE 子句中。
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
子查询用于返回数据，这些数据将作为条件在主查询中使用，以进一步限制要检索的数据。		 SELECT emp_id, f_name, l_name, salary
FROM employees
where salary
< (SELECT AVG(salary)
FROM employees);

SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all;



SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs);
Implicit Inner Join   隐式内连接 SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; Implicit Inner Join combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns.
Implicit Inner Join 结合两个或多个记录，但仅显示两个表中匹配的值。内连接仅应用于指定的列。		 SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;
Implicit Cross Join   隐式交叉连接 SELECT column_name(s) FROM table1, table2; Implicit Cross Join is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table.
Implicit Cross Join 定义为笛卡尔积，其中第一个表的行数乘以第二个表的行数。		 SELECT * FROM employees, jobs;