SQL基础二

最新推荐文章于 2023-08-19 20:18:19 发布

钻石大门

最新推荐文章于 2023-08-19 20:18:19 发布

阅读量388

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_41158277/article/details/94310795

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

前期准备：

--部门表
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部门编号)
工资 ＝ 薪金 ＋ 佣金

1.表自己跟自己连接

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.求员工表所有人的薪水和

select 
sum(sal) as "total salary"
from emp;

total salary|
------------|
    29025.00|

2.求员工表的各个部门的薪水和

select 
deptno,
sum(sal) as salary
from emp 
group by deptno;

deptno|salary  |
------|--------|
    10| 8750.00|
    20|10875.00|
    30| 9400.00|

3.求员工表的各个部门的薪水和、员工数、平均薪资

select 
deptno,
sum(sal) as "salary summary",
count(ename) as "people count",
sum(sal)/count(ename) as "mean salary",
avg(sal) as "avg salary"
from emp 
group by deptno;

deptno|salary summary|people count|mean salary|avg salary |
------|--------------|------------|-----------|-----------|
    10|       8750.00|           3|2916.666667|2916.666667|
    20|      10875.00|           5|2175.000000|2175.000000|
    30|       9400.00|           6|1566.666667|1566.666667|

4.找薪水和大于9000的是哪个部门

方法一、用having过滤
select 
deptno,
sum(sal) as "salary summary"
from emp
group by deptno
having sum(sal)>9000;

deptno|salary summary|
------|--------------|
    20|      10875.00|
    30|       9400.00|

方法二、 用子查询
select *
from
(select 
deptno,
sum(sal) as ssal
from emp
group by deptno) as t where t.ssal>9000;

deptno|ssal    |
------|--------|
    20|10875.00|
    30| 9400.00|

5.排序order by

Select * from salgrade order by hisal;
默认顺序是从小到大（asc），后面加上desc就是从大到小排序。
grade|losal|hisal|
-----|-----|-----|
    1|  700| 1200|
    2| 1201| 1400|
    3| 1401| 2000|
    4| 2001| 3000|
    5| 3001| 9999|


Select * from salgrade order by hisal, losal desc;
多个条件排序，用逗号分隔
grade|losal|hisal|
-----|-----|-----|
    1|  700| 1200|
    2| 1201| 1400|
    3| 1401| 2000|
    4| 2001| 3000|
    5| 3001| 9999|

6.聚合group by

聚合函数sum求合，count求数量，avg求平均，max求最大值，min求最小值
Select deptno, sum(sal) from emp group by deptno;
deptno|sum(sal)|
------|--------|
    10| 8750.00|
    20|10875.00|
    30| 9400.00|

聚合语法，一般group by和聚合函数正常来说是放在一起使用
Group by 字段必须出现select 字段，select后面的字段一定要和同句语句中group by后面的字段对等，如select 后面是部门，group by 后面也要是部门。处理聚合函数的字段，如sum（sal）这个求工资和的字段无需和group by后面对等。
As 别名，相当于重新对字段命名
过滤having，相当于where条件，只是在这里用having，having一定是跟在group by 后面。

数量，最大值等比较特殊，后面不需要用group by
如：select count(*) from emp;
Select max(sal) from emp;

7.综合运用

select 
deptno,
job,
sum(sal) as ssum,
count(sal) as pcount
from emp 
where deptno !=10                 
group by deptno,job               
order by deptno,ssum desc         
limit 2;           

deptno|job    |ssum   |pcount|
------|-------|-------|------|
    20|ANALYST|6000.00|     2|
    20|MANAGER|2975.00|     1|

8.连接：JOIN的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。

数据准备
create table testa(aid int,aname varchar(100),address varchar(100));
create table testb(bid int,bname varchar(100),age int);
create table testc(cid int,sal int);
insert into testa values(1,'x1','sh');
insert into testa values(2,'x2','hz');
insert into testa values(3,'x3',null);
insert into testa values(4,'x4','bj');
insert into testa values(5,'x5','gz');

select * from testb
insert into testb values(1,'x1',10);
insert into testb values(2,'x2',11);
insert into testb values(3,'x3',12);
insert into testb values(4,'x4',16);

insert into testb values(7,'x7',19);
insert into testb values(8,'x8',22);
insert into testb values(9,'x9',24);
insert into testb values(10,'x10',44);

内连接：INNER JOIN
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER)四种写法。

select 
a.*,
b.*
from testa as a 
inner join testb as b  on a.aid=b.bid;
aid|aname|address|bid|bname|age|
---|-----|-------|---|-----|---|
  1|x1   |sh     |  1|x1   | 10|
  2|x2   |hz     |  2|x2   | 11|
  3|x3   |       |  3|x3   | 12|
  4|x4   |bj     |  4|x4   | 16|

左连接：LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录

select 
a.*,
b.*
from testa as a 
left join testb as b  on a.aid=b.bid;

aid|aname|address|bid|bname|age|
---|-----|-------|---|-----|---|
  1|x1   |sh     |  1|x1   | 10|
  2|x2   |hz     |  2|x2   | 11|
  3|x3   |       |  3|x3   | 12|
  4|x4   |bj     |  4|x4   | 16|
  5|x5   |gz     |   |     |   |

右连接：RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录

select 
a.*,
b.*
from testa as a 
right join testb as b  on a.aid=b.bid;

aid|aname|address|bid|bname|age|
---|-----|-------|---|-----|---|
  1|x1   |sh     |  1|x1   | 10|
  2|x2   |hz     |  2|x2   | 11|
  3|x3   |       |  3|x3   | 12|
  4|x4   |bj     |  4|x4   | 16|
   |     |       |  7|x7   | 19|
   |     |       |  8|x8   | 22|
   |     |       |  9|x9   | 24|
   |     |       | 10|x10  | 44|

SQL UNION 操作符合并两个或多个 SELECT 语句的结果
union all 结果不去重复
union去重复
注意：
1，名称是第一张表决定
2，生产尽量不用*
3，正常来说，对应字段类型保持一致，mysql发展到今时今日不同字段类型也可以识别出。

select 
a.*,
b.*
from testa as a 
left join testb as b  on a.aid=b.bid
union
select 
a.*,
b.*
from testa as a 
right join testb as b  on a.aid=b.bid;

aid|aname|address|bid|bname|age|
---|-----|-------|---|-----|---|
  1|x1   |sh     |  1|x1   | 10|
  2|x2   |hz     |  2|x2   | 11|
  3|x3   |       |  3|x3   | 12|
  4|x4   |bj     |  4|x4   | 16|
  5|x5   |gz     |   |     |   |
   |     |       |  7|x7   | 19|
   |     |       |  8|x8   | 22|
   |     |       |  9|x9   | 24|
   |     |       | 10|x10  | 44|

面试题：

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

select empno, empno, deptno from emp where deptno = 30;

empno|empno|deptno|
-----|-----|------|
 7499| 7499|    30|
 7521| 7521|    30|
 7654| 7654|    30|
 7698| 7698|    30|
 7844| 7844|    30|
 7900| 7900|    30|

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

select emp.* from emp where deptno = 10 and job = 'MANAGER'
union all 
select emp.* 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|    |    10|

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

select emp.* from emp order by (sal+ifNull(comm,0)) desc, hiredate asc;
empno|ename |job      |mgr |hiredate           |sal    |comm   |deptno|
-----|------|---------|----|-------------------|-------|-------|------|
 7839|KING  |PRESIDENT|    |1981-11-17 00:00:00|5000.00|       |    10|
 7902|FORD  |ANALYST  |7566|1981-12-03 00:00:00|3000.00|       |    20|
 7788|SCOTT |ANALYST  |7566|1982-12-09 00:00:00|3000.00|       |    20|
 7566|JONES |MANAGER  |7839|1981-04-02 00:00:00|2975.00|       |    20|
 7698|BLAKE |MANAGER  |7839|1981-05-01 00:00:00|2850.00|       |    30|
 7654|MARTIN|SALESMAN |7698|1981-09-28 00:00:00|1250.00|1400.00|    30|
 7782|CLARK |MANAGER  |7839|1981-06-09 00:00:00|2450.00|       |    10|
 7499|ALLEN |SALESMAN |7698|1981-02-20 00:00:00|1600.00| 300.00|    30|
 7521|WARD  |SALESMAN |7698|1981-02-22 00:00:00|1250.00| 500.00|    30|
 7844|TURNER|SALESMAN |7698|1981-09-08 00:00:00|1500.00|   0.00|    30|
 7934|MILLER|CLERK    |7782|1982-01-23 00:00:00|1300.00|       |    10|
 7876|ADAMS |CLERK    |7788|1983-01-12 00:00:00|1100.00|       |    20|
 7900|JAMES |CLERK    |7698|1981-12-03 00:00:00| 950.00|       |    30|
 7369|SMITH |CLERK    |7902|1980-12-17 00:00:00| 800.00|       |    20|

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

select job, count(1) as "people count" from emp where (sal+ifNull(comm,0)) >1500 group by job;

job      |people count|
---------|------------|
ANALYST  |           2|
MANAGER  |           3|
PRESIDENT|           1|
SALESMAN |           3|

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

select emp.ename from emp
inner join dept
on emp.deptno = dept.deptno
where dept.dname = 'SALES';

ename |
------|
ALLEN |
WARD  |
MARTIN|
BLAKE |
TURNER|
JAMES |

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

select ename from emp
where ename like 'S%';

ename|
-----|
SMITH|
SCOTT|

select ename from emp
where ename like '%S';

ename|
-----|
JONES|
ADAMS|
JAMES|

select ename from emp
where ename like '%S%';

ename|
-----|
SMITH|
JONES|
SCOTT|
ADAMS|
JAMES|

select ename from emp
where ename like '_L%';

ename|
-----|
ALLEN|
BLAKE|
CLARK|

7.查询每种工作的最高工资、最低工资、人数

select job
,max(sal+ifNull(comm,0)) as maxSal
,min(sal+ifNull(comm,0)) as minSal
,count(1) as number
from emp
group by job;

job      |maxSal |minSal |number|
---------|-------|-------|------|
ANALYST  |3000.00|3000.00|     2|
CLERK    |1300.00| 800.00|     4|
MANAGER  |2975.00|2450.00|     3|
PRESIDENT|5000.00|5000.00|     1|
SALESMAN |2650.00|1500.00|     4|

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

select 
emp.empno 
,emp.ename 
,dept.dname 
,emp.mgr 
,emp.sal+ifNull(emp.comm,0) as salary
,(select grade from salgrade where emp.sal+ifNull(emp.comm,0) >= losal and emp.sal+ifNull(emp.comm,0) <= hisal) as grade
from emp
left join dept
on emp.deptno = dept.deptno
where (emp.sal+ifNull(emp.comm,0)) > (select avg(emp.sal+ifNull(emp.comm,0)) from emp);

empno|ename |dname     |mgr |salary |grade|
-----|------|----------|----|-------|-----|
 7782|CLARK |ACCOUNTING|7839|2450.00|    4|
 7839|KING  |ACCOUNTING|    |5000.00|    5|
 7566|JONES |RESEARCH  |7839|2975.00|    4|
 7788|SCOTT |RESEARCH  |7566|3000.00|    4|
 7902|FORD  |RESEARCH  |7566|3000.00|    4|
 7654|MARTIN|SALES     |7698|2650.00|    4|
 7698|BLAKE |SALES     |7839|2850.00|    4|