SQL Server 编程入门经典(3)之T-SQL基本语句

本章内容简介：

• 如何从数据库检索数据(SELECT)
• 如何向表中插入数据(INSERT)
• 如何适当更新数据(UPDATE)
• 如何删除表中数据(DELETE)

3.1 基本SELECT语句

 如果你在此之前没有使用过SQL语言，或者还未真正理解它，那么注意学习本章的内容SELECT语句极其语法结构是SQL Server执行全部命令的基础。SELECT 语句的语法规则如下：

SELECT [ALL|DISTINCT]  [TOP (<expression>) [PERCENT] [WITH TIES] ] <column list> [FROM <source table(s)/view>] [WHERE <restrictive condition>]

[GROUD BY<column name or expression using a column in the SELECT list>]

[HAVING <restrictive condition based  on the GROUP BY results>]

[ORDER BY <column list>]

[[FOR XML {ROW|AUTO|EXPLICIT|PATH[(<element>)]},XMLDATA][,ELEMENTS][,BINARY base 64]

[OPTION (<query hint>,[,.....n])]

上述语法结构很复杂，以下将依次介绍。

3.1.1 SELECT语句与FROM子句

动词SELECT是整条语句的谓语部分，告诉SQL Server执行什么操作。SELECT 表示仅够读取数据信息。而不能修数据信息。所选择的内容紧跟SELECT 后面表达式或列列表确定。稍候将介绍相关示例。接着，要加入更具体的限制。如SQL server 从那里读取数据。FROM语句指定要读取的数据所在的一个表或几个表名称。了解这些概念。就可以轻松建立一条基本SQL Server 的SELECT语句。启动SQL Server Management Studio，看下下面这条简单的SELECT语句。

SELECT * FROM INFORMATION_SCHEMA.TABLES;

让我们来看一下您在此请求的内容。你已经请求SELECT 信息；当工作在SQL server Management Studio中时。您也可以将该语句认为是显示所选择的信息。其中符号“*”看上去很奇怪。它是一个通配符，实际功能与其他地方使用的含义相同。使用“SELECT *”可以告诉T-SQL你想要选择所有列。然后FROM 字句表示要输出的信息的来源，这里是 INFORMATION_SCHEMA.TABLES

试一试   使用SELECT语句

让我们使用更多的使用SELECT 语句。将AdventureWorks设置当前数据库。回顾第二章所学的内容。只需要在Management Studio的查询窗口顶部工具栏下拉列表选择AdventureWorks条目或者在查询编辑器编写USE AdventureWorks  GO 语句执行既可。选择AdventureWorks数据库之后，先查看该数据库一些实际数据。使用下面语法查询：

SELECT * FROM Sales.Customer;

在“查询”窗口中输入上面语句，在F5执行，查看SQL Server返回结果。该查询列出当前数据（AdventureWorks 数据库）中Sales.Customer表中所有列每一行的信息。如果没有更改任何数据库设置，则在单击“消息”选项卡后，会显示如下信息：

(19820 行受影响)

SELECT 语句告诉“查询”窗口要完成什么工作，而符号“*”表示所要的信息(“*”表示所有的列)，然后在FROM字句。FROM字句定义数据来源。FROM之后可以连接一个表或者多个表，在上面查询中，所有数据来源都来自与Sales.Customer表，下面语句将表示更具体信息，想要按照姓名显示所有的客户列表：

SELECT LastName FROM Person.Contact;

返回结果：

Abbas

abel

..........

Zwilling

注意，为了简化起见，中间行使用了省略号，实际返回19 972行数据。因为显示每个客户的姓名，所以选择了所有的姓名。

注意：许多SQL编码人员养成了简化查询语句的习惯，通常在选择条件使用使用通配符"*"来选择所有列，这一习惯要改掉！输入“*”比输入想要的列名要节省时间，但检索数据也比实际需要的多。此外SQL Server必须计算“*”表示多少列以及那些列，这会降低应用程序的网络使用性能。在过去，我们必须完整输入（因此必须全部记住）想要每个列名，现在SSMS中内置了IntelliSense，因此你只需记住前几个字符。简而言之，良好的规则就是选择需要多少列就现在多少列。

3.1.2 WHERE 字句

接下来介绍条件WHERE语句，WHERE语句主要用来设定返回记录总数。在3.1.1 SELECT 查询中返回数据量太大。19 972行数据，在返回结果中也发费很长的时间，在实际中根本不需要那么多数据。只需要有用的数据。如查找一个姓名为Able的客户信息。即WHERE字句，WHERE字句跟在FROM 字句之后，它定义了要显示的记录必须满足的条件。下面查询条件是LastName=‘Abel’的客户：

SELECT * FROM Person.Contact WHERE LastName='Abel';

针对AdventrueWorks数据库运行该查询语句，返回结果如下:

ContactID           NameStyle       Title   FristName       .......

20                      0                   Ms.      NULL              ........

(1 行受影响)

上面返回结果正好是Person.Contact WHERE LastName='Abel'的信息

下面列出WHERE字句中可以使用的所有运算符

3.1.3  ORDER BY 字句

在前面运行几个实例中，多行输出结果都是按照字母顺序A-Z排序。输出结果是偶然吗，答案否定的，SQL Server 默认一个排序输出从小到大输出。

我们如何修改执行如下代码：

SELECT LastName FROM Person.Contact;--对应结果1

SELECT LastName FROM Person.Contact ORDER BY LastName DESC;--结果2

返回结果：

结果1                        结果2

Achong                     Zwlling

Abel                          Zwlling

........                        ..........

Zwlling                     Achong

（19 972 行受影响）

我们在看一个多ORDER BY 字段排序示例；SQL Server 查询语句如下：

SELECT  OrderQty,SalesOrderID  FROM Sales.SalesOrderDetail ORDER BY OrderQty DESC,SalesOrderID DESC

返回结果：

OrderQty       SalesOrderID

...........　　　............

34                47065

34                46647

34                46611

...........         ..........

1                  43659

(121 317 行受影响)

对于上述查询语句来说没有一点实际意义，通过观察发现有ORDER BY 子句按多列排序。实现方法用逗号分开排序的列。这里先按照OrderQty降序排序，在基于SalesOrderID降序排序。

注意：通常基于返回的一列来对结果进行排序，但值的注意是ORDER BY 字句可以基于查询使用的任何表中的任意列来排序，而不管是否在SELECT列表中，这一点和WHERE字句过滤一样。

3.1.4 使用 GROUP BY 子句聚合数据

回顾前面学习基本查询语法GROUP BY应该在ORDER BY 排序前面，为何这里先介绍ORDER BY 排序在介绍？原因有下2点：

• ORDER BY 子句比GROUP BY 子句更常用，因此我就放在前面讲ORDER BY语句。你要理解，
• 只要按照SQL Server规定的规则，就可以灵活FROM字句后的所以字句。

GROUP BY 子句用于聚合信息。下面介绍没有使用GROUP BY 子句简单的查询，假设你想知道一组给订单中零件订购数。

SELECT  OrderQty,SalesOrderID  FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660,43670,43672);

产生结果集：

OrderQty    SalesOrderID

1               43660
1               43660
1               43670
2               43670
2               43670
1               43670
6               43672
2               43672
1               43672

(9 行受影响)

尽管查询了3个订单，但可以得到订单信息，这时候可以使用计算器，也可以使用GROUP BY 字句和聚合函数。这里将使用SUM()函数：

SELECT  SUM(OrderQty) AS TotalOrderQty,SalesOrderID  FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660,43670,43672) GROUP BY SalesOrderID

产生结果集：

TotalOrderQty  SalesOrderID

2                43660
6                43670
9                43672

(3条受影响)

注意：在使用GROUP BY 子句时候，SELECT 列表中所有列必须是聚合列（SUM、MIN/MAX、AVG等）或是GROUP BY子句中包含的列，同样，如果在SELECT 列表中使用聚合列，SELECT 列表必须只包含聚合列。否则必须有一个GROUP BY子句。

聚合函数：VAG函数计算平均值，SUM函数计算总和，MAX计算最大值，MIN计算最小值，COUNT(列|*)计算返回行数

3.1.5 使用HAVING 子句给分组设置条件

HAVING子句仅能够使用与GROUP BY 子句查询语句中。WHERE子句应用于每一行（在变成一个组某一部分之前），而HAVING子句用于分组聚合值。

我们把3.1.4聚合子句做下修改，查询零件总数大于6的订单ID。

SELECT  SUM(OrderQty) AS TotalOrderQty,SalesOrderID  FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43660,43670,43672) GROUP BY SalesOrderID   HAVING  SUM(OrderQty)>=6

产生结果集：

TotalOrderQty  SalesOrderID

6                43670
9                43672

(2条受影响)

3.2  使用 INSERT 语句添加数据

现在对对SELECT 有基本了解，接下来开始讨论SQL Server的另一个主要问题，如何添加数据到数据库中。可以使用INSERT来添加数据，INSERT基本语法如下：

 INSERT [TOP (<expression> )[PERCENT] [INTO] <table object>

[(<column listt>)][OUTPUT <output clause>]

{VALUES(<data value>)[,(datavalues)][,....n]|<table source>|EXEC <procedure>|DEFAULT VALUES}

上面语法是不是看起来很复杂，简化语法：

INSERT INTO <tables object> [(<column list>)] VALUES(<data value>)[,(datavalues)][,....n]

在该结构中,INSERT为该语句的实际操作。告诉SQL Server执行什么功能，而INSERT后面仅说明操作的信息。INTO关键词无真正的含义，唯一目的是增强整个语句的可读性。INTO关键词完全可选。这里强烈建议在语句加入该关键词，因为增强代码可读性，不加入INTO关键词却省事，单读起来有点奇怪-用不用INTO关键词取决你自己。在现在为止，该语句语法比较简单。接下来列列表(column list)部分更困难。显示列列表为可选项，但在不提供列列表要特别的谨慎。如果是不显示列，假定INSERT每个值与表中一一相对应。最后要插入的值，有二种方式可以插入，这里介绍显示提供数据来插入一行。要提供插入值，需要使用VALUES关键词，圆括号各插入值之间用逗号分开。插入值列表项数必须与列表中列数完全匹配，插入值的数据类型必须能匹配或隐式转换成对应列数的数据类型。

先来做些练习，要练习INSERT、UPDATE和DELETE操作首先创建用于表操作的数据库和数据表需要运行一些未讨论的语句。读者暂且不为其内容担忧，因为后面章节详细讨论它们。可以自行输入这些代码：

CREATE DATABASE LearnData
ON
(
   NAME='LearnData.mdf',
   FILENAME='E:\study\LearnData.mdf',
   SIZE=100MB,
   MAXSIZE=1024MB,
   FILEGROWTH=100MB
)
LOG ON
(
   NAME='LearnData_log.ldf',
   FILENAME='E:\study\LearnData_log.ldf',
   SIZE=100MB,
   MAXSIZE=1024MB,
   FILEGROWTH=100MB
)
USE LearnData
GO
CREATE TABLE Teacher
(
  ID           INT  PRIMARY KEY,
  [Name]       NVARCHAR(20)  NOT NULL,
  Age          INT  CHECK(Age>0),
  BirthDay     DateTime 
)
CREATE TABLE Student
(
    StudentID    INT IDENTITY(1,1) PRIMARY KEY, 
    [Name]       NVARCHAR(20)  NOT NULL,
    [ClassID]    INT  NOT NULL
)
CREATE TABLE ClassRoom
(
   ID           INT IDENTITY(1,1) PRIMARY KEY,
   [Name]       NVARCHAR(20) 
)
INSERT INTO Teacher values(1,'Test',20,'2011-02-11');

SELECT * FROM Teacher WHERE ID=1

SQL Server 假如要插入数据与表每一列对应，插入语句忽略列可选项，第一个示例如下所示：

INSERT INTO Teacher values(1,'Test',20,'2011-02-11');

第一次执行返回结果：(1 行受影响)

假如在执行一次就会返回结果：消息 2627，级别 14，状态 1，第 1 行违反了 PRIMARY KEY 约束 'PK__Teacher__0519C6AF'。不能在对象 'dbo.Teacher' 中插入重复键。语句已终止。

为什么该语句第一次能执行，第二次不能执行呢？其原因本表的ID字段为主键，该字段值不允许重复。只要改变主键值，其他内容不变，就可以插入到新的一行。再有关联数据库设计和约束中会更详细介绍主键可以使用下列语句检查刚插入的数据：

SELECT * FROM Teacher WHERE ID=1;

返回结果正是刚插入的数据：

ID          Name            Age          Birthday

1          Test              20            2011-02-11 00:00:00.000

(1 条受影响) 

在对插入给定列语句向稍微做下修改：

INSERT INTO Teacher(ID,Name,Age) values(2,'Test_2',20);

注意，对以上语句插入的数据行做了二出改动。第一，更改了主键值，这样插入数据不会出错。第二，省略了与Birthday列对应值。这是因为在列列表已经忽略了该列。有几种情况可以忽略列列表中列，而且不能在INSERT语句中提供该列对应的插入数据。下面对SELECT语句稍作修改，在次运行并验证插入值：

SELECT * FROM Teacher WHERE ID=2;

返回结果有点不同正是刚插入的数据：

ID          Name            Age          Birthday 

2           Test               20            NULL

(1 条受影响) 

可以注意到，为提供列为null值。为了省略的列插入了默认值。注意忽略列必须设置为默认值或者NULL，顾名思义它允许该列有NULL值。后面会大量介绍列是否可赋值为NULL值，现在只要记住有些列可以设置为NULL值，有些列不能设置为NULL值，在插入数据时，对于运行设置为NULL的值的列，可以省略插入数据。如果列不能设置为NULL值，则必须为以下3种情况之一，否则系统提示出错信息，INSERT命令被拒绝执行。

• 列定义为默认值。默认值在是该列未提供插入值时候会自动插入一个常量值。后面博客会详细介绍如何定义默认值
• 列定义为接受某种形式的系统生成值。最常用的系统生成值为IDENTITY值，即系统通常从第一个记录开始计数，在到达第二个同样行编号在某种情况下有跳跃。这些值可以作为每一行的标识符。其他较少使用默认值包括SYSDATETIME()和SEQUENCE接收的值
• 插入数据时候已经提供了该列值

3.2.1 多行插入

一次插入多行是SQL Server 2008的新功能，要实现在一点，只需要添加额外的用逗号分隔的插入值，如下所示：

INSERT INTO Teacher(ID,Name,Age)

VALUES

(4,'Test_4,21),

(5,Test_5,22);

该插入语句使用一条语句插入了2组值。要检查结果，可以查询Teacher表：

SELECT * FROM Teacher

显然，得到之前插入的二行和刚插二行数据： 

ID          Name             Age          Birthday

1           Test　　　　　20            2011-01-12 00:00:00.000 

2           Test_2           20            NULL

4           Test_4           21            NULL

5           Test_5           22            NULL

(4 条受影响)

3.2.2 INSERT INTO ....SELECT 语句

如何一次插入一块数据？前面讲到，可以显式执行多行插入，但如果插入数据块是其他数据源选择获取的，又该怎么办？其他数据源包括：

• 数据库中另一个表
• 同一个服务器上完成不同的数据库
• 来自另一个SQL Server的异构查询和其它数据
• 同一个表（通常在这种情况下是执行数学计算或者SELECT语句的其他调整）

INSERT INTO ..... SELECT 语句可以完成一次插入多条一个数据块的功能，其语法结构为INSERT 语句与SELECT 语句结构的组合，如下所示：

INSERT INTO <table name> [<column list>] <select statement>;

由SELECT 语句产生结果集INSERT语句插入的数据，下面举例验证该语法结构。本事例先声明一个临时表变量来保存Teacher某些列数据：

USE  LearnData

CREATE TABLE @TEMP

(

     ID        INT ,

     Name   NVARCHAR(20) 

);

INSERT INTO @TEMP SELECT  ID,Name FROM Teacher;

SELECT ID,Name FROM @TEMP;

运行后输出的结果：

ID        Name

1         Test_1

2         Test_2

4         Test_4

5         Test_5

(4 条受影响)

第一部分信息（4 条受影响）是INSERT ...... SELECT 语句运行的结果-SELECT 语句返回4条记录并插入临时表中，然后用SELECT 语句来验证插入的数据。

 

3.3 用UPDATE 语句更改获取的数据

与其他SQL语句一样，UPDATE语句表示更新，即UPDATE语句来更新已有的数据。尽管一定相似性，但语法结构与SELECT 语法不同。与INSERT语句一样，它也有复杂选项，当有一个满足大多数需求的基本语法：

UPDATE <table name>

SET   <column> =<value> [,<column>=<value>]

FROM <source table(s)>

[WHERE  <restrictive condition>]

可以在多个表中创建更新语句，进会更新一个表数据。既可在多个不同表构建条件或检查数据，但在同一时刻只有一个表为更新的主体。不要太担心这一点-连接多个表还未介绍。因此这里不会太复杂。一下举个事例，接着上面插入Teacher表数据，修改ID=1的数据，回顾一下ID=1数据：{ID :1  ,Name ：Test_1,Age:20,Birthday :2011-02-11 00:00:00.000}，我们创建一个修改语句并检索修改过数据：

UPDATE Teacher SET Name='吴翔' WHERE ID=1;

SELECT * FROM Teacher WHERE ID=1;

执行代码返回结果：

ID          Name       Age      Birthday

1           吴翔          20       2011-02-11 00:00:00.000

(1 条受影响)

(1 条受影响)

返回结果表明，二条受影响，第一条是修改数据，第二条是查询受影响的数据，一条简单UPDATE语句非常的强大，这仅是开始，后面会介绍更多UPDATE 更新语句。

3.4 DELETE 语句

DELETE语句是本章介绍所有语句中最简单的语句。即使复杂的语法形式，该语句中也没有列列表，通常只有表名和WHERE子句,DELETE 语句的完整语法结构如下所示：

DELETE [TOP (<expresstion> ) [PERCENT]]  [FROM] <tabular object>

[OUTPUT <output clause>]

[FROM <table or join condition>]

[WHERE <seach condition>| CURRENT OF [CLOBAL] <cursor name>]

简化语法：

DELETE <table name>  [WHERE < condition> ]

WHERE 子句的工作方式与其他语句中相同。由于要删除整行（不是半行），因此不需要提供列表（使用UPDATE语句删除是半行）。由于WHERE子语句非常简单，因此只对3.2章插入的数据做快速删除。首先运行SELECT语句，确保插入第一行数据还在。

SELECT * FROM Teachar WHERE ID=1

运行结果：1条受影响。

 

其次我们在运行一条DELETE 语句：

DELETE Teacher WHERE ID=1;

运行结果：1条受影响。 

 

我们简单修改下DELETE 语句

DELETE Teacher WHERE ID=2；

运行结果 ：1条受影响。

 

最后我们检索一下数据被删除的数据是否存在：

SELECT * FROM Teacher WHERE ID IN (1,2);

运行结果：0条受影响，说明前面二条DELETE 语句已经执行成功，删除了检索数据

注意：如果不添加WHERE 子句，执行成功会删除数据表中所有数据。