SQL Server2012从入门到精通

1 select * from sys.master_files   可以快速获取SQL Server实例中全部数据库的清单

2 创建数据库

use master;
create database SBSChp4TSQL
on primary
(
    name = 'SBSChp4TSQL1', filename = 'C:\SQLData\SBSTSQL1.mdf',
    size = 10mb, maxsize = 20, filegrowth = 10%
)
log on
(
    name = 'SBSChp4TSQL_log', filename = 'C:\SQLLog\SBSTSQL_log.ldf',
    size = 10mb, maxsize = 200, filegrowth = 20%
);

这里的name就是指逻辑名称，默认与数据库名相同，日志会加_log; 物理文件名也是一样. 默认扩展名分别为mdf和ldf

 

--创建数据库
use master;
create database SBSChpTSQL
on primary(
    name = SBSChp4TSQL,
    filename = 'C:\SQLData\SBSTSQL.mdf',
    size = 10mb,
    maxsize = 50mb,
    filegrowth = 10mb)
log on(
    name = SBSChp4TSQL_log,
    filename = 'C:\SQLLog\SBSTSQL_log.ldf',
    size = 5mb,
    maxsize = 100mb,
    filegrowth = 20);

--添加文件与文件组
use master;
--添加文件组
alter database SBSChp4TSQL
    add filegroup SBSTSQLGroup1;
--添加文件
alter database SBSChp4TSQL
    add file
    (
        name = 'SBSChp4TSQL2',
        filename = 'C:\SQLDATA\SBSTSQL2.ndf',
        size = 10mb,
        maxsize = 20,
        filegrowth = 10
    )
to filegroup SBSTSQLGroup1;

--分离数据库
use master;
exec sp_detach_db @dbname = 'SBSChp4TSQL';

--附加数据库
use master;
create database SBSChp4TSQL on
(filename = 'C:\SQLData\SBSTSQL1.mdf'),
(filename = 'C:\SQLData\SBSTSQL2.ndf'),
(filename = 'C:\SQLLog\SBSTSQL_Log.ldf')
for attach;

 

--创建架构
use SBSChpTSQL;
go
create schema Sales;
go
create schema HumanResources;
go
--修改架构, 语法错误
alter schema Sales transfer HumanResources;
go

--decimal(4,2) 表示精度为4，小数点左右两边各有2位。如果存储货币，个人建议使用money类型
--也才占用8个字节，如果用decimal(18,2)书上说占用9个字节呢。

--创建表
use SBSChpTSQL;
create table HumanResources.Address
(
    AddressID int not null identity(1,1),
    StreetAddress varchar(125) not null,
    StreetAddress2 varchar(75) null,
    City varchar(100) not null,
    State char(2) not null,
    EmployeeID int not null
) --on sbstsqlgroup1;

use SBSChpTSQL;
create table HumanResources.Employee
(
    EmployeeID int not null identity(1,1),
    FirstName varchar(50) not null,
    MiddleName varchar(50) null,
    LastName varchar(50) not null
);
use SBSChp4SSMS;
create table HumanResources.Employee
(
    EmployeeID int not null identity(1,1),
    FirstName varchar(50) not null,
    MiddleName varchar(50) null,
    LastName varchar(50) not null
) on SBSSSMSGroup1;

--添加列
use SBSChpTSQL;
alter table HumanResources.Employee
    add Gender char(1) not null;

--添加计算列
use SBSChpTSQL;
alter table HumanResources.Employee
    add FullName as LastName+','+FirstName;

use SBSChpTSQL;
alter table HumanResources.Employee
    Add Active bit not null;
alter table HumanResources.Employee
    add SocialSecurityNumber varchar(10) not null;
use SBSChp4SSMS;
alter table HumanResources.Employee
    add Active bit not null;
alter table HumanResources.Employee
    add SocialSecurityNumber varchar(10) not null;

--主键约束
use SBSChp4SSMS;
alter table HumanResources.Employee
    add constraint PK_HumanResourcesEmployee_EmployeeID
    primary key(EmployeeID);

alter table HumanResources.Address
    add constraint PK_HumanResourcesAddress_AddressID
    primary key(AddressID);
--默认值约束
alter table HumanResources.Employee
    add constraint DF_HumanResourcesEmployee_Active_True default(1) for Active;
--唯一约束
alter table HumanResources.Employee
    add constraint UQ_HumanResourcesEmployee_SocialSecurityNumber
    UNIQUE(SocialSecurityNumber);
--检查约束
alter table HumanResources.Employee
    add constraint CK_HumanResourcesEmployee_Gender_MF check(Gender ='F' or Gender = 'M');
--删除约束
alter table HumanResources.Employee
    drop constraint CK_HumanResourcesEmployee_Gender_MF;

use SBSChpTSQL;
alter table HumanResources.Address
    add constraint PK_HumanResourcesAddress_AddressID
    primary key (AddressID);
--外键约束
use SBSChpTSQL;
alter table HumanResources.Address
    add constraint FK_Employee_To_Address_On_EmployeeID
        foreign key(EmployeeID) references HumanResources.Employee(EmployeeID);

 六 索引的建立与维护

--附加数据库
use master;
create database AdventureWorks2012 on
(filename = 'C:\SQLData\AdventureWorks2012.mdf')
for attach;
go

--创建聚集索引（包含选项：有则删除，没有则创建）
use AdventureWorks2012;
create clustered index CIX_DatabaseLog_PostTime
on dbo.DatabaseLog
(
    PostTime desc
)
with(drop_existing = on);
go

--创建非聚集索引
use AdventureWorks2012;
create nonclustered index IX_SalesOrderHeader_DueDate
on Sales.SalesOrderHeader
(
    DueDate
);
go

--添加索引选项
--FillFactor设置：表被频繁修改70-90；表无须时常更新90
use AdventureWorks2012;
create clustered index CIX_DatabaseLog_PostTime
on dbo.DatabaseLog
(
    PostTime desc
)
with(drop_existing = on, sort_in_tempdb = on, fillfactor = 80, pad_index = on);

--添加包含性列
use AdventureWorks2012;
create nonclustered index IX_SalesOrderHeader_OrderDate
on Sales.SalesOrderHeader
(
    OrderDate
)
include(Status, AccountNumber)
with(drop_existing = on);

--为索引添加筛选器
use AdventureWorks2012;
create nonclustered index IX_SalesOrderHeader_OrderDate
on Sales.SalesOrderHeader
(
    OrderDate
)
include(Status, AccountNumber)
where(OnlineOrderFlag = 0)
with(drop_existing = on);

--放置索引:新建文件组并增加一个数据文件
use master;
alter database AdventureWorks2012
    add filegroup AW2012FileGroup2;

alter database AdventureWorks2012
add file(
    name = IndexFile,
    filename = 'C:\SQLData\IndexFile.ndf',
    size = 5mb,
    maxsize = 100mb,
    filegrowth = 5mb)
to filegroup AW2012FileGroup2;

--将索引放置在一个文件组中
use AdventureWorks2012;
create nonclustered index IX_SalesOrderHeader_OrderDate
on Sales.SalesOrderHeader
(
    OrderDate
)
include(Status, AccountNumber)
where(OnlineOrderFlag = 0)
with(drop_existing = on)
on AW2012FileGroup2;

--禁用索引
use AdventureWorks2012;
alter index IX_SalesOrderHeader_OrderDate
    on Sales.SalesOrderHeader disable;
--删除索引
use AdventureWorks2012;
drop index CIX_DatabaseLog_PostTime
    on dbo.DatabaseLog;

 

use AdventureWorks2012;
select * from [HumanResources].[Department];
--select语句如果没有指定order by子句，则返回顺序与聚集索引（如果有）一致

--between ... and注意两个边界的数值是内含的。
use AdventureWorks2012;
select AccountNumber, SalesOrderID, OrderDate
from Sales.SalesOrderHeader
where OrderDate between '5/1/2007' and '12/31/2007';

--union的使用
use AdventureWorks2012;
select Name as ProductName from Production.Product where Color = 'Black'
union-- all
select Name as ProductName from Production.Product where Color = 'Silver'
order by Productname;

 十二 修改数据

--用insert into插入一行数据
use AdventureWorks2012;
insert into HumanResources.Department(Name, GroupName, modifiedDate)
values('Payroll', 'Excutive General and Administration', '6/12/2012');

select DepartmentID, Name, GroupName, ModifiedDate
from HumanResources.Department
order by DepartmentID desc;

--使用insert into向标识列中插入数据
use AdventureWorks2012;
set identity_insert HumanResources.Department on
insert into HumanResources.Department(DepartmentID, Name, GroupName, ModifiedDate)
values(19, 'International Marketing', 'Sales and Marketing', '5/26/2012');
set identity_insert HumanResources.Department off


--插入数据时使用序列号
use AdventureWorks2012;
go
if(object_ID('dbo.States')) is not null
    drop table dbo.States
go
create table dbo.States
(
    StateID int primary key,
    StateName varchar(50),
    StateAbbrev char(2)
)
go
create sequence dbo.StateSeq
as int
start with 1
increment by 1
go
insert into dbo.States(StateID, StateAbbrev, StateName)
values
        (next value for dbo.StateSeq, 'LA', 'Louisiana'),
        (next value for dbo.StateSeq, 'TX', 'Texas'),
        (next value for dbo.StateSeq, 'FL', 'Florida')
go
select * from dbo.States

--使用select语句插入数据
use AdventureWorks2012;
insert into HumanResources.Department(Name, GroupName, ModifiedDate)
select
    Name + ' USA', GroupName, ModifiedDate
from HumanResources.Department
where DepartmentID in (20,19);

--使用select into创建一张新表，并插入数据, 也可以插入到临时表中
use AdventureWorks2012;
select DepartmentID, Name, GroupName, ModifiedDate
into dbo.Department
from HumanResources.Department

--更新单行数据
use AdventureWorks2012;
update HumanResources.Department
set Name = Name + ' Europe'
where DepartmentID = 15

--防止查询意外执行二次，添加额外的筛选条件，现在第二次执行将不会生效
use AdventureWorks2012;
update HumanResources.Department
set Name = Name + ' Europe'
where DepartmentID = 15
and Name not like '% Europe'

--删除单行数据
use AdventureWorks2012;
delete from HumanResources.Department
where DepartmentID = 21
--使用truncate删除所有数据行, 不能指定删除数据行的数量
use AdventureWorks2012;
Truncate table dbo.Department

--使用merge执行数据的插入与更新
use AdventureWorks2012;
merge dbo.Department destination
    using HumanResources.Department source
        on destination.Name = source.Name
    when matched then
    update
        set destination.Name = source.Name,
            destination.GroupName = source.GroupName,
            destination.ModifiedDate = source.ModifiedDate
    when not matched by target then
        insert (Name, GroupName, ModifiedDate)
        values (source.Name, source.GroupName, source.ModifiedDate);

--使用output输出insert语句的执行结果
use AdventureWorks2012;
insert into HumanResources.Department
    output inserted.DepartmentID, inserted.Name, inserted.GroupName, inserted.ModifiedDate
    values('International Marketing CHINA', 'Sales and Marketing', '5/23/2012');


--output输出update语句的执行结果
use AdventureWorks2012;
update HumanResources.Department
set Name = Name + ' Europe'
output
    deleted.Name as oldName,
    inserted.Name as updateValue
where DepartmentID = 19

--将output的数据插入一张表中
use AdventureWorks2012;
go
create table dbo.Department_Audit
(
    DepartmentID int not null,
    Name nvarchar(50) not null,
    GroupName nvarchar(50) not null,
    DeleteDate datetime not null
        constraint DF_Department_Audit_DeletedDate_Today default(getdate())
)
go
delete from dbo.Department
output deleted.DepartmentID, deleted.Name, deleted.GroupName
into dbo.Department_Audit(DepartmentID, Name, GroupName)
where DepartmentID = 16;

select * from dbo.Department_Audit

 十三 标量值函数

--getdate()返回datetime类型，sysdatetime()返回datetime2(7)类型
select getdate() as getdate, sysdatetime() as sysdatetime;

--拆分显示日期和时间值, 一个返回整数，一个返回串
select
    day(getdate()) as day,
    month(getdate()) as month,
    year(getdate()) as year,
    datename(weekday, getdate()) as datenameweekday,
    datepart(m, getdate()) as datepart,
    datepart(weekday,getdate()) as datepartweekday,
    datename(month, getdate()) as datenamemonth;
--常用datepart参数及缩写：year y
--                                    month m
--                                    day d
--                                    week wk    
--                                    hour hh
--                                    minute mi,n
--                                    second s
--datefromparts标量值函数
select
    datefromparts(1972,5,26) as datefromparts,
    datetime2fromparts(1972,5,26,7,14,16,10,3) as datetime2fromparts,
    datetimefromparts(1972,5,26,7,14,16,10) as datetimefromparts,
    datetimeoffsetfromparts(1972,5,26,7,14,16,10,12,0,3) as datetimeoffsetfromparts,
    smalldatetimefromparts(1972,5,26,7,14) smalldatetimefromparts,
    timefromparts(7,14,16,10,3) timefromparts

    --日期计算与验证
    select
        datediff(dd,getdate(),'5/26/2021') as daysUntilMyBirthday,
        dateadd(y,1,getdate()) as dateAdd,
        eomonth(getdate()) as eomonth,
        isdate(getdate()) as isValidDate,
        isdate('13/1/2122') as InvalidDate

--cast转换数据
use AdventureWorks2012;
select top(10)
    SalesOrderNumber,
    TotalDue,
    cast(TotalDue as decimal(10,2)) as TotalDueCast,
    OrderDate,
    cast(OrderDate as date) as OrderDateCast
from Sales.SalesOrderHeader;

--convert转换数据,可以有样式编码参数，比cast函数更灵活
select
    convert(varchar(20), getdate()) as [default],
    convert(varchar(20), getdate(), 100) as defaultWithStyle,
    convert(varchar(20), getdate(), 103) as BritishFrenchStyle,
    convert(varchar(8), getdate(), 105) as ItalianStyle,
    convert(varchar(8), getdate(), 112) as ISOStyle,
    convert(varchar(15), cast('111111.22' as money), 1) as moneyWithCommas

--parse和try_parse
select
    try_cast('Patrick' as int) tryCast,
    try_convert(date,'20210611', 112) as tryConvert,
    parse('Saturday, 26 may 2012' as datetime using 'en-us') as Parse,
    try_parse('Patricks Birthday' as datetime using 'en-us') as tryParse

--字符串函数
select
    'LEBLANC '+', '+' PATRICK' RawValues,
    rtrim('leblanc  ')+',  '+ltrim('  patrick') trimValue,
    left('PatrickDTomorr', 7) [left],
    right('DTomorrLeBlanc', 7) [right],
    substring('DTomorrPatrick', 8, len('DTomorrPatrick')) [SubString],
    '12/'+cast(1 as varchar)+'/2012' WithoutConcat,
    concat('12/',1,'/2012') WithConcat

--逻辑函数
declare @choosevar int = 3
select
    choose(@choosevar, 'one', 'two', 'patrick', 'three') [choose],
    iif(datename(month, getdate()) = '06', 'The 4th is this month',
        'No Fireworks') as [iif]

 十四 T-SQL高级主题

--执行简单的聚合
use AdventureWorks2012;
select
    sum(poh.TotalDue) as TotalDue
from Purchasing.PurchaseOrderHeader poh

select
    sum(poh.TotalDue) as TotalDue,
    avg(poh.TotalDue) as AverageTotalDue,
    count(poh.EmployeeID) NumberOfEmployee,
    count(distinct poh.EmployeeID) DistinctNumberOfEmployee
from Purchasing.PurchaseOrderHeader poh

select
    sm.Name as ShippingMethod,
    sum(poh.TotalDue) as TotalDue,
    avg(poh.TotalDue) as AverageTotalDue,
    count(poh.EmployeeID) NumberOfEmployee,
    count(distinct poh.EmployeeID) DistinctNumberOfEmployee
from Purchasing.PurchaseOrderHeader poh
inner join Purchasing.Shipmethod sm
on poh.ShipMethodID = sm.shipMethodID
group by sm.Name

select
    sm.Name as ShippingMethod,
    year(poh.OrderDate) as orderDate,
    sum(poh.TotalDue) as TotalDue,
    avg(poh.TotalDue) as AverageTotalDue,
    count(poh.EmployeeID) as NumberOfEmployees,
    count(distinct poh.EmployeeID) as DistinctNumberOfEmployee
from Purchasing.PurchaseOrderHeader poh
inner join Purchasing.ShipMethod sm
    on poh.ShipMethodID = sm.ShipMethodID
group by sm.Name, year(poh.OrderDate)

--对数据进行分区
use AdventureWorks2012;
go
with ProductQty
as
(
    select top 10
        p.ProductID,
        sum(sod.OrderQty) as OrderQty
    from Sales.SalesOrderDetail as sod
    inner join Production.Product as p
        on sod.ProductID = p.ProductID
    group by p.ProductID
)
select
    p.Name as ProductName,
    pq.OrderQty,
    row_number() over(order by pq.OrderQty desc) rowNumber,
    rank() over(order by pq.OrderQty desc) [rank],
    dense_rank() over(order by pq.OrderQty desc) [denseRank]
from ProductQty as pq inner join Production.Product as p
    on pq.ProductID = p.ProductID;

--having子句
use AdventureWorks2012;
select
    sm.Name as ShippingMethod,
    year(poh.OrderDate) OrderYear,
    sum(poh.TotalDue) as TotalDue,
    avg(poh.TotalDue) as AverageTotalDue,
    count(poh.EmployeeID) as NumberOfEmployee,
    count(distinct poh.EmployeeID) as DistinctNumberOfEmployee
from Purchasing.PurchaseOrderHeader poh
inner join Purchasing.ShipMethod sm
    on poh.ShipMethodID = sm.ShipMethodID
group by sm.Name, year(poh.OrderDate)
having sum(poh.TotalDue) > 5000000

--CTE即Common table expression公用表表达式
use AdventureWorks2012;
with EmployeePOs(EmployeeID, [Total Due])
as
(
    select
        poh.EmployeeID,
        convert(varchar(20), sum(poh.TotalDue), 1)    --两位小数，逗号分隔
    from Purchasing.PurchaseOrderHeader poh
    group by
        poh.EmployeeID
)
select * from EmployeePOs

with EmployeePOs(EmployeeID, [Total Due])
as
(
    select
        poh.EmployeeID,
        convert(varchar(20), sum(poh.TotalDue), 1)
    from Purchasing.PurchaseOrderHeader poh
    group by
        poh.EmployeeID
)select
    ep.EmployeeID,
    p.FirstName,
    p.LastName,
    ep.[Total Due]
from EmployeePOs ep
inner join Person.Person p
    on ep.EmployeeID = p.BusinessEntityID

--表变量（一般小于500行的数据时使用）整个批处理期间都可访问
use AdventureWorks2012;
declare @EmployeePOs as table
(
    EmployeeID int,
    TotalDue money
);
insert into @EmployeePOs
select
    poh.EmployeeID,
    convert(varchar(20), sum(poh.TotalDue), 1)
from Purchasing.PurchaseOrderHeader poh
group by
    poh.EmployeeID;
select
    ep.EmployeeID,
    p.FirstName,
    p.LastName,
    ep.TotalDue
from @EmployeePOs ep
inner join Person.Person p
    on ep.EmployeeID = p.BusinessEntityID

--本地和全局临时表
use AdventureWorks2012;
create table #EmployeePOs
(
    EmployeeID int,
    TotalDue money
)
insert into #EmployeePOs
select
    poh.EmployeeID,
    convert(varchar(20), sum(poh.TotalDue), 1)
from Purchasing.PurchaseOrderHeader poh
group by
    poh.EmployeeID
    
select
    ep.EmployeeID,
    p.FirstName,
    p.LastName,
    ep.TotalDue
from #EmployeePOs ep
inner join Person.Person p
    on ep.EmployeeID = p.BusinessEntityID

--处理T-SQL错误
begin try
    select 1/0;
end try
begin catch--错误号50000以上，消息，状态0-255
    throw 51000, 'You divided my ZERO!!!', 1;
end catch

--控制流关键字
--begin...end
use AdventureWorks2012;
begin
    declare @StartingHireDate datetime = '12/31/2001'
    select e.BusinessEntityID, p.FirstName, p.LastName, e.HireDate
    from HumanResources.Employee e
    inner join Person.Person p
        on e.BusinessEntityID = p.BusinessEntityID
    where HireDate <= @StartingHireDate
end

--if...else
if(datename(m, getdate()) = 'December')
begin
    select 'Time for the holidays!!!!' Results
end
else
begin
    select 'Not sure what''s going on now :(' Results
end

--while
declare @count int = 0
while(@count < 10)
begin
    set @count = @count + 1
    if(@count < 5)
        begin
            select @count as Counter
            continue--继续下次循环
        end
    else
        break--退出循环
        --return;
end

 第十五章 视图

--借助于columnProperty函数确定某个列是否是确定的
SELECT   COLUMNPROPERTY(OBJECT_ID('Sales.SalesOrderDetail'), 'LineTotal', 'IsDeterministic') AS 'Column Length'
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
IF(OBJECT_ID('Purchasing.vwPurchaseOrders')) IS NOT NULL
    DROP VIEW Purchasing.vwPurchaseOrders
GO
CREATE VIEW Purchasing.vwPurchaseOrders
WITH SCHEMABINDING
AS
SELECT
    poh.OrderDate,
    pod.ProductID,
    SUM(poh.TotalDue) TotalDue,
    COUNT_BIG(*) POCount
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
    GROUP BY poh.OrderDate, pod.ProductID
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vwPruchaseOrders_OrderDateProductID
ON Purchasing.vwPurchaseOrders(OrderDate, ProductID)


SELECT * FROM Purchasing.vwPurchaseOrders;

其实没看懂，这个索引视图什么东东