-
博文
2009年4月22日星期三
11 Birthday
日子过得真快,昨天是宇11岁生日了,慢慢长大了,父母在外.没有同其过生日,只有电话问候与祝生日快乐,正好前天也是其期中考试结果出来,本以为对自己生日的礼物,但成绩不太理想,加油啊...希望你过得越来越开心,学习进步;
2009年4月10日星期五
数据库面试题集合
摘录 http://blog.csdn.net/sandyzhs/archive/2009/04/09/4059709.aspx
在整理准备数据库面试的过程中，先是在网上一顿海搜，找到历史面试题，然后一个骨头一个骨头的啃完，现在基本上这些问题（或者说叫做实践）都没有问题了。遇到的困难是：PL/SQL居多，T-SQL太少，所以需要筛选，修改答案，甚至有一些在T-SQL里面还没有支持。
下一步再把数据库T-SQL经典教程在翻看一遍，基本上对数据库就算告一段落了，前前后后共整整1个多月的时间（去年10.1是二周，下载是三周），学习的还行吧。
下面的就是全部内容，大段摘录的，或者是抄的，我都写了出处；有一些实在忘记了，请见谅：向大家共享知识，想必也是作者的本愿吧。
1. 三个范式
即： 属性唯一, 记录唯一, 表唯一
第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段x → 非关键字段y
2. 一些常识：http://www.enet.com.cn/article/2007/0802/
A20070802755140.shtml
² 简要叙述一下SQL Server 2000中使用的一些数据库对象
表格、视图、用户定义的函数，存储过程，触发器等。
² NULL是什么意思
NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。
使用ISNULL（var，value）来进行NULL判断：当var为NULL的时候，var = value，并且返回value
² 什么是索引?SQL Server 2000里有什么类型的索引?
索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格，物理顺序和逻辑顺序一致。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。它允许每个表格有多个非聚集索引。
² 什么是主键?什么是外键?
主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表(可能是同一表)里的字段。那么这些相连的字段就是外键。
² 什么是触发器?SQL Server 2000有什么不同类型的触发器?
有INSTEAD-OF和AFTER两种触发器。触发器是一种专用类型的存储过程，它被捆绑到表格或者视图上。INSTEAD-OF触发器是替代数据操控语言(DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA的INSTEAD-OF-UPDATE触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。
AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。
² 您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?
第一个答案(而且是您希望听到的答案)是使用外键限制。外键限制用来维护引用的完整性integrity。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。
另外一种答案是触发器。触发器可以被用来保证以另外一种方式实现与限制相同的作用，但是它非常难设置与维护，而且性能一般都很糟糕。由于这个原因，微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。
² 对一个投入使用的在线事务处理表格(OLTP)有过多索引需要有什么样的性能考虑?
对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。
² 你可以用什么来确保表格里的字段只接受特定范围里的值?
Check限制，它在数据库表格里被定义，用来限制输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。
² 返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。
OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。
² 什么是相关子查询?如何使用这些查询?
相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。
11. 某一列允许NULL值，但希望确保所有的非空（Non-NULL）值都是唯一的
SQL Server没有实现非NULL值唯一性的内建机制，因此需要通过自定义的trigger：
Create trigger mytrigger on t1 for insert, update as
BEGIN
IF (select max(cnt) from (select count(i.c1)as cnt
from t1, inserted i where t1.c1=i.c1 group by i.c1) x) > 1
ROLLBACK TRAN
END
3. 某列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据
http://www.blogjava.net/looline/archive/2006/12/08/86367.html
** HAVING子句对GROUP BY子句设置条件的方式与WHERE子句和SELECT语句交互的方式类似。WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。
² 显示数据库中的最后一条记录的所有字段（ID是自增的）
SELECT top 1 * FROM Table_Name ORDER BY ID DESC -- 或者
SELECT * FROM Table_Name WHERE ID=（SELECT MAX (ID) FROM Table_Name）
² 显示数据库中的最后十条记录的所有字段（ID 是自增的 DESC 做降序 ASC 做升序）
SELECT top 10 * FROM Table_Name ORDER BY ID DESC
² 对来自表 authors 的前十个作者的 state 列进行更新
UPDATE s SET s.saleprice = s.saleprice+2
FROM (SELECT TOP 10 * FROM sales ORDER BY saleid) AS t1, sales s
WHERE s.saleid=t1.saleid
-- 或者
UPDATE s SET s.saleprice = s.saleprice+2 FROM sales s
WHERE s.saleid in (SELECT TOP 10 saleid FROM sales ORDER BY saleid)
² 找出公司里收入最高的前三名员工
select top 3 * from t1 order by a desc --或者
Select top 3 *, ROW_NUMBER() OVER(order by a DESC) as No from t1
(根据分析，执行计划中的顺序：sort (order by )+ top 3, 然后是where等条件)
² 找出公司里收入最高(低)的三->五名员工
select top 3 a from t1 where a in ( select top 5 a from t1 order by a asc) order by a desc
--弊端：参与排序的一定要是index，或者unique，且选出来的只能是单一的一个列
-- 所以用下面的方法
SELECT top (10-3+1) * FROM (SELECT TOP 10 * FROM customers Order by zip asc)t order by zip desc
² 取出表A中第31条到第40条记录（SQLServer，以自动增长的ID为主键，注意：ID可能不是连续的。）
-- top 10 可以省略
SELECT top 10 * FROM A WHERE ID not in (SELECT top 30 id FROM A)
² 显示出员工的平均工资大于3000元的部门名称（用SQL语句）
注意Full outer join，left join, right join，inner join区别和联系
SELECT Dept_Name
FROM t_Dept
WHERE ID in (SELECT Dept_IDFROM t_Salary
GROUP BY Dept_ID --对部门分组（即：相同部门的，进行同一操作）
Having avg(Salary)>3000)
² 找出那些工资高于他们所在部门的平均工资的员工
select last_name, dept_id, salary from s_emp a
where salary>(select avg(salary) from s_emp where dept_id=a.dept_id)
² 找出那些工资高于他们所在部门的 manager 的工资的员工。
select id, last_name, salary, manager_id from s_emp a
where salary>(select salary from s_emp where id=a.manager_id)
² 有两个表分别如下：
表A(varchar(32) NAME,int GRADE)
数据：ZHANGSHAN 80, LISI 60, WANGWU 84
表B(varchar(32) NAME,int AGE)
数据：ZHANGSHAN 26, LISI 24, WANGWU 26, WUTIAN 26
1）写SQL语句得到如下查询结果：
NAME GRADE AGE
ZHANGSHAN 80 26
LISI 60 24
WANGWU 84 26
WUTIAN NULL 26
答：select * from A right join B on A.NAME = B.NAME
2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。
答：avg(grade) group by name, age
4. 横表竖起来
请写出 SQl 语句实现题目要求的结果:写一个 SQL完成左边的表变成右边的表。
表的结构
要求结果
ProductID SALE_YEAR SALES
001 2001 10
002 2001 15
003 2002 12
003 2003 10
productID 2001 2002 2003
001 10
002 15
003 12 10
² 交叉表的列数是确定的
select name,
sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test group by name
² 交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
5. SQLServer删除重复数据记录
http://www.cnblogs.com/luohoufu/archive/2008/06/05/1214286.html
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复。
² 写出 SQl 语句（或 SQL 语句组），查询所有 id_no 重复的记录。
select dept_ID from salary
group by dept_ID having count(dept_ID) > 1
² 对于第一种重复，比较容易解决，使用
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
truncate table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
² 这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下：
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
² 部分关键字段重复，且记录中有ID. (***这个比较实用***)
第一种方法可一次删除所有重复的..(只保留重复中ID最小的记录)。
delete from table where id not in ( select min(id) from table group by name)第二种方法每次只删除重复中ID最大的一条记录。
delete from table where id in ( select max(id) from table group by name having count(*)>1)
² 使用SQL程序删除
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
² 自己还得出的办法：
select * from user1 where id not in (select top 1 id from user1 where name = user1.name) -- 两个name相等比较重要，否则就不对了。但是group by更加好一些
删就这样写
delete from user1 where id not in (select top 1 id from user1 where name=user1.name)
或
delete from user where id not in ( select max(id) from user where name=user.name)
delete from user where id not in (select max(id) from user group by name having count(*) > 1)
其他方法
A:保留id最大(或者最小)的行,删除其它行
--方法1
delete [user] from [user] t
inner join(select name,max(id) as id from [user] group by name) a
on t.name = a.name and t.id <> a.id
--方法2
delete [user] from [user] t
where exists(select * from [user] where name = t.name and id > t.id)
B:删除所有重复的name行,一行也不留
delete [user] from [user] t
inner join
(select id from [user] a where exists(select * from [user] where name = a.name group by name having count(*) > 1)) as b
on t.id = b.id
6. 一些高难度的SQL
http://www.teecool.com/post/2007072807.html
² 如果表的结构和数据：
表1：usertable
USERID USERNAME
1 user1
2 null
3 user3
4 null
5 user5
6 user6
表2： usergrade;
USERID USERNAME GRADE
1 user1 90
2 null 80
7 user7 80
8 user8 90
那么，执行语句 select count(*) from usergrade where username not in (select username from usertable);
select count(*) from usergrade g where not exists (select null from usertable t where t.userid=g.userid and t.username=g.username);
结果为：语句1( 0 ) 语句2 ( 3 ) A: 0 B:1 C:2 D:3 E:NULL --- 不懂
² 在以下的表的显示结果中，以下语句的执行结果是（知识点：in/exists+rownum）
SQL> select * from usertable;
USERID USERNAME
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
那么，执行语句Select count(*) from usertable t1 where username in
(select username from usergrade t2);
Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username );
以上语句的执行结果是：( c) (c ) A: 0 B: 1 C: 2 D: 3
count(*)和count(GRADE)有区别：前者统计NULL行；后者忽略NULL行
² 关联更新
有表一的查询结果如下，该表为学生成绩表select id,grade from student_grade
ID GRADE
1 50
2 40
3 70
4 80
5 30
6 90
表二为补考成绩表 select id,grade from student_makeup
ID GRADE
1 60
2 80
5 60
执行语句：
update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id);
-- 这里，把1、2、5更新为对应的结果，但是3、4、6也会被更新，但是由于没有对应的值，所以都被更新为了NULL
请问之后查询： select GRADE from student_grade where id = 3;结果为： (c)
A: 0 B: 70 C: null D: 以上都不对
7. 英文
http://www.teecool.com/post/2007072808.html
² Question 1： calculating the Number of Days in a Month
declare @now datetime
--select @now = getdate()
select @now = '20090211'
-- 方法是：下月日-本月日的日期差。所以先构造本月日，在构造下月日，减
select datediff(dd, dateadd(dd, -1* datepart(dd, @now) + 1, @now),
dateadd(MM, 1, dateadd(dd, -1* datepart(dd, @now) + 1, @now)) )
-- 方法：下月日的前一天就是本月的月末，这一天的‘dd’就是本月天数。
select datepart(dd,
dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)
+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))));
² Question2：How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?
select bookid,bookname,
price=case when price is null then 'unknown'
when price between 10 and 20 then '10 to 20' else price end
from books
Question3：to find duplicate values! Find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result:
au_lname number_dups
Ringer 2
Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname
--count(1)和count(*)结果一样，都是根据au_lname分组进行的组内全部统计
² Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quality. Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: 85 0 85 0 0
Fricative 60 35 0 0 25
Total 250 60 165 0 25
Answer 4：用动态SQL实现，前面有。不过看起来很复杂
use pubs
declare @s varchar(8000)
set @s='select isnull(stores.stor_name,''Total'') '
select @s=@s + ',[Qtr' + cast(datepart(q,ord_date) as char(1))
+ ']=sum(case when datepart(q,ord_date)='''
+ cast(datepart(q,ord_date) as char(1)) + ''' then qty else 0 end)'
from Sales
group by datepart(q,ord_date)
set @s=@s + ' ,sum(qty) "Total" from Sales inner join stores on Sales.stor_id = stores.stor_id '
set @s=@s + ' where datepart(yyyy,ord_date) = ''1993'' '
set @s=@s + ' group by stores.stor_name WITH ROLLUP '
set @s=@s + ' order by stores.stor_name '
print @s
exec(@s)
-- print @s的结果是这样的：
select isnull(stores.stor_name,'Total') , -- isnull把rollup造成的NULL值改成了Total，技巧
[Qtr1]=sum(case when datepart(q,ord_date)='1' then qty else 0 end),
[Qtr2]=sum(case when datepart(q,ord_date)='2' then qty else 0 end),
[Qtr3]=sum(case when datepart(q,ord_date)='3' then qty else 0 end),
[Qtr4]=sum(case when datepart(q,ord_date)='4' then qty else 0 end),
sum(qty) "Total"
from Sales inner join stores on Sales.stor_id = stores.stor_id
where datepart(yyyy,ord_date) = '1993'
group by stores.stor_name WITH ROLLUP -- rollup是一个很重要的东西
order by stores.stor_name -- order by，貌似不需要，或者用desc更好
² Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
Tips: sp_recompile can recomplie a store procedure each time
Answer 5：在执行存储过程时,使用 with recompile 选项强制编译新的计划；使用sp_recompile系统存储过程强制在下次运行时进行重新编译
Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
line-no title_id
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
Answer 6：
-- row_number()的这种用法据我了解不行，他必须和over连用
select row_number() over(order by title_id) as line_no ,title_id from titles
² Question 7: the difference of two SQL statements at performance of execution?
Statement 1:
if NOT EXISTS ( select * from publishers where state = ‘NY’)
SELECT ‘Sales force needs to penetrate New York market’
else
SELECT ‘We have publishers in New York’
Statement 2:
if EXISTS ( select * from publishers where state = ‘NY’)
SELECT ‘We have publishers in New York’
else
SELECT ‘Sales force needs to penetrate New York market’
Answer 7：
不同点:初步感觉应该是第二个性能比较好，因为不需要遍历整个数据表，只要有一个存在的就可以结束。但是从执行计划的时间和IO看，结果是一样的。
² Question9: How can I get a list of the stores that have bought both ‘bussiness’ and ‘mod_cook’ type books?
In database pubs, use three table stores,sales and titles to implement this requestment. Now I want to get the result as below:
stor_id stor_name
...
7896 Fricative Bookshop
...
...
...
Answer 9：
use pubs
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook');
-- 或者使用个连续的join也可以
select distinct a.stor_id, a.stor_name from stores a
join sales b on a.stor_id=b.stor_id
join titles c on b.title_id=c.title_id and c.type='business'
and exists (select * from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type title price
business The Busy Executive’s Database Guide 19.9900
...
...
Answer 11：
select distinct t1.price, t1.type from titles t1, titles t2
where t1.price <
( select avg(price) from titles t2 where t1.type = t2.type group by type )
-- 或者
select distinct t1.price, t1.type from titles t1
join titles
on t1.price <
( select avg(price) from titles t2 where t1.type = t2.type group by type )
8. 其他
http://www.teecool.com/post/2007071809.html
² 有订单表SO_Table，单号字段RefNo VARCHAR(10)，需要实现自动编号，格式为YYYYMMXXXX，其中XXXX为序号，如：2004050001，2004050002……2004059999等，采用Transact-SQL实现新订单编号的思路。
思路：
1，IDENTITY(smallint, 100, 1)只有在select…into这样的数据插入里面可用；
2，6位长度的年月可以用：convert(char(6), getdate(), 112)；
或者cast(datepart(YY, @date) as varchar(4)) +
right(cast(datepart(MM, @date)+100 as varchar(3)), 2)
3， 如果必须在SP里面工作的话，可以获得上次的最大序号，加1
² 有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何确何数据的完整性。
使用trigger可以做到；另外，添加过程要在一个transaction中进行；
² 如何求表中相邻(按聚集索引相邻)的两条记录的某字段的值之差。
select s2.saleid, p=(s1.saleprice-s2.saleprice)
from
(select *, id=row_number() over(order by saleid) from sales) s1,
(select *, id=row_number() over(order by saleid) from sales) s2
where s2.id-s1.id=1
-- 下面的办法不行，因为如果相邻的ID不是连续的就不行了。另外，可以使用cursor
select a.source - b.source from test a, test b
where (a.id - b.id) = 1
order by a.id
² 如何删除表中的重复数据。上面有答案。
² 人员情况表（employee）中字段包括，员工号（ID），姓名（name），年龄（age），文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。结果如下：
学历 年龄 人数 百分比
本科以上 20 34 14
大专 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
。。。。。。
Transact-SQL查询语句如何写？
-- Count(*) * 100 由group by限定；SELECT Count(*) FROM是总数。
SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,
Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比
FROM employee GROUP BY wh,age
² 表一(AAA)
商品名称a 商品总量b
A 100
B 120
表二(BBB)
商品名称a 出库数量b
A 10
A 20
B 10
B 20
B 30
用一条Transact-SQL语句算出商品A,B目前还剩多少？
select sum(b)
from (select * from a
union all select a, b*(-1) from b) x
group by a
² 找到连续编号中断的那一个的最小值
select min(t.id) as id from (select id=id+1 from tt) t
where id not in (select id from tt)
9. 实际应用
A) 为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
² 查询选修课程名称为’税收基础’的学员学号和姓名
Select SN,SD FROM S -- 实际上，使用3个join才是合理的
Where [S#] IN(
Select [S#] FROM C,SC
Where C.[C#]=SC.[C#]
AND CN=N'税收基础')
² 查询选修课程编号为’C2’的学员姓名和所属单位
Select S.SN,S.SD FROM S,SC
Where S.[S#]=SC.[S#]
AND SC.[C#]='C2'
² 查询不选修课程编号为’C5’的学员姓名和所属单位
Select SN,SD FROM S
Where [S#] NOT IN( -- not in是正确的。不能使用=，会遗漏
Select [S#] FROM SC
Where [C#]='C5')
² 4. 查询选修全部课程的学员姓名和所属单位
-- 1）RIGHT JOIN可产生NULL行；2）COUNT(*),COUNT(S#)的结果不同
-- 解释：如果COUNT(*)<>COUNT(S#)，
-- 说明C中有某个C#没有在sc中出现，也就是说：这门课程没有被S#同学选中。
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
RIGHT JOIN
C ON SC.[C#]=C.[C#] GROUP BY [S#]
HAVING COUNT(*)=COUNT([S#]))
² 5. 查询选修了课程的学员人数
Select 学员人数=COUNT(DISTINCT [S#]) FROM SC
² 6. 查询选修课程超过5门的学员学号和所属单位
-- 关注having子句中的count()等聚合函数的使用
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)
B)该题目中，很多都有歧义，所以只要掌握的基本的方法，不需要深究细节。
/*
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
*/
² 找出没有选修过“李”老师讲授课程的所有学生姓名
select distinct sname from s
where s.sno not in --因为有这种情况：S.SNO不在SC中存在。
(select sno from sc,c where sc.CNO=c.cno and cteacher = '李')
² 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
Select SNO FROM SC Where SCGRADE<60
GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2
)A -- 实际上，使用 IN 进行条件连接也行
-- where控制条件，having控制分组条件并聚合
Where S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
--下面是错误结果：它只是把没有及格的科目进行了avg，并不是所有的科目的avg
select avg(sc.scgrade), s.sname
from s
join sc on sc.sno = s.sno and sc.scgrade < 60
group by s.sname having count(sc.scgrade)>=2
² 列出既学过“c1”号课程，又学过“c2”号课程的所有学生姓名
select s.sname
from s, (select * from sc
where sc.sno in (select sc.sno from sc where sc.cno = 'c1')
and sc.cno = 'c2') SS -- 把sc.sno in换为exists也可以。两种方案
where s.sno = ss.sno
² 列出“c1”号课成绩比“s2”号同学该门课成绩高的所有学生的学号
select sc.sno, sc.scgrade from sc
where sc.cno='c1' and
sc.scgrade>(select sc.scgrade from sc where sc.cno='c1' and sc.sno='s2')
² 列出“c1”号课成绩比“c2”号课成绩高的所有学生的学号及其“c1”号课和“c2”号课的成绩
select sc1.sno, sc1.cno, sc1.scgrade
from sc sc1,
(select sc1.sno, sc1.cno, sc1.scgrade
from sc sc1, sc sc2
where sc1.cno='c1' and sc2.cno='c2' and sc1.scgrade>sc2.scgrade
and sc1.sno=sc2.sno) sc2
where sc2.sno=sc1.sno and (sc1.cno='c1' or sc1.cno='c2')
1. 老外的一套题
我需要一个模式和一些数据来运行所要考核的查询，列表A创建了所需的这些数据：
IF OBJECT_ID('Sales')>0 DROP TABLE Sales
GO
IF OBJECT_ID('Customers')>0 DROP TABLE Customers
GO
IF OBJECT_ID('Products')>0 DROP TABLE Products
GO
CREATE TABLE Customers(CustomerID INT IDENTITY PRIMARY KEY ,
FirstName VARCHAR(50),
LastName VARCHAR(50),
City VARCHAR(50),
State CHAR(2),
Zip VARCHAR(10) )
GO
CREATE TABLE Products(
ProductID TINYINT IDENTITY PRIMARY KEY ,
ProductName VARCHAR(20),
RecommendedPrice MONEY,
Category VARCHAR(10))
GO
CREATE TABLE Sales(SaleID INT IDENTITY PRIMARY KEY ,
ProductID TINYINT NOT NULL REFERENCES Products(ProductID),
CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
SalePrice MONEY NOT NULL,SaleDate SMALLDATETIME NOT NULL)
GO
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('DVD',105,'LivingRoom')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Microwave',98,'Kitchen')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Monitor',200,'Office')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Speakers',85,'Office')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Refrigerator',900,'Kitchen')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('VCR',165,'LivingRoom')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('CoffeePot',35,'Kitchen')
GO
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('John','Miller','Asbury','NY','23433')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Fred','Hammill','Basham','AK','85675')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Stan','Mellish','Callahan','WY','38556')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Adrian','Caparzo','Denver','CO','12377')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Mike','Horvath','Easton','IN','47130')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Irwin','Wade','Frankfurt','KY','45902')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('George','Marshall','Gallipoli','ND','34908')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Frank','Costello','Honolulu','HI','23905')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Billy','Costigan','Immice','SC','75389')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Shelly','Sipes','Lights','AZ','35263')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Chirsty','Melton','Spade','CA','97505')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Amanda','Owens','Flask','CN','50386')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Brittany','Smits','Bourbon','KY','24207')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Kristy','Bryant','Tarp','FL','58960')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Kelly','Street','TableTop','ID','57732')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Tricia','Hill','Camera','ME','46738')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Holly','Raines','Compact','MS','35735')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Natalie','Woods','Woods','IN','87219')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Wendy','Hilton','Action','KY','47093')
GO
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,1,130,'2/6/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,2,97,'1/7/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,3,200,'8/8/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,4,80,'4/9/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,899,'10/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,6,150,'10/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,7,209,'12/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,8,90,'5/13/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,9,130,'6/14/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,14,85,'6/19/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,15,240,'9/20/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,16,99,'7/21/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,17,87,'3/22/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,18,99,'1/23/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,19,150,'3/24/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,900,'3/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,6,86,'8/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,7,88,'8/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,8,198,'12/13/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,9,150,'5/14/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,14,99,'7/19/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,15,104,'9/20/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,16,270,'2/21/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,17,90,'7/22/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,1,130,'3/6/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,2,102,'4/7/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,3,114,'11/8/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,4,1000,'5/9/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,1100,'10/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,6,285,'6/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,7,87,'10/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,8,300,'7/13/2005')
GO
² 1：返回在2005年10月售出的所有产品的名称、价格和客户姓名，答案：
select c.FirstName,c.LastName, p.productname, s.SalePrice, s.saledate from products p, customers c, sales s
where s.productid = p.productid and c.customerid = s.customerid
and datepart(YY, s.saledate) = 2005 and datepart(MM, s.saledate) = 10
--或者下面的也行。根据执行计划，二者是完全一样的。
SELECT c.FirstName,c.LastName,p.ProductName,s.SalePrice
FROM Sales s
INNER JOIN Customers c ON s.CustomerID=c.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
WHERE s.SaleDate>='10/1/2005'AND s.SaleDate<'11/1/2005'
² 2：返回没有购买产品并且位于客户表格上的人的姓名及其客户ID，答案:
select c.FirstName,c.LastName, c.customerid
from customers c
where c.customerid not in ( select distinct customerid from sales)
--或者下面的。下面的是提供的答案；但是上面的效率更高，二者比42：58
SELECT c.CustomerID,c.FirstName,c.LastName
FROM Sales s
RIGHT OUTER JOIN Customers c ON s.CustomerID=c.CustomerID
WHERE s.CustomerID IS NULL
3：返回客户姓名、销售价格、建议售价、建议售价和实际价格的差额，该差额必需是正数，答案见列表D：
SELECT c.FirstName,c.LastName,s.SalePrice,p.RecommendedPrice,
ABS(s.SalePrice-p.RecommendedPrice) AS diff
FROM Sales s
INNER JOIN Customers c ON s.CustomerID=c.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
² 4：根据产品类别计算平均价格，答案见列表E：
SELECT avg(s.SalePrice) as avg, p.category
FROM Sales s inner join products p
on s.productid = p.productid group by p.category
² 5：将以下的客户和销售信息加入到数据库中：
FirstName:Chris， LastName:Kringle， City:Henryville， State:IN， Zip:47126
ProductID:3， SalePrice:205， SaleDate:12/31/2005
答案见列表F（关注SCOPE_IDENTITY()，这里可以和@@identity互换）：
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Chris','Kringle','Henryville','IN','47126')
INSERT INTO Sales(CustomerID,ProductID,SalePrice,SaleDate)
VALUES(SCOPE_IDENTITY(),3,205,'12/31/2005')
² 6：从数据库中删除来自缅因洲(‘ME’)的客户，答案见列表G：(不过，delete s from...这样的用法很怪，貌似这样就可以把s这个名字引入进来了)
delete s from Sales s where s.CustomerID in
(select CustomerID from Customers where State = 'ME')
-- 或者下面的：（因为不支持delete from A, B这样的语法，所以用join）
DELETE s FROM Sales s JOIN Customers c
ON s.CustomerID=c.CustomerID WHERE c.State='ME'
-- 然后
DELETE c FROM Customers c WHERE c.State='ME'
² 7：返回客户购买了两个或多个产品的平均售价和产品类别，答案：
SELECT AVG(s.SalePrice) avg, p.Category
FROM Sales s, Products p
where s.ProductID=p.ProductID and s.CustomerID in
( SELECT s.CustomerID FROM Sales s
GROUP BY s.CustomerID HAVING COUNT(CustomerID)>=2)
GROUP BY p.Category
-- 或者
SELECT AVG(s.SalePrice) avg, p.Category
FROM Sales s INNER JOIN
( SELECT s.CustomerID FROM Sales s
GROUP BY s.CustomerID HAVING COUNT(CustomerID)>=2)x
ON s.CustomerID=x.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
GROUP BY p.Category
² 8：将销售在2005年6月10日到6月20日之间的产品的销售价格升级为建议售价：（这个还是不太明白，尤其是s.ProductID=s.ProductID，怪）
UPDATE s SET SalePrice=p.RecommendedPrice
FROM Sales s INNER JOIN Products p ON s.ProductID=s.ProductID
WHERE SaleDate>='6/10/2005' AND SaleDate<'6/21/2005'
² 9：根据产品种类计算建议售价超过实际售价10元及以上的销售数量，答案见列表J：
SELECT count(s.SalePrice) count, p.Category
FROM Sales s
INNER JOIN Products p ON s.ProductID=p.ProductID
where p.RecommendedPrice - s.SalePrice >= 10
GROUP BY p.Category
-- 题目有歧义，所以，也可以这样做：
SELECT p.Category,COUNT(*)AS NumberOfSales
FROM Sales s
INNER JOIN Products p ON s.ProductID=p.ProductID
GROUP BY p.Category
HAVING AVG(p.RecommendedPrice)>=AVG(s.SalePrice)+10
² 10：不使用叠代构建，返回所由销售产品的销售日期，以及该日期之前的销售额统计，以及该日期之前的销售额统计，并按照该日期升序排列，答案：
SELECT s.SaleDate, s.SalePrice,
( SELECT SUM(SalePrice) FROM Sales s2 WHERE s2.SaleDate<=s.SaleDate )AS RunningTotal
FROM Sales s ORDER BY s.SaleDate ASC
迄今为止，只有2个人可以正确地回答出所有的问题。
平均大约为50-60%，如果表现高于这个平均，那么优秀的TSQL程序员，如果获得了90%以上的得分，那么他或她就是一位非常优异的程序员。
在整理准备数据库面试的过程中，先是在网上一顿海搜，找到历史面试题，然后一个骨头一个骨头的啃完，现在基本上这些问题（或者说叫做实践）都没有问题了。遇到的困难是：PL/SQL居多，T-SQL太少，所以需要筛选，修改答案，甚至有一些在T-SQL里面还没有支持。
下一步再把数据库T-SQL经典教程在翻看一遍，基本上对数据库就算告一段落了，前前后后共整整1个多月的时间（去年10.1是二周，下载是三周），学习的还行吧。
下面的就是全部内容，大段摘录的，或者是抄的，我都写了出处；有一些实在忘记了，请见谅：向大家共享知识，想必也是作者的本愿吧。
1. 三个范式
即： 属性唯一, 记录唯一, 表唯一
第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段x → 非关键字段y
2. 一些常识：http://www.enet.com.cn/article/2007/0802/
A20070802755140.shtml
² 简要叙述一下SQL Server 2000中使用的一些数据库对象
表格、视图、用户定义的函数，存储过程，触发器等。
² NULL是什么意思
NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。
使用ISNULL（var，value）来进行NULL判断：当var为NULL的时候，var = value，并且返回value
² 什么是索引?SQL Server 2000里有什么类型的索引?
索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格，物理顺序和逻辑顺序一致。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。它允许每个表格有多个非聚集索引。
² 什么是主键?什么是外键?
主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表(可能是同一表)里的字段。那么这些相连的字段就是外键。
² 什么是触发器?SQL Server 2000有什么不同类型的触发器?
有INSTEAD-OF和AFTER两种触发器。触发器是一种专用类型的存储过程，它被捆绑到表格或者视图上。INSTEAD-OF触发器是替代数据操控语言(DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA的INSTEAD-OF-UPDATE触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。
AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。
² 您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?
第一个答案(而且是您希望听到的答案)是使用外键限制。外键限制用来维护引用的完整性integrity。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。
另外一种答案是触发器。触发器可以被用来保证以另外一种方式实现与限制相同的作用，但是它非常难设置与维护，而且性能一般都很糟糕。由于这个原因，微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。
² 对一个投入使用的在线事务处理表格(OLTP)有过多索引需要有什么样的性能考虑?
对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。
² 你可以用什么来确保表格里的字段只接受特定范围里的值?
Check限制，它在数据库表格里被定义，用来限制输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。
² 返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。
OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。
² 什么是相关子查询?如何使用这些查询?
相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。
11. 某一列允许NULL值，但希望确保所有的非空（Non-NULL）值都是唯一的
SQL Server没有实现非NULL值唯一性的内建机制，因此需要通过自定义的trigger：
Create trigger mytrigger on t1 for insert, update as
BEGIN
IF (select max(cnt) from (select count(i.c1)as cnt
from t1, inserted i where t1.c1=i.c1 group by i.c1) x) > 1
ROLLBACK TRAN
END
3. 某列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据
http://www.blogjava.net/looline/archive/2006/12/08/86367.html
** HAVING子句对GROUP BY子句设置条件的方式与WHERE子句和SELECT语句交互的方式类似。WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。
² 显示数据库中的最后一条记录的所有字段（ID是自增的）
SELECT top 1 * FROM Table_Name ORDER BY ID DESC -- 或者
SELECT * FROM Table_Name WHERE ID=（SELECT MAX (ID) FROM Table_Name）
² 显示数据库中的最后十条记录的所有字段（ID 是自增的 DESC 做降序 ASC 做升序）
SELECT top 10 * FROM Table_Name ORDER BY ID DESC
² 对来自表 authors 的前十个作者的 state 列进行更新
UPDATE s SET s.saleprice = s.saleprice+2
FROM (SELECT TOP 10 * FROM sales ORDER BY saleid) AS t1, sales s
WHERE s.saleid=t1.saleid
-- 或者
UPDATE s SET s.saleprice = s.saleprice+2 FROM sales s
WHERE s.saleid in (SELECT TOP 10 saleid FROM sales ORDER BY saleid)
² 找出公司里收入最高的前三名员工
select top 3 * from t1 order by a desc --或者
Select top 3 *, ROW_NUMBER() OVER(order by a DESC) as No from t1
(根据分析，执行计划中的顺序：sort (order by )+ top 3, 然后是where等条件)
² 找出公司里收入最高(低)的三->五名员工
select top 3 a from t1 where a in ( select top 5 a from t1 order by a asc) order by a desc
--弊端：参与排序的一定要是index，或者unique，且选出来的只能是单一的一个列
-- 所以用下面的方法
SELECT top (10-3+1) * FROM (SELECT TOP 10 * FROM customers Order by zip asc)t order by zip desc
² 取出表A中第31条到第40条记录（SQLServer，以自动增长的ID为主键，注意：ID可能不是连续的。）
-- top 10 可以省略
SELECT top 10 * FROM A WHERE ID not in (SELECT top 30 id FROM A)
² 显示出员工的平均工资大于3000元的部门名称（用SQL语句）
注意Full outer join，left join, right join，inner join区别和联系
SELECT Dept_Name
FROM t_Dept
WHERE ID in (SELECT Dept_IDFROM t_Salary
GROUP BY Dept_ID --对部门分组（即：相同部门的，进行同一操作）
Having avg(Salary)>3000)
² 找出那些工资高于他们所在部门的平均工资的员工
select last_name, dept_id, salary from s_emp a
where salary>(select avg(salary) from s_emp where dept_id=a.dept_id)
² 找出那些工资高于他们所在部门的 manager 的工资的员工。
select id, last_name, salary, manager_id from s_emp a
where salary>(select salary from s_emp where id=a.manager_id)
² 有两个表分别如下：
表A(varchar(32) NAME,int GRADE)
数据：ZHANGSHAN 80, LISI 60, WANGWU 84
表B(varchar(32) NAME,int AGE)
数据：ZHANGSHAN 26, LISI 24, WANGWU 26, WUTIAN 26
1）写SQL语句得到如下查询结果：
NAME GRADE AGE
ZHANGSHAN 80 26
LISI 60 24
WANGWU 84 26
WUTIAN NULL 26
答：select * from A right join B on A.NAME = B.NAME
2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。
答：avg(grade) group by name, age
4. 横表竖起来
请写出 SQl 语句实现题目要求的结果:写一个 SQL完成左边的表变成右边的表。
表的结构
要求结果
ProductID SALE_YEAR SALES
001 2001 10
002 2001 15
003 2002 12
003 2003 10
productID 2001 2002 2003
001 10
002 15
003 12 10
² 交叉表的列数是确定的
select name,
sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test group by name
² 交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
5. SQLServer删除重复数据记录
http://www.cnblogs.com/luohoufu/archive/2008/06/05/1214286.html
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复。
² 写出 SQl 语句（或 SQL 语句组），查询所有 id_no 重复的记录。
select dept_ID from salary
group by dept_ID having count(dept_ID) > 1
² 对于第一种重复，比较容易解决，使用
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
truncate table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
² 这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下：
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
² 部分关键字段重复，且记录中有ID. (***这个比较实用***)
第一种方法可一次删除所有重复的..(只保留重复中ID最小的记录)。
delete from table where id not in ( select min(id) from table group by name)第二种方法每次只删除重复中ID最大的一条记录。
delete from table where id in ( select max(id) from table group by name having count(*)>1)
² 使用SQL程序删除
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
² 自己还得出的办法：
select * from user1 where id not in (select top 1 id from user1 where name = user1.name) -- 两个name相等比较重要，否则就不对了。但是group by更加好一些
删就这样写
delete from user1 where id not in (select top 1 id from user1 where name=user1.name)
或
delete from user where id not in ( select max(id) from user where name=user.name)
delete from user where id not in (select max(id) from user group by name having count(*) > 1)
其他方法
A:保留id最大(或者最小)的行,删除其它行
--方法1
delete [user] from [user] t
inner join(select name,max(id) as id from [user] group by name) a
on t.name = a.name and t.id <> a.id
--方法2
delete [user] from [user] t
where exists(select * from [user] where name = t.name and id > t.id)
B:删除所有重复的name行,一行也不留
delete [user] from [user] t
inner join
(select id from [user] a where exists(select * from [user] where name = a.name group by name having count(*) > 1)) as b
on t.id = b.id
6. 一些高难度的SQL
http://www.teecool.com/post/2007072807.html
² 如果表的结构和数据：
表1：usertable
USERID USERNAME
1 user1
2 null
3 user3
4 null
5 user5
6 user6
表2： usergrade;
USERID USERNAME GRADE
1 user1 90
2 null 80
7 user7 80
8 user8 90
那么，执行语句 select count(*) from usergrade where username not in (select username from usertable);
select count(*) from usergrade g where not exists (select null from usertable t where t.userid=g.userid and t.username=g.username);
结果为：语句1( 0 ) 语句2 ( 3 ) A: 0 B:1 C:2 D:3 E:NULL --- 不懂
² 在以下的表的显示结果中，以下语句的执行结果是（知识点：in/exists+rownum）
SQL> select * from usertable;
USERID USERNAME
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
那么，执行语句Select count(*) from usertable t1 where username in
(select username from usergrade t2);
Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username );
以上语句的执行结果是：( c) (c ) A: 0 B: 1 C: 2 D: 3
count(*)和count(GRADE)有区别：前者统计NULL行；后者忽略NULL行
² 关联更新
有表一的查询结果如下，该表为学生成绩表select id,grade from student_grade
ID GRADE
1 50
2 40
3 70
4 80
5 30
6 90
表二为补考成绩表 select id,grade from student_makeup
ID GRADE
1 60
2 80
5 60
执行语句：
update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id);
-- 这里，把1、2、5更新为对应的结果，但是3、4、6也会被更新，但是由于没有对应的值，所以都被更新为了NULL
请问之后查询： select GRADE from student_grade where id = 3;结果为： (c)
A: 0 B: 70 C: null D: 以上都不对
7. 英文
http://www.teecool.com/post/2007072808.html
² Question 1： calculating the Number of Days in a Month
declare @now datetime
--select @now = getdate()
select @now = '20090211'
-- 方法是：下月日-本月日的日期差。所以先构造本月日，在构造下月日，减
select datediff(dd, dateadd(dd, -1* datepart(dd, @now) + 1, @now),
dateadd(MM, 1, dateadd(dd, -1* datepart(dd, @now) + 1, @now)) )
-- 方法：下月日的前一天就是本月的月末，这一天的‘dd’就是本月天数。
select datepart(dd,
dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)
+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))));
² Question2：How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?
select bookid,bookname,
price=case when price is null then 'unknown'
when price between 10 and 20 then '10 to 20' else price end
from books
Question3：to find duplicate values! Find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result:
au_lname number_dups
Ringer 2
Answer 3
select au_lname,number_dups=count(1) from authors group by au_lname
--count(1)和count(*)结果一样，都是根据au_lname分组进行的组内全部统计
² Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quality. Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: 85 0 85 0 0
Fricative 60 35 0 0 25
Total 250 60 165 0 25
Answer 4：用动态SQL实现，前面有。不过看起来很复杂
use pubs
declare @s varchar(8000)
set @s='select isnull(stores.stor_name,''Total'') '
select @s=@s + ',[Qtr' + cast(datepart(q,ord_date) as char(1))
+ ']=sum(case when datepart(q,ord_date)='''
+ cast(datepart(q,ord_date) as char(1)) + ''' then qty else 0 end)'
from Sales
group by datepart(q,ord_date)
set @s=@s + ' ,sum(qty) "Total" from Sales inner join stores on Sales.stor_id = stores.stor_id '
set @s=@s + ' where datepart(yyyy,ord_date) = ''1993'' '
set @s=@s + ' group by stores.stor_name WITH ROLLUP '
set @s=@s + ' order by stores.stor_name '
print @s
exec(@s)
-- print @s的结果是这样的：
select isnull(stores.stor_name,'Total') , -- isnull把rollup造成的NULL值改成了Total，技巧
[Qtr1]=sum(case when datepart(q,ord_date)='1' then qty else 0 end),
[Qtr2]=sum(case when datepart(q,ord_date)='2' then qty else 0 end),
[Qtr3]=sum(case when datepart(q,ord_date)='3' then qty else 0 end),
[Qtr4]=sum(case when datepart(q,ord_date)='4' then qty else 0 end),
sum(qty) "Total"
from Sales inner join stores on Sales.stor_id = stores.stor_id
where datepart(yyyy,ord_date) = '1993'
group by stores.stor_name WITH ROLLUP -- rollup是一个很重要的东西
order by stores.stor_name -- order by，貌似不需要，或者用desc更好
² Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
Tips: sp_recompile can recomplie a store procedure each time
Answer 5：在执行存储过程时,使用 with recompile 选项强制编译新的计划；使用sp_recompile系统存储过程强制在下次运行时进行重新编译
Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
line-no title_id
1 BU1032
2 BU1111
3 BU2075
4 BU7832
5 MC2222
6 MC3021
7 MC3026
Answer 6：
-- row_number()的这种用法据我了解不行，他必须和over连用
select row_number() over(order by title_id) as line_no ,title_id from titles
² Question 7: the difference of two SQL statements at performance of execution?
Statement 1:
if NOT EXISTS ( select * from publishers where state = ‘NY’)
SELECT ‘Sales force needs to penetrate New York market’
else
SELECT ‘We have publishers in New York’
Statement 2:
if EXISTS ( select * from publishers where state = ‘NY’)
SELECT ‘We have publishers in New York’
else
SELECT ‘Sales force needs to penetrate New York market’
Answer 7：
不同点:初步感觉应该是第二个性能比较好，因为不需要遍历整个数据表，只要有一个存在的就可以结束。但是从执行计划的时间和IO看，结果是一样的。
² Question9: How can I get a list of the stores that have bought both ‘bussiness’ and ‘mod_cook’ type books?
In database pubs, use three table stores,sales and titles to implement this requestment. Now I want to get the result as below:
stor_id stor_name
...
7896 Fricative Bookshop
...
...
...
Answer 9：
use pubs
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook');
-- 或者使用个连续的join也可以
select distinct a.stor_id, a.stor_name from stores a
join sales b on a.stor_id=b.stor_id
join titles c on b.title_id=c.title_id and c.type='business'
and exists (select * from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type title price
business The Busy Executive’s Database Guide 19.9900
...
...
Answer 11：
select distinct t1.price, t1.type from titles t1, titles t2
where t1.price <
( select avg(price) from titles t2 where t1.type = t2.type group by type )
-- 或者
select distinct t1.price, t1.type from titles t1
join titles
on t1.price <
( select avg(price) from titles t2 where t1.type = t2.type group by type )
8. 其他
http://www.teecool.com/post/2007071809.html
² 有订单表SO_Table，单号字段RefNo VARCHAR(10)，需要实现自动编号，格式为YYYYMMXXXX，其中XXXX为序号，如：2004050001，2004050002……2004059999等，采用Transact-SQL实现新订单编号的思路。
思路：
1，IDENTITY(smallint, 100, 1)只有在select…into这样的数据插入里面可用；
2，6位长度的年月可以用：convert(char(6), getdate(), 112)；
或者cast(datepart(YY, @date) as varchar(4)) +
right(cast(datepart(MM, @date)+100 as varchar(3)), 2)
3， 如果必须在SP里面工作的话，可以获得上次的最大序号，加1
² 有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何确何数据的完整性。
使用trigger可以做到；另外，添加过程要在一个transaction中进行；
² 如何求表中相邻(按聚集索引相邻)的两条记录的某字段的值之差。
select s2.saleid, p=(s1.saleprice-s2.saleprice)
from
(select *, id=row_number() over(order by saleid) from sales) s1,
(select *, id=row_number() over(order by saleid) from sales) s2
where s2.id-s1.id=1
-- 下面的办法不行，因为如果相邻的ID不是连续的就不行了。另外，可以使用cursor
select a.source - b.source from test a, test b
where (a.id - b.id) = 1
order by a.id
² 如何删除表中的重复数据。上面有答案。
² 人员情况表（employee）中字段包括，员工号（ID），姓名（name），年龄（age），文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。结果如下：
学历 年龄 人数 百分比
本科以上 20 34 14
大专 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
。。。。。。
Transact-SQL查询语句如何写？
-- Count(*) * 100 由group by限定；SELECT Count(*) FROM是总数。
SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,
Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比
FROM employee GROUP BY wh,age
² 表一(AAA)
商品名称a 商品总量b
A 100
B 120
表二(BBB)
商品名称a 出库数量b
A 10
A 20
B 10
B 20
B 30
用一条Transact-SQL语句算出商品A,B目前还剩多少？
select sum(b)
from (select * from a
union all select a, b*(-1) from b) x
group by a
² 找到连续编号中断的那一个的最小值
select min(t.id) as id from (select id=id+1 from tt) t
where id not in (select id from tt)
9. 实际应用
A) 为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
² 查询选修课程名称为’税收基础’的学员学号和姓名
Select SN,SD FROM S -- 实际上，使用3个join才是合理的
Where [S#] IN(
Select [S#] FROM C,SC
Where C.[C#]=SC.[C#]
AND CN=N'税收基础')
² 查询选修课程编号为’C2’的学员姓名和所属单位
Select S.SN,S.SD FROM S,SC
Where S.[S#]=SC.[S#]
AND SC.[C#]='C2'
² 查询不选修课程编号为’C5’的学员姓名和所属单位
Select SN,SD FROM S
Where [S#] NOT IN( -- not in是正确的。不能使用=，会遗漏
Select [S#] FROM SC
Where [C#]='C5')
² 4. 查询选修全部课程的学员姓名和所属单位
-- 1）RIGHT JOIN可产生NULL行；2）COUNT(*),COUNT(S#)的结果不同
-- 解释：如果COUNT(*)<>COUNT(S#)，
-- 说明C中有某个C#没有在sc中出现，也就是说：这门课程没有被S#同学选中。
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
RIGHT JOIN
C ON SC.[C#]=C.[C#] GROUP BY [S#]
HAVING COUNT(*)=COUNT([S#]))
² 5. 查询选修了课程的学员人数
Select 学员人数=COUNT(DISTINCT [S#]) FROM SC
² 6. 查询选修课程超过5门的学员学号和所属单位
-- 关注having子句中的count()等聚合函数的使用
Select SN,SD FROM S
Where [S#] IN(
Select [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)
B)该题目中，很多都有歧义，所以只要掌握的基本的方法，不需要深究细节。
/*
S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
*/
² 找出没有选修过“李”老师讲授课程的所有学生姓名
select distinct sname from s
where s.sno not in --因为有这种情况：S.SNO不在SC中存在。
(select sno from sc,c where sc.CNO=c.cno and cteacher = '李')
² 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(
Select SNO FROM SC Where SCGRADE<60
GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2
)A -- 实际上，使用 IN 进行条件连接也行
-- where控制条件，having控制分组条件并聚合
Where S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
--下面是错误结果：它只是把没有及格的科目进行了avg，并不是所有的科目的avg
select avg(sc.scgrade), s.sname
from s
join sc on sc.sno = s.sno and sc.scgrade < 60
group by s.sname having count(sc.scgrade)>=2
² 列出既学过“c1”号课程，又学过“c2”号课程的所有学生姓名
select s.sname
from s, (select * from sc
where sc.sno in (select sc.sno from sc where sc.cno = 'c1')
and sc.cno = 'c2') SS -- 把sc.sno in换为exists也可以。两种方案
where s.sno = ss.sno
² 列出“c1”号课成绩比“s2”号同学该门课成绩高的所有学生的学号
select sc.sno, sc.scgrade from sc
where sc.cno='c1' and
sc.scgrade>(select sc.scgrade from sc where sc.cno='c1' and sc.sno='s2')
² 列出“c1”号课成绩比“c2”号课成绩高的所有学生的学号及其“c1”号课和“c2”号课的成绩
select sc1.sno, sc1.cno, sc1.scgrade
from sc sc1,
(select sc1.sno, sc1.cno, sc1.scgrade
from sc sc1, sc sc2
where sc1.cno='c1' and sc2.cno='c2' and sc1.scgrade>sc2.scgrade
and sc1.sno=sc2.sno) sc2
where sc2.sno=sc1.sno and (sc1.cno='c1' or sc1.cno='c2')
1. 老外的一套题
我需要一个模式和一些数据来运行所要考核的查询，列表A创建了所需的这些数据：
IF OBJECT_ID('Sales')>0 DROP TABLE Sales
GO
IF OBJECT_ID('Customers')>0 DROP TABLE Customers
GO
IF OBJECT_ID('Products')>0 DROP TABLE Products
GO
CREATE TABLE Customers(CustomerID INT IDENTITY PRIMARY KEY ,
FirstName VARCHAR(50),
LastName VARCHAR(50),
City VARCHAR(50),
State CHAR(2),
Zip VARCHAR(10) )
GO
CREATE TABLE Products(
ProductID TINYINT IDENTITY PRIMARY KEY ,
ProductName VARCHAR(20),
RecommendedPrice MONEY,
Category VARCHAR(10))
GO
CREATE TABLE Sales(SaleID INT IDENTITY PRIMARY KEY ,
ProductID TINYINT NOT NULL REFERENCES Products(ProductID),
CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
SalePrice MONEY NOT NULL,SaleDate SMALLDATETIME NOT NULL)
GO
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('DVD',105,'LivingRoom')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Microwave',98,'Kitchen')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Monitor',200,'Office')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Speakers',85,'Office')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('Refrigerator',900,'Kitchen')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('VCR',165,'LivingRoom')
INSERT INTO Products(ProductName,RecommendedPrice,Category)VALUES('CoffeePot',35,'Kitchen')
GO
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('John','Miller','Asbury','NY','23433')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Fred','Hammill','Basham','AK','85675')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Stan','Mellish','Callahan','WY','38556')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Adrian','Caparzo','Denver','CO','12377')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Mike','Horvath','Easton','IN','47130')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Irwin','Wade','Frankfurt','KY','45902')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('George','Marshall','Gallipoli','ND','34908')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Frank','Costello','Honolulu','HI','23905')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Billy','Costigan','Immice','SC','75389')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Shelly','Sipes','Lights','AZ','35263')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Chirsty','Melton','Spade','CA','97505')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Amanda','Owens','Flask','CN','50386')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Brittany','Smits','Bourbon','KY','24207')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Kristy','Bryant','Tarp','FL','58960')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Kelly','Street','TableTop','ID','57732')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Tricia','Hill','Camera','ME','46738')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Holly','Raines','Compact','MS','35735')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Natalie','Woods','Woods','IN','87219')
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Wendy','Hilton','Action','KY','47093')
GO
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,1,130,'2/6/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,2,97,'1/7/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,3,200,'8/8/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,4,80,'4/9/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,899,'10/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,6,150,'10/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,7,209,'12/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,8,90,'5/13/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,9,130,'6/14/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,14,85,'6/19/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,15,240,'9/20/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,16,99,'7/21/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,17,87,'3/22/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,18,99,'1/23/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,19,150,'3/24/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,900,'3/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,6,86,'8/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,7,88,'8/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,8,198,'12/13/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,9,150,'5/14/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,14,99,'7/19/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(6,15,104,'9/20/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,16,270,'2/21/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(4,17,90,'7/22/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,1,130,'3/6/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,2,102,'4/7/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(1,3,114,'11/8/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,4,1000,'5/9/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(5,5,1100,'10/10/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,6,285,'6/11/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(2,7,87,'10/12/2005')
INSERT INTO Sales(ProductID,CustomerID,SalePrice,SaleDate)
VALUES(3,8,300,'7/13/2005')
GO
² 1：返回在2005年10月售出的所有产品的名称、价格和客户姓名，答案：
select c.FirstName,c.LastName, p.productname, s.SalePrice, s.saledate from products p, customers c, sales s
where s.productid = p.productid and c.customerid = s.customerid
and datepart(YY, s.saledate) = 2005 and datepart(MM, s.saledate) = 10
--或者下面的也行。根据执行计划，二者是完全一样的。
SELECT c.FirstName,c.LastName,p.ProductName,s.SalePrice
FROM Sales s
INNER JOIN Customers c ON s.CustomerID=c.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
WHERE s.SaleDate>='10/1/2005'AND s.SaleDate<'11/1/2005'
² 2：返回没有购买产品并且位于客户表格上的人的姓名及其客户ID，答案:
select c.FirstName,c.LastName, c.customerid
from customers c
where c.customerid not in ( select distinct customerid from sales)
--或者下面的。下面的是提供的答案；但是上面的效率更高，二者比42：58
SELECT c.CustomerID,c.FirstName,c.LastName
FROM Sales s
RIGHT OUTER JOIN Customers c ON s.CustomerID=c.CustomerID
WHERE s.CustomerID IS NULL
3：返回客户姓名、销售价格、建议售价、建议售价和实际价格的差额，该差额必需是正数，答案见列表D：
SELECT c.FirstName,c.LastName,s.SalePrice,p.RecommendedPrice,
ABS(s.SalePrice-p.RecommendedPrice) AS diff
FROM Sales s
INNER JOIN Customers c ON s.CustomerID=c.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
² 4：根据产品类别计算平均价格，答案见列表E：
SELECT avg(s.SalePrice) as avg, p.category
FROM Sales s inner join products p
on s.productid = p.productid group by p.category
² 5：将以下的客户和销售信息加入到数据库中：
FirstName:Chris， LastName:Kringle， City:Henryville， State:IN， Zip:47126
ProductID:3， SalePrice:205， SaleDate:12/31/2005
答案见列表F（关注SCOPE_IDENTITY()，这里可以和@@identity互换）：
INSERT INTO Customers(FirstName,LastName,City,State,Zip)
VALUES('Chris','Kringle','Henryville','IN','47126')
INSERT INTO Sales(CustomerID,ProductID,SalePrice,SaleDate)
VALUES(SCOPE_IDENTITY(),3,205,'12/31/2005')
² 6：从数据库中删除来自缅因洲(‘ME’)的客户，答案见列表G：(不过，delete s from...这样的用法很怪，貌似这样就可以把s这个名字引入进来了)
delete s from Sales s where s.CustomerID in
(select CustomerID from Customers where State = 'ME')
-- 或者下面的：（因为不支持delete from A, B这样的语法，所以用join）
DELETE s FROM Sales s JOIN Customers c
ON s.CustomerID=c.CustomerID WHERE c.State='ME'
-- 然后
DELETE c FROM Customers c WHERE c.State='ME'
² 7：返回客户购买了两个或多个产品的平均售价和产品类别，答案：
SELECT AVG(s.SalePrice) avg, p.Category
FROM Sales s, Products p
where s.ProductID=p.ProductID and s.CustomerID in
( SELECT s.CustomerID FROM Sales s
GROUP BY s.CustomerID HAVING COUNT(CustomerID)>=2)
GROUP BY p.Category
-- 或者
SELECT AVG(s.SalePrice) avg, p.Category
FROM Sales s INNER JOIN
( SELECT s.CustomerID FROM Sales s
GROUP BY s.CustomerID HAVING COUNT(CustomerID)>=2)x
ON s.CustomerID=x.CustomerID
INNER JOIN Products p ON s.ProductID=p.ProductID
GROUP BY p.Category
² 8：将销售在2005年6月10日到6月20日之间的产品的销售价格升级为建议售价：（这个还是不太明白，尤其是s.ProductID=s.ProductID，怪）
UPDATE s SET SalePrice=p.RecommendedPrice
FROM Sales s INNER JOIN Products p ON s.ProductID=s.ProductID
WHERE SaleDate>='6/10/2005' AND SaleDate<'6/21/2005'
² 9：根据产品种类计算建议售价超过实际售价10元及以上的销售数量，答案见列表J：
SELECT count(s.SalePrice) count, p.Category
FROM Sales s
INNER JOIN Products p ON s.ProductID=p.ProductID
where p.RecommendedPrice - s.SalePrice >= 10
GROUP BY p.Category
-- 题目有歧义，所以，也可以这样做：
SELECT p.Category,COUNT(*)AS NumberOfSales
FROM Sales s
INNER JOIN Products p ON s.ProductID=p.ProductID
GROUP BY p.Category
HAVING AVG(p.RecommendedPrice)>=AVG(s.SalePrice)+10
² 10：不使用叠代构建，返回所由销售产品的销售日期，以及该日期之前的销售额统计，以及该日期之前的销售额统计，并按照该日期升序排列，答案：
SELECT s.SaleDate, s.SalePrice,
( SELECT SUM(SalePrice) FROM Sales s2 WHERE s2.SaleDate<=s.SaleDate )AS RunningTotal
FROM Sales s ORDER BY s.SaleDate ASC
迄今为止，只有2个人可以正确地回答出所有的问题。
平均大约为50-60%，如果表现高于这个平均，那么优秀的TSQL程序员，如果获得了90%以上的得分，那么他或她就是一位非常优异的程序员。
Oracle总结
1.如何知道哪些表没有建立主键？
Select table_name from user_tables t
Where not exists (select table_name from user_constraints c where constraint_type=’P’ and t.table_name=c.table_name)
2.相关数据字典的意义
User_tables 表
User_tab_columns 表的列
User_constraints 约束
User_cons_columns 约束与列的关系
User_indexes 索引
User_ind_columns 索引与列的关系
3.关于角色和权限
对于数据库管理员，应该授予DBA角色；对于数据库开发用户，只需要授予CONNECT和RESOURCE角色
Oracle数据库的权限分为系统权限和对象权限。前者在系统级控制对数据库的存取和操作，如用户是否能通过建立会话而连接到数据库，是否能启动、停止数据库，是否能修改数据库参数等；对象权限在方案级控制对数据库的存取和操作，如用户可以存取哪个方案中的对象，是否能对该对象进行查询、插入、更新等。
4.关于Oracle的回收站以及闪回技术
用drop命令删除的表其实并没有物理删除，而是放在了回收站里，还占用着数据库空间。用select * from User_recyclebin就可以看到回收站中的表。
Purge table temp1—将回收站中的某个表彻底删除
Purge recyclebin:清空oracle的回收站；
Flashback table tablename to before drop（rename to temp2） —还原某张表（可以进行更名）；
Flashback table temp to timestamp(systimestamp-interval ‘1’ minute) ---恢复到1分钟前的数据（也可以是second等），但是要注意，首先要启动表的行移动功能：alter table temp enable row movement;
5.选择数据库实例
Windows下：set oracle_sid=…
Unix下：export oracle_sid=…
6.表和表之间的关联更新
Update file_dept a set a.id=(select b.id from temp1 b where a.dept_id=b.dept_id)
7.创建用户：
第一步：建立用户
Create user web identified by web
Default tablespace web
Temporary tablespace temp
Profile “DEFAULT”—大写
Account unlock; —未锁定
第二步：授予权限
与scott相同：grant connect to web;
Grant resource to web;
Grant create view to web;
8.删除某个用户的连接
Select sid,serial#,status from v$session where username=’…’
Alter system kill session ‘sid,serial#’;
9.启动关闭监听
>lsnrctl start
>lsnrctl stop
>lsnrctl status
10.启动和关闭数据库
Ø 启动数据库
>startup nomount ---启动例程，但不装载数据库，并未打开控制文件和数据文件
>startup mount ---启动例程并装载数据库，但不打开数据库，打开控制文件，但并未打开数据文件
>startup open ---启动例程，装载数据库，打开数据库，既打开了控制文件也打开了数据文件
>startup force ---强制启动
>startup restrict
>startup pfile=… ---注意是pfile，不是spfile。可以先用create pfile=… from spfile=…语句将服务器初始化文件导出成文本初始化参数文件后，再使用导出后的文本初始化参数文件
Ø 关闭数据库
过程：关闭数据库-->卸载数据库-->终止例程
语法：shut down[ normal | transactional | immediate | abort ]
尽量避免用abort选项来关闭数据库，如果想尽快关闭，可以使用IMMEDIATE选项，这样所有未提交的事物均被回退，使数据信息以及完整性得以保证。
Ø 说明
在NOMOUNT启动模式下，只能访问那些与SGA区相关的数据字典视图，如V$PARAMETER,V$SGA,V$OPTION,V$PROCESS,V$SESSION,V$VERSION,V$INSTANCE等。这些视图中的信息都是从SGA区中获取的，与数据库无关；
在MOUNT启动模式下，除了可以访问那些与SGA区相关的数据字典视图之外，还可以访问到那些与控制文件相关的数据字典视图，如V$THREAD,V$CONTROLFILE,V$DATABASE,V$DATAFILE,V$LOGFILE等，这些视图中的信息都是从控制文件中获取的。
11.更改用户密码
当忘记了system与sys的密码时，可用如下方法更改：
C:>sqlplus/nolog
SQL>connect 空格/空格 @myoracle as sysdba
SQL>alter user system identified by manager
要注意的是，以上方法只能在服务器端执行，客户端不能执行。
12.设置主机首选身份证明
1）.选择“开始”-->“程序”-->“管理工具”-->“本地安全策略”，打开“本地安全策略”窗口
2）.选择“本地策略”中的“用户权限分配”项
3）.在右边的“策略”列中双击“作为批处理作业登录”项，打开其属性对话框
4）.将Administrator加入用户中，这样，该用户就有了“作为批处理作业登录”的权限
13.如何将数据库从noarchivelog改成archivelog方式？
首先打开INIT.ora文件，确保存档日志目标指向一有效目录，然后启动sqlplus
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog
SQL>alter database open
SQL>archive log list ---列出现在数据库是否归档
在INIT.ora中设置参数archive_log_start=true，它设置存档日志为自动启动。
14.创建表空间的SQL
Create smallfile tablespace “FILEEXCHANGE”
Datafile’D:\oracle\myoracle\fileexchange.dbf’ size 10M
Reuse autoextend on next 5120k MAXSIZE 32767M—最大
Nologging extend management local—区管理方式：本地管理
Segment space management Auto—使用Auto段管理方式
15.Sqlplus技巧
清屏：SQL>ho cls
Sqlplus中的所有命令可用>help index看到，关于每个指令具体的用法可用>？…或help …来查看。
16.修改系统日期格式
Alter session set NLS_DATE_FORMAT=’dd-mon-yyyy hh:mi:ss’;
Select current_date from dual;
Select sysdate from dual;
17.如何将角色赋予用户
1）.创建一个角色：create role myrole;
2）.将对scott.dept表的select权限赋予这个角色：grant select on scott.dept to myrole;
3）.将这个角色赋予用户test：grant myrole to test.
18.SQLLoad的使用
1）.数据文件Loader.txt,文件内容如下
abcd,qq
abc,ee
…
2）.控制文件cont.ctl(以ctl为后缀)，内容如下：
Load data
Infile ‘c:\loader.txt’ ---数据文件
Append
Into table mm(
M1 char terminated by “,”,
M2 char terminated by “,”)---用逗号分隔字段
3）.执行命令：>sqlldr scott/tiger control=c:\cont.ctl data=c:\loader.txt
如果成功就会提示：commit point reached—logical record count 3—表示插入了3条记录
如果每列长度固定，那么可写成mm(m1 position(1:3) char,m2 position(5:7) char)—表示提取1-3位和5-7位
4）.SQLLoader的4种装入表的方式：
APPEND:原先的表有数据，就加在后面；
INSERT：装载空表，如果原先的表有数据，SQLLoader就会停止
REPLACE：原先的表有数据，原先的数据会全部删除
TRUNCATE：指定的内容和REPLACE相同，会用truncate语句删除现存数据
19.如何查看对象所占用的空间
Ø 查看表和索引的大小：select * from user_segments where segmentname=’’
Ø 查看此用户所有对象所占空间大小：select sum(bytes) from user_segments;
Ø 查看每个用户表空间的大小（已使用的表空间，而不是初始化分配的空间）：select tablespace_name sum(bytes)/1024/1024 from dba_segments group by tablespace_name
Ø 查看当前用户每个表占用空间的大小：select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name
20.查看一个表所用的分区
Select table_name,partition_name
from user_tab_partitions
where table_name=’daryxxb’
21.将一个schema中的对象导入到属于另一个表空间的schema中
>imp system/manager@myoracle from user=web to user=net tablespaces=net file=f:\web.dmp
22.数据库链（Database link）
1）.创建（首先要有create public database link权限）
Create public database link remotedb using ‘remote’;表示用目前用户去链接远程服务器，如果要指定用户名，则在using前加上connect 用户名 identified by 口令，最后的’remote’是连接字符串
2）.应用数据库链
Select * from tablename @remotedb;
复制表结构：create table test as select * from test @remotedb where 1=2;
也可以远端进行Update、insert操作
3）.删除（要有drop public database link权限）
Drop public database link remotedb;
4）.查询数据库链信息
Dba_db_links,all_db_links,user_db_links
23.物化视图
1）.主键物化视图
create materialized view mv_emp_pk
Refresh fast start with sysdate next sysdate+1/48 with primary key
As select * from emp@remotedb;
注意：当用fast选项创建物化视图，必须创建基于主表的视图日志，如下：
Create materialized view log on emp;
2）.Rowid 物化视图
Create materialized view mv_emp_rowid
Refresh with rowid
As select * from emp@remote_db
3）.Refresh选项说明
Ø Oracle是用刷新方法在物化视图中刷新数据
Ø 是基于主键还是基于rowid的物化视图
Ø 物化视图的刷新时间和间隔刷新时间
4）.Refresh的方法
Ø Fast—增量刷新，用物化视图日志来发送主表已经修改的数据行到物化视图中
Ø Complete—完全刷新，重新生成整个视图
Ø Force—如果增量刷新可用将完成增量刷新，否则完成完全刷新
24.关于归档日志的空间问题
Ø 查看归档日志的容量使用：select * from v$recovery_file_dest;
Ø 更改归档日志容量空间：
alter system set db_recovery_file_dest_size=8G scope=BOTH;
Alter database open;
Ø 手动删除归档日志文件,需要以下几步：
手动删除archivelog文件夹中的早期的归档日志文件
登录rman ：>rman target /
对归档日志进行验证：rman>crosscheck archivelog all;
删除失效的归档日志：rman>delete expired archivelog all;
25.备份与还原
数据文件的联机备份（热备份）
第一步：将数据库置为归档日志状态：
1).首先查看是否是归档日志模式 >archive log list;
2).如果不是，那么将其设为归档模式 >alter system set log_archive_start=true scope=spfile
3).关闭数据库 >shutdown immediate
4).启动数据库为Mount方式 >startup mount
5).将数据库切换到归档日志模式：>alter database archivelog
6).打开数据库 >alter database open;
7).这时再查看 >archive log list；便显示是归档日志模式了
第二步：备份与恢复
1).alter tablespace web begin backup
2).将表空间web的数据文件进行备份
3).Alter tablespace web end backup;
4).将当前的联机日志进行归档：>alter system archive log current
5).将日志文件切换：>alter system switch logfile; 有几个日志文件就进行几次切换
6).关闭数据库 >shutdown immediate
下面是模拟错误：
7).将web的数据文件删除
8).打开数据库：>startup open；报错—web.dbf文件不能找到
9).让此数据文件脱机，并drop掉>alter database datafile 6 offline drop;6代表6号文件，也就是web.dbf文件
10).将数据库打开>alter database open;
11).将备份的web.dbf文件拷贝到原来的位置
12).恢复数据文件：>recover datafile 6; auto;
13).让数据文件联机：>alter database datafile 6 online；
14).这样，便可以查询web表空间中的数据了
控制文件的备份
1).>alter database backup controlfile to trace; 备份控制文件，会存放在…\admin\myoracle\udump\目录下最近的一个文件，这个文件中的语句就是执行控制文件时的脚本
2).将文件中的语句拷贝出来，另存为一个文件，比如create_ctl.txt
3).关闭数据库
4).执行加载控制文件：sql>@c:\create_ctl.txt;这样就重新创建了控制文件，并且将数据库打开了
日志文件的备份
当日志文件丢失后，可采用如下方法：
1).基于取消的恢复数据库： >recover database until cancel;
2).重新生成日志文件：>alter database open resetlogs;
脱机备份
1).整理需要备份的文件，包括参数文件、控制文件、数据文件和重做日志文件
Ø 参数文件：…\db_1\database\init.ora 以及关于此sid的所有文件
Ø 控制文件：select status,name from v$controlfile;
Ø 数据文件：selelct status,name from dba_data_files;
Ø 重做日志文件：select group#,status,member from v$logfile
2).用sysdba身份用户登录后,>shutdown immediate；关闭数据库
3).将上述整理的文件一一进行备份
4).打开数据库 >startup open；
恢复：当文件丢失，就需要恢复数据库
1).关闭数据库：>shutdown immediate
2).将所有的备份文件复制到原来所在的位置，不得漏掉一个，以便恢复备份时刻数据库的镜像
3).恢复完成后，以open方式启动数据库 >startup open；
//摘录于 http://blog.csdn.net/lihui_79/archive/2009/04/09/4060010.aspx
Select table_name from user_tables t
Where not exists (select table_name from user_constraints c where constraint_type=’P’ and t.table_name=c.table_name)
2.相关数据字典的意义
User_tables 表
User_tab_columns 表的列
User_constraints 约束
User_cons_columns 约束与列的关系
User_indexes 索引
User_ind_columns 索引与列的关系
3.关于角色和权限
对于数据库管理员，应该授予DBA角色；对于数据库开发用户，只需要授予CONNECT和RESOURCE角色
Oracle数据库的权限分为系统权限和对象权限。前者在系统级控制对数据库的存取和操作，如用户是否能通过建立会话而连接到数据库，是否能启动、停止数据库，是否能修改数据库参数等；对象权限在方案级控制对数据库的存取和操作，如用户可以存取哪个方案中的对象，是否能对该对象进行查询、插入、更新等。
4.关于Oracle的回收站以及闪回技术
用drop命令删除的表其实并没有物理删除，而是放在了回收站里，还占用着数据库空间。用select * from User_recyclebin就可以看到回收站中的表。
Purge table temp1—将回收站中的某个表彻底删除
Purge recyclebin:清空oracle的回收站；
Flashback table tablename to before drop（rename to temp2） —还原某张表（可以进行更名）；
Flashback table temp to timestamp(systimestamp-interval ‘1’ minute) ---恢复到1分钟前的数据（也可以是second等），但是要注意，首先要启动表的行移动功能：alter table temp enable row movement;
5.选择数据库实例
Windows下：set oracle_sid=…
Unix下：export oracle_sid=…
6.表和表之间的关联更新
Update file_dept a set a.id=(select b.id from temp1 b where a.dept_id=b.dept_id)
7.创建用户：
第一步：建立用户
Create user web identified by web
Default tablespace web
Temporary tablespace temp
Profile “DEFAULT”—大写
Account unlock; —未锁定
第二步：授予权限
与scott相同：grant connect to web;
Grant resource to web;
Grant create view to web;
8.删除某个用户的连接
Select sid,serial#,status from v$session where username=’…’
Alter system kill session ‘sid,serial#’;
9.启动关闭监听
>lsnrctl start
>lsnrctl stop
>lsnrctl status
10.启动和关闭数据库
Ø 启动数据库
>startup nomount ---启动例程，但不装载数据库，并未打开控制文件和数据文件
>startup mount ---启动例程并装载数据库，但不打开数据库，打开控制文件，但并未打开数据文件
>startup open ---启动例程，装载数据库，打开数据库，既打开了控制文件也打开了数据文件
>startup force ---强制启动
>startup restrict
>startup pfile=… ---注意是pfile，不是spfile。可以先用create pfile=… from spfile=…语句将服务器初始化文件导出成文本初始化参数文件后，再使用导出后的文本初始化参数文件
Ø 关闭数据库
过程：关闭数据库-->卸载数据库-->终止例程
语法：shut down[ normal | transactional | immediate | abort ]
尽量避免用abort选项来关闭数据库，如果想尽快关闭，可以使用IMMEDIATE选项，这样所有未提交的事物均被回退，使数据信息以及完整性得以保证。
Ø 说明
在NOMOUNT启动模式下，只能访问那些与SGA区相关的数据字典视图，如V$PARAMETER,V$SGA,V$OPTION,V$PROCESS,V$SESSION,V$VERSION,V$INSTANCE等。这些视图中的信息都是从SGA区中获取的，与数据库无关；
在MOUNT启动模式下，除了可以访问那些与SGA区相关的数据字典视图之外，还可以访问到那些与控制文件相关的数据字典视图，如V$THREAD,V$CONTROLFILE,V$DATABASE,V$DATAFILE,V$LOGFILE等，这些视图中的信息都是从控制文件中获取的。
11.更改用户密码
当忘记了system与sys的密码时，可用如下方法更改：
C:>sqlplus/nolog
SQL>connect 空格/空格 @myoracle as sysdba
SQL>alter user system identified by manager
要注意的是，以上方法只能在服务器端执行，客户端不能执行。
12.设置主机首选身份证明
1）.选择“开始”-->“程序”-->“管理工具”-->“本地安全策略”，打开“本地安全策略”窗口
2）.选择“本地策略”中的“用户权限分配”项
3）.在右边的“策略”列中双击“作为批处理作业登录”项，打开其属性对话框
4）.将Administrator加入用户中，这样，该用户就有了“作为批处理作业登录”的权限
13.如何将数据库从noarchivelog改成archivelog方式？
首先打开INIT.ora文件，确保存档日志目标指向一有效目录，然后启动sqlplus
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog
SQL>alter database open
SQL>archive log list ---列出现在数据库是否归档
在INIT.ora中设置参数archive_log_start=true，它设置存档日志为自动启动。
14.创建表空间的SQL
Create smallfile tablespace “FILEEXCHANGE”
Datafile’D:\oracle\myoracle\fileexchange.dbf’ size 10M
Reuse autoextend on next 5120k MAXSIZE 32767M—最大
Nologging extend management local—区管理方式：本地管理
Segment space management Auto—使用Auto段管理方式
15.Sqlplus技巧
清屏：SQL>ho cls
Sqlplus中的所有命令可用>help index看到，关于每个指令具体的用法可用>？…或help …来查看。
16.修改系统日期格式
Alter session set NLS_DATE_FORMAT=’dd-mon-yyyy hh:mi:ss’;
Select current_date from dual;
Select sysdate from dual;
17.如何将角色赋予用户
1）.创建一个角色：create role myrole;
2）.将对scott.dept表的select权限赋予这个角色：grant select on scott.dept to myrole;
3）.将这个角色赋予用户test：grant myrole to test.
18.SQLLoad的使用
1）.数据文件Loader.txt,文件内容如下
abcd,qq
abc,ee
…
2）.控制文件cont.ctl(以ctl为后缀)，内容如下：
Load data
Infile ‘c:\loader.txt’ ---数据文件
Append
Into table mm(
M1 char terminated by “,”,
M2 char terminated by “,”)---用逗号分隔字段
3）.执行命令：>sqlldr scott/tiger control=c:\cont.ctl data=c:\loader.txt
如果成功就会提示：commit point reached—logical record count 3—表示插入了3条记录
如果每列长度固定，那么可写成mm(m1 position(1:3) char,m2 position(5:7) char)—表示提取1-3位和5-7位
4）.SQLLoader的4种装入表的方式：
APPEND:原先的表有数据，就加在后面；
INSERT：装载空表，如果原先的表有数据，SQLLoader就会停止
REPLACE：原先的表有数据，原先的数据会全部删除
TRUNCATE：指定的内容和REPLACE相同，会用truncate语句删除现存数据
19.如何查看对象所占用的空间
Ø 查看表和索引的大小：select * from user_segments where segmentname=’’
Ø 查看此用户所有对象所占空间大小：select sum(bytes) from user_segments;
Ø 查看每个用户表空间的大小（已使用的表空间，而不是初始化分配的空间）：select tablespace_name sum(bytes)/1024/1024 from dba_segments group by tablespace_name
Ø 查看当前用户每个表占用空间的大小：select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name
20.查看一个表所用的分区
Select table_name,partition_name
from user_tab_partitions
where table_name=’daryxxb’
21.将一个schema中的对象导入到属于另一个表空间的schema中
>imp system/manager@myoracle from user=web to user=net tablespaces=net file=f:\web.dmp
22.数据库链（Database link）
1）.创建（首先要有create public database link权限）
Create public database link remotedb using ‘remote’;表示用目前用户去链接远程服务器，如果要指定用户名，则在using前加上connect 用户名 identified by 口令，最后的’remote’是连接字符串
2）.应用数据库链
Select * from tablename @remotedb;
复制表结构：create table test as select * from test @remotedb where 1=2;
也可以远端进行Update、insert操作
3）.删除（要有drop public database link权限）
Drop public database link remotedb;
4）.查询数据库链信息
Dba_db_links,all_db_links,user_db_links
23.物化视图
1）.主键物化视图
create materialized view mv_emp_pk
Refresh fast start with sysdate next sysdate+1/48 with primary key
As select * from emp@remotedb;
注意：当用fast选项创建物化视图，必须创建基于主表的视图日志，如下：
Create materialized view log on emp;
2）.Rowid 物化视图
Create materialized view mv_emp_rowid
Refresh with rowid
As select * from emp@remote_db
3）.Refresh选项说明
Ø Oracle是用刷新方法在物化视图中刷新数据
Ø 是基于主键还是基于rowid的物化视图
Ø 物化视图的刷新时间和间隔刷新时间
4）.Refresh的方法
Ø Fast—增量刷新，用物化视图日志来发送主表已经修改的数据行到物化视图中
Ø Complete—完全刷新，重新生成整个视图
Ø Force—如果增量刷新可用将完成增量刷新，否则完成完全刷新
24.关于归档日志的空间问题
Ø 查看归档日志的容量使用：select * from v$recovery_file_dest;
Ø 更改归档日志容量空间：
alter system set db_recovery_file_dest_size=8G scope=BOTH;
Alter database open;
Ø 手动删除归档日志文件,需要以下几步：
手动删除archivelog文件夹中的早期的归档日志文件
登录rman ：>rman target /
对归档日志进行验证：rman>crosscheck archivelog all;
删除失效的归档日志：rman>delete expired archivelog all;
25.备份与还原
数据文件的联机备份（热备份）
第一步：将数据库置为归档日志状态：
1).首先查看是否是归档日志模式 >archive log list;
2).如果不是，那么将其设为归档模式 >alter system set log_archive_start=true scope=spfile
3).关闭数据库 >shutdown immediate
4).启动数据库为Mount方式 >startup mount
5).将数据库切换到归档日志模式：>alter database archivelog
6).打开数据库 >alter database open;
7).这时再查看 >archive log list；便显示是归档日志模式了
第二步：备份与恢复
1).alter tablespace web begin backup
2).将表空间web的数据文件进行备份
3).Alter tablespace web end backup;
4).将当前的联机日志进行归档：>alter system archive log current
5).将日志文件切换：>alter system switch logfile; 有几个日志文件就进行几次切换
6).关闭数据库 >shutdown immediate
下面是模拟错误：
7).将web的数据文件删除
8).打开数据库：>startup open；报错—web.dbf文件不能找到
9).让此数据文件脱机，并drop掉>alter database datafile 6 offline drop;6代表6号文件，也就是web.dbf文件
10).将数据库打开>alter database open;
11).将备份的web.dbf文件拷贝到原来的位置
12).恢复数据文件：>recover datafile 6; auto;
13).让数据文件联机：>alter database datafile 6 online；
14).这样，便可以查询web表空间中的数据了
控制文件的备份
1).>alter database backup controlfile to trace; 备份控制文件，会存放在…\admin\myoracle\udump\目录下最近的一个文件，这个文件中的语句就是执行控制文件时的脚本
2).将文件中的语句拷贝出来，另存为一个文件，比如create_ctl.txt
3).关闭数据库
4).执行加载控制文件：sql>@c:\create_ctl.txt;这样就重新创建了控制文件，并且将数据库打开了
日志文件的备份
当日志文件丢失后，可采用如下方法：
1).基于取消的恢复数据库： >recover database until cancel;
2).重新生成日志文件：>alter database open resetlogs;
脱机备份
1).整理需要备份的文件，包括参数文件、控制文件、数据文件和重做日志文件
Ø 参数文件：…\db_1\database\init
Ø 控制文件：select status,name from v$controlfile;
Ø 数据文件：selelct status,name from dba_data_files;
Ø 重做日志文件：select group#,status,member from v$logfile
2).用sysdba身份用户登录后,>shutdown immediate；关闭数据库
3).将上述整理的文件一一进行备份
4).打开数据库 >startup open；
恢复：当文件丢失，就需要恢复数据库
1).关闭数据库：>shutdown immediate
2).将所有的备份文件复制到原来所在的位置，不得漏掉一个，以便恢复备份时刻数据库的镜像
3).恢复完成后，以open方式启动数据库 >startup open；
//摘录于 http://blog.csdn.net/lihui_79/archive/2009/04/09/4060010.aspx
Oracle函数列表速查
PL/SQL单行函数和组函数详解
函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：
单行函数
组函数
本文将讨论如何利用单行函数以及使用规则。
SQL中的单行函数
SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。
NULL和单行函数
在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。
下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿
column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2
不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：
update empset salary=(salary+bonus)*1.1
这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。
所以正确的语句是：
update empset salary=(salary+nvl(bonus,0)*1.1
单行字符串函数
单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串
ASCII()
c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122
CHR(＜i＞)[NCHAR_CS]
i是一个数字，函数返回十进制表示的字符
select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B
CONCAT(,)
c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null。他和操作符||返回的结果相同
select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda
INITCAP()
c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。
select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.
select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2
INSTRB(,[,i[,j])
与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()
LENGTH()
c1为字符串，返回c1的长度，如果c1为null，那么将返回null值。
select LENGTH('Ipso Facto') ergo from dualergo10
LENGTHb()
与LENGTH()一样，返回字节。
lower()
返回c的小写字符，经常出现在where子串中
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格，参见RPAD。
select LPAD(answer,7,'') padded,answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybe
LTRIM(,)
把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。
select LTRIM('Mississippi','Mis') from dualLTRppi
RPAD(,＜i＞[,])
在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格,其他与LPAD相似
RTRIM(,)
把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。
REPLACE(,[,])
c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。
select REPLACE('uptown','up','down') from dualREPLACEdowntown
STBSTR(,＜i＞[,])
c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
select SUBSTR('Message',1,4) from dualSUBSMess
SUBSTRB(,＜i＞[,])
与SUBSTR大致相同，只是I,J是以字节计算。
SOUNDEX()
返回与c1发音相似的词
select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dualDawes Daws DawsonD200 D200 D250
TRANSLATE(,,)
将c1中与c2相同的字符以c3代替
select TRANSLATE('fumble','uf','ar') test from dualTEXTramble
TRIM([[]] from c3)
将c3串中的第一个，最后一个，或者都删除。
select TRIM(' space padded ') trim from dual TRIMspace padded
UPPER()
返回c1的大写，常出现where子串中
select name from dual where UPPER(name) LIKE 'KI%'NAMEKING
单行数字函数
单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle没有提供内建的弧度和角度的转换函数。
ABS()
返回n的绝对值
ACOS()
反余玄函数，返回-1到1之间的数。n表示弧度
select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0
ASIN()
反正玄函数，返回-1到1，n表示弧度
ATAN()
反正切函数，返回n的反正切值，n表示弧度。
CEIL()
返回大于或等于n的最小整数。
COS()
返回n的余玄值，n为弧度
COSH()
返回n的双曲余玄值，n 为数字。
select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847
EXP()
返回e的n次幂，e=2.71828183.
FLOOR()
返回小于等于N的最大整数。
LN()
返回N的自然对数，N必须大于0
LOG(,)
返回以n1为底n2的对数
MOD()
返回n1除以n2的余数，
POWER(,)
返回n1的n2次方
ROUND(,)
返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。
select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
SIGN()
如果n为负数，返回-1,如果n为正数，返回1，如果n=0返回0.
SIN（)
返回n的正玄值,n为弧度。
SINH()
返回n的双曲正玄值,n为弧度。
SQRT()
返回n的平方根,n为弧度
TAN（)
返回n的正切值,n为弧度
TANH()
返回n的双曲正切值,n为弧度
TRUNC(,)
返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
单行日期函数
单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。
ADD_MONTHS(,＜i＞)
返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。
LAST_DAY()
函数返回包含日期d的月份的最后一天
MONTHS_BETWEEN(,)
返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。
NEW_TIME(,,)
d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。
NEXT_DAY(,)
返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。
select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004
ROUND([,])
将日期d按照fmt指定的格式舍入，fmt为字符串。
SYADATE
函数没有参数，返回当前日期和时间。
TRUNC([,])
返回由fmt指定的单位的日期d.
单行转换函数
单行转换函数用于操作多数据类型，在数据类型之间进行转换。
CHARTORWID()
c 使一个字符串，函数将c转换为RWID数据类型。
SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
CONVERT(,[,])
c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。
HEXTORAW()
x为16进制的字符串，函数将16进制的x转换为RAW数据类型。
RAWTOHEX()
x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。
ROWIDTOCHAR()
函数将ROWID数据类型转换为CHAR数据类型。
TO_CHAR([[,)
x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
TO_DATE([,[,）
c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。
TO_MULTI_BYTE()
c表示一个字符串，函数将c的担子截字符转换成多字节字符。
TO_NUMBER([,[,)
c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。
TO_SINGLE_BYTE()
将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
其它单行函数
BFILENAME(
,)
dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
DECODE(,,[,,,[])
x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。
DUMP(,[,[,[,]]])
x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。
EMPTY_BLOB()
该函数没有参数，函数返回 一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。
EMPTY_CLOB()
该函数没有参数，函数返回 一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。
GREATEST()
exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。
LEAST()
exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。
UID
该函数没有参数，返回唯一标示当前数据库用户的整数。
USER
返回当前用户的用户名
USERENV()
基于opt返回包含当前会话信息。opt的可选值为：
ISDBA 会话中SYSDBA脚色响应，返回TRUE
SESSIONID 返回审计会话标示符
ENTRYID 返回可用的审计项标示符
INSTANCE 在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有 多个实例的情况下使用。
LANGUAGE 返回语言、地域、数据库设置的字符集。
LANG 返回语言名称的ISO缩写。
TERMINAL 为当前会话使用的终端或计算机返回操作系统的标示符。
VSIZE()
x是一个表达式。返回x内部表示的字节数。
SQL中的组函数
组函数也叫集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同的是，在解析时所有的行都是已知的。由于这种差别使组函数与单行函数有在要求和行为上有微小的差异.
组（多行）函数
与单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数可以在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。
AVG([{DISYINCT|ALL}])
返回数值的平均值。缺省设置为ALL.
SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COUNT({*|DISTINCT|ALL} )
返回查询中行的数目，缺省设置是ALL,*表示返回所有的行。
MAX([{DISTINCT|ALL}])
返回选择列表项目的最大值，如果x是字符串数据类型，他返回一个VARCHAR2数据类型，如果X是一个DATA数据类型，返回一个日期，如果X是numeric数据类型，返回一个数字。注意distinct和all不起作用，应为最大值与这两种设置是相同的。
MIN([{DISTINCT|ALL}])
返回选择列表项目的最小值。
STDDEV([{DISTINCT|ALL}])
返回选者的列表项目的标准差，所谓标准差是方差的平方根。
SUM([{DISTINCT|ALL}])
返回选择列表项目的数值的总和。
VARIANCE([{DISTINCT|ALL}])
返回选择列表项目的统计方差。
用GROUP BY给数据分组
正如题目暗示的那样组函数就是操作那些已经分好组的数据，我们告诉数据库用GROUP BY怎样给数据分组或者分类，当我们在SELECT语句的SELECT子句中使用组函数时，我们必须把为分组或非常数列放置在GROUP BY子句中，如果没有用group by进行专门处理，那么缺省的分类是将整个结果设为一类。
select stat,counter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT-- ---------AK 360AL 1212AR 1309AZ 768CA 3982
在这个例子中，我们用state字段分类；如果我们要将结果按照zip_codes排序,可以用ORDER BY语句，ORDER BY子句可以使用列或组函数。
select stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*)-- --------NY 4312PA 4297TX 4123CA 3982
用HAVING子句限制分组数据
现在你已经知道了在查询的SELECT语句和ORDER BY子句中使用主函数，组函数只能用于两个子串中，组函数不能用于WHERE子串中，例如下面的查询是错误的：
错误SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000 GROUP BY sales_clerk
这个语句中数据库不知道SUM()是什么，当我们需要指示数据库对行分组，然后限制分组后的行的输出时，正确的方法是使用HAVING语句：
SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' GROUP BY sales_clerkHAVING SUM(sale_amount)>10000;
嵌套函数
函数可以嵌套。一个函数的输出可以是另一个函数的输入。操作数有一个可继承的执行过程。但函数的优先权只是基于位置，函数遵循由内到外，由左到右的原则。嵌套技术一般用于象DECODE这样的能被用于逻辑判断语句IF....THEN...ELSE的函数。
函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：
单行函数
组函数
本文将讨论如何利用单行函数以及使用规则。
SQL中的单行函数
SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。
NULL和单行函数
在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。
下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿
column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2
不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：
update empset salary=(salary+bonus)*1.1
这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。
所以正确的语句是：
update empset salary=(salary+nvl(bonus,0)*1.1
单行字符串函数
单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串
ASCII()
c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122
CHR(＜i＞)[NCHAR_CS]
i是一个数字，函数返回十进制表示的字符
select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B
CONCAT(,)
c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null。他和操作符||返回的结果相同
select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda
INITCAP()
c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。
select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.
select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2
INSTRB(,[,i[,j])
与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()
LENGTH()
c1为字符串，返回c1的长度，如果c1为null，那么将返回null值。
select LENGTH('Ipso Facto') ergo from dualergo10
LENGTHb()
与LENGTH()一样，返回字节。
lower()
返回c的小写字符，经常出现在where子串中
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格，参见RPAD。
select LPAD(answer,7,'') padded,answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybe
LTRIM(,)
把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。
select LTRIM('Mississippi','Mis') from dualLTRppi
RPAD(,＜i＞[,])
在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格,其他与LPAD相似
RTRIM(,)
把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。
REPLACE(,[,])
c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。
select REPLACE('uptown','up','down') from dualREPLACEdowntown
STBSTR(,＜i＞[,])
c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
select SUBSTR('Message',1,4) from dualSUBSMess
SUBSTRB(,＜i＞[,])
与SUBSTR大致相同，只是I,J是以字节计算。
SOUNDEX()
返回与c1发音相似的词
select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dualDawes Daws DawsonD200 D200 D250
TRANSLATE(,,)
将c1中与c2相同的字符以c3代替
select TRANSLATE('fumble','uf','ar') test from dualTEXTramble
TRIM([[]] from c3)
将c3串中的第一个，最后一个，或者都删除。
select TRIM(' space padded ') trim from dual TRIMspace padded
UPPER()
返回c1的大写，常出现where子串中
select name from dual where UPPER(name) LIKE 'KI%'NAMEKING
单行数字函数
单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle没有提供内建的弧度和角度的转换函数。
ABS()
返回n的绝对值
ACOS()
反余玄函数，返回-1到1之间的数。n表示弧度
select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0
ASIN()
反正玄函数，返回-1到1，n表示弧度
ATAN()
反正切函数，返回n的反正切值，n表示弧度。
CEIL()
返回大于或等于n的最小整数。
COS()
返回n的余玄值，n为弧度
COSH()
返回n的双曲余玄值，n 为数字。
select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847
EXP()
返回e的n次幂，e=2.71828183.
FLOOR()
返回小于等于N的最大整数。
LN()
返回N的自然对数，N必须大于0
LOG(,)
返回以n1为底n2的对数
MOD()
返回n1除以n2的余数，
POWER(,)
返回n1的n2次方
ROUND(,)
返回舍入小数点右边n2位的n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。
select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
SIGN()
如果n为负数，返回-1,如果n为正数，返回1，如果n=0返回0.
SIN（)
返回n的正玄值,n为弧度。
SINH()
返回n的双曲正玄值,n为弧度。
SQRT()
返回n的平方根,n为弧度
TAN（)
返回n的正切值,n为弧度
TANH()
返回n的双曲正切值,n为弧度
TRUNC(,)
返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
单行日期函数
单行日期函数操作DATA数据类型，绝大多数都有DATA数据类型的参数，绝大多数返回的也是DATA数据类型的值。
ADD_MONTHS(,＜i＞)
返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。
LAST_DAY()
函数返回包含日期d的月份的最后一天
MONTHS_BETWEEN(,)
返回d1和d2之间月的数目,如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。
NEW_TIME(,,)
d1是一个日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2时字符串。
NEXT_DAY(,)
返回日期d后由dow给出的条件的第一天，dow使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与d的时间分量相同。
select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004
ROUND([,])
将日期d按照fmt指定的格式舍入，fmt为字符串。
SYADATE
函数没有参数，返回当前日期和时间。
TRUNC([,])
返回由fmt指定的单位的日期d.
单行转换函数
单行转换函数用于操作多数据类型，在数据类型之间进行转换。
CHARTORWID()
c 使一个字符串，函数将c转换为RWID数据类型。
SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
CONVERT(,[,])
c尾字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset的缺省设置为数据库的字符集。
HEXTORAW()
x为16进制的字符串，函数将16进制的x转换为RAW数据类型。
RAWTOHEX()
x是RAW数据类型字符串，函数将RAW数据类转换为16进制的数据类型。
ROWIDTOCHAR()
函数将ROWID数据类型转换为CHAR数据类型。
TO_CHAR([[,)
x是一个data或number数据类型，函数将x转换成fmt指定格式的char数据类型，如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
TO_DATE([,[,）
c表示字符串，fmt表示一种特殊格式的字符串。返回按照fmt格式显示的c,nlsparm表示使用的语言。函数将字符串c转换成date数据类型。
TO_MULTI_BYTE()
c表示一个字符串，函数将c的担子截字符转换成多字节字符。
TO_NUMBER([,[,)
c表示字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparm表示语言，函数将返回c代表的数字。
TO_SINGLE_BYTE()
将字符串c中得多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
其它单行函数
BFILENAME(
,)
dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
DECODE(,,[,,,[])
x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。
DUMP(,[,[,[,]]])
x是一个表达式或字符，fmt表示8进制、10进制、16进制、或则单字符。函数返回包含了有关x的内部表示信息的VARCHAR2类型的值。如果指定了n1,n2那么从n1开始的长度为n2的字节将被返回。
EMPTY_BLOB()
该函数没有参数，函数返回 一个空的BLOB位置指示符。函数用于初始化一个BLOB变量或BLOB列。
EMPTY_CLOB()
该函数没有参数，函数返回 一个空的CLOB位置指示符。函数用于初始化一个CLOB变量或CLOB列。
GREATEST()
exp_list是一列表达式，返回其中最大的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，那么返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。
LEAST()
exp_list是一列表达式，返回其中最小的表达式，每个表达式都被隐含的转换第一个表达式的数据类型，如果第一个表达式是字符串数据类型中的任何一个，将返回的结果是varchar2数据类型，同时使用的比较是非填充空格类型的比较。
UID
该函数没有参数，返回唯一标示当前数据库用户的整数。
USER
返回当前用户的用户名
USERENV()
基于opt返回包含当前会话信息。opt的可选值为：
ISDBA 会话中SYSDBA脚色响应，返回TRUE
SESSIONID 返回审计会话标示符
ENTRYID 返回可用的审计项标示符
INSTANCE 在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有 多个实例的情况下使用。
LANGUAGE 返回语言、地域、数据库设置的字符集。
LANG 返回语言名称的ISO缩写。
TERMINAL 为当前会话使用的终端或计算机返回操作系统的标示符。
VSIZE()
x是一个表达式。返回x内部表示的字节数。
SQL中的组函数
组函数也叫集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同的是，在解析时所有的行都是已知的。由于这种差别使组函数与单行函数有在要求和行为上有微小的差异.
组（多行）函数
与单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数可以在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。
AVG([{DISYINCT|ALL}])
返回数值的平均值。缺省设置为ALL.
SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COUNT({*|DISTINCT|ALL} )
返回查询中行的数目，缺省设置是ALL,*表示返回所有的行。
MAX([{DISTINCT|ALL}])
返回选择列表项目的最大值，如果x是字符串数据类型，他返回一个VARCHAR2数据类型，如果X是一个DATA数据类型，返回一个日期，如果X是numeric数据类型，返回一个数字。注意distinct和all不起作用，应为最大值与这两种设置是相同的。
MIN([{DISTINCT|ALL}])
返回选择列表项目的最小值。
STDDEV([{DISTINCT|ALL}])
返回选者的列表项目的标准差，所谓标准差是方差的平方根。
SUM([{DISTINCT|ALL}])
返回选择列表项目的数值的总和。
VARIANCE([{DISTINCT|ALL}])
返回选择列表项目的统计方差。
用GROUP BY给数据分组
正如题目暗示的那样组函数就是操作那些已经分好组的数据，我们告诉数据库用GROUP BY怎样给数据分组或者分类，当我们在SELECT语句的SELECT子句中使用组函数时，我们必须把为分组或非常数列放置在GROUP BY子句中，如果没有用group by进行专门处理，那么缺省的分类是将整个结果设为一类。
select stat,counter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT-- ---------AK 360AL 1212AR 1309AZ 768CA 3982
在这个例子中，我们用state字段分类；如果我们要将结果按照zip_codes排序,可以用ORDER BY语句，ORDER BY子句可以使用列或组函数。
select stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*)-- --------NY 4312PA 4297TX 4123CA 3982
用HAVING子句限制分组数据
现在你已经知道了在查询的SELECT语句和ORDER BY子句中使用主函数，组函数只能用于两个子串中，组函数不能用于WHERE子串中，例如下面的查询是错误的：
错误SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000 GROUP BY sales_clerk
这个语句中数据库不知道SUM()是什么，当我们需要指示数据库对行分组，然后限制分组后的行的输出时，正确的方法是使用HAVING语句：
SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' GROUP BY sales_clerkHAVING SUM(sale_amount)>10000;
嵌套函数
函数可以嵌套。一个函数的输出可以是另一个函数的输入。操作数有一个可继承的执行过程。但函数的优先权只是基于位置，函数遵循由内到外，由左到右的原则。嵌套技术一般用于象DECODE这样的能被用于逻辑判断语句IF....THEN...ELSE的函数。
2009年4月4日星期六
订阅：
评论 (Atom)