sql学习资料笔记

创建视图和索引
※视图常常被称为虚表。

在视图被创建以后，你可以对视图采用如下命令：select，insert，
update，delete。

※索引是与磁盘上数据的存储方式不同的另外一种组织数据的方法。

◎使用视图
※视图并不占用数据库或表的物理空间。

※创建视图的语法如下：
CREATE VIEW <view_name> [(col1,col2...)]
AS SELECT <col_names>
FROM <table_names>;
※视图可以是多级的，也就是可以用视图来创建视图。

但这会加大维护工作的复杂性。

1。

列的重命名
注意：
当在视图中使用SQL的计算功能时，SQL会要求你给出一个虚字段的名字，这是可以理解，因为象
COUNT(*)或A VG(PAYMENT)是不能作为列名的。

2。

SQL对视图的处理过程
※运行SELECT语句-->查找表-->未找到-->查找视图。

3。

在SELECT语句中的使用约束
在视图的SELECT语句中：
※不能使用UNION操作。

※不能使用ORDER BY子句，但在视图中，使用GROUP BY子句可以有ORDER BY子句相同的功能。

4。

在视图中修改数据
在视图创建以后，就可以用Insert，Update，Delete语句来更新，插入，删除视图中的数据。

5。

在视图中修改数据的几个问题
※对于多表视图，不能使用DELETE语句。

※除非底层表的所有非空列都已经在视图中出现，否那么，你不能使用Insert语句。

有这个限制的原
因是SQL不知道该将什么数据插入到NOT NULL限制列中（没有在视图中出现的）。

※如果对一个归并的表格插入或更新记录，那么所有被更新的记录必须属于同一个物理表。

※如果你在创建视图时使用了DISTINCT语句，那么你就不能插入或更新这个视图中的记录。

※你不能更新视图中的虚拟列（它是用计算字段得到的）。

6。

通用应用程序的视图
下面有几个视图需要完成的任务：
※提供了用户安全功能。

※可以进行单位换算。

※创建一个新的虚拟表格。

※简单的结构化复合查询。

-->视图与安全性
-->在单位换算中使用视图
-->在视图中使用简单的结构化复合查询
7。

删除视图的语句
DROP VIEW view_name;
注意：该命令会使所有与DROP掉的视图相关联的视图不能正常运行。

一些数据库系统甚至会将所有
与DROP掉的视图相关联的视图也删除掉。

◎使用索引
在SQL中使用索引有以下几个原因：
※在使用UNIQUE关键字时强制性地保证数据的完整性。

※可以容易地使用索引字段或其他字段进行排序。

※提高查询的执行速度。

1。

什么是索引
创建索引的语法：
CREATE INDEX index_name
ON table_name(col1[,col2...]);
注意：在不同的数据库系统中，创建索引的语法差别很大。

删除索引的语法：
DROP INDEX index_name;
备注：当表被删除时，所有与表相关的索引也将被删除。

2。

使用索引的技巧
※对于小表来说，使用索引对于性能不会有任何提高。

※当你的索引中有极多不同的数据和空值时，索引会使性能有极大的提高。

※当查询返回的数据很少时，索引可以优化你的查询（比较好的情况是少于全部数据的25％）。

如
果要返回的数据很多时，索引会加大系统开销。

※索引会提高数据的返回速度，但是它使数据的更新速度变慢。

如果要进行大量的更新操作，请先
删除索引，在执行完更新操作后，再恢复索引。

※索引会占用你的数据库空间，在设计数据库的可用空间时要考虑到。

※如果对字段的索引已经对两个表进行了归并操作，这一技术可以极大地提高归并的速度。

※大多数数据库系统不允许对视图创建索引。

※不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低你所期望获得的性能。

※不要将索引和表存储在同一个驱动器上，分开存储会去掉访问的冲突，从而使结果返回的更快。

3。

对更多的字段进行索引
这也叫复合索引。

4。

在创建索引时使用UNIQUE关键字
复合索引通常使用UNIQUE关键字来防止有相同数据的多个记录多次出现。

在创建索引时，可以使用DESC关键字。

5。

索引与归并
在归并查询中，对表中唯一属性的字段或用以归并操作的字段创建索引，可以大大比高归并的效率。

6。

群集（簇）的使用
当使用群集索引时，数据的表中的物理排列方式将会被修改。

使用群集索引通常比传统的不使用群
集的索引速度要快。

许多数据库系统（如Sybase的SQL Server）只允许一个表有一个群集索引。

用于创建群集索引的字段常常是主关键字。

※用Sybase的Transact-SQL创建群集索引的例子:
CREATE UNIQUE CLUSTERED INDEX id_index ON book(id);
※iceriver(林)
返回数据库内所有表的字段详细说明的SQL语句（可以直接粘贴使用）
SELECT
(case when a.colorder=1 then else '' end) N'表名',
a.colorder N'字段序号',
N'字段名',
(case when COLUMNPROPERTY( a.id,,'IsIdentity')=1 then '√'else ''
end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = ))))))) AND (xtype = 'PK'))>0 then '√' else '' end) N'主键',
N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and <>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
--用了游标循环删除，速度有点慢，
--其实可以考虑先生成对应关系的脚本，再删除现有关系、约束等，然后再用TRUNCATE TABLE来删除，
--最后再重建关系会否快一些呢？呵呵，有空测试一下
begin tran
SET NOCOUNT ON
--记录所有外键相关表
select object_name(fkeyID) as cName,
object_name(rkeyID) as fName
into #FkeyTable
from sysforeignkeys
--建立临时表，记录已经删除的表
CREATE table #HaveDel
(
TableName varchar(100)
)
declare @TableName varchar(100)
--循环删除只具有FOREIGN KEY 约束的表对象while exists (select cName from #FkeyTable ) begin
select cName
into #NowTable
from
(
select cName
from #FkeyTable
) as a
left join
(
select fName
from #FkeyTable
on ame=b.Fname
where b.fname is null
declare cur_DelTable cursor
for
select cName
from #NowTable
open cur_DelTable
fetch next from cur_DelTable into @TableName
while @@FETCH_STATUS = 0
begin
print '正在删除：'+ @TableName
exec ('delete from ' + @TableName)
insert into #HaveDel select @TableName
fetch next from cur_DelTable into @TableName end
close cur_DelTable
deallocate cur_DelTable
--删除掉外键相关表中已经删除的表的相关记录
from #FkeyTable
where exists (select cName from #NowTable where #ame = #ame)
drop table #NowTable
end
drop table #FkeyTable
--删除剩余表记录
declare cur_DelTable cursor
for
select
from sysobjects as tbl
left join #HaveDel on =#HaveDel.TableName
where #HaveDel.TableName is null
and tbl.xtype='u'
open cur_DelTable
fetch next from cur_DelTable into @TableName
while @@FETCH_STATUS=0
begin
print '正在删除：'+ @TableName
exec ('delete from ' + @TableName)
fetch next from cur_DelTable into @TableName
end
close cur_DelTable
deallocate cur_DelTable
drop table #HaveDel
print '删除完成！'
commit tran
交叉表实现一例
问题：
源表数据：
ID NAME CODE
1 A AA
2 A BB
3 B CC
4 B DD
5 C EE
6 C FF
7 D GG
8 D HH
9 E II
10 E JJ
希望统计成如下格式：
CODE A C D E F 合计
---------- ----------- ----------- ----------- ----------- ----------- ----- AA 1 0 0 0 0 10% BB 1 0 0 0 0 10% CC 0 1 0 0 0 10% DD 0 1 0 0 0 10% EE 0 0 1 0 0 10%
FF 0 0 1 0 0 10% GG 0 0 0 1 0 10% HH 0 0 0 1 0 10%
II 0 0 0 0 1 10%
JJ 0 0 0 0 1 10%
实现步骤如下：
--建立测试数据
declare @tmp1 table
(
id int,
name varchar(10),
code varchar(10)
)
insert into @tmp1
select 1,'A','AA'
UNION
select 2,'A','BB' UNION
select 3,'B','CC' UNION
select 4,'B','DD' UNION
select 5,'C','EE' UNION
select 6,'C','FF' UNION
select 7,'D','GG' UNION
select 8,'D','HH' UNION
select 9,'E','II' UNION
select 10,'E','JJ' --返回结果SELECT CODE, sum(A) as 'A', sum(B) as 'C',
sum(C) as 'D',
sum(D) as 'E',
sum(E) as 'F',
left(CAST(COUNT(CODE) AS DECIMAL(10,2))/(SELECT COUNT(*) FROM @TMP1)*100,2) +'%' AS '合计'
FROM
(SELECT CODE ,
CASE WHEN NAME='A' THEN count(CODE) ELSE 0 end as A, CASE WHEN NAME='B' THEN count(CODE) ELSE 0 end AS B, CASE WHEN NAME='C' THEN count(CODE) ELSE 0 end AS C, CASE WHEN NAME='D' THEN count(CODE) ELSE 0 end AS D, CASE WHEN NAME='E' THEN count(CODE) ELSE 0 end AS E FROM @tmp1
GROUP BY CODE,name) AS C
GROUP BY CODE
锁定数据库的一个表
SELECT * FROM table WITH (HOLDLOCK)
注意: 锁定数据库的一个表的区别
SELECT * FROM table WITH (HOLDLOCK)
其他事务可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX)
其他事务不能读取表,更新和删除
SELECT 语句中“加锁选项”的功能说明
SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。

用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果。

本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。

功能说明：
NOLOCK（不加锁）
此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。

在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）
此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

UPDLOCK（修改锁）
此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。

使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁）
此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。

这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）
此选项为默认选项，当被选中时，SQL Server 使用共享页锁。

TABLOCKX（排它表锁）
此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。

这将防止其他进程读取或修改表中的数据
一。

dbcc memusage:占用内存多少？
二。

整理数据库中所有表的索引碎片：sp_msforeachtable 'dbcc dbreindex("?")'
三。

数据库远程访问：
方案一：SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=ServerName;
User ID=MyUID;Password=MyPass').Northwind.dbo.表名
方案二：select * from openrowset('SQLOLEDB','sql服务器名';'用户名';'密码' ,数据库名.dbo.表名)
四。

select name from sysobjects where type='u' --读表语句
select name from syscolumns where id=object_id('表名')--读字段名(sysobject:系统表，存放表，视图，规那么，存储过程，默认值和触发器信息)
select db_id (N'数据库名字')：检测相应数据库的ID(dbid)
select object_id (N'对象名')：检测相应对象名的ID(objectid)
SELECT * FROM table WITH (HOLDLOCK):锁定数据库的一个表，其他事务可以读取表，不能删除表；
SELECT * FROM table WITH (TABLOCKX):锁定数据库的一个表, 其他事务不能读取表、更新和删除；
五。

UPDATE 。

FROM。

UPDATE a
SET a....
FROM table a
INNER JOIN table b
ON ......
WHERR......
f exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_copydb]
GO
/*--数据库数据复制
将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制
适用X围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建2003.10(引用请保留此信息)--*/
/*--调用示例
exec p_copydb 'bns_aa','bns_new'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/
create proc p_copydb
@o_dbname sysname, --要复制数据的数据库--源数据库
@n_dbname sysname --接收数据的数据库--目标数据库
as
declare @sql nvarchar(4000)
--禁用约束/触发器,防止复制时的数据冲突
set @sql='declare #tbc cursor for select name
from
[email='+@n_dbname+'..sysobjects]%27+@n_dbname+%27..sysobjects[ /email] where xtype=''U'' and status>=0'
exec(@sql)
declare @tbname sysname
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status=0
begin
set @sql='alter table [email='+@n_dbname+'..['+@tbname+']%27+@n_dbname+%27..%5B% 27+@tbname+%27[/email]] NOCHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table [email='+@n_dbname+'..['+@tbname+']%27+@n_dbname+%27..%5B% 27+@tbname+%27[/email]] disable trigger ALL'
exec(@sql)
fetch next from #tbc into @tbname
end
close #tbc
--复制数据
declare @sql1 varchar(8000)
set @sql='declare #tb cursor for select from '
[email=+@o_dbname+'..sysobjects]+@o_dbname+%27..sysobjects[/ema il] a inner join '
[email=+@n_dbname+'..sysobjects]+@n_dbname+%27..sysobjects[/ema il] b on =
where a.xtype=''U'' and b.xtype=''U'''
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
select @sql1=''
,@sql='select @sql1=@sql1+'',[''++'']'' from(
select name from [email='+@o_dbname+'..syscolumns]%27+@o_dbname+%27..syscolum ns[/email] where id in
(select id from [email='+@o_dbname+'..sysobjects]%27+@o_dbname+%27..sysobjects[ /email] where [email=name='''+@tbname+''']name=%27%27%27+@tbname+%27%27 %27[/email])
) a inner join (
select name from [email='+@n_dbname+'..syscolumns]%27+@n_dbname+%27..syscolum ns[/email] where status<>0x80 and id in
(select id from [email='+@n_dbname+'..sysobjects]%27+@n_dbname+%27..sysobjects[ /email] where [email=name='''+@tbname+''']name=%27%27%27+@tbname+%27%27
%27[/email])
) b on ='
exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out
select @sql1=substring(@sql1,2,8000)
exec('insert into [email='+@n_dbname+'..['+@tbname+']('+@sql1]%27+@n_dbname+%2 7..%5B%27+@tbname+%27%5D%28%27+@sql1[/email]
+') select [email='+@sql1+']%27+@sql1+%27[/email] from [email='+@o_dbname+'..['+@tbname+']']%27+@o_dbname+%27..%5B %27+@tbname+%27%5D%27[/email])
if @@error<>0
print('insert into [email='+@n_dbname+'..['+@tbname+']('+@sql1]%27+@n_dbname+%2 7..%5B%27+@tbname+%27%5D%28%27+@sql1[/email]
+') select [email='+@sql1+']%27+@sql1+%27[/email] from [email='+@o_dbname+'..['+@tbname+']']%27+@o_dbname+%27..%5B %27+@tbname+%27%5D%27[/email])
fetch next from #tb into @tbname
end
close #tb
deallocate #tb
--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status=0
begin
set @sql='alter table [email='+@n_dbname+'..['+@tbname+']%27+@n_dbname+%27..%5B% 27+@tbname+%27[/email]] CHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table [email='+@n_dbname+'..['+@tbname+']%27+@n_dbname+%27..%5B% 27+@tbname+%27[/email]] enable trigger ALL'
exec(@sql)
fetch next from #tbc into @tbname
end
close #tbc
deallocate #tbc
go
1.把某个字段重新生气序列(从1到n):
DECLARE @i int
Set @i = 0
Update Table1 Set @i = @i + 1,Field1 = @i
2.按成绩排名次
Update 成绩表
Set a.名次= (
Select Count(*) + 1
From 成绩表b
Where a.总成绩< b.总成绩
)
From 成绩表a
3.查询外部数据库
Select a.*
From
OpenRowSet('Microsoft.Jet.OLEDB.4.0','c:\test.mdb';'admin';'',Table1) a
4.查询Excel文件
Select *
From OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
5.在查询中指定排序规那么
Select * From Table1 Order By Field1 COLLATE Chinese_PRC_BIN
为什么要指定排序规那么呢?参见:
例,检查数据库中的Pub_Users表中是否存在指定的用户:
Select Count(*) From Pub_Users Where [UserName]='admin' And [PassWord]='aaa' COLLATE Chinese_PRC_BIN
默认比较是不区分大小写的,如果不加COLLATE Chinese_PRC_BIN,那么密码aaa与AAA是等效的,这当然与实际不符.注意的是,每个条件都要指定排序规那么,上例中用户名就不区分大小写.
6.Order By的一个小技巧
Order By可以指定列序而不用指定列名,在下面的例子里说明它的用处(注意,第三列未指定别名)
Select a.ID,,(Select Count(*) From TableB b Where a.ID=b.PID) From TableA a Order By 3
下面介绍下oracle安装文件目录结构(以D盘为例)：
l 默认ORACLE_BASE
n 在Windows中，默认的ORACLE_BASE目录是：
D:\oracle\product\10.1.0
n 在UNIX中，默认的ORACLE_BASE目录是：
/pm/app/oracle/10.1.0
所有的ORACLE软件文件和数据库文件都位于ORACLE_BASE下面的子目录中。

l 默认ORACLE_HOME
n 在Windows中，默认的ORACLE_HOME目录是：
D:\oracle\product\10.1.0\dbct
n 在UNIX中，默认的ORACLE_BASE目录是：
/pm/app/oracle/10.1.0/dbct
ORACLE_HOME是访问所有ORACLE软件的路径。

l Admin文件夹，存储初始化文件和日志文件
l Oradata\oract文件夹，存储数据库数据文件.dbf、控制文件.ctl、重做日志文件.log
熟悉了利用enterprice manager database control 进行数据的基本管理，如仓健用户，分配权限，设置中断点，启动监听程序(Listener),保存身份验证，net manager 配置。

还有如：
1。

远程终端配置；
2。

数据导出/导出；
3。

数据库备份；
4。

isql*plus语法；。