约束,三大范式,多表查询

数据库备份和还原

      图形化界面工具:Navicat

       1.备份:

             选中数据库右击-->转储sql文件--->结构和数据-->指定磁盘中sql文件存储的位置.

    2.还原:先通过NaVicat创建一个数据库(需要和sql文件中的数据库保持同名)-->右键运行sql文件-->浏览磁盘中存储的sql文件,点击运行,最后--->刷新

  dos指令操作

     1.备份:

       在不登录的情况下使用mysqldump  -u登录名  -p登录密码  备份数据库名>存储磁盘的位置(绝对路径)

     2.还原

      登录mysql首先创建同名的数据库,然后使用当前的创建的数据库   use   创建的数据库名

      然后  source  磁盘中sql 文件存储的位置

备份:

还原:

数据库表的约束

    概述:

            对表中的数据进行限定,保证数据的有效性,完整性和正确性.一个表一旦添加了约束,那么不正确的数据将无法添加进来,所以一般约束在创建表设定字段的时候添加上 .

   主键约束: 

            主键:一般是用来唯一标识数据库表中的某一条记录,不能为空

            通常情况下，一般会给表添加一个id字段用作唯一标识，设置为主键，主键一般是提供给数据库操作使用的（查询，修改，删除等等），主键不能重复，也不能为空。

           比如：身份证号，手机号 ，学生学号，员工工号。

           语法：使用关键字primary key

           操作：

-- 查询
 
select * from user;

-- 非法数据插入  唯一 不重复

insert into  user values(7,'0008','小花','12313323','女',19);



-- 非法数据插入  null值插入 非空
insert into  user values(null,'0008','小花','12313323','女',19);

-- 删除主键  sql语句

alter table user drop primary key;

-- 添加主键 
alter table user add primary key(id);

           主键自增:如果希望在添加记录时,不设定表中主键值,系统会自动给主键添加自增值.

                 语法:auto_increment  表示自动自增

-- 非法数据插入   null值插入
insert into  user values(null,'0008','小花','12313323','女',19);

                 修改自增的起始值

                 Alter table 表名 auto_increment = 起始值;

                 

   唯一约束:

          概念:字段值唯一,不允许重复

          关键字:unique

          语法:字段名  字段数据类型  unique

          操作:

       

-- 插入数据
  insert into role values(null,'CEO');
 insert into role values(null,'manager');
 insert into role values(null,'CTO');
 
-- 查询角色表

 select * from role;
  insert into role values(null,null);
  

                  备注:如果插入的是null表明是没有数据,不存在数据重复问题,可以重复插入.

   非空约束:

         概念:记录中的某个字段不能为null

         语法:字段名  字段的数据类型  not null

         

-- 创建一张部门表
 create table department(
   id  int primary key auto_increment,-- id 主键自增
  departmentname  varchar(10) not null -- 部门不能为空
  
)


insert into department values(null,'总裁办');
insert into department values(null,'总裁办12');
insert into department values(null,'总裁办2');
insert into department values(null,'总裁办3');
insert into department values(null,'总裁办4');


-- 查询该表数据
select * from department;
insert into department values(null,null);

   默认约束:

        概念:当没有给字段赋值,系统会赋上一个指定的默认值

        语法:字段名  字段数据类型  default 默认值

  

-- 创建一张表  employee 员工表
create table employee(
   id int primary key auto_increment,
   username varchar(10) not null,
   gender  varchar(1) default '男',
  age int

);
insert into employee(id ,username ,age) values(null,' 小孙',20);
--  查询员工表

select * from employee;

insert into employee (id, username,gender,age) values(null,'小孙','女',20);

        备注:如果给表中的某个字段即添加了唯一约束,那么该字段是不是主键呢?

            一般情况下,一张表中只能有一个主键

         

   外键约束:

           语法:constraints    外键约束名称    foreign   key(外键的字段名称)   reference   主表表名(主键字段名)

          在已有表中添加外键约束:alter   table 从表表名 add constraints 外键约束名称   foreign  key (外键的字段自称)   reference  主表表名(主键字段名)

          删除外键语法:alter table  从表表名    drop foreign  key  外键名称;

          级联操作:

                   注意:在从表中修改主表中不存在的数据是不合法的

                   在主表中,删除从表中已经存在的主表信息,是不合法的,直接删除主表(从表中有记                          录数据关联)会删除失败.

                   概念 :在修改或者删除主表中的主键时,同时他会更新或者删除从表中的外键值,这种动作我们称之为级联操作.

                   语法:

                              更新级联  on  update  cascade   级联更新   只能是创建表的时候创建级联关系.当更新主表中主键,从表中的外键字段会同步更新.

                               删除级联  on delete  cascade  级联删除  当删除主表中的主键时,从表中的含有该字段的记录值会同步删除.

-- 给从表student添加级联操作
create table student(
    s_id int PRIMARY key ,
    s_name VARCHAR(10) not null,
    s_c_id int,
-- constraint  外键约束名称  foreign key(外键的字段名称)  references 主表表名(主键字段名)
	 CONSTRAINT	stu_cour_id FOREIGN key(s_c_id)	REFERENCES	course(c_id)				-- 给s_c_id 添加外键约束
	 ON UPDATE CASCADE	ON DELETE CASCADE
)

insert into student VALUE(1,'小孙',1),(2,'小王',2),(3,'小刘',4);
insert into student VALUE(4,'小司马',1),(5,'小赵',1),(6,'小钱',1);

-- 查询学生表中的记录
select * from student;

-- 级联操作。
-- 更新级联  on update cascade   级联更新 只能是创建表的时候创建级联关系。当更新主表中的主键，从表中的外键字段会同步更新。
update course set c_id = 10 where c_id = 1;
-- 删除级联  on delete cascade   级联删除  当删除主表中的主键时，从表中的含有该字段的记录值会同步删除。

delete from course where c_id = 10;

     约束总结:

约束名	关键字	描述
主键	primary key	唯一不为空
默认	default	插入数据, 该字段没有赋值,系统会自动赋指定的默认值
非空	not null	该字段不能为null(空)
唯一	unique	该字段的值在整个表中只能出现1次
外键	foreign key	从表中添加外键字段,关联主表中的主键字段

 

  表与表之间的关系:

           可以分成三类:

                    1.一对一的关系:一般情况下,一对一的关系基本不用,当两张表是一对一的关系,合成一张表,例如 人和身份证号   学生和学生证号

                    2.一对多的关系: 在表关系中最多的情况,也是最常见的,比如:部门和员工

                    3.多对多的关系:从两个方向观察发现都是1-n关系,比如:学生表和课程表,企业和应聘者(双选会)

                    4. 操作:

                                学生表,课程表,多对多关系

                                用户表,角色表  一对多关系

-- 创建学生表
drop table student;
create table student(
		
		s_id int PRIMARY key auto_increment, -- 主键约束
    s_name VARCHAR(10) not null, -- 非空约束
		s_num int unique not null -- 唯一约束，非空约束

);
-- 创建课程表
drop table course;
create table course (
     
    c_id int PRIMARY key auto_increment COMMENT '课程id', -- 主键约束  
		c_name VARCHAR(10) not null unique COMMENT '课程名称' -- 唯一 非空约束
);

-- 中间表
create table t_stu_cour (
    
     s_id int,
     c_id int,

     CONSTRAINT stu_id foreign key(s_id) REFERENCES student(s_id),
     CONSTRAINT cour_id foreign key(c_id) REFERENCES course(c_id)
);

-- 创建用户表
drop table user;
create table user (

  u_id int PRIMARY key auto_increment COMMENT '用户表主键id', -- 主键约束
  u_name VARCHAR(10) not null, -- 非空约束
  u_phone VARCHAR(11) unique, -- 唯一约束
  u_rid int, -- 需要添加的外键信息
  CONSTRAINT user_rid FOREIGN key(u_rid) REFERENCES role(r_id) 
);
-- 创建角色表
drop table role;
create table role (

   r_id int PRIMARY key auto_increment COMMENT '角色表主键id', -- 主键约束
   r_name VARCHAR(10) not null UNIQUE, -- 唯一约束 非空约束
   r_desc VARCHAR(255) 
)
-- 如果两张表是一对多关系 设计创建时，先创建主表，再创建从表

                   5.表与表之间关系总结

表之间关系	关系维护,创建
一对一	合表,互为外键约束,表之间关系很少
一对多	在从表(多的那一方)的那一方创建外键,关联主表的主键字段,先创建主表,再创建从表
多对多	创建中间表,中间表分别添加外键约束关联各自对应的主键

数据库设计的范式

    什么是范式?

         在设计数据库的时候,需要遵从的规范要求,根据这些规范要求设计出合理的数据库,这些规范被称为范式.这些范式针对的是关系型数据库,

         目前关系型数据库的范式有六种:第一范式(1NF),第二范式(2NF),第三范式(3NF),第四范式(4NF),第五范式(完美范式)(5NF),巴斯-科德范式(BCNF),各种范式呈递次规范.越高的范式数据库的冗余性就越低.

         前三种范式介绍:

              第一范式(1NF):数据库中的每一列是不可分割的原子数据项.

              第二范式(2NF):在第一范式的基础上,非码属性必须完全依赖于码.(在第一范式的基础上消除非主属性对码的部分函数依赖)

                     概念:

                       1.函数依赖   A--B;  如果通过A属性(属性组)的值,可以确定唯一的B属性值,可以称B依赖于A. 

                       2.完全函数依赖   A--->B 如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值.

                       3.部分函数依赖  A-->B  如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一个或某一些属性值即可.

                       4.传递函数依赖  A-->B-->C 如果通过A属性(属性组)的值,可以唯一确定B属性的值.在通过B属性的值可以唯一确定C属性的值,可以称C传递依赖于A 

                      5.码:如果在一张表中一个属性或者属性组,被其他所有属性所完全依赖,则称这个属性 (属性组)为该表的码.

                              主属性:码属性组的所有属性 

                          非主属性:除主属性外的其他属性

              第三范式(3NF):在第二范式的基础上,任何非主属性不依赖于其他的非主属性(在2NF基础上, 消除函数传递依赖)

             三大范式总结:

范式	特征
1NF	表中的每一列具有原子性,表中的每一列不可分割
2NF	消除部分函数依赖,一张表只做一件事
3NF	消除传递函数依赖,表中的每一列都直接依赖于码(主键),不需要通过其他的字段(列)间接依赖于主键

多表连接查询

     1.分类:内连接(显示内连接,隐式内连接),外连接(左外连接,右外连接).

     2.笛卡尔积现象:   左表中的每条记录和右表中的每条记录全关联组合,这种效果就称为笛卡尔积现象

     3.消除笛卡尔积现象: 添加过滤条件.使用where条件语句,达到过滤无效的数据.

     4.内连接:

         4.1  隐式内连接: 省略掉内连接的关键字   inner  join

                  语法:select  字段列表  from  表名1,表名2,...   where  条件语句

                  操作:select * from  student,course   where s_c_id=c_id;

         4.2  显示内连接: 使用内连接关键字 inner join ... on 语句  inner  可以省略

                   语法:select  字段列表   from  表名1 [inner]  join  表名2 on 条件语句

                   操作:select * from student2 as s inner join  course2 as c on s.s_c_id=c.c_id;

         4.3  总结:

                 1.查询哪些表

                 2.确定表关联的条件

                 3.使用连接的方式

                 4.确定查询的字段信息,尽量少用*

   5.外连接

         5.1.左外连接:使用left [outer] join ... on  条件语句  outer  关键字可以省略

               语法:select  字段列表   from 左表(主表)  left  [outer]  join 右表(从表/辅表)  on 条件语句

               注意事项:用左表的记录数据去匹配右表中的记录的数据,如果符合条件的则显示,不显示的数据一律显示为null.保证左表中的数据全部显示.

               操作:select  d.* ,e.username  from   department   as d left  outer join employee as e on e.e_did= d.d_id;

         5.2.右外连接:使用right [outer] join ...on  条件语句  outer  关键字可以省略

               语法:select  字段列表   from 右表(主表)  right  [outer]  join 左表(从表/辅表) 

               注意事项:用右表的记录数据去匹配左表中的记录的数据,如果符合条件的则显示,不显示的数据一律显示为null.保证右表中的数据全部显示.

               操作:select  * from   department   as d right  outer join employee as e on e.e_did= d.d_id;

               三表查询:select * from  employee  right outer join department  on e_did = d_id  right  outer join salary on e_id=s_sid;

  6.子查询(父子查询)

       什么是子查询?     一个查询的结果是另一个查询的条件,形成查询嵌套,里面的查询称之为子查询,一定要出现小括号.

       子查询有三种情况:

                   子查询结果可以是单行单列,只有一个字段,这一个只有一个值

                   也可以是多行多列,只有一个字段,这个字段有多个值

                   还可以是多行多列,有多个字段.多个字段分别有多个值.

                   操作:

                        第一种情况 : 单行单列

                               语法:select  查询字段列表   from  表名   where  字段比较运算符(子查询)

                                特征:我们可以在where的后面使用比较运算符  <>>=<=!=<>

                       第二种情况:多行单列

                                语法:select  查询字段列表  from 表名   where  字段   in(子查询)

                                特征：结果值是一个集合或者一个数组，父查询使用in运算符

select 
	d_name 
from 
	department 
WHERE 
	d_id 
in (
SELECT
  DISTINCT e_did
FROM 
   employee
WHERE
   age > (
 SELECT
    avg(age)
FROM
   employee 
)
)

                     第三种情况;多行多列  一般情况下我你们可以作为一张虚拟表,进行关联二次查询,一般需要给这个虚拟表起一个别名来实现.

                                语法:select   查询字段列表  from 表名.(子查询) as 新表名  where  条件语句;

                                特征:多行多列不能再使用in运算符或者是比较运算符,而是需要进行多表关联,给查询出来的多行多列起别名.

          子查询总结:

               1.单行单列:只有一个值,在where后面可以使用比较运算符,作为条件

               2.多行单列:是一个集合值或者数据值,在where后面使用的是in运算符,作为条件

               3.多行多列:大多数多列结果是放在from后面的,作为多表关联的.可以进行二次条件过滤.

事务:

         什么是事务:一个业务操作,这个操作要么被完全执行成功,要么被撤销掉.这个业务操作是一个整体,在这个整体中所有的sql语句要么全部执行成功,要么被回滚.(业务执行失败)

          操作:

-- 创建账户表
create table account (
   
    id int PRIMARY key auto_increment, -- 主键id
    username VARCHAR(10) not null, -- 账户
    balance double -- 账户余额 
)

-- 插入两条数据
insert into account values(null,'张三',20000),(null,'李四',20000);
-- 张三给李四转账1000块钱
-- 先让张三的钱减掉10000
update account set balance = balance - 10000 where username = '张三';

-- 添加一条语句
update account set balance = balance - 10000  username = '张三';

-- 再让李四的钱增加10000
update account set balance = balance + 10000 where username = '李四';

-- 还原数据
update account set balance = 20000;

-- 查询账户表
SELECT * from account;

         手动操作事务:

                   三个动作:

                        1.开启事务:start  transaction

                        2.提交事务:commit;

                        3.回滚事务:rollback;

        事务的四大特性：

                1. 原子性：这个事务是一个整体，在这个整体中，是不可分割的，在事务所有的sql语句要么完全执行成功，要么都失败

                2. 一致性：事务在执行前和执行后数据库中数据状态是一致的。转账：张三和李四转帐前余额都是20000，转账后，如果成功结果：张三是10000，李四是30000 如果失败结果：张三20000，李四20000.

                3. 隔离性：事务与事务之间是互不影响的，在多个事务并发执行的时候应该处于隔离的状态。

                4. 持久性：一旦事务执行成功，对数据库的影响是持久的。

        事物的隔离级别：读未提交-->read  uncommitted   读已提交--->read  committed 可重复读--> repeatable  read      串行化-----> serializable  锁表    安全性最高  性能最低

         由事务隔离级别引发并发事务操作的问题：脏读，不可重复读，幻读。