SQL Server持续学习

SQL Server 学习

---

创建数据库

与许多 Transact-SQL 语句一样，CREATE DATABASE 语句具有一个必需参数：数据库的名称。CREATE DATABASE 还具有许多可选参数，如希望放置数据库文件的磁盘位置。在您执行不带可选参数的 CREATE DATABASE 时，SQL Server 使用其中许多参数的默认值。

CREATE DATABASE TestData
GO

---

创建表

若要创建表，您必须提供该表的名称以及该表中每个列的名称和数据类型。指出每个列中是否允许空值，也是一种很好的做法。

大多数表有一个主键，主键由表的一列或多列组成。主键始终是唯一的。数据库引擎 将强制实施以下限制：表中的任何主键值都不能重复。

将下面的代码输入到查询编辑器窗口中

USE master;
GO

--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
    DROP DATABASE TestData;
END

--Create a new database called TestData.
CREATE DATABASE TestData;
Press the F5 key to execute the code and create the database.

(上面语句表达的意思是如果系统数据库存在名为testdata的数据库记录那就删除这个数据库，再创建这个数据库)

创建表

先切换到某个数据库中

USE TestData
GO

在查询编辑器窗口中，键入以下代码，并执行它以创建一个名为 Products 的简单表。该表中各列的名称为 ProductID、ProductName、Price 和 ProductDescription。ProductID 列是表的主键。int、varchar(25)、money 和 text 都是数据类型。当插入或更改行时，只有 Price 和 ProductionDescription 列可以不包含数据。此语句包含称为架构的可选元素 (dbo.)。架构是拥有表的数据库对象。如果您是管理员，则 dbo 是默认架构。dbo 代表数据库所有者。

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL)
GO

---

数据类型相关知识

在 SQL Server 中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。

SQL Server 提供系统数据类型集，该类型集定义了可与 SQL Server 一起使用的所有数据类型，可以使用 Transact-SQL 或 Microsoft .NET Framework 定义自己的数据类型。别名数据类型基于系统提供的数据类型。有关别名数据类型的详细信息，用户定义类型从使用 .NET Framework 支持的编程语言之一创建的类的方法和运算符中获取它们的特征。

当两个具有不同数据类型、排序规则、精度、小数位数或长度的表达式通过运算符进行组合时，结果的特征由以下规则确定：

结果的数据类型是通过将数据类型的优先顺序规则应用到输入表达式的数据类型来确定的。

（数据类型有优先级）

当结果数据类型为 char、varchar、text、nchar、nvarchar 或 ntext 时，结果的排序规则由排序规则的优先顺序规则确定

结果的精度、小数位数及长度取决于输入表达式的精度、小数位数及长度。

精确数值	Unicode 字符串
近似数字	二进制字符串
日期和时间	其他数据类型
字符串

在 SQL Server 中，根据其存储特征，某些数据类型被指定为属于以下组：

大值数据类型：varchar(max), nvarchar(max), and varbinary(max)

大型对象数据类型：text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml

精确数值

bigint	numeric
bit	smallint
decimal	smallmoney
int	tinyint
money

(精确数据类型那么多？)

Data type	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Byte

（好吧）

那这个decimal呢

• decimal[(*p[*,*s]*)] and numeric[(*p[*,*s]*)]
  （ p****=**，**s ==****固定精度和比例数字.使用最大精度时，有效值从 - 10^38 +1 到 10^38 - 1。十进制的 ISO 同义词是 dec 和 dec（p， s）。数字在功能上相当于十进制。

• p (precision)
  可存储的十进制数字的最大总数，在小数点左侧和右侧。精度必须为从 1 到 38 的最大精度的值。默认精度为 18。

• s (scale)
  可以存储在小数点右侧的最大十进制数字数。缩放必须是从 0 到 p 的值。只有在指定精度时，才能指定比例。默认比例为 0;因此，0 <= s <= p.最大存储大小因精度而异。

  精度	存储字节
  1 - 9	5
  10-19	9
  20-28	13
  29-38	17

（。。。挺有特色的）

money and smallmoney

Data type	Range	Storage
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

近似数字（就是浮点数）

float	real

用于浮点数数据的近似数字数据类型。浮点数据是近似值;因此，并不是数据类型范围内的所有值都可以准确表示。

数据类型	范围	存储
浮动	- 1.79E+308 至 -2.23E-308、0 和 2.23E-308 至 1.79E+308	取决于 n 的值
真正	- 3.40E × 38 至 -1.18E - 38、0 和 1.18E - 38 至 3.40E = 38	4 字节

Date and Time

date	datetimeoffset
datetime2	smalldatetime
datetime	time

Character Strings

[char]	[varchar]
[text]

Unicode Character Strings

[nchar]	[nvarchar]
[ntext]

Binary Strings

[binary]	[varbinary]
[image]

Other Data Types

[cursor]	[timestamp]
[hierarchyid]	[uniqueidentifier]
[sql_variant]	[xml]
[table]

上面结合java的数据类型概括说sql server中没有short,long,byte只有tinyint,smallint,int,bigint，还有模拟自然数的decimal，还有money,samilmoney，还有日期date，时间time，也有日期时间datatime，字符串的话就是char,大文本可以用text,二进制文件的话就是binary,图片的话用image（个人建议将图片二进制文件转成字符串存入，在java中使用base64,可以放置实际图片的路径也是字符串，后者更好，当然要看实际情况）

---

插入和更新表中的数据

创建 Products 表，可以通过使用 INSERT 语句向该表中插入数据了。插入数据后，将通过使用 UPDATE 语句更改行的内容。您将使用 UPDATE 语句的 WHERE 子句，以限制对单个行的更新。这四条语句将输入以下数据。

ProductID	ProductName	价格	ProductDescription
1	Clamp	12.48	Workbench clamp
50	Screwdriver	3.17	Flat head
75	Tire Bar		Tool for changing tires.
3000	3mm Bracket	.52

INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

以下语句显示如何通过在字段列表（在圆括号中）中和值列表中均切换 ProductID 和 ProductName 的位置，更改提供参数的顺序。

-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO

以下语句演示，只要值是按正确顺序列出的，列的名称就是可选的。此语法很常见，但是建议不要使用它，因为其他人了解您的代码可能会更困难。为 Price 列指定了 NULL，因为还不知道此产品的价格。

-- Skipping the column list, but keeping the values in order
INSERT dbo.Products
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO

只要在默认架构中访问和更改表，架构名称就是可选的。由于 ProductDescription 列允许 Null 值，而且没有提供值，因此可以从语句中完全删除 ProductDescription 列的名称和值。

-- Dropping the optional dbo and dropping the ProductDescription column
INSERT Products (ProductID, ProductName, Price)
    VALUES (3000, '3mm Bracket', .52)
GO

(总结插入记录时如果有创建主键，利用主键自增如果主键是精确的数字型，完全可以忽略主键只要负责写其他字段，相应的值在values里面对应写，字段的顺序可以乱，但是与字的对应必须正确，另一个可以不写字段直接在values里面写值但是顺序必须与字段排列一样)

可以在返回列中的值时使用它们。以下示例对 Price 列执行数学运算。除非通过使用 AS 关键字提供一个名称，否则以此方式更改的列将没有名称。

SELECT ProductName, Price * 1.07 AS CustomerPays
    FROM dbo.Products
GO

---

配置数据库对象的权限

授予用户访问数据库的权限涉及三个步骤。首先，创建登录名。使用登录名，用户可以连接到SQL Server 数据库引擎。然后将登录名配置为指定数据库中的用户。最后，授予该用户访问数据库对象的权限。本课介绍了这三个步骤，并介绍了如何将视图和存储过程创建为对象。

创建登录名

若要访问数据库引擎，用户需要有登录名。登录名可以按 Windows 帐户或 Windows 组成员表示用户身份，登录名也可以是仅存在于 SQL Server 中的 SQL Server 登录名。应该尽可能使用 Windows 身份验证。

默认情况下，计算机上的管理员具有对 SQL Server 的完全访问权限。在本课中，我们需要一个具有更少特权的用户；因此，您将在计算机上创建一个新的本地 Windows 身份验证帐户。为此，您必须是计算机上的管理员。然后您将授予该新用户访问 SQL Server 的权限。下列说明适用于 Windows XP Professional。

创建新的window账户

1. 单击**“开始”，单击“运行”，在“打开”框中，键入 %SystemRoot%\system32\compmgmt.msc /s，再单击“确定”**打开“计算机管理”程序。
2. 在**“系统工具”下，展开“本地用户和组”，右键单击“用户”，再单击“新建用户”**。
3. 在**“用户名”**框中，键入 Mary。
4. 在**“密码”和“确认密码”框中，键入强密码，再单击“创建”**创建新的本地 Windows 用户。

创建登陆名

在 SQL Server Management Studio 的查询编辑器窗口中，键入并执行以下代码（将 computer_name 替换为您计算机的名称）。FROM WINDOWS 指示 Windows 将对用户进行身份验证。除非连接字符串指示其他数据库，否则可选的 DEFAULT_DATABASE 参数将 Mary 连接到 TestData 数据库。此语句引入了分号作为 Transact-SQL 语句的可选结束符。

CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

这将授权通过计算机的身份验证的用户名 Mary 访问此 SQL Server 实例。如果在计算机上存在多个 SQL Server 实例，则您必须在 Mary 必须访问的每个实例上创建该登录名。

(computer_name是计算机名，Mary是用户账户)

---

授予访问数据库的权限

现在 Mary 具有访问此 SQL Server 实例的权限，但是没有访问数据库的权限。在您授权她作为数据库用户之前，她甚至无权访问其默认数据库 TestData。

若要授予 Mary 访问权限，请切换到 TestData 数据库，再使用 CREATE USER 语句将她的登录名映射到名为 Mary 的用户。

在数据库中创建用户

键入并执行下列语句（将 computer_name 替换为您计算机的名称），以授予 Mary 访问 TestData 数据库的权限

USE [TestData];
GO

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

现在，对于 SQL Server 和 TestData 数据库，Mary 都具有访问权限。

创建视图和存储过程

---

既然 Mary 可以访问 TestData 数据库，您可能希望创建一些数据库对象（如视图和存储过程），再将它们的访问权限授予 Mary。视图是存储的 SELECT 语句，而存储过程是以批处理方式执行的一条或多条 Transact-SQL 语句。

视图像表那样进行查询，但不接受参数。存储过程比视图更复杂。存储过程可以同时具有输入参数和输出参数，并可以包括控制代码流的语句，如 IF 和 WHILE 语句。将存储过程用于数据库中的所有重复操作，是一个良好的编程做法。

在此示例中，您将使用 CREATE VIEW 创建一个视图，该视图仅选择 Products 表中的两列。然后，您将使用 CREATE PROCEDURE 创建一个存储过程，该存储过程接受价格参数，并仅返回价格小于指定参数值的那些产品。

创建视图

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO

测试视图

select * from vw_Names;

（这个视图就是被创建的sql语句，这个语句是生成一张表，所以完全可以用select * from来使用，换一句话说只要是有这种结果是表结构都可以用）

---

创建存储过程

以下语句创建一个名为 pr_Names 的存储过程，接受名为 @VarPrice、数据类型为 money 的输入参数。该存储过程打印与输入参数（已从 money 数据类型更改为 varchar(10) 字符数据类型）串联的语句 Products less than。然后，该存储过程对视图执行 SELECT 语句，将输入参数作为 WHERE 子句的一部分进行传递。这将返回价格小于输入参数值的所有产品。

CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO

测试存储过程

若要测试存储过程，请键入并执行以下语句。存储过程应该返回在第 1 课中向 Products 表中输入的、其价格小于 10.00 的两个产品的名称

EXECUTE pr_Names 10.00;
GO

（存储过程有点类似于java中的方法，它是以批处理的方式来处理一条或多条语句，view有点类似于模型）

---

删除数据库对象

在删除对象之前，请确保使用正确的数据库：

USE TestData;
GO

使用 REVOKE 语句删除 Mary 对存储过程的执行权限：

REVOKE EXECUTE ON pr_Names FROM Mary;
GO

使用 DROP 语句删除 Mary 对 TestData 数据库的访问权限：

DROP LOGIN [<computer_name>\Mary];
GO

使用 DROP 语句删除存储过程 pr_Names：

DROP PROC pr_Names;
GO

使用 DROP 语句删除视图 vw_Names：

DROP View vw_Names;
GO

使用 DELETE 语句删除 Products 表中的所有行：

DELETE FROM Products;
GO

使用 DROP 语句删除 Products 表：

DROP Table Products;
GO

正使用 TestData 数据库时，无法删除该数据库；因此，请首先将上下文切换到其他数据库，再使用 DROP 语句删除 TestData 数据库

USE MASTER;
GO
DROP DATABASE TestData;
GO