当前位置：网站首页>Common sense and use of Oracle Database

Common sense and use of Oracle Database

2022-04-22 08:34:00 【Bald little strong 007】

Simple sql

SELECT SYSDATE FROM dual;

CREATE TABLE emp (
  id NUMBER(4),
  name VARCHAR2(20) not null ,
  gender char(10)default 'M',
  birth date,
  sal number(6,2) default 5000,
  job VARCHAR2(30),
  deptno number(2)
);

DESC EMP_table;

drop table emp;

rename emp to emp_table;

ALTER TABLE emp_table add(
 hide_date date,
 accuent number(12)
);

ALTER table emp_table drop(accuent) ;

AlTer table emp_table MODIFY(job varchar2(100));

INSERT INTO EMP_TABLE(
 id,
 name,
 birth
)
VALUES(
 1,
 ' Zhang San ',
 TO_DATE('2021-10-14','YYYY-MM-DD')
);

Basic data type

char： The maximum value is 2000 Bytes , Keep at most 2000 English characters ,1000 The Chinese characters （GBK）.

varchar: Maximum value 4000 Bytes , Save at most 4000 English characters ,2000 The Chinese characters (GBK).
char You don't specify length , The default value is 1,varchar2 Length... Must be specified .

long: varchar2 An extended version of , Store variable length strings , Reach at most 2GB String data for . however long There are many restrictions , Each watch can only have one long Type column ; Cannot be used as primary key , Cannot index , Cannot appear in query criteria …

clob: Store fixed or variable length strings , Do more to achieve 4GB String data for .

date: The most common date type , Used to save date and time ,date Indicates that the date range can be... BC 4712 year 1 month 1 Day to A.D 9999 year 12 month 31 Japan ,date The type is stored in the database and fixed as 7 Bytes , The format is ：

The first 1 byte ： century + 100
The first 2 byte ： year
The first 3 byte ： month
The first 4 byte ： God
The first 5 byte ： Hours + 1
The first 6 byte ： branch + 1
The first 7 byte ： second + 1

TIMESTAMP: The date type , And DATE The difference is that it can not only be saved to date and time , It can also save fractional seconds , The highest accuracy can be achieved ns( nanosecond ). Database internal use 7 perhaps 11 Byte store , Accuracy of 0, use 7 Byte storage , And DATE Function the same , Accuracy greater than 0 Then use 11 Bytes to store .
The format is ：

The first 1 Bytes to 7 byte ： And date identical .
The first 8-11 byte ： nanosecond , use 4 Byte storage , The internal operation type is shaping .

function

|| Symbols can connect strings ：

select name || ',' || gender from emp;

LENGTH（char） Used to return the length of characters , If the character class varchar2, Returns the actual length of the character , If the character type is char, The length should also include the space to be filled in .

select LENGTH(name) from emp;

upper(char) Used to convert characters to uppercase .
lower(char) Used to convert a string to lowercase .
initcap(char) Used to capitalize the first letter of each word in the string .
If the input parameter value is NULL value , Still back NULL value .

dual Pseudo table in database ;

select upper('hello'),lower('HELLO'),initcap('hello world') from dual;

Remove the specified string on both sides of the string , Used to intercept strings

TRIM('c2' FROM 'c1') from c1 Cut back and forth c2.
LTRIM('c1','c2') from c1 Left side （LEFT） Cut off c2;
RTRIM('c1','c2'), from c1 To the right of （Right） Cut off c2
without c2 Just remove the spaces

select trim('e' from 'eeliteee') from dual;

select ltrim('eeeeliteee','e') from dual;

Complement function ：

LPAD(char1 ,n,char2) Left part function .
RPAD(char1,n,char2) Right complement function .

select LPAD(sal,6,'$') from emp;

SUBSTR(char,m,n) Used to get the substring of the string , return char in , from m Bit start fetch n Characters .
If m=0, Start with the first character , If m It's a negative number , Start from the tail . If not set n, perhaps n Is longer than char The length of , Then take it to the end of the string .
The subscripts in the database are all from 1 Start .

select substr('thinking in java',-7,2) from dual;

INSTR(char1,char2,m,n) see char2 stay char1 In the middle ,m To find where to start , If not written, the default value is 1, n For the first time , Do not write. The default value is 1.

select instr('thinking in java','in',4,2) from dual;

TRUNC function , And ROUND The meaning of the function is the same , But it's intercepting numbers .

select TRUNC(22.123,2) from dual;

MOD(m,n) return m Divide n The remainder of ,n by 0 Then return directly m.

select MOD(1000,9) from dual;

CEIL(n) and FLOOR(n) These two functions, as the name suggests , One is the ceiling , Is to take greater than or equal to n The minimum integer value of . One is the floor , Is to take less than or equal to n The maximum integer value of .

SELECT CEIL(4.5),FLOOR(4.5)  FROM dual;

SYSDATE It's essentially a oracle An internal function , Returns the current system time , Accurate to seconds .

SYSTIMESTAMP Internal function , Returns the current system date and time , Accurate to milliseconds ( Time stamp )

TO_DATE() The given string can be formatted according to the specified date , Convert to DATE Type values .

YY	Two digit year
YYYY	Four digit year
MM	Two digit months
MON	Simplified month
MONTH	The whole month
DD	Two digit days
DY	Abbreviation for day of the week
DAY	Day of the week
HH24	24 An hour system
HH12	12 An hour system
MI	Show minutes
SS	Show seconds

Where the date format string is not in English , Other characters of the symbol need to be enclosed in double quotation marks .

select TO_DATE('2021-10-01 13:49:57','YYYY-MM-DD HH24-MI-SS') FROM dual;
--  With Chinese characters  
SELECT TO_DATE('2021 year 11 month 15 Japan  13 when 14 branch 15 second ','YYYY" year "MM" month "DD" Japan " HH24" when "MI" branch "SS" second "') FROM dual;

TO_CHAR Can be DATE Convert to string according to the given date format .

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual;

Date types can be calculated , For a date, adding or subtracting a number is equivalent to adding or subtracting days . Subtract two dates , The difference is the number of days .

select SYSDATE+1 from dual;

LAST_DAY(date): Return date date On the last day of the month , Some business logic is calculated according to natural months , Or it's useful to arrange periodic activities at the end of the month .

select LAST_DAY(SYSDATE) from dual;

ADD_MONTHS(date,1) Return date date Add after i The value of the date after months .

Parameters i It can be any number , Most of the time, take a positive integer
If i Is the decimal , Will be intercepted and participate in the operation .
If i It's a negative number , What you get is minus i Date value after months .

select ADD_MONTHS(SYSDATE,3) from dual;

MONTHS_BETWEEN(date1,date2): Calculation date1 and date2 How many months are there between two date values .
The actual operation is date1-date2, If date2 Than date1 It's late , You get a negative value , Unless the two dates are a full month , Otherwise, you will get the result with two decimal places , For example, calculation 2009 year 9 month 1 Japan To 2009 year 10 month 10 Japan How many months between , You'll get 1.29 Months .

select MONTHS_BETWEEN
       (TO_DATE('02-02-1995','MM-DD-YYYY'),
        TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
  FROM DUAL;

NEXT_DATE(date,char): Returns the week of the specified day of the week from the second day of a given date .
In the Chinese environment , Use it directly Wednesday This form , In English , Need to use WEDNESDAY This English day of the week , To avoid trouble , have access to 1-7 To mean Sunday - Saturday

--  What date is next Wednesday 
  select NEXT_DAY(SYSDATE,4) from dual;

LEAST(expr1,expr2,expr3),GREATEST(expr1,expr2,expr3) It's called the comparison function , There can be multiple parameter values , The return result is the maximum or minimum value in the parameter list . The types of parameters must be consistent , Before the comparison , After the second parameter in the parameter list , Will be implicitly converted to the data type of the first parameter , All if can be converted , Continue to compare , If it cannot be converted, an error will be reported .

EXTRACT(date FROM datetime) from datetime Extract parameters from date Specified data , For example, the year of extraction , month , Japan .date The data type can only be extracted to mm / DD / yyyy , Extract less than minutes and seconds , But the timestamp type can .

--  This year is that year 
  SELECT EXTRACT(YEAR FROM SYSDATE) FROM  dual;

Null valued function ：

nvl(arg1,arg2), When arg1 by null Back when arg2 Value , Otherwise return to arg1 Oneself , The meaning of this function is to null Replace the value with non null value .

select nvl(null,0) from dual;

nvl2(arg1,arg2,arg3) When arg1 Not for null Return parameters when arg2, if null, Then return to arg3

sql Inquire about

We can add aliases to complex column names , If the alias uses double quotation marks , Then the alias can be case sensitive and include spaces .

select name, sal+1 as "s al" from emp;

ANY and ALL Conditions ：ALL and ANY Not to be used alone , It needs to be combined with the single line comparison operator >,>=,<,<= Use it together .

> any: Greater than the minimum
<any: Less than maximum
>all: Greater than the maximum
<all: Less than the minimum

HAVING Words and sentences must follow GROUP BY After Clause , The function is to add filter conditions to filter GROUP BY The grouping , It can remove groups that do not meet the conditions .HAVING Clause can use aggregate function as filter condition

selec min(sal),deptno sal from emp group by deptno having min(sal)>500;

... LEFT OUTER JOIN ... ON ... The left outer join
... RIGHT OUTER JOIN ... ON ... Right connection
... FULL OUTER JOIN ... ON ... Full outer join

--  The left outer join , There are  +  Number , Over there  null
SELECT 
e.empno,e.ename,e.sal,e.job,
d.deptno,d.dname,d.loc
from emp e,dept d
WHERE
e.deptno=d.deptno(+)

stay DDL The subquery is used in the statement , Create tables directly ：

CREATE TABLE myemp 
AS
SELECT 
e.empno,e.ename,e.sal,e.job,
d.deptno,d.dname,d.loc
from emp e,dept d
WHERE
e.deptno=d.deptno(+)

Subquery

Single row single column subquery —— Return single line data
Multi row single column subquery —— Returns multiple lines of data
Multi row and multi column subquery —— Return multi row and multi column data

EXISTS keyword , Whether the following sub query has data , If there is data, return true, If there is no data, return false

SELECT d.deptno,d.dname,d.loc FROM dept d
WHERE EXISTS(
SELECT * FROM emp e WHERE e.deptno=d.deptno
)

HAVING Subquery ：

SELECT MIN(sal), deptno from emp
GROUP BY deptno 
HAVING MIN(sal) > (
SELECT MIN(sal) from emp WHERE deptno=30
)

FROM Subquery ： stay FROM Using a subquery in a clause usually treats the result of the subquery as a table , Second query based on the query result .

chestnuts ： See who's paid more than the average in their department .

SELECT e.ename,e,sal,e.deptno FROM emp e, 
(
SELECT  AVG(sal) avg_sal ,deptno FROM emp GROUP BY deptno
) t
WHERE e.deptno=t.deptno
and e.sal>t.avg_sal

SELECT Some subqueries ： Put the subquery in SELECT Clause section , It can be considered as another manifestation of external connection , More flexible use ：

SELECT e.ename, e.sal (
SELECT d.deptno FROM dept d WHERE d.deptno=e.depetno
) dname FROM emp e;

Pagination

Paging in different databases , SQL Statements are different .
ORACLE A pseudo column is provided in ：ROWNUM, This field does not exist in any table , But each table can query this field , The value of this field is the line number of each record in the result set . ROWNUM Field values are dynamically generated , With the query process , Just find a record ,ROWNUM The line number will be generated for this record , from 1 Start each increment 1.

SELECT ROWNUM,name FROM emp;

SELECT *
FROM (
  SELECT ROWNUM rn,t.* 
  FROM ( 
     SELECT empno,ename,sal,deptno
     FROM emp
     ORDER BY sal DESC )  t
     WHERE ROWNUM <= 10)
     WHERE rn >=6

`DECODE` function

DECODE(expr,search1,result1...) function , For comparison expr Value , If it matches which one search Conditions , Just return to the right result result . There can be multiple groups search and result Correspondence of , If there is no one search On condition matching , Returns the last default Value .default The value of is optional , If not provided default Parameter values , When there is no match , Will return NULL.

SELECT ename,job,sal,
DECODE(
job,
'MANAGER',sal*2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal) bonus
FROM emp

and DECODE Functions with similar functions CASE sentence , The implementation is similar to if-else The operation of ：

SELECT ename,job,sal,
CASE job WHEN 'MANAGER' THEN sal*1.2
         WHEN 'ANALYST' THEN sal*1.1
         WHEN 'SALESMAN' THEN sal*1.05
         ELSE sal END
    bonus
    FROM emp;

DECODE Can be used in grouping and sorting .

Sorting function

The sort function allows the result set to be grouped by a specified field , Sort by the specified field within the group , The function then generates a line number for each group .

ROW_NUMBER()： Generate consecutive and unique numbers in the group

SELECT ename,deptno,sal,
ROW_NUMBER()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

RANK function ： Generate discontinuous and non unique numbers in the group .

SELECT ename,deptno,sal
RANK()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

DENSE_RANK function ： Generate consecutive but not unique numbers in the group .

SELECT ename,deptno,sal
DENSE_RANK()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

Set operations

UNION and UNION ALL, In order to merge multiple SELECT Result of statement , You can use set operators , Implement the collection and , hand over , Bad .

Set operators include UNIION,UNION ALL,INTERSECT and MINUS, Multiple collection operations SELECT The number of columns in the statement and the type of data must match ,ORDER BY Clause can only be placed in the last query statement , The syntax operation is as follows ：

SELECT statement1 
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

Advanced grouping functions

ROLLUP(a,b,c....) function ：

GROUP BY ROLLUP(a,b,c)
Equate to
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
Full table

CUBE() function ： The grouping strategy is to group each combination of parameters .

GROUP BY CUBE(a,b,c)
Equate to
a,b,c
a,b
a,c
b,c
a
b
c
Full table

The number of combinations equals 2 Several times of the parameter of .

GROUPING SETS() This function allows you to specify your own grouping policy , Then group the statistical results together . Each parameter of the function is a grouping method .

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY 
GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id

View

Views are also called virtual tables , Virtual tables , Is a logical representation of a set of data , The view corresponds to a SELECT sentence , The name given to the result set , The name of the view , The view itself does not contain any data , It contains only one query statement mapped to the base table , When the data of the base table changes , The view also changes . If the field contains a function or expression , Then this field must specify an alias

CREATE OR REPLACE VIEW v_emp_1 
AS 
SELECT * FROM emp;

Simple view ： SELECT Statement is based on a single table , And does not contain the operation of any function , Expression or grouping function , It's called a simple view .

Complex view ： SELECT The statement is also based on a single table , But it contains single line functions , expression , Grouping function or GROUP BY Clause .

Connection view ： SELECT Statement based on multiple tables .

Simple views can be used DML SQL Statement operation （ Additions and deletions ）, Complex things don't work ！

You can add check options to the view , To ensure that the view is updated DML The basic table data will not be polluted during operation . WITH CHECK OPTION When the check option is added to the view , The view requires that the data in the view be modified DML After the operation , The view must be visible to the record , Otherwise, operation is not allowed .

Add a read-only option to the view ： When a view adds a read-only option , This view cannot be DML operation .WITH READ ONLY

Delete view ：

DROP  VIEW v_emp_10;

Deleting a view does not affect the data in the base table .

The data dictionary

View data dictionary , It is helpful to understand the database objects that have been created .
View related digital dictionary

USER_OBJECTS
USER_VIEWS
USER_UPDATE_COLUMNS

--  In database dictionary  USER_OBJECTS  Query the names of all views in 
SELECT object_name FROM user_objects WHERE object_type = 'VIEW'

Sequence

Sequence is also one of the database objects , The function is to generate a series of numbers according to the specified rules , It is generally used to provide data values for each data primary key of the table .

CREATE SEQUENCE sequence_name
[START WITH i] [INCREMENT BY j]
[MAXVALUE m | NPMAXVALUE]
[MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE p | NOCACHE ]
sequence_name The sequence of
The first sequence value of a sequence is i, The step is j
If j It's a certificate , Indicates incremental , If it's a negative number , It means decreasing
The maximum value generated by sequence class is m , The minimum is n
If no optional parameters are set , The sequence value is 1 The step value is also 1
CYCLE Indicates that after increasing to the maximum value or decreasing to the minimum value , Whether to reuse the sequence , If it decreases and has a maximum , Start with the maximum . If it increases and has a minimum value , Start with the minimum . If not from START WITH Start with the specified value . The default is NOCYCLE.
CACHE Used to specify prefetch first p Data in the sequence , To improve the generation efficiency of sequence values , The default is 20

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

The sequence supports two pseudo Columns ：
NEXTVAL: Get the next number in the sequence , The sequence will be based on the number generated at the end of the sequence plus step in to get .NEXTVAL Will cause the sequence to step , The sequence cannot be rolled back .

CURRVAL： Get the last generated number of the sequence , It should be noted that , The newly created sequence must be in use NEXTVAL After generating a number, you can use CURRVAL.

select seq_emp_id.NEXTVAL from dual;
select seq_emp_id.CURRVAL from dual;

Delete sequence ：

DROP SEQUENCE emp_seq

Indexes

Syntax for index creation ：
like Fuzzy queries do not use indexes

stay EMP Tabular ENAME Index columns
CREATE INDEX idx_emp_ename ON emp(ename)
Composite index
CREATE INDEX idx_emp_job_sal ON emp(job,sal)
Add index to function
CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename))

Rebuild index ：