Oracle 基础笔记（二）

1. View   就是一个子查询或者就是一张表,视图中的表叫虚表，实际数据依然在实际当中的表里面 .

 

--Grant user to create view
conn sys/oracle as sysdba;
grant create table, create view to kenny;
conn kenny/kenny1

 

--求比普通员工的最高薪水还要高的经理人名称
create view v$_manager_employee_id as select distinct manager_id from hr.employees where manager_id is not null
select * from v$_manager_employee_id;
select first_name,salary from hr.employees where employee_id in 
(select * from v$_manager_employee_id)
and salary > (select max(salary) from hr.employees where salary is not null and employee_id not in 
(select * from v$_manager_employee_id))

 2.Insert 语句

 

---backup table
create table emp as select * from hr.employees;
---insert into 
--第一种形式:不写字段的名字，直接按照字段的顺序挨着排的往里插
insert into emp values('value1','value2','value3');
--第二种形式:指定某些字段往里插，其他不插的字段默认都是空值
insert into emp (empColumn1, empColumn4) values (60, 'game2');
--第三种形式：从其他表中查询插入，但是查询出来的表结构和要插入的表结构要一致
insert into emp select * from hr.employees;

 

3.rownum

/*
当你没有进行排序的时候，它默认的顺序是先导进去的先选出来，后导进去的后显示出来
在Oracle里面有一个尾字段叫-->rownum 它是按照1，2，3...进行排列的，它不显示
*/
--取前5行
select * from emp where rownum < 5 
--取10行以后 不能直接写 where rownum > 10;
select * from ( select rownum r,first_name from emp )where r > 10;
--求薪水最高的前5个人,首先要先排序再查，再取前五个
select salary from (
  select salary from emp where salary is not null order by SALARY desc )where rownum <= 5
--取薪水最高的第6个人---第10个人(重点掌握)--》不论是哪种数据库，这个应用非常广泛
select salary from (
select salary,rownum r from
(select rownum r,salary from emp where salary is not null order by SALARY desc )
) where r >= 6 and r <=10

 

4.DDL语句 - create table,drop table

-- create student table
CREATE TABLE student(
SNO Number(4) NOT NULL PRIMARY KEY,
SNAME VARCHAR(25));
-- create course table
CREATE TABLE course(
CNO Number(4) NOT NULL PRIMARY KEY,
CNAME VARCHAR(25),
CTEACHER VARCHAR(25));
-- create student_course table
CREATE TABLE student_course(
SNO Number(4) NOT NULL,
CNO Number(4) NOT NULL,
SCGRADE Number(4));
--注意最后一个列是没有逗号结束的
-- 删除一张表
drop table student_course;

--列出2门以上(含2门)不及格学生姓名及平均成绩
select stu.sname,stu.sno, t.avgGrade from student stu 
join ( select s.sno,avg(sc.SCGRADE) avgGrade from student s 
join student_course sc on s.sno = sc.sno where s.sno in (
select student.sno from student 
join student_course on student.sno = student_course.sno 
where student_course.SCGRADE < 60 
group by student.sno 
having count(student.sno) >= 2) group by s.sno ) t on stu.sno = t.sno

 

5.constraint 约束

  1）NOT NULL 约束只能在列级定义，不能在表级定义

 

--为约束条件not null起名字为：stu_name_nn
CREATE TABLE student(
SNO Number(4) constraint stu_name_nn NOT NULL,--列级定义也称内敛定义
SNAME VARCHAR(25));

     2)unique 惟一约束列的列值不能重复，但可以为NULL。UNIQUE约束既可以在列级定义，也可以在表级定义

--外部定义"语法要求您指定哪一列(或多列)被约束影响。由于对于列而言不是"内联"的，语句不能够知道您试图约束哪一列，除非在子句中明确将它指定出来。
CREATE TABLE student
 (
    SNO Number(4),
    SNAME VARCHAR(25),
    constraint stu_name_nn2 unique (SNO) --表级定义也称外部定义
 );

  3)PRIMARY KEY  可以理解为： 非空 ，唯一 两个的组合.

主键约束列的列值不仅不能重复，而且也不能为NULL。主键约束既可以在列级定义，也可以在表级定义。一张表最多只能具有一个主键约束，当一个表中的多个列都要为主键是，可以在表级定义.

  

  4) FOREING KEY  要求外部键列的数据必须在主表的主键列（或惟一列）中存在，或者为NULL.FOREING KEY约束既可以在列级定义，也可以在表级定义。

关键字说明：

FOREING KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时必须指定该选项，在列级定义外部键约束不需要指定该选项

REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时，该选项必须指定。

ON DELETE CASCAED:该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选项，那么当删除主表数据时会级联删除从表的相关数据。

ON DELECT SET NULL:该选项用于指定转换相关的外部键值为NULL，如果在定义外部键约束时指定了该选项，那么当删除主表数据时会将从表外部键列的数据设置为NULL。

CREATE TABLE student_course
(
    SNO Number(4),
    CNO Number(4),
    SCGRADE Number(4),
    CONSTRAINT fk_sno_name FOREIGN KEY(SNO) REFERENCES student(SNO),
    CONSTRAINT fk_cno_name FOREIGN KEY(CNO) REFERENCES course(CNO)
);

 5）CHECK 约束既可以在列级定义，也可以在表级定义。CHECK约束允许列为NULL。

 

CREATE TABLE student_course1
(
    SNO Number(4),
    CNO Number(4),
    SCGRADE Number(4),
    CONSTRAINT scgrade_chk CHECK (SCGRADE BETWEEN 0 AND 100)
);

 约束的管理：

1. 增加约束

如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;

如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY子句，如：

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] constraint_type (column,...);

ALTER TABLE table_name MODIFY column [CONSTRAINT constraint_name] NOT NULL;

 

2.修改约束名

在同一个方案中，约束名必须惟一,并且约束名也不能与其他对象同名。当用IMPDP工具或者IMP工具导入其他对象时，如发现有同名的对象，将会出错

语法：

ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_constraint_name;

例：

ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028 TO ck_emp01_salary;

 

3. 删除约束

当删除特定表的主键约束时，如果该表具有相关的从表，那么在删除主键约束时必须带有CASCAED选项

语法：

ALTER TABLE table_name DROP CONSTRAINT constraint_name | PRIMARY KEY

例1

ALTER TABLE emp01 DROP CONSTRAINT ck_emp01_salary;

例2

ALTER TABLE dept01 DROP PRIMARY KEY CASCAED

 

4. 禁止约束

禁止约束指使约束临时失效。当禁止了约束之后，约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据之前，为了加快数据装载速度，应该首先禁止约束，然后装载数据。

语法：

ALTER TABLE table_name DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键约束

例：

ALTER TABLE emp05 DISABLE CONSTAINT SYS_C00502;

 

9.3.5 激活约束

语法：

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

例：

ALTER TABLE emp05 ENABLE CONSTRAINT SYS_C005022;

 

9.4 显示约束信息

1.USER_CONSTRAINTS

2.USER_CONS_COLUMNS