数据库查询

目录

一.单表查询

1　基础查询

1.1　查询所有列

1.2　查询指定列

2　条件查询

2.1　条件查询介绍

3　模糊查询

4　字段控制查询

5　排序

6　聚合函数  sum avg max min count

7　分组查询

8 LIMIT

二.多表查询（重要）

1.合并结果集

2　连接查询 （非常重要）

3　自然连接

4　子查询（非常重要）

---

数据库查询是我们学习数据库的一个重点，也是非常基础的一个知识点，熟练的掌握数据库的查询会让我们更快的掌握数据库的其他知识；

一.单表查询

1　基础查询

1.1　查询所有列

SELECT * FROM stu;

1.2　查询指定列

SELECT sid, sname, age FROM stu;

2　条件查询

2.1　条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

1. =、!=、<>、<、<=、>、>=；
2. BETWEEN…AND；
3. IN(set)；
4. IS NULL；  
5. AND；
6. OR；
7. NOT；

示例：

2.2　查询性别为女，并且年龄50的记录

SELECT * FROM stu

WHERE gender='female' AND ge<50;

2.3　查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu

WHERE sid ='S_1001' OR sname='liSi';

2.4　查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu

WHERE sid IN ('S_1001','S_1002','S_1003');

2.5　查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM tab_student

WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

2.6　查询年龄为null的记录

SELECT * FROM stu

WHERE age IS NULL;

2.7　查询年龄在20到40之间的学生记录

SELECT *

FROM stu

WHERE age>=20 AND age<=40;

或者

SELECT *

FROM stu

WHERE age BETWEEN 20 AND 40;

2.8　查询性别非男的学生记录

SELECT *

FROM stu

WHERE gender!='male';

或者

SELECT *

FROM stu

WHERE gender<>'male';

或者

SELECT *

FROM stu

WHERE NOT gender='male';

2.9　查询姓名不为null的学生记录

SELECT *

FROM stu

WHERE NOT sname IS NULL;

或者

SELECT *

FROM stu

WHERE sname IS NOT NULL;

3　模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

通配符:

_ 任意一个字母

   %：任意0~n个字母

 '张%'

示例：

1.查询姓名由5个字母构成的学生记录

select * from stu

where name Like '_____';

2.查询姓名以"s"开头的学生记录

select * from stu

where name like 's%';

%是0到n个字符；

3.查询姓名中第二个字母为"i"的学生记录

select * from stu

where name Like '_i%';

4　字段控制查询

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

4.3　给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

5　排序

关键字：ASC（升序）

DESC（降序）

示例：

5.1　查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

5.2　查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

5.3　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

6　聚合函数  sum avg max min count

聚合函数是用来做纵向运算的函数关键字如下：

1. COUNT()：统计指定列不为NULL的记录行数；
2. MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
3. MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
4. SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
5. AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

示例：

6.1 COUNT

当需要纵向统计时可以使用COUNT()。

1. 查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

1. 查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

1. 查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp

WHERE sal > 2500;

1. 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

1. 查询有佣金的人数，以及有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2 SUM和AVG

当需要纵向求和时使用sum()函数。

1. 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

1. 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

1. 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

1. 统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

6.3 MAX和MIN

1. 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

7　分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

记住：查询一组数据时使用；

7.1　分组查询

示例：

查询部门的男女人数

select gender ,count(*) from stu where gender='male' or gender='female';

7.2 HAVING子句

1. 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

注：having与where的区别:

1.having是在分组后对数据进行过滤.

  where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)

          where后面不可以使用分组函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

8 LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

关键字：like

示例：

8.1　查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始！

8.2　分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

1. 第一页记录起始行为0，一共查询10行；
2. 第二页记录起始行为10，一共查询10行；
3. 第三页记录起始行为20，一共查询10行；

8.2　分页查询

查询语句书写顺序：select – from- where- group by- having- order by-limit

查询语句执行顺序：from - where -group by - having - select - order by-limit

示例：

上述都是一些简单的单表查询，下面是多表查询：

二.多表查询（重要）

多表查询有如下几种：

1. 合并结果集；UNION 、  UNION ALL
2. 连接查询

• 内连接  [INNER] JOIN  ON
• 外连接  OUTER JOIN ON

• 左外连接 LEFT [OUTER] JOIN
• 右外连接 RIGHT [OUTER] JOIN
• 全外连接（MySQL不支持）FULL JOIN

• 自然连接  NATURAL JOIN

1. 子查询

1.合并结果集

（联合查询）

作用：把两个select语句的查询结果合并到一起

合并结果有两种方式：

1. union
2. union all

示例：

create table A(

name varchar(10),

score int

)

create table B(

name varchar(10),

score int

)

insert into A values('a',10),('b',20),('c',30);

insert into B values('a',10),('b',20),('d',40);

SELECT * FROM A

UNION

SELECT *FROM B;

结果：

SELECT * FROM A

UNION ALL

SELECT *FROM B;

结果：

2　连接查询 （非常重要）

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

直接select *from student，score；会怎么样？

会出现下面结果：笛卡尔积；

红框是我们要的数据，怎么去除，加条件

我们就要用到连接查询

-- 演示笛卡尔积SELECT * FROM student,score;

-- 通过主外键关系来去除无用信息SELECT * FROM student s,score c WHERE s.stuid=c.stuid;SELECT s.stuid,s.stuname,c.score,c.courseid FROM student s,score c WHERE s.stuid=c.stuid;SELECT * FROM student ,score WHERE student.stuid=score.stuid;

-- 继续演示员工表的笛卡尔积现象SELECT * FROM emp;SELECT * FROM dept;SELECT * FROM emp,dept;

-- 继续通过主外键关系来去除无用信息SELECT * FROM emp e,dept d WHERE e.deptno = d.deptno;-- 改进版

SELECT e.empno ,e.ename ,e.job,e.sal,e.mgr,e.deptno FROM emp e,dept d WHERE e.deptno = d.deptno;

上面的查询不是标准的sql查询是一种“方言”查询99查询

下面就是标准查询

2.1　内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

　　其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

2.2　外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

2.3　右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

3　自然连接

关键字：natural；

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

1. 两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

4　子查询（非常重要）

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

1. 子查询出现的位置：

• where后，作为条为被查询的一条件的一部分；
• from后，作表；

1. 当子查询出现在where后作为条件时，还可以使用如下关键字：

• any
• all

1. 子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

示例：

-- 子查询

SELECT * FROM emp;

-- 我要查询与scott在同一个部门的员工

SELECT * FROM emp WHERE deptno=

(SELECT deptno FROM emp WHERE ename='scott');

--  工资高于JONES的员工

SELECT * FROM emp WHERE sal>

(SELECT sal FROM emp WHERE  ename='jones');

-- 工资高于30号部门所有人的员工信息

SELECT * FROM emp WHERE sal>

(SELECT MAX(sal) FROM emp WHERE deptno=30);