--Create a database
--Determine if the database exists if it exists delete exists if the parameter returns a result set that is not NULL, then returns TRUE, otherwise false sysdatabases exists in master
--Switch database
Use master
if exists (SELECT * from sysdatabases where name= ' School ')
Drop Database School
--Automatically create folders xp--extends procedure
--Create Folder
exec sp_configure ' show advanced options '
Reconfigure--run refresh
exec sp_configure ' xp_cmdshell ', 1--set to turn on the auto-create folder feature
Reconfigure--run refresh
exec xp_cmdshell ' mkdir d:aa\bb\cc\dd '
Go-the token for the end of the batch process, stating that the statement before GO is a batch statement block
Create DATABASE School
On primary--set filegroup, default is PRIMARY filegroup
(
Name= ' School_data ',--logical name
SIZE=3MB,--Initial Size
filegrowth=10%,--File growth
MAXSIZE=100MB,--Maximum Capacity
Filename= ' D:aa\bb\cc\dd\school_data.mdf '
)
Log on
(
Name= ' School_log ',
SIZE=3MB,
filegrowth=10%,
Filename= ' D:aa\bb\cc\dd\school_log.ldf '
)
-------------------
--Creating databases and tables with code
--sql is mainly divided into DDL (data definition language, building table, building database and other statements. ), DML (Data Manipulation language multipulation)
--The initial size of the database name logical name automatically increases the full path
--Syntax:
--create Database
--on primary--Set file group
--(
--name= ' logical name _data ',--when it's not a single SQL command that can run independently, it's just a part of the structure, plus,
--name= "
--size= Initial size,--the value should not be included in the "", when the database is created error
--filegrowth= automatic growth,
--maxsize= maximum capacity,
--filename= ' full path '--the last sentence is not required, while the full path contains the folder and file name as well as the extended name
--)
--log on
--(
--name= ' logical name _log ',--when it's not a single SQL command that can run independently, it's just a part of the structure, plus,
--size= Initial size,--the value should not be included in the "", when the database is created error
--filegrowth= automatic growth,
--maxsize= maximum capacity,
--filename= ' full path '--the last sentence is not required, while the full path contains the folder and file name as well as the extended name
--)
--Determine if the database exists if it exists delete exists if the parameter returns a result set that is not NULL, then returns TRUE, otherwise false sysdatabases exists in master
--Switch database
Use master
if exists (SELECT * from sysdatabases where name= ' School ')
Drop Database School
--Automatically create folders xp--extends procedure
exec sp_configure ' show advanced options ', 1--turn on premium option
Reconfigure
exec sp_configure ' xp_cmdshell ', 1--set to turn on the auto-create folder feature
Reconfigure
exec xp_cmdshell ' mkdir d:\aa\qq\ss\dd '
Create DATABASE School
On primary--set filegroup, default is PRIMARY filegroup
(
Name= ' School_data ',--logical name
SIZE=3MB,--Initial Size
filegrowth=10%,--File growth
MAXSIZE=100MB,--Maximum Capacity
Filename= ' D:\aa\qq\ss\dd\School_data.mdf '
),
Filegroup AA-Create a filegroup, and later, when you create the table, you can make sure that the table is created on that filegroup
(
Name= ' school_data1 ',--logical name
SIZE=3MB,--Initial Size
filegrowth=10%,--File growth
MAXSIZE=100MB,--Maximum Capacity
Filename= ' D:\AA\QQ\SS\DD\SCHOOL_DATA1.NDF '
)
Log on
(
Name= ' School_log ',--logical name
SIZE=3MB,--Initial Size
filegrowth=10%,--File growth
--MAXSIZE=100MB,--Maximum capacity log file does not generally set the maximum capacity
Filename= ' D:\aa\qq\ss\dd\School_log.ldf '
)
SQL CREATE Database