T-Sql小结_02 关键字_01

SQL最重要的莫过于增删改查了，这次我们就来看下这几个的关键字

增-Create, Insert

首先创建肯定是增加吧，创建的关键字就是Create，不管是创建数据库（DB），数据表（Table），视图（View），存储过程（Procedure），函数（Function）等等，都得用到这个Create。给表里插入数据就要用到Insert了，下面让我们来实践下。　　

 1 Create Database Study_DB
 2 on  primary                  -- 默认就属于primary文件组,可省略
 3 (
 4 /*--数据文件的具体描述--*/
 5     name='Study_DB_data',  -- 主数据文件的逻辑名称
 6     filename='D:\Study_DB_data.mdf', -- 主数据文件的物理名称
 7     size=5mb,               --主数据文件的初始大小
 8     maxsize=100mb,    -- 主数据文件增长的最大值
 9     filegrowth=15%      --主数据文件的增长率
10 )
11 log on
12 (
13 /*--日志文件的具体描述,各参数含义同上--*/
14     name='Study_DB_log',
15     filename='D:\Study_DB _log.ldf',
16     size=2mb,
17     filegrowth=1mb
18 )

Create Study_DB

 1 USE [Study_DB]
 2 GO
 3 
 4 /****** Object:  Table [dbo].[t_Grade]    Script Date: 2017/5/9 5:44:01 下午 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE TABLE [dbo].[t_Grade](
12     [SdId] [uniqueidentifier] NOT NULL,
13     [SdName] [nchar](20) NULL,
14     [ClassId] [nchar](10) NULL,
15     [KmId] [nchar](10) NULL,
16     [Grade] [int] NULL,
17  CONSTRAINT [PK_t_Grade] PRIMARY KEY CLUSTERED 
18 (
19     [SdId] ASC
20 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21 ) ON [PRIMARY]
22 
23 GO

Create Table

 1 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小明','301','001',100);
 2 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小明','301','002',79);
 3 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小明','301','003',68);
 4 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刘','301','001',123);
 5 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刘','301','002',135);
 6 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刘','301','003',142);
 7 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刚','301','001',149);
 8 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刚','301','002',150);
 9 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小刚','301','003',150);
10 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小英','301','001',140);
11 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小英','301','002',31);
12 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小英','301','003',99);
13 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小爱','302','001',111);
14 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小爱','302','002',67);
15 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小爱','302','003',13);
16 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小胡','302','001',45);
17 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小胡','302','002',98);
18 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小胡','302','003',150);
19 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小玲','302','001',127);
20 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小玲','302','002',140);
21 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小玲','302','003',143);
22 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小松','302','001',143);
23 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小松','302','002',150);
24 Insert Into [dbo].[t_Grade](SdId,SdName,ClassId,KmId,Grade) Values(NEWID(),N'小松','302','003',136);

Insert Into

建好后差不多是这个样子：

上面的数据不太方便查看，那么我们可以定义一个视图，组合成我们想要看的样子。

 1 Create View [V_Grade] As
 2 Select sdName, 
 3 sum(case when KmId='001' then Grade end) as Chinese,
 4 sum(case when KmId='002' then Grade end) as Math,
 5 sum(case when KmId='003' then Grade end) as English,
 6 (sum(case when KmId='001' then Grade end) +
 7 sum(case when KmId='002' then Grade end) +
 8 sum(case when KmId='003' then Grade end) )as Total
 9 from t_grade
10 GROUP BY SdName

Create View

然后再通过视图查看如下：

这样就比较清晰一点了。

细心的朋友可能会发现，上面插入的语句中，会发现学生编号SdId这个字段，同一个人的这个字段是不一样的，这很明显是不对的。

那么如果我们要重新去删除表，插入数据的话，会比较麻烦。或者说你换了系统，需要快速的弄出一套这种用作Demo的表的时候，你可能希望有没有一个方法可以执行一下就可以实现一些稍微复杂的SQL操作的。答案是有的，那就是存储过程。

这里引用一下百度百科里面对存储过程的定义如下：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

下面我们就按照我们的需要来建一个存储过程，它主要要完成以下几点功能

　　1. 如果原来存在t_Grade表的话删除该表，并创建一个新的t_Grade表，字段包括

　　　[GradeId] -- 成绩Id
　　　[SdId] -- 学生Id
　　　[SdName] --学生姓名
　　　[ClassId] -- 班级Id
　　　[KmId] --科目Id
　　 [Grade] --分数

　　2. 分别插入三个学生的三个科目的分数，当然学生Id不能一样的，分数就取（1~150）范围内的随机数吧

语句如下:

USE [Study_DB]
GO

/****** Object:  StoredProcedure [dbo].[Proc_ReCreate_Grade]    Script Date: 2017/5/15 2:47:43 下午 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_ReCreate_Grade]
AS
BEGIN
    IF OBJECT_ID (N't_Grade', N'U') IS NOT NULL        
    Begin
        DROP TABLE t_Grade;
    End
        

    CREATE TABLE [dbo].[t_Grade](
    [GradeId] [int] IDENTITY(1,1) NOT NULL,
    [SdId] [uniqueidentifier] NOT NULL,
    [SdName] [nchar](20) NULL,
    [ClassId] [nchar](10) NULL,
    [KmId] [nchar](10) NULL,
    [Grade] [int] NULL,
    CONSTRAINT [PK_t_Grade] PRIMARY KEY CLUSTERED 
    (
        [GradeId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    DECLARE @IDNo uniqueidentifier;
    DECLARE @StuName nvarchar(10);
    DECLARE @ClassId int;
    DECLARE @ClassStr nvarchar(10);
    DECLARE @KeMuId int;
    DECLARE @KeMuStr nvarchar(10);
    
    
    set @ClassId = 1;

    While @ClassId <= 3
    Begin
        Set @ClassStr = '30' + CAST(@ClassId as nvarchar);
        set @KeMuId = 1;
        if(@ClassId = 1)
        begin
            set @IDNo = NEWID();
            set @StuName = N'小明';
        end
        else if(@ClassId = 2)
        begin
            set @IDNo = NEWID();
            set @StuName = N'小刘';
        end
        else if(@ClassId = 3)
        begin
            set @IDNo = NEWID();
            set @StuName = N'小亮';
        end
        while @KeMuId <= 3
        Begin
            Set @KeMuStr = '00' + CAST(@KeMuId as nvarchar);            
            Insert into t_Grade(sdid,SdName,ClassId,KmId,Grade) values(@IDNo,@StuName,@ClassStr,@KeMuStr,ceiling(rand()*150));
            Set @KeMuId = @KeMuId + 1;
        END
        Set @ClassId = @ClassId+1
    END
END


GO

Create Procedure

执行之后，存储过程就好了，你可以通过exec [dbo].[Proc_ReCreate_Grade]来执行这个存储过程，你可以通过它来很轻松的删除掉原来的表，然后再重新给这个表插入几条演示数据。

是不是很方便呢？

然后你可以看到，我们上面建的VIEW也会根据你的数据变化儿变化，这就是View的方便之处，不需要你重新去删除再重建的。

posted @ 2017-05-09 18:18 一朵粑粑花阅读(252) 评论(0) 收藏举报

刷新页面返回顶部

一朵粑粑花

T-Sql小结_02 关键字_01

增-Create, Insert

公告