oracle基础总结

1.    用户管理：

a)      create user 用户名 identified by密码 defaulttablespace表空间名temporary tablespace 临时表空间名 ACCOUNT UNLOCK;
GRANT CONNECT TO 用户名; 连接数据库
GRANT RESOURCE TO 用户名;
grant alter,delete,update,insert,select on 表名 to 用户名;

revoke alter,delete,update,insert,select on表名 from用户名;

如果要让赋权后的得用户也可以把权限付给别人就在赋权得语句中加入，with grant option(对象权限增、删、改、查等操作);with  admin option(系统权限连接connect、资源利用resouce 等);

注意：如果回收初始赋权用户得权限，那么通过他给其他用户的权限也会被回收；

     有时会出现用户被锁，使用alter user用户名account unlock；

 

Oracle单表查询

数据类型：char 定长最大2000字符；

                      varchar2(20) 最大长度4000字符；

                      clob字符型的大对象，最大为4G

                      number(m,n)  表示m位有效数，n表示小数位数；

                      date日期类型包括年月日时分秒默认格式’DD-MON-YY’

                      alertsession set nls_date_format=’yyyy-mm-dd’ //修改日期的格式

                      timestamp这是date的扩展，比date更加具体

                      blob二进制数据，用来存储图片、视屏、声音等4G

创建表：

               createtable stu( --表名

               connumber(4) ,--学号

               numevarchar2(20),--姓名

               sexchar(2),--性别

               birthdaydate,--出生日期 );

修改表结构：

               alert  table 表名 add(列)；//增加列

               alerttable 表名 modify (列)；//修改列

               alerttable 表名  drop  column 列;//删除列

               rename 表名 to 新表名;//修改表名

               droptable 表名;//删除表

添加数据：

               instert  into 表名 values (1, ‘李康’,’ 男’,’24-11月-1991’);

instert into 表(列,列…)values (值,值,值…)

插入空置

        instert into 表  values (null,null…)

is null /is not null 查询空

修改数据；

              update 表名 set 具体列 where 条件

删除数据：

delate from 表名;

delete 删除所有纪录，表的结构还在，写日志，可以利用rollback to(回滚点)回滚命令恢复，速度慢

truncate table   表名;

truncate  删除表的所有数据，表结构不变，不写日志，不可回复

drop  table  表；drop 输出表喝数据；

savepoint  回滚点；

 

查询数据：

desc 表名; --查询表结构

       select 列名1,列2,列3 from 表名；

        count 统计记录条数

        distinct 去掉重复记录

＊  如果在oracle表达式中有一个位null那么这个表达式就为null

＊  nvl函数处理null值。nvl(值1,值2) //如果值1 位null那么就去值2的值，值1不为null就去值1.

＊  like操作符%代表多个字符、_代表一个字符

＊  in 代替多个or

＊  is null判断空

第九讲

order by 查询排序，默认asc为从小到大  desc 从大到小

按照部门号升序并且按照员工待遇降序：

select * from emp order by deptno aasc,sal desc;

*在使用多字段排序，order by  后面直接跟要排序的字段并注明升序还是降序

使用别名： select ename ,(sal+nvl(comm,0)) as “年薪”  from emp order by “年薪”

 

复杂查询：

max、min、avg、sum、count //分组函数必须喝分组函数一起或者独立使用。

最高工资\最低工资：select max (sal),min(sal) from emp;

//查询最高工资的员工信息

select * from emp t where t.sal=(select max(sal) from emp);

//查找比平均

select * from emp  twhere t.sal>(select avg(sal) from emp);

group by  用于对查询的结果分组统计

* 使用group by分组时，字段必须要先查出来在分组。。

having 子句用于分组显示结果//对分组的结果在筛选。

//分组统计平均工资

select avg(sal).max(sal) , deptno from emp t group bydeptno;

 //根据多个条件分组

select avg(sal),max(sal),deptno,job from emp t group bydeptno,job;

//工资小于2000 的部门号，最大工资，平均工资

select avg(sal),max(sal) ,deptno from emp group by deptnohaving avg(sal)<2000;

总结：

1. 分组函数只能出现选择列、having、order by子句中

2.    如果select语句中出现group by 、having、order by 那么他们的顺序只能为group by、having，order by

select avg(sal),max(sal),deptno from emp group by deptnohaving avg(sal)>2000 order by avg(sal);

3.    在选择列中如果有列、表达式、个分组函数那么这些列和表达式必须有一个出现在group by 子句中，否则会出错。

多表查询：

select t1.ename,t1.sal,t2.dname from emp t1,dept t2 wheret1.deptno=t2.deptno;

笛卡尔集 emp 表与 dept表要进行笛卡尔集组合

多表查询的条件至少不少于表的个数－1  //消除笛卡尔集廉洁的多余项

自链接：在同一张表的链接查询

显示一个员工的上级姓名

子查询：数据库在执行sql是从左到右，尽可能把过滤多的数据先执行

all 所有 any任何一个

多列子查询：select * from emp where (deptno,job)=(select  deptno,job from emp where ename=’liakng’);

说明：当在from子句中使用子查询时，该子查询会被作为一个试图来使用，当在from子句中使用子查询时，必须给子查询指定表名；在给表去别名时不使用 as

分页查询：

oracle 一个有三种方式：

1.    rownum  (select * from  emp)//子查询

2.    显示rownum [oracle 分配的]

select a1.*,rownum rn from (select * from emp) a1;

3.    select a1.*,rownum rn from(select * from emp) a1  whererowmun<10;//返回10条记录

4.    如果要查询5-10的记录必须先查训1-10，在把查询的结果在进行一次查询5-10；

select a2.* from (select a1.*,rownum rn from(select * fromemp) a1 where rownum<=10) a2 where rn>5;//查询6-10的记录

a．        指定查询列，只需要修改最里面的子查询；

b．        要进行排序，只需在里层进行排序

用查询结果创建表

create table 表1( 列,列…) as select 列,列… from 表1；

inster into 表名1（列,列…） select (列,列…) from  表2;

update 表明1  set (列,列…)=(select (列,列…) from 表2 where ….)where 更新条件;

      合并查询：在实际操作中，为了合并多个select语句的结果，可以使用集合操作符号union,union all,intersect,minus；

union:该操作符用于取得两个结果的并集，当使用该操作符时会自动去掉结果集中的重复行。

Union all：操作与union相似，但是不会取消重复行，也不会排序；

 Interest:取两个结果集的交集；

Minus:使用该操作符取两个结果的差集，他只会显示存在第一个集合中，而不会存在第二个集合中的数据；