Oracle学习(一)

一.数据库和数据库管理系统
1.1名词解释
oracle   数据库的一种
DBMS   数据库管理系统  负责对数据的管理
DB     数据库  数据的真正存放
RDBMS   关系型数据库管理系统
RDB     关系型数据库
1.2 基于二维表的数据库
1.3现在主流关系型数据库管理系统
oracle oracle(甲骨文)  
db2       IBM  
sqlserver mic(微软)  
非商业型数据库
mysql    sun-----oracle(操作系统 java)


二.sql学习 (结构化查询语言)
2.1 select语句   查询语句
select
  2.2 ddl语句      数据定义语句
      create   table
      drop     table
      alter   table 
  2.3 dml语句    数据操作语句
      insert 
delete
      update      
2.4 tcl        事务控制语句
      commit; 提交 确认
      rollback; 回滚 撤销
      savepoint  保存点;    
  2.5 dcl     数据控制语句
      grant  授权
      revoke  回收权限    
 三.使用数据库
  3.1登录数据库
  telnet  IP
  telnet  192.168.0.26            
  使用sqlplus  进入数据库
  sqlplus   
  !clear
  exit
3.2 查询数据库中的表结构
  desc  表名;
      desc  s_emp;
desc  s_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 ID    员工编号                                     NOT NULL NUMBER(7)
 LAST_NAME  姓                               NOT NULL VARCHAR2(25)
 FIRST_NAME 名                                         VARCHAR2(25)
 USERID     用户编号                                      VARCHAR2(8)
 START_DATE 入职日期                                 DATE
 COMMENTS   备注                                        VARCHAR2(255)
 MANAGER_ID 领导的员工编号                     NUMBER(7)
 TITLE      职位                                         VARCHAR2(25)
 DEPT_ID    部门编号                                      NUMBER(7)
 SALARY     月薪                                       NUMBER(11,2)
 COMMISSION_PCT  提成                             NUMBER(4,2) 


四.select语句
4.1 from 子句
    select  salary  from s_emp;
    select  first_name,salary from s_emp;
    select  * from  s_emp;
    4.2表中字段数据的数学运算+ - *  /
    把每个员工的工资加100显示出来
    select  salary,salary+100 from s_emp;
    计算每个员工的月薪 和 年薪
    select  salary,salary*12  from s_emp;
  select  salary,100+salary*12  from s_emp;
    select  salary,(100+salary)*12  from s_emp;
    sql中的除法不取整
    select  salary,salary/22 from s_emp;
  4.3数据库中的别名
    字段或者表达式的后面 加一个名字
    select  salary sal,(100+salary)*12 yearsal from s_emp; 
    字段或者表达式的别名只能有一个
    select  salary sal,(100+salary)*12 yearsal from s_emp;  
    要处理别名的原样显示 "别名"
    select  salary sal,(100+salary)*12 "year Sal" from s_emp; 
    4.4sql 中字符串的表达
    使用单引号 引起一串字符
    ''   ' '   'a'   'hello world'   '_'
    显示s_emp 表中first_name  last_name
    select first_name,last_name  from s_emp;
    字符串的拼接   ||(字符串拼接符)
    select  first_name||last_name from s_emp;
  @  _
    select  first_name||'@'||last_name from s_emp;
  first_name  和 last_name 之间拼接一个 '
    this  is   this's 
    %d  %lf %%
    select  first_name||''''||last_name from s_emp;
    first_name  和 last_name 之间拼接两个 '
    select  first_name||''''''||last_name from s_emp;
    select  first_name||''''||''''||last_name from s_emp;


4.5 null值的处理
    null 值和任何值做运算 结果都是null
    换一种年薪的计算方式
    12*salary+12*salary*(commission_pct/100)
12*salary*(1+commission_pct/100)
    列出每个员工的年薪 和 新的计算方式的年薪
    select  salary*12 yearsal,salary*12*(1+commission_pct/100) newys from  s_emp;
       
    4.6null值的处理  null值处理函数
null 值要尽早处理
    nvl(par1,par2)  par1,par2可以是任意类型，但要求类型保持一致。
    当par1为null 则返回par2的值
    当par1不为null 就返回值par1的值
select  salary*12 yearsal,nvl(salary*12*(1+commission_pct/100),0) newys from  s_emp;
select  salary*12 yearsal,salary*12*(1+nvl(commission_pct,100)/100) newys from  s_emp;  
  显示每个员工的 first_name  manager_id salary  如果manager_id 为null 则显示成-1
  select  first_name,nvl(manager_id,-1),salary from s_emp;


    4.7 数据的排重显示   distinct
sql语句只关心做什么 不管怎么做
select  salary from s_emp;
select  distinct salary from s_emp;

如何初始化sql编程环境
1.上传初始化脚本
2.运行脚本
SQL>@路径名/脚本名


显示 s_emp 表中 title  和 salary
select  title ,salary from s_emp;   
联合排重
select distinct title,salary from  s_emp;

/* error */ 
-- 这是单行注释
select title,distinct salary from s_emp;   
select distinct id,title,salary from s_emp;      
4.8 where子句
     限制表中的数据返回,符合where条件数据就被选中,不符合where条件数据就被过滤掉。
4.8.1两个极限条件
where  1=1;  /* 恒等 */
where  1=2;  /* 永假 */
select id,first_name from s_emp;
select id,first_name from s_emp where 1=1;
select id,first_name from s_empwhere 1=2;  //no rows  selected    
4.8.2 number 类型条件的书写
找出工资大于1400的员工,列出id  first_name  salary
select id,first_name,salary from s_emp where salary>1400; 
select id,first_name,salary from s_emp where salary<1400; 
select id,first_name,salary from s_emp where salary=1400;
select id,first_name,salary from s_emp where salary!=1400; 

4.8.3 字符串类型条件的书写
找出first_name 叫Carmen的 员工的id,first_name,salary
select  id,first_name,salary from s_emp where first_name='Carmen';
字符串类型 要注意单引号
字符串类型的值要严格区分大小写
sql语句不区分大小写        
SELECT  ID,FirSt_Name,salary from s_emp where first_name='Carmen';          
4.9 常见条件运算符
>   <   >=  <=  =  !=


4.9.1表达区间   [a,b]
where   字段  between a  and b;                     
s_emp 表中 salary在[1400,2500] 的员工的id ,salary 
select  id,salary from s_emp where salary  between 1400 and  2500;                 
4.9.1表达一个字段在一个范围内取值
where 字段 in (a,b,c,d)                 
部门编号是 31  或者 32  或者是50 的员工的信息。列出id,first_name,dept_id                   
select id,first_name,dept_id from s_emp where dept_id in(31,32,50); 
select id,first_name,dept_id from s_emp where dept_id in(32,31,50);
最终数据没有差别  但执行效率 可能受到极大的影响。出现概率高的放前面。
      
把s_emp表中id是1或者是10或者是25的员工查询出来 列出id  first_name
select id,first_name from s_emp where  id  in(1,10,25);
         
    5.0 模糊查询   like 
李嘉诚   李小龙  李刚   李成龙  史泰龙  龙龙   小龙女
ls  *.txt
ls  *.c 老
ls  *.*
5.1sql 中的统配符
%    0-n个任意字符
_    1个任意字符
     
找出姓李的人
where   name  like '李%';
name 中带龙的
where   name  like '%龙%';
倒数第二个是龙
where   name  like '%龙_';
找出s_emp  表中first_name 中带a的
select first_name from s_emp where first_name like '%a%';
找出s_emp  表中first_name 中第二个字符是a的。
select first_name from s_emp where first_name like '_a%';  
     
desc  user_tables;
select  table_name from user_tables;
s_emp   员工表
s_dept  部门表
从user_tables 表中 找出table_name 符合S_ 开头的表名.
     
select table_name from user_tables where table_name like 'S_%';
处理_ 或者 %的转义问题
select table_name from user_tables where table_name like 'S\_%' escape  '\'; 
从user_tables 表中 找出table_name 符合S__ 开头的表名. 
select table_name from user_tables where  table_name like 'S\_\_%' escape  '\';
    6.0 NULL 值的判断
     where  字段  is  null;
     如果字段的值 是NULL 就选中数据,如果不是NULL 就不选中。
     找出提成是10的员工的id first_name commission_pct 
     select  id,first_name,commission_pct from s_emp where commission_pct=10;
     select  id,first_name,commission_pct from s_emp where commission_pct!=10;     
     select  id,first_name,commission_pct from s_emp where commission_pct is null;
    7.0 逻辑条件 连接符号
     and 
        [a,b]
        where  字段名>=a and 字段名<=b;
        [1400,2500]
        select  id,first_name,salary
            from s_emp
                where salary>=1400 and 
                salary<=2500;        
     or
       员工的编号 是1 或者是10 或者是15
       的。列出id first_name salary
       select  id,first_name,salary
           from s_emp 
           where id=1 or id=10 or id=25;
     not 
     =      !=  ^=  <>
     >      <=   
     <      >=
     between a and  b  not  between a and b
     in     not in (注意NULL)
     like   not like 
     is null   is not null 
   4.3 数据排序
     4.3.1 order by   排序标准;
           永远出现在sql语句最后
     4.3.2 按照工资排序(升序 asc 
                        降序 desc) 
           显示 id first_name salary
     select  id,first_name,salary
         from  s_emp;                
     select  id,first_name,salary
         from  s_emp order by salary;   
     select  id,first_name,salary
         from  s_emp order by salary desc;   
    4.3.3 排序的语法
     order  by 排序标准  排序方式;
     排序方式:
     升序  asc  默认的顺序
         自然顺序   字典顺序
     降序  desc             
    4.3.4 多字段排序
     select  first_name,salary from s_emp
         order by  salary;
     如果salary的值相同 则启用名字降序排列
     select  first_name,salary from s_emp
         order by  salary,first_name desc; 
    4.3.5 NULL 值在排序中的处理
     NULL在排序中作为最大值 
     select  id,manager_id from s_emp
         order by manager_id;
  4.4  单行函数
     4.4.1 概念
     针对sql语句影响的每一行  都返回一个结果。
     sql影响多少行 就返回多少个结果。
     select  first_name,upper(first_name)  
         from s_emp;  
     select  first_name,upper(first_name)  
         from s_emp  where id=1; 
     select  first_name,upper(first_name)  
         from s_emp  where id>1; 
     select  first_name,upper(first_name)  
         from s_emp  where id<1;
     组函数:针对sql语句影响的一组数据做
     处理  最终返回一个结果。
     无论sql影响多少行 都返回一个结果。
     select count(id) from s_emp 
         where id=1; 
     select count(id) from s_emp 
         where id>1; 
     select count(id) from s_emp 
         where id<1; 
     4.4.2  处理字符串的单行函数
     upper   字符串变大写
     lower   字符串变小写
         select  first_name,lower(first_name)
             from  s_emp;
     测试表   dual
     select * from dual; 
     select  lower('HELLO') FROM  DUAL;
     concat(par1,par2) 连接两个字符串
         用的很少  因为有 ||
     select  concat('hello ','world') 
         from dual;
     select  concat(concat('hello ','world')
     ,' hello') from dual; 
     
     length   求字符串长度
     select  length('hello')  from dual;
     initcap  把每个单词的首字母变大写
     select   initcap('one world one dream')
         from dual; 
     substr(par1,par2,par3)
     par1要处理的字符串
     par2从什么位置开始截取 编号从1开始
         可以是负数  -1 代表最后一个字符
     par3 截取的长度
     select  substr('hello',0,2) from dual;
     select  substr('hello',1,2) from dual;
     select  substr('hello',-1,2) from dual;
     列出 s_emp 表中 first_name 以及
     first_name的后三个字符
     select  first_name,substr(first_name,
         -3,3)  from  s_emp;
     replace  替换
     select  replace('hello','lo','test')
         from dual;  
     nvl(par1,par2)
     4.4.3  数字处理函数
     round  四舍五入函数
     select  round(9.57)  from dual;
     select  round(9.99)  from dual;
     /* 保留小数点后一位 */
     select  round(9.57,1)  from dual;
     select  round(9.54,1)  from dual;
     select  round(9.547,2)  from dual;
     /* 对小数点前1位进行四舍五入*/
     select  round(12.88,-1) from  dual;
     select  round(12.88,-2) from  dual;
     trunc  截取  
     /* 截掉小数部分 */
     select  trunc(9.57)  from dual;
     select  trunc(9.99)  from dual;
     /* 截取 并保留几位小数 */
     select  trunc(9.57,1)  from dual;
     select  trunc(9.54,1)  from dual;
     select  trunc(9.547,2)  from dual;
     /* 截取 对小数前的第几位进行截取 */
     select  trunc(12.88,-1) from  dual;
     select  trunc(12.88,-2) from  dual;
     
     4.4.4  格式显示函数
     to_char(par1,par2)
     par1要处理数字数据  
     par2 是格式  可以省略  代表把数字变成
     字符串类型。如果有格式 则按照格式显示
     数字类型的数据。
     格式如下:
     fm   代表格式开头 可以省略
     9    小数点前代表0-9的任意数字
          小数点后1-9的任意数字
     0    小数点前 代表强制显示前导零
          12345  012,345     
          1234   001,234
          小数点后  代表0-9的任意数字 
     $    美元符号
     L    本地货币符号  ￥   RMB
     .    小数点
     ,    分隔符
     select  salary,to_char(salary,
     'fm$099,999.99')  from s_emp; 
     select  salary,to_char(salary,
     'fm$099,999.00')  from s_emp; 
     select  salary,to_char(salary,
     'fmL099,999.00')  from s_emp;
     如何修改数据库的本地语言
     1.远程登录服务器
       telnet   IP
     2.切换shell
       bash
     3.修改配置文件
       vi  .bash_profile
       export  NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
       export  NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
     4.保存退出
       esc
       shift+zz
     5.source  .bash_profile
     6.重新进入sqlplus 
         
     4.4.5  函数的嵌套
     把一个函数的返回值作为另一个函数的参数。
     把s_emp  表中 first_name 和 first_name 
     的后三个字符显示出来
     结合 substr  和  length
     select  first_name,substr(first_name,
       -3,3) from s_emp;
     select  first_name,substr(first_name,
       length(first_name)-2,3) from s_emp;
     列出s_emp 表中 id,first_name,manager_id
     如果manager_id 是NULL 则显示成BOSS
     select  id,first_name,
         nvl(to_char(manager_id),'BOSS') 
         from  s_emp;  
   4.5  多表查询
     4.5.1 为什么?
     需要的数据来自于多张表。
     表的设计决定的。
     4.5.2  表的介绍
     s_emp   员工表
     id       员工编号
     first_name  员工名
     dept_id     部门编号
     s_dept   部门表
     id       部门编号
     name     部门名
     列出每个员工的id  first_name和部门编号
     select  id,first_name,dept_id
         from  s_emp;
     列出每个员工的id  first_name和部门编号
     还要把部门名显示出来。
     字段如果重名则加表名区分
     /* 两张表所有的记录匹配的可能 
        笛卡尔积 */
      select  s_emp.id,first_name,dept_id,
          name
          from  s_emp,s_dept;    
    /* 表达表关系的条件 称之为 表的连接条件员工所在的部门编号是一个部门的编号
       dept_id=s_dept.id */ 
      select  s_emp.id,first_name,dept_id,
          name
          from  s_emp,s_dept
              where dept_id=s_dept.id;