数据库上机【2】

数据库上机【2】

所有表的基本信息(上机1的内容):

#创建Employees表
CREATE TABLE Employees(
	EmployeeID CHAR(6)PRIMARY KEY,
	Ename CHAR(10)NOT NULL,
	Education CHAR(4)NOT NULL,
	Birthday DATE NOT NULL,
	Sex BIT DEFAULT 1 NOT NULL,
	Workyer INT NULL,
	Address VARCHAR(40)NULL,
	PhoneNumber CHAR(12)NULL,
	DepartmentID CHAR(3)NOT NULL,
	FOREIGN KEY(DepartmentID) REFERENCES Departments(DepartmentID)
);
#插入信息
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000001','刘1','本科',DATE('2001-12-01'),1,2,'北京','123456654321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000002','刘2','本科',DATE('2001-11-1'),1,4,'北京','123457754321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000003','刘3','初中',DATE('2002-1-1'),0,2,'上海','123456574321','004');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000004','刘4','本科',DATE('2001-12-1'),1,2,'上海','223456654321','002');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000005','刘5','本科',DATE('2001-2-1'),1,2,'青岛','323456654321','003');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000006','刘6','高中',DATE('2002-12-1'),0,2,'青岛','423456654321','005');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000007','刘7','本科',DATE('2001-12-18'),0,3,'长沙','523456654321','007');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000008','刘8','高中',DATE('1999-12-1'),1,2,'长沙','623456654321','006');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000009','刘9','本科',DATE('2001-12-1'),1,2,'成都','123456654325','008');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000010','刘10','本科',DATE('2003-12-1'),1,2,'成都','123456654326','009');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000011','刘11','硕士',DATE('1997-12-1'),1,2,'成都','123456654329','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000012','刘12','本科',DATE('2001-9-1'),1,2,'成都','823456654321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000013','刘13','本科',DATE('2000-2-1'),1,2,'成都','923456654321','002');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000014','刘14','本科',DATE('1999-12-1'),0,1,'长沙','123456654324','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000015','刘15','本科',DATE('2001-12-1'),1,2,'长沙','123456654321','002');
#修改信息
UPDATE Employees SET PhoneNumber = '123456789010' WHERE EmployeeID = '000010';
UPDATE Employees SET Birthday = DATE('1995-10-01') WHERE EmployeeID = '000007';
#删除信息
DELETE FROM Employees WHERE EmployeeID = '000010';
#删除表
DROP TABLE Employees;

#创建Departments表
CREATE TABLE Departments(
	DepartmentID CHAR(3)PRIMARY KEY,
	DepartmentName CHAR(20)NOT NULL,
	Note VARCHAR(100)NULL
);
#插入信息
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('001','研发部','1');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('002','市场部','2');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('003','销售部','3');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('004','咨询部','4');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('005','项目部','5');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('006','生产部','6');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('007','财务部','7');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('008','人力资源部','8');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('009','经理办公室','9');
INSERT INTO Departments(DepartmentID,DepartmentName,note) VALUE('010','秘书办','10');
#修改信息
UPDATE Departments SET DepartmentName = '秘书处' WHERE DepartmentID = '010';
#删除信息
DELETE FROM Departments WHERE DepartmentID = '010';
#删除表
DROP TABLE Departments;

#创建Salary表
CREATE TABLE Salary(
	EmployeeID CHAR(6)PRIMARY KEY,
	Income FLOAT NOT NULL,
	Outcome FLOAT NOT NULL
);
#插入信息
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000001',30000.5,20000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000002',26000.5,20000.56);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000003',31000.5,10000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000004',30023.525,12000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000005',2000.5,1000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000006',4000.5,5000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000007',5999.28,4000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000008',3000.5,2000);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000009',10000.33,2000.56);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000010',3000.5,1300);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000011',3000.5,1400);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000012',3000.5,1700);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000013',3000.5,1600);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000014',3000.5,1850);
INSERT INTO Salary(EmployeeID,Income,Outcome) VALUE('000015',3000.5,2800);
#修改信息
UPDATE Salary SET Outcome = 2000.75 WHERE EmployeeID = '000010';
#删除信息
DELETE FROM Salary WHERE EmployeeID = '000010';
#删除表
DROP TABLE Salary;

上机2的内容:

#上机实验2
#实验内容一:查询员工的姓名、地址、收入水平
SELECT Ename 姓名,Address 地址,CASE
	WHEN Income < 3000 THEN '低收入'
	WHEN Income BETWEEN 3000 AND 7000 THEN '中等收入'
	ELSE '高收入' END AS 收入
FROM Employees,Salary
WHERE Employees.EmployeeID = Salary.EmployeeID;
#实验内容二:查询财务部年龄不低于研发部雇员年龄的雇员姓名
SELECT Ename
FROM Employees
WHERE DepartmentID = '007' AND Birthday < ALL
(SELECT Birthday FROM Employees WHERE DepartmentID = '001');
#实验内容三:查询财务部收入在5k以上的雇员姓名及薪水情况
SELECT Ename 姓名,Income 收入,Outcome 支出
FROM Employees,Salary
WHERE Employees.EmployeeID = Salary.EmployeeID 
	AND Income > 5000
	AND DepartmentID = '001';
#实验内容四:查询财务部雇员的最高和最低收入
SELECT MAX(Income) 最高收入,MIN(Income) 最低收入
	FROM Employees,Salary
	WHERE Employees.EmployeeID = Salary.EmployeeID
		AND Employees.DepartmentID = '001'
#实验内容五:创建视图Employees_View以及DS_View
CREATE VIEW Employees_View
AS
SELECT Employees.EmployeeID 员工号码,Employees.Ename 姓名,Income - Outcome 实际收入
FROM Salary,Employees
WHERE Employees.EmployeeID = Salary.EmployeeID;

CREATE VIEW DS_View
AS
SELECT Departments.*
FROM Departments;
#实验内容六:从视图Employees_View中查询某员工的实际收入
SELECT 姓名 姓名,实际收入 实际收入
FROM Employees_View
WHERE 姓名 = '刘6';
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Alan_Lowe

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值