1.字段属性
主键, 唯一键和自增长.
1.1 主键
主键: primary key,主要的键. 一张表只能有一个字段可以使用对应的键, 用来唯一的约束该字段里面的数据, 不能重复: 这种称之为主键.
一张表只能有最多一个主键.
主键对应的字段中的数据不允许重复: 一旦重复,数据操作失败(增和改)
(1)增加主键
SQL操作中有多种方式可以给表增加主键: 大体分为三种.
- 1: 在创建表的时候,直接在字段之后,跟
primary key
关键字(主键本身不允许为空)
优点: 非常直接; 缺点: 只能使用一个字段作为主键 - 2: 在创建表的时候, 在所有的字段之后, 使用
primary key
(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键) - 3: 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.
Alter table 表名 add primary key(字段列表)
;
前提: 表中字段对应的数据本身是独立的(不重复)
(2)更新主键 & 删除主键
没有办法更新主键: 主键必须先删除,才能增加.
删除:Alter table 表名 drop primary key
;
(3)主键分类
在实际创建表的过程中, 很少使用真实业务数据作为主键字段(业务主键,如学号,课程号); 大部分的时候是使用逻辑性的字段(字段没有业务含义,值是什么都没有关系), 将这种字段主键称之为逻辑主键.
1.2 自动增长
当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.
自增长通常是跟主键搭配.
(1)新增自增长
自增长特点: auto_increment
- 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)
- 自增长字段必须是数字(整型)
- 一张表最多只能有一个自增长
(2)自增长使用
当自增长被给定的值为NULL或者默认值的时候会触发自动增长.
是否是自增长可以通过查看表创建语句看到.
(3)修改自增长
字段改变: 必须先删除自增长,后增加(一张表只能有一个自增长)
修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效) Alter table 表名 auto_increment = 值
;
可以修改变量实现不同的效果: 修改是对整个数据修改,而不是单张表: (修改是会话级)Set auto_increment_increment = 5
:一次自增5
(4)删除自增长
自增长是字段的一个属性: 可以通过modify来进行修改(保证字段没有auto_increment即可)
Alter table 表名 modify 字段 类型
;
1.3 唯一键
唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题.
唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)
(1)增加唯一键
基本与主键差不多: 三种方案
- 1: 在创建表的时候,字段之后直接跟unique/ unique key
- 2: 在所有的字段之后增加unique key(字段列表); – 复合唯一键
- 3: 在创建表之后增加唯一键
(2)更新唯一键 & 删除唯一键
更新唯一键: 先删除后新增(唯一键可以有多个: 可以不删除).
删除唯一键
Alter table 表名 drop unique key
; – 错误: 唯一键有多个
Alter table 表名 drop index 索引名字
; – 唯一键默认的使用字段名作为索引名字
1.4 索引
几乎所有的索引都是建立在字段之上.
索引: 系统根据某种算法, 将已有的数据(未来可能新增的数据),单独建立一个文件: 文件能够实现快速的匹配数据, 并且能够快速的找到对应表中的记录.
(1)索引的意义
- 提升查询数据的效率
- 约束数据的有效性(唯一性等)
(2)增加索引的前提条件:
索引本身会产生索引文件(有时候有可能比数据文件还大) ,会非常耗费磁盘空间.
如果某个字段需要作为查询的条件经常使用, 那么可以使用索引(一定会想办法增加);
如果某个字段需要进行数据的有效性约束, 也可能使用索引(主键,唯一键)
Mysql中提供了多种索引
- 主键索引: primary key
- 唯一索引: unique key
- 全文索引: fulltext index
- 普通索引: index
2. 关系
将实体与实体的关系, 反应到最终数据库表的设计上来: 将关系分成三种: 一对一, 一对多(多对一)和多对多.
所有的关系都是指的表与表之间的关系.
2.1 一对一
一对一: 一张表的一条记录一定只能与另外一张表的一条记录进行对应; 反之亦然.
2.2 一对多
一对多: 一张表中有一条记录可以对应另外一张表中的多条记录; 但是返回过, 另外一张表的一条记录只能对应第一张表的一条记录. 这种关系就是一对多或者多对一.
2.3 多对多
多对多: 一张表中(A)的一条记录能够对应另外一张表(B)中的多条记录; 同时B表中的一条记录也能对应A表中的多条记录: 多对多的关系
3. 范式
Normal Format, 是一种离散数学中的知识, 是为了解决一种数据的存储与优化的问题: 保存数据的存储之后, 凡是能够通过关系寻找出来的数据,坚决不再重复存储: 终极目标是为了减少数据的冗余.
范式: 是一种分层结构的规范, 分为六层: 每一次层都比上一层更加严格: 若要满足下一层范式,前提是满足上一层范式.
六层范式: 1NF,2NF,3NF…6NF, 1NF是最底层,要求最低;6NF最高层,最严格.
Mysql属于关系型数据库: 有空间浪费: 也是致力于节省存储空间: 与范式所有解决的问题不谋而合: 在设计数据库的时候, 会利用到范式来指导设计. 一般情况下,只有前三种范式需要满足.
3.1 1NF
第一范式: 在设计表存储数据的时候, 如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理(拆分),那么说表的设计不满足第一范式: 第一范式要求字段的数据具有原子性。
3.2 2NF
第二范式: 在数据表设计的过程中,如果有复合主键(多字段主键), 且表中有字段并不是由整个主键来确定, 而是依赖主键中的某个字段(主键的部分): 存在字段依赖主键的部分的问题, 称之为部分依赖: 第二范式就是要解决表设计不允许出现部分依赖.
3.3 3NF
第三范式: 理论上讲,应该一张表中的所有字段都应该直接依赖主键(逻辑主键: 代表的是业务主键), 如果表设计中存在一个字段, 并不直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键: 把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖. 第三范式就是要解决传递依赖的问题.
3.4 逆规范化
有时候, 在设计表的时候,如果一张表中有几个字段是需要从另外的表中去获取信息. 理论上讲, 的确可以获取到想要的数据, 但是就是效率低一点. 会刻意的在某些表中,不去保存另外表的主键(逻辑主键), 而是直接保存想要的数据信息: 这样一来,在查询数据的时候, 一张表可以直接提供数据, 而不需要多表查询(效率低), 但是会导致数据冗余增加.
逆规范化: 磁盘利用率与效率的对抗
4. 数据高级操作
增删改查
4.1 新增数据
基本语法 Insert into 表名 [(字段列表)] values (值列表)
;
(1)主键冲突
在数据插入的时候, 若主键对应的值已经存在: 插入一定会失败!
当主键存在冲突的时候(Duplicate key),可以选择性的进行处理: 更新和替换
- 更新:在原来的数据仅仅修改变化的内容
Insert into 表名[(字段列表:包含主键)] values(值列表) on duplicate key update 字段 = 新值
; - 替换:直接使用更新语句全部重写
Replace into 表名 [(字段列表:包含主键)] values(值列表)
;
(2)蠕虫复制
蠕虫复制: 从已有的数据中去获取数据,然后将数据又进行新增操作: 数据成倍的增加.
复制旧表结构创建新表:Create table 表名 like 数据库.表名
;
复制旧表数据到新表:Insert into 表名[(字段列表)] select 字段列表/* from 数据表名
;
意义:
- 从已有表拷贝数据到新表中
- 可以迅速的让表中的数据膨胀到一定的数量级: 测试表的压力以及效率
4.2 更新数据
基本语法Update 表名 set 字段 = 值 [where条件]
;
高级新增语法 Update 表名 set 字段 = 值 [where条件] [limit 更新数量]
;
limit:指这条语句影响的数量,注意没有等号。
4.3 删除数据
与更新类似: 可以通过limit来限制数量:Delete from 表名 [where条件] [limit 数量]
;
删除: 如果表中存在主键自增长,那么当删除之后, 自增长不会还原
4.4 查询数据
基本语法:Select 字段列表/* from 表名 [where条件]
;
完整语法:
Select [select选项] 字段列表[字段别名]/*
from 数据源
[where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];
(1)Select选项
Select选项: select对查出来的结果的处理方式
- All: 默认的,保留所有的结果
- Distinct: 去重, 查出来的结果,将重复给去除(所有字段都相同):
select distinct * from table1;
(2)字段别名
字段别名: 当数据进行查询出来的时候, 有时候名字并一定就满足需求(多表查询的时候, 会有同名字段). 需要对字段名进行重命名: 别名
语法:字段名 [as] 别名
;
select name as'姓名',number as '学号' from table1;
(3)数据源
数据源: 数据的来源, 关系型数据库的来源都是数据表: 本质上只要保证数据类似二维表,最终都可以作为数据源.
数据源分为多种: 单表数据源, 多表数据源, 查询语句
- 单表数据源:
select * from 表名
; - 多表数据源:
select* from 表名1,表名2...
;
从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接)。
子查询: 数据的来源是一条查询语句(查询语句的结果是二维表)
Select * from (select 语句) as 表名
;
(4)Where子句
Where子句: 用来判断数据,筛选数据.
Where子句返回结果: 0或者1, 0代表false,1代表true.
判断条件: 下面的符号都可以使用
比较运算符: >, <, >=, <= ,!= ,<>, =, like, between and, in/not in
逻辑运算符: &&(and), ||(or), !(not)
Where原理: where是唯一一个直接从磁盘获取数据的时候就开始判断的条件: 从磁盘取出一条记录, 开始进行where判断: 判断的结果如果成立保存到内存;如果失败直接放弃.
Between本身两边都是闭区间; between左边的值必须小于或者等于右边的值
(5)Group by子句
Group by:分组的意思, 根据某个字段进行分组(相同的放一组,不同的分到不同的组)
基本语法: group by 字段名;
分组的意思: 是为了统计数据(按组统计: 按分组字段进行数据统计)
SQL提供了一系列统计函数
- Count(): 统计分组后的记录数: 每一组有多少记录
- Max(): 统计每组中最大的值
- Min(): 统计最小值
- Avg(): 统计平均值
- Sum(): 统计和
Count函数: 里面可以使用两种参数: *代表统计记录,字段名代表统计对应的字段(NULL不统计)
分组会自动排序: 根据分组字段,默认升序
对分组的结果然后合并之后的整个结果进行排序:Group by 字段 [asc|desc]
多字段分组: 先根据一个字段进行分组,然后对分组后的结果再次按照其他字段进行分组
字符串字段连接函数: 可以对分组的结果中的某个字段进行字符串连接(保留该组所有的某个字段): group_concat(字段)
;
回溯统计: with rollup
: 任何一个分组后都会有一个小组, 最后都需要向上级分组进行汇报统计: 根据当前分组的字段. 这就是回溯统计: 回溯统计的时候会将分组字段置空.
(6)Having子句
功能: 与where子句一样: 进行条件判断的.
区别:
- Where是针对磁盘数据进行判断: 进入到内存之后,会进行分组操作: 分组结果就需要having来处理。
- Having能做where能做的几乎所有事情, 但是where却不能做having能做的很多事情.
分组统计的结果或者说统计函数都只有having能够使用.
Having能够使用字段别名: where不能:
where是从磁盘取数据,而名字只可能是字段名: 别名是在字段进入到内存后才会产生.
(7)Order by子句
Order by: 排序, 根据某个字段进行升序或者降序排序, 依赖校对集.
使用基本语法:
Order by 字段名 [asc|desc]
; – asc是升序(默认的),desc是降序
多字段排序: 先根据某个字段进行排序, 然后排序好的内部,再按照某个数据进行再次排序:
(8)Limit子句
Limit子句是一种限制结果的语句: 限制数量.
Limit有两种使用方式:
- 1: 只用来限制长度(数据量):
limit 数据量
; - 2: 限制起始位置,限制数量:
limit 起始位置,长度
;
【整理自网上资料】