Oracle introduction 2

Previous: Oracle introduction 1

Learning video: https://www.bilibili.com/video/BV1tJ411r7EC?p=15

Arithmetic operator: + - */

Logical operators: and or not

Comparison operators: '=', '<', '>', '< =', '> =', '< >', '! ='. Note that "=" means equal rather than assignment, and the last two means not equal.

Find column:

-- Alias column,If the column name has spaces, enclose them in double quotes
select name Name,salary*15 "year    salary" from staff where name='Zhang San';
--If the values of the columns are to be concatenated, use the||
select name||'-'||salary*15 "Annual salary of employees" from staff where name='Zhang San';
--Constant column
select salary,'Li Ziwei' "Full name" from staff;

Null operation: if there is a null value participating in the operation, return null forever. To avoid this situation, use nvl(param1,param2) function. If param1 is null, return value is param2. Otherwise, param1.

select salary+nvl(bonus,0) all_salary from staff;

Row weight: no matter multiple columns or single column, you only need to add the "distinct" keyword at the front of the column to achieve row weight. Multi column row weight is to see whether the whole combination is repeated, not whether one of the columns is repeated.

select distinct salary,name from staff;

where: condition filtering, you can add limit conditions such as 1 = 1 is always true, 1! = 1 is always false. String comparisons are case sensitive.

-- To judge empty is null
select salary,name from staff where salary>80000 or bonus is null;
-- Eternal condition
select salary,name from staff where 1=1;
-- Permanent false condition
select salary,name from staff where 1!=1;
-- String is strictly case sensitive,In single quotes
select salary,name from staff where name='Popo';

Like in fuzzy query: the wildcard "_" represents any character and the wildcard "%" is a string of any length. These two wildcards are often used in fuzzy query using "like".

select salary,name from staff where name like 'Zhang%';
select salary,name from staff where name like 'Zhang_';
select salary,name from staff where name like '%o_';

between...and...: equivalent to "value a > = value b and value a < = value c", remember to include equal to. If you need data that is not in this range, just add a not keyword before between.

select salary,name from staff where salary between 80000 and 90000;
select salary,name from staff where salary>=80000 and salary<=90000;
select salary,name from staff where not salary between 80000 and 90000;

In of fuzzy query: it means within a certain range, but the values within this range can be expressed accurately. in(4,5,6) means that the fields can be either 4 or 5 or 6. If it is not in this range, it is not in(...).

select salary,name from staff where salary in (40000,80000);
select salary,name from staff where salary not in (40000,80000);

Judge null: use "is null" instead of = null, and "is not null" instead of "is not null".

-- For judgment is null´╝îNon empty use is not null
select salary,name from staff where bonus is null;
select salary,name from staff where bonus is not null;

Order by sorting: the sorting statement is always placed at the end. By default, it is asc ascending (small - > large), desc descending (Large - > small). For multi column sorting, you only need to write order by once, such as "order by column 1 desc,order by column 2 asc, and column 3 desc".

select salary,name from staff order by salary;
select salary,name from staff order by salary asc;
select salary,name from staff order by salary desc;
-- Do not write descending ascending, default ascending
select salary,name from staff order by salary ,name ;
select salary,name from staff order by salary desc,name desc;

Tags: Oracle

Posted on Sun, 03 May 2020 07:43:06 -0400 by shelluk