Oracle数据库

day01Oracle day1
20120214
=======================
宁丽娟 [e-mail]

程序员
DBA

一.什么是数据库
软件 = 数据仓库 + 管理软件
Database : 数据库
DBMS: DataBase Management System

Oracle
DB2(IBM) Sybase(sybase)
mysql(Oracle)  sqlserver(MS)
sqlite(嵌入式数据库)
access

二.访问数据库
1.telnet远程登录到数据库服务器
C:>telent 192.168.0.26
openlab/open123
(这个步骤和数据库无关)

2.sqlplus连接数据库
sqlplus是数据库的命令行客户端工具
% sqlplus
数据库的帐号/密码: openlab/open123
SQL> 

3. 表(table)
1)建表
--最长30个字符.
--数字,字符,日期
create table users_ning(
id number,
pwd char(4),
name varchar(20),
phone varchar(30),
email varchar(50)
);

number  数字类型
char / varchar 字符类型
       varchar2
date    日期类型

2)插入数据
insert into users_ning
values(1001, '1234', 'liucs',
'13800000000',
'[e-mail]');

SQL语句: Structured Query Language
SQL>create table  建表
SQL>insert into...插入数据
SQL>select ...from... 查询
SQL>commit  提交

三.查询数据
1)准备数据
create table dept_ning1(
deptno number(2),
dname varchar2(20),
location varchar2(20)
);
insert into dept_ning1
values(10, 'developer', 'beijing');
insert into dept_ning1
values(20, 'finance', 'shanghai');
insert into dept_ning1
values(30, 'sales', 'guangzhou');
insert into dept_ning1
values(40, 'operation', 'tianjin');
commit;
select * from dept_ning;

create table emp_ning100(
empno number(4) primary key,
ename varchar2(20),
job varchar2(20),
salary number(7,2),
bonus number(7,2),
hiredate date default sysdate,
manager number(4),
deptno number(2)
);

insert into emp_ning100
values(1001, 'zhangwj', 'Manager',
10000, 2000, '12-MAR-10', 1005, 10);
--继续插入其他9条记录
insert into emp_ning100
values(1011, 'tom', null,
null, null, null, null, null);
--如果null值太多,可以简写为:
insert into emp_ning100
(empno, ename) values(1011,'tom');



commit;

--复制表
--将emp_ning100复制为emp_ning200
create table emp_ning200
as
select * from emp_ning100;
2)学习查询语句
--按薪水排序,由高到低
select * from emp_ning
order by salary desc;

--null值被视作最大.

--按入职时间排序
select ename, hiredate from emp_ning
order by hiredate

public Integer nvl(
    Integer bonus,Integer value){
if (bonus == null)
return value;
else
return bonus;
}
--查询员工的月收入,处理null值
--如果薪水或奖金是null值,按0计算
select ename, salary, bonus,
nvl(salary, 0) + nvl(bonus, 0)
as month_sal
from emp_ning
order by salary desc;

select ename,job from emp_ning
where upper(job) = 'ANALYST';
      lower(job) = 'analyst';
--in (list)
select ename, job
from emp_ning
where lower(job) in ('analyst',
'programmer', 'clerk');

--查找部门10或部门20的员工
select ename from emp_ning
where deptno in (10, 20);

--查找薪水在5000到8000之间的员工
--包括5000和8000.
select ename, salary
from emp_ning
where salary >= 5000
  and salary <= 8000;
--[5000, 8000] 闭区间
select ename, salary
from emp_ning
where salary between 5000
      and  8000;

//
--
#
<!-- 注释 -->

空值:
select ename, salary
from emp_ning
order by salary desc;

select ename
from emp_ning
where salary is null;

select ename
from emp_ning
where salary is not null;

select count(*) from user_tables;
--所有名字中包含emp的表的个数
select count(*) from user_tables
where table_name like '%EMP%';

--查找书名里包含java的书
select book_name from books
where lower(book_name)
      like '%java%';

select ename, salary,
    salary * 0.12345678 as tax
from emp_ning;

round : 四舍五入
trunc : 截取


张三  6  5  7  8    6.5
李四  6  3  7  7    5.75 -> 6
                    5.2  -> 5

日期:
sysdate: 系统时间
'14-FEB-12' : oracle默认的日期格式
DD-MON-RR
now: 2012年
         YY      RR
95年    2095    1995
05年    2005    2005

select to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss')
from dual;

select ename, hiredate,
to_char(hiredate, 'yyyy-mm-dd') h
from emp_ning;

小结:
1.DDL(Data Definition Language): //数据定义语言
create / drop
2.DML(Data Manipulation language)://数据操作语言
insert / update / delete
3.DQL(Data Query Language)://数据查询语言
select
4.TCL(Transaction Control Language)//事物处理控制语言
commit


--java程序员
SCJP: sun certification
      java programmer
OCJP: Oracle ....

google: scjp 模拟题

--DBA
OCP: Oracle certification Professor



day02Oracle day2
20120215
=====================
一.Oracle中的日期处理
select to_char(sysdate, 'yyyy-mm-dd')
from dual;

select ename, hiredate,
round(sysdate - hiredate) days
from emp_ning;

select sysdate - 10 from dual;

select ename, hiredate,
round(months_between(sysdate, hiredate))
as mons
from emp_ning;

--函数的嵌套
f4(f3(f1(f2(p1,p2)), p3), p4)

concat(concat(concat(concat('a','b'),'c'),'d'), 'e')

'a' || 'b' || 'c' || 'd' || 'e'

select 'hello' || 'world' from dual;

--计算三个月以后的时间
select add_months(sysdate, 3)
from dual;

select last_day(sysdate) from dual;

--修改tom的入职时间是12年2月1号
update emp_ning
set hiredate =
to_date('2012/02/01','yyyy/mm/dd')
where ename = 'tom';

--增加一个职员:
--1012, 'jerry', '2012-01-12'
insert into emp_ning
(empno, ename, hiredate)
values(1012, 'jerry',
to_date('2012/01/12','yyyy/mm/dd')
);

to_date('2012-01-12','yyyy-mm-dd')
to_char(sysdate, 'yyyy-mm-dd')

      to_char       to_number
日期  -------> 字符 --------> 数字
      <------       <--------
      to_date        to_char

    to_char         to_number
10000 --> $10,000.00 --> 10000
数字      字符           数字


2012-02-01
2012/02/01

04/05/10

二.单行函数计算
nvl(bonus, 0)
upper(job) = 'ANALYST'
round(salary, 2)
to_char(sysdate, 'yyyy/mm/dd')
to_date('2012-01-12','yyyy-mm-dd')
select ename, salary, bonus,
coalesce(bonus, salary * 0.5, 100)
as bo
from emp_ning;

coalesce(list):
返回参数列表中第一个非空值

0-9分
create table ielts_ning(
name char(10),
s1 number(2,1),
s2 number(2,1),
s3 number(2,1),
s4 number(2,1)
);
insert into ielts_ning
values('A', 8, 8, 6, 7);
insert into ielts_ning
values('B', 6, 6, 7, 7);
insert into ielts_ning
values('C', 6, 7, 7, 7);
insert into ielts_ning
values('D', 8, 6.5, 6, 6);

姓名 听力 阅读 写作 口语 总分
name  s1  s2   s3    s4
      number(2,1)
张三 8    8    6     7  7.25-> 7.5
李四 6    6    7     7  6.5 -> 6.5
     6    7    7     7  6.75-> 7
                        6.125 -> 6
                        6.625  6.5

[0, 0.25) [0.25, 0.75) [0.75, 1)
   0            0.5       1

select name, s1, s2, s3,s4,
      (s1+s2+s3+s4)/4  s
from ielts_ning;

7.25
整数位: trunc((s1+s2+s3+s4)/4) -> 7

小数:mod((s1+s2+s3+s4)/4, 1) -> 0.25

select name, s1, s2, s3, s4,
trunc((s1+s2+s3+s4)/4) +
case when mod((s1+s2+s3+s4)/4, 1) < 0.25       then 0
     when mod((s1+s2+s3+s4)/4,1)>=0.25              and 
          mod((s1+s2+s3+s4)/4,1)<0.75
     then 0.5
     when mod((s1+s2+s3+s4)/4, 1) >= 0.75      then 1
end as total_s
from ielts_ning;

double calculate(double score){
    int i = trunc(score);//整数
    double j = mod(score, 1); //小数
    double result = 0;
    if (j < 0.25)
       result = i;
    else if ( j >= 0.25 && j < 0.75)
       result = i + 0.5;
    else if ( j >= 0.75)
       result = i + 1;
    return result;
}

create or replace function calculate_ning(score number)
return number
is
  --定义变量
  i number; --整数
  j number; --小数
  result number; --返回结果
begin
  --程序体
  i := trunc(score); --数据库中赋值:=
  j := mod(score, 1);
  if j < 0.25 then
     result := 0;
  elsif j >= 0.25 and j < 0.75 then
     result := 0.5;
  elsif j >= 0.75 then
     result := 1;
  end if;
  return i + result;
end;
/
Function Created. 表示创建成功
Function Created  with Compil...编译错误
SQL>show errors 检查错误信息
修改后, 再次执行, 直到创建成功为止

--函数建立以后,在sql语句中使用
select name, s1, s2, s3, s4, calculate_ning((s1 + s2 + s3 + s4) / 4) from ielts_ning;

--当数据库中提供的函数不够用时,可以创建自己的函数
--和java中定义自己的方法是一样的道理.
nvl                     length()
upper                   iterator()
round                   indexOf()
calcaulate_ning         random()
.....                   code15To18()


第一部分: SQL 语句
第二部分: PL/SQL: 在数据库中编程
           包括:funcation 函数
                procedure 过程
                package   包
                trigger   触发器

select ename, job, salary,
case job when 'clerk' then salary * 1.05
         when 'Programmer' then salary * 1.1
         when 'Analyst' then salary * 1.15
         else salary
end as new_sal
from emp_ning;

--用case实现
case when job = 'clerk' then salary * 1.05
     when job = 'Programmer' then salary*1.1
     when job = 'Analyst' then salary * 1.15
     else salary
end

--用decode函数实现
select ename, job, salary,
decode(upper(job), 'CLERK', salary * 1.05,
            'PROGRAMMER', salary * 1.1,
            'ANALYST', salary * 1.15,
            salary) as new_sal
from emp_ning;

三.分组函数计算
count

--求某列的最大值
select max(salary) from emp_ning;

select max(s1) from ielts_ning;
select max(calculate_ning((s1+s2+s3+s4)/4))
from ielts_ning;

select min(salary) from emp_ning
select min(s1) from ielts_ning;

--组函数忽略空值
select avg(nvl(salary, 0))
from emp_ning;
select sum(salary) / count(salary)
from emp_ning;

count / sum / avg / max / min

select deptno, count(*)
from emp_ning
where deptno is not null
group by deptno;
--没有group by短语,语法错误
select deptno, count(*)
from emp_ning
where deptno is not null;
--没有语法错误,信息不全
select count(*)
from emp_ning
where deptno is not null
group by deptno;

--查询各个部门中的最多人数
select max(count(*))
from emp_ning
where deptno is not null
group by deptno;

--哪个部门的人数最多?
select deptno, count(*)
from emp_ning
where deptno is not null
group by deptno
having count(*) = 4;

--查找人数最多的部门号
--10  6
select deptno, count(*)
from emp_ning
where deptno is not null
group by deptno
having count(*) = (
           select max(count(*))
           from emp_ning
           where deptno is not null
           group by deptno);

--人数最多的部门的名字和工作地点
select dname, location
from dept_ning
where deptno = 10;

select dname, location
from dept_ning
where deptno = (
select deptno, count(*)
from emp_ning
where deptno is not null
group by deptno
having count(*) = (
           select max(count(*))
           from emp_ning
           where deptno is not null
           group by deptno));

select 列, 组函数
from 表
where 条件
group by ...
having...
order by...

--表里没有的数据,需要计算的数据做条件,用having
--表里有的数据, 做条件,用where
--所有的组函数做条件,必须用having


10   10000
20   5000
30   8000
.....

7000
--哪些部门的平均工资比整个机构的平均工资高?
--查询的条件是平均工资,是组函数计算出来的结--果,所以使用having, 而不是where
select deptno, avg(nvl(salary,0))
from emp_ning
group by deptno
having avg(nvl(salary,0)) > (
               select avg(nvl(salary,0))
               from emp_ning);

--哪个部门的人数超过5个人?
select deptno, count(*)
from emp_ning
group by deptno
having count(*) > 5;

--哪个部门的薪水总和比部门20的薪水总和高?
select deptno, sum(salary)
from emp_ning
group by deptno
having sum(salary) > (
select sum(salary)
from emp_ning
where deptno = 20);
name job     salary
a   android  8000
b   java     5000
c   c++      6000
d   java     10000
e   android  9000
....
select job, avg(salary)
from mytable
group by job;


四.子查询
select ename from emp_ning
where salary = (select max(salary)
                from emp_ning);

--谁的薪水比tom高?
select ename from emp_ning
where salary > (select salary
from emp_ning
where ename = 'tom');

insert into emp_ning(empno, ename, salary)
values(1020, 'tom', 5000);

--再执行
--出错:ORA-01427: 单行子查询,返回了多于一行记录.

--改进:
select ename from emp_ning
where salary > (select salary
from emp_ning
where ename = 'tom'
                and deptno = 10);

--子查询结果多于一条记录,
3000
5000
--比两条记录都大: all
--比任何一条大都行: any
select ename ,salary from emp_ning
where salary > all (select salary
from emp_ning
where ename = 'tom');

--谁和tom同部门?
select ename, deptno
from emp_ning
where deptno in (select deptno
from emp_ning
where ename = 'tom')
and ename <> 'tom';

--谁的薪水最高?
select ename from emp_ning
where salary = (select max(salary)
from emp_ning);

--每个部门薪水最高的是谁?
select ename, salary, deptno
from emp_ning
where (deptno, salary)
in(
select deptno, max(salary)
from emp_ning
group by deptno);

--把部门10和20的两个tom薪水都改成10000
update emp_ning
set salary = 10000
where ename = 'tom';

--再查, 查看结果

数据结构 线性表, 树, 图
范式 1NF  2NF  3NF
day03Oracle day3
20120216
==========================
select distinct/列名/表达式/单行函数/组函数/
from 表名
where 条件(子查询)1 or 条件2
      and 条件3
group by 列名
having 组函数的条件
order by 列名/列别名/表达式/组函数

select deptno, count(*) c
from emp_ning
group by deptno
--order by c;
order by 2;

--每个班的学生人数?
1    4
2    2
3    73
4    65
5    48
--把班级人数>50人的班级查出来
select class_id, count(*)
from t_student_ning
group by class_id
having count(*) > 3
order by 2;

--每个班的成绩比例加起来是否=100?
--百分之百
1    100
2    100

select class_id, sum(scale)
from t_assess_rule_ning
group by class_id;

--每个学生的总分?按总分由高到低排序
3     173
2     171
1     157

1    1    90
1    2    87
1    3    75
1    4    67
1    5    92
1    6    95

select student_id, sum(test_score)
from t_performance_ning
group by student_id
order by 2 desc;

一.子查询
--谁的总成绩比1号学生的总成绩高?
select student_id, sum(test_score)
from t_performance_ning
group by student_id
having sum(test_score) > (
select sum(test_score)
from t_performance_ning
where student_id = 1
);

--谁的薪水比公司的平均薪水低?
select ename, salary
from emp_ning
where salary < (select avg(nvl(salary,0))
                from emp_ning);

--谁的薪水比本部门的平均薪水低?
--关联子查询
select ename, salary, deptno
from emp_ning x
where salary < (
         select avg(nvl(salary,0))
         from emp_ning
         where deptno = x.deptno         
         );

--谁的薪水比同经理的员工平均薪水低?
select ename, salary, manager
from emp_ning x
where salary < (
         select avg(nvl(salary,0))
         from emp_ning
         where manager = x.manager      
         );

张三  8000    10    1001        
李四  5000    20    1001
王五  15000   30    1001
赵六  10000   10    1002

5000

10 12000
20 5000
30 8000
.....


--哪些员工是别人的经理?
exists: 子查询是否有结果返回
        有  true
        没有 false

select empno, ename
from emp_ning x
where exists (select 1
              from emp_ning
              where manager = x.empno);

--哪些人不是别人的经理?
select empno, ename
from emp_ning x
where not exists (select 1
              from emp_ning
              where manager = x.empno);

--哪些部门没有员工?
--判断依据: 部门表的编码,没有出现在职员表的部门编码列中.
--使用 not exists
select deptno, dname, location
from dept_ning x
where not exists (
select 1
                from emp_ning
                where deptno = x.deptno);


集合操作:

select distinct deptno from emp_ning;

集合的合集: union / union all
集合的交集: intersect
select ename, salary, deptno
from emp_ning
where deptno = 10
intersect
select ename, salary, deptno
from emp_ning
where salary > 8000;

二.多表联合查询

select emp_ning.*, dept_ning.*
from emp_ning join dept_ning
  on emp_ning.deptno = dept_ning.deptno;

select e.*, d.*
from emp_ning e join dept_ning d
  on e.deptno = d.deptno;

select e.ename, d.dname, d.location
from emp_ning e join dept_ning d
  on e.deptno = d.deptno;

--主键: Primary Key = PK
--列值是唯一的,不重复的
--主表 / 父表

--外键: Foreign Key = FK
--列值参照某个主键列值
--从表 / 子表

--查询学生的名字,科目,成绩
select stu.student_name,
       per.subject_id,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id;


select stu.*, per.*, sub.*
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
;
--列出学生的姓名/科目名/成绩
select stu.student_name,
       sub.subject_name,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
;

--1班的学生成绩
select stu.student_name,
       sub.subject_name,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
where stu.class_id = 1;

--1班学生的成绩的总分,并排序
select stu.student_name,
       sum(per.test_score) total_score
from t_student_ning stu
     join
     t_performance_ning per
     on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
order by total_score desc;


张无忌   张三丰
郭芙蓉   张无忌

--列出员工的名字和他的经理名字
--自连接
select worker.ename, manager.ename
from emp_ning worker
join emp_ning manager
on worker.manager = manager.empno;

update emp_ning
set deptno = null
where ename = 'tom';

--部门是null的员工不会被查出来
--没有员工的部门,也不会被查出来.
--内连接
select e.ename, d.dname
from emp_ning e join dept_ning d
on e.deptno = d.deptno;

--外连接: 左外连接, 右外连接
select e.ename, d.dname
from emp_ning e
     left outer join dept_ning d
on e.deptno = d.deptno;

select e.ename, d.dname
from dept_ning d
     right outer join emp_ning e
on e.deptno = d.deptno;

-- t1-驱动表
t1 left outer join t2
t2 right outer join t1

外连接的结果集 = 内连接的结果集 +
  驱动表中在匹配表中没有对应记录的记录和  空值的组合

--部门表做驱动表
select e.ename, d.dname
from emp_ning e right outer join
     dept_ning d
on e.deptno = d.deptno;
--等价的语句,部门表做驱动表
select e.ename, d.dname
from dept_ning d left outer join
     emp_ning e
on e.deptno = d.deptno;

--left / right 外连接 都可以
--关键是谁做驱动表.


--全外连接: full outer join
select e.ename, d.dname
from emp_ning e full outer join
     dept_ning d
on e.deptno = d.deptno;


--哪些部门没有员工?
--1.关联子查询实现
select dname, location
from dept_ning x
where not exists (
             select 1 from emp_ning
             where deptno = x.deptno)

--2.集合
select deptno from dept_ning
minus
select distinct deptno from emp_ning;

--3.外连接
--where 匹配表的pk is null = 驱动表中匹配不上的记录.
--相当于过滤掉内连接的结果集.
select e.empno, e.ename, d.deptno, d.dname, d.location
from emp_ning e right outer join dept_ning d
on e.deptno = d.deptno
where e.empno is null;

Top-N分析
--薪水最高的三个人?
伪列: rownum

--测试rownum的作用
select rownum, empno, ename, salary
from emp_ning;
--希望:先排序,再取前三条.
--实际:先取前三条,再排序.
select empno, ename, salary
from emp_ning
where salary is not null
and rownum < 4
order by salary desc;

--先排序,再取前三条.
select*from
(
select empno, ename, salary
from emp_ning
where salary is not null
order by salary desc)
where rownum < 4;

--计算学生总分: 学生表,成绩表
select *
from (
    select stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = 1
    group by stu.student_name
    order by total_score desc)
where rownum < 2;

--输入:班号, 输出:该班的最高分
create or replace function maxScore_ning(p_class_id number)
return number
is
  v_total_score number;
begin
  --v_total_score赋值为指定班的最高分
select total_score into v_total_score
from (
    select stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = p_class_id
    group by stu.student_name
    order by total_score desc)
where rownum < 2;
  return v_total_score;
end;
////////最高成绩的名字，总分select student_name,total_score from (select stu.student_name,sum(per.test_score) total_scorefrom t_student_xiaobo stujoint_performance_xiaobo peron stu.student_id=per.student_idwhere stu.class_id=1group by stu.student_nameorder by total_score desc)where rownum<2;select student_id from (select stu.student_id,sum(per.test_score) total_scorefrom t_student_xiaobo stujoint_performance_xiaobo peron stu.student_id=per.student_idgroup by stu.student_idorder by total_score desc)where rownum<2;//////

select stu.student_name, sum(per.test_score)
from t_student_ning stu
join t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
having sum(per.test_score) = maxscore_ning(1);

--返回最高成绩的学生学号
create or replace function maxScore_ning1(p_class_id number)
return number
is
  v_student_id number;
begin
select student_id into v_student_id
from (
    select stu.student_id,
         stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = p_class_id
    group by stu.student_id, stu.student_name
    order by total_score desc)
where rownum < 2;
  return v_student_id;
end;
/
//////最高成绩的学生号，名字，总分
select student_id,student_name,total_score from(select stu.student_id, stu.student_name, sum(per.test_score) total_scorefrom t_student_xiaobo stujoin t_performance_xiaobo peron stu.student_id = per.student_idwhere stu.class_id=2group by stu.student_id, stu.student_nameorder by total_score desc)where rownum < 2;
/////
Oracle day3
20110901
==================================
复习: 查询语句
SQL>select count(*) from user_tables;

select distinct / 列名 / 表达式 / 组函数 /
from 表名
where 条件1 and 条件2 or 条件3
      like / between..and.. / in
      bonus is null
order by 列名 / 列别名 / 列序 / desc

今天的内容:
1.分组查询
2.多表联合查询

一.分组查询
--整个数据表作为一个大组
select count(*) from employees_ning;

--按部门分组,每个部门有多少人
select deptno, count(*)
from employees_ning
group by deptno;

--按职位分组,每个职位有多少人
select job, count(*)
from employees_ning
group by job;

select avg(salary)
from employees_ning;

--每个部门的平均工资是多少?
select deptno, avg(salary)
from employees_ning
group by deptno;

--每个部门的薪水总和是多少?
select deptno, sum(salary)
from employees_ning
group by deptno;

--薪水总和大于100000的部门?
select deptno, sum(salary)
from employees_ning
group by deptno
having sum(salary) > 100000;

--每个职位的薪水总和大于50000元,并按倒序排列.
select job, sum(salary)
from employees_ning
where job <> 'President'
group by job
having sum(salary) > 50000
order by sum(salary) desc;



update employees_ning
set salary = salary + 100000
where salary < 0;

select ename, salary from employees_ning;

二.多表联合操作
--查询职员表和部门表,列出职员名字和所在工作地点
select employees_ning.ename,
       department_ning.loc
from employees_ning
join department_ning
on employees_ning.deptno
   = department_ning.deptno;
--使用表的别名简化查询.语法完全相同.
select e.ename,d.loc
from employees_ning e
join department_ning d
on e.deptno = d.deptno;

--语法格式:
a join b on a.c1 = b.c2;

select e.ename, d.loc
from employees_ning e, department_ning d
where e.deptno = d.deptno;

--新员工没有分配部门
insert into employees_ning(id,ename)
values(1020, 'rose');

--上述关联查询语句,结果集没有rose这条记录.

--关联查询的结果集,是在两个表中都能找到匹配记录的集合.

--等值连接
a join b on a.c1 = b.c2
--列出员工名字和他的经理名字
--没有经理的人不会被列出来
--等值关联的特殊形式:自关联
select worker.ename, manager.ename
from employees_ning worker
join employees_ning manager
on worker.manager = manager.id;

--职位是Manager的员工属于哪些部门?
select e.ename, e.job, d.dname
from employees_ning e
join department_ning d
on e.deptno = d.deptno
and e.job = 'Manager';

select e.ename, e.job, d.dname
from employees_ning e
join department_ning d
on e.deptno = d.deptno
where e.job = 'Manager';

--驱动表和匹配表
--a: 驱动表
--b: 匹配表
--查询从驱动表开始,遍历驱动表.
select a.c, b.c
from a join b on a.c1 = b.c2
--职员表做驱动表,部门表做匹配表
select e.ename, d.dname
from employees_ning e
join department_ning d
on e.deptno = d.deptno;

--部门表做驱动表,职员表做匹配表
select e.ename, d.dname
from department_ning d
join employees_ning e
on e.deptno = d.deptno;

--等值连接时,驱动表和匹配表可以互换位置,结果集相同.
--没有分配部门的员工和没有员工的部门都不会被查出来.

--salgrade:薪水等级表
CREATE TABLE SALGRADE_XXX
      ( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE_XXX VALUES (1,0,2000);
INSERT INTO SALGRADE_XXX VALUES (2,2001,4000);
INSERT INTO SALGRADE_XXX VALUES (3,4001,6000);
INSERT INTO SALGRADE_XXX VALUES (4,6001,9000);
INSERT INTO SALGRADE_XXX VALUES (5,9001,99999);
COMMIT;
--非等值连接.
select e.ename, e.salary, s.grade
from employees_ning e
join salgrade s
on e.salary between s.losal
            and s.hisal;

--外连接:
--驱动表中的记录一条都不能少.
--驱动表中有匹配不上的记录, 和空值组合,放在结果集中.

--外连接的结果集 = 内连接的结果集 + 驱动表中匹配不上的记录和null值的组合

select e.ename, d.dname
from employees_ning e
left outer join department_ning d
on e.deptno = d.deptno;

select e.ename, d.dname
from department_ning d
right outer join employees_ning e
on e.deptno = d.deptno;

--部门40下没有员工
--列出员工名字和部门名字,如果部门下没有员工,也列出来.
--部门表做驱动表
select e.*, d.*
from employees_ning e
right outer join department_ning d
on e.deptno = d.deptno;

--使用左外连接或右外连接不重要,重要的是选定哪个表做驱动表.

e                      d
1001 张三 10           10 研发部 bj
1002 李四              20 市场部 tj

--外连接:驱动表和匹配表不能互换

--d做驱动表: 2条
1001 张三 10 10 研发部 bj
             20 市场部 tj
select e.*, d.*
from e right outer join d
on e.deptno = d.deptno;

--e做驱动表:2条
1001 张三 10 10 研发部 bj
1002 李四
select e.*, d.*
from e left outer join d
on e.deptno = d.deptno;


--等值连接:一条
--1001 张三 10 10 研发部 bj
select e.*, d.*
from e join d
on e.deptno = d.deptno

--希望得到结果集:
1001 张三 10 10 研发部 bj
1002 李四
             20 市场部 tj
select e.*, d.*
from e full outer join d
on e.deptno = d.deptno;


--外连接,e表做驱动表
--查询条件:匹配表的PK is null
  得到:驱动表中匹配不上的记录,即
       没有部门的员工.
select e.ename, d.dname
from employees_ning e
left outer join department_ning d
on e.deptno = d.deptno
where d.deptno is null;

--查询没有员工的部门:
select d.deptno, d.dname, d.loc
from employees_ning e
right outer join department_ning d
on e.deptno = d.deptno
where e.id is null;

--外连接 + where 匹配表PK is null
--实现否定问题: 没有部门的员工,没有员工的部门.

--全外连接:full outer join: 并集
--全外连接的结果集 = 内连接的结果集 + 左外连接的结果集 + 右外连接的结果集
select e.*, d.*
from employees_ning e
full outer join department_ning d
on e.deptno = d.deptno;

--哪些部门没有叫Mark的员工?
select d.deptno, d.dname
from employees_ning e
right outer join department_ning d
on e.deptno = d.deptno
and e.ename = 'Mark'
where e.id is null;

1)and e.ename = 'Mark'
e表只剩下叫Mark的员工记录,比如2条
2) 驱动表: 4条
   匹配表: 2条
   join完成后:结果集: 4条
   1001 Mark 10 10 研发部 bj
                20 市场部 tj
                30 人事部 sh
   1002 Mark 40 40 后勤部 bj
3)where e.id is null
   20 市场部 tj
   30 人事部 sh

三.子查询
--谁是薪水最低的员工?
select min(salary) from employees_ning;
--3000
select * from employees_ning
where salary = 3000;

--用一条SQL语句实现上述功能:
select * from employees_ning
where salary = (select min(salary)
                from employees_ning);

--谁的薪水比Mark高?
--前提:叫Mark的人只有一个,子查询只有一条结果返回.
--> < = 是单数据比较运算符
select * from employees_ning
where salary > (select salary
                from employees_ning
                where ename = 'Mark');

--假设有学生表Student,其中有名字name和年龄字段age.
--查询和张三同龄的学生.
--如果只有一个张三,19岁
select *
from student
where age = (select age
             from student
             where name = '张三');
--班里有两个张三,分别是x和y岁,
--查询和张三同龄的学生.
select *
from student
where age in (select age
              from student
              where name = '张三');

--单数据比较和多数据比较

--机构下薪水最高的人是谁?
select * from employees_ning
where salary = (select max(salary)
                from employees_ning);

--每个部门下薪水最高的人是谁?
--查询每个部门的最高薪水
select deptno, max(salary)
from employees_ning
group by deptno;

select * from employees_ning
where (deptno, salary) in
      (select deptno, max(salary)
       from employees_ning
       group by deptno);

--查询和Mark同薪水同职位的员工,不能是Mark自己.
--前提:叫Mark的人只有一个
select * from employees
where salary = (select salary
                from employees_ning
                where ename = 'Mark')
and job = (select job
           from employees_ning
           where ename = 'Mark')
and ename <> 'Mark'
order by 1;


day04Oracle day4
20120217
=====================
DML: insert / update / delete
DDL: create / drop / alter /      truncate
DCL: grant / revoke

一 DML
1.insert
insert into dept_ning
values(55, 'market', 'beijing');

insert into dept_ning
(deptno,  dname, location)
values(56, 'research', 'beijing');
--数据没有全部提供,必须写列名
insert into emp_ning(empno, ename,
salary, deptno)
values(1234,'rose',5000,10,500);

--出错,没有足够的值
insert into emp_ning(empno, ename, salary)
values(1234,'rose');

--出错,值太多了
insert into emp_ning(empno, ename, salary)
values(1234,'rose',5000,500);

insert into emp_ning(empno, ename,
salary, hiredate)
values(1234, 'rose', 5000,
to_date('2012/01/01','yyyy/mm/dd'));

create table emp_bak
as
select * from emp_ning
where deptno = 10;

delete from emp_bak;

insert into emp_bak
(select * from emp_ning
where deptno = 20);

create table myObjects
as
select * from all_objects
where rownum < 1000;

delete myObjects;

insert into myObjects
(select * from all_objects
where rownum < 10000);
2.update
update 表名
set 列1 = 新值1,列2 = 新值2,...
where 条件;

update emp_ning
set bonus = 1000, deptno = 20,
    job = 'Programmer'
where empno = 1234;

3.delete
delete emp_ning where empno = 1234;
delete emp_ning where deptno = 10;
delete emp_ning;
select count(*) from emp_ning;
rollback;
select count(*) from emp_ning;

create table emp_bak1_ning
as
select empno, ename,salary
from emp_ning
where deptno = 30;


--适合bak1中重复记录非常多的场合.
              复制非重复记录
emp_bak1_ning--------------> bak2
删掉表bak1, 把bak2改名为bak1.


--适合bak1中重复记录非常少的场合.
--直接在bak1中删除.

--查询重复记录中地址最大的那条
select empno, ename, salary,
       max(rowid)
from emp_bak1_ning
group by empno, ename, salary;
--删除除了地址最大的重复记录之外所有的记录.
delete emp_bak1_ning
where rowid not in (
select max(rowid)
from emp_bak1_ning
group by empno, ename, salary);

rownum, rowid是Oracle独有的.

事务 Transaction
commit / rollback

-----事务开始--------------
DML:insert / delete / update

commit(提交,确认),
或者 rollback(回滚,撤销)
-----事务终止--------------

A                B
insert : 1234    select (查不到)
commit          
                 可以查到 1234
update          
                 update(挂起)
commit(释放锁)   修改成功
update(挂起)
                 rollback;
修改成功.

---开始事务-----
update account
set money = money - 500
where id = 'A';

update account
set money = money + 500
where id = 'B';

if (都成功)
commit;
else
    rollback;
-----事务终止-----

正常退出会话: commit
异常退出会话: rollback
DDL操作: commit

create table temp_ning (id number);
insert into temp_ning values(1);
insert into temp_ning values(2);
--设置保存点,名字是a(自定义)
savepoint a;
insert into temp_ning values(3);
insert into temp_ning values(4);
--回滚到指定点
rollback to a;
select * from temp_ning;--2条记录

insert into temp_ning values(100);
savepoint b;
insert into temp_ning values(5);
savepoint c;
insert into temp_ning values(6);
--b点之后的保存点被取消
rollback to b;

insert / update / delete

二.DDL操作:
create / drop / alter / truncate

create table 表名(
列1 列1数据类型,
列2 列2数据类型,
....
);

drop table 表名;

create function f_name()....
drop function f_name;

alter: 修改对象结构
create table temp_ning(id number);

--增加列,只能加在最后.
alter table temp_ning
      add (name char(20));

alter table temp_ning
      add (age number(3));

--改列名
alter table temp_ning
rename column name to stuname;

--改列的类型char(20)->char(10)
--放大可以
--缩小的话, 如果数据有超过10个字符的,不能修改.
id    stuname        age
1     tom             18
2     zhangsan        20

--删除列age
alter table temp_ning
drop column age;

alter table 表名
--add (name char(20));
--rename column 旧列名 to 新列名;
--modify (age number(3));
--drop column 列名;

--截取表:把表数据全部删除.
--立即生效,不能rollback
truncate table temp_ning;

delete temp_ning + commit

--三思而后行
drop table ....;
truncate table ...;

三.DCL语言
grant / revoke
--授权
grant select on emp_ning to scott;
--撤销权限
revoke select on emp_ning from scott;

sys/安装时设置
system/安装时设置
scott/tiger(测试用户)
....
openlab/open123
ninglj/*****

四.脚本文件
文档
源代码
数据库脚本文件

1.创建脚本文件script_ning.sql
$vi script_ning.sql
-----------------------
drop table temp_ning;
create table temp_ning(id number);
insert into temp_ning values(1);
commit;
-----------------------
esc键-> :wq
2.sqlplus中批处理执行脚本文件
SQL>@ /user/openlab/script_ning.sql

数据库在192.168.0.26
数据库的口令:openlab/open123
Oracle的端口号: 1521
Oracle的名字(sid):tarena
sqlplus安装在本地机器上

C:>sqlplus openlab/open123@192.168.0.26:1521/tarena
SQL>

连接数据库的工具:
--首选,命令行工具
sqlplus
--图形工具
pl/sql developer(非官方,免费)
toad(非官方,收费)
sql developer(官方,免费,11g以上)


补充练习: procedure 过程
PL/SQL:  Procedure Language / SQL

函数(function)
过程(procedure)
包(package)
触发器(trigger)

--输入班号,输出最高分的学生名字和总成绩
create or replace procedure cal_ning(
p_class_id in number, p_student_name out char,
p_total_score out number
)
is
begin
  select student_name, total_score
  into  p_student_name, p_total_score
  from (
    select stu.student_name,
           sum(per.test_score) total_score
    from t_student_ning stu join t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = p_class_id
    group by stu.student_name
    order by total_score desc)   where rownum < 2;
end;
/

--打开输出,默认值是off
SQL>set serveroutput on
--匿名块,用来测试过程或函数
SQL>declare
  p_student_name char(20);
  p_total_score number;
begin
  cal_ning(&no, p_student_name,       p_total_score);
  --向控制台输出变量值,System.out.print
  dbms_output.put_line(p_student_name);
  dbms_output.put_line(p_total_score);
end;
/
--可以用&abc符号表示在运行时输入变量值
select * from emp_ning
where deptno = &abc;

--改进版,输入共多少个班,输出每个班的最高成绩的学生姓名和总分
--假设是班号是1-p_class_num
create or replace procedure cal_ning1(
   p_class_num in number)
is
  p_student_name char(20);
  p_total_score number;
begin
  for i in 1..p_class_num loop
    select student_name, total_score
    into  p_student_name, p_total_score
    from (
      select stu.student_name,
           sum(per.test_score) total_score
      from t_student_ning stu join t_performance_ning per
      on stu.student_id = per.student_id
      where stu.class_id = i
      group by stu.student_name
      order by total_score desc)
    where rownum < 2;
    dbms_output.put_line
        (p_student_name || ', ' || p_total_score);
  end loop;
end;
/
SQL>exec cal_ning1(2); --2个班





create or replace procedure cal_zsb(  p_class_num in number)isp_student_name char(20);p_total_score number;beginfor i in 1..p_class_num loopselect student_name, total_scoreinto  p_student_name, p_total_scorefrom(select stu.student_name,sum(per.test_score) total_scorefrom t_student_xiaobo stu join t_performance_xiaobo peron stu.student_id = per.student_idwhere stu.class_id = igroup by stu.student_nameorder by total_score desc)where rownum < 2;end loop;end;/


day05Oracle day5
20110905
=========================
复习:约束条件
pk / fk / not null / unique / check

create table major(
id number(2) primary key,
name varchar2(20)
);
insert into major values(1,'音乐系');
insert into major values(2,'数学系');
commit;
create table student(
sid number(3) primary key,
sname varchar2(20),
mid number(2)
);
alter table student add constraint
student_mid_fk foreign key(mid)
references major(id);
insert into student values(101,'张无忌', 2);
insert into student values(102,'任盈盈',1);
insert into student values(103,'张三丰',2);
commit;

--删除约束条件
alter table student drop constraint student_mid_fk;

--重建约束条件
alter table student add constraint
student_mid_fk foreign key(mid)
references major(id)
on delete set null;

--删除父表中的数据,将会同时将子表中的数据的参照列设置为null.
delete from major where id = 2;


--删除约束条件
alter table student drop constraint student_mid_fk;

--重建约束条件
alter table student add constraint
student_mid_fk foreign key(mid)
references major(id)
on delete cascade;



小结:
alter table student add constraint
student_mid_fk foreign key(mid)
references major(id)
[选择一: 啥也不写]
[选择二: on delete set null]
[选择三: on delete cascade];

--查看约束条件的名字
user_tables: 数据库中openlab账户下所有的表
user_constraints: openlab账户下所有的约束条件.
select constraint_name, constraint_type
from user_constraints
where table_name = 'STUDENT';

--删除约束条件:
alter table 表名 drop constraint 约束条件名;

--在建表后建立约束条件:
alter table 表名 add constraint 约束条件名 约束条件的类型 (约束条件建立在哪个列上)
[references 主表名(主表参照列)];

书:
1, 笑傲江湖, 金庸, 100
2, thinking in java, bruce, 5
3, xml的前世今生, 李裔, 30
分类:
101, 小说
102, 历史
103, 语言
104, java
中间表:
1, 101
1, 102
2, 103
2, 104
3, 102
3, 103
3, 104

--创建三个表/约束条件/数据的sql语句:
--一.创建三个表结构
--1.创建书Books表,创建PK
create table books(
bid number(2) primary key,
bname varchar2(20)
);
--2.创建书的类型Types表,创建PK
create table types(
tid number(3) primary key,
tname varchar2(20)
);
--3.创建中间表BookTypes
create table booktypes(
bid number(2),
tid number(3)
);

--二.创建约束条件
--4.创建中间表BookTypes的主键(联合主键)
alter table booktypes add constraint
bt_id_pk primary key(bid, tid);
--5.创建中间表BookTypes的两个外键约束条件
alter table booktypes add constraint
bt_bid_fk foreign key (bid)
references books(bid);
alter table booktypes add constraint
bt_tid_fk foreign key (tid)
references types(tid);

--三.基础数据
--6.新增数据: Books / Types / BookTypes
insert into books values(1,'笑傲江湖');
insert into books values(2,'thinking in java');
insert into books values(3, 'xml的前世今生');
commit;
insert into types values(101,'小说');
insert into types values(102,'历史');
insert into types values(103,'语言');
insert into types values(104,'java');
commit;
insert into booktypes values(1,101);
insert into booktypes values(1,102);
insert into booktypes values(2,103);
insert into booktypes values(2,104);
insert into booktypes values(3,102);
insert into booktypes values(3,103);
insert into booktypes values(3,104);
commit;


--7.查询
--查询:笑傲江湖 属于哪些分类?
select b.bname, t.tname
from booktypes bt
join books b on bt.bid = b.bid
join types t on bt.tid = t.tid
and b.bname = '笑傲江湖';

--查询:历史类书籍都有哪些?
select b.bname, t.tname
from booktypes bt
join books b on bt.bid = b.bid
join types t on bt.tid = t.tid
and t.tname = '历史';

--思考题:学生和课程的关系(选课计划)
student:
101, 张三丰
102, 赵敏
103, 郭芙蓉

course:
1, java
2, oracle
3, 骑马
4, 烹饪

student_course(sid, cid, score)
张三丰: oracle / 骑马
赵敏: java / 烹饪
郭芙蓉: java / oracle / 骑马 / 烹饪

--选修java的学生是哪些人?
--赵敏选修了哪些课程?


一.事务 Transaction
DML: insert / update / delete
DDL: create / alter / drop

--启动事务
update users set account = 20000
where id = 1234;
insert into log values(谁,什么时间,操作,原数据,新数据);
--提交事务
--回滚事务

事务:一组DML操作的逻辑单元.
启动事务:第一个DML操作开始 或 上一个事务的终结.

事务的终结:
1)显式终结: commit / rollback
2)隐式终结: DDL(commit) / 正常退出(commit) / 异常退出(rollback)
两种结果: commit / rollback

delete from city;
select * from city;
alter table province add....

create table mytemp(id number);
--事务起点
insert into mytemp values(1);
insert into mytemp values(2);
commit;
--事务终止
--事务起点
update mytemp set id = 10 where id = 1;
delete from mytemp where id = 2;
--delete from mytemp;
--在事务中:
--所有数据只有当前会话可见.
--更新或删除的数据被暂存在数据库内存中
select * from mytemp;
--如果commit:
--所有的改变被永久保存到数据库中
--暂存数据的内存被释放
--锁被释放
commit;
--如果rollback:
--所有的改变被取消,暂存的数据被恢复回数据中.
--暂存数据的内容被释放.
--锁被释放

事务语句: commit / rollback / savepoint

create table temp(id number)
insert into temp values(1);
savepoint A;
insert into temp values(2);
savepoint B;
insert into temp values(3);
savepoint C;
insert into temp values(4);
rollback to B; --回退到B点
select * from temp; --两条记录

--savepoint使事务回退到指定的保存点.

二.数据库的主要对象
表 Table  user_tables
视图 View
索引 Index
约束条件 Constraint
序列 Sequence
过程 Procedure
函数 Function
包 Package
触发器 Trigger
--过程 / 函数 / 包 / 触发器都是PL/SQL对象.

1.视图View: 数据表的逻辑显示.
--视图的本质是查询语句,用一个名字和查询语句关联起来,简化查询.
create or replace view v_emp_ning
as
select deptno, avg(salary) avg_sal,
       count(*) count_emp,
       sum(salary) sum_sal,
       max(salary) max_sal,
       min(salary) min_sal
from emp_ning
group by deptno;

select * from v_emp_ning;

desc v_emp_ning


create view v_book_type
as
select b.bname, t.tname
from books b
join booktypes bt on b.bid = bt.bid
join types t on t.tid = bt.tid;

--隐藏某些列
create or replace view v_emp_detail
as
select ename, job from emp_ning;

--删除视图
drop view v_emp_detail;

--如果删除了视图所查询的表,视图虽然存在,但已非法.

2.序列 Sequence
--创建序列
create sequence myseq_ning;
--使用序列产生唯一的/连续的数字值
select myseq_ning.nextval from dual;
/
/
--使用序列产生的数字值作为主键.
insert into temp values(myseq_ning.nextval);
--查询表中的数据:
select * from temp;

--序列的起始值是1000,步进是10
create sequence myseq_ning1
start with 1000
increment by 10;

--使用序列产生的值:序列名.nextval
select myseq_ning1.nextval from dual;
insert into temp values(myseq_ning1.nextval);

--删除序列:不会对已产生的值产生影响
drop sequence myseq_ning1;

Oracle day5
20120220
========================
一.约束条件
数据必须遵循的规则或限制.

primary key 主键 PK
foreign key 外键 FK
not null 非空 NN
unique 唯一 UK
check  检查 CK

--建表时,建立主键约束条件
--id列受限,
create table student_ning(
id number(4) primary key,
name char(10)
);
--1.ok
insert into student_ning
values(1, 'peter');
--2.ok
insert into student_ning
values(2, 'tom');
--3.错 ORA-00001: 唯一约束被违反,主键重复
--constraint: 约束
insert into student_ning
values(2, 'jerry');
--4.错, 主键列不允许有空值
insert into student_ning(name)
values('zhangsan');

某些数据库: 主键自增长
mysql  / sql server

oracle : 主键发生器: 序列


drop table student_ning;
--建表时,设置name列为Not Null约束
create table student_ning(
id number primary key,
name char(10) not null);

--1.ok,插入记录
insert into student_ning values(1,'peter');
--2.错,name不能插入null值
insert into student_ning(id) values(2);
--insert into student_ning values(2, null);


drop table student_ning;
--建表时,给email列创建唯一约束
create table student_ning(
id number(4) primary key,
name char(10) not null,
email char(20) unique
);
--1.ok
insert into student_ning
values(1, 'zhangsan','[e-mail]');
--2.错,email相同, 违反唯一约束, ORA-00001
insert into student_ning
values(2, 'zhangsanf', '[e-mail]')


drop table student_ning;
--gender:性别, 只允许:'M', 'F'
create table student_ning(
id number(4) primary key,
name char(10) not null,
email char(20) unique,
gender char(1) check (gender in ('F','M'))
);

--1.ok
insert into student_ning
values(1,'zhangsan', '[e-mail]','M');
--2.
insert into student_ning
values(2,'zhangsanf', '[e-mail]','X');

--查询约束条件
select constraint_name,
        constraint_type
from user_constraints
where table_name = 'STUDENT_NING';

--命名规则:表名_列名_约束条件类型
--列级约束
create table student_ning1(
id number(4)
  constraint stu_n_id_pk primary key,
name char(10)
  constraint stu_n_name_nn not null,
email char(20)
  constraint stu_n_email_uk unique,
gender char(1)
  constraint stu_n_gender_ck
    check (gender in ('F','M'))
);

--表级约束
--非空,只能定义在列级.其他约束可以定义在表级create table student_ning2(
id number(4),
name char(10)
    constraint stu_n2_name_nn not null,
email char(20),
gender char(1),
constraint stu_n2_id_pk primary key (id),
constraint stu_n2_email_uk unique (email),
constraint stu_n2_gender_ck
    check (gender in ('F','M'))
);

--建表,除了非空以外的约束,全部放在建表后再建
create table student_ning3(
id number(4),
name char(10) not null,
email char(20),
gender char(1)
);
alter table student_ning3 add
constraint stu_n3_id_pk primary key (id);

alter table student_ning3 add
constraint stu_n3_email_uk unique (email);

alter table student_ning3 add
constraint stu_n3_gender_ck
    check (gender in ('F','M'));

select constraint_name, constraint_type
from user_constraints
where table_name = 'STUDENT_NING3';

--专业表
create table major_xiaobo(
id number(2) primary key,
name char(20) not null);

insert into major_xiaobo values(1, 'java');
insert into major_xiaobo values(2, 'oracle');
insert into major_xiaobo values(3, 'c++');
insert into major_xiaobo values(4, 'android');
commit;

select * from major_ning;

create table student_ning4(
id number(4),
name char(10) not null,
mid number(2));

alter table student_ning4 add
  constraint stu_n4_id_pk primary key (id);

alter table student_ning4 add
  constraint stu_n4_mid_fk foreign key (mid)
  references major_ning(id);

insert into student_ning4
values(1, 'peter', 1);

insert into student_ning4
values(2, 'tom', 3);

--错:parent key not found, 父表中没有9这个id
insert into student_ning4
values(3, 'jerry', 9);

--错:parent key not found
update student_ning4
set mid = 8 where id = 1;

--错:child record found
--major_ning表的子表,有参照id=3的记录
delete major_ning where id = 3;