sql语句(SQL SERVER)

已于 2023-07-29 16:16:05 修改
阅读量3k 收藏 53
点赞数 6
文章标签： 数据库 sql sqlserver
于 2022-09-20 17:19:32 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46068645/article/details/126957538
版权

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

idnameage
1张三18
2李四19

表2

idstudentidsubjectidscore
11188
21269
32159
42280

检索结果

nameagescore
张三1888
张三1869
李四1959
李四1980

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);
eagle eye
关注
  • 6
    点赞
  • 踩
  • 53
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
SQL SERVER 分组求和sql语句
09-09
主要介绍了SQL SERVER 分组求和sql语句,需要的朋友可以参考下
必须要学的SQL Server 的常用语句（满满的干货），拿走不谢~~~
m0_49526063的博客
05-31 7135
文章目录前言一、基本常用语句二、连接和分组三、进阶级语句/函数1.CASE WHEN2.DATEDIFF3.select嵌套4.CAST 转换类型5.GETDATE() 函数6.DISTINCT总结 前言 前几天做项目的时候遇到算数据百分比的问题，其中很多数据都不是现成的，所以用到了很多关于数据库的知识。我在这里把它一一列举出来，有需要的朋友拿走不谢！嘿嘿！ 一、基本常用语句 具体写法如下： 选择：select * from table1 where 范围 插入：insert into tabl.
经典SQL Server语句大全（超详细）
weixin_33923762的博客
06-28 3328
经典SQL语句大全（参照他人，只为学习，还请见谅） 一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack...
SQL学习笔记——创建数据库和表
qq_53152917的博客
03-10 5833
步骤：在对象资源管理器中选择表CP→点击右键→点击“编辑前200行”，在右侧会打开表数据窗口→将光标定位到某一列中，输入该列的值→当一行数据输入完成之后，按Enter键还或者将光标定位到下一行，则当前行的数据自动保存。打开 SSMS（Microsoft SQL Server Management Studio）→新建查询→输入如下图的sql语句→点击执行。步骤：对象资源管理器中选择表CP→点击右键→点击“编辑前200行”，在右侧会打开表数据窗口→将光标定位到某一列中，右键点击删除。
MySQL数据库的SQL语句
怎么也想不出名字的博客
12-04 1719
SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理邓功能。 在数据表中删除指定的数据记录 delete 清空表后，返回结果内有删除的记录条目;delete工作时是一行一行的删除记录数据的; 如果表中有自增长字段，使用delete from删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增长 创建临时表 临时表创建成功之后，使用show tables命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以执行
SQL server 语句大全
02-15
目录 SQL语法 2 数据库 5 创建数据文件 5 修改数据库 6 删除 7 数据表 8 判断数据库中的某表是否存在，存在删除该表 8 修改表语法 8 删除数据表 8 约束 8 高级查询 9 聚合函数（统计） 10 SQL变量 11 局部变量 11 全局变量 11 SQL中的代码块 14 数据库中的if和else语句 14 SQL中的while语句 14 Case－End多分支语句 14 子查询 15 视图（虚拟表）和索引 16 视图 16 索引 16 事务管理 17 数据库的安全性 18 建立权限 19 存储过程（procedure） 20 无参数存储过程 20 带参数 20 带参数模糊查找 20 带参数空值和模糊查找 21 多参数、输入与输出参数 21 触发器（trigger） 22 什么是触发器？ 22 触发器的分类 22 创建触发器语法 22 事前触发器（Insert of） 23 事后触发器（for或after） 23 使用触发器方法其一 —— 判断 24 数据库备份和恢复 25
《SQL Server基础——SQL语句》
Dustpolaris的博客
06-14 9577
SQL Server基础——SQL语句 一、创建和删除数据库； 二、创建数据表； 三、创建视图； 四、约束语句； 五、修改语句； 六、终局之战； 七、查询语句； 八、分类汇总； 九、连接查询； 十、特殊查询；
SQL Server语句
qq_46630774的博客
01-14 2009
SQL简介： 结构化查询语言，全拼 Structured Query Language 主要用于对数据库的操作，大多数据库遵循sql原则 SQL server使用的是sql的扩展集T-SQL 全拼 Transact-SQL SQL组成： SQL由四部分组成分别为DML DDL DQL DCL DML:数据表操作语言，用于插入（insert），修改(update)，查询(select)数据表的操作 DDL:数据库的定义语言，建立数据库，数据库对象和定义其列，大部分是CREATE开头 DQL:对于数据表尽
SQL Server 语句
pjh_2003的博客
05-18 186
SQL Server 语句 A.SQL Server 复制表 Select into 目标表Sales.Stores_2 from 原表Sales.stores; B.SQL Server 标识列 要为表添加创建标识列,可以使用IDENTITY属性, IDENTITY[(seed,increment)] ·seed是表中的第一行的值(第一条记录标识列使用的值) ·increment 是添加到上一行标识列的增量值. -SQL Server允许每个表只拥有一个标识列....
SQL Server 索引维护sql语句
01-19
使用以下脚本查看数据库索引碎片的大小情况： 代码如下:DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS 以下使用脚本来处理维护作业： 代码如下:/*Perform a ‘USE ’ to select the database ...
SQLServer实例语句
06-11
SQLServer实例语句，偏基础吧，合适小白拿来练习用，就是一些实例的语句哈，需要的可以下载下来练习下，初级面试题
非常好用的SQL Server 抓取SQL语句工具HOOK
03-23
非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK
sqlserver 子查询语句
03-23
表ta userno(编号),username(名字) 表tb userno(编号),score(分数) 想查询结果为 userno(编号),username(名字)..."子查询"是SQL语言中的一个重要概念，它允许在一个查询语句（主查询）中嵌套另一个查询语句（子查询）
【课堂练习】
最新发布
JacksonLeo的博客
04-29 289
虽然现代JVM的垃圾回收机制已经非常高效，但是在性能敏感的场合，仍然需要注意对象的创建频率。例如，在SysLogininforMapper中的deleteLogininforByIds方法，如果处理大量ID时，应考虑使用批处理技术，而不是循环单条处理。例如，在SysMenuMapper中的查询方法，如果涉及到复杂的查询条件或者大量数据的查询，应确保SQL语句的优化和索引的使用。异常处理：代码中应该有更明确的异常处理逻辑，比如使用try-catch块捕获可能的异常，并进行适当的处理，如记录日志、回滚事务等。
探秘MySQL主从复制的多种实现方式
todoitbo的博客
04-26 1035
本文将深入探讨MySQL主从复制的多种实现方式，包括基于语句、基于行和混合模式等。无论您是初学者还是有经验的数据库管理员，都能通过本文全面了解MySQL主从复制技术的多样化选择，从而提高数据库的可用性和性能。
【ROMA解决方案和功能架构】
数据也是生产力，保持热爱，奔赴山海！
04-23 781
1、基于ROMA融合集成平台，实现企业应用、数据、API 、OT设备、云服务、合作伙伴应用之间的集成连接。2、ROMA主要包含四个组件：数据集成（Fast Data Integration，简称FDI）、服务集成（API Connect，简称APIC）、消息集成（Message Queue Service，简称MQS）、设备集成（LINK）。
mysql-connector-java和spring-boot-starter-jdbc和mybatis-spring-boot-start
xzy的博客
04-23 1023
JDBC是使用java语言操作mysql数据库的规范，java语言必须按照这个规范写才可以操作mysql数据库。
ubuntu18源码安装postgresql15.2数据库
04-27 327
由于官方的源只能安装到pg10这个版本，整了好一会没有成功就改为源码安装了。
sqlserver的sql语句
07-29
对于你提供的引用内容，我无法直接回答"sqlserver的sql语句"这个问题，因为引用内容中提供了一些关于SQL Server的查询语句和操作。如果你有具体的问题或需要帮助，请提供更具体的信息，我将尽力为你提供帮助。 #### 引用[.reference_title] - *1* *2* *3* [SQL Server 常用SQL语句（收藏版）](https://blog.csdn.net/huang714/article/details/106640872)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值