怎么做可以把网站图片保存下来,织梦手机网站源码下载,暴雪公司现状,wordpress 黑链文章目录1、简单查询2、去除单列的重复结果查询3、去除多列的重复结果查询4、限制查询结果条数5、对查询结果排序#xff08;1#xff09;按照单个列的值进行排序#xff08;2#xff09;按照多个列的值进行排序6、带搜索条件查询#xff08;1#xff09;简单搜索条件查询…
文章目录1、简单查询2、去除单列的重复结果查询3、去除多列的重复结果查询4、限制查询结果条数5、对查询结果排序1按照单个列的值进行排序2按照多个列的值进行排序6、带搜索条件查询1简单搜索条件查询2匹配列表中的元素查询3匹配NULL值查询4多个搜索条件查询aAND 操作符bOR 操作符c通配符d转义通配符7、表达式和函数查询1表达式2函数a文本处理函数b日期和时间处理函数c数值处理函数d聚集函数ⅰcount函数ⅱmax函数ⅲmin函数ⅳsum函数ⅴavg函数ⅵ给定搜索条件下聚集函数的使用ⅶ聚集函数中distinct的使用ⅷ组合聚集函数8、分组查询1带有where子句的分组查询2作用与分组的过滤条件a分组列b作用与分组的聚集函数3分组和排序4嵌套分组5注意事项9、简单查询语句中各个子句的顺序10、子查询1标量子查询2列子查询3行子查询4表子查询5exists和not exists子查询6不相关子查询和相关子查询11、连接查询1连接过程2外连接和内连接a外连接ⅰ左外连接语法ⅱ右外连接语法b内连接c小结3多表连接12、组合查询1涉及单表组合查询2涉及不同表的组合查询3包含或去除重复的行4组合查询中的order by和limit子句1、简单查询
# select * from 表名;
select * from tb_student;# select 列1,列2,列3,... from 表名;
select number,name,sex from tb_student;# select 列名 [as] 列的别名 from 表名;
select number as 学号 from tb_student;2、去除单列的重复结果查询
有时候我们查询某个列的数据时会有一些重复的结果比如我们查询tb_student表的学院信息就会查询出很多重复的结果我们可以将distinct放在被查询的列前边这样就可以去掉重复的数据
# select distinct 列名 from 表名;
select distinct department from tb_student;3、去除多列的重复结果查询
# select distinct 列名1,列名2,... from 表名;
select distinct department, major from tb_student;4、限制查询结果条数
有时候查询结果条数会很多都显示出来可能会撑爆屏幕所以MySQL给我们提供了一种限制结果集中的记录条数的方式就是在查询语句的末尾使用这样的语法
# limit 开始行, 限制条数;
select * from tb_student limit 0, 2;开始行指的是我们想从第几行数据开始查询 限制条数是结果集中最多包含多少条记录 注这里的开始行可以不填默认开始行是0。 5、对查询结果排序
1按照单个列的值进行排序
# order by 列名 asc|desc
select * from tb_student_score order by score asc;
select * from tb_student_score order by score;上面这两种写法效果是一样的。 ASC是指按照指定列的值进行由小到大进行排序也叫升序 DESC是指按照指定列的值进行由大到小进行排序也叫降序 注 中间的“|”表示这两种方式只能选一个 asc/desc不填的话默认使用的是asc 2按照多个列的值进行排序
# order by 列1 asc|desc, 列2 asc|desc ...
select * from tb_student_score order by subject asc,score desc;
select * from tb_student_score order by subject, score desc;上面这两种写法效果是一样的。 6、带搜索条件查询
1简单搜索条件查询
select * from tb_student where name davis;我们把搜索条件放在where子句中name davis’中的称之为比较操作符除了之外还提供了很多别的比较操作符。比如
操作符示例描述a ba等于b 或者 !a ba不等于ba ba小于ba ba小于或等于ba ba大于ba ba大于或等于bBETWEENa BETWEEN b AND c满足 b a cNOT BETWEENa NOT BETWEEN b AND c不满足 b a c2匹配列表中的元素查询
有的时候搜索条件中指定的匹配值并不是单个值而是一个列表只要匹配到列表中的某一项就算匹配成功这种情况可以使用IN操作符
操作符示例描述INa IN (b1, b2, …)a是b1b2…中的某一个NOT INa NOT IN (b1, b2, …)a不是b1b2…中的任意一个
select * from tb_student where number IN (100101, 100102);select * from tb_student where number NOT IN (100101, 100102);3匹配NULL值查询
我们前边说过NULL代表没有值意味着你并不知道该列应该填入什么数据在判断某一列是否为NULL的时候并不能单纯的使用操作符而是需要专业判断值是否是NULL的操作符
操作符示例描述IS NULLa IS NULLa的值是NULLIS NOT NULLa IS NOT NULLa的值不是NULL
select * from tb_student where name IS NULL;select * from tb_student where name IS NOT NULL;4多个搜索条件查询
aAND 操作符
select * from tb_student where number 100101 and name davis;AND操作符把两个搜索条件连接起来表示只有当两个条件都满足的记录才能被加入到结果集。 bOR 操作符
select * from tb_student where number 100101 or name davis;在给定多个搜索条件的时候某条记录在符合某一个搜索条件的时候就将其加入结果集中。 c通配符
有时候我们并不能精确的描述我们要查询的哪些结果比方说我们只是想看看姓“王”的学生信息而不能精确的描述出这些姓“王”的同学完整姓名我们称这种查询为模糊查询。MySQL中使用下边两个操作符来支持模糊查询
操作符示例描述likea like ba匹配bnot likea not like ba不匹配b
既然我们不能完整的描述要查询的信息那就用某个符号来替代这些模糊的信息这个符号就被称为通配符。
MySQL中支持下边两个通配符
通配符示例描述%王%任意一个字符串_王_任意一个字符
# 两个字以及两个字以上的姓王的名字都会匹配出来
select * from tb_student where name like 王%;# 只有两个字的姓王的名字会被匹配出来
select * from tb_student where name like 王_;注 LIKE或者NOT LIKE操作符只用于字符串匹配。 通配符不能代表NULL如果需要匹配NULL的话需要使用IS NULL或者IS NOT NULL。 d转义通配符
如果待匹配的字符串本身就包含普通字符’%‘或者’_该咋办怎样区分他是一个通配符还是一个普通字符呢
答如果匹配字符串中需要普通字符’%‘或者’_‘的话需要在他们前边加上一个反斜杠’来和通配符区分开来也就是说 \%代表普通字符’%’ \_代表普通字符’_’ 7、表达式和函数查询
1表达式 2函数
我们在使用MySQL过程中经常会有一些需求比方说将给定文本中的小写字母转换成大写字母把某个日期数据中的月份值提取出来等等为了解决这些常见的问题设计MySQL的大叔贴心的为我们提供了很多所谓的函数比如 UPPER函数是用来把给定的文本中的小写字母转换成大写字母 MONTH函数是用来把某个日志数据中的月份值提取出来 NOW函数用来获取当前的日期和时间
我们使用这些函数可以在函数名后加一个小括号()就好表示调用一个这个函数简称函数调用。针对某些包含参数的函数我们可以在小括号里将参数填入即可。
下面来介绍一些常用的MySQL内置函数 a文本处理函数
名称调用示例示例结果描述leftleft(‘abc123’, 3)abc给定字符串从左边取指定长度子串rightright(‘abc123’, 3)123给定字符串从右边取指定长度子串lengthlength(‘abc’)3给定字符串的长度lowerlower(‘ABC’)abc给定字符串的小写格式upperupper(‘abc’)ABC给定字符串的大写格式ltrimltrim(’ abc’)abc给定字符串左边空格去除后的格式rtrimrtrim(abc )abc给定字符串右边空格去除后的格式substringsubstring(‘abc123’, 2, 3)bc1给定字符串从指定位置截取指定长度子串concatconcat(‘abc’, ‘123’, ‘xyz’)abc123xyz将给定的各个字符串拼接成一个新字符串
我们在前面的表达式说过函数调用也算是一种表达式的操作数他可以和其他操作数用操作符连接起来组成一个表达式来作为查询列表的一部分或者放到搜索条件中我们以concat函数为例
select concat(学号为, number, 的学生成绩是, score) as 成绩描述 from tb_student_score;b日期和时间处理函数
名称调用示例示例结果描述nowleft(‘abc123’, 3)2021-12-10 16:18:20返回当前日期和时间curdatecurdate()2021-12-10返回当前日期curtimecurtime()16:18:20返回当前时间datedate(‘2021-12-10 16:18:20’)2021-12-10将给定日期和时间值的日期提取出来date_adddate_add(‘2021-12-10 16:18:20’, interval 2 day)2021-12-12 16:18:20将给定的日期和时间值添加指定的时间间隔date_subdate_sub(‘2021-12-10 16:18:20’, interval 2 day)2021-12-08 16:18:20将给定的日期和时间值减去指定的时间间隔datediffdatediff(‘2021-12-10’, ‘2021-12-11’)-1返回两个日期之间的天数第二个参数 - 第一个参数date_formatdate_format(now(), ‘%m-%d-%Y’)12-10-2021用给定的格式显示日期和时间
在使用date_add和date_sub这两个函数时需要注意增加或减去的时间间隔单位是可以自己定义的下面是MySQL支持的一些时间单位
时间单位描述microsecond毫秒second秒minute分钟hour小时day天week星期month月quarter季度year年
如果我们想让2021-12-10 16:18:20这个时间值增加2分钟可以这样写
select date_add(2021-12-10 16:18:20, interval 2 minute);在使用date_format函数是需要注意我们可以通过一些所谓的格式符来自定义日期和时间的显示格式下面是MySQL中常用的一些日期和时间的格式符以及他们对于的含义
格式符描述%b简写的月份名称Jan、Feb、…、Dec%D带有英文后缀的月份中的日期0th、1st、2nd、…、31st%d数字格式的月份中的日期01、02、03、…、31%f微秒000000 - 999999%H二十四小时制的小时00 - 23%h十二小时制的小时01 - 12%i数值格式的分钟00 -59%M月份名January、February、…、December%m数值形式的月份00 - 12%p上午或下午AM代表上午、PM代表下午%S秒00 - 59%s秒00 - 59%W星期名Sunday、Monday、…、Saturday%w周内第几天0星期日、1星期一、…、6星期六%Y4位数字形式的年2021%y2位数字形式的年21c数值处理函数
下面列举一些数学上常用到的函数
名称调用示例示例结果描述absabs(-1)1取绝对值pipi()3.141593返回圆周率coscos(pi())-1返回一个角度的余弦expexp(1)2.718281828459045返回e的指定次方modmod(5, 2)1返回除法的余数randrand()0.7537623539136372返回一个随机数sinsin(pi()/2)1返回一个角度的正弦sqrtsqrt(9)3返回一个数的平方根tantan(0)0返回一个角度的正切d聚集函数
统计一下表中的行数某一列数据的最大值是什么我们把这种函数称之为聚集函数下面介绍MySQL中常用的几种聚集函数
函数名描述count返回某列的行数max返回某列的最大值min返回某列的最小值sum返回某列值之和avg返回某列的平均值注聚集函数这个名不太直观把它理解为统计函数可能更容易理解。 ⅰcount函数
有两种使用方式 count(*)对表中行的数目进行计数不管列的值是不是NULL count(列名)对特定的列进行计数会忽略掉该列为NULL的行
select count(*) from tb_student;select count(name) from tb_student;ⅱmax函数
select max(score) from tb_student_score;ⅲmin函数
select min(score) from tb_student_score;ⅳsum函数
select sum(score) from tb_student_score;ⅴavg函数
select avg(score) from tb_student_score;ⅵ给定搜索条件下聚集函数的使用
聚集函数并不是一定要统计一个表中的所有记录我们可以指定搜索条件来限定这些聚集函数作用的范围比如统计某门课程的平均分
select avg(score) from tb_student_score where subject 英语;ⅶ聚集函数中distinct的使用
默认情况下聚集函数将计算指定列的所有非NULL数据如果我们指定的列中有重复数据的话可以选择使用distinct来过滤掉这些重复数据。
select count(distinct major) from tb_student;ⅷ组合聚集函数
这些聚集函数可以集中在一个查询中使用
select count(*) as 记录总数, max(score) as 最高成绩, min(score) as 最低成绩, avg(score) as 平均成绩 from tb_student_score;8、分组查询
要按照不同的课程来统计平均分我们只需在GROUP BY子句中添加上分组列就好了MySQL会帮助我们自动建立分组来方便我们统计信息具体语句如下
select subject_id, avg(score) from tb_student_score group by subject_id;这个查询的执行过程就是按照subject_id中的值进行分组的分别对每个分组中记录的score列调用avg函数进行数据统计。
在使用分组的时候必须要意识到分组的存在仅仅是为了方便我们分别统计各个分组中的信息所以我们只需要把分组列和聚集函数放到查询列表处就好当然如果非分组列出现在查询列表中就会出现报错例如下面这种写法就会报错
select number, subject_id, avg(score) from tb_student_score group by subject_id;1带有where子句的分组查询
上边的例子是将表中的每条记录都划分到某个分组中我们可以在划分分组的时候就将某些记录过滤掉这时就需要使用where子句了比如老师觉得各个科目的平均分太低了想把分数低于60分的记录去掉之后再统计平均分就可以这样写
select subject_id, avg(score) from tb_student_score where score 60 group by subject_id;这个过程可以分成两个步骤 1、将记录进行过滤后分组 2、分别对各个分组进行数据统计 2作用与分组的过滤条件
有时候某个带有group by子句的查询中可能会产生非常多的分组如果我们不想在结果集中得到这么多记录只想把那些符合某些条件的分组加入到结果集从而减少结果集中记录的条数那就需要把针对分组的条件放到having子句中比方说老师想要查询平均分大于80分的课程那么就可以这样写
select subject_id, avg(score) from tb_student_score group by subject_id having avg(score) 80;其实这里所谓的针对分组的条件一般是指下边这两种
a分组列
也就是我们可以把用于分组的列放到having子句的条件中比如这样
select subject_id, avg(score) from tb_student_score group by subject_id having subject_id 1;b作用与分组的聚集函数
并不是having子句中只能放置在查询列表出现的那些聚集函数只要是针对这个分组进行统计的聚集函数都可以比方说老师想查询最高分大于98分的课程的平均分那么可以这样写
select subject_id, avg(score) from tb_student_score group by subject_id having max(score) 98;其中的max(score)这个聚集函数并没有出现在查询列表中但仍然可以作为having子句中表达式的一部分。 3分组和排序
如果我们想对各个分组查询出来的统计数据进行排序需要为查询列表中有聚集函数的表达式添加 别名比如想按照各个学科的平均分从大到小降序排序可以这样写
select subject_id, avg(score) as avg_score from tb_student_score group by subject_id order by avg_score desc;4嵌套分组
有时候按照某个列进行分组太笼统一个分组内可以被继续划分成更小的分组比如学生可以先按照学院进行分组然后在根据专业来继续分组从而划分成更小的分组我们把这种对大的分组下继续分组的情形叫做 嵌套分组。
我们只需在group by子句中把各个分组列依次写上用逗号“,”分隔开就好了例如
select department, major, count(*) from tb_student group by department, major;注在嵌套分组中聚集函数将作用在最后一个分组列上。 5注意事项
使用分组来统计数据给我们带来了非常大的便利但是要随时提防有坑的地方 如果分组列中有NULL值那么NULL也会作为一个独立的分组存在 如果存在多个分组列也就是嵌套分组聚集函数将作用在最后的那个分组列上 如果查询语句中存在where子句和order by子句那么group by子句必须出现在where子句之后order by子句之前 非分组列不能单独出现在检索列表中可以被放到聚集函数中 group by子句后也可以跟随表达式但不能是聚集函数 where子句在分组前进行过滤作用于每一条记录where子句过滤掉的记录将不包括在分组中而having子句在数据分组后进行过滤作用于整个分组。 9、简单查询语句中各个子句的顺序
如果在一个查询语句中出现了多个子句那么他们之间的顺序是不能乱放的顺序如下
select [distinct] 查询列表
[from 表名]
[where 布尔表达式]
[group by 分组列表]
[having 分组过滤条件]
[order by 排序列表]
[limit 开始行, 限制条数]其中中括号[]中的内容表示可以省略我们在书写查询语句的时候各个子句必须严格遵守这个顺序不然会报错。 10、子查询
截止到目前我们介绍的查询语句都是作用于单个表的但是有时候会有从多个表中查询数据的需求。
1标量子查询
在tb_student表中拿学生的姓名查找出该学生的学号然后拿这个学号到tb_student_score表中查询该学号的成绩这个子查询的结果只有一个值这种子查询称之为标量子查询。
select * from tb_student_score where number (select number from tb_student where name davis);标量子查询单纯的代表一个值由标量子作为的操作数组成的搜索条件只要符合表达式语法就可以。 2列子查询
如果我们想查询某个专业的学生的成绩我们需要先从tb_student表中根据专业ID找到对应的学生学号然后在通过学号找到tb_student_score表中找到对应的成绩信息这样我们需要两条查询语句第二条查询语句的搜索条件也是用到了第一条查询语句的查询结果我们可以把第一条查询语句作为内层查询把第二条语句作为外层查询来将这两个查询语句合并为一个查询语句
select * from tb_student_score where number in (select number from _tb_student where major_id 1);3行子查询
只要子查询的结果集中最多只包含一条记录而且这条记录中有超过一个列的数据如果该条记录只包含一个列的话该子查询就成了标量子查询那么这个子查询就可以被称之为行子查询。
select * from tb_student_score where (id, number) (select id, number from tb_student limit 1);我们在子查询语句中加了limit 1这个子句意味着子查询最多只能返回一条记录所以该子查询就可以被看作一个行子查询。 注在想要得到标量子查询或行子查询但又不能保证子查询的结果集中只有一条记录时应该使用limit 1子句来限制记录数量。 4表子查询
如果子查询结果集中包含多行多列那么这个子查询也可以被称之为表子查询。
select * from tb_student_score where (id, number) in (select id, number from tb_student where id 10);5exists和not exists子查询
有时候外层查询并不关心子查询中的结果是什么而只关心子查询的结果集是不是空集这时可以用到下边这两个操作符
操作符示例描述existsexists (select …)当子查询结果集不是空集时表达式为真not existsnot exists (select …)当子查询结果集是空集时表达式为真
例如
# 查询一个不存在的学号number
select * from tb_student_score where exists (select * from tb_student where number 1001010101);我们查询了一个不存在的学号子查询的结果集是一个空集于是exists表达式的结果为false所以外层查询也就不查了直接返回一个Empty set表示没有结果。 6不相关子查询和相关子查询
前边介绍的子查询和外层查询都没有依赖关系也就是说子查询可以独立运行并产生结果之后在拿结果作为外层查询的条件去执行外层查询这种子查询称为 不相关子查询。
有时候我们需要在子查询的语句中引用到外层查询的值这样的话子查询就不能当作一个独立的语句去执行这种子查询被称为 相关子查询。
比方说我们想看一些学生的基本信息但是前提是这些学生在tb_student_score表中有成绩记录那么可以这样写
select number, name, id_number from tb_student where exists (select * from tb_student_score where tb_student_score.number tb_student.number);查询过程如下 先执行外层查询获得tb_student表的第一条记录把查询到的number值当作参数传入到子查询此时子查询的意思是判断tb_student_score表中的number字段是否存在这个值该值存在的话那么tb_student表的第一条记录可以被加入到结果集不存在的话记录就不会被加入到结果集中。 与上边类似。 … tb_student表中没有更多记录了结束查询。 11、连接查询
1连接过程
连接的本质就是把各个表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
select t1.*, t2.* from t1, t2;连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合像这样的结果集就可以称之为 笛卡尔积。
如果我们乐意我们可以连接任意数量张表但是如果没有任何限制条件这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100 * 100 * 100 1000000 行数据所以在连接的时候过滤掉特定记录组合是有必要的在连接查询中的过滤条件可以分两种 涉及单表的条件这种只涉及单表的过滤条件我们之前都提到过一万遍了我们之前一直称为搜索条件比如 t1.m 1 是只针对 t1 表的过滤条件 t2.n ‘d’ 是只针对 t2 表的过滤条件。 涉及两表的条件这种过滤条件我们之前没有见过比如 t1.m t2.m 、 t1.n t2.n 等这些条件中涉及到了两个表我们稍后会仔细分析这种过滤条件是如何用的。
下边我们就要看一下携带过滤条件的连接查询的大致执行过程了比方说下边的查询语句
select * from t1, t2 where t1.m1 1 and t1.m1 t2.m2 and t2.n2 d;2外连接和内连接
比如说查询各个同学的各科成绩可是有个问题有的同学因某些原因没有参加考试所以在成绩表中没有对应的成绩记录那如果老师想查看所有同学的考试成绩即使是缺考的同学也应该展示出来但是到目前为止我们介绍的连接查询是无法完成这样的需求的我们稍微思考一下这个需求其本质是想驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。为了解决这个问题就有了 内连接 和 外连接 的概念。 对于 内连接 的两个表驱动表中的记录在被驱动表中找不到匹配的记录该记录不会加入到最后的结果集我们上边提到的连接都是所谓的内连接。 对于 外连接 的两个表驱动表中的记录即使在被驱动表中没有匹配的记录也仍然需要加入到结果集。
根据选取的驱动表的不同外连接仍然可以细分为2种
左外连接选取左侧的表为驱动表。右外连接选取右侧的表为驱动表。 a外连接
可是这样仍然存在问题即使对于外连接来说有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。那么把过滤条件分为两种就解决了放在不同地方的过滤条件是有不同语义的 where子句中的过滤条件where子句中的过滤条件就是我们平时见的那种不论是内连接还是外连接凡是不符合where子句中的过滤条件的记录都不会被加入最后的结果集。 on子句中的过滤条件对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配on子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用NULL值填充。
需要注意的是这个on子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把记录加入结果集这个场景下提出的所以如果把on子句放到内连接中MySQL会把它和where子句一样对待也就是说内连接中的where子句和on子句是等价的。
一般情况下我们都把只涉及单表的过滤条件放到where子句中把涉及两表的过滤条件都放到on子句中我们也一般把放到on子句中的过滤条件也称之为 连接条件。 ⅰ左外连接语法
左外连接语法还是挺简单的比如我们要把t1表和t2表进行左外连接查询可以这样写
select * from t1 left [outer] join t2 on 连接条件 [where 普通过滤条件];其中中括号里的outer单词是可以省略的。对于left join类型的连接来说我们把放在左边的表称之为外表或者驱动表右边的表称之为内表或者被驱动表。上述例子中t1就是外表或者驱动表t2就是内表或者被驱动表。需要注意的是对于左外连接和右外连接来说必须使用on子句来指出连接条件。
看看怎样写查询语句才能把所有的学生的成绩信息都查询出来即使是缺考的考生也应该被放到结果集中
select tb_student.number, name, subject, score from tb_student left join tb_student_score on tb_student.number tb_student_score.number;ⅱ右外连接语法
右外连接和左外连接的原理是一样的语法也只是把left换成right而已
select * from t1 right [outer] join t2 on 连接条件 [where 普通过滤条件];只不过驱动表是右边的表被驱动表是左边的表。 b内连接
内连接和外连接的根本区别就是在驱动表中的记录不符合on子句中的连接条件时不会把该记录加入到最后的结果集我们最开始唠叨的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法就是直接把需要连接的多个表都放到from子句后边。其实针对内连接MySQL提供了好多不同的语法我们以t1和t2表为例
select * from t1 [inner | cross] join t2 [on 连接条件] [where 普通过滤条件];也就是说在MySQL中下边这几种内连接的写法都是等价的
select * from t1 join t2;
select * from t1 inner join t2;
select * from t1 cross join t2;上边的这些写法和直接把需要连接的表名放到from语句之后用逗号“,”分隔开的写法是等价的
select * from t1, t2;现在我们虽然介绍了很多种内连接的书写方式不过熟悉一种就好这里我们推荐 inner join 的形式书写内连接因为 inner join 语义很明确可以和 left join 和 right join 很轻松的区分开。这里需要注意的是由于在内连接中on子句和where子句是等价的所以内连接中不要求强制写明on子句。 c小结
连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表两表连接产生的笛卡尔积肯定是一样的。
而对于 内连接 来说由于凡是不符合on子句或where子句中的条件的记录都会被过滤其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去所以对于内连接来说驱动表和被驱动表是可以互换的并不会影响最后的查询结果。
对于 外连接 来说由于驱动表中的记录即使在被驱动表中找不到符合on子句连接条件的记录也会被加入结果集所以此时驱动表和被驱动表的关系就很重要也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。 3多表连接
select * from t1 inner join t2 inner join t3 where t1.m1 t2.m2 and t1.m1 t3.m3;select * from t1 inner join t2 on t1.m1 t2.m2 inner join t3 on t1.m1 t3.m3;其实不管是多少个表的连接本质上就是各个表的记录在符合过滤条件下的自由组合。
12、组合查询
我们之前说的都是单条查询语句其实多条查询语句产生的结果集也可以被合并成一个大的结果集这种将多条查询语句产生的结果集合并起来的查询方式称之为合并查询或者组合查询。
1涉及单表组合查询
select m1 from t1 where m1 2;
select m1 from t1 where m1 2;如果我们想把上边两个查询语句的结果合并到一个大的结果集中最简单的方式是使用or操作符把两个查询语句中的搜索条件连接起来就像这样
select m1 from t1 where m1 2 or m1 2;除了这种方式我们还可以使用union来将两个查询语句连在一起就像这样
select m1 from t1 where m1 2 union select m1 from t1 where m1 2;并不是说使用union连接起来的各个查询语句的查询列表处只能有一个表达式有多个表达式也是可以的只要数量相同就可以了比如下边这个使用union连接起来的各个查询语句的查询列表处都有2个表达式
select m1, n1 from t1 where m1 2 union select m1, n1 from t1 where m1 2;查询结果
------------
| m1 | n1 |
------------
| 1 | a |
| 3 | c |
------------注使用union连接起来的各个查询语句的查询列表中位置相同的表达式的类型应该是相同的。当然这不是硬性要求如果不匹配的话MySQL将会自动的进行类型转换比方说下面这个组合查询语句
select m1, n1 from t1 where m1 2 union select n1, m1 from t1 where m1 2;查询结果
------------
| m1 | n1 |
------------
| 1 | a |
| c | 3 |
------------使用union连接起来的两个查询中第一个语句的查询列表是m1, n1第二个查询语句的查询列表是n1, m1我们应该注意 第一个查询的查询列表处的m1和第二个查询的查询列表的n1对应第一个查询的查询列表处的n1和第二个查询的查询列表的m1对应m1和n1虽然类型不同但是MySQL会帮助我们自动进行必要的类型转换 这几个查询语句的结果集都可以被合并到一个大结果集中但是这个大的结果集总是要有展示一下列名吧所以就规定组合查询的结果集中显示的列名将以第一个查询中的列名为准上边的例子就采用了第一个查询中的m1n1作为结果集的列名。 2涉及不同表的组合查询
如果只在同一个表中进行组合查询貌似体现不出组合查询的强大很多情况下组合查询还是用在涉及不同表的查询语句中的比方说下边这两个查询
select m1, n1 from t1 where m1 2;
select m2, n2 from t2 where m2 2;第一个查询是从t1表中查询m1, n1这两个列的数据第二个查询是从t2表中查询m2, n2这两个列的数据我们可以使用union直接将这两个查询语句拼接到一起
select m1, n1 from t1 where m1 2 union select m2, n2 from t2 where m2 2;3包含或去除重复的行
我们看下边这两个查询
select m1, n1 from t1;
select m2, n2 from t2;查询结果
# t1表
------------
| m1 | n1 |
------------
| 1 | a |
| 2 | b |
| 3 | c |
------------# t2表
------------
| m2 | n2 |
------------
| 2 | b |
| 3 | c |
| 4 | d |
------------很显然t1表里有3条记录t2表里有3条记录我们使用union把他们合并起来
select m1, n1 from t1 union select m2, n2 from t2;查询结果
------------
| m1 | n1 |
------------
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
------------合并后的结果只剩下4条记录因为使用union来合并多个查询的记录会默认过滤掉重复的记录由于t1表和t2表都有2, b、3, c这两条记录所以合并后的结果集就把他俩去重了如果我们想要保留重复记录可以使用union all 来连接多个查询
select m1, n1 from t1 union all select m2, n2 from t2;查询结果
------------
| m1 | n1 |
------------
| 1 | a |
| 2 | b |
| 3 | c |
| 2 | b |
| 3 | c |
| 4 | d |
------------4组合查询中的order by和limit子句
组合查询会把各个查询的结果汇总到一块如果我们相对最终的结果集进行排序或者只保留几行的话可以在组合查询的语句末尾加上order by和limit子句就像这样
select m1, n1 from t1 union select m2, n2 from t2 order by m1 desc limit 2;查询结果
------------
| m1 | n1 |
------------
| 4 | d |
| 3 | c |
------------如果我们能为各个小的查询语句加上括号“()”那就更清晰了就像这样
(select m1, n1 from t1) union (select m2, n2 from t2) order by m1 desc limit 2;注由于最后的结果集展示的列名是第一个查询中给定的列名所以order by子句中指定的排序列也必须是第一个查询中给定的列名。 如果我们只想单独为各个小的查询排序而不为最终的汇总的结果集排序行不行呢
(select m1, n1 from t1 order by m1 desc) union (select m2, n2 from t2 order by m2 desc);查询结果
------------
| m1 | n1 |
------------
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
------------从结果来看我们为各个小查询加入的order by子句好像并没起作用这是因为设计MySQL规定组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的也就是说我们在各个小查询中加入order by子句的作用和没加一样但是我们只是单纯的想从各个小的查询中获取有限条排序好的记录加入最终的汇总那是可以的比如这样
(select m1, n1 from t1 order by desc limit 1) union (select m2, n2 from t2 order by m2 desc limit 1);查询结果
------------
| m1 | n1 |
------------
| 3 | c |
| 4 | d |
------------
