SQL Server必知必会

SQL Server必知必会

2009-10-27-17:57:57

Structure Query Language：SQL

结构化 查询语言

数据库产品： DBI —> IBM

Oracle —> oracle

Sybase —> sybase

SQL Server —> Microsoft

MYSQL —> 网格型

Access

数据模型的三要素：1.数据结构 2.数据操作3.数据通信的约束条件

E-R方法：1.长方形—>实体型

2.椭圆—>实体的属性

3.菱形—>实体的联系

SQL Server的版本：1.企业版2.开发版3.个人版4.标准版

当前流行的基本数据模型：1.层次模型2.网状模型3.关系模型

SQL Server的特点：1.一体化的特点2.统一的语法结构，多种使用方法

3.非过程语法 4.是所有关系数据库的公共语言。

SQL Server的数据库文件：

.mdf主要数据文件，该文件包含数据库的启动信息，并用于存储数据。

.ndf 次要数据文件

.ldf 事务日志文件，用于恢复数据库的日志信息，每个数据库都必须至少有一个事务日志文件。

在SQL中，凡是用‘ ’括起来的一定是字符或日期。

数据（Data）是描述事物的符号记录，它具有多种表现形式，可以是文字、图形、图像、声音、语言等。

数据库（Database，DB）是指长期储存在计算机内的、有组织的、可共享的数据集合。数据中的数据按一定的数学模型组织、描述和储存，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享。

数据库系统（Database System，DBS）广义上讲是由数据库、硬件、软件和人员组成，其中管理的对象是数据。

数据库管理系统（Database Management System)，DBMS是位于用户与操作系统之间的一层数据管理软件，是数据库系统的核心，在操作系统的支持下，解决如何科学的组织和储存数据，如何高效的获取和维护数据库的系统软件。其主要功能包括数据定义功能、数据操纵功能、数据库的运行管理和数据库的建立与维护。数据库管理系统可以分为层次型、网状型、关系型、面向对象型DBMS。

Microsoft SQL Server 系统数据库：

1.master数据库 主要的存储控制

2.model数据库 模板

3.Tempdb数据库 临时数据库

4.msdb数据库 调度/报警

5.pubs数据库 记录一个虚拟的出版公司的数据信息

6.Northwind数据库 一个虚拟的贸易公司的数据信息

一、语 句功能

数据操作

SELECT --从数据库表中检索数据行和列

INSERT --向数据库表添加新数据行

DELETE --从数据库表中删除数据行

UPDATE --更新数据库表中的数据

数据定义

CREATE TABLE --创建一个数据库表

DROP TABLE --从数据库中删除表

ALTER TABLE --修改数据库表结构

CREATE VIEW --创建一个视图

DROP VIEW --从数据库中删除视图

CREATE INDEX --为数据库表创建一个索引

DROP INDEX --从数据库中删除索引

CREATE PROCEDURE --创建一个存储过程

DROP PROCEDURE --从数据库中删除存储过程

CREATE TRIGGER --创建一个触发器

DROP TRIGGER --从数据库中删除触发器

二、SQL SERVER的数据类型

　　数据类型是数据的一种属性，表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然，不同的程序语言都具有不同的特点，所定义的数据类型的各类和名称都或多或少有些不同。SQLServer 提供了 25 种数据类型。

(1)二进制数据包括 Binary、Varbinary 和 Image

　　Binary 数据类型既可以是固定长度的(Binary),也可以是变长度的。

　　Binary[(n)] 是 n 位固定的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储大小是 n + 4 个字节。

　　Varbinary[(n)] 是 n 位变长度的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节，不是n 个字节。

　　在 Image 数据类型中存储的数据是以位字符串存储的，不是由 SQL Server 解释的，必须由应用程序来解释。例如，应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 Image 数据类型中。

(2) 字符数据的类型包括 Char，Varchar 和 Text

　　字符数据是由任何字母、符号和数字任意组合而成的数据。

　　Varchar 是变长字符数据，其长度不超过 8KB。Char 是定长字符数据，其长度最多为 8KB。超过 8KB 的ASCII 数据可以使用Text数据类型存储。例如，因为 Html 文档全部都是 ASCII 字符，并且在一般情况下长度超过 8KB，所以这些文档可以 Text 数据类型存储在SQL Server 中。

(3) 　Unicode 数据类型包括 Nchar,Nvarchar 和Ntext

Nchar 长度固定 最多存储 4000 个字符

Nvarchar 长度变化 最多存储 4000 个字符

Ntext 存储多于 4000 个字符

(4)日期和时间数据类型包括 Datetime 和 Smalldatetime两种类型

Datetime存储的日期范围是从 1753 年 1 月 1 日开始，到9999 年12 月 31 日结束的日期和时间数据，最小时间单位为百分之三秒(每一个值要求 8 个存储字节)。

Smalldatetime 数据类型时，所存储的日期范围是 1900年 1 月 1日 开始，到 2079 年 12 月 31 日结束最小时间单位为分钟(每一个值要求 4 个存储字节)。

（5）数字数据类型

　　数字数据只包含数字。数字数据类型包括正数和负数、小数（浮点数）和整数

　　整数存储的数据类型是 　　 Int/Smallint和 Tinyint。

int 4个 所有数字 -2 147 483 648 ---- 2 147 483 647

smallint 2个 整数数据 -32768---32768

tinyint 1个 0-255

　　精确小的数据在 SQL Server 中的数据类型是 Decimal 和 Numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。

　　在SQL Server 中，近似小数数据的数据类型是 Float 和 Real。例如，三分之一这个分数记作。3333333，当使用近似数据类型时能准确表示。因此，从系统中检索到的数据可能与存储在该列中数据不完全一样。

（6）货币数据表示正的或者负的货币数量。

　　Money数据类型要求 8 个存储字节

Smallmoney 数据类型要求 4 个存储字节。

（7）特殊数据类型

　　　Uniqueidentifier 由 16 字节的十六进制数字组成，表示一个全局唯一的。当表的记录行要求唯一时，GUID是非常有用。例如，在客户标识号列使用这种数据类型可以区别不同的客户。

字段类型 描述

bit 0或1的整型数字

int 从-2^31(-2,147,483,648)到2^31(2,147,483,647)的整型数字

smallint 从-2^15(-32,768)到2^15(32,767)的整型数字

tinyint 从0到255的整型数字

decimal 从-10^38到10^38-1的定精度与有效位数的数字

numeric decimal的同义词

money 从-2^63(-922,337,203,685,477.5808)到2^63-1(922,337,203,685,477.5807)的货币数据，最小货币单位千分之十

smallmoney 从-214,748.3648到214,748.3647的货币数据，最小货币单位千分之十

float 从-1.79E+308到1.79E+308可变精度的数字

real 从-3.04E+38到3.04E+38可变精度的数字

datetime 从1753年1月1日到9999年12日31的日期和时间数据，最小时间单位为百分之三秒或3.33毫秒

smalldatetime 从1900年1月1日到2079年6月6日的日期和时间数据，最小时间单位为分钟

timestamp 时间，一个数据库宽度的唯一数字

uniqueidentifier 全球唯一标识符GUID

char 定长非Unicode的字符型数据，最大长度为8000

varchar 变长非Unicode的字符型数据，最大长度为8000

text 变长非Unicode的字符型数据，最大长度为2^31-1(2G)

nchar 定长Unicode的字符型数据，最大长度为8000

nvarchar 变长Unicode的字符型数据，最大长度为8000

ntext 变长Unicode的字符型数据，最大长度为2^31-1(2G)

binary 定长二进制数据，最大长度为8000

varbinary 变长二进制数据，最大长度为8000

image 变长二进制数据，最大长度为2^31-1(2G)

smallint 16 位元的整数。

interger 32 位元的整数。

decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。

float 32位元的实数。

double 64位元的实数。

char(n) n 长度的字串，n不能超过 254。

varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。

date 包含了 年份、月份、日期。

time 包含了 小时、分钟、秒。

timestamp 包含了 年、月、日、时、分、秒、千分之一秒。

常见的数据库对象包括数据表、约束、规则、视图、存储过程触发器。

SQL serrer中每个数据库最多可储存20亿个数据表，每个数据表可以有1024列，每行最多可以存储8060字节。

数据的完整性是指存储在数据中数据的一致性和正确性。

存储过程的运行不是自动的,而是由应用程序激发而运行。

触发器是一种能够自动的运行的SQL Server 对象。

在SQL Server中，数据表是一种很重要的数据对象，是组成数据库最基本的元素。

创建数计库：

create database student

on primary

(name=student=date,

filename=c:\program files\microsoft SQL server\mssql\Data\student.mdf;

size=25M,

maxsize=100M,

filegrowth=15%)

log on

(name =student_log,

filename=c:\program files\microsoft SQL server\mssql\Data\student.ldf;

size=10M,

maxsize=20M,

filegrowth=2M)

三、数据操作 ＤＭＬ （Data Manipulation Language)

数据定义好之后接下来的就是数据的操作。在创建数据库、数据表和数据视图之后，可以进行数据处理的操作了。数据操作是用户对数据的基础管理，主要包括增加数据（insert)、查询数据（query）、更改数据（update) 、删除数据（delete）四种模式。

１、增加数据：

Insert INTO table_name (column1,column2,...)

valueS ( value1,value2, ...)

1.若没有指定column 系统则会按表格内的栏位顺序填入数据。

2.栏位的数据形态和所填入的数据必须吻合。

3.可以经过一个子查询（subquery）把别的表格的数据填入。

创建一个einsun图书馆的数据表包括书名/ISBN/价格/库存量/出版社/借书日期

create table einsun

( isbn varchar(18),

name nvarchar not null,

price smallmoney not null,

store smallint not null,

pubs nvarchar(10) not null,

constrarint pk_einsun primary key(isbn)

)

insert into einsun

(isbn,name,price,store,pubs,date)

values

('071304027','中国','72.5','100','einsun出版社','2009-8-02')

insert into 表名

(列名)

values

('值列名')

２、查询数据：

基本查询

Select column1,columns2,...

FROM table_name

把table_name 的特定栏位数据全部列出来

Select *

FROM table_name

Where column1 = xxx

[AND column2 〉 yyy] [OR column3 〈〉 zzz]

1.’*’表示全部的栏位都列出来。

2.Where 之后是接条件式，把符合条件的数据列出来。

Select column1,column2

FROM table_name

orDER BY column2 [DESC]

ORDER BY 是指定以某个栏位做排序，[DESC]是指从大到小排列，若没有指明，则是从小到大排列

组合查询是指所查询得数据来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。

Select *

FROM table1,table2

Where table1.colum1=table2.column1

1.查询两个表格中其中 column1 值相同的数据。

2.当然两个表格相互比较的栏位，其数据形态必须相同。

3.一个复杂的查询其动用到的表格可能会很多个。

整合性的查询：

Select COUNT (*)

FROM table_name

Where column_name = xxx

查询符合条件的数据共有几笔。

Select SUM(column1)

FROM table_name

1.计算出总和，所选的栏位必须是可数的数字形态。

2.除此以外还有 AVG() 是计算平均、MAX()、MIN()计算最大最小值的整合性查询。

Select column1,AVG(column2)

FROM table_name

GROUP BY column1

HAVING AVG(column2) 〉 xxx

1.GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM等整合性查询的关键字

一起使用。

2.HAVING : 必须和 GROUP BY 一起使用作为整合性的限制。

复合性的查询

Select *

FROM table_name1

Where EXISTS (

Select *

FROM table_name2

Where conditions )

1.Where 的 conditions 可以是另外一个的 query。

2.EXISTS 在此是指存在与否。

Select *

FROM table_name1

Where column1 IN (

Select column1

FROM table_name2

Where conditions )

1. IN 後面接的是一个集合，表示column1 存在集合里面。

2. Select 出来的数据形态必须符合 column1。

其他查询

Select *

FROM table_name1

Where column1 LIKE ’x%’

LIKE 必须和後面的’x%’ 相呼应表示以 x为开头的字串。

Select *

FROM table_name1

Where column1 IN (’xxx’,’yyy’,..)

IN 后面接的是一个集合，表示column1 存在集合里面。

Select *

FROM table_name1

Where column1 BETWEEN xx AND yy

BETWEEN 表示 column1 的值介于xx 和 yy 之间。

３、更改数据：

Update table_name

SET column1=’xxx’

Where conditoins

1.更改某个栏位设定其值为’xxx’。

2.conditions 是所要符合的条件、若没有 Where 则整个 table 的那个栏位都会全部被更改。

４、删除数据：

Delete FROM table_name

Where conditions

删除符合条件的数据。

SQL语句的基本语法

select语句是数据库应用技术的核心，学习SQL Server的过程中可能用得最多的就是select语句。

between and 检索两值之间的内容。

in 检索匹配列表的数据。

like 检索匹配字符样式的数据。

SQL Server中的函数：

1．数学函数

abs(n) 求n的绝对值

nvod(m,n) 求m除以n的余数

celling(n) 返回大于等于n 的最小整数

floor(n) 返回小于等于n的最大整数

round(n,m) 对n做四舍五入处理，保留m位

sqrt(n) 求n的平方根

power(n,m) 求n乘指定次方m的值n是底数，m是指数

square 求n的平方

select ceilling(13.4), floor(13.4), round(13.4567,3)

14 13 13.4570

rand产生一个0到1之间的随机数。

2．字符串函数

upper 小写字符串转换为大写字符串

lower 大写字符串转换为小写字符串

ltrim 删除字符串前面的空格

rtim 删除字符串后面的空格

left 取字符串左边的几个字符

right 取字符串右边的几个字符

select left(‘abcdefg’,4) abcd

3.日期和时间函数

getdate()给出系统当前的时间

求以前到现在：datediff(year,’1988-12-01’,’2009-08-02’)

month day huur minute second

求今后： dateadd(day,1800, getdate())

求100天前的时间：

select dataadd(day,-100,getdate())

计算你到70岁生日还有多少天？

select datediff(day,getdate(),dateadd(year,70,’1988-12-01’))

4.转换函数

显示今天是2009年8月2日

select ‘’+cast (year(getdate()) as varchar)+’年’+cast(mont(getdate())as varchar)+’月’

+cast(day(getdate())as varchar)+’日’

5．系统函数

6．聚合函数

avg 平均值count 统计个数sum 总和max 最大值min 最小值

select avg(grade) from stud_grade where course_id=’071304027’

一.Select语句的完整语法为：

Select[ALL|DISTINCT|DISTINCTROW|TOP]

{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}

FROM tableexpression[,…][IN externaldatabase]

[Where…]

[GROUP BY…]

[HAVING…]

[ORDER BY…]

[WITH OWNERACCESS OPTION]

用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。

1 FROM子句

FROM子句指定了Select语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 得到的复合结果。如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。

例：下列SQL语句返回所有有定单的客户：

Select orderID,Customer.customerID

FROM orders Customers

Where orders.CustomerID=Customers.CustomeersID

2 ALL、DISTINCT、DISTINCTROW、TOP谓词

(1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。

例：Select ALL FirstName,LastName

FROM Employees

(2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。

(3) DISTINCTROW 如果有重复的记录，只返回一个

(4) TOP显示查询头尾若干记录。也可返回记录的百分比，这是要用 TOP N PERCENT子句（其中N 表示百分比）

例：返回5%定货额最大的定单

Select TOP 5 PERCENT*

FROM [ order Details]

orDER BY UnitPrice*Quantity*(1-Discount) DESC

3 用 AS 子句为字段取别名

如果想为返回的列取一个新的标题，或者，经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。

例：返回FirstName字段取别名为NickName

Select FirstName AS NickName ,LastName ,City

FROM Employees

例：返回新的一列显示库存价值

Select ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock

FROM Products

二 .Where 子句指定查询条件

orderby 排序 desc降序 asc 升序

group by 分组 having

from子句：首先用from子句连接条件，然后再用where子句搜索条件，最后用having

内连接：inner join

外连接

左外连接left outer join

右外连接 right outer join

全外连接 full outer join返回两个表的所有行

嵌套查询中子查询的select语句中不能使用order by

1 比较运算符

比较运算符 含义：= 等于〉大于〈小于〉= 大于等于 〈= 小于等于 〈〉 不等于 !〉 不大于 !〈 不小于

例：返回2009年8月的定单

Select orderID, CustomerID, orderDate

FROM orders

Where orderDate〉’2009-08-01’ AND orderDate〈’2009-08-31’

使用 NOT 表达式求反。

例：查看2009年8月1日以后的定单

Where Not orderDate〈=’2009-08-01’

2 范围（BETWEEN 和 NOT BETWEEN）

BETWEEN …AND…运算符指定了要搜索的一个闭区间。

例：返回2009年8月到2009年9月的定单。

Where orderDate Between ‘2009-08-01’ And ‘2009-08-31’

3 列表（IN ，NOT IN）

IN 运算符用来匹配列表中的任何一个值。IN子句可以代替用OR子句连接的一连串的条件。

例：要找出住在 London、Paris或Berlin的所有客户

Select CustomerID, CompanyName, ContactName, City

FROM Customers

Where City In(‘London’,’ Paris’,’ Berlin’)

4 模式匹配(LIKE)

LIKE运算符检验一个包含字符串数据的字段值是否匹配一指定模式。

LIKE运算符里使用的通配符

通配符 含义

？ 任何一个单一的字符

* 任意长度的字符

# 0~9之间的单一数字

[字符列表] 在字符列表里的任一值

[！字符列表] 不在字符列表里的任一值

- 指定字符范围，两边的值分别为其上下限

例：返回邮政编码在（171）555-0000到（171）555-9999之间的客户

Select CustomerID ,CompanyName,City,Phone

FROM Customers

Where Phone Like ‘(171)555-####’

LIKE运算符的一些样式及含义

样式含义 不符合

LIKE ‘A*’ A后跟任意长度的字符 Bc,c255

LIKE’5

’ 5*5 555

LIKE’5?5’ 5与5之间有任意一个字符 55,5wer5

LIKE’5##5’ 5235，5005 5kd5,5346

LIKE’[a-z]’ a-z间的任意一个字符 5,%

LIKE’[!0-9]’ 非0-9间的任意一个字符 0,1

LIKE’[[]’ 1,*

三 .用ORDER BY子句排序结果

ORDER子句按一个或多个（最多16个）字段排序查询结果，可以是升序（ASC）也可以是降序（DESC），缺省是升序。ORDER子句通常放在SQL语句的最后。

ORDER子句中定义了多个字段，则按照字段的先后顺序排序。

Select ProductName,UnitPrice, UnitInStock

FROM Products

ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName

ORDER BY 子句中可以用字段在选择列表中的位置号代替字段名，可混合字段名和位置号。

例：下面的语句产生与上列相同的效果。

Select ProductName,UnitPrice, UnitInStock

FROM Products

ORDER BY 1 DESC , 2 DESC,3

四 .运用连接关系实现多表查询

例：找出同一个城市中供应商和客户的名字

Select Customers.CompanyName, Suppliers.ComPany.Name

FROM Customers, Suppliers

Where Customers.City=Suppliers.City

例：找出产品库存量大于同一种产品的定单的数量的产品和定单

Select ProductName,OrderID, UnitInStock, Quantity

FROM Products, [Order Deails]

Where Product.productID=[Order Details].ProductID

AND UnitsInStock〉Quantity

另一种方法是用 Microsof JET SQL 独有的 JNNER JOIN

语法：

FROM table1 INNER JOIN table2

ON table1.field1 comparision table2.field2

其中comparision 就是前面Where子句用到的比较运算符。

Select FirstName,lastName,OrderID,CustomerID,OrderDate

FROM Employees

INNER JOIN orders ON Employees.EmployeeID=Orders.EmployeeID

注意：

INNER JOIN不能连接Memo OLE Object Single Double 数据类型字段。

在一个JOIN语句中连接多个ON子句

语法：

Select fields

FROM table1 INNER JOIN table2

ON table1.field1 compopr table2.field1 AND

ON table1.field2 compopr table2.field2 or

ON table1.field3 compopr table2.field3

也可以

Select fields

FROM table1 INNER JOIN

（table2 INNER JOIN [( ]table3

[INNER JOER] [( ]tablex[INNER JOIN]

ON table1.field1 compopr table2.field1

ON table1.field2 compopr table2.field2

ON table1.field3 compopr table2.field3

外部连接返回更多记录，在结果中保留不匹配的记录，不管存不存在满足条件的记录都要返回另一侧的所有记录。

FROM table [LEFT|RIGHT]JOIN table2

ON table1.field1comparision table.field2

用左连接来建立外部连接，在表达式的左边的表会显示其所有的数据

例：不管有没有定货量，返回所有商品

Select ProductName ,OrderID

FROM Products

LEFT JOIN orders ON Products.PrductsID=Orders.ProductID

右连接与左连接的差别在于：不管左侧表里有没有匹配的记录，它都从左侧表中返回所有记录。

例：如果想了解客户的信息，并统计各个地区的客户分布，这时可以用一个右连接，即使某个地区没有客户，也要返回客户信息。

空值不会相互匹配，可以通过外连接才能测试被连接的某个表的字段是否有空值。

Select *

FROM talbe1

LEFT JOIN table2 ON table1.a=table2.c

1 连接查询中使用Iif函数实现以0值显示空值

Iif表达式： Iif(IsNull(Amount,0,Amout)

例：无论定货大于或小于￥50，都要返回一个标志。

Iif([Amount]〉50,?Big order?,?Small order?)

五. 分组和总结查询结果

在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。

GROUP BY 子句的语法

Select fidldlist

FROM table

Where criteria

[GROUP BY groupfieldlist [HAVING groupcriteria]]

注：Microsoft Jet数据库 Jet 不能对备注或OLE对象字段分组。

GROUP BY字段中的Null值以备分组但是不能被省略。

在任何SQL合计函数中不计算Null值。

GROUP BY子句后最多可以带有十个字段，排序优先级按从左到右的顺序排列。

例：在‘WA’地区的雇员表中按头衔分组后，找出具有同等头衔的雇员数目大于1人的所有头衔。

Select Title ,Count(Title) as Total

FROM Employees

Where Region = ‘WA’

GROUP BY Title

HAVING Count(Title)〉1

聚集函数

SUM ( ) 求和

AVG ( ) 平均值

COUNT ( ) 表达式中记录的数目

COUNT (* ) 计算记录的数目

MAX 最大值

MIN 最小值

VAR 方差

STDEV 标准误差

FIRST 第一个值

LAST 最后一个值

--AVG

use pangu

select avg(e_wage) as dept_avgWage

from employee

group by dept_id

--MAX

--求工资最高的员工姓名

use pangu

select e_name

from employee

where e_wage =

(select max(e_wage)

from employee)

六. 用Parameters声明创建参数查询

Parameters声明的语法:

PARAMETERS name datatype[,name datatype[, …]]

其中name 是参数的标志符,可以通过标志符引用参数.

Datatype说明参数的数据类型.

使用时要把PARAMETERS 声明置于任何其他语句之前.

例:

PARAMETERS[Low price] Currency,[Beginning date]datatime

Select orderID ,OrderAmount

FROM orders

Where orderAMount〉[low price]

AND orderDate〉=[Beginning date]

七. 功能查询

所谓功能查询,实际上是一种操作查询,它可以对数据库进行快速高效的操作.它以选择查询为目的,挑选出符合条件的数据,再对数据进行批处理.功能查询包括更新查询,删除查询,添加查询,和生成表查询.

1 更新查询

Update子句可以同时更改一个或多个表中的数据.它也可以同时更改多个字段的值.

更新查询语法:

Update 表名

SET 新值

Where 准则

例:英国客户的定货量增加5%,货运量增加3%

Update OEDERS

SET orderAmount = orderAmount *1.1

Freight = Freight*1.03

Where ShipCountry = ‘UK’

2 删除查询

Delete子句可以使用户删除大量的过时的或冗于的数据.

注:删除查询的对象是整个记录.

Delete子句的语法:

Delete [表名.*]

FROM 来源表

Where 准则

例: 要删除所有94年前的定单

Delete *

FROM orders

Where orderData〈#94-1-1#

3 追加查询

Insert子句可以将一个或一组记录追加到一个或多个表的尾部.

INTO 子句指定接受新记录的表

valueS 关键字指定新记录所包含的数据值.

Insert 子句的语法:

INSETR INTO 目的表或查询(字段1,字段2,…)

valueS(数值1,数值2,…)

例:增加一个客户

Insert INTO Employees(FirstName,LastName,title)

valueS(‘Harry’,’Washington’,’Trainee’)

4 生成表查询

可以一次性地把所有满足条件的记录拷贝到一张新表中.通常制作记录的备份或副本或作为报表的基础.

Select INTO子句用来创建生成表查询语法:

Select 字段1,字段2,…

INTO 新表[IN 外部数据库]

FROM 来源数据库

Where 准则

例:为定单制作一个存档备份

Select *

INTO ordersArchive

FROM orders

八. 联合查询

UNION运算可以把多个查询的结果合并到一个结果集里显示.

UNION运算的一般语法:

[表]查询1 UNION [ALL]查询2 UNION …

例:返回巴西所有供给商和客户的名字和城市

Select CompanyName,City

FROM Suppliers

Where Country = ‘Brazil’

UNION

Select CompanyName,City

FROM Customers

Where Country = ‘Brazil’

注:

缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项

UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同.

每一个查询参数中可以使用GROUP BY 子句 或 HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句.

九. 交叉查询

交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部.

Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法:

TRANSFORM aggfunction

Select 语句

GROUP BY 子句

PIVOT pivotfield[IN(value1 [,value2[,…]]) ]

Aggfounction指SQL聚积函数,

Select语句选择作为标题的的字段,

GROUP BY 分组

Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制它的取值.

value代表创建列标题的固定值.

例:显示在1996年里每一季度每一位员工所接的定单的数目:

TRANSFORM Count(OrderID)

Select FirstName&’’&LastName AS FullName

FROM Employees INNER JOIN orders

ON Employees.EmployeeID = orders.EmployeeID

Where DatePart(“yyyy”,OrderDate)= ‘1996’

GROUP BY FirstName&’’&LastName

orDER BY FirstName&’’&LastName

POVOT DatePart(“q”,OrderDate)&’季度’

十 .子查询

子查询可以理解为 套查询.子查询是一个Select语句.

1 表达式的值与子查询返回的单一值做比较

语法:

表达式 comparision [ANY|ALL|SOME](子查询)

ANY和SOME谓词是同义词,与比较运算符(=,〈,〉,〈〉,〈=,〉=)一起使用.返回一个布尔值True或False.ANY的意思是,表达式与子查询返回的一系列的值逐一比较,只要其中的一次比较产生True结果,ANY测试的返回 True值(既Where子句的结果),对应于该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的一系列的值的比较都产生True结果,才回返回True值.

例:主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品

Select * FROM Products

Where UnitPrice〉ANY

(Select UnitPrice FROM[Order Details] Where Discount〉0.25)

2 检查表达式的值是否匹配子查询返回的一组值的某个值

语法:

[NOT]IN(子查询)

例:返回库存价值大于等于1000的产品.

Select ProductName FROM Products

Where ProductID IN

(Select PrdoctID FROM [Order DEtails]

Where UnitPrice*Quantity〉= 1000)

3检测子查询是否返回任何记录

语法:

[NOT]EXISTS (子查询)

例:用EXISTS检索英国的客户

Select ComPanyName,ContactName

FROM orders

Where EXISTS

(Select *

FROM Customers

Where Country = ‘UK’ AND

Customers.CustomerID= orders.CustomerID)

约束

数据的完整，是用户使用和管理数据库的基础。完整的数据，为客户提供可用的数据服务。数据完整性，主要是指数据的一致性和正确性。

SQL Server定义约束的方式五种类型。Check 、Primary key 、foreign key、unique、defauit。

CHECK约束

CHECK约束用于限制输入到一列或多列的值的范围，从逻辑表达式上判断数据的有效性，也就是一个列的输入内容必须满足CHECK约束的条件，否则数据通信无法正常输入，从而强制数据通信的完整性。

CHECK约束是限制列可接受的值，它可以强制域的完整性。在数据库中，一般会经常使用CHECK约束。用户可以通过代码和SQL Server Management Studio图形工具建立CHECK约束。

在ALTER TABLE语句中，使用ADD CONSTRAINT…CHECK…语句为数据表增加CHECK约束

alter table stud_info

add

conatraint ck_stud_info check(gender=’男’ or gender=’女’)

主键约束

一般在SQL Server数据库中，保存数据的表都要设置主键。设置完主键约束的数据表将符合两项数据完整性规则：一是列不允许有空值，即指定的PRIMARY KEY约束，将数据列隐式转换为NOT NULL约束。二是不能有重复的值。如果对具有重复值或允许有空值的列添加PRIMARY KEY约束，则数据库引擎将返回一个错误并且不添加约束。

PRIMARY KEY约束 主键约束

一个表只能有一个主键，而且主键约束中的列不能为空值。

use student

create table student

( id smallint,

name nvarchar(8) not null,

sex bit not null,

birthday smalldatatime null,

address nvarchar(20),

td varchar(20),

constraint pk_student primary key (id)

)

外键约束

外键是用于建立和加强两个表数据之间关系的约束，它链接两表的一列或多列。通过将数据表中主键值的列添加到另一个数据表中，可创建两个表之间的关系。这个主键列就成为第二个表的外键。一般表现为两个数据表中，一张数据表的某一列的所有值，全部取自另外一张表的主键值。外键关系特性是关系型数据库二维表间关系的最重要的组成之一。

foreign key约束 外键约束

为stud_grade表中course_id建立外键

alter table stude grade

add constraint fk_couse_id

forign key(couse_id)

UNIQUE约束

UNIQUE约束是指表中的任何两行都不能有相同的列值。主键也强制实施唯一性，但主键不允许 NULL的出现。一般情况下UNIQUE约束用于确保在非主键列中不输入重复的值。

用户可以在创建表时，将UNIQUE约束作为表定义的一部分。也可以在已经存在的数据表中，使用图形工具或者T-SQL脚本添加UNIQUE约束。一个表可含有多个UNIQUE约束。

USE [AdventureWorks]

CREATE TABLE [dbo].[new_phone_with_unique](

[id] [int] NOT NULL,

[employeeid] [int] NULL UNIQUE NONCLUSTERED,

[phone] [nchar](10),

[memo] [nchar](10),

CONSTRAINT [PK_new_phone_uni] PRIMARY KEY CLUSTERED

(

[id] ASC

)

DEFAULT约束

默认值是一种常用的约束。在数据表中插入行时没有为列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。

USE AdventureWorks

ALTER TABLE dbo.new_employees ADD CONSTRAINT DF_name_test DEFAULT 'UNKNOWN' FOR name

视图

视图view 视图是一个虚拟表，不包括数据，只是用来查看数据的窗口而已。

视图是通过定义查询建立的虚拟表。与普通的数据表一样，视图由一组数据列、数据行构成。由于视图返回的结果集，与数据表有相同的形式，因此可以像数据表一样使用。

在SQL Server2005数据库中，视图分为三种，根据实现的使用，标准视图是最为常用的视图，索引视图和分区视图是SQL Server 2005数据库中引入的新的特性。

Create view v-1

select * from dbo.产品

drop view v-1

索引

索引提供对数据的快速访问，用于减轻搜索数据库的压力。

SQL Server 2005数据库的索引分为聚集索引和非聚集索引和唯一索引。

PK一定是聚集索引，聚集索引不一定是PK。

drop index cp-1

存储过程

一． 存储过程的特点

1. 存储过程可以接受参数。

2. 存储过程以输出参数的形式返回多个值。

3. 存储过程可以调用查询、修改（SELECT、INSERT、DELETE、UPDATE）的SQL语句，或者其他存储过程；而在用户自定义函数中只允许调用SELECT语句。

4. 存储过程只能以语句的形式调用，不能以表达式的形式调用。

二． 存储过程的优点

T-SQL程序的主要原因是存储过程有以下优点：

1. 模块化的程序设计

存储过程只需要编写一次，就可以由应用程序多次调用。

2. 执行速度快：

存储过程在第一次执行时会被编译并存储在内存中，以后执行时就不需要在从外存中调入和编译了，因此执行速度比文件形式存储的SQL代码要高。

3. 网络通讯量少

存储过程的代码存储在服务期端，从客户端调用时只需要向服务期发送一条指令就可以了。

4. 可以作为安全机制的辅助手段

三． 存储过程的分类

1. 系统存储过程：存储在master表中，可以在任何数据库中调用。

2. 本地存储过程：或者称为用户自定义存储过程，只可以用户数据库中被创建、执行。

四． 创建存储过程

CREATE PROCEDURE 过程名

[ { @参数名 数据类型 } [ OUTPUT ] ]

SQL语句 [ ...n ]

五． 执行存储过程

EXECUTE 过程名 [ 参数列表 ]

存储过程实例：

/*定义一个存储过程，返回两个整数的和，积*/

CREATE PROCEDURE Caculate

@m1 int,

@m2 int,

@sum int OUTPUT,

@product int OUTPUT

SET @sum = @m1 + @m2

SET @product = @m1 * @m2

加密 with encryption

一个set语句只能给一个变量赋值，print以消息方式输出，只能输出字符串。产生了一个0-1的随机数。

set @ a =rand*10

declare select newid()全球唯一标识符

在SQL Server系统中，可管理的最小空间是页，一个页是8KB的磁盘物理空间。

select 产品名称，单价，

case

when单价>60 then ‘太贵了’

when单价>=30 and 单价<40 then ‘好’

when单价>=40and 单价<-60 then ‘’

else ‘不贵’

end as 价格评价

from dbo.产品

1+2+3+.......+100:

declare @i int ,@s int

set @ i=1 set @ s=0

while @i<=100

begin

set @s=@s+@i

set @i=@i+1

end

select @s

100内的偶数

declare @i int

set @ i=0

while @i<=100

begin

if@i%2=0

end

select @i

六． 系统存储过程 sp_helptext v-1

游标

一． 游标的分类

1． 按照实现方法分类

Transact-SQL游标(程序游标)

由SQL语言实现的一种游标。

API服务器游标

客户端应用程序调用API函数、ODBC驱动程序、OLE-DB提供者传递给SQL Server服务器，在服务器缓存中存储结果集,由服务器实现各种游标操作。

API客户端游标

客户端游标操作由SQL Server ODBC驱动程序、ADO动态链接库内部实现，在客户端缓存中存储结果集，然后根据应用程序请求在客户端缓存中进行各种游标操作

2． 按操作方式分类

静态游标

静态游标总是显示游标打开时的状态，其表示的数据集不会改变，基表中的数据更新不会影响到静态游标中的记录。不能使用静态游标修改基表中的数据。

动态游标

动态游标中的数据是和基表中的数据同步的。可以使用动态游标修改基表中的数据。

键集驱动游标：键集驱动游标中记录的个数和排列次序是不变的，但是数值是和基表同步的。

二． 游标的工作过程

1. 声明游标：用DECLARE语句声明游标，并定义游标特征。如游标中的记录是否允许修改等游标属性。

2. 打开游标：执行OPEN语句打开游标。

3. 读取游标：使用FETCH语句从游标的结果集中检索数据。从游标中检索一行或多行数据，称提取数据。

4. 关闭游标：完成游标操作后，用CLOSE关闭游标。关闭的游标并没有从内存中删除，如需要的话，可以再使用OPEN语句打开游标。

5. 释放游标：最后执行DEALLOCATE语句从内存中删除游标，并释放其所占用的资源。

三． 有关游标的操作

1. 声明游标：

DECLARE 游标名 CURSOR

[ STATIC | KEYSET | DYNAMIC ]

FOR 查询语句

2. 打开游标

OPEN 游标名

3. 从游标中提取记录

FETCH [ [ NEXT | PRIOR | FIRST | LAST

| ABSOLUTE n | RELATIVE n ]

FROM 游标名

当执行FETCH命令之后，系统将执行状态保存在全局变量 @@FETCH_STATUS中，返回值共有三个：

0：表示读取成功。

-1: FETCH 语句失败或此行不在结果集中。

-2: 被提取的行不存在。

4. 关闭游标

CLOSE 游标名

5. 释放游标

DEALLOCATE 游标名

事务

事务是绑定在一起的一系列数据库操作，要么全部执行、要么全部不执行。SQL Server使用事务保证数据的一致性和确保在系统失败时可以恢复数据。

一． 事务的特点

事务具有ACID属性：

1. 原子性(Atomicity)：指事务中的所有操作构成一个整体，要么都做，要么都不做。

2. 一致性(Consistency)：事务完成时，所有的数据都具有一致的状态。

3. 隔离性(Isolation)：一个事务不可以访问另一个事务正在处理的数据。

4. 持续性(Duration)：当事务完成之后，事务对数据的修改将被保存到数据库中。

二． 事务管理

SQL Server使用一以下4条语句管理显式事务：

1. 开始事务

BEGIN TRANSACTION [ 事务名 ]

2. 提交事务

COMMIT TRANSACTION [ 事务名 ]

如果事务成功执行，COMMIT TRANSACTION将从事务开始以来所执行的所有数据修改保存到数据库中。

3. 回滚事务

ROLLBACK TRANSACTION [ 事务名 | 保存点名 ]

ROLLBACK TRANSACTION将事务回滚到事务的起点或事务内的某个保存点。

4. 设置保存点

SAVE TRANSACTION { 保存点名 | @保存点变量名 }

保存点提供了一种机制，用于回滚部分事务。

当事务执行过程中，如果检测到错误时，可以选择只回滚到保存点，而保留保存点之前的结果。

三． 事务实例

/*学校决定调整所有教师的工资，软件技术系的基本工资增加200，信息工程系增加150，艺术系增加300*/

BEGIN TRANSACTION

UPDATE tTeacher SET iSalary = iSalary + 200 WHERE ncDept = '软件技术'

UPDATE tTeacher SET iSalary = iSalary + 150 WHERE ncDept = '信息工程'

UPDATE tTeacher SET iSalary = iSalary + 300 WHERE ncDept = '艺术'

COMMIT TRANSACTION

触发器

触发器是一段用T-SQL语言编写的代码，在用户更改数据表的时候会被自动激活。

一． 触发器的作用

1. 在数据库的相关表中实现级联更新数据。

2. 实现比约束更完善的数据完整性。

3. 监视和撤销用户对数据的非法修改。

二． 触发器的分类

1． AFTER触发器（后触发器）：在数据更改操作（INSERT、UPDATE、DELETE）发生后被激活。

2． INSTEAD OF触发器：在数据更改操作之前被激活，并取代数据更改操作。

三． INSERTED表和DELETED表

为了追踪用户对哪些数据做了更改，在触发器被激活时系统会创建两张特殊的临时表：

1． INSERTED表：存储INSERT操作插入到相关表中的记录和UPDATE操作更新后的记录。

2． DELETED表：存储DELETE操作删除的记录和UPDATE操作更新前的记录。

四． 触发器和事务的关系

1． 触发器也是事务的一部分，触发器执行失败将导致事务失败。

2． 在触发器中可以使用ROLLBACK TRANSACTION语句对当前事务中的那一点所做的所有数据修改将被回滚，包括触发器所做的修改。

五． 触发器的创建

CREATE TRIGGER <触发器名>

ON <表名 | 视图名>

{FOR | AFTER | INSTEAD OF} {[INSERT] [,UPDATE] [,DELETE]}

{ [IF UPDATE(列名)] [{AND | OR} UPDATE (列名)] }

数据库安全管理

一． SQL Server的三层安全模型。

1. 服务器的安全管理：身份验证、数据库系统用户的管理,服务器固定角色管理。

2. 数据库级别的安全管理：角色和用户的管理。

3. 数据库对象的安全管理：数据库对象的权限设置。

二． SQL Server的身份验证模式

1. Windows身份验证模式：使用Windows的用户信息登录SQL Server，并获得由数据库系统赋予的相应权限，安全性更高。

2. 混合模式：同时使用Windows身份验证和SQL Server身份验证。

SQL Server身份验证：使用由SQL Server管理的账户访问数据库系统，与操作系统无关。

三． 服务器安全管理

1. 服务器固定角色：

服务器固定角色是为了进行服务器级别的管理工作而设置的，不可以被删除，不可以增加新的角色，也不可以重新设置权限。

所有服务器固定角色中，权限最高的是系统管理员System Administrators。

2. 登陆帐号管理：

默认帐号：在数据库系统安装好之后，会自动创建两个默认帐号 SA和BUILTIN\Administrator。

四． 据库安全管理

1. 数据库用户管理

用户在数据库服务器上拥有帐号之后，并不能具有数据库的访问权，还需要将用户的帐号添加到数据库的用户列表中，才能访问某个数据库。

数据库上的特殊用户：

（1） dbo：数据库的最初创建者，拥有完全控制数据库的权力。

（2） Guest：此帐号的权限是最低的，此帐号的目的是供用户匿名访问数据库。

2. 数据库角色管理

数据库的角色代表了用户在该数据库上具有的权限。

数据库角色中权限最高的是数据库的拥有者：db_owner。

数据库上的特殊角色public：

（1） 所有的数据库用户都属于该角色。

（2） 该角色定义了所有用户的公共权限。

（3） 所有数据库都有public角色，并且不可以被删除。

五． 数据库对象的安全管理

数据库对象的权限管理包括：对象权限和语句权限。

1． 对象权限

对象权限是指用户数据库中的表、视图、存储过程等对象能执行哪些操作。各种对象上能设置的权限如下：

（1） 表和视图：SELECT、INSET、UPDATE、DELETE。

（2） 表中的字段：SELECT、UPDATE。

（3） 存储过程：EXECUTE。

2． 语句权限

语句权限管理用于控制用户是否可以执行以下数据定义语句（DDL）：

T-SQL语句说明

CREATE DATABASE 创建数据库，只能由SA设置此语句的权限

CREATE TABLE 创建表

CREATE DEFAULT 创建默认

CREATE VIEW 创建视图

CREATE PROCEDURE 创建存储过程

CREATE RULE 创建规则

CREATE FUNCTION 创建函数

BACKUP DATABASE 备份数据库

BACKUP LOG 备份日志

3． 权限的管理

权限管理包含三个内容：

（1） 授予权限（GRANT）允许某个用户或角色执行某个操作或语句。

（2） 收回权限（REVOKE）：收回曾经授予给用户或角色的某个权限。

（3） 拒绝权限（DENY）：明确禁止某个用户或角色对一个对象执行某种操作或执行对象创建语句。

特别注意收回权限（REVOKE）和拒绝权限（DENY）的区别：

（1） 收回（REVOKE）权限并不一定能禁止用户执行某个操作或语句，如果用户所属的某个角色还具有此权限，那么用户仍然可以执行此操作。

（2） 拒绝（DENY）权限的作用是明确的禁止用户执行某个操作或语句，无论他所属的角色是否拥有此权限。

4． 使用SQL语言进行权限的管理

（1） 管理语句权限

GRANT { 语句 [ ,...n ] } TO 用户名

REVOKE {语句 [ ,...n ] } TO 用户名

DENY { 语句 [ ,...n ] } TO 用户名

（2） 对象权限管理

GRANT {权限 [ ,...n ] } ON 对象名 [ (列名 [ ,...n ] ) ] TO [ 用户名 | 角色名]

REVOKE {权限 [ ,...n ] } ON 对象名 [ (列名 [ ,...n ] ) ] TO [ 用户名 | 角色名]

DENY {权限 [ ,...n ] } ON 对象名 [ (列名 [ ,...n ] ) ] TO [ 用户名 | 角色名]

数据库备份与恢复

一． 数据库的备份方式

（1） 完全备份：备份所有数据文件；完全备份用于重新创建数据库，每个数据库都必须至少作一次完全数据库备份，因为它是所有恢复模式的基础。

（2） 差异备份：只备份上次备份以来被修改的那些数据。

（3） 事务日志备份：仅备份数据库的日志文件。

二． 数据库的恢复模型

（1） 简单恢复模型

使用简单恢复模型可以将数据库恢复到最新的备份，但无法将数据库还原到故障点或特定的检查点。这种模式下只备份数据库，不备份日志。

（2） 完全恢复模型

完全恢复模型是默认的恢复模型也是最安全的恢复模型，这种模型必须同时备份数据库和日志。

在完全恢复模型下将记录大容量数据操作的完整日志，因此可以将数据库恢复到故障点状态。

（3） 大容量日志恢复模型：

与完全恢复模型相似，这种模型也必须同时备份数据库和日志。

此模式下，记录大容量数据操作的日志没有完全恢复模式详细，所有能够恢复的数据会比完全恢复模式要少。

经典的SQL语句

首先,简要介绍基础语句：

1、创建数据库

CREATE DATABASE database-name

2、删除数据库

drop database dbname

3、备份sql server

--- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice ''disk'', ''testBack'', ''c:\mssql7backup\MyNwind_1.dat''

--- 开始备份

BACKUP DATABASE pubs TO testBack

4、创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、删除新表drop table tabname

6、增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、添加主键： Alter table tabname add primary key(col)

删除主键： Alter table tabname drop primary key(col)

8、创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、创建视图：create view viewname as select statement

删除视图：drop view viewname

10、几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’

排序：select * from table1 order by field1,field2 [desc]

总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

11、几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、使用外连接

A、left outer join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

sql: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

其次，大家来看一些不错的sql语句

1、复制表(只复制结构,源表名：a 新表名：b) (Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in ''"&Server.MapPath(".")&"\data.mdb" &"'' where..

4、子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11、四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

12、日程安排提前五分钟提醒

sql: select * from 日程安排 where datediff(''minute'',f开始时间,getdate())>5

13、一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、前10条记录

select top 10 * form table1 where 范围

15、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、随机取出10条数据

select top 10 * from tablename order by newid()

18、随机选择记录

select newid()

19、删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

20、列出数据库里所有的表名

select name from sysobjects where type=''U''

21、列出表里的所有的

select name from syscolumns where id=object_id(''TableName'')

22、列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when ''A'' then pcs else 0 end),sum(case vender when ''C'' then pcs else 0 end),sum(case vender when ''B'' then pcs else 0 end) FROM tablename group by type

显示结果：

type vender pcs

电脑 A 1

光盘 B 2

光盘 A 2

手机 B 3

手机 C 3

23、初始化表table1

TRUNCATE TABLE table1

24、选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）

Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)

sql server:select top n * from 表名 order by newid()

三、技巧

1、1=1，1=2的使用，在SQL语句组合时用的较多

"where 1=1" 是表示选择全部 "where 1=2"全部不选，

如：

if @strWhere !=''''

begin

set @strSQL = ''select count(*) as Total from ['' + @tblName + ''] where '' + @strWhere

end

else

begin

set @strSQL = ''select count(*) as Total from ['' + @tblName + '']''

end

我们可以直接写成

set @strSQL = ''select count(*) as Total from ['' + @tblName + ''] where 1=1 安定 ''+ @strWhere

2、收缩数据库

--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

--收缩数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

3、压缩数据库

dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限

exec sp_change_users_login ''update_one'',''newname'',''oldname''

5、检查备份集

RESTORE VERIFYONLY from disk=''E:\dvbbs.bak''

6、修复数据库

ALTER DATABASE [dvbbs] SET SINGLE_USER

DBCC CHECKDB(''dvbbs'',repair_allow_data_loss) WITH TABLOCK

ALTER DATABASE [dvbbs] SET MULTI_USER

7、日志清除

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

USE tablename -- 要操作的数据库名

SELECT @LogicalFileName = ''tablename_log'', -- 日志文件名

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = 1 -- 你想设定的日志文件的大小(M)

-- Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size

FROM sysfiles

WHERE name = @LogicalFileName

SELECT ''Original Size of '' + db_name() + '' LOG is '' +

CONVERT(VARCHAR(30),@OriginalSize) + '' 8K pages or '' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ''MB''

FROM sysfiles

WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

SELECT @StartTime = GETDATE(),

@TruncLog = ''BACKUP LOG '' + db_name() + '' WITH TRUNCATE_ONLY''

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)

AND (@OriginalSize * 8 /1024) > @NewSize

BEGIN -- Outer loop.

SELECT @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES (''Fill Log'')

DELETE DummyTrans

SELECT @Counter = @Counter + 1

END

EXEC (@TruncLog)

END

SELECT ''Final Size of '' + db_name() + '' LOG is '' +

CONVERT(VARCHAR(30),size) + '' 8K pages or '' +

CONVERT(VARCHAR(30),(size*8/1024)) + ''MB''

FROM sysfiles

WHERE name = @LogicalFileName

DROP TABLE DummyTrans

SET NOCOUNT OFF

8、更改某个表

exec sp_changeobjectowner ''tablename'',''dbo''

9、存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch

@OldOwner as NVARCHAR(128),

@NewOwner as NVARCHAR(128)

DECLARE @Name as NVARCHAR(128)

DECLARE @Owner as NVARCHAR(128)

DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR

select ''Name'' = name,

''Owner'' = user_name(uid)

from sysobjects

where user_name(uid)=@OldOwner

order by name

OPEN curObject

FETCH NEXT FROM curObject INTO @Name, @Owner

WHILE(@@FETCH_STATUS=0)

BEGIN

if @Owner=@OldOwner

begin

set @OwnerName = @OldOwner + ''.'' + rtrim(@Name)

exec sp_changeobjectowner @OwnerName, @NewOwner

end

-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner

END

close curObject

deallocate curObject

10、SQL SERVER中直接循环写入数据

declare @i int

set @i=1

while @i<30

begin

insert into test (userid) values(@i)

set @i=@i+1

end

posted @ 2009-10-27 17:59 旭坤阅读(1217) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

旭坤的博客

Reality Save and Hope---.NET小菜鸟!种子必须时刻汲取充分的营养才可以长成参天大树！我是一颗小小小草！不停的汲取，厚积而薄发！

SQL Server必知必会

公告