alter约束操作

1、创建表的同时创建主键约束
一、无命名
create table accounts (
accounts_number number primary key,
accounts_balance number
);
二、有命名
create table accounts (
accounts_number number primary key,
accounts_balance number,
constraint yy primary key(accounts_number)
);

2、删除表中已有的主键约束
一、无命名
SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='accounts';
找出主键名
ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;
二、有命名
ALTER TABLE ACCOUNTS DROP CONTRAINT yy;

3、向表中添加主键约束
ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

oracle中not null约束是我们用到的最多的约束之一了。我们可以在创建表时让系统自动指定not null约束的名字来创建，也可以手动的的指定not null约束的名字来创建，也可以在表创建好后手动的修改表已达到增加not null约束的目的。

下面是一个例子
create table test_not_null
(id number not null,
name varchar2(30) ,
tel varchar2(20) constraint test_not_null_tel not null);

在这里我们创建了一个表，并分别给id 和 tel两列创建了not null的约束，其中id的约束是系统自动命名的，而tel的约束使我们手动命名的。对于地二个列name，oracle系统不会自动增加not null的约束。

我们可以在这个表里面插入一行数据，例如：
insert into test_not_null(id,tel) values(123,’88888888′);

我们也还可以使用修改表的方式来增加not null约束。例如刚才我们没有在test_not_null上建立空值约束，我们可以现在通过修改的方式来建立
alter table test_not_null
modify(name not null);

如果你执行了刚才我们的插入数据的操作，你执行这句话的时候可能会有以下错误：
ORA-02296: 无法启用 (SYS.) - 找到空值

这是因为我们刚才在插入数据时已经给name这一列增加了一个空值，现在又要在这一列上增加not null约束，显然是不行的。
如果实际问题中我们真的遇到这种需求，例如对于某个字段我们认为原来可以为空，并且再该列插入了很多空值，后来我们发现实际上是不应该为空的，可能会需先将原来的空值全部更新到一个新的有统一意义的值，然后在进行空值约束的加入操作。例如我们可以这样做：
update test_not_null
set name = ‘不知道’
where name is null;

现在我们再增加列的not null约束是没有问题了。
alter table test_not_null
modify(name not null);

      1、创建表的同时创建主键约束
                        （1）无命名 create table student (studentid int primary key not null,
                                                          studentname varchar(8),age int);
                        （2）有命名 create table students (studentid int ,studentname varchar(8),
                                                    age int,constraint yy primary key(studentid));
     2、删除表中已有的主键约束
                        （1）有命名 alter table students drop constraint yy;
                        （2）无命名 可用 SELECT *　from user_cons_columns　where ..;
                             查找表中主键名称得student表中的主键名为SYS_C002715
                             alter table student drop constraint SYS_C002715;
                    　　（３) 使约束失效:
                             alter table tbl_employee disable constraint fk_emp;
                     删除约束:
                            alter table tbl_department drop constraint pk_dept;
                     查询约束:
select CONSTRAINT_NAME from user_constraints where table_name='TBL_EMPLOYEE';
select CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns where table_name='TBL_EMPLOYEE';
     ３. 删除表.
　　　　      Drop　table table_name;
４、操作表数据
           插入表记录：
            ①. a. insert into table_name col1,col2 values (val1,val2);
例：Insrt into xs(xh,xm,) values (‘09’,to_date(‘19860210’,’yyyymmdd’));
                    insert into    depto values('100','xieyunchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)
                 b.从一个表中向另一个表中插入数据
　　　　　　　　　　　　　Insert into table1(col1,col2,col3) select (col1,col2,col3)
　　　　　　　　　　　　　　　　　　　from othertable
                 c.使用子查询插入数据
                 　   insert into employee (empno,ename,sal,deptno)
                              select empno,ename,sal,depto from emp;
                d.INSERT INTO EMP (ENAME,HIREDATE) VALUE(‘AA’,TO_DATE(‘1980-12-09’,’YYYY-MM-DD’))
　　　　　　③. 删除表数据：
　　　　　　　　　Delete from table_name where condition;
　　　　　　④. 修改表记录
　　　　　　　　　Update table_name set column_name=expression,…where condition.
                基于一张表修改另一张表的数据
                  UPDATE EMPLEE SET DEPTNO=(SELECT DENPNO FROM EMP WHERE EMPNO=7788)
                                 WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
　　　　　　⑤. 删除所有记录但保留表结构.
　　　　　　　　　Truncate table table_name;
　　　　　　⑥.查询数据
               查询表结构:DESC table_name
　　　　　　　　　Select ename,sal,12*sal+100 from emp
               注:select count(dinstinct(deptno)) from emp
               a.查询大于平均的：
                  select empno from emp a,(select avg(sal) as sal_sal from emp) b
                         where a.sal>b.sal_sal;
　　　　　　　如果列中有空值时,则结果也为空（关于null值的处理（p４７））.
　　　　　　　　　如：select ename,name,12*sal+comm如果comm的值为null，结果也为null;
                      如上所示,comm为null时.则12*sal+comm也为null;解决方法是用nvl方法替换.
              在两个表中查询:以下两种方式都是一样的.
                  a.select t_phone_operation.operation_name
                                                from t_phone_operation ,t_phone_operation_charge
                                                where t_phone_operation.operation_id=t_phone_operation_charge.operation_id and
                                                                                    t_phone_operation_charge.phone_num=’159..’;
                                   b.select operation_name from t_phone_operation where operation_id in (select operation_id from t_phone_operation_charge where phone_num='159...'
使用日期格式显示日期：
select ename,to_char(hiredate,’yyyy—mm---dd’) from emp
select ename where hiredate>to_date(‘1999-12-31’,’yyyy-mm-dd’);
             

              使用别名的三种方式:
                  a.    select ename as name,sal salary,from emp
                  b.    select ename ” name”, sal*12 ”annual salary”
              使用连接操作符:
                  Select ename || job as “employees” from emp
              用连接字符:
                  Select ename ||’ ’||’ is a ’||’ ’||job as “employee details”
              限制重复的行：
                  Select distinct deptno from emp
              注意大小写:
                  Select ename,job,deptno from emp where job=’CLERK’
              使用between ….and 运算符
                  Select ename,sal from emp where sal between 1000 and 1500;
              使用in 运算符
                  Select empno,ename,sal,mgr from emp where mgr in(23,231,2345);  
              Like运算符：(模糊查询)
                  %代表至多任意字符
　                ＿代表一个任意字符
                        如：select ename from emp where ename like ’s%’;
                  显示第三个字符为大写A的所有信息
                        SELECT ENAME ,SAL FROM EMP WHERE ENAME LIKE '__A%';
                  显示雇员名包含"_"的雇员信息(其中ESCAPE后的字符a为转义字符)
                        SELECT ENAME,SAL WHERE ENAME LIKE '%a_% ESCAPE 'a';
             Null运算符(关于null值的处理（p４７）：)
　               测试一个值是否为空：
                        Select ename,mgr from emp where mgr is null (注意是is null ,不是=null)
             使用NVL函数处理NULL值
                 NVL函数用于将NULL转换为实际值,其语法为NVL(exp1,exp2).如果是exp1 是null,则返回
                 exp2,如果exp1不为null,则返回exp1,参数exp1和exp2是任意相同的数据类型.
                 如:
                 SELECT ENAME, SAL,SAL+NVL(COM,0) AS "月收入" from EMP;
             使用NVL2函数处理NULL值
                语法为NVL(exp1,exp2,exp3),如果exp1不是null,则返回exp2,如果exp1是null,则返回exp3;
             用WHERE语句限制日期
                示例一.符合默认日期格式
                  select * from emp where hiredate>'01-1月-82'
                示例2:不符合默认日期格式(需要用TO_DATE函数转换)
                  SELECT * FROM EMP WHERE HIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');
            逻辑操作符：And or not
            Order by 子句
                       a.   使用order by 子句对查询数据排序
                             ―――asc：升序，默认
                             ―――desc：降序：

                       依据多列进行排序：
                             Select ename,deptno,sal from emp order by deptno,sal desc
                总结：
                SELECT    [DISTINCT] {*,COLUMN[ALIAS],…..}    //DISTINCT不重复
                FROM      tablename
                WHERE     condition
                ORDER BY   {column,expr,alias} [ASC|DESC]
            分组查询：
           常用分组函数：
                MAX , MIN ,AVG ,SUM,COUNT,VARIANCE(取方差),STDDEV（取标准差）
●     当使用分组函数时，分组函数只能出现在选择列表，order by ,和having子句中，而不能出现在where 和group by子句中.
●     当执行SELECT 语句时，如果选择列表同时包含列，表达式，和分组函数，那么这些列和表达式必须出现在GROUP BY子句中.  
select deptno,avg(sal),count(*) from emp group by deptno;
        having avg(sal)<2000;
●如果在select 语句中同时包含group by ,    having ,以及order by子句，则必须将order by子句放在最后.
●当限制分组结果时，必须要使用having ,而不能在WHERE 子句中使用分函数限制分组显示结果.
子查询：
    子查询的作用：
●     在insert 或create table 语句中使用子查询，可以将表数据插入到目标表中.
●     通过update 子句中使用子查询，可以修改一列事多列数据.
●     通过在where ,having,start with 子句中使用子查询，可以提供条件值.
单行子查询：
select ename ,sal,deptno from emp where deptno=(select deptno from emp where ename=’scott’)
多行子查询：
select ename ,job,sal,deptno from emp where job in
(select distinct job from emp where deptno=10)
使用比较符：
IN:匹配子查询结果中的任何一个就可以（见上例）.
ALL:必须要符合子查询结果中的所有值.
ANY:只要符合子查询结果中的任何一个就可以.
ALL的使用：
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ALL
       (SELECT SAL FROM EMP WHERE DEPTNO=30)
ANY 的使用：
   SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>ANY
(SELECT SAL FROM EMP WHERE DEPT=’30’)
                多列子查询：
                    SELECT ENAME ,JOB,SAL,DEPTNO FROM EMP WHERE (DEPTNO,JOB) =
                        (SELECT DEPTNO,JOB FROM EMP WHERE ENAME =’SMITH’)
SELECT ENAME,SAL ,COMM,DEPTNO FROM EMP WHERE (SAL,NVL(COMM,-1)) IN (SELECT     SAL,NVL(COMM,-1)FROM EMP WHERE DEPTNO=30)
           





            ⑦.删除表数据：
                    Delete from table_name where condition;
                    DELETE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)
            ⑧. 修改表记录
                    Update table_name set column_name=expression,…where condition.
                    UPDATE TABLE_NAME SET COL1=A*1.1,COL2=B*2 WHERE
                    更新日期：
                    UPDATE TABLE_NAME SET HIREDATE =TO_DATE(‘1987/12/1’,’YYYY/MM/DD’);
                    更新关连数据
                    UPDATE EMP SET (ENAME,SAL,COMM)=(SELECT ENAME,SAL,COMM FROM EMP WHERE ENAME=’XX’)WHERE ENAME=’YY’
                    复制其他表数据
                    UPDATE EMPLEYEE SET DEPTNO=(SELECT DEPT FROM EMP WHERE EMPNO=7788)WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7788)
            ⑨.删除所有记录但保留表结构.
                    Truncate table table_name;
                   
            ⑩.事务
             a. 当出现下面的事件时,事务便结束
                  ①. commit or rollback 被执行时.
                  ②. Ddl or dcl被执行时.create or drop 等
                  ③. 用户退出.
                  ④. 系统中止或当机.
             b. 事务保存点.
    　　　        update ....
                  savepoint update_done
                  insert ....
                  rollback TO update_done

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/rodger004/archive/2008/06/06/2517469.aspx