grant select[update|delete|insert|all] on emp to xiaohong
conn xiaohong/m123 select * from scott.emp 第六讲 1.创建用户tea ,stu 并给这两个用户resource,connect角色 conn system/yzj; create user tea identified by tea; grant resource to tea; grant connect to tea; create user stu identified by stu; grant resource to stu; grant connect to stu; 2.使用scott用户把对emp表的select权限给tea conn scott/tiger; grant select on emp to tea; 使用tea查询scott的emp表 conn scott/tiger; select * from scott.emp; 使用scott用户把对emp表的所有权限赋给tea conn scott/tiger; grant all on emp to tea; 使用tea更新/删除/插入 scott的emp表 conn tea/tea; update scott.emp set job='Teacher' where job='&job'; delete from scott.emp where job='&job'; insert into scott.emp values(8888,'FORD','Teache','7698','08-9月 -81',1500,300,20); 使用scott收回权限 conn scott/tiger; revoke select on scott.emp from tea; revoke all on scott.emp from tea; 想办法将让tea把自己拥有的对scott.emp的权限转给stu scott->tea->stu [权限转移.] conn scott/tiger; grant all on scott.emp to tea with grant option; //with grant option 表示得到权限的用户,可以把权限继续分配 //with admin option 如果是系统权限,则带with admin option conn tea/tea; grant select on scott.emp to stu; 使用stu查询scott用户的emp表 conn stu/stu; select * from scott.emp; 使用tea收回给stu的权限 conn tea/tea; revoke select on scott.emp from stu; profile是口令限制，资源限制的命令集合,当建立数据时，oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户 需求：只允许某个用户，最多尝试登录三次，如果三次没有成功，则锁定两天，两天后才能重新登录 基本语法： create profile 文件名 limit failed_login_attempts 3 password_lock_time 2; alter user 用户名 profile 文件名; 给账户用户解锁 alter user 用户名 account unlock; 终止命令：为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作 需求：说一个账号密码,最多10,宽限期为2天，到时必须设置新的密码 creat profile myprofile limit password_life_time 10 password_grace_time 2 继续加限制条件; alter user tea profile myprofile; 口令历史 如果希望用户在修改密码时，不能使用以前使用过的密码,可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码 进行比较,当发现新旧密码一样时，就提示用户重新输入密码 create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 1; alter user tea profile password_history; 删除profile drop profile profile文件名 cmd中启动oracle的监听服务 lsnrctl start 启动数据库实例 oradim -startup -sid orclxuzhu 查看电脑什么时候安装的操作系统,网卡信息 systeminfo 第七讲 oracle认证方式 conn system/xxx; conn system/xxx as sysdba; show user; user为"sys" conn xxx/xxx as sysdba; show user; user为"sys" 不用认证用户名和密码 用户就是sys oracle用户验证机制 普通用户,默认是以数据库方式验证,比如：conn scott/xx 特权用户(sys),默认是以操作系统认证(即：只要当前这个用户是在ora_dba组中,则可以通过),比如：conn system/orclxuzhu as sysdba dbms 一看到 as sysdba 则认为要以特权用户登录,前面的用户名和密码不看,登录后,自动切换成sys用户<=>conn sys/orclxuzhu 如果当前用户不在ora_dba组,这时再使用数据库验证方式 如果输入用户名和密码是对的 还是可以连接上的 conn sys/orclxuzhu as sysdba; 我们可以通过修改sqlnet.ora文件,让特权用户登录的时候,直接使用数据库验证 搜索到sqlnet.ora文件,修改 SQLNET.AUTHENTICATION_SERVICES=(NTS) 如何找回管理员密码 搜索名为PWD数据库名.ora文件 数据库实例名是根据实际情况定,比如orclxuzhu 拷贝一份密码文件 生成新的密码文件,在dos控制台下输入命令 orapwd file=E:\myoracle\oracle\database\PWDorclxuzh.ora password=xuzhu entries=10 entries 表示登录sys最多用户 如果希望新的密码生效,则需要重新启动数据库实例 第八讲 在运行里面输入 sqlplus 这个是dos窗口 sqlplusw 这个是应用窗口 create table table_name{ 列名 列的数据类型 ...... } create table users( id number, name varchar2(32), password varchar2(32), birthday date); char(size) 定长 存放字符串最大2000个字符 1个a1个字符 1个汉字2个字符 原因：1个空间是1个字节来表示的 举例说明 create table test1(name char(32)); 这样在name这列,最多只能放入32个字符,如果超过,就报错,如果不够'abc',则用空格补全 varchar2 变长 存放字符串最大4000个字符 1个a1个字符 1个汉字2个字符 原因：1个空间是1个字节来表示的 select name,dump(name) from test1; 如果我们的数据长度是固定,比如商品编号(8),则应当使用char来存放,因为这样存取速度快,如果存放数据的长度是变化,则使用varchar2 nchar 定长 编码方式是unicode 最大字符2000个 1个a1个字符 1个汉字1个字符 原因：1个空间是2个字节来表示的 nvarchar2 变长 编码方式是unicode 最大字符4000个 1个a1个字符 1个汉字1个字符 原因：1个空间是2个字节来表示的 clob 字符型大对象 变长 最大8tb blob 变长 最大8tb 说明,我们实际开发中很少把文件存放数据库(效率问题),实际上我们一般记录文件的有一个路径(http://www.baidu.com/image/3.jpg d:/file/a.jpg) 然后通过io 网络来操作 如果我们要求对文件安全性,可以考虑放入数据库 第九讲 number是变长的 number可以存放整数,也可以存放小数 number(p,s) p为整数位,s为小数位 范围：1<=p<=38,-84<=s<=127 保存数据范围：-1.0e-130<=number value <1.0e+126 -1.0e-130(科学计数法) :就是-1.0乘以10的-130次方 保存在机器内部的范围：1~22bytes 举例说明： 有效位：从左到右,第一个非0数就是第一个有效位 date日期类型 用于表示时间(年/月/日/时/分/秒) insert into test8 values('11-11月-11')； oracle日期的默认格式是'dd-mm-yyyy';如果我们希望使用自己习惯的日期添加,也可以,但是需要借助oracle函数 第十讲 使用alter table 语句添加 修改 或删除列的语法 alter table tablename add (columnname datatype); alter table tablename modify (columnname datatype); alter table tablename drop column columnname; alter table tablename drop column (columnname1,columnname2); 修改表的名称：rename 表名 to 新表名 删除表 drop table tablename; 增删改查 create table test10(id number); insert into test10 (id) values('123'); 这是可以的 dbms视图把数据转化成对应的字段类型 字符和日期类型数据应包含在单引号中 oracle 会把‘’=Null create table test14(name varchar2(64),age number); insert into test14 (name,age) values("shunping",null);正确 insert into test14 (name) values("abc");正确 age就是null 如果给表的每列都添加值,则可以不带列名 update 如果没有where语句 就是更新所有的行 第十一讲 update students set fellowship=10 where fellowship is null; delete 如果不使用where子句,将删除表中所有数据 delete from 表名 删除表中所有数据,表结构还在,写日志,可以恢复的,速度慢 delete语句不能删除某一列的值,如果要删除某列的值,则需要使用update语句 truncate table 表名： 删除表中所有数据,表结构还在,不写日志,无法找回删除的记录,速度快 savepoint aa; delete from students; rollback to aa; select * from students; 查看表结构 desc 表名 去重复行 返回的数据完全一样才是重复行 select distinct deptno,job from emp order by deptno; 数据库的内容，字段 表名都不区分大小写 sqlserver2000 不区分大小写 oracle 区分大小写 虚表 select abs(-100) from dual; select abs(sal) from emp; select sal*sal from emp; select sal*abs(sal) from emp; select ename,sal*13+comm*13 from emp; select ename,sal*13+NVL(comm,0)*13 from emp; oracle 不让用单引号 select ename,sal*13+NVL(comm,0)*13 "年薪" from emp; select ename,sal*13+NVL(comm,0)*13 as 年薪 from emp; 说明：NVL函数是oracle提供的,用于处理数据null的问题 nvl(comm,0):如果comm为空null,则返回0,如果不为空,则返回本身的值 ||:在查询时如果希望把多列拼接起来作为一列返回可以使用|| select ename || '是一个' || job from emp; select ename || ' 是一个 ' || job from emp; 第十二讲 我们希望删除用户,同时保留该用户的数据对象,怎么处理 锁定用户 alter user scott account lock; 虽然锁定了用户 但是scott.emp在system 依然使用它的对象 select * from scott.emp; 用户解锁 alter user scott account unlock; select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-1-1'; select * from emp where to_char(hiredate,'yyyy')='1980'; select * from emp where to_char(hiredate,'mm')='4'; select * from emp where sal>=2000 and sal<=2500; select * from emp where sal between 2000 and 2500; %表示任意0到多个字符,_表示任意单个字符 如何显示首字母为S的员工姓名和工资 select ename,sal from emp where ename like 'S%'; 如何显示第三个字符为大写O的所有的员工姓名和工资 select ename,sal from emp where ename like '__O%'; 第十三讲 如何显示empno为123,345,800的雇员情况 select * from emp where empno in(123,345,800); 如何显示没有上级的雇员的情况 select * from emp where mgr is null; 不写asc也行 默认是升序 select * from emp order by sal asc; 降序 select * from emp order by sal desc; select * from emp order by deptno,hiredate desc; select ename,sal*13+nvl(comm,0)*13 年薪 from emp order by 年薪； 下面这个也能排序 select ename,sal*13+nvl(comm,0)*13 年薪 from emp order by sal*13+nvl(comm,0)*13； select max(sal) from emp; select min(sal) from emp; 只能返回一个值 不确定是哪个人的 select max(sal*13+nvl(comm,0)*13) 年工资 from emp select max(sal),min(sal) from emp; avg会忽略sal为空的人 select avg(sal),sum(sal) from emp; select sum(comm)/count(*) from emp; 总的员工数 select count(*) from emp; comm不为空的字段 select count(comm) from emp; select ename,job from emp where sal=(select max(sal) from emp); select * from emp where sal>(select avg(sal) from emp); select avg(sal),max(sal),deptno from emp group by deptno; 显示每个部门的每种岗位的平均工资和最低工资 select avg(sal),max(sal),deptno,job from emp group by deptno,job order by deptno; select avg(sal),deptno from emp group by deptno; select avg(sal),deptno from emp group by deptno having avg(sal)<2000; select avg(sal),deptno from emp group by deptno having avg(sal)>100 order by avg(sal); 第十四讲 多表查询 这下面两个查询语句效果一样 select distinct * from emp,dept; select * from emp,dept; 跟上面的条数一样但是数据呈现有点不一样 select * from dept,emp; 显示各个员工的姓名,工资,及其工资级别 select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal; select * from emp where empno=(select mgr from emp where ename='FORD'); 显示各员工的姓名和他的上级领导姓名 select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno; select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno(+);左外连 select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename !='SMITH'; select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename <>'SMITH'; 第十五讲 如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号 select ename,job,sal,deptno 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 ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno =30); 如何显示工资比部门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 where emp where ename='SMITH'); select * from emp where deptno=(select deptnofrom where emp where ename='SMITH') and job=(select job where emp where ename='SMITH');不好 第十六讲 在from子句中使用子查询 如何显示高于自己部门平均工资的员工的信息 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用 各个部门的平均工资是多少？ select avg(sal),deptno from emp group by deptno; 把上面查询的结果当做一个临时表对待 select t2.ename,t2.sal,t1.myavg ,t2.deptno from emp t1,(select avg(sal) myavg,deptno from emp group by deptno) t1 where t2.deptno=t1.deptno and t2.sal>t1.myavg; 如何显示各个部门最高工资的员工信息 select t2.ename,t2.sal,t1.mymax ,t2.deptno from emp t1,(select max(sal) mymax,deptno from emp group by deptno) t1 where t2.deptno=t1.deptno and t2.sal=t1.mymax; 显示每个部门的信息(编号,名称)和人员数量 先查询出各个部门有多少人 select deptno,count(*) from emp group by deptno; 部门相同的只显示一条数据 select deptno from emp group by deptno; 分组之后部门的数量 select count(*) from emp group by deptno; 部门表中有个一个部门 但是没有人 结果没有显示那个没有人的部门信息 select t1.dname,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno; 把没有人的那个部门也显示出来了 select t1.dname,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno(+); 分页查询 分页查询是我们学习任何数据库,必须掌握的一个要点 mysql: select * from 表名 where 条件 limit 从第几条取,取几条 sqlserver: select top 4 * from 表名 where id not in(select top 4 id from 表名 where 条件); 排除前4条,再取4条,这个案例实际上是取出5-8 select emp.*,rownum from emp; oracle: select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4; 说明：上面的这个sql是oracle数据库效率比较高的查询方法,在百万级都可以及时响应 create table mytest as select empno,ename,sal,comm,deptno from emp; 自我复制 insert into mytest (empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from emp; 如果我们需要针对不同的情况,分页,请在最内层进行处理,包括多表 第十七讲 显示每个部门每个岗位的平均工资,每个部门的平均工资,每个岗位的平均工资 ?? select avg(sal),deptno,job from emp group by cube(deptno,job) inner join select 列名.. from 表1 inner join 表二 on 条件.. left join的另外一种写法 select stu.name,stu.id,exam.grade from stu,exam where stu.id = exam.id(+); right join的另外一种写法 select stu.name,stu.id,exam.grade from stu,exam where stu.id(+)= exam.id; full out join select stu.name,stu.id,exam.grade from stu full outer join exam on stu.id = exam.id; 第十八讲 select ename,job,hiredate from emp where hiredate between '01-2月-81' and '01-5月-81' order by hiredate; select ename,job,hiredate from emp where to_char(hiredate,'yyyy-mm-dd') between '1981-02-01' and '1981-05-01' order by hiredate; 小结： 1.分组函数(avg...)只能出现在选择列表,having、order by 子句中 2.如果在select 语句中同事包含有group by，having，order by 那么他们的顺序是group by，having，order by 3.在选择列中如果有列,表达式,分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错 下面这句错误 order by 没有意义
select ename,job,sal from emp where job in(select job from emp where depto = 30 order by job)
第十九讲 创建数据库有两种方式： 通过oracle提供的向导工具 推荐 我们可以用手工步骤直接创建 client： oracle orclxuzhu 方案(schema) scott 数据对象(table 过程 触发器 view 序列) sqlplus dbms 方案(schema) system 也有自己的数据对象 sqlplusw pl/sql developer 网页 企业管理器 数据库orclxuzhu2 口令 orclxuzhu2 当我们创建完一个新的数据库实例后,在服务中就会有两个新的服务创建,这时,你根据实际需要去启动相应的数据库实例 在同一台机器,可以同时启动多个数据库实例,我们在登录或链接的时候,需要指定主机字符串 java连接oracle有两种方式 1.jdbc直连 特别说明：如果使用jdbc连接,需要启动监听服务 A；sun公司提供了一套对数据库操作接口/类 放在java.sql包 B；oracle公司把接口实现就打了一个包 ojdbc14.jar 提供给程序员使用 2.jdbc-odbc桥连接 第二十讲 dml 语句(数据操作语言)[insert,update,delete] ddl 语句(数据定义语音) create table drop table dql 语句(数据查询语言) select dcl 语句(数据控制语言) commit rollback //完成查询 ps.executeQuery(); //插入 删除 更新 ps.executeUpdate(); 第二十一讲 jdbc-odbc桥连接 步骤如下： (1)配置数据源 (2)1521的监听实际上是可以不起的 Class.forName("sun.jdbc.odbc.jdbcOdbcDriver");//实际上是sun公司提供的 url="jdbc:odbc:hsporc"; hsporc是数据源名称 桥连和直连 实际上改下driver url 就可以互相使用 insert into emp(empno,hiredate) values(2222,'1988-11-11')报错 因为默认格式是(日-月-年)而且年是两位的,怎么解决 我们可以使用函数 TO_DATE(string,'format') 把字符串转成指定格式的日期 insert into emp(empno,hiredate) values(2222,to_date('1988-11-11','yyyy-mm-dd'));
