1. Functions

1.1 case conversion function

function	describe
LOWER()	All lowercase
UPPER()	uppercase
INITCAP()	title case

1.2 String Operating Functions

function	describe
concat()	Split String
Substr (field, startIndex,endIndex)	substr
length()	String Length
Instr (field,'character')	Location index where specified characters appear
trim()	Remove spaces before and after specified characters

1.3 Digital Operational Functions

function	describe
Round (parameter, reserve bits)	ceil
TRUNC (parameter, reserved bits)	Direct rounding, not rounding
mod(x,y)	Remainder of x divided by y

1.4 Period Function

function	describe
add_months(date,n)	Add an n-month to the date date
lastday(date)	Returns the last day of the current month on the specified date
round(date,[fmt])	Returns a rounding in fmt format
trunc(date,[fmt])	No rounding of dates, intercept directly
extract(fmt from date)	Extract a specific part of a date
sysdate	Return current system time

• fmt:
  • YEAR: Intake January 1 of a year, discard for thousands of months, and the second half as the next month
  • MONTH: Intake into the first day of a year, i.e. discard the previous month and the second half as the next month
  • DDD: By default, one day of the month, the closest day, the first half of the day, the second half of the day as the next day
  • DAY: Round to the most recent Sunday, that is, the first half of the week, and the second half of the week as the next Sunday
  • Be careful:
    • YEAR,MONTH,DAY can be matched for DATE type
    • HOUR,MINUTE,SECOND must match TIMESTAMP type
    • The result of the HOUR matching is not time zone added, so the result running in China is less than 8 hours

1.5 Conversion Function

function	describe
to_char(date | number,[ fmt ])	Converts a date or string to a specific string
to_date(x,[ fmt ])	Converts a string to a date type as fmt
to_number(x,[ fmt ])	Converts a string to a number in fmt format

• Date formatting elements:
  • YYYY: Year represented by 4 digits
  • YEAR: Year described in English
  • MM: Month in 2 digits
  • MONTH: Month described in English
  • MON: Three-letter English description month for short
  • DD: Date represented by two digits
  • DAY: Days of the week in English
  • DY: Short name for the day of the week in three-letter English description
  • HH24:MI:SS: Formatting in seconds and minutes
  • DDspth: The day of the month described in English
  • fm: Format keyword, optional

1.6 Other one-line functions

function	describe
nvl(x,value)	Return value if x is empty, otherwise return x
nvl2(x,value1,value2)	If x is empty, return value1, otherwise return value2

1.7 Aggregate Function

function	describe
avg()	Draw value
sum()	Summation
min()	Maximum
max()	minimum value
count()	count

1.8 Conditional Expression

• case...when...then...else...end:equivalent to if...else if...else...else

  --Write 1: Follow the field directly
   case field when value 1 then result 1
  	when value 2 then result 2
  		else result three end s
   --Write 2: Follow the judgement statement
   case when condition judges 1 then result 1
  	when condition judgement 2 then result 2
  		else result 3 end
  

• decode:

   Decode (field 1,'value 1', result 1,'value 2', result 2, result 3)
  

2. Views

• Definition:
  • When we query multiple times with the same complex statement, we don't want to write complex statements every time, so we can create a view
• Advantage:
  • Provides another level of table security
  • Hide the complexity of the data
  • Simplified SQL commands
  • Isolate base table changes
• Grammar:
  • Create a view:
    • CREATE ViEW View Name AS sql Query Statement
  • Delete View:
    • DORP VIEW View Name
  • Modify View:
    • UPDATE VIEW View Name WHERE Condition
  • Query View:
    • SELECT * FROM view name

3. Collection Operations

Method	describe
UNION	Remove duplicate records
UNION ALL	Keep duplicate records
INTERSECT	intersect
MINUS	Difference Set

• Grammar:

  • Select statement set operation method select statement

4. Connection Query

• (+): oracle-specific way in which the unmarked party is used as the base table

  -- Equivalent to left outer connection
  select * from a,b where a.id = b.id(+)
  -- Equivalent to right outer connection
  select * from a,b where a.id(+) = b.id
  

5. Sort operation

• Put nulls in the result set first or last: nulls first/nulls last
  • null default
  • Write after order by

6. Sequence operation

• Equivalent to autogrowth sequence in MySql

  • Complete Writing:

    • create sequence sequence sequence name
    • start with 5 - start with 5
    • increment by 2 - 2 per increase
    • maxvalue 20 - maximum 20
    • cycle - Circulable
    • Cache 5 - cache 5

  • Create Sequence:

    • create sequence sequence sequence name

  • Query sequence:

    • select * from emp
      • Current value: currval
      • Next value: nextval

  • Delete Sequence:

    • drop sequence sequence sequence name

7. Index

• Increase the speed of retrieval

  • Big data creates indexes, indexes columns that are often used
  • Index should not exceed four levels
  • Primary key with index

  --Create an index:
     create index name on table name (column)
  --Delete index:
     drop index index name
  

8. Window Functions

• It can be simply understood as showing all the data after grouping

  row_number()over( partition by "Partition field" order by "Grouping Fields" asc/desc ) 
  

9. sql exercise

drop table emp;
create table emp(
       eid number,
       ename varchar2(224),
       birthday date,
       salary float,
       did number
)

insert into emp values(1,'jack',to_date('2000-1-30 16:20:31','yyyy-mm-dd hh24:mi:ss'),3000.0,2);
insert into emp values(2,'rose',to_date('1999-5-1 17:00:00','yyyy-mm-dd hh24:mi:ss'),5000.0,3);
insert into emp values(3,'admin',to_date('2000-10-1 00:56:59','yyyy-mm-dd hh24:mi:ss'),4000.0,4);
insert into emp values(4,'zhangsan',to_date('1998-1-1 12:30:00','yyyy-mm-dd hh24:mi:ss'),4500.0,1);
insert into emp values(5,'lisi',to_date('1900-4-1 14:07:20','yyyy-mm-dd hh24:mi:ss'),55000.0,null);

drop table dept;
create table dept(
       did number,
       dname varchar2(225)
)
insert into dept values(1,'Administration');
insert into dept values(2,'Operation and maintenance');
insert into dept values(3,'Personnel matters');
insert into dept values(4,'Development');

-- String Processing Functions
--  Convert Uppercase upper
select upper('aaa') from emp;

--  Convert lowercase lower
select lower('AAA') from emp;

--  title case initcap
select initcap('hello word!!!') from emp;

--  substr substr
select substr('helloword!!!',0,5) from emp;

--  String substitution replace
select replace('helloword!!!','!','?') from emp;

--  String Length length
select length('helloword!!!') from emp;

--   Specify where characters appear instr
select instr('hellowword!!!','h') from emp;


--  Numeric Functions
--  Rounding round
select round(15.66,-2) from emp;    -- 0
select round(15.66,-1) from emp;    -- 20
select round(15.66,0) from emp;     -- 16
select round(15.66,1) from emp;     -- 15.7
select round(15.66,2) from emp;     -- 15.66

-- Intercept trunc
select trunc(15.66,-2) from emp;    -- 0
select trunc(15.66,-1) from emp;    -- 10
select trunc(15.66,0) from emp;     -- 15
select trunc(15.66,1) from emp;     -- 15.6
select trunc(15.66,2) from emp;     -- 15.66

--  Remainder mod
select mod(20,3) from emp;


--  Date function
--  Query System Time sysdate
select sysdate from emp;

--  Query how old you are this year
select ename,(sysdate - birthday)/365 from emp;

--  How many months have been queried  months_between
select ename,months_between(sysdate,birthday) from emp;

--  Query date after three months add_months
select ename,birthday,add_months(birthday,3) from emp;

--  Conversion function 
--  Converts the current time to a specified format string  to_char  fm/Zero removal
select to_char(sysdate,'yyyyfm/mm/dd hh24:mi:ss') from emp;
--  Converts a number to a string
select to_char(99) from emp;

-- Show adult month day
select to_char(sysdate,'yyyy') || 'year' || to_char(sysdate,'mm') || 'month' || to_char(sysdate,'dd') || 'day' from emp;

--  Convert string to date type to_date
select to_date('1999-10-1 15:24:31','yyyy/mm/dd hh24:mi:ss') from emp;

--  Converts a string to a number to_number
select to_number('99') from emp;

--  General function
--  Null value processing function nvl /If is null,Returns the specified character
select nvl(ename,'nothing') from emp;

--  Null value processing function nv2 /If buweinull,Returns the specified character 1/by null,Returns the specified character 2
select nvl2(ename,'Yes','nothing') from emp;


--  Conditional expression
--  Converts the specified content to'xxx' decode
select decode(ename,'admin','Administrators','Other') from emp;
--  Other ways case when then end
select case ename when 'admin' then 'Administrators' else 'Other' end from emp;

--  Query the three highest paid people
--  rownum: Add a sequence number to the result set
select rownum,e.* from (select * from emp order by salary desc) e where rownum <= 3;
select * from (select rownum r,e1.* from (select * from emp order by salary desc) e1 ) e2 
where e2.r > 3 and e2.r <= 6;
/*
Paging Formula:
pageNum = 1
pageSize = 2
select * from (select rownum r,e1.* from (select * from Table order by sort field desc) e1) E2 
where r > (pageNum - 1) * pageSize and r <= pageNum * pageSize;
*/


--  Set function
--  intersection intersect/Amount to and
select * from emp where ename = 'admin'
intersect
select * from emp where salary = 4000.0;

--  Union union/Amount to or
select * from emp where ename = 'admin'
union
select * from emp where salary = 5000;

--  Difference set minus/First result set minus second result set
select * from emp where ename = 'admin'
minus
select * from emp where salary = 4000;


--  exists/If there is a return true/Conversely false
--  Find out which one has no department information
select * from emp where exists(select * from dept where emp.did = dept.did)
select * from emp where not exists(select * from dept where emp.did = dept.did)


--  Sequence equivalent mysql Autogrowth Series in,Operations failures will also grow
/*
Complete Writing:
create sequence Sequence Name
start with 5  -- Cluster 5 Begins
increment by 2 -- 2 per increase
maxvalue 20 -- Maximum 20
cycle -- Circulable
cache 5 -- Cache 5
*/
-- Create Sequence
create sequence emp_seq;

--  Query Sequence
select emp_seq.nextval from emp;

--  Delete Sequence
drop sequence emp_seq;


-- Indexes
/*
The purpose is to improve retrieval speed
 Syntax: create index name on table name (column name)
Principle: Big data creates indexes
      Create indexes for frequently used columns
      Index should not exceed 4 levels
      Primary key with index
*/
--  Create Index
create index index_ename on emp(ename)

--  Delete Index
drop index index_ename;