SQL常用语句和函数（转）

╰半夏微凉° 2022-07-14 10:39 349阅读 0赞

# 1.求字持串的长度LENGTH #

您可用LENGTH函数求字符串的长度。LENGTH返回一个数值。该值等于参数中的字符个数。  
例：使用LENGTH函数  
    SQL＞select Last\_Name, length(Last\_Name) from customer order by LastName;

2.使用SUBSTR函数从字符串中提取子串  
语法：  
    SUBSTR函数的语法如下：  
    SUBSTR(string, string charcter, number of charcters)  
    变量定义如下：  
    string为字符列或字符串表达式  
　　string charcter为子串的起始位置  
　　number of charcters为返回字符的个数c  
例：说明了怎样使用SUBSTR函数取得教师的姓的前四个字符  
    SQL＞select last\_Name, substr(Last\_Name, 1, 4) from instector order by Last\_Name  
例：在SUBSTR函数中使用LENGTH函数（取后三个字符）  
    5Qt．＞select last\_Name, substr(Last\_Name, Length(Last\_Name) - 2, 3) from instector order by Last\_Name

3.在字符串中查找模式  
例：使用LIKE运算符  
    SQL＞column description format a40 word\_wrapped  
    SQL＞column title format a35  
    SQL＞select Title, Description from Course where Description like '%thory%' or Description like '%theories%';

4.替换字符串的一部分  
  经常遇到的数据操纵任务是在特定的列中将数据由一种模式转换成另一种模式。  
  假设您希望在Course表中改变课程说明，将说明中的字seminar用字discussion替代．那么您可用oracle提供的函数REPLACE，该函数使得某列的字符串能被另一字符串代替。  
语法：  
    REPLACE函数的语法如下：  
    REPLACE(string, existion\_string, \[replacement\_string\])  
    变量定义如下：  
    string为字符表达式c  
    existion\_string为已存在的字符串。  
    replacement\_string为用来替代的可选字符串。  
例：使用REPLACE函数  
　　显示了在Course表中如何使用REPLACE来改变课程名称(title)：首先使用查询显示当前课程名称，UPDATE语句中使用REPLACE函数将SEMINAR改变成DISCUSSION，另一查询显示了UPDATE语句的效果。  
    SQL＞update Course set Title = replace(Title, 'SEMINAR', "DISCUSSION');  
注：如果在REPLACE函数中您没有指明用什么字符串去替代，则列中的原字符串将被删除。

5.删除字符串的空格  
　　如果字符串列中包含前导或尾部空格，按某指定值对列进行查询，很可能返回错误结果。  
LTRIM和RTRIM。

6.LPAD左填充函数  
使用LPAD对字符串进行左填充。  
语法：  
    LPAD(string, n, pad\_string);  
    变量定义如下：  
　　string为需左填充的字符串直接值或字符申列。  
　　n为LPAD返回的字符串长度。  
　　pad\_string为左填充到string的字符串。  
　　SQL＞select lpad(my\_col, 20) from test\_trim;  
　　使用LENGTH函数人微言轻LPAD函数的参数：  
　　SQL＞select lpad(my\_col, length(my\_col) + 8, 'you say') from test\_trim;

7.改变字符串的大小写  
　　oracle提供了三个改变字符串大小写的函数：  
　　INITCAP转换每个字的第一个字符为大写  
　　LOWER转换所有字符为小写  
　　UPPER转换所有字符为人写

8.使用DECODE函数(值转换函数)转换字符串  
语法  
    DECODE的语法是：  
    DECODE(expression, value1, returned\_value1, ... valueN, returned\_valueN, \[default\_returned\_value\]  
变量定义如下：  
　　Dexpression为合法的oracle表达式。  
　　valueN为此expression可能取的值：  
　　returned\_valueN为expression等于valueN时DECODE的返回值。  
　　default\_returned\_value为可选项，它是expression不等于任何valueN时DECODE的返回值。  
　　SQL＞select Schedult\_id, Day, decode(Day, 1, 'sun', 2, 'mon', 3, 'tue', 4, 'wed', 5, 'thu', 6, 'fri', 7, 'sat')  
         from Schedult\_Type\_Details order by Schedult\_id, Day;

9.转换字符串为ASCII值  
　　SQL＞select Last\_Name, SCAII(Last\_Name) from Instructor order by Last\_Name;

10.当前日期和时间：SYSDATE

11.转换日期为字符串  
语法:  
    T0\_CHAR函数的格式如下：  
    TO\_CHAR(date\_value，format)  
　　SQL＞select Last\_Name, First\_Name, to\_char(Hire\_Date, "MONTH DD, YYYY') H\_DATE from Employee order by Hire\_Date;  
　　在SUBSTR函数中嵌入to\_char函数  
　　SQL＞select Last\_Name, First\_Name, substr(to\_char(Hire\_date, 'MON), 1, 1) the\_fist\_letter\_of\_the\_month from Employee;

12.转换字符串为日期  
语法：  
　　to\_date(string\_value, date\_format);  
　　SQL＞select SYSDATE, to\_date('07-04-1976', 'MM-DD-YYYY') from dual;

13.日期和时间  
　　在to\_char函数中使用时间格式：  
　　SQL>select Employee\_id, (to\_char(time, clocked\_in, 'HH:MM:SS') Time\_Clocked\_In from Time\_Clock;

14.计算两个日期的差值  
　　SQL>select sysdate + 7 from dual;

15.在Insert\\Update等操作时，数字会自动转换为字符串。数字字段可以用to\_char转换为串。

16.格式化数值字段  
　　select to\_char(Additional\_Fees, '9,999.99') from Course;  
　　货币符号：  
　　select to\_char(Additional\_Fees, '￥9,999.99') from Course;  
　　科学计数法：  
　　select to\_char(Additional\_Fees, '9.9999EEEE') from Course;

17.将字符串转换成数字  
　　to\_number是to\_char的逆运算。  
　　update Security\_Price set Last\_Qtr\_EPS = to\_number('$2.81', '$999.99');

18.内部统计函数  
　　AVG(value):计算平均值，它的参数来自于它所作用的若干行。  
　　STDDEV(value):返回它所作用的若干行作为参数的标准差。  
　　VARIANCE(value):返回它所作用的若干行作为参数的。

19.四舍五入函数和截取函数  
　　四舍五入ROUND(value, \[scale\]) ROUND(101.8) = 102 Round(123.37, 1) = 123.4  
　　截取TRUNC(value, \[scale\]) Trunc(123.33) = 123 Trunc(123.567, 2) = 123.56  
　　求最大整数FLOOR(value) Floor(128.3)=128 Floor(129.8) = 129 除了不能截取小数部分外，Floor几乎等同于Trunc函数。  
　　求最小整数CELL(value) Cell(128.3)=129 Cell(129.8) = 130

20.求最大或最小值  
　　MAX和MIN。

21.替换NULL  
　　NVL(column, value)。  
　　select nvl(Additional, 0) from Course;

22.内部聚合函数  
　　COUNT函数：  
　　查找相异的行：DISTINCT  
　　分组：GROUP BY: select Department\_id, count(\*) from curse group by Department\_id;  
　　group by 和having: select Department\_id, count(\*) from Course group by Department\_id having count(\*) = 4; --哪些部门开设了4门课。  
　　avg和group by: select Department\_id, avg(Additional\_Fees) from Course group by Department\_id;

23.EXISTS  
　　select Last\_Name, Fist\_Name from Instructor I where exists(select \* from Class C where I.Instruetor\_id = C.Instructor\_id);

24.SELECT语句的集合操作  
　　INTERSECE(交)  
    UNION(并): select Epicenter\_Latitude, Epicenter\_Longtitude from UNSD\_Event UNION select Location\_Lat, Location\_Lon, Richter\_Number from RIND\_Event order by 1;  
    MINUS(差): select Last\_Name, First\_Name from Instructor minus select Last\_Name, First\_Name from Hoover\_Instructor;  
      
25.用子查询建立一个表  
　　create table Anthor as select \* from instructor where 1 = 2;

26.创建一个简单的视图  
　　create view Stueent\_No\_Personal  
    as  
    select Student\_ID, Last\_Name, First\_Name from Student;  
      
27.PL/SQL块的结构  
　　说明部分  
　　执行部分  
　　例外处理部分  
　　例：  
　　DECLARE  
　　　max\_records CONSTANT int := 100;  
      i int := 1;  
    BEGIN  
      FOR i in 1..max\_records LOOP  
        INSERT INTO test\_table(record\_number, current\_date) values(i, sysdate);  
      END LOOP;  
      COMMIT;  
   END;  
   /

28.用%TYPE声明一个变量  
　　例：为了说明一个用来储存修理仓库技师名字的变量，可这样 Tech\_Name Dept\_Esimate.Techican %TYPE;

29.用%ROWTYPE声明一个变量  
　　例：用一个复合变量存储Depot\_Estimate表的一行。 Depot\_Est\_Row Depot\_Esimate %ROWTYPE;  
　　Depot\_Est\_Row的元素可以用以下方法使用：Depot\_Est\_Row.Techniciam := 'yujj';

30.一些常见的控制结构  
　　IF语句：  
　　if MOD(i, 5) = 0 then  
　　　rec\_number := 5;  
    elseif MOD(i, 7) = 0 then  
      rec\_number := 7;  
    else  
      rec\_number := i;  
    end if;  
      
    简单的LOOP/EXIT语句：  
    LOOP  
    　　EXIT WHEN...;  
    ENDLOOP;  
      
    WHILE\_LOOP语句：  
    WHILE i <100 LOOP  
      i := i + 1;  
    END LOOP;  
      
    FOR\_LOOP语句：  
    for i in 1..max loop  
      i :=  i + 1;  
      dbms\_output.put\_line('j:'||to\_char(j));  
    end loop;  
      
    GOTO语句：  
    goto more\_processing;  
    ...  
    <<more\_processing>>  
    ...  
    end;  
      
    NULL语句：  
    if (mod(i, 10) = 0 ) then  
      i := i + 1;  
    else  
      NULL;  
    end if;  
      
    赋值语句：  
    pl/sql中使用:=进行赋值。  
      
31.在PL/SQL中使用SQL语句  
    PL/SQL和SELECT语句：  
    set serveroutput on  
    declare  
      Average\_Body\_Temp Patient.Body\_Temp.Deg\_F%TYPE;  
    begin  
      dbms\_output.enable;  
      select avg(Body\_Temp\_Deg\_F) into Average\_Body\_Temp from Patient;  
      dbms\_putput.put\_line('Average body temp is Deg.F:' || to\_char(Average\_Body\_Temp.'999.99'));  
    end;  
      
    子块的应用：  
    一个匿名块，它包含另一个子块  
    declare  
      max\_i constrant int := 100;  
      i int := 1;  
      rec\_number int;  
    begin  
      for i in 1..max\_i loop  
        if mod(i, 5) = 0 then  
          rec\_number := 5;  
        else  
          rec\_number := i;  
        end if;  
        insert into test\_table(record\_number, current\_date) values(record\_number, sysdate);  
          
        -- Here is a sub block;  
        declare  
          max\_j constrant int := 20;  
          j int := 1;  
        begin  
          for j in 1..max\_j loop;  
            rec\_number := rec\_number \* j;  
            insert into test\_table(record\_number, current\_date) values(record\_number, sysdate);  
          end loop;  
        end;  
      end loop;  
      
    一个过程的示例：  
    declare  
      New\_Patient\_ID Patient.Patient\_ID%TYPE;  
      High\_Fever constant real := 42.0;  
      
    procedure Record\_Patient\_Temp\_Deg\_C(Patient\_ID varchar2, Body\_Temp\_Deg\_C real) is  
    Temp\_Deg\_F real;  
    begin  
      Temp\_Deg\_F := (9.0/5.0)\*Body\_Temp\_Deg\_C + 32.0;  
      insert into Patient(Patient\_ID, Body\_Temp\_Deg\_F) values(Patient\_ID, TempDeg\_F);  
      commit;  
    end;  
      
    begin  
      New\_Patient\_ID := 'GG9999';  
      Record\_Patient\_Temp\_Deg\_C(New\_Patient\_ID, High\_Fever);  
    end;  
    /  
    --一个过程内声明的变量在过程体外不能被使用。  
      
    一个函数的示例：  
    function Max\_Additional\_Fees(Dept\_ID IN varchar2)  
      return varchar2 is  
      Additional\_Fees Course.Additional.Fees%TYPE;  
    begin  
      ...  
      return something;  
    end;  
      
    过程和函数的参数类型：IN/OUT/IN OUT  
      
32.关于dbms\_output包的调用：先输入set serveroutput on;

33.系统视图USER\_SOURCE，保存有过程、函数、包等。它有以下四列：  
　　NAME　包括过程、函数、包或包体的名字  
　　TYPE　批出源代码是属于过程、函数包还是包体  
　　TEXT　包含源代码中的一行  
　　LINE　包含在TEXT中源代码所包含的行数  
　　例：select line, text from user\_source where name = 'DROP\_CLASS' order by line;

34.SELECT和存储函数：  
　　在SELECT中可以使用存在的存储函数  
    create or replac function DegF\_add10(Deg\_F IN number)  
      return number is  
    Deg\_C number;  
    begin  
      deg\_c := deg\_f + 10;  
      return deg\_c;  
    end DegF\_add10;  
      
    select body\_temp, DegF\_add10(body\_temp) from patient;

35.附加PL/SQL数据类型  
    boolean    binary\_integer, natural, positive    %type    %rowtype    pl/sql表或数组    用户自定义记录  
      
36.ORACLE预定义例外  
　　DUP\_VAL\_ON\_INDEX:当SQL语句要在存在唯一索引的列中建立重复数据时发生。  
　　INVALID\_NUMBER:当SQL语句指定无效数字时发生。  
　　NO\_DATTE\_FOUND:当SELECT语句没有返回任何行时发生。  
　　TOO\_MANY\_ROWS:在PL/SQL环境中，一条SELECT语句检索多行数据时发生。为了从一个查询检索任意数目的行，可使用游标，可将游标看成是查询返回结果的窗口。  
　　　　在pl/sql中，一条SELECT语句返回多个数据行时就会发生这个例外。  
　　VALUE\_ERROR:多数情况与截断和转换误差有关。如给一个varchar2(5)的字段赋值长度超过5的字符串。

37.SQLCODE和SQLERRM  
　　SQLCODE包括当前执行oracle pl/sql语句的错误状态。如果SQL语句没有错误，则SQLCODE为0；  
　　SQLERRM包括与SQLCODE相关的错误信息。如果SQL语句执行成功，则SQLCODE为0，SQLERRM为一个字符串：ORA\_0000:normal,successful completion.

38.使用游标的步骤  
　　1.说明(declare)游标，给游标起个名字，并且与SELECT语句在语法上相关联。  
　　2.打开(open)游标，Oracle RDBMS执行与游标相关的查询，并确定所限定的行（活动集）。  
　　3.用游标提取数据行：将每一行的值返回到PL/SQL子程序环境中，一次返回一行。  
　　4.关闭(close)游标，释放相关资源。

39.说明游标  
　　CURSOR cursor\_name  
    \[(parameter1 parameter1\_datetype\[ := default1\],  
    ..  
    \[parameterN parameterN\_datetype\[ := derfaultN\])\]  
    IS select\_stmt;  
   
40.打开游标  
   Open cursor\_name;  
   说明：如果在定义游标时，没有声明参数的类型，在OPEN游标时，必须给相关的参数指定变量或是直接值。如open tempcur(20,30);

41.从游标中取出行  
    loop  
      fetch tmpcur into field1, field2;  
      exit when tmpcur%notfound;  
        
      dbms\_output.put\_line(field1);  
    end loop;

42.关闭游标  
    close tmpcur;  
      
43.取游标返回的行数  
　　dbms\_output.put\_line(tmpcur%rowcount);

44.在oracle数据库中查询结果的行号使用伪列ROWNUM表示(从1开始)。!!!数据库分页可以使用它!!!  
    例如select \* from employee where rownum<10 返回前10条记录。但因为rownum是在查询之后排序之前赋值的，所以查询employee按birthday排序的第100到120条记录应该这么写：  
    select \* from (  
      select my\_table.\*, rownum as my\_rownum from (select name, birthday from employee order by birthday  
        ) my\_table where rownum <120  
    )where my\_rownum>=100  
    例：给多表查询的结果加上序列号  
　　select rownum as "SerialNo", b."pName", a."compName" from "fundCompAccount" a, "fundPersonAccount" b   
　　where a."compAccount" = b."compAccount";

\--以上已经打印20040511  
　　Oracle选定前20条数据  
  　　select \* from "fundDuty" where rownum < 20;  
    Oracle选定21至40条数据  
      select \* from "fundDuty" where rownum <=40  
      minus  
      select \* from "fundDuty" where rownum <=21