整理收集Oracle的常用sql -- 创建数据表 create table student_1( studID        int not null primary key, studName  varchar2(50), password   varchar2(10), age            int, sex            char(10) ); -- 查询数据表 select * from student_1 -- 向数据表student_1中插入数据 insert into student_1 values(1,'张三','123456',20,'男') insert into student_1 values(2,'李四','123456',21,'女') insert into student_1 values(3,'王五','123456',22,'男') insert into student_1 values(4,'赵六','123456',23,'女') -- 查询年龄为20的学员姓名 select studName from student_1 where age=20 -- 查询姓名为李四的学员信息 select * from student_1 where studName='李四' -- 向student_1表中添加一个字段 alter table student_1 add(study varchar2(20)) -- 向student_1中添加一个日期字段 alter table student_1 add timeday date; -- 向student_1中添加日期 insert into student_1 values(6,'张三','123456',20,'男','大专',to_date('2000.1.1','yyyy.mm.dd')) -- 删除student_1表中study字段 alter table student_1 drop column study; -- 修改姓名为张三的学历 update student_1 set study='大专' where studName='张三'; -- 删除student_1表的张三这条记录 delete from student_1 where studName='张三' -- ROWID 用于记录数据库中记录的唯一行号 select rowid,studID,studName from student_1 -- 过滤重复行的方式 -- 方式1 select distinct trim(studName),studID,password,age,sex,study,timeday from student_1 -- 方式2 select distinct trim(studName),studID,password,age,sex from student_1 group by trim(studName),studID,password,age,sex -- 方式3 select distinct trim(studName),studID,password,age,sex,max(rowid) from student_1 group by trim(studName),studID,password,age,sex -- 删除重复记录 delete from emp_bak where rowid not in ( select max(rowid) from emp_bak group by empno ); -- 数字运算符(+、-、*、/) -- 1加号运算 select age+1 from student_1 -- 2减号运算 select age-1 from student_1 -- 3乘号运算 select age*2 from student_1 -- 4除号运算 select age/2 from student_1 -- 任何数+null都为null select 1+null from dual; -- 比较运算符(=、>、<、>=、<=、<>、!=) select * from student_1 where age=20 select * from student_1 where age>20 select * from student_1 where age=22 select * from student_1 where age<=22 select * from student_1 where age<>22 select * from student_1 where age!=20 -- 逻辑运算符(or 、and、 not) select * from student_1 where age=21 or age=23 select * from student_1 where age=21 and studName='李四' select * from student_1 where not age=21 -- 使用BETWEEN…AND…范围(在上限和下限之间) select * from student_1 where age between 22 and 23 -- 使用IS NULL 和 IS NOT NULL 找出NULL值或非NULL 值 select * from student_1 where study is null select * from student_1 where study is not null -- 使用like关键字查询模式匹配(或模糊查询) ,“%”号代表0个或多个字符,而”_”则表示单个字符 select studName from student_1 where studName like '张%' escape '\'; select studName from student_1 where studName like '张_' select studName from student_1 where studName like '%三' select studName from student_1 where studName like '_三' -- 使用order by语句,desc是降序排列,asc是升序排列,默认为升序排列 select * from student_1 order by age desc select * from student_1 order by age asc select * from student_1 order by age -- 使用group by语句查询 select count(studName) from student_1 group by study -- 使用having语句 select trim(studName),count(*) from student_1 group by trim(studName) having count(*)=2; select count(*) studName from student_1 group by studName having count(*)=2; -- 查询部门只有三个人的人员信息。 -- 使用max函数返回列或表达式的最大值(数据最大,时间最晚) select max(age) from student_1 -- 使用min函数返回列或表达式的最小值(数据最小,时间最早) select min(age) from student_1 -- 使用avg函数返回列或表达式的平均值 select avg(age) from student_1 -- 使用sum函数返回列或表达式的总和 select sum(age) from student_1 -- 使用count函数返回非NULL行的行数 select count(age) from student_1 -- 使用VARIANCE函数返回列或表达式的方差 select variance(age) from student_1 -- 使用STDDEV函数返回列或表达式的标准偏差 select stddev(age) from student_1 -- 查询student_1表中有几个女生 select count(sex) from student_1 where sex='女' -- 查询student_1表中年龄的总和 select sum(age) from student_1 -- 创建数据表 create table emps( sid number(4) not null primary key, deptno number(4) not null, deptName varchar2(10), empName varchar2(10), salary number(4), job varchar2(20) ) -- 删除表 drop table emps -- 插入数据 insert into emps values(1,1,'销售部','张三',1200,'职员'); insert into emps values(2,1,'销售部','李四',2000,'经理'); insert into emps values(3,1,'销售部','王五',3000,'总经理'); insert into emps values(4,2,'客服部','赵六',1200,'职员'); insert into emps values(5,2,'客服部','周期',2500,'经理'); insert into emps values(6,2,'客服部','李吧',4000,'总经理'); insert into emps values(7,1,'销售部','李三',1300,'职员'); insert into emps values(8,1,'销售部','周三',1400,'职员'); insert into emps values(9,2,'客服部','李六',1400,'职员'); insert into emps values(10,2,'客服部','周六',1500,'职员'); insert into emps values(11,2,'客服部','齐期',3000,'经理'); insert into emps values(12,2,'客服部','鲁期',2800,'经理'); insert into emps values(13,2,'销售部','齐九',3000,'经理'); insert into emps values(14,2,'销售部','鲁十',2800,'经理'); -- 查询表 select * from emps -- 不包含组的查询 Select sid,salary,empname from emps Where deptno=2 -- 包含组的查询 Select avg(salary),avg(distinct salary),max(salary),min(salary),sum(salary), count(*),count(salary),count(distinct salary),count(distinct deptname),count(deptname) from emps where deptno=2 -- 单列分组显示每个部门的平均工资和最高工资 Select deptno,avg(salary),max(salary) from emps group by deptno -- 多列分组显示每个部门、每种岗位的平均工资和最高工资 Select deptno,job,avg(salary),max(salary) from emps group by deptno,job -- 使用ROLLUP和CUBE限定词生成报表 -- 显示每个部门、每种岗位的平均工资和最高工资及其横向统计结果 Select deptno,job,avg(salary),max(salary) from emps group by rollup(deptno,job) -- 显示每个部门、每种岗位的平均工资和最高工资及其纵向统计结果 Select deptno,job,avg(salary),max(salary) from emps group by cube(deptno,job) -- 查询所有职员的平均工资 select avg(salary) from emps where job='职员' -- 查询所有职员工资的总和8000 select sum(salary) from emps where job='职员' -- 查询所有经理的工资16100 select sum(salary) from emps where job='经理' -- 查询经理的平均工资值为2683.33333333333 select avg(salary) from emps where job='经理' -- 查询部门编号1的经理的平均工资2000 select avg(salary) from emps where job='经理' and deptno=1 值为1333.33333333333 -- 查询部门编号1的经理的平均工资2820 select avg(salary) from emps where job='经理' and deptno=2 -- 查询部门编号1的职员的平均工资1300 select avg(salary) from emps where job='职员' and deptno=1 -- 查询部门编号2的职员的平均工资1366.66666666667 select avg(salary) from emps where job='职员' and deptno=2 -- 显示平均工资高于2000元的部门编号、平均工资和最高工资 select deptno,avg(salary),max(salary) from emps group by deptno having avg(salary)>2000 -- 函数的使用 -- ACOS(n) 返回n的反余弦值 select ACOS(-1) from dual -- ASIN(n) 返回n的反正弦值 select ASIN(1) from dual -- ATAN(n) 返回n的反正切值 select ATAN(1) from dual -- ATAN2(n,m) 返回数字n除以m的反正切值,m不能为0 select ATAN2(2,1) from dual -- COS(n) 返回n的余弦值 select COS(-3.14159265358979) from dual -- COSH(n) 返回n的双曲余弦值 select COSH(1) from dual -- SIN(n) 返回n的正弦值 select SIN(30*3.14159265359/180) "sin(30°)" from dual -- SINH(n) 返回n的双曲正弦值 select SINH(1) from dual -- TAN(n) 返回n的正切值 select TAN(1) from dual -- TANH(n) 返回n的双曲正切值 select TANH(1) from dual -- EXP(n) 返回e的n次幂(e=2.71828183…) select EXP(1) from dual -- LN(n) 返回n的自然对数,n 不能为0 select LN(2.71828182845905) from dual -- LOG(m,n) 返回以m为底的n的对数,m不能为0 select LOG(8,64),log(3,27),log(2,1024),log(2,8) from dual -- 转换ASCII码 -- ASCII(c) 返回c的首字符在ASCII码中对应的十进制数 select ASCII('ABC') from dual -- CHR(n) 返回十进制ASCII码n对应的字符 select CHR(65) from dual -- SIGN(n) 检测n的正负.如果n小于0,则返回-1;如果n 等于0,则返回0;如果n大于0,则返回1 select SIGN(-2.3),sign(0),sign(47) from dual -- MOD(m,n) 返回m 除以n 之后的余数,如果n为0,则返回m select MOD(14,5),mod(8,2.5),mod(-64,7) from dual -- SQRT(n) 返回n的平方根,n 必须大于0 select SQRT(64) from dual -- POWER(m,n) 返回m 为底的n次幂.m和n可以为任意数字,但如果m为负数,则n必须为正数 select POWER(2,10),power(5,3),power(2,-4) from dual -- ABS(n) 返回n的绝对值 select ABS(-1) from dual -- CEIL(n) 返回大于等于n的最小整数 select CEIL(9.8),ceil(-32.85),ceil(0),ceil(5) from dual -- FLOOR(n) 返回小于等于n的最大整数 select FLOOR(9.8),floor(-32.85),floor(0),floor(5) from dual -- ROUND(n,[m]) 执行四舍五入运算.如果省略m,则四舍五入到整数位; -- 如果m是负数,则到小数点前m位;如果m 是正数,则到小数点后m位 select ROUND(1234.5678) from dual -- TRUNC(n,[m]) 执行截取数字.如果省略m,则将n的小数部分截取; -- 如果m是负数,则截取到小数点前m位;如果m是正数,则截取到小数点后m位 select TRUNC(1234.5678),TRUNC(1234.5678,2),TRUNC(1234.5678,-2) from dual -- 转换大小写 -- LOWER(c) 返回将c全部字符都小写的字符串 select LOWER(' i love you ') from dual; -- UPPER(c) 返回将c全部字符串都大写的字符串 select upper(' i love you ') from dual; -- INITCAP(c) 返回将c的每个首字符都大写,其他字符都小写的字符串. -- 单词之间以空格,控制字符和标点符号分界 select INITCAP('i love you') from dual; -- 连接字符串 -- CONCAT(c1,c2) 返回将c2添加到c1后面而形成的字符串.如果c1是NULL,那么返回c2; -- 如果c2是NULL,那么返回c1;如果c1,c2都是NULL,那么返回NULL select concat('ab','cd') ,concat('ab',null),concat(null,'cd'),concat(null,null) from dual; select 'ab'||'cd' from dual; -- 字符串处理 -- LENGTH(c) 返回c的长度,包括所有的后缀空格.如果c是NULL,则返回NULL select LENGTH(' i love you ') from dual; -- LTRIM(c1[,c2]) 去掉c1左边所包含的c2中的任何字符, -- 当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格 select LTRIM(' i love you ') from dual; -- RTRIM(c1 [,c2]) 去掉c1右边所包含的c2中的任何字符, -- 当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格 select RTRIM(' i love you ') from dual; -- TRIM([c1] c2 from c3) c1是保留字,可以取如下字符串:LEADING,TRAILING,BOTH.从c3字符串的c1处开始,删除c2字符, -- 然后返回剩余的c3字符串.c1默认为BOTH,c2默认为空格.如果c1,c2,c3中有任何一个为NULL,则返回NULL select TRIM(' i love you ') from dual; -- LPAD(c1,n[,c2]) 在c1的左边填充c2,直到字符串的总长度到达n. -- c2的默认值为空格.如果c1的长度大于n,则返回c1左边的n个字符 select LPAD('abc',5,'*'),lpad('abc',2),lpad('abc',9) from dual; -- RPAD(c1,n[,c2]) 在c1的右边填充c2,直到字符串的总长度到达n. -- c2的默认值为空格.如果c1的长度大于n,则返回c1右边的n个字符 select RPAD('abc',5,'*'),rpad('abc',2,'*'),rpad('abc',9) from dual; -- SUBSTR(c,m[,n]) 返回c的子串,其中m是子串开始的位置, -- n是子串的长度.如果m为0,则从c的首字符开始;如果m是负数,则从c的结尾开始 select SUBSTR('i love you',1,3) from dual; -- INSTR(c1,c2[,n[,m]]) 在c1中从n开始搜索c2第m次出现的位置,并返回该位置数字. -- 如果n是负数,则搜索从右向左进行,但位置数字仍然从左向右计算.n和m默认都是1 select INSTR('mississippi','i',3,3) s1,INSTR('mississippi','i',1,3) s2,INSTR('mississippi','i',-2,3) s3 from dual; -- REPLACE(c1,c2 [,c3]) 把c1中出现的c2都替换成c3,然后返回剩余的字符串.c3默认为NULL. -- 如果c3为NULL,那么所有出现c2的字符都被删除;如果c2为NULL,则将返回c1;如果c1为NULL,则将返回NULL select REPLACE('i love you','i','I') from dual; -- TRANSLATE(c1,c2,c3) 把所有在c2中出现的字符,逐一用对应在c3中出现的字符代替,然后返回被替代之后的c1字符串. -- 如果c1,c2,c3中有NULL,则返回NULL;如果c3中字符少于c2,则将在c2中不匹配的字符从c1中删除;如果c2中的字符少于c3,则以c2为主。 select TRANSLATE('i love you','i','I') s1,translate('abcdefgabcd','abc','AB') s2, translate('abcdefgabcd','ab','ABC'),translate('abcdefgabcd','ab','') s4 from dual; -- 服务器(会话)时间、时区、时间戳 select sysdate, -- 服务器时间 current_date, -- 会话时间 systimestamp, -- 服务器时间戳 current_timestamp, -- 会话时间戳 localtimestamp, -- 会话时间戳不带时区 sessiontimezone, -- 会话时区 dbtimezone oracle -- 默认时区 from dual; -- 时区偏差计算 select tz_offset(dbtimezone) chicago,tz_offset('US/Eastern') newyork,tz_offset('Europe/London') london, tz_offset('Asia/singapore') singapore from dual; -- 转换格林威治时间 select SYS_EXTRACT_UTC(current_timestamp) from dual; -- 加日期 select sysdate+1 from dual select add_months(sysdate,3) from dual; -- 如何用add_months函数实现? -- 也可以直接用,不用函数转换 select sysdate + interval '007 12:30:06' DAY TO SECOND from dual; select sysdate + interval '01-02' year to month from dual; -- 取最后一天 select LAST_DAY(sysdate) from dual; -- 取下个星期几的日期 select next_day(sysdate,'星期一'),next_day(sysdate,2) from dual; -- 取日期年月日 select extract(year from sysdate) year,extract(month from sysdate) month,extract(day from sysdate) day from dual; -- 日期差(月): select MONTHS_BETWEEN(sysdate,'31-8月-98'),MONTHS_BETWEEN(sysdate,to_date('2000.1.1','yyyy.mm.dd')) from dual; -- 日期差(日): 天: ROUND(TO_NUMBER(END_DATE - START_DATE)) 小时: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24) 分钟: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60) 秒: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60) 毫秒: ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60) 例: select round(to_date('2010-3-24', 'yyyy-mm-dd') - sysdate) from dual; -- 日期四舍五入 select round(to_date('04-07-01','yy-mm-dd'),'year') y1, round(to_date('04-06-30','yy-mm-dd'),'year') y2, round(to_date('04-07-15','yy-mm-dd'),'month') m1, round(to_date('04-07-16','yy-mm-dd'),'month') m2 from dual; -- 日期截除 select sysdate,trunc(sysdate,'year') y,trunc(sysdate,'month'),trunc(sysdate,'day') d, trunc(to_date('2000.11.9','yyyy.mm.dd'),'day'),trunc(to_date('2000.11.4','yyyy.mm.dd'),'day') from dual -- 当前时间加一分钟 select sysdate,sysdate+numtodsinterval(2,'hour'),sysdate+numtodsinterval(30,'minute') from dual; select sysdate,sysdate+numtoyminterval(2,'year'),sysdate+numtoyminterval(6,'month') from dual; select sysdate,sysdate+to_dsinterval('007 12:00:00') "+7.5 days",sysdate+to_dsinterval('030 00:00:00') "+30 days" from dual; select sysdate,sysdate+TO_YMINTERVAL('01-02') "+14 months",sysdate+TO_YMINTERVAL('00-02') "+2months" from dual -- 日期to_char的使用 select to_char(sysdate,'yyyy-mm-dd***hh-mi-ss*(*)') from dual; select to_char(sysdate,'AD') from dual; select to_char(sysdate,'AM') from dual; select to_char(sysdate,'DY') from dual; select to_char(sysdate,'DAY') from dual; select to_char(sysdate,'D') from dual; select to_char(sysdate,'DD') from dual; select to_char(sysdate,'DDD') from dual; select to_char(sysdate,'W') from dual; select to_char(sysdate,'WW') from dual; select to_char(sysdate,'MM') from dual; select to_char(sysdate,'MON') from dual; select to_char(sysdate,'MONTH') from dual; select to_char(sysdate,'YYYY') from dual; select to_char(sysdate,'YYY,YY,Y') from dual; select to_char(sysdate,'Y,YYY') from dual; select to_char(sysdate,'YEAR') from dual; select to_char(sysdate,'HH12') from dual; select to_char(sysdate,'HH24') from dual; select to_char(sysdate,'MI') from dual; select to_char(sysdate,'SS') from dual; -- 单行转换函数例子 -- 数字to_char的使用 select to_char(123.456,'9.99EEEE'),TO_CHAR(123.456,'9999.90') from dual; select to_char(123.4,'9999.99') from dual; select to_char(-123.4,'9999.99') from dual; select to_char(0.3,'9999.9') from dual; select to_char(12.3,'0009.90') from dual; select to_char(1234.5,'9,999.9') from dual; select to_char(1234.5,'9G999D9') from dual; select to_char(1234.5,'9,999.9') from dual; select to_char(1234.5,'9G999D9') from dual; select to_char(123,'$999') from dual; select to_char(123,'L999') from dual; select to_char(0.1,'FM99.99') from dual; select to_char(120000,'9.9EEEE') from dual; select to_char(123,'999MI') from dual; select to_char(123,'999PR') from dual; select to_char(123,'S999') from dual; select to_char(123,'RN') from dual; select to_char(123,'rn') from dual; select to_char(22,'XX') from dual; -- to_date select TO_DATE('04-5月-1957 13:14:15','DD-mon-yy hh24:mi:ss') from dual; -- to_number select TO_NUMBER('1234.56') from dual; -- cast函数 select cast('1234.56' as number) from dual; -- 返回字符串c对应的UNICODE字符 select unistr('\00D6'),unistr('ca\00D6on') from dual; -- 用nvl函数处理 nvl(expr1, expr2),expr1为NULL,返回expr2;不为NULL,返回expr1。注意两者的类型要一致 select empname,salary,comm,salary+nvl(comm,0) from emp where deptno=2 order by empname; -- 用nvl2函数处理 nvl2(expr1, expr2, expr3) expr1不为NULL,返回expr2; 为NULL,返回expr3。 expr2和expr3类型不同的话,expr3会转换为expr2的类型。 select empname,salary,comm,nvl2(comm,salary+comm,salary) from emp where deptno=2 order by empname; -- nullif(expr1, expr2) 相等返回NULL,不等返回expr1 select nullif(1, 1) from dual; -- 创建成绩表 create table marks( mid int not null primary key, studid int not null, writeEax int, maxEax int ) -- 添加测试数据 insert into marks values(1,2,85,67) insert into marks values(2,3,80,70) insert into marks values(3,4,79,89) insert into marks values(4,5,78,80) insert into marks values(5,6,83,78) -- 当从两个或两个以上的表中选择数据,并且在where子句中没有指定连接条件时就是笛卡儿连接 Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m -- 相等连接,从笛卡儿集合中过滤下相等条件的集合 Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m where s.studid=m.studid -- 不等连接,从笛卡儿集合中过滤掉相等条件的集合 Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m where s.studid!=m.studid -- 自我连接,将一个表复制一个结果集进行连接 Select e.deptno,e.deptname ,m.deptname 销售部 from emps e,emps m Where m.deptno=e.deptno -- 内连接查询 select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s inner join marks m on s.studid = m.studid -- 左外连接查询 select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s left join marks m on s.studid = m.studid -- 右外连接查询 select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s right join marks m on s.studid = m.studid -- 完全外连接查询 select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s full join marks m on s.studid = m.studid -- 使用union合并查询,获取结果集的并集,并自动去除重复行,以第一列的结果进行排序 Select deptno,deptname,empname,salary,job from emps Where deptno=2 Union Select deptno,deptname,empname,salary,job from emps Where job='经理'; -- 使用union all合并查询,获取结果集的并集,不去除重复行,不排序 Select deptno,deptname,empname,salary from emps Where deptno=2 Union all Select deptno,deptname,empname,salary from emps Where job='经理'; -- 使用intersect,获取结果集的交集,只会显示同时存在于结果集合中的数据,并且以第一列的结果进行排序 Select deptno,deptname,empname,salary from emps Where deptno=2 intersect Select deptno,deptname,empname,salary from emps Where job='经理' -- 使用minus,获取结果集的差集,只会显示在第一个结果集中存在, -- 但在第二个结果集中不存在的数据,并且以第一列的结果进行排序 Select deptno,deptname,empname,salary from emps Where deptno=2 minus Select deptno,deptname,empname,salary from emps Where job='经理' -- 单行子查询,显示工资最高的雇员信息 select * from emp where sal=(select max(sal) from emp); -- 多行子查询,显示部门编号为20的岗位相同的雇员信息 select * from emp where job in (select distinct job from emp where deptno = 20) and deptno = 20; -- 显示每个部门的最高工资 select distinct deptno,max(salary) maxsal from emps group by deptno; -- 显示高于部门编号为20的,所有员工工资的雇员信息 select * from emp where sal > all(select sal from emp where deptno=20); -- 显示高于部门编号为20的,任何员工工资的雇员信息 select * from emp where sal > any(select sal from emp where deptno=20); -- 显示大于部门平均薪资的员工信息 select * from emp e, (select avg(sal) avgSal, deptno from emp group by deptno) d where e.deptno = d.deptno and e.sal > d.avgSal; -- 设置只读事务 -- 事务四大特性: 原子性、一致性、隔离性、持久性 -- 事务设置三种状态 set transaction isolation level read committed set transaction read only set transaction read write -- 事务点、事务提交和回滚 savepoint sp1 -- 事务点 commit -- 事务提交 rollback -- 事务回滚