关于SQL server的一些知识点

1. 关于怎么打开xp_cmdshell的方法：

   exec sp_configure ‘show advanced option‘,1
   reconfigure
   go
   exec sp_configure ‘xp_cmdshell‘,1
   reconfigure
   go

2. 关于截取表字段名称：

   select NewName=left(trim(name),1) from student

3. SQL临时表的用法：

   select * into #A from student 将student内容插入到临时表#A

4. SQL添加表外键关联：
   alter table borrow add constraint FK_student foreign key(stuID) references student(stuID)
5. SQL多表查询：

   select stuent.stuName,stuent.stuID,book.title,book.BID,borrow.T_time from student,book,borrow where student.stuID=borrow.stuID,book.BID=borrow.BID,borrow.T_time between ‘2007-12-15‘ and ‘2008-1-8‘

6. SQL根据表数据的个数添加对应的序号：

   update stu
   set sCount =
   (select Temp.value from (select sCode, value = 20 * ROW_NUMBER() over (order by sCode ) from stu) as Temp where Temp.scode = stu.sCode )

7. SQL创建聚集索引和非聚集索引：

   CREATE CLUSTERED INDEX IX_Id ON Person(Id)

   CREATE NONCLUSTERED INDEX IX_Name ON Person(Name) --创建非聚集索引

关于SQL server的一些知识点

标签：