SQLSERVER实用

库操作：

创建数据库 ， 删除数据库 ， 备份数据库

表操作：

创建新表 ， 删除新表 ， 增加一列 ， 添加主键 ， 创建索引 ， 创建视图

高级查询运算 :

UNION   运算符， EXCEPT   运算符   ， INTERSECT   运算符， 使用外连接

Sql 语句具体用法：

Sql常用语句写法

复制表 ， 拷贝表 ， 跨数据库之间表的拷贝 ， 子查询 ， 显示内容 ， 外连接查询 ， 四表联查 ， 在线视图查询

创建临时表

前 10条记录 ， 随机取出 10条数据 ， 选择从 10到 15的记录

找出 PPP表中所有重复的纪录 ， 找出 PPP表中最小的数，不能使用 min()或可以使用 min()

between ， in ，

删除主表中已经在副表中没有的信息，删除重复记录 ，

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

<返回 >