db_oracle常用执行语句

1、select deptno,loc from dept;  

 //查询dept表里的deptno,loc字段

2、select ename,sal,sal+300 from emp;     

//加法运算

3、select ename,sal,12*sal+100 from emp;  

//运算的优先级

4、select ename,sal,12*(sal+100) from emp; 

//括号的优先级

5、select ename,job,comm from emp;       

//空值引用

6、select ename NAME,12*sal+comm
   from emp

   where ename='KING';                    

//空值存在为空

7、select ename AS name,sal salary from emp; 

//别名的引用,区别as的作用

8、select ename "NAME",sal*12 "Annual Salary"

   from emp;                                  

//别名字段的引用，字符串

9、select ename||job AS "Employees"

   from emp;                                 

//连接符   

10、select ename ||' '||'is a'||' '||job
             AS "Employee Details"

    from emp;                                

//连接符

11、select 'delete from'||table_name||'where 1=0;'||chr(10)

    from user_all_tables;                    

 //连接符，chr（10）回车

12、select distinct deptno from emp;          

//过滤重复行distinct

13、select ename,job,deptno
    from emp
    where job = 'CLERK';                      

    select * from emp where hiredate < '1-1月-1982';

 //条件过滤

14、select ename,sal,comm
    from emp

    where sal <= comm;                         

//运算符<=

15、select ename,sal
    from emp

    where sal between 1000 and 1500;           

//运算符between and

16、select empno,ename,sal,mgr
    from emp

    where mgr in(7902,7566,7788);              

//运算符in

17、select ename
    from emp

    where ename like 'S%';                    

 //运算符like

18、select ename
    from emp

    where ename like '_A%';                    

//运算符_

19、select ename
    from emp

    where ename like '%A/_B%' escape '/';      

//将_ %等特殊字段转成字符格式

20、select ename,mgr
    from emp

    where mgr is NULL;                         

//NULL的使用

21、select ename,job,empno,sal
    from emp
    where sal >= 1000

    and   job = 'CLERK';                      

 //and的用法，可以多讲一下，加以区别

22、select ename,job,empno,sal
    from emp
    where sal >= 1000

    or   job = 'CLERK';                        

//or的用法

23、select ename,job,empno,sal
    from emp

    where job not in('CLERK','MANAGER','ANALYST');  

//not in的用法

24、select ename,job,empno,sal
    from emp
    where job = 'SALESMAN'
    or job = 'PRESIDENT'

    and sal > 1500;                            

//or  and的优先级

25、select ename,job,empno,sal
    from emp
    where (job = 'SALESMAN'
    or job = 'PRESIDENT')

    and sal > 1500;                               

//or  and加括号

26、select ename,job,empno,hiredate
    from emp

    order by hiredate;                            

//order by

27、select ename,job,empno,hiredate
    from emp

    order by hiredate desc；                        

 //desc用法

28、select ename,empno,sal*12 annsal
    from emp

    order by annsal;                            

 //别名

29、select ename,empno,sal,deptno
    from emp

    order by deptno,sal desc;                    

 //双列desc排序

30、select LENGTH(ename),ename

    from emp;                                         

 //length字段长度函数

31、select LENGTH(ename),CONCAT(ename,job),INSTR(ename,'A')

    from emp;                                                

 //length，concat，instr组合使用

32、select ename,(SYSDATE-hiredate)/7 WEEKS
    from emp

    where deptno = 10;                                       

//时间函数，计算周数

33、select ename,hiredate
    from emp;


    SELECT  ename,   
    TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE             

    FROM    emp;

//to_char转型hiredate

34、SELECT  sal
    from EMP
    WHERE ename = 'SCOTT';


    SELECT  TO_CHAR(sal,'$99,999') SALARY
    from EMP

    WHERE ename = 'SCOTT';                                   

//$转换

    
35、SELECT ename, sal, comm, (sal*12)+NVL(comm,0)
    FROM   emp;
    select ename,sal,comm,sal*12+comm

    from emp;                                             

//nvl(comm,0) 比较

36、SELECT emp.ename,emp.empno,emp.deptno,dept.deptno,dept.loc
    from emp,dept

    where emp.deptno=dept.deptno;                        

 //多表查等值查询

37、SELECT e.ename,e.empno,e.deptno,d.deptno,d.loc
    from  emp e,dept d

    where e.deptno=d.deptno;                               

 //给表取别名

38、SELECT e.empno,e.sal,s.grade,s.losal,s.hisal
    from emp e,salgrade s
    where e.sal

    between s.LOSAL and s.HISAL;                              

 //多表不等值查询

39、SELECT worker.ename||worker.mgr||' worksfor'||manager.ename,
    manager.empno
    FROM emp worker, emp manager

    WHERE worker.mgr = manager.empno;                     

//表的自连接，

40、select avg(sal),max(sal),min(sal),sum(sal) from emp where job like 'SALES%';   

//avg，max，min，sum

41、select min(hiredate),max(hiredate) from emp;                                   

//min，max适用于任何数据类型

42、select count(*) from emp where deptno=30;                                      

//表的记录数count

43、select count(comm) from emp where deptno=30;                                   

//表comm非空的记录数

44、select avg(comm) from emp;                                                     

//comm的平均值

45、select avg(nvl(comm,0)) from emp;                                             

 //加了nvl的比较

46、select avg(sal),deptno from emp group by deptno;                              

//group by的使用，分组

47、select deptno,job,sum(sal) from emp group by deptno,job;                       

//按部门分组统计emp表中各职位的工资总和

48、select deptno,count(ename) from emp；                                

//执行语句失败，count不是单组函数，需要和group by配合使用

49、select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;         

//where不能跟分组函数一起用，执行语句失败，where不是限定

50、select job,sum(sal) PAYROLL from emp where job NOT LIKE 'SALES%'
    group by job
    having sum(sal) > 5000

    order by sum(sal);                                                             

//where可以条件筛选跟having配合使用

51、select max(avg(sal)) from emp group by deptno;                                 

//多组函数组合使用

52、select ename,sal from emp

    where sal > (select sal from emp where ename='BLAKE');                          

//查询工资大于blake工资的人

53、select ename from emp where sal >

                                 (select sal from emp where empno = 7566);          

//查询工资大于员工号是7566人的工资的人

54、select ename,job from emp where job = 
                                      (select job from emp where empno = 7369)
                          and sal > 

                                   (select sal from emp where empno = 7876);     

//查询职位跟员工号7369的职位一样，工资大于工号是7876员工工资的人的名字和职位

55、select ename,job,sal from emp
    where sal = 

            (select min(sal) from emp);                                             

//查询工资最低的人的姓名等等

56、select deptno,min(sal)
    from emp
    group by deptno
    having min(sal) > 

                     (select min(sal) from emp where deptno = 20);                  

//按部门查询每个部门中工资最少的人，并且最少工资要大于部门是20最少工资

57、select empno,ename from emp
    where sal = 

               (select min(sal) from emp group by deptno);                          

//执行语句失败，提示单行子查询返回了多个行

    select empno,ename from emp
    where sal in 

               (select min(sal) from emp group by deptno);                          

//将=换成in就可以了

58、select ename,job from emp
    where job = 

               (select job from emp where ename = 'SMYTHE');                         

//子查询没返回值，没有叫SMYTHE的这个人

59、SELECT ename,job,sal,empno from emp
   where sal < any 
               (select sal from emp where job = 'CLERK')

   and job <> 'CLERK';                                                               

//any的使用，是小于任意一个就行，注意它跟all的区别

60、select ename,job,sal,empno from emp
    where sal > all

                   (select avg(sal) from emp group by deptno);                       

//选出大于所有部门平均工资的人的相关信息

61、insert into dept(DEPTNO,DNAME,LOC) values (50,'DEVELOPMENT','BEIJING');          

//insert into 语句

62、insert into dept(deptno,dname) values(60,'TEST');                                

//缺值添加，但是一定要在dept表后面输入要添加的相关列

63、insert into dept values(70,'CEO','NANJING');                                    

 //如果values后面添加的值很全，则dept表后可以不跟列属性

64、select sysdate from dual;
    INSERT INTO  emp (empno, ename, job,
      mgr, hiredate, sal, comm,
      deptno)
  VALUES    (7196, 'GREEN', 'SALESMAN',
      7782, SYSDATE, 2000, NULL,

      10);                                                                           

//插入当前系统时间

65、INSERT INTO emp
  VALUES      (2296,'AROMANO','SALESMAN',7782,
    TO_DATE('1月 3,03', 'MON DD, YY'),
    1300, NULL, 10);
66、update emp set (job,deptno)=
   (select job,deptno from emp where empno=7499)

    where empno=7698;                                                                

//将员工号为7698员工的岗位，deptno修改为员工号为7499员工一样

67、select * from dept;
   update dept set LOC='TEST' where deptno=50;
   rollback;
   delete from dept where deptno=60;
   commit;

   rollback;                                                                            

//修改和删除记录的回滚演示

68、select * from dept;
   update dept set loc='tianxia' where deptno=50;
   savepoint update_tianxia;
   update dept set loc='xuwuhu' where deptno=70;
   savepoint update_xuwuhu;
   rollback to update_tianxia;
   commit;

   rollback to update_xuwuhu;                                                           

//事物的savepoint点和回滚

69、create table nanjing
(
class number(3),
name varchar2(20),
age number(3),
sex varchar2(10)

)                                                                                       

//创建表

70、select distinct object_type from user_objects;                                      

//查看数据库中有哪些对象

71、CREATE TABLE   dept30
   AS 
   SELECT empno, ename, sal*12 ANNSAL, hiredate
   FROM  emp

   WHERE  deptno = 30;                                                                  

//按照查询的数据来创建一张新表

72、alter table dept30 

   add (job varchar(9));                                                                

//添加新列

73、alter table dept30 

   modify (job varchar(15));                                                                

//修改列

74、create table haha
 (
 adress varchar2(20) not null,
 people varchar2(10))
 select * from haha;

 insert into haha(adress,people) values('','wang');                               

//not null约束

75、create table testing
 (plan varchar2(10),
 desgin varchar2(20),
 stroy varchar2(10),
 constraint testing_stroy_uk unique(stroy)
 );
 select * from testing;
 insert into testing values('migntian','safasdf','houtian');

 insert into testing values('migntian','safasdf','houtian');                          

//唯一性约束

76、create table zhuwai
 (zhujian varchar2(2),
 qita varchar2(3),
 waijian varchar2(2),
 constraint zhuwai_zhujian_pk primary key(zhujian),
 constraint waijian_waijian_fk foreign key(waijian) references zhuwai(zhujian)
 );
 select * from zhuwai;
 insert into zhuwai values('qi','qi','qi');

 insert into zhuwai values('qi','qi','qi');                                          

//主键外键操作

77、create table zhihui
 (zhujian varchar2(10),
 name varchar2(20),
 waijian varchar2(10),
 constraint zhihui_zhujian_pk primary key(zhujian)
 );
 select * from zhihui;
 create table ka
 (kname varchar2(20),
 address varchar2(20),
 zhujian varchar2(20),
 constraint ka_zhujian_fk foreign key(zhujian) references zhihui(zhujian)
 );
 select * from ka;
 insert into zhihui values('asdfad','asdf','asdf');

77、select * from scott.emp;
 CREATE VIEW   empvu10
  AS SELECT  empno, ename, job
  FROM      scott.emp
 WHERE deptno = 10;

 select * from empvu10;                                                           

//创建视图

Create   sequence  v2;
           Insert   into  dept(deptno,dname)  values(v1.nextval,'aaa');
           Insert   into  dept(deptno,dname)  values(v1.nextval,'sss');
           Insert   into  dept(deptno,dname)  values(v1.nextval,'ddd');                                                         
78、create index suoyin on emp(ename);

  select * from user_indexes;                                                        

//索引创建和查询

79、create user xiaoming identified by xiaoming;                                    

//创建用户

80、GRANT  create table, create sequence, create view

    TO     scott;                                                                   

//权限赋予create

81、alter user xiaoming identified by xinmima;                                      

//修改密码

82、grant select on scott.emp to xiaoming;                                          

//权限赋予select

83、revoke select on scott.emp from xiaoming;                                       

//权限回收

84、create role manager;                                                          

//创建角色

85、grant create session to myrole with admin option;
 create user xinxin identified by xinxin;

 grant myrole to xinxin;                                                            

//角色的定义