文章目录

• oracle常用笔记（一）
• 1. 创建表空间
• 2. 创建用户
• 3. 操作表
• 4. 序列
• 5. SQL函数
• 1.字符函数
• 2. 数字函数
• 3. 转换函数
• 4. 日期函数
• 5. 通用函数
• 6. 聚合函数

oracle常用笔记（一）

1. 创建表空间

create tablespace itcast  --创建表空间datafile \'c:\\itcast.dbf\' --表空间对应的物理文件地址size 100m  --表空间大小autoextend on  --自动增长 ，当表空间存储都占满时，自动增长next 10m --自动增长的大小

2. 创建用户

create user itheima  --用户名identified by itheima  --密码default tablespace itheima;  --归属的表空间grant dba to itcastuser --授予权限（CONNECT 基本角色 ，RESOURCE 开发，dba 超管）

3. 操作表

ALTER TABLE TABNAME RENAME TO TABNAME2;--修改表名ALTER TABLE TABNAME RENAME COLUMN COLNAME TO COLNAME2; --修改表列名ALTER TABLE TABNAME MODIFY COLNAME NUMBER(20); --修改字段类型ALTER TABLE TABNAME ADD COLNAME VARCHAR2(40); --添加表列ALTER TABLE TABNAME DROP COLUMN COLNAME; --删除表列ALTER TABLE TABNAME ADD (COLNAME1 VARCHAR2(10),COLNAME2 NUMBER); --增加多列ALTER TABLE TABNAME MODIFY (COLNAME1 VARCHAR2(20),COLNAME2 VARCHAR2(20)); --修改多列ALTER TABLE TABNAME DROP (COLNAME1,COLNAME2); --删除多列COMMENT ON TABLE TABNAME IS \'表名注释\';--表名注释COMMENT ON COLUMN TABNAME.COLNAME IS \'列名注释\';--列注释delete from TABLE TABNAME; -- 删除表中全部记录drop from TABLE TABNAME; --删除表truncate TABLE TABNAME; --先删除表再重新创建表，在数据量较大和带有索引时效率高

4. 序列

CREATE SEQUENCE seqpersonid --创建序列select seqpersonid.nextval from dual; --获取下一个序列--seqpersonid.nextval 可在插入自增语法中使用select seqpersonid.currval from dual; --获取当前序列--dual:虚表，补全语法，没有意义

5. SQL函数

• 需要select或者having才能返回结果，在where中是不返回结果的

1.字符函数

//将第一个字母转成大写initcap(n)//从m位置开始在x中查找字符串y出现的位置，n是出现次数instr(x,y,m,n)//求字符串长度length(n)//将字符串各字符转换成小写lower(x)//大写......upper(x)//在字符串x左边补齐字符y（缺省则补空格），得到总长为n的字符串lpad(x,n,y)//在字符串x右边......rpad(x,n,y)//去掉左边\\右边\\两边去掉指定字符select trim(leading \'*\' from \'**sbash**\') from dual;//x左边去掉指定字符y，默认去空格ltrim(x,y)//x右边......rtrim(x,y)//如果x不是null，则返回x，否则返回ynul(x,y)//如果x不是null，则返回y，否则返回znul2(x,y,z)//如果x是数字，则返回x，否则返回ynanvl(x,y)//x中替换y为zreplace(x,y,z)//从字符串x中的m开始取长度为n的子串，n缺省时取到结尾substr(x,m,n)//连接字符串concat(m,n)

2. 数字函数

round(5.89,-1)       // 10 保留十位数round(5.89)          // 6 保留个位数round(5.89,1)        // 5.9round(15.89,-1)      // 20round(15.89,-2)      // 0--round：四舍五入trunc(5.89,-1)       // 0trunc(5.89)          // 5trunc(5.89,1)        // 5.8--trunc:截断

3. 转换函数

//转换为日期select to_date(\'2018-06-18\',\'yyyy-MM-dd\') from dual//fm yyyy-MM-dd  去日期中的0//转为为二进制数bin_to_num(n)//2018-06-18 09:35:45select to_char(sysdate,\'yyyy-MM-dd HH24:mi:ss\') from dual

4. 日期函数

//获取系统时间select sysdate from dual        //18-6月 -18//求下个月最后一天select last_day(add_mon ths(sysdate,1)) from dual//从x开始，下一个第n天的日期（从星期天开始算）select next_day(sysdate,n) from dual//-.03225806select months_between(sysdate,sysdate+1) from dual//18-6月 -18     01-1月 -18     01-7月 -18select round(sysdate),round(sysdate,\'yyyy\'),round(sysdate,\'MM\') from dual//18-6月 -18     01-1月 -18     01-6月 -18select trunc(sysdate),trunc(sysdate,\'yyyy\'),trunc(sysdate,\'MM\') from dual//2018-06-18 09:35:45select to_char(sysdate,\'yyyy-MM-dd HH24:mi:ss\') from dual

5. 通用函数

1. 空值处理 nvl

select e.sal*12+nvl(e.comm,0) from emp e;

1. Decode 函数

   [ol]case when （Mysql/Oracle同用）

select e.ename,case e.enamewhen \'aaa\' then \'a\'  --条件判断when \'bbb\' then \'b\'else \'c\'    -- 不写else时，不满足以上条件则为nullfrom emp e;select e.sal,case 					--范围判断case不写when e.sal>10 then \'a\'  --条件判断when e.sal>5 then \'b\'else \'c\'    -- 不写else时，不满足以上条件则为nullfrom emp e;

[/ol]

select e.ename,decode(e.ename,  --列名\'aaa\',\'a\',    --条件判断\'bbb\',\'b\',\'c\') “列名” --c为else条件 --oracle中除了起别名，都用单引号，或者不加from emp e;

6. 聚合函数

//count，返回非空数据的条数select count(1) from A  // 等于count(*)，1查询的是主键//min、max求最值select min(列名),max(列名) from A//sum求和，avg求均值select sum(列名),avg(列名) from A

6. 分组查询

select e.deptno , avg(e.sal)from emp egroup by e.deptno;select e.deptno , avg(e.sal)from emp ewhere e.sal>50  --筛选e.sal>50的值group by e.deptnohaving avg(e.sal)>100;  --筛选e.sal平均值>100的e.deptno

注意：

1. 如果使用分组函数，SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其他字段。

2. 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值

3. 分组查询时，需要给查询的值加条件，不能使用where，要使用having，条件不能使用别名

4. where是过滤分组前的数据，having是过滤分组后的数据

   表现形式：where在group by 之前，having在group by之后

7. 多表查询

1. 等值连接

select * from emp e , dept d where e,deptno = d.deptno;

1. 内连接

select * from emp e inner join dept d on e.deptno = d.deptno;

1. 外连接(左右外连接)

select * from emp e right join dept d on e,deptno = d.deptno;select * from dept d left join emp e on e,deptno = d.deptno;--左右外连接，左外链就是要查询的全部数据的条件放左边，反之亦然。

• oracle专属外连接

select * from emp e , dept d where e.deptno(+) = d.deptno;--(+)显示的是等号对面条件的全部数据

1. 自连接

select e1.ename ,e2.ename from emp e1 ,emp e2 where e1.mgr=e2.empno

8. 子查询

1. 单行子查询

//where中————查询年龄 小于 平均年龄的学生的学号和姓名select id,name from student where age< (select avg(age) from student);//语法错误，聚合函数应该在having中做比较select id,name from student where age<avg(age);//having中————查询课程为‘c002’的平均成绩 大于 所有学生的平均分select cno,avg(score) from sc where cno=\'c002\' group by cnohaving avg(score)>(select avg(score) from sc);//from中————查询哪些员工的工资 高于 所任职位的平均工资select ename,sal,avgjob from emp a,(select avg(sal),job from emp group by job) bwhere a.job=b.job and a.sal>b.avgjob;//错误写法，子查询返回的是一个表select ename，工资 from 表名 where 工资>（select 职位，avg(工资) from 表名 group by 职位）;

9. Rownum与分页查询

ROWNUM:表示行号，当进行select查询时，每查询出一条记录就会在改行前加一个行号，行号从1开始，依次递增，不能跳序。实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

• 分页查询第一种写法

select *from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5;

• 第二种

select *from (select rownum r ,emp.* from emp) bwhere b.r >5 and b.r

因为为rownum不支持大于号，只支持小于号，所以实现分页就要使用子查询