3.1 SQL概述

略

3.2数据定义

3.2.1模式的定义以及删除

1.定义模式

什么是数据库模式？定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等。

格式：CREATE SCHEMA <模式名> AUTHORIZATION <用户名>；

• 如果没有指定<模式名> ，则<模式名> 隐含等于<用户名>

• 要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的CREATE SCHEMA权限（安全性）。//后边的数据库安全性讲的很详细。

• 在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句，格式为：AUTHORIZATION <用户名> [<表定义子句>| <视图定义子句>| <授权定义子句>]。

• 例子：

    // 给用户Longlong定义一个名为 test 的模式

    CREATE SCHEMA test AUTHORIZATION LongLong ;

    //定义模式可以进一步创建基本表、视图，定义授权。

    CREATE SCHEMA test AUTHORIZATION Longlong

         CREATE TABLE Tab1(Col1 SMALLINT,

                          Col2, CHAR(20),

                          Col3, INT);

2.删除模式

格式： DROP SCHEMA <模式名> <CASCADE | RESTRICT>

• CASCADE（级联）
  删除模式的同时把该模式中所有的数据库对象全部删除

• RESTRICT（限制）
  如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。

3.2.2 基本表的定义、删除和修改

1.定义基本表

    CREATE TABLE <表名>

          （<列名> <数据类型>[ <列级完整性约束条件> ]

          [，<列名> <数据类型>[ <列级完整性约束条件>] ] …

          [，<表级完整性约束条件> ] ）；

• <表名>：所要定义的基本表的名字

• <列名>：组成该表的各个属性（列）

• <列级完整性约束条件>：涉及相应属性列的完整性约束条件(primary key、not null、check、unique等等)

• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

• SQL中域的概念用数据类型来实现，定义表的属性时需要指明其数据类型及长度，主要数据类型如下：

• 常用完整性约束：
  主码约束：PRIMARY KEY，NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。用在表级完整性上可以设置两个属性作为主键。

create table student(
    sno varchar(11) primary key,
    sname varchar(10)
)

    create table sc(
        sno varchar(11),
        cno varchar(6),
        grade float,
        primary key(sno,cno)//(sno、cno都为主键)
    )

唯一性约束：UNIQUE:保证某列的每行必须有唯一的值，可以为空

create table student(
    sno varchar(11) unique,
    sname varchar(10)
)

非空值约束：NOT NULL：指示某列不能存储 NULL 值。
参照完整性约束：FOREIGN KEY(外码) REFERENCES（被参照表）
用户自定义完整性约束：

• check(当前列要满的约束条件)

• DEFAULT -规定没有给列赋值时的默认值。

create table student(
    ssex varchar2(2) check(ssex in ('男','女')),ssex只能是'男'或者'女'
    scourse varchar(20)  default '数据库'//如果没有插入值，默认为数据库
)

2. 模式与表

每个基本表都属于某个模式，一个模式包含多个基本表,定义基本表有三种方式定义其所属模式：

• 在表名中明显的给出模式名

• 创建模式时同时创建表

• 设置所属的模式，在创建表时不必给出模式名，类似缺省。

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式，搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名，若搜索路径中的模式名都不存在，系统将给出错误，相关操作：

• 显示当前的搜索路径：SHOW search_path;

• DBA可以设置搜索路径：SET search_path TO “S-T”, PUBLIC;

3.修改基本表

ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT <完整性约束名>  [ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;

• <表名>是要修改的基本表；

• ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件；

• DROP COLUMN 子句用于删除表中的列：

  • 如果指定了 CASCADE 短语，则自动删除引用了该列的其他对象。

  • 如果指定了 RESTRICT 短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列。

• DROP CONSTRAINT 子句用于删除指定的完整性约束条件；

• ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型。

例:

//向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD Scome DATE  ;
//直接删除属性列:Scome
ALTER TABLE Student  DROP COLUMN Scome ;
//将年龄的数据类型改为半字长整数
ALTER TABLE Student ALTER COLUMN Sage  SMALLINT;
ALTER TABLE Student MODIFY Sage SMALLINT;//ORACLE中
//删除学生姓名必须取唯一值的约束。
ALTER TABLE Student DROP UNIQUE(Sname); 

5、删除基本表

DROP TABLE <表名><CASCADE|RESTRICT>

• RESTRICT：删除表是有限制的。
  欲删除的基本表不能被其他表的约束(CHECK,FOREIGN KEY等)所引用，不能有视图、触发器(TRIGGER)
  如果存在依赖该表的对象，则此表不能被删除

• CASCADE：删除该表没有限制。
  在删除基本表的同时，相关的依赖对象一起删除；

• 默认为RESTRICT

3.2.3索引的建立与删除

索引是加快查询速度的有效手段

1. 建立索引

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

• <表名>指定要建索引的基本表名字

• 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔

• 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC

• UNIQUE表明此索引的每一个索引值只对应唯一的数据记录

• CLUSTER表示要建立的索引是聚簇索引

例：为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按姓名升序建立唯一索引，Course表按课程名称升序建唯一索引，SC表按课程号升序和课程成绩降序建索引。

CREATE UNIQUE INDEX  Stusname ON Student(Sname) ;
CREATE UNIQUE INDEX Coucname ON Course(Cname) ;
CREATE INDEX SCcno_Grade ON SC(Cno ASC, Grade DESC) ;

2.修改索引

ALTER INDEX <旧索引名> RENAME TO<新索引名>

3.删除索引

DROP INDEX <索引名>；

删除索引时，系统会从数据字典中删去有关该索引的描述。

3.2.4数据字典

3.3数据查询

语法

SELECT [ALL|DISTINCT] <目标列表达式>
            [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

3.3.1单表查询

select 目标表达式 from 表名

目标表达式：

• 属性列：select sno,pno,sname

• 全部信息:select *

• 表达式&列比名: 例:select 2023-sage表示查询出生年份，在后边加空格可以加别名:select 2023-sage birth year 这样查询出来的列名也是birth year

• 函数:select lower(sdept):查询sdept，并将其转换为小写

• 字符串常量

选择若干原组

• 消除重复行：DISTINCT：作用目标为所有列

//DISTINCT同时作用于Grande和Cno，查询选修课程的各种成绩
SELECT DISTINCT Cno, Grade FROM SC;

• 条件表达式（WHERE字句）

select * from table

where 条件表达式

其中字符串匹配需要注意用法：

• % 不限数量: 例:查询刘姓的人：where name like ’刘%‘

• _限一个字: 例: 查询姓刘，且全名是三个字的人:where name like '刘_ _'

• 转义: escape ： where like DB\_DESIGN ESCAPE '\'escape 将'\'定义为转义字符，然后，查询DB_DESIGN的字符。

查询结果排序（ORDER BY字句）

DESC为降序，sdept为升序（默认为升序）

SELECT Sno,  Grade
    FROM  SC
    WHERE  Cno= '3'
    ORDER BY Grade DESC  ;

聚集函数

• 计数
  COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>）

• 计算总和
  SUM（[DISTINCT|ALL] <列名>）

• 计算平均值
  AVG（[DISTINCT|ALL] <列名>）

• 求最大值
  MAX（[DISTINCT|ALL] <列名>）

• 求最小值
  MIN（[DISTINCT|ALL] <列名>）

  • DISTINCT短语：在计算时要取消指定列中的重复值

  • ALL短语：不取消重复值，ALL为缺省值

• 对空值的处理
  除COUNT(*)外，都跳过空值

例:

//查询学生2021084219的选秀课程总学分
select sum(学分)
from sc,coures
where sc.学号='2021084219' and sc.课程号=course.课程号;

GROUP BY字句

用GROUP BY子句分组，细化聚集函数的作用对象（范围）。对查询结果分组后，聚集函数将分别作用于每个组。

• GROUP BY子句的作用对象是查询的中间结果表；

• 分组方法：按指定的一列或多列值分组，值相等的为一组；

• 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数。

例:求各个课号及相对应的选课人数

select cno,count(sno)
from sc
group by cno
//先分组，再计数 ，按照cno分组,
////select后只能跟两类信息，1.group by后的cno，2.聚集函数

结果为:

group后不能用where查询，要用having

• 使用HAVING短语筛选最终输出结果：只有满足HAVING短语指定条件的组才输出

• HAVING短语与WHERE子句的区别：作用对象不同

• WHERE子句作用于基表或视图，从中选择满足条件的元组。

• HAVING短语作用于，从中选择满足条件的组。

例:查询选修了9门以上课程的学生学号。

SELECT Sno
     FROM  SC
     GROUP BY Sno
     HAVING  COUNT(*) >9 ;

3.3.2 连接查询

是跟在where后边的。基本用到的都是等值连接,非等值连接这里不说。

例:查询每个学生及其选修课的情况

select student.*,sc.*
from student,sc
where student.sno=cno.sno

结果:

当两个表有相同的列属性时，这个时候一般用等值连接通过相同的属性将这两个表连成一个大表，之后进行查询。

自然连接:在等值连接时把目标中的重复的属性列去掉叫做自然连接。

自身连接

例:查询每门课的间接先修课(先修课的先修课)

给course表起两个别名:first，second

select first.cno,second.cpnso
from course first,course second
where first.cpno=second.cno

外连接

:当悬浮连接(不符合查询条件而无法连接的元组)，在普通连接时，会在查询结果后被删除，如果想将悬浮元组保留下来，则需要用外连接

• 左外连接:列出左边关系中所有元组:LEFT OUT JOIN SC ON

• 右外连接:列出右边关系中所有元组:RIGHT OUT JOIN SC ON

例:查询每个学生及其选修课程:

外连接:select student.*from student LEFT OUT JOIN SC ON(student.sno=sc,sno)，将左边关系(student)的所有元组列出来

多表连接

加个 and就行了

例:

select student.sno,sname 
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno

3.3.3嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

    SELECT Sname        /*外层查询、父查询*/
         FROM Student
         WHERE Sno IN
             (SELECT Sno        /*内层查询、子查询*/
                    FROM SC
                    WHERE Cno= '1'  )  ;

上述代码先执行内层查询，找到所有选了课程号为1的课程的学生学号，外层查询利用内层查询的结果去查询这些学号对应的名字。

子查询的限制：

• 不能使用ORDER BY子句，ORDER BY只能对最终查询结果排序。

• 层层嵌套方式反映了 SQL语言的结构化；有些嵌套查询可以用连接运算替代。

分类:

不相关子查询：子查询的查询条件不依赖于父查询。由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

相关子查询：子查询的查询条件依赖于父查询。(子查询需要用到父查询中的一些信息)

• 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；

• 然后再取外层表的下一个元组；

• 重复这一过程，直至外层表全部检查完为止。

例:找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno, Cno
    FROM SC x
    WHERE Grade >=(SELECT AVG(Grade)
                           FROM SC y
                           WHERE  y.Sno=x.Sno)  ;

在实际应用中，我们不用管是相关还是不相关

子查询的谓词

带有IN谓词的子查询：

例：查询与小明在同一个系学习的学生。

SELECT Sno, Sname, Sdept//外查询：查询系名和内查询一样的学生信息。
    FROM Student
    WHERE Sdept  IN
          (SELECT Sdept//内查询：查找陈磊所在的系名
                FROM Student
                WHERE Sname=  '小明' )  ;

该例子也可以用自身连接完成：

    SELECT  S1.Sno, S1.Sname, S1.Sdept
         FROM     Student S1, Student S2
         WHERE  S1.Sdept = S2.Sdept  AND
                          S2.Sname = '小明' ;

带有比较运算符的子查询：

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >），与ANY或ALL谓词配合使用。

例：假设学生姓名不能重复，一个学生只可能在一个系学习，并且必须属于一个系，则上面的例子中可以用 = 代替IN ：

    SELECT Sno, Sname, Sdept
        FROM    Student
        WHERE Sdept   =
               (SELECT Sdept
                       FROM   Student
                        WHERE Sname= '小明')  ;

带有ANY或ALL谓词的子查询：

ANY：任意其中一个。

ALL：所有值。

需要配合比较运算符使用，如：

> ANY 大于子查询结果中的某个值

> ALL 大于子查询结果中的所有值

例：查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

    SELECT Sname, Sage
        FROM    Student
        WHERE Sage < ANY (SELECT  Sage
                                        FROM    Student
                                        WHERE Sdept= 'IS')
                AND Sdept <> 'IS' ;  
                /* 注意这是父查询块中的条件 */

• DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19，20);

• 处理父查询，找所有不是IS系且年龄小于19 或 20的学生。

ANY和ALL谓词有时可以用集函数实现：

用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数。

带有EXISTS谓词的子查询：

EXISTS代表存在量词:

$\ni$

带有EXISTS谓词的子查询只返回逻辑真值"true"或逻辑假值:"false".

例:查询所有选修了1号课程的学生姓名

SELECT sname
from student
where exists
(select *
from sc
where sno=student.sno and cno='1')

使用EXIST后，若内层查询结果为空，则外层的WHERE字句返回真值，否则返回假

因为只会返回真假，所以要select *, 给出列名无意义

3.3.4集合查询

略

3.5数据更新

插入数据

1.插入单个元组

INSERT  INTO <表名> [(<属性列1>[，<属性列2 >…)]
      VALUES (<常量1> [，<常量2>]    …           )

INTO子句

• 指定要插入数据的表名及属性列

• 属性列的顺序可与表定义中的顺序不一致

• 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致

• 指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句

• 提供的值必须与INTO子句匹配，包括值的个数、值的类型

例:

INSERT    INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES ('16110581111', '殷晓琛', '男',  20, 'MA');
或者
INSERT    INTO Student
VALUES ('16110581111', '殷晓琛', '男',  20, 'MA');

2. 插入子查询结果

    INSERT   INTO <表名>  [(<属性列1> [，<属性列2>…  )] 子查询；

修改数据

UPDATE <表名>
SET<列名>=<表达式>[,<列名>=<表达式>]
[WHERE<条件>];

例:

将学生2021084219的年龄改为22岁:

UPDATE student
SET  sage=22
where sno='2021084219'
//如果这里不加where条件，就是将所有学生的年龄改为22

删除数据

DELETE
FROM <表名>
[where <条件>];

3.6 空值的处理

略

3.7 视图

视图算是一个虚表，视图的意义请看4.3 视图机制

然后他的语法和其他差不多，就是将table改为view.