前言
所有Example在SQL2000的查询分析器(Microsoft SQL Server/80/Tools/Binn/isqlw.exe)执行通过。(*以下Example 简记为EX))
* append file 分支包括函数类型列表,数据类型列表。
一、 基本DML
基本DML的增加数据(insert)、更改数据(update) 、删除数据(delete) 、查询数据(query)四种模式:
1.增加数据:
· INSERT INTO table_name (column1,column2,...) VALUES 二、 复杂查询
1.组合查询:
组合查询是指所查询得数据来源并不只有单一的表格,而是联合一个以上的表格才能够得到结果的。
SELECT *
FROM table1,table2
WHERE table1.colum1=table2.column1
说明:
1.查询两个表格中其中 column1 值相同的数据
2.当然两个表格相互比较的列,其数据形态必须相同
3.一个复杂的查询其用到的表格可能会很多个
EX)
SELECT * FROM employees a , employeeterritories b WHERE a.employeeid=b.employeeid
SELECT distinct lastname FROM employees a , employeeterritories b WHERE a.employeeid=b.employeeid (没发现有distinctrow???)
2.整合性的查询:
· 条数记数
SELECT COUNT (*)
FROM table_name
WHERE column_name = xxx
说明:
查询符合条件的数据条数
EX)
SELECT count(*) FROM employees
· 含有聚合函数:
在SQL查询分析器中打开对象浏览器可看到函数列表
以下是常用的聚合函数
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
说明:
EX)
SELECT sum(unitprice) FROM [order details] *** 表名分开的情况
GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM 等整合性查询的关键字一起使用
SELECT ProductID ,avg(unitprice) FROM [order details] group by ProductID
HAVING : HAVING 子句通常与 GROUP BY 子句结合使用一起使用作为整合性的限制
例如,下列 WHERE 子句仅限定以高于 $100 的单价销售产品的订单,而 HAVING 子句进一步将结果限制为只包括 100 件以上的订单:
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
3.复合性的查询
SELECT *
FROM table_name1
WHERE EXISTS ( SELECT * FROM table_name2 WHERE conditions )
说明:
WHERE 的 conditions 可以是另外一个的 query
1.EXISTS 在此是指存在与否
SELECT *
FROM table_name1
WHERE column1 IN ( SELECT column1 FROM table_name2 WHERE conditions )
说明
IN 后面接的是一个集合,表示column1 存在集合里面SELECT 出来的数据形态必须符合 column1
EX)
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE not exists (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
4.其他查询 (like,between,top n)
SELECT *
FROM table_name1 WHERE column1 LIKE 'x%'
说明:
1.LIKE 必须和后面的'x%' 相呼应表示以 x为开头的字串
SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy
说明
1. BETWEEN 表示 column1 的值介於 xx 和 yy 之间
· 关于like
通配符 描述 示例
% 包含零个或更多字符的任意字符串。 WHERE title LIKE '%computer%' 将查找处于书名任意位置的包含单词 computer 的所有书名。
_(下划线) 任何单个字符。 WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字(Dean、Sean 等)。
[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。 WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如,Carsen、Larsen、Karsen 等。
[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。 WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。
SELECT CompanyName, ContactName, Phone, Fax
FROM Customers
WHERE CompanyName LIKE N'%snabbk?p'+'%'
ORDER BY CompanyName ASC, ContactName ASC; ===>***N的作用???
· 关于between
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
· 关于TOP N
SELECT TOP 5 orderid,productid,quantity from [order details] order by quantity desc ( value1,value2, ...)
1.若没有指定column 系统则会按表格内的列顺序填入数据
2.列的数据形态和所填入的数据必须吻合
3.table_name 也可以是景观 view_name
· INSERT INTO table_name (column1,column2,...) SELECT columnx,columny,... FROM another_table
说明:也可以经过一个子查询(subquery)把别的表格的数据填入
EX)
(OrderDate,RequiredDate,Freight,ShipName)
(CONVERT(DateTime,'2004/12/02 1:1:1'),Null,99,'GEC')
*该语句包含了日期,数字,字符,和空值的输入。其中日期类型为隐性转换 如果比较 char 和 datetime 表达式 SQL Server 可将它们自动转换。
OrderDate《==='2004/12/02 1:1:1'
2.更改数据:
· UPDATE table_name SET column1=value1, column2=value2 ... ... WHERE conditoins
1.更改某个列设定其值为'xxx'
2.conditions 是所要符合的条件、若没有 WHERE 则 整个 table 的那个列都会全部被更改
EX)
SET orderdate=' 2004-1-1 1:1 :1' ,freight= 99,shipname='Gecheng'
WHERE orderid=10248
3.删除数据:
· DELETE FROM table_name WHERE conditions
EX)
DELETE [Order Details]
FROM Suppliers, Products
WHERE Products.SupplierID = Suppliers.SupplierID
AND Suppliers.CompanyName = 'Lyngbysild'
AND [Order Details].ProductID = Products.ProductID
* [order details] 是一个空格分开的表名
4.查询数据:
· SELECT column1,columns2,... FROM 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]是指从大到小排列,若 没有指明[ASC],则是从小到大排列
EX)
SELECT * FROM employees WHERE employeeid>3 OR employeeid=1 ORDER BY employeeid ASC;
组合查询 , 整合性的查询 , 含有聚合函数 , 复合性的查询 , 其他查询(like,between.... ...) 后续
二、复杂查询
1.组合查询:
组合查询是指所查询得数据来源并不只有单一的表格,而是联合一个以上的表格才能够得到结果的。
SELECT *
FROM table1,table2
WHERE table1.colum1=table2.column1
说明:
1.查询两个表格中其中 column1 值相同的数据
2.当然两个表格相互比较的列,其数据形态必须相同
3.一个复杂的查询其用到的表格可能会很多个
EX)
SELECT * FROM employees a , employeeterritories b WHERE a.employeeid=b.employeeid
SELECT distinct lastname FROM employees a , employeeterritories b WHERE a.employeeid=b.employeeid
(没发现有distinctrow???)
2.整合性的查询:
· 条数记数
SELECT COUNT (*)
FROM table_name
WHERE column_name = xxx
说明:
查询符合条件的数据条数
EX)
SELECT count(*) FROM employees
· 含有聚合函数:
在SQL查询分析器中打开对象浏览器可看到函数列表
以下是常用的聚合函数
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
说明:
EX)
SELECT sum(unitprice) FROM [order details] *** 表名分开的情况
GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM 等整合性查询的关键字一起使用
SELECT ProductID ,avg(unitprice) FROM [order details] group by ProductID
HAVING : HAVING 子句通常与 GROUP BY 子句结合使用一起使用作为整合性的限制
例如,下列 WHERE 子句仅限定以高于 $100 的单价销售产品的订单,而 HAVING 子句进一步将结果限制为只包括 100 件以上的订单:
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
3.复合性的查询
SELECT *
FROM table_name1
WHERE EXISTS ( SELECT * FROM table_name2 WHERE conditions )
说明:
WHERE 的 conditions 可以是另外一个的 query
1.EXISTS 在此是指存在与否
SELECT *
FROM table_name1
WHERE column1 IN ( SELECT column1 FROM table_name2 WHERE conditions )
说明
IN 后面接的是一个集合,表示column1 存在集合里面SELECT 出来的数据形态必须符合 column1
EX)
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE not exists (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
4.其他查询 (like,between,top n)
SELECT *
FROM table_name1 WHERE column1 LIKE 'x%'
说明:
1.LIKE 必须和后面的'x%' 相呼应表示以 x为开头的字串
SELECT * FROM table_name1 WHERE column1 BETWEEN xx AND yy
说明
1. BETWEEN 表示 column1 的值介於 xx 和 yy 之间
· 关于like
通配符 描述 示例
% 包含零个或更多字符的任意字符串。 WHERE title LIKE '%computer%' 将查找处于书名任意位置的包含单词 computer 的所有书名。
_(下划线) 任何单个字符。 WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字(Dean、Sean 等)。
[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。 WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如,Carsen、Larsen、Karsen 等。
[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。 WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。
SELECT CompanyName, ContactName, Phone, Fax
FROM Customers
WHERE CompanyName LIKE N'%snabbk?p'+'%'
ORDER BY CompanyName ASC, ContactName ASC; ===>***N的作用???
· 关于between
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
· 关于TOP N
SELECT TOP 5 orderid,productid,quantity from [order details] order by quantity desc
三、外联接的使用
仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。
而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行,只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。
Microsoft SQL Server 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字:
LEFT OUTER JOIN 或 LEFT JOIN
RIGHT OUTER JOIN 或 RIGHT JOIN
FULL OUTER JOIN 或 FULL JOIN
SQL Server 支持 SQL-92 外联接语法,以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义,而旧式 Transact-SQL 外联接有时会产生歧义,因此建议使用 SQL-92 语法。
· 使用左向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。
若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果:
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
不管是否与 publishers 表中的 city 列匹配,LEFT OUTER JOIN 均会在结果中包含 authors 表的所有行。注意:结果中所列的大多数作者都没有相匹配的数据,因此,这些行的 pub_name 列包含空值。
· 使用右向外联接
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明:不管第一个表中是否有匹配的数据,结果将包含第二个表中的所有行。
若要在结果中包括所有的出版商,而不管城市中是否还有出版商居住,请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果:
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
· 使用完整外部联接
若要通过在联接结果中包括不匹配的行保留不匹配信息,请使用完整外部联接。Microsoft? SQL Server? 2000 提供完整外部联接运算符 FULL OUTER JOIN,不管另一个表是否有匹配的值,此运算符都包括两个表中的所有行。
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 FULL OUTER JOIN 运算符指明:不管表中是否有匹配的数据,结果将包括两个表中的所有行。
若要在结果中包括所有作者和出版商,而不管城市中是否有出版商或者出版商是否住在同一个城市,请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果:
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
四、字符串函数
这些标量函数对字符串输入值执行操作,返回字符串或数字值。
返回字符表达式最左端字符的 ASCII 代码值。
根据 Unicode 标准所进行的定义,用给定整数代码返回 Unicode 字符。
用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
语法
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
由数字数据转换到字符数据。
返回字符串中从左,右边开始指定个数的字符。
LEFT ( character_expression , integer_expression )
返回由重复的空格组成的字符串。
语法
SPACE ( integer_expression )
以指定的次数重复字符表达式。
语法
REPLICATE ( character_expression , integer_expression )
*若要在 Unicode 数据中包括空格,请使用 REPLICATE 而非 SPACE。
返回字符、binary、text 或 image 表达式的一部分。有关可与该函数一起使用的有效 Microsoft® SQL Server™ 数据类型的更多信息,请参见数据类型。
语法
SUBSTRING ( expression , start , length )
返回给定字符串表达式的字符(而不是字节)个数,其中不包含尾随空格。
语法
LEN ( string_expression )
返回将大写小写字符数据转换的字符表达式。
去空格后返回一个字符串
... ...
五、日期和时间函数
函数 确定性
DATEADD 具有确定性
DATEADD ( datepart , number, date )
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
datepart 是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 缩写
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
DATEDIFF 具有确定性
返回跨两个指定日期的日期和时间边界数。
DATEDIFF ( datepart , startdate , enddate )
DATENAME 不具有确定性
返回代表指定日期的指定日期部分的字符串。
DATENAME ( datepart , date )
DATEPART 除了用作 DATEPART (dw, date) 外都具有确定性。dw 是工作日的日期部分,取决于由设置每周第一天的 SET DATEFIRST 所设置的值。
SELECT DATEPART(weekday,GETDATE()) AS 'WORD DAY'
DAY 具有确定性
MONTH 具有确定性
YEAR 具有确定性
GETDATE 不具有确定性
按 datetime 值的 Microsoft? SQL Server? 标准内部格式返回当前系统日期和时间。
2004-12-02 00:31:30.840
GETUTCDATE 不具有确定性
返回表示当前 UTC 时间(世界时间坐标或格林尼治标准时间)的 datetime 值。当前的 UTC 时间得自当前的本地时间和运行 SQL Server 的计算机操作系统中的时区设置。
2004-12-01 16:33:12.107
六、转换函数
要转换的表达式(例如,销售报告要求销售数据从货币型数据转换为字符型数据)。
要将所给表达式转换到的数据类型,例如,varchar 或 SQL Server 提供的任何其它数据类型。
除非将被转换的值存储起来,否则转换仅在 CAST 或 CONVERT 函数的作用期内有效。
在下面的示例中,第一个 SELECT 语句中使用 CAST,第二个 SELECT 语句中使用 CONVERT,将 title 列转换为 char(50) 列,以使结果更可读:
SELECT CAST(title AS char(50)), ytd_sales
SELECT CONVERT(char(50), title), ytd_sales
----------------------------------------- -----------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
在下面的示例中,int 类型的ytd_sales 列转换为 char(20) 列,从而可以对该列使用 LIKE 谓词:
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
----------------------------------------- ---------
Fifty Years in Buckingham Palace Kitchens 15096
SQL Server 自动处理某些数据类型的转换。例如,如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式,SQL Server 可将它们自动转换。这种转换称为隐性转换。对这些转换不必使用 CAST 函数。但是,在下列情况下使用 CAST 都是可以接受的:
如果试图进行不可能的转换(例如,将含有字母的 char 表达式转换为 int 类型),SQL Server 将显示一条错误信息。
如果转换时没有指定数据类型的长度,则 SQL Server 自动提供长度为 30。
要转换为 float 或 real 数据类型的 char 或 varchar 类型表达式还可选择性地包含指数符号(e 或 E,后面有可选的 + 或 - 符号,再后面是数字)。
SELECT SUBSTRING(title, 1, 25) AS Title, CONVERT(char(2), ytd_sales)
在进行数据类型转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则该值将被截断。例如,CAST(10.3496 AS money) 的结果是 $10.35。
style 参数
SELECT CONVERT(char(10), GETDATE(), 111) + ' ' + CONVERT(char(8), GETDATE(), 108)
此语句把当前日期转换为样式 111,yyyy/mm/dd + hh:mm:ss
CAST 和 CONVERT
函数
在表中,左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
不带世纪数位 (yy) 带世纪数位 (yyyy)
标准
输入/输出**
- 0 或 100 (*) 默认值 mon dd yyyy hh:miAM(或 PM)
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yy
4 104 德国 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
- 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]
- 21 或 121 (*) ODBC 规范(带毫秒) yyyy-mm-dd hh:mm:ss[.fff]
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM
* 默认值(style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121)始终返回世纪数位 (yyyy)。