Oracle Learning SQL (i) retrieving data using Select

What can select do?

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/71/A9/wKiom1XV-dXjc32KAAHWZqCVaRI172.jpg "title=" 01.PNG "alt=" Wkiom1xv-dxjc32kaahwzqcvari172.jpg "/>

Projection: The specified column in the query table.

Selection: The specified row in the query table.

Join: A connection between a table and a table, the data to be viewed is placed in multiple tables, and the table is related to the data between the tables.

Basic syntax for the SELECT statement:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/71/A5/wKioL1XV_T2jDhTkAABw_aCG5UE334.jpg "title=" 02.PNG "alt=" Wkiol1xv_t2jdhtkaabw_acg5ue334.jpg "/>

After the SELECT keyword, specify the columns that you want to display.

The FROM keyword is followed by the table that contains the columns.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/71/A5/wKioL1XV_f-QYY1IAAFFxnN4V60467.jpg "title=" 03.PNG "alt=" Wkiol1xv_f-qyy1iaaffxnn4v60467.jpg "/>

To demonstrate the Select command, we need to log in to the database using Sqlplus.

Switch to the Oracle user, unlock the Scott user with the following command and set the password to Tiger

[Email protected] ~]# su-oracle 11gdg1-> sqlplus/as sysdbasql*plus:release 11.2.0.3.0 Production on Tue Jul 14 09  : 59:36 2015Copyright (c) 1982, Oracle. All rights reserved.  Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit productionwith The partitioning, OLAP, Data Mining and Real application testing optionssql> alter user Scott account unlock identified by Tiger; User altered.

All of the following demos are logged into Scott users using Scott users.

Sql> Conn scott/tigerconnected.

Example one, select all columns

Sql> select * FROM dept; DEPTNO dname LOC-------------------------------------------------------------------------------------------10 ACCOUNTING NEW YORK20, DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON

Example two, selecting the specified column

Sql> select Deptno,dname from dept; DEPTNO dname----------------------------------------------------ACCOUNTING20 RESEARCH30 SALES40 OPERATIONS

Columns are separated by commas, and the results are displayed in the same order as the fields after select.

SQL statement Authoring Rules

• Casing is not sensitive.

• Statements can occupy one or more rows.

• Keywords cannot be omitted, or split into multiple lines.

• The usual case clause is exclusive of one row.

• Use indentation to increase the readability of the statement.

• In Sqlplus or other tools, a semicolon is added to the end of each statement. Tell the client that your statement is over.

SELECT can perform arithmetic operations on fields that are shaping or date-type

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/71/A5/wKioL1XWAzLgDLyMAACd2sc2XEs517.jpg "title=" 04.PNG "alt=" Wkiol1xwazlgdlymaacd2sc2xes517.jpg "/>

Example three, showing the employee's name, salary and salary +300

Sql> select ename,sal,sal+300 from emp;

Precedence of Operators

Like the concepts in mathematics, multiplication takes precedence over add and subtract, and can use parentheses to change precedence

Example Four,

Sql> select ename,sal,sal*12+300 from emp; ename SAL sal*12+300--------------------------------------------------SMITH 8009900ALLEN 19500WA RD 1250 15300JONES 2975 36000

Sql> Select ename,sal,sal* (12+300) from EMP; ename SAL sal* (12+300)----------------------------------------------------SMITH 800249600ALLEN 1600499200W ARD 1250390000JONES 2975928200MARTIN 1250390000

Null value

Sql> select Ename,sal,comm from emp; ename salcomm--------------------------------------------------SMITH 800ALLEN 300WARD 1250 500JON ES 2975MARTIN 12501400BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER + 0ADAMS 1100JAME S 950FORD 3000MILLER 1300

We see that some of the data under the Comm column is empty. This is a special value, which we call null.

Null can be understood as an indeterminate, unreachable, unknown number. This number is not equal to any number, including null itself.

Null is not a 0 or a space.

Example five, null acts on any arithmetic operator, and the result is null

Sql> select Ename,sal,sal+comm,sal-comm,sal*comm from emp; ename SAL sal+comm sal-comm sal*comm----------------------------------------------------------------------S MITH 800ALLEN 16001900 1300 480000WARD 12501750 625000JONES 2975MARTIN 12502650-1 50 1750000

Column Aliases

Sql> Select Ename xingming,sal as gongzi,sal*12 "annual salary" from EMP;  Xingming Gongzi Annual salary-----------------------------------------------------SMITH 9600ALLEN 1600 19200WARD 1250 15000

Column connections

Use | | Concatenate two strings together.

Sql> Select Ename| | Job from EMP; ename| | JOB---------------------------------------------------------Smithclerkallensalesman

Literal string, enclosed in "

Sql> Select Ename| | ' is a ' | | Job as "Employee Detail" from EMP; Employee Detail---------------------------------------------------------------------------SMITH is a clerkallen is a Salesmanward is a salesman

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1686704

Oracle Learning SQL (i) retrieving data using Select