mysql学习2之列属性、关系（一对一，一对多，多对多），范式，高级增删改查操作

列属性主键，唯一键和自增长主键：primary key，用来唯一的约束该字段里面的数据，不能重复，一张表中只能有一个主键增加主键SQL操作中有多种方式给表增加主键：大体分为三种1、在创建表的时候，直接在字段后加 primary key --增加主键create table my_pri1(id int primary key comment '学号',name varchar(20) not null comment '姓名')charset utf8;2、--复合主键create table my_pri2(number char(10) comment '学号',course char(10) comment '课程代码',score tinyint unsigned default 60 comment  '成绩',primary key(number,course))charset utf8;3、当表已经创建好之后，再次额外追加主键，可以通过修改 表字段属性，也可以直接追加--追加主键create table my_pri3(course char(10) not null comment '课程编号',name varchar(20) not null comment '课程名')charset utf8;alter table my_pri3 modify course char(10) primary key comment '课程编号';或add primary key(course);追加主键前提：表中字段对应的数据本身是独立的（不重复的）。主键约束主键对应的字段中的数据不允许重复：一旦重复，数据操作失败--向pri1表插入数据insert into my_pri1 values(1,'学生一'),(2,'学生二');insert into my_pri2 values('number0001','course0001',65),('number0002','course0002',89);--主键冲突insert into my_pri1 values(1,'学生三'); insert into my_pri2 values('number0001','course0001',78);更新主键&删除主键没有办法更新主键：主键必须先删除才能增加alter table 表名--删除主键desc my_pri3;alter table my_pri3 drop primary key;主键的分类在实际创建表的过程中，很少使用真实业务数据作为主键字段（业务主键，如学号，课程号），大部分的时候是使用逻辑性的字段（字段没有业务含义），将这种主键称为逻辑主键。create table my_student(id int primary key auto_increment comment '逻辑主键 自增长',number char(10) not null comment '学号',name varchar(10) not null)charset utf8;自增长当对应的字段，不给值，或者说给默认值，或者给NULL的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1操作，得到一个新的不同的字段。自增长通常是跟主键搭配。auto_increment 自增长特点：1、任何一个字段要做自增长必须前提是本身是一个索引（key 一栏有值）2、自增长字段必须是数字（整型）3、一张表最多只能有一个自增长4、自增长默认第一个元素是1，每次自增1--自增长create table my_auto(id int primary key auto_increment comment '自增长',name varchar(20) not null)charset utf8;自增长使用当自增长给定的值为NULL或者默认值的时候会触发自增长--触发自增长insert into my_auto(name) values("自增长");insert into my_auto values(null,"自增长2");insert into my_auto values(default,"默认值");注意：自增长如果对应的字段输入了值，那么自增长失效，但是下一次还是能够正确的自增长（从最大值+1）insert into my_auto values(6,"指定值");insert into my_auto values(default,"自增长3");如何确定下一次是什么自增长？可以通过查后表创建语句看到show create table my_auto;修改自增长1、自增长如果是涉及到字段改变：必须先删除自增长后增加（一张表只能有一个自增长）2、修改当前自增长已存在的值：修改只能比当前已有的自增长的最大值大，不能小（小不生效）alter table my_auto auto_increment =20;insert into my_auto values(null,'修改1');--向下修改,无效alter table my_auto auto_increment=10;insert into my_auto values(null,"修改2"); 为什么自增长是从1开始？每次加1？所有系统的实现（如字符集，校对集）都是由系统内部的变量进行控制的查看自增长对应--查看自增长变量show variables like 'auto_increment%';| auto_increment_increment | 1     步长| auto_increment_offset    | 1     起始值+--------------------------+------可以修改变量实现不同的效果：修改是对整个数据修改，而不是单张表：（修改是会话级）set auto_increment_increment = 5 ; --一次自增5show variables like 'auto_increment%';insert into my_auto values(null,"修改会话级");删除自增长自增长是字段的一个属性：可以通过modify来进行修改(保证字段没有auto_increment)--删除自增长alter table my_auto modify id int primary key ; --错误，主键是单独存在的alter table my_auto modify id int; --有主键的时候，不需要再加主键唯一键一张表往往很多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键唯一键可以解决唯一性约束唯一键默认的允许字段为空，而且可以多个为空（空字段不参与唯一比较）增加唯一键方案1：在创建表的时候，字段之后直接跟unique/unique key create table my_unique1(number char(10) unique comment '学号：唯一，允许为空',name varchar(20) not null)charset utf8;方案2：在所有字段后增加unique key(字段列表)create table my_unique2(number char(10) not null comment '学号',name varchar(20) not null,unique key(number))charset utf8;方案3：在创建表之后增加unique key create table my_unique3(id int primary key auto_increment,number char(10) not null,name varchar(20) not null)charset utf8;alter table my_unique3 add unique key(number);唯一键约束唯一键本质与主键相同，唯一的区别是允许为空insert into my_unique1 values(null,'唯一空');insert into my_unique1 values('number0001','学生一'),(null,'唯一空二'); insert into my_unique1 values('number0001','学生二');  --报错如果唯一键也不允许为空，与主键的约束是一样的更新唯一键&删除唯一键更新唯一键：先删除后新增（可以有多个，可以不删除）alter table my_unique1 drop unique key; --错误：唯一键有多个alter table my_unique1 drop index 索引名字;  --默认使用字段名作为索引名desc my_unique3;alter table my_unique3 drop index number ;索引几乎所有的索引都是建立在字段之上索引：系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件，文件能够实现快速的匹配数据，并且能够快速找到对应表的记录。索引的意义：1、提升查询数据的效率2、约束数据的有效性（唯一性等）增加索引的前提条件：索引本身会产生索引文件（有时候可能比数据文件还大），会非常耗费磁盘空间。如果某个字段需要作为查询的条件经常使用，那么可以使用索引。mysql中提供了多种索引1、主键索引 primary key2、唯一索引 unique key 3、全文索引 fulltext index  4、普通索引 index 全文索引：针对文章内部的关键字进行索引	全文索引最在的问题：在于如何确定关键字		英文很容易：英文单词与单词之间有空格		中文很难：没有空格，而且中文可以各种随意组合（分词）		关系将实体与实体的关系，反应到最终数据库的设计上来，将关系分为：一对一，一对多，多对多所有的关系都是指的表与表之间的关系一对一一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。学生表：姓名，性别，年龄，身高，体重，籍贯，家庭住址，紧急联系人其中姓名、性别、年龄、身高，体重属于常用数据，但是籍贯、住址和联系人为不常用数据如果每次查询都是查询所有数据，不常用的数据就会影响效率，实际又不用常用信息表：ID(P)，姓名，性别，年龄，身高，体重不常用信息表：ID(P)，籍贯，家庭住址，紧急联系人解决方案：将常用的和不常用的信息分享存储，分成两张表不常用信息表和常用信息表，保证不常用信息表与常用信息表能够对应上：找一个具有唯一性的字段来共同连接两张表。一个常用表中的一条记录永远只能在一张不常用表中匹配一条记录，反之亦然。一对多		一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一母亲与孩子的关系：母亲，孩子两个实体母亲表：ID(P),名字，年龄，性别孩子表：ID(P),名字，年龄，性别以上关系：一个妈妈可以在孩子表中找到多条记录（也可能是一条），但是一个孩子只能找到一个妈妈是一种典型的一对多的关系。但是以上设计：解决了实体的设计表问题，但是没有解决关系问题，孩子找不到母亲，母亲也找不到孩子解决方案：在某一张表中增加一个字段，能够找到另外一张表中的记录:在孩子表中增加一个字段指向母亲表，因为孩子表的记录只能匹配到一条母亲表的记录。母亲表：ID(P),名字，年龄，性别孩子表：ID(P),名字，年龄，性别，母亲表ID（母亲表主键）多对多一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能对应A表中的多条记录老师和学生老师表 T_ID(P),姓名，性别学生表 S_ID(P),姓名，性别以上设计方案：实现了实体的设计，但是没有维护实体的关系一个老师教过多个学生，一个学生也被多个老师教过解决方案：增加一张中间关系表老师与学生的关系表：ID(P),T_ID,S_ID 老师表与中间表形成一对多的关系，而中间表是多表；维护了能够唯一找到一表的关系；同样的学生表与中间表也是一个一对多的关系; 学生找老师：找出学生ID--->中间表寻找匹配记录（多条）--->老师表匹配（一条）老师找学生：找出老师ID--->中间表寻找匹配记录（多条）--->学生表匹配（一条）范式Normal Format,是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题；保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储；终极目标是为了减少数据的冗余。范式：是一种分层结构的规范，分为6层：每一层都比上一层更加严格；若要满足下一层范式前提是满足上一层范式六层范式：1NF,2NF,3NF,...6NF,1NF是最底层，要求最低mysql属于关系型数据库：有空间浪费；也是致力于节省存储空间；在设计数据库的时候，会利用到范式来指导设计。但是数据库不单是要解决空间问题，要保证效率问题；范式只为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现；一般情况下，只有前三种范式需要满足。范式在数据库的设计当中是有指导意义：但是不是强制规范。1NF 第一范式在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理（拆分），那么说表的设计不满足第一范式；第一范式要求字段具有原子性。原子性指字段不可再分。讲师代课表：讲师姓名(P)、性别、班级(P)、教室、代课时间（天）、代课时间段（开始--结束）  不满足1NF，代课时间段需要拆分解决方案：将代课时间段拆分成开始和结束讲师代课表：讲师姓名(P)、性别、班级(P)、教室、代课时间（天）、开始时间、结束时间2NF 第二范式在数据表设计的过程中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键的部分）；存在字段依赖主键的部分的问题，称之为部分依赖；第二范式就是要解决部分依赖。讲师代课表：讲师姓名(P)、性别、班级(P)、教室、代课时间（天）、开始时间、结束时间以上表中：因为讲师没有办法作为独立主键，需要结合班级才能作为主键（复合主键）代课时间，开始和结束字段都与当前的代课主键（讲班和班级）决定，但性别并不依赖班级，同时教室不依赖讲师，性别只依赖讲师，教室只依赖班级，出现了性别和教室依赖主键中的一部分，部分依赖，不符合2NF。解决方案1：可以将性别与讲师单独成表，班级与教室也单独成表解决方案2：取消复合主键，使用逻辑主键讲师代课表：ID(P)、讲师姓名、性别、班级、教室、代课时间（天）、开始时间、结束时间3NF 第三范式要满足必须满足第二范式第三范式：理论上讲，应该一张表中的所有字段都应该直接依赖主键（逻辑主键：代表的是业务主键），如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键；把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。第三范式要求解决传递依赖。讲师代课表：ID(P)、讲师姓名、性别、班级、教室、代课时间（天）、开始时间、结束时间以上设计方式中：性别依赖讲师存在，讲师依赖主键；教室依赖班级，班级依赖主键解决方案：将存在传递依赖的字段，以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息的时候，使用对应的实体表的主键加进来。讲师代课表：ID(P)、讲师ID、班级ID、代课时间（天）、开始时间、结束时间讲师表： ID(P)、讲师姓名、性别 (ID等价于讲师)班级表： ID(P)、班级、教室     (ID等价于班级)逆规范化有时候，在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以获取到想要的数据，但是就是效率低一点，会刻意的在某些表中，不去保存另外表的主键（逻辑主键），而是直接保存想要的数据信息；这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询（效率低）；但是会导致数据冗余增加。数据高级操作数据操作：增删改查基本语法insert into 表名[(字段列表)] values(值列表);在数据插入的时候，假设主键对应的值已经存在：插入一定会失败！主键冲突当主键存在冲突的时候，可以选择性的进行处理：更新和替换1、主键冲突：更新操作insert into 表名[(字段列表：包含主键)] values(值列表) on duplicate key update 字段 = 新值;show tables; --查看所有数据表insert into my_class values('PHP0810','b205');insert into my_class values('PHP0810','b206'); --主键冲突--冲突处理：更新insert into my_class values('PHP0810','b206') on duplicate key updateroom = 'b206';2、主键冲突：替换replace into 表名[(字段列表)] values(值列表);--主键冲突：替换replace into my_class values('PHP0810','b207');--没有冲突，直接插入replace into my_class values('PHP0811','b208');蠕虫复制从已有的数据中去获取数据，然后将数据又进行新增操作：数据成倍的增加表创建的高级操作：从已有表创建新表（复制表结构）create table 表名 like 数据库.表名;create table my_utf8(name varchar(20))charset utf8;--复制创建表(只复制结构，不复制数据)create table my_copy like my_utf8;蠕虫复制：先查出数据，然后将查出的数据新增一遍insert into 表名[(字段列表)] select 字段列表/* from 数据表名;insert into my_copy select username from t_user;insert into my_copy select username from my_copy;意义：1、从已有表中拷贝数据到新表中2、可以迅速的让表中的数据膨胀到一定的数量级；测试表的压力以及效率。更新数据基本语法update 表名 set 字段 = 值 [where 条件]高级新增语法update 表名 set 字段 = 值 [where 条件][limit 限制数量]update my_copy set name='修改后' where name like '%C%' limit 3;删除数据与更新类似：可以通过limit限制数量--删除数据：限制数量为2delete from my_copy where name ='修改后' limit 2;删除：如果表中存在主键自增长，那么当删除之后，自增长不会还原思路：数据的删除是不会改变表结构，只能删除表后重建表truncate 表名; --先删除表，后新增表--清空表：重置自增长truncate my_student;查询数据基本语法select 字段列表/* from 表名 [where 条件]完整语法select[select 选项] 字段列表[字段别名]/* from 数据源 [where 条件子句] [group by子句][having子句][limit子句]select选项：select 对查出来的结果的处理方式	ALL：默认的，保留所有的结果	distinct 去重select distinct * from my_copy;字段别名当数据进行查询出来的时候，有时候名字并不一定满足需求(多表字段会有同名字段)需要对字段名进行重命名：别名语法：字段名 [as] 别名数据源数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以作为数据源。数据源分为多种：单表数据源，多表数据源，查询语句单表数据源：select * from 表名;select * from my_copy;多表数据源：select * from 表名1,表名2...;select * from my_copy,my_student;从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留（记录数和字段数），将这种结果称为：笛卡尔积（交叉连接）；没太大用，应该尽量避免。子查询：数据的来源是一条查询语句select * from (select 语句 ) as 别名;select * from (select * from my_student ) as student;where子句用来判断数据，筛选数据where子句返回结果：0或者1,0代表false,1代表true;判断条件：	比较运算符：>,<,>=,<=,!=,<>,=,like,between and,in/not in	逻辑运算符：&&(and),||(or),!(not)where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件；从磁盘取出一条记录，开始进行where判断，判断的结果如果成立保存到内存；如果失败直接放弃。alter table my_student add age tinyint unsigned;alter table my_student add height tinyint unsigned;--增加值  rand取得一个0到1之间的随机数update my_student set age=floor(rand()*20+20),height=floor(rand()*20+170);条件查询1：要求找出学生id为1或者3或者5的学生select * from my_student where id in (1,3,5);条件查询2：找出身高在180到190之间的学生select * from my_student where height between 180 and 190;between本身是闭区间；左边的值必须小于或者等于右边的值。group by 分组根据某个字段分组--根据性别分组select * from my_student group by sex;分组的意义：是为了统计数据（按组统计：按分组字段进行数据统计）SQL提供一系列统计函数	count() :统计分组后的记录数：每一组有多少记录	max() ：统计每组中最大的值	min() : 统计最小值	avg() : 统计平均值	sum() : 统计和--分组统计：身高高矮，年龄平均和总年龄select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;分组不统计没有意义count函数：里面可以有两种参数*/字段名，NULL不统计分组会自动排序：根据分组字段：默认升序group by 字段 [asc|desc] ---对分组的结果然后合并之后的整个结果排序多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。select c_id,sex,count(*) from my_student group by c_id,sex ;函数：group_concat(字段)，可以对分组的结果中的某个字段进行字符串连接（保留该组所有的值）select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex;回溯统计：with rollup任何一个分组后的结果都会有一个小组，最后都需要向上级分组进行汇报统计，根据当前分组的字段，就是回溯统计select c_id,count(*) from my_student group by c_id;--回溯统计select c_id,count(*) from my_student group by c_id with rollup;select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex;--多字段分组回溯统计select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex with rollup;having子句与where子句一样：进行条件判断的。where是针对磁盘数据进行判断；进入到内存之后，会进行分组操作，分组结果就需要having来处理。--求出所有班级人数大于等于2的学生人数select c_id,count(*) from my_student group by c_id having  count(*)>=2;having能够使用字段别名，where不能；where是从磁盘取数据，而别名是在数据进入内存后才产生。select c_id,count(*) as total from my_student group by c_id having total>=2;select name as 名字,number from my_student having 名字 like 'Jim';order by子句排序：根据某个字段进行升序或都降序排序，依赖校对集。order by 字段名 asc|desc; asc 升序  desc 降序排序可以进行多字段排序；先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序。--多字段排序：先班级排序，再性别排序select * from my_student order by c_id asc,sex desc;limit子句是一种限制结果的语句：限制数量limit有两种使用方式方案1：只用来限制长度(数据量) ：limit 数据量--查询前两个select * from my_student limit 2;方案2：限制起始位置，限制数量：limit 起始位置,长度--查询从第2个数开始找2个  记录数从0开始编号select * from  my_student limit 1,2;可以用来实现分页，为用户节省时间，提高服务器的响应效率，减少资源浪费