编程之路之数据库mysql(六)- SELECT查询语句详解
一、SELECT查询语句配置
SELECT [选项子句] 字段表达式子句 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]
注:有[]表示可以省略
重要:各个子句的顺序是固定的,不能随便调整顺序。
1、字段表达式子句
表示我们需要查询哪些内容。
*表示所有的字段
也可以指明需要哪些字段
也可以使用函数
字段或函数调用可以使用别名,如
Studname as name
Count(*) as num
AS关键字可以省略,如
Studname as name写成 studname name
还可以是表达式
字段也可以作为函数的参数或参与运算
2、选项子句
有两个值,all|distinct,代表所有|去除重复。默认是all
不指定则返回所有的。
增加distinct会把重复的记录去除
注意:重复是指整个记录的内容相同
3、FROM子句
FROM子句用于指定数据源。
数据源可以是多个,中间用逗号分开。
4、特殊的数据源,dual
Dual是一个虚拟的数据源,完全是为了语法匹配。
5、Where子句
Where子句通常是用于条件判断。
比较运算符
> 大于
< 小于
>=
<=
!= <>
=
逻辑运算符
&& and
|| or
!
Xor
上面使用and,如果改成or
举一个优先级的例子
In和not in
In: 当数据和集合中的某个数据相等时叫in
Not in :当数据和集合中的所有数据都不相等叫not in
当比较的字段没有NULL数据时,in + not in等于所有的数据。
Is null和is not null
两种方式加起来,应该是所有的记录
Between和Not between
注意:between是包含边界值的(判断的是一个范围)
Between和not between加起来是所有记录
7、Group by子句
对记录进行分组。Group by子句通常需要使用一些统计函数辅助分组。
1.group by处怎么写?
Group by 准备对其分组的字段1,准备对其分组的字段2
2.字段表达式处怎么写?
通常先写上被分组的字段列表,后面跟分组的函数
统计函数:count() sum() avg() max() min()
分组的目的在于统计
还可以对多个字段进行分组的统计
8、回溯统计,with rollup
在原有group by语句基础上再加上with rollup
9、Having子句
通常用于对分组后的结果进行再次的过滤。
select * from studinfo having gender=1;
并不等于having可以代替where!
相当于省略了where子句和group by子句
不能用where代替having
各个子句是有顺序的,位置的固定的。
10、ORDER BY子句
用于排序的一个子句。
语法格式:order by 字段1 asc|desc, 字段2 asc|desc, …..
多个字段排序的规则:当某几条记录它的第一个字段的值相同时,将根据第2个字段的要求进行排序
Tip:如果不写asc或desc,默认是asc
11、LIMIT子句
用于分页。
语法格式有两种:
limit 起始记录位置,记录数
limit 记录数 相当于 limit 0, 记录数
tip:记录的起始位置是从0开始计算
如何计算起始位置的值:
$start = ($page-1)*$perpage
起始位置值=(第几页-1)*每页记录数
Order by对分页取出的记录有很大影响
二、联合查询
把多条查询语句的结果组合成一个查询结果,这种方式就叫做联合查询。
使用union关键字可以进行联合。
语法格式:
Select语句1
Union 选项distinct|all
Select语句2
Union
Select语句3
…..
选项:
Distinct代表去除重复的记录,是默认选项,可以不写
All 代表查询出来来的所有记录
联合查询特点:
1.各个SELECT语句查询出来的字段数量要一样多,不要求对应的字段类型一致
2.查询结果的字段名使用第1个SELECT语句的字段名
3.会自动去除重复的数据
3.当查询语句中含有order by和limit子句时,SELECT语句应该放到括号中
建议每一个SELECT语句都放括号中比较好
4.当需要对整个查询结果进行排序时,更需要把每个SELECT语句放到括号中,排序的字段名要使用第一个SELECT语句的字段名
5.当SELECT中只有order by 而没有limit子句时,order by会被优化掉,不起作用
三、子查询,subquery
当把一条SELECT语句放到另外一条SQL语句中时,称为子查询。
要求:找出所有同学中年龄最大的同学
如果使用排序加limit
但是仔细观察,年龄最大的同学不只一个时,就不知道limit多少了,所以这个方法不好
在这个情况,使用子查询是很好的选择
子查询可以得到最大年龄
把这句查询作为一个子查询放到select语句中就可以了
1、子查询的分类
按位置分:
WHERE型子查询:当子查询出现在WHERE子句中
FROM型子查询:当子查询出现在FROM子句中
按返回结果分:
标量子查询:返回结果是一行一列的数据
行子查询:返回结果是一行记录,一行多列
列子查询:返回结果是多行一列
表子查询:返回结果是一个表,多行多列
2、WHERE型子查询
上面的例子就是一个WHERE型子查询
3、FORM型子查询
子查询是出现在from子句中的,它是作为一个数据源的方式
子查询的结果是作为一个临时数据源,需要使用as关键字起一个别名
4、标量子查询
返回的是一行一列的数据
5、列子查询
返回结果是多行一列的数据(是同一字段的数据)
要求:查找出有学生的班级的详细信息
使用SQL,可以从学生表获取到有学生的班级的ID
再从班级表中得到班级的信息
列子查询通常使用: 字段名 in 子查询的结果
6、行子查询
要求:用子查询,查出班级ID为1的年龄最大,性别是男的学生资料
先使用查询找出班级ID为1的最小年龄数据
再把子查询作为条件,放到where子句中,比较时使用相应字段的集合=的方式
7、表子查询
要求:找出所有班中年龄最小的学生的资料
先用子查询查到多生多列的结果
再把子查询作为条件
总结:
如果返回结果是标量或行,可以使用=进行比较
如果返回结果是列或表,需要使用in进行比较
四、In,some(),all()
比如这是一个使用in的例子
集合:(1,20) (2,18) (5,18)
In相当于 where 组合字段=集合数据1 or 组合字段1=集合数据2 or …..
Not in又是什么?
不等于集合中的任何一个数据
In是集合内,not in是集合外
Some()它和in很相似,也是表示数据等于我里面的一些数据就合格
结果相同,只不同some()是函数形式
再看in和not in
In和not in会得到两部分的结果,一个是条件内的,一个条件外的
In有not in和它对应,那么=some()是不是和!=some()对应呢?!=some()是不是和not in一样呢?
!=some()含义是数据不等于集合中的某些数据
有一个a 集合是(b,c,d)
A!=b or a!=c or a!=d
我们会发现,!=some()无法得到有用的结果
结论:!=some()是没有实际意义的,只有=some()相当于in比较有用
提示:除了=some()和!=some(),其实还可以>some()
=All() 要求数据要等于集合的所有数据(除非集合内只有一个数据)
!=all() 要求数据不能等于集合内的任何一个数据,有点象not int
结论:可以认为!=all()和not in是具有相同作用的
五、条件判断函数exists()
比如:查询有学生的班级的信息
可以通过子查询
还可以通过使用exists()条件判断函数
两种方法都得到了有学生班级的班级资料
通过判断exists()的返回结果是true代表前面查询的记录是合格,如果为false,则前面查询的记录是不合格。
六、连接查询,join
连接查询就把多个表进行拼接。如A表有5个字段,B表有4个字段,则连接后的查询结果一共有5+4=9个字段。
连接查询的方式:
内连接:inner join
外连接:又分成left join左外连接,right join右外连接
交叉连接:cross join
自然连接:又分为natural inner join,natural left join,natural right join
语法格式:
SELECT 字段列表 FROM A表 [inner join|left join|right join|cross join|natural inner join|natural left join|natural right join] B表 on 连接条件
1、内连接,inner join
两个表的数据将根据连接条件判断是否是合格数据,是则做两个表数据的连接。
如果两个表中凡是不符合连接条件的数据,都将被过滤掉,不会出现在结果中。
体会:一对多的关系
Classinfo是一表。Studinfo是多表。
那么如果不加连接条件呢?(inner join是允许不加连接条件的)
不加连接条件,将产生一个笛卡尔积。结果的记录数等于两个表记录数的乘积。
内连接语法上可以省略inner关键字
2、外连接之左外连接,left join
以左表为主
特点:
左表的数据会全部出现,右表符合连接的数据将会与左表数据进行对接。
左表中找不到连接条件的记录,右表数据部分将补NULL
右表的数据如果有不满足连接条件的记录不会出现
3、外连接之右连接,right join
以右表为主
观察结果,发现和左连接类似。
特点:
右表的数据会全部出现,左表符合连接的数据将会与右表数据进行对接。
右表中找不到连接条件的记录,左表数据部分将补NULL
左表的数据如果有不满足连接条件的记录不会出现
把classinfo作为右表为主的情况
结论:
不管左连接右连接,一表的数据通常会有重复情况。多表通常是出现一次。
举例:
商品信息表,含有商品分类
商品信息表是多表(使用分类),好多商品可以是同一分类
商品分类表是一表,分类是唯一的
当列出商品列表时,肯定是把商品信息表(多表)放到左边,left join商品分类表(一表)
好处:主表(当前左表是主表)的记录将全部出现
提示:left和right不能省略。
4、交叉连接,cross
两个表的记录进行交叉对接。
根据条件,成立则进行记录的对接。
如果不加条件,同样是一个笛卡尔积。
5、连接方式,using
前面我们都是使用的on进行连接。
还有一种连接方式叫using
当进行inner join时,使用using,会
连接条件字段将会放到第一的位置
连接条件字段现在只出现一次了(on是出现两次)
合格的数据会进行对接
使用using的要求:两个表在连接条件字段必须名称相同
6、自然连接之自然内连接,natural join
自然连接 相当于 内连接 + using()
7、自然左连接,natural left join
结果是左连接一样,只不过连接字段是自己发现的,连接字段写到最前面了。
同样,左表中找不到 匹配数据的记录,右表部分补NULL
8、自然右连接,natural right join
和自然左连接类似。
举个例子
足球比赛,主客场
1.球队信息表(球队id, 球队名称)
2.比赛结果表(主队ID,客队ID,比分)
球队信息表
球队ID |
球队名称 |
1 |
国安队 |
2 |
恒大队 |
3 |
鲁能队 |
比赛信息表
ID |
主队ID |
客队ID |
比分 |
1 |
1 |
2 |
0:2 |
2 |
1 |
3 |
3:1 |
3 |
2 |
1 |
2:0 |
4 |
2 |
3 |
3:5 |
5 |
3 |
1 |
7:1 |
6 |
3 |
2 |
1:1 |
创建数据表并插入记录
查询显示格式
主队名 比分 客队名
如果不要队名,很好查,比赛信息表中就有
但是我们要求显示是球队名称
如果left join球队表一次
将得到一个加宽了字段数量的新表,join上来的球队是主队的名称
双进行left join,则会把客队的名称连接上来
我们指定字段名
我是小咖
发个邀请:
如果你正好想学习php,可以与我一起交流,我的VX:feilueze333。下面资料免费赠送。