MySQL DML操作 加强练习题

MySQL DML操作 加强练习题

题目1

1.创建员工表 emp[可参考文档下方]，包含以下字段：编号、姓名、性别、年龄、电
话、地址、入职时间、邮件地址；执行以下操作：
A. 查询所有职员的所有信息
B. 查询所有职员的姓名,电话,地址
C. 查询所有女职员的详细信息
D. 查询年龄在 18 到 20 岁之间的职员的姓名,性别
E. 查询家住长安的男职员的姓名,电话,地址
F. 查询唐僧、猪八戒、白骨精的电话,地址
G. 查询家住花果山和西海的职员的姓名,性别,年龄
H. 查询家住西海,年龄在 15 到 25 岁之间的男职员的姓名
I. 查询邮件地址为空的职员
J.查询入职时间超过两年的员工
K.查询男女职员的数目信息
L.分别求男，女员工年龄总和
M.找出年龄最小的两位长安员工
N.求所有员工的年龄总和

emp数据信息

drop table if exists emp;
create table emp
(
 id varchar(12),
 name varchar(12),
 sex char(2),
 age int,
 phone varchar(11),
 address varchar(20),
 times timestamp,
 email varchar(20)
);
insert into emp values('A100101','唐僧','男',58,'13111223344','长安','2000-1-1','133912@qq.com');
insert into emp values('A100102','孙悟空','男',38,'13111223344','花果山','2000-1-1','133552@qq.com');
insert into emp values('A100103','猪八戒','男',28,'13111223344','高老庄','2001-1-1','133682@qq.com');
insert into emp values('A100104','沙僧','男',27,'13111223344','流沙河','2002-1-1','133512@qq.com');
insert into emp values('A100105','小龙女','女',18,'13111223344','东海','2010-1-1','133312@qq.com');
insert into emp values('A100106','白龙马','男',19,'13111223344','西海','2011-1-1','133112@qq.com');
insert into emp values('A100107','白骨精','女',20,'13111223344','白骨洞','2015-1-1','133222@qq.com');
insert into emp values('A100106','太白金星','男',19,'13111223344','长安','2011-1-1','133182@qq.com');
select * from emp;

答案

#第一题

#A.查询所有职员的所有信息
select * from emp;
#B.查询所有职员的姓名,电话,地址
SELECT NAME,PHONE,EMAIL FROM EMP;
#C.查询所有女职员的详细信息
SELECT * FROM EMP WHERE SEX='女';
#D. 查询年龄在 18 到 20 岁之间的职员的姓名,性别
SELECT name,sex FROM emp WHERE age BETWEEN 18 AND 20;
#E. 查询家住长安的男职员的姓名,电话,地址
SELECT name,phone,address FROM emp WHERE sex='男' AND address='长安';
#F. 查询唐僧、猪八戒、白骨精的电话,地址
SELECT name,phone,address FROM emp WHERE name='唐僧' or name='猪八戒' or name='白骨精';
#G. 查询家住花果山和西海的职员的姓名,性别,年龄
SELECT name,sex,age,address FROM emp WHERE address='花果山' or address='西海';
#H. 查询家住西海,年龄在 15 到 25 岁之间的男职员的姓名
SELECT name FROM emp WHERE address='西海' AND sex='男' AND age BETWEEN 15 AND 25;
#I. 查询邮件地址为空的职员
SELECT * FROM emp WHERE email IS NULL;
#J.查询入职时间超过两年的员工
SELECT * FROM emp WHERE TIMESTAMPDIFF(YEAR,times,now())>2;
#K.查询男女职员的数目信息
SELECT COUNT(SEX) FROM emp WHERE sex='男';
SELECT COUNT(SEX) FROM emp WHERE sex='女';
#L.分别求男，女员工年龄总和
SELECT SUM(age) FROM emp WHERE sex='男';
SELECT SUM(age) FROM emp WHERE sex='女';
#M.找出年龄最小的两位长安员工
SELECT MIN(age) FROM emp WHERE address='长安';
#N.求所有员工的年龄总和
SELECT SUM(age) FROM emp;

---

题目二

2.创建部门表 dept 和员工表 emps[可参考文档下方]，完成以下操作：
A.找出各月最后一天受雇的所有雇员
B.找出早于 12 年之前受雇的雇员
C.显示只有首字母大写的所有雇员的姓名
D.显示正好为 6 个字符的雇员的姓名
E.显示不带有"R"的雇员姓名
F.显示所有雇员的姓名的前三个字符
G.显示所有雇员的姓名，用"a"替换所有的"A"
H.显示所有雇员的姓名以及满 10 年服务年限的日期
I.显示雇员的详细资料，按姓名排序
J.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。
K.显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，而工作按薪
金排序
L.显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最
前年份的排在最前面
M.显示在一个月为 30 天的情况下所有雇员的日薪金，忽略余数
N.找出在（任何年份的）2 月受聘的所有雇员
O.对于每个雇员，显示其加入公司的天数
P.显示姓名字段的任何位置包含"A"的所有雇员的姓名
Q.以年、月和日显示所有雇员的服务年限

两表数据信息

DROP TABLE IF EXISTS EMPS;
DROP TABLE IF EXISTS DEPT;
CREATE TABLE DEPT
(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
CREATE TABLE EMPS
(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT,
HIREDATE TIMESTAMP,
SAL FLOAT,
COMM FLOAT,
DEPTNO INT,
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');

INSERT INTO EMPS VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMPS VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMPS VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMPS VALUES(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO EMPS VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMPS VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO EMPS VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMPS VALUES(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);
INSERT INTO EMPS VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMPS VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO EMPS VALUES(7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);
INSERT INTO EMPS VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO EMPS VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO EMPS VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

SELECT * FROM DEPT;
SELECT * FROM EMPS;

答案

#第二题

#A.找出各月最后一天受雇的所有雇员
SELECT * FROM EMPS WHERE HIREDATE=LAST_DAY(HIREDATE);
#B.找出早于 12 年之前受雇的雇员
SELECT * FROM EMPS WHERE TIMESTAMPDIFF(YEAR,HIREDATE,NOW())>12;
#C.显示只有首字母大写的所有雇员的姓名
#regexp是正则表达式 用于复杂查询搜索
#binary区分大小写 众所周知 MySQL不区分大小写
#   ^   匹配输入字符串的开始位置
#     $ 指字符串的结束
#  \ 转义符 避免把[A-Z]被识别为一个字符
SELECT ENAME FROM EMPS WHERE ENAME REGEXP BINARY '^\[A-Z]';
#D.显示正好为 6 个字符的雇员的姓名
SELECT ENAME FROM EMPS WHERE LENGTH(ENAME)=6;
#E.显示不带有"R"的雇员姓名
SELECT ENAME FROM EMPS WHERE ENAME NOT LIKE '%R%';
#F.显示所有雇员的姓名的前三个字符
SELECT SUBSTR(ENAME FROM 1 FOR 3) FROM EMPS;
#G.显示所有雇员的姓名，用"a"替换所有的"A"
SELECT REPLACE(ENAME,'A','a') FROM EMPS;
#H.显示所有雇员的姓名以及满 10 年服务年限的日期
SELECT ENAME,ADDDATE(HIREDATE,INTERVAL 10 YEAR) FROM EMPS;
#I.显示雇员的详细资料，按姓名排序
SELECT * FROM EMPS ORDER BY ENAME;
#显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。
SELECT ENAME,HIREDATE FROM EMPS ORDER BY DATEDIFF(NOW(),HIREDATE) DESC;
#K.显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，而工作按薪金排序
SELECT ENAME,JOB,SAL FROM EMPS ORDER BY JOB DESC,SAL ASC;
#L.显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最前年份的排在最前面
SELECT ENAME,HIREDATE FROM EMPS ORDER BY DATE_FORMAT(HIREDATE,'%Y') ASC,DATE_FORMAT(HIREDATE,'%m') ASC;
#M.显示在一个月为 30 天的情况下所有雇员的日薪金，忽略余数
SELECT ENAME,round(sal/30,0) AS daysal FROM EMPS;
#N.找出在（任何年份的）2 月受聘的所有雇员
SELECT * FROM EMPS WHERE DATE_FORMAT(HIREDATE,'%m')=2;
#O.对于每个雇员，显示其加入公司的天数
SELECT ENAME,TIMESTAMPDIFF(DAY,HIREDATE,NOW()) AS JOBDAY FROM EMPS; 
#P.显示姓名字段的任何位置包含"A"的所有雇员的姓名
SELECT ENAME FROM EMPS WHERE ENAME LIKE '%A%';
#Q.以年、月和日显示所有雇员的服务年限
SELECT ENAME,HIREDATE,TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) AS '加入年数',TIMESTAMPDIFF(MONTH,HIREDATE,NOW()) AS '加入月数',TIMESTAMPDIFF(DAY,HIREDATE,NOW()) AS '加入天数' FROM EMPS;