数据库第四次实验

目录

1.建立数据表并插入数据

2 视图的创建

2.1 行列子集视图的创建

2.2 多表视图

2.3视图上建立视图

2.4分组视图

2.5带表达式的视图

3 删除视图

​​​​​​​​​​​​​​4 查询视图

​​​​​​​5 更新视图

5.1 修改某一个属性

5.2 删除一条数据

5.3 插入一条数据

​​​​​​​6 索引

6.1建立索引

6.2修改索引

6.3删除索引

---

1.建立数据表并插入数据

SQL语句：

create database ExperimentFour;
use ExperimentFour;

create table Student4
(
   Sno char(2) not null,
   Sname varchar(20) not null,
   Ssex char(2) not null,
   constraint s2 check(Ssex in('男', '女')),
   Sage int not null,
   constraint s3 check (Sage between 0 and 130),
   Department varchar(20) not null
);

create table Course4
(
   Cno char(2) not null ,
   Cname varchar(20) not null,
   Cprepare varchar(20) not null,
   Ccredit float not null,
   constraint c3 check(0 < Ccredit)
);

create table SC4
(
   Sno char(2) not null,
   Cno char(2) not null,
   Performance float not null,
   constraint sc1 check(Performance between 0 and 100)
);

insert into student4(sno, sname, ssex, sage, department)
values (21, '张三', '男', 18, '计通学院'),
       (04, '李四', '男', 19, '化学院'),
       (02, '翠花', '女', 18, '文新学院');

insert into course4(cno, cname, cprepare, ccredit)
values (11, '概率论', '高等数学', 3),
       (12, '数字电路', '离散结构', 2.5),
       (13, '数据结构', '离散结构', 3.5),
       (01, '嵌入式', '数字电路', 2);

insert into sc4(sno, cno, performance)
values (21, 11, 66),
       (02, 01, 89),
       (04, 12, 59);

截图：

2 视图的创建

2.1 行列子集视图的创建

SQL语句：

use experimentfour;

create view ComputerStudent
as
select Sname, Sno, Sage
from student4
where Department = '计通学院'
with check option;

select *
from ComputerStudent;

截图：

2.2 多表视图

SQL语句：

use experimentfour;

create view Chinese_S1(Sno, Sname, Performance)
as
select student4.Sno, Sname, Performance
from student4, sc4
where Department = '文新学院' and student4.Sno = sc4.Sno and sc4.Cno = 1;

select *
from Chinese_S1;

截图：

2.3视图上建立视图

SQL语句：

use experimentfour;

create view GoodStudent(Sno, Sname)
as
select Sno, Sname
from chinese_s1
where Performance > 80;

select *
from GoodStudent;

截图：

2.4分组视图

SQL语句：

use experimentfour;

create view S_G(Sno, Performance)
as
select Sno, AVG(Performance)
from sc4
group by Sno;

select *
from S_G;

截图：

2.5带表达式的视图

SQL语句：

use experimentfour;

create view BT_S(Sno, Sname, Birth)
as
select Sno, Sname, 2023 - Sage
from student4;

select *
from BT_S;

截图：

​​​​​​​3 删除视图

SQL语句：

use experimentfour;

drop view goodstudent;

截图：

​​​​​​​​​​​​​​4 查询视图

SQL语句：

use experimentfour;

select Sno, Performance
from s_g
where Performance > 60;

截图：

​​​​​​​5 更新视图

5.1 修改某一个属性

SQL语句：

use experimentfour;

update computerstudent
set Sname = '沸羊羊'
where Sno = 21;

select *
from computerstudent;

截图：

5.2 删除一条数据

SQL语句：

use experimentfour;

delete
from computerstudent
where Sno = 21;

select *
from computerstudent;

截图：

5.3 插入一条数据

SQL语句：

use experimentfour;

create view student
as
select * from student4;

insert into student
values (10, '李明', '男', 20, '能动学院');

select * from student;

截图：

​​​​​​​6 索引

6.1建立索引

SQL语句：

use experimentfour;

create unique index Stusno on Student4(Sno);
create unique index Coucno on Course4(Cno);
create unique index SCno on SC4(Sno ASC, Cno DESC);

6.2修改索引

SQL语句：

use experimentfour;

alter table sc4 rename index SCno to SCSno;

6.3删除索引

SQL语句：

use experimentfour;

drop index Stusno on student4;