【MySQL】深圳大学数据库实验1 MYSQL数据库SQL语句练习实验

m0_74854470

已于 2024-09-06 00:38:39 修改

阅读量568

点赞数 26

文章标签：数据库 mysql sql

于 2024-09-05 23:39:06 首次发布

本文链接：https://blog.csdn.net/m0_74854470/article/details/141944620

版权

四、EXERCISE 1 SIMPLE COMMANDS

五、EXERCISE 2 JOINS

六、EXERCISE 3 FUNCTIONS

七、EXERCISE 4 DATES

7.1EXERCISE 4 前置知识

7.2 练习

实验过程仅作参考

-- 提示：！！！直接复制实验Word数据，由于Word排版问题，部分数据前后会带有空格！！！--

一、创建表结构

1.1创建员工表

-- 创建表
-- 创建员工表
create table emp2022150222
(
    EMPNO    int         not null,
    ENAME    varchar(30) not null,
    JOB      varchar(30) not null,
    MGR      int,
    HIREDATE varchar(30) not null,
    SAL      int         not null,
    COMM     int,
    DEPTNO   int         not null,
    primary key (EMPNO)
) comment '员工表';

1.2创建部门表

-- 创建部门表
create table dept2022150222
(
    DEPTNO int         not null,
    DNAME  varchar(30) not null,
    LOC    varchar(30) not null,
    PRIMARY KEY (DEPTNO)
)comment '部门表';

二、给表插入数据

2.1插入员工数据

-- 插入员工数据
insert into emp2022150222 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '17-Dec-90', 13750, null, 20),
       (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-89', 19000, 6400, 30),
       (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-93', 18500, 4250, 30),
       (7566, 'JONES', 'MANAGER', 7839, '02-APR-89', 26850, null, 20),
       (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-97', 15675, 3500, 30),
       (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-90', 24000, null, 30),
       (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-88', 27500, null, 10),
       (7788, 'SCOTT', 'ANALYST', 7566, '19-APR-87', 19500, null, 20),
       (7839, 'KING', 'PRESIDENT', 7902, '17-NOV-83', 82500, null, 10),
       (7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-92', 18500, 6250, 30),
       (7876, 'ADAMS', 'CLERK', 7788, '23-MAY-96', 11900, null, 20),
       (7900, 'JAMES', 'CLERK', 7698, '03-DEC-95', 12500, null, 30),
       (7902, 'FORD', 'ANALYST', 7566, '03-DEC-91', 21500, null, 20),
       (7934, 'MILLER', 'CLERK', 7782, '23-JAN-95', 13250, null, 10),
       (3258, 'GREEN', 'SALESMAN', 4422, '24-Jul-95', 18500, 2750, 50),
       (4422, 'STEVENS', 'MANAGER', 7839, '14-Jan-94', 24750, null, 50),
       (6548, 'BARNES', 'CLERK', 4422, '16-Jan-95', 11950, null, 50);

2.2插入部门数据

-- 插入部门数据
insert into dept2022150222
(DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'LONDON'),
       (20, 'RESEARCH', 'PRESTON'),
       (30, 'SALES', 'LIVERPOOL'),
       (40, 'OPERATIONS', 'STAFFORD'),
       (50, 'MARKETING', 'LUTON');

三、添加约束关系

-- 给员工表添加外键，将员工表的DEPTNO关联部门表的DEPTNO
alter table emp2022150222
    add constraint emp2022150222___fk_DEPTNO_dept2022150222_DEPTNO
        foreign key (DEPTNO) references dept2022150222 (DEPTNO);

四、EXERCISE 1 SIMPLE COMMANDS

#   1.	List all information about the employees.
select *
from emp2022150222;

# 	2.	List all information about the departments
select *
from dept2022150222;

# 	3.	List only the following information from the EMP table ( Employee name, employee number, salary, department number)
select ENAME, EMPNO, SAL, DEPTNO
from emp2022150222;

# 	4.	List details of employees in departments 10 and 30.
select *
from emp2022150222
where DEPTNO in (10, 30);

# 	5.	List all the jobs in the EMP table eliminating duplicates.
select distinct JOB
from emp2022150222;

# 	6.	What are the names of the employees who earn less than £20,000?
select ENAME
from emp2022150222
where SAL < 20000;

# 	7.	What is the name, job title and employee number of the person in department 20 who earns more than £25000?
select ENAME, JOB, EMPNO
from emp2022150222
where DEPTNO = 20
  and SAL > 25000;

# 	8.	Find all employees whose job is either Clerk or Salesman.
select *
from emp2022150222
where JOB in ('CLERK', 'SALESMAN');

# 	9.	Find any Clerk who is not in department 10.
select *
from emp2022150222
where JOB = 'CLERK'
  and DEPTNO != 10;

# 	10.	Find everyone whose job is Salesman and all the Analysts in department 20.
select *
from emp2022150222
where (JOB = 'SALESMAN')
   or (JOB = 'ANALYST' and DEPTNO = 20);

# 	11.	Find all the employees who earn between £15,000 and £20,000.Show the employee name, department and salary.
select ENAME, DNAME
from emp2022150222,
     dept2022150222
where emp2022150222.DEPTNO = dept2022150222.DEPTNO
  and SAL between 15000 and 20000;

#   12. Find the name of the President.
select ENAME
from emp2022150222
where ENAME = 'PRESIDENT';

#   13. Find all the employees whose last names end with S
select *
from emp2022150222
where ENAME like '%S';

#   14. List the employees whose names have TH or LL in them
select *
from emp2022150222
where ENAME like '%TH%'
   or ENAME like '%LL%';

#   15. List only those employees who receive commission.
select *
from emp2022150222
where COMM is not null;

#   16. Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.
select ENAME, JOB, SAL, HIREDATE, DEPTNO
from emp2022150222
order by ENAME;

#   17. Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.
select ENAME, JOB, SAL, HIREDATE, DEPTNO
from emp2022150222
order by SAL ASC;

#   18. List all salesmen in descending order by commission divided by their salary.***
select *
from emp2022150222
where JOB = 'SALESMAN'
order by COMM, SAL DESC;

#   19. Order employees in department 30 who receive commision, in ascending order by commission
select *
from emp2022150222
where DEPTNO = 30
  and COMM is not null
order by COMM ASC;

#   20. Find the names, jobs, salaries and commissions of all employees who do not have managers.
select ENAME, JOB, SAL, COMM
from emp2022150222
where MGR is null;

# 	21.	Find all the salesmen in department 30 who have a salary greater than or equal to £18000.
select *
from emp2022150222
where JOB = 'SALESMAN'
  and DEPTNO = 30
  and SAL >= 18000;

五、EXERCISE 2 JOINS

#   本节并未考察内连接inner join 、左（外）连接left [outer] join 、右（外）right [outer] join的用法，故以下练习用哪种写法都可以
#   在MySQL中，并不支持全（外）连接 full [outer] join
#   1.  Find the name and salary of employees in Luton.
select ENAME, SAL
from emp2022150222 e
         inner join dept2022150222 d
where e.DEPTNO = d.DEPTNO
  and d.LOC = 'LUTON';

# 	2.	Join the DEPT table to the EMP table and show in department number order.
select *
from emp2022150222 e
         left join dept2022150222 d on e.DEPTNO = d.DEPTNO
order by e.DEPTNO;

#   3.	List the names of all salesmen who work in SALES
select ENAME
from emp2022150222 e
         inner join dept2022150222 d
where e.DEPTNO = d.DEPTNO
  and d.DNAME = 'SALES'
  and e.JOB = 'SALESMAN';

#   4.	List all departments that do not have any employees.
#   join写法
select d.DNAME
from emp2022150222 e
         right join dept2022150222 d on e.DEPTNO = d.DEPTNO
group by d.DNAME
having count(e.DEPTNO) = 0;
#   其他写法
select DNAME
from dept2022150222
where DEPTNO not in (select DEPTNO from emp2022150222);

# 	5	For each employee whose salary exceeds his manager's salary, list the employee's name and salary and the manager's name and salary.
select e.ENAME, e.SAL, m.ENAME, m.SAL
from emp2022150222 e,
     emp2022150222 m
where e.MGR = e.EMPNO
  and e.SAL > m.SAL;

# 	6. 	List the employees who have BLAKE as their manager.
select e.*
from emp2022150222 e,
     emp2022150222 m
where e.MGR = m.EMPNO
  and m.ENAME = 'BLAKE';

六、EXERCISE 3 FUNCTIONS

-- ！！！注意：在本节中，若column数据中含有null，不可以直接使用avg(column)！！！--

#   本节考察了常用函数count(),avg(),sum(),max(),min()函数
#   1.	Find how many employees have a title of manager without listing them.
select count(*)
from emp2022150222
where JOB = 'MANAGER';

# 	2.	Compute the average annual salary plus commission for all salesmen
select avg(SAL) + avg(COMM)
from emp2022150222
where JOB = 'SALESMAN';

# 	3.	Find the highest and lowest salaries and the difference between them (single SELECT statement)
select max(SAL), min(SAL), max(SAL) - min(SAL)
from emp2022150222;

# 	4.	Find the number of characters in the longest department name
select max(length(DNAME))
from dept2022150222;

# 	5.	Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement).
select count(SAL), count(COMM)
from emp2022150222
where DEPTNO = 30;

# 	6.	List the average commission of employees who receive a commission, and the average commission of all employees (assume employees who
#    	do not receive a commission attract zero commission)
select avg(a.COMM), sum(b.COMM) / count(*)
from emp2022150222 a,
     emp2022150222 b
where a.COMM is not null;

#   7.	List the average salary of employees that receive a salary, the average commission of employees that receive a commission, the average
#       salary plus commission of only those employees that receive a commission and the average salary plus commission of all employees
#       including  those that do not receive a commission. (single statement)
select avg(a.SAL), avg(b.COMM), avg(c.SAL + c.COMM), avg(d.SAL + ifnull(d.COMM, 0))
from emp2022150222 a,
     emp2022150222 b,
     emp2022150222 c,
     emp2022150222 d
where a.SAL != 0
  and b.COMM is not null
  and c.COMM is not null;

# 	8.	Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a
#     	month and 8 working hours in a day.
select ENAME, round(SAL / 22) monthly_SAL, round(SAL / 22 / 8) daily_SAL
from emp2022150222
where DEPTNO = 30;

# 	9.	Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.
select ENAME, floor(SAL / 22) monthly_SAL, floor(SAL / 22 / 8) daily_SAL
from emp2022150222
where DEPTNO = 30;

七、EXERCISE 4 DATES

7.1EXERCISE 4 前置知识

-- 日期类型 DATE 包括年、月、日
-- 日期时间类型 DATE 包括年、月、日、时、分、秒

-- 一、
-- 日期格式化函数
-- STR_TO_DATE(str,format) 根据日期时间格式format,将字符串str转换为指定的日期时间
-- 输入一个字符串str,和一个日期时间格式 输出一个日期时间
-- format需要指定字符串哪些部分对应DATE哪些
-- format参数定义了输入字符串的日期时间格式，可以包含各种日期时间格式化符号，如%Y、%m、%d等，用来表示年、月、日等不同部分
-- %Y：四位年份 %y：两位年份 %m：两位月份 %c：月份（0-12）%d：两位日期
-- %H：小时（00-23）%h：小时（01-12）%i：两位分钟 %s：两位秒数 %p：AM或PM
-- %b 月份的缩写形式如（Jan、Feb、Mar）
-- 注 %Y 四位年份 %y 两位年份(自动根据后两位补全前两位，具体细节未知，如补全错误，需用代码矫正)

-- DATE_FORMAT(date,format) 将日期时间date格式化为指定的格式format

-- 二、
-- 日期计算函数
-- DATE_ADD(date,INTERVAL expr type) 给日期增加指定的时间间隔
-- 输入 日期date , 时间间隔expr 和 要变化的值的时间类型，包括YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MICROSECOND、WEEK、QUARTER(季度)
-- 如select date_add(now(),INTERVAL 7 DAY); 表示当前日期向后推7天

-- DATE_SUB(date,INTERVAL expr type) 给日期减少指定的时间间隔
-- 方法同DATE_ADD(date,INTERVAL expr type)

-- CURDIFF(date1,date2)  返回起始时间date1和结束时间date2之间的天数

-- WEEKDAY(date) 输入一个DATE/DATETIME类型，返回一个数字 ，数字范围为 0~6 表示星期一到星期日
-- DAYOFWEEK(date) 输入一个DATE/DATETIME类型，返回一个数字 ，数字范围为 1~7  1表示星期日 2表示星期一 3表示星期二 ...
-- 注：西方国家有认为星期日作为一周的开始的习俗，因此1表示星期日
-- WEEKDAY(date)和DAYOFWEEK(date)的区别：WEEKDAY(date) 则认为周一是一周的开始，而DAYOFWEEK(date) 认为周日是一周的开始

-- 三、
-- ‌获取当前日期和时间的函数‌‌，无输入，有输出
-- NOW() 获取当前的日期和时间，包含年、月、日、时、分、秒
-- CURDATE() 获取当前日期（包括年、月、日），不包括当前时间（即不包括当前小时、分钟、秒）
-- CURDATE() 获取当前日期（包括年、月、日），不包括当前时间（即不包括当前小时、分钟、秒）
-- CURRENT_TIMESTAMP() 获取当前的日期和时间

-- 四、
-- ‌提取日期和时间部分的函数，输入一个日期类型，输出一个日期类型
-- DATE() 提取字段的日期部分 TIME() 提取字段的时间部分
-- YEAR() 提取字段的年 MONTH() 提取字段的月 DAY() 提取字段的日
-- HOUR() 提取字段的小时 MINUTE() 提取字段的分钟

7.2 练习

#    EXERCISES 4	DATES
#    本节考察了较多对日期函数的使用
#    1.	Select the name, job, and date of hire of the employees in department 20. (Format the HIREDATE column to MM/DD/YY)
select ENAME, JOB, STR_TO_DATE(HIREDATE, '%d-%b-%y')
from emp2022150222
where DEPTNO = 20;

#    2.	Then format the HIREDATE column into DoW (day of the week), Day (day of the month), MONTH (name of the month) and YYYY(year)
select HIREDATE,
       DAYOFWEEK(STR_TO_DATE(HIREDATE, '%d-%b-%y')),
       DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')),
       MONTH(STR_TO_DATE(HIREDATE, '%d-%b-%y')),
       YEAR(STR_TO_DATE(HIREDATE, '%d-%b-%y'))
from emp2022150222;

#    3.	Which employees were hired in April?
select *
from emp2022150222
where DAYOFWEEK(STR_TO_DATE(HIREDATE, '%d-%b-%y')) = 4;

#    4.	Which employees were hired on a Tuesday?
select *
from emp2022150222
where DAYOFWEEK(STR_TO_DATE(HIREDATE, '%d-%b-%y')) = 2;

#    5.	Are there any employees who have worked more than 30 years for the company?
select *
from emp2022150222
where YEAR(CURDATE()) - YEAR(STR_TO_DATE(HIREDATE, '%d-%b-%y')) > 30;

#    6. Show the weekday of the first day of the month in which each employee was hired. (plus their names)
#       题意：展示员工入职当月，当月的第一天是星期几
select ENAME,
       DAYNAME(STR_TO_DATE(CONCAT(DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%y-%b'), '-01'),
                           '%y-%b-%d')) as firstday_weekday
from emp2022150222;

#     7.Show details of employee hiredates and the date of their first payday.
#     (Paydays occur on the last Friday of each month) (plus their names)
select ENAME,
       HIREDATE,
       -- 判断在一个月最后一个星期五之前还是之后
       CASE
           WHEN STR_TO_DATE(HIREDATE, '%d-%b-%y') > DATE_SUB( -- 之后
                   LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                   (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')))  - 4 + 7) % 7 DAY
                                                    ) THEN
               DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%y-%m'), '-01'),
                                             '%Y-%m-%d') + INTERVAL 1 MONTH),
                        INTERVAL (WEEKDAY(LAST_DAY(
                       STR_TO_DATE(CONCAT(DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%Y-%m'), '-01'), '%Y-%m-%d') +
                       INTERVAL 1 MONTH)) - 4 + 7) % 7 DAY
               )
           ELSE -- 之前

               DATE_SUB(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                        (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))) - 4 + 7) % 7 DAY)
           END as fist_payday
from emp2022150222;

#     8.Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
select ENAME,
       HIREDATE,
       DATE_SUB(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))) - 4 + 7) % 7 DAY)
from emp2022150222;