本文总结了 SQL Server 数据库操作方法，包括脚本操作、表结构修改、范式转换，以及 SQL 语法的 inner join、left join、right join 的详细使用方法。此外，还介绍了自定义函数实现、一对多关系的添加与更改，以及分页查询的实现。通过实例解释了各个概念的应用，旨在提高数据库管理与设计能力。
于自己平日遇到的SQL SERVER 所集结过的问题笔录。
1、脚本操作(部分是具体实例)
1-1、表重名 EXEC sp_rename '原来表名称','新的表名称'
1-2、添加列 ALTER table [tablename] ADD[colname] [type]
1-3、删除列 ALTER table [tablename] DROP [colname] [type]
1-4、修改列 ALTER table [tablename] ALTER [colname] [newtype]
1-5、删除表 DROPtable [tablename]
2-1、创建数据库
CREATE DATABASE stusystem
ON
(
NAME=stusystem_DAT,
FILENAME='E:\stusystem_DAT.mdf',
SIZE=20MB,
MAXSIZE=40MB,
FILEGROWTH=5%
),
(
NAME=stusystem_DAT2,
FILENAME='E:\stusystem_DAT2.ndf',
SIZE=20MB,
MAXSIZE=40MB,
FILEGROWTH=5%
)LOG ON
(
NAME=stusystem_LOG,
FILENAME='E:\stusytem_LOG.ldf',
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=1MB
)
3-1、创建表
CREATE TABLE student
(
stuid int PRIMARY KEY,
stunumber char(6) UNIQUE NOT NULL,
stuname varchar(20) NOT NULL,
stupassword varchar(20) DEFAULT '123456' NOT NULL,
stusex char(2) NOT NULL,
stubirthday datetime NOT NULL,
claid int NOT NULL,
CONSTRAINT stu_cla FOREIGN KEY (claid) REFERENCE class(claid)
)
2、范式解析
第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。544
假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)
即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题：
(1) 数据冗余：
同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
(2) 更新异常：
若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
(3) 插入异常：
假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。
(4) 删除异常：
假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
把选课关系表SelectCourse改为如下三个表：
学生：Student(学号, 姓名, 年龄)；
课程：Course(课程名称, 学分)；
选课关系：SelectCourse(学号, 课程名称, 成绩)。
这样的数据库表是符合第二范式的， 消除了数据冗余、更新异常、插入异常和删除异常。
另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。
所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：
关键字段 → 非关键字段x → 非关键字段y
假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，关键字为单一关键字"学号"，因为存在如下决定关系：
(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)
这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
(学号) → (所在学院) → (学院地点, 学院电话)
即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。
它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。
把学生关系表分为如下两个表：
学生：(学号, 姓名, 年龄, 所在学院)；
学院：(学院, 地点, 电话)。
这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。
鲍依斯-科得范式（BCNF）：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式。
假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
(仓库ID, 存储物品ID) →(管理员ID, 数量)
(管理员ID, 存储物品ID) → (仓库ID, 数量)
所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
(仓库ID) → (管理员ID)
(管理员ID) → (仓库ID)
即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：
(1) 删除异常：
当仓库被清空后，所有"存储物品ID"和"数量"信息被删除的同时，"仓库ID"和"管理员ID"信息也被删除了。
(2) 插入异常：
当仓库没有存储任何物品时，无法给仓库分配管理员。
(3) 更新异常：
如果仓库换了管理员，则表中所有行的管理员ID都要修改。
把仓库管理关系表分解为二个关系表：
仓库管理：StorehouseManage(仓库ID, 管理员ID)；
仓库：Storehouse(仓库ID, 存储物品ID, 数量)。
这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。
3、sql语法：inner join on, left join on, right join on详细使用方法
a、语法
select * FROM table1 INNER JOIN table2 ON table1 . field1 compopr table2 . field2
INNER JOIN 操作包含以下部分：
| 部分 | 说明 |
| table1, table2 | 要组合其中的记录的表的名称。 |
| field1，field2 | 要联接的字段的名称。如果它们不是数字，则这些字段的数据类型必须相同，并且包含同类数据，但是，它们不必具有相同的名称。 |
| compopr | 任何关系比较运算符：“=”、“<”、“>”、“<=”、“>=”或者“<>”。 |
b、操作实例
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
实验如下:
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录. 还有就是inner join 可以结合where语句来使用 如： select * from A innerjoin B on A.aID = B.bID where b.bname='2006032401' 这样的话 就只会放回一条数据了
自定义函数
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 05/31/2012 19:39:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
@SplitString text, -- 如果要传入NText类型，下面需要相应的修改，注释行为NText下同
@Separator varchar(2) = ','-- NVarChar(2) = N','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000) -- NVarChar(4000)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText varchar(8000);-- NVarChar(4000)
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=datalength(@SplitString)+1;--DATALENGTH(@SplitString)/2
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value])
VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
select * from [dbo].Split('2$4$的方式地方','$')
select reverse(left(reverse('2$4$的方式地方'),charindex('$',reverse('2$4$的方式地方'),1)-1))
一对多添加
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Add]
(
@para_Id INT OUTPUT,
@para_Xml NTEXT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc output, @para_Xml
IF @@Error<>0
BEGIN
RETURN
END
BEGIN TRANSACTION
INSERT INTO TEE
(
First_Name,
Last_Name,
Mid_Name
)
(
SELECT
First_Name,
Last_Name,
Mid_Name
FROM OpenXML(@iDoc,'/EE', 1)
WITH
(
First_Name varchar(20) '@First_Name',
Last_Name varchar(20) '@Last_Name',
Mid_Name varchar(20) '@Mid_Name'
)
)
IF @@Error<>0
BEGIN
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRANSACTION
SET @para_Id=0
RETURN
END
SET @para_Id=@@IDENTITY
INSERT INTO ERole
(
E_Id,
Role_Id,
Created_By,
Created_At
)
(
SELECT
@para_Id,
Role_Id,
1,
GETDATE()
FROM OpenXML(@iDoc,'/EE/Role/RoleItem', 1)
WITH
(
Role_Id INT '@Role_Id'
)
)
IF @@Error<>0
BEGIN
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRANSACTION
SET @para_Id=0
RETURN
END
EXEC sp_xml_removedocument @iDoc
COMMIT TRANSACTION
END
一对多更改
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Update]
(
@para_ID INT,
@para_Xml NTEXT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc output, @para_Xml
IF @@Error<>0
BEGIN
RETURN
END
BEGIN TRANSACTION
UPDATE el
SET
el.First_Name = T.First_Name,
el.Last_Name=T.Last_Name,
el.Mid_Name=T.Mid_Name
FROM
EmployeeList el INNER JOIN
(
SELECT
Employee_ID,
First_Name,
Last_Name,
Mid_Name
FROM OpenXML(@iDoc,'/EE', 1)
WITH
(
Employee_ID INT '@Employee_ID',
First_Name varchar(20) '@First_Name',
Last_Name varchar(20) '@Last_Name',
Mid_Name varchar(20) '@Mid_Name'
)
)T ON T.Employee_ID = el.Employee_ID
IF @@Error<>0
BEGIN
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM EmployeeRole WHERE Employee_Id = @para_ID
INSERT INTO EmployeeRole
(
Employee_Id,
Role_Id,
Created_By,
Created_At
)
(
SELECT
@para_Id,
Role_Id,
1,
GETDATE()
FROM OpenXML(@iDoc,'/EE/Role/RoleItem', 1)
WITH
(
Role_Id INT '@Role_Id'
)
)
IF @@Error<>0
BEGIN
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRANSACTION
RETURN
END
EXEC sp_xml_removedocument @iDoc
COMMIT TRANSACTION
END
分页查询
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_EmployeeList_Pager]
-- Add the parameters for the stored procedure here
@param_page_number INT,
@param_pageSize INT,
@param_orderBy NVARCHAR(50),
@param_Direction VARCHAR(10),
@param_Total INT OUTPUT,
@param_SearchName NVARCHAR(50),
@param_searchType INT,
@param_searchAccount NVARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(4000)
CREATE TABLE #TempList(
Employee_ID INT NULL,
First_Name varchar(20) NULL,
Last_Name varchar(20) NULL,
Mid_Name varchar(20) NULL,
Name varchar(100) NULL
) ON [PRIMARY]
SET @Sql = '
INSERT INTO
#TempList
SELECT
Employee_ID,
First_Name,
Last_Name,
Mid_Name,
First_Name + '' ''+Last_Name
FROM
EmployeeList
WHERE 1 = 1
'
IF(@param_SearchName <> '')
BEGIN
SET @Sql +=' AND First_Name + '' ''+Last_Name LIKE ''%'+ @param_SearchName + '%'''
END
IF(@param_searchType = 1)
BEGIN
SET @Sql += ' AND Is_Active = 1'
END
IF(@param_searchType = 2)
BEGIN
SET @Sql += ' AND Is_Active = 0'
END
EXEC sp_executesql @sql
SET @Sql='SELECT * FROM(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY '+@param_orderBy +' '+@param_Direction+') AS rownum
From
#TempList
) as D
WHERE rownum BETWEEN '+CAST((@param_page_number-1)*@param_pageSize+1 AS VARCHAR)+' AND ' + CAST( @param_page_number*@param_pageSize AS VARCHAR)
EXEC sp_executesql @sql
SELECT @param_Total = COUNT(1) FROM #TempList
END
待续中...
扫一扫
2407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
