ORACLE基础SQL命令(HR模板用户创建、运算符、单行子函数、多行函数)

一、实验用户、表等数据来源(如果安装数据库的时候忘记创建模板就需要手动运行脚本来创建表)

 进入/u01/app/oracle/product/19.0.0/dbhome_1/demo/schema/human_resources目录有以下文件

hr_analz.sql执行dbms_stats里面的函数，选择最佳物理访问策略

hr_code.sql在employees上创建语句级触发，以允许在工作时间使用DML，department_id或job_id列修改之后触发行级触发器，创建存储过程，将一行插入JOB_HISTORY表中。

hr_comnt.sql对regions、locations、departments、job_history、countries、jobs、employees表及其列做备注

hr_cre.sql创建6个表及其相关约束和索引，regions、locations、departments、job_history、countries、jobs、employees，还有emp_details_view视图

hr_drop_new.sql删除存储过程、视图、序列、表

hr_drop.sql同hr_drop_new.sql

hr_idx.sql创建索引

hr_popul.sql向表中添加数据，populate为增添数据

hr_main_new.sql创建用户，指定密码和使用的默认表空间和临时表空间，执行hr_cre.sql -> hr_popul.sql -> hr_idx.sql -> hr_code.sql -> hr_comnt.sql -> hr_analz.sql

hr_main.sql同hr_main_new.sql但是取消授权sys.dbms_stats

只需登录数据库执行sql脚本即可，在当前目录下登录数据库，执行@hr_main.sql即可，还需输入以下信息，日志路径输入/tmp

 

二、对表的操作

1、查看某用户有多少表，以创建的hr用户为例

conn hr/hr123
select * from tab;  //因为显示出来的TNAME列比较长，可以通过col调整
col tname for a30;

2、查看表结构

desc employees;

 

 

三、运算符(优先级从上至下1>2>3....)

1、算数运算符

+、-、*、/

2、连接运算符

||

3、比较运算符

>、>=、<、<=、<>或!=或^=

4、is [not] null、[not]like、[not] in

5、[not] between ... and ...

6、not

7、and

8、or

值排序中NULL是最大值，默认排序是升值排序，所以NULL排在后面，如果指定desc降序排列是NULL在前面，可用nulls last和nulls first指定null位置；order by 后面跟1表示第一列排序，1,2 desc 表示1列是升序，2列是降序。

like '_' 中 _ 匹配一个任意字符，like '%' 中 % 匹配一个或多个任意字符；字段别名如果有空格或特殊字符，要用双引号括起来，不能用单引号括起来。

oracle中的转义字符有 _ 、 % 、 & 、 '  ，_ 匹配任意一个字符，% 匹配一个或多个任意字符，& 是获取变量，' 是单引号 

insert into employees(employee_id,first_name,last_name,email,hire_date,job_id,salary) values(207,'chenmu','_aylor','chen.com','10-Aug-22','IT_PROG',5000);
insert into employees(employee_id,first_name,last_name,email,hire_date,job_id,salary) values(208,'yang','%%aylor','yang.com','10-Aug-22','IT_PROG',6000);

a、_ 测试

select employee_id,first_name,last_name,email,hire_date,job_id,salary from employees where last_name like '_aylor';
select employee_id,first_name,last_name,email,hire_date,job_id,salary from employees where last_name like '/_aylor' escape '/';  //取消 _ 的转义

b、% 测试

select employee_id,first_name,last_name,email,hire_date,job_id,salary from employees where last_name like '%ylor';
select employee_id,first_name,last_name,email,hire_date,job_id,salary from employees where last_name like '/%ylor' escape '/';

c、& 测试

上面出现old和new值两行是因为verify参数，显示替代变量被替代前后的语句，set verify on显示，set verify off不显示。

想通过输入字段然后判断字段是否为空，出现两次&column需要输入两次，因为sql从后向前解析，所以第一个&column可以写成&&column，这样就只需输入一次就够了，&column是临时的，每次执行都要赋值，&&column就记住变量了。

 select first_name,last_name,&column from employees where &column is not null;
select first_name,last_name,&&column from employees where &column is not null;

 

 

 d、'  测试

select first_name||''last_name is'||last_name test from employees;

可在前面再添加一个单引号，或者在使用quote

select first_name||'''last_name is '||last_name test from employees where employee_id>206;
select first_name||q'['last_name is ]'||last_name test from employees where employee_id>206;

 

四、函数

1、单行函数(输入一行返回一行，而不是输入多行返回一行，单行行数可以嵌套nest)

（1）字符函数

a、initCap(string1)将每个单词首字母大写

select first_name,initCap(first_name) from employees order by employee_id;

b、concat(str1,str2)连接str1和str2两个字符串，但是只能连接两个表达式，可以嵌套

select first_name,last_name,concat(first_name,last_name) from employees order by employee_id;

c、instr(str1,str2,start_position,nth_appearance)查找str2在str1中出现的位置，start_position代表从str1的哪个位置开始查找，nth_appearance表示要查找第几次出现的str2，后返回其位置

select last_name,instr(last_name,'%',1,2) from employees order by employee_id;

d、lpad(str,padded_length,[pad_str])在str左边填充pad_str字符，padded_length是填充之后的字符串长度，如果padded_length比str长度小，就会从左到右截取str中的padded_length个字符，如果pad_str未写，默认填充空格字符，rpad(str,padded_length,[pad_str])在str右边填充，padded_length比str长度小，处理同lpad。

select first_name,salary,lpad(salary,10,'*') lpad10,lpad(salary,3,'*') lpad3 from employees order by employee_id;
select first_name,salary,rpad(salary,10,'*') rpad10,rpad(salary,3,'*') rpad3 from employees order by employee_id

 e、trim截取字符

trim(both|leading|trailing 'char' from str1)，both表示从str1左右遍历，leading表示从str1左侧遍历，trailing表示从str1右侧遍历，默认值是both。一旦str1中字符为char就去除，否则直接返回结果，注意这里是char。

select 'ccacbc' str1,trim(both 'c' from 'ccacbc') both,trim(leading 'c' from 'ccacbc') leading,trim(trailing 'c' from 'ccacbc') trailing from dual;

ltrim(str1,str2)从str1左边开始遍历，一旦str1中字符出现在str2中(字符集)就去除，否则直接返回结果，rtrim(str1,str2)同ltrim，但其从右边开始遍历。

 select 'ccacbc' str1,ltrim('ccacbc','cb')left,rtrim('ccacbc','cb')rtrim from dual;

 f、replace(source,old,new)在source中将old字符串替换成new字符串

select replace('chenmu*yang*chenmu*yang','mu','MM') from dual;

（2）数字函数

a、round(number,num_digits)number保留小数点后num_digits位，从num_digits后一位开始四舍五入

select 125.453738 test,round(125.453738,1)  r1,round(125.453738,2) r2,round(125.453738,0) r0,round(125.453738,-2) rr2 from dual;

b、ceil(number)如果number有小数，返回int(number)+1，如果number没有小数，返回int(number)，没有四舍五入

select ceil(125.453333)  r1,ceil(125.546788) r2,ceil(125.00000) r3  from dual;

c、floor(number)无论number有没有小数，都返回int(number)，没有四舍五入

select floor(125.453333)  r1,floor(125.546788) r2,floor(125.00000) r3  from dual

d、trunc(number,num_digites)截取number小数点后num_digits位，不进行四舍五入(在MySQL是TRUNCATE函数)

 select 125.453738 test,trunc(125.453738,1)  r1,trunc(125.453738,2) r2,trunc(125.453738,0) r0,trunc(125.453738,-2) rr2 from dual

e、to_char(number,'format_model')将number转为相应的格式；9代表显示数字；0代表强制显示0；$表示美元符号；L表示使用当地货币；. 表示小数点(D)；, 表示千分符(同G)；. 和 , 一起使用，D和G一起使用

 select 4023.156 test,to_char(4023.156,'$0,999.0000') testA, to_char(4023.156,'L0,999.0000') testB,to_char(4023.156,'$0G999D0000') testC from dual;

f、to_number(str)将数字格式的str转换成数字

select to_number('07') from dual;

g、mod(被除数，除数)=余数

select employee_id, last_name from employees where mod(employee_id,2)=0;  //查询id号是偶数的员工

（3）时间函数

有四种修改NLS_DATE_FORMAT的方式

第一种是在用户的.bash_profile中增加两句：export NLS_LANGUAGE=AMERICAN；export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'。

第二种是SQLPLUS的glogin.sql文件增加一句：alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
第三种是直接修改当前会话的日期格式：alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

第四种是修改系统的日期格式，在重启之后生效： alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

系统的时间格式是DD-MON-RR，而不是DD-MON-YY。RR是后两位年份，但是需要根据系统时间来判断前两位年份。

 

date -s '2050-08-11';  //在root用户下修改系统时间到2050
clock -w;
select sysdate,to_char(sysdate,'yyyy-mm-dd')year from dual;
select to_char(to_date('11-Aug-59','DD-MON-RR'),'yyyy-mm-dd') from dual;
select to_char(to_date('11-Aug-36','DD-MON-RR'),'yyyy-mm-dd') from dual;

两个时间相减的到的是天数；相关时间和时区有相关连接https://www.cnblogs.com/muhai/p/16623341.html

a、to_char(date,'format')将date类型转为字符类型

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')time from dual;

b、to_date(str,'format')将时间格式的字符类型转为date类型

select to_date('20220228','yyyy-mm-dd') from dual;

c、trunc(date,'format')根据相应格式截取时间

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';  //将其转换成熟悉的格式
select trunc(sysdate,'yy') from dual;  //截取年份，得到年的第一天

to_char和trunc的参考连接https://www.cnblogs.com/muhai/p/15335482.html

d、cast(col1  as data_type1)将某一列数据类型转换成data_type1类型

 select cast('12.555555' as decimal(6,2))dec from dual;  //将12.555555换成2位小数

f、round(date,'format')根据时间判断，如果是后半年就返回下一年第一天；如果是后半月就返回下一个月第一天；如果是后半天就返回下一天

select round(sysdate,'year')year,round(to_date('2022-10-16','yyyy-mm-dd'),'month')month,round(to_date('2022-10-16 12:00:01','yyyy-mm-dd hh24:mi:ss'),'dd')dd from dual; 

以上三个函数都可参考时间格式参考连接

g、months_between(date1,date2)，查看两个日期之间相差的月份

参考连接：https://www.cnblogs.com/muhai/p/15435554.html

h、add_months(date,num)，对date加减月份

参考连接：https://www.cnblogs.com/muhai/p/15435679.html

i、next_day、last_day等时间函数可参考https://www.cnblogs.com/muhai/p/15788504.html

 

2、多行处理函数(分组函数，对某一组数据进行操作，显示的字段需要先通过group by分组；高级分组函数除了对某一组数据进行操作外还返回一行汇总，分组函数不统计空值)

分组函数筛选是要使用having，而不是通过where来筛选，group by和having位置可以调换，但是要在where之后。

select last_name,count(*) from employees where last_name='Taylor' having count(*)>1 group by last_name;

（1）分组函数：count()统计某一组有多少行，但不统计空值

select last_name,count(*) from employees group by last_name;  //对last_name进行分组，查看last_name有多少种，每种有多少个,last_name没有重复显示

高级分组函数：count()统计某一组有多少行，并返回所有组的总行数，不统计空值

select last_name,count(*) from employees group by rollup(last_name);
//等价于先select last_name,count(*) from employees group by last_name，最后一行select count(*) from employees group by 0

（2）sum()函数对某一组中的某一列求和

select sum(salary) from employees;  //所有人作为一组，求所有人的工资总和

select last_name,sum(salary) from employees group by last_name;  //对last_name进行分组，并对组进行salary求和，last_name只出现一次
select last_name,sum(salary) from employees group by rollup(last_name);  //在底部对数据进行汇总
//等价于select last_name,sum(salary) from employees group by last_name，最后select sum(salary) from employees group by 0
select last_name,sum(salary) from employees group by cube(last_name);  //在上面对数据进行汇总
//等价于select sum(salary) from employees group by 0，最后select last_name,sum(salary) from employees group by last_name

 

select last_name,job_id,sum(salary) from employees group by rollup(last_name,job_id);
//等价于select last_name,job_id,sum(salary) from employees group by last_name,job_id
//还有select last_name,sum(salary) from employees group by last_name
//最后select sum(salary) from employees group by 0
select last_name,job_id,sum(salary) from employees group by cube(last_name,job_id);
//等价于一、select sum(salary) from employees group by 0
//二、select job_id,sum(salary) from employees group by job_id
//三、select last_name,sum(salary) from employees group by last_name
//四、最后select last_name,job_id,sum(salary) from employees group by last_name,job_id

（3）avg()对某一组中的某一列求平均值，使用方法同sum()

select sum(salary),count(*),sum(salary)/count(*) div,avg(salary) from employees;  //对整个表求平均值，等价于sum()/个数

 select last_name,sum(salary),count(*),sum(salary)/count(*) div,avg(salary) from employees group by last_name;

（4）min()函数对某一分组中的某一列求最小值，方法同sum

select last_name,min(salary) from employees group by last_name;

 

（5）max()函数对某一分组中的某一列求最大值，方法同sum

select last_name,max(salary) from employees group by last_name;

 

3、分析函数（要显示的字段不必先用group by分组，over(partition by columnA)是对columnA进行分区，over(oder by columnB)是对cloumnB进行累计统计）

（1）count()统计各分区中的个数

select last_name,count(*) over(partition by last_name)count from employees; //查看各个last_name分区有多少行，last_name重复出现

（2）sum()统计各分区中某一列的和

select last_name,salary,sum(salary)over (partition by last_name)sum from employees;  //对last_name进行分区，并对各个区进行salary求和，last_name出现多

select last_name,salary,sum(salary)over (partition by last_name order by salary)sum from employees;  //对last_name进行分区，并根据salary排序累加,last_name出现多次

（3）avg()对各个分区中的某一列求平均值

select last_name,salary,avg(salary)over (partition by last_name)avg from employees;  //根据last_name进行分区，求各个分区的平均值

（4）min()对各个分区的某一列求最小值

c/avg/min  //将上面的sql中的avg直接替换成min
/;  //可直接执行替换后的语句
select last_name,salary,min(salary)over (partition by last_name)avg from employees;

（5）max()对各个分区的某一列求最大值

 （6）row_number()对数据进行排序,重复值也会分出1,2,3

select last_name,salary,row_number() over (partition by last_name order by salary)row_number from employees;  //对last_name进行分区，并对salary进行排序

（7）dense_rank()对数据进行排序，重复值不会分出1,2,3

select last_name,salary,dense_rank() over (partition by last_name order by salary)dense_rank from employees;