一.创建数据库
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