2.查询操作-mysql

主要总结：单表查询，使用各聚合函数查询，连接查询，子查询，合并查询结果，为表、字段取别名。

练的时候还是用图形化界面去写sql，黑框写sql错了不好改 也不太好编辑。

一、单表查询

1.1查询所有字段信息和查询指定字段信息

查询所有字段的话可以直接上select*可以把所有字段写出来，区别的是写所有字段的话可以按照字段排列的顺序显示，select*查询出来显示的就是表的顺序结构

select * from t_bookInfo

select bookName,id,bookPrice from t_bookInfo;

 结果分别是

1.2带where条件的查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 条件表达式；

where的约束条件可以用一个等号连接，它并不像c，java是两个等号==判断是否相等。比如找出id为2的书

select * from t_bookInfo where id = 2;

1.3带IN关键词查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1，元素 2，元素 3)；

这个关键字和where配置使用，筛选条件的作用，in后面跟的是一个元素集合，只要这个判断条件的字段在集合里面就符合条件

比如这样

select * from t_bookInfo where id in (2,4);

当然也可以not in原理用法都是一样的

1.4带between and关键词查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2

between and关键词判断的是一个区间而不是一个集合。当然也有not不在这个区间内。

比如这样

select * from t_bookInfo where id BETWEEN 2 and 4;

得到id在区间[2, 4]的记录，左右都是闭区间。

1.5带like的模糊查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’；

模糊查询有两个字符：'%'代表任意字符，'_'代表单个字符。比如在某些时候不知道一本书的全部名字，只知道书名的几个字开头什么的，就可以 用模糊查询完成。很多时候查询操作都是进行的模糊查询，查询出来的记录都有相同的部分。

想得到关于书名有算法两个字的书 就可以通过like结合%字符完成

select * from t_bookInfo where bookName like  '%算法%';

这里   %算法%     %可以理解为代表若干个任意字符

select * from t_bookInfo where bookName like  '算法__';

 

select * from t_bookInfo where bookName like  '算法_';

'_'代表的也是任意字符 不过是一个不是若干个 ，通过测试发现一个下划线代表的一个字符，一个汉字通过一个模糊字符也能完成，我的猜测是一个汉字是两字节的，比如由字节ab构成一个汉字，字节a和模糊字符匹配上就算是符合条件了。反正是模糊查询。

1.6空值查询，带AND，OR查询

空值查询就是判断条件的时候判断属性是否为空，不难理解。

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL；

AND和OR就是且、或的意思，就如同条件 && 和 ||

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]

查询书名有算法二字的并给70块以上的书本

select * from t_bookInfo where bookName like  '算法%' AND bookPrice > 70;

记录必须得都满足这两条件才算符合条件 就是都得符合

就是这样纸的

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]

or的话就是只要判断所有条件中的任一就算符合条件

select * from t_bookInfo where bookName like  '%算法%' OR bookPrice > 70;

只要是名字里面有算法二字或者说大于70块钱就符合条件。

1.7  DISTINCT 去重复查询

SELECT DISTINCT 字段名 FROM 

在字段前面加上distinct修饰，去掉相同的记录，判断相同的时候是根据你选择的字段，判断记录的这些属性是否都相同，都相同才算是重复的

表数据是这样的

有两本算法基础 使用disitinct关键词去重

select DISTINCT  bookName, bookPrice from t_bookInfo;

这个去重操作可以看一下所有书的价格情况，去掉重复的价格。

1.8对查询结果排序

SELECT 字段 1，字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC]

使用order by对查询的记录排序，asc是升序，desc是降序

select * from t_bookinfo ORDER BY bookPrice desc;

根据书的价格降序排列

1.9使用group by分组查询

GROUP BY 属性名 
[HAVING 条件表达式][WITH ROLLUP]1，单独使用无意义
2，与 GROUP_CONCAT()函数一起使用；
3，与聚合函数一起使用
4，与 HAVING 一起使用(限制输出的结果)；
5，与 WITH ROLLUP 一起使用(最后加入一个总和行)

这个看着有点复杂 还是通过实际的sql语句看比较清晰。

这个关键词上次去银联面试就被问到了，当时不确定答案没说，group by在面试的时候还是较频繁的提问的。

用法就是分组，根据字段进行分组，单独使用是没有意义的，通常和group_concat()一起使用。

先给表里加一个字段，书的类型 type

我现在要根据书的类型来分类，看一下每个类型下都有哪些书

select bookType,GROUP_CONCAT(bookName) from t_bookinfo GROUP BY bookType;

和Having的用法是限制分组里面的成员的数量，现在继续增加需求，分类完了，那个类里面有超过1本书再输出。

select bookType,GROUP_CONCAT(bookName) from t_bookinfo GROUP BY bookType HAVING COUNT(bookName) > 1;

with rollup就是多输出一行，汇总一下

select bookType,GROUP_CONCAT(bookName) from t_bookinfo GROUP BY bookType WITH ROLLUP;

聚合函数后面还有很多。

1.10 limit分页查询

SELECT 字段 1，字段 2...FROM 表名 LIMIT 初始位置，记录数；

这个表里面有很多记录，查询的时候，整个表查，但可以通过limit限制范围

把整个表分页，一页有多少个数据

select * from t_bookinfo LIMIT 0,3;

select * from t_bookinfo LIMIT 4,6;

limit a,b 这个用法相当于 从表里面第a+1条件 到b+1条结束，就在这个范围内。

  limit关键词常和排序order by联合一起考察，比如查询价格70元的书籍，降序输出前5条

这是表数据

SELECT bookName,bookPrice,bookType from  t_bookinfo WHERE bookPrice > 70 ORDER BY bookPrice desc LIMIT 0,4;

二、使用聚合函数查询

聚合函数通常也会group by结合一起使用，常用的有：count（），avg（），max（），min（），sum（）。其他还有很多，查一下mysql手册都可以查到的。

2.1  count（）聚合函数

用来统计查询到的记录的数量，比如查询各个类别的图书有多少本

SELECT bookType,COUNT(bookName),GROUP_CONCAT(bookName)
from t_bookinfo
GROUP BY bookType;

有时也可以单独使用都是可以的，

2.2  sum()函数

用来计算查询的所有记录之和，比如查询各个类别下的图书价格之和

SELECT bookType,SUM(bookPrice),GROUP_CONCAT(bookName)
from t_bookinfo
GROUP BY bookType;

和上面的计算count用法差不多，将count换成sum即可

2.3 max（），min（），avg（）函数

这些都是一些数学的函数，求记录最大值，最小值，平均值

比如要求一个类别里面最贵的图书，最便宜的图书，平均价格

SELECT bookType,max(bookPrice), min(bookPrice), AVG(bookPrice), GROUP_CONCAT(bookName)
from t_bookinfo
GROUP BY bookType;

聚合函数用法大同小异，用法都是差不多的，没什么难的，多练习掌握用法。

三、连接查询 多表查询

通常是查询两个或两个以上表的数据，首先理解一个笛卡尔积，比如有两个集合A，B，A=[a1, a2], B=[b1, b2, b3];

这两个集合笛卡尔积的所有结果就是a1b1 , a1b2, a1b3, a2b1, a2b2, a2b3。

连接查询包括内连接和外连接查询。再建一张表t_bookType来练习，调整一下两个表的结构如下。

把以前表里面类型用序号来表示。先查询这两张表的所有记录

SELECT * FROM t_bookinfo t_bookType ;

查询到的记录是很多的，记录数量是两个表数量的成绩，就是一个笛卡尔积。

3.1 内连接查询

最常用的连接查询之一，可以查询两个或两个以上的表

SELECT * FROM t_bookinfo ,t_bookType where t_bookinfo.bookType = t_booktype.id ;

就查到了所有的图书 不像上面那样有很多重复的书名。

3.2外连接查询

SELECT 属性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2；

 3.2.1  右连接查询

可以查询出“表名 2”的所有记录，而“表名 1”中，只能查询出匹配的记录

先给图书类型的表里面加几个记录， 表1里面没有的类型

SELECT * FROM t_bookinfo RIGHT JOIN t_bookType on t_bookinfo.bookType = t_booktype.id ;

虽然有的类型图书表1里面没有 但是还是显示出来 给个null

3.2.2 左连接查询

与左连接相反，可以查询出“表名 1”的所有记录，而“表名 2”中，只能查询出匹配的记录

表2有的记录和表1匹配才能查询出来 比如这里往表一里面加入一些新的记录 如下

新加入的操作系统类型是7 第二张表里面是没有的

SELECT * FROM t_bookinfo left JOIN t_bookType on t_bookinfo.bookType = t_booktype.id ;

表2里面没有类型7的记录。就显示一个null，可以得到表1的全部记录。

四、子查询

子查询可以理解为一个嵌套查询，对一个表查询得到的结果，接着对这些结果继续筛选接着查询。

4.1 带IN，EXISTS关键词查询

SELECT * FROM t_bookinfo WHERE t_bookinfo.bookType IN (SELECT id FROM t_booktype);

查询表1里面的图书类型 表2里面有这个类型才算符合条件

后面的sql语句返回的是一个集合，各个图书类型的集合，in的意思就是判断图书类型是否在这个集合里面

而EXISTS关键词查询得到的是一个逻辑值，空和非空，非空的情况下才执行外面也就是前面的查询

SELECT * from t_bookinfo WHERE EXISTS (SELECT * from t_booktype where id = 9);

后面这个子查询没查到记录 没有类型是9的类型 就不执行前面的操作

EXISTS关键词的作用就是来判断后面查询结果是否返回NULL

4.2 带ANY，ALL关键词查询

ANY 关键字表示满足其中任一条件 后面的子查询返回的集合只要有一个元素满足条件 就算是条件成立

ALL 关键字表示满足所有条件 后面子查询的集合，外层查询条件字段必须都大于集合里面每一个元素才算是条件成立

这里往表2里面再加一个字段用来练习ANY和ALL 如下

SELECT * FROM t_bookinfo WHERE bookPrice > ALL (SELECT price from t_booktype);

这里是ALL关键词，对查询到的集合，价格是都大于集合里每一个元素的。

SELECT * FROM t_bookinfo WHERE bookPrice > ANY (SELECT price from t_booktype);

而ANY的话只要价格大于集合里任意一个元素就算条件成立。

一个都满足条件，一个是满足任意一个条件成立即可。

五、合并查询结果

有两个关键词分别是UNION和UNION ALL，

UNION是对查询得到的记录合并去重复记录 显示出来

UNION ALL是不去重的操作合并

select bookPrice from t_bookinfo UNION  SELECT price from t_booktype;

这个可以看出去重后的结果两个表查询出来是没有重复的

这是不去重的 就是单纯的把两个表查询的所有记录罗列出来

select bookPrice from t_bookinfo UNION ALL SELECT price from t_booktype;

注意的是要合并两个表的查询结果，两个表选择的字段个数得相同，不能一个select两个字段，一个select三个字段，是没办法合并的。

六、表、字段取别名

给表取别名主要是在多表查询的时候，用别名代替表名，看着舒服点。

就是在from 表名的时候 后面加上别名   from 表名 别名

select * from t_bookinfo tbI, t_booktype tbt where tbI.bookType = tbt.id;

注意一下取别名之后用别名的时候别敲错了。

为字段取别名是为了输出的时候显示，有时候使用了聚合函数，显示的时候，那就是那个函数名字一堆的，我可以给这个字段娶一个别名使得显示不同。

直接在字段后面as别名就行了

SELECT  tbt.bookType, GROUP_CONCAT(tbi.bookName) as someBook, COUNT(tbi.bookName) as bookCount 
FROM t_bookinfo tbi, t_booktype tbt 
 WHERE tbi.bookType = tbt.id GROUP BY tbi.bookType;

统计一下各个类型下都有什么书 都有多少本书

注意这里输出显示的时候就是别名了而不是那个聚合函数的名字了。

下一篇：总结增删改查数据