T-SQL 小全

====================================================== ----数据库概念：创建、删除、使用数据库 ----====================================================== /****** 数据库信息描述:书籍管理系统 ******/ Use master GO if Exists(select 1 from master..sysdatabases where name = ‘StuScore‘)--如果系统存在StuScore数据库，则删除该数据库 Drop Database StuScore GO Create Database StuScore--创建StuScore数据库 GO USE StuScore--使用StuScore数据库 GO --====================================================== ----数据表的创建、删除、修改 ----====================================================== /****** 表信息描述:书籍表 ******/ IF (OBJECT_ID(‘t_Books‘, ‘U‘) IS NOT NULL) DROP TABLE t_Books GO CREATE TABLE t_Books( K001 VARCHAR(200) NULL --种类 ,K002 VARCHAR(200) NOT NULL PRIMARY KEY --代码【主键】 ,K003 VARCHAR(200) NULL --名称 ,K004 VARCHAR(200) NULL --出版社 ,K005 VARCHAR(200) NULL --作者 ,K006 VARCHAR(200) NULL --书籍介绍 ) GO /****** 表信息描述:进销存表 ******/ IF (OBJECT_ID(‘t_BookSale‘, ‘U‘) IS NOT NULL) DROP TABLE t_BookSale GO CREATE TABLE t_BookSale( S001 VARCHAR(200) NULL --书籍代码 ,S002 VARCHAR(200) NULL --出入库标识 【进、销】 ,S003 DATETIME NULL --出入库时间 ,S004 DECIMAL(18,2) NULL --数量 ,S005 DECIMAL(18,2) NULL --单价 ) GO --====================================================== ----增删改查数据 ----====================================================== /****** 数据【新增】 ******/ INSERT INTO t_Books(K001,K002,K003,K004,K005,K006) VALUES (‘计算机类‘,‘A000001‘,‘C#‘,‘清华大学出版社‘,‘John Sharp‘,‘‘) INSERT INTO t_Books(K001,K002,K003,K004,K005,K006) VALUES (‘计算机类‘,‘A000002‘,‘SQL‘,‘清华大学出版社‘,‘Hong Ting‘,‘‘) INSERT INTO t_Books(K001,K002,K003,K004,K005,K006) VALUES (‘计算机类‘,‘A000003‘,‘Oracle‘,‘北京大学出版社‘,‘Hu BiYun‘,‘‘) INSERT INTO t_BookSale(S001,S002,S003,S004,S005) VALUES (‘A000001‘,‘进‘,‘2016-02-03 16:20:30‘,20,88.00) INSERT INTO t_BookSale(S001,S002,S003,S004,S005) VALUES (‘A000001‘,‘销‘,‘2016-03-12 09:28:10‘,12,99.00) INSERT INTO t_BookSale(S001,S002,S003,S004,S005) VALUES (‘A000001‘,‘进‘,‘2016-05-12 12:10:10‘,20,88.00) INSERT INTO t_BookSale(S001,S002,S003,S004,S005) VALUES (‘A000002‘,‘进‘,‘2016-05-12 12:10:10‘,6,36.00) /****** 数据【修改】 ******/ UPDATE t_Books SET K005=‘John Sharp 123‘ ,K006=‘我是修改操作之后的结果‘ WHERE K002=‘A000001‘ /****** 数据【删除】 ******/ DELETE FROM t_Books WHERE K002=‘A000003‘ /****** 数据【查询】 ******/ SELECT * FROM t_Books -- *代表查询所有列 SELECT K001,K002 书籍代码,K003 AS 书籍名称 --这里的AS后面也是别名，可写可不写 FROM t_Books WHERE 1=1 AND (K002=‘A000001‘ OR K002=‘A000002‘) -- WHERE 过滤行数据 AND表示要同时满足的条件 OR表示只要其中任一条件满足即可 ORDER BY K002 DESC -- ORDER BY 对结果集按照一个列或者多个列进行排序 ASC表示升序 DESC表示降序 默认不写是升序 SELECT t1.K003,t2.* --这里写上t1.是因为查询的时候如果有多张表，这些表里的字段可能会重名，为防止不明确，最好写上所属表 FROM t_Books t1 INNER JOIN t_BookSale t2 ON t2.S001=t1.K002 -- INNER JOIN 表示全匹配 ON 后面写的是两个表的关联字段 WHERE 1=1 AND K002 IN(‘A000001‘,‘A000002‘) -- IN 操作符允许您在 WHERE 子句中规定多个值 AND K003 LIKE ‘%#%‘ -- LIKE 操作符表示模糊匹配其中%代表任意字符 这句条件表示K003包含#字符即可 ORDER BY K002 DESC --====================================================== ----视图的创建、删除、使用 /* 作用： 1、视图能够简化用户的操作 2、视图使用户能以多钟角度看待同一数据 3、视图对重构数据库提供了一定程度的逻辑独立性 4、视图能够对机密数据提供安全保护 5、适当的利用视图可以更清晰的表达查询 */ ----====================================================== /****** 视图信息描述:书籍表 ******/ IF (OBJECT_ID(‘v_Books‘, ‘V‘) IS NOT NULL) --视图简称是view 所以在命名的时候最好是v_开头 DROP VIEW v_Books GO CREATE VIEW v_Books AS SELECT 1 AS 列1 --这里可以写你想写的任何东西,只要能返回查询数据就可以,注意返回的结果集，列名不要空，否则报错 GO SELECT * FROM v_Books --视图的本质就是表，但是它只能查询，不能操作其他 --====================================================== ----存储过程的创建、删除、使用 /* 什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块 ，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查） ，然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。 存储过程的好处： 1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。 2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。 3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。 */ ----====================================================== /****** 存储过程信息描述:书籍表 ******/ IF (OBJECT_ID(‘p_Books‘, ‘P‘) IS NOT NULL) --视图简称是procture 所以在命名的时候最好是p_开头 DROP PROCEDURE p_Books --这里PROCEDURE也可以简写为PROC GO CREATE PROC p_Books @P1 VARCHAR(200) --这里是参数 AS Begin SELECT * FROM t_Books WHERE K002=@P1 --这里可以写你想写的任何东西,可以返回东西，也可以不返回东西 END GO EXEC p_Books ‘A000001‘ --这里是调用存储过程 其中 ‘A000001‘是传进去的参数 --DECLARE可以声明变量，后面的语句效果跟上面的是一样的 DECLARE @PPP VARCHAR(200) SET @PPP=‘A000001‘ EXEC p_Books @PPP --====================================================== ----函数的创建、删除、使用 /* 类似C#的方法，将常用的代码块整合到一起 */ ----====================================================== /****** 函数信息描述:书籍表 ******/ IF (OBJECT_ID(‘f_Books‘, ‘F‘) IS NOT NULL) --视图简称是funcation 所以在命名的时候最好是f_开头 DROP FUNCTION f_Books GO CREATE FUNCTION f_Books(@P1 VARCHAR(200))--这里是参数 Returns VARCHAR(200)--这里是返回值类型 AS Begin Declare @result VARCHAR(200) Set @result=‘我是函数-‘ Return @result+@P1 End GO SELECT dbo.f_Books(K003),* FROM t_Books --调用的时候需要加上dbo，函数后面加上括号，里面写参数值，这个跟C#里面的方法是一样的 --====================================================== ----触发器的创建、删除、使用 /* a.允许/限制对表的修改 b.自动派生列，如自增字段 c.强制数据的一致性 d.提供审计和日志记录 e.防止无效的事务处理 f.启动复杂的业务逻辑 */ ----====================================================== /****** 触发器信息描述:书籍表 ******/ IF (OBJECT_ID(‘tr_Books_Insert‘, ‘TR‘) IS NOT NULL) --触发器简称是trigger 所以在命名的时候最好是tr_开头 DROP TRIGGER tr_Books_Insert GO CREATE TRIGGER tr_Books_Insert ON t_Books --触发对象,针对哪张数据表进行操作 FOR INSERT --触发动作,INSERT 或者 UPDATE 或者 DELETE 也就是插入或者更新或者删除 AS Begin DECLARE @K002 VARCHAR(200) SELECT @K002=K002 FROM INSERTED --这里的INSERTED是张系统自带的临时表，就是存储你刚刚插入的数据，还有一张DELETED临时表，就是删除的表。UPDATE的动作，你可以认为是先删除后插入 UPDATE t_Books SET K006=‘我是触发结果‘ WHERE K002=@K002 End GO INSERT INTO t_Books(K001,K002,K003,K004,K005,K006) VALUES (‘文学‘,‘B000001‘,‘莎翁选集‘,‘北京大学出版社‘,‘周国平‘,‘‘) SELECT * FROM t_Books

 

T-SQL 小全

标签：