SQL语句知识

关于查询时间：

如果是date类型：
start_time  date default sysdate-1/24;一小时以前
end_time  date default sysdate;当前时间

如果是varchar2类型：

start_time varchar2(20) default to_char(sysdate-1/24,'yyyy-mm-dd hh24:mi:ss');
end_time varchar2(20) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

查询指定时间内数据：

select * from table where time>to_date('2020-03-01 08:00','yyyy-mm-dd hh24:mi')

字段是varchar类型转化为number类型进行查询：

select * from zx_kanban_out t where cast(t.MACHINERY_NUMBER as NUMBER(5)) >0

将查询结果插入到另一张表中：

（1）如果两张表（导出表和目标表）的字段一致
insert into 目标表 select * from 来源表 where 条件；
（2）如果只导入指定字段
insert into 目标表(字段1，字段2)select 字段1，字段2 from 来源表 where 条件
（3）如果跨数据库操作，把A数据库的table全部插入到B数据库的table中
select * into B.table from A.table where ....
（4）如果要插入的目标表不存在
select * into 目标表 from 表 where ....

获取系统时间：
select sysdate from dual
字段是varchar类型转化为number类型进行查询：
select * from zx_kanban_out t where cast(t.MACHINERY_NUMBER as NUMBER(5)) >0

"group by"按照指定的规则对数据进行分组，也就是选出重复的数据字段：

select 类别 from 表 group by 类别

关于存储过程：

定义一个存储过程procedure:
create or replace procedure Cnc_PDA(pro_result out  SYS_REFCURSOR)
as
  SqlTable varchar2(100) :=' ';---统计数据源表
begin
SqlTable :='CNC_SOURCE_'||to_number(to_char(sysdate, 'yyyyMM' ));
open pro_result for ' select * from '||SqlTable||' 
end Cnc_PDA;
//这是个有返回值的方法，定义一个游标pro_result out  SYS_REFCURSOR，然后open pro_result执行语句
execute   immediate ‘SQL语句’ //这只是执行语句，并且要有commit；
-----如果要有输入值，通过输入值查询存储过程
PROCEDURE get_before_hourcount(in_order_no varchar,pro_result out  SYS_REFCURSOR)
//在方法里添加变量就行，然后语句中t.order_no = '||in_order_no ||‘ and; 

起别名：

查看员工的员工id，名字和年薪,年薪列名为annual
select id,last_name,salary*12 as annual from s_emp;

nvl()函数：对null值得替换运算

查看所有员工的员工id，名字和提成，如果提成为空，显示成0
select id,last_name,nvl(commission_pct,0) commission_pct from s_emp;

distinct关键词去重复数据：

distinct关键词只能放在select关键词后面
select id,distinct title from s_emp;
如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录
查看所有员工的职位名称和部门id，同职位同部门的只显示一次：
select distinct title,dept_id from s_emp;

in(list):在一个列表中：

查看员工号1,3,5,7,9员工的工资
select id,last_name,salary from s_emp where id in (1,3,5,7,9);

like:模糊查询

查看员工名字以C字母开头的员工的id，工资
select id,last_name,salary from s_emp where last_name like 'C%';
% ：通配0到多个字符        _ : 当且仅当通配一个字符
转义字符：
默认为\,可以指定 指定的时候用escape 符号指明即可,转义字符只能转义后面的一个字符
查看员工名字中包含一个_的员工id和工资，注意:_是一个特殊字符,所以要转义
select id,last_name,salary from s_emp where last_name like '%\_%' escape '\';

trunc 截取到某一位

截取到小数点后面2位
select trunc(45.929,2) from dual;
截取到个位 (个十百千万...)
select trunc(45.923,0) from dual;
截取到十位 (个十百千万...)
select trunc(45.923,-1) from dual;	

mod 取余

把10和3进行取余 (10除以3然后获取余数)
select mod(10,3) from dual;

sysdate关键字

显示时间:当前时间
select sysdate from dual;
注意:sysdate进行加减操作的时候,单位是天，例如显示时间:明天的这个时候
select sysdate+1 from dual;

显示时间:1小时之后的这个日期
select sysdate+1/24 from dual;
months_between 俩个日期之间相差多少个月(单位是月)，例如30天之后和现在相差多少个月
select months_between(sysdate+30,sysdate) from dual;

add_months  返回一个日期数据:表示一个时间点,往后推x月的日期
例如：'01-2月-2016'往后推2个月
select add_months('01-2月-2016',2) from dual;	

next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如：离当前时间最近的下一个星期5是哪一个天
select next_day(sysdate,'星期五')from dual;注意:如要使用'FRIDAY',需把当前会话的语言环境改为英文

last_day 返回一个日期数据:表示一个日期所在月份的最后一天
例如:当前日期所在月份的最后一天(月底)
select last_day(sysdate) from dual;

round  对日期进四舍五入,返回操作后的日期数据
例如:把当前日期四舍五入到月
select round('01-2月-2016','MONTH') from dual;

to_char 把日期转换为字符

select to_char(sysdate,'dd-mm-yy') from dual;   yyyy：四位数的年份  yy：两位数的年份
select to_char(sysdate,'yy-mm-dd') from dual;   mm：两位数的月份（数字）
select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM') from dual; mi：分钟   ss：秒

to_char 把数字转换为字符

select to_char(salary,'$999,999.00') from s_emp;

把字符转换为日期
select to_date('10-12-2016','dd-mm-yyyy')from dual;
select to_date('25-5月-95','dd-month-yy')from dual;
select to_date('95/5月/25','yy/month/dd')from dual;

对查询结果集的操作：
union 取俩个结果集的并集
union all 把俩个结果集合在一起显示出来
minus 第一个结果集除去第二个结果集和它相同的部分
intersect 求俩个结果集的交集
注意:前提条件 俩个结果集中查询的列要完全一致

组函数

having 分组之后的进行进一步数据筛选的关键字
having和where的功能类似
avg 求平均值
count 计算有多少条数据
max 最大值
min 最小值
sum 求和
stddev 标准差
variance 方差

sql语句的各部分构成
select …from …where …group by …having …order by …
假如select…from…后面的语句都出现了,那么他们的执行顺序为:
where–>group by分组–>执行组函数–>having筛选->order by
where后面一定【不能】出现组函数
group by和having的关系:
1.group by可以单独存在,后面可以不出现having语句
2.having不能单独存在,有需要的话,必须出现在group by后面

查询s_emp表中部门的平均工资大于等于1400的部门,并且显示出这些部门的名字,同时按照部门编号进行排序
select se.dept_id,avg(se.salary),sd.name
		from s_emp se,s_dept sd
		where se.dept_id = sd.id
		group by se.dept_id,sd.name
		having avg(se.salary)>=1400
		order by se.dept_id;

查询s_emp表中最大的工资数,并且显示出这个最大工资的员工的名字
select s2.last_name,max(s1.salary)
		from s_emp s1,s_emp s2
		group by s2.last_name,s2.salary
		having s2.salary = max(s1.salary);

查询平均工资比 41号部门的平均工资高的部门中员工的信息
select last_name,salary,dept_id
	from s_emp
	where dept_id in(
		select dept_id
		from s_emp
		group by dept_id
		having avg(salary)>(
			select avg(salary)
			from s_emp
			where dept_id=41
		)
	);