(MySQL进阶)2020.11.17

约束

什么是约束？
　　概述:约束指的是我们可以对表中的字段进行限制、从而来保证数据的完整性(准确性、有效性)，来减少数据的冗余。(冗余指的就是脏数据)
　　主键约束　primary key
　　唯一约束　unique
　　非空约束　not null
　　默认约束　default
　　外键约束　foreign key

主键约束

概述:主键约束primary key 被主键约束的字段、规定值不能重复、而且非空、通常情况下一个表必须有一个主键、来去标识当前一条数据
　　两种使用方式
　　创建表的时候添加主键约束
　　　create table student(
　　　　　sno int primary key,
　　　　　sname varchar(20)
　　　)
　　给已存在的表添加主键约束
　　　alter table student modify sno int primary key;
注意事项:
　　1、如果表中有违法主键约束的数据、那么添加主键约束就会失败
自动增长auto_increment:将主键的维护权交给数据库管理
　　创建表的时候添加主键约束
　　create table student(
　　　　 --主键自动增长
　　　　sno int primary key auto_increment,
　　　　sname varchar(20)
　　)
　　给已存在的表上添加主键自增约束
　　　　alter table student modify sno int primary key auto_increment;
　　插入值方式
　　　　insert into student (sname) values(‘pgone’)
　　　　insert into student values(null,‘马苏’)
　　注意:
　　　　1、自增只能在主键的基础设置
　　　　2、自增每次增量是1
　　　　3、自增要求字段类型必须还是int
　　　　4、自增序列一旦使用下次就不会出现

唯一约束

概述:被约束的字段不能出现重复值、优先级要低于主键
通常情况下我们会对 手机号、邮箱、身份证号这些关键性的字段进行约束
　　使用方式一
　　　create table student(
　　　　sno int primary key auto_increment,
　　　　sname varchar(20),
　　　　--唯一约束
　　　　phone varchar(11) unique
　　　)
　　使用方式二
　　　alter table student modify phone varchar(11) unique;

非空约束

概述:被约束的字段不能够插入null值
　使用方式一
　　create table student(
　　　sno int primary key auto_increment,
　　　sname varchar(20),
　　　--唯一约束
　　　phone varchar(11) unique,
　　　--非空约束
　　　idCard varchar(18) not null
　　)
　使用方式二
　　alter table student modify idCard varchar(18) not null;

默认约束

概述:被约束的字段如果没有给值会自动插入一个默认值
　使用方式一
　　create table student(
　　　sno int primary key auto_increment,
　　　sname varchar(20),
　　　 --唯一约束
　　　phone varchar(11) unique,
　　　-非空约束
　　　idCard varchar(18) not null,
　　　--默认约束
　　　sex varchar(2) default ‘男’
　　)
　使用方式二
　　alter table student modify sex varchar(2) default;

外键约束

概述:描述表与表之间的关系、来保证数据完整性和一致性。
外键约束foreign key
只有两表以上才可以添加外键约束
　　alter table 从表 add constraint foreign key(外键) references 主表(主键)
示例:constraint:约束 foreign key:外键 references:引入|来源
　　alter table idCard add constraint foreign key(pid) references person(pid);
快捷键操作

表关系

概述:在我们的关系型数据库中、表与表中有关系模型、常见关系模型中,可以划分为三种
　　一对一、一对多、多对多

一对一关联

建表原则:在一对一的关系中、我们任选一张表作为从表、在从表中添加一个外键字段、指向主表中的主键字段（含有外键的表就是从表）
注意:
　　1、主表必须有主键
　　2、主表中主键的类型和从表中外键的类型必须一致
　　3、添加数据先插主表、再插从表

一对多关联

建表原则:
　　在多的一方(从表)中添加一个外键字段、指向一的一方(主表)中的主键字段
注意:
　　1、主表必须有主键
　　2、主表中主键的类型和从表中外键的类型必须一致
　　3、添加数据先插主表、再插从表

多对多关联

建表原则
　　创建一张中间表、中间表中至少含有2个字段、分别表示两主表的外键、分别指向两主表中的主键字段
注意:
　　1、主表必须有主键
　　2、主表中主键的类型和从表中外键的类型必须一致
　　3、添加数据先插两主表、再插中间表

连接查询

为什么使用连接查询:我们要查询的数据来自于多张表
　　内连接:查询两表中有关联关系的数据
　　外连接:查询基表中所有数据　关联表中有关联关系的数据

连接查询-内连接

隐式内连接

语法:select 要展示的数据 from 表1,表2 where 关联关系
示例:查询每个部门下的员工信息

显式内连接

语法格式: select 展示的内容 from 表1 inner join 表2 on 关联关系
示例:查询每个部门下的员工信息

连接查询-外连接查询

概述：查询基表中所有内容、关联表中有关联关系的内容
基表：在左外连接中、left关键字左侧的表称为基表,
　　　在右外连接中、right关键字右侧的表就是基本
　　　（左外和右外可以互换关键就在于基本的确定）

左外连接

语法格式 select 展示内容 from 表1　left join 表2 on 关联关系
示例:查询每个部门下的员工信息

右外连接

语法格式 select 展示内容 from 表1 right join 表2 on 关联关系
示例:查询每个部门下的员工信息

聚合函数

SQL中存在一些聚合函数，对一组值进行数据统计，并返回单个值，也被称为组函数。这些特殊函数是不能直接在where后面当做条件使用的，经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。
　注意：
　　1、聚合函数可以在分组后进行使用
　　2、聚合函数可以和having一起使用
聚合函数包括：
　　MAX()求最大值
　　MIN()求最小值
　　AVG()求平均值
　　COUNT()求总数
　　SUM()求总和
员工表为数据支撑
　聚合函数
　1、求员工的最高工资
　　select MAX(e.sal) from emp e
　2、求员工的最低工资
　　select MIN(e.sal) from emp e
　3、求公司的月支出
　　select SUM(e.sal) from emp e
　4、求所有员工的平均薪资
　　select AVG(e.sal) from emp e
　5、求员工的个数
　　select COUNT(*) from emp e
　　select COUNT(empno) from emp e
　　select COUNT(comm) from emp e
　　在求总数的时候尽量使用count(*) 因为它会判断每行中每列的内容是否为空如果一行中有一列的内容不为空它就会认为当前行算一条数据、如果你用comm对列求总数、它就会判断当前列上对应有没有内容、
　　如果有内容就算一条数据、如果没有就不统计(慎用)

分组查询

分组查询关键字：group by
　　原理：先对数据进行分组，再对分组后的数据进行汇总。 分组查询通常用于配合聚合函数，达到分类汇总统计信息的目的。而其分类汇总的本质实际上就是先将信息分组，分组后相同类别的信息会聚在一起，然后通过需求进行统计计算。
1、求每个部门下员工的个数
　select e.deptno,COUNT(*) from emp e group by e.deptno
2、求每种工作的最高薪资
　select MAX(e.sal),e.job from emp e group by e.job
3、求每个工作的平均薪资按照薪资从高排序
　select e.job,AVG(e.sal) from emp e group by e.job order by AVG(e.sal) desc
4、求每个部门的平均工资,要求展示最低工资大于1600
　select AVG(e.sal),e.deptno from emp e group by e.deptno having AVG(e.sal)>1600
5、查询每个工种中平均薪资大于1500
　select e.job,AVG(e.sal) from emp e group by e.job having AVG(e.sal) >1500
　
where与having区别:
　　1、where后面不可以跟聚合函数使用,having后面可以
　　2、where主要用于分组前进行筛选、having主要用于分组后进行筛选
　　3、where优先级要高于having

子查询

当一个查询语句的查询结果作为另外一个查询语句的查询条件来看待这个就称为子查询
在多表操作中，子查询是一种常见的查询方式。

数据库的备份和恢复

命令形式

导出
　1、打开cmd使用管理员命令运行
　2、在里面输入如下命令
　　mysqldump -u用户名 -p密码 要导出的数据库名称 > 导出的地址(盘符+文件名)
　　注意:不要加;
导入
　1、创建一个空的数据库 create database test;
　2、打开cmd使用管理员方式
　3、输入如下命令
　　mysql -u用户名 -p密码 导入的数据库名称 < 文件地址(盘符+文件名)

视图工具形式