SRE文档

深浅模式

1.数据库操作 ​

https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool

1.1 创建 ​

1.1.1指定路径创建 ​

sql
USE master;
GO

CREATE DATABASE Sales COLLATE Chinese_PRC_CI_AS ON
(NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED, --默认单位是MB
    FILEGROWTH = 5)
LOG ON
(NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5 MB,
    MAXSIZE = 25 MB,
    FILEGROWTH = 5 MB);
GO

-- 检查新数据库是否创建成功
SELECT name FROM sys.databases WHERE name = 'Sales';
USE master;
GO

CREATE DATABASE Sales COLLATE Chinese_PRC_CI_AS ON
(NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED, --默认单位是MB
    FILEGROWTH = 5)
LOG ON
(NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5 MB,
    MAXSIZE = 25 MB,
    FILEGROWTH = 5 MB);
GO

-- 检查新数据库是否创建成功
SELECT name FROM sys.databases WHERE name = 'Sales';

1.1.2默认创建 ​

sql
#创建数据库并指定字符集
create database db_name COLLATE Chinese_PRC_CI_AS
#创建数据库并指定字符集
create database db_name COLLATE Chinese_PRC_CI_AS

2.表操作 ​

1.复制表结构 ​

      一、两个数据库部署在同一服务器上：

           从数据库A复制到B：

           复制表结构和数据：select * into  数据库B.dbo.Department from 数据库A.dbo.Department 

           只复制表结构:  select * into  数据库B.dbo.Department from 数据库A.dbo.Department  where 1!=1

           只复制部分字段：select a1，a2 into  数据库B.dbo.Department from 数据库A.dbo.Department
      一、两个数据库部署在同一服务器上：

           从数据库A复制到B：

           复制表结构和数据：select * into  数据库B.dbo.Department from 数据库A.dbo.Department 

           只复制表结构:  select * into  数据库B.dbo.Department from 数据库A.dbo.Department  where 1!=1

           只复制部分字段：select a1，a2 into  数据库B.dbo.Department from 数据库A.dbo.Department