#创建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';
数据库上机【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, PhoneN