数据库课堂笔记三(SQL语句)-----2

ANY或ALL	含义
>ANY	大于子查询中的某个值
>ALL	大于子查询中的所有值
<ANY	小于子查询中的某个值
<ALL	小于子查询中的所有值
>=ANY	大于等于子查询中的某个值
>=ALL	大于等于子查询中的所有值
<=ANY	小于等于子查询中的某个值
<=ALL	小于等于子查询中的所有值
=ANY	等于子查询中的某个值
=ALL	等于子查询中的所有值
！=(或<>)ANY	小于等于子查询中的某个值
！=(或<>)ALL	小于等于子查询中的所有值

select file1,...,filen
from bi_name
where m(选择条件) <any(
                     select m
					 from bi_name
					 where 选择条件）
and 选择条件;
-- 查询中也可以用聚集函数

ANY或ALL所对应的关系

	=	<>或！=	<	<=	>	>=
ANY	IN	–	<MAX	<=MAX	>MAX	>=MAX
ALL	–	NOT IN	<MIN	<=MIN	>MIN	>=MIN

带有EXISIT的子查询

select file1,...,filen
from bi_name
where exisit (
                     select m
					 from bi_name
					 where 选择条件）
and 选择条件;
--  带有EXISIT的子查询不返回任何数据，只产生逻辑值true或逻辑值false

视图

-- 创建视图
create view <视图名>[<列名>,<列名>,...]
AS <子查询>
[with check option];
-- with check option表示对视图进行update、delete、insert操作时要保证更新、删除、插入
-- 的行满足视图定义的中的谓语条件（即子查询的条件表达式）
-- 其中子查询可以是任意的select语句，是可以含有order by子语句和distinct短语
eg:
mysql> create view view_stu1 as
    -> select student.SNo,student.SName,sc.Score
    -> from student,sc
    -> where sc.Score between 60 and 90
    -> and sc.SNo=student.SNo
    -> with check option;
 -- 视图得update、delete、insert是基于基本表的,视图是基本表的子集
 
-- 删除视图
drop view<视图名> ;
--查询视图
select file1,file2
from <视图名>
where 选择条件;

视图和派生表的区别

派生表只是在执行语句是临时定义的，语句执行后立即删除
视图是永久存在的

更新视图

 update <视图名> set file = 要更新的数据;
 insert into <视图名> values ();
 delete from <视图名> where 选择条件;

视图的作用

1.简化用户操作
2.使用户从多角度看待同一数据
3.提供了一定的逻辑独立性
4.对机密数据提供安全保护
5.适当的利用视图可以更清晰的表达数据查询

存储过程

-- 创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body;
其中，proc_parameter的参数如下：
[ IN | OUT | INOUT ] param_name type

--调用存储过程
CALL sp_name([parameter[,...]]);

-- eg:创建无参数一个存储过程，返回学号为了“16041101”选修的号课程及成绩
mysql> delimiter |
mysql> create procedure sn()
    -> begin
    -> select SNo,CNo,Score
    -> from sc
    -> where SNo in(
    ->               select SNo
    ->               from student
    ->               where SSex='男');
    -> end |
-- delimiter使用来定义结束符的，mysql默认的结束符为";"

-- 调用无参数存储过程
mysql> delimiter ;
mysql> call sn();

-- 创建一个有参数的存储过程，实现查询某个专业女学生名单
mysql> DELIMITER //
mysql> CREATE PROCEDURE hjl_su(IN ICName varchar(8))
 BEGIN
    SELECT student.SNo,SName
   FROM sc,student
   WHERE CNo=(SELECT CNo
              FROM course
               WHERE CName='数据结构')
     AND sc.SNo=student.SNo AND SSex='女' AND major=ICName;
 END//

-- 调用有参数存储过程
mysql> DELIMITER ;
mysql> CALL hjl_su('计算机');

自定义函数

--创建自定义函数
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
[characteristic ...] routine_body;
--调用函数
SELECT sp_name ([func_parameter[,...]]);

eg:
-- 创建一个无参数的自定义函数，返回student表中学生的数目
mysql> delimiter -
mysql> create function s_HJLstu()
    ->  returns int
    ->   begin 
    ->   return (select count(*)  from student);
    ->  end -

mysql> delimiter ;
mysql> select s_HJLstu() as '人数';



-- 创建一个参数的自定义函数，返回某个学生的姓名。
mysql> delimiter - 
mysql> 	CREATE  FUNCTION name_hjl (no1 char(10))
    -> RETURNS VARCHAR ( 10 )
    -> BEGIN
    -> 	DECLARE NAME VARCHAR ( 8 );
    -> SELECT SName INTO NAME
    -> 	    FROM student
    -> 	    WHERE SNo = no1;
    -> RETURN NAME;
    -> END -
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select name_hjl('16041101');

游标

--创建一个存储过程，通过光标实现查询每一个学生的学号、姓名。
delimiter -
CREATE PROCEDURE pro1_hjl () #创建一个存储过程
BEGIN

			DECLARE SSCNO CHAR (10) ; #创建一个char型局部变量SSCNO
			DECLARE SSNAME CHAR (10) ;#创建一个char型局部变量SSNAME
			DECLARE found boolean DEFAULT TRUE ;#创建一个布尔型的局部变量SSCNO
			DECLARE my CURSOR FOR  # 声明光标
					SELECT SNo,SName FROM student ;
			DECLARE CONTINUE HANDLER FOR NOT found SET found = FALSE ; #查询异常处理
			OPEN my ; #打开光标
			FETCH next FROM my INTO SSCNO,SSNAME ; #从第一行记录提取赋值给SSCNO和SSNAME两个变量
			SELECT SSCNO,SSNAME ; #输出SSCNO和SSNAME变量中的数据
			WHILE found DO #用while循环提取多行数据
						FETCH next FROM my INTO SSCNO,SSNAME ; #提取下一行数据，赋值给SSCNO和SSNAME
						if found=TRUE THEN   #foun为true 则输出
               SELECT SSCNO,SSNAME;
            END IF ;
      END WHILE;#结束循环
			CLOSE my ;#关闭光标
			SELECT SNo,SName FROM student ;#输出结果集
END-


delimiter ;
CALL pro1_hjl ();

触发器

delimiter - 
	CREATE TRIGGER HJL_judgescore#对sc表进行插入时激活触发器
	BEFORE INSERT ON sc#before事件触发器
	FOR EACH ROW#行级触发器
	BEGIN#定义触发动作体
	IF( new.score<0||new.score>100) THEN#行级触发器，可在过程体中
	   set new.score =NULL; #不符合的新值置为空
	 END IF;
	 END –

-- 触发语句：
mysql> mysql> insert into sc
    -> values('18043222','00009',101);

mysql> insert into sc
    -> values('18043222','00005',-5);

运行结果如图