SQL Server的使用笔记

1.在修改table时，SQL Srever 2000可以如下：
alter database dbstudent modify file(name=dbstudent,maxsize=30)
语句来修改数据库的存根文件，使之到达最好的使用效率。
在sql server 2000中可以对表的列进行添加（该列是能为空的），对列的约束进行删除，还不能对列进行修改！
alter table student add phone CHAR(8);
alter table student modify phone CHAR(12);//会报错：服务器: 消息 170，级别 15，状态 1，行 16
第 16 行: 'phone' 附近有语法错误
2.select HOST_NAME() as 'hostname',HOST_ID() as 'host_id',USER_NAME() as 'user_name',USER_ID() as 'user_id', DB_NAME() as 'db_name';//返回一些系统信息
select CURRENT_USER as 'currentuser' ,CURRENT_TIMESTAMP as 'dthghgime';//返回当前用户和时间

select lenth=DATALENGTH(code),code from student;--返回code字段的设计长度
select col_length('student','name') as 'int',
col_length('student','code') as 'char'//还回对应列的设计长度

3.select HOST_NAME() as 'hostname',HOST_ID() as 'host_id',USER_NAME() as 'user_name',USER_ID() as 'user_id', DB_NAME() as 'db_name';//返回一些系统信息
select CURRENT_USER as 'currentuser' ,CURRENT_TIMESTAMP as 'dthghgime';//返回当前用户和时间

select lenth=DATALENGTH(code),code from student;--返回code字段的实际占的长度，对于可变长的而言，是一个变化的
select col_length('student','name') as 'int',
col_length('student','code') as 'char'//还回对应列的设计长度，不论是否时可变长的还是不可变长的。

4.在sql server 2000中支持like多种应用：
1）like '%jhdfjd'
2) like '%jhfdh%'
3) like '%djfj%djj_'
4) like '%jdfjk...jhsdh%'

sql server支持的not关系是：and not。而不是直接用not

begin transaction mustt
insert into student values(12,'kkk','j大洒扫','j','djhdjh')

insert into student values(45,'jhsjhs','j','h','asjkdjk')
if @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION mustt
print 'error'
RETURN
END
COMMIT TRANSACTION mustt

//在上面的事务中，当两条插入语句有出现错误的时候，没有错误的就插入到表中，错误的语句不影响到正确的插入语句。

保存事务：
begin transaction savemustt
insert into student values(13,'kkk','j','j','djhdjh')
save transaction before_insert_data2
insert into student values(46,'jhsjhs','j','h','asjkdjk')

ROLLBACK TRANSACTION before_insert_data2 /*这样就以第一条记录为保存点，且只插入第一条记录，*/
5./*声明触发器
create trigger dl_stu_mess4 on student for delete
as
declare @name_id int
select @name_id=stu.name from student stu,deleted d where stu.name=d.name
if exists(select * from books where books.name=@name_id)
delete from books where books.name=@name_id
print 'jhsdjkjk'
print @name_id
*/
/*
create trigger dl_stu_mess3 on books for delete
as
declare @name_id int
select @name_id=bo.name from books bo,deleted d where bo.name=d.name
if exists(select * from student where student.name=@name_id)
delete from student where student.name=@name_id
print 'jhsdjkjk'
print @name_id
*/
--sp_depends books 查看触发器
--sp_helptrigger student; 查看触发器信息
--drop trigger dl_stu_mess4; 删除触发器
insert into student values(2,'4jkjjk','d','j','shs');
--delete from student where stu.name=2; 删除操作
select * from student;
--delete from books where books.name='2'
--select * from books;
6.sql支持数据类型转换，可以把stirng转成int，但可能会出现异常.
7.一般不要用name等之类的关键词来命名字段，这样会对访问的时候产生影响。