Linux系统中数据库启动和关闭&Basic SQL


第一步:使用oracle用户登录

第二步:在oracle用户下输入sqlplus /nolog 

第三步:如需使用管理员权限,则输入connect /as sysdba

第四步:启动和关闭

startup 

startup nomount #只启动实例

startup mount   #打开控制文件

startup open    #打开数据文件和重做日志

 

shutdown

shutdown normal        #不接受新的连接,需要等待所有的用户断开连接 

shutdown immediate     #回滚未结束的事务关闭数据库

shutdown transactional #等用户结束事务关闭数据库

shutdown abort         #直接关闭数据库  

第五步:如果是启动服务,要开启监听 

退出sqlplus模式输入lsnrctl start


[oracle@RHEL64 ~]$ sqlplus /nolog


SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 20 20:58:56 2014


Copyright (c) 1982, 2009, Oracle.  All rights reserved.


SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.


Total System Global Area  551165952 bytes

Fixed Size                  2215224 bytes

Variable Size             373293768 bytes

Database Buffers          171966464 bytes

Redo Buffers                3690496 bytes

Database mounted.

Database opened.

SQL> 


解锁scott用户:


SQL> alter user scott account unlock;


User altered.


SQL> commit;


Commit complete.


SQL> conn scott/tiger

ERROR:

ORA-28001: the password has expired



Changing password for scott

New password: 

Retype new password: 

Password changed

Connected.


显示当前用户:

SQL> show user

USER is "SCOTT"


设置SQL*Plus的环境变量:linesize和pagesize

SQL> set linesize 300

SQL> set pagesize 3000


计算一个查询花费的时间:

SQL> set timing on

SQL> select * from dept;  


    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON


Elapsed: 00:00:00.07

SQL> 


emp,dept,salgrade表结构如下:


DEPT表:

Linux系统中数据库启动和关闭&Basic SQL

EMP表:

Linux系统中数据库启动和关闭&Basic SQL

salgrade表:

Linux系统中数据库启动和关闭&Basic SQL



查询emp表中所有人的姓名:

SQL> select ename from emp;


ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER


14 rows selected.

SQL> 


把查询结果中ename改为中文“姓名”:

SQL> select ename 姓名 from emp;


查询emp表中不重复的部门:

SQL> select distinct deptno from emp;


    DEPTNO

----------

        30

        20

        10


SQL> 


查询emp表中工资大于2k的员工:

SQL> select ename,sal from emp where sal>2000;


ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

CLARK            2450

SCOTT            3000

KING             5000

FORD             3000


6 rows selected.


SQL> 


查询emp表中工资3k到3k的员工:

SQL> select ename,sal from emp where sal between 2000 and 3000;


ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

CLARK            2450

SCOTT            3000

FORD             3000


SQL> 


查询emp表中,ename列以A开头的员工:

SQL> select ename from emp where ename like ‘A%‘;     


ENAME

----------

ALLEN

ADAMS


SQL> 


查询emp表中,ename列含有A的员工:

SQL> select ename from emp where ename like ‘%A%‘;


ENAME

----------

ALLEN

WARD

MARTIN

BLAKE

CLARK

ADAMS

JAMES


7 rows selected.


SQL> 


查询没有员工的部门:

SQL> select * from dept where deptno in (select deptno from emp group by deptno having count(*)<1);


no rows selected


SQL> 


查询有员工的部门:

SQL> select * from dept where deptno in (select deptno from emp group by deptno having count(*)>1);


    DEPTNO DNAME          LOC

---------- -------------- -------------

        30 SALES          CHICAGO

        20 RESEARCH       DALLAS

        10 ACCOUNTING     NEW YORK


SQL> 


查询emp表中姓名和工资额,按照工资降序排列:


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


ENAME             SAL

---------- ----------

KING             5000

FORD             3000

SCOTT            3000

JONES            2975

BLAKE            2850

CLARK            2450

ALLEN            1600

TURNER           1500

MILLER           1300

WARD             1250

MARTIN           1250

ADAMS            1100

JAMES             950

SMITH             800


14 rows selected.


SQL> 


查询emp表中补贴为null的员工:

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


ENAME            COMM

---------- ----------

SMITH

JONES

BLAKE

CLARK

SCOTT

KING

ADAMS

JAMES

FORD

MILLER


10 rows selected.


SQL> 



使用to_char函数查询1981年入职的员工:

SQL> select ename,hiredate from emp where to_char(hiredate,‘yyyy‘)=‘1981‘;


ENAME      HIREDATE

---------- ---------

ALLEN      20-FEB-81

WARD       22-FEB-81

JONES      02-APR-81

MARTIN     28-SEP-81

BLAKE      01-MAY-81

CLARK      09-JUN-81

KING       17-NOV-81

TURNER     08-SEP-81

JAMES      03-DEC-81

FORD       03-DEC-81


10 rows selected.


SQL> 


使用nvl函数查询每个人每个月的总收入,如果补贴为null,则视其为0:


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


ENAME      SAL+NVL(COMM,0)       COMM

---------- --------------- ----------

SMITH                  800

ALLEN                 1900        300

WARD                  1750        500

JONES                 2975

MARTIN                2650       1400

BLAKE                 2850

CLARK                 2450

SCOTT                 3000

KING                  5000

TURNER                1500          0

ADAMS                 1100


ENAME      SAL+NVL(COMM,0)       COMM

---------- --------------- ----------

JAMES                  950

FORD                  3000

MILLER                1300


14 rows selected.


SQL>         


查询emp表中哪些员工补贴比工资还高:

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


ENAME             SAL       COMM

---------- ---------- ----------

MARTIN           1250       1400


SQL> 


查询每个部门的最高工资:


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


    DEPTNO   MAX(SAL)

---------- ----------

        30       2850

        20       3000

        10       5000


SQL> 


查询每个部门是那个员工获得了部门的最高工资:

SQL> select e1.ename,e1.sal,e1.deptno from emp e1,(select deptno,max(sal)最大值 from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal=e2.最大值;


ENAME             SAL     DEPTNO

---------- ---------- ----------

BLAKE            2850         30

SCOTT            3000         20

KING             5000         10

FORD             3000         20


SQL> 


查询10号部门和20号部门平均工资的差异:


SQL> select (select avg(sal) from emp where deptno=10)-(select avg(sal) from emp where deptno=20) distance from dual;


  DISTANCE

----------

741.666667


SQL> 



查询emp表每个员工和最高工资之间的差距:

SQL> select ename,(select max(sal) from emp)-sal distan from emp;


ENAME          DISTAN

---------- ----------

SMITH            4200

ALLEN            3400

WARD             3750

JONES            2025

MARTIN           3750

BLAKE            2150

CLARK            2550

SCOTT            2000

KING                0

TURNER           3500

ADAMS            3900


ENAME          DISTAN

---------- ----------

JAMES            4050

FORD             2000

MILLER           3700


14 rows selected.


SQL> 


查询emp表中工资超过平均工资的员工:

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


ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

CLARK            2450

SCOTT            3000

KING             5000

FORD             3000


6 rows selected.


SQL> 


查询每个部门中工资超过平均工资的员工:

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


    DEPTNO ENAME             SAL

---------- ---------- ----------

        20 JONES            2975

        30 BLAKE            2850

        10 CLARK            2450

        20 SCOTT            3000

        10 KING             5000

        20 FORD             3000


6 rows selected.


SQL> 


列出所有部门的详细信息和部门人数:

SQL> select a.*,(select count(*) from emp where deptno=a.deptno) total from dept a;


    DEPTNO DNAME          LOC                TOTAL

---------- -------------- ------------- ----------

        10 ACCOUNTING     NEW YORK               3

        20 RESEARCH       DALLAS                 5

        30 SALES          CHICAGO                6

        40 OPERATIONS     BOSTON                 0


SQL>















本文出自 “Jerry” 博客,请务必保留此出处http://alipay.blog.51cto.com/7119970/1556718

Linux系统中数据库启动和关闭&Basic SQL

上一篇:PLSQL安装


下一篇:mogodb and pymongo