Oracle's basic statement

The SELECT statement in SQL

Spool d:\ basic query. txt
--spool recorded on the screen as a text file
--Clear screen
Host Clear (Linux)
Host CLS (window)
--Current user
Show user
--The table under the current user
SELECT * from tab; (tab data dictionary, tables and views)
--Structure of the employee table
DESC EMP
Empno Employee number ENAME employee Name Job Mgr Boss
HireDate Entry date sal monthly Comm Bonus DEPTNO Department number
--Query all employee information
SELECT * FROM emp
--Set line width
Show Linesize
Set Linesize 150
--Set column width
Col ename for A8 (set name this line is 8 characters wide)
Col Sal for 9999 (4 columns are represented by numbers)
/(Executes the previous statement)
/*sql Optimization (Database optimization)
1. Use column names as much as possible
*/

Where the cursor is located represents the previous record
If the SQL statement is written incorrectly, the modified method
--c command Change
2 (on behalf of modified second article)
C/form/from (What to change to what)
/(Executes the previous statement)
--An SQL statement that supports arithmetic expressions
Select Empno,ename,sal,sal*12,comm,sal*12+comm
From EMP
/* NULL problem in SQL statement
1. An expression that contains null is NULL, the workaround
Filter null function NVL (A, a) if a is empty, the value is B, if a is not empty, the value is a
2.null Forever!=null
is NLL represents equal to null
3. If the collection contains null, do not use not, but you can use the in
*/

--Alias of column
Select EMPNO,ENAME,SAL,SAL*12,COMM,SAL*12+NVL (comm,0)
From EMP
Ed Open the default editor
Select Empno as "employee number", ename "name", Sal Monthly, sal*12 as, comm as, SAL*12+NVL (comm,0) as
From EMP
As "employee number" and "name" two aliases no difference
Aliases that contain keywords or special symbols or pure numbers should be added ""

Select *| {DISTINCT column|expression [alias]}
From table

--distinct
Select distinct deptno,job form emp; (two columns as a whole does not repeat)

--Connectors and--concat function the same (connection string)
Select ' Hello ' | | ' World ' string from dual;
Select concat (' Hello ', ' word ') from dual;
When the operation is not related to any table
Select 3+2 from dual;

--explanation dual: pseudo-table
Dual appears only to meet the requirements of the SELECT statement (sql99 requires select must and from each other)
--Query Employee information: * * * Salary is * * *
Select Ename| | ' The salary is ' | | Sal information from EMP;

Hadoop Data Warehouse hive Support SQL92 can write select directly
--string
Single quotation marks: representing dates and characters
Double quotes: Represents the alias of a column

--sql and Sqlplus
SQL additions and deletions (change data)
Sqlplus Tools provided by Oracle (data cannot be modified)
The difference between the two:
SQL is not abbreviated (INSERT Select Update Delete)
Sqlplus abbreviation (desc ed C col for)

The ports in Linux cannot be changed to less than 1024 (Linux system reserved)

The Oracle Service listener listens on a port of 1521 and logs on to the database (ORCL) service by listening on 1521 ports

--Task scheduling: (ORACLEJOBSCHEDULERORCL)
What to do after the arrival time
Oracledbconsoleorcle (Oracle's console, equivalent to a listening 1158 port)


To administer the database:
192.168.56.101:1158/em/console/logon/logon
Sys
Password
Sysdba

Isqlplus only available in oralce9i and Oracle 10g (Sqlplus Web version)
192.168.56.101:5560/isqlplus
Scott
Password
Web version complies with HTTP protocol, plaintext protocol is unsafe

11G compliance with HTTPS encryption protocol

--spool off

Single-line function:
Functions: Simplifying operations
Function:
Single-line function (NVL)
Single-line transformation produces results
character, value, date, conversion, general, conditional expression

Multiline function (max)
Multiple lines of transformation produces results

Single-line function:
Capitalization control: lower (lowercase) upper (upper case) Initcap (uppercase)
Character functions: Concat (String connection) concat (A, a, b) connect the A to the other
substr (substring of string) substr (A,b,c) from A, the B bit begins to take C-bit
Length Length (a) LENGTHB (a) byte length
InStr (A string to find another string) InStr (A, B) return position/no return 0
Lpad|rpad (left padding, right padding) Lpad (a,b,c) will be a, populated with B bits, filled with a character C
Trim (remove pre-and post-specified characters) trim (a from B) remove front and back a in B
Replacel (what to replace) Replalce (A,B,C) replaces B in a with C

Numeric functions: Round (A, b) a retains a decimal number (rounded) reserved decimals, rounded
Trunc (A, B) truncated, truncated value

Date:
Mysql:date datetime (contains month day)
Oracle:date= Date + time (DD-MON-RR)
--Query the current time:
Select Sysdate from dual

To_char what format to convert to a string

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

Date operations only add and subtract without multiply and divide (two dates cannot be added (meaningless)
--Yesterday Today tomorrow
Select (sysdate-1) yesterday, Sysdate today, (sysdate+1) tomorrow
From dual

--Calculation of the employee's seniority: Day of the week month
Select Ename,hiredate, (sysdate-hiredate) days, (sysdate-hiredate)/7 weeks,
(syddate-hiredate)/30 month, (sysdate-hiredate)/365 Years

from EMP;
--Two months of difference in Date: Months_between (A, B)
--How many months to add: add_months (sysdate,56)
--the last day of the current month: Last_day (sysdate)
--The next date for the specified date Next_day
/*
Next_day application: Automatic backup of data per week
Offsite Disaster recovery (database remote backup)
1. Distributed database
2. Snapshots and triggers
*/
Select Next_day (sysdate, ' Monday ') from dual
Select round (sysdate, ' month '), round (sysdate, ' year ') from dual

Conversion functions: Conversion of data types (implicit and explicit types)
SELECT * from emp where hiredate= "1 July-November-81" (Implicit type conversion)
Display Type conversions:
Number character Date
To_char;to_number;to_date

YYYY year mm month DY day DD

To_char convert dates to strings (To_number opposite)
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss "Today is" Day ") from dual;

--Query Employee Salary: Two decimal places, thousand symbol currency code
Select To_char (Sal, ' l9,999.99 ') from EMP;

To_date convert characters to dates (characters are converted to dates by date format)
To_date (char, ' Format_model ') and To_char opposite

General functions: Applies to any type and also to null values
Oracle contains 2 of the enhancements that do not include 2

--NVL2 (A,b,c) when a =null, return C; otherwise return b
Select SAL*12+NVL2 (comm,comm,0) from EMP;

--nullif (A, B) returns NULL when A=B, otherwise returns a
Select Nullif (' abc ', ' abc ') value from dual;

--coalesce find the first non-null value from left to right
Select Comm,sal,coalesce (comm,sal) "First not null value" from EMP;

Conditional expression: If-then-else logic:
Case expr when Expr1 then RETURN_EXPR1
When Expr2 then RETURN_EXPR2
When EXPR3 then RETURN_EXPR3
else else_expr
End
--Pay rise
Select Ename,job,sal before the rally
When the case job is ' president ' then sal+1000
When the ' manager ' then sal+800
Else sal+400 after the rise
from EMP;

Case job when sal<3000 and then * * *
When sal>=3000 and sal<6000 then * * *
else * * *

Select Ename,job,sal before the rally
Decode (Job, ' president ', sal+1000
' manager ', sal+800
Sal +400) after the rise
from EMP;

Filtering and Sorting
--character case sensitive, date format sensitive (default DD-MON-RR)
Date and string to enclose in single quotation marks
SELECT *
From EMP
where hiredate = ' 1 July-November-81 '
where hiredate = ' 1981-11-17 ' ERROR
--Query the default format:
SELECT * from v$nls_parameters (dynamic view) (Gets the language of the operating system)
Alter Session|system set nls_date_format= ' Yyyy-mm-dd '
Comparison operation: Assignment using: = Symbol
Java:int a = 0;
PL/SQL:A Number: =0
Between and 1. with boundary 2. Small value in front, large value in the rear
In (null,30) (can) and not in (null,30,40) (Can not)
--like Fuzzy query% means that all characters _ represent any one character
--query employees with underlined employees
SELECT *
From EMP
where ename like '%\_% ' escape ' \ ' (Escape declaration transfer character is \)

Oracle's basic statement