数据库 MySQL(二)_where age like'1_

数据库 MySQL(二)

准备数据

01. 筛选条件

即用于where条件中

比较运算符

1. =：等于(注意！不是==)
2. !=或<>：不等于
3. >=：大于等于
4. >：大于
5. <=：小于等于
6. <：小于
7. is null：空值
8. is not null：非空值

逻辑运算符

1. and：与

   mysql> select * from student where class=18 and gradName='first_grad';
   1

   
2. or：或

   mysql> select * from student where age>20 or gradName='third_grad';
   1

   
3. not：非

其他操作

1. 排序(order by)

   select columns from 表名 order by col [asc/desc];

   • asc：正序(默认)
   • desc：倒序

   mysql> select * from student order by age;
   #按age正序进行查找所有数据
   mysql> select * from student order by age desc;
   #按age倒序进行查找
   1
   2
   3
   4

   

2. 限制(limit)

   select columns from 表名 limit start,count;

   • start：开始(下标)位置
   • count：从start开始显示count个数据
     数据库中的数据下标是从0开始的

mysql> select * from student limit 0,3;
#查询从第0个数据开始的三条数据
mysql> select * from student limit 2,4;
#查询从第2条数据开始的四条数据
1
2
3
4

3. 去重(distinct)

   select distinct 字段 from 表名;

mysql> select distinct age from student;
mysql> select distinct gradName from student;
1
2

4. 模糊查询(like '%')

   • %：任意多个字符
   • _：任意一个字符

   mysql> select * from student where age like '1_';
   #查找所有20岁以下的数据
   1
   2

   

5. 范围查询

   1. 连续范围：

      between a and b：a <= value <= b

   mysql> select * from student where age between 20 and 24;
   #查找age介于[20,24]的所有数据
   mysql> select * from student where age not between 20 and 24;
   #查找age不在[20,24]的所有数据
   1
   2
   3
   4

   

   2. 间隔范围(非连续范围)：

      in

      a in (10,20,30,[...])

      mysql> select * from student where age in (19,23,25);
      #查找年龄为19，23，25的所有数据
      1
      2

      

小结

02. 聚合与分组

常用聚合函数

1. count(字段)：统计字段的个数(不包括null)
   不包括null是指字段值为null的不计入统计

   mysql> select count(number) from student;
   #统计number的个数(不包括null)
   1
   2

   

2. max(字段)：找该字段的最大值

   mysql> select max(age) from student;
   #查找年龄最大的数据
   1
   2

   

3. min(字段)：找该字段的最小值

   mysql> select min(age) from student;
   #查找年龄最小的数据
   1
   2

   

   mysql> select max(age),min(age) from student;
   #查找年龄最大和最小的数据
   1
   2

   

4. sum(字段)：对该字段进行求和

   mysql> select sum(age) from student;
   #对age进行求和
   1
   2

   

5. avg(字段)：对该字段求平均值

   mysql> select avg(age) from student;
   1

   

6. group_concat(字段)：列出该字段的全部字段值

   mysql> select group_concat(name) from student;
   #列出name的所有字段值
   1
   2

   

分组查询(group by)

常与聚合函数一起用
PS：在分组的情况下，只能够出现分组字段和聚合字段，其他的字段没有意义，会报错

1. select 字段[,聚合函数] from 表名 group by 字段;：按字段进行分组
   PS：select后的字段与by后的字段必须是同一个字段，且select后只能跟一个字段，但可以跟多个聚合函数，select后的字段与聚合函数位置可以任意排序

   mysql> select gradName from student group by gradName;
   #按年级分组
   mysql> select gradName,group_concat(name) from student group by gradName;
   #和聚合函数一起用，按年级分组
   mysql> select max(age),gradName,min(age) from student group by gradName;
   #求出各组中age的最大值与最小值
   1
   2
   3
   4
   5
   6

   
   

   mysql> select group_concat(name) from student group by gradName;
   #直接查看分组后数据
   1
   2

聚合筛选(having)

having：对聚合出来的数据进行再次筛选
就是层层筛选，最终达到精确查找的目的

select 字段1 from 表名 group by 字段1 having 字段1的条件;


having是对select 字段1 from 表名 group by 字段1所筛选出来的数据字段1进行再次筛选，所以having后面的条件必须是对已筛选出来的字段1的数据进行的再次筛选。

mysql> select gradName,group_concat(name) from student froup by gradName having gradName='first_grad';
#在用gradName分组的情况下再筛选出first_grad的数据
1
2

PS：加having条件表达式，可以对输出的结果做出限制

假如说

一个查询语句中同时包含了别名(as)，聚合函数，where，having

那么他们的执行顺序是

1. 先是执行：where
2. 然后执行：聚合函数和别名
3. 最后执行：having

having与where区别：

having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的

1. 区别一：
   where 是数据从磁盘读入内存时候一条一条判断的
   having 是将所有数据读入内存，在分组统计前，根据having的条件再将不符合条件的数据删除
2. 区别二：
   having 子句可以使用字段别名，where不可以用
3. 区别三：
   having可以使用统计函数，where不可用
   having筛选必须是 根据前面select字段的值 进行筛选
   ​

总之一条sql中有where, having, group by的时候，顺序是 where group by having

mysql> select gradName,count(name) from student where age>18 group by gradName;
#计算出每个年级中大于18岁的人数
mysql> select gradName,count(name) from student where age>18 group by gradName having gradName='first_grad';
#计算出一年级中大于18岁的人数
'''因为select筛选出来的是gradName，所以having只能对gradName进行再筛选，而不能对其他字段进行筛选'''
1
2
3
4
5

小结

03. 子查询

定义：

将一个查询的结果留下来用于下一次查询(select中嵌套select)

要求：

1. 嵌套在查询内部
2. 必须始终出现在圆括号内

例：

mysql> select avg(age) from student;	
#求出学生的平均年龄
mysql> select * from student where age > 19.7273;	
#查找出大于平均年龄的数据
mysql> select * from student where age > (select avg(age) from student);
#将求出的平均年龄的SQL语句用于查找大于平均年龄的语句中
1
2
3
4
5
6

04. 连接查询

内连接(inner join)

1. 无条件内连接

无条件内连接，又名交叉连接/笛卡尔连接

第一张表中的每一项会和另一张表的每一项依次组合
select * from 原表 inner join 新表

mysql> select * from student inner join scoren;
#student表中的每一项会与scoren表中的每一项依次组合
1
2

2. 有条件内连接

在无条件内连接的基础上，加上一个on子句
on 连接条件
当连接的时候，筛选出那些有实际意义的记录来进行组合
select * from 原表 inner join 新表 on 原表.字段1=新表.字段2;

mysql> select * from student inner join scoren on student.number=scoren.id;
#只将student表中number与scoren表中id值相同的数据组合筛选出来
mysql> select student.number,student.name,student.age,scoren.id from student inner join scoren on student.number=scoren.id;
#再上条代码的基础上只打印出student表中的number,name,age字段和scoren表中的id字段
1
2
3
4

外连接({left|right} join)

1. 左外连接：(以左表为基准)

两张表做连接的时候，在连接条件不匹配的时候，

留下左表中的数据，而右表中数据以NULL填充

1. select * from 左表 left join 右表 on 左表.字段1=右表.字段2
2. select 字段3,字段4[,...] from 左表 left join 右表 on 左表.字段1=右表.字段2

mysql> select * from student left join scoren on student.number=scoren.id;
#student为左表,scoren为右表
mysql> select student.name,scoren.score left join scoren on student.number=scoren.score;
#只显示student中的name和scoren中的score
1
2
3
4

2. 右外连接：(以右表为基准)

对两张表做连接的时候，在连接条件不匹配的时候，

留下右表中的数据，而左表中的数据以NULL填充

1. select * from 左表 right join 右表 on 左表.字段1=右表.字段2
2. select 字段3,字段4[,...] from 左表 right join 右表 on 左表.字段1=右表.字段2

mysql> select * from student left join scoren on student.number=scoren.id;
#student为左表,scoren为右表
mysql> select student.name,scoren.score left join scoren on student.number=scoren.score;
#只显示student中的name和scoren中的score
1
2
3
4

总结