`
wang_2011_ying
  • 浏览: 150835 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

SQL语句及函数

 
阅读更多
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;

     ...

     <>

     ...

     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>

     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
分享到:
评论

相关推荐

    常用SQL语句及函数

    常用SQL语句及函数

    常用SQL语句及函数使用方法

    常用SQL语句及函数使用方法,主要用于SQL SERVER 其他数据库仅供参考

    Oracle数据库常用sql语句的分类和常用函数.docx

    以上内容总结了Oracle数据库中常用的SQL语句及函数,涵盖了数据库的基本操作、数据管理、数据定义和查询等方面。对于初学者来说,掌握这些基础是非常重要的。希望这份指南能够帮助大家更好地理解和应用Oracle数据库...

    sql语句万能生成器,sql语句,sql语句生成

    SQL语句是数据库操作的核心,它用于查询、插入、更新和删除数据,是任何数据库管理系统中的基础工具。在IT行业中,编写SQL语句是一项必备技能,但手动编写和调试SQL语句可能会耗费大量时间和精力,尤其在处理复杂...

    Oracle Sql语句转换成Mysql Sql语句

    本项目提供了一个Java源码工具,能够帮助用户便捷地将Oracle SQL语句转换为MySQL SQL语句。 Oracle SQL与MySQL SQL的主要差异在于以下几个方面: 1. **数据类型**:Oracle支持的数据类型如NUMBER、LONG、RAW等在...

    sql语句函数电子书

    sql语句函数电子书 非常适合新手查询的一本书 老手绕道

    经典SQL语句函数大全

    本资源“经典SQL语句函数大全”聚焦于SQL中的各种函数,旨在帮助用户掌握SQL的核心功能并提升数据库操作效率。** **一、SQL基本概念** SQL分为DDL(Data Definition Language)和DML(Data Manipulation Language)...

    Visual C++源代码 117 如何直接获取SQL语句聚合函数值

    Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值...

    PB脚本中SQL语句写法与SQL中语句写法对照

    PB脚本中SQL语句写法与SQL中语句写法对照 PB脚本中SQL语句写法与SQL中语句写法对照是非常重要的知识点,因为PB脚本和SQL语言在写法和应用中有所不同。本文将对PB脚本中SQL语句写法和SQL中语句写法进行对比和分析。 ...

    基本SQL语句及PL/SQL语句

    标题中的“基本SQL语句及PL/SQL语句”涵盖了关系数据库管理中两个重要的概念:SQL(Structured Query Language)和PL/SQL(Procedural Language/SQL)。SQL是用于管理和处理关系数据库的标准语言,而PL/SQL是Oracle...

    sql与oracle函数比较已经经典的sql语句大全

    这是sql与oracle函数比较已经经典的sql语句大全,里面包含了Oracle常用函数与sql常用函数对比翻译,oracle与sqlserver字符、时间函数的不同总结,SQLServer和Oracle函数比较和经典SQL语句大全,对新手及高级程序员都...

    hibernate执行原生sql语句

    "hibernate执行原生sql语句" Hibernate 是一种流行的 ORM(Object-Relational Mapping)框架,用于将 Java 对象映射到关系数据库中。然而,在一些情况下,我们需要直接执行原生 SQL 语句,而不是使用 Hibernate 的...

    SQL表值函数

    创建SQL表值函数需要使用CREATE FUNCTION语句。下面是一个简单的示例: ```sql CREATE FUNCTION [dbo].[BOM]() RETURNS @return TABLE ``` 四、SQL表值函数的组成部分 SQL表值函数由以下几个部分组成: * 函数名...

    完成超长SQL语句执行前拆分

    对于那些使用VB进行数据库编程的开发者来说,当面对超长SQL语句时,`splitSql`函数提供了一种有效的解决方案。通过这种方法,可以轻松地避免由于SQL语句过长而导致的执行错误,从而提高程序的稳定性和可靠性。

    SQL语句大全(存储过程、函数、基本语法)

    这个压缩包中的"SQL语句大全"很可能是一个详细的指南,涵盖了SQL的各种方面,包括基本语法、函数以及存储过程。下面将对这些主题进行深入的阐述。 首先,SQL的基本语法是所有查询的基础。它包括了SELECT语句,用于...

    解决python 执行sql语句时所传参数含有单引号的问题

    在Python编程中,当我们需要执行SQL语句时,有时会遇到参数中包含单引号(')的情况,这可能导致SQL语法错误。在描述的问题中,作者在尝试插入数据到`teacher`表时遇到了编程错误,因为`t_info`字段的值`result2`...

    sqlserver自动生成sql语句工具sqlserver转oracle

    本篇文章将详细探讨如何利用工具实现SQL Server自动生成SQL语句并转换到Oracle。 首先,标题中的"sqlserver自动生成sql语句工具"指的是可以分析SQL Server数据库结构和数据,自动生成对应的SQL创建语句的软件。这种...

    ABAP SQL 语句 动态 操作

    在ABAP中,我们可以使用`DATA`声明和`CONCATENATE`函数来动态构建SQL语句。例如,`sql(256)`变量用于存储构建的SQL语句,通过将不同部分连接在一起,如表名、字段名和条件,以适应不同的查询需求。 2. **数据结构...

    arcgis中的sql语句

    ### ArcGIS中的SQL语句详解 #### 一、SQL在ArcGIS中的角色与应用 SQL,全称为Structured Query Language,即结构化查询语言,是专为数据库设计的一种标准语言,用于管理和操作数据库中的数据。在ArcGIS环境中,SQL...

Global site tag (gtag.js) - Google Analytics