数据库上机【1】

数据库上机【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,'China','123456654321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000002','刘2','本科',DATE('2001-11-1'),1,4,'China','123457754321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000003','刘3','初中',DATE('2002-1-1'),0,2,'China','123456574321','004');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000004','刘4','本科',DATE('2001-12-1'),1,2,'China','223456654321','002');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000005','刘5','本科',DATE('2001-2-1'),1,2,'China','323456654321','003');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000006','刘6','高中',DATE('2002-12-1'),0,2,'China','423456654321','005');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000007','刘7','本科',DATE('2001-12-18'),0,3,'China','523456654321','007');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000008','刘8','高中',DATE('1999-12-1'),1,2,'China','623456654321','006');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000009','刘9','本科',DATE('2001-12-1'),1,2,'China','123456654325','008');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000010','刘10','本科',DATE('2003-12-1'),1,2,'China','123456654326','009');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000011','刘11','硕士',DATE('1997-12-1'),1,2,'China','123456654329','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000012','刘12','本科',DATE('2001-9-1'),1,2,'China','823456654321','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000013','刘13','本科',DATE('2000-2-1'),1,2,'China','923456654321','002');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000014','刘14','本科',DATE('1999-12-1'),0,1,'China','123456654324','001');
INSERT INTO Employees(EmployeeID,Ename,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID) 
VALUE('000015','刘15','本科',DATE('2001-12-1'),1,2,'China','123456654321','002');
#修改信息
UPDATE Employees SET PhoneNumber = '123456789010' WHERE EmployeeID = '000010';
#删除信息
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;

#查询1
SELECT Employees.*,Departments.DepartmentName,Departments.Note,Salary.Income,Salary.Outcome
FROM Employees,Departments,Salary
WHERE Employees.EmployeeID = Salary.EmployeeID AND Employees.DepartmentID = Departments.DepartmentID;
#查询2
SELECT Employees.Ename,Employees.PhoneNumber,Employees.Address
FROM Employees;
#查询3
SELECT EmployeeID
FROM Salary
WHERE Income > 6000;
#查询4
SELECT EmployeeID,Income - Outcome
FROM Salary;
#查询5
SELECT COUNT(DepartmentID)
FROM Employees
WHERE DepartmentID = '007';
#查询6
SELECT Ename '姓名',Birthday '生日'
FROM Employees
WHERE Sex = 1;

#额外操作
CREATE TABLE game(
	gameName VARCHAR(10)NOT NULL,
	playTime TIME
);
ALTER TABLE game ADD startTime TIME;
ALTER TABLE game DROP startTime;
DROP TABLE game;