数据库基础_3

一、数据完整性

1、作用：保证用户输入的数据保存到数据库中是正确的

2、确保数据的完整性 = 在创建表时给表中添加约束

3、完整性的分类：

• 实体完整性
• 域完整性
• 引用完整性

4、实体完整性：标识每一行数据是不重复的

   实体：即表中的一行（一条记录）代表一个实体（entity）

   约束类型：主键约束（primary key）唯一约束（unique）自动增长列（auto_increment）

• 主键约束：每个表中要有一个主键，特点，数据唯一，且不能为null
• • 添加方式：
  • create table student(id int primary key,name varchar(50));
  • create table student(id int,name varchar(50),primary key(id));
  • create table student(classid int,stuid int,name varchar(40),primary key(classid,stuid));//此种方法优势在于，可以创建联合主键
  • create table student(id int,name varchar(50));alter table student add constraint pk_stu_id primary key(id);
• 唯一约束：数据不能重复
• • 添加方式：
  • create table student(id int primary key,name varchar(50) unique);
• 自动增长列
• • 给主键添加自动增长的数值，列只能是整数类型
  • create table student(id int primary key auto_increment,name varchar(50));

5、域完整性：限制此单元格的数据正确，不对照此列的其他单元格比较

   域代表当前单元格

   域完整性约束：数据类型 非空约束（not null） 默认值约束（default） check约束（MySQL不支持）check(sex = '男' or sex = ‘女’)

• 数据类型
• 非空约束
• • create table student(id int primary key,name varchar(50) not null,sex varchar(10));
• 默认值约束
• • create table student(id int primary key,name varchar(50) not null,sex varchar(10) default '男');

6、引用完整性（参照完整性） 只与插入数据修改删除数据时数据的完整性有关，与查询无关

• 外键约束foreign key
• create table student(sid int primary key,name varchar(50) not null,sex varchar(10) default ‘男’);
• create table score(id int,score int,sid int,constraint fk_score_sid foreign key(sid) references student(id));
• 外键列的数据类型一定要与主键的类型一致
• 第二种添加外键方式：
• • alter table score add constraint fk_stu_score foreign key (sid) references stu(id);

7、表与表之间的关系

• 一对一
• • 例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
  • 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
  • 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
• 一对多
• • 最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
• 多对多
• • 例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

二、多表查询

1、多表查询有如下几种：

• 合并结果集 union union all
• 连接查询
• • 内连接 [inner]join on
  • 外连接 outer join on
  • • 左外连接 left [outer] join
    • 右外连接 right[outer] join
    • 全外连接 (mysq不支持) full join
  • 自然连接 natural join
• 子查询

2、合并结果集

• 作用：合并结果集就是把两个select语句的查询结果合并到一起
• 合并结果集有两种方式
• • union 去除重复记录 select * from t1 union select * from t2;
  • union all 不去除重复记录 select * from t1 union all select * from t2;  
• 要求：被合并的两个结果：列数、列类型必须相同

3、连接查询

• 连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
  那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
• 也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了
• 使用主外键关系作为条件去除无用信息：select * from emp,dept where emp.deptno=dept.deptno
• select emp.ename ,emp.sal , emp.comm ,dept.name from emp,dept where emp.deptno=dept.deptno
• select e.ename ,e.sal , e.comm ,d.name from emp as e,dept as dwhere e.deptno=d.deptno
• 内连接
• 上面的连接语句就是内连接，但它不是SQL标准中的查询方式
• 标准的内连接为：
• select * from emp e inner join dept d on e.deptno=d.deptno;
• 内连接的特点：查询结果必须满足条件
• 外连接
• 外连接的特点：查询出的结果存在不满足条件的可能
• 左连接：select * from emp e left outer join dept d on e.deptno=d.deptno
• 左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示null
• 这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。
• 右连接：select * from emp e right outer join dept d on e.deptno=d.deptno
• 右连接是先查询出右表（即以右表为主），然后查询左表，左表中满足条件的显示出来，不满足条件的显示null
• 例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。
• 连接查询心得
• 连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
  两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
• select s.stuid,s.stuname,c.score,cc.name from student s,score c,coure cc where s.stuid=c.stuid and c.courseid=cc.courseid;
• select s.stuid,s.stuname,c.score,cc.name from student s join score c on s.stuid=c.stuid join course cc on c.courseid=cc.courseid;
• 多个表查询，n表连接查询，至少要有n-1个条件

4、自然连接

• 自然连接无需你给出主外键等式，它会狄总找到这一等式
• 两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会自然连接找到
• select * from emp natural join dept;
• select * from emp natural left join dept;
• select * from emp natural right join dept;

5、子查询

• 一个select语句中包含另外一个完整的select语句，子查询就是嵌套查询，即select中包含select，如果一条语句中存在两个，或两个以上select，那么就是子查询语句了
• 子查询出现的位置：
• • where后，作为被查询的条件的一部分
  • from后，作表
• 当子查询出现在where后作为条件时，还可以使用一下关键字:any all
• 子查询结果集的形式
• • 单行单列（用于条件）
  • 单行多列（用于条件）
  • 多行单列（用于条件）
  • 多汗多列（用于表）
• 查询与scott用一个部门的员工
• select * from emp where deptno=(select deptno from emp where ename='scott');
• 查询工资高于jones的员工
• select * from emp where sal > (select sal from emp where ename = 'jones');
• 工资高于30号部门所有人的员工信息
• select * from emp where sal>(select max(sal) from emp where deptno = 30);
• select * from emp where sal> all(select sal from emp where deptno = 30);
• 查询工作和工资与martin完全相同的员工信息
• select * from emp where(job,sal) in (select job,sal from emp where ename='martin');
• 有2个以上直接下属的员工信息
• select * from emp where empno in(select mgr from emp group by mgr having count(mgr)>=2);
• 查询员工编号为7788的员工名称，员工工资，部门名称，部门地址
• select e.ename,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and empno='7788';
• select e.ename,e.sal,d.dname,d.loc from emp e,(select dname,loc,deptno from dept)d where e.deptno=d.deptno and e.empno = 7788;

6、自连接

• 求7369员工编号、姓名、经理编号和经理姓名
• select e1.empno,e1.ename,e2.mgr,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.empno=7369;

7、求各个部门薪水最高的员工所有信息

   select e1.* from emp e1,(select depno,max(sal) maxSal from emp group by deptno) e2 where e1.sal=e2.maxSal and e1.deptno = e2.deptno;

三、MySQL中的函数

1、时间日期相关函数

2、字符串相关函数

3、数学相关函数

四、MySQL数据库的备份与恢复

1、生成sql脚本 导出数据

• 在控制台使用mysqldump命令可以用来生成指定数据库的脚本文件，但是要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句，所以在恢复数据时，还需要自己手动创建一个数据库之后再去恢复数据
• mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径

2、执行sql脚本 恢复数据

• 前提：必须先创建数据库名
• 执行sql脚本需要登录mysql，然后进入指定数据库，才可以执行sql脚本
• 执行sql脚本不只是用来恢复数据库，也可以在平时编写sql脚本，然后使用执行sql脚本来操作数据库
• source c:\mydb1.sql
• 还可以通过以下的方式来执行脚本文件
• musql -u用户名 -p密码 数据库<要执行脚本文件路径
• 这种方式无需登录mysql