（小白）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);

本文地址：https://blog.csdn.net/imthewiner/article/details/108969208