SQL学习笔记(完整)

简述数据库完整性及其作用

1.域完整性：检查约束默认约束非空约束

2.实体完整性：主键约束唯一约束标识列

3.引用完整性：外键约束

4.自定义完整性：触发器

简述实现数据库操作的语句以及语法

--增
insert into 表名(列1，列2，……) values(值1，值2，……)
--删
delete FROM 表名 WHERE 条件
--改
update 表名 SET 列1=新值1，列2=新值2，…… WHERE 条件
--查
SELECT 列1，列2，…… 
FROM 表名 
WHERE 条件  
group by 列 having 条件  --(结果筛选) 
order by 列[ASC|DESC]  --(结果排序）

常用的聚合函数

--聚合函数
--  max(列) 
--	min(列) 
--	sum(列) 
--	avg(列) 
--	count(*)

表连接分为哪几种类型

--内联查询，可能造成数据列丢失
SELECT 列1,列2，…… 
FROM 表1
inner join 表2 on 表1.列=表2.列
SELECT 列1,列2，…… 
FROM 表1,表2
WHERE 表1.列=表2.列

--左外联查询,保证左表一定出现在结果集中
SELECT 列1,列2，…… 
FROM 表1
left join 表2 on 表1.列=表2.列

--右外联查询,保证右表一定出现在结果集中
SELECT 列1,列2，…… 
FROM 表1
right join 表2 on 表1.列=表2.列

了解设计数据库的步骤

良好的数据库设计：

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

糟糕的数据库设计：

数据冗余、存储空间浪费
内存空间浪费
数据更新和插入的异常

软件项目开发周期中的数据库设计：

需求分析阶段：分析客户的业务和数据处理需求
- 收集信息：与该系统有关人员进行交流，充分了解用户需求，理解数据库需要完成的任务
- 标识实体（Entity）：标识数据库要管理的关键对象或实体，实体一般是名词
- 标识每个实体的属性（Attribute）：例如客人编号、姓名
- 标识实体之间的关系（Relationship）：对象之间的依赖
概要设计阶段：设计数据库的E-R模型图，确认需求信息的准确和完整
- E-R图（Entity-Relationship）分析对象之间的关系：
  - 将各实体转换为对应的表，将个属性转换为各表对应的列
  - 标识每个表的主键列
  - 在表之间建立主外键，提现实体之间的映射关系
- 转化E-R图为数据库模型图：
  - 通过微软提供的Microsoft Visio软件—数据库模型图来绘制数据库模型图
  - 箭头关系由一实体指向另一被依赖的实体
- 映射基数：
  - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kX8iLdr9-1656908913581)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655519985662.png)]
详细设计阶段：应用三大范式审核数据库结构
- 为什么需要数据规范化：减少数据冗余，减少操作异常
- 不符合规范的表设计
  - 信息重复；更新异常；插入异常(无法正确表示信息)；删除异常(丢失有效信息)
- 第一范式（1NF）的目标是确保每列的原子性
  - 如果每列都是不可再分的最小数据但愿（也成为最小的原子单元），则满足第一范式（1NF）
- **第二范式要求每个表只描述一个事情 **
- 第三范式：如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）
代码编写阶段：物理实现数据库，编码实现应用
软件测试阶段
安装部署

SQL语句分为四种

**1、数据查询语句：DQL(Data Query Language):SELECT **

2、数据操纵语句:DML(Data Manipulate Language):Insert Update Delete

3、数据定义语句：DDL(Data Defination Language):建库、建表、加约束

4、数据控制语句：DCL(Data Control Language):权限管理

SqlServer数据库由哪几种文件组成？

主数据文件：*.mdf
次要数据文件：*.ndf
日志文件：*.ldf

创建SqlServer数据库时，需要指定哪些属性？
文件存放位置，分配的初始空间，属于哪个文件组
文件增长：可以按百分比或实际大小指定增长速度
文件容量设置：可以指定文件增长的最大值或不受限

创建数据库的语法

Create Database db_name
ON[Primary]
(
--	<数据文件参数>[,……n]<文件参数>
)
[LOG ON]
(
-- <日志文件参数>[,……n]
)

使用CREATE DATABASE语句创建数据库MySchool，要求：

该数据库具有一个数据文件和一个日志文件
文件保存在D盘已有的文件夹Project下
数据文件的大小初始为5MB，最大值为100MB，文件增长率是15%
日志文件的大小初始为2MB，增长量是1MB

CREATE DATABASE MySchool
	on primary
	(
        name 	   = 	'myschool_data',  --逻辑名
        size	    =    5mb,  --初始大小
        maxsize     =    100mb,  --最大值
        filegrowth  =    15%,  --增长方式
        filename='d:\project\myschool_data.mdf'  --文件路径
    )
    log on
    (
     	name 	= 'myschool_log',
        size 	= 2mb,
        maxsize = 50mb,
        filegrowth =1mb,
        filename = 'd:\project\myschool_log.ldf'
    )

使用CREATE DATABASE语句创建数据库Employee，要求：

数据库具有多个数据和日志文件
分别设置主数据文件和次要数据文件
分别设置不同的日志文件

CREATE DATABASE Employee
ON PRIMARY
	--主要数据文件Employee_data
	(
    	name = 'Employee_data',
        size = 5mb,
        maxsize = 100mb,
        filegrowth = 10%,
        filename = 'd:\project\Employee_data.mdf'
    ),
    --次要数据文件Employee_data2
    (
    	name = 'Employee_data2',
        size = 5mb,
        maxsize = 100mb,
        filegrowth = 10%,
        filaname = 'd:\project\Employee_data2.ndf'
    )
    --主要日志文件Employee_log
LOG ON
	(
    	name = 'Employee_log',
        size = 2mb,
        maxsize = 50mb,
        filegrowth = 1mb,
        filename = 'd:\project\Employee_log.ldf'
    ),
    --次要日志文件Employee_log2
    (
    	name = 'Employee_log2',
        size = 2mb,
        maxsize = 50mb,
        filegrowth = 1mb,
        filaname = 'd:\project\Employee_log2.ldf'
    )
--批处理，立即执行
GO

如果SQL Server中已存在数据库MySchool，运行下列语句，会出现什么问题？

CREATE DATABASE MySchool
ON
(
--数据文件配置信息	……
)
LOG ON
(
--日志文件配置信息	……
)
GO

删除数据库

--删除数据库db_name
Drop database db_name
--一般要添加删除条件
--由于sysdatabase表示在master库中，因此需要先调用master库
USE master
if exists(SELECT * FROM sysdatabases WHERE name = 'db_name')
drop database db_name
create database db_name
ON Primary
	(
        --数据文件配置，name,size,maxsize,filegrowth,filename
    )
LOG ON
	(
        --日志文件配置，name,size,maxsize,filegrowth,filename
    )

使用SQL语句创建数据库

创建学生管理系统的物理数据库，要求数据库取名为MySchool
物理文件保存在D:project下
数据文件的初始大小为10mb，允许自动增长，文件增长率是20%，不限制文件的最大值
日志文件初始大小为3MB，每次自动增量是1mb，文件最大值为20mb

CREATE database MySchool
ON PRIMARY
	(
    	name = 'MySchool_data',
        size = 10mb,
        filegrowth = 20%,
        filename = 'D:\peoject\MySchool_data.mdf'
    )
LOG ON
	(
    	name = 'MySchool_log',
        size = 3mb,
        maxsize = 20mb,
        filegrowth = 1mb,
        filaname = 'D:\project\MySchool_log.ldf'
    )

简述创建表的基本步骤

确定表中有哪些列
确定每列的数据类型
- 基本数据类型：
  - 整型数据：int,smallint
  - 浮点数据：float,numeric,decimal //注：numeric( int 长度，int 精度)
  - 字符数据:
    - 固定长度：char(6)
    - 可变长度：varchar(10|max)
    - 固定长度：nchar(6)
    - 可变长度：nvarchar(10|max)
  - 布尔数据：true/false，bit:1/0
  - 货币数据：money
  - 日期数据：datetime
给表添加各种约束
创建各表之间的关系

附图(字符数据类型)：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KyVQl6yd-1656908913582)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655708021124.png)]

创建表的语法

CREATE table tb_name
(
	--列名1 数据类型 列1的特征,
    --列名2 数据类型 列2的特征,
    --列名3 数据类型 列3的特征,
)
--创建表时应先调用需要建表的库
USE db_name
IF Exists(SELECT * FROM sysobjects WHERE name = 'student')
DROP table student
create table student(
	studentno int not null,
    loginpwd  nvarchar(50) not null,
    sex 	  bit not null,
    gradeid   int not null,
    phone     nvarchar(255) nul,
    address	  nvarchar(255) null,
    bornDate  datetime  not null,
    Email     nvarchar(255) null,
    IdentityCard varchar(18) not null
)
GO

创建表Subject

检查判断课程表Subject是否已存在
- 若存在该表，则先执行删除，再创建新表
使用SQL语句创建课程表Subject

Use db_name
IF Exists(SELECT * FROM sysobjects WHERE name = 'Subject')
DROP table Subject
Create Table Subject
(
    --标识列使用Identity(起始值,自增值)
	SubjectNo int not null identity(1,1) ,
    SubjectName nvarchar(50) not null,
    ClassHour int null,
    GradeID   int null
)
GO

创建表

检查判断成绩表Result是否已存在
- 若已存在该表，则先执行删除，再创建新表
使用SQL语句创建成绩表Result

Use db_name
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Result')
Drop table Result
CREATE TABLE Result
(
	StudentNo  int not null,
    SubjectNo  int not null,
    ExamDate   datetime  not null,
    StudentResult  int  not null
)
GO

数据完整性有哪几种？各自作用是什么？

使用变量

变量分为：

局部变量：
- 局部变量必须标记@作为前缀，如@age
- 局部变量的使用也是先声明，再赋值
全局变量：
- 全局变量必须以标记@@作为前缀，如@@version
- 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

声明局部变量

语法

--DECLARE @变量名 数据类型
--示例
DECLARE @name varchar(8)
DECLARE @seat int

--赋值SET @变量名 = 值
--或 SELECT @变量名 = 值
--示例
SET @name = '张三'
SELECT @name = studentName FROM Student WHERE studentNo = '10011'

练习：查找与李文才学号相邻的同学信息

DECLARE @name = varchar(8) --学生姓名
SET @name = '李文才'
SELECT StudentNo,StudentName,BornDate,Address 
	FROM Student
	WHERE StudentName = @name
--查找与李文才学号相邻的同学信息
DECLARE @StudentNo int
SELECT @StudentNo = StudentNo FROM Student
	WHERE StudentName = @name
SELECT StudentNo,StudentName,BornDate,Address
	FROM Student
	WHERE(StudentNo = @StudentNo +1)
		or (StudentNo = @StudentNo -1)
GO

SET与SELECT区别：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WXsTx9Bd-1656908913583)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655719449252.png)]

DECLARE @studentname varchar(20)
DECLARE @borndate datetime
--无法同时给多个变量赋值
SET @studentname = '张三'		--,@borndate = '1980-02-01' 
SET @borndate = '1980-02-01'

--同时给@studentname与@borndate赋值
SELECT @studentname='张三',@borndate = '1980-02-01'

--同时将学号为10000的同学的姓名，出生日期赋值给@studentname,@borndate
SELECT @studentname=studentname,@borndate=borndate FROM student 
	WHERE studentNo='10000'

--以下结果报错，一个变量无法同时获取多个值
SET @studentname=  (SELECT studentname FROM student)
--以下结果正确， 仅赋值给@studentname学号为10000的学生姓名
SET @studentname=  (SELECT studentname FROM student WHERE studentno = '10000')

--SELECT赋值可以赋值多个，但只会取最后一个值
--以下@studentname变量，只会取查询到的studentname列的最后一个值
SELECT @studentname = studentname FROM student

--表达式未返回值时，变量将被赋NULL值
--以下由于SELECT子句没有返回值，因此@studentname会被赋值为NULL
SET @studentname = （SELECT studentname FROM student WHERE 1<0)

--使用SELECT语句赋值时，若表达式未返回值时，则变量保持原值
--下面赋值由SELECT进行，由于条件表达式没有返回值，因此@studentname保持原值不变
SELECT @studentname = (SELECT studentname FROM student WHERE 1<0)

全局变量

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T2XSauCn-1656908913583)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655720260640.png)]

--全局变量@@ERROR
--@@ERROR用于存储最后一条SQL语句产生错误的错误号
--一旦@@ERROR大于0，则前一条SQL语句出错

--@@IDENTITY
--最后一次插入的标识值
--类型转换函数
--1.convert(varchar(10),100)
--2.cast(100 as varchar(10))
--日期差函数,计算终止日期与起始日期间的差值
--datediff(day,起始日期,终止日期)
--datepart(year,'2022-06-20')

DECLARE @stuno int 
SET @stuno = 20011

SELECT studentname,datediff(year,borndate,getdate()) as 年纪 
	FROM student 
	WHERE studentno= @stuno

DECLARE @bornyear int 
SELECT @bornyear = Datepart(year,borndate) FROM student WHERE studentno=@stuno
SELECT * FROM student 
	WHERE datepart(year,borndate)=@bornyear+1 
		or datepart(year,borndate)=@bornyear-1

逻辑控制语句

分支结构

IF-ELSE语句
CASE-END语句

循环结构

WHILE语句
CASE WHEN THEN END

DECLARE @subjectno int
DECLARE @latestExamDate datetime
DECLARE @count int //存储没有几个的学员
SELECT @subjectno = subjectno FROM subject WHERE subjectname = 'winform'
SELECT @latestExamDate = max(examdate) FROM result WHERE subjectno = @subjectno
SELECT @count = count(*) FROM result 
	WHERE subjectno =@subjectno 
		and examdate =@latestExamDate
		and studentresult < 60

while(1=1)
	BEGIN
		if(@count=0)
			break
		update result SET studentresult =studentresult+2 
			WHERE subjectno = @subjectno
				and examdate = @latestExamDate
				and studentresult<90
		SELECT @count = count(*) FROM result 
			WHERE subjectno = @subjectno
				and examdate = @latestExamDate
				and studentresult<60 
	end

掌握简单子查询的用法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H7H3HjNR-1656908913584)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655735318196.png)]

//先写出获取李斯文的年龄 
DECLARE @age_li int
SET @age_li = SELECT datediff(year,BornDate,getdate()) 
				FROM studetn WHERE studentname ='李斯文'

SELECT * FROM student 
	WHERE (SELECT datediff(year,BornDate,getdate()) FROM student) < @age_li

//第二种方法,找出出生日期大于李斯文出生起的人
SELECT * FROM student 
	WHERE borndate > (SELECT borndate FROM student WHERE studentname ='李斯文')

掌握IN子查询的用法

//科目为JavaLogic的编号
SELECT subjectno FROM subject WHERE subjectname ='javalogic'
//查找logic课程编号的最近一次考试时间
SELECT max(examdate) FROM result 
	WHERE subjectno = (SELECT subjectno FROM subject WHERE subjectname ='javalogic')
//查找javalogic最近一次考试的学员名单
SELECT studentno FROM result 
	WHERE subjectno = (SELECT subjectno FROM subject WHERE subjectname ='javalogic')
		and examdate = (WHERE subjectno = (SELECT subjectno FROM subject WHERE 			           						subjectname ='javalogic'))
//根据查询到的学员编号查询学生信息
SELECT * FROM student WHERE  studentno IN(
	SELECT studentno FROM result 
	WHERE subjectno = (SELECT subjectno FROM subject WHERE subjectname ='javalogic')
		and examdate = (WHERE subjectno = (SELECT subjectno FROM subject WHERE 			           						subjectname ='javalogic'))
)

掌握EXISTS子查询的用法

//使用mysql语句检测数据库MySchool是否已经创建
Use Master
IF EXISTS(SELECT * FROM sys.sysdatabases)
drop database myschool
//如果SELECT * FROM tb_name 结果集存在
IF EXISTS(SELECT * FROM tb_name)
	BEGIN
		//结果集存在执行此处
	END
ELSE
	BEGIN
		//结果集不存在则执行此处
	END

应用SQL进行综合查询

SELECT * FROM student 
	WHERE studentno in (student studentno FROM result)

SELECT * FROM student 	
INNER JOIN result on student.studentno = result.studentno

事务

使用事务保证操作数据的完整性
掌握如何创建并使用视图
掌握如何创建并使用索引

为什么需要事务

假定资金从账户A转到账户B，至少需要两步

账户A的资金减少
然后账户B的资金相应增加

//假定张三的账户直接转账1000元到李四的账户
CREATE TABLE bank
(
	customerName char(10), //顾客姓名
    currentMoney MONEY     //当前余额
)
GO
ALTER TABLE bank
	add constraint CK_currentMoney check(currentMoney >= 1)
GO
INSERT INTO bank(customerName,currentMoney) values('张三',100)
INSERT INTO bank(customerName,currentMoney) values('李四',1)

//转账测试：张三转账1000元给李四
//我们可能会这样编写语句
//张三的账户少1000元，李四的账户多1000元3
//由于currentMoney有检查约束不得小于1，所以张三会扣款失败，保持原值，而李四则收款成功
UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = '张三'
UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = '李四'
GO
//再次查看转账后的结果
SELECT * FROM bank
GO

if exists(select * from sysobjects where name = 'bank')
drop table bank

//建表
create table bank
(
	customername varchar(20) primary key,
    currentmoney money
)
GO

//给currentmoney添加检查约束
alter table bank add constraint CK_currentmoney check(currentmoney >= 1)
GO

insert into bank value('张三',1000)
insert into bank value('李四',1)
GO

事务：

事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元

事务的四个特性（ACID，既Atomicity、Consistency、Isolation、Durability）：

原子性（Atomicity）
- 事务是一个完整的操作，事务的各步骤操作是不可分的（原子的），要么都执行，要么都不执行
一致性（Consistency）
- 当事务完成时，数据必须处于一致状态
隔离性（Isolation）
- 并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
永久性（Durability）
- 事务完成后，它对数据库的修改被永久保持

使用SQL语句管理事务

//开始事务
BEGIN TRANSACTION

//提交事务
COMMIT TRANSACTION

//回滚(撤销)事务
ROLLBACK TRANSACTION

一旦事务提交或回滚，则事务结束
判断某条语句执行是否出错：
- 使用全局变量@@ERROR
- @@ERROR只判断当前一条T-SQL语句执行是否有错
- 为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计
  - 如：SET @errorSum = @errorSum + @@ERROR

使用事务处理转账问题：

BEGIN TRANSACTION
	DECLARE @errorSum int
    SET @errorSum =0
    UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = '张三'
    SET @errorSum = @errorSum + @@ERROR
	UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = '李四'
	SET @errorSum = @errorSum + @@ERROR
	if(@errorSum > 0)
	BEGIN 
		print '转账失败'
		rollback TRANSACTION
	end
	else
	BEGIN
		print '转账成功'
		commit TRANSACTION
	end
GO

事务分类

显式事务
- 用BEGIN TRANSACTION明确指定事务的开始
- 是最常用的事务类型
隐性事务
- 通过设置SET IMPLICIT_TRANSACTION ON 语句，将隐性事务模式设置为打开
- 其后的T-SQL语句自动启动一个新事物
- 提交或回滚一个事务后，下一个T-SQL语句又将启动一个新事物
自动提交事务
- SQL Server的默认模式
- 每条单独的T-SQL语句视为一个事务

视图

视图是一张虚拟表
- 表示一张表的部分数据或多张表的综合数据
- 其结构和数据是建立在表的查询基础上
视图中不存放数据
- 数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图

视图的作用

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

如何创建视图

1）使用管理器创建视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cCbCRCtx-1656908913584)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655772083387.png)]

2）

//使用SQL语句创建视图
CREATE VIEW view_name
	AS
	<SELECT 语句>

//视同T-SQL语句删除视图
IF EXISTS(select * from sysobjects where name = 'view_name')
DROP VIEW view_name

//使用T-SQL语句查询视图
SELECT * FROM VIEW_NAME


//例
use myschool
go 

if exists(select * from sysobjects where name  ='view_v1')
	drop view view_v1
go

create view view_v1 as
	select student.studentname as 姓名,student.studentno as 学号
			,result.studentresult as 成绩,subject.subjectname as 课程名
    from student inner join result on student.studentno = result.studentno
		inner join subject on result.subjectno = subject.subjectno
		
select * from view_v1

索引

汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等

我们可以根据拼音或偏旁部首，快速查找某个字词

索引是以B-Tree的存储形式

SQLServer中的数据也是按页存放的

索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据

索引页：数据库中存储索引的数据也；索引页类似于字典中按拼音或笔画排序的目录页

索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能

唯一索引
- 唯一索引不允许两行具有相同的索引值
主键索引
- 是唯一索引的特殊类型
聚集索引（Clustered）
- 表中各行的物理顺序与键值的逻辑（索引）顺序相同
非聚集索引（NonClustered)
- 非聚集索引指定表的逻辑顺序

使用SQL语句创建索引

//指定索引类型：唯一索引/聚集索引/非聚集索引
//聚集索引只有一个，且为主键索引
CREATE [UNIQUE|CLUSTERED|NONCLUSTERED]
	//指定索引名称
	INDEX index_name
	//指定充当索引的表命、列名
	ON table_name(column_name……)
	//填充因子(系数)：指定一个0—100之间的值，标识索引填充的百分比
	[WITH FILLFACTOR=x]
//使用SQL语句删除索引
//注：索引也会在表被删除时，同时被删除
DROP INDEX table_name.index_name

//-------------------------------------------------//

//在Student表的StudentName列创建非聚集索引
USE MySchool
GO
//检测是否存在该索引（索引存放在系统表sysindexes中）
IF EXISTS(select name from sysindexes where name ='Index_Student_StudentName')
DROP INDEX student.Index_Student_StudentName
GO
//学生姓名列创建非聚集索引：填充因子30%
CREATE NONCLUSTERED INDEX Index_Student_StudentName
	ON Student(StudentName)
	WITH FILLFACTOR = 30
GO

//----------------------------------------------//
//使用索引查询‘李’姓的学生信息
//指定按索引:Index_Student_StudentName查询
Select * from Student 
	WITH(INDEX=Index_Student_StudentName)
	WHERE StudentName LIKE '李%'

索引的优越点：

优点：
- 加快访问速度
- 加强行的唯一性
缺点
- 带索引的表在数据库中需要更多的存储空间
- 曹总数据的命令需要更长的处理时间，因为它们需要对索引进行更新

按照下列标准选择建立索引的列：

频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作连接的列（主键/外键）

请不要使用下面的列创建索引：

仅包含几个不同值的列
表中仅包含几行

经验

查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
避免在ORDER BY子句中使用表达式
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

存储过程

存储过程的优点

执行速度更快
允许模块化程序设计
提供系统安全性
减少网络流通量

存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过制定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

1 系统存储过程

以sp_开头，用来进行系统的各项设定，取得信息，相关管理工作。

2 本地存储过程

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

3 临时存储过程

分为两种存储过程：

一是本地临时存储过程，以井号（#）作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它。

二是全局临时存储过程，是以两个井号（##）作为其名称的前两个字符，则该存储过程将成为一个存放在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

4 远程存储过程

在SQL Server2005中，远程存储过程（Remote Stored Procedure）是喂鱼远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

5 扩展存储过程

扩展存储过程（Extended Procedures）是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头

常用的系统存储过程

系统存储过程	说明
sp_databases	列出服务器上的所有数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程
sp_password	添加或修改登录账户的密码
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

基本语法

定义存储过程的语法

CREATE PROCEDURE sp_name
	--@para1 type = default value output,
	--……,
	--@para2 type = default value output
AS
	SQL语句
GO

创建存储过程

create procedure 存储过程名
@[参数名] [类型],@[参数名] [类型]
as
BEGIN
…… SELECT avg(grade) FROM Course WHERE cno = 'c1'
End


Exec 存储过程名
--以上格式还可以简写
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
BEGIN
……
end
/*注：'sp_name'为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*

删除存储过程

1.基本语法：

drop procedure sp_name

2.注意事项：

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

--测试1
--创建存储过程，查询JavaLogin最近一次考试平均分以及通过考试的学员名单
Create Procedure sp_getAwg
as
	declare @subjectNO int,
	declare @date datetime,
	declare @avg float
	select @subjectNo = subject from subject where subjectName = 'java logic'
	select @date = max(Examdate) from result where subject = @subject
	select AVG(result) from result 
		where subjectNo = @subjectNo and examdate = @examdate
    if(@avg > 70)
    	print '优秀'
    else
    	print '较差'
    select * from student inner join result on student.studentNo = result.studentNo
    	where subjectNo = @subjectNo and examdate = @examdate and studentResult < 60
go

exec sp_getAvg

测试2

//使用无参存储过程完成数据查询
//利用存储过程查询各学期开设的课程名称和每门课程的课时
//字段名GradeName 学期名  SubjectName 课程名  ClassHour课时
IF EXISTS(select * from sysobjects where name = 'sp_subject_time')
drop procedure sp_subject_time
go

//创建存储过程
Create procedure sp_subject_time
as
	select  GradeName,SubjectName,ClassHour From Grade
		Inner join SUbject on grade.gradeid = subject.subjectid
		order by subject.gradeid,subjectno
go

Exec sp_subject_time

存储过程参数

输入参数
- 向存储过程传入值
输出参数
- 存储过程执行后，传出执行结果

下面程序段的功能是：在数据库中判断是否存在名为my_proc的存储过程，若存在，则删除之，然后创建同名的存储过程，该存储过程的功能是向author_id、author_name、address字段插入数据。阅读并回答以下问题：

USE bookdb
GO
IF EXISTS(SELECT * FROM Sysobjects WHERE name=’my_proc’)
DROP PROC my_proc
GO
CREATE PROC my_proc
@a int, @b char(8),@c char(50)
AS
INSERT INTO clients(client_id,client_name,address)
VALUES(@a,@b,@c)
GO
EXECUTE my_proc  1,'张华','成华大道'

使用SQL语句建个存储过程proc_stu，然后以student表中的学号Stu_ID为输入参数@s_no，返回学生个人的指定信息

IF EXISTS(select * from sysobjects where name = 'proc_stu')
DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu
@s_no int
AS
SELECT * from student where Stu_ID = @s_no
GO
EXEC proc_stu 12345

编写存储过程根据员工号查询员工全部信息

--判断数据库是否存在
if exists(select * from sysdatabases where name ='')
--判断表是否存在
if exists(select * from sysobjects where name = '')
--判断视图是否存在
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS 
           	WHERE TABLE_NAME = N'视图名'
          )
--判断存储过程是否
if exists (select * from sysobjects 
           	where id = object_id(N'存储过程名') 
           	and OBJECTPROPERTY(id, N'IsProcedure') = 1
          )

RAISEERROR用户自定义错误

处理存储过程中的错误

使用PRINT语句显示错误信息
错误信息是临时的，只能显示给用户
RAISEERROR显示用户定义的错误信息时
- 可指定严重级别
- 设置系统变量@@ERROR
- 记录所发生的的错误等
RAISEERROR语句的用法如下：
- RAISEERROR(msg_id|msg_str,severity, state WITH option[，…… n])
- msg_id：在sysmessage系统表中指定用户定义错误信息
- msg_str：用户定义的特定信息，最长255个字符
- serverity：定义严重性级别。用户可使用的级别为0-18级
- state：表示错误的状态，1至127之间的值
- option：指示是否将错误记录到服务器错误日志中

**例题：**完善上例。调用存储过程时，如果传入的及格线参数不在0~100之间时，将弹出错误警告，终止存储过程的执行。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-poeaLnOf-1656908913585)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655828780068.png)]


Create procedure proc_query student
	@score int =60,
	@subName varchar(20)
as
	declare @subjectNO int,
	declare @date datetime,
	declare @avg float
	
	if @score <= 0 or @score > 100	
		begin
			raiseerror('及格分数线必须在0-100之前',16,1)
		end
	else
	select @subjectNo = subject from subject where subjectName = @subName	
	select @date = max(Examdate) from result where subjectno = @subjectNo	
	select AVG(result) from result 
					where subjectNo = @subjectNo and examdate = @date
    if(@avg > 70)
    	print '优秀'
    else
    	print '较差'
    select student.* from student 
    	inner join result on student.studentNo = result.studentNo
    	where subjectNo = @subjectNo 
    	and examdate = @date and studentResult < @score
go

create procedure proc_1
	@gradeid int output,
	@subjectid int output,
	@gradename varchar(20),
	@subjectname varchar(20),
	@classhour int -36
as
	declare @errorsum int 
	set @errorsum=0

  if len(ltrim(rtrim(@gradename))) = 0 or len(ltrim(rtrim(@subjectname)))
        begin
            Raiseerror('年级、课程名称不能为空',16,1)
            return
        end
  else
        begin
        	begin transaction
            if exists(select * from grade where gradename = @gradename)
                begin
                    select @gradeid = gradeid from grade where gradename = @gradename
                    insert into subject (subjectname,classour,gradeid) 
                            values(@subjectname,@classhour,@gradeid)
                    set @errorsum = @errorsum + @@error
                    set @subjectid = @@Identity
                end
            else
                begin
                    insert into grade(gradename) values(@gradename)
                    set @errorsum = @errorsum + @@error
                    set @gradeid=@@Identity
                    set @subjectid = @@Identity
                end
             if @Errorsum = 0
             	commit transaction
             else
             	rollback
        end
go

练习

指出下列语句的错误：

--创建存储过程
Create procedure proc_stuinfo
	@stuName varchar(10),
	@m int output,
	@stuAge=18 int   --错误，正确写法@stuAge int = 18 
	AS
		--……代码略
	GO
--调用存储过程
--根据上面代码，要传递三个变量的值分别是@stuName 必传，@m 必传 output类型，@stuAge 选传
Declare @s int
Exec proc_stuinfo '张三',@s,30 --没问题
Exec proc_stuinfo '张三'，@s   --没有指定@s的类型,应该声明是输出参数，正确写法：'张三',@s output
Exec proc_stuinfo 
	@stuAge=22,@stuName='李四',@m=@s output --正确，指定接收的变量进行传值

触发器

了解为什么需要触发器

理解触发器的工作原理

掌握如何使用inserted表和deleted表

掌握如何创建

INSERT触发器
UPDATE触发器
DELETE触发器

为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统

例：当账户信息表发生交易时，交易信息表就要同步进行更新

一旦A表发生变动，B表就要同步进行变动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tjOioNxB-1656908913585)(C:\Users\Admin\AppData\Roaming\Typora\typora-user-images\1655831040760.png)]

触发器特征：

它是一种特殊的存储过程
也具备事务的功能
它能在夺标之间执行特殊的业务规则

什么是触发器

触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
触发器通常用于强制业务规则
触发器是一种高级约束，可以定义比用CHECK约束更为复杂的约束
- 可执行复杂的SQL语句（if/while/case)
- 可饮用其它表中的列
触发器定义在特定的表上，与表相关
自动触发执行
不能直接调用
是一个事务（可回滚）

DELETE触发器

INSERT触发器

UPDATE触发器

inserted表和deleted表

触发器触发时
- 系统自动在内存中创建deleted表或inserted表
- 只读、不允许修改；触发器执行完成后，自动删除
inserted表
- 临时保存了插入或更新后的记录行
- 可以从inserted表中检查插入的数据是否满足业务需求
- 如果不满足，则向用户报告错误消息，并回滚插入操作
deleted表
- 临时保存了删除或更新前的记录行
- 可以从deleted表中检查被删除的数据是否满足业务需求
- 如果不满足，则向用户报告错误消息，并回滚插入操作

修改操作	inserted表	deleted表
增加（INSERT）记录	存放新增的记录	-----
删除（DELETE）记录	------	存放被删除的记录
修改（UPDATE）记录	存放更新后的记录	存放更新前的记录

创建触发器的语法：

//insert触发器，inserted表中存放新增的记录
Create trigger tigger_name
on table_name
[with encryption]
for [delete|insert|update]
as
	--sql语句 
go


create trigger tri_tansinfo_insert
on tansinfo
for insert 
as
	declare @cardid varchar(50)
	declare @transtype varchar(10)
	declare @transmoney float
	
	select @cardid=cardid,@transtype=transtype,@transmoney=transmoney
		from inserted
	if(@transtype = '支取')
		begin
			update bank set balance = balance - @transmoney 
				where cardid = @cardid
		end
	else
		begin
			update bank set balance = balance + @transmoney
		end
		
go


//delete触发器，deleted表中存放删除的记录
Create trigger trig_delete_transinfo
ON transinfo
for delete
as
	print '开始备份数据，请稍后……'
	if not exist(select * from sysobjects where name = 'backupTable')
		select * into backupTable from deleted
	else
		insert into backupTable select * from deleted
	print '数据备份成功，备份表中的数据为：'
	select * from backupTable
go

UPDATE触发器练习

//跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示
//transinfo表与bank表
//	分析:1.在bank表上创建update触发器 
//		2.修改前的数据可以从deleted表中获取 
//		3.修改后的数据可以从inserted表中获取
Create trigger trig_update_bank
on bank
for update
as
	declare @beforeMoney Money,@afterMoney Money
	select @beforeMoney = currentMoney from deleted
	select @afterMoney = currentMoney from inserted
	if ABS(@afterMoney - @beforeMoney)>20000
		begin
			print '交易金额：' 
				+ convert(varchar(8), ABS(@afterMoney - @beforeMoney))
			Raiseerror('每笔交易不能超过2万元,交易失败',16,1)
			rollback transaction
		end
go

列级触发器练习

//对某一列进行修改时，可触发
Create trigger trig_update_transinfo
on transinfo
for update
as
	if update(transDate)
		begin
			print '交易失败'
			Raiseerror('安全警告：交易日期不能修改,由系统自动产生',16,1)
			rollback transaction
		end
go
		from inserted
	if(@transtype = '支取')
		begin
			update bank set balance = balance - @transmoney 
				where cardid = @cardid
		end
	else
		begin
			update bank set balance = balance + @transmoney
		end
		
go


//delete触发器，deleted表中存放删除的记录
Create trigger trig_delete_transinfo
ON transinfo
for delete
as
	print '开始备份数据，请稍后……'
	if not exist(select * from sysobjects where name = 'backupTable')
		select * into backupTable from deleted
	else
		insert into backupTable select * from deleted
	print '数据备份成功，备份表中的数据为：'
	select * from backupTable
go

UPDATE触发器练习

//跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示
//transinfo表与bank表
//	分析:1.在bank表上创建update触发器 
//		2.修改前的数据可以从deleted表中获取 
//		3.修改后的数据可以从inserted表中获取
Create trigger trig_update_bank
on bank
for update
as
	declare @beforeMoney Money,@afterMoney Money
	select @beforeMoney = currentMoney from deleted
	select @afterMoney = currentMoney from inserted
	if ABS(@afterMoney - @beforeMoney)>20000
		begin
			print '交易金额：' 
				+ convert(varchar(8), ABS(@afterMoney - @beforeMoney))
			Raiseerror('每笔交易不能超过2万元,交易失败',16,1)
			rollback transaction
		end
go

列级触发器练习

//对某一列进行修改时，可触发
Create trigger trig_update_transinfo
on transinfo
for update
as
	if update(transDate)
		begin
			print '交易失败'
			Raiseerror('安全警告：交易日期不能修改,由系统自动产生',16,1)
			rollback transaction
		end
go

更多推荐

SQL学习笔记(完整)

SQL学习笔记(完整)

简述数据库完整性及其作用

简述实现数据库操作的语句以及语法

常用的聚合函数

表连接分为哪几种类型

了解设计数据库的步骤

SQL语句分为四种

SqlServer数据库由哪几种文件组成？

创建SqlServer数据库时，需要指定哪些属性？

创建数据库的语法

使用CREATE DATABASE语句创建数据库MySchool，要求：

使用CREATE DATABASE语句创建数据库Employee，要求：

如果SQL Server中已存在数据库MySchool，运行下列语句，会出现什么问题？

删除数据库

使用SQL语句创建数据库

简述创建表的基本步骤

创建表的语法

创建表Subject

创建表

数据完整性有哪几种？各自作用是什么？

使用变量

逻辑控制语句

掌握简单子查询的用法

掌握IN子查询的用法

掌握EXISTS子查询的用法

应用SQL进行综合查询

事务

为什么需要事务

使用SQL语句管理事务

事务分类

视图

视图的作用

如何创建视图

索引

使用SQL语句创建索引

经验

存储过程

存储过程的优点

常用的系统存储过程

基本语法

定义存储过程的语法

创建存储过程

删除存储过程

存储过程参数

RAISEERROR用户自定义错误

练习

触发器

什么是触发器

DELETE触发器

INSERT触发器

UPDATE触发器

相关文章

发布评论取消回复

最近发表

热门文章

标签列表