Oracle之单表查询及常用函数

1.语法：
　　select 字段列表
　　from 表名
　　[where 查询条件]
　　[group by 分组]
　　[having 分组条件]
　　[order by 排序]

select * 代表查询所有的字段
    select id as "编号",sname 学生姓名,age "【年龄】"  --as 之后是别名 也可以直接省略
    select t.*
    from t_student t -- 给表取别名

    where classid is null -- 空判断
    where  age not in (20,23,...) --范围判断
    where age between 20 and 25   --区间判断between'A' and 'Z'

    where  sname not like '%江%'   --sname like '江%'  -- '江%' 以江开头 '%江' 以江结尾 '%江%' 包含江
                     --模糊查询 _一个下划线表示一个位置
    order by  age -- asc 升序 desc降序 默认升序
    select distinct age,sex from t_student   --distinct 去掉重复记录

统计函数：
　　count:统计条数

select count(*) from t_student
    select count(id) from t_student 
    select count(classid) from t_student 
    -- 统计的是该列中非空的记录的个数
    select count(1) from t_student;
    select id,sname,age,sex,classid,1,2,3 from t_student
     sum:求和
    select sum(age) from t_student;
     min:取最小值
    select min(age) from t_student;
     max:取最大值
    select max(age) from t_student;
     avg:取平均值
    select avg(age) from t_student;
     
     select sum(age),min(age),max(age),avg(age) from t_student;

count(1)与count(*)比较：

　　如果你的数据表没有主键，那么count(1)比count(*)快
　　如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
　　如果你的表只有一个字段的话那count(*)就是最快的啦
　　count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
　　如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
　　因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

count详解：
　　count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
　　distinct 列名,得到的结果将是除去值为null和重复数据后的结果

　　group by :分组函数
　　　　select age,sex
　　　　from t_student
　　　　group by age,sex
　　--注意：分组函数中的字段列表只能出现分组的字段和统计函数
　　-- 分组函数在没有统计函数使用的时候作用和 distinct 是一样的
　　　　select sex,count(sex)
　　　　　from t_student
　　　　group by sex
　　--分组函数【聚合函数】在没有和 group by 一块使用的时候统计的是查询的所有的数据
　　--如果和 group by 一块使用的化，那么统计的是分组后的各组数据

select classid,sex,count(1)
    from t_student
    group by classid,sex
    having count(1) > 1 -- 分组后的条件

    select classid,sex,count(1)
    from t_student
    where age > 20 -- 分组之前加条件
    group by classid,sex

where 和 having 的区别
　　where 只能跟在 from 后面表示对查询的数据源过滤
　　having 只能出现在 group by 后面，对分组后的数据进行过滤，

常用函数：
　　concat:连接函数
　　select concat(id,sname)，length(sname) from t_student
　　日期函数：
　　　　字符串转date: to_date
　　　　update t_student set birth=to_date('1990-01-01','yyyy-mm-dd')
　　　　date转字符串: to_char

select sysdate
       ,to_char(sysdate,'yyyy-mm-dd hh:mi:ss')--在数据库中是HH24 mi
       ,to_char(sysdate,'yyyy-mm-dd')
       ,to_char(sysdate,'yyyy-mm')
       ,to_char(sysdate,'yyyy')
    from dual;

months_between(sysdate,date);--两者时间的月份数

add_months:在当前时间的基础上增加月份数
select add_months(sysdate,12) from dual;

last_day():返回指定日期的当月的最后一天
select last_day(sysdate) from dual;

extract:截取日期指定部分的内容
select extract(DAY from sysdate) from dual; --dual是一个系统自带的虚表

nvl(column,value);如果查询的字段为null，就用默认值填充
select id,sname,sex,nvl(sex,'哈哈') from t_student

    decode:类似于Java中的if语句
    select
    id,sname,sex
        ,decode(sex,1,'男') -- if(sex == 1){男}
        ,decode(sex,1,'男',2,'女') -- if(){}else if(){}
        ,decode(sex,1,'男',2,'女','不详')--if(){}else if(){}else{}
    from t_student

rowid:行id，数据存储的位置，唯一
rownum:行号,系统自动维护的，从1开始自增，有1才有2
select t.*,rownum from t_student t

查询出学生表中前5条的学生记录
select t.*,rownum from t_student t where rownum <=5

查询出学生表中第5条到第10条的记录
select t.*,rownum from t_student t where rownum >=5 and rownum <=10 ---这是错误的写法

---分页查询的实现方式(在Oracle中要这么实现)
    select t1.*,rownum
    from 
    (select t.*,rownum num from t_student t) t1 --先查询所有的数据和行号
    where t1.num >= 5 and t1.num <=10           --再从中选出想要的部分

    select t2.*,rownum
    from 
    (select t1.* ,rownum num from t_student t1 where rownum <=10) t2 --先取上限的数据及行号
    where t2.num >=5   --再取下限的数据

case的使用
查询出学生表中年龄在【20岁以下】【21-25】【26以上】分别有多少人

select t.*,
            case when age <= 20 then 1 else 0 end "21以下"
            ,case when age >20 and age <26 then 1 else 0 end "21-25"
            ,case when age >= 26 then 1 else 0 end "26以上"
        from t_student t
        
        select 
            sum(case when age <= 20 then 1 else 0 end ) "21以下"
            ,sum(case when age >20 and age <26 then 1 else 0 end) "21-25"
            ,sum(case when age >= 26 then 1 else 0 end )"26以上"
        from t_student t
        
        select 
            count(case when age <= 20 then 1 else null end ) "21以下"
            ,count(case when age >20 and age <26 then 1 else null end) "21-25"
            ,count(case when age >= 26 then 1 else null end )"26以上"
        from t_student t

函数	说明
ASCII(x)	返回字符x的ASCII码。
CONCAT(x,y)	连接字符串x和y。
INSTR(x, str [,start] [,n)	在x中查找str，可以指定从start开始，也可以指定从第n次开始。
LENGTH(x)	返回x的长度。
LOWER(x)	x转换为小写。
UPPER(x)	x转换为大写。
LTRIM(x[,trim_str])	把x的左边截去trim_str字符串，缺省截去空格。
RTRIM(x[,trim_str])	把x的右边截去trim_str字符串，缺省截去空格。
TRIM([trim_str FROM] x)	把x的两边截去trim_str字符串，缺省截去空格。
REPLACE(x,old,new)	在x中查找old，并替换为new。
SUBSTR(x,start[,length])	返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

ABS(x)	x绝对值	ABS(-3)=3
ACOS(x)	x的反余弦	ACOS(1)=0
COS(x)	余弦	COS(1)=1.57079633
CEIL(x)	大于或等于x的最小值	CEIL(5.4)=6
FLOOR(x)	小于或等于x的最大值	FLOOR(5.8)=5
LOG(x,y)	x为底y的对数	LOG(2,4)=2
MOD(x,y)	x除以y的余数	MOD(8,3)=2
POWER(x,y)	x的y次幂	POWER(2,3)=8
ROUND(x[,y])	x在第y位四舍五入	ROUND(3.456,2)=3.46
SQRT(x)	x的平方根	SQRT(4)=2
TRUNC(x[,y])	x在第y位截断	TRUNC(3.456,2)=3.45