SQL 学习笔记

第一章：信息体系结构原则

根据以下7个相互依赖的数据存储目标设计和评估任何数据存储：

l  简单性；

l  有用性

l  数据完整性

l  性能

l  可用性

l  可扩展性

l  安全性

 

架构设计原则

l  避免过于复杂

l  精心挑选键

l  树立可选数据

l  实现抽象层

 

查询优化的经验总结

问题	醉解解决方案
复杂的业务逻辑	查询、子查询、CTE
动态生成DDL	游标
对列表进行逆规范化	多复制变量或游标
交叉表	包含透视或CASE表达式的查询
导航层次结构	UDF或CTE
计算累计总计和动态	游标

 

 

第二张：关系数据库建模

规范化

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。 

 

面向服务的体系结构（Service-Oriented Architecture，SOA）是一个组件模型，它将应用程序的不同功能单元（称为服务）通过这些服务之间定义良好的接口和契约联系起来。接口是采用中立的方式进行定义的，它应该独立于实现服务的硬件平台、操作系统和 编程语言。这使得构建在各种这样的系统中的服务可以一种统一和通用的方式进行交互。

 

 

 

第七章：基本的查询流程

查询语句的逻辑流程

l  From：查询首先根据select语句的from子句组装初始化数据集

l  Where:筛选阶段实际上是根据where子句进行帅选，它选择那些在Qualifications列中包含“%frist aid%”的记录行

l  聚合：SQL可以对数据及执行聚合运算，如计算平均值、将数据按某列的值编组或将结果转换为透视表或交叉表

l  Order by：根据from子句和where子句涮选出满足条件的行后，按order by子句指定的方式对它们进行排序

l  谓词：选择计算行、执行计算并根据指定的方式进行排序后，SQL可以只显示前几行或只返回指定的行

 

使用查找条件Between

         Between查找条件用于测试给定的值是否在指定的范围内

例：between 1 and 10

 

使用查找条件in

         In查找条件类似于比较运算符equals,但查找与列表中的值匹配的内容。

例：in (‘nc’,’wv’)

 

使用查找条件Like

         Like查找条件使用通配符在字符串查找指定的模式。

描述	SQL通配符	实例·
多个字符	%	‘Able’ like ‘A%’
单个字	_	‘Able’ like ‘Abl_’
匹配指定范围中的字符	[]	‘a’ like ‘[a-g] ‘a’ like ‘[abck]’
匹配不在指定范围内的字符	[^]	‘a’ like ‘[^w-z]’ ‘a’ like ‘[^wsv]’

 

 

使用多个where条件

         使用多个where条件可以使用布尔逻辑运算符and、or、not。

         布尔运算符的优先顺序：and>or>not

 

对结果集排序

l  使用列名指定顺序

l  使用表达式指定排序顺序

例：select lastname+’, ‘ +firstname from customer order by lastname+’, ‘+firstname

l  使用列序号指定顺序

例：select lastname +’, ‘ +firstname as fullname from customer order by 1

 

 

消除查询结果集中的重复记录Distinct

例：select distinct tourname from event

 

排名

         TOP number|percent

例：select top 3 percent from code order by 1;

 

With ties选项

         对于TOP谓词，使用with ties选项可以列出相同值的项。

例：select top 3 with ties *  from code order by 1

 

 

第八章：使用表达式和标量函数

Case表达式Sql server的case命令是一种用来创建动态表达式的灵活又优秀的方法。它根据条件来确定表达式的值。

简单Case

         在简单Case中，第一个参数是要检查的表达式，然后依次列出测试条件。

例：select customerTypeName,

                   Case [default]

                            When 1 then ‘default type’

                            When 0 then ‘possible’

Else ‘-‘

End as assignStatus

From customerType

 

布尔Case

         与简单Case相仿，但又不同，布尔case的每个分支都有自己的布尔表达式。

例：select

                   Case

                            When 1<0 then ‘reality is gone’

                            When 1>0 then ‘life if normal’

                   End as realitycheck

 

关于空值

         由于空值对表达式的破坏性（select 1 +null 结果为null）,因此有些开发人员不喜欢也不允许使用空值，而使用列的默认值（如空字符串，0，或’n/a’）替代空值。

 

 

第九章：使用连接和联合合并数据

         使用联接，在关系代数中，连接首先将连个数据集相乘，然后对返回的结果进行限制，从而只返回连个数据集的交集。

 

内联接

         内联接只返回连个数据集之间匹配的行。内联接名符其实，它只返回两个数据集都有的部分对应的行。

例：select * from table

                   [inner] join table2

                            On table1.column=table.column

 

外联接

         内联接只返回两个数据集中相同部分匹配的行，外联接扩展了内联接，它还返回左边或右边的数据集中不匹配的数据。

例：select * from table1

                   Left|right [outer] join table2

                            On table1.column=table2.column

左联接与右联接的左右是指sql代码中的顺序。

 

全外连接

         全外联接返回两个数据集中的所有数据，而不管他们是否有匹配的行。

例：select thing1,thing2 from one

                   Full outer join two

                            On one.onepk=two.onepk

 

使用联合

         联合操作不同于联接。在关系代数中，联合是加法，而联接是乘法。联接沿水平方向扩展行，而联合将国歌结果集放到一张较长的表中。

         创建联合查询时必须遵循以下规则：

l  列名或别名必须在第一个select语句中指定

l  每个select 语句指定的列数必须相同，且对应的数据类型必须兼容的

l  可以在select语句中使用表达式指定列来源，条件是每个select语句中都包含该列

l  可以在select into 中使用联合，但关键字into 必须放在第一个select语句中

l  除非指定了关键字distinct，否则select命令默认返回所有的行，而联合的默认行为与此相反，将删除重复的行。

l  Order by子句所有select的结果进行排序的，必须放在最后一个select中，但它使用第一个select指定的列名。

例：select onepk,thing1,’from one’ as sources

                   From one

         Union all

         Select twopk,think2,’from two’

                   From two

         Order by thing1

 

交联合

         交联合查找两个数据集共有的行。内联接在水平方向查找共有的行，而交联合在垂直方向上找共有的行。

例：select thing1 from one

         Intersect

         Select thing2 from two

         Order by thing1

 

差联合

         差联合类似于交联合，但只返回在两个数据集之一中出现的行。

例：select thing1 from one

         Except

         Select thing2 from two

         Order by thing1

 

第十章:使用子查询和CTE提供数据

方法和位置

简单子查询：简单子查询是一个独立的查询，能够单独运行。

公用表表达式（CTE）：CTE类似于视图，是简单子查询的一种语法变种。

相关子查询：它类似于简单子查询，但至少引用来外部查询的一列，因此不能单独运行。

 

简单子查询的执行顺序

执行一次简单子查询

将结果传递给外部查询

执行一次外部查询

例：select (select 3) as subqueryvalue.

 

公用表表达式

         可将公用表表达式大一的内容视为临时视图，可以在查询中将其用作视图。

例：with ctename (parameters)

                   As (simple subquery)

                   Select ……

                            From ctename

 

With cteQuery (productCategoryId)

         As (select productcategoryid

                            From productctegory

                                     Where productcategoryname=’kite’)

 

 

第十二章：汇总数据

聚合函数	支持的数据类型	描述
Sun()	Numeric	计算指定列中所有非空的总和
Avg()	Numeric	计算指定列中所有非空值的平均值。该函数返回输入的数据类型，因此，通常输入转换为精度更高的数据类型如： Avg(cast col as float)
Min()	Numeric,string , Datatime	返回指定列中最小的数字或根据排序规则返回最前面的日期或字符串
Max()	Numeric,string , Datatime	返回指定列中最大的数字或根据排序规则返回最后面的日期或字符串
Count([distinct] *)	任何基于行的数据类型	计算结果集中的总行数，最大为2147483647，不能对数据类型uniquedentfier或 blob进行统计
Count_big([distinct] *)	任何基于行的数据类型	与函数count()类似，但由于其返回类型为bingint,因此最大为2^63-1

 

 

筛选分组结果

From子句使用数据源组装数据集

Where子句根据条件限制返回的行

Group by子句组装数据子集

执行聚合函数

Having子句筛选数据子集

计算表达式

Order by子句对结果进行排序

 

 

第十四章：创建视图

         视图是存储的SQL select语句，可以在查询中作为数据源使用。

         设计良好的视图可以向用户隐藏复杂性，同时提供正确的数据。

         如果读者的.net程序包含大量的动态SQL，使用视图来建立数据库抽象层可以比重构应用程序调用存储过程更容易。

 

使用DDL代码创建视图

例：create viewname

                   As

                   Sql select statement

 

Order by 和视图

         视图用作其他查询的数据源，不支持对数据进行排序。

 

对视图的限制

         视图不能包含从选定列创建新表的select into 选项。

         视图不能引用临时表或表变量，应为这些表的生命周期非常短暂。

         视图不能包含compute或compute by 列。

 

保护视图

         保护数据

         With check option 导致视图where子句不但筛选检索的数据，还在通过视图插入和跟新数据时进行检查。

例：create view viewname

         As

         Select [tourname],basecampid

                   From tour

                   Where baseccampid=2

         With check option

 

第十六章：修改数据

插入数据

Insert命令的形式	描述
Insert/values	插入一行数据，通常用于用户界面中的数据
Insert/select	插入一个结果集，通常用于操作数据集
Insert/exec	插入存储过程的执行结果，用于复杂的数据操作
Insert default	创建一个由默认值组成的新行，用于预先填充预留行
Select /into	使用select 语句的结果集创建一个新表

 

 

插入一行数据

         insert [into] table [(columns)] values (values,……)

 

插入select语句的结果集

         Insert [into] table

                   Select columns from data sources

                            [where conditions]

 

插入存储过程的结果集

         Insert [into] table [(columns)]

                   Exec storedProcedure Parameters

 

创建由默认值组成的行

         Insert table default values

 

插入数据时创建表

         Select columns into newTable from datasours [where conditions]

 

更新单个表

Updata table set column = value or expression or column from data sources

 Whereconditions

更新数据数据时引用多个表

         例：delete from table a

                            Where exists (select * from where empl_staatus=’a’ and a.emplid=b.emplid)

 

删除数据

         Delete from table where conditions

 

返回插入的数据

         Insert guidelist (lastname,fristname,qualifications)

                   Output inserted.*

                   Values (‘nielsen’,’paul’,’trainer’)

 

返回跟新后的数据

         Update guide set qualifications = ‘scuba’

                   Output deleted.qualifications as oldquals,inserted.qualifications as newquals where guideid =3

 

返回删除的数据

Delete guide

         Output deleted.guideid,deleted.lastname,deleted.firstname

         Where guideid=3

 

 

第十七章：实现数据库物理架构

设计数据库的物理架构

         通过头脑风暴拿出一个简单、灵活、优秀的设计方案

物理设计的六个步骤：

l  创建数据库文件

l  创建表

l  创建主键和外键

l  创建数据列

l  添加数据库完整性约束

l  创建索引

 

数据库逻辑架构转换为物理架构时，可能需要做如下修改：

l  将复杂的逻辑设计转换为更简单、更灵活的表结构

l  将组合主键转换为计算机生成的单列主键

l  将业务规则转换为约束或触发器

l  使用连接表将多对多关系转换为两个一个对多关系。

 

规划可扩展性

l  使用一致的命名约定

l  如果简单的数据结构就能满足要求，就应该是呀那个过于复杂和笨拙的数据结构

l  使用脚本而不是Management Studio进行开发

l  避免使用不可移植的、不符合ANSI标准的T-SQL扩展

l  一开始就实施数据完整性约束。即使短时间地采用松散的数据完整性规则，清除不符合要求的数据也成为负担。

l  首先开发核心特征，在这些特性能正常工作后再开发要的特性。

l  编写文档时，不仅要指出过程的作用，还要指出其工作原理

 

合理的逆规范化

         逆规范化是一种冗余数据以方便数据检索的技术，它有意识地违反了范式。

使用建议：

l  如果数据将以OLTP（On-Line Transaction Processing联机事务处理系统）方式使用，即数据是原始的且数据完整性很重要，则不应进行逆规范化。另外，绝对不要对数据进行逆规范化。

l  为提高性能，可以对OLTP数据库中的汇总数纪录（如账户余额或库存量）进行逆规范化，即使这些数据可以根据数据库存交易表和交易表计算得到。

l  如果数据不是原始数据，且主要用于OLAP（On-Line Analysis Processing联机分析处理）或制作报表，则数据一致性不是重点，为提高性能进行逆规范化将是明智之举。

 

创建文件组的数据库

Create database newDB

On

Primary

         (name=newDB.

                   Filename=’d:/sqldata/newdb.mdf’,

                   Size=50mb,

                   Maxsize=5gb,

                   Filegrowth=25mb),

Filegroup groupTwo

(name=newdbgroup2,

Filename=’e:/sqldata/newdbtwo.ndf’,

Size=50mb,

Maxsize=5gb,

Filegrowth=25mb)

Log on’

(name=newDBlog,

Filename=’f:/sqllog/newdblog.ndf’,

Size=100mb,

Maxsize=25gb,

Filegrowth=25mb);

 

修改文件组

Alter database add filegroup

Alter database remove filegroup

 

删除数据库

Drop database newdb;

 

创建表

Create table tablename();

 

创建主键和外键

Create table tablename(

         GuideID INT not null primary key,

         Tourid int not null foreign key referrences dbo.tour(tourid)

)

 

列约束和默认值

主键约束：确保主键包含唯一的非空值。

外键约束：确保未见为有效的主键值。

为空性：指定列是否接受空值。

CHECK约束：自定义的布尔约束。

唯一约束：确保值是唯一的。

Create table tablename(

         GuideID INT not null primary key,--主键约束

         Tourid int not null foreign key referrences dbo.tour(tourid),--外键约束

         EmployeeNumber char(8) unique,--唯一约束

         Employee char(8) check (employ <> ‘1’),--check约束

)

 

创建和修改DDL触发器

Create | alter trigger triggername

On all server |database

{with option}

For | after eventtype or eventgroup , ……n

As

code

?????

 

第十八章：Transact-sql编程

变量的默认值

Declare @Test int,

              @TestTwo nvarchar(25)

Select @Test,@TestTwo

Set @Test=1;

Set @TestTwo=’a value’;

Select @Test,@TestTwo;

Go

 

使用SET和SELECT命令

Declare @TempID int ,

                   @TempLastName varchar(25);

Set @TempID=99;

Select @TempID=PersonID,

         @TempLastName = LastName

From Person

Order by PersonID;

 

在SQL查询中使用变量

例：declare @productcode char(10)

         Set @productcode =’1011’

Select productname from product where productcode = @productcode;

 

流程空值语句

If    if exists()    if/else

 

Begin/end

 

While()

Goto   (跳出深度循环时才允许使用)

Sp_help的使用

Sp_help 表名，可以查到该表的相关信息。

 

 

局部临时表

         临时表的创建方法与用户定义表相同，但临时表的名称必须要以#打头

例：

Create table #productTemp(

         ProductID int primary key

);

 

全局临时表

全局临时表与局部临时表类似，但作用域更大，所有用户都可以引用全局临时表，仅当最后一个引用他的会话结束后才被删除。

例：

If not exists(select * from tempdb.dbo.sysobjects where name=’##tempwork’)

         Create table ##TempWord(

                   Pk int,

                   Col1 int

)

 

 

表变量

表变量类似与临时表，但表变量存放在内存中，临时表需要写磁盘。

例：declare @workTable table(

                   Pk int primary key,

Coll int not null);

 

         Insert into @worktable (pk,coll) values(1,101);

 

 

第二十一章：开发存储过程

存储过程的优点：

l  存储过程是经过编译的，是执行查询和批处理的最快方式。

l  在服务器而不是在桌面计算机上执行，极大减少了网络流量。

l  存储过程是模块化的，提供了部署功能和修改代码的建议途径。

l  存储过程在数据库安全方面扮演了重要角色。

 

管理存储过程

Create / alter / drop

Create procedure procedurename

As

Select product from productCategory

Return;

 

返回记录集

Exce procedurename;

 

编译存储过程

     存储过程的编译是自动进行的，通常是在首次执行时被编译并保存到内存中。但也可以强制编译。

Exce sp_recompile procedurename;

 

向存储过程中输入参数(可以有默认值的)

基本跟变量一样。

例：create procedure categoryGet

              (@CategoryName nvchar(13)=null)

     As

              Select productCategoryName,productCategoryDescription from productCategory

              Where productCategoryname=@categoryname;

 

Exec categoryget ‘kite’

 

 

从存储过程中返回数据

     存储过程有四种方法返回数据。

输出参数

     通过输出参数，存储过程可以向调用他的客户程序返回数据。Output

例：

     Create proc getproductname(

              @productcode char(10),

              @productname varchar(25) output)

As

     Select @productname=productname from productname where code =@productcode

 

Declare @proname varchar(25);

Exec getProductname ‘1011’ ,@prodname output;

Print @prodname

 

使用return命令

              Return命令无条件地终止存储过程的执行，并向调用他的批处理或客户返回一个值。返回值0表示执行成功。

例：create proc isitok(

              @ok varchar(10))

As

If @ok=’ok’

         Return 0;

Else

         Return -100;

 

 

最后，提高数据库运行效率的办法 

　　在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是： 

　　(1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。 

　　(2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。 

　　(3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。 　 (4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。  　(5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。 

　　总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。