mysql高级语句的查询语句

一、排序语法，关键字排序

升序和降序

默认的排序方式就是升序

升序：ASC

降序：DESC

配合语法：order by 语法

1、升序

select * from info order by name; 根据名字升序排序，不需要加ASC

 select * from info order by id;根据id升序排序

2、 降序（指定列）

select * from info order by name desc;根据名字降序

select * from info order by id desc;  根据id降序

3、多个列排序

 以多个列作为排序关键字，只有第一个参数有相同的值第二个字段才有意义。

select * from info order by hobbid desc,id;

重复值对应的第二个字段按照指定的排序进行排序

select * from info order by id desc,score;#根据id降序，成绩升序

二、区间判断

1、and 且

两个条件都要满足

select * from info where score > 70 and score <=90; 成绩大于70且小于90

 

2、or 且 

 两个条件只要满足一个即可

select * from info where score > 70 OR score <=90;成绩大于70或者成绩小于90

符合条件的都展示出来

3、嵌套多条件

select * from info where score > 70 or ( score > 0 and score < 20 );
查找成绩大于70 或者 成绩大于0且成绩小于20

 三、分组查询

对sql查询的结果进行分组，使用group by 语句来实现

group by 语句配合聚合函数一起使用。

聚合函数的类型：统计 count，求和 sum，求平均数 avg，最大值max，最小值 min。

select  （指定列）count（name），hobbid（进行分组） from info group by hobbi；

最少两个列，group by 不能跟聚合函数后面的列  

 

 在聚合函数分组语句中，所有的非聚合函数列，都要在group by语句当中。

select count(name),hobbid,name from info group by hobbid,name;

select count(name),hobbid,name from info where score >=80 GROUP BY hobbid,name;
以爱好为分组，统计成绩大于80的名字

 

GROUP BY为一个整体，在其前面可以使用where，在其后面要使用having

select count(name),hobbid,score from info GROUP BY hobbid,score having score >=80;

select avg(score),hobbid from info group by hobbid having avg(score) >= 60;  

 

 统计姓名，以兴趣和分数作为分组，统计出成绩大于80的，然后按照降序对姓名的列进行排序

select count(name),hobbid,score from info group by hobbid,score having score > 80 order by count(name) desc;

四、偏移量 

limit 1,3 1是位置偏移量（可选参数）

如果不设定位置偏移量，默认就是从第一行开始（0+1），默认值是0

select * from info limit 3； 显示前三行

 

select * from info limit 1,3; 显示2-4行

 

使用limit 和降序，只显示最后最后三行

select * from info order by id desc limit 3;

五、别名as

 表和列的别名：因为在实际工作中，表的名字和列的名字可能会很长，书写起来不太方便，多次声明表和列时，完整的展示太复杂了，设置别名可以使书写简化了可读性增加了，简洁明了。

格式一：

select name as 姓名，score as 成绩 from info；

 格式二：

select name 姓名，score 成绩 from info；

 

格式三：

select i.name 姓名,i.score 成绩 from info i;

格式四：

select i.name 姓名,i.score 成绩 from info as i;

六、 对表进行复制

create table test as select * from info；

只能复制表的数据，不能复制表的结构，主键的约束复制不了

desc info；查看区别

 desc test;查看复制的表

 用条件方式复制表

create table test1 as select * from info where score >=60;

 test表

七、通配符

 like ：模糊查询

% ：表示0个，1个 或者多个字符类似于*

_：表示单个字符。

select * from info where address like 's%';

八、子查询

子查询： 内查询，嵌套查询，select语句当中又嵌套了一个select。

嵌套的select才是子查询，先执行子查询的语句，外部的select在根据子条件的结果进行过滤查找。

子查询可以是多个表，也可以是同一张表

关联语句 in not in

select（select）

select id,name,score from info where id in(select id from info where score>=80);
先将子查询的表的内容查询出来

 

多张表查询

select id,name,score from info where id  in (select id from test where score>=80);

 

select id,name,score from info where id  not in (select id from test where score>=80);

 

update info set score=80 where id in (select id from test where id =2);
根据test表中的id=2，然后更新info表中id=2的分数

 info表修改前

 

info表修改后

在子查询当中多表查询（不要超过三张）和别名：

info表

test表

 

info表和test表，这两张表id部分相同，然后根据id相同的部分来查询info表的id的值

 

select a.id from info a where id in (select b.id from test b where a.id = b.id );

查询出info表成绩大于80的数据

select a.name from info a where a.score>80 and  a.id in (select b.id from test b where a.id = b.id );

求出平均数

select avg(a.score) from info a where   a.id in (select b.id from test b where a.id = b.id );

 九、exists

exists 判断子查询的结果是否为空，不为空返回true，空返回false

select count（*）from info where exists （select id from test where score>80）;

这里不是in和not in会传给主表。

这里只是判断条件，存在才执行，不存在，结果为空则不执行。

这里的值不是计数的作用

 查询分数，如果分数小于50的则统计info的字段数

select count(*) from info where exists (select id from info where score<50);

select id from info where score<50查询这个条件存不存在，仅仅是一个判断的条件，并不是将结果传到下一个命令中，结果存在执行此条命令select count(*) from info，若不存在则不执行。

十、视图

视图是一个虚拟表，表的数据是基于查询的结果生成的，视图可以简化复杂的查询，隐藏复杂的细节，访问数据更安全。

视图是多表数据的集合体。

1、视图和表之间的区别

• 存储方式，表是实际的数据行，视图不存储数据，仅仅是查询结果的虚拟表
• 数据更新，更新表可以直接更新视图表的数据
• 占用空间，表实际占用空间，视图表不占用空间，只是一个动态结果的展示。

视图表的数据可能是一张表的部分查询数据，也可能是多个表的一部分查询数据。

2、查看当前数据库中的视图表

show full tables in xy102 where table_type like 'VIEW';

 

create view test2 as select * from info where score >=80;

 

查询select * from test2;实际上等于执行select * from info where score >=80

 

 当修改表中id=1的分数修改为79，视图表执行执行select * from test2;

结果 

 

当修改视图id=2的分数修改为90时，查看原表也被修改

5.5之前视图只读，5.5之后双向的，修改视图表的信息，原表是数据也会发生更改

视图表：  

原表：

 

 创建一张视图表，视图表包含id name address，从info和test当中的name值相同的部分创建

create view test4 as select a.id,a.name,a.address from info a where a.name in 
(select b.name from test b where a.name = b.name );

 

1、视图就是查询语句的别名，有了视图表可以简化查询的语句

2、表的权限不一样，库的权限是有控制的，所以查询视图表的权限相对低

3、既可以保证原表的数据安全，也简化了查询的过程

删除视图表

drop view test4；

十一、 连接查询

把两张表或者多个表的记录结合起来，基于这些表，共同的字段，进行数据的拼接。

前提：要确定一个主表作为结果集，然后把其他表的行，有选择性的选定到结果上。

test1表

 

test2表

 

连接的类型：

1、内连接inner join

取两张表或者多张表之间符合条件的数据记录的集合。

取两个表或者多个表之间的交集

select a.a_id,a.a_name from test1 a INNER JOIN test2  b on a.a_name=b.b_name;

select * from test1 a INNER JOIN test2  b on a.a_name=b.b_name;

 

2、左连接

左外连接，left jion .......on 或者left outer join

以左边的表为基础，接收左表的所有行，以左表的记录和右表的记录进行匹配

匹配左表的所有，以及右表中符合条件的行，不符合显示null,不展示。

select * from test1 a left join test2 b on a.a_name=b.b_name;

写在left左边的为左表，在左边中选择name作为一个筛选的条件，只有结果相同的其显示的都能显示出来，否则是null。

以比较条件为标准，两个表相同的展示出来，并作拼接，不同的结果显示null

 3、右连接

右外连接，right jion .......on 或者right outer join

以右边的表为基础，接收右表的所有行，以右表的记录和左表的记录进行匹配

匹配右表的所有，以及左表中符合条件的行，不符合显示null,不展示。

select * from test1 a right join test2 b on a.a_name=b.b_name;