第二章：SQL的数据查询

第二章：SQL的数据查询

2.1 SELECT基本语法

单表操作：projection投影列，selection选择行，join连接

SELECT {
  * | [DISTINCT] column列 |expression [alias],...}
FROM	table;

• SELECT identifies the columns to be displayed.选择特定的列显示
• FROM identifies the table containing those columns.
Selecting All Columns ：SELECT * FROM departments;
Selecting Specific ColumnsSELECT department_id, location_id FROM departments;
Writing SQL Statements：语法结构
• SQL statements are not case sensitive.语句大小写不敏感
• SQL statements can be entered on one or more lines.书写一行或多行
• Keywords cannot be abbreviated or split across lines.关键字不可分行
• Clauses are usually placed on separate lines.
• Indents are used to enhance readability.
• In SQL Developer, SQL statements can be optionally terminated by a semicolon (😉. Semicolons are required when you execute multiple SQL statements.
• In SQL*Plus, you are required to end each SQL statement with a semicolon (😉.
Column Heading Defaults

Arithmetic expressions and NULL values in the SELECT statement
算术表达和空值

Defining a Null Value空值是特殊数据类型，函数转换运算
• Null is a value that is unavailable, unassigned, unknown, or inapplicable.
• Null is not the same as zero or a blank space.不是零也不是空格
Defining a Column Alias别名
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
• Requires double quotation“” marks if it contains spaces or special characters, or if it is case-sensitive

SELECT last_name AS name, commission_pct as  comm FROM employees;

SELECT last_name "Name" /*显示结果大小写不变*/, salary*12 "Annual Salary" FROM employees;

Concatenation Operator联合运算符：两个字段放在一个字段输出
• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression

SELECT       last_name||job_id  AS "Employees"   FROM    employees;

Literal Character Strings文本字符串
• A literal is a character字符串, a number数字, or a date日期 （需要单引号）that is included in the SELECT statement.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.

**SELECT last_name ||' is a '||job_id       AS "Employee Details"//单引号
FROM employees;**

Alternative Quote (q) Operator
• Specify your own quotation mark delimiter.