Oracle Learning Chapter One simple query statement--03

1.1 Simplest query statement

Example 1-1

Sql> select * from EMP;

Example 1-1 results

The * number here represents all columns, and it is the same as listing all the column names after select. Query statement with a semicolon (;) End. An EMP (employee) is an employee table.

1.2 How to select a specific column in a query statement

In the normal use of the process we often need is only one of a table or a few pieces of data, but the above query may seem messy results. If we point to information that knows several specific columns, it is easy to use a SELECT statement. It's like we go to the market to buy things, and we don't buy all the things in the marketplace. For example:

Buy cabbage, Pork

from the vegetable market;

Let's give an example:

The company's accountant may need to determine each employee's work number (EMPNO), name (ename), and salary (SAL) each time the payroll is paid. You can use the following statement to implement her requirements, example 1-2.

Example 1-2

Sql> SELECT empno,ename,sal from  EMP;

-Note that here the keyword Select, from, is not differentiated in Oracle case

Example 1-2 results

Selecting the specified column in a query statement is the projection that is called in the relational Database (project)

Of course, after the SELECT keyword, you can select any of the columns and then separate them with commas, and you can specify the order in which they are displayed, as needed. (typically sorted from left to right, followed by a select column), Example 1-3

Example 1-3

Sql>select sal,ename,empno from EMP;

Example 1-3 results

1.3 How to write query statements

Oracle Specifies that the keywords that make up SQL are not abbreviated, that is, you cannot write a select in a query to a SEL or SELEC, and you cannot write from a fro or FR, or to separate the keywords, that is, the keyword must be written as it is.

Of course, you can write SQL statements in uppercase, lowercase, or case-by-case, as in example 1-4

Example 1-4

Sql>select sal,ename,empno from EMP;

Example 1-4 results

Note: Although the use is fine, Oracle still recommends using uppercase and lowercase.

We may find that in the above example, the SQL statement is written in a sentence, in fact we can put the SQL statement on multiple lines, so that our reading. (When the sentence is simple to see, when the content of the query is more, the advantages of writing branches out.) ), such as example 1-5

Example 1-5

Sql> SELECT sal,ename,empno  2 from  EMP;

Output Result:

Note: The complete SQL command is called the statement (statement), the Rose keyword, and the following option called the word (clause), for example, "SELECT * from EMP;" Called a statement, while a "select *" is called a clause, "from EMP" is also called a clause.

Example 1-6

Sql> SELECT empno,ename,sal  2         eptno,job  3 from  EMP;

Example 1-6 results

From the statement in example 1-6 we can easily see: the first row and the second behavior of the SELECT clause, the third behavior from clause. The query results for example 1-6 show the work number (EMPMO), name (ename), payroll (SAL), department number (DEPTNO), and position (job) of each employee in the EMP table.

1.4 Default display format for column headings and data

At the beginning of the article, I have seen some problems with the display of date types due to the different character sets. In order to solve this problem you can use the SQL statement in 1-7.

Example 1-7

Sql> alter session  2  Set nls_date_language = ' AMERICAN ';

Example 1-7 results

To make the display and home clear, you can use example 1-8 and example 1-9 of the Sql*plus format statement

Example 1-8

Sql> Col hiredate for A15

Example 1-9

Sql> Col ename for A8

Note: If you do not understand the above Sql*plus command, please do not be nervous, the latter will slowly explain

Sql*plus the default list title results display:

The character and date data are left-aligned;

The digital data is right-justified.

Examples of example 1-10 can be used to verify the above conclusions.

Example 1-10

Sql> SELECT empno,ename,sal,  2         hiredate,job  3 from  EMP;

Example 1-10 results

No words, after writing the article, save the release, the picture is gone, very silent. Re-editing one side, also found some statements of errors, has been corrected

Oracle Learning Chapter One simple query statement--03