SQL上课笔记

上课

游标

声明

DECLARE StuCur SCROLL CURSOR --创建一个游标
-- SCROLL 动态游标，可前进可后退
-- 不写SCROLL 静态游标，只能一步一步前进，不能后退
FOR SELECT * FROM student
FOR UPDATE OF email
--锁字段 也就是从你select语句执行后 表的email 字段被锁定 别人将不能进行修改操作 直到你进行update commit后才能修改
DECLARE @学号 char(10) @姓名char(10)@...--定义一些变量，用来导出游标所指数据
--@xx 变量 @@xx 系统变量

打开游标


OPEN stucur
IF @@ERROR=0 --若成功打开一个游标
   PRINT '共有'+ CONVERT(VARCHAR(3),@@cursor_rows)+'个学生'
-- @@cursor_rows 游标所指表的总行数
-- 初始游标指向表的顶端，需要下移一行才有数据
FETCH NEXT FROM stucur INTO @学号,@姓名,@...--导出游标的数据
--有多少属性就导出多少
PRINT @...

修改

FETCH NEXT FROM stucur --静态只能用next
--动态可以用PRIOR ,NEXT ,FIRST ,LAST ,ABSOLUTE ,RELATIVELY
UPDATE student
SET email='..' WHERE CURRENT OF StuCur

关闭释放游标

CLOSE cursor
DEALLOCATE stucur

创建表

从已有的表创建表

只要表结构不要数据 :
既要表结构又要数据 :

--1
select * into newtable from oldtable where 1=2
--2
select * into newtable from oldtable

存储过程

无参数

--利用存储过程，查询所有学生信息
USE teaching
GO
IF EXISTS(
    SELECT * FROM SYSOBJECTS
        WHERE name='pro1' AND type='p')
    DROP PROCEDURE pro1
    GO
CREATE PROCEDURE pro1
AS SELECT * FROM student
GO
EXEC pro1;

带输入参数的存储过程

例：创建一个查找某同学信息的存储过程

--1.打开DB
USE teaching
GO
--2.查重
IF EXISTS(
    SELECT * FROM SYSOBJECTS
        WHERE name='pro2' AND type='P')--P必须大写
    DROP PROCEDURE pro2
    GO
--3.创建
CREATE PROCEDURE pro2 @stuname char(10)
--输入参数类型与表中一致:char(10)
AS SELECT * FROM student
WHERE sname LIKE @stuname
GO
--4.调用
EXEC pro2 'Alice';
-- or:
--EXEC pro2 @stuname='Alice';

带输入输出 eg: 查找某同学学号和tel

--1.打开DB
 USE teaching
 GO
 --2.查重
 IF EXISTS(
     SELECT * FROM SYSOBJECTS
         WHERE name='pro3' AND type='P')--P必须大写
     DROP PROCEDURE pro3
     GO
 --3.创建
 CREATE PROCEDURE pro3 @stuname nchar(8),@stuid nchar(10) OUTPUT, 	@stutel char(12) OUTPUT
 AS 
 SELECT @stuid=studentno,@stutel=phone --字段名
 FROM student
 WHERE sname = @stuname
 GO

 --4.调用
 DECLARE @sn nchar(10),@st char(12)
 EXEC pro3 '梁欣',@SN OUTPUT,@ST OUTPUT
 PRINT '梁欣的学号和电话分别为：'+@sn+','+@st;

使用return返回pro的值，只能是整数

使用pro向学生表插入一行记录

--1.
USE teaching
GO 
--2.
  IF EXISTS(
     SELECT * FROM SYSOBJECTS
         WHERE name='pro4' AND type='P')--P必须大写
     DROP PROCEDURE pro4
     GO
--3.
CREATE PROCEDURE pro4 @stuid nchar(12),
					@stuname nchar(8),@stusex 
nchar(1),@stuemail nchar(20)--...与原表一致
					,@flag int OUTPUT
AS
	BEGIN --代码过长时用begin and 括起来
		SET @flag=1
		IF EXISTS (SELECT *
				  FROM student
                  WHERE studentno=@stuid)
           SET @flag=0
        ELSE--end if
        	INSERT INTO student(studentno,
                                sname,
                                sex,
                               	email)
            VALUES(@stuid,@stuname,@stusex,@stuemail)
    END   --end begin    
--4.调用
DECLARE @flag int
EXEC pro4 '2020001','Alice','女','alice@163.com',--...
		  @flag OUTPUT
IF @flag=0 
	PRINT '已存在学生，无法插入'
ELSE 
	PRINT '插入成功'

向学生信息表删除一行记录

--1.
USE teaching
GO 
--2.
  IF EXISTS(
     SELECT * FROM SYSOBJECTS
         WHERE name='pro5' AND type='P')--P必须大写
     DROP PROCEDURE pro5
     GO
--3.创建
CREATE PROCEDURE pro5 @stuid nchar(12),
					@flag int OUTPUT
AS
	BEGIN --代码过长时用begin and 括起来
		SET @flag=1
		IF EXISTS (SELECT *
				  FROM score
                  WHERE studentno=@stuid)
           --有选课记录不能删除
           SET @flag=0
        ELSE--end if
        	DELETE FROM student
        	WHERE studentno=@stuid
    END   --end begin    
RETURN    
--4.调用
DECLARE @flag int
EXEC pro5 '2020001',
		  @flag OUTPUT
IF @flag=0 
	PRINT '该生有选课，无法删除'
ELSE 
	PRINT '删除成功'

函数

标量函数

-- 函数
--1

IF EXISTS(SELECT * 
		  FROM SYSOBJECTS
		  WHERE NAME='fun1' AND TYPE='FN'
		  )
	DROP FUNCTION func1
GO
CREATE FUNCTION dbo.func1(@stuSex nchar(1)) --函数的参数有括号
RETURNS int
AS 
BEGIN
	DECLARE @num int
	SET @num=0
	SELECT @num=COUNT(*) FROM student
	WHERE sex=@stuSex
	RETURN @num --区别3：return在begin end里
END
GO
--调用
DECLARE @num int
SET @num=dbo.func1('男') -- 函数直接赋值调用，无需exec
PRINT('男生人数为：'+cast(@num AS char(2)))
GO

内嵌表值函数

--2.
-- 输出某同学的学号、姓名、成绩
IF EXISTS(SELECT * 
		  FROM SYSOBJECTS
		  WHERE NAME='func2' AND TYPE='IF'
		  )
	DROP FUNCTION func2
GO

CREATE FUNCTION dbo.func2(@stuName AS nchar(8)) --函数的参数有括号
RETURNS TABLE
AS 
RETURN (
	SELECT student.studentno,sname,courseno,final
	FROM student,score
	WHERE student.studentno=score.studentno
		AND student.sname LIKE @stuName 
)
GO

--调用
SELECT * 
FROM dbo.func2('韩吟秋')
GO

多语句表值函数


--3.
--函数前不能用use
IF EXISTS 
	(SELECT * 
	FROM SYSOBJECTS
	WHERE NAME='func3' AND TYPE='TF'
	)
	DROP FUNCTION func3
GO

--写函数统计某性别人数
CREATE FUNCTION dbo.func3(@stuNAME AS nchar(8)) 
RETURNS @stuTable table (学号 nchar(10),
						 课程号 nchar(10),
						 成绩 numeric(6,2))
AS 
BEGIN
	INSERT @stuTable --将select结果放入新表
	SELECT studentno,courseno,final
	FROM score
	WHERE studentno IN (SELECT studentno
						FROM student
						WHERE sname LIKE @stuName)
RETURN 
END
GO

--调用
SELECT * 
FROM dbo.func3('韩吟秋')
GO

事务

BEGIN TRANSACTION tran_scode
UPDATE SC SET S#=‘201404001’ WHERE S#=‘201403001’ 
    IF @ERROR<>0

数据库安全控制

授权用户
非授权用户

授权管理*

GRANT SELECT,INSERT,UPDATE 
ON S
TO xia —-授权给用户xia

授权回收*

REVOKE UPDATE(Sno)
ON TABLE Student 
FROM U4;

完整性约束

数据的完整性指数据正确性、有效性、相容性。如性别只取男、女；学号必须唯一。

域完整性约束

作用对象：列
指给定列的输入有效性。
强制域有效性的方法：限制类型、格式或可能值的范围。

GRADE CHECK

alter table Student 
add constraint unique_c unique (sname);

alter table Student 
add constraint check_sex check(Sex in ('男','女'));

add constraint default_sex default('男') for Sex;

实体完整性

将行定义为特定表的唯一实体。
作用对象为列
强制表的标识符列或主键完整性

主键（自动）

参照完整性

作用对象：关系。
输入或删除记录时，参照完整性保持表之间已定义的关系。

触发器

--触发器
USE teaching
GO
IF EXISTS(SELECT *
		  FROM SYSOBJECTS
		  WHERE NAME='tri1'
		  AND TYPE='TR')
	DROP TRIGGER tri1
	GO

CREATE TRIGGER tri1
ON score
for insert,update
AS
	DECLARE @x int
	SELECT @x=final
	FROM INSERTED
	IF @x >=0 and @x<=100
	BEGIN 
		PRINT ('插入完成')
		RETURN  
	END
	ELSE 
		BEGIN 
            PRINT('分数不在0-100的')
            rollback --(transaction)
		END
	GO

索引

聚族索引Clustered
非聚族索引Nonclustered
唯一索引Unique

--创建
--若不指定，默认为nonclustered
create clustered index StuSco_Index
on SC(Cno ASC,Score DESC);

--unique既可以是clustered也可以是nonclustered
create unique clustered index Stuno_Index
on Student(Sno ASC);


--删除索引
drop index SC.StuSco_Index

SQL上课

zihanlizi

2022/04/27

上课

游标

创建表

存储过程

函数

事务

数据库安全控制

授权管理*

授权回收*

完整性约束

域完整性约束

实体完整性

参照完整性

触发器

索引