SQLServer系列(三)：基本操作

一.创建数据库

USE MASTER;

GO

IF EXISTS(SELECT *
          FROM   sys.databases
          WHERE  [name] = 'student')
  BEGIN
      ALTER DATABASE student

      SET SINGLE_USER WITH

      ROLLBACK IMMEDIATE

      IF Db_id('student') IS NOT NULL
        DROP DATABASE student
  END
ELSE
  BEGIN
      CREATE DATABASE student ON PRIMARY ( NAME=N'student', FILENAME=N'D:ApplicationDataSQLSERVERstudent.mdf', SIZE=5MB, MAXSIZE=UNLIMITED, FILEGROWTH=15% ) LOG ON ( NAME=N'student_log', FILENAME=N'D:ApplicationDataSQLSERVERstudent_log.mdf', SIZE=2MB, MAXSIZE=20MB, FILEGROWTH=1MB ) WITH CATALOG_COLLATION = DATABASE_DEFAULT
  END

GO 

二.创建表

USE student;
GO
IF EXISTS(SELECT *
          FROM   sys.objects
          WHERE  [name] = 'studentInfo')
  BEGIN
      DROP TABLE studentInfo;
  END
GO
CREATE TABLE studentInfo
  (
     stuId      INT PRIMARY KEY IDENTITY(1, 1),
     stuName    NVARCHAR(20) NOT NULL,
     stuPhone   NVARCHAR(15) UNIQUE,
     stuSex     NVARCHAR(2) CHECK(stuSex='男' OR stuSex='女'),
     stuAddress NVARCHAR(50) DEFAULT('China')

  ) 
GO

三.创建存储过程

IF EXISTS(SELECT * FROM sys.procedures WHERE [name]='procedure_name') 
BEGIN
DROP PROCEDURE [procedure_name]
END
GO
CREATE PROCEDURE [procedure_name]
AS
BEGIN
  INSERT studentInfo(stuName,stuPhone,stuSex) VALUES('Robin',110,'男');
  UPDATE studentInfo SET stuPhone='123456' WHERE stuName='Robin';
END
GO

四.创建视图

USE student;
GO
CREATE VIEW VIEW_NAME
AS
SELECT * FROM studentInfo;
GO

五.创建索引

USE student

GO

IF EXISTS(SELECT *
          FROM   sys.indexes
          WHERE  [name] = 'INDEX_NAME')
  DROP INDEX INDEX_NAME ON studentInfo

CREATE NONCLUSTERED INDEX INDEX_NAME
  ON studentInfo(stuName)
  WITH FILLFACTOR=30

GO 

六.创建触发器

IF EXISTS(SELECT *
          FROM   sys.triggers
          WHERE  name = 'TRIGGER_NAME')
  DROP TRIGGER TRIGGER_NAME

GO

CREATE TRIGGER TRIGGER_NAME
ON studentInfo
FOR DELETE
AS
  BEGIN
      PRINT '删除'
  END 

七.创建标量函数

USE student;

GO

IF EXISTS(SELECT *
          FROM   sys.objects
          WHERE  type = 'FN'
                 AND [name] = 'FUNCTION_NAME')
  DROP FUNCTION dbo.FUNCTION_NAME

GO

CREATE FUNCTION dbo.Function_name (@stuId INT)
RETURNS INT
AS
  BEGIN
      DECLARE @COUNT INT

      SELECT @COUNT = Count(*)
      FROM   studentInfo

      RETURN @COUNT
  END

GO 

八.创建表值函数

USE student;

GO

IF EXISTS(SELECT *
          FROM   sys.objects
          WHERE  type = 'IF'
                 AND [name] = 'FUNCTION_NAME')
  DROP FUNCTION dbo.FUNCTION_NAME

GO

CREATE FUNCTION dbo.Fnuction_name (@stuId INT)
RETURNS TABLE
AS
    RETURN
      (SELECT *
       FROM   studentInfo)

GO 

九.声明游标

DECLARE @STUID INT
DECLARE STU_CURSOR CURSOR FOR
  SELECT stuId
  FROM   studentInfo

OPEN STU_CURSOR

FETCH NEXT FROM STU_CURSOR INTO @STUID

WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE studentInfo
      SET    stuPhone = '123456'
      WHERE  stuId = @STUID

      PRINT @STUID

      FETCH NEXT FROM STU_CURSOR INTO @STUID
  END

CLOSE STU_CURID

DEALLOCATE STU_CURSOR

GO