MySQL之表的查询和操作

分组函数/聚合函数/多行处理函数：

多行处理函数的特点：输入多行，最终输出一行。

分组函数在使用时候必须先进行分组，然后才能使用，如果你没有对数据进行分组，整张表默认为一组

举例：

select max(score) from tbsc;		//查询该表中的分数最大值

其他几个使用方法也是类似的，这里就不进行举例了。

分组函数在使用的时候需要注意的问题：

1：我们不需要和之前的单行处理函数一样，当参与的数据中包含NULL，如果不进行处理，会导致结果也为NULL，分组函数自动忽略NULL，将非NULL的值进行数据运算

NULL不是一个值，因此，我们也不能理解成=NULL/!=NULL,而是应该理解成 is NULL或者 not is NULL

2:分组函数中count(*)和count(具体的字段)的区别：

count(具体的字段):表示统计该字段下所有不为NULL的元素的总数

count(*)：统计表中的所有行数（只要有一行中某一列存在数据count则++）

那么是否存在有一行数据的所有列数都为NULL呢？

答案是不存在的，无论是在MySQL还是SQL中，都不会存在这样的列。

3：分组函数不能直接使用在where字句中

举例：

查询比最低分数高的分数：

select stuname,score from tbsc,tbstudent where score>min(score);

报错：

无效的使用了分组查询,原因是：分组函数出现在了where字句中

4：所有的分组函数可以组合起来一起使用。

举例：

//查询最高，最低，平均，总分数
select max(score),avg(score),min(score),sum(score) from tbsc;

查询结果：

分组查询：

什么是分组查询？

在实际的应用中，可能会有这样的需求，需要先进行分组，再对每一组的数据进行操作，这个时候我们需要使用分组查询，怎么进行分组查询呢？

语法格式：

select
.....
group by
.......

单个字段进行分组：

//查询每个课程对应的平均分数
select cosname,avg(score) from tbcourse,tbsc group by cosname;

查询结果：

关键字的顺序问题：

经过之前的学习，我们学习了很多的关键字，例如select， from， where， group by等等，那么如果将他们综合使用，他们的先后顺序是否可以颠倒？

答案是不可以的，在书写的时候，必须遵循以下顺序：

select
.......
from
.......
where
.......
group by
.......
order by
......

但是他们的执行顺序可不是根据这样的先后顺序执行的。

执行顺序如下：

from-->where-->group by-->select-->order by

执行顺序的限制，也就会导致分组函数不能直接使用在where字句中，原因是分组函数必须先分组再使用，而根据语法执行顺序，group by的执行顺序在where后面，因此分组函数不能直接使用在where后面。

那么我们上述提到的实例，也没有进行分组，为什么可以使用分组函数max呢？

select max(score) from tbsc;

原因是select的执行顺序在group by之后，其实已经进行分组了，才输出结果。

易错点：

select stuname,cosname,avg(score) from tbcourse,tbsc,tbstudent group by cosname;

查询结果：

根据查询结果，我们会发现，这样的查询是毫无意义的，为什么会这样说呢？原因是我们是根据课程进行分组，，但学生姓名和课程名之间完全没有关联，这样毫无意义的语法虽然在MySQL中可以正常查询，但是在Oracle中是没办法执行的，那么也就说明Oracle的语法比MySQL更为严谨一些。

结论：在进行分组查询时，select后面只能跟参加分组的字段和分组函数，跟其他不相关的字段是毫无意义的，在除了MySQL以外的其他数据库中会报错的

按两个字段进行分组：

//查询不同每门课程不同cid的最高分数
select cosname,cid,max(score) from tbsc,tbcourse group by cosname,cid;

查询结果：

where和having怎么选？

举例：

要求显示最低分数小于85的分数

方案一：

思路:首先根据课程进行分组，找到每个课程的最小值，再对找出的最小值进行深度筛选，选出小于85的分数

先分组再筛选：

select cosname,min(score) from tbcourse,tbsc group by cosname having min(score)<85;

having函数可以对分完组之后的数据进一步过滤，having不能单独使用，并且having不能代替where，having必须和group by搭配使用

方案二：

思路：先查找出每个课程的最小分数且最小分数必须小于85，最后根据课程名进行分组

先筛选再分组：

select cosname,min(score) from tbcourse,tbsc where score<85 group by cosname;

对比两个方案，很明显第一个方案的查找效率更低一些，它涉及的不相关数据更多一些。

因此，我们可以得出一个结论，where和group by，优先选择 where， where实在解决不了的，我们再选择group by

那么什么样的问题是where无法解决的呢？

答案就是：需要显示限制条件中包含分组函数的。

举例：

显示课程平均分数大于85的

select avg(score),cosname from tbsc,tbcourse group by cosname having  avg(score)>85;

有的人会产生疑惑这里真的用不了where吗？

select avg(score),cosname from tbsc,tbcourse where  score>85 group by cosname ;

这样不就好了吗？

你仔细查看这里虽然确实查询了每门课程的平均薪资，但是where后面的限制条件不知不觉被修改为了分数大于85，而不是平均分数。

因此该实例必须使用having。

单表查询总结：

select
.....
from
.....
where
......
having
......
order by
......

以上关键字只能根据这个顺序，不能进行先后颠倒。

执行顺序：

from–>where–>group by–>having–>select–>order by

综合应用：

找出每个课程的平均分数，要求显示除C语言程序设计之外平均分数大于85的，按照平均分数降序排列：

select cosname,avg(score) from tbsc,tbcourse 
where cosname!='C语言程序设计' 
group by cosname  
having avg(score)>85
order by avg(score) desc;

查询结果：

连接查询：

distinct关键字：

将查询结果中的重复记录进行删除，原表中的数据不会发生改变

举例：

去除单个字段中的重复记录：

select distinct(score) from tbsc;

查询结果：

去除多个字段中的重复记录：

distinct出现在多个字段前，表示多个字段联合起来去除重复记录。

select distinct cosname,score from tbcourse,tbsc;

查询结果：

但是下面这种写法是错误的，因为查询的字段是两个，如果单独的将一个字段中的重复信息进行去除，很有可能导致数据无法相互对应。

select score,distinct cosname from tbcourse,tbsc;

distinct只能出现在字段名前面

distinct和分组函数结合使用：

//统计课程的数量
select count(distinct cosname) from tbcourse;

查询结果：

笛卡尔现象：

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为笛卡尔现象。

举例：

两张表连接，没有任何条件限制：

将两张表的名字和课程进行连接—–tbstudent中的每个名字分别和tbcourse中的每个课程进行连配对

select stuname,cosname from tbstudent,tbcourse;

查询结果如下：

总记录为：学生数*课程数

怎么避免笛卡尔现象？

连接时，添加条件，满足这个条件的记录被筛选出来。

-- 课程表中的cosid和分数表中的cid相等的课程名和学分
select cosid,cid,cosname,coscredit from tbcourse,tbsc where tbcourse.cosid=tbsc.cid ;

查询结果：

为了提高查询效率，我们可以给表起别名：

select course.cosid,score.cid,course.cosname,course.coscredit 
from tbcourse course,tbsc score
 where course.cosid=score.cid ;

查询如下：

与上述为起别名的查询结果相同，但是查询效率提高了。

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。

内连接：

完全能够匹配上条件的数据查询出来。

内连接之等值连接：

SQL.92语法

//查询cosid和cid相等的课程名
select cosid,cid,cosname from tbsc,tbcourse where tbsc.cid=tbcourse.cosid ;

SQL99语法：

//查询cosid和cid相等的课程名
select cosid,cid,cosname from tbsc  inner (可省略)join tbcourse on tbsc.cid=tbcourse.cosid ;

查询结果是相同的：

那么这两种有什么区别呢？

观察两种语法格式，我们不难看出，SQL92这种书写格式，如果后期还需要添加更多的筛选条件，那么只能使用and了，这样会导致结构不清晰。

而SQL99的优点在于，表连接的条件是独立的，连接之后，如果还需要进一步的筛选，再往后面加where即可。

SQL99语法格式：

select
......
from
a
join 
b
on
a和b的连接条件
where
筛选条件

内连接之非等值连接：

-- 查询每个课程分数，要求显示的分数大于等于85小于95
select s.score,c.cosname from tbsc s join tbcourse c on s.score where s.score>=85 and s.score<95;-- 条件不是一个等量关系，称为非等值连接

查询结果：

内连接之自连接：

自连接—-一张表看作两张表。

举例：

//查询员工对应的领导编号，要求显示员工名和对应的领导名
select 
a.ename as '员工名',b.ename as '领导名'
from emp a
join emp b
on a.mgr=b.empno;-- 员工的领导编号=领导的员工编号

外连接：

外连接（右外连接）：

right的含义：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

select e.stuid,d.sid,e.stuname,d.score
from tbsc d right outer[可省略] join tbstudent e
on e.stuid=d.sid

查询结果如下：

结论：内连接和外连接的区别在于，在内连接中，两张表是属于平等的关系，显示出来的数据也是两张表能够匹配上的，但是外连接存在主次之分，显示出来的数据不完全是两者都能匹配上的。

外连接（左外连接）：

left的含义：表示将join关键字左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表

举例：

select e.stuid,d.sid,e.stuname,d.score
from tbsc d left outer[可省略] join tbstudent e
on e.stuid=d.sid

查询结果如下：

带有right的是右外连接，又叫做右连接，带有left的是左外连接，又叫做左连接，任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法。

外连接的查询结果条数一定大于等于内连接

三张表或者四张表进行连接：

语法：

select 
.....
from
a
join -- 内连接
b
on
a和b的连接条件
join -- 内连接
c
on
a和c的连接条件
right join -- 外连接
d
on
a和d的连接条件
-- 一条SQL中内连接和外连接可以混合，都可以出现

找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级。

我们先来分析这个问题：

首先我们要查找的内容是员工名，部门名，薪资，薪资等级，以下三张表都有涉及到，其次员工的部门名称需要从部门信息表中获取，那么就需要将员工信息表和部门信息表进行连接，每个员工工资等级需要从工资等级表进行获取，那么就需要将员工信息表和工资等级表进行连接。

要想实现表的连接，那么就需要找出他们属性之间的关系。

如下表所示：

员工信息表和部门信息表之间的共有属性是部门号码，可直接通过等值连接进行。

员工信息表和薪资等级表之间的有关属性是工资，员工工资属于工资等级表的[最低工资，最高工资]。

命令：

select e.name,e.sal,d.dname,s.grade
from 
emp e
join 
dept d
on
e.deptno=d.deptno -- 部门编号相等
join
salgrade s
on
e.sal between s.losal and s.hisal; -- 薪资处于这个范围

子查询：

select 语句中嵌套select语句，被嵌套的select语句称为子查询。

select
....(select)
from
....(select)
where
....(select)

where中的子查询：

举例：

查询分数高于最低分数的学生姓名和分数。

拿到这个问题，我们首先会想到要想得到高于最低分数的学生姓名和分数，那么就必须先知道最低分数。

第一步查询最低分数：

select min(score) from tbsc;

第二步将第一步查询到的结果作为限制条件查询学生姓名和分数。

select stuname,score from tbstudent,tbsc where score>80;

这样虽然能够正确查询，但是编写两条命令固然很麻烦，因此，我们需要使用子查询将其合并为一条命令。

select stuname,score from tbstudent,tbsc where score>(select min(score) from tbsc)

(select min(score) from tbsc)作为子查询，先进行查询，将返回结果反馈给父查询。

from子句中的子查询：

from后面的子查询，可以将子查询的查询结果当做一张临时表。

举例：

找出每个岗位的平均工资的薪资等级。

第一步找到每个岗位的平均工资：

select job,avg(sal) from emp group by job;

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t.

select * from salargrade;s表

第三步：将t表和s表进行连接，连接条件即为t表的平均工资处在s表的最低工资到最高工资范围内（avg(sal）between s.losal and s.hisal;

select 
t.*,s.grade
from
(select job,avg(sal) from emp group by job) t
join 
salgrade s
on
t.avg(sal) between s.losal and s.hisal;

注意：

1：这里不能直接写成t,因为t表是我们假象出来的，他在数据库中并不存在，select job,avg(sal) from emp group by job是我们查询到的结果，为了方便，我们起别名为t

(select job,avg(sal) from emp group by job) t

2:这里需要给avg(sal)起别名，原因是后续代码中t.avg(sal)，系统会自动认为它是个函数。

(select job,avg(sal)  as avgsal from emp group by job) t

那么后续代码中的t.avg(sal)都要修改为t.avgsal

t.avg(sal) between s.losal and s.hisal;

正确命令如下：

select 
t.*,s.grade
from
(select job,avg(sal)  as avgsal from emp group by job) t
join 
salgrade s
on
t.avgsal between s.losal and s.hisal;

select后面出现的子查询：

举例：

找出每个员工的部门编号，要求显示员工名，部门名？

 -- e.enmae为员工名 d.dname为部门名
select e.ename,
(select d.dname from dept d where e.deptno=d.deptno)-- 这条语句是用来查询部门名
-- 注：这里的部门名是有限制条件的，他必须是每个员工对应的部门
as dname from emp e;

这种子查询只能一次返回一条结果，如果多余一条就会报错

依旧是上述事例：

下面这种就是错误的，因为一次返回的是多条记录

select e.ename,
(select d.dname from dept )//一次返回多条记录
as dname from emp e;

union的用法：

基本用法：

举例：

查询选修Java程序设计和C语言程序设计的学生姓名？

根据我们之前学过的内容，常规查询如下：

select stuname,cosname from tbstudent,tbcourse where cosname='C语言程序设计'or cosname='Java程序设计';

除此之外，我们还可以使用in:

select stuname,cosname from tbstudent,tbcourse where cosname in('C语言程序设计', 'Java程序设计');

现在我们就要介绍第三种查询方法了，使用union

select stuname,cosname from tbstudent,tbcourse where cosname='C语言程序设计'
union
select stuname,cosname from tbstudent,tbcourse where cosname='Java程序设计';

查询结果如下：

无论上述三种那种查询方法，均可以正确查询，但是union相比于前面两种存在很大的优势，它的查询效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的在增长，但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

下面我们举一个例子来体会一下union的效率之高：

a连接 b 连接c
a:10条记录
b:10条记录
c:10条记录

那么普通查询所匹配的次数是：10* 10* 10=1000次

而使用union查询：
a连接b一个结果：10* 10–>100次
a连接c一个结果：10* 10–>100次
使用union所匹配的次数就是：100次+100次=200

union使用注意事项：

1：union在进行结果集合并的时候，要求两个结果集的列数相同

2：结果集合合并时列和列的数据类型也必须一致，当数据类型不相同时，虽然在MySQL中不会报错，是由于它的语法要求不是很严格，但是在Oracle中会报错

limit：

limit是将查询结果集的一部分取出来，通常使用在分页查询中

分页的作用是为了提高用户的体验，因为一次全部被查出来，用户体验感会很差。

在数据库中的使用：

完整用法：limit(startIndex,length)；
缺省用法：limit 数字；– 取出前几项数据，注意这里不能使用length-startIndex，因为前后记录都被包含

-- 查询年龄最大的5个人的信息
select *from tbstudent
order by stubirth
limit 5;

查询结果：

注意：

在MySQL中limit是在order by之后才执行

举例：

//取出从第二名到第六名的学生姓名和学号，按照学号降序排列
select stuname,stuid from tbstudent
order by 
stuid desc
limit 2,6;

查询结果如下：

分页：

每页显示三条记录：

第一页：limit 	0,3	[0,1,2]
第二页：limit	3,3	[3,4,5]
第三页: limit	6,3 [6,7,8]

每页显示pagesize条记录：

第pageNo页:

limit （pageNo-1）*pagesize , pagesize

public static void main(String[] args){
//用户提交过来一个页码，以及每页显示的记录条数
int pageNo=5;//第五页
int pagesize=10;//每页显示10条
int startInex=(pagsize-1)*pagesize;
String sql="select....limit"+startIndex+","+pagesize;
} 

创建表：

建表的语法格式：（建表属于DDL语句，DDL包括： create， drop， alter）

方式1：

create table 表名（字段名1 数据类型，字段名2，数据类型，字段名3，数据类型）;

方式2：

create table 表名(
字段名1 数据类型；
字段名2 数据类型；
字段名3 数据类型；
）；

表名：建议以t_或者tbl_开始，可读性强，顾名思义

字段名：顾名思义

表名和字段名都属于标识符

数据类型：

其实在MySQL中包含了很多数据类型，但是我们只需要掌握一些常见的即可

char（最长255）: 定长字符串

在上述实例中，我们规定名字的字段为10个字符，但是现在传入的Jack只有4个字符，由于char是定长字符长，因此即使传入的对象的字段小于10个，他依然会开辟10个内存空间大小，剩下未被占据的空间，将有空格补充

varchar（最长255）: 可变长度的字符串

举例：

上述实例中，我们规定名字的字段为10个字符，但是现在传入的实例对象“jack”只有4个字符，那么此时varchar就会动态的分配4个字符的内存空间

对比于char，varchar的优点在于它比较智能，能够根据实际的数据长度动态分配空间，在一定程度上节省了空间，而char不论实际的数据长度是多少，都会分配定义时的固定长度去存储数据，使用不恰当的时候，可能会导致空间的浪费

既然如此，char就没有一丁点的好处吗？

当然不是，varchar虽然有那么多的优点，但是它有一个很大的缺点就是需要动态分配空间，速度慢，而char因为不需要动态分配内存空间，所以速度会比较快一些

那么他们二者该如何选择呢？

根据实际字段灵活变通

举例：

性别只包含两种：男or女，那么我们对于性别字段当然是选char,大可不必选择varchar

再比如名字字段，我们就必须选择varchar，因为它的长度并不是固定不变的

int（最长11）：数字中的整数型，等价于Java中的int

bigint:数字中的长整形，等价于Java中的long

float：单精度浮点型数据

double：双精度浮点型

date：短日期类型

datetime：长日期类型

clob:字符串大对象

最多可以存储4G的字符串，比如：存储一篇文章，存储一个说明，超过255个字符的都需要使用该对象存储

blob：二进制大对象

专门用来存储照片声音，视频等流媒体数据

往blob类型的字段上插入数据的时候，例如插入一个图片，视频等，必须使用IO流才能完成

举例;

create table student1
(
id int,
name varchar(10),
age int(3),
sex char(1),
email varchar(255)
);

执行该命令后左边刷新：

删除表：

-- 建议采用第二种方式删除表
drop table t_student;-- 当这张表不存的时候会报错
drop table if exists t_student ;-- 如果这张表存在，删除

插入数据：

语法格式：

-- 字段名和值需要一一对应，数量和数据类型都要相互对应
insert into 表名（字段名1，字段名2，字段名3......） values（值1，值2，值3）；

举例：

-- 完整插入
insert into student1 (id,name,age,sex,email) 
values(21100,'张三',10,'男','3242841279@qq.com');

查询结果：

-- 部分插入
insert into student1 (id) values(21100);

查询结果：

上述的部分插入，将数据成功插入了指定的字段id中，那么我们是否可以用同样的方法，将名字也插入呢？

insert into student1 (name) values('jack');

查询结果如下：

名字并没有成功插入我们上一条记录中的名字字段中，而是产生了一条新的记录

由此我们可以得出一个结论，insert语句但凡是执行成功了，那么必然会多一条记录，没有给其他字段指定字段值的话，默认值是NULL

修改默认值NULL：

– 修改某个字段的默认值—default

举例：

email varchar(255) default "无"

查询结果：

省略insert后面的字段名：

如果要省略字段名进行数据的插入，那么必须将数据与字段对应起来，比如第一个字段是id,那么你传输的第一个数据就必须需是id的值，顺序是不能颠倒的

insert into student2 values(2,"张三",18,'男',"75986@qq.com");

不能传入单个字段的数据，例如下述所举例的这种就是不可以的

insert into student2 values(2);

insert插入日期：

MySQL给出了一种函数—-str_to_data()函数：

不过需要注意的是，该函数并不是专门的日期插入函数，而是将字符串转换为日期格式

语法格式：

str_to_date('字符串日期'，'日期格式')

MySQL的日期格式：
%y—年
%m—月
%d—日
%h—时
%i—分
%s—秒

举例：

insert into t_user(id,name,birth)values(21100234,张三,str_to_date('12-1900-1','%m-%Y-%d'));

但并不是所有的日期在插入的时候需要使用该函数进行转换，当你所插入的日期恰好满足%m-%Y-%d时，此时该字符串会被自动转换成日期类型

举例：

insert into t_user(id,name,birth)values(21100234,'张三','1999-12-1');

以默认格式显示日期：

举例：

select id,name,birth as birth from t_user;

查询结果：

以特定格式显示日期：

使用date_format()函数

举例：

select id,name,date_format(birth,'%m/%d/%y') as birth from t_user;

查询结果：

date和datetime的区别：

date是短日期：只包括年月日信息

MySQL默认短日期格式：%y-%m-%d

datetime是长日期：包括年月日时分秒信息

MySQL默认长日期格式：%y-%m-%d %h:%i:%s

获取系统当前时间：

now()函数

在定义时，应设置为datetime类型的，这样才能够精准的获取当前系统时间