NEFU数据库基本操作实验总结

左手的ㄟ右手 2024-04-17 11:43 65阅读 0赞

前言

> SQL语言集数据定义、数据操纵、数据查询、数据控制功能于一体。  
> 数据定义：create，drop，alter  
> 数据操纵：增(insert into … )，删(delete from …)，改(update … set …)  
> 数据查询：select  
> 数据控制：grant(授予权限)，revoke(收回权限)

## 一、数据定义（create，alter，drop） ##

基本知识概述

--创建表
    create table 表名(
       
    );
     
    -- 查看表结构
    desc 表名;
    -- 修改表名
    alter table 表名 rename to 新的表名;
    -- 添加一列
    alter table 表名 add 列名 数据类型;
    -- 删除列
    alter table 表名 drop 列名;
    -- 删除表
    drop table 表名;
    drop table  if exists 表名 ;

1.建立基本表  
创建教材中的学生表（Student）、学生选课表（SC）、课程表（Course）  
（1）学生表：Student (Sno，Sname，Sage，Ssex，Sdept），其中学号Sno为主码

/*
    Sno，Sname，Sage，Ssex，Sdept
    代表学生的学号、姓名、年龄、性别和所在系别
    */
    Create Table Student(
      Sno number Primary Key,
      Sname varchar(10),
      Sage int,
      Ssex char(2),
      Sdept varchar2(10)
    );

![4d258b2c28ed49cb80d12f53ab11dfdb.png][]

**另外一种形式**

Create Table Student(
    --Sno 学号
      Sno number,
    --姓名
      Sname varchar(10),
    --年龄
      Sage int,
    --性别
      Ssex char(2),
    --系别
      Sdept varchar2(10),
      Constraint pk_Student primary key(Sno)
    );

![966c73ffdcf84907b52bf1fadb2b2fe3.png][]

2）·课程表：Course (Cno, Cname, Cpno, Ccredit)其中课程号Cno主码；先行课为外码参照Course表中Cno字段。

/*
    Cno, Cname, Cpno, Ccredit
    课程的编号、名称、先修课程编号和学分
    */
    Create Table Course(
      Cno number Primary Key,
      Cname varchar(10),
      Cpno varchar2(10)references Course(Cno),
      Ccredit int
    );

**另外一种形式**

/*
    Cno, Cname, Cpno, Ccredit
    课程的编号、名称、先修课程编号和学分
    */
    Create Table Course(
      Cno number Primary Key,
      Cname varchar(10),
      Cpno varchar2(10),
      Ccredit int,
    --[constraint pk_Course]可选可不选，选了可自定义约束名
      constraint pk_Course Foreign key (Cpno) references Course(Cno)
    );

3)·学生选课表：SC(Sno, Cno, Grade)其中学号Sno、课程号Cno为主码；Sno为外码参照Student表中sno字段；Cno为外码参照Course表中cno字段。

Create Table Sc(
      Sno number references Student(Sno),
      Cno number references Course(Cno),
      Grade int,
      primary key(Sno,Cno)
    );

![516e230fc355463dbc8c2ffe8e65ffbe.png][]

2.修改基本表

1）在Student表中加入属性BloodType（char（2）型）。

alter table Student 
    add BloodType char(2);

2）修改表student中的Sdept属性的数据类型为varchar2(40)，注意和定义表的时候类型不同。

alter table Student 
    modify Sdept varchar2(40);

3）给表student的sage列添加一个自定义约束sage必须大于15且小于30。

alter table Student 
    --[constraint ck_Sage]可选，但不选删除较为麻烦
    add constraint ck_Sage check (Sage > 15 and Sage < 30);

4）删除3）中新添加的约束。

alter table Student 
    drop constraint ck_Sage;

5）删除表student中的字段BloodType。

alter table Student 
    drop (BloodType);  --记得加括号，不加括号就要在drop前面写column

3.删除基本表  
（1）删除基本表Student。

drop table Student;
    --请实验最后删除

（2）删除基本表SC。

drop table SC;

4.索引操作

1.建立索引

（1）在Student表上建立关于Sname的唯一索引stusnam+学号后四位

create unique index stusnam3067
    on student(sname);

（2）在SC表上建立关于Sno升序、Cno降序的唯一索引i\_sc+学号后四位

Create Unique Index i_sc3067
    On Sc(Sno ASC,Cno DESC);

2.删除索引

1）删除Student表上的索引stusnam+学号后四位

drop index stusnam3067;

2）删除Course表上的索引i\_sc+学号后四位

drop index i_sc3067;

## 二、数据操纵（insert，update，delete） ##

**DML(Data Manipulation Language)数据操作语言概述**

----增加 insert into
    -- 写全所有列名
    insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
    -- 不写列名（所有列全部添加）
    insert into 表名 values(值1,值2,...值n);
    -- 插入部分数据
    insert into 表名(列名1,列名2) values(值1,值2);
    ----删除 delete
    -- 删除表中数据
    delete from 表名 where 列名  = 值;
    -- 删除表中所有数据
    delete from 表名;
    -- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
    truncate table 表名;
    ----修改 update
    -- 不带条件的修改(会修改所有行)
    update 表名 set 列名 = 值;
    -- 带条件的修改
    update 表名 set 列名 = 值 where 列名=值;

1.插入数据  
1）向Student表中插入数据  
2）向Course表中插入数据  
3）向SC表中插入数据

可参考如下数据，也可不参考。

![在这里插入图片描述][20200705090121559.png]

insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215121,'李勇','男',20,'CS');
    insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215122,'刘晨','女',19,'CS');
    insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215123,'王敏','女',18,'MA');
    insert into STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) values(200215125,'张立','男',19,'IS');
    
    --记得先把COURSE表中的外键约束关了，否则数据插不进去
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(1,'数据库',5,4);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(2,'数学',null,2);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(3,'信息系统',1,4);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(4,'操作系统',6,3);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(5,'数据结构',7,4);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(6,'数据处理',null,2);
    insert into COURSE(CNO,CNAME,CPNO,CCREDIT) values(7,'PASCAL语言',6,4);
    
    insert into SC(SNO,CNO,GRADE) values(200215121,1,92);
    insert into SC(SNO,CNO,GRADE) values(200215121,2,85);
    insert into SC(SNO,CNO,GRADE) values(200215121,3,88);
    insert into SC(SNO,CNO,GRADE) values(200215122,4,90);
    insert into SC(SNO,CNO,GRADE) values(200215122,3,80);

关闭操作

![c7f1d1cdf2b6460e98e3f89b1cd00292.png][]

2.修改数据  
1）将王敏的同学的年龄改为20。

update student
    set sage = 20
    where sname = '王敏';

2）将全部同学的年龄加1。

update student
    set sage = sage + 1;

3）将’CS’系同学的选课信息中的成绩置0。

update SC set grade=0 where sno in 
    (
        select sc.sno from STUDENT join SC on(student.sno=sc.sno)
        where sdept='CS'
    );

3.删除数据  
1）删除和’刘晨’在同一个系的学生的信息。（包括刘晨自己也会被删除）

delete from STUDENT where sdept in
    (
        select sdept from STUDENT
        where sname='刘晨'
    );

> 然后发现删除不了，因为有外键约束，SC表依赖当前表的SNO，  
> 所以要**先删掉SC表中SNO的外键约束**`alter table sc drop constraint SYS_C0051208;`  
> 再执行删除语句即可。

2）删除’CS’系同学的选课信息。

delete from SC where sno in
    (
        select sno from STUDENT
        where sdept='CS'
    );

### 三、数据查询（select） ###

**DQL(Data Query Language)数据查询语言**

> **基础关键字**
> 
>  *   **BETWEEN...AND （**选取区间数据）**和 IN( 集合)**
>  *   **is not null(不为null值) 与 like（模糊查询）、distinct（去除重复值）**
>  *  **WHERE – 条件过滤**
>  *  g**roup by 分组查询 HAVING 筛选分组**
>  *  **order by 排序查询**
> 
>     -- 查询年龄大于等于20 小于等于30				
>     SELECT * FROM student WHERE age >= 20 &&  age <=30;
>     SELECT * FROM student WHERE age >= 20 AND  age <=30;
>     SELECT * FROM student WHERE age BETWEEN 20 AND 30;
>     				
>     -- 查询年龄22岁，18岁，25岁的信息
>     SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
>     SELECT * FROM student WHERE age IN (22,18,25);
> 
> **单表查询**
> 
> 查询全部列
> 
>     SELECT * FROM 表名称;
> 
> 指定查询
> 
>     SELECT 列名称 FROM 表名称;
> 
> **多表查询**
> 
> **连接查询**
> 
> 内连接查询：
> 
> 隐式内连接：使用where条件消除无用数据
> 
>     -- 查询员工表的名称，性别。部门表的名称
>     SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
>      
>     SELECT 
>         t1.name, -- 员工表的姓名
>         t1.gender,-- 员工表的性别
>         t2.name -- 部门表的名称
>     FROM
>         emp t1,
>         dept t2
>     WHERE 
>         t1.`dept_id` = t2.`id`;
> 
> 2.显式内连接
> 
>     -- 语法： 
>     select 字段列表 from 表名1 [inner] join 表名2 on 条件
>     -- 例如：
>     SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;   
>     SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
> 
> **外连接查询**
> 
> **子查询：查询中嵌套查询**

1.包括排序、分组的单表查询

（1）求数学系学生的学号和姓名

select sno,sname 
    from student 
    where sdept='MA';

（2）求选修了课程的学生学号。

使用distinct方法

--学号distinct去重，因为SC表中学号可以重复，一个学号可选多门课
    select distinct sno from sc;

使用含有in的子查询

select sno 学号 from Student
    where Sno in 
    (select Sno 
     from SC);

使用含有exists的子查询方法

select sno 学号 
    from Student
    where EXISTS 
    (select Sno 
     from SC
     where Student.Sno = SC.Sno);

使用=ANY的嵌套查询

select sno 学号 
    from Student
    where Sno = any
    (select Sno 
     from SC
     where Student.Sno = SC.Sno);

（3）求选修课程号为‘2’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

select sno as 学生号,grade as 成绩
    from sc 
    where cno=2 
    order by grade DESC,sno ASC;

（4）求选修课程号为’2’且成绩在80～90之间的学生学号和成绩，并将成绩乘以0.8输出。

-- 注意and的优先级高于or，加一个括号即可
    select sno as 学生号,grade*0.85 as 成绩
    from sc 
    where cno=2 and grade>=80 and grade<=90;

另一种形式

select sno as 学生号,grade*0.85 as 成绩
    from sc 
    where cno=2 and grade between 80 and 90;

（5）求数学系或计算机系姓张的学生的信息。

select * 
    from student S,SC C
    where sname like '张%' and (sdept='MA' or sdept='CS') and S.Sno = C.Sno;

自然连接优化

Select *
    From student
    Natural Join Sc
    Where sname Like '张%';

（6）求缺少了成绩的学生的学号和课程号。

SELECT Student.Sno, SC.Cno
    FROM Student
    LEFT JOIN SC ON Student.Sno = SC.Sno
    WHERE SC.grade IS NULL;

（7）查询各个课程号与相应的选课人数。**（本题需要运行截图）**

使用GROUP BY语句

Selete Course.cno AS 课程号, COUNT(Sc.cno) AS 选课人数
    From course
    left JOIN Sc on course.cno = Sc.cno
    GROUP BY course.cno;

**（二） 多表连接查询****（报告中填入题目要求和命令，除（1）题外不需要运行截图）**

（1）查询每个学生的情况以及他所选修的课程。**（本题需要运行截图）**

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

（2）求学生的学号、姓名、选修的课程及成绩。

select student.sno,sname,cno,grade 
    from student,sc 
    where student.sno=sc.sno;

（3）求选修课程号为‘1’且成绩在90分以上的学生学号、姓名和成绩。

内连接查询

select student.sno,sname,grade 
    from student,sc 
    where student.sno=sc.sno and cno=1 and grade>=90;

使用INNER JOIN和子查询结合

Select s.Sno, s.Sname, sc.Grade
    From Student s
    inner Join (
        Select Sno, Grade From Sc Where Cno = '1' And Grade > 90
    ) sc On s.Sno = sc.Sno;

> 首先从Sc表中选出选修课程号为‘1’且成绩在90分以上的学生学号和成绩，然后将结果与Student表连接，最后选出学生学号、姓名和成绩

（4）查询每一门课程的间接先行课。

使用自连接

SELECT c1.Cname 课程, c2.Cname 先导课
    FROM Course c1, Course c2
    WHERE c1.Cpno = c2.Cno;

使用INNER JOIN语句

Select c1.Cname 课程, c2.Cname 先导课
    From Course c1
    Inner Join Course c2 on c1.Cpno = c2.Cno;

（5）查询与’刘晨’在同一个系学习的学生。

使用子查询

Select*
    From Student
    where Sdept = (
    Select Sdept From Student
    Where Sname = '刘晨');

IN语句

Select*
    From Student
    where Sdept in (
    Select Sdept From Student
    Where Sname = '刘晨');

集合操作

Select*
    From Student
    Where Sdept = (Select Sdept From Student Where Sname = '刘晨')
    Union
    Select Sno, Sname, Sage, Ssex, Sdept
    From Student
    Where Sno = (Select Sno From Student Where Sname = '刘晨');

（6）查询选修了课程名为‘信息系统‘的学生学号和姓名。

select sc.sno,sname 
    from student,sc,course 
    where student.sno=sc.sno and sc.cno=course.cno and cname='信息系统';

（7）查询平均成绩在80分以上的学生学号和平均成绩

select sno,
    avg(grade) from sc 
    group by sno
    having avg(grade)>=80;

（8）查询选修了1门以上课程的学生的学号。

select sno from sc group by sno having count(*)>1;

3.嵌套查询  
（1）求选修了信息系统的学号和姓名。

方法一

select S.sno 学号,S.sname 姓名
    from student S,course C,SC
    where S.sno = SC.sno and C.cno = SC.cno and C.cname='信息系统';

方法二

select sno 学号,sname 姓名
    from student where sno in(
        select sno from sc where cno in (
            select cno from course 
            where cname='信息系统'
        )
    );

（2）查询与刘晨在同一个系学习的学生。

select * 
    from student 
    where sdept in(
    	select sdept from student 
    	where sname='刘晨'
    )and sname<>'刘晨';

（3）求选修1号课程的成绩高于刘晨的成绩（指刘晨选修的所有的课程的成绩）的学生学号及成绩。

select sno,grade 
    from sc 
    where cno=1 and grade >(
        select max(grade) from sc where sno in(
            select sno from student
            where sname='刘晨')
    );

（4）求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。

select * 
    from student 
    where sage <(
        select max(sage) from student 
        where sdept='CS'
    );

（5）求其他系中比计算机系学生年龄都小的学生姓名及年龄。

select sname,sage 
    from student 
    where sage <(
        select min(sage) from student 
        where sdept='CS'
    );

（6）求没有选修3号课程的学生姓名。

select sname 学生
    from student 
    where not exists 
    (
        select * from sc
        where cno=3 and student.sno=sc.sno
    );

（7）查询选修了全部课程的学生姓名。**（本题需要运行截图）**

select sname from student where not exists 
    (
        select * from course where not exists
        (
            select * from sc
            where sc.cno=course.cno and sc.sno=student.sno
        )
    );

（8）求至少选修了学号为“200215121”的学生所选修全部课程的学生学号和姓名。**（本题需要运行截图）**

select * from sc sc1 where sno=200215121 and not exists
    (
        select * from sc sc2
        where sc1.cno=sc2.cno and sc2.sno=200215122
    );

（9）求选修课程超过2门的学生的学号和姓名。

Select s.sno,s.sname
    From student s, sc
    Where s.sno=sc.sno
    Group By s.sno,s.sname
    Having COUNT(*)>2;

## 四、视图 ##

> **语法：**
> 
>     CREATE VIEW 视图名 AS
>     SELECT 列名
>     FROM 表名
>     WHERE 查询条件;
> 
> CREATE OR REPLACE VIEW是SQL语句中的一种，用于创建或替换一个视图。CREATE VIEW用于创建一个新的视图，而CREATE OR REPLACE VIEW则用于替换已经存在的视图。  
> 
> 
>     CREATE OR REPLACE VIEW 视图名 AS
>     SELECT 列名
>     FROM 表名
>     WHERE 查询条件;

（1）建立信息系学生的视图。并查询此视图，观察结果。

create view CS_STUDENT as select * from student where sdept='CS';
    select * from CS_STUDENT;

（2）在视图上建立信息系选修了1号课程的学生的视图。

create view CS_STUDENT_CNO1(sno,sname,sage,ssex,sdept) as
    select sc.sno,sname,sage,ssex,sdept from CS_STUDENT,sc 
    where sc.sno=CS_STUDENT.sno and sc.cno=1;

（3）将学生的学号及其平均成绩定义为一个视图。

create view GRADE_AVG1(sno,grade_avg) as
    select sno,avg(grade) from sc group by sno;

（4）将Student表中所有女生记录定义为一个视图F\_stu（sno，sname，sdept，sex），并设置其更新限制with check option。

create view F_stu(sno,sname,sdept,sex) as
    select sno,sname,sdept,ssex from student where ssex='女'
    with check option;
    select * from F_stu;

（5）对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’插入视图中，结果如何？

insert into F_stu(sno,sname,sdept) values(200215129,'smith','MA');
    select * from F_stu;

结果显示：视图 WITH CHECK OPTIDN where 子句违规。

（6）对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’，sex为‘女’插入视图中，结果如何？

insert into F_stu(sno,sname,sdept,sex) values(200215139,'smith','MA','女');

## 五、授权（grant） ##

DCL(Data Control Language)数据控制语言

----管理用户
    
    --添加用户
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    
    --删除用户
    DROP USER '用户名'@'主机名';
    
    ----权限管理
    
    -- 查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
    SHOW GRANTS FOR 'lisi'@'%';
    
    ------ 授予权限
    grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
     
    -- 给张三用户授予所有权限，在任意数据库任意表上
    GRANT ALL ON *.* TO 'zhangsan'@'localhost';
    
    -- 撤销权限：
    revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
    
    ----角色（权限的集合）
    
    --创建一个角色
    create role testrole; # 角色名应该不能含数字，试了一下，role1无法创建
    
    --给角色授予权限
    grant select on sc to testrole;
    
    --将角色授予某一用户
    grant testrole to S2021xxxxxxx;
    
    --检查此用户是否具有相应的权限
    select * from S2021xxxxxxx.sc; # 在u5中查询

1.  假设你的用户名是S20130001（做实验时请根据你的账号修改），把你在数据库中创建的Student表的查询权限授给用户” S20130002”后，同组同学 ” S20130002”执行相应的查询。

grant select on student to S20212xxxxx;

（1）查询S20130001用户的Student表中全体学生的详细记录。**（本题需要运行截图）**

select sname,sno,ssex from S20212xxxxx.student;

（2）查询S20130001用户Student表中所有姓刘的学生的姓名、学号和性别。**（本题需要运行截图）**

（3）查询S20130001用户Student表中名字中第二字为“阳”字的学生的姓名和学号。

2.把S20130001用户的Student表和Course表的全部权限授予用户 ” S20130002”；然后让” S20130002”用户修改S20130001的数据。

grant all on student to S20212xxxxx; # all表示全部权限
    grant all on course to  S20212xxxxx;

3.把S20130001用户的表Student的修改学生学号的权限赋予用户”S20130002”，然后让S20130001用户修改S20130001的student表的SNO数据。

grant update(sno) on student to  S20212xxxxx;

4.把S20130001用户的SC表的插入权限授予“S20130002”用户，然后让“S20130002”用户向SC表插入一条记录。

grant insert on sc to S20212xxxxx;

5.把对表SC的查询权限授予所有用户。

grant select on sc to public; # public表示所有用户

（1）让“S20130002”用户查询S20130001用户的SC表中选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

select sno,grade from  S20212xxxxx.sc where cno=3 order by grade DESC;

（2）让“S20130002”用户查询S20130001用户的SC表中各个课程号与相应的选课人数。

select cno,count(*) from  S20212xxxxx.sc group by cno;

（二）回收权限（报告中填入题目要求和命令，除4题外不需要运行截图）  
1.收回用户“S20130002”修改学生学号的权限

revoke update on student from S20212xxxxx;

注意：UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column  
（不能只回收一列的update权限，只能回收一个表的）

2.收回所有用户对表sc的查询权限

revoke select on S20212xxxxx.sc from public;

3.收回用户“S20130002”sc表的insert权限

revoke insert on D2018214184.sc from S20212xxxxx;

4.在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）（本题需要运行截图）

在u2用户上：update S20212xxxxx.student set sname='王敏' where sno=200215121;  
显示：“权限不足”  
在u2用户上：select \* from S20212xxxxx.sc;  
显示：“table or view does not exist”  
在u5用户上：insert into S20212xxxxx.sc(sno,cno,grade) values(200215121,7,99);  
显示：“table or view does not exist”

说明回收权限成功。

## 六、角色（权限的集合） ##

1.创建一个角色。

create role testrole; # 角色名应该不能含数字，试了一下，role1无法创建

2.给角色授予权限。

grant select on sc to testrole;

3..将角色授予某一用户。

grant testrole to S2021xxxxxxx;

4.检查此用户是否具有相应的权限。

select * from S2021xxxxxxx.sc; # 在u5中查询

## 七、**完整性** ##

**1．建立教师表Teacher，要求教师名称Tname列取值唯一，教师编号TNO列为主码。**

create table Teacher
    (
        TNO int primary key,
        TNAME varchar(20) unique
    );

2.建立学生登记表Student，要求学号在9000至9999之间，年龄<29,性别只能是’男’或’女’，姓名非空。

create table Student 
    (
        SNO int primary key,
        SAGE int,
        SSEX char(2), 
        SNAME varchar(20) not null,
        constraint sno_ck check(SNO>=9000 and SNO<=9999),
        constraint sage_ck check(SAGE<29),
        constraint ssex_ck check(SSEX in ('男','女'))
    );

3.修改表Student的结构，由年龄小于29改为小于40

alter table student drop constraint sage_ck; 
    alter table student add constraint sage_ck check(sage<40);

修改约束不能直接修改，只能删除再添加。  
尝试用`alter table student modify sage int constraint sage_ck1 check(sage<40);`

4．建立课程表COURSE，要求课程表中的每门课程的学分不得超过7分，且主讲教师字段TNO参照Teacher表TNO字段，且当删除教师表中一行记录时，如果它被参照，则将Course表中相应记录中TNO的值设置为空。建立表SC，要求SNO参照STUDENT表的学号，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。对上述新建立和修改定义的表，每个表输入3条数据，其中1条数据符合完整性约束，2条违反约束条件的，验证和体会Oracle的实体完整性和参照完整性。

（1）建表和插入数据。

create table Course
    (
        CNO int primary key,
        TNO int,
        CREDIT int check(CREDIT<=7),
        foreign key (TNO) references Teacher(TNO) on delete set null
    );
    
    create table SC
    (
        SNO int,
        CNO int,
        primary key (SNO,CNO),
        foreign key (SNO) references Student(SNO) on delete cascade,
        foreign key (CNO) references Course(CNO) on delete cascade
    );
    
    insert into Teacher(TNO,TNAME) values(101,'刘晨');
    insert into Teacher(TNO,TNAME) values(102,'张勇'); 
    insert into Course(CNO,TNO,CREDIT) values(001,101,3);
    insert into Course(CNO,TNO,CREDIT) values(002,101,2);
    insert into Student(SNO,SAGE,SSEX,SNAME) values(9001,20,'男','张三');
    insert into Student(SNO,SAGE,SSEX,SNAME) values(9002,21,'男','李四');
    insert into SC(SNO,CNO) values(9001,001);
    insert into SC(SNO,CNO) values(9001,002);
    
    # 违反主码约束：ERROR 1062 (23000): Duplicate entry '101' for key 'PRIMARY'
    insert into Teacher(TNO,TNAME) values(101,'李苏');
    
    # 违反外键约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmps`.`Course`, CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`TNO`) REFERENCES `Teacher` (`TNO`) ON DELETE SET NULL)
    insert into Course(CNO,TNO,CREDIT) values(003,105,3);
    
    # 违反主码约束：ERROR 1062 (23000): Duplicate entry '9002' for key 'PRIMARY'
    insert into Student(SNO,SAGE,SSEX,SNAME) values(9002,20, '男','王五');
    
    # 违反外键约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmps`.`SC`, CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `Student` (`SNO`) ON DELETE CASCADE)
    insert into SC(SNO,CNO) values(8001,001);

[4d258b2c28ed49cb80d12f53ab11dfdb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/12/a5e60a4bf38d463492e29de399462c40.png
[966c73ffdcf84907b52bf1fadb2b2fe3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/12/92b3cfb83a804d089f6c94b8a1fe62b1.png
[516e230fc355463dbc8c2ffe8e65ffbe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/12/267bf06970d94d5496951fd57fb77d25.png
[20200705090121559.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/12/f98e2758b1e54cb7bcf8f72a72867b9e.png
[c7f1d1cdf2b6460e98e3f89b1cd00292.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/12/8ae9b6d756054cbe97f15dc603c23f3f.png