2018-09-11 oracle数据库

1、安装oracle 11g

准备
系统centos6.9，安装图形界面，因为oracle的安装是在图形界面下进行的
ip地址设置为静态IP
关闭防火墙和selinux

[root@oracle ~]# vim /etc/hosts
192.168.20.112 oracle.magedu.com

将数据库的压缩包放到root目录下
同步时间
/usr/sbin/ntpdate pool.ntp.org > /dev/null 2>&1

[root@oracle ~]# ls
anaconda-ks.cfg  Documents  install.log         Music           p13390677_112040_Linux-x86-64_1of7.zip  pdksh-5.2.14-30.x86_64.rpm  Public     Videos
Desktop          Downloads  install.log.syslog  orainst_11g.sh  p13390677_112040_Linux-x86-64_2of7.zip  Pictures                    Templates
[root@oracle ~]# unzip  p13390677_112040_Linux-x86-64_1of7.zip;unzip  p13390677_112040_Linux-x86-64_2of7.zip 
[root@oracle ~]# cat orainst_11g.sh  #脚本内容
#!/bin/bash
PKG="
unixODBC
unixODBC-devel
binutils
compat-libstdc++-33
elfutils-libelf
elfutils-libelf-devel
elfutils-libelf-devel-static
gcc
gcc-c++
glibc
glibc-common
glibc-devel
glibc-headers
kernel-headers
ksh
libaio
libaio-devel
libgcc
libgomp
libstdc++
libstdc++-devel
make
numactl-devel
sysstat
"
PACKINSTALL=

for PACKAGE in $PKG ;do
    rpm -q $PACKAGE || PACKINSTALL="$PACKINSTALL $PACKAGE"
done
echo
if [ -z "$PACKINSTALL" ];then
   true
  else
   echo "The followling packages will be install: $PACKINSTALL "
   read -p "Continue? (y/N): " answer
   case $answer in
        ([yY]|[Yy][Ee][Ss])
             echo 1
             yum -y install $PACKINSTALL ;;
        (*);;
   esac
fi

cat /etc/group |grep oinstall &> /dev/null || /usr/sbin/groupadd oinstall
cat /etc/group |grep dba &> /dev/null || /usr/sbin/groupadd dba

id oracle &> /dev/null 
    if [ $? = 0 ];then
     groups oracle | grep dba &> /dev/null || /usr/sbin/usermod -g oinstall -G dba oracle
    else
     /usr/sbin/useradd -g oinstall -G dba oracle
     echo oracle |passwd --stdin oracle
    fi

KELNUM=$(cat /etc/sysctl.conf |grep -v ^# |grep -v ^$ |grep -E "fs.aio-max-nr|fs.file-max|kernel.shmall|kernel.shmmax|kernel.shmmni|kernel.sem|net.ipv4.ip_local_port_range|net.core.rmem_default|net.core.rmem_max|net.core.wmem_default|net.core.wmem_max" |awk '{print $1}' |sort -u |wc -l )

if [ $KELNUM -lt 11 ];then
cat >> /etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
/sbin/sysctl -p
fi


grep oracle /etc/security/limits.conf &> /dev/null 
  if [ $? != 0 ];then
cat >> /etc/security/limits.conf << EOF
oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   1024
EOF
 fi

grep pam_limits.so /etc/pam.d/login &> /dev/null
  if [ $? != 0 ];then
cat >> /etc/pam.d/login <<EOF
session required pam_limits.so
EOF
  fi

[ -d /u01/app ] || ( mkdir -p /u01/app/ ; chown -R oracle:oinstall /u01/app/ ; chmod -R 775 /u01/app )

grep -i oracle ~oracle/.bash_profile &> /dev/null
  if [ $? != 0 ];then
cat >> ~oracle/.bash_profile <<EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=\$PATH:\$ORACLE_HOME/bin
EOF
  fi

[ -d ~oracle/database ] || mv /root/database ~oracle 
[root@oracle ~]# ./orainst_11g.sh   #执行脚本，安装依赖包、创建用户和组、设置内核参数、设置环境变量等
[root@oracle ~]# rpm -ivh  pdksh-5.2.14-30.x86_64.rpm
warning: pdksh-5.2.14-30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 73307de6: NOKEY
error: Failed dependencies:
    pdksh conflicts with ksh-20120801-37.el6_9.x86_64
[root@oracle ~]# rpm -e ksh
[root@oracle ~]# rpm -ivh  pdksh-5.2.14-30.x86_64.rpm  # 安装此包
warning: pdksh-5.2.14-30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 73307de6: NOKEY
Preparing...                ########################################### [100%]
   1:pdksh                  ########################################### [100%]
执行结束后重启虚拟机，然后以oracle用户登陆图形界面

1、以oracle用户登陆到图形界面下，安装数据库软件

QQ截图20180910135527.png

QQ截图20180910135858.png

QQ截图20180910135915.png

QQ截图20180910140144.png

QQ截图20180910140230.png

QQ截图20180910140257.png

QQ截图20180910140459.png

QQ截图20180910152844.png

最后会让以root用户的身份去执行如下两个脚本

[root@oracle ~]# bash /u01/app/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@oracle ~]# bash /u01/app/oracle/11.2.0/db_1/root.sh 
Performing root user operation for Oracle 11g 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/11.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

2、同样以oracle用户登陆图形界面安装并创建数据库实例
执行dbca命令会弹出创建数据库

QQ截图20180911090607.png

QQ截图20180911090704.png

QQ截图20180911090737.png

注意数据库的SID要和环境变量里设置的相同，脚本中设置的为orcl

QQ截图20180911090911.png

启动oracle监听器

QQ截图20180911091208.png

![QQ截图20180911091423.png](https://upload-images.jianshu.io/upload_images/6854348-6a2ff5f720dd8fb9.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

QQ截图20180911091625.png

QQ截图20180911091750.png

QQ截图20180911091854.png

QQ截图20180911091950.png

QQ截图20180911092002.png

最后一步安装要好长时间

QQ截图20180911141640.png

总结：本次安装刚开始是在centos7.5上安装的。总是在图形界面卡死，安装失败，最后更换为centos6.9的系统安装成功，图片都是在centos7.5安装时候的截图
本地连接数据库，可以使用如下两种方法

远程也可以连接

[root@oracle app]# su - oracle
[oracle@oracle ~]$ sqlplus sys/1 as sysdba  #as sysdba表示以dba权限连接
[oracle@oracle ~]$ sqlplus / as sysdba
进入数据库发现无法用上下键像mysql数据库那样查看历史命令，需要以root用户安装此软件包
[root@oracle ~]# rpm -ivh  rlwrap-0.37-1.el6.x86_64.rpm
[oracle@oracle ~]$ rlwrap sqlplus / as sysdba #前面加上此命令就可了，也可以删除了
[oracle@oracle ~]$ vim .bashrc 
alias sqlplus='rlwrap sqlplus'
[oracle@oracle ~]$ . .bashrc #使生效

2、sql语句

SCOTT用户是我们学习Oracle过程中一个非常重要的实验对象，在我们建立数据库的时候，如果是选择定制模式的话，SCOTT用户是不会默认出现的，如何出现参考：
https://blog.csdn.net/lixiyuyu/article/details/51205475

SQL> conn sys/1 as sysdba;  #连接到数据库后如果从某个用户断开。可以重新用命令连接， sys用户是一个超级用户，有修改其他用户密码的权限
SQL>  @/u01/app/oracle/11.2.0/db_1/rdbms/admin/scott.sql  #前面加@可以创建出来scoot用户，用于测试用
SQL>  alter user scott identified by gg;  #修改scott用户的密码为gg
SQL> conn scott/gg  #连接到scoot用户
SQL> show user  #查看用户，oracle数据库是以用户为单位，每个用户下面会有很多表
SQL> select * from tab;    #查看该用户下有哪些表
SQL> desc dept  #查看某个表的结构
SQL> select * from dept;   #查看表
SQL> set line 1000  #可以扩大显示终端的长度，不然查看的时候有些列会出现在下一行
SQL> select * from emp;  #查看emp表
SQL> select ename,sal from emp;  #只显示某一个列
SQL> select ename,sal*12 from emp;   #显示年薪
SQL> select ename as  xingming ,sal*12 as nianxin from emp;  #查询的时候显示别名，as可以省略不加
SQL> select ename as  "xingming" ,sal*12 as "nianxin" from emp;  #加上双引号。显示的时候不会显示大写
SQL> select ename ||sal  from emp;  #两列显示为一列
SQL> select ename ||'de yuexin shi '||sal  from emp;  #也可以加一个字符串显示，别名要加双引号，而字符串加单引号

ENAME||'DEYUEXINSHI'||SAL
----------------------------------------------------------------
SMITHde yuexin shi 800
ALLENde yuexin shi 1600
WARDde yuexin shi 1250
SQL> select distinct sal from emp;  #可以去除表中重复的行
SQL> select * from all_users;  #查看所有的用户
oracle不是像mysql一样以一个个的数据库为单位。而是创建一个数据库实例后，以用户为单位，此用户里面会有很多表
SQL> select ename,sal from emp where ename='SCOTT';  #注意字符串要加单引号
SQL> select * from emp where deptno=20;
SQL> select * from emp where sal >800;
SQL> select * from emp where sal != 3000;  #不等于3000
SQL> select * from emp where sal <> 3000;  #也表示不等于
SQL> select * from emp where sal between 800 and  3000; #在什么之间
SQL> select * from emp where sal in (800,1100);

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
SQL> select * from emp where sal not in (800,1100);

SQL> select ename from emp where ename like 'SCO%';  #%相当于*

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

ENAME         SAL       COMM
---------- ---------- ----------
ALLEN        1600        300
WARD         1250        500
MARTIN       1250       1400
TURNER       1500          0
SQL> select ename from emp where ename like 'SCO__';  #一个下划线表示一个任意字符

ENAME
----------
SCOTT
SQL> select ename,sal from emp where sal > 1000 and job like '%MAN%';
SQL> select ename,sal from emp order by sal;  #按照哪一列排序
SQL> select ename,sal from emp order by sal desc;  #倒叙排列
SQL> select ename,sal from emp where deptno=20  order by sal;  #排序要放在最后，不能放在where之前，先限定再排序
SQL> select ename,sal as "yuexin" from emp where deptno=20  order by "yuexin";  #基于别名排序

SQL> select ename,sal,comm from emp order by 3;  #表示按照显示的第三列排序

ENAME         SAL       COMM
---------- ---------- ----------
TURNER       1500          0
ALLEN        1600        300
WARD         1250        500
MARTIN       1250       1400
SQL> select deptno,ename,sal from emp where deptno=&bumen order by sal;
Enter value for bumen:    #每次查询都会询问，可以定义变量。像read一样，输入的是什么就按照限定条件进行查询
[oracle@oracle ~]$ vim /u01/app/oracle/11.2.0/db_1/sqlplus/admin/glogin.sql 
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"  #可以修改命令提示符
[oracle@oracle ~]$ sqlplus sys/1 as sysdba  #以哪个用户连接就显示哪个用户
SYS@orcl>  #命令提示符显示的是用户名和数据库实例名，orcl是创建的数据库实例
[oracle@oracle ~]$vim /u01/app/oracle/11.2.0/db_1/sqlplus/admin/glogin.sql 
DEFINE _EDITOR=vim
SYS@orcl>select 
  2  * from 
  3  scott.dept;   #换行输入一个查询语句
SYS@orcl>ed   #此时数据ed就会进入vim编辑器。对上一条语句就行编辑，如果写的有错误，就可以在vim编辑器里面修改了。很方便
Wrote file afiedt.buf
select
* from
scott.dept
/
SCOTT@orcl>select * from emp where deptno=&bumen;
Enter value for bumen: 30
old   1: select * from emp where deptno=&bumen
new   1: select * from emp where deptno=30  #发现总是显示一行old和new

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
SCOTT@orcl>set verify off;  #设置后不再显示old和new
SCOTT@orcl>select * from emp where deptno=&bumen;
Enter value for bumen: 30

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30

3、单行函数、转换函数、分组函数

oracle的数据类型有字符型、数字型和日期型三种

一、单行函数
SYS@orcl>select username from dba_users;   #也可以这样查看所有的用户
SYS@orcl>set pagesize  4000  #这样就不会换页了
SYS@orcl>select username from dba_users;
SYS@orcl>create user "scott" identified by magedu;
SYS@orcl>select username from dba_users where lower(username) like 'sco%'; #先把这一列转化成小写，再like

USERNAME
------------------------------
scott
SCOTT
SCOTT@orcl>select initcap(ename) from emp;  #显示的列为首字母大写

INITCAP(EN
----------
Smith
Allen
Ward
SCOTT@orcl>select round(45.923,2) from emp;  #可以显示数字，但这个表中并没有和这个列
SCOTT@orcl>select round(45.923,2) from emp;

ROUND(45.923,2)
---------------
      45.92
      45.92
SCOTT@orcl>select 'hello' from emp;  #表中没有hello这一列，也可以显示出hello

'HELL
-----
hello
hello
hello
二、转化函数
SCOTT@orcl>select sysdate from dual;

SYSDATE
---------
12-SEP-18
SCOTT@orcl>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; #表示把时间转化成字符串格式

TO_CHAR(SYSDATE,'YY
-------------------
2018-09-12 10:21:46

SCOTT@orcl>select ename,hiredate from emp;

ENAME      HIREDATE
---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81
MARTIN     28-SEP-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
SCOTT      19-APR-87
KING       17-NOV-81
TURNER     08-SEP-81
ADAMS      23-MAY-87

ENAME      HIREDATE
---------- ---------
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82
SCOTT@orcl>select ename,hiredate from emp where hiredate < to_date('01-JAN-85','DD-MON-RR');   #只显示85年1月1日之前入职的

ENAME      HIREDATE
---------- ---------
SMITH      17-DEC-80
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

ENAME      HIREDATE
---------- ---------
MILLER     23-JAN-82

12 rows selected.
SCOTT@orcl>select ename,hiredate from emp where hiredate < to_date('01-01-85','DD-mm-RR'); #把时间转化成日期

SCOTT@orcl>select to_char(sysdate,'year,month') from dual;

TO_CHAR(SYSDATE,'YEAR,MONTH')
-------------------------------------------------------------------------------
twenty eighteen,september

SCOTT@orcl>select upper(to_char(sysdate,'year,month')) from dual;  #嵌套函数

UPPER(TO_CHAR(SYSDATE,'YEAR,MONTH'))
-------------------------------------------------------------------------------
TWENTY EIGHTEEN,SEPTEMBER
空值函数
SCOTT@orcl>select ename,sal,nvl(comm,0) from emp;  #表示把空值自动设置成0
SCOTT@orcl>select ename,sal+nvl(comm,0) from emp; #总月薪
SCOTT@orcl>select ename,(sal+nvl(comm,0))*12 from emp;  #总年薪
SCOTT@orcl>create table emptmp as select * from emp;   #创建一个临时表来自于emp表
SCOTT@orcl>insert into emptmp (empno,ename,sal)values (8000,'jiake',1000);
SCOTT@orcl>select * from emptmp;
SCOTT@orcl>select ename,nvl(hiredate,'01-JAN-80') from emptmp;  #空值的添加一个默认值
三、分组函数
SCOTT@orcl>select max(sal) from emp;  #取出最大工资
SCOTT@orcl>select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429


SCOTT@orcl>select round(avg(sal),0) from emp;

ROUND(AVG(SAL),0)
-----------------
         2073
SCOTT@orcl>select min(sal) from emp;
SCOTT@orcl>select sum(sal) from emp;
SCOTT@orcl>select count(sal) from emp;
SCOTT@orcl>select count(*) from emp;  #查看表里面有多少行
SCOTT@orcl>select min(hiredate),max(hiredate) from emp;

MIN(HIRED MAX(HIRED
--------- ---------
17-DEC-80 23-MAY-87
SCOTT@orcl>select deptno,avg(sal) from emp group by deptno;  #显示每个部门的平均值，如果不加 group by deptno会报错，只能显示平均工资一列，但无法显示部门

    DEPTNO   AVG(SAL)
---------- ----------
    30 1566.66667
    20   2175
    10 2916.66667
在使用组函数时，除了组函数本身以外的列要想显示出来，必须被group by条件限定

SCOTT@orcl>select deptno,avg(sal) from emp  group by deptno having avg(sal) > 2000 order by 2 desc;   #组函数用having限定不用where，order by的时候可以不写哪一列，而用数字代替

    DEPTNO   AVG(SAL)
---------- ----------
    10 2916.66667
    20   2175

4、多表查询语句


SCOTT@orcl>select  * from emp;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

SCOTT@orcl>select * from dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS     BOSTON
自然连接，需要两个表中有一列是相同的
SCOTT@orcl>select empno,ename,dname from emp natural join dept;  #要查询部门名称就需要查询两张表

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14 rows selected.
SCOTT@orcl>create table dept_tmp as select deptno deptid,dname,loc from dept;  #创建一个表来自dept表
SCOTT@orcl>select * from dept_tmp;

    DEPTID DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS     BOSTON

SCOTT@orcl>select empno,ename,dname from emp join dept_tmp on (emp.deptno=dept_tmp.deptid);   #两个表中没有相同的列名，

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING
SCOTT@orcl>select empno,ename,dname from emp e join dept_tmp d on (e.deptno=d.deptid);  #起别名
SCOTT@orcl>select empno,ename,dname from emp join dept using(deptno);  #如果两个表有两个列名字相同，需要用using指明连接哪一列
如何创建HR用户
参考：https://blog.csdn.net/zhuoshengda/article/details/78759792
SYS@orcl>conn HR/hr;
HR@orcl>select * from tab;
HR@orcl>select * from employees;
HR@orcl>select * from departments;
HR@orcl>select * from locations;
HR@orcl>select e.first_name,d.department_name,l.city from employees e join departments d on (e.DEPARTMENT_ID=d.DEPARTMENT_ID) join locations l on (d.LOCATION_ID=l.LOCATION_ID); #三张表的查询
HR@orcl>select e.first_name,d.department_name,l.city from employees e join departments d on (e.DEPARTMENT_ID=d.DEPARTMENT_ID) join locations l on (d.LOCATION_ID=l.LOCATION_ID) and e.manager_id=149;
SCOTT@orcl>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SCOTT@orcl>select x.ename as yuangong,s.ename as lingdao from emp x join emp s on (x.mgr=s.empno);  #自连接，实际上是一张表。只是自己想象成两张表

YUANGONG   LINGDAO
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD

13 rows selected.

SCOTT@orcl>select x.ename as yuangong,s.ename as lingdao from emp x left join emp s on (x.mgr=s.empno);   #显示没有领导的员工，左边有，右边没有

YUANGONG   LINGDAO
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING
SCOTT@orcl>select x.ename as yuangong,s.ename as lingdao from emp x right join emp s on (x.mgr=s.empno);  #右边有左边没有
SCOTT@orcl>select x.ename as yuangong,s.ename as lingdao from emp x full join emp s on (x.mgr=s.empno);  #左右都有
SCOTT@orcl>select x.ename as yuangong,nvl(s.ename,'KING') as lingdao from emp x left join emp s on (x.mgr=s.empno);   #空就给一个默认的值

YUANGONG   LINGDAO
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING       KING
SCOTT@orcl>select * from salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999
SCOTT@orcl>select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;  #显示工资等级，不等值连接的例子

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4
SCOTT            3000          4
FORD             3000          4
KING             5000          5
SCOTT@orcl>select ename,dname from emp cross join dept;  #实现交叉连接

5、子查询

SCOTT@orcl>select ename,sal from emp where sal > (select sal from emp where ename='SCOTT');  #查询谁的工资比SCOTT高

ENAME             SAL
---------- ----------
KING             5000

SCOTT@orcl>select ename,sal from emp where sal= (select min(sal) from emp);  #谁的工资最少
SCOTT@orcl>select ename,sal from emp where sal= (select max(sal) from emp);
SCOTT@orcl>select ename,sal from emp where sal> (select avg(sal) from emp);
SCOTT@orcl>select ename,sal from emp where sal in (select min(sal) from emp group by deptno);

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
MILLER           1300
HR@orcl>select employee_id,job_id from employees union select  employee_id,job_id from job_history;   #取并集，去除重复项
HR@orcl>select employee_id,job_id from employees union all select  employee_id,job_id from job_history;   #不去除重复项

HR@orcl>select employee_id,job_id from employees intersect select  employee_id,job_id from job_history;   #取交集，只显示重复行

EMPLOYEE_ID JOB_ID
----------- ----------
        176 SA_REP
        200 AD_ASST

6、对数据库的修改操作

SCOTT@orcl>create table emp26 as select * from emp;
SCOTT@orcl>insert into emp26 values (8000,'JIAKE','XUEWEI',1000,'12-JUL-17',0,NULL,40);
SCOTT@orcl>insert into emp26 (empno,ename,sal,deptno)values(8001,'yanguo',9000,50);
SCOTT@orcl>insert into emp26 (sal,mgr,ename,empno)values (100,1000,'xiaolongnv',1000);
SCOTT@orcl>select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp26;
SCOTT@orcl>insert into emp26 (empno,ename,hiredate)values(8002,'chuanyue',to_date('2017-8-12 23:10:50','yyyy-mm-dd hh24:mi:ss'));
SCOTT@orcl>select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp26;  #发现可以显示时分秒了
SCOTT@orcl>update emp26 set sal=sal*1.5;
SCOTT@orcl>update emp26 set sal=sal*1.5 where deptno=20;
SCOTT@orcl>update emp26 set sal=(select sal from emp26 where ename='SCOTT') where ename='JIAKE';
SCOTT@orcl>delete from emp26;  #删除表中的数据库
SCOTT@orcl>rollback;  
SCOTT@orcl>delete from emp26 where ename='WARD';
SCOTT@orcl>truncate table emp26;  #删除表后不能用rollback的方式恢复表中的内容
SCOTT@orcl>create table dept26 as select * from dept;
SCOTT@orcl>inset into dept26 values (50,'SALES','BEIJING');
SCOTT@orcl>select * from dept26;
SCOTT@orcl>commit;
SCOTT@orcl>set autocommit on  #设置为自动提交，默认是手动提交的

提交数据有三种类型：
显式提交、隐式提交及自动提交。下面分别说明这三种类型。
1、显式提交：用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；
2、隐式提交：用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
3、自动提交：若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

最后编辑于：2019.04.19 13:35:36

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,732评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,496评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,264评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,807评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,806评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,675评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,029评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,683评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,704评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,666评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,773评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,413评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,016评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,978评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,204评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,083评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,503评论 2赞 343