写在前面
我们上篇博客,学习了MySQL的基础增删查改操作!
当我们需要将多张表的数据组织在一起或是多张表多有联系,那么该如何去创建表!
如何实现增删查改功能呢!
我们本篇博客就带大家来学习一下mysql表的一些稍微复杂的操作!
数据库约束
not null- 指示某列不能存储 NULL 值。
unique- 保证某列的每行必须有唯一的值。
default - 规定没有给列赋值时的默认值。
primary key - not null 和 nuique的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。
check - 保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略check子句。
null约束
创建表时可以指定某列不为空!
我们指定了student_1表中name和score不能为空!
当我们插入一个空值时:
可以看到但我们name为空时,插入失败!
正确插入方式是要将name和score都赋予值,不能为空!
注意:
这里的指定列不为空语法是:
属性 类型 not null
只需要在某一列后面写上not null
切记:is not null是条件语句!
nuique:唯一约束
指定某一列中的数据,唯一的,不重复!
就比如我们的身份证号:每个人都是唯一的!
我们设置了id为(unique)唯一约束,那么id就不能重复!
default默认值约束
指定插入值时,name为空 ,有默认值!
就相当于,我们不想让对方知道我们的姓名,我们就采用匿名的方式!
这就好比现在双减政策下,我们不能将学生的成绩排名公之于众,只有采用这种匿名的方式,很好解决了!
primary key:主键约束
指定id为主键!
那么id既不能是null也需要瞒住unique唯一性;
扩展: 对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1
--主键约束 primary key 是 unique 和 not null的结合! id int primary key auto_increment
当我们使用了auto_increment时,如果主键没有赋值,那么自动赋值为该表中的最大值加1;
foreign key:外键约束
外键用于关联其他表的主键(primary key)或唯一键(unique)
比如好多表互相有联系时!那么我们就需要用到foreign key外键约束!
SQL语句
foreign key (字段名) references 主表(列名)
学生信息和班级信息就需要使用外键约束!
当我们外键约束的表中插入,主表中不含的数据时,插入数据失败!!!
因为没有103这个班级,所以这个学生信息有误!!!
check约束
MySQL使用时不报错，但忽略该约束
当我们table表中的某些字段的值含有那么几个默认的选项! 那么我们就可以用check检查是否满足!
比如 性别 属性 只有男或者女!当我们插入的数据性别不为这二者时显然信息错误! 而check便可以很好的检测出!
然而并未起到作用…这个了解一下即可!!!
表的设计
三大范式
一对一
一个人只有一个身份证号!!!
一对多
一个班级里有好多学生
多对多
一个课程有多个学生上,一个学生要上多个课程
新增
我们可以在一张表中插入其他表的查询结果!!!
将student_2表中的数据插入到student_1中
必须是两个表对应的字段类型匹配才可以!!!
我们已经成功将student_2表中的名字信息插入到了student_1表!
查询
聚合查询
聚合函数
常见的的统计计数,计数平均值等,可以通过聚合函数来实现,常见的聚合函数如下:
count
查询多少学生!
sum
查询总分
avg
查询每科平均成绩
max
查询最大值
min
查询最小值
group by 子句
select中使用 group by 子句可以对指定列进行分组查询。需要满足：使用 group by进行分组查询时,select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。
也就是说,group by子句可以通过列进行分组!
例如下方的emp中根据 role这一列我们可以将该表分成多组!
根据role分组
查询不同role组中的人数 平均薪水,最高薪水,最低薪水!
having
group by子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要用having显示平均工资低于1500的角色和它的平均工资!
联合查询
实际上我们的数据库,查询数据时,如果有多张表的数据,我们采用多表查询,多表查询通常采用笛卡尔积的方式!
啥是笛卡尔积呢???
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 [1] --来自百度 。
通俗点说就是,R表和S表,笛卡尔积后变成了RXS表如果R表有n*m S表有x*y,那么RXS就是n*x行 m*y列!!!
那么笛卡尔积有什么用呢?
当我们需要查询一个学生表对应的班级表时,我们便可以借助笛卡尔积!!!
但是我们可以看到使用笛卡尔积后,学生表和班级表中的班级id并没有一一对应! 所以当我们使用笛卡尔积查看两张表时需要加一些限制条件!!!
当我们加了一个限制条件后,where class.id = student.class_id;
我们便可以将两张表的对应关系找出来!~!!!
内连接
SQL语句
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
我们可以通过上述语句进行内连接!!!
使用where限制条件!!!
查找qq为112的学生!!!
使用join on限制条件!!!
表1 join 表2 on 条件
外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
-- 左外连接，表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; -- 右外连接，表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;
我们先查看这两张表的内容!!
当我们使用内连接,左外连接,右外连接时区别如下:
内连接:
左外连接:
右外连接:
自连接
自连接是指同一张表连接自身查询!
案例:
显示所有数学成绩比语文成绩高的人的成绩的学生信息!
如果我们这张表中的每一门成绩都在一列中,那么显然我们只要使用一个where判断一下即可!
比如:
但是如果每门的成绩并不是在单独的一列中,那该如何查询呢?
这时就需要用到自连接了!!!
就比如下方我们有三张表 分别为成绩表和学生表和课程表!
如果我们要查询java程序设计成绩比数据库原理成绩高的学生信息该如何操作呢?
自连接就是将行改成列!!!
将一直表起多个别名,就得到多张表!!!
通过笛卡尔积,我们就可以将这两张表中的行变成列!
我们先对成绩表进行笛卡尔积!!!
一开始的成绩表:
可以看到学生id为1的学生的课程成绩都在一列中!!
而我们要将不同科目的成绩放在不同的列才能进行比较!!!
进行笛卡尔积后的成绩表:
选择课程java程序设计和数据库原理的课程id!!!
我们将两门课的成绩分别放在了一个列中了!!!
我们再进行限定就可以找到满足条件的学生信息
最后查询java程序设计比数据库原理的课程成绩高的学生信息!!!
步骤有点繁琐!!!
原理就是将行转化成列!
将一张表期别名利用笛卡尔积转化!!!
子查询
子查询简单讲就是,多个sql查询语句的嵌套!!!
就是套娃!!!
不建议的操作! 反人类!
代码可读性差
了解一下即可!
如果我们要查询java程序设计的成绩!!
我们第一步要获取到这个课程的id
然后通过这个课程id在成绩表中寻找即可!!!
子查询就一步即可!!!
显然这玩意除了能装外没啥用!!!
合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用union和union all时，前后查询的结果集中，字段需要一致!
我们就是可以用union 和 union all在相同表或者不同表中合并多个select 相当于 单表查询中的or
union
查询刘备和曹操的成绩信息:
or
union
union和union all可以合并多个sql语句! 可以在不同的表中进行查询!!!
union all
union all 和union唯一的区别就是 union all不会去掉结果集中的重复行! 而union会进行去重!
