oracle常用sql语句函数
-
sql执行顺序
•FROM
•WHERE
•GROUP BY
•HAVING
•SELECT
•DISTINCT
•UNION
•ORDER BY
SQL语言分为五大类
DDL(数据定义语言 Data Definition Language) - Create、Alter、Drop 这些语句自动提交,无需用Commit提交。
DQL(数据查询语言 Data Query Language) - Select 查询语句不存在提交问题。
DML(数据操纵语言 Data Manipulation Language) - Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言 Data Transaction Language) - Commit、Rollback 事务提交与回滚语句。
DCL(数据控制语言 Data Control Language) - Grant、Revoke 授予权限与回收权限语句。
一、Oracle数据库操作
1、创建数据库
create database databasename
2、删除数据库
drop database dbname
3、备份数据库
--完全备份exp demo/password@orcl buffer=1024 file=d:\back.dmp full=ydemo:用户名password:密码buffer: 缓存大小file: 具体的备份文件地址full: 是否导出全部文件ignore: 忽略错误,如果表已经存在,则也是覆盖
二、Oracle表操作
1、创建表
create table qy_test(ID integer not null,name varchar(50) default 'name' ,age number not null,sex nvarchar2(50) default '男',tm timestamp default sysTIMESTAMP,primary key(ID))--添加表注释:comment on table qy_test IS '测试表';--添加字段注释:comment on column qy_test.id is '编号';comment on column qy_test.name is '姓名';comment on column qy_test.age is '年龄';comment on column qy_test.sex is '性别';--根据已有的表创建新表:--表数据复制insert into table1 (select * from table2);--复制表结构create table table1 select * from table2 where 1>1;--复制表结构和数据create table table1 as select * from table2;--复制指定字段create table table1 as select id, name from table2 where 1>1;
2、删除表
drop table tabname
3、重命名表
--说明:alter table 表名 rename to 新表名eg:alter table tablename rename to newtablename
4、增加字段
--说明:alter table 表名 add (字段名 字段类型 默认值 是否为空);例:alter table tablename add (ID int);eg:alter table tablename add (ID varchar2(30) default '空' not null);
5、修改字段
说明:alter table 表名 modify (字段名 字段类型 默认值 是否为空);
eg:alter table tablename modify (ID number(4));
6、重名字段
说明:alter table 表名 rename column 列名 to 新列名 (其中:column是关键字)eg:alter table tablename rename column ID to newID;
7、删除列
说明:alter table 表名 drop column 列名;eg:alter table tablename drop column ID;
8、添加主键
alter table tabname add primary key(col)
9、删除主键
alter table tabname drop primary key(col)
10、创建索引
create [unique] index idxname on tabname(col….)
11、删除索引
drop index idxname--注:索引是不可更改的,想更改必须删除重新建。
12、创建视图
create view viewname as select statement
13、删除视图
drop view viewname
三、Oracle操作数据
1、数据查询
select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]
2、插入数据
insert into 表名 values(所有列的值);例: insert into test values(1,'zhangsan',20);insert into 表名(列) values(对应的值);例: insert into test(id,name) values(2,'lisi');
3、更新数据
update 表 set 列=新的值 [where 条件] -->更新满足条件的记录例: update test set name='zhangsan2' where name='zhangsan'update 表 set 列=新的值 -->更新所有的数据例: update test set age =20;
4、删除数据
delete from 表名 where 条件 -->删除满足条件的记录例: delete from test where id = 1;delete from test -->删除所有commit; -->提交数据rollback; -->回滚数据--delete方式可以恢复删除的数据,但是提交了,就没办法了 delete删除的时候,会记录日志 -->删除会很慢很慢truncate table 表名 (清空表数据速度很快)drop table 表名(删除整个表)--删除所有数据,包括表结构一并删除,不会记录日志,数据不能恢复-->删除很快
5、数据复制
--表数据复制insert into table1 (select * from table2);--复制表结构create table table1 select * from table2 where 1>1;--复制表结构和数据create table table1 select * from table2;--复制指定字段create table table1 as select id, name from table2 where 1>1;
四、Oracle查询操作
1、SQL语言分为五大类:
DDL(数据定义语言 Data Definition Language) - Create、Alter、Drop 这些语句自动提交,无需用Commit提交。
DQL(数据查询语言) - Select 查询语句不存在提交问题。
DML(数据操纵语言 Data Manipulation Language) - Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。
2、并发查询:
select /*+ parallel(8)*/ count(*) from table_name
3、查询当前用户:
select user from dual
4、查询所有用户:
select * from all_users
5、查询所有表:
select * from all_tables
6、查询当前用户下的所有表:
select * from user_tables;select * from all_tables where owner=upper('用户名');--all_tables 比 user_tables 多了一个 OWNER 字段,用来区分用户
7、查询表字段注释:
select comments from user_col_comments where TABLE_NAME='table_name'
8、限制返回记录条数 rownum :
select * from acct_credit.vs_hive_account_credit where rownum<=10;
9、计数统计:
--去重统计select count(distinct column_name) from table_name; --先统计再求和select sum(a) from (select count(*) as a from CHENGDU.CFG_C_FTTHPONCUTOVER UNION ALLselect count(*) as a from SUINING.CFG_C_FTTHPONCUTOVER )
10、case when:
select ename,casewhen sal<1000 then 'lower'when sal>1001 and sal<2000 then 'modest'when sal>2001 and sal<4000 then 'high'else 'too high'endfrom emp;
11、创建序列号:
-- 创建序列 Student_stuId_Seqcreate sequence Student_stuId_Seqincrement by 1 --自增步长start with 1 --起始值minvalue 1 --最小值 maxvalue 999999999 --最大值-- 更改序列 Student_stuId_Seq--alter sequence Student_stuId_Seqincrement by 2 --自增步长设置为2minvalue 1maxvalue 999999999;--获取序列自增ID select Student_stuId_Seq.Nextval as sequence from dual;-- 删除序列 -- drop sequence Student_stuId_Seq;--调用序列,插入Student数据insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'张三');insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'李四');
12、查看表分区:
--查询分区字段SELECT * FROM all_PART_KEY_COLUMNS where name='table_name'--查询分区SELECT * FROM ALL_TAB_PARTITIONS where table_name=upper('table_name'); --查询每个分区的数据量select t.partition_name,t.num_rows from all_tab_partitions t where table_name='table_name'
五、Oracle查询操作
0、基础查询
select * from a1;

select * from a2;

select * from a1 inner join a2 on a1.id = a2.id;

select * from a1 full join a2 on a1.id = a2.id;

select * from a1 left join a2 on a1.id = a2.id;

select * from a1 right join a2 on a1.id = a2.id;

select * from a1 union select * from a2;

select * from a1 union select * from a1;

select * from a1 union all select * from a2;

1、查询结果排序
acs:升序排序(默认)
desc:降序排序
dbms_random.value() / dbms_random.value:随机排序
--升序排序
select * from qy_test order by id;
--降序排序
select * from qy_test order by desc;
--随机排序
select * from qy_test order by dbms_random.value():
2、where 和 having 的区别
where:where子句的作用是在分组之前过滤数据,条件中不能包含聚合函数
having:having子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中可以包含聚合函数(每组的聚合函数结果可能不一样,所以有聚合函数的条件是针对每组内部的),使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
--where中不能使用聚合函数
select id from qy_test where id > 1 group by id;
--在having中可以使用聚合函数
select id from qy_test group by id having id>1 and id>max(age);
五、Oracle查询骚操作
2、按照A字段分组,取B字段的最大值,查询所有字段信息:
对某一字段分组后,取出另一字段最大值的所有记录
select p.*from (select A, max(B) as B from table_name group by A) tinner join (select * from td_log_runjob) pon t.A = p.Aand t.B = p.Bselect *from scripts_sqlldr_schedule a,(select interface, max(end_time) as end_timefrom scripts_sqlldr_schedulegroup by interface) bwhere a.interface = b.interfaceand a.end_time = b.end_time
3、比较两张表的数据差异,minus: A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果,即在A中存在,而在B中不存在的记录。
--比较两张表的数据差异
select * from A minus select * from B--比较两张表某个字段或某些字段的差异
select A.a,A.b from A minus select B.a,B.b from B
六、Oracle函数
1.字符函数
1、 字符与ASCII码的相互转化
chr(x): 给出整数X,返回对应的ASCII码字符。
ASCII(): 给出ASCII码字符,返回chr对应的整数X。
--chr(x): 给出整数X,返回对应的ASCII码字符。select chr(94) as str from dual;--ASCII(): 给出ASCII码字符,返回chr对应的整数X。select ASCII('^') as str from dual;
2、连接两个字符串
CONCAT(string1,string2):连接两个字符串
string1 || string2:连接两个字符串
--CONCAT(string1,string2)select concat('yyyyMM','dd HH') as time from dual;--string1 || string2select 'yyyyMM'||'dd HH' as time from dual;--结果: yyyyMMdd HH
3、大小写转换:
LOWER(): 转成小写
UPPER():转成大写
select lower('ASDASDAD') as str from dual;
select upper('asdasdasd') as str from dual;
4、在string1字符左边或右边粘贴数个string2字符,直到字符总字节数达到x字节。string2默认为空格。
select lpad(rpad('log',10,'*'),17,'*') as log from dual;
--结果: *******log*******select lpad(rpad('log',10),17) as log from dual;
5、截取字符串左右两边指定字符串,默认去除空格
trim: 去除左右两边指定字符串字符串
ltrim: 去除左边指定字符串
rtrim: 去除右边指定字符串
select trim(' ABCD ') from dual;
select trim('A' from 'ABCD') from dual;
select ltrim('ABCD', 'A') from dual;
select rtrim('ABCD', 'D') from dual;
6、替换字符串中指定字符为新的字符串
replace(str,old,new)
select replace('ABCD', 'B', 'F') from dual; --AFCD
7、从指定位置开始截取指定长度的字符串
substr(str,start, [length]): start 截取起始位置(索引从1开始),length不填默认截取到末尾
select substr('ABCDEF', 2, 3) from dual; --BCD
select substr('ABCDEF', 2) from dual; --BCDEF
2.数字函数
1、保留指定位数的小数
round(num, digits) : 四舍五入
trunc(num, digits) : 直接截取指定位数的小数
select round(3.1415926, 3) from dual; --3.142
select round(3.1415926, 2) from dual; --3.14select trunc(3.1415926, 3) from dual; --3.141
select trunc(3.1415926, 2) from dual; --3.14
2、取绝对值 ABS(num),获取 num 的绝对值
select ABS(-5) from dual; --5
3、求余弦值,反余弦值
余弦值: cos(num)
反余弦值:acos(num)
select cos(1) from dual; --0.54030230586814
select acos(1) from dual; --0
4、求平方根,sqrt(num)
select sqrt(3) from dual; --1.73205080756888
3.日期函数
1、某一个日期上,加上或减去指定的月数 正数为加 负数为减
select add_months(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 2) from dual; --2019/9/9 7:25:31
select add_months(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), -3) from dual; --2019/4/9 7:25:31
2、获取指定日期的当月的最后一天
select LAST_DAY(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss')) from dual; --2019/7/31 7:25:31
3、日期的四舍五入
round(date, format) : date 为指定日期,format 为指定格式,默认为 format 为 ddd 四舍五入到某天
year:四舍五入到某年的1月1日
month:四舍五入到某月的1日
ddd:四舍五入到某天
day:四舍五入到某周的周日
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'year') from dual; --2020/1/1
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'month') from dual; --2019/7/1
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'ddd') from dual; --2019/7/9
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'day') from dual; --2019/7/7
4.转换函数
1、把日期和数字转换为制定格式的字符串
to_char(date, format) :将日期的指定格式内容转换为字符串
format: 'yyyy"年"MM"月"dd"日"'
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyy"年"MM"月"dd"日"') from dual;
--2019年07月09日
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yy') from dual; --19
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyy') from dual; --2019
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMM') from dual; --201907
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMMdd') from dual; --20190709
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMMdd HH') from dual; --20190709 07
to_char(number, format) :将数字转换为字符串 (当保留小数点时时四舍五入)
| 参数 | 示例 | 说明 |
| 9 | 999 | 指定位置处显示数字 |
| . | 9.9 | 指定位置返回小数点 |
| , | 99,99 | 指定位置返回一个逗号 |
| $ | $999 | 数字开头返回一个美元符号 |
| EEEE | 9.99EEEE | 科学计数法表示 |
| L | L999 | 数字前加一个本地货币符号 |
| PR | 999PR | 如果数字式负数则用尖括号进行表示 |
select to_char(123456789.123, '999,999,999.99') from dual; --123,456,789.12
select to_char(123456789.125, '999,999,999.99') from dual; --123,456,789.13
2、把字符串转换为数字类型
to_number(str, format): 将字符串转换为数字类型
select to_number('-$12,345.67','$99,999.99')"num" from dual;
3、把字符串转换为日期类型
to_date(str,[,format]) : 将str转换为 format格式的日期
select to_date('2019-07-09 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual; --2019/7/9 13:23:44
select to_date('20190709','yyyyMMdd') from dual; --2019/7/9
select to_date('201907','yyyyMM') from dual; --2019/7/1
select to_date('2019','yyyy') from dual; --2019/当前月份/1
5.空值处理函数
1、空值判断,返回不通结果
nvl(str, value1) : 如果str为空,返回value1,否则返回str
nvl2(str, value1, value2) : 如果str为空,返回value2,否则返回value1
select nvl('', 'null') from dual; --null
select nvl('s', 'null') from dual; --sselect nvl2('', 'not null', 'null') from dual; --null
select nvl2('s', 'not null', 'null') from dual; --not null
6.聚合函数
1、聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值
等。
| 名称 | 作用 | 语法 |
| AVG | 平均值 | AVG(表达式) |
| SUM | 求和 | SUM(表达式) |
| MIN、MAX | 最小值、最大值 | MIN(表达式)、MAX(表达式) |
| COUNT | 数据统计 | COUNT(表达式) |
















