|
|
|
|
SQL连接,类型,建表,用法收集
|
|
来源： 作者： 发布时间：2007-12-03
|
|
####################连接###########################
DBHost = "abus76"
DBUser = "sa"
DBPass = "111111"
DBName = "gbbook"
set Conn=Server.CreateObject("ADODB.Connection")
'Conn.Open "driver={sql server};server=" & DBHost & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & DBName '这一句有时读不出字段
conn.open "provider=sqloledb;uid=" & DBUser & ";pwd=" & DBPass & ";server=" & DBHost & ";database="& DBName
#####################################################
bigint 整型,8bytes的整数类型，范围是-2^63 (-9223372036854775808) 到2^63-1 (9223372036854775807)
binary (n) 固定长度二进制数据，n在1~8000之间，存储空间为n+4字节.
bit 1位，值为0或1
char 非unicode字符串的固定长度，n=1~8000
datetime 时间 8字节，描述某天的日期和时刻，值的精确度为1/300秒 默认时间不用noe()而用GETDATE( )
decimal 数字,值为0~255
float N在1~24之间，4字节，7位精度 N=1~7为real N在25~53之间，8字节，15位精度 =8~15为float
image 图片
int 整型 （-2^31 (-2,147,483,648) 到2^31 - 1 (2,147,483,647)）
money 8字节，存放货币类型，值为-2^63~2^63-1
nchar 固定长度unicode字符串n=1~4000
ntext 可变长度unicode数据，最大长度为230-1个字符
numeric
nvarchar 固定长度unicode字符串n=1~4000
real (看float)
smalldatetime 4字节，描述某天的日期和时刻，精度为分钟
smallint （小型整数）是精度为 5 位的两字节整数。值为-2^15~2^15-1
smallmoney 4字节，存放货币类型，值为-214748.3648~+214748.3647近似数值数据类型
sql_variant
text 文本(备注类型)
timestamp 8字节，存放在数据库内唯一的数据
tinyint 1字节，值为0~255
uniqueidentifier 16字节，存放全局唯一标识（GUID）
varbinary (n)可变长度二进制数据，n=1~8000
varchar 可变长度，非unicode字符串n=1~8000
############ SQL Server 中易混淆的数据类型 ################
(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的长度都在1到8000之间，它们的区别在于char是定长字符数据，而varchar是变长字符数据。所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。
后面三种数据类型和前面的相比，从名称上看只是多了个字母"n"，它表示存储的是Unicode数据类型的字符。写过程序的朋友对Unicode应该很了解。字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较：nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。
(2)datetime和smalldatetime
datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。
smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。
(3)bitint、int、smallint、tinyint和bit
bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。
int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据。
tinyint：从0到255的整数数据。
bit：1或0的整数数据。
(4)decimal和numeric
这两种数据类型是等效的。都有两个参数：p（精度）和s（小数位数）。p指定小数点左边和右边可以存储的十进制数字的最大个数，p必须是从 1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数，s必须是从0到p之间的值，默认小数位数是0。
(5)float和real
float：从-1.79^308到1.79^308之间的浮点数字数据。
real：从-3.40^38到3.40^38之间的浮点数字数据。在SQL Server中，real的同义词为float(24)。
例:
strSQL = "Create TABLE gbook("
strSQL =strSQL +"id int IDENTITY (1,1) NOT NULL , " ' 自动编号
strSQL =strSQL +"jd_name nvarchar (20) NULL , " ' 字符型 20
strSQL =strSQL +"jd_tel nvarchar (50) NULL , "
strSQL =strSQL +"jd_addr nvarchar (120) NULL , "
strSQL =strSQL +"jd_email nvarchar (120) NULL , "
strSQL =strSQL +"jd_title nvarchar (120) NULL , "
strSQL =strSQL +"jd_content ntext NULL , " ' 备注型
strSQL =strSQL +"jd_date datetime NULL , "
strSQL =strSQL +"jd_ip nvarchar (15) NULL , "
strSQL =strSQL +"jd_view tinyint NULL , " ' 数字字节型
strSQL =strSQL +"jd_cls tinyint NULL "
strSQL =strSQL +") "
修改字段属性
strSQL = "Alter TABLE jd_biao "
strSQL =strSQL +"Alter COLUMN jd_addr nvarchar (255) NULL "
strSQL =strSQL +" "
'将表jd_biao的jd_addr字段,改为字符型255
因为我装的是MSDE，没有发现查询分析器，也没办法运行sql文件,我到处支找SQL的光盘也找不到，苦
恼了好一陈子。只能在导出导入里新建一个数据库(呵呵，发现在ms.net里居然可以编辑查看sql数据
库，虽然也不能运行sql,不过给了我不少方便),就此写了一个ASP文件。全自动的生成chinaasp的sql
数据库。
去chinaasp不觉得什么，现在真正解开他的内容一看，真的令人叹服，委实是精品中的精品。难怪这么
多人效仿而乐此不疲。
如果你有查询分析器，这个用一下反正也很方便，如果你没有查询分析器，那是更好了，其中连接SQL的
可能是被人改了，不能用，我都改好了。
以后如果写其他的SOL数据库，只要打开conn.asp与setup.asp。依样画葫芦就是了，总之自已手写出来的
东西，用得特舒服。
声明：好象chinaasp不提供免费使用的授权，我也不太清楚，这个纯粹是个人研究，不可以真的拿去用了
或改了。
CONN.ASP文件：
＜%
dim conn
dim connstr
connstr="driver={SQL Server};server=localhost;UID=sa;PWD=;database=bbsbbs"
set conn=server.createobject("ADODB.CONNECTION")
if err.number＜＞0 then
err.clear
set conn=nothing
Response.Write "创建ADO对象失败"
Response.End
else
conn.open connstr
if err then
err.clear
set conn=nothing
Response.Write "SQL数据库连接失败，请检查数据库是否存在，以及odbc是否存在"
Response.End
end if
end if
sub endConnection()
conn.close
end sub
%＞
setup.asp文件：
＜!--#include file="conn.asp"--＞
＜html＞
＜STYLE TYPE="text/css"＞
＜!--
body{
BACKGROUND: white;
FONT-FAMILY: 宋体;
FONT-SIZE: 75%
}
--＞
＜/STYLE＞
＜title＞chinaasp安装＜/title＞＜body＞
＜%
If Request.QueryString("setup") = "yes" Then
on error resume next
Set adoRS = Server.CreateObject("ADODB.Recordset")
strSQL = "Create TABLE Announce ( "
strSQL =strSQL +"AnnounceID int IDENTITY (1, 1) NOT NULL ,"
strSQL =strSQL +"ParentID int NULL , "
strSQL =strSQL +"Child int NULL , "
strSQL =strSQL +"BoardID int NULL , "
strSQL =strSQL +"UserName nvarchar (50) NULL , "
strSQL =strSQL +"UserEmail nvarchar (255) NULL , "
strSQL =strSQL +"URL nvarchar (255) NULL , "
strSQL =strSQL +"URLTitle nvarchar (255) NULL , "
strSQL =strSQL +"URLPic nvarchar (255) NULL , "
strSQL =strSQL +"Topic nvarchar (255) NULL , "
strSQL =strSQL +"Body ntext NULL , "
strSQL =strSQL +"DateAndTime datetime NULL , "
strSQL =strSQL +"hits int NULL , "
strSQL =strSQL +"length int NULL , "
strSQL =strSQL +"RootID int NULL , "
strSQL =strSQL +"layer int NULL , "
strSQL =strSQL +"orders int NULL , "
strSQL =strSQL +"emote int NULL , "
strSQL =strSQL +"ip nvarchar (20) NULL , "
strSQL =strSQL +"signature nvarchar (255) NULL , "
strSQL =strSQL +"replyMail bit NOT NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create TABLE board ("
strSQL =strSQL +"BoardID int IDENTITY (1, 1) NOT NULL , "
strSQL =strSQL +"BoardName nvarchar (100) NULL ,"
strSQL =strSQL +"BoardMaster int NULL , "
strSQL =strSQL +"BoardMaster2 int NULL , "
strSQL =strSQL +"BoardMaster3 int NULL , "
strSQL =strSQL +"cateID int NULL , "
strSQL =strSQL +"description nvarchar (500) NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create TABLE boardCate ("
strSQL =strSQL +"id int IDENTITY (1, 1) NOT NULL , "
strSQL =strSQL +"CateName nvarchar (50) NOT NULL , "
strSQL =strSQL +"Description nvarchar (244) NULL , "
strSQL =strSQL +"Orders int NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create TABLE essence ("
strSQL =strSQL +"id int IDENTITY (1, 1) NOT NULL , "
strSQL =strSQL +"announceid int NULL , "
strSQL =strSQL +"boardid int NULL , "
strSQL =strSQL +"username nvarchar (50) NULL , "
strSQL =strSQL +"useremail nvarchar (255) NULL , "
strSQL =strSQL +"url nvarchar (255) NULL , "
strSQL =strSQL +"urlTitle nvarchar (255) NULL , "
strSQL =strSQL +"urlPic nvarchar (255) NULL , "
strSQL =strSQL +"Topic nvarchar (255) NULL , "
strSQL =strSQL +"Body ntext NULL , "
strSQL =strSQL +"dateAndTime nvarchar (50) NULL , "
strSQL =strSQL +"hits int NULL , "
strSQL =strSQL +"length int NULL , "
strSQL =strSQL +"emote int NULL , "
strSQL =strSQL +"keywords nvarchar (255) NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create TABLE site ( "
strSQL =strSQL +"SiteMasterPwd nvarchar (50) NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create TABLE [user] ( "
strSQL =strSQL +"UserID int IDENTITY (1, 1) NOT NULL , "
strSQL =strSQL +"UserName nvarchar (50) NULL , "
strSQL =strSQL +"UserEmail nvarchar (255) NULL , "
strSQL =strSQL +"UserPassword nvarchar (11) NULL , "
strSQL =strSQL +"icq nvarchar (20) NULL , "
strSQL =strSQL +"homepage nvarchar (255) NULL , "
strSQL =strSQL +"sex nvarchar (10) NULL , "
strSQL =strSQL +"passanswer nvarchar (100) NULL , "
strSQL =strSQL +"incometime smalldatetime NULL , "
strSQL =strSQL +"birthday smalldatetime NULL , "
strSQL =strSQL +"points int NULL , "
strSQL =strSQL +"visits int NULL , "
strSQL =strSQL +"country nvarchar (20) NULL , "
strSQL =strSQL +"city nvarchar (20) NULL , "
strSQL =strSQL +"passquest nvarchar (100) NULL , "
strSQL =strSQL +"signature nvarchar (255) NULL , "
strSQL =strSQL +"disabled int NULL "
strSQL =strSQL +") "
Set adoRS = Conn.Execute(strSQL)
strSQL ="Alter TABLE [boardCate] WITH NOCHECK ADD CONSTRAINT [PK_boardCate] PRIMARY KEY CLUSTERED ([id])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Alter TABLE [Announce] WITH NOCHECK ADD CONSTRAINT [DF__announce__replyM__3F466844] DEFAULT (0)
FOR [replyMail],"
strSQL =strSQL +"CONSTRAINT [PK_Announce] PRIMARY KEY NONCLUSTERED ([AnnounceID])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Alter TABLE [board] WITH NOCHECK ADD CONSTRAINT [PK_board] PRIMARY KEY NONCLUSTERED ([BoardID])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Alter TABLE [essence] WITH NOCHECK ADD CONSTRAINT [PK_essence] PRIMARY KEY NONCLUSTERED ([id])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Alter TABLE [user] WITH NOCHECK ADD CONSTRAINT [DF_user_disabled] DEFAULT (0) FOR [disabled],"
strSQL =strSQL +"CONSTRAINT [PK_user] PRIMARY KEY NONCLUSTERED ([UserID])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [Boardid] ON [Announce]([BoardID])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [announce0] ON [Announce]([BoardID], [AnnounceID], [ParentID])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [IX_Announce] ON [Announce]([orders])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [announce00] ON [Announce]([RootID], [BoardID], [orders])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [IX_boardCate] ON [boardCate]([Orders])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [Boardid] ON [essence]([boardid])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [points] ON [user]([points])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [username] ON [dbo].[user]([UserName])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="Create INDEX [IX_user] ON [dbo].[user]([UserName])"
Set adoRS = Conn.Execute(strSQL)
strSQL ="insert site(sitemasterpwd) values(“12345“)"
Set adoRS = Conn.Execute(strSQL)
strSQL ="alter table announce add lock bit NOT NULL DEFAULT (0)"
Set adoRS = Conn.Execute(strSQL)
%＞
＜p＞ ＜/p＞
＜p＞操作成功，请勿在相同的数据库重复安装, ＜a href="DEFAULT.ASP" target="_blank" ＞请登录论坛＜/a＞＜br＞
＜br＞
＜br＞
＜a href="admin" target="_blank"＞管理密码已为您设置为12345,请登录管理页面＜/a＞ ＜/p＞
＜%
Else
Response.Write("＜a href=setup.asp?setup=yes＞单击此处开始安装＜/a＞＜br＞＜br＞＜br＞＜a href=""http://www.yescnet.com";;"＞一鹤的chinaasp论坛数据库安装程序＜/a＞ ")
End If
%＞
＜hr＞
＜p align="center"＞＜br＞
＜a href="http://www.yescnet.com";;target="_blank" ＞一鹤的chinaasp论坛数据库安装程序＜/a＞ ||
＜a href="http://www.yescnet.com";; ＞CNET中文网＜/a＞ || ＜a href="http://www.yescnet.com";; ＞WWW.YESCNET.COM＜/a＞＜/p＞
＜/body＞＜/html＞
--语 句 功 能
--数据操作
Select --从数据库表中检索数据行和列
Insert --向数据库表添加新数据行
Delete --从数据库表中删除数据行
Update --更新数据库表中的数据
--数据定义
Create TABLE --创建一个数据库表
Drop TABLE --从数据库中删除表
Alter TABLE --修改数据库表结构
Create VIEW --创建一个视图
Drop VIEW --从数据库中删除视图
Create INDEX --为数据库表创建一个索引
Drop INDEX --从数据库中删除索引
Create PROCEDURE --创建一个存储过程
Drop PROCEDURE --从数据库中删除存储过程
Create TRIGGER --创建一个触发器
Drop TRIGGER --从数据库中删除触发器
Create SCHEMA --向数据库添加一个新模式
Drop SCHEMA --从数据库中删除一个模式
Create DOMAIN --创建一个数据值域
Alter DOMAIN --改变域定义
Drop DOMAIN --从数据库中删除一个域
--数据控制
GRANT --授予用户访问权限
DENY --拒绝用户访问
REVOKE --解除用户访问权限
--事务控制
COMMIT --结束当前事务
ROLLBACK --中止当前事务
SET TRANSACTION --定义当前事务数据访问特征
--程序化SQL
DECLARE --为查询设定游标
EXPLAN --为查询描述数据访问计划
OPEN --检索查询结果打开一个游标
FETCH --检索一行查询结果
CLOSE --关闭游标
PREPARE --为动态执行准备SQL 语句
EXECUTE --动态地执行SQL 语句
DESCRIBE --描述准备好的查询
---局部变量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
---全局变量
---必须以@@开头
--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
--CASE
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
--WAITFOR
--例 等待1 小时2 分零3 秒后才执行Select 语句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 点零8 分后才执行Select 语句
waitfor time ’23:08:00’
select * from employee
***Select***
select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
stockname like '[^F-M]%' --------- (^排除指定范围)
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层select只返回一个行的值，
--------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having选定指定的组
select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
table1.id =* table2.id -------- 右外部连接
select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value为select语句
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表
***alter table*** --- 修改数据库表结构
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
***function(/*常用函数*/)***
----统计函数----
AVG --求平均值
COUNT --统计数目
MAX --求最大值
MIN --求最小值
SUM --求和
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
--求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP()函数返回总体标准差
--VAR()
--VAR()函数返回表达式中所有值的统计变异数
--VARP()
--VARP()函数返回总体变异数
----算术函数----
/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
PI() --返回值为π 即3.1415926535897936
RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数
----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
CHAR() --函数用于将ASCII 码转换为字符
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
STR() --函数把数值型数据转换为字符型数据
LTRIM() --函数把字符串头部的空格去掉
RTRIM() --函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个SOUNDEX 函数返回值的第一个字符不同
--1 两个SOUNDEX 函数返回值的第一个字符相同
--2 两个SOUNDEX 函数返回值的第一二个字符相同
--3 两个SOUNDEX 函数返回值的第一二三个字符相同
--4 两个SOUNDEX 函数返回值完全相同
QUOTENAME() --函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --函数将指定的字符串的字符排列顺序颠倒
REPLACE() --函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串
----数据类型转换函数----
CAST() 函数语法如下
CAST() ( AS [ length ])
CONVERT() 函数语法如下
CONVERT() ([ length ], [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
199 Jan 15 2000
----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD( , ,)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF( , ,)
--函数返回两个指定日期在datepart 方面的不同之处
DATENAME( , ) --函数以字符串的形式返回日期的指定部分
DATEPART( , ) --函数以整数值的形式返回日期的指定部分
GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME(, ) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY([, seed increment]) [AS column_name])
--IDENTITY() 函数只在Select INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL(, ) --函数将表达式中的NULL 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(, )
--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
在ADO使用Select语法一
Select表达式
接着，让我们看看Select表达式，Select用来在表中寻找符合特定条件的记录，语法如下：
Select [关键字] { * | 表名称.* | [表名称.]字段名称1 [AS 别名1] [, [表名称.] 字段名称2 [AS 别名2] [, ...]]}
FROM 表运算式 [, ...] [IN 外部表]
[Where... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
关键字：可以用来限制返回之记录的数量，可以设定为ALL、DISTINCT、
DISTINCTROW 、或TOP。如果没有指定述语，缺省值为 ALL。
*：选取全部的字段。
别名：代替在表中原有的字段名称。
表运算式：一个或多个以逗号分隔的表名称。
外部表：如果表不存在目前的表当中，须设定包含表运算式中表的表名称。
执行Select 表达式，并不会改变表之中现有的资料。
一个 Select 表达式的最基本语法是：
Select * FROM 表运算式
您可以使用星号（*）来选取表的全部字段。譬如下例选取 [产品] 表中的全部字段：
Select * FROM 产品
字段名称包含空格或标点符号时，须使用括号[ ]将它括在其中。例如：
Select [电脑 的产品]
如果在FROM子句中的字段名称中，包含多个的表中时，需在字段名称前加上表名称和点（.）运算子，即 表名称.字段名称。譬如下例选取 [产品] 表的 [价格] 字段和 [订单] 表的 [数量] 字段：
Select 产品.价格, 订单.数量
FROM 产品, 订单
Where 产品.代号 = 订单.代号
当您使用Recordset 对象时，Recordset并不认得rs(表名称.字段名称) 格式，即rs(“产品.价格”)会发生错误，您必须使用AS来设定字段名称的别名。譬如：
Select 产品.价格 AS 价格, 订单.数量AS 数量
FROM 产品, 订单
Where 产品.代号 = 订单.代号
如此就可以使用rs(“价格”) 和rs(“数量”)，来读取其字段的资料。
与Select表达式相关的语法：
关键字：ALL/ DISTINCT/ DISTINCTROW/ TOP。
Where：寻找FROM中的表符合特定条件的资料记录。Where 可以使用下面的
BETWEEN、LIKE、IN运算子：
Between...And：判断表达式的值是否落在指定的范围之中。
Like：寻找相符合的关键字。
IN运算式：限定范围。
NOT IN运算式：表示不属于所指定的范围。
orDER BY子句：可以设定排序的字段。
GROUP BY子句：将查询的结果做统计。
HAVING子句：使用在Select 表达式中，筛选已经GROUP BY统计的记录。
Union：可以合并多组查询的结果。
JOIN：连接组合两个表中的字段记录。
子查询（sub query）：表达式中，可以包括Select表达式。
Select...Into：将查询的结果，建立一个产生的表。
详细介绍如下：
ALL/DISTINCT/DISTINCTROW/TOP关键字
使用Select查询时，可以加上关键字，以选取所查询的记录。如下：
ALL：返回所有的记录。
DISTINCT：指定字段中的记录有重复时只返回一条记录，记录不重复。
DISTINCTROW：指定字段中的记录有重复时都不返回。
TOP：返回前面几个记录或几个百分比的记录。
语法如下：
Select [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM 表
ALL返回所有的记录。不加关键字时，与加ALL的意义相同，将返回所有的记录。譬如下列两个例子的执行结果相同，都从产品表中返回所有的记录：
Select ALL * FROM 产品
与以下的执行结果相同：
Select * FROM 产品
DISTINCT不选取指定字段之中的重复资料。使用DISTINCT后，查询的结果，列在
Select DISTINCT后的每个字段的资料值，若相同则只取一条记录，换句话说，指定字段的资料不会有重复的现象。例如，在产品表之中有一些产品名称相同的产品，加上DISTINCT的SQL表达式只会返回一条记录字段为产品名称资料相同的记录：
Select DISTINCT 产品名称 FROM 产品
如果您不加上DISTINCT，上例查询将返回数个包含相同产品名称的记录。
如果Select DISTINCT子句后指定数个字段，查询的结果，所有字段的组合值不会有重复的现象。
DISTINCTROW则不返回指定字段所有重复的记录。
TOP n [PERCENT] ，返回前面几条记录或几个百分比的记录。排列的顺序可以使用ORDER BY子句来指定。譬如找到成绩前10名的学生姓名：
Select TOP 10 姓名
FROM 学生
orDER BY 成绩
如果您没有包含ORDER BY子句，查询将由学生表返回任意的10个记录。
TOP不在相同值间作选择，如果第10及第11的成绩是相同的，查询将返回11个记录。
您可使用PERCENT来设定前面几个百分比的记录，譬如譬如找到成绩前10%的学生姓名：
Select TOP 10 PERCENT 姓名
FROM 学生
orDER BY 成绩
让我们看一个在ASP程式中使用这个SQL指令的例子。
可以利用Distinct找出不重复的记录，譬如ASP程式rs7.asp如下，[Insert Into 产品(代号,名称) Select Distinct 代号,名称 From 产品 where 代号 ='C2000'] 使用Distinct与Insert Into新增代号为C2000的记录：
%
'Distinct 代号,名称只判断代号,名称是否相同, 相同者只加一条记录
sql = "Insert Into 产品(代号,名称) Select Distinct 代号,名称 From 产品 where 代号 ='C2000'"
Set a = conn1.Execute(sql)
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select * from 产品 where 代号 ='C2000'"
rs3.Open sql,conn1,1,1,1
%
TABLE COLSPAN=8 CELLPADDING=5 BORDER=0
TR
TD ALIGN=CENTER BGCOLOR="#800000"FONT COLOR="#FFFFFF"代号
/FONT/TD
TD ALIGN=CENTER BGCOLOR="#800000"FONT COLOR="#FFFFFF"名称
/FONT/TD
TD ALIGN=CENTER BGCOLOR="#800000"FONT COLOR="#FFFFFF"价格
/FONT/TD
TD ALIGN=CENTER BGCOLOR="#800000"FONT COLOR="#FFFFFF"数量
/FONT/TD
/TR
% Do while not rs3.EOF %
TR
TD BGCOLOR="f7efde" ALIGN=CENTER%= rs3("代号")%/TD
TD BGCOLOR="f7efde" ALIGN=CENTER%= rs3("名称")%/TD
TD BGCOLOR="f7efde" ALIGN=CENTER%= rs3("价格")%/TD
TD BGCOLOR="f7efde" ALIGN=CENTER%= rs3("数量")%/TD
/TR
%
rs3.MoveNext
Loop
rs3.Close
%
/TABLE
Where
Where寻找FROM中的表符合特定条件的资料记录，Where 使用在Select、Update、或Delete表达式。
如果Select查询中没有指定Where子句，将返回表中所有的资料。如果在Select查询多个表，且没有使用Where子句、或JOIN 子句的话，查询结果为多个表资料的乘积。
Where设定特定的条件，譬如：
FROM 产品 Where 分类 = '电脑'：表示选择 [分类] 为 [电脑] 的 [产品]
Where 价格 Between 1000 And 5000：表示价格介于1000至5000之间 。
一个Where子句，最多可包含40个运算式，运算式之间由AND或OR等逻辑运算子相连结。
设定特定的条件时，依照字段类型，前后加不同的符号，譬如：
文字：前后加单引号，譬如Where 分类 = '电脑'。
数字：前后不加符号，譬如Where 数量 100。
日期：前后加#号，譬如Where 日期 = #5/15/99#。
Where可以使用下面的BETWEEN、LIKE、IN运算子。
******************************************************
ADO使用Select语法二
接上一篇文章：
Where可以使用下面的BETWEEN、LIKE、IN运算子。
Between...And
Between...And判断表达式的值是否落在指定的范围之中。语法为：
expr Between value1 And value2
表示如果expr的值介于value1和value2之间，则Between...And运算子会返回True；否则它会返回False。
您也可以加上逻辑运算 Not expr Between value1 And value2，来判断相反的条件，即expr落在value1和 value 2的范围之外。
如果expr、value1、或value2为Null时，则Between...And会返回Null值。
您不能在 Between...And 运算子中使用通配符*，例如，您不能使用 100* 和 500* 来寻找介于1000至5000中的产品代号。
Like
Like寻找相符合的关键字。
Like跟随一个运算式，列出所要比较的字串或字符串文字。
在一运算式中，您可以使用 Like 运算子将一字段资料和一字串运算式做一比较。您可以使用通配符*来找出相类似的资料，譬如Like "王*"，则查询会返回所有字段中以 [王] 做为开头的资料。
Like后运算式可以使用的符号：
%：通配符，表示一个或多个字符，譬如LIKE jack% 将找到所有以jack为起头的字串文字，LIKE %jack%则找到包括jack的字串文字。
_：一个字符的通配符，譬如LIKE jack_ 将找到如jack1、jacka等字串文字，LIKE _jack_ 则找到如ajackb、cjackf等字串文字。
[x-y]：指定字符范围，譬如LIKE [a-c]jack 将找到如ajack1、bjack、cjack等字串文字。
[^x-y]：指定排除的字符范围，譬如LIKE [^a-c]jack 将不会找到如ajack1、
bjack、cjack等字串文字。
下例中，会得到以字母A做为开头而其后接着介于B到G之间的任何字母和一个数字的资料：
Like "A[B-G]#"
IN
IN运算式，限定范围。
语法为：
expr [Not] In(value1, value2,…)
其中expr为运算式字段。value1, value2, …为指定的范围清单。
例如，您可以使用In运算子，来指定学生必须居住的城市，为广州市、深圳市、或汕头市：
Select *
FROM 学生
Where 城市 In ('广州市','深圳市','汕头市')
让我们看一个ASP程式使用这个SQL指令的例子。
我们可以利用IN子句决定范围，譬如ASP程式rs24.asp如下，[Select 姓名,科目,分数 From 考试 Where 分数 In (Select 分数 From 考试 Where 分数
=60)]，使用IN找出分数大于或等于60分的记录：
%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver=
{Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "Select 姓名,科目,分数 From 考试 Where 分数 In (Select 分数
From 考试 Where 分数=60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "pIn: 低于60分不算"
Do while not rs2.EOF
Response.Write "BR" & rs2("姓名") & " " & rs2("科目") & " 分数: " &
rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%
以上的ASP程式rs24.asp，在用户端使用浏览器，浏览执行的结果，显示分数大于或等于60分的记录。
NOT IN
NOT IN运算式，表示不属于所指定的范围。
让我们看一个于ASP程式中使用这个SQL指令的例子。
我们可以利用Not In子句决定不应显示的记录，譬如ASP程式rs24.asp如下，[Select 姓名,科目,分数 From 考试 Where 分数 Not In (Select 分数 From 考试 Where 分数=60)]，使用IN找出分数不大于或等于60分的记录，即小于60分的记录：
%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver=
{Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "Select 姓名,科目,分数 From 考试 Where 分数 Not In (Select 分
数 From 考试 Where 分数=60)"
rs2.Open SqlStr,conn1,1,1
Response.Write "pNot In: 高于60分不算"
Do while not rs2.EOF
Response.Write "BR" & rs2("姓名") & " " & rs2("科目") & " 分数: " &
rs2("分数")
rs2.MoveNext
Loop
rs2.Close
%
以上的 ASP程式rs24.asp，在用户端使用浏览器，浏览执行的结果，显示分数不大于或等于60分的记录
*****************************************************
在ADO使用Select语法三
接上一篇文章：
orDER BY
orDER BY子句，可以设定排序的字段。
在一个SQL表达式之中，ORDER BY子句通常放置于最后。
若要以递减顺序来进行排序（Z~A，9 ~0），须于您要递减排序的每一字段的尾部加DESC字。譬如：
Select 姓名
FROM 员工
orDER BY 工资 DESC, 年龄
表示[工资] 字段以递减顺序来进行排序，[年龄] 字段以递增顺序来进行排序。
orDER BY子句中排序的字段，不可以包含MEMO字段类型或OLE对象类型，否则会发生错误。
在ORDER BY子句之中包含多个的字段时，首先以ORDER BY之后的第一个字段来进行排序。然后，若在此字段之中有相同的资料记录，才会以列第二字段来进行排序，依此类推。
GROUP BY
GROUP BY将查询的结果做统计，语法如下：
Select fieldlist
FROM table
Where criteria
[GROUP BY groupfieldlist]
使用Where子句，可以设定不想要做统计的资料，而使用HAVING子句可以筛选已经统计之后的字段。
不可以对MEMO字段类型或OLE对象类型的字段做统计，否则会发生错误。
譬如ASP程式rs22.asp如下，[Select 种类, Avg(价格) As 平均 From 产品 Group By 种类] 使用GROUP BY将查询的结果做 [种类] 统计，统计各个种类统计来平均价格Avg(价格)：
%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver=
{Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "Select 种类, Avg(价格) As 平均 From 产品 Group By 种类"
Response.Write "pAvg(价格)"
rs2.Open SqlStr,conn1,1,1
Do while not rs2.EOF
Response.Write "BR" & rs2("种类") & ": " & rs2("平均")
rs2.MoveNext
Loop
rs2.Close %
以上的 ASP程式rs22.asp，用户端使用浏览器，浏览执行的结果，显示依据 [种类] 统计来统计平均价格。
让我们多看个例子，譬如ASP程式rs22.asp如下，[Select 种类, Sum(数量*价格) As 总计 From 产品 Group By 种类] 使用GROUP BY将查询的结果做 [种类] 统计，统计各个种类的数量与价格相乘的总价Sum(数量*价格)：
%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver=
{Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "Select 种类, Sum(数量*价格) As 总计 From 产品 Group By 种类"
rs2.Open SqlStr,conn1,1,1
Response.Write "pSum(数量*价格)"
Do while not rs2.EOF
Response.Write "BR" & rs2("种类") & ": " & rs2("总计")
rs2.MoveNext
Loop
rs2.Close %
以上的 ASP程式rs22.asp，在用户端使用浏览器，浏览执行的结果，显示依据 [种类] 来统计总价。
Groupfieldlist为用以统计的字段名称，最多为10个字段。
在groupfieldlist中字段名称的顺序，将决定统计层次，由最高至最低的层次来分组。
最后，譬如ASP程式rs22.asp如下，[Select 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目] 使用GROUP BY将查询的结果做依据 [姓名] 和 [科目] 统计，统计各个统计的 [平均] 分数Avg(分数)：
%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver=
{Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "Select 姓名,科目,Avg(分数) As 平均 From 考试 Group By 姓名,科目"
rs2.Open SqlStr,conn1,1,1
Response.Write "pGroup By 姓名,科目"
Do while not rs2.EOF
Response.Write "BR" & rs2("姓名") & " " & rs2("科目") & " 平均: " &
rs2("平均")
rs2.MoveNext
Loop
rs2.Close
%
以上的 ASP程式rs22.asp，在用户端使用浏览器，浏览执行的结果，显示依据 [姓名] 和 [科目] 来统计 [平均] 分数
2.6. 修改表
如果你建立了一个表后发现自己犯了一些错误，或者是应用的需求 发生了变化，那么你可以删除这个表然后重新建立它。但是如果这个 表已经填充了好多数据了，或者是该表已经被其它数据库对象参照， （比如一个外键约束） 那这可不是一个方便的选项。因此 PostgreSQL 提供了一族命令用于修改现有表。
你可以
增加字段，
删除字段，
增加约束，
删除约束，
修改默认值，
重命名字段，
重命名表。
所有这些动作都是用 Alter TABLE命令执行的。
2.6.1. 增加字段
要增加一个字段，使用这条命令：
Alter TABLE products ADD COLUMN description text;
新增的字段对于表中已经存在的行而言最初将先填充空值。
你也可以同时在该字段上定义约束，使用通常的语法：
Alter TABLE products ADD COLUMN description text CHECK (description <> '');
一个新字段不能用非空约束，因为最初的时候该字段必须包含空值。 但是你可以稍后增加一个非空约束。同样，你也不能在一个新字段 上定义默认值。根据 SQL 标准的说明，这样需要对现存行的新 字段填充默认值，而这个特性还没有实现。但是你可以稍后调整 字段预设。
2.6.2. 删除字段
要删除一个字段，使用这个命令：
Alter TABLE products Drop COLUMN description;
2.6.3. 增加约束
要增加一个约束，使用表约束语法。比如：
Alter TABLE products ADD CHECK (name <> '');
Alter TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
Alter TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
要增加一个不能写成表约束的非空约束，使用下面语法：
Alter TABLE products Alter COLUMN product_no SET NOT NULL;
这个约束将立即进行检查，所以表在加入约束之前必须符合约束条件。
2.6.4. 删除约束
要删除一个约束，你需要知道它的名字。如果你给了它一个名字， 那么事情就好办了。否则系统会分配一个生成的名字，这样你就需要 把它找出来了。psql 的命令 \d tablename 在这儿可以帮忙﹔ 其它接口可能也提供了检查表的细节的方法。然后就是这条命令：
Alter TABLE products Drop CONSTRAINT some_name;
除了非空约束外，所有约束类型都这么用。要删除非空类型，用
Alter TABLE products Alter COLUMN product_no Drop NOT NULL;
（要记得非空约束没有名字。）
2.6.5. 改变默认值
要给一个字段设定默认值，使用一个象下面这样的命令：
Alter TABLE products Alter COLUMN price SET DEFAULT 7.77;
要删除默认值，用
Alter TABLE products Alter COLUMN price Drop DEFAULT;
这样相当于把预设设定为空，至少在 PostgreSQL里是这样的。 结果是，如果我们删除一个还没有定义的默认值不算错误，因为预设隐含就是空值。
2.6.6. 给字段改名字
重命名一个字段：
Alter TABLE products RENAME COLUMN product_no TO product_number;
2.6.7. 给表改名字
To rename a table:
Alter TABLE products RENAME TO items;
（阅读次数：）
|
| 上一篇：精妙Sql语句 下一篇：SQL2000分页存储过程 |
|
|
[收藏]
[推荐]
[评论(0条)]
[返回顶部] [打印本页]
[关闭窗口] |
|
|
|
|
