sql函数基础常用命令
(1) 数据记录筛选：
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 like ''%字段值%'' order by 字段名 [desc]"
sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 in (''值1'',''值2'',''值3'')"
sql="select * from 数据表 where 字段名 between 值1 and 值2"
(2) 更新数据记录：
sql="update 数据表 set 字段名=字段值 where 条件表达式"
sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"
(3) 删除数据记录：
sql="delete from 数据表 where 条件表达式"
sql="delete from 数据表" (将数据表所有记录删除)
(4) 添加数据记录：
sql="insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"
sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表)
(5) 数据记录统计函数：
AVG(字段名) 得出一个表格栏平均值
COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加
引用以上函数的方法：
sql="select sum(字段名) as 别名 from 数据表 where 条件表达式"
set rs=conn.excute(sql)
用 rs("别名") 获取统的计值，其它函数运用同上。
(5) 数据表的建立和删除：
CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )
例：CREATE TABLE tab01(name varchar(50),datetime default now())
DROP TABLE 数据表名称 (永久性删除一个数据表)
select distinct * into yjkc2 from yjkc
drop table yjkc
select * into yjkc from yjkc2
drop table yjkc2
SQL连接方式
dim conn
set conn=server.createobject("ADODB.connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=服务器;UID=用户名;PWD=密码;DATABASE=表名;"
access连接方式
dim conn
dim connstr
on error resume next
connstr="DBQ="+server.mappath("数据库路径")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set conn=server.createobject("ADODB.CONNECTION")
conn.open connstr
包含页
调用表
打开数据库之后固定修改某个字段
conn.execute("Update 表名 Set 字段=字段+1 Where 字段=变量"
删除内容
set rs=server.createobject("adodb.recordset")
sql="select * from 表名 where 字段=变量"
rs.open sql,conn,1,3
有图片就删除图片
Picture=rs("newspic")
set fs=server.CreateObject("scripting.filesystemobject")
Picture=server.MapPath(Picture)
if fs.FileExists(Picture) then
fs.DeleteFile Picture,true
end if
rs.delete
rs.update
response.redirect request.servervariables("h
ttp_referer")
end if
分页
| id | 关 键 词; | 价 格 | 修 改 | 删 除 | |
| 暂时没有记录！！ | |||||
| 共有<%=rs.recordcount%>个关键词，<%=page%>/<%=TotalPages%>页 【?page=1">首页】 <% if page>1 then %> 【?page=<%=StartPageNum-1%>">上一页】 <%else%> <%end if%> <% For I=StartPageNum to EndPageNum if I<>page then %><%=I%> <% else %><%=I%> <% end if %><% Next %><% if EndPageNum | |||||
r>) 参数:timeinterval 是时间单位; date1,date2是有效的日期表达式,firstdayofweek,firstdayofyear 是任意选项. 例子: 结果:There are 150 days to millenium from 8/4/99.
8. #.函数day() 功能:返回一个整数值,对应于某月的某日 格式:day(date) 参数:date是一个有效的日期表达式; 例子lt;% =date(#8/4/99#) %> 结果:4
9. #.函数formatcurrency() 功能:转换成货币格式 格式:formatcurrency(expression [,digit[,leadingdigit[,paren[,groupdigit>>) 参数:expression 是有效的数字表达式;digit表示小数点后的位数;leadingdigit,paren,groupdigit是任意选项. 例子lt;%=FormatCurrency(34.3456)%> 结果34.35
10. #.函数Formatdatetime() 功能:格式化日期表达式/变量 格式:formatdatetime(date[,nameformat]) 参数:date为有效的日期表达式/变量;nameformat是指定的日期格式常量名称. 例子lt;% =formatdatetime("08/04/99",vblongdate) %> 结果:Wednesday,August 04,1999
11. #.函数Isnumeric() 功能:返回一个布尔值,判断变量是否为数字变量,或者是可以转换成数字的其它变量. 格式:isnumeric(expression) 参数:expression 是任意的变量. 例子: 结果: true.
12. #.函数Isobject() 功能:返回一个布尔值,判断变量是否为对象的变量, 格式:isobject(expression) 参数:expression 是任意的变量. 例子: 结果: true
13. #.函数:Lbound() 功能:返回一个数组的下界. 格式:Lbound(arrayname[,dimension]) 参数:arrayname 是数组变量,dimension 是任意项 例子: 结果:0
14. #.函数Lcase() 功能:将一字符类型变量的字符全部变换小写字符. 格式:Lcase(string) 参数:string是字符串变量 例子: 结果:this is lcase!
15. #.函数left() 功能:截取一个字符串的前部分; 格式:left(string,length) 参数:string字符串,length截取的长度. 例子: 结果:this i
16. #.函数len() 功能:返回字符串长度或者变量的字节长度 格式:len(string |varname) 参数:string字符串;varname任意的变量名称 例子: 结果:15
17. #.函数ltrim() 功能:去掉字符串前的空格. 格式:ltrim(string) 参数:string 字符串. 例子: 结果:Today
19. #.函数minute() 功能:返回一数值, 表示分钟 格式:minute(time) 参数: time是时间变量 例子lt;% =minute(#12:23:34#) %> 结果:23
20. #.函数month() 功能:返回一数值, 表示月份 格式:month(time) 参数:time是日期变量 例子lt;% =month(#08/09/99) %> 结果:9
21. #.函数monthname() 功能:返回月份的字符串(名称). 格式:Monthname(date [,abb]) 参数:date是日期变量,abb=true时 则月份的缩写, 例子: 结果:April
22. #.函数Now() 功能:返回系统的当前时间和日期. 格式:now() 参数:无 例子: 结果: 05/10/00 8:45:32 pm
23. #.函数:replace() 功能:在字符串中查找,替代指定的字符串. 格式:replace(strtobesearched,strsearchfor,strreplacewith [,start[,count[,compare>]) 参数:strtobesearched是字
符串; strsearchfor是被查找的子字符串;strreplacewith 是用来替代的子字符串.start,count,compare 是任意选项. 例子: 结果:this is an orange.
24. #.函数right() 功能:截取一个字符串的后部分 格式:right(string,length) 参数:string字符串,length截取的长度. 例子: 结果:st!
25. #.函数rnd() 功能:返回一个随机数值 格式:rnd[(number)] 参数:number是任意数值. 例子: 结果:0/1数值之一,无randomize(), 则不能产生随机数.
26. #.函数round() 功能:完整数值 格式:round(expression[,numright]) 参数:expression数字表达式;numright任意选项. 例子: 结果: 12
27. #.函数rtrim() 功能:去掉字符串后的空格. 格式:rtrim(string) 参数:string 是字符串 例子: 结果:this is a test!
28. #.函数second() 功能:返回一个整数值. 格式:second(time) 参数:time是一个有效的时间表达式; 例子lt;% =second(# 12:28:30#) %> 结果:30
29. #.函数strReverse() 功能:返回与原字符串排列逆向的字符串. 格式:strreverse(string) 参数:string是字符串 例子lt;% =strreverse("this is a test!") 结果:!tset a si siht
30. #.函数time() 功能:返回当前系统的时间值. 格式:time() 参数:无 结果:9:58:28 Am 关键词：ASP
31. #.函数trim() 功能:删去字符串前,后的空格. 格式:trim(string) 参数:string 字符串. 例子: 结果:this is a test!
32. #.函数UBound() 功能:返回一个数组的上界. 格式:Ubound(expression [,dimension]) 参数:expression 是数组表达式/数组变量,dimension 是任意项 例子: 结果: 2
33. #.函数:UCase() 功能:将一字符类型变量的字符全部变换成大写字符. 格式:Ucase(string) 参数:string是字符串变量 例子: 结果:THIS IS LCASE!
34. #.函数Vartype() 功能:返回变量的常量代码(整数) 格式:Vartype(varname) 参数:varname是任何类型的变量名称. 例子: 结果:2 (2表示整数,须要参考ASP常量代码.)
35. #.函数Weekday() 功能:返回一个整数,对应一周中的第几天. 格式:Weekday(date [,firstofweek]) 参数:date为日期变量,firstofweek为任选项. 例子: 结果:3(3 表示是星期二)
36. #.函数weekdayname() 功能:返回字符串,对应星期几. 格式:weekdayname(weekday[,abb[,firstdayofweek>) 参数:weekday为日期变量,abb,firstdayofweek为任选项. 例子: 结果: Wednesday
37. #.函数year() 功能:返回日期表达式所在的年份. 格式:year(date) 参数:date是有效的日期表达式 例子: 结果:1999
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]
变量定义如下：
Dexpre
ssion为合法的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