经典的SQL语句(基础篇)

 一、数据库对象的操作
        1、创建数据库
         CREATE DATABASE database-name 
        2、删除数据库
         drop database dbname
        4、创建新表
         create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
         根据已有的表创建新表： 
         A：create table tab_new like tab_old (使用旧表创建新表)
         B：create table tab_new as select col1,col2… from tab_old definition only
        5、删除新表 drop table tabname 
        6、增加一个列  Alter table tabname add column col type
        7、添加主键   Alter table tabname add primary key(col) 
            删除主键： Alter table tabname drop primary key(col) 
        8、创建索引：create [unique] index idxname on tabname(col….) 
        9、创建视图：create view viewname as select statement 
        10、初始化表table1 TRUNCATE TABLE table1
        11、删除视图：drop view viewname
        12、更表所有权人
         --创建架构
         --CREATE SCHEMA [Username] AUTHORIZATION [Username]
         exec sp_changeobjectowner 'tablename','dbo'  --在使用之前需要创建架构
        13、存储更改全部表

CREATE   PROCEDURE  dbo.User_ChangeObjectOwnerBatch
   @OldOwner   as   NVARCHAR ( 128 ),
   @NewOwner   as   NVARCHAR ( 128 )
  AS

  DECLARE   @Name     as   NVARCHAR ( 128 )
  DECLARE   @Owner    as   NVARCHAR ( 128 )
  DECLARE   @OwnerName    as   NVARCHAR ( 128 )

  DECLARE  curObject  CURSOR   FOR  
   select   ' Name '     =  name,
    ' Owner '     =   user_name (uid)
   from  sysobjects
   where   user_name (uid) = @OldOwner
   order   by  name

  OPEN   curObject
  FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner
  WHILE ( @@FETCH_STATUS = 0 )
  BEGIN      
   if   @Owner = @OldOwner  
   begin
    set   @OwnerName   =   @OldOwner   +   ' . '   +   rtrim ( @Name )
    exec  sp_changeobjectowner  @OwnerName ,  @NewOwner
   end
  --  select @name,@NewOwner,@OldOwner

   FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner
  END

  close  curObject
  deallocate  curObject
  GO

 二、管理维护数据库
        1、备份数据库
         --- 创建 备份设备
         EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
         --- 开始 备份
         BACKUP DATABASE pubs TO testBack 
        2、收缩数据库
          --重建索引
          DBCC REINDEX
          DBCC INDEXDEFRAG
          --收缩数据和日志
          DBCC SHRINKDB
          DBCC SHRINKFILE
         3、压缩数据库
          dbcc shrinkdatabase(dbname)
         4、转移数据库给新用户以已存在用户权限
          exec sp_change_users_login 'update_one','newname','oldname'
         5、检查备份集
          RESTORE VERIFYONLY from disk='E:/dvbbs.bak'
         6、修复数据库

  ALTER   DATABASE   [ dvbbs ]   SET  SINGLE_USER
  GO
  DBCC  CHECKDB( ' dvbbs ' ,repair_allow_data_loss)  WITH  TABLOCK
  GO
  ALTER   DATABASE   [ dvbbs ]   SET  MULTI_USER

        7、