SQL语法练习2_[sql]insert into `employee`(`number`,`name`,`hired-CSDN博客

本文链接：https://blog.csdn.net/weixin_41796772/article/details/129508817

本文展示了如何使用SQL语句创建名为shs_employees的员工表，包括字段如编号、姓名、入职日期、薪水等，并插入多条员工数据。接着，提出了多个查询示例，如按薪资排序、排除特定部门、查找特定条件的员工、计算部门平均薪资等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.创建表

DROP TABLE IF EXISTS shs_employees;
CREATE TABLE shs_employees(
number int DEFAULT NULL,
name  VARCHAR(255) DEFAULT NULL,
hiredate DATE DEFAULT NULL,
salary double DEFAULT NULL,
bonus double DEFAULT NULL,
department VARCHAR(10)
)default charset = utf8;

2.插入数据

INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1001', '张曦', '2020-06-01', '18000', '3000', '研发部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1002', '刘军', '2019-06-01', '16000', '1000', '研发部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1003', '张六', '2018-02-10', '6000', '500', '财务部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1004', '王麻嘛', '2022-02-10', '27000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1005', '刘明', '2013-02-10', '6000', '500', 'UI部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1006', '王丽子', '2022-02-10', '6000', '500', 'UI部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1007', '刘四', '2022-02-25', '5000', '100', '财务部门');
INSERT INTO shs_employees (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1008', '余宇军', '2022-02-25', '10000', '50', '研发部门');

– 1. 根据员工的薪资升序或者降序排列
– 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息
– 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列
– 4.查询每位员工综合年薪根据年薪降序排列
– 5.求每个不同部门的平均薪水
– 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
– 7.求每个部门中最高薪水
– 8.求每个部门有多少员工
– 9.查询员工最高薪资和最低薪资员工信息
– 10.查询员工最高薪资和最低薪资的差距

-- 1.根据员工的薪资升序或者降序排列
SELECT * FROM shs_employees ORDER BY salary;
SELECT * FROM shs_employees ORDER BY salary DESC;

-- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息.
SELECT salary FROM shs_employees ORDER BY salary;

-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列
SELECT * FROM shs_employees WHERE name NOT LIKE '%麻%' && salary>=15000 ORDER BY salary;

-- 4.查询每位员工综合年薪 根据年薪降序排列
SELECT * FROM shs_employees  ORDER BY bonus DESC;

-- 5.求每个不同部门的平均薪水
SELECT department,AVG(salary) as 薪水 FROM shs_employees GROUP BY department;

-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
SELECT department,AVG(salary) as 薪水 FROM shs_employees GROUP BY department HAVING AVG(salary)>13000;

-- 7.求每个部门中最高薪水
SELECT department,MAX(salary) FROM shs_employees GROUP BY department;

-- 8.求每个部门有多少员工
SELECT department,COUNT(`name`) FROM shs_employees GROUP BY department;

-- 9.查询员工最高薪资和最低薪资员工信息
SELECT `name`,MAX(salary) FROM shs_employees;
SELECT `name`,MIN(salary) FROM shs_employees;

-- 10.查询员工最高薪资和最低薪资的差距
SELECT MAX(salary)-MIN(salary) FROM shs_employees;