MySQL

最新推荐文章于 2023-07-22 18:14:59 发布

bug女孩的每一天

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量196

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_56159271/article/details/127939620

版权

1.创建一个表 bigdata 并切换
- create database bigdata;
- use bigdata;

--部门表

// dept部门表(deptno部门编号/dname部门名称/loc地点)
create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

// 往里插入数据

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

--工资等级表

// salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)
create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);
// 往里插入数据

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

--员工表

// emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
工资 ＝ 薪金 ＋ 佣金

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

//插入相关数据

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

1.查询出部门编号为30的所有员工的编号和姓名

table：emp

查什么：

1.维度：group by

2.指标: 聚合函数

3.普普通通的字段：编号和姓名

怎么查：

where：

部门编号为30

mysql> select
    -> empno,ename,deptno
    -> from emp
    -> where deptno=30;
+-------+----------+--------+
| empno | ename    | deptno |
+-------+----------+--------+
|  7499 | ALLEN    |     30 |
|  7521 | WARD     |     30 |
|  7654 | MARTIN   |     30 |
|  7698 | BLAKE    |     30 |
|  7844 | TURNER   |     30 |
|  7900 | lebulang |     30 |
+-------+----------+--------+
6 rows in set (0.02 sec)

2.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

// 方法一

mysql> select
    -> *
    -> from emp
    -> where deptno=10 and job='MANAGER'
    -> union
    -> select
    -> *
    -> from emp
    -> where deptno=20 and job='SALESMAN';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

// 方法二

mysql> select
    -> *
    -> from emp
    -> where
    -> (deptno=10 and job='MANAGER') or (deptno=20 and job='SALESMAN');
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

3.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。

指标：工资= sal+ comm

排序：工资降序排序、入职日期升序排序

//方法一

mysql> select
    -> (sal+comm_alias) as earning,
    -> hiredate
    -> from
    -> (
    -> select
    -> sal,
    -> ifnull(comm,0) as comm_alias,
    -> hiredate
    -> from emp
    -> ) as a
    -> order by earning desc ,hiredate asc;
+---------+---------------------+
| earning | hiredate            |
+---------+---------------------+
| 5000.00 | 1981-11-17 00:00:00 |
| 3000.00 | 1981-12-03 00:00:00 |
| 3000.00 | 1982-12-09 00:00:00 |
| 2975.00 | 1981-04-02 00:00:00 |
| 2850.00 | 1981-05-01 00:00:00 |
| 2650.00 | 1981-09-28 00:00:00 |
| 2450.00 | 1981-06-09 00:00:00 |
| 1900.00 | 1981-02-20 00:00:00 |
| 1750.00 | 1981-02-22 00:00:00 |
| 1500.00 | 1981-09-08 00:00:00 |
| 1300.00 | 1982-01-23 00:00:00 |
| 1100.00 | 1983-01-12 00:00:00 |
|  950.00 | 1981-12-03 00:00:00 |
|  800.00 | 1980-12-17 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
+---------+---------------------+
18 rows in set (0.00 sec)

// 方法二

mysql> select
    -> ifnull((sal+comm),sal) as earning,
    -> hiredate
    -> from emp
    -> order by earning desc ,hiredate asc;
+---------+---------------------+
| earning | hiredate            |
+---------+---------------------+
| 5000.00 | 1981-11-17 00:00:00 |
| 3000.00 | 1981-12-03 00:00:00 |
| 3000.00 | 1982-12-09 00:00:00 |
| 2975.00 | 1981-04-02 00:00:00 |
| 2850.00 | 1981-05-01 00:00:00 |
| 2650.00 | 1981-09-28 00:00:00 |
| 2450.00 | 1981-06-09 00:00:00 |
| 1900.00 | 1981-02-20 00:00:00 |
| 1750.00 | 1981-02-22 00:00:00 |
| 1500.00 | 1981-09-08 00:00:00 |
| 1300.00 | 1982-01-23 00:00:00 |
| 1100.00 | 1983-01-12 00:00:00 |
|  950.00 | 1981-12-03 00:00:00 |
|  800.00 | 1980-12-17 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
| -200.00 | 2022-10-01 00:00:00 |
+---------+---------------------+
18 rows in set (0.01 sec)

4.列出薪金大于1500的各种工作及从事此工作的员工人数。

emp

维度：工作

指标：员工人数

where : 薪金大于1500

mysql> select
    -> job,
    -> count(2) as cnt
    -> from emp
    -> where
    -> sal > 1500
    -> group by job;
+-----------+-----+
| job       | cnt |
+-----------+-----+
| ANALYST   |   2 |
| MANAGER   |   3 |
| PRESIDENT |   1 |
| SALESMAN  |   1 |
+-----------+-----+
4 rows in set (0.01 sec)

5.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

colunm: ename

where

销售部 deptno没有

dept : deptno ; deptname

主表： emp : 业务主线

从表： dept : 辅助查询业务

// 方法一

mysql> select  ename
    -> from emp left join dept
    -> on emp.deptno = dept.deptno
    -> where dname='SALES';
+----------+
| ename    |
+----------+
| ALLEN    |
| WARD     |
| MARTIN   |
| BLAKE    |
| TURNER   |
| lebulang |
+----------+
6 rows in set (0.00 sec)

// 方法二

mysql> select ename
    -> from emp
    -> where deptno in(
    -> select deptno
    -> from dept
    -> where dname='SALES'
    -> );
+----------+
| ename    |
+----------+
| ALLEN    |
| WARD     |
| MARTIN   |
| BLAKE    |
| TURNER   |
| lebulang |
+----------+
6 rows in set (0.01 sec)

6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L __

mysql> select ename
    -> from emp
    -> where ename like "S%" OR ename like "%S" OR  ename like "%S%" OR ename like "_L%";
+-------+
| ename |
+-------+
| SMITH |
| ALLEN |
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| ADAMS |
+-------+
7 rows in set (0.01 sec)

8.列出薪金高于公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级

table:emp、dept
维度：没有
指标：员工号, 员工姓名，

部门名称 join dept dname on deptno

上级领导编号 / 名字 =》join emp => ename on mgr=empno

工资 ifnull

工资等级工资 vs salgrade ? => grade

where :薪金高于公司平均薪金

mysql> select
    -> dname,
    -> c.ename as mgr_name,
    -> earning,
    -> a.empno,
    -> a.ename,
    -> case
    -> when earning> 700 and earning<=1200 then 1
    -> when earning>1200 and earning<=1400 then 2
    -> when earning>1400 and earning<=2000 then 3
    -> when earning>2000 and earning<=3000 then 4
    -> when earning>3000 and earning<=9999 then 5
    -> end  as grade
    -> from (
    -> select
    -> empno,
    -> ename,
    -> deptno,
    -> mgr,
    -> ifnull((sal+comm),sal) as earning
    -> from emp
    -> where
    -> 
Display all 803 possibilities? (y or n) 
    -> sal > (
    -> 
Display all 803 possibilities? (y or n) 
    -> 
Display all 803 possibilities? (y or n) 
    -> select
    -> 
Display all 803 possibilities? (y or n) 
    -> 
Display all 803 possibilities? (y or n) 
    -> avg(sal) as avg_sal
    -> 
Display all 803 possibilities? (y or n) 
    -> 
Display all 803 possibilities? (y or n) 
    -> from emp
    -> 
Display all 803 possibilities? (y or n) 
    -> )
    -> ) as a left join
    -> (
    -> select
    -> deptno,
    -> dname
    -> from dept
    -> ) as b
    -> on a.deptno=b.deptno
    -> left join
    -> (
    -> select
    -> empno,
    -> ename
    -> from emp
    -> ) as c
    -> on a.mgr=c.empno;
+------------+----------+---------+-------+-------+-------+
| dname      | mgr_name | earning | empno | ename | grade |
+------------+----------+---------+-------+-------+-------+
| RESEARCH   | JONES    | 3000.00 |  7788 | SCOTT |     4 |
| RESEARCH   | JONES    | 3000.00 |  7902 | FORD  |     4 |
| SALES      | BLAKE    | 1900.00 |  7499 | ALLEN |     3 |
| ACCOUNTING | KING     | 2450.00 |  7782 | CLARK |     4 |
| RESEARCH   | KING     | 2975.00 |  7566 | JONES |     4 |
| SALES      | KING     | 2850.00 |  7698 | BLAKE |     4 |
| ACCOUNTING | NULL     | 5000.00 |  7839 | KING  |     5 |
+------------+----------+---------+-------+-------+-------+
7 rows in set (0.01 sec)

//--------------

select
dname,
c.ename as mgr_name,
earning,
a.empno,
a.ename,
-- 薪资等级
case
	when earning> 700 and earning<=1200 then 1
	when earning>1200 and earning<=1400 then 2
	when earning>1400 and earning<=2000 then 3
	when earning>2000 and earning<=3000 then 4
	when earning>3000 and earning<=9999 then 5
end  as grade
from (
	select
	empno,
	ename,
	deptno,
	mgr,
	ifnull((sal+comm),sal) as earning
	from emp
	where
		sal > (
			select
			avg(sal) as avg_sal
			from emp
		)
) as a left join
(
	select
	deptno,
	dname
	from dept
) as b
on a.deptno=b.deptno
left join
(
	select
	empno,
	ename
	from emp
) as c
on a.mgr=c.empno;

9.列出薪金高于在各自部门工作的员工的平均薪金的员工姓名和薪金、部门名称。

table：emp
维度：部门
指标：平均薪金 , 员工姓名和薪金、部门名称

where

薪金高于各自部门工作的员工的平均薪金

-- 1.各自部门工作的员工的平均薪金
select
deptno,
avg(sal) as sal_avg
from emp
group by
deptno
-- emp :  员工姓名和薪金、部门名称 平均薪金
		部门名称 => join dept => dname

mysql> select
    ->   ename,
    ->   sal,
    ->   a.deptno,
    ->   dname,
    ->   round(sal_avg,2) as sal_avg_alias
    -> from
    ->   (
    ->     select
    ->       ename,
    ->       sal,
    ->       a.deptno,
    ->       b.dname
    ->     from
    ->       (
    ->         -- 主表
    ->         select
    ->           ename,
    ->           sal,
    ->           deptno
    ->         from
    ->           emp
    ->       ) as a
    ->       left join dept b on a.deptno = b.deptno
    ->   ) as a
    ->   left join (
    ->     select
    ->       deptno,
    ->       avg(sal) as sal_avg
    ->     from
    ->       emp
    ->     group by
    ->       deptno
    ->   ) as b on a.deptno = b.deptno
    ->   where
    ->   sal >  round(sal_avg,2);
+-------+---------+--------+------------+---------------+
| ename | sal     | deptno | dname      | sal_avg_alias |
+-------+---------+--------+------------+---------------+
| KING  | 5000.00 |     10 | ACCOUNTING |       2916.67 |
| JONES | 2975.00 |     20 | RESEARCH   |       2175.00 |
| SCOTT | 3000.00 |     20 | RESEARCH   |       2175.00 |
| FORD  | 3000.00 |     20 | RESEARCH   |       2175.00 |
| ALLEN | 1600.00 |     30 | SALES      |       1566.67 |
| BLAKE | 2850.00 |     30 | SALES      |       1566.67 |
+-------+---------+--------+------------+---------------+
6 rows in set (0.01 sec)