sql server 创建ldf sql server怎么用sql语句创建库

1、第一节

1.1什么是数据库

用来存储数据的仓库 ，就像存放文件的保险柜。

1.2如何创建数据库

拿SQL server举例

两种方式：

1) 右键数据库-》创建数据库

2）SQL 语句创建

新建查询链接-》输入以下语句-》选中执行

--CREATE DATABASE [数据库名]
--例：创建学校数据库
CREATE DATABASE SCHOOL;

1.3如何创建表

1）选择数据库-》右键创建表

设置主键-》右键设计-》右键所选对象（例如：id）-》设置主键

2）语句创建，新建查询-》输入语句

CREATE TABLE [表名]
(
	[字段] int PRIMARY KEY,--设置主键
	[字段] varchar(20) NOT NULL,--不为空
	[字段] int ,
	[字段] int DEFAULT [默认值],--设置默认值
);

CREATE TABLE Student
(
	id int PRIMARY KEY,
	name varchar(20) NOT NULL,
	age int ,
	sex int DEFAULT 1,
	phone int
);

2、第二节(查询)

2.1检索（SELECT）

查找单列数据

SELECT name 
FROM Student;

查找多列数据

SELECT id,name,sex 
FROM Student;

查找全部数据

SELECT * 
FROM Student

2.2检索数据排序

默认排序是根据数据插入顺序输出

排序 ORDER BY(默认升序)

ASC升序

DESC降序

单个列排序

--根据id升序排序
SELECT name 
FROM Student
ORDER BY id;

多个列排序

根据需求进行排序

SELECT 	id,name,age
FROM Student
ORDER BY name,age;

按列位置进行排序

根据第二列第三列排序（输出结果同上）

SELECT 	id,name,age
FROM Student
ORDER BY 2,3;

指定方向排序

--根据id降序排列
SELECT name 
FROM Student
ORDER BY id DESC;

--根据id升序排列
SELECT name 
FROM Student
ORDER BY id ASC;

2.3过滤查询

使用WHERE语句查询

--查找姓名为张三的信息
SELECT name,age,sex
FROM Student
WHERE name = '张三';

操作符

检查单个值

--查找年龄大于等于18的信息
SELECT name,age,sex
FROM Student
WHERE age>=18;

不匹配查询

--查找性别不是男(1)的信息【查找所有女生(0)】
SELECT name,age,sex
FROM Student
WHERE sex!=1;

范围值查询

--查找年龄在6-18的信息
SELECT name,age,sex
FROM Student
WHERE age BETWEEN 6 AND 18;

空值查询

--查找手机号为空的信息
SELECT name,age,sex
FROM Student
WHERE phone IS NULL;

2.4高级过滤查询

1、组合查询

AND

--查找年龄大于十八的男生
SELECT name,age,sex
FROM Student
WHERE age >18 AND sex = 1;

OR

--查找年龄大于十八或者是男生的信息
SELECT name,age,sex
FROM Student
WHERE age >18 OR sex = 1;

计算次序

AND 、OR进行组合

--年龄大于18的id大于等于1或者是男生的信息
SELECT name,age,sex
FROM Student
WHERE (id>=1 OR sex = 1)
AND age >18

2、IN操作符

用来指定范围

--查找年龄在6-18的信息并根据age升序排序
SELECT name,age,sex
FROM Student
WHERE age IN (6,18)
ORDER BY age;

3、NOT操作符

--查找女生并按年龄升序排序
SELECT name,age,sex
FROM Student
WHERE NOT sex=1
ORDER BY age;

2.5通配符进行过滤查询

1、LIKE操作符

通配符：用来匹配值的一部分的特殊字符

搜索模式：有字面值、通配符或两者进行组合构成的搜索条件

1.1百分号(%)通配符

%表示任意字符出现的任意次数

--查找姓氏为李的所有信息
SELECT *
FROM Student
WHERE name LIKE '李%';

1.2下划线(_)通配符

_ 通配符匹配任意单个字符

--查找姓氏为李姓名为两位的所有信息
SELECT *
FROM Student
WHERE name LIKE '李_';

1.3方括号([ ])通配符

[]通配符用来指定一个字符集，必须匹配指定的位置

--查找姓氏为李和王的所有信息
SELECT *
FROM Student
WHERE name LIKE '[李王]%';

^ 脱字节

用来否定条件

--查找姓氏不为李和王的所有信息
SELECT *
FROM Student
WHERE name LIKE '[^李王]%';

3、第三节(计算字段)

3.1计算字段

例如查找公司名称、同时显示公司名称和公司地址，但是这两个信息存放在两列中，我们需要的字段表中并没有直接显示，因此我们需要直接从数据库中检索出转换、计算或格式化过的数据，这样的数据被称为计算字段

3.2拼接字段

将两个值进行拼接成单个值

--查找姓名年龄 并将其拼接成一列
SELECT name + '('+age+')'
FROM Student

格式化数据时可使用函数(例：去除串尾空格，使用函数 RTRIM() )

--查找姓名年龄 并将其拼接成一列
SELECT RTRIM(name) + '('+RTRIM(age)+')'
FROM Student

使用别名

--查找姓名年龄 并将其拼接成一列
SELECT RTRIM(name) + '('+RTRIM(age)+')' AS NAME
FROM Student

3.3执行算术计算

--查找信息并添加sum列（男生年龄+1，女生年龄+0）
SELECT name,age,sex,age+sex AS SUM
FROM Student

4、第四节(函数)

4.1文本处理函数

UPPER()函数 文本转大写

--查找姓名并转大写
SELECT UPPER(name)
FROM STUDENT

用法同上

SOUNDEX()函数

soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得对字符串进行发音比较而不是字母比较。

例如由于输入错误name列中有sam，但是实际是san，直接查找会显示为空，使用SOUNDEX()则可以避免这样的情况

SELECT *
FROM STUDENT
WHERE SOUNDEX(name) = SOUNDEX('san');

4.2日期和时间处理函数

--查找入学时间是2022的学生姓名
SELECT name	
FROM STUDENT
WHERE DATEPART(yyyy,date) = 2022;

DATEPART(datapart,date)

date 参数是合法的日期表达式。datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

GETDATE()

--获取当前时间
INSERT INTO CLASS
VALUES (1,'一班',GETDATE())

4.3数值处理函数

4.4排序

--dense_rank()
--查找成绩并排序 （力扣 sql-178-分数排名）
SELECT score,
dense_rank() over (order by score desc) as rank
FROM Scores

5、第五节(聚集函数)

5.1 sql中的聚集函数

5.2 AVG()函数

平均值

--查找平均年龄
SELECT AVG(age) AS AVGAGE
FROM STUDENT;
--查找男生的平均年龄
SELECT AVG(age) AS AVGAGE
FROM STUDENT
WHERE sex = 1;

5.3 COUNT()函数

计算个数

--查找人数
SELECT COUNT(name) AS COU
FROM STUDENT;
--查找男生人数
SELECT COUNT(name) AS COU
FROM STUDENT
WHERE sex = 1;

5.4 MAX() \ MIN()函数

MAX()最大值

--查找最大年龄
SELECT MAX(age) AS AGE
FROM STUDENT;

MIN()最小值

--查找最小年龄
SELECT MIN(age) AS AGE
FROM STUDENT;

5.5 SUM()函数

求和

--查找年龄和
SELECT SUM(age) AS AGESUM
FROM STUDENT;

5.6 聚集不同值

ALL DISTINCT

默认是ALL 全部数据

DISTINCT 对查询结果去重

--查找不同年龄段的个数
SELECT COUNT(DISTINCT age) AS COUN
FROM STUDENT;

5.7 组合聚集函数

--数据个数、最大年龄、最小年龄、平均年龄
SELECT COUNT(*) AS NUM,
	   MAX(AGE) AS MAX,
	   MIN(AGE) AS MIN,
	   AVG(AGE) AS AVG
FROM STUDENT;

6、第六节(分组)

GROUP BY

6.1 创建分组

--根据姓名分组
SELECT NAME , COUNT(*) AS NUM
FROM STUDENT
GROUP BY NAME;

6.2 过滤分组

通过HAVING 进行过滤分组

HAVING 支持 WHERE 所有的通配符

--根据姓名分组并过滤出出现次数大于等于二的姓名和出现次数
SELECT NAME , COUNT(*) AS NUM
FROM STUDENT
GROUP BY NAME
HAVING COUNT(*)>=2;

6.3 分组和排序

--分组并根据NAME排序
SELECT NAME , COUNT(*) AS NUM 
FROM STUDENT
GROUP BY NAME
HAVING COUNT(*)>=2
ORDER BY ID,AGE;

获取指定行数数据
OFFSET
FETCH

--获取第三行到第四行数据
SELECT * FROM CLASS
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY 
--查找第二高分数
SELECT DISTINCT SCORE 
FROM GRADE
ORDER BY SCORE DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY

7、第七节(子查询)

7.1 利用子查询进行过滤

--查找成绩大于等于90分的学生
SELECT NAME
FROM STUDENT
WHERE ID IN ( SELECT studentid
			  FROM Grade
			  WHERE SCORE >=90
			 );

也可嵌套多个子查询，但在实际开发中考虑性能，并不会使用过多的嵌套

7.2作为计算字段使用子查询

--查找学生姓名、年龄、总成绩
SELECT NAME, 
	   AGE,
	  (SELECT  SUM(SCORE)
       FROM GRADE
       WHERE grade.studentid = student.id) AS SCORE
FROM STUDENT;

8、第八节(表联结)

8.1创建联结

两个表连接

--查找姓名 年龄 成绩
SELECT NAME,
	   AGE,
	   SCORE
FROM STUDENT,GRADE
WHERE STUDENT.ID = GRADE.STUDENTID;

8.2笛卡尔积

检索出的行的数目 = 第一个表行数*第二个表的行数

SELECT NAME,
	   AGE,
	   SCORE
FROM STUDENT,GRADE
WHERE STUDENT.ID = GRADE.STUDENTID;

表1

id	name	age
1	张三	18
2	李四	19

表2

id	studentid	subjectid	score
1	1	1	88
2	1	2	69
3	2	1	59
4	2	2	80

检索结果

name	age	score
张三	18	88
张三	18	69
李四	19	59
李四	19	80

8.3内连接

INNER JOIN

内连接只匹配两者的公共部分

--查找两个学生表中重复的学生
SELECT *
FROM STUDENT_1 A
INNER JOIN STUDENT_2 B
ON A.ID = B.ID;

8.4自联结

--例：想要给张老师所带班级的所有代课老师授予优秀教师，我们需要先找到张老师所带的班级，然后找到该班级的代课老师

SELECT TEA_ID,TEA_NAME,TEA_PHONE
FROM TEACHERS
WHERE CLASS_ID = (SELECT CLASS_ID
                  FROM TEACHERS
                  WHERE TEA_NAME = '张老师'
                 );

8.5外连接

左连接

LEFT JOIN \ LEFT OUTER JOIN(两者没有区别，前者是后者简写)

以左边信息为主，只查询左表有的信息。

--所有学生包含没有考试的学生
SELECT *
FROM STUDENT A
LEFT JOIN GRADE B
ON A.ID = B.STUDENTID;

右联结

RIGHT JOIN\RIGHT OUTER JOIN

只匹配右表存在的信息

--查询参加考试的学生的信息
SELECT * 
FROM STUDENT A
RIGHT JOIN GRADE B 
ON A.ID = B.STUDENTID;

全联结

FULL JOIN \ FULL OUTER JOIN

--查找所有信息
SELECT * 
FROM STUDENT A
FULL JOIN GRADE B 
ON A.ID = B.STUDENTID;

8.6使用带有聚集函数的联结

--查找参加考试信息并统计人数
SELECT NAME ,COUNT(NAME) AS NUM
FROM STUDENT A
RIGHT JOIN GRADE B 
ON A.ID = B.STUDENTID
GROUP BY A.NAME;

8.7组合查询

使用UNION

-- 查询 年龄大于18的男生
SELECT ID,NAME,AGE
FROM STUDENT
WHERE AGE>18
UNION
SELECT ID,NAME,AGE
FROM STUDENT
WHERE SEX = 1;

UNION会自动去除重复的行

若要想要包含重复的行则使用UNION ALL

-- 查询 年龄大于18的男生
SELECT ID,NAME,AGE
FROM STUDENT
WHERE AGE>18
UNION ALL
SELECT ID,NAME,AGE
FROM STUDENT
WHERE SEX = 1;

对组合查询进行排序

-- 查询 年龄大于18的男生,并根据id,NAME,AGE排序
SELECT ID,NAME,AGE
FROM STUDENT
WHERE AGE>18
UNION
SELECT ID,NAME,AGE
FROM STUDENT
WHERE SEX = 1
GROUP BY ID,NAME,AGE;

9、第九节(插入)

9.1数据插入(INSERT)

1、插入完整行

--插入整行信息
INSERT INTO STUDENT
VALUES ('6',
    	'JACK',
        '23',
        '1',
        '1234567'
	   );
--    或 
-- 这样更加安全，但相对繁琐
INSERT INTO STUDENT(
	ID,
	NAME,
	AGE,
	SEX,
	PHONE
)
VALUES ('6',
    	'JACK',
        '23',
        '1',
        '1234567'
	   );

2、插入部分行

-- 插入id，姓名，年龄
INSERT INTO STUDENT(
	ID,
	NAME,
	AGE
)
VALUES ('6',
    	'JACK',
        '23'
	   );

3、插入检索出的数据

--	查找STUDENT插入到STUDENT_S中
INSERT INTO STUDENT_S(
	ID,
	NAME,
	AGE,
	SEX,
	PHONE)
SELECT ID,
	NAME,
	AGE,
	SEX,
	PHONE
FORM STUDENT;

9.2 从一个表复制到另一个表

SELECT *
INTO STUDENT_S
FROM Student;

10、更新和删除数据

10.1 更新数据

UPDATE

--修改姓名，年龄
UPDATE STUDENT
SET NAME = 'MC赵四'，
	AGE = '55'
WHERE ID = '5';

10.2 删除数据

DELETE

--从表中删除一行数据
DELETE FROM STUDENT
WHERE ID = '8';

ALTER

--删除列
ALTER TABLE TEACHER
DROP COLUMN TEACHERID;

11、创建和操作表

11.1 创建表基础

CREATE TABLE STUDENTS
(
	S_ID INT 		 PRIMARY KEY,	--主键(唯一标识)
    NAME VARCHAR(20) NOT NULL,		--(不为空)
    AGE  INT         NULL,			--为空(可省略，默认不填为空)
	SEX  VARCAHR(10) DEFAULT '男'   --设置默认值
)

11.2约束

主键 PRIMARY KEY

主键是一种特殊的约束，用来保证一个列中的值时唯一的，并且永远不改动。

-- 创建表时声明自增主键
CREATE TABLE TTT
(
	ID INT NOT NULL PRIMARY KEY IDENTITY(1001,1),-- IDENTITY(初始值，自增量)
	NAME VARCHAR(20) NOT NULL,
	AGE INT,
	SEX VARCHAR(10)
)
-- 修改主键
ALTER TABLE TTT
ADD PRIMARY KEY (ID);

外键 FOREIGN KEY

外键时表中的一个列，必须与另一个表的主键对应。通常用于连接两个表

-- 创建表时声明外键
CREATE TABLE TTT
(
	ID INT NOT NULL PRIMARY KEY IDENTITY(1001,1),-- IDENTITY(初始值，自增量)
	NAME VARCHAR(20) NOT NULL,
	AGE INT CHECK(AGE>=0 AND AGE<200),
	SEX VARCHAR(10) DEFAULT '男',
    PHONE INT NOT NULL UNIQUE,
    CLASSID INT REFERENCES CLASS(ID)
);
-- 修改外键
ALTER TABLE TTT
ADD CLASSID INT 
FOREIGN KEY(CLASSID) REFERENCES CLASS(ID);

非空 NOT NULL

使数据不能为空

NAME VARCHAR(20) NOT NULL;

默认 DEFAULT

给数据设置默认值

SEX VARCHAR(10) DEFAULT '男';

检查 CHECK

检查约束用来保证这一列数据满足一组指定的条件。

-- 年龄满足大于等于0小于200
AGE INT CHECK(AGE>=0 AND AGE<200)

唯一 UNIQUE

约束一组数据中该列数据没有重复、唯一。

-- 手机号唯一
PHONE INT NOT NULL UNIQUE,

11.3 更新表

ALERT

添加时可以添加约束

--添加列
ALTER TABLE STUDENT
ADD CLASSID INT ;
--添加列并设成外键
ALTER TABLE STUDENT
ADD CLASSID INT 
FOREIGN KEY(CLASSID) REFERENCES CLASS(ID);
--删除列
ALTER TABLE TEACHER
DROP COLUMN TEACHERID;
--添加约束--
--添加外键
ALTER TABLE STUDENT      
ADD CONSTRAINT CLASSID
FOREIGN KEY(CLASSID) REFERENCES CLASS(ID);
--添加主键
ALTER TABLE STUDENT      
ADD CONSTRAINT ID
PRIMARY KEY(ID);
--添加默认约束
ALTER TABLE STUDENT
ADD CONSTRAINT AGE
DEFAULT 1 FOR AGE;

11.4 删除表

DROP

DROP TABLE STUDENT

11.5 重命名表

SP_RENAME 'STUDENT_S','STUDENT_S1';

12、第十二节(视图)

12.1 什么是视图

视图是一个虚拟的表，并不存在具体的列和行。本质上就是查询。

例，我们想要查找某一学科及格的同学。

--查找某一学科及格的同学
SELECT STUDENT.NAME,STUDENT.LASSID
FROM STUDENT,GRADE,SUBJECT
WHERE STUDENT.ID = GRADE.STUDENTID
	  AND GRADE.SUBJECTID = SUBJECT.ID
	  AND SUBJECT.NAME = '语文'
	  AND GRADE.SCORE >= 60;

任何需要这个数据的人都必须要了解相关表的结构，并知道如何创建查询和对表进行联结。

为了检索别的学科及格的学生数据，必须要修改where语句，因此非常麻烦。

如果我们把这个查询封装成一个名为 sub_pass的虚拟表。这样就可一比较简单的实现不同科目的查询。

--SUB_PASS表
SELECT STUDENT.NAME,STUDENT.CLASSID,SUBJECT.name as subna
FROM STUDENT,GRADE,SUBJECT
WHERE STUDENT.ID = GRADE.STUDENTID
	  AND GRADE.SUBJECTID = SUBJECT.ID
	  AND GRADE.SCORE >= 60;

例：

--查询数学
SELECT NAME,CLASSID
FROM SUB_PASS
WHERE subna = '数学';

12.2 为什么使用视图

1. 重用SQL语句
2. 简化复杂sql操作，在编写查询后，可以方便的重用它而不必知道他的基本结构
3. 使用表的组成部分而不是整个表
4. 保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
5. 能够更改数据的格式和表示，视图可以返回和底层表的表示很格式不同的数据。

12.3 视图的规则和限制

• 视图必须唯一命名(与表一样)
• 对视图的创建数目没有限制
• 为了创建视图，必须具有足够的访问权限，通常由数据库管理人员授权
• 视图可以嵌套
• 视图不能使用索引

12.4 创建视图

CREATE VIEW 创建视图

DROP VIEW 删除视图

--创建sub_pass视图
CREATE VIEW sub_pass AS
SELECT STUDENT.NAME,STUDENT.CLASSID,SUBJECT.name as subna
FROM STUDENT,GRADE,SUBJECT
WHERE STUDENT.ID = GRADE.STUDENTID
	  AND GRADE.SUBJECTID = SUBJECT.ID
	  AND GRADE.SCORE >= 60;

--查询
SELECT NAME
FROM SUB_PASS
WHERE SUBNA = '数学';

用视图重新格式化数据

--  查看学生姓名及班级
SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS
FROM student;

使用视图格式化数据

-- 创建视图
CREATE VIEW STUCLASS AS
SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS
FROM STUDENT;

--使用视图查询
SELECT CLASS
FROM STUCLASS;

使用试图过滤掉不想要的数据

-- 过滤掉未填写班级的学生
CREATE VIEW STUCLASS AS
SELECT RTRIM(NAME)+'是'+RTRIM(CLASSID)+'班' AS CLASS
FROM STUDENT
WHERE CALSSID IS NOT NULL;

使用计算字段

-- 使用计算字段查询学生信息及教材费
CREATE VIEW BOOK_S AS
SELECT ID,
	   NAME,
	   BOOK_NUM,
	   BOOK_PRICE,
	   BOOK_NUM*BOOK_PRICE AS PRICE
FROM BOOK;

13、第十三节(存储过程)

13.1 什么是存储过程

存储过程是大型数据库系统中，一组为了完成特定功能的sql 语句集，存储在数据库中，经过一次编译后再次调用后不需要再次编译，用户通过指定存储过程并给出参数 (如果该存储过程有参数) 来调用存储过程

人话：专门干一件事的sql语句

可以由数据库调用，也可由程序调用

13.2 为什么使用存储过程

• 效率高

• 编译一次后会存放到数据库，每次调用时直接执行。普通sql语句保存时，会保存到其他地方，需要先分析编译在执行

• 降低网络流量

• 在远程调用时，不会传输大量的字符串类型sql语句

• 复用性

• 存储过程往往只针对一个特定的功能编写，需要某个功能时，可以再次调用

• 可维护性高

• 当功能需求发生小变动时，修改存储过程比较容易，花费精力小

• 安全性高

• 某个特定功能的存储过程一般只有特定的用户可以使用，有身份限制，相对安全性高

13.3 创建存储过程

-- 创建无参
-- 查询学生全部信息
CREATE PROCEDURE stuInfo
AS
SELECT *
FROM STUDENT;

--调用，执行存储过程
EXEC stuInfo;

-- 创建有参
-- 根据传参（学生ID）来查询学生信息
CREATE PROCEDURE stuInfo(@STUID INT)
AS 
SELECT *
FROM STUDENT
WHERE ID = @STUID;

--调用，执行存储过程
-- 查找1号学生全部信息
EXEC stuInfo 1;

13.4 修改、删除、重命名操作

修改

-- 修改为只查找学生姓名
ALTER PROCEDURE stuInfo
AS
SELECT NAME
FROM STUDENT;

删除

DROP PROCEDURE stuInfo;

重命名

SP_RENAME stu_only, stuInfo;

13.5 有参存储过程

-- 一个参数 -----------------------
CREATE PROCEDURE stuInfo(@STUID INT)
AS 
SELECT *
FROM STUDENT
WHERE ID = @STUID;

-- 两个参数 -----------------------
CREATE PROCEDURE stuInfo(@STUID INT,@STUAGE INT)
AS 
SELECT *
FROM STUDENT
WHERE ID = @STUID
AND   AGE = @STUAGE;
--调用
EXEC stuInfo 1,11;

-- 创建有返回值的存储过程 ------------
CREATE PROCEDURE stuName(
    @STUID  INT,
	@s_NAME varCHAR(20) OUTPUT) AS
SELECT @s_NAME = NAME
FROM STUDENT
WHERE ID = @STUID;
-- 调用,执行存储过程，根据学号输出姓名
DECLARE @NAME VARCHAR(20)
EXEC STUNAME 1,@NAME OUTPUT
PRINT @NAME;

-- 创建带通配符的存储过程 ------------
CREATE PROC STU_NAME(
	@S_NAME VARCHAR(20) = '%')
AS
SELECT * 
FROM STUDENT 
WHERE NAME LIKE @S_NAME;
-- 调用
EXEC STU_NAME '张%';
-- 或 EXEC STU_NAME '%三';

-- 创建加密存储过程 -----------------
CREATE PROC STU_INFO
WITH ENCRYPTION -- WITH ENCRYPTION子句对用户隐藏存储过程的文本
AS 
SELECT *
FROM STUDENT;
-- 调用
EXEC STU_INFO;
EXEC SP_HELPTEXT 'STU_INFO'; -- 对象 'STU_INFO' 的文本已加密。

-- 不缓存存储过程 ------------------
CREATE PROC INFO_S
WITH RECOMPILE
AS
	SELECT * 
	FROM STUDENT
GO 
	EXEC INFO_S;
	EXEC SP_HELPTEXT INFO_S;
	
-- 创建带有游标的存储过程 -----------
CREATE PROC STU_USER
	@S_CUR CURSOR VARYING OUTPUT
AS
	SET @S_CUR  = CURSOR FORWORD ONLY STATIC FOR
	SELECT ID,
		   NAME,
		   AGE
	FROM STUDENT
	OPEN @S_NAME;
GO
DECLARE @CUR     CURSOR,
		@S_ID    INT,
		@S_NAME  VARCHAR(20),
		@S-AGE   INT
EXEC STU_USER @S_CUR = @CUR OUTPUT;
FETCH NEXT FROM @CUR INTO @S_ID,@S_NAME,@S-AGE;
WHILE(@@FETCH_STATUS=0)
BEGIN
	FETCH NEXT FROM @CUR INTO @S_ID,
							  @S_NAME,
							  @S-AGE;
	PRINT 'S_ID:'+CONVERT(VARCHAR,@S_ID)+',S_NAME:'+@S_NAME+',S_AGE'+@S_AGE;
END
CLOSE @CUR;   	  --关闭游标
DEALLOCATE @CUR;  --释放游标

创建插入数据的存储过程

CREATE PROC S_INSERT
	@S_ID INT ,
	@S_NAME VARCHAR(20)
AS
DECLARE @S_NUM INT		
SELECT @S_ID = COUNT(*)+1
FROM STUDENT
SELECT @S_NUM = MAX(AGE)+1
FROM STUDENT
INSERT INTO STUDENT(ID,AGE,NAME)
VALUES(@S_ID,@S_NUM,@S_NAME);
go
exec S_INSERT 2,'魏老板';
SELECT * FROM STUDENT;

14、第十四节(事务)

14.1 事务处理

什么是事务

事务处理可以用来维护数据库的完整性，他保证成批的SQL操作要么完全执行，要么完全不执行。

事务的四大特性

• 原子性

• 要么全部完成，要么全部取消。如果事务崩溃，状态回到事务之前(事务回滚)

• 隔离性

• 隔离性是当多个用户并发访问数据库时，比如操作同一张表，数据库为每一位用户开启的用户，不能被其他事务干扰，多个并发事务之间相互隔离
• 例：T1和T2两个并发事务，在T1看来，T2发生在T1之后或之前，并不是同时进行。在T2看来是在T1之前或之后。

• 持久性

• 事务发生后对数据的改变是永久的

• 一致性

• 数据与表一致，只有合法的数据才能写入数据库

事务处理

事务：指一组AQL语句

回退(回滚)：指撤销指定SQL语句的过程

提交：指将未存储的SQL语句存储到数据库

保留点：指事务处理中设置的临时占位符，你可以对它发布回退

14.2 控制事务处理

BEGIN 开启一个事务

ROLLBACK 事务回滚

COMMIT 确认事务

-- STUDENT表插入一行信息
BEGIN TRANSACTION  --简写TRAN
INSERT INTO STUDENT
(ID,NAME,AGE,SEX,PHONE)
VALUES(
	14,
    '脑',
    22,
    1,
    11122233344
)
COMMIT TRANSACTION

ROLLBACK 事务回滚

-- 插入数据，如果插入失败回滚输出1，成功输出0
BEGIN TRAN ADDINFO
DECLARE @ERROR_S INT;
SET @ERROR_S = 0;
BEGIN TRY
	INSERT INTO STUDENT VALUES(9,'魏老板',23,1,12112,201);
	INSERT INTO STUDENT VALUES(10,'马大师',23,1,12112,201);
	INSERT INTO STUDENT VALUES(11,'脑师傅',23,1,12112,201);
END TRY
BEGIN CATCH
	SET @ERROR_S = @ERROR_S+1;
END CATCH
IF(@ERROR_S>0)
BEGIN
	ROLLBACK TRAN ADDINFO;
	PRINT @ERROr_S;
END
ELSE
BEGIN
	COMMIT TRAN ADDINFO;
	PRINT @ERROR_S;
END

SAVE 保留点

对于简单的事务可以使用ROLLBACK、COMMIT进行整体回退提交。而复杂的事务可能需要部分提交和回退。

为了支持部分回退事务处理，必须能在事务处理块中合适的位置放置占位符。而这些占位符就是保留字。

-- 依次插入数据,第一条数据插入成功后会设置一个保留点，第二条数据插入时，如果失败则回退到保留点。往后依次。
BEGIN TRAN ADD_UI
INSERT INTO STUDENT VALUES(21,'王富贵',23,0,12121,201);
IF @@ERROR <> 0 ROLLBACK TRAN;
SAVE TRAN SUCCESS1;
INSERT INTO STUDENT VALUES(22,'那个人',23,0,12121,201);
IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS1;
SAVE TRAN SUCCESS2;
INSERT INTO STUDENT VALUES(23,'那个村',23,0,12121,201);
IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS2;
SAVE TRAN SUCCESS3;
INSERT INTO STUDENT VALUES(14,'那个镇',23,0,12121,201);
IF @@ERROR <> 0 ROLLBACK TRAN SUCCESS3;
COMMIT TRAN;

15、第十五节(游标)

15.1 游标

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

作用

1. 遍历数据行；
2. 保存查询结果，方便下文调用；

15.2 创建游标

DECLARE CURSOR_NAME(游标名称)
[insensitive] [scroll] cursor
for 
select [xxxx] -- 查询语句

-- 创建游标
DECLARE STU_NAME CURSOR
FOR
SELECT * FROM STUDENT;
-- 打开游标
OPEN S_NAME;

-- 使用FETCH访问游标数据
-- 声明参数
DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT;
FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID;
-- 打印信息
PRINT cast(@ID as varchar)+@NAME+cast(@AGE as varchar)+cast(@SEX as varchar)+cast(@PHONE as varchar)+cast(@CLASSID as varchar);
-- 打印全部学生信息
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM STU_NAME 
	INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID;
	PRINT cast(@ID as varchar)+@NAME+cast(@AGE as varchar)+cast(@SEX as varchar)+cast(@PHONE as varchar)+cast(@CLASSID as varchar);
END
--关闭游标
CLOSE STU_NAME;

next

返回结果集当前行的下一行，首次提取返回第一行。

frior

返回结果集的上一行，首次提取无数据返回。

first

返回结果集第一行。

last

返回结果集最后一行。

absolute

移动到结果集的第n行。如果n为正数，从结果集的第一行（包含第一行）起移到第n行；如果n为负数，则从结果集的最后一行起移到第n行。

relative

从游标指针的当前位置移动n行。如果n为正数，则读取游标当前位置起向后的第n行数据；如果n为负数，则读取游标当前位置起向前的第n行数据。

15.3 基于游标定位UPDATE语句和定位DELETE语句

-- UPDATE
UPDATE [表名] 
SET [列名]
WHERE CURRENT [游标名];
-- 修改性别
OPEN STU_NAME;
DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT
FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID;
UPDATE STUDENT SET SEX = 1
WHERE CURRENT OF STU_NAME;
CLOSE STU_NAME;

-- DELETE
DELETE FROM [表名]
WHERE CURRENT OF [游标名]

-- 删除一个学生数据
OPEN STU_NAME;
DECLARE @ID INT,@NAME VARCHAR(20),@AGE INT,@SEX INT,@PHONE INT,@CLASSID INT
FETCH NEXT FROM STU_NAME INTO @ID,@NAME,@AGE,@SEX,@PHONE,@CLASSID;
DELETE FROM STUDENT
WHERE CURRENT OF STU_NAME

15.4 删除游标

CLOSE 关闭游标并不会释放其占用的数据结构

若想删除需要用DEALLOCATE进行删除

DEALLOCATE STU_NAME;

16、第十六节(索引、触发器)

16.1 索引

索引是用来排序数据一加快搜索和排序操作的速度。

单列索引

CREATE INDEX NAME 
ON
STUDENT(NAME)

多列suoyin

CREATE INDEX NAME 
ON
STUDENT(NAME,AGE)

唯一索引

CREATE UNIQUE INDEX NAME
ON 
STUDENT(NAME)

隐式索引

-- 由数据库服务器在创建某些对象的时候自动生成。例如主键约束和唯一约束。

修改索引

use database_name

exec sp_rename ‘table_name.old_name’ ‘new_name’

删除索引

DROP INDEX STUDENT.NAME;

查看索引

use [数据库名]
exec sp_helpindex [表名]

16.2 触发器

触发器时特定的存储过程，在特定的数据库活动发生时自动执行。

触发器具有以下数据的访问权：

• INSERT 操作的所有数据；
• UPDATE操作的所有新、旧数据；
• DELETE操作中删除的数据；

触发器的作用

• 保证数据一致。例：在INSERT或UPDATE中转化所有姓名大写
• 基于某个表的变动在其他表上执行活动。例：每当更新或删除一行时将审计跟踪记录写入某个日志表
• 进行额外的验证并根据需求回退数据。例：保证某个学生的额外加分不超限定，如果超出，则阻塞插入
• 计算计算列的值或更新时间戳。

创建一个触发器

-- 插入、更新数据时会把姓名修改为大写
CREATE TRIGGER NAME_UPER
ON STUDENT
FOR INSERT,UPDATE
AS
UPDATE STUDENT
SET NAME = UPPER(NAME);