杂七杂八

1.什么是主码(主键)：能够唯一表示数据表中的每个记录的（字段）或（字段）的组合称为主码。

编号	姓名	职称	职务
200201	刘洋	博导	室主任

 

2. 创建表：

   create table customer 

   ( 

     name1 varchar2(30) not null,

     state varchar2(2),

     sale number

    )

   tablespace test;

 

3.插入数据：insert into customer values('张三','bj',2000);

             insert into customer(name1,state) values('张红','tj');

4.删除表中的数据：

  删除记录：delete from tablename  where 条件；

  整表数据删除：truncate table;

   truncate table命令将快速删除表中的所有记录，但保留数据表结构，这种快速删除与delect from数据表的删除全部数据表记录不一样，delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate命令删除的数据将不可以

 

5. SELECT 语句执行后，将出现下列情况之一 a.只检索了一行 b.检索了多行 c.不检索任何行, 仅当它检索一行时，SELECT才成功操作 其他两种情况将导致错误并产生异常处理程序

用select语句操作一下在数据字典视图user_tables察看所建立的表：

   select * from user_tables where table_name='customer';

   select * from customer where name1!='张三';

   select * from customer where sale between 1 and 4000;

   select * from customer where name 1 in ('张三','张红');

   select * from customer where name1 like'张%';

   select * from customer where name1 like’张_’;

select name1,nvl(sale,0)*2+1 sale1 from customer; -- nvl(x,y)函数：当x为空(NULL)时，则返回y的值，否则返回x的值

 

   select name1||sale from customer;

   select * from customer where sale>1000;

   select sysdate from dual;其中dual 是系统提供的虚拟表

   select uid from dual;当前用户标识

   select user from dual;用户登录进数据库的名字

 

(4)create sequence customer_sequence

   increment by 1 

   nocycle;

   select customer_sequence.nextval from dual;  1

   select customer_sequence.currval from dual;  1

   如果刚定义完就进行select customer_sequence.currval from dual的查询，将会出现如下提示：customer_sequence.currval尚未在此进程中定义。所以先进行select customer_sequence.nextval from dual的查询。

 

(5)select  owid,name1,state,sale from customer where name1='张三'for update;  rowid 结合for update使用可以实现从表中选择数据后，可以把这行锁住，保证选择的数据和更新的数据保持一致性，更新时可以依据rowid 作为条件来更新该数据行：

 update customer set sale=3000 where rowid='AAAH9XAALAAAACXAAA';

 select* from customer where rownum<4;

 select* from customer where rownum<7 order by name1,state desc;

 

(6)select distinct name1 from customer;用distinct语句的SQL代码效率较差，数据量极大的时候尤其如此，所以应避免使用，最好使用其他方式得到所需要的数据

 

(7)select f_ename

   from t_xemp

   where f_deptno in (select f_deptno from  t_xdept where f_dname='财务科')

 

(8)select e.f_ename,d.f_dname from t_xemp e

   inner join t_xdept d on e.f_deptno=d.f_deptno;

 

(9)select ASCII('Z') from dual  90

   select chr(90) from dual     Z

   initcap(str)：字符串的第一个字母大写。

 

(10)select length(ltrim('wo ai  ')) from dual    7

    select length(ltrim(' wo ai  ')) from dual   7

    select length(trim (' wo ai  ')) from dual   5

    select length(rtrim(' wo ai  ')) from dual   6

 

(11)select f_deptno from t_xemp union all  select f_deptno from t_xdept

    select count(*)行数 from  t_xemp where nvl(f_sale,0)<7000;注意：行数不要加号。

 

(12)SQL> variable id number

    SQL> execute :id:=3;

    SQL> print id

 

(13)regedit 注册表  ceil(2.5)返回 3； floor(2.5)返回2；---前一个返回一个大于或等于x的整数3，后面一个是用来返回小于或等于x的整数。Power（x，y）返回x的y次幂。

 

(14)alter table t_fenglijun

    add constraint pk_aa primary key (f_id);--添加主键

    alter table t_fenglijun  --删除主键

    drop primary key;

 

(16)<a href="  ">..</a>

 

(17)comment on table BASEINFO.T_ADMIN

    is '管理员表';

 

(18)-- Add comments to the columns

   comment on column BASEINFO.T_ADMIN.F_ADMINID 

   is '管理员ID';

 

(19)create index BASEINFO.IX_ROLEEMPLOYEEROLEID on BASEINFO.T_ROLEEMPLOYEE (F_ROLEID) --创建索引

 

(20)判断为有效字符或数字：

        i := 1;

        Flag:= -1;

        LOOP

 chrTemp := SUBSTR(NewPassword,i,1);

                      --包含字符

        IF((ASCII(chrTemp) >=ASCII('A')) AND (ASCII(chrTemp) <=ASCII('Z'))) OR ((ASCII(chrTemp) >=ASCII('a')) AND (ASCII(chrTemp) <=ASCII('z')))THEN

           flag:=1;

        ELSE

            --包含整数

            IF (ASCII(chrTemp) >=ASCII('0')) AND (ASCII(chrTemp) <=ASCII('9')) THEN

                flag:=1;

            ELSE

                flag:= -1;

            END IF;

        END IF;

        EXIT WHEN (i>intlength-1)OR flag =-1 ;

           i:=i+1;

        END LOOP;

 

(21) %TYPE 属性 :提供变量或列的数据类型，在对引用到数据库中的列的变量进行声明时有用  a.需要知道列的确切数据类型   ：mydate 表名.字段名%type；

    b.如果列定义发生变化，则变量的数据类型在运行时也将随之更改

 

(22)%ROWTYPE属性 :当记录变量具有与表或视图中的行或从游标获取的行相同的结构时有用。记录中的字段具有与表/视图中的列相同的名称和数据类型

mytable  tablename%rowtype  :mytable 与表tablename的行结构相同。

 

(23)使用“&”操作符及赋值操作符可以接受用户输入的值示例

    mbranch_code := ‘&mcode’;--字符串

    num := &num;--数值

   “mcode”是绑定变量，不应该声明，但是需要声明 mbranch_code

 

（24）a.IF <条件1> THEN

         语句;

      ELSIF <条件2> THEN

         语句;

      ELSIF <条件3> THEN

         语句;

      ELSE

         语句;

      END IF;

      b.IF <条件> THEN语句;

        ELSE 语句;

        END IF;

(25)  LOOP

         语句;

         IF <条件> THEN

             EXIT;   --立即退出循环

          END IF;

      END LOOP;

 

(26) FOR <计数器> IN [逆转]

       lower_bound .. higher_bound

     LOOP

         语句;

     END LOOP;

   循环在指定的整数范围内进行,对于一定范围内的每个整数，都执行一次该语句此范围为循环架构的一部分，它位于 FOR 和 LOOP 之间 ,当首次进入循环时，范围只判定一次 ,每执行一次循环，循环计数器就会增加

 

(27) 使用尖括号进行定义 (<<  >>)

     <<if_fare_label>>

     IF 条件 THEN

        语句;

     END IF;

        语句;

     GOTO if_fare_label;

  在下列位置可以使用 GOTO 语句来转移控制:从程序块到可执行语句.从异常处理程序分支到封  闭的程序块.

  不允许在下列位置使用 GOTO 语句来转移控制:从某个 IF 语句或循环子句内转到其他语句.从封闭程序块转到某个子程序块.从异常处理程序转到当前程序块.子程序之外.转到关键字

 

(28)NULL 语句:明确指定不进行操作

      IF newfare > 90 THEN

        语句;

      ELSE

        NULL;

      END IF;

 

(30)PL/SQL 支持两种注释样式:单行注释:可以在行中的任何地方以双分号 (--) 开始,可以扩展到行尾.多行注释:这些注释以 /*开始并以*/结束,可以跨越多行

 

(31)不能在同一程序块两次声明异常，但可以在两个不同的程序块声明同一个异常在程序块中声明的异常对于程序块是局部的，而对于所有子程序块则是全局的。封闭的程序块不能引用在子程序块中声明的异常，因为程序块只能引用局部或全局异常

 

（31）bitand函数是位与的意思 bitand（num，8），如果num=8，那么位与的结果为1，否则为0

 

（32）raise_application_error(-20000,’’);

Select  round(x，y)

Select  round(5678.3432)   from  dual;  5678     y不写，四舍五入为整数。
select  round(5678.3432,2)  from  dual   5678.34   y大于0，四舍五入为y位小数。
select  round(5678.3432,-2) from  dual  5700    y小于0，则四舍五入到小数点向左第y位

select  initcap('asBXsd')  from dual  Asbxsd   返回首字母大写，其余字母小写

 

instr(w,x[,y[,z]])查找串x在串w中出现的位置，若不在串w中，则返回0。同时，若指定了y参数，则从第y个字符开始查找；若指定了z参数，表示查找串x在串w中第z次出现的位置 select instr(‘This is a example,isn’t it?’,’is’,4,2) from dule;;  19

 

substr substr ( x [,y [,z ] ] ) 函数和substrb ( x[,y [,z ] ] )函数

substr ( x [,y [,z ] ] ) 函数

返回串x中从第y个字符开始到第z个字符结束的字符串。若不指定z，则返回从第y个字符知道结束的子串。例如

select substr(‘This is an example’,12,4) from dual;

结果：exam

substrb ( x[,y [,z ] ] )函数

在多字符时运用

例：select * from t_user t
ORDER BY substr(f_employeeName ,instr(f_employeeName,'.')) 

 

select lpad('very good',29,'ok!') from dual;  输出：ok!ok!ok!ok!ok!ok!okvery good

count（*|[distinct|all]x）函数，若用“*”作参数，则返回满足条件的所有行数，不管其是否重复或有空值。若用“x”作参数，则查找列x不为空的行数。

 

glb（[distinct|all]x）函数返回标签x的最大下限

lub（[distinct|all]x）函数返回标签x的最小上限

 

greast（x[,y[,…]]）函数返回参数列表中的最大值，在作字符串的比较的时候，其参数的类型由第一个参数决定，后面的参数被强制转换为此种数据类型。

least（x[,y[,…]]）函数返回参数列表中的最小值。

 

nvl（x，y）函数，当x为空的时候，返回y的值，否则返回1的值。当x和y的数据类型不一致时，则在比较前，系统强制将y的数据类型转换成x的类型。只有在x为char而y为varchar2时返回类型为y （varchar2）类型。

user函数返回当前用户的数据库用户名；uid函数返回唯一标识当前用户的整数。这两个函数在完整性约束检查时会用到。select user，uid  from dual；

 

decode(e,s1,t1[,s2,t2]…default)函数相当于if…elsif…end if 功能。

 

create  table  新表 as  select  列的列表 from  旧表  where  约束条件

 

拷贝表结构，而不拷贝表中的数据，只要在where子句中指定一个永假值即可，语句如下

：create  table  新表 as  select  列的列表 from  旧表  where 1=2；--显然1=2永远不会相等，新表在旧表中找不到可以拷贝的行数据，则建立了一张空表。

 

修改表结构：alter table <表名>

            add/modify (<列1> 数据类型 [约束条件]，

                <列2> 数据类型 [约束条件]，…..)；增加列也不是任意的，定义为NOT NULL的列不能动态的增加，因为系统不知道增加的这个列该填些什么，且其又不能为空，只能产生错误。

解决此问题的方法：先增加一个可以为空的列；再用不为空的值填充它，如下： update 表名 Set f_sex=‘男‘；再用modify 修改此列： alter table <表名> modify（f_sex  char（2）not null）；

 

列的删除,建议先为这个表创建一个副本，可以用create table as 命令来做

create table t_XX as(select * from t_YY);

create table t_XX as(select * from t_YY where f_XX!= ..)

，不要选择要删除的列。再用更名命令对新旧表进行重新命名。rename t_xx to t_oldxx ；rename  t_newxx  to t_xx ；用drop命令删除 t_oldxx ：drop table t_oldxx；

 

用视图进行插入操作的时候，要求该视图必须基于一个表，且用户对该表具有insert权限，还要视图中的列包含基表中的某些特殊列（not null、primary key）,（若视图中包含group by，distinct或引用了伪列，也不能向视图中插入行）最后要说的时列名表于列值表也要按顺序对应、类型匹配。

 

删除表中的所有行：delete from 表名

 

用group by分组查询：select中包括的列表中除了group by 指定的聚组函数求得的新列，不能有表中的其他列。因为它们对于每个相同的分组，值都可能不一样，系统不知道该列出哪个值。聚组函数只能是系统制定的那些函数：avg函数，sum函数、max函数、min函数、stddev函数（标准误差）、variance（方差）函数

having子句是对各个组返回的结果进行筛选，满足一定条件的结果值才能作为最后的结果。having子句后面指定的查询条件一般都是有关聚组函数产生的结果的。

 

 

select * from t_organ t

WHERE f_OrganID BETWEEN 29999 AND 39999

ORDER BY f_OrganFlag,f_OrganName

 

 

找出一个表中某一个字段相同的，主键不同的列

SELECT * FROM t_user t
INNER JOIN t_user f
ON t.f_username=f.f_username AND t.f_userid!=f.f_userid

说明：表与自己进行内连接

或：select count(*) ,t_f_username

from t_user t

group by t.f_username

having count(*)>1

注：count函数用法：count（字段名）或者count（*）来统计总数

 

创建视图：

create [or replace] view <视图名>

as

         select <列名1>,<列名2>……<列名n>

         from <表1>,<表2>……<表n>

         where <约束条件>;

TYPE curRecordSet  is REF CURSOR  至多只允许游标在说明部分说明一次 要么在包头中定义，要么在包体中定义