SQL> show user USER 为\
SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID GAO 25
58.USER
返回当前用户的名字 SQL> select user from dual; USER GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true SQL> select userenv('isdba') from dual; USEREN FALSE
SQL> select userenv('isdba') from dual; USEREN TRUE
SESSION 返回会话标志
SQL> select userenv('sessionid') from dual; USERENV('SESSIONID') 152 ENTRYID 返回会话人口标志
SQL> select userenv('entryid') from dual; USERENV('ENTRYID') 0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual; USERENV('INSTANCE') 1
LANGUAGE 返回当前环境变量
SQL> select userenv('language') from dual; USERENV('LANGUAGE')
SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual; USERENV('LANG') ZHS TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual; USERENV('TERMINA GAO VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual; VSIZE(USER) USER 6 SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值 SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 语句已处理。
SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('zhu',5555.55); SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3; AVG(DISTINCTSAL) 3333.33
SQL> select avg(all sal) from gao.table3; AVG(ALLSAL) 2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp; MAX(DISTINCTSAL) 5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3; MIN(ALLSAL) 1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL)
1182.5032
SQL> select stddev(distinct sal) from scott.emp; STDDEV(DISTINCTSAL) 1229.951
64.VARIANCE(DISTINCT|ALL) 求协方差
SQL> select variance(sal) from scott.emp; VARIANCE(SAL) 1398313.9
65.GROUP BY 主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL) 10 3 8750 20 5 10875 30 6 9400
66.HAVING 对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having nt(*)>=5;
DEPTNO COUNT(*) SUM(SAL) 20 5 10875 30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by tno ;