继MySQL的大致介绍后,我们来看看MySQL中的查询语句怎么写。
建库及导入数据
在这之前,如果我们还没有学习DML和DDL,我们就需要一些数据来帮助我们学习DQL,而这些数据如下:
dept(部门)表,里面的字段分别为部门编号、部门名称、位置
emp(员工)表,里面的字段分别为员工编号、员工名称、职位、上级领导编号,雇佣日期,工资,补贴,部门编号
salgrade(工资等级)表,里面的字段分别为等级,最低界限,最高界限
以后我们都以这些数据来进行示例。
首先我们需要学会建库,建库的语句很简单:
create database 库名;
比如,我需要建一个名为ccb的库:
与之对应的删库为:
drop database 库名;
比如,我需要删除一个名为ccb的库:
建完库后,我们就需要导入数据,这时候,我们需要一种扩展名为.sql的文件(这种文件我们称为sql脚本,里面存放大量的sql语句),文章中使用的表可以在公众号中发送【MySQL源文件】获取。之后用选中库后用以下的语句:
source sql文件;
将以上提到的三个表格导入到相应的库中。
导入后用show tables;检查下是否成功导入了,如果有那三张表后,我们就可以开始来学DQL了。
简单查询语句最基本的查询格式为:
select
字段名 [as 重命名字段名],
字段名 [as 重命名字段名],
...
from
表名;
[]里面的内容可以省略,我们可以选择我们需要的字段来查看数据,如:
从中我们还可以看出在SQL语句中不区分大小写。那[]里面是干什么的呢?其实是在输出时给某个字段重命名,如:
当然,as是可以省略的:
*一般都是表示通配符,所以想看整个表的数据就用
select * from 表名;
如查询dept表中所有内容:
但用*的效率比较低,所以不建议使用。
条件查询语句格式为:
select
字段名 [as 重命名字段名],
字段名 [as 重命名字段名],
...
from
表名
where
条件;
那么有哪些条件可用呢?
1.>、>=、
2.<>、!= 都表示不等于
3. and or 并且,或者
从以上可以看出在MySQL中,字符串是用''括起来,那么能不能用""呢?
答案是可以的,但因为''是所有数据库都支持的,""不是所有数据库都支持,所以在MySQL中我们一般都用''把字符串括起来。并且标准的sql语句要求字符串用''括起来。
4.between...and... 在两者之间,是一个闭区间
可以看出between and 和上面的>= and <=等价。
5.not 取反,和Java中的!取反一样,这里就不再演示
6、in、not in
可以看出in和上面的or其实也差不多,括号里面放需要的取值。而not in就是不在那个集合里面的元素,也不再演示。
7、is null、 is not null 为空、不为空
在MySQL中,表示数据为空用NULL表示,那有时候我们需要找出为NULL的数据这时候就需要用到它。而is not null就是找出不为空的,也不演示。
8、like 后面跟一个字符串,可以用来进行模糊查询
其中,%表示匹配任意多的字符;而_表示匹配一个字符
而如果模糊搜索中需要%或者_的话,我们就需要用反斜杠来进行转义了,如:
'%\%%' 匹配包含%的文本
'%\_%' 匹配包含_的文本
对于条件查询,以上就是最常见的一些条件了。
上述提到了NULL这个为空的关键字,在这里,我们还需要知道的一点是在运算中,如果加进去了数据为空的单元格,那么计算的结果永远都是空的。比如:
可以看到,工资加补贴应该都是有值的,但因为有数据为空的参与运算,所以结果还是为空。
去重查询语句格式为:
select distinct
字段名 [as 重命名字段名],
字段名 [as 重命名字段名],
...
from
表名;
可以看出,去重其实就是在select后面加distinct而已,如:
而需要注意的是,distinct只能出现在字段的前面,并且查询多个字段去重时,多个字段相同时才去重。如:
排序查询语句格式为:
select
字段名 [as 重命名字段名],
字段名 [as 重命名字段名],
...
from
表名
order by
字段名 [asc/desc],
[字段名 [asc/desc],]
...;
其中asc表示升序:
desc表示降序:
1、如果最后的asc/desc不写的话会怎么样?
可以看出,默认的排序规则为升序。
2、可能我们在查询多个字段时,有多个排序规则。比如出现这种情况:在查询ENAME和SAL两个字段时,我想先通过SAL的降序排序,然后在相等的SAL中通过ENAME的降序排列,那么我们应该怎么写呢?
只需要看哪个排序的优先级高,写在排序的前面就行。
3、如果我们在select的时候,对表格重命名了,那么写重命名后的名字还能实现嘛?或者写重命名之前的名字还能实现吗?
可以看出其实两者都是可以的。
4、最后,对于排序查询,还有一个可以取巧的地方,我们可以将以上的字段名改成字段在select后面出现的下标,也是可以达到同样的效果:
以上我们用sal排序,sal在select后面的第三个位置出现,我们就可以写3来代替sal。
函数
对于MySQL,也有自己的一套常用的函数,感兴趣的读者可以去网上查看,这里就不再一一列举。这里拿常用的函数来说明。
多行处理函数
最常用的就是聚合函数了:
max,min,avg,sum,count。
顾名思义,我们通过字面意思可以知道上述函数的作用分别为:求最大值,求最小值,求平均值,求和,求记录条数。也叫做分组函数/多行处理函数。
那么该如何使用呢?我们看一些简单的例子,如:求所有员工的工资总和:
求所有员工最大工资:
从上面的例子可以看出,分组函数有个特点就是多行输入,一行输出。而且自动忽略NULL(上述求和的时候有NULL值,但是最终结果不是NULL)。因此又引出了count(*)和count(字段)的区别:
可以看到,count(*)计算全部记录条数,而count(字段)计算该字段所有非空记录条数。
再来个例子,求工资大于平均工资的员工,应该怎么写呢?是这样?
select
ename,sal
from
emp
where
sal > avg(sal);
我们可以复制过去看看:
会发现报错了,为什么呢?等我们等下看完分组查询语句之后就知道了。
单行处理函数
有多行处理函数,就有单行处理函数,如ifnull函数,这种函数的特点是n行输入,n行输出。
ifnull函数用法:传入两个参数,第一个传入字段,第二个表示如果这个字段的值为null就替换为指定的值。如:
可以看到,上面的ifnull就把comm为null的单元格都变为0了。
分组查询语句格式为:
select
字段名 [as 重命名字段名],
字段名 [as 重命名字段名],
...
from
表名
group by
字段名
having
字段名;
通过字面意思也可以大致上知道group by就是指定按字段进行分组,而having其实就是在group by分组的基础上再次进行分组。例如:
找出每个职位的最高工资:
找出每个职位最高工资大于1500的职位:
对于group by和having,具有这样的关系:有group by才有having!而且在严格的角度来说,在经过group by分组后,select之后所加的字段只能是分组函数和分组的字段。但在MySQL中,不满足这个条件是可以运行的,但是在其他数据库中就可能会报错了!
到这里,我们的DQL语句基本上都有了,它们之间可以相互组成形成更复杂的DQL语句,这里我们看下这个语句组合起来后的执行顺序:
5、select
..
1、from
..
2、where
..
3、group by
..
4、having
..
6、order by
..;
可以看到,group by是在where语句之后执行的,而且分组函数是在group by语句之后执行的(和group by连用,没有group by时整个表数据自成一组)。因此,这就解释了上述那个分组函数最后一个例子报错的原因了,因为where语句先执行,如果where里面有分组函数,但因为分组函数又是在group by后执行的,所以就报错了!因此,分组函数不能在where中使用!
最后,因为先走条件语句,之后再走分组语句,所以我们之后能先在where语句中排除一些数据的话,可以大大提高执行效率,还是以找出每个职位最高工资大于1500的职位为例:
select job, max(sal) maxsal from emp group by job having maxsal > 1500;
其实等价于:
select job, max(sal) maxsal from emp where sal > 1500 group by job;
看上面的运行时间是0.01s,而下面的为0.00s。可能现在还看不出区别,但以后就很重要了。
连接查询语句因为数据都放在同一张表的话会造成数据冗余,所以一般数据都是放在很多张表中的,而连接查询就是联合着其他表一起查询的语句。
连接查询有两种划分方式:
1、按年代划分:
SQL92
SQL99
2、按连接方式划分:
内连接:等值连接、非等值连接、自连接
外连接:左外连接、右外连接
全连接(MySQL没有)
其中,SQL92支持内连接,而SQL99支持内连接和部分外连接。
在多表查询的时候会出现一种现象:笛卡尔积现象(简单说明就是比如两张表进行查询,表1有m行,表2有n行,结果集有m*n行,然后再进行筛选),造成这种现象的原因是因为连接查询中没有条件进行过滤,所以需要两两进行比较。加了条件筛选,就可以避免笛卡尔积现象了,但是!不会减少记录的匹配次数(=参与查询的表的行数乘积)
对于SQL92,这里看一个简单的例子来方便理解:求员工的名字和所在部门的信息。
在多表查询的时候一般都会将表重命名,这样效率高。
接下来,就看看按连接方式划分的那些连接的格式:
内连接
格式:
...
表1
[inner] join
表2
on
连接条件
where
筛选条件
...
以上就是内连接的基本格式了,而等值连接就是条件是等量关系,非等值连接就是条件是非等量关系,自连接就是表1和表2是同一张表,例如:
其余的以此类推,这里就不再举例。
外连接
格式:
...
表1
(left/right) [outer] join
表2
on
连接条件
where
筛选条件
...
以上就是外连接的基本格式了,那么什么是左外连接,什么是右外连接呢?我们先看看内连接——它是一种没有主副表之分的连接,凡是表之间能匹配上的记录都是能查出来的。而外连接就有主副表之分了,用外连接就是主要查询主表中的数据,但副表没有数据和主表匹配时,就会生成null与之匹配。因此,和内连接不一样的是,外连接在join前面加了left/right来区分哪一个是主表哪一个是副表,(在以上的两个表中,如果是left,则表1是主表;如果是right,则表2是主表)。
这里为了便于看出外连接和内连接的区别,举一个看似不恰当的例子:求每个员工的补贴是在哪个薪水等级
内连接:
外连接:
这是不是就应证了上面那些话,有主表会用null补全。
全连接
全连接就是左右都是主表。在MySQL中没有全连接,但是我们可以用union来实现,等下讲union的时候就明白了。
以上的例子都是两张表进行的,那么如果要进行多张表的话,那该怎么做呢?其实很简单,格式如下:
...
表1
join
表2
on
连接条件
join
表3
on
连接条件
...
以此类推,用什么查询直接在join前面加就可以了,这里就不再举例。
子查询语句简单理解就是select语句中继续嵌套select语句,而被嵌套的select语句就称为子查询。
常见的子查询主要位于以下几个位置:
1、select后面
2、from后面
3、where后面
接下来,我们简单看下这三种的简单举例:
求每个部门中的员工个数:
找出每个部门平均工资等级:
找出工资大于平均工资的员工:
以上就是简要的子查询的概念,要完成一个子查询就必须先把问题拆解为若干个,然后去拼接。如找出工资大于平均工资的员工,我们就可以先求平均工资(也就是位于where中的子查询),然后再求大于这个平均工资的员工。
分页查询语句格式为:
select
...
[order by]
limit [offset, ] length;
其实就是在之前的DQL语句的结尾中再加一个limit关键字,其中offset表示起始位置,默认为0(第一条记录就为0),而length表示一页中显示几条记录。而且limit只能写在语句的最后!比如:
找出所有员工的第1~5条的记录(第一页):
找出所有员工的第6~10条的记录(第二页):
找出所有员工的第11~15条的记录(第三页):
从以上我们可以看出这样的一个规律:
如果每页显示的记录数为length,那么,第num页的limit的数值就写(num-1)*length, lengh;
到这,我们又可以更新下执行顺序的问题了:
5、select
..
1、from
..
2、where
..
3、group by
..
4、having
..
6、order by
..
7、limit
..;
联合查询语句格式为:
查询语句1
union
查询语句2;
用于将查询结果集相加。用union的时候必须要保证列数相同,否则会报错:
而且如果没有要求将表命名的话,结果字段名会显示第一张表的字段名,如:
可以看到上面是员工名,下面是部门名,而字段名就是第一张表字段名。
那么怎么用union来实现全连接呢?我们也一样来看一个例子,我们要显示所有部门和所有员工的关系,这时候我们就需要用全连接了(用emp作为主表,可能对应不上部门编号,会被淘汰;用dept作为主表,可能对应不上员工,也会被淘汰):
(以上的emp表中多了一个LiHua,其deptno没有对应dept表中的已有的deptno)
可以看到,结果就可以看到所有部门和所有员工的关系了(部门编号为40没有对应员工所以为null,员工名称为LiHua没有对应部门所以也为null)。
到这,基本的DQL语句就差不多结束了,从以上我们也知道了SQL语句都是以;结尾的。