列属性
主键,唯一键和自增长
主键: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 ; --一次自增5
show 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 key
2、唯一索引 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 update
room = '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限制数量
--删除数据:限制数量为2
delete 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;
可以用来实现分页,为用户节省时间,提高服务器的响应效率,减少资源浪费
mysql学习二之列属性、关系(一对一,一对多,多对多),范式,高级增删改查操作...
最新推荐文章于 2022-08-09 08:30:00 发布