从Select出发认识SQL语句

灌了桶盐的鱼

已于 2022-05-08 09:31:37 修改

阅读量701

点赞数 3

文章标签： sql 数据库 database

于 2022-05-07 17:14:18 首次发布

本文链接：https://blog.csdn.net/qq_45932673/article/details/124629950

版权

说明：本文是作为数据库相关知识进行的笔记，旨在于同学习数据库的读者朋友分享知识等。如若存在不足之处请大家指点一二。

前言

SQL语句的执行顺序真的是从Select开始的吗？

当我们遇到一些问题，比如查询某某学生信息或者查询某个系中学生成绩平均分，这时候的SQL语句书写都是SELECT—FROM—WHERE结构，的确，对于SQL语句而言，是遵循这样一个结构的，后面我们也会介绍到，像这样的结构我们称为块。
由此我们会有这样直观想法，SQL是从SELECT开始书写的，那按着本有的脚本知识基础，SQL是从SELECT开始的吗？显然，这是值得我们探讨的。在SQL中，SELECT和FROM是必不可少的选项，其语法格式为SELECT [DISDINCT|ALL] select_list FROM table.[]的内容是可选的，默认情况下是all，DISTINCT用于去除查询结果集中的重复值所在的记录；关键字ALL用于返回查询结果集中的全部记录，且SELECT子句和SQL的其他子句并无差别，都不区分大小写。FROM子句决定了要从哪个（些）数据源(如表)查询数据。到这，我们可以大致清楚SELECT - FROM 的语义，即从某个(些)数据源中选择得到一个结果集，并将其返回所查询的结果集记录。
综上可知，SQL的执行其实并非从SELECT开始的，更进一步，再后文涉及到其他子句时，我们就知晓SQL语句的执行过程了，笔者在这先给出SQL的执行顺序，以便有个印象。
SQL语句执行顺序:FROM/JION->WHERE->GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT。
对应中文翻译为:获取数据->根据条件进行相关过滤->分组 -> 分组过滤 -> 筛选字段 -> 排序 -> 分页
LIMIT分页子句本次并未涉及，所以可以先不作介绍。

关于Select的说明

SELECT其实是一条计算机SQL数据库中中常用的函数。当然在其他语言中它是个选择对象，如在网页HTML中就是这样的。SELECT 将从一个或更多表中返回记录行，处理过程同上文介绍无异。
当我们清楚了SQL语句的处理过程后，可能读者朋友会疑问，既然SQL并不是从SELECT开始执行，标题不是在误导人吗？在笔者看来，遵从SQL的语法规则，本文起题与执行顺序并不违背，所以对于SQL的学习，简单而言，我们是从SELECT开始的。

数据准备

前言：> 在学习SQL语句之前需要有一定数据库基本知识，了解DB,DBNS,DBS等，同时了解数据库中什么是关系，数据关系及模型等。有一定的数据结构基础，要想深入学习SQL语言，还应具备一定的编程语言。总而言之，了解上述知识当然是锦上添花，若从未接触过以上信息，我也希望本文能带给您一定的用途。
初始认识SQL：> SQL是一种关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。
**表的定义：>**为方便本文使用SQL语句进行相应操作，先建立三个表，分别为Student表，SC表和Course表。语句为

//学生表Student
create table student 
(sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);
//创建课程表
create table course
(cno char(4) primary key,
cname char(40),
cpno char(4),
ccredit smallint,
foreign key (cpno) references course(cno)
);
//创建学生选课表
create table sc
(sno char(9),
cno char(4),
grade smallint,
birth date,
primary key (sno,cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
);
//插入数据
insert into student
values ('200215121','李勇','男',20,'CS');
insert into course
values ('1','数据库',null,4);
insert into sc
values ('200215121','1',92);

大概表的内容如下：> 依次是学生表、课程表和成绩表。（表中数据是随意取的，如有冒犯到相关信息对应的本人，请见谅）

基本表的相关操作 需要注意的是，SQL语句不区分大小写！
1.修改基本表：ALTER TABLE <表名> +相应操作，如：

// 向Student表添加“入学时间”序列，其数据类型为日期型。
ALTER TABLE Student ADD enter DATE；
//将年龄的数据类型由字符型改为整型
ALTER TABLE Student ALTER COLUMN Sage INT；
//增加课程名称必须唯一的条件约束
ALTER TABLE Course ADD UNIQUE(Cname);

2.删除基本表：DROP TABLE <表名> [RESTRICT | CASCADE];
RESTRICT | CASCADE这两个分别代表有限制条件和无限制条件的删除方式。前者删除时不能有关联对象，如视图，触发器等，后者在删除基本表时会把关联表一并删除，所以读者在使用时需要注意！
如：删除Studen表，用DROP TABLE Student RESTRICT显然是不成功的，因为学生表中的Sno被成绩表SC参照了，再如有以下定义(具体格式后面了解)：

//在Stduent基础上建立视图
CREATE VIEW S_VIEW
AS 
SELECT Sno,Sname
from Student
where Sdept = 'IS';

此时用上一条删除语句会出现问题，可删除条件更改为CASCADE，但这样会时Student表关联的其他对象一并删除，所以需要慎重操作。
3.认识索引对于大量数据的操作而言，建立索引是一种极其加快查询速度的有效手段。当然，对于索引的类型也是相当丰富的，诸如散列索引，顺序索引等，感兴趣的朋友可以文外了解，这里主要介绍以下关于索引的应用。
索引的建立 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名>[<次序>] [,<列名>[次序]…])
其中次序用ASC和DESC表示，前者表示升序，后者表示降序，一般默认前者。UNIQUE表示建立唯一索引，CLUSTER表示建立聚簇索引。这里不详细介绍，后面涉及再议。这里举个简单例子：为成绩表建立唯一索引，课程号降序，学号升序显示

// 为成绩表建立唯一索引，课程号降序，学号升序显示
CREATE UNIQUE INDEX Scon ON sc(Sno ASC,Cno DESC);

索引的修改
一般格式为ALTER INDEX <旧索引名> RENAME TO <新索引名>
删除索引
一般格式为：DROP INDEX <索引名>

数据查询

对于数据操作，直接上手给问题，撕代码，不再给出一般格式。

1.单表查询

选择表中若干列

在单表查询过程中，选择表中的全部或部分列其实就是关系代数中的投影运算。下面来看具体案例：

1.查询全体学生的姓名、学号。
2.查询所有学生的详细信息
3.查询全体学生姓名及出生年份，属性列可以是表达式！

//1.查询全体学生的姓名、学号。
SELECT Sname,Sno
from student;
//2.查询所有学生的详细信息（*表示查询所有序列）
select *
from student;
//3.查询全体学生姓名及出生年份（用表达式查询）
select Sname,2022 - Sage

选择表中若干元组

1.查询选修了课程的学生学号
2.查询所有年龄在20岁以下的学生姓名及年龄。
3.查询全体计算机系的学生名单。
4.查询所有考试不及格的学生学号。
5.查询年龄在20~23的学生姓名、系和年龄。
6.查询计算机系、信息系的学生姓名和性别。
7.查询既不是计算机系，也不是信息系的学生姓名和系
8.查询学号为2021005的学生信息。
9.查询所有王姓的学生信息。
10.查询“李”姓且全名是两个名字的学生信息。
11.查询学生姓名中第二个字为“敏”的学生信息。
12.查询所有不姓李学生姓名。
13.查询java_web课程的课程号和学分。
14.查询有课程记录单缺少考试成绩的学生信息。
15.查询所有有成绩的学生学号和课程号。
16.查询查询计算机系且年龄小于20岁的学生信息。

//1.查询选修了课程的学生学号，这里需要去掉重复行！
//没有DISTINCT 默认为ALL，即保留结果表中取值重复的行
select DISTINCT Sno
from sc;
//2.查询所有年龄在20岁以下的学生姓名及年龄。
select  sname,sno
from student
where sage <20;
//3.查询全体计算机系的学生名单。
select  sname
from student 
where sdept = 'CS';
//4.查询所有考试不及格的学生学号。
//有可能同一个人出现多门课程不及格情况，所以用distinct
select distinct Sno
from sc
where grade < 60;

//5.查询年龄在20~23的学生姓名、系和年龄。
select sname,sdept,sage
from student
where sage between 20 and 23;
//6.查询计算机系、信息系的学生姓名和性别。
select sname,ssex
from student
where sdept in('CS','MA','IS');
//实际上，谓词IN 可以等价于多个OR运算符的缩写。所以此列可更改为
select sname,ssex
from student
where sdept='CS' or sdept='MA' or sdept='IS';
//7.查询既不是计算机系，也不是信息系的学生姓名和系
select sname,ssex
from student
where sdept not in('CS','MA','IS');
//8.查询学号为2021005的学生信息。
select *
from student
where sno like '2021005';
//最后一句等价于 where sno = '2021005';

//9.查询所有王姓的学生信息。
select *
from student
where sname like '王%';
//10.查询“李”姓且全名是两个名字的学生信息。
select sname
from student
where sname like '李_';
//11.查询学生姓名中第二个字为“敏”的学生信息。
select *
from student
where sname like '_敏%'.;
//12.查询所有不姓李学生姓名。
select sname
from student'
where sname not like '李%';
//13.查询java_web课程的课程号和学分。
//这里需要用到转义字符。
select cno,ccredit
from course
where cname like 'java\_web' ESCAPE '\';
//14.查询有课程记录但缺少考试成绩的学生信息。
select sno,cno
from sc
where grade is NULL;	//这里的is不能用=代替！
//15.查询所有有成绩的学生学号和课程号。
select sno,cno
from sc
where grade is not NULL;	
//16.查询查询计算机系且年龄小于20岁的学生信息。
select sname,sage
from student
where sdept='CS' and sage < 20;

ORDER BY子句、聚集函数和GROUP BY 子句

需要说明的是order by 子句和group by子句。前者是对某一列的结果值进行升序或降序排列，默认为升序；后者是将结果按某一列的值进行分组，该属性列的值相等为一组，通常在每组中用聚集函数，若其带having短语，则只有满足指定条件的组才能予以输出。***注意having和where的使用区别,同时where子句中不能用聚集函数！***。
案例如下：
1.查询选修了3号课程的学生的学号及其成绩，查询结构按降序排列。
2.查询学生总人数。
3.查询选修了课程的学生人数。
4.计算选修了1号课程的学生平均分。
5.查询选修了3号课程的最高成绩。
6.查询2021001的选修课程的总学分。
7.求各个课程号及相应的选课人数。
8.查询选修了三门以上课程的学生学号。
9.查询平均成绩大于85分的学生学号和平均成绩。

//1.查询选修了3号课程的学生的学号及其成绩，查询结构按降序排列。
//如果有空值，排序时的次序由系统具体实现来决定，默认空值最大
SELECT Sno,GRADE
FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC;
//2.查询学生总人数。
select COUNT(*)
FROM Student;
//3.查询选修了课程的学生人数。
SELECT COUNT(DINSTINCT Sno)
FROM SC;
//4.计算选修了1号课程的学生平均分。
SELECTC AVG(Grade)
FROM SC
WHERE Cno = '1';
//5.查询选修了3号课程的最高成绩。
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
//6.查询2021001的选修课程的总学分。
SELECT SUM(Ccredit)
FROM SC,COURSE
WHERE Sno='2021001' and SC.Cno=Course.cno;
//7.求各个课程号及相应的选课人数。
SELECT Cno,COUNT(sno)
FROM SC
GROUP BY Cno;
//8.查询选修了三门以上课程的学生学号。
SELECT Sno 
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
//9.查询平均成绩大于85分的学生学号和平均成绩。
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=85;

2.连接查询

上文中大部分操作仅针对单表查询，下面将进行两个表以上的查询操作。
连接是从两个关系的笛卡尔积中选取属性间满足给的条件的元组。连接运算为等号使称等值连接，而自然连接是特殊的等值连接，它要求两个关系中比较分量必须是同名的属性组，且在结果中把重复属性列去除。在连接过程中被舍弃的元组称为悬浮元祖，下面将用案例来具体实现。
查询每个学生及其选修课程情况

SELECT Student.*,SC.*
FROM Student,SC
WHERE SStudent.Sno = SC.Sno;

上述等值连接语句执行过程如下：
在关系数据库执行连接操作时，首先在表Student中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student表中第一个元组的Sno相等的SC元组，找到后就将Student表的第一个元组与该元组拼接起来，形成结果表中一个元组。SC全部查找完后，再找Student中第二个元组，然后再从头开始扫描SC，逐一查找满足连接条件的元组，找到后就将Student中第二个元组与该元组连接起来，形成结果表中一个元组。重复上述操作，知道Student中全部找完为止，这也是嵌套循环连接的基本思想。
若在等值连接中把目标列中的重复属性列去掉，则为自然连接。如：

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cnno,Grade
FROM Student,SC
WHERE SStudent.Sno = SC.Sno;

除此之外，通常还涉及自身连接和外连接以及多表连接。如：
1.查询每一门课的间接先修课程.
2.查询查询学生基本情况及其选课情况，若某个学生没有选课，则显示NULL。
3.查询每个学生的学号，姓名，选修的课程名和成绩。

//1.查询每一门课的间接先修课程.
SELECT FIRST.Cno,SECEND.Cpno
FROM Course FIRST,Course SECEND
WHERE FIRST.Cpno=SECEND.Cno;
//2.查询查询学生基本情况及其选课情况，若某个学生没有选课，则显示NULL。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Scno,Grade
FROM Student LEFT OUTER JION SC ON (Student.Sno=SC.Sno);
//3.查询每个学生的学号，姓名，选修的课程名和成绩。
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno and SC.Cno = Course.Cno;

3.嵌套查询

一个SELECT-FROM-WHERE 语句称为一个查询块，将一个查询块嵌套在另一个查询块里边儿的WHERE子句或HAVING短语的条件中就称为嵌套查询。
案例如下：
1.查询与刘晨在同一个系的学习的学生。
2.查询选修了信息系统课程的学生姓名和学号。
3.找出每个学生超过他自己选修课程平均成绩的课程号。
4.查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄。
5.查询非计算机中比计算机系所有学生年龄都小的学生姓名及年龄。
6.查询所有选修了1号课程的学生姓名。
7.查询没有选修1号课程的学生姓名。
8.查询选修了全部课程的学生姓名。
9.查询至少选修了学生2021001选修的全部课程的学生学号。

//1.查询与刘晨在同一个系的学习的学生。
SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SDEPT IN (
		SELECT SDEPT 
		FROM STUDENT
		WHERE SNAME='刘晨');
		//由于子查询条件不依赖于父查询，故该查询称为不相关子查询
		//相关子查询第3个列子所示
//2.查询选修了信息系统课程的学生姓名和学号。
SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN (
		SELECT SNO
		FROM SC
		WHERE CNO IN (
		SELECT CNO
		FROM COURSE
		WHERE CNAME='信息系统')
		)
		
//3.找出每个学生超过他自己选修课程平均成绩的课程号。
SELECT SNO,CNO
FROM SC X
WHERE GRADE>=(
		SELECT AVG(GRADE)
		FROM SC Y
		WHERE Y.SNO=X.SNO)
		);
//4.查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄。
SELECT SNAME,SAGE
FROM STUDENT
WHERE SAGE<ANY(SELECT SAGE
				FROM STUDENT
				WHERE SDEPT='CS')
	  AND SDEPT <> 'CS'; 
	  //利用聚集函数，上述等价于
SELECT SNAME,SAGE
FROM STUDENT
WHERE SAGE<(SELECT MAX(SAGE)
				FROM STUDENT
				WHERE SDEPT='CS')
	  AND SDEPT <> 'CS'; 
//5.查询非计算机中比计算机系所有学生年龄都小的学生姓名及年龄。
SELECT SNMAE,SAGE
FROM STUDENT
WHERE SAGE<ALL(
			SELECT SAGE
			FROM STUDENT
			WHERE SDEPT='CS')
	  AND  SDEPT <> 'CS'; 
//6.查询所有选修了1号课程的学生姓名。
SELECT SNAME
FROM STUDENT
WHERE EXISTS
		(SELECT *
		FROM SC
		WHERE SNO=STUDENT.SNO AND CNO='1'
		);//带有EXIST谓词的子查询语句不返回任何数据，所以使用*表示，其只产生TRUE OR FALSE；
//7.查询没有选修1号课程的学生姓名。
SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
		(SELECT *
		FROM SC
		WHERE SNO=STUDENT.SNO AND CNO='1'
		);
//8.查询选修了全部课程的学生姓名。
SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
		(SELECT *
		FROM COURSE
		WHERE NOT EXISTS(
				SELECT *
				FROM SC
				WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO)
		);
//9.查询至少选修了学生2021001选修的全部课程的学生学号。
SELECT DINSTINCT SNO
FROM SC SCX
WHERE NOT EXISTS
		(SELECT *
		FROM SC SCY
		WHERE SCY.SNO='2021001' AND
			  NOT EXISTS(
				SELECT *
				FROM SC SCZ
				WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO)
		);

4.集合查询

集合操作主要包括并UNION,交INSERSECT和差EXCEPT等。
案例：
1.查询计算机科学系的学生及年龄不大于19岁的学生。
2.查询选修了1课程或者选修了2号课程的学生。
3.查询计算机科学系的学生与年龄不大于19岁的学生交集。
4.查询既选修课程1又选修课程2的学生。
5.查询计算机科学系的学生与年龄不大于19岁的学生的差集。

//1.查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM STUDENT
WHERE SDEPT='CS'
UNION
SELECT *
FROM STUDENT
WHERE SAGE<19;
//2.查询选修了1课程或者选修了2号课程的学生。
SELECT SNO
FROM SC
WHERE CNO='1'
UNION
SELECT SNO
FROM SC
WHERE CNO='2';
//3.查询计算机科学系的学生与年龄不大于19岁的学生交集。
SELECT *
FROM STUDENT
WHERE SDEPT='CS'
INSERSECT
SELECT *
FROM STUDENT
WHERE SAGE<=19;
//4.查询既选修课程1又选修课程2的学生。
SELECT SNO
FROM SC
WHERE CNO='1'
INSERSECT
SELECT SNO
FROM SC
WHERE CNO='2';
//5.查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM STUDENT
WHERE SDEPT='CS'
EXCEPT
SELECT *
FROM STUDENT
WHERE SAGE<=19;

5.基于派生表的查询

子查询不仅可以出现在WHERE 子句中，也可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
如查询所有选修了1号课程的学生姓名，可以如下：

SELECT  SNAME
FROM STUDENT , ( SELECT  SNO,FROM SC WHERE CNO='1'  )  SCX
WHERE STUDENT.SNO=SCX.SNO;

数据更新

数据更新操作主要有三种：向表中添加若干行数据、修改表中的数据和删除表中数据。
1.插入数据
用INSERT INTO … VALUES…格式
需要注意的是，INTO子句中没有属性列时，新元组在这些列上取空值。
2.插入子查询结果
子查询不仅可以嵌套在SELECT语句中用以构造父查询条件，还可以嵌套在INSERT语句中用以生成要插入的批量数据。
3.修改数据即对数据进行更新操作。使用UPDATE… SET…格式
4.删除数据该语句功能是从指定表中删除满足条件的元组。如果省略WHERE 子句则表示要删除表中全部元组，但表仍在字典中。
案例：
1.将一个新学生元组（学号：2021056，姓名：李三，性别：男，所在系：IS，年龄：18）插入到STUDENT表中。
2.插入一条选课记录（‘2021001’，‘1’）
3.对每个系，求学生的平均年龄，并把结果存入数据库。
4.将学生2021005的年龄改为23岁。
5.将计算机科学系全体学生的成绩置零。
6.删除学号为2021006的学生记录
7.删除计算机科学系所有学生的选课记录。

//1.将一个新学生元组（学号：2021056，姓名：李三，性别：男，所在系：IS，年龄：18）插入到STUDENT表中。
INSERT 
INTO STUDENT(SNO,SNAME,SSEX,SDEPT,SAGE)
VALUES('2021056','李三','男','IS',18); 
//2.插入一条选课记录（‘2021001’，‘1’）
INSERT 
INTO SC(SNO,CNO)
VALUES('20210001','1'); 
//如果没有指出属性名，则写法如下
INSERT 
INTO SC
VALUES('20210001','1',NULL); 
//3.对每个系，求学生的平均年龄，并把结果存入数据库。
//先建一个表，其中存放系名和平均年龄，然后对STUDENT表按系分组求平均年龄，再把系名和平均年龄存入新表中。
CREATE TABLE D_A(
				SDEPT CHAR(15),
				AVG_AGE SMALLINT);
INSERT 
INTO D_A(SDEPT,AVG_AGE)
SELECT SDEPT,AVG(SAGE)
FROM STUDENT
GROUP BY SDEPT;
//4.将学生2021005的年龄改为23岁。
UPDATE STUDENT
SET SAGE = 22
WHERE SNO='2021005'; 
//5.将计算机科学系全体学生的成绩置零。
UPDATE SC
SET GRADE=0
WHERE SNO IN
		(SELECT SNO
		FROM STUDENT
		WHERE SDEPT='CS');
//6.删除学号为2021006的学生记录
DELETE
FROM STUDENT
WHERE SNO='2021006';
//7.删除计算机科学系所有学生的选课记录。
DELETE 
FROM SC
WHERE SNO IN(
			SELECT SNO
			FROM STUDENT
			WHERE SDEPT='CS');

为验证第三题结果，可参见下图对每个系，求学生的平均年龄，并把结果存入数据库。

视图

相信到这里读者朋友对视图已经不陌生了，因为一开始介绍的时候就已经涉及到视图。对于视图而言我们需要知道的是：视图与基本表不同，而是一个虚表。，数据库中只存储视图定义，而不存放实际内容。需要注意的是，组成视图的属性列名或者全部省略，或者全部指定没有第三种选择。
下面介绍视图相关概念。
1、行列子集视图 。若一个视图是从单个基本表导出，且只是去掉了基本表的某些行和列，但保留了主码，则称该类视图为行列子集视图。一般地，此类视图可更新。
2、虚拟列 。在定义视图时可以根据应用需要设置一些派生属性列，由于这些属性列不实际存在于基本表，故称为虚拟列，带虚拟列的视图称为带表达式的视图。
3、分组视图 。用带有聚集函数和group by子句的查询来定义的视图。
4、视图消解 。从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询，这一过程为视图消解。
下面进行案例介绍
1.建立信息学系学生视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
2.建立信息学系选修了1号课程且成绩在90分以上的学生视图。
3.定义一个反映学生出生年份的视图。
4.将学生的学号及平均成绩定义为一个视图。
5.删除视图DA_S。
6.在信息学系学生的视图中找到年龄小于20岁的学生。
7.将信息学系的学生视图IS_STUDENT中学号为2021005的学生姓名改为刘辰。

///1.建立信息学系学生视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_STUDENT
AS
SELECT SNO,SNAME,SAGE
WHERE SDEPT='IS'
WITH CHECK OPTION  //该子句加上以后若对该视图进行插入修改等操作时，数据库系统会自动加上where后的条件。

//2.建立信息学系选修了1号课程且成绩在90分以上的学生视图。
CREATE VIEW IS_S1(SNO,SNAME,GRADE)
AS
SELECT STUDENT.SNO,SNAME,GRADE
FROM STUDENT,SC
WHERE SDEPT='IS' AND STUDENT.SNO=SC.SNO AND SC.CNO='1';

CREATE VIEW IS_S2
AS 
SELECT SNNO,SNAME,GRADE
FROM IS_S1
WHERE GRADE>=90;
//3.定义一个反映学生出生年份的视图。
CREATE VIEW DA_S(SNO,SNAEM,SBIRTH)
AS
SELECT SNO,SNAME,2021-SAGE
FROM STUDENT;
//4.将学生的学号及平均成绩定义为一个视图。
CREATE VIEW S_G(SNO,GAVG)
AS
SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO;
//5.删除视图DA_S。
DROP VIEW DA_S;
//6.在信息学系学生的视图中找到年龄小于20岁的学生。
SELECT SNO,SAGE
FROM IS_STUDENT
WHERE SDEPT='IS' AND SAGE<20;
//7.将信息学系的学生视图IS_STUDENT中学号为2021005的学生姓名改为刘辰。
UPDATE IS_STUDENT
SET SNAME='刘辰'
WHERE SNO='2021005' AND SDEPT='IS';

注意！并非所有视图皆可更新。
不是所有视图都可以更新！

触发器

触发器是用户定义在关系上的一类由事件驱动的特殊过程。一旦定义触发器将被保存在数据库服务器中。
触发器又叫做事件-条件-动作规则。是监视某种事件情况，并触发某种操作，当对一个基本表进行增删改操作时，就可能自动激活执行触发器。
案例分析：
1.当对表SC的grade属性进行修改时，若分数增加10%，则将此操作记录到另一个表SC_U(SNO,CNO,OLDGRADE,NEWGRADE)中，其中oldgrade是修改前分数，newgrade是修改后分数。
2.将每次对表STUDENT的插入操作锁增加的学生个数记录到表S_I中。
3.定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则，教师工资不得低于4000元，如果低于则自动修改为4000元。
触发器的激活和删除
触发器激活时的执行顺序：
（1）执行该表上的BEFORE触发器
（2）激活触发器的SQL语句
（3）执行该表上的AFTER触发器。
删除时使用 DROP TRIGGER <触发器名> ON<表名>即可

///1.当对表SC的grade属性进行修改时，若分数增加10%，则将此操作记录到另一个表SC_U(SNO,CNO,OLDGRADE,NEWGRADE)中，其中oldgrade是修改前分数，newgrade是修改后分数。
CREATE TRIGGER SC_T				//创建名为SC_T的触发器
AFTER UPDATE OF GRADE ON SC		//触发事件及触发时机
REFERENCING						
	OLDROW AS OLDTUPLE,
	NEWROE AS NEWTUPLE
FOR EACH ROW					//行级触发器，即每执行一次grade的更新操作，下面规则就执行一次
WHEN(NEWTUPLE.GRADE>=1.1*OLDTUPLE.GRADE)//触发条件
	INSERT INTO SC_U(SNO,CNO,OLDGRADE,NEWGRADE)
 VALUES(OLDTUPLE.SNO,OLDTUPLE.CNO,OLDTUPLE.GRADE,NEWTUPLE.GRADE)
///2.将每次对表STUDENT的插入操作锁增加的学生个数记录到表S_I中。
CREATE TRIGGER S_C				
AFTER INSERT ON STUDENT		//触发事件及触发时机
REFERENCING						
	NEW TABLE AS DELTA
FOR EACH STATEMENT					//语句触发器，即每执行完一次insert操作，下面规则就执行一次
	INSERT INTO SC_I(NUM)
	SELECT COUNT(*) FROM DELTA
//3.定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则，教师工资不得低于4000元，如果低于则自动修改为4000元。
CREATE TRIGGER INSERT_OR_PDATE
BEFORE INSERT OR UPDATE ON TEACHER
REFERENCING 
			NEW ROW AS NEWTUPLE
FOR EACH ROW
BEGIN     		//这是一个PL/SQL过程块，定义触发动作体
	IF(NEWTUPLE.JOB='教授')AND (NEWTUPLE.SAL<4000)
	THEN NEWTUPLE.SAL:=4000;
	END IF;
END;

数据库编程

1.存储过程

数据库编程主要涉及到嵌入式SQL语句，游标，过程化SQL和存储过程等，在应用系统中使用SQL编程来访问和管理数据库中数据的方式除了以上几点外，还有JDBC,ODBC等。
鉴于篇幅原因，本次只对存储过程和ODBC工作流程进行简单介绍，对于其他知识点，后期会进行增加。
对于存储过程而已，我们为什么要用存储过程呢？下面给出主观性回答：
为什么需要存储过程。
1）在数据库服务器中存储过程的命令只在创建时需要进行编译，以后直接调用无需编译，执行速度快。
2）当数据库进行诸如更新、查询等操作时只需将这些复杂操作封装起来与数据库事务处理结合使用，减少网络通信量。
3)存储过程可以重复使用，提高了可重用性，减少数据库开发人员的工作量，有更强的适应性与复用性。
4)更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力，可维护性较高。
5)可设定只有某用户才能对指定存储过程的使用权，且存储过程比多条sql稳定，只要数据库不出现问题，基本上是不会出现什么问题的，安全性高。
6)应用程序和数据库的编码工作可以单独进行，减少耦合度，分布式工作。
对此似乎有点云里雾里？没关系，对于存储过程而已，我们可也理解为存储过程，其实就是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。这点与我们所熟知的封装、函数等即为类似。
以案例来说明：
1.利用存储过程实现下面应用：从账户1转指定数额的款项到账户2中。假设账户关系表为Acount(acountnum,total)。

//1.利用存储过程实现下面应用：从账户1转指定数额的款项到账户2中。假设账户关系表为Acount(acountment,total)。
CREATE OR REPLACE PROCEDURE TRANSFER(inAcount INT , outAcount INT,amount FLOAT)
//定义存储过程，其参数为转入账户，转出账户，转出额度
AS DECLARE
	totalDepout FLOAT;
	totalDepin  FLOAT;
	inAcountnum INT;
BEGIN
	SELECT total INTO totalDepout FROM Acount WHERE inAcountnum=outAcount;
	IF totalDepout IS NULL 
	THEN 
		ROLLBACK;
		RETURN;
	END IF;
	IF totalDepout<amount  THEN
		ROLLBACK;
		RETURN;
	END IF;
	SELECT acountnum INTO inAcountnum FROM Acount
	WHERE acountnum = inAcount;
	IF inAcountnum IS NULL THEN
		ROLLBACK;
		RETURN;
	END IF;
	UPDATE Acount SET total=total-amount 
	WHERE acountnum=outAcount;
	UPDATE Acount SET total=total+ amount 
	WHERE acountnum=inAcount;
	COMMIT;
END;

2.创建一个存储过程，用于查找收入在5000-8500之间并属于计算机学院的教师编号，姓名及邮件。

CREATE PROCEDURE S_TEACHER_ID(@SHOLNAME CHAR(30),@INCOME1 DOUBLE,@INCOME2 DOUBLE)
AS
BEGIN
	SELECT TEACHER_ID,NAME,EMAILADDRESS
	FROM TEACHER,SHOOLS
	WHERE TEACHER.SCHOOLID=SCHOOLS.SCHOOLID
		  AND  SCHOOLNAME=@SHOLNAME
		  AND INCOME BETWEEN @INCOME1 AND @INCOME2
	COMMIT;
END;
call procedure S_TEACHER_ID('计算机学院',5000,8500);

执行存储过程：

	CALL/PERFORM PROCEDRE TRANSFER(参数1、参数2...)

修改存储过程

ALTER PROCEDURE TRANSFER RENAME TO TTRS

删除存储过程

DROP PROCEDURE TRANSFER();

2.ODBC编程

ODBC是人们在研究和开发连接不同关系数据管理系统中的方法、技术和软件过程中，为了使DB开发，实现DB互连而推出的接口标准。
ODBC的工作流程如下。
（1）配置数据源。配置数据源有两种方法：一是运行数据源管理工具来进行配置，二是使用Driver Manager提供的ConfigDsn函数来增加、修改或删除数据源。
（2）初始化环境。没有和具体的驱动程序相关联，由Driver Manager来进行控制，并配置环境属性。应用程序通过调用连接函数和某个数据源进行连接后，Driver Manager才调用所连的驱动程序中的SQLAllocHandle，来真正分配环境句柄的数据结构。
（3）。建立连接。应用程序调用SQLAllocHandle分配连接句柄，通过SQLConnect、SQLDriverConnect或SQLBrowseConnect与数据源连接。
（4）分配语句句柄。处理任何SQL语句之前，应用程序还需要首先分配一个语句句柄，语句句柄含有具体的SQL语句以及输出的结果集等信息。
（5）执行SQL语句。处理SQL语句的两种方式：预处理（SQLPrepare、SQLExecute适用于语句的多次执行）和直接执行（SQLExecdirect）。应用程序根据语句类型进行的处理；有结果集的语句则进行结果集处理。没有结果集的函数，可以直接利用本语句句柄继续执行新的语句或是获取行计数（本次执行所影响的行数）之后继续执行。
（6）结果集处理。应用程序可以通过SQLNumResultCols来获取结果集中的列数通过SQL DescribeCol或是SQLColAttribute函数来获取结果集每一列的名称、数据类型、精度和范围。ODBC中使用游标来处理结果集数据。
（7）中止处理
处理结束后，应用程序首先释放语句句柄，然后释放数据库连接并与数据库服务器断开，最后释放ODBC环境。