MySQL基础2（查询和视图） - 指南

\.  d:\database\java113.sql   //执行sql文件的语句

补充：insert into 目标表 (列名，列名)  select  (列名，列名)  原始表。（将就表中的一些元素插入到新表中）。

一条SQL语句中各部分的执行顺序：

select distinct id,name,avg(age)
from student
where class_id = 1 join class on student.class_id = class.id
group by student.id having avg(age) > 0
order by student.id asc limit 100;

顺序：from --> join on --> where --> group --> having --> select --> order by --> limit

修改表的结构和列：

alter table 表名 [add][modify][drop]  要修改的内容

例：alter table test add primary key(id);

        alter table test modify id bigint auto_increment;

1.查询

1.1聚合查询

1.1.1聚合函数

本质上是针对数据表中的某一列进行操作

1.1.count（列名）  #统计记录的条数，推荐使用count(*)。

1.2.sum(列名)  #求和函数,列必须是数值类型

1.3.avg(列名)  #求平均值

1.4.max(列名)，min(列名)。#求最大值和最小值

1.5.round（数值，小数点的位数） #格式化小数输出格式

分组查询

1.1.2.GROUP BY子句

select 要分组的列，聚合函数（列名）  from 表名 group by 要分组的列 

1.1.3.HAVING

having  对分组的结果进行过滤（与上面的group by合作使用）

语法展示：

select 要分组得列，聚合函数  from 表名  group by 要分组的列  having  对分组的结果进行过滤

1.2联合查询

1.2.1内连接

select * from 表名，表名  where 连接条件[where...]
select * from 表名 [inner] join 表名 on 连接条件[where...]

连接查询时的步骤：

1.确定要参与查询的表，也就是要查询的数据保存在哪些表中。

2.取笛卡尔积

3.确定表与表之间的连接条件

4.确定查询的过滤条件，得到想要的结果行

5.精减查询字段

1.2.2.外连接，分为左外连接和右外连接

select * from table1 left join table2 on table1.*** = table2.***
select * from table1 right join table2 on table1.*** = table2.***

左连接以左表为基准，左边的表中所有的数据全部显示，右表中没有对应的记录用NULL去填充。右连接以右表为基准，右边的表中所有的数据全部显示，左表中没有对应的记录用NULL去填充。

主要应用在两张表数据不一致的场景里

1.2.3.自连接

-- 语法
select * from table t1,table t2 where t1.*** = t2.***
-- 例：求score中id为3的数据的成绩比id为1的数据的成绩高
select * from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score

把行转换成列，在查询的时候可以使用where条件进行过滤，实现行与行之间的比较

1.2.4.子查询

单行子查询：select * from table1 where id = (select id from table2 where clo = ***)
-- 例：查询和小明是同班同学的人
 select * from student where class_id = (select class_id from student where name = '小明';
多行子查询：select * from table1 where id in (select id from table2 where clo = ***)
-- 例：查询语文或英文课程的成绩信息
select * from score where course_id in (select course_id from course where name = '语文' or name = '英语');

内层查询的结果作为外层查询的条件，把多条语句合并成一条执行。子查询可以无限嵌套，层数据过多会影响效率。

-- 例题： 查询三班学生中所有比三班平均分高的成绩信息
select * from score sc,  -- 真实表为sc
(select avg(sc.score) avg_score
    from score sc,student st,class c
    where c.class_id = st.class_id
    and st.student_id = sc.student_id
    and c.name = '三班' ) tmp  -- tmp是临时表的别名
where sc.score > tmp.score;

1.2.5.合并查询

select * from table1 union select * from table2; (union是去重的）
select * from table1 union all select * from table2; (不去重的语句）

作用是把两个结果集中合并成一个返回

注意：合并查询时多个查询的查询列表必须匹配。MYSQL不对结果做校验，写的时候务必注意。演示：select 列名1，列名2 from table1 union select 列名1，列名2  from table2;

2.视图

语法：

-- 创建视图
create view view_name [column_list] as ( select_statement)
-- 删除视图
drop view v_name

解释：view_name 是视图的名字，select_statement是一个完整的select 语句

       注：如果所选取的列的属性重名，需要为重复的列起别名

代码示例：

-- 创建视图时指定列名
create view v_student_score_v1 (
    id, name, class_id, class_name, course_id, course_name, score
) as (
    select
        s.student_id,
        s.name,
        cls.class_id,
        cls.name,
        c.course_id,
        c.name,
        sc.score
    from student s, class cls, course c, score sc
    where s.class_id = cls.class_id
    and sc.student_id = s.student_id
    and sc.course_id = c.course_id
);
-- 更新视图
select * from v_student_score
update v_student_score_v1 set score = 80 where student_id = 1 and course_id = 1;
-- 删除视图
drop view v_student_score_v1

视图是一个虚拟的表，它是基于一个或多个基础表和其他视图的查询的结果集。

视图本身是不存储数据的。

不论是更新了视图还是基础表，相互间都会被影响，查询出来的数据都是最新结果。

优点：1.简单性：可以将复杂的查询封装到一个简单的查询。

2.安全性：可以隐藏表中的敏感数据。

3.逻辑数据独立性：使用到应用程序与数据库的解耦。

4.重命名列：视图允许用户重命名列名，以增强数据可读性。

select返回的临时表可以和真实表联合查询：

-- 派生表与真实表 JOIN
SELECT *
FROM (
    SELECT student_id, name, class_id
    FROM student
    WHERE score > 90
) AS high_score_students
JOIN class ON high_score_students.class_id = class.class_id;
-- 派生表与真实表 UNION 查询的为交集
SELECT student_id, name FROM student WHERE class_id = 1
UNION
SELECT student_id, name FROM (
    SELECT student_id, name FROM student WHERE score > 85
) AS temp_students;
-- 在 WHERE 条件中使用派生表
SELECT *
FROM student
WHERE class_id IN (
    SELECT class_id FROM (
        SELECT class_id, AVG(score) as avg_score
        FROM student
        GROUP BY class_id
        HAVING avg_score > 80
    ) AS high_avg_classes
);
-- 视图（本质也是派生表）与真实表合并查询
SELECT vs.*, t.teacher_name
FROM v_student_score_v1 vs
JOIN teacher t ON vs.class_id = t.class_id
WHERE vs.score > 90;