sql- day3

最新推荐文章于 2024-11-13 17:26:18 发布

ddl12131

最新推荐文章于 2024-11-13 17:26:18 发布

阅读量306

点赞数

文章标签： python list 编辑器 sql

本文链接：https://blog.csdn.net/ddl12131/article/details/121616275

版权

Day3
（1）去除重复记录
查询结果的去重
mysql> select distinct job from emp; //distict去除重复纪录
±----------+
| job |
±----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
±----------+
5 rows in set (0.00 sec)
Select ename, distinct job from emp;
注意：distinct只能出现在所有字段的最前面
mysql> Select ename, distinct job from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘distinct job from emp’ at line 1

(2)mysql> select distinct deptno,job from emp;
±-------±----------+
| deptno | job |
±-------±----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
±-------±----------+
9 rows in set (0.00 sec)

(3)统计岗位的数量
Select distinct job from emp;
Select count(distinct job) from emp;
(4)8 连接查询概述

什么是链接查询，大部分情况下都不是从单表查询数据,一般都是多张表联合起来查询取出最终的结果;
实际开发中，一般一个业务对应多张表，比如学生和班级，起码两张表

根据表的连接方式划分
Sql92 sql99
根据表的连接方式来划分，包括
等值连接
非等值连接
外连接;
左外连接（）左连接；右外连接（右连接）
全连接（不讲，很少用）

（4）在表的连接查询方面有个现象称为
笛卡尔乘积现象

案例：找出每一个员工的部门名，要求显示员工名和部门名
mysql> select deptno,ename from emp;
±-------±-------+
| deptno | ename |
±-------±-------+
| 20 | SMITH |
| 30 | ALLEN |
| 30 | WARD |
| 20 | JONES |
| 30 | MARTIN |
| 30 | BLAKE |
| 10 | CLARK |
| 20 | SCOTT |
| 10 | KING |
| 30 | TURNER |
| 20 | ADAMS |
| 30 | JAMES |
| 20 | FORD |
| 10 | MILLER |
±-------±-------+
14 rows in set (0.00 sec)

mysql> select deptno,ename from emp;
±-------±-------+
| deptno | ename |
±-------±-------+
| 20 | SMITH |
| 30 | ALLEN |
| 30 | WARD |
| 20 | JONES |
| 30 | MARTIN |
| 30 | BLAKE |
| 10 | CLARK |
| 20 | SCOTT |
| 10 | KING |
| 30 | TURNER |
| 20 | ADAMS |
| 30 | JAMES |
| 20 | FORD |
| 10 | MILLER |
±-------±-------+

语法
。。。A。。。。Join B on 连接条件 where…
（7）内连接–非等职连接
最大特点：l连接条件中的关系是非等量关系
案例：
找出每个员工的工资等级，要求显示员工名，工资，工资等级

mysql> select * from salgrade; s:
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+
5 rows in set (0.00 sec)

mysql> select sal,ename from emp;
±--------±-------+
| sal | ename |
±--------±-------+
| 800.00 | SMITH |
| 1600.00 | ALLEN |
| 1250.00 | WARD |
| 2975.00 | JONES |
| 1250.00 | MARTIN |
| 2850.00 | BLAKE |
| 2450.00 | CLARK |
| 3000.00 | SCOTT |
| 5000.00 | KING |
| 1500.00 | TURNER |
| 1100.00 | ADAMS |
| 950.00 | JAMES |
| 3000.00 | FORD |
| 1300.00 | MILLER |
±--------±-------+
14 rows in set (0.00 sec)

（10）
mysql> select e.ename,e.sal,s.grade
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal;
±-------±--------±------+
| ename | sal | grade |
±-------±--------±------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±-------±--------±------+
（11）自连接 :
最大的特点是:一张表看作两张表
自己连接自己
案例：
找出每个员工的上级领导，要求显示员工名和对应领导名
mysql可以 Oracle不行
mysql> select * from emp where ename = ‘smith’;
±------±------±------±-----±-----------±-------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±------±------±-----±-----------±-------±-----±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
±------±------±------±-----±-----------±-------±-----±-------+
1 row in set (0.00 sec)
（12）mysql> select job,sum(sal) from emp group by job;
±----------±---------+
| job | sum(sal) |
±----------±---------+
| ANALYST | 6000.00 |
| CLERK | 4150.00 |
| MANAGER | 8275.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 5600.00 |
±----------±---------+
5 rows in set (0.00 sec)
（13）mysql> select empno, ename, mgr from emp;
±------±-------±-----+
| empno | ename | mgr |
±------±-------±-----+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
±------±-------±-----+
14 rows in set (0.00 sec)
（14）难mysql> select a.ename as ‘员工名’, b.ename as ‘领导名’
-> from
-> emp a
-> inner join
-> emp b
-> on
-> a.mgr = b.empno;
±-------±-------+
| 员工名 | 领导名 |
±-------±-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±-------±-------+
13 rows in set (0.00 sec)
mysql> select * from emp;
±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-------±----------±-----±-----------±--------±--------±-------+
14 rows in set (0.00 sec)

15.外连接
什么是外连接和内连接有什么区别？
内连接:假设A 和B表进行连接,
外连接：有主表
左外连接：表示左边的这张表是主表
右外连接

16.外连接（右外连接/右连接）笔试外连接可以做出来内连接删
Select
A.ename ‘员工’,b.ename ‘领导’
From emp b
Right join
Emp a
On a.mgr=b.empno;
17.Outer 可以省略
找出哪个部门没有员工
mysql> select d.* from emp e
-> right join
-> dept d
-> on e.deptno =d.deptno
-> where e.empno is null;
±-------±-----------±-------+
| DEPTNO | DNAME | LOC |
±-------±-----------±-------+
| 40 | OPERATIONS | BOSTON |
±-------±-----------±-------+
1 row in set (0.00 sec)

22.select e.ename ‘员工’,d.name, s.grade, el.ename ‘领导’
from emp e
-> join dept d
-> on e.deptno = d.deptno
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> left join emp el
-> on e.mgr=el.empno;

24.insert delete update
Select

25.Navicat
26.where后面嵌套子查询
子查询
什么是子查询？子查询都可以出现在哪里
Select
…(select),
From …(select);
Where …(select);
27.where 子句中使用子查询
案例：找出高于平均薪资的员工信息
Select * from emp where sal>avg(sal);
mysql> Select * from emp where sal>avg(sal);
ERROR 1111 (HY000): Invalid use of group function
mysql> select * from emp where sal>(select avg(sal) from emp);
±------±------±----------±-----±-----------±--------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±------±----------±-----±-----------±--------±-----±-------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | |
±------±------±----------±-----±-----------±--------±-----±-------+
6 rows in set (0.00 sec)

28.第一步：找出平均薪资
Select avg(sal) from emp;
第二部：where过滤
29.from 后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级;

30.B DBMS SQL的关系
卸载
Sql–progam file

隐藏的 program data2

31.from后面嵌套子查询
第一步：找出每个部门平均薪水(按照部门编号,求
mysql> select deptno,avg(sal) from emp group by deptno;
±-------±------------+
| deptno | avg(sal) |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+
3 rows in set (0.00 sec)
第二步:将部门的平均薪水当作新表
第二步:将以上的查询结果当临时表 t，让t表和salgrade 表连接条件是：’t.
mysql> select t.*, s.grade
-> from t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal;
ERROR 1146 (42S02): Table ‘notaeasyday.t’ doesn’t exist
mysql>

Select t.*, s.grade
From (select deptno,avg(sal) as avgsal from emp group by deptno) t
Join
Salgrade s
On t.avgsal between s.losal and s.hisal;

mysql> Select t., s.grade
-> From (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> Join
-> Salgrade s
-> On t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
3 rows in set (0.00 sec)
mysql> select t., s.grade
-> from (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> join
-> salgrade s
-> on t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
±-------±------------±------+
3 rows in set (0.00 sec)
mysql> select t.*, s.grade
-> from t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal;
ERROR 1146 (42S02): Table ‘notaeasyday.t’ doesn’t exist
32.案例:找出每个部门薪资等级的平均值
第一步找出每个员工的薪水等级
Select ename,sal from emp;
mysql> select ename,sal from emp;
±-------±--------+
| ename | sal |
±-------±--------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
±-------±--------+

33.mysql> select e.ename, e.sal, e.deptno, s.grade from emp e join salgrade s
34. -> on e.sal
35. -> between s.losal and s.hisal;
36.±-------±--------±-------±------+
37.| ename | sal | deptno | grade |
38.±-------±--------±-------±------+
39.| SMITH | 800.00 | 20 | 1 |
40.| ALLEN | 1600.00 | 30 | 3 |
41.| WARD | 1250.00 | 30 | 2 |
42.| JONES | 2975.00 | 20 | 4 |
43.| MARTIN | 1250.00 | 30 | 2 |
44.| BLAKE | 2850.00 | 30 | 4 |
45.| CLARK | 2450.00 | 10 | 4 |
46.| SCOTT | 3000.00 | 20 | 4 |
47.| KING | 5000.00 | 10 | 5 |
48.| TURNER | 1500.00 | 30 | 3 |
49.| ADAMS | 1100.00 | 20 | 1 |
50.| JAMES | 950.00 | 30 | 1 |
51.| FORD | 3000.00 | 20 | 4 |
52.| MILLER | 1300.00 | 10 | 2 |
53.±-------±--------±-------±------+
54.14 rows in set (0.00 sec)
第二部：基于以上结果,继续按照deptno分组，求grade平均值
Select e.deptno,avg(
From emp e
Join salgrade s
On e.sal between s.losal and s.hisal
Group by e.deptno;

mysql> Select e.deptno,avg(s.grade)
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> group by e.deptno;
±-------±-------------+
| deptno | avg(s.grade) |
±-------±-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------------+
3 rows in set (0.00 sec)

55.在select后面嵌套子查询
案例:
找出每个员工所在的部分名称. 要求显示员工名和部门名
Select e.ename, d.dname
From emp e
Join dept d
On e.deptno = d.deptno;
Select e.ename, (select d.dname from dept d where e.deptno = d.deptno)
As dname from emp e;