oracle入门到精通(4)
---------------------------------------------
1、运算符
2、分组查询
3、函数
---------------------------------------------
oracle运算符的分类:(5种类型)
1、连接运算符 ||
2、算术运算符 + - * / %
3、比较运算符 > >= < <= == != in not in like between
4、逻辑运算符 and or
5、联合运算符 union union all intersect
连接运算符：||
作用：将多个内容连接成一条语句
select * from inf;
select name||'的年龄是'||age||'岁' from inf;
算术运算符 + - * / %
select id,name,age+10 age from inf;
update inf set age=age+10
比较运算符(关系运算符) > >= < <= == != in not in like between
select * from inf where age between 36 and 32;
select * from inf where name like 'c____';
逻辑运算符 and or
select * from inf where name like '%h%' and age=36
select * from inf where name like '%h%' or age=36
联合运算符 union union all intersect
联合运算符的作用,用于将多个查询语句的结果,连接成一个整体
--前提:连接的多张表,必须要有相同的字段结构
create table one
(
id number(11) primary key,
name varchar2(20) not null,
age number(11)
)
insert into one values(1,'jack',23);
insert into one values(2,'tom',24);
insert into one values(3,'chris',26);
insert into one values(4,'cindy',25);
create table two
(
id number(11) primary key,
name varchar2(20) not null,
age number(11)
)
insert into two values(1,'jack',23);
insert into two values(2,'tom',24);
insert into two values(5,'bruce',21);
insert into two values(6,'lee',22);
---union： 作用,连接两张表的查询结果,相同的数据,只显示一次,不同的数据,都显示出来
语法：
查询语句1
union
查询语句2;
select * from one
union
select * from two;
---union all 作用:两张表的数据,不论是否重复,都要全部显示
select * from one
union all
select * from two;
--intersect 作用:只显示两张表中有交集的数据
select * from one
intersect
select * from two;
--------------------------------
drop table salary2;
---查询每一个用户的销售总额,并且按销售总额,降序排序
create table salary2
(
id number primary key,
name varchar2(20) not null,
month varchar2(20),
amount number(20) --销售总额
);
insert into salary2 values(1,'jack','一月份',5000);
insert into salary2 values(2,'jack','二月份',6000);
insert into salary2 values(3,'jack','三月份',7000);
insert into salary2 values(4,'andy','一月份',5000);
insert into salary2 values(5,'andy','二月份',4000);
insert into salary2 values(6,'andy','三月份',3000);
insert into salary2 values(7,'chris','一月份',8000);
insert into salary2 values(8,'chris','二月份',5000);
insert into salary2 values(9,'chris','三月份',7000);
---分组函数: group by
在查询语句中,如果使用到了分组函数,就只能查询两种数据:
---1 用来分组的字段
---2 聚合函数
select 字段A,聚合函数 from 表 group by 字段A
select name,sum(amount) from salary2 group by name having sum(amount)>15000
order by sum(amount) desc;
--如果 where , group by orderby having
select * from 表 where .....group by .....having.....order by
--having语句,用于在分组之后,设置查询条件,它不允许单独使用,必须与group by 一起使用
Oracle数据中特殊的表:-------------- dual (虚表)
这张表本身并不存在,使用它的目的,主要让查询语句满足基本的语法规范
select 'abc' from dual;
select sysdate from dual;---系统日期是: sysdate
create table inf5
(
id number(11) primary key,
name varchar2(20) not null,
bir date
)
--语法: insert into 表(字段) values('日期-月份-年份')
insert into inf5 values(2,'cindy','29-7月-2014'); --正确
insert into inf5 values(3,'joe','22/7月/2014'); --正确
insert into inf5 values(4,'chris',to_date('2014-07-21','yyyy-MM-dd'));--正确
insert into inf5 values(5,'lee',to_date('2014/04/21','yyyy/MM/dd'));--正确
insert into inf5 values(6,'austin',date'2001-12-11');--正确
to_date('日期','yyyy-MM-dd') --转换日期格式的函数
select * from inf5;
---------
日期函数: months_between(开始时间,结束时间) --计算两个时间,间隔的月份
select months_between(date'2012-05-1',date'2011-05-01') month from dual;
select floor(months_between(sysdate,date'2011-8-29')/12) month from dual;
floor(小数)--------取得一个小于当前值的最大整数
drop table info2;
create table info2
(
name varchar2(20) not null,
gender varchar2(20) not null,
class varchar2(20),
score number,
bir date
)
select name,gender,floor(months_between(sysdate,bir)/12) age from info2
where floor(months_between(sysdate,bir)/12)>25 order by bir asc;
--months_between(开始,结束) 计算两个时间间隔的月份
-- add_months(时间,要添加的月份); 在指定时间上,添加上指定的月份,形成新的时间
select add_months(date'2012-04-22',3) from dual;
select add_months(sysdate,3) from dual;
next_day(时间,星期几); --返回下一个星期几是哪一天
--星期天 1 星期一 2 星期二 3 ......星期六 7
select next_day(sysdate,2) from dual; --计算从当前时间开始的下一个星期一是几号
--last_day(时间); 得到指定的时间的这个月的最后一天是哪一天
select last_day(sysdate) from dual;
--trunc(时间); 截断时间日期中的时分秒,只显示年-月-日
select sysdate from dual;
select trunc(sysdate) from dual; --只保留年月日
--extract(输出格式) 输出时间的指定部份的值
select extract(year from sysdate ) from dual; --取得指定时间的年份
select extract(month from sysdate ) from dual; --取得月份
select extract(day from sysdate ) from dual; --取得日期
months_between();
--select months_between(date'2012-05-1',date'2011-05-01') month from dual;
add_months();
--select add_months(sysdate,3) from dual;
next_day();
--select next_day(sysdate,2) from dual;
last_day();
--select last_day(sysdate) from dual;
trunc();
--select trunc(sysdate) from dual;
extract();
--select extract(day from sysdate ) from dual;
--字符函数
lower();将所有字母全部转换成小写
upper();将所有字母全部转换成大写
length();计算字符的长度
ltrim();去掉左侧的空格
rtrim();去掉右侧的空格
trim();;去掉两边的空格
substr();截取指定的长度的字符串
replace();替换原字符串指定部份的内容
lpad();用指定符号替换掉信息
---------------------------------------------------------
select lower('ABC张三丰123') from dual;--转换成小写
select upper('abdef') from dual; --转换成大写
select length('abcd') from dual;--计算长度
select rtrim('abc ') from dual;
select ltrim(' abc') from dual;--去掉右侧空格
select trim(' abc ') from dual;--去掉两边的空格
substr();---截取指定的内容
语法: substr('原字符串',开始位置,指定长度)
--开始位置是从1开始
select substr('hello大家好123',1,3) from dual;
select substr(tel,4,5) from inf3;
--replace() ---替换字符串的指定内容
select replace(原字符串,'旧数据','新数据')
select replace('13989141027','89141','*****') from dual;
--把inf3表中的所有电话中间五位用*替换
select replace (tel,substr(tel,4,5),'*****') from inf3;
---lpad() rpad();
rpad(字符串,10,'*');
--要把字符串输出10个符号,如果长度不够10位就用星号从右向左填充,超过10位只显示10位
select lpad('abcde',10,'*') from dual;
select rpad(name,10,'*') from inf3;
--concat(字符串1,字符串2) 将两个字符串,连接成一个字符串
select concat('abc','123') from dual;
----数学函数
select abs(-29) from dual; --求绝对值
select floor(100.23) from dual;--求一个小于当前值的最大整数(取整)
select power(x,y) from dual; --计算x的y次方
select power(2,8) from dual;
select sqrt(4) from dual;
--sign(值) --判断是正数,负数,还是0 正数返回1,负数返回-1,0就返回0
select sign(0) from dual;
--round(值,有效位数) ---四舍五入,指定小数点后的有效位数
select round(1234.5678,2) from dual;
-------------------
--to_char 与to_date 都是转换函数
select to_char(sysdate,'month') from dual;
/*
yyyy--年 year
MM--mm 月 month
dd 日
hh --小时
mi---分钟
ss --秒
day --星期几
*/