Mainly based on w3school sql (because it is the basis of sql), supplemented by courseware and exercises
To prevent you from dying first because you read the wrong address
https://www.w3school.com.cn/s.asp

select where

Query statements, the most commonly used

select Listing from Table name where condition

The asterisk is equivalent to querying all tables, but it is only used in practice. It will not be used in practical application. In multi table query, it represents the contents of all tables. If only the contents of one table are required, the table name is required asterisk
When judging conditions, we use < >, but because we don't write in the database, we use java and other tools, so we still use! =, Specific situation and specific analysis

, judge

, which is generally used to distinguish column names, table names, etc
and or is used in condition judgment
Null is very special. Only is null is supported
in for (list)
like is used to query strings
%Represents matching any number of characters
_ Indicates a match of one character

De duplication distinct, group by

Using group by
Don't use distinct. It seems to be a problem of efficiency

Sort order by, over

order by can be used
During the exercise, due to the existence of single line function, group by needs to be added. However, for easier display, you can choose to follow the single line function with over ()
as

select department_id, sum(salary) over(partition by Group content order by Sort content) from department

It is not necessary for sum and order by
asc default ascending order
desc descending order
If you need ranking, there will also be row_number(),rank,dense_rank(),
rank() is the rank 1 1 3 4 with the same rank
dense_rank() does not empty rank 1 1 2 4 of the same rank
row_number() ranks 1 2 3 4

insert

insert into Table name (column...) values(value...)

insert all 
into Table 1(column...)
into Table 2(column...)
...
select column from surface where ...

For Chinese questions, please turn up varchar2 or varchar. The specific size depends on the character set, details, baidu, etc

update

Remember the conditions. Don't accidentally change everything

update Table name set Listing = New value,... where condition

alter

alter table Table name add Column name type
alter table Table name alter column Column name type
alter table Table name drop column Listing

alter table Table name add constraint Custom name primary key(column,...)
alter table Table name add primary key(column)
alter table Table name add check(Custom condition)

alter table Table name drop constraint Custom name
alter table Table name drop primary key(column)
alter table Table name drop check(Custom condition)

drop

drop table Table name
drop index Table name

Be careful when deleting
truncate is also deleted, but it will be submitted automatically

delete

delete from Table name where condition

If you do not write conditions, all the data in the table will be deleted, but the header still exists

Pinch head

There are two methods: one is to use rownum to forcibly select, and the other is to query first, and then external query the upper table with conditions

	select * from department where rownum<4;

	select * from (
		select count(*) cnt from department))
		 where cnt < 3

rename

Column name, table name, etc. can be added directly after it, with or without as

select count(*) cnt from department
select count(*) as cnt from department

select * from (select count(*) cnt from department) t

join

Please look at other tutorials in detail. This is very common and around when doing exercises. Various connections mainly focus on inline. It's better to watch a teaching video

union，minus，intersect

union Union
union all with union
minus difference set
intersect intersection
Do not understand Union, difference set, intersection mathematics

create and constraints

create table Table name (
Name type,
...
)

create index name on Table name (column name)

oracle has one library, multiple tables, multiple permissions and multiple users
Building tables depends on your position. Some positions are not related to building tables and databases, which should be handed over to special people, depending on the situation
If you contact, you can build a table. It's nothing special
check custom constraints

dual

select content from dual

select sysdate from dual

Display the results. There can only be one result

date

sysdate gets the current time
months_ Between (date, date) gets the number of months in the interval. Days, hours, minutes and seconds will be displayed as floating-point calculations
add_ Months (date, number) add a few months
next_ When is the next day of the week
last_ Day is the last day of the month

Numerical conversion

to_char
to_date
to_number

function

lower to lowercase
upper to uppercase
initcap word initial capital
concat string connection can only be connected to two at a time. Multiple connections need to be nested. String connection is more useful||
substr (string, start position, length)
Length gets the length of the string
Where does instr (string, character) appear
lpad (string, length, symbol) left complement completes the string to the specified length with a symbol
rpad
trim (character from string) deletes the specified character from the left to the right
trim (string) removes spaces on both sides
round (target, digits) is an approximate value for numbers, and the digits are reserved. For the approximate value of date, see the month
trunc near zero value
Coalesce (value 1, value 2,...) gets the first value that is not null unless it is all null
sum
avg
count(*) = count(1)
Count (column) is not empty
max
min
Note the use of group by. Group by needs to list all the query values
Subtotal rollup
Subtotal of cube permutation and combination

case and decode

It just depends.

case  when expression then Value 1 
when expression then Value 2
...
else
 value n
end

decode(Value, value 1, result 1, value 2, result 2,...)

Transaction lock encountered

with

It may be to create a temporary table, which can speed up when you reference the temporary table many times

with Name 1 as( query)，
Name 2 as,...

Formal inquiry

recursive query

select content
from surface
start with Initial value
connect by Connection conditions

prior Represents the front table, not the back table
prior employee_id = manager_id

Other functions, such as materialized view, session window, vpd, etc