SQLSERVER实用
库操作:
表操作:
创建新表 , 删除新表 , 增加一列 , 添加主键 , 创建索引 , 创建视图
高级查询运算 :
UNION 运算符, EXCEPT 运算符 , INTERSECT 运算符, 使用外连接
Sql 语句具体用法:
复制表 , 拷贝表 , 跨数据库之间表的拷贝 , 子查询 , 显示内容 , 外连接查询 , 四表联查 , 在线视图查询
前 10条记录 , 随机取出 10条数据 , 选择从 10到 15的记录
找出 PPP表中所有重复的纪录 , 找出 PPP表中最小的数,不能使用 min()或可以使用 min()
BeginTrans 、 RollbackTrans 、 CommitTrans 方法,
Execute 方法
存储过程相关:
SQL-PROCEDURE联机从书 , 创建存储过程 , 存储过程的执行方法 , 存储过程的撤消 , 带参数的存储过程
下列语句部分是
Mssql
语句,不可以在
access
中使用
SQL
分类:
DDL—
数据定义语言
(CREATE
,
ALTER
,
DROP
,
DECLARE)
DML—
数据操纵语言
(SELECT
,
DELETE
,
UPDATE
,
INSERT)
DCL—
数据控制语言
(GRANT
,
REVOKE
,
COMMIT
,
ROLLBACK)
简要介绍基础语句
1
、说明:创建数据库
CREATE DATABASE
database-name ;
<返回
>
2
、说明:删除数据库
DROP DATABASE
database-name;
<返回
>
3
、说明:备份
sql
server
--
创建
备份数据的
device
USE
master;
EXEC
sp_addumpdevice
'disk', 'testBack
', 'c:\mssql7backup\MyNwind_1.dat
';
---
开始
备份
BACKUP DATABASE
pubs TO
testBack
;
<返回
>
4
、说明:创建新表
CREATE TABLE
tabname
(col1 type1 [not null
] [primary key
],col2 type2 [not null],..);
根据已有的表创建新表:
A
:
CREATE TABLE
tab_new
LIKE
tab_old
(
使用旧表
创建新表
);
B
:
CREATE TABLE tab_new as select col1,col2 …FROM tab_old DEFINITION ONLY;
<返回
>
5
、说明:
删除新表 : DROP TABLE tabname
<返回
>
6
、说明:
增加一个列 : ALERT TABLE tablename ADD columnname columntype [null];
删除一列:
ALERT TABLE tablename
DROP
columnname
;
注:列增加
后将不能删除。
DB2
中列加上后数据类型也不能改变,唯一能改变的是增加
varchar
类型的长度。
<返回
>
7
、说明:
添加主键 : ALERT TABLE tabname ADD primary key( col ) ;
删除主键: ALERT TABLE tabname DROP primary key( col ) ;
<返回
>
8
、说明:
创建索引 : CREATE [unique] INDEX idxname ON tabname ( col ….);
删除索引: DROP INDEX idxname ;
注:索引是不可更改的,想更改必须删除重 新建。
<返回
>
9
、说明:
创建视图 : CREATE VIEW viewname AS select statement ;
删除视图: DROP VIEW viewname ;
<返回
>
10
、说明:
简单的基本的
sql
语句
选择:
SELECT
* FROM
table1 WHERE
范围
插入:
INSERT INTO
table1(field1,field2) VALUES
(value1,value2)
删除:
DELETE FROM
table1 WHERE
范围
更新:
UPDATE
table1 SET field1=value1 WHERE
范围
查找:
SELECT
* FROM
table1 WHERE
field1 LIKE
’%value1%’
--like
的语法查资料
!
排序:
SELECT
* FROM
table1 ORDER BY
field1,field2 [desc
]
总数:
SELECT
COUNT
(*) AS totalcount
from table1
求和:
SELECT
SUM
(field1) AS
sumvalue
from table1
平均:
SELECT
AVG
(field1) AS avgvalue
from table1
最大:
SELECT
MAX
(field1) AS maxvalue
from table1
最小:
SELECT
MIN
(
field1) AS minvalue
from table1
<返回
>
11
、说明:几个高级查询运算词
A
:
UNION
运算符
UNION
运算符通过组合其他两个结果表(例如
TABLE1
和
TABLE2
)并消去表中任何重复行而派生出一个结果表。当
ALL
随
UNION
一起使用时(即
UNION ALL
),不消除重复行。两种情况下,派生表的每一行不是来自
TABLE1
就是来自
TABLE2
。
<返回
>
B
:
EXCEPT
运算符
EXCEPT
运算符通过包括所有在
TABLE1
中但不在
TABLE2
中的行并消除所有重复行而派生出一个结果表。当
ALL
随
EXCEPT
一起使用时
(EXCEPT ALL)
,不消除重复行。
<返回
>
C
:
INTERSECT
运算符
INTERSECT
运算符通过只包括
TABLE1
和
TABLE2
中都有的行并消除所有重复行而派生出一个结果表。当
ALL
随
INTERSECT
一起使用时
(INTERSECT ALL)
,不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
<返回
>
12
、说明:使用外连接
A
、
LEFT OUTER JOIN
左外连接(左连接):结果集几包括
连接表的匹配行,也包括左连接表的所有行。
SQL
:
select a.a
, a.b
, a.c
, b.c
, b.d
, b.f
from a left out join b on a.a
= b.c
;
B
:
RIGHT OUTER JOIN
右外连接
(
右连接
)
:结果集既包括
连接表的匹配连接行,也包括右连接表的所有行。
C
:
FULL OUTER JOIN
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
一些不错的
sql
语句
1
、说明:复制表
(
只复制结构
,
源表名:
a
新表名:
b) (Access
可用
)
法一
:
SELECT
* INTO
b FROM
a WHERE
1<>1;
或者
SELECT
* INTO
b FROM
a WHERE
1=0;
法二:
SELECT
TOP
0 * INTO
b FROM
a;
<返回
>
2
、说明:拷贝表
(
拷贝数据
,
源表名:
a
目标表名:
b) (Access
可用
)
INSERT INTO
b(a, b, c) SELECT
d,e,f
FROM
b;
或者
SELECT
* INTO
b FROM
a;
<返回
>
3
、说明:跨数据库之间表的拷贝
(
具体数据使用绝对路径
) (Access
可用
)
INSERT INTO
b(a, b, c) SELECT
d,e,f
FROM b IN‘
具体数据库
’ WHERE
条件
例子:
..FROM b IN '"&Server.MapPath
(
".")&"\data.mdb" &"' where..
<返回
>
4
、说明:子查询
(
表名
1
:
a
表名
2
:
b)
SELECT
a,b,c
FROM
a WHERE
a IN
(SELECT
d FROM
b )
或者
: SELECT
a,b,c
FROM
a WHERE
a IN
(1,2,3);
<返回
>
5
、说明:显示文章、提交人和最后回复时间
SELECT
a.title,a.username,b.adddate
FROM table a,(SELECT
max(adddate
) adddate
FROM
table WHERE
table.title
=a.title
) b
<返回
>
6
、说明:外连接查询
(
表名
1
:
a
表名
2
:
b)
SELECT
a.a
, a.b
, a.c
, b.c
, b.d
, b.f
F ROM
a LEFT OUT JOIN
b ON
a.a
= b.c
;
<返回
>
7
、说明:在线视图查询
(
表名
1
:
a )
SELECT
* FROM
(select a,b,c
from a) T WHERE
t.a
> 1;
<返回
>
8
、说明:
between
的用法
,between
限制查询数据范围时包括了边界值
,not between
不包括
SELECT
* FROM
table1 WHERE
time BETWEEN
time1 AND time2;
SELECT
a,b,c
, FROM
table1 WHERE
a NOT BETWEEN
数值
1 ADN
数值
2;
<返回
>
9
、说明:
in
的使用方法
select * from table1 where a [not] in (‘
值
1’
,’
值
2’
,’
值
4’
,’
值
6’
);
<返回
>
10
、说明:两张关联表,删除主表中已经在副表中没有的信息
DELETE FROM
table1 WHERE NOT EXISTS
( SELECT * FROM table2 WHERE table1.field1=table2.field1 );
<返回
>
11
、说明:
四表联查问
题
SELECT
* FROM
a,b,c,d
WHERE
关联条件;(未找到例子)
SELECT
* FROM
a LEFT INNER JOIN
b ON
a.a
=b.b
RIGHT INNER JOIN
c ON
a.a
=c.c
INNER JOIN
d ON a.a
=d.d
WHERE
.....
<返回
>
12
、说明:日程安排提前五分钟提醒
SELECT
* FROM
日程安排
WHERE
datediff
('minute',
开始时间
,getdate
())>5;
<返回
>
13
、说明:一条
sql
语句搞定数据库分页
SELECT TOP
10 b.*
FROM
(
SELECT TOP 20 主键字段 , 排序字段
FROM 表名 order by 排序字段 DESC
) a, 表名 b
WHERE b. 主键字段 = a. 主键字段
ORDER BY
a.
排序字段
<返回
>
14
、说明:前
10
条记录
SELECT TOP
10 * FROM
table1 WHERE
范围
<返回
>
15
、说明:选择在每一组
b
值相同的数据中对应的
a
最大的记录的所有信息
(
类似这样的用法可以用于论坛每月排行榜
,
每月热销产品分析
,
按科目成绩排名
,
等等
.)
SELECT
a,b,c
FROM
tablename
ta
WHERE a=
(
SELECT max( a)
FROM tablename tb
WHERE tb.b =ta.b
);
<返回
>
16
、说明:包括所有在
TableA
中但不在
TableB
和
TableC
中的行并消除所有重复行而派生出一个结果表
(SELECT
a FROM
tableA
)
EXCEPT (SELECT a FROM tableB )
EXCEPT (SELECT a FROM tableC ) ;
<返回
>
17
、说明:随机取出
10
条数据
SELECT TOP
10 * FROM
tablename
ORDER BY
newid
();
<返回
>
18
、说明:随机选择记录
SELECT newid
()
<返回
>
19
、说明:删除重复记录
DELETE FROM
tablename
WHERE
id NOT IN
(select max(id) FROM
tablename
group BY col1,col2,...)
<返回
>
20
、说明:列出数据库里所有的表名
SELECT
name FROM
sysobjects
WHERE
type='U
'
<返回
>
21
、说明:列出表里的所有的
SELECT
name FROM
syscolumns
WHERE
id=object_id
('TableName
');
<返回
>
22
、说明:列示
type
、
vender
、
pcs
字段
,以
type
字段排列,
case
可以方便地实现多重选择,类似
select
中的
case
。
SELECT TYPE,sum
(case vender when
'A' then pcs
else 0 end),sum
(case vender when
'C' then
pcs
else 0 end),sum
(case vender when
'B' then pcs
else 0 end) FROM
tablename
GROUP BY
type
显示结果:
type vender pcs
电脑
A 1
电脑
A 1
光盘
B 2
光盘
A 2
手机
B 3
手机
C 3
<返回
>
23
、说明:初始化表
table1
TRUNCATE
TABLE
tabname
;
<返回
>
24
、说明:选择从
10
到
15
的记录
SELECT TOP
5 *
FROM
(
SELECT TOP 15 *
FROM table ORDER BY id ASC
) table_ 别名 ORDER BY id DESC ;
25 、说明:找出 PPP 表中最小的数 ,不能使用 min() 或可以使用 min()
不能用 min():
SELECT num FROM ppp WHERE num<=( SELECT num FROM ppp );
或者
SELECT top 1 FROM ppp WHERE ORDER BY num;
可以用
min():
SELECT
num FROM ppp
WHERE
num=(
SELECT
min(num) FROM
ppp
);
26 、说明:找出 PPP 表中所有重复的纪录
SELECT * FROM ppp WHERE num in (SELECT num FROM ppp GROUP BY num HAVING ( count(num)>1));
27 、说明:创建临时表
CREATE TABLE #temptabname ( 字段 1 字段类型,字段 2 字段类型, …);
Access
左连接语法
(
最近开发要用左连接
,Access
帮助什么都没有
,
网上没有
Access
的
SQL
说明
,
只有自己测试
,
现在记下以备后查
)
语法
:
SELECT table1.fd1,table1,fd2,table2.fd2
FROM
table1 left join table2 on table1.fd1,table2.fd1
WHERE
...
<返回
>
Conn.Execute
说明
Execute
方法
该方法用于执行
SQL
语句。根据
SQL
语句执行后是否返回记录集,该方法的使用格式分为以下两种:
1
.执行
SQL
查询语句时,将返回查询得到的记录集。
用法为:
Set
对象变量名
=
连接对象
.Execute("SQL
查询语言
")
Execute
方法调用后,会自动创建记录集对象,并将查询结果存储在该记录对象中,通过
Set
方法,将记录集赋给指定的对象保存,以后对象变量就代表了该记录集对象。
2
.执行
SQL
的操作性语言时,没有记录集的返回。
此时用法为:
连接对象
.Execute "SQL
操作性语句
" [, RecordAffected
][, Option]·RecordAffected
为可选项,此出可
放置一个变量,
SQL
语句执行后,所生效的记录数会自动保存到该变量中。通过访问该变量,就可知道
SQL
语句队多少条记录进行了操作。
·Option
可选项,该参数的取值通常为
adCMDText
,它用于告诉
ADO
,应该将
Execute
方法之后的第一个字符解释为命令文本。通过指定该参数,可使执行更高效。
·BeginTrans
、
RollbackTrans
、
CommitTrans
方法
这三个方法是连接对象提供的用于事务处理的方法。
BeginTrans
用于开始一个事物;
RollbackTrans
用于回滚
事务;
CommitTrans
用于提交所有的事务处理结果,即确认事务的处理。
事务处理可以将一组操作视为一个整体,只有全部语句都成功执行后,事务处理才算成功;若其中有一个语句执行失败,则整个处理就算失败,并恢复到处里前的状态。
BeginTrans
和
CommitTrans
用于标记事务的开始和结束,在这两个之间的语句,就是作为事务处理的语句。判断事务处理是否成功,可通过连接对象的
Error
集合来实现,若
Error
集合的成员个数不为
0
,则说明有错误发生,事务处理失败。
Error
集合中的每一个
Error
对象,代表一个错误信息。
存储过程相关
1 、说明:联机帮助 使用:
存储过程 T-SQL 查询方法 :1 选择查询分析器 ------2SQLServer 联机从书 -------3 在索引中选择 :CREATE PROCEDURE
2 、说明:创建存储过程
A 创建简单存储过程
USE pubs – 访问数据源
If exists
(select name from sysobjects where name =' tim_PROCEDURE ' and type = 'P') drop procedure tim_PROCEDURE
go
create procedure tim_PROCEDURE -- 创建存储过程 [ 存储过程名 ]
as --AS 标志
select * from ps_ProjApply --select 操作并对变量赋值
go
3 、说明 Procedure 存储过程的使用
A 查询分析器中使用: execute au_info_all -- 执行存储过程 [ 存储过程名 ] 或者 exec au_info_all -- 简写
B Ado.Net 中使用 :
SqlCommand cmd =new SqlCommand ( );
cmd.CommandType =CommandType.Procedure ;
4 、说明: Procedure 的撤消
A:
if exists (select name from sysobjects where name='tim_PROCEDURE ' and type='p') drop procedure tim_PROCEDURE
-- 撤消存储过程
-- 其中 : P 代表存储过程, FN 代表用户自定义函数, V 代表示图, U 代表数据表
go -- 执行操作
5 、带参数的 Procedure
A: 简单操作
USE test -- 表
-- 如果存在此存储过程,先删除掉
IF exists ( select name from sysobjects where name=’tim_PROCEDURE ’ and type=’P’)
Drop procedure tim_procedure
-- 创建存储过程
create procedure tim_PROCEDURE@name nvarchar (20),
@editid int ,
@option nvarchar ( 20)
as
declare @maxid int
select @maxid =count(*) from [user]
If ( @option='INS')
------------------------------------------------------------
begin
set @maxid =@maxid+1
insert [user] (id,age,name )values (@maxid,20,@name)
end
-------------------------------------------------------------
if ( @option='UPD')
-------------------------------------------------------------
begin
update [user] set name=@namewhere id=@editid
end
-------------------------------------------------------------
Select * from [user]
go
-- 执行存储过程
--execute tim_PROCEDURE '',3 ,'UPD' go