记录一次MySQL练习作业

1. 查询员工表所有数据，并说明使用*的缺点

SELECT * FROM employees;
使用*会降低代码可读性，会使代码更难以维护。当表格结构发生更改时，查询可能会返回不再需要的列，导致代码错误。

2. 查询所员工的 email 全名,公司 email 统一以 @qq.com 结尾

SELECT employee_id, CONCAT(email,"@qq.com") FROM employees;

3. 打印公司里所有的 manager_id(去除重复数据)

SELECT DISTINCT manager_id FROM employee;

4. 按照入职日期由新到旧排列员工信息

SELECT * FROM employees ORDER BY hiredate DESC;

5. 按照工资由高到低的顺序显示员工信息

SELECT * FROM employees ORDER BY salary DESC;

6. 查询职位(JOB_ID)为'ST_CLERK'的员工的工资

SELECT salary FROM employees WHERE job_id = 'ST_CLERK';

7. 查询 50 号部门的员工姓名以及全年工资.

SELECT first_name, last_name, salary * 12 AS annual_salary FROM employees WHERE department_id = 50;

8. 查询 80 号部门工资大于 7000 的员工的全名与工资

SELECT CONCAT(first_name, ' ', last_name) AS full_name, salary FROM employees WHERE department_id = 80 AND salary > 7000;

9. 查询工资高于 7000 但是没有提成的所有员工.

SELECT * FROM employees WHERE salary > 7000 AND commission_pct IS NULL;

10. 查询入职日期在 1997-5-1 到 1997-12-31 之间的所有员工信息

SELECT * FROM employees WHERE DATE(hiredate) BETWEEN '1997-05-01' AND '1997-12-31';

11. 显示姓名中没有'L'字的员工的详细信息

SELECT * FROM employees WHERE first_name NOT LIKE '%L%' AND last_name NOT LIKE '%L%';

12. 查询电话号码以 8 开头的所有员工信息.

SELECT * FROM employees WHERE phone_number LIKE '8%';

13. 查询 80 号部门中 last_name 以 n 结尾的所有员工信息

SELECT * FROM employees WHERE department_id = 80 AND last_name LIKE '%n';

14. 查询所有 last_name 由四个以上字母组成的员工信息

SELECT * FROM employees WHERE LENGTH(last_name) >= 4;

15. 查询 first_name 中包含"na"的员工信息.

SELECT * FROM employees WHERE first_name LIKE '%na%';

16. 显示公司里所有员工的工资级别，使用 case when

SELECT CONCAT(first_name,' ',last_name)AS 姓名,salary,
   CASE
      WHEN salary <= 5000 THEN 'A'
      WHEN salary >= 5001 AND salary <= 8000 THEN 'B'
      WHEN salary >= 8001 AND salary <= 15000 THEN 'C'
      ELSE 'D'
   END AS salary_level
FROM employees;

17. 根据入职时间打印出员工级别

SELECT CONCAT(first_name,' ',last_name)AS 姓名, hiredate,
   CASE
      WHEN YEAR(hiredate) <= '1985' THEN '资深员工'
      WHEN YEAR(hiredate) BETWEEN '1986' AND '1990' THEN '普通员工'
      ELSE '新员工'
   END AS employee_level
FROM employees;

函数练习

1. 请打印出 1997 年入职的员工(考察知识点:单行函数)

SELECT * FROM employees WHERE DATE_FORMAT(hiredate, '%Y') = '1997';

2. 把 hiredate 列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)

SELECT * FROM employees WHERE MONTH(hiredate) = MONTH(CURRENT_DATE());

3. 查询出员工表中最高工资，最低工资，和平均工资

SELECT MAX(salary) AS 最高工资, MIN(salary) AS 最低工资, AVG(salary) AS 平均工资 FROM employees;

数据库数据表的创建

/*Table structure for table `employees` */
DROP TABLE IF EXISTS employees;
CREATE TABLE `employees` (
    `employee_id` INT(6) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(20) NULL DEFAULT NULL COLLATE 'gb2312_chinese_ci',
    `last_name` VARCHAR(25) NULL DEFAULT NULL COLLATE 'gb2312_chinese_ci',
    `email` VARCHAR(25) NULL DEFAULT NULL COLLATE 'gb2312_chinese_ci',
    `phone_number` VARCHAR(20) NULL DEFAULT NULL COLLATE 'gb2312_chinese_ci',
    `job_id` VARCHAR(10) NULL DEFAULT NULL COLLATE 'gb2312_chinese_ci',
    `salary` DOUBLE(10,2) NULL DEFAULT NULL,
    `commission_pct` DOUBLE(4,2) NULL DEFAULT NULL,
    `manager_id` INT(6) NULL DEFAULT NULL,
    `department_id` INT(4) NULL DEFAULT NULL,
    `hiredate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`employee_id`) USING BTREE,
    INDEX `dept_id_fk` (`department_id`) USING BTREE,
    INDEX `job_id_fk` (`job_id`) USING BTREE
)
COLLATE='gb2312_chinese_ci' ENGINE=InnoDB AUTO_INCREMENT=207;