-
博文
2009年5月8日星期五
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
订阅：
评论 (Atom)