MySql教程

MySQL-数据的增删改查(模糊查询,聚集函数,分组,多表查询)

本文主要是介绍MySQL-数据的增删改查(模糊查询,聚集函数,分组,多表查询),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

添加数据

insert into tableName(field1,field2...) values(value1,value2...);

删除数据

delete from tableName [where]; 

truncate tableName;

delete 和 truncate的区别:

区别deletetruncate
效率delete 是一条一条的删除,效率低truncate 先删除整个表,再创建一个新的空的表,效率高
是否影响自增不会影响自增自增会归零

更新数据

update tableName set field1 = value1,field2 = value2...[where]

如果没有where条件默认更新所有记录

查询数据

1. 基本语法

select field1,field2... from tableName;	-- 查询指定字段

select * from tableName;	-- 查询所有字段

2. distinct 去重

过滤表中重复的数据

select distinct field1 from tableName;

3. as 别名

查询的列可以运算并起一个别名

例如,计算学生的总成绩

select name,(math+english+chinese) as sumscore from student;

4. where 条件过滤

运算符:> = <

and &&

or ||

Not !

is null

select name,(math+english+chinese) as sumscore 
from student 
where sumscore>200;

5. 模糊查询

本质:比较运算符

  • is null 当操作符为空,结果为真

  • is not null 当操作符不为空,结果为真

  • between 当a在b与c 之间,结果为真(a between b and c )

  • like 当a 匹配b,结果为真(a like b)

    • like % 匹配0到任意字符

    • like _ 匹配一个字符

  • in 当a 在a1 , a2……中,结果为真(a in a1,a2…)

-- 查询所有姓杨的同学
select name from student where name like '杨%';
-- 查询数学成绩为66,79,85的同学
select name from student where math in(66,79,85);

5. order by 排序

升序为 asc(默认)

降序为 desc

位于select语句的句尾

-- 对总分从高到低进行排序
select (math+english+chinese) as sumscore 
from student order by sumscore desc;

6. 聚集函数

  1. count 获取数量

    -- 统计学生的总数量
    select count(*) from student;
    
  2. sum 求和

    忽略null值,可以使用 ifnull(filed ,0) 填充null值

    -- 统计全班的总成绩
    select sum(if null(math,0)+english+chinese) from student;
    
  3. avg 平均值

    -- 一个班级的数学平均分
    select avg(math) from student;
    -- 一个班总分平均分
    select avg(if null(math,0)+english+chinese) from student;
    
  4. max 最大值

    select max(math) from student;
    
  5. min 最小值

    select min(math) from student;
    

7. group by 分组

基本格式:

SELECT *|colName1,colName2,… | 统计函数
FROM tableName
WHERE 条件1
GROUP BY colName1,colName2,…
[HAVING 分组后的过滤条件(可以使用统计函数)]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

单独使用group by 分组

此时查询的是每一个分组的一条记录,单独使用意义不大

根据性别进行分组

在这里插入图片描述
在这里插入图片描述

group by 和聚集函数一起使用

可以统计出某个,或者某些字段在一个分组中的最大值、最小值、平均值

根据性别进行分组,得到男女的总数

在这里插入图片描述

group by 和having一起使用

在这里插入图片描述

根据学号进行分组,求总成绩大于250分的学生

在这里插入图片描述

在这里插入图片描述

having 和 where的区别:

  • where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据where条件中不能包含聚集函数,使用where条件过滤出特定的行。
  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚集函数,使用having 条件过滤出特定的组,也可以使用多个分组标准进行分组。

8. 多表查询

连接查询

在用户查看数据的时候,需要显示的数据来自多张表

内连接查询是最常见的连接查询,内连接查询可以查询两张或两张以上的表

连接过程:从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留

普通内连接

前提条件:需要有外键

关键字: inner join … on

基本语法:select from 左表 [inner] join 右表 on 左表.字段 = 右表.字段;

select Sname,Cno,Grade 
from student inner join sc 
on student.Sno=sc.Sno

在这里插入图片描述

隐式内连接(使用最多)

可以不使用关键字 inner join … on

基本语法:select from 左表,右表 where 左表.字段 = 右表.字段;

select Sname,Cno,Grade 
from student,sc 
where student.Sno=sc.Sno

外连接查询

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL

  • 左连接 left join

    基本语法: select from 左表 left join 右表 on 左表.字段 = 右表.字段;

    左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。

  • 右连接 right join

    基本语法: select from 左表 right join 右表 on 左表.字段 = 右表.字段;

    右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置NULL。

联合查询

联合查询结果是将多个select语句的查询结果合并到一块,因为在某种情况下需要将几个select语句查询的结果合并起来显示。比如现在需要查询两个班级的所有学生的信息,这就需要从A班查询所有学生信息,再从B班查询所有的学生信息,然后将两次的查询结果进行合并。
可以使用unionunion all关键字进行操作

基本语法:

select 语句1
union[union 选项]
select 语句2
union|[union 选项]
select 语句n

-- union 选项 可以为all(无论重复都输出),distinct(去重,【默认的】)

select name from classA
union all
select name from classB

联合查询的意义:

  1. 查询同一张表,但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序
  2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

注意:在联合查询中: order by不能直接使用(不能出现两次),需要对查询语句使用括号才行;

select *from
(select *from student 
where sex="woman" 
order by score)student
union
select *from
(select *from student 
where sex="man" 
order by score)student ;

即order by不能直接出现在union的子句中,但是可以出现在子句的子句中。

子查询
  • 使用in关键字的子查询

可以将原表中特定列的值与子查询返回的结果集中的值进行比较

select * from student 
where Sno in 
(select Sno from sc 
 where Grade in
 (select Grade from sc 
  where Grade>80))

例: 查询每门课程成绩大于80的学生的所有信息,
先在子查询中查出成绩大于80的结果集,然后将原成绩表中的成绩与结果集进行比较,如果存在,就得到学生的学号,再将原学生表中的所有学号与结果集进行比较,输出最终的学生信息。

在这里插入图片描述

在这里插入图片描述

  • 带比较运算符的子查询

    如果可以确认子查询返回的结果只包含一个单值,那么可以直接使用比较运算符连接子查询。
    经常使用的比较运算符包括等于(=)、不等于(<>或!=)、小于(<)、大于(>)、小于等于(<=)和大于等于(>=)

    查询奖学金等级为1的学生信息

    select *
    from student 
    where score> 
    (select score 
    from scholarship
    where rank=1);
    
  • 带exists的子查询

    exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表),
    exists是接在where之后
    exists返回的结果只有0和1.

    如果存在成绩大于90的人就列出整个表

在这里插入图片描述

  • 带any关键字的子查询

    any关键字表示满足其中的任意一个条件,使用any关键字时,只要满足内层查询语句结果的的任意一个,就可以通过该条件来执行外层查询语句。

  • 带all关键字的子查询

    all和any刚好是相反的,all关键字表示满足所有结果,使用all关键字,要满足内层查询语句的所有结果,才可以通过该条件来执行外层查询语句。

这篇关于MySQL-数据的增删改查(模糊查询,聚集函数,分组,多表查询)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!