关系数据库标准语言SQL

SQL核心功能

  1. 数据查询：select
  2. 数据定义：create、drop、alter
  3. 数据操作：insert、update、delete
  4. 数据控制：grant、revoke

SQL的定义功能

  1. 定义基本表的结构
1
2
3
4
5
create table <表名>
(<列名><数据类型>[完整性约束条件]，
<列名><数据类型>[完整性约束条件]，
[表级完整性约束条件]
);

表是要定义的基本表的名字，表由多个属性组成。定义表的各个属性的时候要指明数据类型和长度。

完整性约束条件包括列、元组、表级完整性约束条件。用户自定义完整性约束条件（唯一性约束：UNIQUE、非空值约束：NOT NULL）属于列级约束条件，而实体完整性（主键 PRIMARY KEY）、参照完整性（外键 FOREIGN KEY）都属于表级的完整性约束条件。

  • 主关键字的定义

    • 在属性那一行加上一句PRIMARY KEY就可以表名这个属性是这个表的主关键字。
    • 在列完所有属性以后加上一句PRIMARY KEY（属性1，属性2···）

    如果关键字是由多个属性组成的，一定要用第二种方法才行。

  • 外部关键字的定义

    • 只有一个属性是外部关键字的话，可以直接在属性的说明和类型后面加上一句 REFERENNCES 表名 (属性)
    • 如果很多个属性都是关键字的话，那就在属性列表的最后一句加上 FOREIGN KEY (属性1) REFERENCES 表名 (属性2)

  • 默认值的定义

    • 在定义属性后面加一个保留字DEFAULT
1
2
3
4
5
6
7
8
9
create table student
(学号 char(8) NOT NULL UNIQUE, -- 不能取空值，也不能取一个重复的值
性别 char(2) DEFAULT '男',
姓名 char(8),
出生年份 smallint,
籍贯 char(8),
学院 char(15),
PRIMARY KEY(学号)
);
1
2
3
4
5
6
7
8
create table lesson
(课程号 char(8) NOT NULL UNIQUE,
课程名 char(15),
学时 smallint,
开课学期 char(4),
课程性质 char(15),
PRIMARY KEY(课程号)
);
1
2
3
4
5
6
7
8
create table study
(学号 char(8),
课程号 char(8),
成绩 samllint,
PRIMARY KEY(学号,课程号),
FOREIGN KEY(学号) REFERENCES 学生(学号),
FOREIGN KEY(学号) REFERENCES 课程(课程名)
);
1
2
3
4
5
6
create table study
(学号 char(8) references 学生(学号),
课程号 char(8) references 课程(课程号),
成绩 smallint,
primary key(学号,课程号)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 用户自定义完整性约束
-- 列值约束
create table student
(学号 char(8) NOT NULL UNIQUE, -- 不能取空值，也不能取一个重复的值
性别 char(2) 
	check(性别='男' or 性别='女'),
姓名 char(8),
出生年份 smallint,
籍贯 char(8),
学院 char(15),
PRIMARY KEY(学号)
);
-- 默认值约束，默认值是提前给某列指定的取值
create table student
(学号 char(8) NOT NULL UNIQUE, -- 不能取空值，也不能取一个重复的值
性别 char(2) DEFAULT '男',
姓名 char(8),
出生年份 smallint,
籍贯 char(8),
学院 char(15),
PRIMARY KEY(学号)
);

SQL的修改和删除

  1. 修改基本表

    语法格式(COLUMN 是可要可不要的)

    1
    2
    3
    4
    ALTER TABLE <表名>
    ADD 新列名 数据类型 完整性约束 -- 增加新的列和新的完整性约束条件
    DROP 列名	完整性约束名
    MODIFY COLUMN 列名 数据类型
    • 表名是要修改的基本表，ADD子句用于增加新的属性列和新的完整性约束条件，DROP用于删除约束条件，ALTER COLUMN子句用于修改原来有的列的定于包括列名数据类型。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ALTER TABLE 学生 ADD COLUMN 年龄 SMALLINT;
    ALTER TABLE 学生 ADD 入学日期 DATE;-- 不论基本表中原来是否已有数据，新增加的一列一律取空值
    ALTER TABLE 学生 ADD PRIMARY KEY(学号);-- 也可以只增加约束条件，可以是主码、外码、取值唯一等常用的列级约束
    ALTER TABLE 学生 ADD UNIQUE(姓名);
    ALTER TABLE 学习 ADD FOREIGN KEY(学号) REFERENCES 学生(学号);
    ALTER TABLE 学生 DROP COLUMN 年龄; -- 删除列
    ALTER TABLE 学生 DROP 入学日期;
    ALTER TABLE 学生 DROP PRIMARY KEY; -- 删除属性
    -- 也可以通过给约束起名字，然后通过约束名字去删除
    ALTER TABLE 课程 MODIFY COLUMN 课程名 CHAR(20);

  2. 删除基本表

    1
    2
    3
    DROP TABLE 表名 
    restrict:受限删除
    cascade:级联删除

索引的建立与删除

顺序访问的执行效率很低，所以被抛弃；索引能帮助用户快速地找到自己想要的数据。

搜索码：用于在文件中查找记录的属性或者属性集

索引是根据关系表当中某些字段的值，按照一定结构存放的文件（B+树）

索引分类：

  • 聚集索引：字典按照拼音
  • 非聚集索引：字典按照部首

  1. 建立索引

    1
    create [unique] [cluster] index 索引名 on 表名(列名[次序],···)

    ASC是升序，DESC是降序。

    • 为学生、课程和学习表建立索引；

      1
      2
      3
      create unique index stu_idx_sno on 学生(学号);
      create unique index cou_idx_cno on 课程(课程号);
      Create unique index sc_idx _cno on 学习(学号ASC,课程号DESC);

  2. 删除索引

    1
    2
    DROP INDEX 索引名;
    DROP INDEX stu_idx_sno;

数据查询(重点)

  • 单表查询（只用了一张表）

    select …from…where…group by…having…order by…

    学生（学号，姓名，性别，籍贯，出生年份，学院）

    课程（课程号，课程名，学时，先修课程号，课程性质）

    学习（学号，课程号，成绩）

    • 选择表中若干列（投影）

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      select 姓名,籍贯
      from 学生;-- 默认获得重复的元组
      select distinct 姓名,籍贯
      from 学生;-- 默认重复列中只取一列
      select *
      from 学生;-- 选出所有

      select 学号,year(now())-出生年份
      from 学生;
      select 学号,year(now())-出生年份 as 年龄 -- 给列定义别名
      from 学生;

    • 选择表中若干元组（选择）

      查询条件 谓词
      比较 =、<>、>、<、<=、>=
      算术运算 +、-、*、/
      确定范围 BETWEEN AND/NOT BETWEEN AND
      确定集合 IN/NOT IN
      字符匹配 LIKE/NOT LIKE
      空值 IS NULL/IS NOT NULL
      多重条件 AND/OR 
      1
      2
      3
      select 学号,课程,成绩
      from 学生
      where 成绩<60;
      1
      2
      3
      select distinct 学号
      from 学生
      where 成绩<60;
      1
      2
      3
      4
      select *
      from 学生
      where 学院="信电"
      and 出生年份>=2000;
      1
      2
      3
      select 姓名,性别,学院,出生年份
      from 学生
      where 出生年份 between 1996 and 1998;
      1
      2
      3
      select 姓名,性别,学院
      from 学生
      where 学院 in('信电','环测','计算机');
      1
      2
      3
      select 姓名,性别,学院
      from 学生
      where 学院 not in('信电','环测','计算机');
      1
      2
      3
      select 姓名,学号
      from 学生
      where 姓名 like '王%';-- 姓王的学生
      1
      2
      3
      select 姓名,学号
      from 学生
      where 姓名 like '_王%';-- 第二个字为王的学生
      1
      2
      #如果本身含有%和_,加个escape让后面的符号不是通配符
      where 课程号 like 'DB\_Design' ESCAPE'\';
      1
      WHERE 成绩 IS NULL;-- 不能换成等于号

    • 对查询进行分组

      • group by 查询各个课程号相应选课人数

      • select 子句后面只能出现用于分组的属性列和集函数。

      • 细化了集函数的作用对象，集函数作用于整个查询结果，分组以后，作用于每个组。

        1
        2
        3
        select 课程号,count(学号)as 选课人数
        from 学习
        group by 课程号;-- 查询各个课程号对应的选课人数

    • having子句

      • 查询学号在091501-091506至少选修了三门课程的学生的学号和选修课课程数

        1
        2
        3
        4
        5
        select 学号,count(课程号)as 选课数
        from 学习
        where 序号 between '091501' and '091506'
        group by 学号
        having count(课程号)>=3;

    • 使用集函数

      计数（count）、计算总和（sum）、计算平均值（avg）、求最大值（max）、求最小值（min）

      max <distinct/all> 列名

      distinct 表示去掉重复的，all表示不取消重复值，all为缺省值

      1
      2
      select count(*) as 总人数
      from 学生;
      1
      2
      3
      select avg(year(now())-出生年份) as 平均年龄
      from 学生
      where 学院='计算机';
      1
      2
      3
      select max(成绩) as 最高分
      from 学习
      where 课程号='180101';

    • 对查询结果进行排序

      1
      2
      3
      4
      select 学号,成绩
      from 学习
      where 课程号='180102'
      order by 成绩 desc;
      1
      2
      3
      select *
      from 学生
      Order by 学院 asc,year(now())-出生年份 desc;

  • 多表查询

    前面的查询都是针对一个表进行的，若查询涉及两个及以上的表，则称之为连接查询。

    • 等值连接

      方法分为theta和ANSI方式

    1. ANSI

      1
      2
      3
      4
      表一 inner join 表二 on 连接条件
      -- 查询学生及其选修课程情况，要将这两个表学号相同的连接起来
      select 学生.*,学号.*
      from 学生 inner join 学习 on 学生.学号=学习.学号;

    2. theta

      1
      2
      3
      4
      -- 运用where子句
      select 学生.*,学习.*
      from 学生,学习
      where 学生.学号=学习.学号;

    • 等值连接和非等值连接

      用来连接两个表的条件称为连接条件或者连接谓词。

      连接的时候连接的列名必须是可比的，不一定相同，但是类型要一样。

      1
      表名1.列名1 比较运算符 表名2.列名2 -- 比较运算符一般有>=,<=,>,<,=,!=
      1
      表名1.列名1 between 表名2.列名2 and 表名2.列名3

      自然连接是等值连接运算的一种特殊情况，也就是对相同属性进行连接，但是在最后的大表中去掉了重复的属性列，保留了所有不重复的属性列。

      1
      2
      3
      4
      -- 自然连接学习表和学生表
      select 学生.学号,性别,姓名,出生年份,学院,课程号,成绩
      from 学生,学习
      where 学生.学号=学习.学号;

    • 自连接查询

      一个表与自己进行连接，关系代数是笛卡尔乘积，需要给自己起别名。

      1
      2
      3
      4
      5
      6
      7
      -- 求一门课程的间接先修课
      select first.课程号 as 课程号, first.课程名 as 课程名, second.先修课程号 as 间接先修课程号
      from 课程 as first inner join 课程 as second on first.先修课程号=second.课程号;
      -- 查询和刘晨在同一个学院学习的学生的姓名
      select s2.姓名
      from 学生 as s1 inner join 学生 as s2 on s1.学院=s2.学院
      where s1.姓名='刘晨';

    • 外连接查询

      外连接的方式可以分为theta和ansi两种，theta方式主要使用where子句，ansi主要使用outer、join、on等关键字。

      1） 左外连接（left outer jion)：查询学生的姓名以及他们选修课程的课程号和成绩。

      1
      2
      3
      4
      5
      6
      7
      8
      -- theta 方式
      select 姓名,课程号,成绩
      from 学生,学习
      where 学习.学号(+)=学生.学号;
      -- ansi方式
      select 姓名,课程号,成绩、
      from 学生
      left outer join 学习 on 学生.学号=学习.学号;

      2） 右外连接（right outer jion）：查询所有的课程信息及选修该课程的学生的学号以及成绩。

      1
      2
      3
      4
      5
      6
      7
      8
      -- theta方式
      select 课程名,学号,成绩
      from 学习,课程
      where课程.课程号=(+)学习.课程号;
      -- ansi方式
      select 课程名,学号,成绩
      from 学习
      right outer join 课程 on 学习.课程号=课程.课程号;

    • 复合条件连接查询(where子句有多个条件)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      select 学生.学号,姓名,成绩
      from 学生,学习
      where 学生.学号=学习.学号
      and 学习.课程号='180101'
      and 学习.成绩>90;-- theta

      select 学生.学号,姓名,成绩
      from 学生 join 学习 on 学生.学号=学习.学号
      and 学习.课程号='180101'
      and 学习.成绩>90; -- ansi

      select 学生.学号,姓名,课程号,学习.成绩
      from 学生,学习,课程
      where 学生.学号=学习.学号
      and 学习.课程号=课程.课程号; -- 多表连接，查询每个学生选修的课程名以及成绩

    • 集合运算查询（交、并、差）

    • ps：书上用的下面的写法， 是有问题的，不对，他不会展示学生选的其余课程。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      交-union
      并-intersect
      差-except
      (select 学号,课程号,成绩
      from 学习
      where 课程号='180101')
      	union  -- 想保留重复的时候用union all，也可以用or
      (select 学号,课程号,成绩
      from 学习
      where 课程号='180102');

      (select 学号,课程号,成绩
      from 学习
      where 课程号='180101')
      	intersect  -- 想保留重复的时候用intersect all，mysql不支持，可以换in或者exists
      (select 学号,课程号,成绩
      from 学习
      where 课程号='180102');

      (select 学号,课程号,成绩
      from 学习
      where 课程号='180101')
      	except   -- 想保留重复的时候用except all，MySQL不支持，可以换not in或者not exists
      (select 学号,课程号,成绩
      from 学习
      where 课程号='180102');
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      select 学号,课程号,成绩
      from 学习
      where 学号 in(
      (select 学号
      from 学习
      where 课程号='180101')
      	union  
      (select 学号
      from 学习
      where 课程号='180102'));
      select 学号,课程号,成绩
      from 学习
      where 学号 in(
      (select 学号
      from 学习
      where 课程号='180101')
      	INTERSECT  
      (select 学号
      from 学习
      where 课程号='180102'));
      select 学号,课程号,成绩
      from 学习
      where 学号 in(
      (select 学号
      from 学习
      where 课程号='180101')
      	EXCEPT 
      (select 学号
      from 学习
      where 课程号='180102'));

  • 嵌套查询

    一个select ···from ···where称为一个查询块，将一个查询块嵌套在另一个查询块里面的where子句或者having条件中的叫做嵌套查询。

    1
    2
    3
    4
    5
    6
    select 姓名
    from 学生
    where 学号 in
    (select 学号
    from 学习
    where 课程号='180101'); -- 查询选修了180101课程号的学生的姓名

    上层的查询叫做父查询，下层的查询叫做子查询，允许多层嵌套查询，子查询不能用order by。

    • 带in谓词的子查询

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      -- 查询与王颖在同一个学院学习的学生学号和姓名
      select 学号,姓名
      from 学生
      where 学院 in 
      (select 学院
      from 学生
      where 姓名='王颖');

      -- 查询选修了数据库原理这门课的学生学号和姓名
      select 学号,姓名
      from 学生
      where 学号 in
      (select 学号
      from 学习
      where 课程号 in
      (select 课程号
      from 课程
      where 课程名='数据库原理'));

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

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      select 学号,姓名
      from 学生
      where 学号 in
      (select 学号
      from 学习
      where 课程号 = -- 比较运算符只能返回单值
      (select 课程号
      from 课程
      where 课程名='数据库原理'));

      -- 查询180106课程中成绩高于课程平均分的学生学号姓名成绩
      select 学生.学号,姓名,成绩
      from 学生 inner join 学习 on 学生.学号=学习.学号
      where 课程号='180106'
      and 成绩>
      (select avg（成绩) as 课程平均分
      from 学习
      where 课程号='180106');

    • 带有any或者all谓词的子查询

      > 大于子查询结果的某个值 大于子查询结果的所有值
      < 小于子查询结果的某个值 小于子查询结果的所有值
      >= 大于等于子查询结果的某个值 大于等于子查询结果的所有值
      <= 小于等于子查询结果的某个值 小于等于子查询结果的所有值
      = 等于子查询结果的某个值 没意义
      != 不等于子查询结果的某个值 不等于子查询结果的任何一个值 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      -- 查询其他学院比计算机学院某个学生年龄小的学生名单，排序，也可以用集函数max
      select 姓名
      from 学生
      where year(now())-出生年份<any
      	(select year(now())-出生年份
      	from 学生
      	where 学院='计算机')
      and 学院<>'计算机'
      order by year(now())-出生年份 desc;

      select 姓名
      from 学生
      where year(now())-出生年份<
      (select max(year(now())-出生年份)
      from 学生
      where 学院='计算机')
      and 学院<>'计算机';

      集函数和any/all对应关系

      = <>/!= < <= > >=
      any in – <max <=max >min >=min
      all – not in <min <=min >max >=max

    • 带有exists谓词的子查询

      exists代表存在量词，不返回任何数据，只返回true或者false。一般用*。

      带有 exists 谓词的子查询

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      -- 查询选修了180102号课程的学生学号和姓名
      select 学号,姓名
      from 学生
      where exists
      (select *
      from 学习
      where 学生.学号=学习.学号
      and 学习.课程号='180102');
      -- 查询没有选修数据库原理课程的学生
      select 学号,姓名
      from 学生
      where not exists
      (select *
      from 课程 inner join 学习 on 课程.课程号=学习.课程号
      where 学习.学号=学生.学号
      and 课程名='数据库原理');

    • 除运算

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 选修过全部课程的学生的学号和姓名
    -- 该学生选修的课的集合包含所有课程的集合
    -- 不存在（B-A），B是小集合，A是大集合
    select 学号,姓名
    from 学生
    where not exists(
    select *
    from 课程
    where not exists(
    select *
    from 学习
    where 学习.学号=学生.学号
    and 学习.课程号=课程.课程号));
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    -- 查询至少选修了091501学生选修的全部课程的学生学号
    -- 某位学生选修的课程是大集合 091501学生选修的全部课程是小集合
    select 学号
    from 学生
    where not exists
    (select *
    from 学习 as first
     where 学号='091501'and not exists
    (select *
    from  学习 as second
    where second.学号=学生.学号
    and second.课程号=first.课程号));
    -- 查询选修了全部课程的学生的姓名
    -- 某位学生选的课是大集合，全部课程是小集合
    select 姓名
    from 学生
    where not exists
    (select *
    from 课程
    where not exists
    (select *
    from 学习
    where 学习.学号=学生.学号
    and 学习.课程号=课程.课程号));
    -- 查询被所有学生都选修的课程名称
    -- 某个课程选修的人是大集合 全部学生是小集合
    select 课程名
    from 课程
    where not exists
    (select *
    from 学生
    where not exists
    (select *
    from 学习
    where 学习.学号=学生.学号
    and 学习.课程号=课程.课程号));

数据更新

  • 插入数据

    • 功能：将新的元组插入表中

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      -- 插入单个元组
      insert
      into 学生
      values('091530','夏雨','男','海南','1999','计算机')
      insert
      into学习（学号，课程号）
      values（‘091530’，‘080102’）;-- 没有的列取空值

      -- 插入子查询结果
      -- 统计每门课平均分，把结果存进去表中
      -- 第一步：建表
      CREATE TABLE 课程平均分
      (课程名 char(15),
      课程号 char(8),
      平均分 double);
      -- 第二步：插入数据
      INSERT 
      INTO 课程平均分(课程号,课程名,平均分)
      select 课程号,课程名,avg(成绩)
      from 学习,课程
      where 课程.课程号=学习.课程号
      group by 课程名,课程号;

  • 修改数据（带有where子句）

    • 如果只修改一行，那么where子句具有唯一性

      • 将091611号学生籍贯改为江苏(修改某个元组)

        1
        2
        3
        UPDATE 学生
        SET 籍贯='江苏'
        WHERE 学号='091611';-- 学号唯一

      • 将选修了180101号课程的学生成绩增加一分（修改多个元组）

        1
        2
        3
        UPDATE 学习
        SET 成绩=成绩+1
        where 课程号='180101';

      • 将计算机学院学生成绩清零（带有子查询）

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        UPDATE 学习
        SET 成绩=0
        WHERE 学号 in
        (SELECT 学号
        FROM 学生
        where 学院='计算机学院');

        set SQL_safe_updates=0;
        UPDATE 学习
        SET 成绩=0
        WHERE EXISTS
        (SELECT *
        FROM 学生
        where 学院='计算机'
        and 学生.学号=学习.学号);

  • 删除数据

    成行删除

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    DELETE 
    FROM 学生
    WHERE 学号='092010';-- 删掉一些元组

    DELETE
    FROM 学习;-- 删掉所有元组

    -- 带有子查询
    DELETE 
    FROM 学习
    WHERE 学号 IN 
    (SELETE 学号
    FROM 学生
    WHERE 学院='计算机');

    DELETE 
    FROM 学习
    WHERE 学号 EXISTS
    (SELETE 学号
    FROM 学生
    WHERE 学院='计算机'
    AND 学生.学号=学习.学号);

视图（也是一种表）

​ 功能：提高了数据的安全性

​ 新建的视图后面的属性列是新取的名字，不存在冲突问题。与之前的表没有什么关系，也可以换名字

  • 建立视图

    • 行列子集视图（从单个基本表抽取若干行若干列）

      1
      2
      3
      4
      5
      6
      7
      -- 建立计算机学院学生的视图
      CREATE VIEW CS_STU
      AS
      SELETE 学号,姓名,籍贯
      FROM 学生
      WHERE 学院='计算机';
      -- 如果后面加上with check option 的话表示透过视图进行数据更新，修改插入删除操作都会自动检查是否是计算机学院的学生

    • 基于多个表

      1
      2
      3
      4
      5
      6
      7
      8
      9
      -- 建立计算机学院选修了《数据库原理》的学生成绩视图
      create view cs_db(学号,姓名,成绩)
      as
      select 学生.学号,姓名,成绩
      from 学生,学习,课程
      where 学生.学号=学习.学号、
      and 学习.课程号=课程.课程号
      and 学院='计算机'
      and 课程名='数据库原理';

    • 基于视图的视图

      1
      2
      3
      4
      5
      6
      -- 建立计算机学院选修《数据库原理》而且成绩在九十分以上的同学的视图
      create view cs_db_good-- 后面不写的话直接用select的，select后面必须有
      as
      select 学号,姓名,成绩
      from cs_db
      where 成绩>90;

    • 带有表达式的视图

      1
      2
      3
      4
      5
      -- 建立一个反映学生年龄的视图
      create view stu_age(学号,姓名,年龄)
      as
      select 学号,姓名,year(now())-出生年份
      from 学生;

    • 带有分组的视图

      1
      2
      3
      4
      5
      6
      -- 建立学生平均成绩视图，显示学号、姓名以及平均成绩
      create view stu_grade(学号,姓名,平均成绩)-- 视图相当于新建一个表，所以不需要学生.学号，相当于重新定义列，后面如果不写直接用select的
      as
      select 学生.学号,姓名,avg(成绩) as 平均成绩
      from 学习 inner join 学生 on 学生.学号=学习.学号
      group by 姓名,学生.学号;

  • 删除视图

    1
    2
    DROP VIEW 视图名;
    DROP VIEW stu_grade;

  • 查询视图(其实就是查询基本表)

    1
    2
    3
    4
    5
    6
    cs_stu 定义：
    create view cs_stu
    as
    select 学号,姓名,籍贯
    from 学生
    where 学院='计算机';
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 查询每门课中成绩高于平均分的学生学号
    select sname,cname,grade
    from s inner join sc as s1 on s.sno=s1.sno
    inner join c on s1.cno=c.cno
    where grade>
    	(select avg(grade)
    	from sc as s2
    	where s2.cno=s1.cno);
    -- 改为(视图表里面有成绩课程号课程名)
    select sname,cname,grade
    from sc as s1 join avg_cou on s1.cno=avg_cou.cno
    where grade>=平均分;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 查询计算机学院选修了数据库原理课程的学生的学号、姓名和成绩
    select 学号,姓名,成绩
    from cs_stu.学习,课程
    where cs_stu.学号=学习.学号
    and 学习.课程号=课程.课程号
    and 课程名='数据库原理';

    -- 转换为
    select 学号,姓名,成绩
    from 学生 inner join 学习 on 学生.学号=学习.学号
    inner join 课程 on 课程.课程号=学习.课程号 
    where 课程名='数据库原理'
    and 学院='计算机';

  • 更新视图（其实就是更新基本表）

  • 不一定所有视图都是可以更新的，只有从单个基本表导出来的并且只是去掉了某些行和列的视图才可以更新（称之为行列子集视图）

    • 如果视图的属性是来自于属性表达式或者常数，那么就只能删除，不能插入或者修改。

    • 如果视图属性来自于集函数，那么是不可以更新的。

      更新学生平均成绩视图，把学号为091527的学生的平均成绩改为90

      1
      2
      3
      4
      UPDATE STU_AVG
      SET 平均成绩=90
      WHERE 学号='091527';
      -- stu_avg中平均成绩是avg(成绩)，有函数，不能更新

    • 如果视图定义当中有group by，那么就不可以对他进行更新。

    • 视图定义有distinct选项，那么也不可以更新。

    • 如果视图定义有嵌套查询，而且嵌套查询的子句涉及该视图的基本表，那么也是不可以更新的。

    • 如果视图有两个及以上的基本表构成，也不能更新。

    • 在一个不允许更新的视图上定义一个新的视图，那么新的视图也不能够更新。

      建立考试成绩在总平均分之上的学生的视图

      1
      2
      3
      4
      5
      6
      7
      create view good_stu
      as
      select 学号,课程号,成绩
      from 学习
      where 成绩>
      	(select avg(成绩)
      	from 学习);
    1
    2
    3
    4
    5
    6
    cs_stu 定义：
    create view cs_stu
    as
    select 学号,姓名,籍贯
    from 学生
    where 学院='计算机';

  • 将计算机学院学生视图中学号为091503的学生姓名改成刘辰。

    1
    2
    3
    4
    5
    6
    7
    UPDATE cs_stu
    SET 姓名='刘辰'
    WHERE 学号='091503';
    -- 转换为对基本表的更新
    UPDATE 学生
    SET 姓名='刘辰'
    where 学号='091503' and 学院='计算机';

  • 向计算机学院的学生视图中国插入一个新的学生记录（学号：091622，姓名：赵新，籍贯：浙江）

    1
    2
    3
    4
    5
    6
    7
    INSERT
    INTO CS_STU
    VALUES('091622',赵新,浙江);
    -- 转换为对基本表的更新
    INSERT
    INTO 学生(学号,姓名,籍贯,学院)
    VALUES('091622','赵新','浙江','计算机');

  • 删除计算机学院的学生视图中学号为091422的学生记录

    1
    2
    3
    4
    5
    6
    7
    DELETE
    FROM CS_STU
    WHERE 学号='091422';
    #转换为基本表的删除
    DELETE
    FROM 学生
    WHERE 学号='091422' AND 学院='计算机';

数据控制

  • 授权

    • 把学生表查询权限授予用户user1

      1
      2
      3
      GRANT SELECT
      ON TABLE 学生
      TO USER1;

    • 把查询学习表和修改成绩权限授权user2

      1
      2
      3
      GRANT UPDATE(成绩),select
      on table 学习
      to user2;

    • DBA把在数据库SMD中建立表的权限授予用户3

      1
      2
      3
      GRANT CREATE
      ON DATABASE SMD
      TO USER3;

    • 把学生表的insert权限授予user6用户，并允许他将这个权限授予别人

      1
      2
      3
      4
      grant insert
      on table 学生
      to user6
      with grant option;-- 不加最后这句话就不能传播权限

  • 收回权限(把grant改成revoke)

    • 级联回收user6->user7->user8······
课程复习

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！