大数据笔记4—mysql数据库笔记2

单表查询

纸上得来终觉浅，绝知此事要躬行。

排序查询

语法书写顺序:select 字段 [from 表名] [where 条件] [order by 字段1 asc|desc,字段2 asc|desc,... asc|desc]
asc:升序 默认就是升序 asc可以省略
desc:降序
可以省略 但是顺序不能变
逻辑执行顺序:
1.from  找到这张表
2.where 根据条件进行过滤筛选
3.select 将筛选之后的数据展示出来
4.order by 将最终查询到的结果进行排序
-- 查询常量的时候 字段名与查询的常量是一致的  显示的条数取决于查询到的条数
select '哈哈' from student;

需求一:查询所有商品信息,使用价格排序(降序)

select * from product order by price desc;

需求二:在价格排序(降序)的基础上，以分类排序(降序)

select * from product order by price desc,category_id desc;
如果我们以多个字段进行排序

首先会保障前面的字段排序是正确的 然后在前面字段值相同的情况下 再进行后面字段的排序 以此类推

需求三:显示商品的价格(去重复)，并排序(降序)

select distinct price from product order by price;
select distinct price from product order by price asc;
select distinct price from product order by price desc;

聚合查询

在列的方向上进行统计

注意:null不参与统计

聚合函数

函数:为我们封装好的一个功能 我们直接调用就可以使用这个功能

count(字段) 统计条数

sum(字段) 求和 非数字 求和结果为0

avg(字段) 求平均值 非数字结果为0

max(字段) 求最大值 字符串 按照字典排序的大小

min(字段) 求最小值 字符串 按照字典排序的大小

排序:数值排序 字典排序(字符在码表中的位置 先比较第一个字符 第一个字符相同的情况下再比较第二个字符)

排序查询:order by 字段 asc|desc,字段 asc|desc,…

​ 注意:使用多个字段进行排序的时候 会优先为前面的字段排序 前面字段相同的情况下再为后面的字段排序

聚合查询

​ 列的方向上进行统计 null不参与运算

​ 聚合函数:count sum avg max min

示例1: 查询商品的总条数

select * from product;
-- count(字段) null 不参与运算 字段如果存在null值 会导致统计结果不正确
select count(pid) 商品数量 from product;
-- 很多情况下 我们不确定当前字段是否存在null值
select count(1) 商品数量 from product;
select 1 from product;
-- * 只要有一个字段不为null  就能统计出来
select count(*) 商品数量 from product;

商品数量
13

--示例2: 查询价格大于200商品的总条数
select count(1) from product where price > 200;

count（*）
7

--示例3:查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id='c001';

sum（price）
13000

--示例4: 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id='c002';

avg（price）
688.2

--示例5: 查询商品的最大价格和最小价格
select max(price),min(price) from product;

max（price）	min(price)
5000	1

分组查询(重要)

分组之后 按照组的范围进行聚合操作 完成统计

语法:select 字段 [from 表名] [whre 条件] [group by 字段1,字段2,...] [order by 字段1 asc|desc,...
,字段1 asc|desc,...]
执行顺序:
1.from
2.where
3.group by
4.select
5.order by

#1 统计各个分类商品的个数

每个 各个 每种这类说明的时候 后面往往跟着需要分组的字段
1.查询的字段:count()
2.查询的表 product
3.分组字段 category_id (各个分类)

select count(*) 商品个数 from product group by category_id;


-- 直接查询商品个数 会发现一个问题
-- 我们不知道具体是哪一个分类的个数
-- 分组查询的查询字段往往是:所有的分组字段,聚合函数
-- 确定 分组字段 每个 各个 每种... 后面跟的往往是分组用的字段
-- 查询字段中不要出现非分组中的一些字段 1.不合理 2.报错
select category_id,count(*) 商品个数 from product group by category_id;

category_id	商品个数
c001	3
c002	5
c003	3
c004	1
c005	1

统计各个分类商品的个数,且只显示个数大于1的信息

1.查询的字段 category_id,count()
2.表 product
3.分组字段 category_id
4.条件 count的个数大于1
select category_id,count(*) from product where count(*) > 1 group by category_id; -- 这个不行
select category_id,count(*) from product group by category_id having count(*) > 1;

having和where的区别

having
having跟在group by之后 分组之后
having的条件可以是聚合函数

where
跟在from之后 分组之前
where的条件不能是聚合函数

分页查询

每一次查询只查询一部分数据
语法:
select 字段 from 表名 limit offset,count;
offset:偏移量 从第几条数据开始查 在表中第一条数据是 0
count:一次查询多少条记录

简单来说 第一个是第几页 第二个是显示多少条

insert into select语句

insert into 表名 select ...
注意:
这个表要插入的是查询的结果
查询的结果一定要与表中的字段要匹配 类型 个数 顺序
只要保证 查询到结果能够插入到表中的字段当中

总结

书写语法

select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段 limit offset,count

逻辑执行顺序

1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit

多表查询

交叉查询

笛卡尔积查询 – 查询出来的是两张表的乘积
写法和简单查询类似，只需在后面加上表即可

select * from A表,B表;

即可把表中的所有数据显示出来

内连接查询（inner join）

-- 内连接查询
-- 隐式内连接
select * from A,B where A.id=B.id;
select 字段 from 表1,表2,... where 条件
-- 显示内连接
select 字段 from 表1 inner join 表2 on 条件;
select * from A inner join B on A.id = B.id;

内连接查询  查询的是两张表的交集(交集:指的是关联字段的交集 数据:关联字段对应的数据)

内连接查询 查询的是两张表的交集

外连接查询（left/right join）

-- 查询所有的水果以及他们对应的价格
-- 苹果没有对应的价格的 苹果也得要
# 外链接查询
-- 左外连接查询
-- 左表的全部 + 右表交集部分的数据 左右指的就是在sql中的位置
# 语法select 字段 from 表名1 left [outer] join 表名2 on 条件;
select * from A left join B on A.id = B.id;
-- 右外连接查询
-- 右表的全部 + 左表交集部分的数据
# 语法select 字段 from 表名1 right [outer] join 表名2 on 条件;
select * from A right join B on A.id = B.id;

子查询(嵌套查询)

将上一条sql的查询结果 作为下一条sql的查询条件

-- 查询价格最高的水果名称
-- 1.查询出最高的价格来
select max(price) from B; -- 4.2
-- 2.根据最高的价格查询出对应的id
select id from B where price = 4.2; -- 2 有可能是多个id
-- 3.根据id查找水果名称
select name from A  where id in (2);

-- 子查询 将上一条sql查询结果可以作为下一条sql的条件
select name from A  where id in (select id from B where price = (select max(price) from B));

做个练习

准备数据

create database studentmanager;
use studentmanager;

CREATE TABLE teacher (
  id int(11)  primary key auto_increment,
  name varchar(20) not null unique
 );
CREATE TABLE student (
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  city varchar(40) NOT NULL,
  age int 
) ;
CREATE TABLE course(
  id int(11) NOT NULL primary key auto_increment,
  name varchar(20) NOT NULL unique,
  teacher_id int(11) NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher (id)
);

CREATE TABLE studentcourse (
   student_id int NOT NULL,
   course_id int NOT NULL,
   score double NOT NULL,
   FOREIGN KEY (student_id) REFERENCES student (id),
   FOREIGN KEY (course_id) REFERENCES course (id)
);

insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);

需求1：查询获得最高分的学生信息。

-- 1.查询最高的分数
select max(score)
from studentcourse;
-- 95
-- 2.根据最高的分数 找到学生的id
select student_id
from studentcourse
where score = 95;
-- 4
-- 3.根据学生id 找到对应学生信息
select *
from student
where id in (4);

select *
from student
where id in (select student_id from studentcourse where score = (select max(score) from studentcourse));

需求2：查询编号是2的课程比编号是1的课程最高成绩高的学生信息。

# 需求2：查询编号是2的课程比编号是1的课程最高成绩高的学生信息。
-- 1.查询出编号是1的课程最高的成绩
select max(score) from studentcourse where course_id=1; -- 80
-- 2.查询编号是2的课程比编号为1的课程成绩还要高的学生id
select student_id from studentcourse where score > 80 and course_id = 2; -- 1 4 6 9
-- 3.根据id找到学生信息
select * from student where id in (1,4,6,9);
-- ctrl + alt + L 格式化
select *
from student
where id in (select student_id
             from studentcourse
             where score > (select max(score)
                            from studentcourse
                            where course_id = 1)
               and course_id = 2);

需求3：查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩（借助于临时表）。

# 查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩（借助于临时表）。
-- 1.查询出编号是1的课程最高的成绩
select max(score)
from studentcourse
where course_id = 1;
-- 80
-- 2.查询编号是2的课程比编号为1的课程成绩还要高的学生id
select student_id, score
from studentcourse
where score > 80
  and course_id = 2;
-- 1 4 6 9
-- 3.根据id找到学生信息
select *
from student
where id in (1, 4, 6, 9);

-- 临时表:将一条sql查询的结果看做成一张表使用
-- 语法:(sql) as 表名 as可以省略
select stu.name, temp.score
from (select student_id, score
      from studentcourse
      where score > (select max(score)
                     from studentcourse
                     where course_id = 1)
        and course_id = 2) as temp,
     student stu
where temp.student_id = stu.id;

需求4：查询每个平均成绩大于70分的同学的学号和平均成绩。

# 需求4：查询每个同学的平均成绩大于70分的同学的学号和平均成绩。
-- 每个 分组
-- 每个学生 求平均成绩 以学生分组(student_id)
-- 查询的字段:student_id 平均成绩avg
-- 表: studentcourse
-- 条件 平均分 > 70;
select student_id,avg(score) from studentcourse group by student_id having avg(score) > 70;

-- 截取小数
-- truncate(小数,保留的位数) 没有四舍五入
-- format(小数,保留的位数) 有四舍五入 136,154,15.12
-- round(小数,保留的位数) 有四舍五入 13615415.12
select student_id,round(avg(score),2) 平均分 from studentcourse group by student_id having avg(score) > 70;

-- 现在我们查找的是 学生id与成绩
-- 改成 查询学生的姓名与平均分

需求5：查询每个同学的学号、姓名、选课数、总成绩。

# 需求5：查询每个同学的学号、姓名、选课数、总成绩。
-- 子查询
-- 在中间表中 根据student_id进行分组 聚合 查询出
-- 每个学生 count 选课数与 sum 总成绩
-- 查询的字段   student_id count sum
select student_id, count(*) 课程总数, sum(score) 总成绩
from studentcourse
group by student_id;
-- 将这个查询的结果看做成 中间表 与 学生表进行多表的查询

select stu.id,stu.name,temp.课程总数,temp.总成绩
from student stu,
     (select student_id, count(*) 课程总数, sum(score) 总成绩 from studentcourse group by student_id) as temp
where stu.id = temp.student_id;
-- 以上查询的方式 子查询 临时表 多表查询
-- 多表查询
select * from student stu,studentcourse st where stu.id = st.student_id;


select stu.id,stu.name,count(*) 总课程数,sum(score) 总成绩
from
     student stu,studentcourse st where stu.id = st.student_id
group by stu.id,stu.name;

需求6：查询学过赵云老师所教课的同学的学号、姓名。

# ：查询学过赵云老师所教课的同学的学号、姓名
-- 1. 找到赵云老师的id
select id
from teacher
where name = '赵云';
-- 3
-- 2.根据赵云老师的id 找到所教课程信息
select id
from course
where teacher_id = 3;
-- 6
-- 3 .根据课程信息找到学生id
select student_id
from studentcourse
where course_id in (6);
-- 4.根据id找到学生的信息
select *
from student
where id in (3, 4, 5);


select *
from student
where id in (select student_id
             from studentcourse
             where course_id in (select id from course where teacher_id =
                                                             (select id from teacher where name = '赵云')));
                                                             
-- 多表查询   
select s.* from teacher t,course c,studentcourse st,student s
where t.id=c.teacher_id and c.id=st.course_id and st.student_id=s.id and t.name='赵云';