数据库实验四 查询语句

#1.查询每个员工的所有数据

USE yggl;

#查询departments和salary

SELECT * FROM departments;
SELECT *FROM salary;

#2.查询每个员工的姓名、地址和电话

SELECT NAME,Address,phonenumber FROM employees;

#查询departments表中的部门名称

SELECT departmentname FROM departments;

#查询salary表中领取工资的人员编号

SELECT employeeid FROM salary;

#3.查询employeeid为000001的员工的地址和电话

SELECT address,phonenumber 
FROM employees 
WHERE employeeid='000001';

#查询月收入高于2000元的员工号码

SELECT employeeid FROM salary WHERE income>2000; 

#查询1970年以后出生的员工的姓名和地址

SELECT  NAME,address 
FROM employees 
WHERE birthday>1970;

#查询所有广告部的员工的号码和姓名

SELECT employeeid,NAME FROM employees 
WHERE departmentid=
(
   SELECT departmentid 
    FROM departments 
     WHERE departmentname='广告部');

#4.查询employees表中女员工的地址和电话，制定列名为’地址’和’电话’, 其中女 性别为0

SELECT address AS 地址,phonenumber AS 电话 
FROM employees 
WHERE gender='0';

#查询员工表中男员工的姓名和出生日期，要求用中文名列

SELECT NAME AS 姓名,birthday AS 出生日期 
FROM employees 
WHERE gender='1';

#5.查询employees表中的员工姓名和性别，要求gender的值为1显示男，0显示女

SELECT NAME AS 姓名,
    CASE
        WHEN gender='1' THEN '男'
        WHEN gender='0' THEN '女'
    END AS 性别
    FROM employees;

#查询员工表中员工的姓名、地址和收入水平，2000元以下显示为低收入，
#2000~3000元显示为中等收入，3000元以上显示为高收入

SELECT NAME AS 姓名,address AS 地址,CASE WHEN income<2000 THEN "低收入"
WHEN income BETWEEN 2000 AND 3000  THEN "中等收入"
WHEN income>3000  THEN "高收入"
END AS 收入水平 
FROM employees,salary
WHERE employees.Employeeid=salary.employeeid;

#6.计算每个员工的实际收入

SELECT employeeid ,income-outcome AS 实验收入 FROM salary;

#显示每个员工的姓名和当前年龄

SELECT NAME,2022-LEFT(birthday,4) FROM employees;

#7.获得员工总数。

SELECT COUNT(*) FROM employees;

#计算salary表中员工月收入的平均数

SELECT AVG(income) FROM salary;

#获得employees表中最大的员工号码

SELECT MAX(Employeeid) FROM employees

#计算salary表中所有员工的总支出

SELECT SUM(outcome) FROM salary;

#查询广告部雇员的最高和最低实际收入

SELECT MAX(income-outcome),MIN(income-outcome);

#8.找出所有姓王的员工的部门号

SELECT departmentid FROM employees WHERE NAME LIKE '王%';

#找出所有其地址中含有“中山”的员工的号码及部门号

SELECT Employeeid,departmentid FROM employees WHERE address LIKE '%中山%'

#查找员工号码中倒数第二个数字为0的姓名、地址和学历

SELECT NAME,address,education FROM employees WHERE address LIKE '%0_'

#9.找出所有收入在2000~3000元的员工号码

SELECT employeeid FROM salary WHERE income BETWEEN 2000 AND 3000;

#找出所有在部门1或2工作的员工的号码

SELECT employeeid
FROM employees INNER JOIN departments ON
employees.departmentid=departments.departmentid
WHERE departments.departmentid=1 OR
departments.departmentid=2

#–子查询—
#（1）查找在广告部工作的员工的情况

SELECT * FROM employees 
WHERE departmentid=
(
  SELECT departmentid 
    FROM departments 
      WHERE departmentname='广告部'); 

#查找所有收入在2500元以下的员工的情况

SELECT * FROM salary WHERE income<2500

#(2)查找研发部年龄不低于市场部所有员工年龄的员工的姓名
– update employees set departmentid =‘4’ where name=‘李丽’;

SELECT NAME 
   FROM employees 
    WHERE departmentid IN
    (SELECT departmentid 
        FROM departments 
          WHERE departmentname='研发部')
   AND
      birthday<=ALL
         (SELECT birthday 
              FROM employees
                WHERE departmentid IN
                   ( SELECT departmentid 
                       FROM departments
                         WHERE departmentname ='市场部') 
         );

#用子查询的方法查找研发部比市场部所有员工收入都高的员工的姓名

SELECT NAME
FROM employees,salary,departments
WHERE income > ALL(
SELECT income
FROM employees,salary,departments
WHERE employees.employeeid=salary.employeeid
AND employees.departmentid=departments.departmentid
AND departmentname='财务部')
AND employees.employeeid= salary.employeeid
AND employees.departmentid=departments.departmentid
AND departmentname='市场部'

#(3)查找比广告部所有的雇员收入都搞的员工的姓名

SELECT NAME FROM employees WHERE employeeid IN 
(
   SELECT employeeid 
    FROM salary 
     WHERE income>ALL
   (
    SELECT income 
     FROM salary 
      WHERE employeeid IN 
    (
      SELECT employeeid 
       FROM employees 
        WHERE departmentID=
      (
        SELECT departmentid 
         FROM departments 
          WHERE departmentname='广告部'))));   

#用子查询的方法查找年龄比市场部所有员工年龄都大的员工的姓名
– --连接查询的使用–

SELECT NAME FROM employees
WHERE departmentid IN(
SELECT departmentid FROM departments
WHERE departmentname!='市场部')
AND birthday <=ALL(SELECT birthday FROM employees
WHERE departmentid IN(SELECT departmentid FROM departments
WHERE departmentname='市场部'))

#(1) 查询每个员工的情况及薪水的情况

SELECT * FROM employees,salary

#查询每个员工的情况及其工作部门的情况

SELECT * FROM employees,departments

#(2)使用内连接的方法查询名字为“王林”的员工所在的部门

SELECT departmentname
FROM departments JOIN employees 
ON departments.departmentid=employees.departmentid
WHERE employees.name='王林'

#使用内连接的方法查找不在广告部工作的所有员工的信息

SELECT * FROM departments 
INNER JOIN employees ON 
departments.departmentid=employees.departmentid
WHERE departmentname!='广告部'

#使用外连接方法查找所有员工的月收入

SELECT Income 
FROM employees  LEFT OUTER JOIN salary
ON employees.employeeid=salary.employeeid

#(3)查找广告部收入在2000元以上的员工的姓名及薪水详情

SELECT NAME,income,outcome
   FROM employees,salary,departments
   WHERE employees.Employeeid=salary.employeeid
     AND 
        employees.departmentid=departments.departmentid
     AND 
        departmentname='广告部' 
     AND 
        income>2000

#查询研发部在1966年以前出生的员工的姓名及薪水详情

SELECT NAME,income,outcome
FROM employees,salary,departments
WHERE employees.employeeid=salary.employeeid
AND employees.departmentid=departments.departmentid
AND departmentname ='研发部'
AND birthday<'1966-01-01'

– 4.group by
#(1)查找employees表中男性和女性的人数

SELECT gender,COUNT(gender) FROM employees GROUP BY gender

#按部门列出在该部门工作的员工人数

SELECT departmentid,COUNT(departmentid) 人数
FROM departments
GROUP BY departmentid

#按员工的学历分组，列出本科、大专和硕士的人数

SELECT education,COUNT(education) 人数
FROM employees
GROUP BY education

#(2)查找员工数超过2人的部门名称和员工数量

SELECT departmentid ,COUNT(departmentid ),
 CASE 
	WHEN departmentid = '1' THEN '财务部'
	WHEN departmentid = '2' THEN '人力资源部'
	WHEN departmentid = '3' THEN '经理办公室'
	WHEN departmentid = '4' THEN '研发部'
	WHEN departmentid = '5' THEN '市场部'
	WHEN departmentid = '6' THEN '财务部'
	END 部门
	 FROM employees 
GROUP BY departmentid
HAVING COUNT(departmentid )>2;

#按员工的工作年份分组，统计各个工作年份的人数，如工作1年的多少人，工作2年的多少人

SELECT workyear,COUNT(workyear) 人数
FROM employees
GROUP BY workyear

#(3)将employees表中的员工号码由大到小排列

SELECT *
FROM employees
ORDER BY employeeid DESC

#将员工信息按出生日期从小到大排列

SELECT *
FROM employees
ORDER BY birthday DESC

#在order by子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列

SELECT NAME,gender,workyear
FROM employees,salary
WHERE employees.employeeid=salary.employeeid
ORDER BY (income-outcome) DESC

#(4)返回employees表中的前5位员工的信息

SELECT *
FROM employees LIMIT 5

#返回employees表中从第3位员工开始的5个员工的信息

SELECT * FROM employees
LIMIT 2,3