SQL Fundamentals: Basic SELECT statement基本的select语句（控制操作的现实列）(FROM-SELECT)

SQL Fundamentals || Oracle SQL语言

 

Capabilities of the SELECT Statement（SELECT语句的功能）

Data retrieval from data base is done through appropriate and efficient use of SQL. Three concepts from relational theory encompass the capability of the SELECT statement: projection, selection, and joining.

• Projection（投影）: A project operation selects only certain columns (fields) from a table. The result table has a subset of the available columns and can include anything from a single column to all available columns. （选择列的能力）
• Selection（选择）: A select operation selects a subset of rows (records) in a table (relation) that satisfy a selection condition. The ability to select rows from out of complete result set is called Selection. It involves conditional filtering and data staging. The subset can range from no rows, if none of the rows satisfy the selection condition, to all rows in a table. （从表里选择所需要的行）
• Joinin连接）: A join operation combines data from two or more tables based on one or more common column values. A join operation enables an information system user to process the relationships that exist between tables. The join operation is very powerful because it allows system users to investigate relationships among data elements that might not be anticipated at the time that a database is designed. （连接多个表）

Consider the above table structures. Fetching first_name name, department_id and salary for a single employee from EMPLOYEES table is Projection. Fetching employee details whose salary is less than 5000, from EMPLOYEES table is Selection. Fetching employee's first name, department name by joining EMPLOYEES and DEPARTMENTS is Joining.

 

 

 

• 控制操作的显示列：基本的SELECT语句
• 控制行：限定查询和排序显示
• 分组统计查询

 

简单查询语句语法：

SELECT [DISTINCT] * | 列名称 [AS] [列别名] , 列名称 [AS] [列别名] ,...

FROM 表名称 [表别名] ;

 

各个子句的执行顺序：

 

1、FROM-->

2、WHERE -->

3、GROUP BY（HAVING子句）-->

4、SELECT-->

5、ORDER BY-->

 

 

执行顺序：

• 第一步执行FROM字句：表示确定数据来源
• 第二部执行SELECT字句：确定要显示的数据列。

 

 

在整个简单查询字句，主要有2个子句：

SELECT字句：

内容：

*	表示查询所有的数据列；
列名称	表示显示指定的列，列也可以设置别名；
DISTINCT	表示去除重复数据

FROM字句：

定义要使用的数据表（数据来源）

 

其他查询操作：

消除重复数据 DISTINCT	SELECT DISTINCT job FROM emp;
四则运算	SELECT empno,ename,sal*12,sal/30 FROM emp; SELECT empno,ename,(sal+200)*12+5000 FROM emp ;
为四则运算查询结果设置别名	SELECT empno 雇员编号,ename 雇员姓名,(sal+200)*12+5000 AS 年薪 FROM emp ; 别名中有空格或特殊字符或区分大小写，这时候用双引号括起来
常量	在SELECT子句中使用常量，为以上的查询增加一个货币的描述 SELECT empno AS 雇员编号 , ename AS 雇员姓名,(sal+200)*12+5000 AS 年薪 , '￥' AS 货币 FROM emp ; 日期和字符串常量用单引号括起来。
串联操作符||	使用“||”进行连接显示 SELECT '编号是：' || empno || '的雇员姓名是：' || ename || '，基本工资是：' || sal 雇员信息 FROM emp ; 在查询语句中出现的字符串，必须使用“'”括起来
引用运算符 []{}<>	q'[']' SELECT ename,job,ename||q'['s job is]'||job as "Employees" FROM emp;

 

• 简单查询是将一张表中的全部或部分列进行显示的操作；
• 简单查询中通过“*”表示查询全部的内容，也可以指定具体的列名称，显示具体列的内容；
• 在SQL中可以使用“+”、“-”、“*”、“/”四则运算，但是要注意运算符的优先级；
• 可以为一个显示的列进行别名的设置，这样以后显示时会将相应的列名称替换成别名显示；
• 通过“||”可以进行数据的连接，在查询语句中出现的字符串，必须使用“'”括起来。

 

 

• Basic SELECT statement基本的select语句

The basic syntax for a SELECT statement is presented below.

SELECT语句的基本语法如下。

• |：多选一
• []：可选择的内容
• {}:多选一
• 没有被{}括起来的是必选

SELECT [DISTINCT | ALL] {* | select_list}
FROM {table_name [alias] | view_name}
    [{table_name [alias] | view_name}]... 
[WHERE condition]
[GROUP BY condition_list]
[HAVING condition]
[ORDER BY {column_name | column_# [ ASC | DESC ] } ...

 

In its simplest form, a SELECT statement must include the following:

SELECT	The SELECT clause is mandatory and carries out the relational project operation. 选择子句是强制性的，并执行关系项目操作。 SELECT identifies the columns to be displayed. A SELECT clause, which specifies the columns to be displayed. SELECT标识要显示的列。
FROM	The FROM clause is also mandatory. It identifies one or more tables and/or views from which to retrieve the column data displayed in a result table. FROM identifies the table containing those columns. A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause. 它标识包含SELECT子句中列出的列的表。

 

In the syntax:

SELECT	Is a list of one or more columns
*	Selects all columns
DISTINCT	Suppresses(阻止) duplicates(重复)
Column | expression	Selects the named column or the expression列名或表达式
alias	Gives the selected columns different heading(标题)
FROM table	Specifies the table containing the columns

Note: Throughout this course, the words keyword, clause, and statement are used as follows:

• A keyword(关键字) refers to an individual（独特的） SQL element（有特殊含义的SQL元素）

——for example,SELECT and FROM are keywords.

• A clause(子句) is a part of a SQL statement（Select语句的一个组成部分）

——for example, SELECT emplovee_id, last_name , and so on

• A statement(语句) is a combination（组合） of two or more clauses（Select语句是两个或者多个子句的组合）

——for example, SELECT * FROM employees（SELECT *叫一个子句，FROM employees叫一个子句）

注意：习惯将关键字写成大写

WHERE	The WHERE clause is optional and carries out the relational select operation. It specifies which rows are to be selected.
GROUP BY	The GROUP BY clause is optional. It organizes data into groups by one or more column names listed in the SELECT clause.
HAVING	The optional HAVING clause sets conditions regarding which groups to include in a result table. The groups are specified by the GROUP BY clause.
ORDER BY	The ORDER BY clause is optional. It sorts query results by one or more columns in ascending or descending order.

 

Selecting All Columns:

SELECT * FROM departments；

从departments表中选择所有的行rows. 每个行要显示所有列column.

 

Selecting Specific Columns:

SELECT department_id, location_id FROM departments；

从departments表中选择指定行.

 

Write SQL Statements

SQL statements are not case sensitive.	SQL语句不区分大小写
SQL statements can be entered on one or more lines.	可以在一行或多行上输入sql语句
Keywords cannot be abbreviated(缩写) or split across lines.	关键字不能缩写或跨越多行
Clauses are usually placed on separate lines.	子句通常放在单独的行上
Indents(缩进) are used to enhance redability.	缩进是用来增强可读性
In SQL Developer, SQL statements can be optionally terminated by semicolon(;).Semicolons are required when you execute multiple SQL statements.	In SQL Developer,SQL语句可以随意终止的分号（；） 当你执行多条SQL语句时分号是必需的。
In SQL*Plus, you are required to end each SQL statement with a semicolon(;).	In SQL*Plus,，你需要用分号（；）结束每个SQL语句。

Chose the statements which correctly specify a rule to write a SQL statement

1. SQL statements are case sensitive
2. Keywords can be abbreviated to build a standard
3. SQL statements are case in-sensitive
4. clauses must be placed together

Answer: C.SQL statements are not case sensitive.

 

Column Heading Defaults:

默认的列标题（表的第一行）:

SQL Developer	-Default heading alignment: Left-aligned(左对齐) -Default heading display: Uppercase(大写)
SQL *Plus	-Character and Data column headings are left-aligned -Number column headings are right-aligned -Default heading display : Uppercase

 

 

Arithmetic expressions and NULL values in the SELECT statement

SELECT语句中的算术表达式和空值

 

首先介绍显示表结构的命令

DESCRIBE command

描述命令：显示表结构

 

isplaying the Table Structure

You can display the structure of a table by using the DESCRIBE command.

The command displays the column names and the data types, and it shows you whether a column must contain data(that is, whether the column has a NOT NULL constraint.)

 

 

The structural metadata of a table may be obtained by querying the database for the list of columns that comprise it using the DESCRIBE command. It will list the used column names, their null property and data type.

Syntax:

DESC[RIBE] [SCHEMA].object name

For example,

SQL> desc emp;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

 

will display the EMPLOYEE table structure i.e. columns, their data types, precision and nullable property.

 

 

An arithmetic expression can be creaeted using the column names, operators and constant values to embed an expression in a SELECT statement.

算术表达式可以使用列名称、运算符和常量值嵌入一个SELECT语句中的表达创建。

The operator applicable to a column depends on column's data type.

适用于列的运算符依赖于列的数据类型。

For example, arithmetic operators will not fit for character literal values.

例如，算术运算符不适合字符面值。

 

 For example,

SELECT employee_id, sal * 12 ANNUAL_SAL
FROM employees;

The above query contains the arithmetic expression (sal * 12) to calculate annual salary of each employee.

上面的查询包含算术表达式（sal* 12）来计算每个雇员的年薪。

 

Arithmetic Expressions

算术表达式

• You may need to modify the way in which data is displayed, or you may want to perform calculations, or look at what-if scenarios. All these are possible using arithmetic expressions.

你可能需要修改数据的显示方式，或想执行计算，通过使用算术表达式可以实现.

• An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

一个算术表达式可以包含列名、常量数字值和算术操作符.

• Create expressions with number and date data by using arithmetic operators.

 

Arithmetic operators

算术运算符

The slide lists the arithmetic operators that are available in SQL.

You can use arithmetic operators in any clause of a SQL statement(except the FROM clause)

除了FROM子句，可在SELECT的任何其他子句中使用算术操作符.

Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction.

对DATA和TIMESTAMP数据类型，只能使用+ -操作符.

 

Operators act upon the columns (known as operands) to result into a different result. In case of multiple operators in an expression, the order of evaulation is decided by the operator precedence. Here are the elementary rules of precedence -

• Multiplication and division occur before Addition and Subtraction.
• Operators on the same priority are evaluated from left to right.
• Use paretheses to override the default behavior of the operators.

Below table shows the precedence of the operators, in such cases. Precedence Level Operator Symbol Operation

Description	Operator	Precedence
Addition	+	Lowest
Subtraction	-	Lowest
Multiplication	*	Medium
Division	/	Medium
Brackets	( )	Highest

        

Examine the below queries (a), (b), and (c)

SQL> SELECT 2*3 FROM DUAL;

       2*3

----------

SQL> SELECT ename,sal,sal+200 FROM emp;

ENAME             SAL    SAL+200

---------- ---------- ----------

SMITH             800       1000

WARD             1250       1450

 

SQL> SELECT ename,sal,sal+(comm*sal) FROM emp;

ENAME             SAL SAL+(COMM*SAL)

---------- ---------- --------------

SMITH             800

WARD             1250         626250

Query (a) multiplies two numbers, while (b) shows addition of $1500 to salaries of all employees.

Query (c) shows the addition of commission component to employee's salary.

As per the precedence, first commission would be calculated on the salary, and then added to the salary.

 

注意，如上sal+200这个列是运算列，并不是真正存在的列，这里可以对这个列设定一个列别名.

 

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(区分大小写).

如果别名列中有空格，或特殊字符，或区分大小写，这个时候这个列标题要用双引号引起来.

 

An alias is used to rename a column or an expression during display. The alias to a column or an expression appears as the heading in the output of a query. It is useful in providing a meaningful heading to long expressions in the SELECT query. By default, the alias appears in uppercase in the query output without spaces. To override this behavior, alias must be enclosed within double quotes to preserve the case and spaces in the alias name.

SELECT price * 2 as DOUBLE_PRICE, price * 10 "Double Price"
FROM products;

DOUBLE_PRICE        Double Price
------------        ------------
39.9                        39.9
60                        60
51.98                        51.98

 

Concatenation operators

串联操作符||

A 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.

 

将列或字符串连接起来，创建一个组合列.

 

Concatenation operator can be used to join two string values or expressions in a SELECT query. The double vertical bar symbol is used as string concatenation operator. It is applicable only for character and string column values resulting into a new character expression. Example

SQL> SELECT ename,job,ename||job as "Employees" FROM emp;

 

ENAME      JOB       Employees

---------- --------- -------------------

SMITH      CLERK     SMITHCLERK

The above query shows concatenation of two character literals values.

 

Literals

字面量/常量

• A literal is a character, a number, or a date that is included in the SELECT statement.

常量可以是字符串，数字，日期.

• Data and character literal values must be enclosed within single quotation (单引号)marks.

日期和字符串常量必须用单引号引起来.

• Each character string is output once for each row returned.

为返回的每一行打印一次.

 

单引号'引起来的是字面量.

单引号为定界符.

 

Any hard coded value, which is not stored in database, in the SELECT clause, is known s Literal. It can be number, character, or date value. Character and date values must be enclosed within quotes. Consider the below SQL queries.examples of using literals of different data types in SQL queries.

The query below uses two character literals to join them together.

SQL>	SELECT 'ORACLE'||' CERTIFICATION' FROM DUAL

The query below uses character literals to pretty print the employee's salary.

SQL>	SELECT first_name ||'earns'|| salary||' as of '|||sysdate FROM employees

SQL> SELECT ename,job,ename||'job is'||job as "Employees" FROM emp;

 

ENAME      JOB       Employees

---------- --------- -------------------------

SMITH      CLERK     SMITHjob isCLERK

 

Quote Operator

引用运算符

• Specify your own quotation mark （引号）delimiter.
• Select any delimiter（定界符）.
• Increase readability and usability（适用性）.
• Q运算符适用于字符串本身包含单引号的情况.
• 使用Q操作符可以选择自己的引号定界符.
• 你可以选择任何方便的定界符，[],{},(),<>.

The quote operator is used to specify the quotation mark delimiter of your own. You can chose a convenient delimiter, depedning on the data.

如下例子中，使用q操作符，中括号[]被作为字符串的定界符，里面的单引号'就是普通的字符.

SELECT         department_name|| ' Department' ||q'['s Manager Id: ]'|| manager_id
FROM departments;

SQL> SELECT ename,job,ename||q'['s job is]'||job as "Employees" FROM emp;

 

ENAME      JOB       Employees

---------- --------- ----------------------------

SMITH      CLERK     SMITH's job isCLERK

 

NULL空值

• Null is a value that is unavailable, unassigned(未赋值), unknown, or inapplicable（不适用的）.
• Null is not the same as zero（数值类型的0） or a blank space（字符类型的空格）.
• 包含NULL值的算术表达式，其结果还是NULL.

 

 

If a column doesn't has a definite value, it is considered as NULL. NULL value denotes unknown or unavailable. It is not zero for numeric values, not blank space for character values.

Columns with NULL value can be selected in a SELECT query and can be the part of an arithmetic expression. Any arithmetic expression using NULL values results into NULL. For this reason, columns with NULL value must be handled differently by specifying their alternate values using Oracle supplied functions like NVL or NULLIF.

SQL> SELECT NULL + 1000 NUM
FROM DUAL;

NUM
--------

 

 

 

 

Duplicate Rows

The default display of queries is all rows, including duplicate rows.

DISTINCT Keyword:Suppresses(阻止) duplicates(重复)

这里可以选择多个列，但是他会取多个列的组合的重复，如列1的前三行为1，2，2；列2的前三行为1，3，4，会取出第一行，显示第二行和第三行.

If the data is expected to have duplicate results, use DISTINCT keyword to eliminate duplicates and diplay only the unique results in the query output. Only the selected columns are validated for duplication and the rows will be logically eliminated from the query output. To be noted, the DISTINCT keyword must appear just after the SELECT clause.

The simple query below demonstrates the use of DISTINCT to display unique department ids from EMPLOYEES table.

DISTINCT后可以跟多个列，去掉多个列的组合完全一样的重复行.

SQL> SELECT DISTINCT deptno FROM EMP;

 

    DEPTNO

----------

        30

        20

        10

Question：Which of the following clause is used to suppress duplicates in a SELECT statement?

1. INTERSECT
2. DUPLICATE
3. DISTINCT
4. UNIQUE

Answer: C, D. Duplicate data can be restricted with the use of DISTINCT or UNIQUE in the SELECT statement.

 

使用DISTINCT 的方法COUNT函数和NVL函数的区别：

NVL	SELECT DISTINCT NVL(emp_name, 'AAA') FROM employees;
COUNT	SELECT COUNT(DISTINCT department) AS "Unique departments" FROM employees WHERE salary > 55000;