SQLServer令总结.docx

1、SQLServer令总结SQL SERVER命令总结查询语句:SELECT ALL/DISTINCTTOP as 别名, as 别名FROM as 别名WHERE 条件表达式GROUP BY HAVINGORDER BYASC/DESC 应该注意:SELECT语句的顺序:SELECT-FROM-WHERE-GROUP BY- HAVING ORDER BYSELECT INTO:生成新表格DISTINCT:唯一GROUP BY:分组汇总ORDER BY:排序,默认情况下为升序.ASC:升序DESC:降序AS:起别名HAVING:筛选分组汇总后的行SELECT TOP nFROM:表示查询前N行

2、SELECT TOP n PERCENT*FROM:表示按一定的百分比提取LIKE:模糊查询,仅于CHAR或是VERCHAR以及通配符连用其他模糊查询:IN:用于返回给定的值与列表中的值相匹配的行BETWEENAND: 在.之间查询IS NOT NULL:查询不为空的数据查询中使用的常量:一般与 “+”连用.起到一个组合的目的注意1. SELECT后面的字段如果不包括聚合函数,必须参加分组(GROUP BY2. GROUP BY后面不能使用聚合函数3. 查询时,可以使用2个聚合函数使用INSERT插入数据 添加完整记录: INSERT INTOVALUESVALUES:字段值列表(顺序与建表的

3、顺序必须一样) 添加不完整记录:INSERT INTO(字段名列表)VALUES特别的记住:字段名列表和字段值列表是一一对应的.即后面的字段值随着前面的字段名的改变而改变.另一种方法:语法:INSERTSELECTFROM表名2)还可以将数据有选择性的添加到另一个表中语法:INSERT INTO Sales(Stor_id)SELECT stor_id FROM StoresUPDATE语句更新一行:UPDATESETWHERE(范围)更新多行:UPDATESET;-没有WHERE更新一行和更新多行的区别在于有没有WHERE的存在DELETE语句1:删除一行:语法:DELETE FROMWHE

4、RE2:删除多行:语法:DELETE FROM删除一行和删除多行区别在于有没有WHERE的存在3: TRUNCATE TABLE用于删除表中所有行的命令.DROP删除表与DELETE的区别在于:TRUNCATE不写日值文件,无法恢复数据JOIN语句 联接信息:Table_a AS table_alias_a JOIN table_b AS table_alias_bOntable_alias_a .=table_alias_b.-公共字段-A:内联接INNER JOIN:查询两个表中的公共部分B:外联接左外联接:left OUTER JOIN右外联接:right OUTER JOIN完整联接:

5、FULL OUTER JOINC:自联接创建表的结构1.名称：表名 字段名 2.数据类型： 系统定义的 用户定义的: exec sp_addtype 用户定义的数据类型名 , 数据类型(系统有的),not null(是否允许为空)3.大小: 4.约束: 主键约束 PRIMARY KEY create table 表名(字段 数据类型 primary key / primary key(字段) 唯一约束 UNIQUE create table 表名(字段 数据类型 unique)默认约束DEFAULT create table 表名(字段 数据类型 default 值/表达式)检查约束 CHEC

6、K create table 表名(字段 数据类型 check (字段的表达式)外键约束 FOREIGN KEY create table表名(字段 数据类型 foreign key references(应用) 另一个表名(字段)更新表的结构1.修改数据类型：alter table 表名 alter column 列名 数据类型2.添加一列： alter table 表名 add 列名 数据类型3.删除一列： alter table 表名 drop column 列名4.增加约束： alter table 表名 add constraint 约束名 约束表达式 主键约束： primary k

7、ey（列名列表） 唯一约束： unique（列名） 默认约束： default 值for 列名 检查约束： check （列的检查约束表达式） 外键约束： foreign key（列名）references 表名（列名）删除表的结构drop table 表名子查询Select 字段 from 表（select查询）子查询就是内层查询产生一个用于外层查询的条件子查询经常使用的是条件运算符（,=,=,!=）子查询与in联合使用经常用于判断一列数据子查询与exists联合使用经常用于判断一张表的字段触发器1。触发器:是一种特殊存储过程,它能对有触发器的表中的数据进行保护.2。触发器主要通过操作事件(

8、insert,update,delete)进行触发而被自动执行， 不能被调用，也不能传递参数3。a)触发器根据数据修改语句可分为：insert触发器，update触发器，delete触发器 b)根据引起触发时刻可分为after触发器，instead触发器。 i.after触发器是在执行数据操作之后激发 ii.instead of 触发器是在执行数据操作之前激发iii.一个表可以建立多个after触发器，可以建立一个instead of触发器触发器中的inserted表和deleted表1。触发器运行后在内存中自动创建。2。他们用于对触发器执行后某些数据的测试条件3。用户不能直接操作2个表4。d

9、elete触发器将删除的内容放在deleted表中。 insert触发器将添加的内容存在inserted表中。 update触发器将替换前的内容放在deleted表中，将替换后的新内容放在inserted表中。创建触发器：CREATE TRIGGER 触发器名 ON 表名/视图名FOR 触发器类型AST-SQL语句例如：/*如果被删除的学生有考试成绩的话，就不允许删除这个学生*/create trigger t_stuon tbl_stufor deleteas begin declare id intselect id = stuid from deletedif exists(select

10、 * from tbl_cj where stuid = id) begin print 学生有成绩,不可删除! rollback transaction endenddelete tbl_stu where stuid = 1007当删除时，看消息框提示删除触发器：drop trigger 触发器名例如：drop trigger t_stu修改触发器：将创建触发器的create关键字更换成alter存储过程存储过程：是存储在sqlserver服务器中的一组预编译过的t-sql语句，当第一次调用后，就驻留在内存中，以后调用时不必再进行编译。优点在于一次编写，多次调用。调用过程在服务器端执行，执

11、行速度快。存储过程分为：系统存储过程，本地存储过程。存储过程语法：create procedure 存储过程名 参数 参数类型as 任意数量的T-SQL语句例如：create procedure stu_name stuname char(10) as select * from stu调用存储过程 exec 存储过程名参数例如：exec stu_name aa事务-显示事务Create table aaa( Aid int , Aname char(10), Asex char(2) check(asex in (m,n)Set xact_abort onBegin transactionI

12、ntsert into aaa values(101,aa,n)Intsert into aaa values(102,bb,m)Intsert into aaa values(103,cc,n)Intsert into aaa values(104,dd,a)Intsert into aaa values(105,ee,m)If error = 0 CommitElse RollbackGoSelect * from aaaDrop table aaa-隐性事务Set xact_abort onSet implicit_transactions onCreate table aaa( Aid

13、 int , Aname char(10), Asex char(2) check(asex in (m,n)Intsert into aaa values(101,aa,n)Intsert into aaa values(102,bb,m)Intsert into aaa values(103,cc,n)Intsert into aaa values(104,dd,a)Intsert into aaa values(105,ee,m)Commit-默认提交事务Create table aaa( Aid int, Aname char (10), Asex char (2) check (as

14、ex in (m,n)Intsert into aaa values(101,aa,n)Intsert into aaa values(102,bb,m)Intsert into aaa values(103,cc,n)Intsert into aaa values(104,dd,a)Intsert into aaa values(105,ee,m)/*-Commit :提交事务中的一切操作，使得事务对数据库的修改有效Rollback：回滚事务中的一切操作，使得事务对数据库的操作无效- -事务控制语句1 设置隐性事务模式1） set implicit_transactions on启动隐性事务

15、模式2） set implicit_transactions off 关闭隐性事务模式2 设置自动回滚模式1） set xact_abort on 当事务中任意一条语句产生运行时错误，整个事物将中止，并整体回滚。2） set xact_abort off 当事务中语句产生运行时错误，将中止本条语句并且回滚本条语句。注意：set xact_abort 的设置是在执行或运行时设置，而不是在分析时设置。-首次执行下列语句都会自动启动一个隐性事务：Alter table,create,delete,drop,fetch(读取),grant（授予）,insert,open,revoke（撤销）,sele

16、ct,truncate table,update事务中不可以使用的语句：1. 数据库创建:create database2. 数据库修改:alter database3. 数据库删除:drop database4. 数据库备份:dump database,backup database5. 数据库还原:load database,restore database6. 事务日志备份:dump transaction,backup log7. 事务日志还原:load transcation,restore log8. 配置:reconfigure9. 磁盘初始化:disk init10. 更新统计

17、数据:updata statistics11. 显示或设置数据库选项:sp_dboptin游标游标：允许用户能够从select语句查询的结果集中，逐条访问纪录，可以根据用户需要逐行的显示、修改、删除这些记录的数据访问处理机制。（游标就是数据表上的指针）使用游标的步骤：（1） 声明游标declare 游标名 cursor for select 语句（2） 打开游标open 游标名1） 当游标被打开时，指针会自动指在第一行；2） 打开游标后，如果error表示游标打开操作成功；3） 打开游标后，可以用cursor_rows返回纪录行数；（3） 处理数据：a) 移动到当前行并读取数据fetch游标名

18、b) 删除当前行数据delete from 表名视图名 where current of游标名c) 修改当前行数据update from表名视图名 set 列名表达式 where current of 游标名（4） 关闭游标close 游标名（5） 释放游标deallocate游标名游标类型：（）static：静态，当用户A察看表中记录的同时，如果有用户B修改表中记录，A用户将不会看到B修改的纪录，只看A使用open打开表时所看到的纪录。（）Dynamic：动态，当用户A察看表中记录的同时，如果有用户B修改表中记录，A用户将看到B修改的纪录，也就是表中记录会不断的刷新，但是会耗费较大的系统资源

19、。（）Forward Only：仅向前，游标仅能向前滚动。（）Scroll：滚动，游标可以前后左右滚动。select * from student/*打开游标，读取数据*/declare c_stu cursor keyset for select * from studentopen c_stuif error = 0 print 学生总数 + convert(char(5),cursor_rows)else print 读取学生数出错！close c_studeallocate c_stu-keyset:键集游标,当游标打开时，在tempdb数据库中自动创建keyset表，用来记录游标读取

20、的数据（仅纪录表示字段）-/*使用游标处理数据*/-使用游标，在表中逐行读取select * from studentdeclare c_stu cursor for select * from studentopen c_stufetch next from c_stuwhile fetch_status = 0 fetch next from c_stuclose c_studeallcoate c_stu-fetch的使用-1.first：移动到第一行，并作为当前行-2.next:移动到下一行，并作为当前行-3.prior:移动到上一行，并作为当前行-4.last:移动到最后一行，并作为

21、当前行-5.absolute n:n0时，从第一行开始，移动n行，作为当前行，n0时，从当前行正数移动n行，作为当前行，n0时，从当前行倒数n行，作为当前行。-7.打开游标后第一次执行fetch next可以得到表中第一条数据，执行fetch prior得不到任何数据-8.可用fetch_status返回执行fetch操作后游标的状态：- 0表示成功读取。- -1表示读取操作超出结果集。- -2表示行在表中不存在。-修改数据，将数据表中的第2行数据的姓名改成“张三”select * from studentdeclare c_stu cursor for select * from stude

22、ntopen c_stufetch c_stufetch c_stuupdate student set sname = 张三 where current of c_stuclose c_studeallocate c_stu-update student set sname = 张三 where current of c_stu-如果去掉where current of c_stu，不进行指定，将更新表中所有字段-删除数据，将表中第2行数据删除select * from studentdeclare c_stu cursor for select * from studentopen c_s

23、tufetch c_stufetch c_studelete from student where current of c_stuclose c_studeallocate c_stu函数/*-自定义函数的种类： 1.标量函数 2.内嵌表函数 3.多语句表值函数-标量函数基本语法create function 函数名returns 返回的参数类型 as begin 函数体 return 函数返回的标量值end-内嵌表函数语法create function 函数名returns table as return (select查询语句)-*/*-注意：函数是可以带参数的，但是参数不可以是： 1.

24、时间戳(timestamp) 2.游标(cursor) 3.表(table)-*/*-标量函数create function chinacode(str varchar(255)returns char(2) asbegin declare i int,j int 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 是endselect dbo.chinacode(我是中国人)select dbo.chinacode(我,是中国人)select dbo.chinacode(ilovechina)-substring(expression , start , length ) 字符串截取函数expression :字符串start :是一个整数，表示截取的位置length :是一个整数，表示一次截取的长度-*/*-内嵌表函数-1.内嵌表create function age(maxage int,minage int)returns table as return(select * from tbl_st