Microsoft SQL SERVER 2008技术内幕 T-SQL语言基础(上)

8、ORDER BY 

第一章 T-SQL查询和编程基础

7、Distinct(去重复)

1.2.2 数据库

一个SQL 语句的执行顺序

3.3.3 多表联接

2、Where(条件筛选  谓词筛选

2.4 NULL值

9、TOP/OFFSET-FETCH

2.1.4 HAVING 子句

 

2.1.3 GROUP BY 子句

3、Group by(分组)

2.6.3.2 LEFT 和 RIGHT函数

LEFT和RIGHT函数是SUBSTRING函数的简略形式,它们分别返回输入字符串中从左边或右边开始制定个数的字符。

LEFT(string,n),RIGHT(string,n)

4、Having(分组  谓词筛选 )

1.1.5 数据生命周期

数据首先进入联机事务处理(OLTP,Online Transactional Processing )然后进入数据仓库(Data Warehouse)再进入联机分析处理(OLAP,OnLine Analytical Processing)最后进入DM(Data mining )。

6、表达式(开窗函数 聚合函数 等等)

第二章 单表查询

5、Select (表达式)

2.6.3 运算符和函数

1、From (告诉程序 来自哪张表  如果是表表达式 依旧是如此顺序)

1.1.4 关系模型(Relational Model)

关系模型是一个用于表示数据的语义模型,其理论基础是集合论和谓词逻辑。
  关系模型的目标是要用最少的或完全无冗余地支持完整数据的持久化表示,而且还要将数据完整性(强制的数据一致性)定义为模型的一部分。

关系模型几个相关的概念:

  • 命题 —— proposition
  • 域 —— domain
  • n元关系 —— n-ary relaiton
  • n重元组 —— n-tuple
  • 序偶 —— ordered pair

在关系模型中,关系在数据库的实现中就表现为数据表。对关系进行操作的结果得到的还是一个关系。
  一个域就是一个属性可能的(或有效的)** 一组取值 的集合。 约束(Constraint)
  关系模型最大的优点之一就是将数据完整性定义为模型的一部分,完整性是通过规则(或约束)来实施的。
约束的例子:
  提供实体完整性(entity integrity)的
候选键(candidate key)**——指在关系中能够防止同一元组(数据行)多次出现的属性集(一个或多个属性)。
  外键(foreign key)——用于实施引用完整性,外键是在关系(称为引用关系,referencing relation)中的一个或多个属性上定义,通过它来应用另一个(或者,也可能是同一个)关系中的候选键。
规范化(Normalization)
  关系模型同时也定义了规范化规则(也称为范式)。规范化是一种形式化的数学处理过程,以确保每一个实体只由一个关系来表示。

皇家赌场手机版 1

第一范式(1NF)无重复的列:
  第一范式要求表中的行必须是唯一的属性应该是原子的(atomic)——即列不能再拆分。即一个表表示一个关系。

皇家赌场手机版 2

  数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。很显然,在当前的任何关系数据库管理系统(DBMS)中,傻瓜也不可能做出不符合第一范式的数据库,因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此,你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

第二范式(2NF)属性完全依赖于主键 [ 消除部分子函数依赖 ]:
  第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,第二范式包括两条规则,首先数据必须满足第一范式,其次要求非键属性(nonkey attribute)和候选键属性之间必须满足一定的条件。非正式地说,如果要获得任何非键属性值,就必须提供同一行中某个候选键的所有属性值。
  例如员工信息表中加上了员工编号(emp_id)列,因为每个员工的员工编号是惟一的,因此每个员工可以被惟一区分。

皇家赌场手机版 3

不符合第二范式

皇家赌场手机版 4

符合第二范式

另外,所有单关键字的数据库表都符合第二范式,因为不可能存在组合关键字。

第三范式(3NF)属性不依赖于其它非主属性 [ 消除传递依赖 ]:
  第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。要求数据必须满足第二范式。其次,所有非键属性必须非传递依赖于候选键。所有非键属性都必须互相独立。换句话说,一个非键属性不能依赖于其他非键属性。
参考书本和http://www.open-open.com/lib/view/open1404791721950.html

1.3.1 创建表

创建表语句:

--创建表,表名:Employees(empid:雇员ID mgrid:经理ID ssn:社会保险号(social securuty number))
USE testdb;
IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
    DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
    empid INT NOT NULL,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    hiredate DATE NOT NULL,
    mgrid INT NULL,
    ssn VARCHAR(20) NOT NULL,
    salary MONEY NOT NULL
);

这里,类型‘U’代表用户表。

1.2.1 SQL Server实例

SQL Server实例是指安装的一个SQL Server数据库引擎/服务。在同一台计算机上可以安装多个实例(补充:但是需要不同的实例名)。

2.5 同时操作(All-At-Once Operation)

1.1.3 谓词逻辑(Predicate Logic)

3.1 交叉联接

2.6.3.7 REPLICATE函数

REPLICATE函数以指定的次数复制字符串值。
语法:

REPLICATE(string,n)
2.1.5 SELECT 子句
2.1.8 OVER子句
1.1.5 数据生命周期

2.6 处理字符数据

2.1.8 OVER子句

在group by子句中,在对数据进行分组以后,查询为每个组只返回一行;因此,也就要限制所有的表达式为每个组只能返回一个值。
  聚合开窗函数使用OVER子句提供窗口作为上下文,对窗口中的一组值进行操作,而不是使用group by子句提供上下文。
  注意:只有在select和order by处理阶段才允许使用over子句。
例如:如果在对OrderValues视图进行查询的SELECT子句中指定了SUM(val) OVER表达式,这个函数就会对SELECT阶段操作的所有行计算其总价格。
  如果想对行限制或分区,则可以使用PARTITION BY子句。

皇家赌场手机版 5

OVER 子句的一个优点就是能够在返回基本列的同时,在同一行对它们进行聚合,也可以在表达式中混合使用基本列和聚合值列。例如,以下查询为OrderValues的每一行计算当前价格占总价格的百分比,以及当前价格占客户总价格的百分比:

--over()练习,所占百分比
SELECT orderid,custid,val,
  100.*val / SUM(val) OVER() AS pctall,
  100.*val / SUM(val) OVER(PARTITION BY custid) AS pctcust
  FROM Sales.OrderValues;

注意:在表达式中使用的是十进制数100.(100后面加个点),而不是直接使用证书100,因为这样可以隐式地将整数值val和SUM(val)转换成十进制实数值。否则,表达式的除法将是“整数除法”,会截取小数部分。
OVER 子句也支持四种排名函数:ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)以及NTILE。

皇家赌场手机版 6

再看一个在内部为子集分配行号:

皇家赌场手机版 7

1.3.2 定义数据完整性

作为模型的一部分而实施的数据完整性(也就是说,作为定义的一部分)称为声明式(declarative)数据完整性。用代码来实施的数据完整性(例如,用存储过程或触发器)称为过程式(procedural)数据完整性
  本节主要介绍声明式约束的一些例子,包括主键、唯一约束(UNIQUE)、外键、检查约束(CHECK)以及DEFAULT约束。
在添加约束之前,先了解一下如何解除约束:

alter table 表名 drop contrainst 约束键名

主键约束(Primary Key Constraints)
  每个表只能定义一个主键。
添加主键约束:

--为表添加主键约束Primary Key Constraints
ALTER TABLE dbo.Employees
    ADD CONSTRAINT PK_Employees
    PRIMARY KEY(empid);

皇家赌场手机版 8

唯一约束(Unique Constraints)

--为表添加唯一约束Unique Constraints 添加在ssn列上
ALTER TABLE dbo.Employees
    ADD CONSTRAINT UNQ_Employees_ssn
    UNIQUE(ssn);

这地地方在管理器上貌似并不能直观的看到,或许只有插入数据的时候才能知道吧,试试看:

皇家赌场手机版 9

外键约束
  先创建一个Orders表,然后设置自己的empid为外键指向被引用表(referenced table)中的一组候选键(主键或唯一约束)。注意:引用表和被引用表可能是同一个表。

--添加一个Orders表
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
    DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
    orderid INT         NOT NULL,
    empid   INT         NOT NULL,
    custid  VARCHAR(10) NOT NULL,
    orderts DATETIME    NOT NULL,
    qty     INT         NOT NULL,
    CONSTRAINT PK_Orders
        PRIMARY KEY(orderid)
);
--添加外键约束
ALTER TABLE dbo.Orders
    ADD CONSTRAINT FK_Orders_Employees
    FOREIGN KEY(empid)
    REFERENCES dbo.Employees(empid);

--自己给自己添加外键约束
ALTER TABLE dbo.Employees
    ADD CONSTRAINT FK_Employees_Employees
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid);

检查约束(Check)
  检查约束用于定义在表中输入或修改一行数据之前必须满足的一个谓词。

--添加检查约束(check)
ALTER TABLE dbo.Employees
    ADD CONSTRAINT CHK_Employees_salary
    CHECK(salary > 0);

皇家赌场手机版 10

默认约束(Default)

--默认设置,由它返回当前的日期和时间值
ALTER TABLE dbo.Orders
    ADD CONSTRAINT DFT_Orders_orderts
    DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

皇家赌场手机版 11

默认添加orderts

1.3.1 创建表

1.3 创建表和定义数据完整性

1.3 创建表和定义数据完整性

使用的架构名为dbo,数据库名testdb
创建数据库语句:

IF DB_ID('testdb') IS NULL
    CREATE DATABASE testdb;

执行之后刷新也不出现数据库名,然后我重启了下SQL Server就出现了……
  DB_ID函数接受一个数据库名称作为输入,返回它的内部数据库ID

2.6.3.4 CHARINDEX函数

CHARINDEX函数返回字符串中某个子串第一次出现的起始位置。
语法:

CHARINDEX(substring,string[,start_pos])

例子:

SELECT CHARINDEX(' ','IT BEN');

所有的聚合函数都会忽略NULL,只有一个例外——COUNT(*)。

如果只想处理不重复的已知值,可以在聚合函数的圆括号中指定DISTINCT关键字。
例如:

--DISTINCT的使用
SELECT
    empid,
    YEAR(orderdate) AS numcusts,
    COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY empid,YEAR(orderdate)

3.4 外联接

2.1.5 SELECT 子句

T-SQL允许查询返回没有名称的结果集列,但关系模型不允许这样。
注意:选择Sales.Orders表的orderid和orderdate列,结果不小心,忘记了在两个列名之间加一个逗号,如下:

select orderid orderdate
from Sales.Orders;

这一查询在语法上是有效的,意思是说你想将orderid列的别名定义为orderdate,得到输出只有一列包含订单ID的列,其别名为orderdate。要查出这样的bug可能是很难的
  SELECT子句是在FROM、WHERE、GROUP BY以及HAVING子句后处理的。这意味着对于SELECT子句之前处理的那些子句,在SELECT子句中为表达式分配的别名并不存在。
  为了确保select语句执行的结果中行的唯一性,SQL提供的方法就是使用DISDINCT子句来删除重复的行。

3.1.2 ANSI SQL-89语法(不支持使用)

跟上面的代码相似,就是把Cross Join改成了逗号联接。结果是一样的。

皇家赌场手机版 12

皇家赌场手机版 13

3.3 特殊的联接实例

2.6.2 排序规则(Collation)

2.1 SELECT 语句的元素

SELECT 语句的目的是对表进行查询、应用一定的逻辑处理,并返回结果。
  Microsoft SQL Server引擎并不教条地严格遵守逻辑查询处理;相反,在物理地处理一个查询时,它可以自由地调整处理阶段的顺序,只要最终的结果能够和逻辑查询处理的规定保持一致。SQL SERVER 可以(事实上经常)在查询的物理处理中采用很多快捷方式。
创建一条查询语句:

--开始用一个USE语句来设置会话(session)的数据库上下文,如果会话已经位于需要查询的数据库上下文中,则
--不需要再使用USE语句
USE InsideTSQL2008;

SELECT empid,YEAR(orderdate) AS orderyear,COUNT(*) AS numorders
    FROM Sales.Orders
    WHERE custid = 71
    GROUP BY empid,YEAR(orderdate)
    HAVING COUNT(*) > 1
    ORDER BY empid,orderyear;

解释(从select开始):

  • 1.从Sales.Orders表中查询数据行
  • 2.对订单数据进行过滤,值保留客户ID等于71的记录
  • 3.按雇员ID和订单年份对订单数据进行分组
  • 4.对分组数据(雇员ID和订单年份)进行过滤,只保留具有多个订单的分组
  • 5.选择(返回)每个分组的雇员ID、订单年份,以及订单数量。
  • 6.按照雇员ID和订单年份对输出结果进行排序。

在逻辑上,应该是按以下顺序来处理它的各个子句:

    FROM Sales.Orders
    WHERE custid = 71
    GROUP BY empid,YEAR(orderdate)
    HAVING COUNT(*) > 1
      SELECT empid,YEAR(orderdate) AS orderyear,COUNT(*) AS numorders
    ORDER BY empid,orderyear;

我们看看C#里提供的LINQ语法:

//创建查询
            var query = from CustomerInfo ci in customers
                        where ci.Age >= 80
                        select ci;
2.6.3.5 PATINDEX函数

PATINDEX函数返回字符串中某个模式第一次出现的起始位置。
语法:

PATINDEX(patten,string)

例子:

SELECT  PATINDEX('%[0-9]%','afd123afadsf')

3.4 外联接

  外联接会应用内联接所应用的两个逻辑处理步骤(笛卡尔积和ON过滤),此外还多加一个外联接特有的第三步:添加外部行。
  外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行,再把这些行添加到联接的前两个步骤生成的结果表中;对于来自联接的非保留表的那些列,追加的外部行中的这些列用NULL作为占字符。

2.6 处理字符数据

1.1.3 谓词逻辑(Predicate Logic)

关系模型的数学基础——谓词逻辑。
  不严格的说,谓词就是用来刻画事物是否具有某种性质或满足某种表达式条件的一个词项用于维护数据的逻辑完整性和定义它的结构,谓词也可以用于对数据进行过滤以定义其子集等多种应用场合。

第二章 单表查询

3.4.1 外联接基础
SELECT C.custid,C.companyname
FROM Sales.Customers AS C
    LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE O.custid IS NULL;

皇家赌场手机版 14







注意:

INNER JOIN 等价于 JOIN
你可以理解为
JOIN 是 INNER JOIN 的缩写。
LEFT JOIN 等价于 LEFT OUTER JOIN
RIGHT JOIN 等价于 RIGHT OUTER JOIN

因为ANSI SQL-92语法相对更加安全,且值得推荐使用的语法,故此不再介绍——89语法。

USE InsideTSQL2008;
SELECT E.empid,E.firstname,E.lastname,O.orderid
FROM HR.Employees AS E
    JOIN Sales.Orders AS O
        ON E.empid = O.empid;
1.2.3 架构(Schema)和对象

一个数据库包含多个架构,而每个架构则又包含多个对象。

2.5 同时操作(All-At-Once Operation)

SQL支持一种所谓的同时操作的概念,其含义是认为在同一逻辑查询处理阶段中出现的所有表达式都是同时进行计算的。
  从逻辑上来说,SELECT列表中各表达式的计算是没有顺序的——它们只是一组表达式。在逻辑上SELECT列表中的所有表达式都是在同一时刻进行计算的。

SELECT col1,col2
FROM dbo.T1
WHERE col1<>0 AND col2/col1>2;

如果表达式col1<>0的结果为FALSE,SQL Server将会按照“短路(short-circuit)求值”的原则,停止计算这个表达式。
  在ANSI SQL 中有“同时操作的这么个概念,所以SQL Server可以按它喜欢的任意顺序来自由地处理WHERE子句的表达式。

3.3.1 组合联接
1.2.1 SQL Server实例
2.1.7 TOP 选项

TOP选项是T-SQL特有的,用于限制查询返回的行数或百分比。

--Top选项练习
SELECT TOP(5) orderid,orderdate,custid,empid
    FROM Sales.Orders
    ORDER BY orderdate DESC;

注意:当在查询中指定了TOP以后,ORDER BY子句就会起到双重作用。首先,作为select处理阶段一部分的top选项要依靠order by子句先为各个行定义他们的逻辑优先顺序,在这种优先顺序的基础上再去过滤其他请求。其次,作为select处理阶段之后的order by阶段,与为了展示数据而对行进行排序的order by子句完全一样。

入门心法:要练此功,先废其功。(先忘记已学的其他语言,用T-SQL来思考。)
所需代码:https://github.com/956159241/Microsoft-SQL-Server-2008-T-SQL-Fundamentals

2.1.2 WHERE 子句

在WHERE子句中,可以指定一个谓词或逻辑表达式,从而过滤由FROM阶段返回的行。
练习:

--WHERE 子句练习
SELECT orderid,empid,orderdate,freight
    FROM Sales.Orders
    WHERE custid = 71;

3.2 内联接

两个输入表进行笛卡尔积运算,然后根据用户指定的谓词对结果进行过滤。

1.3.2 定义数据完整性
3.1.1 ANSI SQL-92语法
3.3.1 组合联接

   组合查询跟内联接相似,不同之处是加了多条件。

ON
AND 
WHERE
3.3.2 不等联接
SELECT E1.firstname,E1.lastname,E2.firstname,E2.lastname
FROM HR.Employees AS E1
    JOIN HR.Employees AS E2
    ON E1.empid < E2.empid;
1.1.2 集合论
2.6.3 运算符和函数

注意:

皇家赌场手机版 15

使用+运算符:

--运算符和函数
SELECT empid,firstname + N' ' + lastname AS fullname
FROM HR.Employees;

通过将一个名为CONCAT_NULL_YIELDS_NULL的会话选项设置为OFF,就可以改变SQL Server处理串联的方式。这时,SQL Server将把NULL值作为空字符串来进行串联。

SET CONCAT_NULL_YIELDS_NULL OFF;
2.1.4 HAVING 子句

HAVING 子句用于指定对组进行过滤的谓词或逻辑表达式,这与WHERE阶段对单独的行进行过滤相对应。
  因为HAVING子句是对行进行分组后处理的,所以可以在逻辑表达式中引用聚合函数。

--HAVING 子句练习
SELECT
   empid,
  YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*) > 1

本文由皇家赌场手机版发布于首页,转载请注明出处:Microsoft SQL SERVER 2008技术内幕 T-SQL语言基础(上)

相关阅读