个人网站域名快速备案流程上海搜索引擎关键词优化
目录
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;