【Oracle】学习笔记（四）

算术操作符：

数值计算
加（+），减（-），乘（*），除（/）

比较操作符：

比较俩个表达式的值
=      !=      <      >      <=      >=      between…and      in      like      is null

逻辑操作符

用于组合多个计较运算的结果以生成一个或真或假的结果
and      or      not

集合操作符

将俩个查询的结果组成一个结果
union      union      intersect   minus
intersect：操作符只返回俩个查询的公共行
minus：操作符返回从第一个查询结果中排除第二个查询中出现的行

连接操作符

将多个字符串或数据值合并成一个字符串
||      concat

操作符的优先级

从高到低
算术操作符
连接运算符
比较运算符
NOT 逻辑运算符
AND 逻辑运算符
OR 逻辑运算符

单行函数

从表中查询的每一行只返回一个值
可以出现在select子句中和where子句中
分为：
日期函数：对日期进行运算，并生成日期数据类型或数值类型的结果，默认格式为dd-mon-yy
sysdate:系统时间
日期函数包括：
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
ROUND
NEXT_DAY
TRUNC
EXTRACT

字符函数：
Initcap(char)
Lower(char)
Upper(char)
Ltrum(char,set)
Rtrim(char,set)
Translate(char,from,to)
Replace(char searchstring,[rep string])
Instr(char,m,n)
Substr(char,m,n)
Concat(expr1,expr2)
length(char)等等

数字函数：
Abs(n)
Ceil(n)
Cos(n)
Cosh(n)
Floor(n)
Power(m,n)
Mod(m,n)
Round(m,n)
Trunc(m,n)
Sqrt(n)
Sign(n)

转换函数：
转换函数将值从一种数据类型转换为另一种数据类型，常用有：
TO_CHAR
TO_DATE
TO_NUMBER

其他函数：
以下是用来换空值的函数：
NVL,NVL2,NULLIF

聚合函数：

或分组函数：
基于一组行来返回结果
为每一组行返回一个值
AVG
MAX
MIN
SUM
COUNT

分析函数：

根据一组行来计算聚合值
用于计算完成聚集的累计排名，移动平均次数等
分析函数为每组记录返回多个行

ROW_NUMBER：返回连续的排位，不论值是否相等
RANK：具有相等值的行排位相同，序数随后跳跃
DENSE_RANK：具有相等值的行排位相同，序号是连续的

操作

create table classInfo( 			   --如果有主外键引用，那么在删除主表时要求先删除从表
  cid number(4) primary key,  		   --班级编号
  cname varchar2(100) unique not null, --班级名称
  cyear number(4), 					   --入学年份
  clen number(1) 					   --学制
);

--创建学生信息表
drop table stuInfo;
create table stuInfo(
  stuNo number(10) primary key,
  cid number(4)
    constraint FK_stuInfo_cid references classInfo(cid),
  stuName varchar2(100) not null,
  stuPwd varchar2(20) not null,
  stuCardId varchar2(20)
    constraint CK_stuInfo_stuCardId check(length(stuCardId)=18),
  stuSex varchar2(4) default '男'
    constraint CK_stuInfo_sex check( stuSex='男' or stuSex='女'),
  stuAge number(3)
    constraint CK_stuInfo_stuAge check(stuAge between 12 and 30),
  stuTel varchar2(15) unique, --注意：唯一约束不对NULL做约束
  stuAddr varchar2(100),
    constraint CK_stuInfo_stuTelAndAddr check( stuTel is not null or stuAddr is not null)
);

--添加数据
--语法:insert into<表名>[(<列名1，列名2,...>)values(<值1，值2,...>)]
--如果没有指定列的顺序，即表名后面没有列列表，添加数据的顺序必须跟表中的字段顺序一致
insert into classInfo values(1001,'计科2019',2019,4);

--多行数据添加
--将dept表中的deptno和dname列中的值添加到classInfo表中的cid和cname中
--insert into <要添加数据的表名>(列列表) select <列列表> from <数据来源的表名>
insert into classInfo(cid,cname) select deptno,dname from dept

--同时向classInfo表中添加班级信息
insert into classInfo
select 1004,'计科1901班',2019,4 from dual union
select 1005,'计科1901班',2019,4 from dual union
select 1006,'计科1901班',2019,4 from dual 
--再多插入几个

--数据更新
--语法:update <表名>set <列名=新值>，<列名=新值>,...[where<更新条件>]
update classInfo set cyear=2014,clen=3;
select * from classInfo;

--将classInfo表中的1001班班级的学制全部改成5年
update classInfo set clen=5 where cid=1001;

--将classInfo表中入学年份为2014年的学制改为4年
update classInfo set clen=4 where cyear=2014;

--将classInfo表中所有年份减1，即2015变成2014,2016变成2015
update classInfo set cyear=cyear-1

--删除数据
--语法： delete from <表名> [删除条件]
--删除classInfo 表中 cid 小于100的班级信息
delete from classInfo where cid<100
select * from classInfo
insert into classInfo

--删除stuInfo表中身份证号码为空的学生信息
insert into stuInfo values(seq_stuInfo.Nextval,1004,'李四','123','',default,25,120,'衡阳')
select * from stuInfo
delete from stuInfo where stuCardId is null

--删除classInfo表中cid为1001的班级信息
--此时删除失败，因为该班级信息表有学生信息，此时必须先删除该班级下所有学生才能删除该信息
select * from classInfo
delete from classInfo where cid=1001
delete from stuInfo where cid=1001
--对于这种有主外键引用的数据，可以在创建表时指定是否级联删除，但是一般不使用，因为容易造成数据的丢失

基础查询

--查询的语法：select<列名列表> from <表名>[,表名]...[where<查询条件>]

select cid as 班级编号,cname 班级名称 from classInfo

--alias column name 列重命名
--alias table name  表重命名

--算术运算符 +, -, *, /  针对的是计算
--emp是scott用户下的表。查询此表：select * from scott.emp
--将emp表中的基本工资和奖金累加后返回
select empno,ename,sal+comm as 金额 from emp

--比较运算符 =, !=, <, >, <=, >= between...and, in, not in, like, is null
--查询一年后 一年后小于21岁的
select * from student where age+1<21

--查询学号为1004的学生信息
select * from student where sid=1004

--查询并不是1002班的所有学生信息
select * from student where cid!=1002
select * from student where cid<>1002

--查询年龄在20到22岁之间的所有学生
select * from student where age>20 and age<22;
select * from student age between 20 and 22;
select * from student where age in(20,21,22);

事务控制

--事务控制
--commit: 提交并结束事务
--rollback：撤销事务中已完成的工作
--savepoint: 标记事务中的可以回滚的点

--向classInfo表中添加一条数据
insert into classInfo values(1110,'软件1902班'，2016,4)
commit --提交事务 事务就结束

insert into classInfo values(1111,'软件1903班',2016,4)
insert into classInfo values(1112,'软件1903班',2016,4)
rollback;//可以回滚 再来查 会找不到这两天插入的语句

--用户此时发现，数据删除了，想要放弃删除，但不放弃修改，在没有提交事务的前提下，可以将数据回滚保存点p2
--最后提交事务，事务一旦提交就不能回滚
insert into classInfo values(1113,'软件1903班'，2016,4)
savepoint p1
insert into classInfo values(1114,'软件1904班'，2016,4)
rollback to savepoint p1
select * from classInfo

--删除表中所有数据
delete from stuInfo
rollback
select * from stuInfo
--或
truncate table stuInfo

函数操作

/*
oracle中sql操作符分为以下几类
  算术操作符
  比较操作符
  逻辑操作符
  集合操作符
  连接运算符
  
查询的语法：select<列名列表> from <表名>[,表名]...[where<查询条件>]
*/

--查询所有姓张的学生信息（模糊查询）
--查到所有姓张的
select * from student where sname like '张%'

--查询所有姓名为张某的学生信息
--只能查到姓张的且姓名就俩个字 比如张三 不能查到张三飞
select * from student where sname like '张_'

--查询所有住址为空的学生信息
select * from student where addr is null

--逻辑操作：and or not
--查询年龄大于23的所有男生
select * from student where age>23 and sex='男'

--查询地址为湖南省衡阳市或益阳市的所有学生信息
select * from student where addr='湖南省衡阳市' or addr='湖南省益阳市'
select * from student where addr in('湖南省衡阳市','湖南省益阳市')

--查询所有家庭地址不为空的学生信息
select * from student where addr is not null

--连接操作符 union,union all,intersect,minus
--获取系统所有的用户信息（包括学生信息与员工信息）的编号和姓名
select sid,sname,'学生信息'用户信息 from student
union
select empno,ename,'员工信息'用户信息 from emp

--union会自动剔除重复的信息(要求所有的列的值都相同时才会剔除)，而union all不会
insert into student values(7788,'Scott')
select sid,sname from student
union all
select empno,ename from emp

select sid,ename from student
union
select empno,ename from emp

--获取者俩个表中的相同数据（交集）
create table a(
 id number(4) primary key,
 aname varchar2(10)
);
create table b(
 id number(4) primary key
 aname varchar2(10)
);

insert into a values(1,'张三')
insert into a values(2,'张四')

insert into b values(1,'张三')
select id,aname from a
intersect
select id,bname from b

--minus 返回第一个查询语句中的所有数据，但是如果这些数据在第二个查询结果中出现，则这条数据在结果中不显示
--第一个结果集减去第一个结果集与第二个结果集的交集
select id,aname from a
minus
select id,bname from b

--exists 存在 查询所有属于1001班的学生信息
select * from student where not exists(select * from classInfo where classInfo.cid=1001 and classInfo.cid=student.cid)

--any all some
--求出1003班学生中年龄比1001班任何一个都大的学生信息
select * form student where cid=1003 and age>any(select age from student where cid=1001)
select sname,age from student where cid=1003
select sname,age from student where cid=1001

--求出其他班中，有比1001班学生年龄小的学生信息->小于最大者
select * from studenet where cid!=1001 and age<(select max(age) from student where cid=1001)

--获取学生信息格式为学号——姓名
select sid 学号,sname 姓名 from student

--函数
--数值函数
--1.求员工信息表中每个员工的日平均基本工资(按每个月30天计算),要求忽略小数部分
select ename,sal/30 sal, floor(sal/30) 日平均工资 from emp

--2.要求求出的日平均工资保留俩位小数返回
select ename,sal/30 sal,trunc(sal/30,2)...

--3.要求求出的日平均工资向上取整返回
select ename,sal/30 sal,ceil(sal/30,2)...

--4.四舍五入
select ename,sal/30 sal,round(sal/30,2)...

--字符函数

--1.将所有员工的名字转换成小写字母显示
select lower(ename) from emp
--2.将所有员工的名字转换成大写字母显示
select upper(ename) from emp

--3.显示员工信息表中员工姓名正好为5个字符的员工信息
select ename from emp where length(ename)=5

--4.以首字母大写其他字母小写的方式显示所有员工的姓名
--0和1都是第一个字符
select substr('HelloWorld',0,1) values from dual
select substr('HelloWorld',1,1) values from dual

--'1'+'2'||  变成Helloworld
select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp

--5.从学生信息表中查询显示所有学生的学号，姓名，省份
select addr from student
select sid,sname,addr,substr(addr,1,instr(addr,'省',1,1)) 省份 from student
select instr('湖南省益阳市省','省',1,2)value from dual

--6.将学生信息表中地址为长沙市的学生显示地址改为湘潭市
select sid,sname,replace(addr,'长沙市','湘潭市')地址 from student

--转换函数
--1.修改员工日期格式
select hiredate from emp
update emp set hiredate=to_date('2018/02/27','yyyy/mm/dd')where empno=7788

--2.显示员工的入职年份和月份
select extract(month from hiredate) 月,extract(year from hiredate) 年 from emp

--日期函数
--1.从emp表中查找已经入职10年的员工
select * from  emp where add_months(hiredate,12*10)<sysdate
--2.求每个员工的入职天数
select ename,trunc(sysdate-hiredate) from emp

--3.找出每个月倒数第二天入职的员工
select * from emp where hiredate = last_day(hiredate)-1

--4.获取每个员工入职的年份
select ename,hiredate,extract(month from hiredate) from emp
select extract(year from sysdate) from dual

--5.求23天之后的日期
select sysdate+23 from dual