MySQL入门(三) 数据库表的查询操作【重要】

序言

　1、MySQL表操作(创建表，查询表结构，更改表字段等)，

　2、MySQL的数据类型(CHAR、VARCHAR、BLOB,等)，

　本节比较重要，对数据表数据进行查询操作，其中可能大家不熟悉的就对于INNER JOIN(内连接)、LEFT JOIN(左连接)、RIGHT JOIN(右连接)等一些复杂查询。 通过本节的学习，可以让你知道这些基本的复杂查询是怎么实现的，但是建议还是需要多动手去敲，虽然理解了什么是内连接等，但是从理解到学会，是完全不一样的感觉。

　　　--WH

一、单表查询

　　　　　　1.1、查询所有字段

　　　　　　1.2、查询指定字段

　　　　　　1.3、查询指定记录

　　　　　　1.4、带IN关键字的查询

　　　　　　1.5、带BETWEEN AND 的范围查询

　　　　　　1.6、带LIKE的字符匹配查询

　　　　　　1.7、查询空值

　　　　　　1.8、带AND的多条件查询

　　　　　　1.9、带OR的多条件查询

　　　　　　1.10、关键字DISTINCT(查询结果不重复)

　　　　　　1.11、对查询结果排序

　　　　　　1.12、分组查询(GROUP BY)

　　　　　　1.13、使用LIMIT限制查询结果的数量

　　　集合函数查询

　　　　　　1.14、COUNT()函数

　　　　　　1.15、SUM()函数

　　　　　　1.16、AVG()函数

　　　　　　1.17、MAX()函数

　　　　　　1.18、MIN()函数　　　　

二、多表查询

　　　小知识

　　　　　　为表取别名

　　　　　　为字段取别名

　　　基于两张表　

　　　　　　2.1、普通双表连接查询

　　　　　　2.2、内连接查询

　　　　　　2.3、外连接查询

2.3.1、左外连接查询

2.3.2、右外连接查询

　　　　　　2.4、复合条件连接查询

　　　子查询

　　　　　　2.5、带ANY、SOME关键字的子查询

　　　　　　2.6、带ALL关键字的子查询

　　　　　　2.7、带EXISTS关键字的子查询

　　　　　　2.8、带IN关键字的子查询

　　　　　　2.9、带比较运算符的子查询

　　　　

　　　合并结果查询

　　　　　　2.10、UNION[ALL]的使用

三、使用正则表达式查询

　　　　　　3.1、查询以特定字符或字符串开头的记录

　　　　　　3.2、查询以特定字符或字符串结尾的记录

　　　　　　3.3、用符号"."来替代字符串中的任意一个字符

　　　　　　3.4、使用"*"和"+"来匹配多个字符

　　　　　　3.5、匹配指定字符串

　　　　　　3.6、匹配指定字符中的任意一个

　　　　　　3.7、匹配指定字符以外的字符

　　　　　　3.8、使用{n,}或者{n,m}来指定字符串连续出现的次数

四、综合案例 练习数据表查询操作

　　　　　　4.1、搭建环境

　省略

　　　　　　4.2、查询操作

　省略

　　　　　　4.3、在已经创建好的employee表中进行如下操作

4.3.1、计算所有女员工(F)的年龄

4.3.2、使用LIMIT查询从第3条记录开始到第六条记录

4.3.3、查询销售人员(SALSEMAN)的最低工资

4.3.4、查询名字以字母N或者S结尾的记录

4.3.5、查询在BeiJing工作的员工的姓名和职务

4.3.6、使用左连接方式查询employee和dept表

4.3.7、查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果

4.3.8、使用LIKE查询员工姓名中包含字母a的记录

4.3.9、使用REGEXP查询员工姓名中包含T、C或者M 3个字母中任意1个的记录

　　想直接做题的，跳过讲解，直接到练习区。

　　　　　　这张讲解的目录就是想上面这样，可以直接看自己感兴趣的部分，而不用从最基础的看起，接下来就一步步实现这上面庞大的工作量了。

一、单表查询

　　　　　　创建查询环境

CREATE TABLE fruits(

　　f_id CHAR(10) NOT NULL,

　　s_id INT NOT NULL,

　　f_name char(255) NOT NULL,

　　f_price DECIMAL(8,2) NOT NULL,

　　PRIMARY KEY(f_id)

);

解释：

　　　f_id：主键 使用的是CHAR类型的字符来代表主键

　　 s_id：这个其实是批发商的编号，也就是代表该水果是从哪个批发商那里过来的，写这个字段的目的是为了方便后面扩增表。

　　　f_name：水果的名字

　　　f_price：水果的价格，使用的是DECIMAL这个数据类型，如果不清楚这个类型去查看一下上面一篇讲解数据类型的文章。

　　　　　　　添加数据。

INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES

　　　('a1' , 101 , 'apple' , 5.2),

　　　('b1' , 101 , 'blackberry' , 10.2),

　　　('bs1' , 102 , 'orange' , 11.2),

　　　('bs2' , 105 , 'melon' , 8.2),

　　　('t1' , 102 , 'banana' , 10.3),

　　　('t2' , 102 , 'grape' , 5.3),

　　　('o2' , 103 , 'coconut' , 9.2),

　　　('c0' , 101 , 'cherry' , 3.2),

　　　('a2' , 103 , 'apricot' , 2.2),

　　　('l2' , 104 , 'lemon' , 6.4),

　　　('b2' , 104 , 'berry' , 7.6),

　　　('m1' , 106, 'mango' , 15.6),

　　　('m2' , 105 , 'xbabay' , 2.6),

　　　('t4' , 107, 'xbababa' , 3.6),

　　　('m3' , 105 , 'xxtt' , 11.6),

　　　('b5' , 107, 'xxxx' , 3.6 );

　　　　　　blackberry：黑莓　　melon：甜瓜　　grape：葡萄　　coconut：椰子　　cherry：樱桃　　apricot：杏子　　berry：浆果　　mango：芒果　　后面几个xbabay都是为了测试所编写的，没有实际意义。

　　　　　　注意：在复制我的代码到cmd窗口时，应注意语句之间不能有空格，不然会报错，我这里是为了使你们观看更清楚，所以每行前面度加有空格，

　　　　　　1.1、查询所有字段

　SELECT * FROM fruits;

　　

　　　解释：* 代表所有字段，也就是从表中将所有字段下面的记录度查询出来

　　　　　　1.2、查询指定字段

　　　查询f_name 和 f_price 字段的记录

　　　SELECT f_name, f_price FROM fruits;

　　

　　　　　　1.3、查询指定记录

　　　指定记录：也就是按条件进行查询，将满足一定条件的记录给查询出来，使用WHERE关键字

　　　SELECT * FROM fruits WHERE f_name = 'apple';　　//将名为apple的记录的所有信息度查询出来

　　

　　　SELECT * FROM fruits WHERE f_price > 15；　　　　//将价格大于15的记录的所有字段　　查询出来

　　

　　　　　　1.4、带IN关键字的查询

　　　IN关键字：IN(xx，yy，...) 满足条件范围内的一个值即为匹配项

　　　SELECT * FROM fruits WHERE f_name IN('apple','orange');

　　

　　　SELECT * FROM fruits WHERE s_id IN(101, 105); //s_id 为101 或者 105 的记录

　　

　　　SELECT * FROM fruits WHERE s_id NOT IN(101,105); //s_id 不为101或者105的记录

　　

　　　

　　　　　　1.5、带BETWEEN AND 的范围查询

　　　BETWEEN ... AND ... : 在...到...范围内的值即为匹配项，

　　　SELECT * FROM fruits WHERE f_price BETWEEN 5 AND 15;　　//f_price 在5到15之间，包括5和15。

　　

　　　SELECT * FROM fruits WHERE f_price NOT BETWEEN 5 AND 15;　　//f_price 不在5到15之间。

　　

　　　　

　　　　　　1.6、带LIKE的字符匹配查询

　　　　LIKE: 相当于模糊查询，和LIKE一起使用的通配符有 "%"、"_"

　　　　　　　　"%"：作用是能匹配任意长度的字符。

　　　　　　　　"_"：只能匹配任意一个字符

　　　　SELECT * FROM fruits WHERE f_name LIKE 'b%';　　//f_name以b字母开头的所有记录

　　

　　　　SELECT * FROM fruits WHERE f_name LIKE 'b%y';　　//f_name以b字母开头，y字母结尾的所有记录

　　

　　　　SELECT * FROM fruits WHERE f_name LIKE '____y'; 　　//此处有四个_，说明要查询以y字母结尾并且y之前只有四个字符的记录

　　

　　　　总结：'%'和'_'可以在任意位置使用，只需要记住%能够表示任意个字符，_只能表示一个任意字符

　　

　　　　　　1.7、查询空值

　　　　空值不是指为空字符串""或者0，一般表示数据未知或者在以后在添加数据，也就是在添加数据时，其字段上默认为NULL，也就是说，如果该字段上不插入任何值，就为NULL。此时就可以查询出来。

　　　　SELECT * FROM 表名 WHERE 字段名 IS NULL;　　//查询字段名是NULL的记录

　　　　SELECT * FROM 表名 WHERE 字段名 IS NOT NULL;　　//查询字段名不是NULL的记录

　　　　这里由于没有合适的数据，就不自己在创建表，添加数据，然后来测试这条语句了，很简单，看一下就应该懂了

　　　　　　1.8、带AND的多条件查询

　　　　AND: 相当于"逻辑与"，也就是说要同时满足条件才算匹配

　　　　SELECT * FROM fruits WHERE s_id = 101 AND f_price > 5;　　//同时满足s_id = 101、f_price >5 这两个条件才算匹配。

　　　　

　　　　　　1.9、带OR的多条件查询

　　　　OR: 相当于"逻辑或"，也就是说只要满足其中一个条件，就算匹配上了，跟IN关键字效果差不多

　　　　SELECT * FROM fruits WHERE s_id = 101 OR f_price > 10;　　//s_id =101 或者 f_price >10 ，只要符合其中一个条件，就算匹配

　　　　

　　　　　可以看到，查询出来的记录，f_price有低于10的，那么其肯定s_id=101，s_id不等于101的，其f_price肯定大于10，这就说明了OR的效果。只要满足其中一个条件，就算匹配。

　　　　　　1.10、关键字DISTINCT(查询结果不重复)

　　　　　SELECT s_id FROM fruits;　　//查询所有的s_id，会出现很多重复的值。

　　　　　　　　

　　　　　使用DISTINCT就能消除重复的值

　　　　　SELECT DISTINCT s_id FROM fruits;

　　　　　　　　

　　　　　将重复的值删除后，就只留下7条记录了。

　　　　　　　

　　　　　　1.11、对查询结果排序(ORDER BY)

　　　　　看上面输出的值没顺序，可以给他们进行排序。使用关键字 ORDER BY，有两个值供选择 DESC 降序 、 ASC 升序(默认值)

　　　　　SELECT DISTINCT s_id FROM fruits ORDER BY s_id;　　　　//默认就是升序，

　　　　　　　　

　　　　　SELECT DISTINCT s_id FROM fruits ORDER BY s_id DESC;　　//使用降序，也就是从高到底排列

　　　　　　　

　　　　　　1.12、分组查询(GROUP BY)

　　　　　分组查询很多人不知道什么意思，一开始我也是很蒙圈的，所以没关系，一起来看看。

　　　　　分组查询就是将相同的东西分到一个组里面去，现实生活中举个例子，厕所分男女，这也是一个分组的应用，在还没有分男女厕所前，大家度共用厕所，后面通过分男女性别，男的跟男的分为一组，女的和女的分为一组，就这样分为了男女厕所了。这就是分组的意思， 在上面对s_id进行查询的时候，发现很多重复的值，我们也就可以对它进行分组，将相同的值分为一组，

　　　　　SELECT s_id FROM fruits GROUP BY s_id;　　//将s_id进行分组，有实际意义，按批发商进行分组，从101批发商这里拿的水果度会放在101这个组中

　　　　　　　

　　　　　解释：将s_id分组后，就没有重复的值了，因为重复的度被分到一个组中去了，现在在来看看每个组中有多少个值

　　　　　SELECT s_id, COUNT(f_name), GROUP_CONCAT(f_name) FROM fruits GROUP BY s_id;

　　　　　　　　

　　　　　解释：

　　　　　　　COUNT():这个是下面要讲解到的一个函数，作用就是计算有多少条记录，　

　　　　　　　GROUP_CONCAT(): 将分组中的各个字段的值显示出来　

　　　　 SELECT s_id, COUNT(f_name), GROUP_CONCAT(f_name), GROUP_CONCAT(f_price) FROM fruits GROUP BY s_id; 　　　　　　　　

　　　　　　　　

　　　　　分组之后还可以进行条件过滤，将不想要的分组丢弃，使用关键字 HAVING

　　　　　SELECT s_id,COUNT(f_name),GROUP_CONCAT(f_name) FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1;//他能够过s_id分组，然后过滤出水果种类大于1的分组信息。

　　　　　　　　

总结：

　　　　　　　　知道GROUP BY的意义，并且会使用HAVING对分组进行过滤， HAVING和WHERE都是进行条件过滤的，区别就在于 WHERE 是在分组之前进行过滤，而HAVING是在分组之后进行条件过滤。

　　　　　　1.13、使用LIMIT限制查询结果的数量　　

　　　　　LIMIT[位置偏移量] 行数 通过LIMIT可以选择数据库表中的任意行数，也就是不用从第一条记录开始遍历，可以直接拿到 第5条到第10条的记录，也可以直接拿到第12到第15条的记录。 具体看下面例子

　　　　　SELECT * FROM fruits LIMIT 4;　　//没有写位置偏移量，默认就是0，也就是从第一条开始，往后取4条数据，也就是取了第一条数据到第4条的数据。

　　　　　　　

　　　　　SELECT * FROM fruits LIMIT 4,3;　　//从第5条数据开始，往后取3条数据，也就是从第5条到第8条

　　　　　　　

　　　　　注意:LIMIT的第一个参数不写默认就是0，也就是说，第一条记录的索引是0，从0开始的，第二个参数的意思是取多少行的记录，需要这两个才能确定一个取记录的范围

　　　集合函数查询

　　　　　　1.14、COUNT()函数

　　　　　这个函数在上面其实用过，作用是统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，

　　　　　　　COUNT(*):计算表中的总的行数，不管某列有数值或者为空值，因为*就是代表查询表中所有的数据行

　　　　　　　COUNT(字段名):计算该字段名下总的行数，计算时会忽略空值的行，也就是NULL值的行。

　　　　　SELECT COUNT(*) FROM fruits;

　　　　　　　　

　　　　　SELECT COUNT(f_name) FROM fruits;　　//查询fruits表中f_name字段名下有多少个行数，

　　　　　　　　

　　没有空值，所以计算出来的行数和总的记录行数是一样的。

　　　　　　1.15、SUM()函数

　　　　 SUM()是一个求总和的函数，返回指定列值的总和

　　　　　SELECT SUM(f_price) FROM fruits;　　//这个没有实际的意义，只是测试SUM()函数有求总和的能力

　　　　　　　

　如果有人觉得这个测试的不准，可以手动去加一下所有的f_price。然后来进行对比，反正我是信了。

　　　　　　1.16、AVG()函数

　　　　AVG()函数通过计算返回的行数和每一行数据的和，求的指定列数据的平均值(列数据指的就是字段名下的数据，不要搞不清楚列和行，搞不清就对着一张表搞清楚哪个是列哪个是行)，通俗点讲，就是将计算得来的总之除以总的记录数，得出一个平均值，

　　　　 SELECT AVG(f_price) FROM fruits;　　

　　　　　　　

　　就相当于　　116/16 = 7.25

　　　　　　1.17、MAX()函数

　　　　　MAX()返回指定列中的最大值

　　　　　 SELECT MAX(f_price) FROM fruits;

　　　　　　　　

　　　　　　1.18、MIN()函数　　　　

　　　　MIN()返回查询列中的最小值

　　　　 SELECT MIN(f_price) FROM fruits;

　　　　　　　

二、多表查询

　　　小知识

　　　　　　为表取别名

　因为是对两张表进行查询了，那么每次写表名的话就有点麻烦，所以用一个简单别名来代表表名

　 格式：表名 AS 别名

　 在下面的例子中会用的到，到时候不要不认识

　　　　　　为字段取别名

　给字段名取别名的原因是有些字段名是为了显示更加清楚，比如

　　　　　　　SELECT f_price AS '价格' FROM fruits;

　　　　　　　　

　　　　　　语句执行顺序问题

　　　一、sql执行顺序 、

　　　　　(1)from 　　
　　　　　(2) on
　　　　　(3) join
　　　　　(4) where
　　　　　(5)group by
　　　　　(6) avg,sum....
　　　　　(7)having
　　　　　(8) select
　　　　　(9) distinct
　　　　　(10) order by

　也就是说，我们每次执行的SQL语句，都是从FROM开始的。

　　　基于两张表　

　　　　　搭建查询环境，前面已经有一张表了，现在在增加一张suppliers(供应商)表和前面哪个fruits表创建练习，也就是说 让fruits中s_id字段值指向suppliers的主键值，创建一个外键约束关系。

　　　　　　　　CREATE TABLE suppliers　　

　　　　　　　　(

　s_id INT NOT NULL,

　s_name CHAR(50) NOT NULL,

　s_city CHAR(50) NULL,

　s_zip CHAR(10) NULL,

　s_call CHAR(50) NOT NULL,

　PRIMARY KEY(s_id)

　　　　　　　　);

　　　　　　　　其实这里并没有达到真正的外键约束关系，只是模拟，让fruits中的s_id中的值 能匹配到 suppliers 中的主键值，通过手动添加这种数据，来达到这种关系，反正是死数据，也不在添加别的数据，就不用建立外键约束关系了，这里要搞清楚

　　　　　　　　INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call)

　　　　　　　　VALUES

　　　　　　　　(101,'Supplies A','Tianjin','400000','18075'),

　　　　　　　　(102,'Supplies B','Chongqing','400000','44333'),

　　　　　　　　(103,'Supplies C','Shanghai','400000','90046'),

　　　　　　　　(104,'Supplies D','Zhongshan','400000','11111'),

　　　　　　　　(105,'Supplies E','Taiyuang','400000','22222'),

　　　　　　　　(106,'Supplies F','Beijing','400000','45678'),

　　　　　　　　(107,'Supplies G','Zhengzhou','400000','33332');

　　

　　　　　　2.1、普通双表连接查询

　问题：查询水果的批发商编号，批发商名字，水果名称，水果价格

　　　　分析：看下要求，就知道要查询两张表，如果需要查询两张表，那么两张表的关系必定是外键关系，或者类似于外键关系(类似于也就是说两张表并没有真正加外键约束，但是其特点和外键是一样的，就像上面我们手动创建的两张表一样，虽然没有设置外键关联关系，但是其特性跟外键关系是一样的。)

　 SELECT s.s_id,s.s_name,f.f_name,f.f_price FROM fruits AS f, suppliers AS s WHERE f.s_id = s.s_id;

　　　　

　　　　　解释：这里使用了表别名，并且这里是连接两张表的关系是在于 fruits中的s_id 等于 suppliers中的s_id。 这个大家度能理解把，也就是水果中记录了批发商的编号，通过该编号就能在suppliers表中找到对应的批发商的详细信息，就这样，两张表就联系起来了。

　　　　　注意：第一个执行的是FROM，所以上面为表取别名，在语句的任何地方的可以使用。

　　　　　　2.2、内连接查询

　　　　　知道了上面两张表基本的连接查询后，内连接查询就很简单了，因为内连接跟上面的作用是一样的，唯一的区别就是语法的不一样

　　　　　 格式：表名 INNER JOIN 表名 ON 连接条件

　　　　　 问题：查询水果的批发商编号，批发商名字，水果名称，水果价格

　　　　　　　 SELECT s.s_id,s.s_name,f.f_name,f.f_price

　　　　　　　 FROM fruits AS f INNER JOIN suppliers AS s

　　　　　　　 ON f.s_id = s.s_id;

　　　　　不知道这样写sql语句会不会让你们看的更清楚

　　

　　　　　

　　　还需要知道一个特殊一点的东西，那就是自连接查询，什么是自连接查询？就是涉及到的两张表都是同一张表。

　　　问题：查询供应f_id='a1'的水果供应商提供的其他水果种类？

　　　　　SELECT f2.f_id,f2.f_name

　　　　　FROM fruits AS f1 INNER JOIN fruits AS f2

　　　　　ON f1.s_id = f2.s_id AND f1.f_id = 'a1';

　　　　

　　　　　解释：把fruits表分开看成是两张完全一样的表，在f1表中找到f_id='a1'的s_id，然后到f2这张表中去查找和该s_id相等的记录，也就查询出来了问题所需要的结果。还有另一种方法，不用内连接查询，通过子查询也可以做到，下面会讲解，这里先给出答案，到时可以回过头来看看这个题。

　　　　　SELECT f_id,f_name

　　　　　FROM fruits

　　　　　WHERE s_id = (SELECT s_id FROM fruits WHERE f_id='a1');

　　　　

　　　　　效果和内连接是一样的，其实原理度是一样的，还是借助的两张表，只是这个更容易让人理解，可以通过这个来去理解上面那个自连接查询。

　　　　　　2.3、外连接查询

　　　　　内连接是将符合查询条件(符合连接条件)的行返回，也就是相关联的行就返回。

　　　　　 外连接除了返回相关联的行之外，将没有关联的行也会显示出来。

　　　　　 为什么需要将不没关联的行也显示出来呢？这就要根据不同的业务需求了，就比如，order和customers，顾客可以有订单也可以没订单，现在需要知道所有顾客的下单情况，而我们不能够只查询出有订单的用户，而把没订单的用户丢在一边不显示，这个就跟我们的业务需求不相符了，有人说，既然知道了有订单的顾客，通过单表查询出来不包含这些有订单顾客，不就能达到我们的要求吗，这样是可以，但是很麻烦，如何能够将其一起显示并且不那么麻烦呢？为了解决这个问题，就有了外连接查询这个东西了。

2.3.1、左外连接查询

　　　　　 格式： 表名 LEFT JOIN 表名 ON 条件； 返回包括左表中的所有记录和右表中连接字段相等的记录，通俗点讲，就是除了显示相关联的行，还会将左表中的所有记录行度显示出来。用例子来展示一下所说效果把。

　　　　　 由于上面我们所用到的fruits和suppliers中的记录都是设计好的，并没有哪个供应商没有提供水果，现在为了体现左外连接的效果，在suppliers中增加两条记录，fruits中并没有对应这两条记录得水果信息，

　　　　　 INSERT INTO suppliers VALUES(108,'suppliers T','Jiangxi','33333','11111');

　　　　　 INSERT INTO suppliers VALUES(109,'suppliers Y','Jiangxi','22222','44444');

　

　　　　　SELECT s.s_id,s.s_name,f.f_id,f.f_name

　　　　　FROM suppliers AS s LEFT JOIN fruits AS f

　　　　　ON s.s_id = f.s_id;

　

解释：

　　 suppliers表是在LEFT JOIN的左边，所以将其中所有记录度显示出来了，有关联项的，也有没有关联项的。这就是左外连接的意思，将左边的表所有记录都显示出来(前提是按照我们所需要的字段，也就是SELECT 后面所选择的字段)。如果将suppliers表放LEFT JOIN的右边，那么就不会在显示108和109这两条记录了。来看看

　　　　　SELECT s.s_id,s.s_name,f.f_id,f.f_name

　　　　　FROM fruits AS f LEFT JOIN suppliers AS s

　　　　　ON s.s_id = f.s_id;

　

　　　　　　　

2.3.2、右外连接查询

　　　　　 格式： 表名 RIGHT JOIN 表名 ON 条件 返回包括右表中的所有记录和右表中连接字段相等的记录

　　　　　 其实跟左外连接差不多，就是将右边的表给全部显示出来

　　　　　　　SELECT s.s_id,s.s_name,f.f_id,f.f_name

　　　　　　　FROM fruits AS f RIGHT JOIN suppliers AS s

　　　　　　　ON s.s_id = f.s_id;　　　　　//这条语句出来的结果是跟上面左外连接一样，就是调换了一下位置，其实效果还是一样的。

　

　　　　　　　 　　

　　　　　注意：

　　　　　　　LEFT JOIN 和 RIGHT JOIN这只是一种写法，其中还有另一种写法 LEFT OUTER JOIN 和 RIGHT OUTER JOIN .

　　　　　　　一般写这种复杂查询的时候，写sql语句的顺序应该是先从FROM　　

　　　　　　2.4、复合条件连接查询

　　　　　在连接查询(内连接、外连接)的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确，通俗点讲，就是将连接查询时的条件更加细化。

　　　　　 问题一：在fruits和suppliers表中使用INNER JOIN语法查询suppliers表中s_id为107的供应商的供货信息？

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM suppliers AS s INNER JOIN fruits AS f

ON s.s_id = f.s_id AND s.s_id = 107;

　

　　　　　问题二：在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果进行排序

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM suppliers AS s INNER JOIN fruits AS f

ON s.s_id = f.s_id

ORDER BY f.s_id;　　　　　　　　//对f.s_id进行升序。默认的是ASC，所以不用写。

　

　　　　　　对f.s_id进行排序其实也就是对s.s_id进行排序，效果是一样的，因为条件就是f.s_id=s.s_id。

　　　子查询

　　　　　　　　子查询，将查询一张表得到的结果来充当另一个查询的条件，这样嵌套的查询就称为子查询

　　　　　　2.5、带ANY、SOME关键字的子查询　　　　　

　　搭建环境

　　　　　 CREATE TABLE tb11 (num1 INT NOT NULL);

　　　　　 CREATE TABLE tb12 (num2 INT NOT NULL);

　　　　　 INSERT INTO tb11 VALUES(1),(5),(13),(27);

　　　　　 INSERT INTO tb12 VALUES(6),(14),(11),(20);

　　ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE，通俗点讲，只要满足任意一个条件，就返回TRUE。

　　　　 SELECT num1 FROM tb11 WHERE num1 > ANY(SELECT num2 FROM tb12);//这里就是将在tb12表中查询的结果放在前一个查询语句中充当条件参数。只要num1大于其结果中的任意一个数，那么就算匹配。

　　　SOME关键字和ANY关键字的用法一样，作用也相同，这里不做多讲解了。

　　　　　　2.6、带ALL关键字的子查询

　　　使用ALL时表示需要同时满足所有条件。

　　　　　　　SELECT num1 FROM tb11 WHERE num1 > ALL(SELECT num2 FROM tb12);　　//num1需要大于所有的查询结果才算匹配

　

　　　　　　2.7、带EXISTS关键字的子查询

　　　EXISTS关键字后面的参数是任意一个子查询，如果子查询有返回记录行，则为TRUE，外层查询语句将会进行查询，如果子查询没有返回任何记录行，则为FALSE，外层查询语句将不会进行查询。

　　　　　　　SLEECT * FROM tb11 WHERE EXISTS(SELECT * FROM tb12 WHERE num2 = 3);　　//查询tb12中有没有num2=3的记录，有的话则会将tb11的所有记录查询出来，没有的话，不做查询

　

　　　　　　2.8、带IN关键字的子查询

　　　 这个IN关键字的作用跟上面单表查询的IN是一样的，不过这里IN中的参数放的是一个子查询语句。

　　　　　　　SELECT s_id,f_id,f_name

　　　　　　　FROM fruits

　　　　　　　WHERE s_id IN(SELECT s_id FROM suppliers WHERE s_id = 107);

　

　　　　　　2.9、带比较运算符的子查询

　　　　除了使用关键字ALL、ANY、SOME等之外，还可以使用普通的比较运算符。来进行比较。比如我们上面讲解内连接查询的时候，就用过子查询语句，并且还是用的=这个比较运算符，这里就不做多解释了，可以往上面看一下　

　　　　

　　　合并结果查询

　　　　　　　　利用UNION关键字，可以将查询出的结果合并到一张结果集中，也就是通过UNION关键字将多条SELECT语句连接起来，注意，合并结果集，只是增加了表中的记录，并不是将表中的字段增加，仅仅是将记录行合并到一起。其显示的字段应该是相同的，不然不能合并。

　　　　　　2.10、UNION[ALL]的使用

　　　　　　　　UNION：不使用关键字ALL，执行的时候会删除重复的记录，所有返回的行度是唯一的，

　　　　　　　　 UNION ALL：不删除重复航也不对结果进行自动排序。

　　　　　　　　格式：

　　　SELECT 字段名,... FROM 表名

　　　UNION[ALL]

　　　SELECT 字段名,... FROM 表名

　　　　　　　　问题一：查询所有价格小于9的水果的信息，查询s_id等于101个103所有水果的信息，使用UNION连接查询结果

　　　SELECT s_id,f_name,f_price FROM fruits WHERE f_price < 9

　　　UNION ALL

　　　SELECT s_id,f_name,f_price FROM fruits WHERE s_id = 101 OR s_id=103;

　　　解释：显示的字段都是s_id,f_name,f_price，只是将两个的记录行合并到一张表中。仅仅增加的是记录行，而显示的字段还是那三个，没有增加，

　　　

　　　使用UNION，而不用UNION ALL的话，重复的记录就会被删除掉。

　

三、使用正则表达式查询

　　　　　　这个非常简单，就是使用REGEXP关键字来指定正则表达式，画一张表格，就能将下面所有的度覆盖掉。

　　　

　　　　　　3.1、查询以特定字符或字符串开头的记录

　　　SELECT * FROM fruits WHERE f_name REGEXP '^b';　　　　//以b开头的记录

　　　　

　　　就讲解这一个，下面这些的形式跟这个一模一样，唯一的差别就在正则表达式不一样，一般使用这种模糊查询，使用MySQL中的'_'和'%'就已经足够了。

　　　　　　3.2、查询以特定字符或字符串结尾的记录

　　　　　　3.3、用符号"."来替代字符串中的任意一个字符

　　　　　　3.4、使用"*"和"+"来匹配多个字符

　　　　　　3.5、匹配指定字符串

　　　　　　3.6、匹配指定字符中的任意一个

　　　　　　3.7、匹配指定字符以外的字符

　　　　　　3.8、使用{n,}或者{n,m}来指定字符串连续出现的次数

四、综合案例 练习数据表查询操作

　　　　　　4.1、搭建环境

　　　　　　　　两张表： employee(员工)表和dept(部门)表。

　　　　　CREATE TABLE dept

　　　　　(

　　　　　　　d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,　　　　//部门编号

　　　　　　　d_name VARCHAR(50),　　//部门名称

　　　　　　　d_location VARCHAR(100)//部门地址

　　　　　);

　　　　　 CREATE TABLE employee

　　　　　 (

　　　　　　　e_no INT NOT NULL PRIMARY KEY,　　　　//员工编号

　　　　　　　e_name VARCHAR(100) NOT NULL,　　　　//员工姓名

　　　　　　　e_gender CHAR(2) NOT NULL,　　　　　　//员工性别

　　　　　　　dept_no INT NOT NULL,//部门编号

　　　　　　　e_job VARCHAR(100) NOT NULL,　　　　//职位

　　　　　　　e_salary SMALLINT NOT NULL,　　　　　//薪水

　　　　　　　hireDate DATE,　　　　//入职日期

　　　　　　　CONSTRAINT dno_fk FOREIGN KEY(dept_no) REFERENCES dept(d_no)

　　　　　 );

表结构

　　　　

　　　　　　　　插入数据

　　　　INSERT INTO dept

　　　　VALUES

　　　　　　　(10,'ACCOUNTING','ShangHai'),

　　　　　　　(20,'RESEARCH','BeiJing'),

　　　　　　　(30,'SALES','ShenZhen'),

　　　　　　　(40,'OPERATIONS','FuJian');

　　　单词解释：ACCOUNTING：会计部门　　RESEARCH:研发部　　SALES:销售部　　OPERATIONS:实践部

　　　

　　　　INSERT INTO employee

　　　　VALUES

　　　　　　　(1001, 'SMITH' , 'm' , 20 , 'CLERK' , 800 , '2005-11-12'),

　　　　　　 (1002, 'ALLEN' , 'f' , 30 , 'SALESMAN' , 1600, '2003-05-12'),

　　　　　　　(1003, 'WARD' , 'f' , 30 , 'SALESMAN' , 1250, '2003-05-12'),

　　　　　　 (1004, 'JONES' , 'm' , 20 , 'MANAGER' , 2975, '1998-05-18'),

　　　　　　　(1005, 'MARTIN' , 'm' , 30 , 'SALESMAN' , 1250, '2001-06-12'),

　　　　　　 (1006, 'BLAKE' , 'f' , 30 , 'MANAGER' , 2850, '1997-02-15'),

　　　　　　　(1007, 'CLARK' , 'm' , 10 , 'MANAGER' , 2450, '2002-09-12'),

　　　　　　 (1008, 'SCOTT' , 'm' , 20 , 'ANALYST' , 3000, '2003-05-12'),

　　　　　　　(1009, 'KING' , 'f' , 10 , 'PRESIDENT' , 5000, '1995-01-01'),

　　　　　　 (1010, 'TURNER' , 'f' , 30 , 'SALESMAN' , 1500, '1997-10-12'),

　　　　　　　(1011, 'ANAMS' , 'm' , 20 , 'CLERK' , 1100, '1999-10-15'),

　　　　　　 (1012, 'JAMES' , 'f' , 30 , 'CLERK' , 950, '2008-06-15');

　　　单词解释：SALESMAN:销售员　　CLERK:普通职员　　MANAGER:经理　　PRESIDENT:董事长　　ANALYST:分析师 m:male 男性　　f:female 女性

　　　　

　　　　　　4.2、查询操作 　　　　

4.2.1、在employee表中，查询所有记录的e_no，e_name和e_salary字段值

　　　　　　　SELECT e_no,e_name,e_salary FROM employee;

　　　　

4.2.2、在employee表中，查询dept_no等于10和20的所有记录

　　　　　　　方式一：SELECT * FROM employee WHERE dept_no IN(10,20);

　　　　

　　　　　　　方式二：SELECT * FROM employee WHERE dept_no = 10 OR dept_no = 20;

　　　　

4.2.3、在employee表中，查询工资范围在800~2500之间的员工信息

　　　　　　　SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

　　　　

4.2.4、在employee表中，查询部门编号为20的部门中的员工信息

　　　　　　　SELECT * FROM employee WHERE dept_no = 20;

　　　　

4.2.5、在employee表中，查询每个部门最高工资的员工信息

　　　　　分析：每个员工都可能处于不同的部门，要先找出每个部门中的所有员工，应该想到分组，将相同部门的员工分为一组。然后在使用MAX()函数比较最大的salary。注意不要MAX(GROUP_CONCAT(e_salary)), 这样写就会报错，GROUP_CONCAT(e_salary)是将分组中所有的e_salary显示出来，我们直接MAX(e_salary)就会将该分组中所有的e_salary进行比较，拿到最大的一份。

　　　　　　　SELECT e_no,e_name,MAX(e_salary) FROM employee GROUP BY dept_no;

　　　　　　

　　　　　　　我尝试过其他方法，但是最终还是要对在相同部门中的员工做比较，只有通过分组才能对相同部门中的员工做比较，如果不分组，那么将对全部的记录进行比较。

4.2.6、查询员工BLAKE所在部门和部门所在地

　　　　　分析：看下题目就应该知道是两张表，也就应该使用连接查询，找出是用内连接还是外连接，然后找出连接条件，

　　　方式一：SELECT d.d_no,d.d_name,d.d_location

　　　　　　　 FROM employee AS e,dept AS d

　　　　　　　 WHERE e.e_name = 'BLAKE' AND e.dept_no = d.d_no;

　　　　

　　　方式二：SELECT d.d_no,d.d_name,d.d_location

　　　　　　　 FROM employee AS e INNER JOIN dept AS d

　　　　　　　 ON e.e_name = 'BLAKE' AND e.dept_no = d.d_no;

　　　　

4.2.7、使用连接查询，查询所有员工的部门和部门信息

　　　　　分析：这个题跟上面哪个题差不多，使用内连接

　　　　　SELECT e.e_no,e.e_name,d.d_no,d.d_name,d.d_location

　　　　　FROM employee AS e INNER JOIN dept AS d

　　　　　ON e.dept_no = d.d_no;

　　　　

4.2.8、在employee中，计算每个部门各有多少员工

　　　　　分析：每个部门用分组

　　　　　SELECT COUNT(e.e_name)

　　　　　FROM employee AS e

　　　　　GROUP BY e.dept_no　　

　　　 　　　　　

　　　　　　注意：SUM()和COUNT()要分清楚，SUM()是计算数值总和的，COUNT()是计算总的记录行数的。

4.2.9、在employee表中，计算不同类型职员的总工资数

　　　　　分析：对员工职位类型进行分组

　　　　　SELECT e.e_job,SUM(e.e_salary)

　　　　　FROM employee AS e

　　　　　GROUP BY e.e_job;

　　　　　　

4.2.10、在employee表中，计算不同部门的平均工资

　　　　　分析：对部门进行分组

　　　　　 SELECT e.dept_no,AVG(e.e_salary)

　　　　　 FROM employee AS e

　　　　　 GROUP BY e.dept_no;

　　　　　　

4.2.11、在employee表中，查询工资低于1500的员工信息

　　　　　　SELECT * from employee WHERE e_salary < 1500;

　　　　　　　　

4.2.12、在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列

　　　　　　SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC;

　　　　　　

4.2.13、在employee表中，查询员工姓名以字母A或S开头的员工信息

　　　　　　方式一：　SELECT * FROM employee WHERE e_name LIKE 'A%' OR e_name LIKE 'S%';

　　　　　　

　　　　　　方式二：SELECT * FROM employee WHERE e_name REGEXP '^A' OR e_name REGEXP '^S';

　　　　　　

4.2.14、在employee表中，查询到目前为止，工龄大于等于10年的员工信息

　　　　　　方式一：SELECT * FROM employee WHERE (YEAR(NOW()) - YEAR(hireDate)) > 10;　　

　　　　　　

　　　　　　方式二：SELECT * FROM employee WHERE (YEAR(CURDATE()) - YEAR(hireDate)) > 10;　

　　　　　 　

　　　　　　　解释：记得我们前一节学的数据类型吗，其中CURDATE()代表的是YYYY-MM-DD, NOW()代表的是YYYY-MM-DD HH:MM:SS，所以这里两个度能用，只要将其截取为为YEAR,然后相减，就能得到相差几年了。

　　　　　　4.3、在已经创建好的employee表中进行如下操作

4.3.1、计算所有女员工(F)的年龄(从入职到现在的时间)

　　　　　　　SELECT e.e_name,e.e_gender,YEAR(CURDATE()) - YEAR(e.hireDate)

　　　　　　　 FROM employee AS e

　　　　　　　 WHERE e.e_gender = 'f';

　　　　　　

4.3.2、使用LIMIT查询从第3条记录开始到第六条记录

　　　　　　　　SELECT * FROM employee LIMIT 2,3;　

　　　　　　

4.3.3、查询销售人员(SALSEMAN)的最低工资

　　　　　　　　SELECT MIN(e_salary)

　　　　　　　　 FROM employee

　　　　　　　　 WHERE e_job = 'SALESMAN';　

　　　　　　　　

4.3.4、查询名字以字母N或者S结尾的记录

　　　　　　　　方式一：

　　　SELECT * FROM employee WHERE e_name LIKE '%N' OR e_name LIKE '%S';　

　　　　　　　　

　　　　　　　　方式二：

　　　SELECT * FROM employee WHERE e_name REGEXP 'N$' OR e_name REGEXP 'S$';

　　　　　　　　

4.3.5、查询在BeiJing工作的员工的姓名和职务

　　　　　　　方式一：SELECT e_name,e_job FROM employee WHERE dept_no = (SELECT d_no FROM dept WHERE d_location = 'BeiJing');

　　　　　　　　

　　　　　　　方式二：SELECT e.e_name, e.e_job FROM employee AS e,dept AS d WHERE e.dept_no = d.d_no AND d.d_location = 'BeiJing';

　　　　　　　　

　　　　　　　方式三：SELECT e.e_name,e.e_job FROM employee AS e INNER JOIN dept AS d ON e.dept_no = d.d_no AND d.d_location = 'BeiJing';

　　　　　　　　

4.3.6、使用左连接方式查询employee和dept表

　　　　　　　SELECT * FROM employee LEFT JOIN dept ON employee.dept_no = dept.d_no;

　　　　　　　　

4.3.7、查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果

　　SELECT * FROM employee WHERE YEAR(hireDate) BETWEEN 2001 AND 2005

　　 UNION

　　 SELECT * FROM employee WHERE dept_no IN(20,30);

　　　　　　　　

4.3.8、使用LIKE查询员工姓名中包含字母a的记录

　　SELECT * FROM employee WHERE e_name LIKE 'a%' OR e_name LIKE '%a' OR e_name LIKE '%a%';　

　　　　　　　　　　　

4.3.9、使用REGEXP查询员工姓名中包含T、C或者M 3个字母中任意1个的记录

　　SELECT * FROM employee WHERE e_name REGEXP '[TCM]';

　　　　　　　　

总结：

　　　　　　花了我一天多的时间写这篇文章，收获很大，总结以下几点

　　　　　　1、在写复杂查询的时候，也就是涉及到两张表时，先写FROM，然后在写别的，要知道SQL语句的执行顺序

　　　　　　2、单表查询不是很难，记住几个特点的，LIKE、GROUP BY很重要。记住他的用法

　　　　　　3、两张表的查询也就是内连接、外连接，外连接包括左外连接和右外连接，理解了这几个，基本上就没什么难处

　　　　　　4、多练，把我写的全部自己实现一遍，基本上你就能够全部理解透彻了，并且在做题的过程中慢慢就会自己总结一些做题的经验。

　希望能对大家有所帮助，如果有帮助，就请顺手点个推荐把，哈哈。