SQL server常用脚本

CREATE DATABASE TestData

GO

• 创建和查询数据库对象

-- 创建数据库

CREATE DATABASE TestData

GO

-- 切换数据库

USE TestData

GO

--创建数据库之后，你可以使用 SHOW DATABASES 语句查看：

-- 创建表

CREATE TABLE dbo.Products

(ProductID int PRIMARY KEY NOT NULL,

    ProductName varchar(25) NOT NULL,

    Price money NULL,

    ProductDescription varchar(max) NULL)

GO

--插入数据

-- Standard syntax

INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)

VALUES (1, 'Clamp', 12.48, 'Workbench clamp')

GO

--删除表中的所有行

TRUNCATE TABLE TestData.dbo.Products;

GO

-- 更新表

UPDATE dbo.Products

    SET ProductName = 'Flat Head Screwdriver'

    WHERE ProductID = 50

GO

-- The basic syntax for reading data from a single table

SELECT ProductID, ProductName, Price, ProductDescription

FROM dbo.Products

GO

--使用星号 (*) 选择表中的所有列。 星号用于临时查询。 在永久代码中，请提供列列表，使语句返回预测列，即使稍后会将新列添加到表中，也是如此。

-- Returns all columns in the table

-- Does not use the optional schema, dbo

SELECT * FROM Products

GO

-- Returns only two of the columns from the table

SELECT ProductName, Price

    FROM dbo.Products

GO

--where子句使用

SELECT ProductID, ProductName, Price, ProductDescription

FROM dbo.Products

    WHERE ProductID < 60

GO

--对 Price 列执行数学运算。 除非通过使用 AS 关键字提供一个名称，否则以此方式更改的列将没有名称

-- Returns ProductName and the Price including a 7% tax

-- Provides the name CustomerPays for the calculated column

SELECT ProductName, Price * 1.07 AS CustomerPays

FROM dbo.Products

GO

--select语句中的有用函数

      --字符串函数

       --日期和时间数据类型及函数

       --数学函数

       --文本与图像函数

--视图

--执行以下语句创建一个视图，该视图执行 Select 语句，并将产品的名称和价格返回给用户。

CREATE VIEW vw_Names

AS

SELECT ProductName, Price FROM Products;

GO

--视图的处理方式与表类似

--测试视图

SELECT * FROM vw_Names;

GO

--存储过程

--创建存储过程

--该存储过程对视图执行 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

-- 测试存储过程

EXECUTE pr_Names 10.00;

GO

• 配置数据库对象的权限

授予用户访问数据库的权限涉及三个步骤。 首先，创建登录名。 使用登录名，用户可以连接到 SQL Server 数据库引擎。 然后将登录名配置为指定数据库中的用户。 最后，授予该用户访问数据库对象的权限。

创建登录名：若要访问 数据库引擎，用户需要有登录名。 登录名可以将用户身份表示为 Windows 帐户或 Windows 组成员，登录名也可以是仅存在于 SQL Server 中的 SQL Server登录名。默认情况下，计算机上的管理员具有对 SQL Server的完全访问权限。我们需要一个具有更少特权的用户；因此，将在计算机上创建一个新的本地 Windows 身份验证帐户。此时，用户必须是计算机上的管理员。 然后授予该新用户访问 SQL Server 的权限。

创建新的 Windows 帐户

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

创建 SQL 登录名

在 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 必须访问的每个实例上创建登录名。

 备注：因为 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 都具有访问权限。

创建视图和存储过程

作为管理员，可以从 Products 表和 vw_Names 视图执行 SELECT，以及执行 pr_Names 过程；但是 Mary 不能。 若要授予 Mary 必要的权限，请使用 GRANT 语句。

授予对存储过程的权限

执行以下语句将 Mary 存储过程的 EXECUTE 权限授予 pr_Names 。

GRANT EXECUTE ON pr_Names TO Mary;

GO

在这种情况下，Mary 只能通过使用存储过程访问 Products 表。 如果您希望 Mary 能够对视图执行 SELECT 语句，则您还必须执行 GRANT SELECT ON vw_Names TO Mary。 若要删除对数据库对象的访问权限，请使用 REVOKE 语句。

 备注：如果表、视图和存储过程不是由同一架构拥有，则授予权限将变得更复杂。

关于 GRANT

必须具有 EXECUTE 权限才能执行存储过程。 必须具有 SELECT、INSERT、UPDATE 和 DELETE 权限才能访问和更改数据。 GRANT 语句还用于其他权限，如创建表的权限。

• 删除数据库对象

撤销存储过程权限

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

REVOKE EXECUTE ON pr_Names FROM Mary;

GO

删除权限

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

SQL复制

DROP USER Mary;

GO

1. 使用 DROP 语句删除 Mary 对此 SQL Server 2005 (9.x)实例的访问权限。

SQL复制

DROP LOGIN [<computer_name>\Mary];

GO

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

SQL复制

DROP PROC pr_Names;

GO

1. 使用 DROP 语句删除视图 vw_Names：

SQL复制

DROP VIEW vw_Names;

GO

删除表

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

SQL复制

DELETE FROM Products;

GO

1. 使用 DROP 语句删除 Products 表：

SQL复制

DROP TABLE Products;

GO

删除数据库

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

USE MASTER;

GO

DROP DATABASE TestData;

GO

“编写 Transact-SQL 语句”教程到此结束。 请记住，本教程只是简要概述，它并未介绍所用语句的所有选项。 设计和创建有效的数据库结构以及配置对数据的安全访问，需要比本教程中显示的数据库更复杂的数据库。

后续步骤

• SQL Server 数据库的备份和还原
• 维护计划
• sp_configure (Transact-SQL)

常用sql语句实例化：

sqlserver常用关键字：SELECT、INSERT、UPDATE、DELETE、ALTER、DROP、CREATE、USE、SHOW

--update

UPDATE table_name

SET column1 = value1, column2 = value2....columnN=valueN

[ WHERE  CONDITION ];

--ALTER TABLE

--ALTER TABLE 命令用于修改数据表。ALTER TABLE 可以用来修改数据表的字段，例如：

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

--也可以用来修改数据表的名称，例如：

ALTER TABLE table_name RENAME TO new_table_name;

Cmd操作SQL server

1. sqlcmd -s sa进入SQL server
2. 查看当前所有数据库：

Select name from sysdatabases

Go;

1. 查看当前数据库的所有表

Select name from sysobjects

Go

4、退出：exit