ORACLE知识

环境变量
ORACLE_SID
sqlplus 客户端
user_name:amdin
password:admin
sqlplus command:
desc/describe －－
        －－buffer   sql 语句
list
edit －－vi 编辑器来修改 sql 语句
clear screen－－清屏
set pause on －－可实现分屏显示
set pause off －－取消分屏显示
set pause “Please enter Enter Key”－－增加提示信息
col{umn｝－－－定义选择的宽度，格式等。
sql 语句:
select first_name,salary from s_emp;
运行：；回车
当上一天语句没有以;结束，既未运行。可用/运行。
例：
SQL＞ select first_name
    2   form e_emp
SQL>/－－－－－此时可运行最后执行的那个 sql 语句
select 语句可用＋ － ＊ ／
要防止空值，所以要使用空值转换
NVL（start_date,'01-JAN-95')
NVL(title, 'No Title Yet')
NVL(salary,1000);
例算年薪
SQL>select first_name,salary*12*(1+nvl(commission_pct,0)/100) from s_emp;
给列取别名：
select first_name,salary*12 “annual salary”from s_emp;
    || 拼接字符
取别名时用双引号，拼字符时用单引号
select first_name||''||last_name
拼接可自动生成一些 sql 脚本。
Select 'select * from'||table_name from user_tabels;
Distinct －－排序功能,联合唯一？
Order By 字段名（别名，字段位置） DESC（降序）
where 子句 跟条件表达式。
比较运算符         ＝ ＞ ＞＝ ＜ ＜＝
字符串单引         字符串大小写敏感
注意：数据类型,逻辑关系，where 子句的关系。
Between and 是一个闭区间 ［ ］
IN 后跟集合，相当于 or 的意思　＝any
where (dept_id=41 or dept_id=42) and salary>1000
Like　通配符 ％０或多个符　＿任意一个字符
转义字符\
select table_name from user_tables where table_name like 'S\_%' escape '\';
IS NULL 判断一个字段为空
single Row Functions
字符函数：LOWER（‘SQL COURTSE’）转小
           UPPER
           INITCAP
           CONCAT('GOOD','String')   GoodString;
           SUBSTR('String',1,3)           Str
           LENGTH('String')        6
数值函数：ROUND 四舍五入
           TRUNC 截取
multiple row functions
日期处理：Century year,month,day,hours,minutes,seconds
      default date display is DD-MON-RR
      alter session set nls_date_format='yyyy mm dd hh24:mi:ss';设置 oracle 时间格式
                                         十分钟之后的时间
      select sysdate+1/144 from dual;
日期函数：
     MONTHS_BETWEEN(日期一，日期二)
     ADD_MONTHS（日期，加的天数）
     NEXT_DAY（日期，星期）例：next_day(‘’，‘friday')；
     LAST_DAY(日期)
     ROUND（显示的日期，按年或月截取）什么都无就截掉时分秒
     TRUNC   (显示的日期，按年或月截取)
                            将日期转成字符 fmt—转换的格式 YEAR DAY,MM,HH24:MI:SS
     TO_CHAR(DATE,'fmt')
AM
                                                              ddspth DD “of” MONTH
     select to_char(start_date,'YEAR') from s_emp
                        将字符转为日期
     TO_DATE('','fmt')
     insert into test values(to_date('2009 01 10 20:29:40','yyyy mm hh24:mi:ss')
日期是格式敏感的               那么 钱呢？     货币？
      千年虫         RR 格式在跨世纪时将不会出现问题。YY 就会出现问题！
Y2K
  字符和数字的转换：                                    格式有：9，0，＄ L . ，
                         to_char(number,'fmt')
   TO_NUMBER('字符'，格式)
业务需求要变成 sql 语句
Oracle
vi 关键命令
a 下一字符编辑
i 插入
o 换行输入
          左，下，上，右
h,j,k,l
r 替换
d 删除，dd 删除一行
u 撤消
：wq 保存退出
执行脚本：sqlplus asd0811/asd0811 @demobld.sql
Join
内连接：严格匹配
等值连接：
表里数据要对应能找到
select e.ename,s.grade from emp e,salgrade s where e.salary between s.hightsal and
s.lowsal
自连接：select e.first_name “Employee”,m.first_name “Manager”
         from s_emp e,s_emp m
         where e.manager_id（＋）=m.id
外连接：一个也不能少 （＋）
连接要注意表结构和表关系，很重要！
Group Functions 多行函数 所有组函数处理的都是非空值
AVG（DISTINCT｜ALL｜n）
COUNT（DISTINCT｜ALL｜expt|*)
MAX（DISTINCT｜ALL｜expr)
MIN（DISTINCT｜ALL｜expr)
SUM（DISTINCT｜ALL｜expr)
                   处理非空值
COUNT（dept_id)
COUNT（distinct dept_id） 处理非空的且排除重复的
group by 字段     按字段分组         字段越多分的粒度越细，结果集越少！
有了 Group by 子句 select 后跟字段与 group by 后字段一样
＜＞不等
        过滤方式
having
与 where 区别。where 过滤的是一行一行的记录。单行函数
having 过滤的是分组后的结果。组函数
group by 与 having 有严格顺序
子查询：select last_name,title from s_emp where title = (select title from s_emp
                                                      where last_name='Smith')
                                                  and last_name<>'Smith'
查询平均公资大于部门号是 32 号的部门
select dept_id,avg(salary) from s_emp group by dept_id
having avg(salary) > (select avg(salary) from s_emp where dept_id=32)
select table_name from user_tables;查看有多少表
distinct 排重
数字＋ － ＊ ／ 字符 拼接 ｜｜
％ 任 0 个或多个 ＿任一个
转义字符 \
order by 默认是升序         desc 表降序
后跟字段。或字段的序号                order by 2 表选择的第二个字段
select e.first-aname,m.first_name from s_emp e left outer join s_emp m on
e.manager=m.id;
left outer join; full outer join right outer join;inner join
组函数忽略空值，但不排重！
数据库的设计
懂业务，懂技术，B/S,C/S               设计。
Design E-R 实体关系图－－－＞表（sql 脚本）---->存数据库
通过流程帮助客户提出需求。
Build and Document
Database Design
Performance 性能        Integrated application 集成    Intergration with other system
选型
。。。
Entity 实体     一个抽象的概念
Examples: customers,sales
            属性    描述实体
Attribute
Relationship 关系
Examples: orders and items,customers and sales
表名大写，属性小写。 Unique Identifier # 表不能重复 唯一 Mandatory marked with *
必需要有。非空
                       Primary marked with #
                                                可有可无，
                       Opitonal marked with o
        虚线表 may be 实线表 must be
    one to one one to many many to many
范示：
第一范示（first normal form）：all attributes must be single-valued 每一个属性只
有一个值 会有数据冗余
第二范示(second normal form)：an attribute must depend upon its entity's entire
UID 在第一范示基础上增加了一个主属性标识。会有数据冗余。非主属性不完全依懒于主
属性，非主属性与非主属性之间存在依懒关系。
第三范示(three normal form)：No mon-UID attribute can be dependent upon another
non-UID attribute 在第二范示基础上，非主属性严格依懒于主属性
看需求来确定使用何种范示
完整性约束
primary key ，unique key ,foreign key
主键约束－－－唯一且非空。用来标识一条记录。一张表只能有一个主键。
        联合主键 composite primary key
外键－－－foreign key 唯一可非空。外键是主键一部分时，不能为空。If an FK is
part of a PK,then it cannot be NULL.
唯一性约束,非空约束，检查性约束。
PK,UK,FK,CK,NN
用户自定义约束             触发器
数据库设计步骤：
约定命名习惯
设计索引 design the indexes
定义视图       establish view definitions
数据空间存储          plan the physical storage space
定义约束         redefine integrity constraints
E-R 图转换成数据表:
Map Entities to Tables table
Map the attributes to Columns
Map Unique Identifiers to Primary Keys
Map Relationships to Foreign Keys
TABLE
create table
datatype: varchar2 char,number,
char 定长，varchar2 变长。
没有指定数字宽度 number(4,2) 4 表示有效位数，3 表示多少位小数（四舍五入）。
Insert into test(c2) values (123)---错误。超出了有效位。
字符串用单引号引起 空用 NULL，
CLOB---Character date up to 4GB
BLOB—Binary data up to 4GB
DATE
dual is a dummy table used to view sysdate.
Default date display is DD-MON-RR
YYYY     YEAR  MM    MONTH    DY   DAY
函数：
  to_date
  to_char
select first_name,to_char(start_date) from s_emp where to_char(start_date,'MM'))='03'
                                                        to_char(start_date,'fmmm')=
'3'
RR 时间保证跨世纪：                       0－49     50－99
                                  当前
                        0－49                Before
                                            当前
                        50－99      After
has an fm element to remove padded blanks or suppress leading zeros 前导零
select to_char(trunc(last_day(sysdate)+1),'yyyy-mm-dd hh24:mi:ss') from dual; 下个月的
第一天。
INDEX 索引
每条记录的地址
创建 1：自动创建 define a PRIMARY KEY or UNIQUE constraint
创建 2：手工创建 create non-unique indexes
       CREATE  INDEX test_ind_name ON test(name);
user_indexes 系统索引表        user_ind_columns 约束索引字段表
适合建索引的情况：字段列常用作查询，字段列值范围广，大量的空值，有时要使用联合
索引，并非越多索引效率越好。
不适合建情况：表太小，字段列不常用作查询条件，常更新
UNIQUE 唯一性索引
Non-unique 非唯一性索引，加快查询速度
               单值索引
Single column
                            多值索引
Concatenated or composite
DROP INDEX 删除索引
视图：CREATE VIEW      myview AS SELECT * FROM test WHERE id<10;
视图不占空间， 可简化查询操作。
CREATE OR REPLACE VIEW    empinfo(id,enam,rname) AS SELECT e.id,e.first_name,r.name
FROM s_emp e,s_region r WHERE ....; 创建或替换
视图里是聚合信息时不能插入单个细节记录。
基表不存在也可以创建视图用 CREATE FORCE VIEW myview                  默认是 NOFORCE。
CREATE OR REPLACE VIEW myview AS SELECT ...FROM     WHERE .
WITH CHECK OPTION CONSTRAINT emp_ck. 视图添加约束。约束为 WHERE 条件。 符号 V
WITH READ ONLY 只读视图。O
字查询里不能用 ORDAR BY。
Inline view 内联视图
哪些员工的工资比本部门的平均工资高？
SELECT first_name,salary,avg
  2   FROM s_emp,(SELECT dept_id,avg(salary) avg
  3                       FROM s_emp
  4                       GROUP BY dept_id) a
  5   WHERE s_emp.dept_id=a.dept_id
  6* AND salary>avg
    rownum 记录的位置，必须等于 1 或＜＝一个数
  工资排名前五名
  1   SELECT first_name,salary
  2   FROM (SELECT rownum,first_name,salary
  3   FROM s_emp
  4   ORDER BY salary DESC) a
  5* WHERE rownum<=5
分页显示
SELECT r,a.*
FROM (SELECT rownum r,aa.*
      FROM (SELECT *
                FROM s_emp) aa
         )a
WHERE r BETWEEN 1 AND 10
例 2：
SELECT r,first_name,salary
FROM ( SELECT rownum r,first_name,salary
FROM ( SELECT first_name,salary
FROM s_emp
ORDER BY salary DESC
)
WHERE rownum<=20
)
WHERE r BETWEEN 16 AND 20
排名相同的情况
              去除掉重复后的排名，有两个 3 名就无第四名。
rank() over
dense_rank() over() 按顺序排名 有两个 3 名。第五名就将成为第四名
SELECT dense_rank() over(ORDER BY salary DESC) r,first_name,salary FROM s_emp;
limit
关联子查询：
who earn more than the average salary in their department 思路：将部门号传给子查
询取到部门的平均值。
SELECT last_name,salary,dept_id
  2   FROM s_emp outer
  3   WHERE salary > (SELECT AVG(salary)
  4   FROM s_emp
  5   WHERE dept_id=outer.dept_id)
  6   ;
         子查询是否存在。存在为真，不存在为假。
EXISTS
             不存在为真，存在为假。
NOT EXISTS
SELECT first_name
  2   FROM s_emp outer
  3   WHERE EXISTS (
  4   SELECT manager_id／常量。
  5   FROM s_emp inner
  6   WHERE outer.id=inner.manager_id);
chooes all courses
SELECT name FROM students s WHERE NOT EXISTS (SELECT 'x' FROM courses c WHERE
NOT EXISTS (SELECT 'x' FROM stu_cou sc WHERE s.id=sc.sid AND c.id=sc.cid) )
1 查询 zhaojun 同学有哪些课程没有选择
2 查询哪些同学          选择了 zhaojun 同学选的课程
3 查询哪些同学选择了和 zhaojun 同学相同的课程
1 查询 zhaojun 同学有哪些课程没有选择
SELECT name
FROM course c
WHERE  NOT EXISTS (SELECT 1
              FROM stu_course sc,student s
         WHERE sc.s_id=s.sid AND s.name='zhaojun' AND sc.c_id=c.cid);
2 查询哪些同学(name) 选择了 zhaojun 同学选的课程
SELECT DISTINCT s_id
FROM stu_course outer
WHERE c_id IN (SELECT c_id
               FROM stu_course sc WHERE sc.s_id=2)
SELECT DISTINCT s_id
FROM stu_course outer
WHERE EXISTS (SELECT 1
               FROM stu_course sc WHERE sc.s_id=2 AND outer.c_id=sc.c_id);
3 查询哪些同学选择了和 zhaojun 同学相同的课程
SELECT s_id
FROM stu_course a
WHERE NOT EXISTS(SELECT DISTINCT s_id
                   FROM stu_course outer
                   WHERE NOT EXISTS (SELECT 1
                                          FROM stu_course sc WHERE sc.s_id=2 AND
outer.c_id=sc.c_id) AND a.c_id=sc.c_id));
4 查询哪些同学选的课程不合 zhaojun 一样。
SELECT DISTINCT s_id
FROM stu_course outer
WHERE NOT EXISTS (SELECT 1
                 FROM stu_course sc WHERE sc.s_id=2 AND outer.c_id=sc.c_id);
也可以用 NOT IN
SELECT DISTINCT s_id
FROM stu_course outer
WHERE c_id NOT (SELECT c_id
                 FROM stu_course sc WHERE sc.s_id=2)
但此时如果有 zhaojun 同学一门课也没选，空记录将查不到
UNION 排重/UNION ALL 不排重。           联合      联合类型要一致。
            交集。
INTERSECT
          集合相减。
MINUS
ALTER 修改表结构。
ALTER TABLE ADD column 增加一个字段
ALTER TABLE test DROP　字段
                                       修改字段名；
ALTER TABLE test RENAME COLUMN
                  。。MODIFY (title varchar2(50))     修改字段类型
ALTER TABLE
ALTER TABLE s_emp     ADD CONSTRAINT s_emp_manager_id_fk
                                                    增加约束
FOREIGN KEY (manager_id)    REFERENCES s_emp(id)
ALTER TABLE s_emp DROP CONSTRAINT s_emp_manager_id_fk; 删除约束
                                                    级联删除主键。
ALTER TABLE s_dept DROP PRIMARY KEY CASCADE;
                                                                  使约束失效
ALTER TABLE s_emp DISABLE CONSTRAINT s_emp_id_pk CASCADE
                                                         使约束恢复有效
ALTER TABLE s_emp ENABLE CONSTRAINT s_emp_id_pk;