Oracle (day 4)

Time:2021-4-29
1、 Distinct
Syntax format: select distinct column name from table name
2、 Statistical function
avg(x)
count(1) count(*)
max(x)
min(x)
sum(x)
StdDev (x) to find the standard deviation of the value of X in a group of rows
Variance (x) find the variance of x value in a group of rows
Round and TRUNC:
select round(avg(sal),4) from emp; Round off
select trunc(avg(sal),4) from emp; No matter how many digits after the exact decimal point, most of them do not enter 1
3
1. Group query (keyword): group by
2. Filtering (keyword): having
  select deptno,max(sal) from emp group by deptno;
When grouping statements are used, the columns appearing in the select statement must appear in the group by
  select deptno job,avg(sal) from emp group by deptno,job;
Having statement having statement must have group by statement, and vice versa.
  select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000;
3. Order by
1) , order by clause is always last in the whole select statement.
2) The, ASC table is in ascending order, generally default and omitted, and the desc table is in descending order.
3) Null is the largest.
4) , followed by multiple columns to sort the columns in turn.
4、 Connection query
1. Inner join: inner join on, where and
2. Outer join: left join, right join and full join
 
 
  select e. *,dname ,loc from emp e
  full join dept d on e.deptno =d.deptno
  where e.deptno = 10;
 
5、 Exercises:
 
 
External connection exercises:
 

上面练习用到的四个表。