Database--oracle

about Oracle

1. Sqlplus Connect Remote Oracle Command

Sqlplus (need to set environment variables) Scott (username)/5456 (password) @192.168.230.128:1521 (IP)/ORCL (database name)

2. View Current User

Show user--Sqlplus command

3. View the table under the current user

SELECT * from tab;

4. Execute the previous SQL statement

/--Sqlplus command

Table Operations

1. View Employee table structure

DESC EMP;

2. Query the data in the employee table

SELECT * from EMP;

3, check the employee chart employee number, name, salary, annual salary (monthly *12)

SELECT empno,ename,sal,sal*12 from EMP;

4, 3 ways to take aliases

/** * 3 Ways to Alias: * 1. Add no as is the same * 2. If there is no special field in the alias, you can not add "" */SELECTEMPNO "Employee ID", ename as "employee Name", Sal Monthly, Sal * 12 "annual   Salary",-- There are spaces, to add "" NVL (COMM, 0) "award Select Gold",--have special fields, to add "SAL * 12+NVL (COMM, 0) annual income fromemp;

4, go to Heavy

SELECT distinctjob,empno fromemp;--, distinct all fields after the modification, as long as the fields do not repeat the display

5. String connection

Connector "| |"

/** * String Connection * MySQL version: select concat (' Hello ', ' world ');--to use single quotation marks (single quotation marks for date and string, double quotation marks for column aliases) * Oracle version: Select concat (' Hello ', ' wo Rld ') from dual;--if the previous operation, and no tables are related to the dual table (pseudo-table) * select ' Hello ' | | "World" from dual; */

6. Sorting

The order by can be followed by a column, an expression, an alias, an ordinal

Selectempno,ename,sal,sal * 12 "annual salary" Fromemp ORDER BY "annual salary", sal;--multiple column sort, order by first sort, sorted by following column

Desc only applies to the field closest to him

Selectempno,ename,sal,sal * 12 "annual salary" Fromemp ORDER byempno, "annual salary" desc;--desc only for the field closest to him

3 features of NULL values

1) The result of an expression that contains null is NULL

Selectempno,ename,sal,sal * 12,comm,sal * 12+COMMFROMEMP;

At this point we need a method NVL

NVL (A, A, b)--determine if A is null, or if NULL to take the value of B.

Modified by:

Selectempno,ename,sal,sal * 12,NVL (COMM, 0), SAL * 12+NVL (COMM, 0) fromemp;

2) NULL is never null

Null value is not valid, unspecified, unknown or unpredictable value; null value is not 0 or space

select* fromemp Wherecomm in ((+), NULL);

3) If the collection contains null, do not use not in, you can use the in

select* fromemp Wherecomm not in (NULL);

4) The sorting problem of NULL value

select* fromemp ORDER Bycomm DESC; --reason: In Oracle, the maximum null value

Workaround:

select* fromemp ORDER bycomm DESC NULLS last;

SQL and Sqlplus

SQL Optimization

1.* and column names, use column names as much as possible--column names: reduces the analysis process
2.where parse order is from right-to-left, try to put fake put in front

Database--oracle