Mysql公司表5月11作业

-- (1)删除数据库
drop database if exists Company;
-- (2)创建数据库
create database if not exists Company;
-- (3)使用数据库
use Company;

drop database if exists Rankinfo;
create table Rankinfo(
RankID int not null primary key AUTO_INCREMENT comment '职称编号',
RankName nvarchar(20) not null comment '职称名称',
RankLevel nvarchar(2) not null comment'职称等级'
);

select*from Rankinfo;
insert into Rankinfo values(null,'总经理', 'A');
insert into Rankinfo values(null,'总监', 'B');
insert into Rankinfo values(null, '经理', 'C');
insert into Rankinfo values(null, '主管', 'E');
insert into Rankinfo values(null, '组长', 'F');
insert into Rankinfo values(null, '行政', 'G');
insert into Rankinfo values(null, '技术员', 'H');
insert into Rankinfo values(null, '工程师', 'H');

drop database if exists Employee;
create table Employee(
EmployeelD int not null primary key AUTO_INCREMENT comment'员工编号',
Name nvarchar(20) not null comment'员工姓名',
Sex nvarchar(2) not null comment'性别',
Age int not null comment'年龄',
RankID int not null comment'职称编号',
Salary Decimal(12,2) not null comment'月基本工资',
Address nvarchar(50) not null comment'工作地',
Count nvarchar(20) not null comment'中国'
);

alter table Employee
add foreign key(RankID) references Rankinfo(RankID);
Alter table Employee modify column Count nvarchar(20) default '中国';

select*from Employee;
insert into Employee
values(null,'陈小芳', '男', '29', '1', '18000.00', '湖北', default),
      (null, '冯哒哒', '女', '21', '7', '52000.00' ,'南京', default),
	  (null, '龚小旋', '女', '26', '3', '8000.00' ,'北京', default),
	  (null, '海小洋', '男', '30', '1', '21000.00' ,'湖北', default),
	  (null, '贺小涵', '男', '26', '5', '5500.00' ,'南京', default),
	  (null, '王二狗', '女', '22', '5', '5600.00' ,'南京', default),
      (null, '王小胜', '男', '28', '2', '7000.00' ,'湖北', default),
	  (null, '徐娇娇', '女', '25', '4', '7500.00' ,'湖北', default),
	  (null, '颜小易', '男', '27', '4', '7000.00' ,'湖北', default),
	  (null, '易小飞', '男', '30', '2', '15000.00' ,'湖北', default),
	  (null, '余小兰', '女', '23', '7', '2500.00' ,'湖北', default),
      (null, '张伟', '男', '18', '7', '0','深圳', default);
      
SET SQL_SAFE_UPDATES = 0;
set Foreign_key_checks=0;
-- 2. 因工作需要,将贺小涵的工作地调到南京
UPDATE Employee SET Address = '南京' WHERE Name = '贺小涵';
-- 3. 删除王小林员工的所有信息
DELETE FROM Employee WHERE Name = '王小林';
-- 4. 删除职级编号为 6 的数据
DELETE FROM RankInfo WHERE RankID = 6;
-- 5. 分别查询两张表中所有数据
SELECT * FROM RankInfo;
SELECT * FROM Employee;
-- 6. 查询全部员工的职级编号，要求职级编号唯一(没有重复)
SELECT DISTINCT RankID FROM Employee;
-- 7. 查询所有员工的姓名、月基本工资、以及增长 10%之后的月基本工资
SELECT Name, Salary, Salary * 1.1 AS NewSalary FROM Employee;
-- 8. 查询级别编号为 3 的职级信息
SELECT * FROM Employee WHERE RankID = '3';
-- 9. 查询基本工资不低于 8000 的员工信息
SELECT * FROM Employee WHERE Salary >= 8000;
-- 10.查询职级名称不为“工程师”的所有职级信息
SELECT * FROM RankInfo WHERE RankName != '工程师';
-- 11.查询年龄大于 25 岁，且月基本工资高于 6000 的员工姓名/性别/年龄及基本工资
SELECT Name, Sex, Age, Salary FROM Employee WHERE Age > 25 AND Salary > 6000;
-- 12.查询月基本工资高于 7000 或者职级名称为“技术员”的员工信息并以中文字段显示
SELECT * FROM Employee WHERE Salary > 7000 OR RankID = (SELECT RankID FROM RankInfo WHERE RankName = '技术员');
-- 13.查询所有职级信息，按照职级编号升序排列
SELECT * FROM RankInfo ORDER BY RankID ASC;
-- 14.查询全部员工的员工号、姓名、职级编号和月基本工资信息，按月基本工资降序排列
SELECT Name, RankID, Salary FROM Employee ORDER BY Salary DESC;
-- 15.查询所有员工，要求按职级编号升序排列，若职级编号相同，按照月基本工资降序排列
SELECT * FROM Employee ORDER BY RankID ASC, Salary DESC;
-- 16.查询月基本工资最高的 5 名员工
SELECT * FROM Employee ORDER BY Salary DESC LIMIT 5;
-- 17.按月基本工资降序排列，显示前 20%的员工
-- SELECT * FROM Employee ORDER BY Salary DESC LIMIT (SELECT COUNT(*) * 0.2 FROM Employee);
-- 18.查询员工姓“王”的员工信息
SELECT * FROM Employee WHERE Name LIKE '王%';
-- 19.查询职员的工作地点在武汉或南京的职员信息
SELECT * FROM Employee WHERE Address IN ('武汉', '南京');
-- 20.查询月基本工资在 5000 - 10000 之间的职员信息
SELECT * FROM Employee WHERE Salary BETWEEN 5000 AND 10000;
-- 21.查询年龄在“20”到“25”岁之间的员工信息
SELECT * FROM Employee WHERE Age BETWEEN 20 AND 25;
-- 22.计算职员月基本工资总额
SELECT SUM(Salary) AS 基本工资总额 FROM Employee;
-- 23.查询最高月基本工资公司职员
SELECT MAX(Salary) AS 最高月基本工资 FROM Employee;
-- 24.查询公司职员的最低月基本工资
SELECT MIN(Salary) AS 最低月基本工资 FROM Employee;
-- 25.查询公司年龄最大的职员
SELECT * FROM Employee ORDER BY Age DESC LIMIT 1;
-- 26.查询公司职员月基本工资的平均值
SELECT AVG(Salary) AS 平均基本工资 FROM Employee;
-- 27.统计公司职员的总人数
SELECT COUNT(*) AS 总人数 FROM Employee;
-- 28.统计统计年龄大于 28 的公司职员的人数
SELECT COUNT(*) AS 人数 FROM Employee WHERE Age > 28;
-- 29.统计员工月基本工资超过 10000 元的员工数
SELECT COUNT(*) AS 人数 FROM Employee WHERE Salary > 10000;
-- 30.查询公司职员总人数、平均月基本工资、最高月基本工资和最低月基本工资
SELECT 
    COUNT(*) AS 总人数,
    AVG(Salary) AS 平均月基本工资,
    MAX(Salary) AS 最高月基本工资,
    MIN(Salary) AS 最低月基本工资
FROM Employee;
-- 31.查询职级为 3 的职员人数、平均月基本公司、最高月基本工资和最低月基本工资
SELECT 
    COUNT(*) AS 人数,
    AVG(Salary) AS 平均月基本工资,
    MAX(Salary) AS 最高月基本工资,
    MIN(Salary) AS 最低月基本工资
FROM 
    Employee
WHERE 
    RankID = 3;
-- 32.按职级分组计算员工数、月基本工资平均值和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均工资升序排列
SELECT 
    RankID,
    COUNT(*) AS 人数,
    AVG(Salary) AS 平均月基本工资,
    SUM(Salary) AS 月基本工资总额
FROM 
    Employee
GROUP BY 
    RankID
ORDER BY 
    月基本工资总额 ASC, 
    平均月基本工资 ASC;
-- 33.查询每个职级的员工的最高月基本工资，按职级号升序排列
SELECT 
    RankID, 
    MAX(Salary) AS 最高月基本工资
FROM Employee
GROUP BY RankID
ORDER BY RankID ASC;
-- 34.获取不同职级的员工在不同城市工作的人数，按员工职级升序排列
SELECT 
    RankID, 
    Address, 
    COUNT(*) AS 人数
FROM Employee
GROUP BY RankID, Address
ORDER BY RankID ASC;
-- 35.获取在不同城市工作的男女员工人数，按性别升序排列，如果性别相同，则按员工人数升序排列
SELECT 
    Sex, 
    Address, 
    COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY Sex, Address
ORDER BY Sex ASC, EmployeeCount ASC;
-- 36.查询每个职级的员工最高月基本工资大于 8000 的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列
SELECT 
    RankID, 
    MAX(Salary) AS 最高月基本工资
FROM Employee
GROUP BY RankID
HAVING MAX(Salary) > 8000
ORDER BY RankID ASC;
-- 37.用内联接查询员工姓名、职级号、职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
JOIN RankInfo r ON e.RankID = r.RankID;
-- 38.使用左外联接查询员工姓名、职级号和职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
LEFT JOIN RankInfo r ON e.RankID = r.RankID;
-- 39.使用右外联接查询员工姓名、职级号和职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
RIGHT JOIN RankInfo r ON e.RankID = r.RankID;

SET SQL_SAFE_UPDATES = 0;
set Foreign_key_checks=0;
自增数据查询报错
— 14.查询全部员工的员工号、姓名、职级编号和月基本工资信息，按月基本工资降序排列（去掉EmployeeID）
SELECT Name, RankID, Salary FROM Employee ORDER BY Salary DESC;