数据库基本查询

普通查询

记录查询

1.最基本的查询语句是由 SELECT 和FROM 关键字组成的

SEIECT * FROM t emp;
SELECT empno, ename, sal FROM t emp;

2.SELECT语句屏蔽了物理层的操作，用户不必关心数据的真实存储，交由数据库高效的查找数据

使用列别名

1.通常情况下，SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列名重命名的机制

SELECT
empno,
sal*12 As "income"
FROM temp;

查询语句的子句执行顺序

SELECT
empno,
sal*12 As "income"
FROM t emp;

数据分页

1.比如我们查看朋友圈，只会加载少量部分信息，不用一次性加载全部朋友圈，那样只会浪费CPU时间、内存和网络带宽
2.如果结果集的记录很多，则可以使用LIMIT关键字限定结果集数量。

SELECT......FROM......LIMIT 起始位置，偏移量；

SELECT empno, ename FROM t_emp LIMIT 0, 20;

数据分页的简写用法

1.如果LIMIT子句只有一个参数，它表示的是偏移量，起始值默认为0

SELECT empno, ename FROM t_emp LIMIT 10;
SELECT empno, ename FROM t_emp LIMIT 0,10;

执行顺序:FROM -> SELECT -> LIMIT

排序

如果没有设置，查询语句不会对结果集进行排序。也就是说，如果想让结果集按照某种顺序排列，就必须使用ORDER BY子句。 以下代码是根据sal 升序
1.ASC代表升序（默认），DESC代表降序
2.如果排序列是数字类型，数据库就按照数字大小排序，如果是日期类型就按照日期大小排序，如果是字符串就按照字符集序号排序。

#SELECT... FROM ...ORDER BY 列名 [ASC I DESC];//ASC 升序（默认）   DESC降序
SELECT ename, sal FROM t_emp ORDER BY sal;  //升序
SELECT ename, sal FROM t_emp ORDER BY hiredate DESC //降序

3.我们可以使用ORDER BY规定首要排序条件和次要排序条件。数据库会先按照首要排序条件排序，如果遇到首要排序内容相同的记录
，那么就会启用次要排序条件接着排序

# 优先根据 sal  降序排，其次根据hiredate 升序排
SELECT empno, ename, sal, hiredate FROM t_emp ORDER BY sal DESC, hiredate ASC
# 优先根据 deptno升序排，其次根据sal 降序排
SELECT empno, ename, deptno, sal FROM t_emp ORDER BY deptno, sal DESC
SELECT empno, ename, sal  FROM t_emp ORDER BY sal DESC LIMIT 0, 5;

去除重复记录

1.如果我们需要去除重复的数据，可以使用DISTINCT关键字来实现

#SELECT DISTINCT 字段 FROM ...;
SELECT DISTINCT job FROM t_emp;

tips:
1.仅仅是结果集里面显示的 根据job 字段去重
2.使用DISTINCT的SELECT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效。

SELECT DISTINCT job, ename FROM t_emp;

条件查询

1.很多时候，用户感兴趣的并不是逻辑表里的全部记录，而只是它们当中能够满足某一种或某几种条件的记录。这类条件要用WHERE子
句来实现数据的筛选

#AND 代表逻辑与，OR代表逻辑或    
SELECT...FROM...WHERE 条件 [AND IOR]条件
SELECT empno, ename, sal FROM t_emp WHERE deptno=10 AND sal >= 2000;

2.WHERE语句中的条件运算会用到以下四种运算符：数学运算符、比较运算符、逻辑运笪符、按位运算符

SELECT deptno,empno,ename,sal 
FROM t_emp 
WHERE (deptno=10 OR deptno=20) AND sal>=2000;

#查询部门编号10 ，年薪1.5w以上，工龄20年以上的人员
SELECT empno,ename,sal,hiredate 
FROM t_emp 
WHERE  deptno =10 AND (sal+IFNULL(comm,0))*12>=15000
AND DATEDIFF(NOW(),hiredate)/365>=20;

3.逻辑运算符
AND 与关系 age ＞ 18 AND sex = "男"
OR 或关系 empno = 8000 OR deptno = 20
NOT 非关系 NOT deptno = 20
XOR 异或关系 age ＞ 18 xOR sex ="男”
tips : xor ture+ ture = false , false+false=false , ture + flase =true

4.WHERE子句的注意事项
WHERE子句中，条件执行的顺序是从左到右的。所以我们应该把索
引条件，或者饰选掉记录最多的条件写在最左侧,这样能优化查询速度

SELECT empno, ename FROM t emp
WHERE ename = "FORD" AND sal >=2000;
SELECT empno, ename FROM temp
WHERE deptno = 10 AND sal >= 2000;

5.各种子句的执行顺序
条件查询中，WHERE子句应该是第几个执行？
FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT

数据库高级查询

6.聚合函数

#聚合函数 AVG 求平均值 
SELECT AVG(sal+IFNULL(comm,0)) AS avg 
FROM t_emp;

#SUM 求和 
SELECT SUM(sal) FROM t_emp WHERE deptno IN(10,20);

# MAX求最大值
SELECT MAX(comm) FROM t_emp;
SELECT MAx(sal+IFNULL(comm,0)) FROM t_emp 
WHERE deptno IN (10,20);//查询10和20部门中，月收入最高的员工？
SELECT MAX(LENGTH(ename)) FROM t_emp;//查询员工名宇最长的是几个字符？

#MIN函数用来获得非值的最小值 
SELECT MIN(empno) FROM t_emp;
SELECT MIN(hiredate) FROM t_emp;

#COUNT(*)用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数。
SELECT COUNT (*) FROM t emp;
SELECI COUNT (comm) FROM t emp;

#查询10和20部门中，底薪超过2000元，并且工龄超过15年的员工人数？
SELECT COUNT(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>=2000 
AND DATEDIFF(NOW(),hiredate)/365>=15;

#查询1985年以后入职的员工，底薪超过公司平均底薪的员工数量？
#这里是查询不出来的，聚合函数AVG 不能出现在where条件语句里面，因为结果集没有查询出来 ，聚合函数需要有结果集才能继续。
SELECT COUNT(*) FROM t_emp
WHERE hiredate >="1985-01-01" AND sal>AVG(sal);

7.数据库表的分组查询
默认情况下汇总函数是对全表范围内的数据做统计
GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理

#按照部门编号分组，求每组的底薪平均值
SELECT deptno ,ROUND(AVG(sal))
FROM t_emp GROUP BY deptno;

#逐级分组 
#查询每个部门里，每种职位的人员数量和平均底薪 
SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno,job ORDER BY deptno;

8.对SELECT子句的要求
查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP
BY子句的分组列，其余内容均不可以出现在SELECT子句中

SELECT deptno, COUNT (*), AVG (sal)
FROM t_emp GROUP BY deptno;
# 下面这条语句查询不出来的原因是 ，结果集 sal 多条 跟count 一条 对应不上
SELECT deptno, COUNT (*), AVG (sal), sal
FROM t emp GROUP BY deptno;

9.对分组结果集再次做汇总计算

SELECT
deptno, COUNT (*), AVG (sal), MAX (sal), MIN (sal)
FROM temp GROUP BY deptno WITH ROLLUP;

WITH ROLLUP 分组结果集再次做汇总计算，对列计算，插在最下面一行

10.GROUP CONCAT函数
GROUP CONCAT函数可以把分组查询中的某个字段拼接成一个字
符串

#查询每个部门内底新超过2000元的人数和员工姓名 , 
# ename 多条拼接成一条跟count* 对应
SELECT deptno, COUNT(*),GROUP_CONCAT(ename)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

11.各种子句的执行顺序
查询语句中，GROUP BY子句应该第几个执行？
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

12.分组查询遇到的困难？
以下代码有问题 ， where 是优先于group by 执行的，一旦where字句中出现AVG类似的汇总函数，所以MySQL不知道 AVG汇总函数没办法知道按照什么样的范围统计汇总数值
所以引入HAVING子句来解决这个问题。

#查询每个部门内底新超过2000元的人数和员工姓名 （此语法错误）
SELECT deptno, COUNT(*),GROUP_CONCAT(ename)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

#查询每个部门内底新超过2000元的人数和员工姓名 （此语法正确）
SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG (sal) >=2000;

13. HAVING子句

#查询每个部门中，1982年以后入职的员工超过2个人的部门编号 ，通过having子句
SELECT deptno 
FROM t_emp
WHERE hiredate >="1982-01-01"
GROUP BY deptno HAVING COUNT(*) >=2 AND AVG(sal) >=2000
ORDER BY deptno ASC;

14.HAVING子句的特殊用法

SELECT deptno, COUNT(*)
FROM t_emp
WHERE  deptno IN (10, 20)
GROUP BY 1;

15.从多张表中提取数据
规定了连接条件的表连接语向，就不会出现笛卡尔积

SELECT e.empno, e.ename, d.dname
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

16.表连接的分类
内连接是结果集中只保留符合连接条件的记录
外连接是不管符不符合连接条件，记录都要保留在结果集中

17.内连接的简介
内连接是最常见的一种表连接，用于查询多张关系表符合连接条件的记录。
内连接的多种语法形式

#以下写法都一样
SELECT .... FROM 表1 JOIN 表2  ON  连接条件；
SELECT  … FROM 表1 JOIN  表2  WHERE 连接条件；
SELECT  … FROM 表1，表2 WHERE 连接条件；

#查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级？(表内连接用法)
SELECT
e.empno, e.ename, d.deptno,e.sal,e.job, s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s oN e.sal BETWEEN s.losal AND s.hisal;

#查询与SCOTT相同部门的员工都有谁
SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename!="SCOTT";

#相同的数据表也可以做表连接,用表连接的方式来实现上面的查询，效率更高
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename !="SCOTT"

#查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄？
SELECT COUNT(*) , MAX(e.sal) , MIN(e.sal) , AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)/365)
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级
SELECT
e.job,
MAX(e.sal+IFNULL(e.comm, 0)),
MIN(e.sal+IFNULL(e.comm, 0)),
AVG(e.sal+IFNULL(e.comm, 0)),
MAX(s.grade) ,
MIN(s.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(e.comm, 0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

#查询每个底薪超过部门平均底薪的员工信息
SELECT e.empno, e.ename, e.sal
FROM t_emp e JOIN
(SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;

#左外连接，左表保留所有,右表如果有值出值，没值出null值匹配
SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno;

#右外连接，右表保留所有,左表如果有值出值，没值出null值匹配
SELECT e.empno, e.ename, d.dname
FROM t_dept d RIGHT JOIN t_emp e
ON e.deptno=d.deptno;

#外连接练习1:查询每个部门的名称和部门的人数？
SELECT d.dname, COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno;

#UNION关键字可以将多个查询语句的结果集进行合并
#（查询语句）UNION（直询语包）UNION（直询语句）
#查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称用NULL代替。
(
SELECT d.dname, COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno
) UNION
(SELECT d.dname, COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno);

#查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门？
SELECT
e.empno, e.ename, d.dname, e.sal+IFNULL(e.comm, 0), s.grade,
FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
t.empno AS mgrno, t.ename AS mname, t.dname AS mdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno, e1.ename, d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno=d1.deptno
) t ON e.mgr=t.empno;

内连接总结：内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以。
外连接总结: 左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

18.外连接的注意事项
内连接只保留符合条件的记录，所以查询条件写在ON子句和
WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE
子向里，不合符条件的记录是会被过滤掉的，而不是保留下来。

SELECT
e.empno,e.ename, d.dname
FROM
t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE e.deptno = 10;

19.子查询的分类
子查询可以写在三个地方：WHERE子句、FROM子句、SELECT子
句，但是只有FROM子句子查询是最可取的
19.1)WHERE子查询(不推荐)
这种子查询最简单，最容易理解，但是却是效率很低的子查询
查询底薪超过公司平均底薪的员工的信息

#WHERE子查询
SELECT
empno, ename, sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);

19.2)FROM子查询(推荐)
这种子查询只会执行一次，所以FROM子查询查询效率很高

#FROM子查询
SRTECT
e.empno, e.ename, e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno, AVG (sal) as avg
FROM t_emp GROUP BY deptno) t
ON e.deptno=t. deptno AND e.sal>=t.avg;

19.3)SELECT子查询(不推荐)
这种子查询每输出一条记录的时候都要执行一次，查询效率很低

#SELECT子查询
SELECT
e.empno, e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno)
FROM t_emp e;

20.数据操作语言：子查询（二）
20.1）单行子查询和多行子查询
单行子查询的结果集只有一条记录，多行子查询结果集有多行记录
多行子查询只能出现在WHERE子句和FROM子句中

#如何用子查询查找FORD和MARTIN两个人的同事？
SELECT ename
FROM t_emp
WHERE deptno IN(SELECT deptno FROM t_emp WHERE ename IN("FORD", "MARTIN"))
AND ename NOT IN("FORD", "MARTIN");

20.2)WHERE子句中的多行子查询
WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断

#查询比FORD和MARTIN底薪都高的员工信息？
SELECT ename FROM t_emp
WHERE sal >= ALL
(SELECT sal FROM t_emp
WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD", "MARTIN");

SELECT ename FROM t_emp
WHERE sal >= ANY
(SELECT sal FROM t_emp
WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD", "MARTIN");

20.3）EXISTS关键字(存在)
EXISTS关键宇是把原来在子查询之外的条件判断，写到了子查询的里面。
SELECT...FROM 表名 WHERE [NOT] EXISTS（子查询）;

#查询工资等级是3级或者4级的员工信息
SELECT empno, ename, sal
FROM t_emp
WHERE EXISTS(
SELECT grade FROM t_salgrade
WHERE sal BETWEEN losal AND hisal
AND grade IN (3, 4)
);

t_bonus.png

t_dept.png

t_emp.png

t_salgrade.png

以下为建表的源码仅供参考:

为了还原这些查询涉及的表并插入一些记录，我们需要创建三个表：t_emp、t_dept 和 t_salgrade。下面是表的定义以及插入的一些示例数据：

表结构和示例数据

创建 t_emp 表

CREATE TABLE t_emp (
    empno INT PRIMARY KEY,
    ename VARCHAR(50),
    job VARCHAR(50),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(10, 2),
    comm DECIMAL(10, 2),
    deptno INT
);

INSERT INTO t_emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(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, '1982-12-09', 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, '1983-01-12', 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);

创建 t_dept 表

CREATE TABLE t_dept (
    deptno INT PRIMARY KEY,
    dname VARCHAR(50),
    loc VARCHAR(50)
);

INSERT INTO t_dept (deptno, dname, loc) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

创建 t_salgrade 表

CREATE TABLE t_salgrade (
    grade INT PRIMARY KEY,
    losal DECIMAL(10, 2),
    hisal DECIMAL(10, 2)
);

INSERT INTO t_salgrade (grade, losal, hisal) VALUES
(1, 700.00, 1200.00),
(2, 1201.00, 1400.00),
(3, 1401.00, 2000.00),
(4, 2001.00, 3000.00),
(5, 3001.00, 5000.00);

查询示例

查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级

SELECT
    e.empno, e.ename, d.dname, e.sal, e.job, s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

查询与 SCOTT 相同部门的员工

SELECT ename
FROM t_emp
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename = 'SCOTT')
AND ename != 'SCOTT';

使用表连接的方式查询与 SCOTT 相同部门的员工

SELECT e2.ename
FROM t_emp e1
JOIN t_emp e2 ON e1.deptno = e2.deptno
WHERE e1.ename = 'SCOTT' AND e2.ename != 'SCOTT';

查询 RESEARCH 部门的人数、最高底薪、最低底薪、平均底薪、平均工龄

SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(e.sal), AVG(DATEDIFF(NOW(), e.hiredate)/365)
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
WHERE d.dname = 'RESEARCH';

查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级

SELECT
    e.job,
    MAX(e.sal + IFNULL(e.comm, 0)),
    MIN(e.sal + IFNULL(e.comm, 0)),
    AVG(e.sal + IFNULL(e.comm, 0)),
    MAX(s.grade),
    MIN(s.grade)
FROM t_emp e
JOIN t_salgrade s ON (e.sal + IFNULL(e.comm, 0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

查询每个底薪超过部门平均底薪的员工信息

SELECT e.empno, e.ename, e.sal
FROM t_emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal > t.avg_sal;

左外连接查询

SELECT e.empno, e.ename, d.dname
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno;

右外连接查询

SELECT e.empno, e.ename, d.dname
FROM t_dept d
RIGHT JOIN t_emp e ON e.deptno = d.deptno;

查询每个部门的名称和部门的人数

SELECT d.dname, COUNT(e.deptno)
FROM t_dept d
LEFT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno;

UNION 查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用 NULL 代替

(SELECT d.dname, COUNT(e.deptno)
FROM t_dept d
LEFT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno)
UNION
(SELECT d.dname, COUNT(*)
FROM t_dept d
RIGHT JOIN t_emp e ON d.deptno = e.deptno
GROUP BY d.deptno);

查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门

SELECT
    e.empno, e.ename, d.dname, e.sal + IFNULL(e.comm, 0) AS sal, s.grade,
    FLOOR(DATEDIFF(NOW(), e.hiredate) / 365) AS years_of_service,
    t.empno AS mgrno, t.ename AS mname, t.dname AS mdname
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN (
    SELECT e1.empno, e1.ename, d1.dname
    FROM t_emp e1
    JOIN t_dept d1 ON e1.deptno = d1.deptno
) t ON e.mgr = t.empno;

这样创建了所需的表结构并插入了一些数据，执行上述查询将会得到相应的结果。