当前位置：Gxlcms > 数据库问题 > SQL Fundamentals: Basic SELECT statement基本的select语句

SQL Fundamentals: Basic SELECT statement基本的select语句

时间：2021-07-01 10:21:17 帮助过：31人阅读

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

SQL statements are case sensitive
Keywords can be abbreviated to build a standard
SQL statements are case in-sensitive
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

描述命令：显示表结构

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.

SQL Fundamentals: Basic SELECT statement基本的select语句

人气教程排行