SQL SERVER常用SQL整理

一、数据库

1、创建数据库

use master
　　if exists(select * from sysdatabases where name = 'OrderDB')
　　    drop database OrderDB
　　create database OrderDB
　　on
　　(
　　    name='OrderDB_data',
　　    filename = 'D:\DB\OrderDB_data.mdf',
　　    size=10,
　　    filegrowth=15%
　　)
　　log on
　　(
　　    name='OrderDB_log',
　　    filename='D:\DB\OrderDB_log.ldf',
　　    size=3,
　　    filegrowth=10%
　　)

2、删除数据库

drop database OrderDB

二、表

1、建表

--用户表　　　　if exists (select * from sysobjects where name = 'Tse_User')
　　    drop table Tse_User
　　Create table Tse_User
　　(
　　    ID int identity(1,1),
　　    UserID int not null,
　　    UserName varchar(64) not null,
　　    RealName varchar(64) null,
　　    PRIMARY KEY (UserID)
　　)

--产品表
　　if exists (select * from sysobjects where name = 'Tse_Product')
　　    drop table Tse_Product
　　Create table Tse_Product
　　(
　　    ID INT IDENTITY(1,1),
　　    ProductID varchar(64) not null,
　　    ProductName varchar(256) not null,
　　    Price float not null,
　　    Storage int not null,      --库存
　　    PRIMARY KEY(ProductID)
　　)

--订单表
　　if exists (select * from sysobjects where name = 'Tse_Order')
　　    drop table Tse_Order
　　Create table Tse_Order
　　(
　　    ID int identity(1,1),
　　    OrderID varchar(64) not null,
　　    UserID int not null,
　　    ProductID varchar(64) not null,
　　    Number int not null,             --购买数量
　　    PostTime datetime not null,
　　    PRIMARY KEY(OrderID),
　　    FOREIGN KEY (UserID) REFERENCES Tse_User(UserID),
　　    FOREIGN KEY (ProductID) REFERENCES Tse_Product(ProductID)
　　)

2、删表

　　drop table Tse_User

3、清空表

truncate table Tse_User    清除表中所有数据，下次插入编号从1开始

delete from Tse_User      清除表中所有数据，但下次插入编号从原有编号+1开始

4、生成临时表

插入数据，将员工姓名全部打印出来
use master
　　go
　　create table #Employee
　　(
　　    ID int identity(1,1),
　　    Name varchar(64) not null,
　　    primary key (ID)
　　)
　　insert into #Employee(Name) values('zhangsan')
　　insert into #Employee(Name) values('lisi')
　　insert into #Employee(Name) values('wangwu')
　　insert into #Employee(Name) values('tony')
　　insert into #Employee(Name) values('mike')
　　
　　declare @i int 
　　declare @Name varchar(64)
　　declare @Count int
　　declare @Str nvarchar(4000)
　　set @i = 0
　　select @Count = COUNT(0) from #Employee
　　while(@i < @Count)
　　begin
　　    set @Str = 'select top 1 @Name = Name from #Employee where id not in (select top '+
　　    STR(@i) +'id from #Employee)'
　　    exec sp_executesql @Str ,N'@Name varchar(64) output', @Name output
　　    select @Name, @i
　　    set @i = @i + 1
　　End

5、查看表结构及表附加属性

SP_HELP Tse_User

三、增删改查

1、插入

--插入用户表数据

insert into Tse_User(UserID, UserName, RealName, Email, Mobile)

values(111, 'zhangsan', 'zhangsan', 'zs@126.com', '')

--插入产品表数据

INSERT INTO Tse_Product(ProductID, ProductName, Price, Storage)

VALUES('PD00030', 'Benz', 500500.0, 30000)

　

--插入订单表数据

declare @OrderID VARCHAR(64)

--将年，月，日，时，分，秒，毫秒以字符串形式连接起来作为订单号

　　SET @OrderID = DATENAME(YEAR, GETDATE()) + DATENAME(MONTH, GETDATE()) +DATENAME(DAY, GETDATE())+

　　DATENAME(HOUR, GETDATE()) + DATENAME(MINUTE, GETDATE())+DATENAME(SECOND, GETDATE()) +DATENAME(MILLISECOND, GETDATE())

　　INSERT INTO Tse_Order(OrderID,  UserID,  ProductID,  Number,  PostTime)

　　VALUES(@OrderID,  115,  'PD00040',  10,  GETDATE())

2、修改

Update Tse_User set RealName = '李四' where UserID = 112

3、删除

Delete from Tse_User Where UserID = 111

4、简单查询

select * from Tse_User with(nolock)

select * from Tse_Order with(nolock) where ID >= 2

四、连接

1、内连接

--左右表匹配的行

SELECT * FROM Tse_Order AS O WITH(NOLOCK)

INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserID

WHERE U.UserID = 111

2、左连接（左外连接）

--左边表中所有行，右边匹配左边, 右边为空的补NULL

SELECT * FROM Tse_User AS U WITH(NOLOCK)

LEFT JOIN Tse_Order AS O WITH(NOLOCK) ON U.UserID = O.UserID

3、右连接（右外连接）

--右边表中所有行，左边匹配右边，左边为空的补NULL

SELECT * FROM Tse_Order AS O WITH(NOLOCK)

RIGHT JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

4、全连接

--左右表所有行，为空的补NULL

SELECT * FROM Tse_Order AS O WITH(NOLOCK)

FULL JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

5、分组和排序

按UserID分组

SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID 

按UserID分组，订单数量大于等于3

SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) >=3

按UserID分组，订单数量大于等于1，按订单数量升序

SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) >=1 ORDER BY Number ASC

6、通配符

LIKE：匹配多个未知字符

匹配一个未知字符

--匹配126邮箱的

SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@126.com'

--匹配所有包含@的邮箱

　　SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@%'

--匹配16开头，后面跟一个任意字符的邮箱

　　SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE '%@16_.com'

--匹配除126以外的所有邮箱

       SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email NOT LIKE '%@126.com'

五、视图

  1、删除视图　　

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = 'V_Tse_TotalInfo')

　　DROP VIEW V_Tse_TotalInfo

  2、创建视图

--包含用户表，产品表和订单表关联后的所有信息　

　CREATE VIEW V_Tse_TotalInfo

　　AS

　　SELECT O.OrderID, O.UserID, O.ProductID, O.PostTime, U.UserName, U.RealName,

　　U.Email, U.Mobile, P.ProductName, P.Price FROM Tse_Order AS O WITH(NOLOCK) 

　　INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserID

　　INNER JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID

六、存储过程和事务

创建存储过程，先删除订单表（外键表）中的记录，再删除产品表（主键表）中的记录
CREATE PROCEDURE [dbo].[SC_Tse_DeleteProduct]
　　(
　　    @ProductID VARCHAR(64),
　　    @Result int output
　　)
　　AS
　　BEGIN
　　    SET NOCOUNT ON;
　　
　　    BEGIN TRAN   --开始事务
　　    BEGIN
　　        DELETE FROM Tse_Order WHERE ProductID = @ProductID
　　        
　　        DELETE FROM Tse_Product WHERE ProductID = @ProductID
　　        
　　        IF (@@ERROR <> 0)
　　        BEGIN
　　            SET @Result = -999
　　            ROLLBACK TRAN   --回滚
　　        END
　　        ELSE
　　        BEGIN
　　            SET @Result = 888
　　            COMMIT TRAN     --提交
　　        END
　　    END
　　END

七、游标

获取所有产品的名字，以‘|’分隔，包含在输出参数@Names中

CREATE PROCEDURE SC_Tse_GetProductNames
    (
        @Names varchar(max) OUTPUT
    ) 
    AS
    BEGIN
        SET NOCOUNT ON;
        declare @ProductName varchar(64)
        declare curTest cursor
        for (select ProductName from Tse_Product)
        open curTest                --打开游标
        fetch next from curTest into @ProductName
        while @@fetch_status = 0   --获取成功
        begin
            if (@ProductName is not null and @ProductName <> '')
            begin
                if (@Names is null or @Names = '')
                begin
                    set @Names = @ProductName
                end
                else
                begin
                    set @Names = @Names + '|'+ @ProductName
                end
            end
            fetch next from curTest into @ProductName
        end
        
        close curTest            --关闭游标
        deallocate curTest        --释放游标
    END

八、触发器

因为用户编号在订单表中为外键，所以，直接删除某个用户时，如果该用户下了订单，就会提示有外键不能删除。针对这种情况，可以考虑使用触发器。

创建触发器，删除用户表中用户时，会自动先删除订单表中的订单
CREATE TRIGGER TR_Tse_DelUser
    ON Tse_User
    INSTEAD OF DELETE  --代替默认的删除
    AS
    BEGIN
        SET NOCOUNT ON
        DELETE FROM Tse_Order WHERE UserID IN (SELECT UserID FROM Deleted)
        DELETE FROM Tse_User WHERE UserID IN (SELECT UserID FROM Deleted)
　　END
使用触发器，添加订单时，产品表库存相应减少

CREATE TRIGGER TR_Tse_ADDOrder
    ON Tse_Order
    AFTER INSERT
    AS
    BEGIN
        UPDATE Tse_Product SET Storage = Storage - (SELECT Number FROM INSERTED)
        WHERE ProductID IN (SELECT ProductID FROM INSERTED)
　　END

九、作业

　在SQL SERVER数据库中建立作业的步骤大致如下：

　1.安装完SQL SERVER后，打开SQL SERVER Management Studio，找到SQL SERVER代理。

　2.在“作业”上点击鼠标右键，选择新建作业。

　3.在弹出的界面上，常规属性中填写名称。

　4.单击“步骤”属性，再点击新建。

　5.填写步骤名称， 类型， 选择数据库， 命令填写你要执行的存储过程或sql语句。

　6.选择高级属性，由于这里只有一步，所以选择“退出报告成功的作业”，也可以选择脚本输出到文件的路径。点击确定。

　7.下一步点击“计划”-“新建”。

　8.执行计划中输入名称，选择计划类型，执行频率（每天/每周/每月）,按你想要的选择。

　9.还可以在通知属性中，选择作业完成后发电子邮件等操作（如果远程的话）。

　10.最后点击确定，这个作业就新建好了。

　11.新建成功后，从作业中可以看到这个作业。

　12.另外，你还可以通过作业活动监视器监视作业的执行情况。