数据库概论 SQL语言实现

 

一、实验目的

1、了解某个关系数据库管理系统（例如：MySQL、SQL Server、Oracle等）的基本操作。

2、熟练掌握SQL数据定义语句，能够熟练使用SQL数据定义语句创建、修改及删除关系模式。

3、熟练掌握SQL数据操纵语句，能够熟练使用SQL数据操纵语句插入、修改及删除元组。

4、熟练掌握SQL数据查询语句，能够熟练使用SQL数据查询语句实现数据基本查询，包括单表查询、统计查询、连接查询及嵌套查询等。

二、实验环境

运行环境:MySQLWorkbench 8.0CE

三、实验内容

1、创建关系模式

（1）职工表employee，其结构为(职工号ssn char(18)，职工名name char(10)，生日bdate char(10)，地址address char(30)，性别sex char(2)，薪水salary float，直接领导人职工号superssn char(18) ，部门号dno char(3))，其中职工号为主码，其它属性值要求非空。

（2）部门表department，其结构为(部门号dnumber char(3)，部门名dname varchar(30)，部门经理职工号mgrssn char(18)，部门经理受雇日期mgrstartdate date)，其中部门号为主码，其它属性值要求非空。

（3）部门地址表depart_location，其结构为(部门号dnumber char(3)，部门地址dlocation varchar(30))，其中部门号为主码，其它属性值要求非空。

（4）项目表project，其结构为(项目号pnumber char(3)，项目名pname varchar(30)，项目所在地plocation varchar(30)，项目隶属部门号dnum char(3))，其中项目号为主码，其它属性值要求非空。

（5）职工参与项目表works_on，其结构为(职工号essn char(18)，项目号pno char(3)，工作时间hours int)，其中(职工号，项目号)为主码，其它属性值要求非空。

（6）家属表dependent，其结构为(职工号essn char(18)，家属名dependent_name char(10)，性别sex char(2)，生日bdate char(10)，与职工关系relationship char(10))，其中(职工号，家属名)为主码，其它属性值要求非空。

2、为上述关系模式分别插入如下数据：

（1）职工表：employee

ssn	name	bdate	address	sex	salary	superssn	dno
230101198009081234	张三	1980-09-08	哈尔滨道里区十二道街	男	3125	23010119751201312X	d1
230101198107023736	李四	1981-07-02	哈尔滨道外区三道街	男	2980	23010119751201312X	d1
23010119751201312X	张红	1975-12-01	哈尔滨南岗区三十道街	男	4260	23010119751201312X	d1
230101198204078121	王二	1982-04-07	哈尔滨动力区六十道街	男	2890	23010119751201312X	d1
23010119950101XXXX	灰太狼	1995-01-01	青青草原狼堡	男	1200	23010119960101XXXX	d2
23010119960101XXXX	红太狼	1996-01-01	青青草原狼堡	女	3600	23010119960101XXXX	d2
23010120050101XXXX	喜羊羊	2005-01-01	青青草原大肥羊学校	男	1000	23010120050101XXXX	d3
XXXXXXXXXXXXXXXXXX	超人	3000-01-01	外星	男	100000	23010120050101XXXX	d4

（2）部门表：department

dnumber	dname	mgrssn	mgrstartdate
d1	研发部	23010119751201312X	2008-01-01
d2	捕羊部	23010119960101XXXX	2006-01-01
d3	防狼部	23010120050101XXXX	2006-01-01
d4	全能部	XXXXXXXXXXXXXXXXXX	3000-01-01

（3）部门地址表depart_location

dnumber	dlocation
d1	哈尔滨
d2	青青草原
d3	青青草原
d4	地球

（4）项目表：project

pname	pnumber	plocation	dnum
研究项目1	p1	哈尔滨	d1
哈同公路	p2	哈尔滨	d1
立交桥	p3	哈尔滨	d1
机场建设	p4	哈尔滨	d1
抓羊	p5	青青草原	d2
吃羊	p6	青青草原	d2
防狼	p7	青青草原	d3

（5）员工参与项目表：works_on

essn	pno	hours
23010119751201312X	p1	100
23010119751201312X	p2	90
23010119751201312X	p3	85
23010119751201312X	p4	100
230101198009081234	p1	65
230101198009081234	p2	76
230101198009081234	p3	67
230101198107023736	p2	89
230101198107023736	p3	79
230101198107023736	p4	91
230101198204078121	p2	23
230101198204078121	p3	36
23010119950101XXXX	p2	11
23010119950101XXXX	p5	100
23010119950101XXXX	p6	100
23010119960101XXXX	p5	100
23010119960101XXXX	p6	100
23010120050101XXXX	p7	100
XXXXXXXXXXXXXXXXXX	p1	100
XXXXXXXXXXXXXXXXXX	p2	100
XXXXXXXXXXXXXXXXXX	p3	100
XXXXXXXXXXXXXXXXXX	p4	100
XXXXXXXXXXXXXXXXXX	p5	100
XXXXXXXXXXXXXXXXXX	p6	100
XXXXXXXXXXXXXXXXXX	p7	100

（6）家属表：dependent

essn	dependent_name	sex	bdate	relationship
230101198009081234	张三妻	女	1983-09-02	配偶
230101198009081234	张三儿	男	2005-01-01	父子
23010119950101XXXX	小灰灰	男	2009-01-01	父子
23010119960101XXXX	小灰灰	男	2009-01-01	母子

3、完成下列查询

（1）查询参加了“p2”项目的职工号。

（2）查询参加了项目名为“哈同公路”的职工数量。

（3）查询在“研发部”工作且工资低于3000元的职工名字和地址。

（4）查询没有参加项目“p1”的职工姓名。

（5）查询没有家属的职工名字。

（6）查询由“张红”领导的职工的姓名和所在部门的名字。

（7）查询至少参加了3个项目的职工号。

（8）查询至少参加了项目“p1”和项目“p2”的职工号。

（9）查询参加了全部项目的职工号和姓名。

（10）在参加了“p2”项目的职工中，查询比职工“张三”的酬金低的职工姓名 。

（11）查询这样的职工姓名，该职工参加了“张三”没有参加的某个项目。

（12）查询这样的职工姓名，该职工至少参加了王二参加的所有项目(不列出王二)。

（13）查询这样的职工姓名和他在项目中的平均工作时间，该职工至少参加了两

个项目，并且在这两个项目中的工作时间都不低于100小时。

（14）查询这样的职工姓名，该职工至少参加了三个部门的项目。

（15）查询至少参加了项目“P1”、项目“P2”和项目“P3”的职工姓名。

4、关系模式及数据的维护

（1）为dependent表添加工作单位和职业两个属性。

（2）为工作单位和职业这两个属性添加值。

（3）把employee表中所有职工的工资改为3000元。

（4）把dependent表中“230101198009081234”的家属张三妻的职业改为“教师”。

（5）把研究部的职工工资提高10%。

（6）删除dependent表中职工“23010119950101XXXX”的家属。

（7）删除在研究部工作的职工信息。

（8）分别删除工作单位和职业属性。

四、实验结果（测试用例、实验结果）

1.完成下列查询的查询结果如下图所示：

（1）查询参加了“p2”项目的职工号如图4-1

图4-1

1. 查询参加了项目名为“哈同公路”的职工数量如图4-2

图4-2

1. 查询在“研发部”工作且工资低于3000元的职工名字和地址如图4-3

图4-3

1. 查询没有参加项目“p1”的职工姓名如图4-4

图4-4

1. 查询没有家属的职工名字如图4-5

图4-5

1. 查询由“张红”领导的职工的姓名和所在部门的名字如图4-6

图4-6

1. 查询至少参加了3个项目的职工号如图4-7

图4-7

1. 查询至少参加了项目“p1”和项目“p2”的职工号如图4-8

图4-8

1. 查询参加了全部项目的职工号和姓名如图4-9

图4-9

1. 在参加了“p2”项目的职工中，查询比职工“张三”的酬金低的职工姓名如图4-10

图4-10

1. 查询这样的职工姓名，该职工参加了“张三”没有参加的某个项目如图4-11

图4-11

1. 查询这样的职工姓名，该职工至少参加了王二参加的所有项目(不列出王二)如图4-12

图4-12

（13）查询这样的职工姓名和他在项目中的平均工作时间，该职工至少参加了两

个项目，并且在这两个项目中的工作时间都不低于100小时如图4-13

图4-13

（14）查询这样的职工姓名，该职工至少参加了三个部门的项目如图4-14

图4-14

1. 查询至少参加了项目“P1”、项目“P2”和项目“P3”的职工姓名如图4-15

图4-15

1. 关系模式及数据的维护实验结果如下图所示：
2. 为dependent表添加工作单位和职业两个属性如图5-1所示：

图 5-1

1. 为工作单位和职业这两个属性添加值如图5-2所示：

图 5-2

1. 把employee表中所有职工的工资改为3000元如图5-3所示：

图 5-3

1. 把dependent表中“230101198009081234”的家属张三妻的职业改为“教师”如图5-4所示：

图 5-4

1. 把研究部的职工工资提高如图5-5所示：

图 5-5

1. 删除dependent表中职工“23010119950101XXXX”的家属如图5-6所示：

图 5-6

1. 删除在研究部工作的职工信息如图5-7所示：

图 5-7

（8）分别删除工作单位和职业属性如图5-8所示：

图 5-8

五、实验总结

在编程中主要涉及的sql语句的查询，以及对表的增删改查，都让我对sql定义、查询、操纵语句有了更深层次的理解，并且完全掌握并使用，使我对数据库有了更深层次的认识，在以后的做题以及运用中也更加灵活方便。

附录（源代码）

题目1.查询源代码：

-- 1查询参加了“p2”项目的职工号

SELECT essn

FROM works_on

WHERE pno='p2';

-- 2查询参加了项目名为“哈同公路”的职工数量

SELECT COUNT(*)

FROM works_on

WHERE pno in(SELECT pnumber FROM project WHERE pname='哈同公路');

-- 3查询在“研发部”工作且工资低于3000元的职工名字和地址

SELECT name,address

FROM employee

WHERE salary<'3000' AND dno in(

SELECT dnumber

FROM department

WHERE dname='研发部'

);

-- 4查询没有参加项目“p1”的职工姓名

SELECT name

FROM employee

WHERE NOT EXISTS(

SELECT *

FROM works_on

WHERE ssn=essn AND pno='p1'

);

-- 5查询没有家属的职工名字

SELECT name

FROM employee

WHERE NOT EXISTS(

SELECT *

FROM dependent

WHERE ssn=essn

);

-- 6由张红领导的职工的姓名和所在部门的名字

SELECT e.name,dname

FROM employee e,employee m,department

WHERE e.superssn=m.ssn AND m.name='张红' AND e.dno=dnumber;

-- 7查询至少参加了3个项目的职工号

SELECT essn

FROM works_on

GROUP BY essn HAVING COUNT(*)>=3;

-- 8查询至少参加了项目“p1”和项目“p2”的职工号

SELECT w1.essn

FROM works_on w1,works_on w2

WHERE w1.pno='p1'AND w2.pno='p2' AND w1.essn=w2.essn;

-- 9查询参加了全部项目的职工号和姓名

SELECT ssn,name

FROM employee

WHERE NOT EXISTS(

SELECT *

FROM project

WHERE NOT EXISTS(

SELECT *

FROM works_on

WHERE ssn=essn AND pnumber=pno

)

);

-- 10在参加了“p2”项目的职工中，查询比职工“张三”的酬金低的职工姓名

SELECT name

FROM employee

WHERE ssn in(

SELECT essn

FROM works_on

WHERE pno='p2') AND salary<(SELECT salary FROM employee WHERE name="张三");1XXXX','小灰灰','男','2009-01-01','母子');

-- 11查询这样的职工姓名，该职工参加了“张三”没有参加的项目

SELECT name

FROM employee

WHERE ssn in(

SELECT essn

FROM works_on

WHERE pno NOT in(

SELECT pno

FROM works_on,employee

WHERE ssn=essn AND name="张三"

)

);

-- 12查询这样的职工姓名，该职工至少参加了王二参加的所有项目（不列出王二）

SELECT name

FROM employee

WHERE name!="王二" AND NOT EXISTS(

SELECT *

FROM works_on w1

WHERE essn in(

SELECT ssn

FROM employee

WHERE name="王二"

)AND NOT EXISTS(

SELECT *

FROM works_on w2

WHERE w2.essn=ssn AND w2.pno=w1.pno

)

);

-- 13查询这样的职工姓名和他在项目中的平均工作时间，该职工至少参加两个项目，并且在这两个项目中的工作时间都不低于100小时

SELECT name,avg(hours)

FROM employee,works_on

WHERE ssn=essn AND essn in(

SELECT DISTINCT w1.essn

FROM works_on w1,works_on w2

WHERE w1.essn=w2.essn AND w1.pno!=w2.pno AND w1.hours>=100 AND w2.hours>=100)GROUP BY name;

-- 14查询这样的职工姓名，该职工至少参加了三个部门的项目

SELECT name

FROM employee

WHERE ssn in(

SELECT essn

FROM works_on,project

WHERE pno=pnumber

GROUP BY essn HAVING COUNT(DISTINCT dnum)>=3

);

-- 15查询至少参加了项目“p1”、项目“p2”和项目“p3”的职工姓名

SELECT name

FROM employee

WHERE ssn in(

SELECT essn

FROM works_on

WHERE pno in("p1","p2","p3")

GROUP BY essn HAVING COUNT(*) = 3

);