使用ecs服务器进行mysql操作

1. 创建szu_test数据库

在服务器后台命令行中使用命令登录进数据库

mysql -h localhost -u szu_test -p

查看系统中有哪些数据库，并使用szu_test数据库

一开始数据库是空的

1. 建立DEPT表格

导入数据dept

 Sql语句

1. INSERT INTO DEPT (DEPTNO,DNAME,LOC)
2. VALUES
3. (10 , 'ACCOUNTING' ,'LONDON'),
4. (20 , 'RESEARCH' ,'PRESTON'),
5. (30 , 'SALES' ,'LIVERPOOL'),
6. (40 , 'OPERATIONS' ,'STAFFORD'),
7. (50 , 'MARKETING' ,'LUTON');

 查看导入的情况

1. 建立EMP表格

 导入数据emp

（由于文字太多，这里采用图片显示）

2、完成EXERCISES 1 对表格的操作，1-21题

 题目：

List all information about the employees.

 解析：

这个题目要求我们列出所有的员工信息

 结果：

1. SELECT * FROM EMP;

List all information about the departments

列出所有部门的信息

1. SELECT * FROM DEPT;

List only the following information from the EMP table ( Employee name, employee number, salary, department number)

选择出特定的几个字段

1. SELECT
2. EMPNO, ENAME,SAL, DEPTNO
3. FROM EMP;

List details of employees in departments 10 and 30.

条件查询，用or来表明逻辑关系

1. SELECT *
2. FROM EMP
3. WHERE DEPTNO=10 or DEPTNO=30;

5.

List all the jobs in the EMP table eliminating duplicates.

使用distinct特殊字来对查找出的内容进行去重

2. distinct JOB

What are the names of the employees who earn less than £20,000?

（条件小于）查询

1. SELECT ENAME
3. WHERE SAL<20000;

7.

What is the name, job title and employee number of the person in department 20 who earns more than £25000?

And条件查询

1. SELECT EMPNO, ENAME, JOB
3. WHERE DEPTNO=20
4. and SAL> 25000;

Find all employees whose job is either Clerk or Salesman.

or条件查询，判断字符串相等

3. WHERE JOB='Clerk'
4. or JOB='Salesman';

Find any Clerk who is not in department 10.

and 条件查询

3. WHERE DEPTNO!=10
4. and JOB='Clerk';

Find everyone whose job is Salesman and all the Analysts in department 20.

使用括号的多层逻辑查询

3. WHERE (JOB='Salesman')
4. or (JOB='Analyst'and DEPTNO=20);

Find all the employees who earn between £15,000 and £20,000.

Show the employee name, department and salary.

使用 BETWEEN AND关键字进行查询，找出SAL字段在某个区间的数据

1. SELECT ENAME, SAL, DEPTNO
3. WHERE
4. SAL BETWEEN 15000 AND 20000;

Find the name of the President.

条件查询

3. WHERE JOB='President';

Find all the employees whose last names end with S

使用like关键字来进行特定字符结尾的查询

2. FROM EMP
3. WHERE ENAME like '%S';

List the employees whose names have TH or LL in them

使用like关键字来进行特定字符结尾的or逻辑查询

3. WHERE ENAME like '%th%'
4. or ENAME like '%ll%';

List only those employees who receive commission.

条件查询，判断COMM字段不为空

3. WHERE COMM is not null;

Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

将查询结果用order by关键字排序

1. SELECT ENAME, JOB, HIREDATE, SAL, DEPTNO
3. order by ENAME;

Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.

3. order by SAL asc;

List all salesmen in descending order by commission divided by their salary.

将查询结果用order by和 desc关键字降序排序

3. WHERE JOB='salesman'
4. order by COMM/SAL
5. desc;

Order employees in department 30 who receive commision, in ascending order by commission

and逻辑查询，并且将结果按comm进行升序排序

1. SELECT * FROM EMP
2. WHERE DEPTNO=30 and COMM is not null
3. order by COMM
4. asc;

Find the names, jobs, salaries and commissions of all employees who do not have managers.

判断某个字段非null，进行查询

1. SELECT ENAME, JOB, SAL, COMM
3. WHERE MGR is null;

Find all the salesmen in department 30 who have a salary greater than or equal to £18000.

多条件and逻辑查询，并将结果按升序排序

4. and DEPTNO=30
5. and SAL>=18000
6. ORDER BY COMM asc;

3、完成EXERCISES 2对表格的操作，1-6题

Find the name and salary of employees in Luton.

多表连接查询，首先利用逻辑EMP.deptno = DEPT.deptno，将两张表对应位置合并，再根据loc字段来进行查询，最终找到所有loc=Luton的雇员

1. SELECT ename, sal
2. FROM EMP, DEPT
3. WHERE loc = 'LUTON'
4. AND EMP.deptno = DEPT.deptno;

Join the DEPT table to the EMP table and show in department number order.

多表连接查询，首先利用逻辑EMP.deptno = DEPT.deptno，将两张表对应位置合并，再根据deptno字段进行排序

1. SELECT empno, ename, job, mgr, hiredate, sal, comm, DEPT.deptno, DEPT.dname, DEPT.loc
3. WHERE EMP.deptno = DEPT.deptno
4. ORDER BY deptno;

List the names of all salesmen who work in SALES

连接查询，查找出所有在sales工作的saleman

1. SELECT ename
2. FROM EMP , DEPT
3. WHERE job = 'SALESMAN'
4. AND dname = 'SALES';

List all departments that do not have any employees.

 多级查询，首先利用DEPT.deptno = EMP.deptno语句将emp和dept两表连接，查询出deptno字段作为集合，在集合中利用deptno字段查找出没有任何雇员的部门，

1. SELECT DEPT.deptno
2. FROM DEPT
3. WHERE deptno NOT IN (
5. FROM DEPT, EMP
6. WHERE DEPT.deptno = EMP.deptno
7. );

For each employee whose salary exceeds his manager's salary, list the employee's name and salary and the manager's name and salary.

利用worker.mgr=manager.empno语句将两表连接，再根据条件进行查询worker.sal > manager.SAL

1. SELECT worker.ename,worker.sal,manager.ename,manager.sal
2. FROM EMP worker,EMP manager
3. WHERE worker.mgr=manager.empno
4. AND worker.sal > manager.SAL;

List the employees who have BLAKE as their manager.

利用worker.mgr=manager.empno语句将两表连接，再根据条件进行查询manager.ename = 'BLAKE'

1. SELECT worker.*
2. FROM EMP worker, EMP manager
3. WHERE worker.mgr = manager.empno
4. AND manager.ename = 'BLAKE';

实验结论或体会：

本次实验由于给的时间比较长，所以我一共使用两种方式完成了实验

 在wampserver上完成实验

 使用自己的阿里云弹性云服务器完成实验

由于在服务器上完成实验会更贴近开发中实际使用数据库的环境，于是在实验报告中我呈现的内容为在服务器上完成的

本次实验收获还是很大的，但是也遇到了一些困难

 一开始的服务器配置，mysql的安装废了我不少功夫

 在导入数据上也下了一番功夫，最后是采用的sql语句中的insert方式插入的

 在一开始的1-21题中倒是没有遇到非常难的问题

 在之后的1-6题中，个人感觉某些连接查询的部分还是会费点功夫的

 例如在第二部分的第4题中查询的逻辑就比较复杂

 用到了多级查询，首先利用DEPT.deptno = EMP.deptno语句将emp和dept两表连接，查询出deptno字段作为集合，在集合中利用deptno字段查找出没有任何雇员的部门，