2021-05-14

Mysql（增，删，改，查，子查询，多表联查，聚合函数）

数据库设计过程：
      我们要有ER图（实体图），从需求里面提取关键字段，然后画ER图，然后ER图里面弄清楚表之间的关系

数据库设计的三大范式；
      第一范式（确保每列保持原子性）
    内容不再分割

      第二范式：（确保表中的每列都和主键相关）
      这一列基本上都是主键
      每一列都和其中的某一列相关
 
     第三范式：（确保每列都和主键相关，而不是简介相关）

级联关系分为三种：一对一，一对多，多对多

如以下这个实体图：

create database 库名；（例如 create database db_test；新建db_test数据库）
show databases；  展示数据库
use db_test; 用数据库
drop database  库名；（例如 drop datebase db_test; 丢弃db_test数据库,*注意一般情况下不要丢弃数据库*）
create table 表明(字段+类型);   （例如 create table tb_pupil(stunum varchar(10),stuname varchar(10),gender varchar(5),age int);建表学生信息这个表），
primary key(主键)
auto_increment自增
primary key auto_increment主键自增

 

create table 关联关系表（）； (例如create table r_student_course(id int primary key auto_increment,sid int,cid int,score int);)

show tables； 看表
desc 表名；（例如 desc tb_student;  描述表 tb_student）
drop table表名；（ drop table tb_student; 删这个表)
SQL基本语言    varchar  可变字符  char 不可变字符

改变表结构：   alter table 表名 add（age int）；改变表结构（可以增加字段）
                      alter table 表名 drop a；删掉a字段    
                      alter table 表名 change 字段 字段 类型 default 值； 这个字段默认一个值
增：insert into 表名（字段）value（值）；插入表里，可以批量插入，值可以连续插入很多

删：delete from 表名 ; (它把表里内容全部删了，不合适)
      delete from 表名 where 字段=值 and 字段=值；（俩个都满足就删除）
      delete from 表名 where字段=值 or gender=值；（俩个满足其中一个就删除） 
      delete from 表名 where 字段 is null；这个字段为空这一行就删了
（注意delete删的是表里的内容，结构还在；但是drop删的是表结构，表结构和内容都没了）
     
where真正的含义把这张表拿出来判断真假（它后面是条件要判断）

改：update 表名  set 字段1 = 值1 where 字段2 =值2;（字段2=值2这行里的字段1里的值被改成了值1）
       
查：select 'a'以表的形式展示后面的内容
select ‘a’as参数；把a当作参数  as给取别名
select ‘a’as参数；  挑选  意义是以表的形式展示后面的内容
select*from 表名；（例如 select*from tb_student;  通表查询）
select*from 表名 where 字段 not in （值）;   //不是这个的显示出来
例如 select*from tb_student where gender not in（‘女’）；

select*from 表名 where 字段between 值and 值;    //这个值到这个值之间的显示出来（值包含）
例如selet*from tb_student where age between 19 and 20; //19岁到20岁的
select*from 表名字段 where 字段 in 值;    有这个值的显示出来
select后面是展示内容 from后面是一个表数据where后面仅仅是一个布尔判断，where后面的结果要成立
select主键from 表 where 字段=值 and 字段=值；

子查询：select * from 表 where 主键 in（select id from 表where 字段 = 值 and 字段 =值）；
select*from tb_course where id in(select cid from r_pupil_course where pid =
(select id from tb_pupil where stuname = '张鑫')
); 通过子查询查出张鑫选出的课和他的老师

多表联查
第一种方式是笛卡尔积式的集合表：select*from 表名 ，表名；
例如select*from tb_pupil,tb_course;
关联关系表查询
select*from 表1别名，表2别名，关系表别名；
关联关系表联查：select*from 表1别名，表2别名，关系表别名；

例如select*from tb_pupil p,tb_course c,r_pupil_course;
select*from tb_pupil p,tb_course c,r_pupil_course r ;
select*from tb_pupil p,tb_course c,r_pupil_course r where p.id=r.pid ;//删掉一半的数据
select*from tb_pupil p,tb_course c,r_pupil_course r where p.id=r.pid and c.id=c.cid;//删掉一半的数据
select  p.pupil, c.cname, r.score from tb_pupil p, tb_course c, r_pupil_course r where p.id=r.pid and c.id = r.cid and p.stuname='张鑫';//拿出张鑫的成绩
select  p.pupil, c.cname, r.score from tb_pupil p, tb_course c, r_pupil_course r where p.id=r.pid and c.id = r.cid and r.score<60;//拿出不及格的人
on在where之前join之后；group仅贴在where之后
select*from tb_pupil p join r_pupil_course r on p.id =r.pid join tb_course c on c.id=r.cid;
select*from tb_pupil p join r_pupil_course r on p.id =r.pid join tb_course c on c.id=r.cid and p.stuname='张鑫';
select javascore.stuname from(
    select
        p.id, r.score, p.stuname 
    from
        tb_pupil as p,r_pupil_course as r
    where
        r.pid=p.id and r.cid =1
    )as javascore ,(
    select
        p.id,r.score
    from
        tb_pupil as p , r_pupil_course as r
    where
        r.pid = p.id and r.cid =2
    )as dbscore
    where javascore.score>dbscore.score and javascore.id=dbscore.id    	查询java课程比数据库课程成绩高的所有学生的名字

mysql聚合函数

select count（id） from 表；一般数主键，
select sum（字段）from 表；这个字段的值加起来
select sum（字段）/count（字段）from 表；求平均值
select avg（字段）from 表；求这个字段的平均值
select min（字段）from 表；这个字段的最小值
select max（字段）from 表；这个字段的最大值
select replace（字段，'值1'，'值2'）from 表；把这个字段的值1替换成值2
select comcat（‘值1’，'值2'）；把值1和值2写在一起
select uuid（）；大型id，它安全
insert into tb_test(id，test） value（replace（uuid（），'_' ,‘ ’）,' '）,'aaaaa' );   把uuid（）里面的‘_’替换成’‘。也是把_取了
select*from关系表 group by pid；

on在where之前join之后；group仅贴在where之后
having之前结果表还没有形成，select 1 from join on 2 where3 (group by） having4
顺序是2341
聚合函数不能用在where 和 on 里面
select avg（score）from 关系表group by pid；

select avg（score）from 关系表group by pid；求所有人的平均分，用以下俩种方法做
（1）用聚合函数做 ：select *from 关系表group by pid having avg（score）<70; 
（2)用子查询平均分小于70的人：
select
       *
from
    r_pupil_course as r,
    (select id, avg(score) as av from r_pupil_course group by pid) as t
where
    r.id =t.id and t.av<70;



查平均分最高的人：
用聚合函数嵌套子查询做：
select
       *
from
       r_pupil_course
group by pid
        having avg(score) =(
            select max(m) from (
	select avg(score) as m from r_pupil_course group by pid
            ) as n 	      
        );  //avg=平均分的最高分




select p.stuname, avg(r.score)from
      tb_pupil p,r_pupil_course r
where p.id = r.pid group by r.pid having avg(r.score)>60;//平均成绩大于60分的同学和平均成绩



select p.stuname, avg(r.score)from
      tb_pupil p,r_pupil_course r
where p.id = r.pid group by r.pid having avg(r.score)>60 order by avg(r.score) limit 0,1; //用limit取第一个平均分大于60的人


select p.stuname, avg(r.score)from
      tb_pupil p,r_pupil_course r
where p.id = r.pid group by r.pid having avg(r.score)>60 order by avg(r.score)desc;
//平均成绩大于60分的同学和平均成绩使用order降序


每个科目分数85分到100分的人数：
select
      c.id as  '课程id',
      c.coursename as '课程名称',
      great.cou as '[100-85]',
      good.cou as '[75-85]' 
from
       tb_course as c left join
      (
            select
                  cid,
                  count(pid) as cou 
            from
                  r_pupil_course
            where
                  score>=85 and score <=100 group by cid
       ) as great on c.id=great.cid left join
	(	
             select
                    cid,
                    count(pid)as cou
             from
                   r_pupil_course
             where
                   score >=75 and score<85 group by cid
        )as good   on 1=1;


100到85和70到60分的人数：
select
      cid,
      count(pid) as cou,
      '[100-85]'
from
      r_pupil_course
where
      score>85 and score<=100 group by cid

union

select
      cid,
      count(pid) as cou,
      '[75-60]'
from
      r_pupil_course
where
      score>=60 and score<75 group by cid;

//union查询联合查询只是在统计俩张表字段数一模一样，一般用子查询解决不了的时候在想union