Oracle 笔记

一、安装数据库的注意事项

1、路径不能存在空格和中文
2、添加6.2的版本
3、主要包括安装数据库软件和建库

二、使用Oracle数据库的注意事项

1、在服务器端配置监听器(安装是自带)，在客户端(Net Manager或Net Configuration Assistant)配置网络服务名
2、在服务器端启动数据库服务和监听服务

三、连接数据库的工具

解锁：alter user 用户名 account unlock;

1、SQL*PLus  登录：用户名[@网络服务名 as 登录身份]  如: sys@jbit as sysdba
   (1)查看当前连接数据库的用户
      show user;
   (2)切换用户
      conn 用户名/密码 [as sysdba]
  
      注：管理员加 as sysdba，普通用户不加

   (3)用户登录后查看用户下的表
      select * from tab;

      注：为了防止命令行中数据展示表格错乱的情况可以设计行宽和列宽
          set linesize 300;  --每行展示300个字符
          Col 列名 for a[数字];  --某一列占几个字符

   (4)查看表结构
      desc 表名;

2、PL/SQL Developer

四、Oracle常用的数据类型

字符类型：char/nchar、varchar2/nvarchar2，不建议使用varchar
数值类型：number，不建议使用integer、float、double
日期类型：date、timestamp
LOB类型：blob、clob(存放大文本，如新闻)、bfile

五、Oracle中的伪劣

rowid：存放一行数据的地址(唯一的)
rownum：行号

分页：每页显示5条数据、查询第三页的数据

方式一：

   select * from (select rownum r,a.* from a) b
   where b.r>10 and b.r<16

方式二：

   select * 
   from (select rownum rm, a.* from (select * from emp) a 
         where rownum < 16) b 
   where b.rm > 10

注：给表或列起别名时使用空格“ ”
    如果查询的列中包含行号和其他列，则其他列必须使用表名.类名

六、SQL语言的组成部分

DML(数据操作语言)：对数据的增删查改
DDL(数据定义语言)：建表、删表、修改表结构、截断表(truncate table)等操作
TCL(事物控制语言)：commit、savepoint、rollback
DCL(数据控制语言)：grant(设置权限)、revoke(撤销权限)

七、数据操作语言案例

1、不重复显示所有学员姓名和年龄(关键字：distinct)

  select distinct stuName,stuAge from stuInfo;

2、取出stuName,stuAge列不存在重复数据的记录

  select stuName,stuAge 
      
  from stuInfo
      
  group by stuName,stuAge 
      
  having(count(stuName||stuAge) <2);

3、删除stuName、stuAge列重复的行(保留一行)

  delete
      
  from stuInfo 
      
  where rowid  not in(
	select max(rowid)
	from stuInfo
	group by stuName,stuAge
	having(count(stuAge||stuAge)>1)

	union
	select max(rowid)

	from Stuinfo

	group by stuName,stuAge
	having(count(stuAge||stuAge)=1)     
      
);



  实现思路：查找重复的记录，只保留一行(rowid)
           查找不重复的记录(rowid)
           将重复且保留一行的记录和不重复的记录使用union关键字合并结果集
           删除结果集中没有的记录

八、事务控制语言案例

insert into dept values(50,'a',null);

insert into dept values(60,'b',null);
savepoint a;                          --设置保存点

insert into dept values(70,'c',null);

rollback to savepoint a;              --回滚到指定保存点处，表示直接滚回保存点之后的数据操作

commit;
                           --提交事务

九、DDL语句和mysql的基本一致

(1)表的修改
   添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
   修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
   修改列名: ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2 

(2)添加约束
   设置主键：constraint person_pk_pid primary key(列名)
   唯一约束：constraint person_name_uk unique(列名)或添加列时后面写 unique
   检查约束：constraint person_gender_ck check(gender in (1,2))或添加列时后面写check(gender in (1,2))
   外键约束：constraint order_detail_order_id_fk foreign key(子表的外键) references users(主表的主键)

十、SQL操作符

(1)算术操作符：+、-、*、/
(2)比较操作符：>、<、>=、<=、=、!=或<>、is null/is not null
(3)逻辑操作符：and、or
(4)连接操作符：||
(5)集合操作符
   1)并集(UNION)
     select empno from emp 
     UNION 
     select rempno from retireEmp;

   2)交集(INTERSECT，两张表关联的数据)
 select empno from emp  
     INTERSECT
     select rempno from retireEmp;
   
   3)减集(MINUS,两张表不关联的数据)
     select empno from emp  
     MINUS
     select rempno from retireEmp;

十一、函数

 (1)转换函数
    select TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')

from dual;
注：fm去掉前面的零
select TO_CHAR(1210.7, ‘$9,999.00’) from dual;

    select TO_DATE('2005-12-06','yyyy-mm-dd') from dual;


    select TO_NUMBER('100') from dual;

 (2)转换空值的函数

    计算员工的年薪：(如果薪水为NULL，与任何数相加都是null)
    select ename,
           sal+NVL(comm,0) sal1,

           NVL2(comm,sal+comm,sal) sal2,
           DECODE(to_char(hiredate, ‘MM ’),
                  '01', '一月', '02', '二月',
                  '03', '三月','04','四月',
                  '05','五月','06','六月',

                  '下半年') mon

    from employee;
    

 (3)字符函数
    lower()、upper()、initcap()【首字符大写】
    concat()【连接】、substr("hello",1,3)【截取】
    length()、replace()
 (4)数值函数
    trunc()：取整，也可指定小数保留的位数，如：trunc(123.11)
    round()：四舍五入
    mod()：取余数  
 (5)日期函数
    MONTHS_BETWEEN()：获取两个日期之间的月数
    ADD_MONTHS()：获取几个月后的日期
     

 (6)聚合函数
    count()、sum()、avg()、max()、min()  

 (7)分析函数【rank()、dense_rank()、row_number()】
    select ename, deptno, sal,
   		--按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同,
   		--且和下一条不同记录的排名之间空出排名
  		 RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK",
   		--按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同,
   		--且和下一条不同记录的排名之间不空出排名
   		DENSE_RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC) "DENSE_RANK",
   		--按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号继续
   		--递增，顺序排名
   		ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY sal DESC) "ROW_NUMBER"
from emp;       

十二、排序中的空值问题

 排序  order by  
 经验：当排序时存在null时就会产生问题  使用：nulls first ,  nulls last

 --查询雇员的工资从低到高
 select * from emp order by sal nulls first;(null值的数据在前)

 select * from emp order by sal desc nulls last ;(null值的数据在后)