域名注册好了怎么样做网站,云开发和普通开发区别,软件开发定制费用,上海开公司需要多少钱一、约束
有时候#xff0c;数据库中数据是有约束的#xff0c;比如 性别列#xff0c;你不能填一些奇奇怪怪的数据~
如果靠人为的来对数据进行检索约束的话#xff0c;肯定是不行的#xff0c;人肯定会犯错~因此就需要让计算机对插入的数据进行约束要求#xff01;
约… 一、约束
有时候数据库中数据是有约束的比如 性别列你不能填一些奇奇怪怪的数据~
如果靠人为的来对数据进行检索约束的话肯定是不行的人肯定会犯错~因此就需要让计算机对插入的数据进行约束要求
约束的使用语法
create 表名 (列名 类型 约束);
#在创建表的时候对某一列数据进行约束操作
在mysql中提供了如下约束
约束说明not null某一列的数据不能为null值unique某一列中的值唯一default规定没有给某列赋值时为默认值primary keynot null 和 unique 的结合。记录身份的标识一般配合自增主键使用foreign key保证一个表中的数据匹配另一个表中的值的参照完整性check保证列中的值符合指定条件
注意 使用约束的话一定会数据库的效率有影响 由于not null、 unique、default、check比较容易这里就对主键(primary key)和外键(foreign key) 进行解释。 主键primary key
主键可以理解为记录的身份标识就像身份证一样是我们每个人的身份标识既不能为空又不能重复。
一张表里只能有一个主键毕竟是身份标识嘛存在多个了以谁为准???
虽然主键只能有一个但是主键不一定是一列可以用多个列共同构成主键(联合主键)。
由于主键具有unique属性因此我们一般配合mysql中 ”自增主键” 来使用也就是会自动分配一个逐个增长的值~
语法
create table 表名 (列名 类型 primary key auto_increment);
如果手动插入一个数据此时数据库会与主键最高的值进行比较如果高了就更新此后自动分配的值也会从这个最高值进行增长 拓展 如果是分布式系统在同一时间插入了一个数据此时自增主键就会出错那么如何处理这种并发场景呢 一般会使用一个公式来形成主键 分布式主键值 时间戳 机房编号/主机号 随机因子 此处使用的是字符串拼接操作!!! 前面两个可以防止在同一时间不同机器的的主键冲突后面的随机因子可以防止同一时间同一机器的主键冲突但是理论上还是有可能存在冲突的~ 外键foreign key
案例引入
现在我们有两张表一张是学生表里面有学号、姓名、班级号字段另一张表示班级表里面有班级号、班级名字段。 我们细细观察可以发现肯定是先有班级表学生的班级号才能确定但是现在突然冒出了一个classId 为 300的学生但是在我们的班级表中不存在这个班级号为了防止这种情况发生我们可以使用外键约束~
语法
create table 表名 (列名 类型, 列名 类型, ......, foreign key (列名) references 主表名(列名));
#我们创建学生表的时候可以这样写#create table student (id int, name varchar(20), classId int, foreign key classId references class(classId));#student表的classId 的所有数据都要出自于class表的classId这一列
#也就是说如果class表中没有这个班级号那么我们插入的学生信息将会报错~
使用外键之后被约束的表就称为子表 另一张表相对的就是父表子表中的那一个字段的数据都要出自父表。当然力的作用是相互的约束也不例外当我们想要删除/修改父表的约束子表的那个字段如果子表已经引用过了那么我们将会删除/修改失败 注意 父表中约束子表的字段必须为主键或者unique 二、表的设计
在实际的场景中有大量的数据我们需要明确当前要创建几个表每个表有什么字段这些表中是否存在一定的联系。
因此根据实际开发就整理出了三大范式~ 一对一
例如一个学生只有一个账号一个账号只属于一个学生。
此时我们设计两张表让其中的一张表存储另一张表的唯一属性这样我们可以通过这个属性就能找到想要的值了。或者也可以将两张表整合成为一张表。 一对多
例如一个学生只能属于一个班级一个班级可以拥有多个学生。
此时我们设计两张表将那个“一”的表中添加上“多”的表中的唯一的字段。
补充 如果是redis这种能够支持数组类型的数据库我们可以不这样设计可以使用一个数组类型用来存储多个学生。如下图 多对多
例如一个学生可以选多门课程一个课程可以被多名学生选择。
此时我们需要再创建一张表来描述这两张表之间的关系。 三、查询增强(进阶)
查询操作有很多的花样但一般实际开发中最最最常使用的还是前面基础的crud操作。
0x 00 聚合查询
之前的表达式查询是对于一条记录上的列与列之间进行运算的如果要针对行与行之间进行运算呢这时候就要用到聚合查询了~
首先来了解一些聚合函数
函数说明count()返回查询数据的个数sum()返回查询数据的总和不是数字没意义avg()返回查询数据的平均值不是数字没意义max()返回查询数据的最大值不是数字没意义min()返回查询数据的最小值不是数字没意义
语法
select count([distinct]表达式) from 表名;
select sum([distinct]表达式) from 表名;
select avg([distinct]表达式) from 表名;
select max([distinct]表达式) from 表名;
select min([distinct]表达式) from 表名;
#注意
#count(*) 和 count(列名)的区别count(*)会把一条为null的数据也统计进去而count(列名)则不会
#如果计算字符串的值需要字符串合法因为mysql会尝试转成double
0x 01 分组查询
分组查询一般会配合聚合查询因为分组查询会把几行数据看做是一行如果不使用聚合查询那么显示的结果为一组中某一个数据。
举例
求各班的平均成绩 那么我们的代码得这样写
select class_id, avg(socre) from class group by class_id; 使用group by的时候还可以搭配条件。此时我们需要区分该条件是分组前的还是分组之后的
如果是分组前使用where条件查询
如果是分组后使用having条件查询
举例
查询各班平均分但是学生成绩不能超过100
select class_id, avg(socre) from class where socre 100 group by class_id; 查询平均分大于100分的班级
select class_id, avg(score) from class group by class_id having avg(score) 100; 0x 02 联合查询
联合查询也就是多表查询就是在多张表上进行查询。但是联合查询也会有一些问题......
联合查询会产生笛卡尔积也就是说表A中的每一条数据都会与表B中每一条数据进行组合如果A表中的数据个数为100表B中的数据个数为100那么最终会产生100*100条数据。但是仔细观察会发现有一些数据是“非法”的。笛卡尔积是简单的排列组合穷举所有情况因此你我们需要筛选数据~
如果要联合n张表进行查询那么我们需要使用n-1个连接条件才不会出现笛卡尔积的现象~ 此时就出现了笛卡尔积但是仔细观察会发现class_id 不同的数据都组合到了一起因此我们需要一个连接条件。 select student.class_id, student.name, class.socre from student, class where student.class_id class.class_id; 由于多表查询一般比较复杂我们可以按照如下的步骤来写 1、先进行指定哪个几个表进行笛卡尔积 2、指定连接条件去除笛卡尔积 3、精简列数据 以上查询都是基于“内连接“的操作然而mysql还提供了”外连接“(左外连接右外连接) 案例引入
现有如下表 此时student的每一条记录都可以在score表中找到对应每一个score中记录也可以在student表中找到对应。
此时我们使用外内接或者内连接查询的结果是一样。 但是如果不存在记录那么内连接与外连接就会天差地别~ 外连接分为左外连接和右外连接。 左外连接以左表为基准保证左侧表的每个数据都会出现在最终的结果集里如果右表没有与之对应的记录则显示null。 select 列名 from 表名1 left join 表2 on 连接条件 右外连接以右表为基准保证右侧表的每个数据都会出现在最终的结果集里如果左表没有与之对应的记录则显示null。 select 列名 from 表名1 right join 表2 on 连接条件
我们还可以用集合图来表示这些连接的关系 补充 以上我们都是针对于两个表进行联合查询的但我们甚至还可以将一个表当做两个表来进行联合查询这样我们就能在一张表中进行 行与行的比较~这种连接方式一般被称为“内连接”。 自连接的查询跟上面的联合查询基本没什么区别需要注意将两张表进行别名操作~ 0x 03 子查询
子查询是将多个简单的SQL语句拼成一个复杂的SQL, 也就是说将某一个查询的结果看做是一张表然后进行操作但本质是在套娃。因此我们实际也是可以用简单的查询完成子查询的操作的~ (合成2048合成复杂SQL)
语法形式
select 列名 from 表名 where 列名 (select 列名 from 表名 where 列名 (套娃下去....));
0x 04 合并查询
合并查询就是将多个sql的查询结果集 合并在一起。合并的两个sql结果集的列需要匹配列的个数和类型得是一致的合并的时候是会进行去重的如果不想要去重得使用 union all
select 列名1 列名2 from 表1 union [all] select 列名3 列名4 from 表2;
最终结果
