（小白）MySQL基础学习004 20201008

（小白）MySQL基础学习004 20201008

历程

1. 表关系
   表与表之间（实体）有什么样的关系，每种关系应该如何设计表结构

2. 一对一
   一张表一条记录与另一张表中最多有一条明确的关系，通常此设计方案保证两个表中使用同样的主键即可
   《学生信息表》
   常用表：学生ID（PRI） 姓名、年龄、性别
   不常用表：学生ID（PRI） 籍贯、婚否、住址

3. 一对多（也叫作多对一）
   孩子对妈妈来说是多对一，而妈妈对孩子相当于一对多，类似这样的关系
   在多关系的表中去维护一个字段，这个字段是“一”关系的主键

4. 多对多
   一张表中的一条记录在另一张表中可以匹配多条记录，反过来也是一样
   这种情况，会用第三张表来关系

5. 高级数据操作——新增数据
   多数据插入：
   只要写一次insert指令，但是可以直接插入多条记录
   insert into 表名 [字段列表] values(值列表),(值列表2),(值列表3)…;
   insert into my_gbk values(“张三”),(“李四”),(“王五”);
   

6. 主键冲突
   在有的表中，使用的业务主键（字段有业务含义），但是往往进行数据插入的时候，又不确定数据表中是否已经存在对应的主键（比如说是否有对应ID）；
   create table my_student(
   stu_id varchar(10) primary key comment “主键，学生ID”,
   stu_name varchar(10) not null comment “姓名，不能为空”
   )charset utf8;

insert into my_student values(“stu001”,“张三”),(“stu002”,“张四”),(“stu003”,“张五”),(“stu004”,“张六”);

insert into my_student values(“stu004”,“小婷”);

主键重复，报错

8. 主键冲突的解决方案
   1、主键冲突更新，如果冲突就更新
   insert into 表名 【字段列表】values[值列表] on duplicate key update 字段=新值
   insert into my_student values(“stu004”,“小婷”) on duplicate key update stu_name=“小婷”;
   
   张六更新成了小婷
   2、主键冲突替换
   如果所有的都要更新，那么数据操作就比较多了，所以另一种方法是直接替换数据
   replace into 表名 [字段列表]values(值列表);
   replace into my_student values(“stu001”,“夏洛”),(“stu002”,“山姆”);
   

9. 蠕虫复制
   一分为二，成倍增加
   从已有数据中获取数据，并且将数据插入到数据表中
   insert into 表名 [(字段列表)] select */字段列表 from 表（可以是其它表）；
   create table my_simple(
   name char(1) not null
   )charset utf8;

insert into my_simple values(“a”),(“b”),(“c”), (“d”),(“e”),(“f”);

insert into my_simple(name) select name from my_simple;

注意：
可以在短期内快速增加表的数据量，可以测试表的压力，还可以通过大量数据来测试表的效率（索引）
但要注意主键冲突。

11. 更新数据
    在更新数据的时候，一般都是跟随条件更新，而不是批量更新
    update 表名set 字段名=新值 where 判断条件;
    如果没有条件，就全表更新
    但是可以使用limit来限制更新的数量
    update 表名set 字段名=新值 (where 判断条件) limit 数量;

改变4个a成g
Update my_simple set name=“e” where name=“a” limit 4;

成功改变了前4个。

12. 删除数据
    删除数据一般无法重置auto_increment;
    
    mysql有一个能够重置选项中自增长的语法：
    truncate表名：相当于先drop,再create;
    

13. 查询数据
    完整的查询语句：
    select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制；
    select选项：系统该如何对待查询得到的结果
    All：默认的，表示保存所有的记录
    Distinct：去除重复的记录，只保留一条（这里的重复指所有的字段都相同）
    select all * from my_simple;
    

    select distinct * from my_simple;
    
    已经自动去重了

字段列表：
有的时候需要从多张表中获取数据，在获取数据的时候，可能存在不同表中有同名的字段，这时候就需要将同名的字段命名成不同名的。利用别名：alias
字段名 [as] 别名；
select distinct name as name1,name name2 from my_simple;

from数据源：
sql为前面提供查询的数据源，只要是一个符合二维表结构的数据即可，一张表，多张表，甚至是一张虚拟的表数据都可以
多张表：from 表1，表2，。。。

select * from my_int,my_date;

这里相当于把字段叠加，数据数量相乘得到了一份新的数据
这种结果的专业说法叫：笛卡尔积，这个结果给数据库造成压力，没有其它意义
动态数据
from后面跟的数据不是一个实体表，而是一个从表中查询出来得到的二维结果（子查询）
基本语法：from (select 字段列表 from 表) as 别名
select * from (select int_1,int_8 from my_int) as int_my;

14. where子句
    用来从数据表获取数据的时候进行条件筛选
    原理：一条条获取，拿到一条判断一条，符合就保存，不符合就不拿到内存中，
    where 通过运算符进行判断数据

15. group by 子句 分组统计
    表示分组的含义，根据指定的字段，将数据进行分组，分组的目标是为了统计
    基本语法：

添加班级id
alter table my_student add class_id int;

修改班级：
update my_student set class_id =1 where stu_id in (“stu001”,“stu002”);

update my_student set class_id =2 where stu_id in (“stu003”,“stu004”);
update my_student set class_id =3 where stu_id in (“stu0001”,“stu0002”);

根据班级id进行分组
select * from my_student group by class_id;

发现数据反而变少了
group by只是为了数据统计，如果想用它看数据，结果会很让你失望。分组之后，只会保留每组的第一条数据
如何统计：
利用统计函数：统计函数（聚合函数）
count():统计每组数中的数量，如果统计的目标字段为NULL，则不统计,如果为*（count(*)）就表示为统计记录。
avg()：求平均值
sum():求和
max():
min():
alter table my_student add stu_age tinyint unsigned;

alter table my_student add stu_height tinyint unsigned;

update my_student set stu_age=18,stu_height=177 where stu_id=“stu001”;
update my_student set stu_age=17,stu_height=165 where stu_id=“stu002”;
update my_student set stu_age=19,stu_height=170 where stu_id=“stu003”;
update my_student set stu_age=16,stu_height=180 where stu_id=“stu004”;
update my_student set stu_age=17,stu_height=175 where stu_id=“stu0001”;
update my_student set stu_age=18,stu_height=171 where stu_id=“stu0002”;

按照班级统计：
每班人数，最大的年龄，最矮的身高，平均年龄：
select class_id,count(),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id;

此处如果不用group by class_id;也没问题，默认group by anything;
select class_id,count(),max(stu_age),min(stu_height),avg(stu_age) from my_student;

发现这里就只剩下一条数据了，因为class_id没有分组
我们再试试用max(stu_age)分组呢：
select class_id,count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by max(stu_age);
报错：

16. group_concat():为了将分组中指定的字段进行合并；
    select class_id, group_concat(stu_name),count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id;

这里将stu_name拼接显示了出来

17. 多分组
    将数据按照某个字段进行分组后，对已经分组的数据进行再次分组
    基本语法：group by 字段1，字段2 先按照字段1分组，再按照字段2分组，以此类推
    先加一个性别字段：
    alter table my_student add gender enum(“男”,“女”,“保密”);
    update my_student set gender = 1 where stu_id in (“stu001”,“stu003”,“stu0001”);
    update my_student set gender = 2 where stu_id in (“stu002”,“stu004”,“stu0002”);

多分组：
select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id,gender;

18. 分组排序
    mysql中，分组默认有排序的功能，按照分组字段进行，默认是升序
    基本语法：group by字段[asc|desc],字段[asc|desc]
    班级升序，性别降序
    select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id asc,gender desc;
    

19. 回溯统计
    当分组进行多分组之后，往上统计的过程中，需要进行层层上报，将这种层层上报统计的过程称之为回溯统计，每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为NULL
    基本语法：group by字段[asc|desc] with rollup
    select class_id,count() from my_student group by class_id;
    
    select class_id,count() from my_student group by class_id with rollup;
    
    select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id,gender with rollup;
    

20. having字句：
    本质和where一样，是用来进行数据条件筛选，
    having在group by子句之后，是可以对group by的分组进行筛选的，但where在它之前，所以where是不能的
    insert into my_student values(“stu005”,“小江”,1,19,178,1);
    
    查询一个班级人数大于等于3的结果：
    我们先试试where，这里发现number只有在group by之后才会计算，所以报错！
    select class_id,count(*) as number from my_student where number>=3 group by class_id;
    
    where不能使用聚合函数

select class_id,count() as number from my_student group by class_id having number>=3;

这里number 也可以用count()
说明having可以做where能做的所有事，但where却不能做having的

hvaing是对统计结果进行操作的

另外，sql语句的母子句中，子句可以不存在，但一旦存在，就需要按从左往右去执行

where是内存操作

21. order by子句
    基本语法：order by 字段 esc|desc
    create table my_student(
    stu_id int,
    stu_name varchar(10),
    stu_gender enum(“男”,“女”,“保密”),
    stu_class int
    )charset utf8;

insert into my_student values(2020001,“王二”,1,3);
insert into my_student values(2020004,“王三”,2,2);
insert into my_student values(2020002,“王四”,2,7);
insert into my_student values(2020007,“王五”,1,4);

排序：
select * from my_student order by stu_class asc;

order by 也可以像group by一样进行多字段排序

insert into my_student values(2020002,“张二”,1,3);
insert into my_student values(2020005,“张三”,2,2);
insert into my_student values(2020008,“张四”,2,7);
insert into my_student values(2020009,“张五”,1,4);

select * from my_student order by stu_class asc,stu_id desc;

22. limit子句
    用于限制子句，主要是限制记录数量获取
    select * from my_student order by stu_class asc limit 3;
    
    limit在查询的时候，如果限定为一条记录的时候，使用的比较多，有时候获取多条记录并不能解决业务问题，但会增加服务器压力。

23. 分页
    limit用来限制指定区间的数据
    limit offset,length //offset偏移量，从哪儿开始，length长度
    mysql记录的数量从0开始
    limit 0,2表示获取前两条数据
    select * from my_student limit 0,2;
    select * from my_student limit 2,2;

limit后面的length表示最多获取的数量，但是如果数量不够，系统不会强求

24. 算术运算符
    通常不在条件中使用，而是用在结果运算（select字段中）
    ±*/%
    create table ysf1(
    int_1 int,
    int_2 int,
    int_3 int,
    int_4 int
    )charset utf8;

insert into ysf1 values(100,-100,0,default);

select int_1+int_2-int_2,int_1-int_2,int_1*int_2,int_1/int_2,int_2/int_3,int_2/6,int_4/5 from ysf1;

int_1/int_2 获得的是浮点数
在mysql中除法是用浮点数计算的
结果如果是0，系统会给NULL，用NULL进行任何算术运算结果都是NULL，这里的计算错误，会给NULL

25. 比较运算
    =,或者<=>都是表示等于
    select * from my_student where stu_class>=4;
    
    特殊运算：
    select “1”<=>1,0.02<=>0,0.02<>0;
    
    此处只能用<=>进行判断，而不能用=，这里用=就相当于赋值了；
    另外，在mysql中，会将相比较的类型自动转换成同类型，再比较
    在mysql中没有bool值，只有0和1，正确就是1
    在条件判断的时候，还有相对应的比较运算符，计算区间：
    between 条件1 and 条件2；
    select * from my_student where stu_class between 2 and 5;
    
    这种查找是闭区间查询，也就是2<=x<=5
    这里的条件1必须小于条件2
    如果是5 and 2,就会一条数据也没有

26. 特殊运算符
    and or not
    与 或 非
    select * from my_student where stu_id>2020001 and stu_class>2;

27. IN运算符
    在……里面，是用来替代=，当结果不是一个值，而是一个结果集的时候；基本语法 in(结果1，结果2……），只要结果在其中满足条件，返回1 即true

28. Is运算符
    Is是专门用来判断字段是否为NULL的运算符
    基本语法：is null…
    insert into my_student values(2020011,default,default,8);
    
    查找为空：
    select * from my_student where stu_name = NULL;//无结果
    select * from my_student where stu_name is NULL;//有结果

29. LIKE运算符
    用来进行模糊匹配
    基本语法 like 匹配模式
    匹配单个字符
    %匹配多个字符
    select *from my_student where stu_name like "张";
    

30. 联合查询
    联合查询是合并多个相似的选择查询的结果集，等同于将一个表追加到另一个表，从而实现将两个表查询组合到一起，使用谓词为UNION或UNIONALL
    联合查询：将多个查询结果合并到一起（纵向合并），字段数不多，多个查询的记录数合并
    应用场景：
    将同一张表中不同的结果（需要多条查询语句来实现）合并到一起展示数据；
    最常见，在数据量大的情况下，会对表进行分表操作，需要对每张表进行分数据统计，使用联合查询来将数据存放到一起显示；

31. 基本语法
    select 语句
    union(union 选项)
    select 语句
    union选项，与select 选项基本一样
    distinct 去重，去掉完全重复的数据（默认的）
    all保存所有的结果

alter table my_student add stu_height int;
update my_student set stu_height=180 where stu_id=2020001;
update my_student set stu_height=173 where stu_id=2020002;
update my_student set stu_height=182 where stu_id=2020004;
update my_student set stu_height=181 where stu_id=2020005;
update my_student set stu_height=170 where stu_id=2020007;
update my_student set stu_height=182 where stu_id=2020008;
update my_student set stu_height=177 where stu_id=2020009;
update my_student set stu_height=169 where stu_id=2020011;

select * from my_student where stu_gender=1 order by stu_height asc;
select * from my_student where stu_gender=2 order by stu_height desc;

我们可以在两个语句之间加上一个union --默认选项distinct
select * from my_student where stu_gender=1 union select * from my_student where stu_gender=2;

select * from my_student where stu_gender=1 order by stu_height asc union select * from my_student where stu_gender=2 order by stu_height desc;
这样是报错的，具体原因见第32条

只要字段数相同，不需要类型相同
select stu_id,stu_name,stu_height from my_student
union all
select stu_height,stu_id,stu_name from my_student;
我的这两句单独执行没问题，但是连在一起就报错了，

经测试，我是在网页中写的代码，可能和txt中写的格式不同，把换行换成空格即可
select stu_id,stu_name,stu_height from my_student union all select stu_height,stu_id,stu_name from my_student;

它只保留第一句的字段，但这样已经没意义了

32. order by的使用
    order by如果想要在联合查询里使用，就必须用括号括起来
    (select * from my_student where stu_gender=1 order by stu_height asc) union (select * from my_student where stu_gender=2 order by stu_height desc);
    
    实现是实现了，但我发现并没有按我想要的方式排序

33. order by在联合查询中，必须与limit一起使用
    (select * from my_student where stu_gender=1 order by stu_height asc limit 10) union (select * from my_student where stu_gender=2 order by stu_height desc limit 10);
    
    这样就可以了

34. 连接查询
    将多张表连接一起进行查询（会导致记录数行和字段数列发生改变）
    在关系型数据库的设计过程中，遵循着一对一，一对多和多对多，所以通常在实际操作的过程中，需要利用这层关系保证数据的完整性。
    分类：
    交叉连接
    内连接
    外连接
    自然连接

35. 交叉连接
    将两张表的数据与另一张表彼此交叉
    取出每一条记录后，与另外一张表的全部记录挨个匹配
    迪卡尔积
    语法：表1 cross join 表2
    select * from my_student cross join my_int;
    
    结果是27行
    尽量不用交叉连接。

36. 内连接
    从一张表中取出所有的记录去另一张表中匹配，利用匹配条件进行匹配，成功了则保留，失败了放弃。
    匹配到：继续匹配
    匹配失败：向下继续，如何全表匹配结束，没有返回结果
    表1 【inner】 join 表2 on匹配条件
    create table my_class(
    id int primary key auto_increment,
    name varchar(10) not null unique
    )charset utf8;

insert into my_class values(null,“1班”),(null,“2班”),(null,“3班”);

1、如果内联没有条件，那么就是交叉连接
select * from my_student inner join my_class;

2、使用匹配条件
select * from my_student inner join my_class on class_id=id;

3、因为表的设计通常容易产生同名字段，尤其是ID，所以为了避免发生错误，通常使用表名.字段名
select * from my_student inner join my_class on my_student.class_id=my_class.id;

4、通常，如果条件中使用对应的表名，而表名通常比较长，所以可以通过表别名来简化。
select * from my_student as s inner join my_class as c on s.class_id=c.id;

5、内联接，必须保证匹配到，记录才会保存，如果没有匹配到就不显示
6、内连接因为不强制必须使用匹配条件（on）因此可以在数据匹配完成之后，使用where 条件来限制，效果与on一样（建议使用on）
select * from my_student as s inner join my_class as c where s.class_id=c.id;

37. 内连接的应用
    内连接通常在对数据有精确要求的地方使用，必须保证两种表中能进行数据匹配

38. 外连接
    outer join 按照某一张表为主表（表中所有记录都会保留），根据条件去连接另一张表，从而得到目标数据
    左外连接：左边是主表
    右外连接：右边是主表

原理：
确定连接主表：左连接就是left join,right join就是右连接

语法：
左连接，主表left join 从表on 连接条件，主表数据在左边
右连接，从表right join 从表on 连接条件，右表数据在右边

select * from my_student as s left join my_class c on s.class_id=c.id;

select * from my_student as s right join my_class c on s.class_id=c.id;

39. using关键字
    在连接查询中用来代替对应的on关键字，进行条件匹配
    在连接查询时，使用on的地方用using代替
    使用using的前提是对应的两张表连接的字段是同名（类似自然连接自动匹配）
    使用using关键字，那么对应的同名字段，最后在结果中只会保留一个。

语法：
表1【inner left right】

select * from my_student left join my_class using(class_id);