以下是我对sql 脚本的一些简单总结和复习，大都是参照博友的，然后自己重写了一遍
--新建数据库
USE AInfoLiang
GO
IF EXISTS(SELECT * FROM sys.databases WHERE NAME ='AInfoLiang')
BEGIN
DROP DATABASE AInfoLiang
--PRINT '1'
END
GO
CREATE DATABASE AInfoLiang
ON
(
NAME='AInfoLiang',
FILENAME='C:\AInfoLiang.mdf',
size=5,--数据库的初始大小
maxsize =100, -- 数据库最大是多少
filegrowth=10% --自动增长百分之多少
)
LOG ON
(
NAME ='AInfoLiang_log',
filename='C:\AInfoLiang_log.ldf',
size=5,--数据库的初始大小
maxsize=50,-- 数据库最大是多少
filegrowth=10% --自动增长百分之多少
)
--创建表 --------------------------------------------------
USE AInfoLiang
GO
IF EXISTS(SELECT * FROM sys.tables WHERE NAME='t1')
DROP TABLE t1
GO
CREATE TABLE t1
(
loginid INT IDENTITY(1,1) NOT NULL,
loginname VARCHAR (20),
loginpwd VARCHAR(30)
)
IF EXISTS(SELECT * FROM sys.tables WHERE NAME='t2')
DROP TABLE t2
GO
CREATE TABLE t2
(
userid INT IDENTITY (1,1) NOT NULL,
username VARCHAR(20),
userpwd VARCHAR(20)
)
--向表t1 添加数据
INSERT t1(loginname,loginpwd) VALUES ('11','11')
INSERT t1(loginname,loginpwd) VALUES ('22','22')
INSERT t1(loginname,loginpwd) VALUES ('33','33')
INSERT t1(loginname,loginpwd) VALUES ('44','44')
INSERT t1(loginname,loginpwd) VALUES ('55','55')
INSERT t1(loginname,loginpwd) VALUES ('66','66')
INSERT t1(loginname,loginpwd) VALUES ('77','77')
--向表t2 添加数据
INSERT t2(username,userpwd) VALUES ('11','11')
INSERT t2(username,userpwd) VALUES ('22','22')
INSERT t2(username,userpwd) VALUES ('33','33')
INSERT t2(username,userpwd) VALUES ('44','44')
INSERT t2(username,userpwd) VALUES ('55','55')
INSERT t2(username,userpwd) VALUES ('66','66')
INSERT t2(username,userpwd) VALUES ('77','77')
--放到一个临时表中
SELECT * INTO #t2 FROM t2 --临时表 临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内
ALTER TABLE t2 ADD userPer INT --添加一列
ALTER TABLE t2 DROP COLUMN userper --删除一列
SELECT * FROM t1
UNION ALL
SELECT * FROM t2 --集合两张表的集合
--触发器--------------------------------------------------
USE AInfoLiang
GO
CREATE TRIGGER tri_t1
ON t1 --作用在那张表上(不能是多表张)
FOR INSERT ---执行添加的操作时执行下面的sql语句
AS
SELECT * FROM t1
SELECT * FROM t2
INSERT INTO t1 VALUES('99','99') --当对表t1进行添加数据操作时，触发器则发触发
--视图----------------------------------------------------------
USE AInfoLiang
GO
CREATE VIEW vi_v1
AS
SELECT * FROM t1
SELECT * FROM vi_v1 -- 执行视图
DROP VIEW vi_v1 --删除视图
--存储过程------------------------------------------------------
USE AInfoLiang
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_1'))
DROP PROCEDURE proc_1
GO
CREATE PROCEDURE proc_1
AS
SELECT * FROM t1
GO
EXEC proc_1 --执行存储过程
DROP PROCEDURE proc_1 --删除存储过程
--带参的存储过程
USE AInfoLiang
GO
CREATE PROCEDURE proc_2(@start int,@end int)
AS
SELECT * FROM t1 WHERE loginid BETWEEN @start AND @end
EXEC proc_2
@start = 1, -- int
@end = 10 -- int
--带通配符的存储过程
USE AInfoLiang
GO
ALTER PROCEDURE proc_3
(
@name VARCHAR(20)='%j%'
)
AS
SELECT * FROM t1 WHERE loginname LIKE @name
EXEC proc_3
@name = '%1%' -- VARCHAR(20) --执行带通配符的存储过程
--带输出参数存储过程---------------------------------------------
USE AInfoLiang
GO
CREATE PROCEDURE proc_4
(
@loginid INT,
@loginname VARCHAR(20) OUT,
@loginpwd VARCHAR(20) OUTPUT
)
AS
SELECT * FROM t1 WHERE loginid=@loginid AND loginname=@loginname AND loginpwd=@loginpwd
DECLARE @loginid=1 INT,@loginname VARCHAR(30),@loginpwd VARCHAR(20)
SELECT @loginid=1;
set @loginname = '11';
SELECT @loginpwd = '11';
EXEC proc_4 @loginid,@loginname OUT ,@loginpwd OUTPUT
SELECT @loginname AS dd,@loginpwd AS ddd
--分页------------------------------------------------------
USE AInfoLiang
GO
CREATE PROCEDURE proc_5
(
@start INT ,
@end INT
)
AS
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY loginid ) AS rowid,* FROM t1
) AS temp
WHERE temp.rowid BETWEEN @start AND @end
EXEC proc_5
@start = 2, -- INT
@end = -- INT
--分页2----------------------------------------
USE AInfoLiang
GO
CREATE PROCEDURE proc_fen2
(
@pageindex INT,--起始页
@pagesize INT -- 一页多少个
)
AS
DECLARE @startrow INT ,@endrow INT
SET @startrow=(@pageindex-1)* @pagesize +1 --得到 从那一行的数据开始
SET @endrow =@startrow+@pagesize-1 --得到 结束的行那一条数据
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY loginid) rowid,* FROM t1
) temp
WHERE temp.rowid BETWEEN @startrow AND @endrow
EXEC proc_fen2
@pageindex = 5, -- INT
@pagesize = 2 -- INT
---自定义函数
--定义函数分为二种，一种是标量值函数，另一种是表格值函数
--表格函数又可分为内联表格值函数和多句表格值函数
--1 标量函数
USE AInfoLiang
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE TYPE='fn' AND NAME='fun_1'))
BEGIN
DROP FUNCTION fun_1
END
GO
CREATE FUNCTION fun_1
(
@loginid INT
)
RETURNS VARCHAR(40) --返回值类型
AS
BEGIN
DECLARE @loginname VARCHAR (40)
SELECT @loginname=loginname FROM t1 WHERE loginid=@loginid
RETURN @loginname
END
SELECT dbo.fun_1(1) --执行函数
--表格值（内联函数）----------------------------------------------
USE AInfoLiang
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE TYPE='fn' AND NAME='fun_3'))
BEGIN
DROP FUNCTION fun_3
END
GO
CREATE FUNCTION fun_3
(
@loginid INT
)
RETURNS TABLE
AS
RETURN (SELECT * FROM t1 WHERE loginid=@loginid)
SELECT dbo.fun_2(1)
--表格值（多语句函数）--------------------------------------------------
USE AInfoLiang
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE NAME ='fun_4'))
DROP FUNCTION fun_4
GO
CREATE FUNCTION fun_4
(
@loginid INT
)
RETURNS @t1 TABLE(a VARCHAR(20),b VARCHAR (20))
AS
BEGIN
INSERT @t1
SELECT loginname ,loginpwd FROM dbo.t1 WHERE loginid=@loginid
RETURN
END
SELECT dbo.fun_4(1)