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.,)
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.END,N),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N),
=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,
=ISNULL(D.definition,N),
ColumnDesc=ISNULL(PFD.,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.=O.
ANDO.type=U
ANDO.is_ms_shipped=0
INNERJOINsys.typesT
ONC.user_type_id=T.user_type_id
LEFTJOINsys.default_constraintsD
ONC.=D.parent_object_id
ANDC.column_id=D.parent_column_id
ANDC.default_object_id=D.
LEFTJOINsys.extended_propertiesPFD
ONPFD.class=1
ANDC.=PFD.major_id
ANDC.column_id=PFD.minor_id
--ANDPFD.name=Caption--字段申明对应的形貌称号(一个字段能够增加多个分歧name的形貌)
LEFTJOINsys.extended_propertiesPTB
ONPTB.class=1
ANDPTB.minor_id=0
ANDC.=PTB.major_id
--ANDPFD.name=Caption--表申明对应的形貌称号(一个表能够增加多个分歧name的形貌)
LEFTJOIN--索引及主键信息
(
SELECT
IDXC.,
IDXC.column_id,
Sort=CASEINDEXKEY_PROPERTY(IDXC.,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.=IDXC.
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.=KC.
ANDIDX.index_id=KC.unique_index_id
INNERJOIN--关于一个列包括多个索引的情形,只显现第1个索引信息
(
SELECT,Column_id,index_id=MIN(index_id)
FROMsys.index_columns
GROUPBY,Column_id
)IDXCUQ
ONIDXC.=IDXCUQ.
ANDIDXC.Column_id=IDXCUQ.Column_id
ANDIDXC.index_id=IDXCUQ.index_id
)IDX
ONC.=IDX.
ANDC.column_id=IDX.column_id
--WHEREO.name=N要查询的表--假如只查询指定表,加上此前提
ORDERBYO.name,C.column_id
--★★SQL2005索引及主键信息:★★★★★★★★★★★★★★★★★★
--========================================================================
--索引及主键信息
--邹建2005.08(援用请保存此信息)
--========================================================================
SELECT
TableId=O.,
TableName=O.Name,
IndexId=ISNULL(KC.,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.,IDXC.index_id,IDXC.index_column_id,IsDescending)
WHEN1THENDESCWHEN0THENASCELSEEND,
PrimaryKey=CASEWHENIDX.is_primary_key=1THENN√ELSENEND,
=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.=IDXC.
ANDIDX.index_id=IDXC.index_id
LEFTJOINsys.key_constraintsKC
ONIDX.=KC.
ANDIDX.index_id=KC.unique_index_id
INNERJOINsys.objectsO
ONO.=IDX.
INNERJOINsys.columnsC
ONO.=C.
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(存储过程);
页:
[1]