mysql练习

增删改查

向表中添加5记录，要求所有简历投递日期字段均为今天日期，其中，包含3条电话号码为空的记录
INSERT INTO job (id,recDate,source,position,name,age,tel,sex)
VALUES (411,‘2022-7-7’,‘京城’,‘顾问’,‘冯哥’,18,10086,‘男’),
(412,‘2022-7-7’,‘京城’,‘老师’,‘东哥’,18,10000,‘男’),
(413,‘2022-7-7’,‘京城’,‘班主任’,‘代哥’,18,default,‘男’),
(414,‘2022-7-7’,‘京城’,‘班主任’,‘丽姐’,18,default,‘女’),
(415,‘2022-7-7’,‘京城’,‘班助’,‘吴哥’,18,default,‘男’);

– 将所有求职者的年龄加1岁
UPDATE job set age = age + 1;

– 本公司不打算面试来路不明的求职者，请删除简历来源为“未知”的求职者信息
DELETE FROM job WHERE source = ‘未知’;

– “咨询师”与“咨询顾问”本为一个职位，顾公司欲将表中职位为“咨询师”的改为“咨询顾问”，统一职位名称，请使用SQL命令实现该功能

UPDATE job set position = ‘咨询顾问’ WHERE position = ‘咨询师’;

– 本公司不打算录用男性咨询师，请删除不符合要求的信息
DELETE FROM job WHERE position = ‘咨询顾问’ && sex=‘男’;

– 没有留下电话的求职者，无法约访面试，请删除所有未留电话的求职者信息
DELETE FROM job WHERE tel is null;

– 本公司不打算录用年龄在30-34岁之间的咨询顾问，请删除不符合要求的信息
DELETE FROM job WHERE position=‘咨询顾问’ && age BETWEEN 30 AND 34;

– 本公司的“PHP实习生”、“ 美工”、“校园代理”职位已满，不打算再约面试，请删除这几种职位的求职者信息

DELETE FROM job WHERE position in (‘PHP实习生’,‘美工’,‘校园代理’);

– 查询求职“市场专员”职位的求职者简历来源、姓名、性别、职位
SELECT source,name,sex,position FROM job WHERE position = ‘市场专员’;

– 查询7月份收到的简历信息，显示求职者职位、姓名、年龄、电话
SELECT position,name,age,tel FROM job WHERE MONTH(recDate) = ‘07’;

– 查询年龄在20-23岁之间的求职咨询顾问的女性求职者有哪些人，显示职位、姓名、年龄、性别
SELECT position,name,age,sex FROM job WHERE position = ‘咨询顾问’ && age BETWEEN 20 AND 23 && sex =‘女’;

– 查询电话号码为158开头，且尾号为8的求职者信息，显示姓名、职位、电话
SELECT name,position,tel FROM job WHERE tel like ‘158%’;

– 查询来自于智联招聘和大连快线的求职网络推广的有哪些人，显示简历来源、姓名、职位
SELECT source,name,position FROM job WHERE source in (‘智联招聘’,‘大连快线’);

– 查询求职实习生的有哪些人，显示简历来源、姓名、职位、年龄
SELECT source,name,sex,position,age FROM job WHERE position=‘实习生’;

– 查询没有电话的求职者信息，显示姓名、职位、年龄、电话
SELECT name,position,age,tel FROM job WHERE tel is null;

– 查询所有员工的信息，并将查询结果中的每个列的列名起一个中文的别名
SELECT emp_id AS ‘员工编号’,emp_name as’姓名’,emp_cardid as’卡号’,emp_wage as ‘工资’,emp_salary as’奖金’,emp_city as’城市’,com_id as’部门编号’,emp_date as’入职时间’ FROM employees;

– 查询c03部门的员工有哪些人
SELECT emp_name FROM employees WHERE com_id = ‘c03’;

– 查询工资总额（基本工资+奖金）在4000-7000之间的员工信息
SELECT emp_name,emp_wage,emp_salary FROM employees WHERE emp_wage+emp_salary BETWEEN 4000 and 7000;

– 查询来自大连，沈阳和北京的员工信息
SELECT * FROM employees WHERE emp_city in(‘大连’,‘沈阳’,‘北京’);

– 查询来自未知城市的员工
SELECT * FROM employees WHERE emp_city is NULL;

– 查询2000年以后入职的员工信息
SELECT * FROM employees WHERE year(emp_date)>=2000;

– 查询姓王的员工信息
SELECT * FROM employees WHERE emp_name like ‘王%’;

– 查询人事部的负责人是谁
SELECT com_manager FROM department WHERE com_name = ‘人事部’;

– 查询c05部门的员工来自哪几个城市
SELECT emp_name,emp_city FROM employees WHERE com_id=‘c05’;

– 查询c03部门来自大连的员工有哪些人
SELECT * FROM employees WHERE com_id=‘c03’ && emp_city=‘大连’;

– 查询标题中含有“of”的图书信息
SELECT * FROM titles WHERE title like ‘%of%’;

– 查询出版日期在1991年6月份的图书信息
SELECT * FROM titles WHERE YEAR(pubdate) ='1991’and MONTH(pubdate)=‘06’;

– 查询类型是psychology的图书信息
SELECT * FROM titles WHERE type = ‘psychology’;

– 显示出1994年9月份的订单信息
SELECT * FROM sales WHERE year(ord_date)=‘1994’ and month(ord_date)=‘09’;

– 显示出销售数量为20的图书信息
SELECT * FROM sales WHERE qty=20;

---

– 查询本公司都招聘哪些职位
SELECT DISTINCT(position) FROM job;

– 查询本公司的简历都通过哪些途径获取
SELECT DISTINCT(source) FROM job;

– 在求职市场专员职位的男性求职者中，显示出最年轻的前10位求职者信息
SELECT * FROM job WHERE position = ‘市场专员’&& sex=‘男’ ORDER BY age asc LIMIT 0,10;

– 查询本公司一共收到多少份简历
SELECT count(0) FROM job;

– 查询每个招聘途径收到多少份简历
SELECT count(0),source FROM job GROUP BY source;

– 查询求职“市场专员”职位的男女求职者分别多少人
SELECT count(0),sex FROM job where position=‘市场专员’ GROUP BY sex ;

– 查询哪种招聘途径收到的咨询顾问简历最多
SELECT count(0),source FROM job WHERE position =‘咨询顾问’ GROUP BY source ORDER BY count(0) desc LIMIT 0,1;

– 查询年龄在22-28岁之间的求职者中，每个职位有多少人感兴趣，将结果按照人数由多到少显示
SELECT count(0),position FROM job WHERE age BETWEEN 22 and 28 GROUP BY position ORDER BY count(0) desc;

– 收到简历在20份以上的招聘途径分别是哪些
SELECT count(0),source FROM job GROUP BY source HAVING count(0)>20;

– 查询本公司招聘多少个职位
SELECT count(DISTINCT(position)) FROM job;

– 显示哪个两个职位最受欢迎
SELECT count(0),position FROM job GROUP BY position ORDER BY count(0) desc LIMIT 0,2;

– 显示哪个月份收到的简历数最多
SELECT MONTH(recDate) FROM job GROUP BY position ORDER BY count(0) desc LIMIT 0,1;

– 显示女性求职者中，每个职位的求职者平均年龄是多少岁
SELECT avg(age),position FROM job WHERE sex='女’GROUP BY position;

– 显示投递市场专员职位的求职者中，男女求职者的最大年龄分别是多少岁
SELECT max(age),sex FROM job WHERE position=‘市场专员’ GROUP BY sex;

– 查询工资总额最高的员工信息
SELECT * FROM employees ORDER BY emp_salary + emp_wage desc LIMIT 0,1;

– 查询c02部门获得奖金最高的员工是谁
SELECT emp_name FROM employees WHERE com_id=‘c02’ ORDER BY emp_salary desc LIMIT 0,1;

– 查询所有员工的信息，将信息按照基本工资从高到低显示，当基本工资相同时，按照员工的入职日期由晚到早来显示
SELECT * FROM employees ORDER BY emp_wage,emp_date DESC;

– 查询显示出所有员工的基本工资总和，基本工资平均值，最高工资和最低工资，给每个结果列都要起一个别名
SELECT sum(emp_wage),avg(emp_wage),max(emp_wage),min(emp_wage) FROM employees;

– 查询销售额最高的部门编号
SELECT com_id FROM department group BY com_name ORDER BY com_total desc LIMIT 0,1;

– 根据employee表查询出本公司有几个部门
SELECT COUNT(DISTINCT com_id) FROM employees;

– 查询出本公司的员工来自几个城市
SELECT DISTINCT(emp_city) FROM employees;

– 查询哪个部门的平均工资最高
SELECT avg(emp_wage),com_id FROM employees GROUP BY com_id ORDER BY avg(emp_wage) desc,max(emp_wage) DESC LIMIT 0,1;

– 查询来自每个城市的员工分别有多少人
SELECT count(0),emp_city FROM employees GROUP BY emp_city;

– 查询显示该公司每个城市的员工平均工资
SELECT avg(emp_salary) FROM employees GROUP BY emp_city;

– 在基本工资高于2000元的员工中，查询每个部门的员工平均工资，并显示出部门平均工资高于4000元的记录，按照平均工资由低到高显示!!!
SELECT avg(emp_wage),com_id FROM employees WHERE emp_wage > 2000 GROUP BY com_id having avg(emp_wage)>3000 ORDER BY avg(emp_wage) desc;

– 查询部门人数超过6个人的部门编号
SELECT com_id,count(0) FROM employees GROUP BY com_id HAVING count(0)>6;

– 查询每个部门的最高奖金为多少钱
SELECT max(emp_salary) FROM employees GROUP BY com_id;

– 查询每一年入职的员工有多少人，显示效果如下图所示（提示：函数concat(值1，值2)用于将两个值连接到一起） concat(year(joinDate),’year’)
SELECT CONCAT(year(emp_date),‘年’) ‘入职年份’,count(0) ‘人数’ FROM employees GROUP BY year(emp_date);

多表查询

– 查询与“罗曼”求职同一职位的还有哪些人
SELECT * FROM job WHERE position = (SELECT position FROM job WHERE name=‘罗曼’);

– 查询哪些求职者的年龄高于所有求职者的平均年龄
SELECT * FROM job WHERE age>(SELECT avg(age) FROM job);

– 查询最受欢迎的职位有哪些求职者感兴趣
SELECT position FROM job GROUP BY position HAVING count(0) = (SELECT count(0) FROM job GROUP BY position ORDER BY count(0) desc LIMIT 1);

– 查询与“王亮”同一天面试的还有哪些人
SELECT * FROM job a
inner join job b
on a.name=‘王亮’ and a.recDate = b.recDate and b.name !=‘王亮’;

– 查询哪些职位比“网络推广”职位更受欢迎
SELECT position,count(0) a FROM job GROUP BY position HAVING count(0)>(SELECT count(0) b FROM job WHERE position =‘网络推广’)

– x查询出来自大连所有员工的信息，显示出员工的姓名，所在部门，工资总和
SELECT com_name,a FROM department a
left join (SELECT com_id,sum(emp_wage) a FROM employees GROUP BY com_id) b
on b.com_id = a.com_id
union
SELECT emp_name,com_name FROM employees
right join department
on employees.com_id = department.com_id

– 查询人事部门的所有员工信息，显示员工姓名，城市，基本工资和奖金（只允许使用嵌套查询）
SELECT emp_name,emp_city,emp_wage,emp_salary FROM employees WHERE com_id = (SELECT com_id FROM department WHERE com_name=‘人事部’);

– 查询employees表中工资低于所有员工平均工资的员工信息，显示员工姓名，工资字段
SELECT emp_name,emp_wage FROM employees WHERE emp_wage>(SELECT avg(emp_wage)FROM employees)

– 查询employees表中比“冰红茶”入职晚的所有员工的信息，显示员工姓名，入职日期字段
SELECT emp_name,emp_date FROM employees WHERE emp_date>(SELECT emp_date FROM employees WHERE emp_name=‘冰红茶’)

– 查询employees表中获得最高奖金(emp_salary)的员工信息，显示员工姓名，奖金字段
SELECT emp_name,emp_salary FROM employees WHERE emp_salary=(SELECT max(emp_salary) FROM employees);

– 查询部门经理是“王九”的部门中所有员工信息，显示员工姓名，部门编号
SELECT a.emp_name,b.com_id from employees as a
inner join department as b
on b.com_manager=‘王九’ and a.com_id = b.com_id;

– 查询和张三处在同一部门的所有员工的姓名
SELECT emp_name FROM employees where com_id = (SELECT com_id FROM department where com_manager=‘张三’)

– 查询哪个部门的员工工资总额最高，显示该部门的名称及经理
SELECT com_name,com_manager FROM department where com_id=(SELECT com_id FROM employees GROUP BY com_id ORDER BY sum(emp_wage) desc LIMIT 1)

– 查询工资最高的员工来自哪个部门，显示员工的姓名，所在部门的名称，经理名字及工资数额
SELECT com_name,com_manager,s FROM department a
inner join (SELECT emp_wage+emp_salary s,com_id FROM employees ORDER BY emp_wage+emp_salary desc LIMIT 1) b
on b.com_id=a.com_id

– 按照部门人数由高到低显示出每个部门的名称、人数、部门负责人
SELECT com_name,c,com_manager FROM department a
inner join (SELECT count(0) c,com_id FROM employees GROUP BY com_id ORDER BY c desc) b
on a.com_id=b.com_id
ORDER BY c desc

– 显示出总销售额（com_total）最高的部门有哪些员工
SELECT emp_name FROM employees where com_id=(SELECT com_id FROM department ORDER BY com_total LIMIT 1)

– 查询哪个城市的员工最多，列出该城市的所有员工信息
SELECT a.emp_name,b.emp_city FROM employees a
inner join (SELECT count(0) c,com_id,emp_city FROM employees GROUP BY emp_city ORDER BY c desc limit 1) b
on a.com_id=b.com_id
ORDER BY c desc

– 查询哪个部门的平均年龄最大，显示出该部门的名字
SELECT com_name,v FROM department a
INNER join (SELECT com_id,avg(2022-year (emp_date)) v FROM employees GROUP BY com_id ORDER BY v desc LIMIT 1) b
on a.com_id=b.com_id

– 查询每个员工的姓名，职位和工资情况
SELECT employeeName,positionName,wage
FROM emp e
inner join salary0 s
on e.employeeID=s.employeeID
inner join posmess p
on s.position=p.positionID

– 查询职位是“Teacher”的员工包括哪些，显示员工姓名，工资
SELECT EmployeeName,wage
FROM emp e
inner join salary0 s
on e.employeeID=s.employeeID
inner join posmess p
on s.position=p.positionID and p.positionName=‘Teacher’

– 查询电话号为空的员工是什么职位，显示员工姓名和职位名称
SELECT employeeName,positionName
FROM emp e
inner join salary0 s
on e.employeeID=s.employeeID
inner join posmess p
on s.position=p.positionID WHERE employeetel is null

– 查询出所有员工的基本信息及工资信息，显示员工编号，员工姓名，所处职位名称，工资数额，没有工资的员工只显示员工的编号和姓名

SELECT e.employeeID,employeeName,positionName,wage
FROM emp e
left join salary0 s
on e.employeeID=s.employeeID
LEFT join posmess p
on s.position=p.positionID

– 查询工资总额（基本工资+奖金）最高的员工叫什么
SELECT employeeName
FROM emp e
inner join (SELECT wage,employeeID,position FROM salary0 ORDER BY wage LIMIT 1) s
on e.employeeID=s.employeeID
inner join posmess p
on s.position=p.positionID

– 查询哪个职工目前还没有分配职位
SELECT employeeName
FROM emp e
left join salary0 s
on e.employeeID=s.employeeID
LEFT join posmess p
on s.position=p.positionID
where positionName is null

– 查询出最年长的员工是什么职位，工资多少钱
SELECT employeeName,positionName,wage
FROM (SELECT emp.* FROM emp where employeeAge = (SELECT max(employeeAge)FROM emp)) e
inner join salary0 s
on e.employeeID=s.employeeID
left join posmess p
on s.position=p.positionID