数据库系统实验二：数据操纵与数据查询

数据库系统实验二：数据操纵与数据查询

一、实验目的：

掌握使用SQL命令对数据库表进行插入、修改和删除数据的操作。
掌握在Navicat可视化工具中对数据库表进行插入、修改和删除数据的操作。
掌握SELECT查询语句的基本语法；。
熟练使用子查询、多表查询以及统计查询。
二、实验任务：

1.使用Navicat可视化工具为数据库YGGL中的各表加入如下数据，注意数据类型的匹配。



2.用SQL命令给表Employees插入两条记录，要求至少使用两种不同的方法。

3.用SQL命令将表Departments 的部门“商务部”改为“市场部”，备注为今天的日期（如：20201009）。
4.用SQL命令给职工“陈林琳”添加住址“汉中路120-4-12”，并将其分配到“研发部”。
5.用SQL命令将表salary中ActIncome列的值置为Income-Outcome。然后将所有职工的收入增加100元，观察ActIncome列的变化。
6.备份数据库YGGL，用SQL命令分别完成以下操作：
1）删除Employees中编号为000001的职工信息；
2）删除表Employees中所有女性员工的信息；
3）删除表Employees中的所有信息。
7.用SQL语句完成以下操作：
1）SELECT命令的基本操作：
（1）查询表Employees中的部门号和性别，要求结果没有重复行；
（2）查询月收入高于2000元的员工号码；
（3） 查询财务部所有员工的号码和姓名，并将显示的列名用其中文含义表示；
（4）查询员工的姓名、住址和收入水平；
（5）查询财务部员工的最高和最低实际收入。
2）SELECT命令的高级操作：
（1）使用命令根据表Employees创建一个“男员工”表，包括编号和姓名；
（2）查询所有年龄比研发部员工年龄都大的员工的姓名；
（3）显示每个员工的基本情况及工作部门名，并按出生时间从小到大排列；
（4）按员工的学历分组，列出不同学历的员工人数。
三、实验过程及实验步骤：（要求写出每个实验题目的步骤以及所使用的命令，并附上必要的截图）

实验步骤

1.添加数据

首先使用数据库YGGL USE yggl;
（1）empoyees表插入数据

INSERT INTO employees VALUES 
		('000001',	'王林'	  ,'大专',	'1966-01-23'	,'1',8,	'中山路32-1-508'	,'83355668',	'2'),
        ('010008',	'伍容华'	,'本科',	'1976-03-28'	,'1',3,	'北京东路100-2'	,'83321321',	  '1'),
        ('020010',	'王向容'	,'硕士',	'1982-12-09'	,'1',2,	'四牌楼10-0-108'	,'83792361',	'1'),
        ('020018',	'李丽'	  ,'大专',	'1960-07-30'	,'0',6,	'中山东路102-2'	,'83413301',	  '1'),
        ('102201',	'刘明'	  ,'本科',	'1972-10-18'	,'1',3,	'虎踞路100-2'    ,'83606608',	  '5'),
        ('102208',	'朱俊'	  ,'硕士',	'1965-09-28'	,'1',2,	'牌楼巷5-3-106'	,'84708817',	  '5'),
        ('108991',	'钟敏'	  ,'硕士',	'1979-08-10'	,'0',4,	'中山路10-3-105'	,'83346722',	'3'),
        ('111006',	'张石兵'	,'本科',	'1974-10-01'	,'1',1,	'解放路34-1-203'	,'84563418',	'5'),
        ('210678',	'林涛'	  ,'大专',	'1977-04-02'	,'1',2,	'中山北路24-35'	,'83467336',	  '3'),
        ('302566',	'李玉珉'	,'本科',	'1968-09-20'	,'1',3,	'热和路209-3'    ,'58765991',	  '4');

employee表

（2）department表插入数据

 INSERT INTO departments VALUES
        (1,'财务部'	   ,''), 
        (2,'人力资源部'   ,''),
        (3,'经理办公室'   ,''), 
        (4,'研发部'	   ,''), 
        (5,'商务部'	   ,'');

department表

（3）salary表插入数据

INSERT INTO salary VALUES
		('000001',	2100.8	 ,123.09 ,null),
        ('010008',	1582.62	 ,88.03  ,null),
        ('020010',	2860.0	 ,198.0  ,null),
        ('020018',	2347.68	 ,180.0  ,null), 
        ('102201',	2569.88	 ,185.65 ,null),
        ('102208',	1980.0	 ,100.0  ,null),
        ('108991',	3259.98	 ,281.52 ,null),
        ('111006',	1987.01	 ,79.58  ,null),
        ('210678',	2240.0	 ,121.0  ,null),
        ('302566',	2980.7	 ,210.2  ,null),
        ('308759',	2531.98	 ,199.08 ,null),
        ('504209',	2066.15	 ,108.0  ,null);

salary表

2.插入记录

方法一：批量插入

INSERT INTO employees VALUES
		('308759',	'叶凡'	, '本科',	'1978-11-18'	,1,	2,	'北京西路3-7-52',	'83308901',	'4'),
        ('504209',	'陈林琳',	'大专',	'1969-09-03'	,0,	5,	''	            , '84468158',	'' );

方法二：单独插入

INSERT INTO employees VALUES('308759',	'叶凡'	, '本科',	'1978-11-18'	,1,	2,	'北京西路3-7-52',	'83308901',	'4');
INSERT INTO employees VALUES('504209',	'陈林琳',	'大专',	'1969-09-03'	,0,	5,	''	            , '84468158',	'' );

方法三：手动插入

3.修改部门

UPDATE departments SET DepartmentName = '市场部',Note = '2022/10/1' WHERE DepartmentName = '商务部';

4.添加住址

UPDATE employees SET Address = '汉中路 120-4-12',DepartmentID = '4'  WHERE Name = '陈林琳';

5.更改列、涨工资

UPDATE salary SET ActIncome=Income-Outcome;
UPDATE salary SET Income+=100;

更改前

变化后

6.删除操作

（1）删除Employees中编号为000001的职工信息

DELETE FROM employees WHERE EmployeeID = '000001';

（2）删除表Employees中所有女性员工的信息

DELETE FROM employees WHERE Sex = '0';

（3）删除表Employees中的所有信息

DELETE FROM employees;

7.查询

基本查询

（1）查询表Employees中的部门号和性别，要求结果没有重复行；

	SELECT DISTINCT DepartmentID,Sex FROM employees;

（2）查询月收入高于2000元的员工号码；

SELECT EmployeeID FROM salary WHERE Income > 2000;

（3） 查询财务部所有员工的号码和姓名，并将显示的列名用其中文含义表示；

SELECT `Name` 姓名,PhoneNumbe 手机号码 FROM employees WHERE DepartmentID = 1;

（4）查询员工的姓名、住址和收入水平；

SELECT `Name`,Address,Income FROM employees,salary WHERE employees.EmployeeID = salary.EmployeeID;

（5）查询财务部员工的最高和最低实际收入。

SELECT MAX(income-Outcome) '最高实际收入', MIN(income-Outcome) '最低实际收入' FROM employees,departments,salary WHERE Departmentname IN(
SELECT DepartmentName
FROM departments
WHERE DepartmentName = '财务部');

高级查询

（1）使用命令根据表Employees创建一个“男员工”表，包括编号和姓名；

CREATE TABLE maleEmployees(EmployeeID char(6),Name char(10)) AS SELECT EmployeeID,`Name` FROM employees WHERE Sex = 1;

（2）查询所有年龄比研发部员工年龄都大的员工的姓名；

SELECT `Name` FROM employees WHERE Birthday < ALL(SELECT Birthday FROM employees WHERE DepartmentID IN (SELECT DepartmentID FROM departments WHERE DepartmentName = '研发部'));

（3）显示每个员工的基本情况及工作部门名，并按出生时间从小到大排列；

SELECT employees.EmployeeID,employees.`Name`,employees.Education,employees.Birthday,
CASE
       WHEN employees.Sex = '1' THEN '男'
       WHEN employees.Sex = '0' THEN '女'
END AS '性别',employees.WorkYear,employees.Address,employees.PhoneNumber,employees.DepartmentID,departments.DepartmentName FROM 
employees JOIN departments ON employees.DepartmentID=departments.DepartmentID ORDER BY Birthday DESC;

（4）按员工的学历分组，列出不同学历的员工人数。

SELECT Education ,count(Education) FROM Employees GROUP BY Education;