MYSQL网页编程之数据库中常常用到的操纵和办理数据库的...

与其他数据库相比，MySQL易学易用。<P>/*
--收拾者：永久de影

--收拾工夫：2010/06/08

--内容：SQL函数的先容：
*/

--★★SQL2000查询出各（某）表字段的属性:★★★★★★★★★★★★★★★★★★

SELECT
表名=casewhena.colorder=1thend.nameelseend,
表申明=casewhena.colorder=1thenisnull(f.value,)elseend,
字段序号=a.colorder,
字段名=a.name,
标识=casewhenCOLUMNPROPERTY(a.id,a.name,IsIdentity)=1then√elseend,
主键=casewhenexists(SELECT1FROMsysobjectswherextype=PKandparent_obj=a.idandnamein(
SELECTnameFROMsysindexesWHEREindidin(
SELECTindidFROMsysindexkeysWHEREid=a.idANDcolid=a.colid)))then√elseend,
范例=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,PRECISION),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,Scale),0),
同意空=casewhena.isnullable=1then√elseend,
默许值=isnull(e.text,),
字段申明=isnull(g.[value],)
FROM
syscolumnsa
leftjoin
systypesb
on
a.xusertype=b.xusertype
innerjoin
sysobjectsd
on
a.id=d.idandd.xtype=Uandd.namedtproperties
leftjoin
syscommentse
on
a.cdefault=e.id
leftjoin
syspropertiesg
on
a.id=g.idanda.colid=g.smallid
leftjoin
syspropertiesf
on
d.id=f.idandf.smallid=0
where
d.name=要查询的表--假如只查询指定表,加上此前提
orderby
a.id,a.colorder


--★★SQL2005查询出各（某）表字段的属性:★★★★★★★★★★★★★★★★★★
--========================================================================
--表布局信息查询
--邹建2005.08(援用请保存此信息)
--========================================================================
SELECT
TableName=CASEWHENC.column_id=1THENO.nameELSENEND,
TableDesc=ISNULL(CASEWHENC.column_id=1THENPTB.[value]END,N),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N),
[IDENTITY]=CASEWHENC.is_identity=1THENN√ELSENEND,
Computed=CASEWHENC.is_computed=1THENN√ELSENEND,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASEWHENC.is_nullable=1THENN√ELSENEND,
[Default]=ISNULL(D.definition,N),
ColumnDesc=ISNULL(PFD.[value],N),
IndexName=ISNULL(IDX.IndexName,N),
IndexSort=ISNULL(IDX.Sort,N),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROMsys.columnsC
INNERJOINsys.objectsO
ONC.[object_id]=O.[object_id]
ANDO.type=U
ANDO.is_ms_shipped=0
INNERJOINsys.typesT
ONC.user_type_id=T.user_type_id
LEFTJOINsys.default_constraintsD
ONC.[object_id]=D.parent_object_id
ANDC.column_id=D.parent_column_id
ANDC.default_object_id=D.[object_id]
LEFTJOINsys.extended_propertiesPFD
ONPFD.class=1
ANDC.[object_id]=PFD.major_id
ANDC.column_id=PFD.minor_id
--ANDPFD.name=Caption--字段申明对应的形貌称号(一个字段能够增加多个分歧name的形貌)
LEFTJOINsys.extended_propertiesPTB
ONPTB.class=1
ANDPTB.minor_id=0
ANDC.[object_id]=PTB.major_id
--ANDPFD.name=Caption--表申明对应的形貌称号(一个表能够增加多个分歧name的形貌)
LEFTJOIN--索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,IsDescending)
WHEN1THENDESCWHEN0THENASCELSEEND,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN√ELSENEND,
IndexName=IDX.Name
FROMsys.indexesIDX
INNERJOINsys.index_columnsIDXC
ONIDX.[object_id]=IDXC.[object_id]
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.[object_id]=KC.[parent_object_id]
ANDIDX.index_id=KC.unique_index_id
INNERJOIN--关于一个列包括多个索引的情形,只显现第1个索引信息
(
SELECT[object_id],Column_id,index_id=MIN(index_id)
FROMsys.index_columns
GROUPBY[object_id],Column_id
)IDXCUQ
ONIDXC.[object_id]=IDXCUQ.[object_id]
ANDIDXC.Column_id=IDXCUQ.Column_id
ANDIDXC.index_id=IDXCUQ.index_id
)IDX
ONC.[object_id]=IDX.[object_id]
ANDC.column_id=IDX.column_id
--WHEREO.name=N要查询的表--假如只查询指定表,加上此前提
ORDERBYO.name,C.column_id

--★★SQL2005索引及主键信息:★★★★★★★★★★★★★★★★★★
--========================================================================
--索引及主键信息
--邹建2005.08(援用请保存此信息)
--========================================================================
SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,Index),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
Sort=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,IsDescending)
WHEN1THENDESCWHEN0THENASCELSEEND,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN√ELSENEND,
[UQIQUE]=CASEWHENIDX.is_unique=1THENN√ELSENEND,
Ignore_dup_key=CASEWHENIDX.ignore_dup_key=1THENN√ELSENEND,
Disabled=CASEWHENIDX.is_disabled=1THENN√ELSENEND,
Fill_factor=IDX.fill_factor,
Padded=CASEWHENIDX.is_padded=1THENN√ELSENEND
FROMsys.indexesIDX
INNERJOINsys.index_columnsIDXC
ONIDX.[object_id]=IDXC.[object_id]
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.[object_id]=KC.[parent_object_id]
ANDIDX.index_id=KC.unique_index_id
INNERJOINsys.objectsO
ONO.[object_id]=IDX.[object_id]
INNERJOINsys.columnsC
ONO.[object_id]=C.[object_id]
ANDO.type=U
ANDO.is_ms_shipped=0
ANDIDXC.Column_id=C.Column_id


--★★SQLServer对年夜容量内存的撑持:★★★★★★★★★★★★★★★★★★

/*
32位操纵体系有个很年夜的缺点，使用程序没法会见年夜于4G的历程地点空间，由于32位的指针没法保留年夜于4G的地点空间
假如年夜于4G，则必要利用地点窗口化扩大插件(AWE)，详细操纵以下：
1，启植物理地点扩大
(1)找到C:oot.ini,并删除其只读属性.
(2)编纂boot.ini，在ARC路径中增加/PAE参数.比方：
在windowsServer2003EnterpriseEdition中，编纂后的ARC路径以下：
muti(0)disk(0)partition(1)windows="windowsServer2003Enterprise,Edition"/fastdetect/PAE
保留后将其恢复为只读形式，然后从头启动盘算机。

假如盘算机上的可用物理内存凌驾16G，应确保boot.ini文件中没有/3gb参数
*/

--★★怎样启动AWE选项:★★★★★★★★★★★★★★★★★★

sp_configureshowadvancedoptions,1
reconfigure
go
sp_configueaweenabled,1
reconfigure
go

--★★手动设置内存选项:★★★★★★★★★★★★★★★★★★

sp_configureshowadvancedoptions,1
go
reconfigure
go
sp_configureminservermemory--服务器最小内存
sp_configuremaxservermemory--服务器最年夜内存
sp_configureindexcreatememory--创立索引占用的内存
sp_configureminmemoryperquery--每次查询占用的最小内存


--★★猎取磁盘读写情形:★★★★★★★★★★★★★★★★★★

select
@@total_readas读取磁盘的次数,
@@total_writeas写进磁盘的次数,
@@total_erroras磁盘写进毛病数,
getdate()as以后工夫

--★★猎取数据库文件的I/O统计信息:★★★★★★★★★★★★★★★★★★

select*fromfn_virtualfilestats(null,null)

--★★猎取I/O事情情形:★★★★★★★★★★★★★★★★★★

select
@@id_busy，--SQL自前次启动以来的用于实行输出和输入操纵的工夫
@@timeticks,--每一个时钟周期对应的微秒数
@@id_busy*@@timeticksasI/O操纵毫秒数,
getdate()as以后工夫

--★★检察SQLSEVERCPU举动,事情情形:★★★★★★★★★★★★★★★★★★

select
@@cpu_busy,--自前次启动以来的事情工夫
@@timeticks,--每一个时钟周期对应的微秒数
@@cpu_busy*cast(@@timeticksasfloat)/1000ascpu事情工夫(秒),
@@idie*cast(@@timeticksasfloat)/1000asCPU余暇工夫(秒)
getdate()as以后工夫

--★★猎取收集数据包统计信息:★★★★★★★★★★★★★★★★★★

select
getdate()as以后工夫,
@@pack_receivedas输出数据包数目,
@@pack_sentas输入数据包数目,
@@packet_erroras毛病包数目


--★★检察服务器事情形态:★★★★★★★★★★★★★★★★★★

createfunctionfgetsstatus(
@servernamevarchar(50)--服务器名
,@useridvarchar(50)=sa--用户名,假如为nt考证体例,则为空
,@passwordvarchar(50)=--暗码

)returnsvarchar(20)
as
begin
declare@revarchar(20),@ireint--前往形态
declare@srvidint--界说服务器、数据库集id
declare@errint,@srcvarchar(255),@descvarchar(255)--毛病处置变量

--★★创立sqldmo工具:★★★★★★★★★★★★★★★★★★

exec@err=sp_oacreatesqldmo.sqlserver,@srvidoutput
if@err0gotolberr

--★★毗连服务器:★★★★★★★★★★★★★★★★★★

ifisnull(@userid,)=--假如是Nt考证体例
begin
exec@err=sp_oasetproperty@srvid,loginsecure,1
if@err0gotolberr

exec@err=sp_oamethod@srvid,connect,null,@servername
end
else
exec@err=sp_oamethod@srvid,connect,null,@servername,@userid,@password

if@err0gotolberr

--★★猎取服务器形态:★★★★★★★★★★★★★★★★★★

exec@err=sp_oagetproperty@srvid,Status,@ireoutput
if@err0gotolberr

set@re=case@irewhen0then未知
when1then运转...
when2then停息
when3then中断...
when4then正在启动...
when5then正在启动中断...
when6then毗连...
when7then正在停息...end
return(@re)
lberr:
execsp_oageterrorinfoNULL,@srcout,@descout
declare@errbvarbinary(4)
set@errb=cast(@errasvarbinary(4))
execmaster..xp_varbintohexstr@errb,@reout
set@re=毛病号:+@re
+char(13)+毛病源:+@src
+char(13)+毛病形貌:+@desc
return(@re)
end
go

selectdbo.fgetsstatus(192.168.102.208,sa,sa)

--------------------
--运转...

--★★猎取服务器形态:★★★★★★★★★★★★★★★★★★

ifobject_id(tb)isnotnulldroptabletb
go
createtabletb(
表名sysname,
纪录数int,
保存空间nvarchar(10),
利用空间varchar(10),
索引利用空间varchar(10),
未用空间varchar(10))
execsp_MSForEachTable@command1=Ninserttbexecsp_spaceused?

select*fromtb

--★★检察服务器版本:★★★★★★★★★★★★★★★★★★

SELECT
SERVERPROPERTY(productversion),
SERVERPROPERTY(productlevel),
SERVERPROPERTY(edition)

--★★检察数据库脱机工夫:★★★★★★★★★★★★★★★★★★

EXECsp_configureshowadvancedoptions,1
RECONFIGURE
go
EXECsp_configurexp_cmdshell,1
RECONFIGURE
GO
selecta.name,a.database_id,a.create_date,b.physical_nameinto#a
fromsys.databasesaleftjoinsys.master_filesbon
a.database_id=b.database_idwherehas_dbaccess(a.name)1andb.type=1

createtable#b(infovarchar(500))
declare@stringvarchar(max)
set@string=
select@string=@string+insertinto#bexecxp_cmdshelldir+physical_name++char(13)+char(10)from#a
execute(@string)

selecta.name,substring(b.info,0,20)as脱机工夫,a.database_id,a.create_date,a.physical_name
from#aaleftjoin#bbon
REVERSE(substring(REVERSE(physical_name),0,charindex(,REVERSE(physical_name))))
=REVERSE(substring(REVERSE(info),0,charindex(,REVERSE(info))))

droptable#a,#b
go
EXECsp_configurexp_cmdshell,0
RECONFIGURE
go
EXECsp_configureshowadvancedoptions,0
RECONFIGURE
go

/*

1.检察数据库的版本

select@@version

2.检察数据库地点呆板操纵体系参数.

execmaster..xp_msver..

3.检察数据库启动的参数!

sp_configure
4.检察数据库启动工夫.

selectconvert(varchar(30),login_time,120)frommaster..sysprocesseswherespid=1

检察数据库服务器名和实例名.

printServerName...............:+convert(varchar(30),@@SERVERNAME).

printInstance..................:+convert(varchar(30),@@SERVICENAME)...

5.检察一切数据库称号及巨细

sp_helpdb。

重定名数据库用的SQL

sp_renamedbold_dbname,new_dbname

6.检察一切数据库用户登录信息..

sp_helplogins

检察一切数据库用户所属的脚色信息

sp_helpsrvrolemember!

修复迁徙服务器时伶仃用户时,能够用的fix_orphan_user剧本大概LoneUser历程.

变动某个数据工具的用户属主

sp_changeobjectowner[@objectname=]object,[@newowner=]owner.

注重:变动工具名的任一部分都大概损坏剧本和存储历程。

把一台服务器上的数据库用户登录信息备份出来能够用add_login_to_aserver剧本

7.检察链接服务器...

sp_helplinkedsrvlogin

检察远端数据库用户登录信息。

sp_helpremotelogin..

8.检察某数据库下某个数据工具的巨细!

sp_spaceused@objname

还能够用sp_toptables历程看最年夜的N(默许为50)

*/

--★★检察功课实行情形:★★★★★★★★★★★★★★★★★★

selectcategory=jc.name,
category_id=jc.category_id,
job_name=j.name,
job_enabled=j.enabled,
last_run_time=cast(js.last_run_dateasvarchar(10))+-+cast(js.last_run_timeasvarchar(10)),
last_run_duration=js.last_run_duration,
last_run_status=js.last_run_outcome,
last_run_msg=js.last_outcome_message+cast(nullif(js.last_run_outcome,1)asvarchar(2)),
job_created=j.date_created,
job_modified=j.date_modified
frommsdb.dbo.sysjobsj
innerjoinmsdb.dbo.sysjobserversjs
onj.job_id=js.job_id
innerjoinmsdb.dbo.syscategoriesjc
onj.category_id=jc.category_id
wherej.enabled=1
andjs.last_run_outcomein(0,1,3,5)--0:Fail1:Succ3:Cancel5:Firstrun
andjc.category_idnotbetween10and20--repl

--★★查询数据库db中表tb的一切索引的随片情形:★★★★★★★★★★★★★★★★★★

usedb
go
select
a.index_id,---索引编号
b.name,---索引称号
avg_fragmentation_in_percent---索引的逻辑碎片
from
sys.dm_db_indx_physical_stats(db_id(),object_id(Ncreate.consume),null,null,null)asa
join
sys.indexesasb
on
a.object_id=b.object_id
and
a.index_id=b.index_id
go

--★★用户成员权限:★★★★★★★★★★★★★★★★★★

USEpubs

--创立脚色r_test
EXECsp_addroler_test

--授与r_test对jobs表的一切权限
GRANTALLONjobsTOr_test
--授与脚色r_test对titles表的SELECT权限
GRANTSELECTONtitlesTOr_test

--增加登录l_test,设置暗码为pwd,默许数据库为pubs
EXECsp_addloginl_test,pwd,pubs

--为登录l_test在数据库pubs中增加平安账户u_test
EXECsp_grantdbaccessl_test,u_test

--增加u_test为脚色r_test的成员
EXECsp_addrolememberr_test,u_test

--回绝平安账户u_test对titles表的SELECT权限
DENYSELECTONtitlesTOu_test

/*--完成上述步骤后,用l_test登录,能够对jobs表举行一切操纵,但没法对titles表查询,固然脚色r_test有titles表的select权限,但已在平安账户中明白回绝了对titles的select权限,以是l_test无titles表的select权限--*/

--从数据库pubs中删除平安账户
EXECsp_revokedbaccessu_test

--删除登录l_test
EXECsp_droploginl_test

--删除脚色r_test
EXECsp_droproler_test

由于MySQL数据库已经如此普及，对企业来说它无疑是一个更好的选择。

having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

也可谈一下你是怎么优化存储过程的?

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);