Shulou(Shulou.com)06/01 Report--

What can SELECT do?

Projection: the column specified in the query table.

Selection: query the specified rows in the table.

Join: joins between tables, the data to be viewed is placed in multiple tables, and the data between tables is related to each other.

The basic syntax of the SELECT statement:

Specify the columns to display after the SELECT keyword.

The table containing these columns is specified after the FROM keyword.

To demonstrate the SELECT command, we need to log in to the database using sqlplus.

Switch to the Oracle user, unlock the scott user and set the password to tiger with the following command

[root@11gdg1] # su-oracle 11gdg1-> sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 14 09:59:36 2015Copyright (c) 1982, 2011, 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 use the scott user, login to the scott user.

SQL > conn scott/tigerConnected.

Select all columns

SQL > select * from dept DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Select specified column

SQL > select deptno,dname from dept; DEPTNO DNAME- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS

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

SQL statement writing rules

Case insensitive.

Statements can occupy one or more lines.

Keywords cannot be omitted or split into multiple lines.

Usually the clause has a single line.

Use indentation to increase the readability of statements.

In sqlplus or other tools, each statement ends with a semicolon. Tell the client that your statement is over.

Arithmetic operator

SELECT can do arithmetic operations on fields of × × or date types, and date types only support +-operations.

SQL > select ename,sal,sal+300 from emp

Precedence of operator

Like the concept in mathematics, multiplication and division takes precedence over addition and subtraction, and priority can be changed using parentheses

Example 4.

SQL > select ename,sal,sal*12+300 from emp ENAME SAL SAL*12+300-- SMITH 800 9900ALLEN 1600 19500WARD 1250 15300JONES 2975 36000SQL > select ename Sal,sal* (120300) from emp ENAME SAL SAL* (123,300)-SMITH 800 249600ALLEN 1600 499200WARD 1250 390000JONES 2975 928200MARTIN 1250 390000

NULL value

SQL > select ename,sal,comm from emp ENAME SAL COMM---SMITH 800ALLEN 1600 300WARD 1250 500JONES 2975MARTIN 1250 1400BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500 0ADAMS 1100JAMES 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 uncertain, unavailable and unknown number. This number is not equal to any number, including null itself.

Null is not a zero or a space.

When null acts on any arithmetic operator, the result is null

SQL > select ename,sal,sal+comm,sal-comm,sal*comm from emp ENAME SAL SAL+COMM SAL-COMM SAL*COMM---SMITH 800ALLEN 1600 1900 1300 480000WARD 1250 1750 750 625000JONES 2975MARTIN 1250 2650-150 1750000

Column alias

SQL > select ename xingming,sal as gongzi,sal*12 "Annual salary" from emp XINGMING GONGZI Annual salary-- SMITH 800 9600ALLEN 1600 19200WARD 1250 15000

Column connection

Use | | to concatenate two strings.

SQL > select ename | | job from emp;ENAME | | JOB---SMITHCLERKALLENSALESMAN

String

Use''to enclose

SQL > select ename | |'is a'| | job as "Employee Detail" from emp;Employee Detail----SMITH is a CLERKALLEN is a SALESMANWARD is a SALESMAN

So what should I do if my string itself contains single quotes? Oracle is represented by two concatenated single quotation marks.

SQL > select ename | |''s salary is'| | sal from emp;ENAME | |''SSALARYIS' | | SAL---SMITH's salary is 800ALLEN's salary is 1600WARD's salary is 1250JONES's salary is 2975

When there are more single quotation marks in the string, it is difficult to write and not readable. Oracle introduces the qnotify 'operator.

The format is as follows

The middle part of # is a string, and the string can contain single quotation marks. The separator can be a single byte or a multibyte. It can also be compared with characters such as [], {}, ()

SQL > select ename | | Q'['s salary is]'| | sal from emp ENAME | | Q' ['SSALARYIS]' | | SAL---SMITH's salary is 800ALLEN's salary is 1600WARD's salary is 1250JONES's salary is 2975MARTIN's salary is 1250BLAKE's salary is 2850CLARK's salary is 2450SCOTT's salary is 3000KING's salary is 5000TURNER's salary is 1500ADAMS's salary is 1100

Duplicate line

By default, duplicate lines are displayed together. As follows

SQL > select deptno from emp; DEPTNO- 20 30 30 20 30 30 30 10 20 10 30 20

If you want to deduplicate the result set, follow the DISTINCT keyword after SELECT

SQL > select distinct deptno from emp; DEPTNO- 30 20 10

Moreover, multiple columns can be weighed together.

SQL > select distinct deptno,job from emp; DEPTNO JOB--20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST9 rows selected.

Of course, you can also remove the weight of the whole watch.

SQL > select distinct * from emp

DESCRIBE command

This command is used to display the table structure, that is, the definition of the table

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 ~ 7 ~ 2) DEPTNO NUMBER (2)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.