实验5 数据库的视图查询
一、实验目的与要求
1、掌握使用数据更新操作的基本要求。
2、掌握视图的建立及查询、更新等操作。
3、SQL语句集合查询的基本操作。
4、发现问题并解决问题的能力。
二、实验重点和难点
实验重点：掌握使用数据更新操作的基本要求；掌握视图的建立及查询、更新等操作。
实验难点：掌握视图的建立及查询、更新等操作。
三、实验内容（代码）
create database lpy5;
use lpy5;
CREATE TABLE Hero (
Sno INT PRIMARY KEY,
Sname VARCHAR(255) unique,
Ssex CHAR(2),
Role_main VARCHAR(255),
Role_user VARCHAR(255) NULL,
Attack VARCHAR(50),
Sprice float,
birthdate DATE NULL
);
CREATE TABLE Player (
Pno INT PRIMARY KEY,
Pname VARCHAR(255) unique,
Page INT,
Psex CHAR(2),
Role VARCHAR(255)
);
CREATE TABLE Record (
Pno INT,
Sno INT,
Score DECIMAL(5,2),
PRIMARY KEY (Pno, Sno),
FOREIGN KEY (Pno) REFERENCES Player(Pno),
FOREIGN KEY (Sno) REFERENCES Hero(Sno)
);
insert into Hero values
(1,'张飞','男','战士','辅助','近战',18888,'2016-6-3'),
(2, '关羽', '女', '法师', NULL, '近战', 18888, '2015-01-01'),
(3, '赵云', '男', '坦克', '刺客', '近战', 18888, '2017-01-01'),
(4, '马超', '男', '射手', NULL, '远程', 16888, '2015-01-01'),
(5, '黄忠', '男', '法师', '辅助', '近战', 19888, '2017-01-01'),
(6, '韩当', '女', '刺客', NULL, '近战', 19888, '2017-01-01'),
(7, '陆逊', '男', '射手', '坦克', '远程', 18888, '2018-01-01'),
(8, '刘备', '男', '坦克', '辅助', '近战', 148888, '2019-01-01');
select * from Hero;
INSERT INTO Player VALUES
(1, '西门庆', 28, '男', '法师'),
(2, '西门吹雪', 19, '男', '刺客'),
(3, '西门小', 18, '女', '法师'),
(4, '公孙离', 19, '男', '射手'),
(5, '妲己', 18, '男', '辅助'),
(6, '杨玉环', 19, '女', '辅助'),
(7, '孙尚香', 18, '男', '射手'),
(8, '王昭君', 19, '男', '射手'),
(9, '貂蝉', 20, '女', '法师'),
(10, '小乔', 21, '男', '刺客');
select * from Player;
INSERT INTO Record (Pno, Sno, Score) VALUES
(1, 1, 9),
(2, 2, 7.8),
(3, 5, 8.1),
(4, 3, 2.3),
(4, 8, 5.6),
(4, 6, 2.8),
(5, 5, 4.1),
(5, 7, 2.4);
select * from Record;
/* 1、使用带子查询的插入语句实现求出每一个段位的玩家的平均年龄，并且存到数据库中*/
alter table Player add Rank varchar(10);
select * from Player;
update Player set Rank ='倔强青铜';
update Player set Rank ='最强王者' where Pno =8;
update Player set Rank ='最强王者' where Pno =4;
update Player set Rank ='至尊星耀' where Pno =10;
update Player set Rank ='至尊星耀' where Pno =8;
update Player set Rank ='至尊星耀' where Pno =7;
create table player_age_avg(
Rank char(10),
avg_age smallint);
-- 插入数据
insert into player_age_avg
select Rank,avg(Player.Page) from Player group by Rank;
select * from player_age_avg;
-- 查询所有用户表xtype='U':表示所有用户表，xtype='S':表示所有系统表
select * from sysobjects where xtype='U';
/* 2、使用不指定属性列的插入语句实现在Hero表中插入五个英雄的信息（同时插入多行数据）*/
insert into Hero
values(11,'王昭君','女','法师','法师','近战',18888,'2002-11-3'),
(12,'妲己','女','法师','法师','近战',10888,'2002-11-4'),
(13,'西施','女','法师','辅助','远战',16888,'2002-11-5'),
(14,'孙悟空','男','打野','打野','近战',17888,'2002-11-6');
select * from Hero;
/* 3、使用指定属性列但属性列的属性与Player属性列顺序不同的方法实现对Player表中插入
五个玩家的信息（同时插入多行数据）*/
insert
into Player(Pno,Pname,Page,Psex,Rank)
values(16,'王西西',19,'女','铂金'),
(17,'李师师',20,'女','星耀'),
(18,'潘巧云',17,'女','青铜'),
(19,'武松',22,'男','王者');
select * from Player;
/*4、修改语句：将Hero01表中次要角色的属性列全部改为空值。*/
update Hero set role_user=null;
select * from Hero;
/*5、将Player01表中Pno为666的ID改为正常顺序ID*/
update player set Pno = 1 where Pno =666;
select * from Player;
/* 6、建立一个王者玩家的视图King_Player，视图属性包括玩家表里面的所有属性。*/
create view King_Player
as
select * from Player;
select * from King_Player;
/*7、建立一个王者玩家并且选择了4号英雄的玩家视图ADCKING_Player，视图属性包括玩家表里面的所有属性。*/
create view ADCKING_Player
as
select Player.* from Player,Record
where Player.Rank like '%王者' and Player.Pno = Record.Pno and Record.Sno =4;
select * from ADCKING_Player;
/*8、将玩家的ID与平均战绩定义为一个视图，视图名为Player_Avg,属性为Pno，AvgG。*/
CREATE VIEW Player_Avg(Pno,AvgG)
AS
select Pno,avg(Score) from Record GROUP BY Pno;
select * from Player_Avg;
/*9、在王者玩家的视图中找出年龄小于20并且选择了英雄为“安琪拉”的玩家，并且用视图消解法表达出来。*/
select distinct ADCKING_Player.Pname
from Record,ADCKING_Player
where ADCKING_Player.Page<20 and ADCKING_Player.Pno=Record.Pno and Record.Sno in(
select Sno
from Hero
where Sname='安琪拉');
/*10、探究 with check option的用法；并且使用with check option语句实现创建一个视图，视图为近战英雄，视图属性是Hero01表中去掉性别、次要角色、上线日期、价格以外全部属性（学会自己找答案）。*/
create view 近战英雄
as
select Sno,Sname,Role_main,Attack
from Hero
with check option;
SELECT * from Hero;
-- （1）实现对视图插入一条（11，’诸葛亮’，男，法师，18888）的数据；若不能插入，找到解决办法。
INSERT into 近战英雄 VALUES(15,'诸葛亮','法师','近战');
select * from 近战英雄;
-- （2）实现对视图删除一条数据，条件为Hno=1q
DELETE from 近战英雄 where Sno=1;
-- （3）实现对视图修改一条数据，条件为Hno=2，将价格提高888；若不能修改，找到解决办法。
-- 注意：不能删除with check option语句。
实验6 SQL语句综合训练（一）
一、实验目的与要求
1、复习SQL的DDL、DML、DQL、DCL等语言。
2、对比关系代数与SQL点区别与联系。
3、发现问题并解决问题的能力。
二、实验重点和难点
实验重点：熟悉并且灵活掌握SQL语句，提高编写SQL语句的速度。
实验重点：自己动手解决问题，代码写上注解为佳，能用多种方法实现查询或者更新等操作为尤佳。
三、实验内容（代码）
CREATE DATABASE lingpengyan6;
use lingpengyan6;
-- 1、使用SQL语句建立数据库并且建立对应的四个基本表。
USE lingpengyan6
GO
CREATE TABLE S
(SNO CHAR(3),
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
use lingpengyan6
go
create table P
(PNO char(3),
PNAME char(10),
COLOR char(4),
WEIGHT int);
use lingpengyan6
go
create table J
(JNO CHAR (3),
JANME CHAR(10),
CITY CHAR(10)
);
use lingpengyan6
go
create table SPJ
(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT
);
INSERT INTO S VALUES
('S1','精益','20','天津'),
('S2','圣锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');
select * from S;
INSERT into P VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
SELECT * FROM P;
INSERT into J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');
SELECT * from J;
INSERT into SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);
SELECT * from SPJ;
-- 2、求供应工程J1零件的供应商号码SNO
select sno
from SPJ
where JNO='J1';
-- 3、求供应工程J1零件P1的供应商号码SNO
select sno
from SPJ
where JNO='J1'and PNO='P1';
-- 4、求供应工程J1零件为红色的供应商号码SNO
select sno
from SPJ
where JNO='J1'and
PNO in(
select PNO
from P
where color='红'
);
-- 5、找出上海厂商供应的所有零件号码
select distinct pno
from SPJ
where sno in
(select sno
from s
where city='上海');
-- 6、找出使用上海产的零件的工程名字
select J.JANME
from J,SPJ,S
where J.JNO=SPJ.JNO
and SPJ.SNO=S.SNO
and S.CITY='上海';
-- 7、把全部红色零件的颜色改为蓝色
update P
set color='蓝'
where color='红';
-- 8、由s5供给j4的零件p6改为由s3供应
update SPJ
set SNO='S3'
where SNO='S5'
and JNO='J4'
and PNO='P6';
-- 9、求没有使用天津供应商生产的红色零件的工程号
select JNO
from J
where not exists
(select *
from SPJ,S,P
where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO
AND SPJ.PNO=P.PNO and S.CITY='天津'
and P.COLOR='红');
-- 10、求至少用了供应商S1所供应的全部零件的的工程号JNO
select distinct JNO
from SPJ SPJZ
where not exists
(select *
from SPJ SPJX
where SNO='S1'
and not exists
(select *
from SPJ SPJY
where SPJY.pno=spjx.pno
and spjy.jno=spjz.jno));
实验7 SQL语句综合训练（二）
一、实验目的与要求
1、复习SQL分组、排序等查询操作。
2、复习视图的建立以及查询操作。
3、发现问题并解决问题的能力。
二、实验重点和难点
实验重点：熟悉并且灵活掌握SQL语句，提高编写SQL语句的速度。
实验重点：自己动手解决问题，代码写上注解为佳，能用多种方法完成实验为尤佳。
三、实验内容（代码）
CREATE DATABASE lingpengyan7;
USE lingpengyan7;
GO
CREATE TABLE S
(SNO CHAR(3),
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
use lingpengyan7
go
create table P
(PNO char(3),
PNAME char(10),
COLOR char(4),
WEIGHT int);
use lingpengyan7
go
create table J
(JNO CHAR (3),
JNAME CHAR(10),
CITY CHAR(10)
);
use lingpengyan7
go
create table SPJ
(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT
);
INSERT INTO S VALUES
('S1','精益','20','天津'),
('S2','圣锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');
select * from S;
INSERT into P VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
SELECT * FROM P;
INSERT into J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');
SELECT * from J;
INSERT into SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);
SELECT * from SPJ;
-- 1、查询以“螺”开头的零件代码
SELECT * FROM P WHERE PNAME LIKE '螺%';
-- 2、查询供应商姓名最后一个字是“民”的供应商信息
select * from S where SNAME like '%民';
-- 3、查询工程项目名不带厂字的工程项目代码以及所在城市
SELECT JNO,CITY FROM J WHERE JNAME NOT LIKE '%厂%';
-- 4、查询供应数量小于300的供应商代码和供应商姓名。
SELECT SNO,SNAME FROM S WHERE SNO IN (SELECT DISTINCT SNO FROM SPJ WHERE QTY<300);
-- 5、查询零件重量小于40并且大于12的零件代码以及零件名。
SELECT PNO, PNAME FROM P WHERE WEIGHT < 40 AND WEIGHT > 12;
-- 6、按照零件的颜色对零件进行分组，查询分组后每个零件的名称
SELECT PNAME,COLOR FROM P GROUP BY COLOR, PNAME;
-- 7、按照供应商所在城市对供应商进行分组，查询分组后每个供应商的城市的名称以及所对应的数量。
SELECT CITY, COUNT(*) AS SupplierCount FROM S GROUP BY CITY;
-- 8、按照工程项目代码对工程项目表进行分组，查询当前工程项目代码下所对应的最高供应数量。
SELECT JNO, MAX(QTY) FROM SPJ GROUP BY JNO;
-- 9、将零件表按照零件的重量升序排列。
SELECT * FROM P ORDER BY WEIGHT ASC;
-- 10、按照供应商代码进行分组，查询当前供应商代码下所对应的平均供应商数量大于250供应商代码。
SELECT SNO FROM SPJ GROUP BY SNO HAVING AVG(QTY) > 250;
-- 11、请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
CREATE VIEW ThreeBuild_SPJ AS
SELECT SPJ.SNO, SPJ.PNO, SPJ.QTY
FROM SPJ
JOIN J ON SPJ.JNO = J.JNO
WHERE J.JNAME = '三建';
-- 12、找出三建工程项目使用的各种零件代码及其数量。
SELECT PNO, SUM(QTY) AS TotalQuantity
FROM SPJ
JOIN J ON SPJ.JNO = J.JNO
WHERE J.JNAME = '三建'
GROUP BY PNO;
-- 13、找出供应商S1的供应情况。
SELECT * FROM SPJ WHERE SNO = 'S1';
实验8 数据库的完整性
一、实验目的与要求
1、熟悉通过SQL对数据进行完整性控制。熟练掌握数据库三类完整性约束（实体完整性、用户自定义完整性、参照完整性）
2、了解SQL SERVER 的违反完整性处理措施。
3、了解主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、唯一性（UNIQUE）约束、检查（CHECK）约束、DEFAULT 约束、允许空值约束、完整性约束命名字句、触发器。
二、实验重点和难点
实验重点：三类完整性约束（实体完整性、用户自定义完整性、参照完整性）
、完整性约束命名字句、触发器。
实验难点：触发器。
三、实验内容（代码）
数据库的名称以你名字的拼音全拼命名，如王珊数据库的名字为：wangshan；
基本表已给出
- 考虑下面的关系模式：
研究人员（人员编号，姓名、年龄、职称）
项目（项目编号，名称，负责人编号，类别）
参与（项目编号，人员编号，工作时间）
一个研究人员可以参与多个项目，一个项目有多个研究人员参加，工作时间给出某个研究人员参加某项目的月数。
创建三张表，并定义下面的完整性约束：
（1）定义三个关系中的主码、外码、参照完整性；
（2）项目名称必须互不相同；
（3）每个研究人员的年龄不能超过35岁；
（4）每个研究人员的职称只能是“讲师”、“副教授”或“教授”
（5）设计SQL语句在表中进行插入、删除、修改操作验证完整性约束是否起作用
按照要求（1）、（2）、（3）、（4）创建表：
CREATE DATABASE lpy6;
USE lpy6;
-- （1）定义三个关系中的主码、外码、参照完整性；
-- 研究人员表
CREATE TABLE researchers(
researcher_id VARCHAR(10) PRIMARY KEY,
researcher_name VARCHAR(10),
age INT CHECK(age <= 35),--（3）每个研究人员的年龄不能超过35岁；
job_title CHAR(10) CHECK(job_title IN('讲师', '副教授', '教授'))--（4）每个研究人员的职称只能是“讲师”、“副教授”或“教授”
);
-- 项目表
CREATE TABLE project(
project_id VARCHAR(5) PRIMARY KEY,
project_name CHAR(50) UNIQUE,-- （2）项目名称必须互不相同；
researcher_id VARCHAR(10),
type VARCHAR(4)
);
-- 参与表
CREATE TABLE attend(
project_id VARCHAR(5),
researcher_id VARCHAR(10),
work_month int,
PRIMARY KEY(project_id,researcher_id),
FOREIGN KEY(project_id) REFERENCES project(project_id),
FOREIGN KEY(researcher_id) REFERENCES researchers(researcher_id)
);
-- （5）设计SQL语句在表中进行插入、删除、修改操作验证完整性约束是否起作用
-- 验证主键 验证结果：插入失败，约束成功
INSERT
INTO researchers
VALUES('111111','lyh',26,'教授');
INSERT
INTO researchers
VALUES('111111','李贻慧',27,'教授');
-- 验证外键 验证结果：插入失败，约束成功
INSERT
INTO attend
VALUES('001','111111',6);
-- 验证项目名称必须不同 验证结果：插入失败，约束成功
INSERT
INTO project
VALUES('01','数据库设计','001','数1');
INSERT
INTO project
VALUES('02','数据库设计','002','数2');
-- 验证研究人员的年龄不能超过35岁
INSERT
INTO researchers
VALUES('2018413449','李贻慧','38','教授');
-- 验证研究人员的职称只能是“讲师”、“副教授”或“教授”
INSERT
INTO researchers
VALUES('2018413449','李贻慧','26','博士');
/*2. 考虑（一）中的关系模式，使用alter table add constraint声明如下完整性约束：*/
-- （1）负责人编号参照研究人员的“人员编号”属性，当对“研究人员”修改时，若违反约束则将负责人编号置为NULL，
-- 当对“研究人员”删除时，若违反约束则将项目中的相应元组删除。
ALTER TABLE project
ADD CONSTRAINT c11 FOREIGN KEY(researcher_id) REFERENCES researchers(researcher_id) ON DELETE SET NULL ON UPDATE SET NULL;
ALTER TABLE project
ADD CONSTRAINT c12 FOREIGN KEY(researcher_id) REFERENCES researchers(researcher_id) ON DELETE CASCADE ON UPDATE CASCADE ;
-- （2） 工作时间在1到12之间
ALTER TABLE attend
ADD CONSTRAINT c2 CHECK(work_month>=1 AND work_month<=12);
-- （3） 研究人员姓名不能为空
ALTER TABLE researchers
ADD CONSTRAINT c3 CHECK(researcher_name IS NOT NULL);
-- （4） 设计SQL语句在表中进行插入、删除、修改操作验证完整性约束是否起作用
INSERT
INTO attend
VALUES('01','111111',15);
/*3. 删除完整性约束*/
-- （1） 删除“参与”的外键
alter table attend
drop FK__attend__research__4222D4EF;
-- （2） 删除“项目”的主键
alter table project
drop PK__project__BC799E1FAEF7DBE9;
-- （3） 删除研究人员年龄的约束
alter table researchers
drop constraint CK__researchers__age__33D4B598 ;
/*4.触发器的实验*/
-- (1)建立和测试DELETE触发器
-- 目的：将book表中删除的行备份在book_bak表中。
-- ①建立测试表
CREATE TABLE book(
bno INT PRIMARY KEY,
bname VARCHAR(20) NOT NULL,
bprice MONEY NOT NULL
)
select * from book;
-- ②建立测试备份表
CREATE TABLE book_bak(
bno INT PRIMARY KEY,
bname VARCHAR(20) NOT NULL,
bprice MONEY NOT NULL
);
-- ③插入测试数据
INSERT INTO book VALUES(1,'数据库系统概论',10);
INSERT INTO book VALUES(2,'计算机网络',20);
INSERT INTO book VALUES(3,'数据结构',30);
INSERT INTO book VALUES(4,'计算机组成原理',40);
-- ④建立DELETE触发器
CREATE TRIGGER book_delete
ON book FOR DELETE AS
INSERT INTO book_bak SELECT * FROM deleted;
-- ⑤测试DELETE触发器
DELETE FROM book WHERE bno=1
DELETE FROM book WHERE bno=2
DELETE FROM book WHERE bno=3
DELETE FROM book WHERE bno=4
SELECT * FROM book
SELECT * FROM book_bak
-- (2)建立和测试INSERT触发器
-- 目的：将book表中插入的行同时插入book_bak表中。
-- ①建立INSERT触发器
CREATE TRIGGER book_insert
ON book FOR INSERT AS
INSERT INTO book_bak SELECT * FROM inserted
-- ②测试INSERT触发器
INSERT INTO book VALUES(5,'C程序设计',10)
INSERT INTO book VALUES(6,'C++程序设计',10)
INSERT INTO book VALUES(7,'Java程序设计',10)
INSERT INTO book VALUES(8,'C#程序设计',10)
SELECT * FROM book
SELECT * FROM book_bak
-- (3)建立和测试UPDATE触发器
-- 目的：检查价格变化，若上下浮动超过20%则不能修改。
-- ①建立UPDATE触发器
CREATE TRIGGER book_update
ON book FOR UPDATE AS
DECLARE @old_price MONEY,@new_price MONEY
SELECT @old_price=bprice FROM deleted
SELECT @new_price=bprice FROM inserted
IF(@new_price>@old_price*1.2) OR (@new_price<@old_price*0.8)
BEGIN
PRINT 'ROLLBACK!'
ROLLBACK
END
ELSE
BEGIN
PRINT 'UPDATE!'
END
-- ②测试UPDATE触发器
UPDATE book SET bprice=11 WHERE bno=5; --允许修改
UPDATE book SET bprice=9 WHERE bno=6; --允许修改
UPDATE book SET bprice=20 WHERE bno=7; --不能修改
UPDATE book SET bprice=5 WHERE bno=8; --不能修改
SELECT * FROM book;
-- 3、管理触发器
-- (1)查看触发器
sp_helptrigger book;
sp_helptext book_update;
-- (2)删除触发器
DROP TRIGGER book_insert
-- (3)禁用触发器
ALTER TABLE book DISABLE TRIGGER book_delete
-- (4)启用触发器
ALTER TABLE book ENABLE TRIGGER book_delete
扫一扫
2725
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
