韩顺平oracle笔记

disc ;退出命令conn连接命令passw[] 用户修改用户密码需要sys/system登录show user 显示当前用户exit断开与数据库的连接start和@ 运行sql脚本如start d:\a.sql;edit 编辑指定的文本edit d:\a.sql;spool 可以将sql*plus屏幕上的内容输出到指定文件中去spool d:\a.sql;spool off;控制输出的各种格式set show 如果希望永久保存相关配置可以去修改glogin.sql linesize 设置显示行的宽度,默认是80个字符show linesizeset linesize 90pagesize 设置每页显示的行数目,默认是14，用法和linesiz一样系统权限用户对数据库的相关权限connect,dba ,resource;对象权限用户对其他用户的数据对象的操作的权限(如select ,insert update ,delete, all) 数据对象比如表,视图,过程角色分为自定义角色和预定义角色如果xiaomign也可以建表那么需要把resource权限赋予xiaoming除外权限有resource,dba是最高的权限,还有connect登录权限创建xiaoming用户,修改用户密码只需要把create改成alter即可create user xiaoming identified by xiaoming ;create user xiaoming identified by xiaohong ;grant connect to xiaoming ;//赋予权限revoke connect to xiaoming;//收回权限希望小明可以去查询scott用户的emp表grant select on emp to xiaoming;希望xiaoming用户可以去修改empbiaogrant update on emp to xiaoming;如果是对象权限就在后面加上with grant optiongrant all on emp to xiaoming with grant option;xiaoming把emp表的查询权限给xiaohonggrant select on scott.emp to xiaominge;如果是系统权限grant connect to xiaoming with admin option;意味着xiaoming可以将这种权限继续传递一旦收回权限,则赋予权限的下级也被收回,诛九族式的;账户锁定使用profile管理文件创建profile文件create profile chen limit failed_login_attempts 3password_lock_time 2;alter user xiaoming profile chen;alter user xiaoming account unlock;//解锁创建个profile文件,要求该用户每隔10天修改自己的登录密码,宽限期市两天create profile myprofile limit password_life_time 10 password_grace_time 2;alter user xiaoming profile myprofile;口令历史如果希望用户在修改密码时,不能使用以前使用过的密码,可以使用口令历史当发现旧密码一样时提示用户重新输入create profile myprfile limit password_life_time 10password_grace_time 2 password_reuse_time 10;注意password_reuse_time 是指口令可以重用的时间是10天后就可以重用删除profile文件drop profile chen[cascade];----------------------------------------------表名必须以字母开头,长度不能超过30个字符,不能使用oracle保留字char 定长最大两千字符,varchar2(10) 变长最大是四千字符clob 字符型对象最大4Gnumber(3,2) 一共有3位有效数字2位小数number(2)表示一个两位整数number范围10的-38次方到10的38次方date 时间类型timestamp 较为精确的时间图片blob类型4G 可以存放图片声音(通常处于安全的考虑会把图片声音放入数据库中) 建表create table student(xh number(4),xm varchar2(20),sex char(2),birthday date,sal number(4,2)--讲学);insert into student values(001,'admin','男',01-03月-1990',4000.50);create table class(classId number(2),cname varchar2(20));-----------------------------------------------------------添加一个字段alter table student add(classId number(2));修改字段长度alter table student modify (xm varchar2(19));修改字段类型或名字alter table student modify (xm char(20));删除一个字段alter table student drop column sal;修改表的名字rename student to stu;删除表drop table student;修改日期的默认格式alter session set nls_date_format='yyyy-mm-dd';insert into student values(2,'小红','男','1990-04-10',3260.50,11);insert into student values(1,'小陈','女','1980-07-10',3310.50,12);insert into student values(3,'小龙','男','null',2330.50,11);设置保存点savepoint a;delete from student;删除所有记录,表的结构还在,记录删除了,写日志,可以恢复的,速度慢drop table student ; 删除表的结构和数据truncate table student; 删除表中的所有记录,表的结构还在,不写日志无法找回删除的记录,速度快delete from student where xh=1;删除一条记录rollback to a;clear;清屏命令--------------------8讲查询mgr字段是指员工上级的编号; comm是指奖金; loc 指部门所在地点desc dept查看表结构取消重复行select distinct deptno,job from emp;查询SMITH(注意名字区分大小写)的薪水,工作,所在部门select ename,sal,job,dname fromemp,dept where emp.deptno=dept.deptno and ename='SMITH';如何显示雇员的年工资(但此种方法会出现年薪空值) select ename ,12*sal+12*comm "年薪" from emp;(必须是"" 不能'')如何处理null值select ename,nvl(sal,0)*13+sal*13 "年薪" from emp;如何显示工资高于三千的员工select * from emp where sal>3000;显示1980-01-01以后入职的员工select * from emp where hiredate>'1982-01-01';工资高于2000低于2500的员工select * from emp where sal>2000 and sal<2500;%表示多个字符_表示任意单个字符显示首字母为S的员工select * from emp where ename like'S%';显示第三个字符时大写O的select * from emp where ename like'__O%';显示雇员编号是123,3445,800的雇员信息select * from emp where empno in(7839,7876,7934);显示没有上级的员工select * from emp where mgr is null;查询工资高于500或者是岗位是MANAGER 的雇员,同时还有满足他们的姓名首字母为大写Jselect * from emp where (sal>500 or job='MANAGER') and ename like'J%';工资从高到低排列select * from emp order by sal desc ;默认是asc从低到高部门号升序而雇员工资降序排列select * from emp order by deptno ,sal desc;使用列的别名排序select ename,sal*12+nvl(com,0)*12 "年薪" from emp order by "年薪" desc; 复杂查询:显示所有员工的平均工资和工资总和select sum(sal) as "工资总和" ,avg(sal) as "平均工资" from emp;显示所有员工中最高工资和最低工资select max(sal) as "最高工资",min(sal) as "最低工资"from emp ;显示所有员工中最高工资的员工姓名,工资select ename, sal from emp where sal=(select max(sal) from emp);计算共有多少员工select count(*) from emp;显示工资高于平均工资的员工信息select * from emp where sal>(select avg(sal) from emp); group by 用于对查询的结果进行分组统计having 子句用于显示分组显示结果显示每个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;显示每个部门的每种岗位的平均工资和最低工资及最高工资select max(sal), avg(sal),min(sal)from emp group by job;显示平均工资低于2000的部门号和它的平均工资select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>200分组函数只能出现在选择列表,having ,order by子句中;如果在select 语句中同时包含有group by,having,order by,那么他们的顺序是group by,having,order by;在选择列种如果有列,表达式,和分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错;案例:显示雇员名,工资及所在部门的名字并按部门排序按平均工资升序select deptno ,avg(sal),max(sal),min(sal) from emp groupby deptno having avg(sal)>2000 order by avg(sal);笛卡尔集:规定多表查询的条件是至少不能少于表的个数减一10讲显示部门号位10的部门名,员工名,和工资select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno anda1.deptno=10;显示各个员工的姓名,工资,及其工资的级别select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal显示雇员名工资及所在部门的名字,并按部门排序select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a2.deptno;自连接:在同一张表的连接查询显示某个员工的上级领导的姓名select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno ;查询和部门10的工作相同的雇员的名字,岗位,工资,部门号select * from emp where deptno=(select deptno from emp where ename='SMITH');单行子查询,指返回一行数据的子查询语句多行子查询指返回多行数据的子查询查询和部门10号的工作相同的雇员的信息select * from emp where job in (select distinct job from emp where deptno=10);显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);或者(这种方法的效率要高)select * from emp where sal>(select max(sal) from emp wheredeptno=30);在多行子查询中使用any操作符显示工资比部门30的任意一个员工的工资高的姓名工资和部门号;select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);或select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);多列子查询:单行子查询是指查询只返回单列,单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列的数据的子查询语句查询与SMITH的部门和岗位完成相同的所有雇员;select * from emp where (deptno,job)=(select deptno,job from emp whereename='SMITH') ;-------------------显示高于自己部门平均工资的员工的信息1,先查询出各个部门的平均工资和部门号select deptno,avg(sal) mysal from emp group by deptno;2把1查出的看做一张子表select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2 ,(select deptno,avg(sal) mysal from emp group by deptno) a1where a2.deptno=a1.deptno and a2.sal>a1.mysal;--------------------分页一共有三种方式(按rowid分其效率最高但是不易理解,按分析函数来分,效率太差,按rownum来分,还行)1。