Oracle —— 操作符｜SQL函数

一、SQL操作符

算术运算符
比较运算符
逻辑运算符
集合运算符
连接操作符

1. 算术操作符

\(+\)、\(-\)、\(\times\)、\(\div\)

检索出课程号是2的成绩+10分后的结果

select sid 学号,cid 课程号,score+10 加10分后的成绩 from t_score where cid=2;

把课程号是2的学生成绩都加10分

update t_score set score=score+10 where cid=2;
select * from t_score where cid=2;

2.比较操作符

>
<
>=
<=
!=(<>)
like
in
not in
between ...and..
is null
检索1980年前出生的学生信息

select * from t_student where sbirthday<'1-1月-1980';

检索1986年出生的学生信息

select * from t_student where sbirthday 
between '1-1月-1986' and '31-12月-1986';

检索班级是1班或者2班的学生信息

select * from t_student where sclass in(1,2);

检索不是2班的学生信息

select * from t_student where sclass !=2;
select * from t_student where sclass <>2;

检索学生表中没有电话号的学生信息

select * from t_student where stel is null;

3.逻辑操作符

not
and
or
检索班级是1班和2班的女生

select * from t_student where sclass=1 or sclass=2;
+ 检索班级是1班的女生
```sql
select * from t_student where sclass=1 and ssex='f';

4.连接操作符 `||`

select * from t_student;

--学号是sid的学生是sname，性别是sex
select '学号是'||sid||'的学生姓名是'||sname 学生信息 from t_student;

5.集合操作符

操作符的优先级：算术>连接||>比较>逻辑

intersect 交
minus 差
union 并

--统计学习操作系统(1)或数据结构(2)的同学学号
----选修了第一门课的学生信息
select sid from t_score where cid=1;
----选修了第二门课的学生信息
select sid from t_score where cid=2;
insert into t_score values('10005',2,98);

--把两张表包含的记录进行显示，公共的部分删掉
select sid from t_score where cid=1
union
select sid from t_score where cid=2;

---显示两张表中的全部记录，重复的也显示
select sid from t_score where cid=1
union all
select sid from t_score where cid=2;
--统计操作系统(1)和数据结构(2)都为及格(60分以上)的同学学号
select sid from t_score where cid=1 and score>=60
intersect
select sid from t_score where cid=2 and score>=60;
---intersect,显示两张表的公共部分

--统计操作系统(1)达到70分但数据结构(2)未达到65的同学学号
select sid from t_score where cid=1 and score>70
minus
select sid from t_score where cid=2 and score>=65;
--minus：表1的查询结果-表2的查询结果，显示剩下的内容

二、SQL函数

1.单行函数

（1）字符函数

select initcap('hello') from dual;---首字母大写
select lower('FUN') from dual;---小写
select upper('sun') from dual;----大写
---清除左边/右边的空格或者内容


select '   abc   ' from dual;
select trim('   abc   ') from dual;
select '>>>'||trim('   abc   ')||'<<<' from dual;
select '>>>'||ltrim('   abc   ')||'<<<' from dual;
select'>>>'||rtrim('   abc   ')||'<<<' from dual;  
select ltrim( 'xyzadams','xyza') from dual;
select rtrim('xyzadams','ams') from dual;



---替换函数:translate,replace
--逐一替换：h--a,e--b,l--c
select translate('helloworld','hel','abc') from dual;

--整体替换：把hel换成abc
select replace('helloworld','hel','abc') from dual;

--第二个字符在字符串中第一次出现的位置，位置从1开始
select instr ('worldwide','d') from dual;

--从原来的字符串中提取子串，从第三个位置开始，提取2个长度的字符串 
select substr('abcdefg',3,2) from dual; 

--把字符串进行连接
select concat ('Hello','world') from dual; 

--显示数字对应的ascii码
select chr(97) from dual;
select chr(65) from dual;  

---补位函数
select  lpad('hello',10,'b')  "左补齐"  from  dual;
select  rpad('hello',10,'b')  "右补齐"  from  dual;

---以上，知道查询结果是什么即可

---查询t_student表,把性别为f的显示为女生，性别为m的显示为男生
select * from t_student;

select sid 学号,sname 姓名,ssex 性别 from t_student;
select sid 学号,sname 姓名,decode(ssex,'f','女','m','男') 
性别 from t_student;

---每个值去比较然后进行值的替换。
decode(列名，值1，替换，值2，替换，值3，替换……)


--trunc截取：把数值取整
decode(trunc(score/10),'10','优秀','9','优秀','8','良好','7','中等',
'6','及格','5','不及格')---替换上表中的score

select sid,cid,decode(trunc(score/10),'10','优秀','9','优秀','8','良好','7','中等','6','及格','5','不及格') from t_score;

------查询优良中差2：switch case语句实现:case  when   then..end
select sid,cid,score from t_score;
select sid,cid, case 
       when score>=90 then  '优秀' 
       when score>=80 then  '良好' 
       when score>=70 then  '中等' 
       when score>=60 then  '及格' 
       else '不及格' 
end 成绩
from t_score;

（2）数学函数

select abs(-15) from dual; --绝对值
---取整函数
select ceil(100.8) from dual; --取比当前值大的最小整数
select floor(100.8) from dual;--取比当前值小的最大整数
select round(100.8) from dual;---四舍五入
select round(100.256,2) from dual; ---按小数精度取整

select cos(180) from dual;  --余弦
select power(4,2) from dual; --求a的b次方
select mod(10,3) from dual; --求模
select sqrt(4) from dual; --开方
select sign(-30) from dual;--符号函数：负数是-1，正数是1,0是0

（3）日期函数

select sysdate from dual;---查询当前系统时间
select sysdate+1 from dual;---查询第二天的日期
select sysdate-1 from dual;

select add_months(sysdate,1) from dual;--查询1个月后的日期
select add_months(sysdate,-1) from dual;---查询1个月前的日期

---比较第一个日期和第二个日期之间相差的月数
select months_between(sysdate,add_months(sysdate,-1)) 
from dual;
select months_between('1-1月-1970','20-1月-1971') 
from dual;
---------------------------------
--日期加1年
select add_months(sysdate,12) from dual;
--日期加1个月
select add_months(sysdate,1) from dual;
--日期加1周
select sysdate+7 from dual;
--日期加1天
select sysdate+1 from dual;
--日期加1小时
select sysdate+1/24 from dual;
--日期加1分钟
select sysdate+1/24/60 from dual;
---日期加1秒
select sysdate+1/24/60/60 from dual;

--显示当前所在月的最后一天
select last_day(sysdate) from dual;
--显示下一个星期几
select next_day(sysdate,1) from dual;--1表示下一个星期天
select next_day(sysdate, '星期日') from  dual;

select next_day(sysdate,7) from dual;
select next_day(sysdate,'星期六') from dual;
select next_day('5-12月-2020',7) from dual;

---日期的截取函数
trunc(具体日期，'年/月/季度')---截取当前日期所在年份/月份/季度的第一天

select trunc(sysdate,'year') from dual; 
select trunc(sysdate,'y') from dual; 
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'q') from dual; 

-----查询当前日期所在的年份，月份，日期
select extract(year from sysdate)  from dual;
select extract(month from sysdate)  from dual;
select extract(day from sysdate)  from dual;

（4）转换函数

字符串转换成日期 to_date

select to_date('2020-07-28','yyyy/mm/dd') from dual;

日期转换成字符串 to_char

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy/mm/dd') from dual;

字符串转换成数值 to_number

select to_number('100') from dual;

其他函数（处理空值的函数）
nvl(列,exp)
- 如果参数中的列为空就在查询中显示exp
nvl2(列,exp1,exp2)
- 参数中的列的为空显示exp1，不空显示exp2
nullif(exp1,exp2)
- 如果两个参数相等显示null，不等显示exp1

---如果两个参数值相同，显示null；否则显示第一个参数的值
select nullif(100,100) from dual;
select nullif(100,200) from dual;

---处理空值的函数
select empno,ename,sal,comm,sal+comm from emp;
--把某一列的空值变成0进行操作：nvl(comm,0)
select empno,ename,sal,comm,sal+nvl(comm,0) from emp;
--判断某一列的值，如果有值，用第二个参数替换；如果为null，替换成0
select empno,ename,sal,comm,sal+nvl2(comm,comm+100,0) from emp;

---单行函数：字符，数学，日期*，转换*，其他函数

2. 聚合函数

min(),max(),sum(),avg(),count()

select sum(score) from t_score group by sid;

3. 分析函数

row_number()......over(partition by 列1 order by 列2), 按照列1分组，按照列2排序
- 生成一列连续序号，``order by```列的值相同的序号也不同
rank()......over(partition by 列1 order by 列2), 按照列1分组，按照列2排序
- 生成一列可能不连续序号，``order by```列的值相同的序号相同
dense_rank()......over(partition by 列1 order by 列2), 按照列1分组，按照列2排序
- 生成一列连续序号，``order by```列的值相同的序号相同
生成一列序号，序号连续，sal值相同序号也不相同

select row_number() over(order by sal desc),empno,ename,sal
from emp;

生成一列序号，序号可能不连续，sal值相同的序号相同

select rank() over(order by sal desc),empno,ename,sal
from emp;

生成一列序号，序号连续，sal值相同的序号相同

select dense_rank() over(order by sal desc),empno,ename,sal
from emp;

对表生成序号的时候，还可以按照一定的分组进行序号的生成
对整张表按照sal进行排序，生成一列序号

select row_number() over(order by sal),empno,ename,sal from emp;

组内进行排序和编号:可以按照部门编号分组

select row_number() 
over(partition by deptno order by sal) 组内编号,
empno,ename,sal,deptno from emp;

posted @ 2020-06-24 15:49 Hyx' 阅读(16) 评论(0) 收藏举报

刷新页面返回顶部

Oracle —— 操作符｜SQL函数

一、SQL操作符

1. 算术操作符

2.比较操作符

3.逻辑操作符

4.连接操作符 ||

5.集合操作符

二、SQL函数

1.单行函数

（1）字符函数

（2）数学函数

（3）日期函数

（4）转换函数

2. 聚合函数

3. 分析函数

公告

4.连接操作符 `||`