Oracle数据库笔记二(between..and、in、like、not、and、or、插入、更新、删除、函数:求余、获取整数、四舍五入、字符串函数、日期和时间函数、转换函数、系统信息函数、聚合函数、查询数据、分组查询)
重要的oracel比较运算符（可以用来进行模糊查询）
1、between..and运算符
此运算符用于测试是否在指定的范围内。通常和where子句一起使用，between..and条件返回一个介于指定上限和下限之内的范围值。
例如：
select name from student where birth between '1980' and '1990';
between..and操作符前可以加关键字NOT，表示指定范围之外的值
例如：
select name from student where birth not between '1980' and '1990';
2、IN运算符
IN运算符用来判断操作数是否为IN列表中的其中一个值。同样NOT IN运算符用来判断操作数是否不是IN列表中的其中一个值
例如：
--从学生表中找出年龄是25和26的学生姓名（25，26中的任何一个，与or相似） select name from student where age in(25,26);
3、LIKE
此运算符用来匹配字符串。在进行匹配时，可以使用下面两种通配符
（1）'%'，用来代表有0个或者多个字符组成的任意顺序的字符串
（2）'_'，（下划线）只能匹配一个字符
例如：
--匹配姓张的学生'张%' select name from student where name like '张%'; --'张_' 代表张后面只有一个字 select name from student where name like '张_'; --'%张%' 代表名字包含"张"字就行 select name from student where name like '%张%'; --'b%y' 代表b开头，y结尾的任意字符 select name from student where name like 'b%y'; --'_ _ _ _y' 代表y前面有4个任意字符，注意下划线之间不能有空格 select name from student where name like '_ _ _ _y';
重要的逻辑运算符
1、NOT运算符
又称取反运算符，常常和IN、LIKE、BETWEEN..AND和NULL等关键字一起使用
例如：
--找到年龄不是25、26岁的学生姓名 select name from student where age not in(25,26);
2、AND运算符
要求两边的表达式结果都为true，如果任何一方的返回结果为null或false，那么逻辑运算的结果就为false，也就是说记录不匹配where子句的要求
例如：
--找到年龄为25和姓张的学生姓名 select name from student age=25 and name like '张%';
3、OR运算符
又称或运算符，也就是说只要左右两侧的布尔表达式任何一方为true，结果就为true
例如：
select name from student age=25 or name like '张%';
Oracle插入、更新、删除数据
1、插入：insert into
使用insert插入数据时，允许列名称为空，此时，值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
例如一：
insert into person (id,name,age,info) values (1,'Green',21,'Larger') --varchar2字符串需要单引号，例如'Green'
例如二：
insert into person (age，name,id,info) values (22,'Green',2,'dancer')
例如三：
insert into person values (3,'Mary',24,'Mucision') --与表的字段相同
2、为表的指定字段插入数据，就是在insert语句中只向部分字段中插入值，而其它字段的值为定义的默认值。
3、将查询结果插入到表中
insert还可以将select语句查询的结果插入表中，如果想要从另外一个表中合并个人信息到person表，
不需要把每一条记录的值一个一个输入，只需要使用一条insert语句和一条select语句组成的组合语句，
即可快速的从一个或多个表中向一个表中插入多个行
例如：
--将表2中的所有记录插入到表1中 insert into table1(字段列表) select (字段列表2) from table2 where (条件); --将person_old表中的数据插入到person表中 insert into person(id,name,age,info) select id,name,age,info from person_old;
4、更新：update
oracle中使用update语句更新表中的记录，可以更新特定的行或者同时所有的行
例如：
--将person表中id为11的人年龄换成15，姓名改为LiMing update person set age=15,name='LiMing' where id=11;
ipdate后面直接加表明（person），注意set，不写where时，将把所有的行都修改
注意：保证update以where子句结束，通过where子句指定被更新的记录所需要满足的条件，如果忽略where子句，Oracle将更新表中所有的行。
例如：
--将person表中年龄在19-22之间的人职业换为student update person set info='student' where age between 19 and 22;
5、删除：delete
从数据库中删除数据使用DELETE语句，DELETE语句允许where子句指定删除条件。如果没有where子句，DELETE语句将删除表中的所有数据
--删除id为11的行 delete from person where id=11; --删除年龄在19-22的行 delete from person where age between 19 and 22;
删除表中字段
例如：
--修改emp表，删除bonus列 alter table emp drop (bonus); --删除字段需要从每行中删除掉该字段中占据的长度和数据，并释放
注意：insert、update、delete区别，update后面不加from，用到set
Oracle函数
Oracle函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数等。
1、数学函数
（1）求余函数MOD(x,y)：返回x被y除后的余数，MOD函数对于带有小数部分的数值也起作用，它返回除法运算后的精确余数
例如：
select MOD(31,8),MOD(234,10) from dual;
（2）获取整数的函数CEIL(x)和FLOOR(x)
函数CEIL(x)：返回不小于x的最小整数值
例如：
select ceil(-3.35),ceil(3.35) from dual; --返回-3，4
函数FLOOR(x)：返回最大整数
例如：
select floor(-3.35),floor(3.35) from dual; --返回-4，3
（3）四舍五入函数ROUND(x)，ROUND(x,y)和TRUNC(x,y)
函数ROUND(x)：返回最接近于参数x的整数，对x值进行四舍五入
例如：
select round(-1.15),round(-1.68),round(1.15),round(1.68) from dual; --返回-1，-2，1，2
函数ROUND(x,y)：返回最接近于参数x的数，其值保留到小数点后面的y位，若y为负值，则将保留x值到小数点左边y位
例如：
select round(1.38,1),round(1.38,0),round(232.38,-1),round(232,38,-2) from dual; --返回1.4，1，230，200
注意，y为负值时，保留的小数点左边的相应位数直接保存为0，进行四舍五入
函数TRUNC(x,y)：返回被舍去至小数点后y位的数字x，若y的值为0，则结果不带有小数点或不带有小数部分。若y没有负值，则截去x小数点左起第y位开始后面所有地位的值
例如：
select trunc(1.31,1),trunc(1.99,1),trunc(1.99,0),trunc(19.99,-1) from dual; --返回1.3、1.9、1、10
补充：ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNC(x,y)函数直接截取值，不会四舍五入
2、字符串函数
（1）length(str)：返回字符串的字节长度
例如：
select length('date'),length('egg') from dual; --返回4、3
（2）concat(s1,s2)：返回结果为连接参数产生的字符串
例如：
select concat('学习','Oracle 10g') from dual; --返回学习Oracle 10g
（3）字符串搜索函数：instr(s,x) 返回x字符在字符串s的位置
例如：
select instr('Hello Oracle','c') from dual; --返回10 （从1开始数）
（4）Lower(str)：可以将字符串str中的字母全部转换成小写字母
例如：
select lower('BEIJING') from dual; --返回beijing
（5）upper(str)：可以将字符串str中的字母全部转换为大写字母
例如：
select upper('beijing') from dual; --返回BEIJING
（6）initcap(str)：将输入的字符串单词的首字母转换成大写
select initcap('hello beautiful') from dual; --Hello Beautiful
（7）substr(s,m,n)：获取指定的字符串。其中参数s代表字符串，m代表截取的位置，n代表截取的长度，当m值为正数时，从左边开始数指定的位置，当m为负数时，从右边开始取指定位置的字符
select substr('abcde好fgh',6,2),substr('abcde好fgh',-6,2) from dual; --返回好f、e好
（8）replace(s1,s2,s3)：替换字符串函数，其中参数s1代表搜索的目标字符串，s2表示在目标字符串中要搜索的字符串，s3是可选参数，用它替换被搜索到的字符串，
如果该参数不用，表示从s1字符串中删除搜索到的字符串。
select replace('this is a dog','dog','cat') from dual; --this is a cat select replace('this is a dog','dog') from dual; --this is a
（9）LTRIM(s,n)：将删除指定的左侧字符。其中s是目标字符串，n是需要查找的字符，如果n不指定，则表示删除左侧的空格(空白)。
select ltrim('this is a dog','this'),ltrim(' this is a dog') from dual; --is a dog、this is a dog
（10）RTRIM(s,n)：将删除指定的右侧字符。其中s是目标字符串，n是需要查找的字符，如果n不指定，则表示删除右侧的空格(空白)
select ltrim('this is a dog','dog'),ltrim('this is a dog ') from dual; --this is a、this is a dog
（11）trim：删除指定的前缀或者后缀的字符，默认删除空格
select trim(both 'x' from 'xyxabcyx'),trim(' abc aa ') from dual; --yxabcy 、 abc aa
3、日期和时间函数
（1）SYSDATE()：获取当前系统日期
select sysdate from dual; --以指定格式输出当前日期 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select sessiontimezone from dual;
（2）last_day(date)：返回参数指定日期对应月份的最后一天
select last_day(sysdate) from dual;
（3）extract(datetime)：可以从指定的时间中提取特定部分，例如提取年份，月份，或者时间等
select extract(year from sysdate),extract(minute from timestamp '1985-10-8 12:23:40') from dual; --2022、23
4、转换函数
主要作用是完成不同数据类型之间的转换。（字符串、日期、数值）
select ASCIISIR('从零开始学') from dual; --可以打印出每个汉字的ASCII码
（1）ASCIISIR(char)：可以将任意字符串转换为数据库字符集对应的ASCII字符串
（2）TO_CHAR：将一个数值型数据转换成字符串数据
select to_char(10.123) from dual; --10.123（字符串） --将日期类型转换为字符串类型 select to_char(sysdate,'yyyy-mm-dd') from dual; --2022-03-25（字符串类型）
（3）TO_DATE：将一个字符型数据转换成日期型数据
select to_char(todate('1996-10-16','yyyy-mm-dd'),'month') from dual; --10月
（4）TO_NUMBER：将一个字符型数据转换成数字数据
select to_number('1999.23') from dual; --1999.23（数字型） select to_number('1999.23abc') from dual --x错误
5、系统信息函数
USER：返回当前会话的登录名
select user from dual; --system
6、NVL函数：由于聚合函数是忽略空值的，例如sum()、avg()等，所以经常使用NVL()函数进行null的转换
nvl(列值,0)，当列值为null时，取值为0，当列值为非null时，取值为列值本身
7、聚合函数
AVG()：返回某列的平均值
COUNT()：返回某列的最大值
MAX()：返回某列的最大值
MIN()：返回某列的最小值
SUM()：返回某列值的和
COUNT()：函数统计数据表中包含记录行的总数，或者根据查询结果返回列中包含的数据行数，其使用方法有两种
（1）COUNT(*)：计算表中总的行数，不管某列有无数值或者空值
（2）COUNT(字段名)：计算指定列下总的行数，计算时将忽略空值的行
select count(*) as cust_num from customers; select count(c_email) from customers;
注意：指定列的值为空的行被COUNT函数忽略，但如果不指定列，而在COUNT函数中使用“ * ”，则所有记录都不会被忽略
SUM()：函数在计算时，忽略列值为null的行
select sum(quantify) from orderitems where o_num=3005; --求3005号订单购买的水果总量
查询数据（先写from--再写where--group by--having--再写select---order by）
1、Oracle从数据库中查询数据的基本语句为select语句
2、在select语句中使用 “ * ”通配符代表所有字段
3、一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符 “ * ”，使用通配符最然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。
通配符的优势是，当不知道所需要的列的名称时，可以使用通配符获取他们。
4、Oracle中SQL语句是不区分大小写的，因此SELECT和select作用是相同的，但是，许多开发人员习惯将关键字使用大写，而数据列和表名使用小写
5、在select语句中通常使用where子句对数据进行过滤
6、数据表创建的时候，设计表可以指定某列中是否可以包含空值（NULL）。
空值不同于0，也不同于空字符串。空值一般表示数据未知，不适用或将在以后添加数据。在select语句中使用is null子句，可以查询某字段内容为空记录，与is null相反的是is not null，该关键字查找字段不为空的记录。
xxx=null 用法是错误的，应该是xxx is null;
select c_id from customers where c_email=null; (X) select c_id from customers where c_email is null; (✔)
7、count：求数据表总列数
select count(*) as cust_num from customers; --cust_num为别名 （as x ：设置别名，as可省略）
8、select语句中，可以使用distinct关键字指示Oracle消除重复的记录值
select distinct s_id from fruits; --将不重复的s_id号找出来
9、多列排序：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
select f_name,f_price from fruits order by f_name,f_price; --按名称、价格进行排序（价格排序不起作用）
(order by 以..排序，默认按照升序对记录进行排序)
10、与DESC（降序）相反的是ASC（升序），将字段中的数据，按字母表顺序升序排序。
实际上，在排序的时候ASC时作为默认的排序方式，所以加不加都可以
select f_name,f_price from fruits order by f_price desc; select f_price,f_name from fruits order by f_price desc,f_name asc; --对价格降序，对名称升序排列
分组查询
分组查询是对数据按照某个或多个字段进行分组，Oracle中使用GROUP BY关键字对数据进行分组
基本语法为：
【GROUP BY 字段】【HAVING <条件表达式>】 --必须有group by才可以写having
其中，“字段”值为进行分组时所依据的列名称；
“HAVING <表达式>”指定满足表达式限定条件的结果将被显示；
GROUP BY关键字通常和聚合函数一起使用，例如，MAX()、MIN()、COUNT()、SUM()、AVG()
select s_id,count(s_id) from fruits group by s_id; --按s_id进行排序，并求出每组的记录数
Oracle中可以在group by字节中listagg()函数，将每个分组中各个字段的值显示出来
--根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来 select s_id,listagg(f_name,',') within group(order by s_sid) as Names from fruits group by s_id; --根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 select s_id from fruits gruop by s_id having count(f_name)>1;
注意：having关键字与where关键字都是用来过滤数据，两者有什么区别呢？
其中重要的一点是：having通过在数据分组之后进行过滤来选择分组，而where在分组之前，用来选择记录，
另外，where排除的记录不再包括在分组中
rollup()：
在group by子句中使用rollup关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量
select s_id,count(*) as total from fruits group by rollup(s_id);
多字段分组：
使用group by可以对多个字段进行分组，group by关键字后面跟需要分组的字段，oracle根据多字段的值来进行层次分组，分组层次从左到右，即先按第一个字段分组，
然后在第一个字段值相同的记录中，再根据第2个字段的值进行分组，依次类推。
select o_num,sum(quantity * item_price) as orderTotal from orderitems group by o_num having sum(quantity * item *item_price)>=100; --查询s_id=103的供应商的水果价格的平均值 select avg(f_price) as avg_price from fruits where s_id=103; --在fruits表中，查询每一个供应商的水果价格的平均值 select s_id,avg(f_price) from fruits group by s_id;
