创建数据库物理模式

--建数据库(单文件)
create database NewDB
on
 PRIMARY
 (NAME =NewDB,
  FILENAME='D:/SQLData/NewDB.mdf',
  SIZE=10MB,
  MAXSIZE=2Gb,
  FILEGROWTH=20)
LOG ON
 (NAME =NewDBLog,
  FILENAME='D:/SQLData/NewDB.ldf',
  SIZE=10MB,
  MAXSIZE=1Gb,
  FILEGROWTH=10%)

--修改数据库设置

ALTER DATABASE NewDB
 MODIFY FILE
 (Name=NewDB,
  SIZE=25MB,
  MAXSIZE =2Gb,
  FILEGROWTH=0)

drop database NewDB

--建数据库(多文件)
create database NewDB
on
 PRIMARY
 (NAME =NewDB,
  FILENAME='D:/SQLData/NewDB1.mdf'),
 (NAME =NewDB2,
  FILENAME='D:/SQLData/NewDB2.ndf')
LOG ON
 (NAME =NewDBLog,
  FILENAME='D:/SQLData/NewDB1.ldf'),
 (NAME =NewDBLog1,
  FILENAME='D:/SQLData/NewDB2.ldf')

--添加多文件

ALTER DATABASE NewDB
 ADD FILE
 (Name=NewDB3,
  FILENAME='D:/SQLData/NewDB3.ndf',
  SIZE=10MB,
  MAXSIZE =2Gb,
  FILEGROWTH=20)

 

---------------文件组
create database NewDB
on
 PRIMARY
 (NAME =NewDB,
  FILENAME='D:/SQLData/NewDB.mdf',
  SIZE=10MB,
  MAXSIZE=2Gb,
  FILEGROWTH=25MB),
FILEGROUP GroupTwo
 (NAME =NewDBGroup2,
  FILENAME='D:/SQLData/NewDBTwo.ndf',
  SIZE=10MB,
  MAXSIZE=2Gb,
  FILEGROWTH=25MB)
log on
 (NAME =NewDBLog,
  FILENAME='D:/SQLData/NewDBlog.ldf',
  SIZE=10MB,
  MAXSIZE=2Gb,
  FILEGROWTH=25MB)

---
ALTER DATABASE NewDB
ALTER MODIFY FILEGROUP GroupTwo

 

 

 

 

 

--表变量
declare  @A table ([id] int,T1 varchar(50),T2 varchar(50))
INSERT INTO @A([id],T1,T2)  select * from t
select * from @A

--ID自动增长的
declare  @A table ([id] int IDENTITY (1, 1),T1 varchar(50),T2 varchar(50))
INSERT INTO @A(T1,T2)  select T1,T2 from t
select * from @A
IDENTITY (1, 1)

 

    

--流程
1.if/Else
  IF
   .... 
  ELSE
   .....
2.if Exists()
eg:
 if Exists(SELECT * FROM TABLE WHERE...)
 BEGIN

 END
--表