mysql进阶语法

mysql高阶语句

高级语法的查询语句

先创建库和表

排序语法，关键字排序

升序和降序

默认的排序方式就是降序

升：ASC

降序：DESC 配合：order by语法

降序排列：要指定列

select * from

数字

多个列升序

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

where条件的筛选功能（比较符号）

区间判断：

add or

嵌套多条件

分组查询

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

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

聚合函数的类型

统计 count

求和：sum

求平均数：avg

最大值：MAX

最小值：min

最少要有两列

这个语法是错误的

这样才是正确的

筛选

在这里插入图片描述

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

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

倒序排列显示后面三行

select * from info order by id desc limit 3;

表和列的别名，

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

给列区别名

给表起别名

对表进行复制：

create table test as select * from info;

可以对表进行复制结构不能复制

复制固定数值到表

通配符

like 模糊查询

%：表示0个，一个或者多个字符 *

_ ：表示单个字符

组合使用 s后面至少包含一个

子查询：

内查询、嵌套查询

select语句当中有嵌套了一个select。嵌套的select才是子查询

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

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

关联语句

in not in

select （select）

大于八十

小于80

切换别的表

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

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

子查询当中多表查询和别名

info表和test表，这两张表id部分相同，查询info表的id的值

取平均值

mysql的视图

视图是一个虚拟表，表的数据是基于查询的结果生成，

视图可以简化复杂的查询，隐藏复杂的细节，访问数据更安全。

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

视图和表之间的区别

1、存储方法，表是实际的数据行，视图不存储数据，不仅仅是查询结果的虚拟表

2、数据更新，更新表可以直接更新视图表的数据，

3、占用空间，表实际占用空间，视图表不占用空间，只是一个动态结果的展示

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

查询当前数据库中的视图表

show full tables in xy102 where table_type like 'VIEW

;

create view v_info as
select a.id,a.name,a.sddress from info a
where a.name in (select b.name from test b where a.name =b.name);
drop tables test1;

删除视图表

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

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

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

连接查询

两张表或者多个表的记录结合起来，基于这些表共同的字段，进行数据的拼接。首选，要确定一个主表作为结果集，然后吧其他表的行有选择性的选定到主表的结果上。

内连接

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

INNER JOIN

语法

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

连接两个名称相同的

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

左连接

左连接，左外连接 left jion left outer join

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

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

select * from test a left join test2 b on a.a_nameb.b_name

left join 左边就是左表 右边就是右表 相同的展示 不相同的显示null

已比较条件为标准，展示结果，两个表相同的部分展示出来，做拼接，不同的结果显示null

右连接 右外连接 jion right outer join

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

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

需求：两张表
第一张表：记录学生的学号，所属专业，课程，姓名 成绩 性别
第二张表：记录学生的学号，手机 家庭地址，兴趣爱好，性别

编写一个查询来查找具有最高分数的学生。

select max(chenji) as max_score from test1;

找出至少有两门课程成绩及格的学生。

select count(id),name from test1
where chenji >= 60
group by name having count(id) >= 2;

查找每个系的学生人数。

select zhuanye,COUNT(DISTINCT id) as stu_num from test1
group by chuanye;

计算每个系的学生平均分数。

select major,lesson,FORMAT(AVG(score),2) as avg_score 
from info1 group by major,lesson;

获取至少同时选修了一门与 ‘xxx’ 相同课程的学生。
找出具有重复名字的学生。
查找在所有课程中都取得了及格分数的学生。
找出每门课程的平均分数，并按照平均分数降序排列。

select kecheng,FORMAT(AVG(chenji),2) as avg_score from test1
group by kecheng
order by avg_score desc;

查找学生选课数量超过平均选课数量的学生信息。
左连接查询 查学号

select * from test1 as i1 left join test2 as i2
on i1.id = i2.id;

右连接查询 查学号

select * from test1 as i1 right join test2 as i2
on i1.id = i2.id;

内连接查询 查学号

select * from test1 as i1 inner join test2 as i2
on i1.id = i2.id;