Oracle special writing
First n lines
- Use the ROWNUM keyword, the same as the limit in mysql
#Get the first ten lines select * from table where ROWNUM<=10;
Time field filtering
- The built-in to is required for comparing time in Oracle_ Date (STR, FMT) function
#Filter results using two time parameters SELECT WELL_ID,SPUD_DATE FROM CD_WELL_SOURCE Where SPUD_DATE >= TO_DATE('1987-12-10 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND SPUD_DATE < TO_DATE('1999-12-10 00:00:00','yyyy-MM-dd hh24:mi:ss') AND ROWNUM<=10;
oracle set primary key auto increment column
New test table
create table member( memberId number primary key, memberMail varchar2(20)not null, memberName varchar2(20) not null, memberPassword varchar2(20) );
A custom sequence (similar to auto increment function) is required
CREATE SEQUENCE test_sequence INCREMENT BY 1 -- How many at a time START WITH 1 -- Count from 1 NOMAXVALUE -- Do not set maximum NOCYCLE -- Always accumulate, not cycle NOCACHE -- No buffer
Finally, you need a trigger to execute it
create trigger emp_trig(Trigger name) before insert on member(Table name) for each row when (new.memberId is null) begin select emp_sequence((sequence name).nextval into:new.memberId((auto increment field) from dual; end;
- Introduce common oracle functions
Introduction to dual table
It is different from the sql writing method of directly obtaining constants in mysql. oracle must contain the from table name. If it is a constant, you need to write a specific dual table
SQL> select 3 from dual; 3 ---------- 3 SQL> select 3; select 3 * ERROR at line 1: ORA-00923: FROM keyword not found where expected
Dual is an actual row column table in Oracle, which can be read by any user. It is often used in the Select statement block without target table
- Decode (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value) is equivalent to case when multiple selection function
# The example is the sex column in the user table. It outputs male for 0 and female for 1. Neither is satisfied or empty. The output is unknown select id, username, age, decode(sex,0,'male',1,'female','unknown') from users;
(+) sign usage
External connections can also be represented by "(+)". Some notes on using (+):
The (+) operator can only appear in the WHERE clause and cannot be used with the OUTER JOIN syntax.
When you use the (+) operator to perform an outer join, if you include multiple conditions in the WHERE clause, you must include the (+) operator in all conditions.
The (+) operator applies only to columns, not expressions.
The (+) operator cannot be used with the OR and IN operators.
The (+) operator can only be used to implement left and right outer joins, not complete outer joins.
select * from t_A a left join t_B b on a.id = b.id Equivalent to Select * from t_A a,t_B b where a.id=b.id(+); Similarly Select * from t_A a,t_B b where a.id(+)=b.id; Right connection
- It is equivalent to contact(), splicing the first and second strings together
# You can splice constants or columns SQL> select 'aaa' || ',' || 'bbb' || ',' || 'ccc' from dual; 'AAA'||','| ----------- aaa,bbb,ccc
- Non empty function, NVL(eExpression1, eExpression2). If expression 1 is empty, expression 2 is returned. It is generally used for numeric types. If it is empty, it returns 0
# If the student table id is empty, 0 will be returned select NVL(id, 0) id1 from student;
- The function is to convert numeric or date type to character type. The second parameter of all formatting functions is the template for conversion.
- See details tochar Daquan
- String interception function, should be very familiar with
SQL> select substr('adbdfg',1,4) from dual; SUBS ---- adbd SQL> select substr('adbdfg',2) from dual; SUBST ----- dbdfg
- Format 1: substr (string, string, int a, int b);
- String the string to be intercepted
- The starting position of the intercepted string (Note: when a is equal to 0 or 1, it is intercepted from the first bit)
- b the length of the string to be intercepted
- Format 2: substr (string, int a);
- String the string to be intercepted
- A can be understood as intercepting all subsequent strings from the a character.
Character lookup function
Format 1: instr (string1, string2) / / instr (source string, target string)
Format 2: instr (string1, string2 [, start_position [, nth_appearance]] / / instr (source string, target string, start position, matching sequence number)
Parsing: the value of string2 should be found in string1 from start_ The value given by position (i.e. position) starts to be retrieved in string1 and the nth is retrieved_ Appearance (several) occurrences of string2.
Note: in Oracle/PLSQL, the instr function returns the position of the string to be intercepted in the source string. Only retrieve once, that is, from the beginning of the character to the end of the character.
1 select instr('helloworld','l') from dual; --Return result: 3 Default first occurrence“ l"Location of 2 select instr('helloworld','lo') from dual; --Return result: 4 Namely“ lo"At the same time, the first letter“ l"Location of occurrence 3 select instr('helloworld','wo') from dual; --Return result: 6 Namely“ wo"At the same time, the first letter“ w"Location of occurrence
Round function usage
Intercept digital function
The format is as follows: ROUND (number[,decimals])
Where: number is the value to be intercepted
decimals indicates that the number of digits after the decimal point should be reserved. Optional. If it is ignored, all decimal parts will be truncated and rounded. If it is negative, it means the number of digits to the left from the decimal point. The corresponding integer number is filled with 0 and the decimal will be removed. It should be noted that, unlike the trunc function, the truncated number should be rounded.
SQL> select round(73.2456,2) from dual; ROUND(73.2456,2) ---------------- 73.25
floor function usage
- Function: returns the maximum integer less than or equal to n
SQL> SELECT FLOOR(0.1) FROM dual; FLOOR(0.1) ---------- 0
replace function usage
The REPLACE function replaces a value in a string with another value.
For example, you can REPLACE each occurrence of a letter with a matching number. The format of REPLACE is as follows:
REPLACE (char, search_string [, replace_string])
If the value of the replace_string variable is not specified, it will be deleted when the value of the search_string variable is found. The input can be any character data type - CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
# example: select replace('0123456789','0','a') from dual;--a123456789 select replace('0123456789','0','') from dual;--123456789 select replace('0123456789','0') from dual;--123456789
1.select trunc(sysdate) from dual --2013-01-06 Today's date is 2013-01-06 2.select trunc(sysdate, 'mm') from dual --2013-01-01 Return to the first day of the month. 3.select trunc(sysdate,'yy') from dual --2013-01-01 Return to the first day of the year 4.select trunc(sysdate,'dd') from dual --2013-01-06 Return to the current date 5.select trunc(sysdate,'yyyy') from dual --2013-01-01 Return to the first day of the year 6.select trunc(sysdate,'d') from dual --2013-01-06 (Sunday)Returns the first day of the current week 7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 The current time is 17:35 8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()Function has no precision of seconds
Number requires a truncated number.
Num_digits is a number used to specify rounding precision. The default value of num_digits is 0.
TRUNC() function intercepts without rounding
# Example select trunc(123.458,1) from dual --123.4