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

Use the emp table under the scott user to test

0. Environment setting

-- the following code is to set up the display of the emp table, col empno for 9999, exchange ename for a10, job for, mgr for, 9999, sal for, sal for, 9999,

1. Query

Query all the contents of the emp table. The * sign represents a wildcard and indicates all fields in the table, but the * sign cannot be used with specific fields.

Select * from emp

Or

Select empno,ename,sal,deptno from emp

Query the employee number, name, salary, department number and listing of the emp table, which is not case-sensitive, but uppercase is recommended

Select empno "number", ename "name", sal "salary", deptNO "Department number" FROM Emp

Non-repetitive work of querying emp tables [distinct]

Select distinct job from emp

Query employee's number, name, monthly salary, annual salary (monthly salary * 12)

Select empno,ename,sal,sal*12 "annual salary" from emp

Query employee's number, name, entry time, monthly salary, annual salary, annual income (annual salary + bonus) [null value judgment and use]

Select empno "number", ename "name", hiredate "entry time", sal "monthly salary", sal*12 "Annual salary", sal*12+comm "Annual income" from emp

If the result is null, the value of null is not displayed in the sqlplus client tool

Note: when null is calculated with a specific number, the result is null.

To solve the null problem, use the NVL () function, NVL (NULL b): if an is NULL, replace it with b; if an is non-NULL, instead of b, return the value of a directly.

Select NVL (null,10) from emp; result has 14 lines of record

Select NVL (null,10) from dual; result has 1 line record

Select empno "number", ename "name", hiredate "entry time", sal "monthly salary", sal*12 "Annual salary", sal*12+NVL (comm,0) "Annual income" from emp

Use column aliases, query employee's number, name, monthly salary, annual salary, annual income (annual salary + bonus), AS can be upper or lower case, and AS can be omitted, aliases are in double quotes [aliases and quotes]

Select empno AS "number", ename as "name", sal "monthly salary" from emp

Or

Select empno AS number, ename as name, sal monthly salary from emp

Difference

Select empno AS "number", ename as name, sal "monthly salary" from emp

Aliases without double quotes cannot have spaces; aliases with double quotes can have spaces

You can only add double quotation marks, not single quotation marks, because in oracle, single quotation marks indicate a string type or a date type.

Column names cannot use single quotation marks because oracle considers single quotation marks to be string or date

[dual dumb table, string concatenation symbol | |]

Use dual dumb table or pseudo table, use string concatenation symbol | |, output "hello world", from is required in oracle

Select 'hello' | |' world' "result" from dual

Use the string concatenation symbol | | to display the following format information: * the salary is * US dollars.

Select ename | | 'salary is' | | sal | | 'USD' from emp

[system time sysdate]

Use sysdate to display the current time of the system. By default, oracle displays only the date, not the time. Format: 26-4-15

Select sysdate from dual

The characteristics of SQL sentence

1) it is the official ANSI standard of SQL92/99. As long as it is written according to this standard, it can be directly implemented in any relational database.

2) the key words of SQL statement cannot be abbreviated, for example: select,where,from

3) case insensitive, uppercase is recommended

4) can add, delete, modify and query the table data

5) must end with a semicolon

6) it is usually called a statement

Characteristics of SQLPLUS command

1) is a tool that comes with oracle, and the commands executed in this tool are called SQLPLUS commands

2) the key words in the commands of the SQLPLUS tool can be abbreviated or not abbreviated, for example: col ename for A10

3) case insensitive, uppercase is recommended

4) cannot add, delete, change and check the table data, but can only complete the display format control, such as setting the display column width, clearing the screen, and recording the execution results.

5) it can end without a semicolon or with a semicolon. Individuals advocate ending with a semicolon regardless of SQL or SQLPLUS.

6) it is often called a command, which is a command in the SQLPLUS tool

Note: the SQLPLUS command is a unique statement in the SQLPLUS tool

The single quotation marks appear as follows:

1) string type, for example: 'hello' | |' world'

2) date type, such as'25-April-15'

The double quotation marks appear as follows:

1) list aliases, such as sal*12 "annual salary" or sal*12 annual salary. It is recommended to use "" double quotes as column aliases.

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.