MySQL知识点

1. 主键冲突

当主键冲突时，可以选择的处理操作有：更新、替换。
- 更新操作：(注意：当主键多的时候会比较麻烦)
insert into 表名[(字段列表：包含主键)] values(值列表) on duplicate key update 字段=新值;
- 替换操作：
replace into 表名[(字段列表：包含主键)] value[(值列表)];

2. 更新数据

update 表名 set 字段=值 [where条件] [limit更新数量];

3. 删除数据

delete from 表名[where条件] [limit 数量];
truncate 表名;—-先删除，再改变

4. 查询数据

select [select选项] 字段列表/* from 表名 [where条件子句] [group by子句] [having子句] [order by 子句] [limit 子句]；
- select选项：select对查询结果的处理方式
- - all：默认选项，保留所有查询结果；
- - distinct：去重，将重复去除；
- where子句：判断数据，筛选数据
- - 原理：唯一一个，从磁盘获取数据时候直接开始判断筛选。如果true，保存到内存，如果false，直接放弃。
- - 判断的运算符：>,<,>=,<=,!= / <>,=,like,between and,(not)in，&&(and),||(or),!(not)
- group by子句：分组
- - 分组的目的是为了统计数据，SQL提供了一些列的统计函数：
count()：统计记录数，忽略NULL；max()：统计组中最大值；min()：统计组中最小值；avg()：统计平均值；sum()：统计和。
- - group by 字段名 [asc | desc]：排序，默认升序。
- - 多字段分组：**select 字段名 from 表名 group by 字段**1，字段2，字段3；
- - 分组后字符串连接：group_concat()

- having 子句：条件判断
- - having可以做where能做的所有事情，where却不能做having能做的一些事。比如：分组统计的结果或统计函数只能用having。
- - having可以使用字段别名，where不能使用别名。
- order by：排序
- - 根据某个字段进行升序或降序排序，依赖校对集。
- limit 子句：限制数量
- - 只限制长度：limit 数据量；
- - 限制起始位置，限制数量：limit 起始位置，数据量；

5. 连接查询：join

• 交叉连接：cross join。一张表的每一条记录都去连接另一张表的每一条记录，形成笛卡尔积。
• • from 左表 cross join 右表 <===> from 左表，右表
• 内连接：（inner）join
• • 从左表中取出每一条记录，去右表中与所有记录进行匹配：匹配条件在左右表中相同的才会保留结果
• • 左表 [inner] join 右表 on 左表.字段=右表.字段
• • 内连接可以没有on，如果没有on 则生成笛卡尔积。
• • 内连接中可以用where代替on（where效率比on低）。
• 外连接：outer join
• • 以某张表为主，取出所有记录，然后与另一张表进行匹配，如果匹配成功则正确保留，如果匹配失败则置空null。
• • 外连接分为两种：
    left join：左连接，以左表为主；最终记录数大于等于左表
    right join：右连接，以右表为主。
• 自然连接：natural join
• • 自动匹配连接条件：系统以字段同名作为匹配条件

6. 外键：foreign key

• 增加外键：一张表可以有多个外键
• • 在创建表的时候增加外键，在所有表字段后使用foreign key references 外部表(主键字段)
    create table tablename(
    id int primary key auto_increament,
    name varchar(20) not null,
    c_id int , foreign key(c_id) references anothertable(id));
• • 在新增表之后增加外键：修改表结构
    alter table tablename add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);
• 修改外键&删除外键：外键不可修改，只能先删除后修改
  alter table tablename drop foreign key 外键名;
• 外键约束
• • 对子表的约束：子表增改操作时，如果对应外键字段在父表找不到对应的匹配，则操作失败；
• • 对父表的约束：父表删改操作时，如果主键已经在子表中被引用，则操作失败。
• 外键条件
• • 外键要存在，必须保证使用innodb存储引擎，若不是，可以创建成功，但是没有约束效果；
• • 外键字段的字段类型必须和父表的主键类型完全一致；
• • 一张表中的外键名字不能重复；
• • 增加外键的字段（数据已经存在），必须保证数据和父表的主键对应。
• 外键约束模式
• • distinct：严格模式（默认），父表不能删除或更新一个已经被子表数据引用的记录；
• • cascade：级联模式，父表删除，对应子表关联数据也被删除；
• • set null：置空模式，父表删除，子表对应的数据被置空。
    通常合理的做法（约束模式）：删除时子表置空，更新时子表级联，具体语法：
    foreign key (外键字段) references 父表(主键字段) on delete set null on update cascade;

7. 联合查询

• 基本语法：
  select 语句1 union [all || distinct(默认去重)] select 语句2……；
  只要求字段数一样，跟数据类型无关
• 在联合查询中使用order by 时必须将select语句用括号括起来，并且使用limit 限定才能使order by生效。

8. 子查询：sub query

标量子查询：子查询得到的结果是一行一列

- 列子查询：子查询得到的结果是一列多行

列子查询需要使用条件匹配：in、any、some、all
=any <===> in； 等于其中一个即可
any <===> some；
=all ；为全部
- 行子查询：子查询得到的结果是多行多列或一行多列
行子查询需要构造行元素：行元素由多个字段构成

- 表子查询：子查询得到的结果是多行多列，并当做一个二维表使用

- exists子查询：判断某些条件是否满足，返回结果只有0、1