SQLServer令总结.docx

SQLServer令总结.docx

《SQLServer令总结.docx》由会员分享，可在线阅读，更多相关《SQLServer令总结.docx（14页珍藏版）》请在冰豆网上搜索。

SQLServer令总结

SQLSERVER命令总结

查询语句:

SELECT[ALL/DISTINCT][TOP]

<目标列>[as别名][,<目标列1>as别名]…

FROM<表名或视图名>[as别名]…

[WHERE条件表达式…]

[GROUPBY<列名1>[HAVING<条件表达式>]]

[ORDERBY<列名2>[ASC/DESC]]

应该注意:

SELECT语句的顺序:

SELECT-->FROM-->WHERE-->GROUPBY-HAVINGORDERBY

SELECTINTO:

生成新表格

DISTINCT:

唯一

GROUPBY:

分组汇总

ORDERBY:

排序,默认情况下为升序.

ASC:

升序

DESC:

降序

AS:

起别名

HAVING:

筛选分组汇总后的行

SELECTTOPnFROM<表名>:

表示查询前N行

SELECTTOPnPERCENT*FROM<表名>:

表示按一定的百分比提取

LIKE:

模糊查询,仅于CHAR或是VERCHAR以及通配符连用

其他模糊查询:

IN:

用于返回给定的值与列表中的值相匹配的行

BETWEEN…AND…:

在...之间查询

ISNOTNULL:

查询不为空的数据

查询中使用的常量:

一般与“+”连用.起到一个组合的目的

注意

1.SELECT后面的字段如果不包括聚合函数,必须参加分组（GROUPBY

2.GROUPBY后面不能使用聚合函数

3.查询时,可以使用2个聚合函数

使用INSERT插入数据

✓添加完整记录:

INSERT[INTO]<目标表名>VALUES

VALUES:

字段值列表（顺序与建表的顺序必须一样）

✓添加不完整记录:

INSERT[INTO]<目标表名>（字段名列表）VALUES<字段值列表>

特别的记住:

字段名列表和字段值列表是一一对应的.即后面的字段值随着前面的字段名的改变而改变.

另一种方法:

语法:

INSERT<表名>SELECTFROM<表名2）

还可以将数据有选择性的添加到另一个表中

语法:

INSERTINTOSales（Stor_id）SELECTstor_idFROMStores

UPDATE语句

更新一行:

UPDATE<新表名>SET<目标列=值>[WHERE<条件>]（范围）

更新多行:

UPDATE<表名>SET<目标列=值>;{<---没有WHERE}

更新一行和更新多行的区别在于有没有WHERE的存在

DELETE语句

1:

删除一行:

语法:

DELETEFROM<表名>[WHERE<条件>]

2:

删除多行:

语法:

DELETEFROM<表名>

删除一行和删除多行区别在于有没有WHERE的存在

3:

TRUNCATETABLE

用于删除表中所有行的命令.DROP删除表

与DELETE的区别在于:

TRUNCATE不写日值文件,无法恢复数据

JOIN语句联接信息:

Table_aAStable_alias_aJOINtable_bAStable_alias_b

On

table_alias_a.=table_alias_b.

--------------------公共字段---------------------------

A:

内联接

INNERJOIN:

查询两个表中的公共部分

B:

外联接

左外联接:

leftOUTERJOIN

右外联接:

rightOUTERJOIN

完整联接:

FULLOUTERJOIN

C:

自联接

创建表的结构

1.名称：

表名字段名2.数据类型：

系统定义的

用户定义的:

execsp_addtype用户定义的数据类型名,数据类型（系统有的）,notnull（是否允许为空）

3.大小:

4.约束:

主键约束PRIMARYKEYcreatetable表名（字段数据类型primarykey/

primarykey（字段…））

唯一约束UNIQUEcreatetable表名（字段数据类型unique）

默认约束DEFAULTcreatetable表名（字段数据类型default‘值/表达式’）

检查约束CHECKcreatetable表名（字段数据类型check（字段的表达式）

外键约束FOREIGNKEYcreatetable表名（字段数据类型foreignkeyreferences（应用）另一个表名（字段））

更新表的结构

1.修改数据类型：

altertable表名altercolumn列名数据类型

2.添加一列：

altertable表名add列名数据类型

3.删除一列：

altertable表名dropcolumn列名

4.增加约束：

altertable表名addconstraint约束名约束表达式

主键约束：

primarykey（列名列表）

唯一约束：

unique（列名）

默认约束：

default‘值’for列名

检查约束：

check（列的检查约束表达式）

外键约束：

foreignkey（列名）references表名（列名）

删除表的结构

droptable表名

子查询

Select字段from表（select查询）

子查询就是内层查询产生一个用于外层查询的条件

子查询经常使用的是条件运算符（>,<,<=,>=,=,!

=）

子查询与in联合使用经常用于判断一列数据

子查询与exists联合使用经常用于判断一张表的字段

触发器

1。

触发器:

是一种特殊存储过程,它能对有触发器的表中的数据进行保护.

2。

触发器主要通过操作事件（insert,update,delete）进行触发而被自动执行，

不能被调用，也不能传递参数

3。

a）触发器根据数据修改语句可分为：

insert触发器，update触发器，delete触发器

b）根据引起触发时刻可分为after触发器，instead触发器。

i.after触发器是在执行数据操作之后激发

ii.insteadof触发器是在执行数据操作之前激发

iii.一个表可以建立多个after触发器，可以建立一个insteadof触发器

触发器中的inserted表和deleted表

1。

触发器运行后在内存中自动创建。

2。

他们用于对触发器执行后某些数据的测试条件

3。

用户不能直接操作2个表

4。

delete触发器将删除的内容放在deleted表中。

insert触发器将添加的内容存在inserted表中。

update触发器将替换前的内容放在deleted表中，将替换后的新内容放在inserted表中。

创建触发器：

CREATETRIGGER触发器名

ON表名/视图名

FOR触发器类型

AS

……T-SQL语句

例如：

/*如果被删除的学生有考试成绩的话，就不允许删除这个学生*/

createtriggert_stu

ontbl_stu

fordelete

as

begin

declare@idint

select@id=stuidfromdeleted

ifexists（select*fromtbl_cjwherestuid=@id）

begin

print'学生有成绩,不可删除!

!

'

rollbacktransaction

end

end

deletetbl_stuwherestuid=1007—当删除时，看消息框提示

删除触发器：

droptrigger触发器名

例如：

droptriggert_stu

修改触发器：

将创建触发器的create关键字更换成alter

存储过程

存储过程：

是存储在sqlserver服务器中的一组预编译过的t-sql语句，当第一次调用后，就驻留在内存中，以后调用时不必再进行编译。

优点在于一次编写，多次调用。

调用过程在服务器端执行，执行速度快。

存储过程分为：

系统存储过程，本地存储过程。

存储过程语法：

createprocedure存储过程名@参数参数类型

as

任意数量的T-SQL语句

例如：

createprocedurestu_name@stunamechar（10）

as

select*fromstu

调用存储过程exec存储过程名　参数

例如：

execstu_name'aa'

事务

--显示事务

Createtableaaa

（

Aidint,

Anamechar（10）,

Asexchar

（2）check（asexin（‘m’,’n’））

）

Setxact_aborton

Begintransaction

Intsertintoaaavalues（101,’aa’,’n’）

Intsertintoaaavalues（102,’bb’,’m’）

Intsertintoaaavalues（103,’cc’,’n’）

Intsertintoaaavalues（104,’dd’,’a’）

Intsertintoaaavalues（105,’ee’,’m’）

If@@error=0

Commit

Else

Rollback

Go

Select*fromaaa

Droptableaaa

--隐性事务

Setxact_aborton

Setimplicit_transactionson

Createtableaaa

（

Aidint,

Anamechar（10）,

Asexchar

（2）check（asexin（‘m’,’n’））

）

Intsertintoaaavalues（101,’aa’,’n’）

Intsertintoaaavalues（102,’bb’,’m’）

Intsertintoaaavalues（103,’cc’,’n’）

Intsertintoaaavalues（104,’dd’,’a’）

Intsertintoaaavalues（105,’ee’,’m’）

Commit

--默认提交事务

Createtableaaa

（

Aidint,

Anamechar（10）,

Asexchar

（2）check（asexin（‘m’,’n’））

）

Intsertintoaaavalues（101,’aa’,’n’）

Intsertintoaaavalues（102,’bb’,’m’）

Intsertintoaaavalues（103,’cc’,’n’）

Intsertintoaaavalues（104,’dd’,’a’）

Intsertintoaaavalues（105,’ee’,’m’）

/*-------------------------------------------------------------------

Commit:

提交事务中的一切操作，使得事务对数据库的修改有效

Rollback：

回滚事务中的一切操作，使得事务对数据库的操作无效

------------------------------------------------------------------

事务控制语句

1．设置隐性事务模式

1）setimplicit_transactionson启动隐性事务模式

2）setimplicit_transactionsoff关闭隐性事务模式

2．设置自动回滚模式

1）setxact_aborton当事务中任意一条语句产生运行时错误，整个事物将中止，并整体回滚。

2）setxact_abortoff当事务中语句产生运行时错误，将中止本条语句并且回滚本条语句。

注意：

setxact_abort的设置是在执行或运行时设置，而不是在分析时设置。

--------------------------------------------------------------

首次执行下列语句都会自动启动一个隐性事务：

Altertable,create,delete,drop,fetch（读取）,grant（授予）,insert,open,revoke（撤销）,select,truncatetable,update

事务中不可以使用的语句：

1.数据库创建:

createdatabase

2.数据库修改:

alterdatabase

3.数据库删除:

dropdatabase

4.数据库备份:

dumpdatabase,backupdatabase

5.数据库还原:

loaddatabase,restoredatabase

6.事务日志备份:

dumptransaction,backuplog

7.事务日志还原:

loadtranscation,restorelog

8.配置:

reconfigure

9.磁盘初始化:

diskinit

10.更新统计数据:

updatastatistics

11.显示或设置数据库选项:

sp_dboptin

游标

游标：

允许用户能够从select语句查询的结果集中，逐条访问纪录，可以根据用户需要逐行的显示、修改、删除这些记录的数据访问处理机制。

（游标就是数据表上的指针）

使用游标的步骤：

（1）声明游标

declare游标名cursorforselect语句

（2）打开游标

open游标名

1）当游标被打开时，指针会自动指在第一行；

2）打开游标后，如果@@error＝０表示游标打开操作成功；

3）打开游标后，可以用@@cursor_rows返回纪录行数；

（3）处理数据：

a）移动到当前行并读取数据

fetch游标名

b）删除当前行数据

deletefrom表名／视图名wherecurrentof游标名

c）修改当前行数据

updatefrom表名／视图名set列名＝表达式wherecurrentof游标名

（4）关闭游标

close游标名

（5）释放游标

deallocate　游标名

游标类型：

（１）static：

静态，当用户A察看表中记录的同时，如果有用户B修改表中记录，A用户将不会看到B修改的纪录，只看A使用open打开表时所看到的纪录。

（２）Dynamic：

动态，当用户A察看表中记录的同时，如果有用户B修改表中记录，A用户将看到B修改的纪录，也就是表中记录会不断的刷新，但是会耗费较大的系统资源。

（３）ForwardOnly：

仅向前，游标仅能向前滚动。

（４）Scroll：

滚动，游标可以前后左右滚动。

select*fromstudent

/*打开游标，读取数据*/

declarec_stucursorkeysetforselect*fromstudent

openc_stu

if@@error=0

print'学生总数'+convert（char（5）,@@cursor_rows）

else

print'读取学生数出错！

'

closec_stu

deallocatec_stu

---------------------------------------------------------------------------------------------------------

--keyset:

键集游标,当游标打开时，在tempdb数据库中自动创建keyset表，用来记录游标读取的数据（仅纪录表示字段）

---------------------------------------------------------------------------------------------------------

/*使用游标处理数据*/

--使用游标，在表中逐行读取

select*fromstudent

declarec_stucursorforselect*fromstudent

openc_stu

fetchnextfromc_stu

while@@fetch_status=0

fetchnextfromc_stu

closec_stu

deallcoatec_stu

-------------------------------------------------------------------------------------------------------

--fetch的使用

--1.first：

移动到第一行，并作为当前行

--2.next:

移动到下一行，并作为当前行

--3.prior:

移动到上一行，并作为当前行

--4.last:

移动到最后一行，并作为当前行

--5.absoluten:

n>0时，从第一行开始，移动n行，作为当前行，n<0时，从最后一行倒数n行，作为当前行。

--6.relativen:

n>0时，从当前行正数移动n行，作为当前行，n<0时，从当前行倒数n行，作为当前行。

--7.打开游标后第一次执行fetchnext可以得到表中第一条数据，执行fetchprior得不到任何数据

--8.可用@@fetch_status返回执行fetch操作后游标的状态：

----0表示成功读取。

-----1表示读取操作超出结果集。

-----2表示行在表中不存在。

-------------------------------------------------------------------------------------------------------

--修改数据，将数据表中的第2行数据的姓名改成“张三”

select*fromstudent

declarec_stucursorforselect*fromstudent

openc_stu

fetchc_stu

fetchc_stu

updatestudentsetsname='张三'wherecurrentofc_stu

closec_stu

deallocatec_stu

------------------------------------------------

--updatestudentsetsname='张三'wherecurrentofc_stu

--如果去掉wherecurrentofc_stu，不进行指定，将更新表中所有字段

------------------------------------------------

--删除数据，将表中第2行数据删除

select*fromstudent

declarec_stucursorforselect*fromstudent

openc_stu

fetchc_stu

fetchc_stu

deletefromstudentwherecurrentofc_stu

closec_stu

deallocatec_stu

函数

/*--------------------------------------

自定义函数的种类：

√1.标量函数

√2.内嵌表函数

3.多语句表值函数

---------------------

标量函数基本语法

createfunction函数名

returns返回的参数类型as

begin

函数体

return函数返回的标量值

end

--------------------------

内嵌表函数语法

createfunction函数名

returnstableas

return（select查询语句）

-------------------------------------*/

/*----------------------------------------

注意：

函数是可以带参数的，但是参数不可以是：

1.时间戳（timestamp）

2.游标（cursor）

3.表（table）

----------------------------------------*/

/*------------------------------------------------------------------------

标量函数

createfunctionchinacode（@strvarchar（255））

returnschar

（2）as

begin

declare@iint,@jint

set@i=len（@str）

set@j=1

while（@j<=@i）

begin

if（unicode（substring（@str,@j,1））<256）

return'否'

set@j=@j+1

end

return'是'

end

selectdbo.chinacode（'我是中国人'）

selectdbo.chinacode（'我,是中国人'）

selectdbo.chinacode（'ilovechina'）

-------------------------------------------------------

substring（expression,start,length）字符串截取函数

expression:

字符串

start:

是一个整数，表示截取的位置

length:

是一个整数，表示一次截取的长度

-------------------------------------------------------

-------------------------------------------------------------------------*/

/*------------------------------------------------------------------------

内嵌表函数

-------------------------------------------------------------------------

1.内嵌表

createfunctionage（@maxageint,@minageint）

returnstableas

return（select*fromtbl_st