Oracle经典笔记

Oracle数据库常用命令sqlplus scott/tiger as sysdba：将scott账户当作dba登录进来alter user scott account unlock; 用户解锁|-账户解锁1.conn / as sysdba2.alter user scott account unlock;3.alter user scott identified by tiger;|-数据类型1. binary_integer：整数，主要用来计数而不是用来表示字段类型2. number：数字类型3. char：定长字符串4. varchar2：变长字符串5. date：日期6 long：长字符串，最长2GB7 boolean：布尔，可取true false nullNumber：NUMBER （precision，scale）precision表示数字中的有效位。

如果没有指定precision的话，Oracle将使用38作为精度。

scale表示数字小数点右边的位数，scale默认设置为0.P是精度，s是刻度精度代表所要存的位数，s是指小数点后保留几位例如存储1234567.89序定义number(9,2)Char和varchar2 的区别CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。

CHAR的效率比VARCHAR2的效率稍高。

|-数据库的启动sqlplus / as sysdbashutdown immediatesql> startup nomountsql> alter database mountsql> alter database open1.在Dos窗口中: rman target / nocatalog2.关闭数据库: shutdown immediate3.启动数据库到第二阶段：startup mount4.启动数据库到第三阶段：alter database open|-创建表空间create tablespace ts1 datafile 'c:\ts1.dbf' size 10M;|-新建用户并授权1.新建用户并制定密码create user user1 identified by abc;2.指定用户所在的表空间alter user user1 default tablespace ts1;3.授予连接和资源的权限grant connect,resource to user1;·显示当前用户show user;·授予最大权限grant connect,dba to user1;|-备份数据库|-远程连接数据库sqlplus sim/sim@ORCLCLCSKY101sim/sim: 用户名/密码ORCLCLCSKY101: 配置的监听|-ORACLE默认表desc 表名：查看表结构·desc emp 员工表·desc dept 部门表·desc salgrade 薪水等级表·desc dual 空表，一般用于计算，例如：select 2*3 from dual; Scott账户的默认表Emp:员工表EMPNO（员工编号）ENAME（员工姓名）JOB（职位）MGR（经理人编号）HIREDATE（入职时间）SAL（薪水）COMM（津贴）DEPTNO（部门编号）Dept:部门表Salgrade：等级表Select语句|-数学表达式·select ename,sal*12 from emp; 薪水*12，年薪·select ename,sal*12 year_sal from emp; 起别名·select ename,sal*12 "year_sal" from emp; 加双引号保持字段的大小写原型·select ename,sal*12+comm from emp; 所有人的年薪，薪水*12+津贴|-sysdate·select sysdate from dual; 当前系统时间|-字符串连接符||·SQL> select sal||'ksks' from emp; ||字符串连接符,字符串用单引号引起SAL||'KSKS'----------------------------------800ksks1600ksks1250ksks2975ksks1250ksks2850ksks2450ksks3000ksks5000ksks1500ksks1100ksks|-去掉重复信息distinct·select distinct deptno from emp;去掉deptno字段的重复信息·select distinct deptno,job from emp;去掉deptno，job组合的重复的信息|-过滤条件where·数学运算符：< > <> = not and or in·select * from emp where deptno=10; deptno（部门）等于10的员工信息·select ename,deptno from emp where deptno<>10;部门不等于10的员工姓名和部门·select ename,sal from emp where sal>=800 and sal<=1500;薪水大于等于800并且小于等于1500·select ename,sal from emp where sal>1000 and deptno=10;薪水>10并且部门=10的·select ename,sal from emp where sal>1000 or deptno=10;薪水>10or部门=10的都取出来|-空值处理NULL·select ename,sal from emp where comm is null; 奖金为空的员工的姓名和工资·select ename,sal from emp where comm is not null; 奖金不为空的员工的姓名和工资·select ename,sal from emp where sal in (800,1500,3000);薪水是800、1500、3000的人的的姓名和薪水·巧妙使用函数nvl（）select ename,sal*12+nvl(comm,0) from emp;如果comm是空值，则用0代替，如果不是null，则就是comm的值|-模糊查询Like·%:通配一个或多个字母、_：通配一个字母、\:转义字符·select ename from emp where ename like '%A%';姓名中带’A’的人·select ename from emp where ename like '_A%'; 姓名中第二个字母是A的人·select ename from emp where ename like '%\%%';查询姓名中带%的人，·select ename from emp where ename like '%$%%' escape '$'; $作为转义字符，查询出姓名中带%的人|-排序order by asc/desc·select * from dept order by deptno desc;将部门表按照部门编号降序排列·select ename,sal from emp order by sal asc;员工表按照薪水升序排列·select ename,sal from emp where deptno<>10 order by sal asc;部门不等于10的员工按照薪水升序排列·select ename,sal,deptno from emp order by deptno asc,sal desc;先按照部门编号升序排序，在部门编号相同的情况下再按照薪水降序排序·select ename,sal*12 from empwhereename not like '_A%' and sal>100order by empno desc;取出来emp的名字、年薪，名字的第二个字母不包含A并且薪水大于100按照员工编号降序排列|-函数|-lower(e):转换成小写substr(字段，n，m)将字段从n开始截取，截取到m（包含第m和第n）|-ascii(a)：求a的ascii码chr(97)：将ascii转换为字符|-round(m,s):四舍五入到s位·select lower(ename) from emp; 将ename转换为小写·select ename from emp where lower(ename) like '_a%';查询小写的姓名包含a的数据·select substr(ename,1,3) from emp;将姓名从第一个开始截取，截取到第三个（包含第一个）·select ascii('a') from dual;求a的ascii码·select chr(97) from dual; 将ascii转换为字符，本条语句结果为a·select round(23.567) from dual;四舍五入到个位。

结果为24·select round(23.567,2) from dual; 四舍五入到小数点后两位，结果为23.57·select round(23.567,-1) from dual;四舍五入到小数点前1位，结果为20|-to_char(字段a，格式)；将字段a转换为想要的格式显示出来·select to_char(sal,'$99,999.9999') from emp;·select to_char(sal,'L99,999.9999') from emp;·select to_char(sal,'L00,000.0000') from emp; L：本地货币|-to_date 将字符串转换为日期·select ename,hiredate from emp where hiredate>to_date('1981-2-20 12:34:56','YYYY-MM-DD HH-MI-SS');将1981-2-20 12:34:56转换为日期然后与日期类型的hiredate做比较|-to_number 将字符串转换为日期·select ename,sal from emp where sal>to_number('$888.88','$999.99');将‘$888.88’转换为数字类型然后才能与sal比较|-组函数max()、min()、avg()、count() 、sum()，把N条数据作为输入，最后产生一个输出select max(sal) from emp; 求最高工资select sum(sal) from emp; 求薪水总和select round(avg(sal),2) from emp; 求平均工资并四舍五入并保留小数点后两位select to_char(avg(sal),'9999.99') from emp;同上select count(ename) from emp where deptno=10;部门编号是10的有名字的人有多少select count(distinct deptno) from emp;去除重复的部门编号后显出部门数|-日期处理·select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;·select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;·select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; 24表示法|-分组group by、having·select avg(sal) from emp group by deptno; 每个部门的平均工资先按照部门进行分组，然后各组内部再按工资求平均数·select deptno,max(sal) from emp group by deptno;按部门分组，组内部分别求最大工资·select avg(sal) from emp group by deptno having avg(sal)>2000;按照部门进行分组，求各平均工资大于2000的薪水。