常用SQL Server 语句

　　　　　　　　    常用SQL语句大全　　　

1、//创建数据库

CREATE DATABASE DBName

2、//删除数据库

DROP DATABASE DBName

3、//备份SQL SERVER

--- 创建 备份数据的 device 　　　　
USE master 　　　　
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'


--- 开始 备份　　　　
BACKUP DATABASE pubs TO testBack 

4、//创建新表

CREATE TABLE TabName 　　
(     　　
ID INT NOT NULL  PRIMARY KEY IDENTITY(1,1),     　　
NAME VARCHAR(50) NOT NULL, 　　
)

--根据已有的表创建新表：
(1)　CREATE TABLE tab_new LIKE tab_old (使用旧表创建新表)
(2)　CREATE TABLE tab_new AS SELECT col1,col2… FROM tab_old DEFINITION only

5、//删除新表

DROP TABLE TabName

6、//增加一个列

ALTER TABLE TabName ADD col BIT NOT NULL DEFAULT 1

--删除一个列

ALTER TABLE TabName DROP CONSTRAINT 默认约束名 　　GO 　
ALTER TABLE TabName DROP COLUMN   colName 　　GO

注： 因为删除列的时候 有默认约束依赖该字段，那么你需要先删除默认约束（错误提示里会有默认约束名），再删除字段

7、//添加主键：

ALTER TABLE TabName ADD  PRIMARY KEY(col)

--删除主键：
ALTER TABLE TabName DROP  PRIMARY KEY(col)

8、//创建索引：

CREATE INDEX  IndexName ON TabName(col...)

--删除索引：
DROP  INDEX IndexName

注：索引是不可更改的，想更改必须删除重新建。

9、//创建视图：

CREATE VIEW ViewName
     AS
    　　SELECT col1,col2
    　　FROM TabName
　　GO

--删除视图：
DROP VIEW ViewName

 

10、//创建存储过程

CREATE PROC  Proc_Name 　　
AS 　　
     SELECT * 　　
     FROM TabName　　 　　
GO

--调用、执行存储过程 　　
EXEC  Proc_Name

--修改存储过程　　
ALTER PROC  Proc_Name 　　
AS 　　
    SELECT * 　　
    FROM TabName　
GO

--删除存储过程
DROP PROC Proc_Name     

 
11、//多连接表

--内连接

　　　SELECT * 
　　　FROM  Tab1 AS t1   INNER JOIN  Tab2 AS  t2 ON  t1.ID = t2.ID

--左连接

　　　SELECT * 
　　　FROM  Tab1 AS t1   LEFT JOIN  Tab2 AS  t2 ON  t1.ID = t2.ID

--右连接

　　　SELECT * 
　　　FROM  Tab1 AS t1   RIGHT JOIN  Tab2 AS t2 ON t1.ID = t2.ID

12、//复制表(只复制结构,源表名：a 新表名：b)

SELECT * INTO b FROM a WHERE <>1

13、//拷贝表(拷贝数据,源表名：a 目标表名：b)

INSERT INTOb(号,姓名,龄)
SELECT 号,姓名,龄
FROM a

14、//跨数据库之间表的拷贝(具体数据使用绝对路径)

INSERT INTOb(a, b, c)
SELECT d,e,f
FROM b  IN ‘具体数据库’
WHERE  条件

15、//子查询(表名1：a 表名2：b)

SELECT a,b,c
FROM a
WHERE a IN (
　　　　　　　SELECT d
　　　　　　　FROM b ) 

16、//显示文章、提交人和最后回复时间

SELECT a.title,a.username,b.adddate
FROM table a,(
　　　　　　SELECT MAX(adddate) adddate
　　　　　　FROM table
　　　　　　where table.title=a.title ) b

 

17、//在线视图查询(表名1：a )

SELECT *
FROM  (
　　　　　　SELECT a,b,c
　　　　　　FROM a
            ) T
WHERE  t.a > 1; 

18、//between的用法,between限制查询数据范围时包括了边界值,not between不包括

SELECT *
FROM table1
WHERE time BETWEEN time1 AND time2

 

19、//in 的使用方法

SELECT *
FROM table1
WHERE a NOT IN (‘值1’,’值2’,’值4’,’值6’)

20、//两张关联表，删除主表中已经在副表中没有的信息

DELETE FROM  table1
WHERE not exists (
　　　　SELECT *
　　　　FROM  table2
　　　　WHERE  table1.field1=table2.field1 )

21、//日程安排提前五分钟提醒

SSELECT *
FROM 日程安排
WHERE　DATEDIFF('minute',f开始时间,GETDATE())>5

22、//一条sql 语句搞定数据库分页

　　//思想：　　　　

SELECT TOP 10 b.* FROM (SELECT TOP 20 主键字段,排序字段 FROM 表名 ORDER BY  排序字段 DESC ) a,表名 b WHERE b.主键字段 = a.主键字段 ORDER BY  a.排序字段

　　//实现：

DECLARE @start INT,@end INT
    　　 @sql  NVARCHAR(600)

SET @sql=’SELECT TOP’+str(@end-@start+1)+’+FROM T WHERE rid NOT IN(SELECT TOP’+str(@str-1)+’Rid FROM T WHERE Rid>-1)’

EXEC sp_executesql @sql

注：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）

 

23、//前10条记录

SELECT TOP *
FROM TABLE
WHERE 范围

 

24、//选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

SELECT a,b,c 
FROM tablename ta 
WHERE a=(
    SELECT MAX(a) 
    FROM tablename tb 
    WHERE tb.b=ta.b)

 

25、//包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(SELECT a FROM tableA ) EXCEPT 
(SELECT a FROM tableB ) EXCEPT 
(SELECT a FROM tableC ) 

 

26、//随机取出10条数据

SELECT TOP 10 *
FROM TABLENAME
ORDER BY  NEWID()

 

27、//删除重复记录

DELETE FROM TABLENAME
WHERE id NOT IN (
        SELECT MAX(id)
        FROM TABLENAME
        GROUP BY col1，col2，。。。)        

 

28、//列出数据库里所有的表名

SELECT name
FROM SYSOBJECTS
WHERE type=‘U’      -- U代表用户

 

29、//列出表里的所有的列名

SELECT name
FROM SYSCOLUMS
WHERE id = object_id('TABLENAME')

 

30、//简单CASE函数和CASE搜索函数

--简单Case函数
CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END

--Case搜索函数
CASE 　　
  　WHEN sex = '1' THEN '男'
　　WHEN sex = '2' THEN '女'
ELSE '其他' END

31、//初始化表table1

TRUNCATE TABLE table1

32、//添加假数据

-- 需要创建一张表 两个字段 一个用户名 一个密码
    DECLARE @username VARCHAR(50) --用户名
    DECLARE @password VARCHAR(50) --密码
    DECLARE @score int --数量
  
    SET @score = 0;
     -- 循环一百次
    WHILE(@score<100)
    BEGIN
        SET @username = 'test'+ CAST(@Score AS VARCHAR(50) )
        SET @password = '123456'+ CAST(@Score AS VARCAHR(50) )
   
        INSERT INTO TableName
        VALUES(@username,@password)
        SET @score =@score+1;
    END

33、//几个简单的基本的sql语句

--选择
SELECT * FROM table1 WHERE 范围
--插入
INSERT INTO table1(field1,field2) VALUSE(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(1) 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