SQLServer知识干货

最新推荐文章于 2020-12-26 15:55:33 发布
最新推荐文章于 2020-12-26 15:55:33 发布
阅读量293 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/asfuyao/article/details/107907576
版权

1. SQLServer基础知识

1.1. 前言

1.1.1. SQLServer版本

版本操作系统年代
1.0、1.1、4.2Sybase OS/21989-1992
NT、6.0、7.0、2000Windows1992-2000
2005、2008、2008 R2、2014、2016Windows2005-2016
2017Windows、Linux2017
  • SQL Server的版本号
    • 8.0 -> sql server 2000
    • 9.0 -> sql server 2005
    • 10.0 -> sql server 2008
    • 10.5 -> sql server 2008 R2
    • 11.0 -> sql server 2012
    • 12.0 -> sql server 2014

1.1.2. 连接方式

ODBC、JDBC、BDE、OLE DB、ADO.NET

1.1.3. 常见管理工具

  • Microsoft SQL Server Management Studio (SSMS)
  • PremiumSoft Navicat Premium
  • JetBrains DataGrip
  • DBeaver Community
  • HeidiSQL
  • Azure Data Studio
  • Visual Studio Code + SQL Server Extensions

1.1.4. 常见连接故障

  • 数据库服务未启动，打开服务面版启动服务
  • 数据库网络服务未配置TCP/IP协议，在数据库配置工具打开TCP/IP协议后重启数据库服务
  • 实例名不是默认的MSSQLSERVER，需要在连接地址后指定实例名（可在配置管理工具中查看实例名），如数据库实例名为SQL，连接时需要在服务器名或IP地址后指定实例名，例：192.168.1.11\SQL
  • 未开启双重认证方式，在服务器端以本地管理员登陆数据库后修改认证方式未双重，即Windows认证 + SQL Server认证

1.1.5. SQLServer学习网站

1.2. 应用基础

1.2.1. 创建数据库

USE master;
GO

CREATE DATABASE Test
  ON
  (
    NAME = Test_dat,
    FILENAME = 'D:\Database\Testdat.mdf',
    SIZE = 10,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5 )
  LOG ON
  (
    NAME = Test_log,
    FILENAME = 'D:\Database\Testlog.ldf',
    SIZE = 5MB,
    MAXSIZE = 1024MB,
    FILEGROWTH = 5MB )
  COLLATE Latin1_General_CI_AS
GO

ALTER DATABASE Test SET RECOVERY SIMPLE;
GO

1.2.2. 创建表和索引

USE Test;
GO

CREATE TABLE dbo.Dev
(
  DevId      INT           IDENTITY(1, 1) NOT NULL,
  DevName    NVARCHAR(200) NOT NULL,
  StockQty   DECIMAL(9, 2) NULL,
  Flag       BIT           NOT NULL DEFAULT( 1 ),
  DevType    SMALLINT      NOT NULL DEFAULT( 1 ),
  CreateUser NVARCHAR(10)  NOT NULL,
  CreateDate DATETIME      NOT NULL DEFAULT( GETDATE()),
  ModifyUser NVARCHAR(10)  NULL,
  ModifyDate DATETIME      NULL,
  CONSTRAINT PK_Dev_DevId PRIMARY KEY CLUSTERED( DevId ASC )
);
GO

USE Test;
GO

CREATE TABLE dbo.Dev
(
  DevId      INT           IDENTITY(1, 1) NOT NULL,
  DevName    NVARCHAR(200) NOT NULL,
  StockQty   DECIMAL(9, 2) NULL,
  Flag       BIT           NOT NULL DEFAULT( 1 ),
  DevType    SMALLINT      NOT NULL DEFAULT( 1 ),
  CreateUser NVARCHAR(10)  NOT NULL,
  CreateDate DATETIME      NOT NULL DEFAULT( GETDATE()),
  ModifyUser NVARCHAR(10)  NULL,
  ModifyDate DATETIME      NULL,
);
GO
--创建主键
ALTER TABLE dbo.Dev
ADD CONSTRAINT PK_Dev_DevID PRIMARY KEY CLUSTERED( DevId );
GO
--删除主键
ALTER TABLE dbo.Dev
DROP CONSTRAINT PK_Dev_DevID;

1.2.3. 修改表和索引

USE Test;
GO

--添加字段
ALTER TABLE dbo.Dev
ADD remark NVARCHAR(500) NULL;
--修改字段名
EXECUTE sp_rename 'dbo.Dev.remark','dbo.Dev.memo';
--修改字段类型
ALTER TABLE dbo.Dev
ALTER COLUMN memo NVARCHAR(300);
--添加有默认值的字段
ALTER TABLE dbo.Dev
ADD delflag INT NOT NULL DEFAULT 0;
--删除字段
ALTER TABLE dbo.Dev
DROP COLUMN delflag;

--删除约束
ALTER TABLE dbo.Dev
DROP CONSTRAINT [DF__Dev__delflag__276EDEB3]
--查询约束
EXECUTE sp_helpconstraint @objname='dbo.Dev'
--查询约束
SELECT * FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.Dev') --所有
SELECT * FROM sys.default_constraints WHERE parent_object_id=OBJECT_ID('dbo.Dev') --默认
SELECT * FROM sys.check_constraints   WHERE parent_object_id=OBJECT_ID('dbo.Dev') --检查
SELECT * FROM sys.key_constraints     WHERE parent_object_id=OBJECT_ID('dbo.Dev') --键
--添加默认约束
ALTER TABLE dbo.Dev
ADD CONSTRAINT DF_Dev_CreateUser DEFAULT( 'Admin' )FOR CreateUser;
--添加检查约束
ALTER TABLE dbo.Dev
ADD CONSTRAINT CK_Dev_StockQtyCheck CHECK( StockQty BETWEEN 0 AND 10000 );
--添加唯一约束
ALTER TABLE dbo.Dev
ADD CONSTRAINT UQ_Dev_DevName UNIQUE(DevName);

USE Test;
GO
--查询索引
SELECT     i.object_id, i.name index_name, i.index_id, i.type_desc, i.is_unique, ic.column_id, c.name column_name,ic.is_descending_key
FROM       sys.indexes i
INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = ic.column_id
WHERE      i.object_id = OBJECT_ID('dbo.Dev');

--创建非聚集索引
CREATE NONCLUSTERED INDEX Index_Dev_DevName
ON dbo.Dev (DevName)
WITH(DROP_EXISTING=ON)
--删除索引
DROP INDEX Index_Dev_DevName ON dbo.Dev;
--创建唯一非聚集索引
CREATE UNIQUE INDEX Index_Dev_DevName
ON dbo.Dev (DevName)
WITH(DROP_EXISTING=ON)

1.2.4. 添加记录

--单条添加
INSERT INTO dbo.Dev( DevName, StockQty, Flag, DevType, CreateUser)
VALUES( N'烘干炉', 2, 1, 0, N'Admin');
DevIdDevNameStockQtyFlagDevTypeCreateUserCreateDateModifyUserModifyDatememo
1烘干炉2.0010Admin2019-04-23 14:22:21.237NULLNULLNULL
--多条记录
INSERT INTO dbo.Dev( DevName, StockQty, Flag, DevType, CreateUser )
VALUES( N'烘干炉', 2, 1, 0, N'Admin' ),
      ( N'三坐标', 8, 1, 0, N'system' );
DevIdDevNameStockQtyFlagDevTypeCreateUserCreateDateModifyUserModifyDatememo
1烘干炉2.0010Admin2019-04-23 14:22:21.237NULLNULLNULL
2三坐标8.0010system2019-04-23 14:22:21.237NULLNULLNULL
--从其他表获取数据插入
INSERT INTO dbo.Dev( DevName, StockQty, Flag, DevType, CreateUser )
SELECT DevName, StockQty, Flag, DevType, CreateUser
FROM   dbo.Dev
WHERE  DevId > 0;
DevIdDevNameStockQtyFlagDevTypeCreateUserCreateDateModifyUserModifyDatememo
1烘干炉2.0010Admin2019-04-23 14:22:21.237NULLNULLNULL
2三坐标8.0010system2019-04-23 14:22:21.237NULLNULLNULL
3烘干炉2.0010Admin2019-04-23 14:38:43.140NULLNULLNULL
4三坐标8.0010system2019-04-23 14:38:43.140NULLNULLNULL

1.2.5. 修改记录

UPDATE dbo.Dev
SET    ModifyUser = N'张三', ModifyDate = GETDATE()
WHERE  DevId = 2;
DevIdDevNameStockQtyFlagDevTypeCreateUserCreateDateModifyUserModifyDatememo
1烘干炉2.0010Admin2019-04-23 14:22:21.237NULLNULLNULL
2三坐标8.0010system2019-04-23 14:22:21.237张三2019-04-23 14:43:20.760NULL
3烘干炉2.0010Admin2019-04-23 14:38:43.140NULLNULLNULL
4三坐标8.0010system2019-04-23 14:38:43.140NULLNULLNULL

1.2.6. 删除记录

--有条件删除
DELETE dbo.Dev WHERE DevId=1
--清空表内所有数据，自增字段复位，并且不产生日志
TRUNCATE TABLE dbo.Dev

1.2.7. 注释和说明

--单行注释

/*
多行注释
*/

--添加表注释
EXECUTE sys.sp_addextendedproperty @name = N'MS_Description', @value = N'设备主数据', @level0type = N'USER',
                                   @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Dev',
                                   @level2type = NULL, @level2name = NULL;

--修改表注释
EXECUTE sys.sp_updateextendedproperty @name = N'MS_Description', @value = N'设备主数据', @level0type = N'USER',
                                      @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Dev',
                                      @level2type = NULL, @level2name = NULL;

--删除表注释
EXECUTE sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'USER', @level0name = N'dbo',
                                    @level1type = N'TABLE', @level1name = N'Dev', @level2type = NULL,
                                    @level2name = NULL;

--添加字段注释
EXECUTE sys.sp_addextendedproperty @name = N'MS_Description', @value = N'设备ID', @level0type = N'SCHEMA',
                                   @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Dev',
                                   @level2type = N'COLUMN', @level2name = N'DevId';

--更新字段注释
EXECUTE sys.sp_updateextendedproperty @name = N'MS_Description', @value = N'设备ID', @level0type = N'SCHEMA',
                                      @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Dev',
                                      @level2type = N'COLUMN', @level2name = N'DevId';

--删除字段注释
EXECUTE sys.sp_dropextendedproperty @name = 'MS_Description', @level0type = N'SCHEMA', @level0name = N'dbo',
                                    @level1type = N'TABLE', @level1name = N'Dev', @level2type = N'COLUMN',
                                    @level2name = N'DevId';

1.2.8. 存储过程和函数

/******************************************************************************
  
  *Copyright(C)
  *FileName:     p_ProcedureName
  *Author:
  *Version:      1.00
  *Date:         2018-12-15
  *Description:  存储过程描述
  *Others:
  *Function List:
  *History:

*******************************************************************************/

CREATE PROCEDURE [dbo].[p_ProcedureName]
  @parameter1 INT,
  @parameter2 NVARCHAR(50)
AS
BEGIN
  SET NOCOUNT ON;

  --存储过程内容

END;

1.2.9. 事务处理

SET XACT_ABORT ON;--设置事务是否整体回滚，非常重要，必须设置，系统默认值是OFF，BEGIN TRY不会捕捉低级错误（如未找到表名或字段名错误），如果设置未OFF发生低级错误时会造成事务未回滚锁表

--声明错误相关变量
DECLARE @ErrorCode INT; --错误代码
DECLARE @ErrorSeverity INT; --错误级别
DECLARE @ErrorState INT; --错误状态
DECLARE @ErrorText NVARCHAR(500); --错误内容

BEGIN TRY
  --开始事务
  BEGIN TRAN;

  /*
      增、删、改代码块

      此处遇到错误会自动跳到CATCH
  */

  --逻辑处理语句，抛出自定义错误
  SET @ErrorCode = -3;
  SET @ErrorSeverity = 16;
  SET @ErrorState = 1;
  SET @ErrorText = N'错误内容';

  --抛出自定义错误，会被CATCH捕获
  RAISERROR(@ErrorText, @ErrorSeverity, @ErrorState);

  --提交事务
  COMMIT TRAN;

  --返回0
  RETURN 0;
END TRY
BEGIN CATCH
  --回滚事务
  ROLLBACK TRAN;

  SET @ErrorCode = ERROR_NUMBER();
  SET @ErrorSeverity = ERROR_SEVERITY();
  SET @ErrorState = ERROR_STATE();
  SET @ErrorText = ERROR_MESSAGE();

  --抛出错误
  RAISERROR(@ErrorText, @ErrorSeverity, @ErrorState);

  RETURN @ErrorCode;
END CATCH;

1.2.10. 游标

--声明变量
DECLARE @DevId INT; --设备ID
DECLARE @DevName NVARCHAR(200);

--设备名称
--声明游标
DECLARE c_Dev CURSOR FOR SELECT DevId, DevName FROM dbo.Dev;

--打开游标，获取首行数据给变量
OPEN c_Dev;

FETCH NEXT FROM c_Dev
INTO @DevId, @DevName;

--从全局变量获取游标状态，0表示已到表尾
WHILE @@FETCH_STATUS = 0
BEGIN
  --处理过程
  SELECT @DevId, @DevName;

  FETCH NEXT FROM c_Dev
  INTO @DevId, @DevName;
END;

--关闭游标
CLOSE c_Dev;
--释放游标资源
DEALLOCATE c_Dev;
xwinds
关注
专栏目录
sqlserver离线安装包
11-01
sqlserver离线安装包
13. 查看数据库对象间的依赖关系
weixin_30342209的博客
10-07 448
在SQL Server中，(可编程)对象间的引用即依赖关系，有多种方式可以检查，随着版本变更，方式也有所不同。 父子关系的对象，不通过依赖关系来查询，比如： 1. 外键关系 use tempdb GO --drop table tb1,tb2 create table tb1 ( col1 int Primary key, col2 int ) insert into...
SQLServer 版本之八大方法搞清 "我是谁"
weixin_30553777的博客
12-15 1512
你正在使用 SQL Server 的哪个版本？ 贴士:作为一个SQL Server数据库管理者或维护、支持人员，应该会经常问自己这样一个问题：我当前SQL Server版本号是？当前版本已经有的累计更新、安全更新包有哪些？这么多包要选哪个？等等，会遇到类似心烦的问题。这里给大家梳理一下关于如何方便的获取SQL Server数据库版本信息，希望在日常运维中有所帮助。 介绍方法前，...
sqlserver版本区别
喜欢在细雨中散步
10-17 2024
10.0.1600其实就是SQL 2008 10.0.2531其实就是 SQL Server 2008 SP1 10.50.1600其实就是SQL 2008 R2 10.50.2500其实就是SQL 2008 R2 SP1
查一个表的所有外键
枫的专栏
04-21 3397
select a.name as 约束名, object_name(b.parent_object_id) as 外键表, d.name as 外键列, object_name(b.referenced_object_id) as 主健表, c.name as 主键列 from sys.foreign_keys A inner join sys.foreign_key_columns
SQLServer出现的问题
YYsixsixsix的博客
12-26 177
SQL Server问题解决方法 问题 解决方法
sql server2019安装包
最新发布
01-19
SQL Server 2019是Microsoft推出的一款关系型数据库管理系统，是SQL Server系列中的一个重要版本。它提供了强大的数据存储、...通过掌握SQL Server 2019的相关知识，可以有效提升数据库管理效率和数据驱动决策的能力。
Sql Server 2014 安装包
09-02
SQL Server 2014是微软推出的一款关系型数据库管理系统，它在企业级数据管理和分析领域扮演着重要的角色。此安装包包含两个主要组件：SQL Management Studio和SQL Server Express。 1. **SQL Management Studio ...
SQL server 的知识总结
07-04
自己总结，非常适合初学者对整个SQLserver知识的整体归纳，已经学习步骤。
sqlserver卸载工具.rar
04-10
"sqlserver卸载工具.rar"就是一个针对这一需求的资源包。 该压缩包文件包含以下几个部分： 1. **说明.txt**：这个文件通常会提供详细的步骤指导，解释如何使用卸载工具以及在卸载过程中需要注意的事项。它可能包含...
Linux环境下访问Windows环境下的ＳＱＬ　Ｓｅｒｖｅｒ数据库
03-21
结合实际需求，提出了一种在Linux 平台下用PHP访问WINDOWS平台上SQL Server 数据库的一种方法（基于一款共享软件— PHP FEDTDS ）。文中对相关的配置过程作了详细说明， 并附有程序实例
SQLServer帮助文档
04-06
SQLServer帮助文档
SQLserver2017安装文档（linux&windows;）
10-20
SQLserver2017 on（LInux &Windows;）安装指南， Step by Step，只看图，不说话
SQL Server OBJECT_ID() 函数
weixin_30586257的博客
09-27 197
OBJECT_ID 返回架构范围内对象的数据库对象标识号。 重要提示 　　使用 OBJECT_ID 不能查询非架构范围内的对象（如 DDL 触发器）。对于在 sys.objects 目录视图中找不到的对象，需要通过查询适当的目录视图来获取该对象的标识号。例如，若要返回 DDL 触发器的对象标识号，请使用 SELECT OBJECT_ID FROM sys.triggers WHERE nam...
sqlserver 查询表中的主键、外键列及外键表，外表中的主键列
weixin_34324081的博客
08-10 2040
1、获取主键信息 EXEC sp_pkeys @table_name='{0}' 2、获取外键 方法二 SELECT Field=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) , FKTable=object_name(b.rkeyid), FKKeyField=(SELECT name FRO...
SQL SERVER 中的 object_id()函数
weixin_30270889的博客
10-10 695
在SQLServer数据库中，如果查询数据库中是否存在指定名称的索引或者外键约束等，经常会用到object_id('name','type')方法，做笔记如下： ? 语法：object_id('objectname')或object('objectname','type') 作用：该函数会返回指定对象的ID值，可以在sysobjects表中进行验证。 其中objectname数据类...
sqlserver 根据表名查询 该表的主键是那些表的外键
笨笨CEO的博客
06-05 2659
select object_name(a.parent_object_id) '[表名]'  from sys.foreign_keys a  where a.referenced_object_id=object_id('表名')
SQL 2008升级SQL 2008 R2完全教程或者10.00.1600升级10.50.1600
热门推荐
Swarb的博客：低头做事，抬头做人
08-23 4万+
今天将由于需要就将我的SQL 2008升级到SQL 2008 R2. 说到为什么要升级是因为，从另一台机器上备份了一个数据库，到我的机器上还原的时候提示“System.Data.SqlClient.Sqlerror:该数据库是在运行版本10.50.2500的服务器上备份的，该版本与此服务器（运行版本10.00.1600）不兼容。请在支持该备份的服务器上还原数据库，或者使用与此服务器兼容的备份。”
SQL Server 各版本发布时间和开发代号
想娶媳妇的苗同学的博客
01-23 3766
SQL Server 各版本发布时间和开发代号 版本 年份 发布名称 代号 1 1989 年 SQL Server 1.0 无 4.21 1993年 SQL Server 4.21 无 6 1995年 SQL Server 6.0 SQL95 6.5 1996年 SQL Server 6.5 Hydra 7 1998年 SQL Server 7.0 Sphinx...
SQLServer基础知识详解
第一章介绍了SQLServer的基础知识，包括其定义和发展历程。SQLServer是一个用于存储、管理和检索数据的系统，支持各种企业级应用，提供了数据安全性、可靠性和高性能。它支持标准的SQL语言，使得用户能够通过结构化...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值