Basic query and conditional filtering for Oracle learning, grouping function usage

Oracle is the first word of Oracle unearthed in Yin ruins, so it is called Oracle in China, founded in 1977 and headquartered in California, USA.

After installation, there are two main services that need to be turned on:

1, instance service, ORACLESERVICEORCL, decide whether the database can be connected

2, monitoring service, Oracleoradb11g_home1tnslistener, decide whether the database can be connected remotely

After installing the client, sign in:

Sqlplus User name/password

If you are Telnet, you need to add a connection alias, or IP:

Sqlplus User name/password @//ip address/instance name orsqlplus username/password @ connection name

Database Basic query:

SELECT * from tab;

tab is a data dictionary that describes what tables and types the current user has, and looks at the table structure:

DESC Dept

Note that if the display row size and width are incorrect, you can change the file by modifying the login settings:

Path: \app\administrator\product\11.2.0\client_1\sqlplus\adminset linesize 140;//setting Row size set pagesize 120;//Setting page width col Empno for 99999;//Modify numeric field length Col ename for a20;//set character field length

SQL syntax considerations: Keywords cannot be abbreviated, cannot wrap, end with a semicolon!

View all commands for Sqlplus:

? Topic

Query SQL statement syntax:

SELECT [Distinct]*|column_name|expr|alias from TableName

Query employee number, name, salary, bonus, annual salary:

Note that the bonus field has a null result and can be resolved by considering an empty function, namely: NVL (A, B), if A is null, returns a

If you need to view the employee table for different department numbers and remove duplicates, you can do this:

In general, we can also use pseudo-tables to perform some mathematical calculations or function operations:

Select 3+20*5,sysdate from dual;

Query condition filtering for comparison operators: = =! <> < > <= >= (Between and)

CASE1: Inquire about employee information for the November 17, 1981 entry Date:

SELECT * from emp where hiredate = ' 1981-11-17 '

It is important to note that if the display text does not match the format string, it is usually written in the exact date format:

If you want to modify this date format, you can go to the administrator user after modification, as follows:

Sql> Select Sysdate from dual;//Modify the session date format sql> alter session set nls_date_format= ' YYYY-MM-DD ';//after which you can query:sql> SELECT * from emp where hiredate = ' 1981-11-17 ';

logical operators and or not, specifically here, find employee information for payroll at 1000~2000:

SELECT * from emp where Sal >= and Sal <= 2000;//or select * from emp where Sal between and 2000;

Between and is a closed interval, from small to large!

How to correctly query employee information for which the bonus is empty-null

SELECT * FROM EMP where comm is null;

　　When there are multiple conditional writes for and OR, how can I write better?

The SQL statement is executed from right to left, and the case should be placed on the right side, or case, should be really placed on the right.

like-a fuzzy query, '% ' matches any number of characters, ' _ ' matches any one character

Focus: Query with the underscore ' _ ' should be how to query:

SELECT * from emp where ename like '%/_% ' escape '/';

　　Sort: Group by; Having ORDER by; The syntax is as follows:

ORDER BY Col|expr|alias|number

Employee salary in order from big to small (serial number)

SELECT * from emp ORDER by Sal desc;//or select * from emp ORDER BY 6 desc;//or select Empno,ename,job,mgr,sal,comm,deptno F ROM emp ORDER by 5 desc;

It is more important to note the null case, the default infinity:

The solution is:

SELECT * from emp where deptno = "ORDER by comm" Desc nulls last;
Or
SELECT * from emp where deptno = the ORDER by NVL (COMM,-1) desc;

Single-line functions for Oracle:

What is a single-line function, which transforms a row, produces only one result.

Lower (lowercase), upper (uppercase), Initcap (capital letter), | | (String connections can be made)

Select lower (' Hello World ') one,upper ("Hello World") two,initcap (' Hello World ') from Dual;select ' aaaa ' | | ' BBBB ' | | ' CCCC ' from dual;

substr (string, position, length), starting from the position to intercept the length, the length can be omitted, representing interception to the end:

Select substr (' HelloWorld ', 1,3) one,substr (' HelloWorld ', 1), substr (' HelloWorld ', -3) three from dual;

Length for string lengths, LENGTHB for byte lengths:

InStr (STR1,STR2), determines whether STR2 is in str1, returns 0 if there is a return first position in the presence of:

Select InStr (' Hello World ', ' Llo ') from dual;

Lpad,rpad, the left and right padding L (r) pad (Str,len,char) returns len length string, if Str is not long enough, it is a char character fill:

Select Lpad (' Hello ', ten, ' # ') from dual;

Trim (str) de-Str,substr,strto, replace

Select ' AAA ' | | Trim (' Hello World ') | | ' BBB ' from dual;

You can play like this:

IMPORTANT: Numeric functions, round rounding, trunc truncation, mod modulo

Select Round (45.926, 2) One, round (45.926, 1) Two, round (45.926, 0) three,  round (45.926,-1) Four, round (45.926,-2) five from dual; Select Trunc (45.926, 2) One, trunc (45.926, 1) Two, trunc (45.926, 0) three,  trunc (45.926,-1) Four, trunc (45.926,-2) five from dual; Select mod (600,1000), mod (1000,600) from Dual;//ceil and floor up, rounding down select Ceil (121/60), Floor (121/60) from dual;

　Important: Conversion functions, To_char,to_number,to_date

Convert your salary to a local currency character type:

Select Sal,to_char (Sal, ' l9,999 ') from EMP;

If you want to turn this back into a numeric type, the statement is the same:

Select To_number (' ￥1,250 ', ' l9,999 ') from dual;

To_char and To_date Show ' Yyyy-mm-dd hh24:mi:ss today is the day of the week '

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

Reverse the above string to a date:

Select To_date (' 2017-11-10 15:48:13 today is Friday ', ' yyyy-mm-dd hh24:mi:ss ' Today is "Day") from dual;

An implicit date conversion problem can be resolved here:

SELECT * from emp where TO_CHAR (hiredate, ' yyyy-mm-dd ') = ' 1981-11-17 ';

The above can display the conversion, as far as possible to display the conversion.

IMPORTANT: Date Functions

1, show yesterday, Today, tomorrow:

Select Sysdate-1 yesterday, Sysdate today, sysdate+1 tomorrow from dual;

2, calculation of employee seniority, can be by day, week, month, year, date subtraction method

Basic query and conditional filtering for Oracle learning, grouping functions using