创建基本表

创建基本表要对表进行命名，定义表的每个列，定义表的完整性约束条件，我们使用CREATE TABLE语句创建基本表

CREATE TABLE <表名>
(<列名> <数据类型> [DEEAULT<缺省值>] [列级约束定义],
<列名> <数据类型> [DEEAULT<缺省值>] [列级约束定义],
<列名> <数据类型> [DEEAULT<缺省值>] [列级约束定义],
...,
[<表级约束定义>],...,<表级约束定义>);

1、表名、列名均是由我们自己去定义的
2、数据类型即sql语言中给定的几种数据类型
3、缺省值是以后我们向数据库该的表中插入元素时，假如我们空缺了这一项，就会填上我们设置的缺省值
4、列级约束定义是对当前列的约束条件

[CONSTRAINT <约束名>] <列约束>

列约束包括以下几种：
NOT NULL：不允许该列取空值；不加NOT NULL限制时，该列可以取空值
PRIMARYKEY：指明该列为主码，其值非空、唯一
UNIQUE：该列上的值唯一，说明改列为候选码
CHECK(<条件>)：指明该列的值必须满足的条件，<条件>为一个bool表达式

排序和分组

排序

ORDER BY子句可以将查询结果按一定的次序显示.
形式：

ORDER BY <排序列> [ASC | DESC]{, <排序列> [ASC | DESC]}

1、<排序列>是必须出现在SELECT语句中的属性名。
2、ORDER BY语句可以有一个或多个排序列，中间用逗号隔开。每个排序列都可以单独指定升序还是降序排列，缺省时为升序。
查询每位学生的每门课程的成绩，按成绩降序排序。

SELECT *
FROM SC
WHERE Cno = 'CS202'
ORDER BY Grade DESC;

3、聚集函数

统计元组个数
COUNT([ALL|DISTINCT]*)	
统计一列中值的个数
COUNT（[ALL | DISTINCT] 列名）
计算一列中值的个数
SUM([ALL | DISTINCT] 列名)
AVG([ALL | DISTINCT] 列名)
MAX([ALL | DISTINCT] 列名)
MIN([ALL | DISTINCT] 列名)

分组

分组的关系和聚集函数的关系很大
GROUP BY语句

GROUP BY<分组列>[HAVING <分组选择条件>]

分组语句细化了聚集函数的作用范围
HAVING短语用来过滤掉不满足的<分组选择条件>的分组,缺省时等价于HAVING TRUE
<分组选择条件>类似于WHERE子句的查询条件，只不过WHERE子句中不允许出现聚集函数

eg：查询每个学生的平均成绩，并输出平均成绩大于85的学生的学号和平均成绩

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno HAVING AVG(Grade) > 85;

对于带GROUP BY子句的SELECT语句，SELECT子句中的结果列必须是GROUP BY子句中的<分组列>或聚集函数。

连接查询

Q:查询阿杜选修数据库系统原理课程的成绩？

当查询需要的信息或者查询条件涉及的属性分布在多个表中时，需要进行链接查询

1、SQL支持连接查询，允许FROM子句中包括多个表
2、当FROM子句中包含多个表时，相当于求这些表的笛卡尔积
3、可以在WHERE子句中说明连接条件，并通过SELECT子句选取所需要的属性来实现各种连接
eg:查询学号为201705001的学生的各科成绩，对每门课程显示课程名和成绩

SELECT Cname, Grade
FROM SC, Course
WHERE SC.Cno = Course.Cno AND Sno = '2017050001'

eg:查询每个学生的平均成绩，并输出平均成绩大于85的学生的学号、姓名和平均成绩

SELECT Student.Sno, Sname, AVG(Grade)
FROM SC, Students
WHERE Student.Sno, Sname
HAVING AVG(Grade) > 85;w

嵌套查询

SQL是一种结构化查询语言，他允许将一个查询作为子查询嵌套在另一个SELECT语句中

比较常见的嵌套是将查询结果嵌套在WHERE或HAVING语句中，子查询不能用ORDER BY语句，只有最终查询结果才能用ORDEFR BY语句

IN引出的子查询

<元组>[NOT]IN<子查询>

eg：查询和林艳在同一专业学习的女同学的学号和姓名

SELECT Sno, Sname
FROM Students
WHERE Sex = '女' AND Speciality IN
(
SELCET Speciality
FROM Students
WHERE Sname = '林艳')；

下面是先找到林艳的专业，然后将此专业作为查询条件

集合的比较引出的查询

SQL语言允许将一个元素与子查询的结果集进行比较。
形式

<值表达式> Θ ALL | SOME | ANY <子查询>

其中<值表达式>通常是属性，Θ是比较运算符。SOME与ANY含义相同。早期只有ANY但是和英语上的any容易混淆，现在多用SOME。当<子查询>的结果为单个值时，ALL、SOME和ANY可以省略。
eg：
查询比软件工程专业所有学生都小的其他专业的学生的学号、姓名、专业和出生日期

SELECT Sno, Sname, Speciality, year(BIrthday)
From Students
WHERE Speciality <>'软件工程' AND year(Birthday) > ALL(SELECT year (Birthday)
FROM Students
WHERE Speciality = '软件工程');

ALL 可以与聚集函数实现的查询互换

SELECT Sno, Sname, Speciality, year(Birthday)
FROM Students
WHERE Speciality<> '软件工程' AND 
year(Birthday) > (SELECT MAX(year(Birthday)))
	FROM Students
	WHERE Speciality = '软件工程');

存在量词引导的子查询

形式：

EXISTS <子查询>
子查询的SELECT子句的形式为SELECT*。EXISTS<子查询>为真，当且仅当<子查询>的结果非空（至少包含一个元组）

EXISTST是根据外层查询的每个元组依次取与内层查询的结果比较看是否为空，若非空则为真输出，反之则不输出，这是一个相关子查询。
这里我们可以把查询分为两种，一种是相关子查询，一种是不相关子查询。相关子查询就是内层查询依赖于外层查询，不相关子查询就是外层查询不依赖于内层查询

感觉NOT EXISTS这里很绕还没有搞清楚，搞清楚再回来补这一点的内容和例子

数据更新

插入

1、向基本表中插入单个元组

INSERT INTO T [(A1, ..., Ak)] VALUE(c1, ..., ck)

eg:
向Students表中添加一条这样的记录

INSERT INTO Students
VALUES ('201716010', '司马相如', '男', 1997-01-28, '2017', '计算数学', 'MATh')

A1，A2…可以不按顺序，只要与下面的值对应就可以，A1，A2等也可以省略，但这是下面的内容则必须按顺序

2、插入查询结果
想基本表中插入单个元组一般用于数据的输入，我们有时候还想将查询结果插入到一个基本表中。插入查询结果的语句的基本形式：

INSERT INTO T [(A1, A2, ..., Ak)]
<查询表达式>

T通常是基本表，也可以是视图， A1，…,Ak是T的属性，<查询表达式>通常是一个SELECT语句

eg:
信息工程学院要为本院每位教师办理一个校内就餐卡，直接用教师号作为主持人的编号，并预存100元，可以用INSERT语句插入到基本表中

INSERT INTO Cardinf(Card-no, Name, Balance)
SELECT Tno, Tname, 100.00
FROM Teachers
WHERE Dno = 'IE';

注意常量100.00出现在SELECT中。这使得查询结果的每个元组的第3列均取常量100.00。

修改

UPDATE语句格式为：

UPDATE T
SET A1 = e1, ..., Ak = ek
[WHERE<修改条件>]

其中T通常是基本表，但也可以是某些视图，A1，…,Ak
是T的属性，而e1，…,ek是表达式,<删除条件>与SELECT语句中的查询条件类似

eg：将职工号为B050041的教师的职称修改为副教授

UPDATE Teachers
SET Title = '副教授'
WHERE Tno = 'B050041';

eg:将软件工程课程成绩低于60分的所有学生的软件工程成绩提高5分

UPDATE SC
SET Grade = Grade + 5
WHERE Grade < 60 AND Cno IN
(SELECT Cno
FROM Course 
WHERE Cname = '软件工程')

删除

当关系表中的某些记录已经不再需要时，可以使用DELETE语句进行删除。

DELETE FROM T
	[WHERE <删除条件>]

T通常是基本表，但也可以是某些视图
<删除条件>与SELECT语句中的查询条件

eg：删除学号为201824010的学生的记录

DELETE FROM Students
WHERE Sno = '201824010'

eg:删除所有的学生的语句

DELETE FROM Students

没有WHERE语句时条件恒真

视图

视图是从一个或多个基本表或视图导出的表，与基本不同的是视图的数据并不物理地存放在基本表中

视图的创建和删除

视图的创建

形式：

CREATE VIEW <视图名> [(<列名>， ...,<(列名)>)]
AS<查询表达式>
[WITH CHECK OPTION]

<视图名>:标识符，我们自己命名
<(列名)>：<查询表达式>结果的诸列
<查询表达式>：通常是一个SELECET语句不包含DISTINCT短语和ORDER BY子句
[WITH CHECK OPTION]：表示该视图是可更新的
eg:

CREATE VIEW SE_Students
AS SELECT Sno, Sname, Sex, Birthday, Dno
FROM Students
WHERE Speciality = '软件工程'
WITH CHECK OPTION;

组成视图的属性列名要么全部省略要么全部指定。如果省略了视图的各个属性名，则有SELECT子句目标列中的各个字段组成。但在下列情况下必须明确指定组成视图的所有属性列名：
1、SELECT目标列中包含聚集函数或者列表达式
2、SELECT子句目标是’*’
3、多表连接时出现了同名属性列
4、需要为视图中某个列定义更合适的名字

eg:建立软件工程专业学生的视图SE_Students,它包含Students中出Speciality之外的所有属性和软件工程专业所有学生的信息

CREATE VIEW SE_Students
AS SELECT Sno, Sname, Sex, Birthday, Dno
FROM Students
WHERE Speciality = '软件工程'
WITH CHECK OPTION;

eg：基于多个表的视图
建立学生成绩视图Students_Grades,它包含如下属性：学号、学生姓名、课程名和成绩

CREATE VIEW Students_Grades(Sno, Sname, Cname, Grade)
AS SELECT S.Sno, Sname, Cname, Grade
FROM Students S, SC, Courses C
WHERE S.Sno = SC.Sno AND C.Cno = SC.Cno;

视图的删除

格式：

DROP VIEW <视图名> [CASCADE | RESTRICT]

删除视图就是把视图的定义从数据字典中删除

基于视图的查询

我们可以对视图进行查询，但最后都会消解为对基本表的查询
eg：查询软件工程专业的男生

SELECT *
FROM SE_Students
WHERE Sex = '男'
上面的语句等价于下面的
SELECT *
FROM(SELECT Sno, Sname, Sex, Birthday, Dno
FROM Students
WGERE Speciality = '软件工程')
AS SE_Students(Sno, Sname, Sex, Birthday, Dno)
WHERE Sex = '男'

基于视图的更新

视图有可更新视图和不可更新视图
一般情况下行列子集视图都可以更新
eg：向软件工程专业的学生的视图SE_Students中插入一个新的记录，学号为201805109，姓名为阿杜，出生年月2023-05-10，女性，院系EI

INSERT INTO SE_Students(Sno, Sname, Birthday, Sex, Dno)
VALUE('201805109', '阿杜', 2023-05-10,'女', 'EI')

eg；将软件工程专业学号为202105268的学生姓名改成‘李岩’

UPDATE SE_Students
SET Sname = '李岩'
WHERE Sno = '201805268'

不可更新视图
S_G学生的平均成绩视图，没有办法透过视图来更改

视图的作用

1、使用视图可以使一些查询表达更加简洁
2、视图提供了一定的逻辑独立性
3、视图可以起到安全保护作用
4、视图使得用户可以从不同的角度看待相同的数据

嵌入式SQL

SQL可以嵌入到C、JAVA等其他语言中，嵌入其他语言有一些问题需要解决
1、如何区分两种语言
2、两种语言的语句如何交换信息（通信）
3、如何连接数据库
下面以C语言为例

（1）、

a、扩充主语言的编译系统，使之能处理SQL语句

b、在编译前先扫描源程序，将SQL语句翻译成目标代码

为了区分源程序中的SQL语句和诸语言语句，SQL规定：
所有嵌入式SQL语句都必须加前缀EXEC SQL

（2）、

a、SQLCODE

1、每个SQL语句执行之后需要反馈一些状态信息，系统将这些状态信息存入SQLCODE
2、主语言语句可以访问SQLCODE，根据结果采取相应动作
3、SQLCODE是一个整型变量
如果SQL语句执行成功，SQLCODE = 0
如果执行结果无数据则SQLCODE = 100
其他情况视为异常，SQLCODE取负值，其具体值依赖于实现

b、主语言变量

一般情况下主语言变量不能再SQL语言中使用
但是使用

EXEC SQL BEGIN DECLARE SECTION;
主语言变量;
EXEC SQL END DECLARE SECTION;

可以在SQL中使用

为了区别SQL语句中出现的的主语言变量之前必须加冒号（：）

游标

游标的作用：
一个SQL语句得到的结果可能是多个记录，而主语言没办法一次处理多个记录，这就需要用到游标。
游标就是一个数据缓冲区，暂时存放SQL语句的执行结果
使用游标需要预先说明游标，在使用前打开游标，通过专门的SQL语句逐一提取记录，并在完成之后关闭游标

如何连接数据库

c语言

EXEC SQL CONNECT TO <SQL服务器>[AS<连接名>][USER<用户名>];
``
建立到当前服务器的默认连接
```sql
EXEC SQl CONNECT TO DEFAULT;

关闭数据库连接

EXEC SQL DISCONNECTION <连接名>;

不使用游标的SQL语句

有些SQL语句不产生结果，这些SQL语句可以不适用游标
说明型语句、数据定义语句、数据控制语句、SQL产生的结果不是元组的集合，主语言可以一次处理那么也不需要游标
（例如查询结果为单个记录的SELECT语句）、非交互的语句

1、查询结果为单个记录的SELECT语句

EXEC SQL SELECT <选择序列>
	INTO <选择目标序列>
	其他子句

eg:查询给定学生的给定课程成绩
假设学生的学号已经赋予主变量Hsno，课程号已经赋值给主变量Hcno，则下面语句将检索相应的成绩，并将结果赋予主变量Hgrade：

EXEC SQL SELECT Grade
INTO:Hgrade:igrade
FROM SC
WHERE Sno=:Hsno AND Cno =:Hcno;

非交互式更新

删除一个学生的记录

EXEC SQL DELETE FROM SC 
	WHERE Sno =:Hsno;
EXEC SQL DELETE FROM Students 
	WHERE Sno =:Hsno;

使用游标的SQL

使用游标的SQOL语句有如下两种情况
1、查询结果为多个元组的SELCET语句
2、交互式更新语句

所有使用游标的SQL语句都必须先通过
1、说明定义游标
2、在使用前打开游标
3、反复推进游标指针并取当前记录进行处理
4、最后，当所有记录都处理完之后，关闭游标

说明游标使用DECLARE语句

EXEC SQL DECLARE <游标名> CURSOR
FOR <SELECT语句>
[<可更新性子句>]

1、游标的内容由打开游标时执行定义游标的<SELECT语句>决定
2、有一个与游标相关联的指针，初始时它指向游标第一行之前的位置
[<可更新性子句>]：有两种形式

FOR READ ONLY

定义只读游标

FOR UPDATE [OF <列名>，..., <列名>]

定义可更新游标

缺省时为只读型游标

可更新型游标

对于可更新型游标，使用CURRENT形式的UPDATE和DELETE语句进行更新，对游标的更新转换成对定义游标的基本表的更新，所以SELECT语句定义的表必须是可更新的

游标的两种状态

游标的打开

EXEC SQL OPEN <游标名>;

关闭游标

EXEC SQL CLOSE <游标名>;

推进游标指针并取当前记录

EXEC SQL FETCH [[<推进方向>] FROM] <游标名>
INTO:<主变量>[:<指示变量>,...,:<主变量>[:<指示变量>]

<推进方向>:NEXT：向前推进一个记录
PRIOR：向后推进一个进路
FIRST：推进到第一个记录
LAST：推进到最后一个记录
缺省值为NEXT
INTO子句中的主变量必须与说明游标中的SELECT语句中的目标列表达式一一对应

Current形式的更新语句

DELETE FROM T
WHERE CURRENT OF <游标>

UPDATE T
SET A1 = e1, ..., Ak = ek
WHERE CURRENT OF<游标名>

eg:从Students和SC中删除某学生的记录：

char YN;//变量YN不在SQL语句中使用
EXEC SQL BEGIN DECLARE SECTION;
	char Givenname[8];
	char Hson[9];
	char Hsname[9];
	char Hsex[9];
	char Henrollyear[9];
	char Hspeciality[9];
	char Hdno[9];
	EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE Stydent_Cursor CURSOR FOR 
SELECT Sno, Sname, Sex, Enrollyear, Speciality, Dno
FROM Students
WHERE Sname=:Givenname
FOR UPDATE;
Givenname='李明';
EXEC SQL OPEN Student_Cursor;
EXEC SQL FETCH Students_Coursor;
while(SQLCODE = 0)
{
printf("%s %s %s % s % s %是\n", Hsno, Hsname, Hsex, Henrollyear, Hspeciality, Hdno);
scanf("%c", &YN);
if(YN == 'y' || YN == 'Y')
{
	EXEC SQL DELETE FROM SC
	WHERE Sno = :Hsno;
	EXEC SQL DELETE FROM Students
	WHERE CURRENT OF Students_Cursor;
	break;
}
EXEC SQL FETCH Student_Cursor
INTO:Hsno,:Hsname,:Hsex, :Henrollyear, Hspeciality,:Hdno;
}
EXEC SQL CLOSE Student_Cursor;