Oracle总结

Oracle    Structured Query Language 结构化查询语言      

 

用CMD启动Oracle服务:

1、启动监听:    lsnrctl start [listener1]...[listenerN]         

2、启动数据库实例:   oradim -startup -sid orcl  

3、启动isqlplus:  isqlplusctl start   

4、设置环境变量: set oracle_sid=orcl

5、启动控制台:      emctl start dbconsole

 

用CMD关闭Oracle服务:

 

//数据字典

Oracle数据字典中的对象名称以三种前缀开头:"User","All","DBA"

"User"域中的记录通常显示有关执行查询的帐户所拥有的对象的信息

"All"域中的记录包括“User”记录以及有关已授予用户其特权的对象的信息。

"DBA"域包含所有数据库对象

 

常用的数据字典: user_tables  user_views  user_constraints  user_triggers

 

关系数据库支持的SQL语言

DDL(Data Define language)数据定义语言

DML(Data Manipulation Language)数据操纵语言

DCL(Data Control Language)数据控制语言

 

在Oracle中,任何含有空值的数学表达式,所得结果都为空值

 

SQL> ed //打开缓冲区

 

"/"斜杠表示执行缓冲区里的内容

 

SQL语句格式:

 

SQL> select distinct/all [] from [] where [] group by [] having [] order by [];

 

SQL> create user [username] identified by [psw];

          //在SQL*PLUS 中建立用户时,要输入一个以字母开头的口令,在企业管理器中可以任意

SQL> desc user_tables;//查询数据字典

 

SQL> alter user scott identified by tiger;//修改用户密码

 

SQL> alter user scott account unlock;

 

SQL> drop user scott cascade;

 

SQL> desc emp;//查询一个表的结构

 

SQL> select ename,sal*12 from emp;

 

SQL> select ename,sal*12 annual_sal from emp;

 

SQL> select ename,sal*12 "annual sal" from emp;

 

SQL> select ename||sal from emp;

 

SQL> select ename||'connect' from emp;

 

SQL> select ename||'two''connect' from emp;

                               //当连接的字符串中有单引号时,可以用两个单引号表示

SQL> select distinct deptno from emp;

 

SQL> select distinct deptno,job from emp;

                                       //当deptno和job都相同时,只显示一条信息

SQL> select * from emp where deptno = 10;

 

SQL> select * from emp where sal>1500;

 

SQL> select * from emp where sal<>1500;

                                       //在SQL中<>代表不等于

SQL> select * from emp where sal between 800 and 1500;

                                       //包含800和1500的值

SQL> select ename,sal,comm from emp where comm is null;

 

SQL> select ename,sal,comm from emp where comm is not null;

 

SQL> select ename,sal,comm from emp where sal in (800,1500);

 

SQL> select ename,sal,hiredate from emp where hiredate>'20-2月-81';

 

SQL> select * from dept order by deptno;

                                       //order by 默认为升序

SQL> select * from dept order by deptno desc;

 

SQL> select empno,ename from emp order by empno [asc];

 

SQL> select empno,ename from emp where deptno<>10 order by empno;

 

SQL> select ename,sal,deptno from emp order by deptno;

 

SQL> select ename,sal,deptno from emp order by deptno,ename desc;

                   //首先按照deptno升序排列,如果deptno相同的时候,按照ename降序排列

SQL> select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;

 

SQL> select lower(ename) from emp;

 

SQL> select ename from emp where ename like '_a%' or ename like '_A%';

 

SQL> select ename from emp where lower(ename) like '_a%';

 

SQL> select upper(ename) from emp;

 

SQL> select substr(ename,1,3) from emp;

                                     //从ename的一个字符开始截取,一个截取3个字符

SQL> select chr(65) from dual;

 

SQL> select ascii('A') from dual;

 

SQL> select round(23.652) from dual;

                                    //四舍五入

SQL> select round(23.652 , 2) from dual;

                                    //四舍五入到小数点后两位

SQL> select round(23.652 , -1) from dual;

                                    //显示结果为20

SQL> select to_char(sal,'$99,999.9999') from emp;

 

SQL> select to_char(sal,'L99,999.9999') from emp;

                                    //L代表系统本地货币

SQL> select to_char(sal,'L00000.000') from emp;

 

SQL> select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;

 

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

 

SQL> select sal from emp where sal > to_number( '$1,250.00' , '$9,999.99' );

                                    //显示处大于1250的全部sal

SQL> select ename,sal*12+nvl(comm,0) from emp;

                                 //如果comm的值为空,则用0代替.否则是comm自己的值

SQL> select max(sal) from emp;

 

SQL> select min(sal) from emp;

 

SQL> select avg(sal) from emp;

 

SQL> select to_char(avg(sal) , '9999.99') from emp;

                                    //精确到小数点后两位

SQL> select round(avg(sal),2) from emp;

 

SQL> select count(*) from emp;

                                    //求出emp表共有多少条记录

SQL> select count(*) from emp where deptno = 10;

                                    //求处depnto=10的部门有多少人

SQL> select count(ename) from emp;

 

SQL> select count(comm) from emp;

                                    //显示出comm字段中值不为空的字段

SQL> select count(distinct deptno) from emp;

 

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

 

SQL> select max(sal) from emp group by deptno,job;

                        //多个字段group by的时候,只有两个字段都一样的时候,才算是一组

SQL> select ename from emp where sal = (select max(sal) from emp);

 

SQL> select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;

 

SQL> select avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;

 

SQL> select ename,sal from emp where sal > (select avg(sal) from emp);

 

SQL> select ename,sal from emp join (select max(sal) max_sal , deptno from emp group by deptno)t on (emp.sal = t.max_sal and emp.deptno = t.deptno);

 

SQL> select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

                                                                 //自连接

SQL> select ename,dname from emp cross join dept;

                                                     //交叉连接 产生笛卡尔乘积

SQL> select ename,dname from emp join dept on (emp.deptno = dept.deptno);

                                                              //SQL1999

SQL> select ename,dname from emp join dept using(deptno);

 

SQL> select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

 

SQL> select ename,dname,grade from emp e join dept d on (e.deptno = d.deptno) join     salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';

 

SQL> select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

 

SQL> select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);

                                                            //左外连接

SQL> select e1.ename,e2.ename from emp e1 right join emp e2 on (e1.mgr = e2.empno);

                                                           //右外连接

SQL> select e1.ename,e2.ename from emp e1 full join emp e2 on (e1.mgr = e2.empno);