Java学习日志Day29_数据库的约束_备份和还原_三大设计范式_多表查询

-- 数据库的约束
-- 就是约束用户操作表的行为
-- 针对一些字段设置用户的限制!

-- 默认约束 :default(当用户没有插入指定的这个字段,启用默认约束--->默认值)

-- 创建一个张表emp
CREATE TABLE emp(
   id INT , -- 编号
   NAME VARCHAR(10) , -- 姓名
   gender VARCHAR(3) DEFAULT '男'  -- 性别
) ;

INSERT  INTO emp VALUES(1,'张三','男'),(2,'文章','男'),(3,'高圆圆','女') ;

-- 插入数据的时候,忘记插入性别这个字段---性别就是null值
INSERT INTO emp(id,NAME) VALUES(4,'张三丰') ;-- 性别 没有值,默认约束给一个默认值

DROP TABLE emp ;


-- 非空约束 not null (限制用户直接插入null值)
CREATE TABLE emp(

   id INT, 
   NAME VARCHAR(10) NOT NULL , -- 非空约束
   age INT 
) ;

INSERT INTO emp VALUES(1,'高圆圆',41),(2,'赵又廷',45) ;

-- 没有使用约束限制他,null值可以插入 (非法数据)

-- 加入非空约束:不能直接添加null值:
-- 错误码: 1048  Column 'name' cannot be null
INSERT INTO emp VALUES (3,NULL,22) ;
INSERT INTO emp(id,age) VALUES(4,25) ;-- 没有给值,启用默认约束  (1 row(s) affected, 1 warning(s)) 警告 


DELETE FROM emp	 WHERE id = 4 ;

DROP TABLE emp ;

-- 修改表:将非空约束去掉
ALTER TABLE emp MODIFY NAME VARCHAR(10) ;
-- 修改表:添加非空约束
ALTER TABLE emp MODIFY NAME VARCHAR(10) NOT NULL ;



-- 唯一约束 unique (限定某个字段的不能重复,必须唯一的)
CREATE TABLE emp(
	id INT UNIQUE, -- 唯一约束
	NAME VARCHAR(10) ,
	age INT
) ;
INSERT INTO emp	VALUES(1,'张三',25),(2,'李四',30) ;

INSERT INTO emp VALUES(2,'王五',35) ;-- Duplicate entry '2' for key 'id' id重复了:值为2

UPDATE emp SET id = 3 WHERE NAME = '王五' ;

-- 删除唯一约束
-- atler table 表名 drop index 唯一约束所作用的字段名称
ALTER TABLE emp DROP INDEX id ;

-- 添加唯一约束
ALTER TABLE emp MODIFY id INT UNIQUE ;



-- 主键约束:primary key 
-- 特点:非空+唯一
CREATE TABLE emp(
     id INT PRIMARY KEY, -- 主键约束
     NAME VARCHAR(10)
 );
 
 INSERT INTO  emp VALUES(1,'高圆圆'),(2,'赵又廷'),(3,'张飞') ;

INSERT INTO emp VALUES(NULL,'耿明刚') ;-- Column 'id' cannot be null 非空约束限制
INSERT INTO emp VALUES(1,'杨德财') ; -- Duplicate entry '1' for key 'PRIMARY :唯一约束限制

INSERT INTO emp(NAME) VALUES('张佳宁') ;
DELETE FROM emp WHERE id = 0 ;

-- 删除主键约束 
-- alter table 表名 drop PRIMARY KEY ;
ALTER TABLE emp DROP PRIMARY KEY ; -- 将唯一约束删除掉

-- 添加主键约束
ALTER TABLE emp MODIFY id INT PRIMARY KEY ;

UPDATE emp SET id = 4 WHERE NAME = '杨德财' ;


-- 自增长约束:auto_increment和主键一块使用

-- 一般都作用于非业务字段(xxid)

-- (默认值:0开始,当前插入数据之后,自增长的主键的值会依次自增1)
CREATE TABLE emp(

  id INT PRIMARY KEY AUTO_INCREMENT,-- 主键+自增长
  NAME VARCHAR(10),
  address VARCHAR(20)
) ;

INSERT INTO emp(NAME,address) VALUES('高圆圆','北京市'),('赵又廷','西安市') ;
INSERT INTO emp VALUES(5,'文章','西安市') ;
INSERT INTO emp(NAME,address) VALUES('华子','延安市') ;



-- 在mysql 操作数据库:id的值如果是自增长的主键(可以一些sql语句获取主键的id值)
--  LAST_INSERT_ID() mysql的函数 :

SELECT LAST_INSERT_ID() ;-- 查询当前表中最后一次插入的自增主键的id值

SELECT * FROM emp ;




-- 外键约束(foreign key)
-- 创建一个员工表:employee
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	NAME VARCHAR(10),-- 员工姓名
	age INT , -- 员工年龄
	gender VARCHAR(3), -- 员工性别
	dept_name VARCHAR(10) -- 部门名称
) ;

INSERT INTO employee(NAME,age,gender,dept_name) VALUES('文章',20,'男','开发部'),
('李四',24,'女','测试部') ,('张佳宁',30,'女','开发部'),('赵又廷',35,'男','测试部'),(
'张三',26,'男','运维部') ;

-- 通过设计的这个表:查看员工的全部信息 存在问题
-- 员工所在的部门:存在数据冗余
/*
	1  文章       开发部
	3  张佳宁     开发部
	2  李四       测试部
	4  赵又廷     测试部
 
 部门存在很多重复数据:很多都在一个部门中	
 
解决方案:
	单独将部门名称----放在一个表: 存储部门的表(部门表)
	
	员工表中 给定字段:dept_id:部门id号
	部门表:主键id: 部门编号
	
*/

DROP TABLE employee;


-- 创建一个部门表
CREATE TABLE dept(
   id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
   NAME VARCHAR(10)  -- 部门名称
) ;
-- 插入三个部门
INSERT INTO dept(NAME) VALUES('开发部'),('测试部'),('运维部');

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	NAME VARCHAR(10),-- 员工姓名
	age INT , -- 员工年龄
	gender VARCHAR(3), -- 员工性别
	dept_id INT,  -- 部门编号
	-- 声明一个外键约束
	CONSTRAINT -- 声明
	fk_dept_emp   -- 外键名称
	FOREIGN KEY (dept_id) -- 作用在从表的指定字段上
	REFERENCES    -- 关联
	 dept(id) -- 主表主键id
	
	
) ;

INSERT INTO employee(NAME,age,gender,dept_id) VALUES('文章',20,'男',1),
('李四',24,'女',2) ,('张佳宁',30,'女',1),('赵又廷',35,'男',2),(
'张三',26,'男',3) ;

-- 插入员工数据 -- 插入了一个不存在的部门(非法数据)
INSERT INTO employee(NAME,age,gender,dept_id) VALUES('杨德财',23,'男',4) ;

-- 需要使用外键约束
-- 当前员工表中部门dept_id 来自于部门dept的id字段
-- 外键需要添加在员工表dept_id字段 (外键的表---从表),部门表dept(主表)

-- 已经给员工表加入外键约束
INSERT INTO employee(NAME,age,gender,dept_id) VALUES('杨德财',23,'男',4) ;



-- 修改表--->删除外键名称
ALTER TABLE employee DROP FOREIGN KEY fk_dept_emp  ; 
-- 添加外键
ALTER TABLE employee
        ADD  	
        CONSTRAINT
	 fk_dept_emp 
	FOREIGN KEY 
		(dept_id)
	REFERENCES 
		dept(id) ;	
		
		




-- 有外键的表--->删除和修改都比较麻烦 (操作从表---主表)
-- CASCADE:级联操作
-- on update cascade  级联修改
-- 当修改主表字段,那么跟从表相关的信息一会被更改
-- on delete cascade  级联删除

-- 添加外键的同时--添加级联修改和级联删除
ALTER TABLE employee
        ADD  	
        CONSTRAINT
	 fk_dept_emp 
	FOREIGN KEY 
		(dept_id)
	REFERENCES 
		dept(id) 
        ON UPDATE CASCADE   --  添加级联修改
        ON DELETE CASCADE ; --  添加级联删除
        
        
        
SELECT * FROM dept ;

SELECT * FROM employee;

-- 直接修改主表的数据,从表随之变化
-- 将name="开发部"的部门id = 4
UPDATE dept SET id = 4 WHERE NAME = '开发部' ;

-- 删除 部门表中3号部门
DELETE FROM dept WHERE id = 3 ;


-- 数据库的备份和还原
/*
   方式1:
	命令行的方式
	数据库的备份
	
	mysqldump.exe 指令 :备份指令
	
	进入dos
	 输入 :mysqldump -uroot -p密码  数据库名 > 路径
	 mysqldump -uroot -p123456 ee_2104 > D:\JavaEE_2104\EE_day29\code\my.sql
	 
	 将ee_2104这个库中所有表都存储在D:\JavaEE_2104\EE_day29\code\my.sql文件中
	 
	 
	 数据库还原
	 1)通过dos先登录mysql
	 2)删除原来备份过的数据库
	 3)创建空数据库
	 4)source 指定之前备份的路径的sql文件 : 自动的进行创建以及表中数据进行插入...
	 
	 source D:\JavaEE_2104\EE_day29\code\my.sql
	 
	
   方式2:图形界面化:直观简单 
       sqlYog /navicat
      
      备份:
        鼠标选择指定数据库,右键--->备份--->以sql文件转存数据库---指定路径
        
      还原: 
	1)删除当前数据库
	2)创建新的数据库
	3)选择新建的数据库名---右键--->导入--->以sql转储文件数据库
	 
*/



USE myee_2104_;

SELECT * FROM dept ;
SELECT * FROM employee;


CREATE DATABASE ee_2104_mysql;

/*
 数据库范式:就是设计关系型数据库使用的一种规范要求!

 常用的范式:三大范式
 1NF:第一范式
 
		在设计关系数据数据库是,最低要求必须遵循1NF
  表中的每一列是不可再拆分的!(独立的原子项)
 
2NF:第二范式
      在1NF的基础上,非主键字段(属性字段)必须完全依赖于主键字段
 
3NF:第三范式


  在第二范式基础上,非主键字段(主键字段和非主键字段)之间不能产生传递依赖!
	A字段-->B字段--->C字段
	A字段--->C字段  (传递依赖)
*/




-- 多表查询
-- 多个表查询相关字段

DROP TABLE employee ;
DROP TABLE dept ;
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
	NAME VARCHAR(10)  -- 部门名称
)  ;
INSERT INTO dept(NAME) VALUES('开发部'),('财务部'),('市场部') ;



CREATE TABLE employee(
   id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
   NAME VARCHAR(10), -- 员工名称
   gender VARCHAR(3), -- 员工的性别
   salary INT, -- 员工工资
   join_date DATE,-- 入职日期
   dept_id INT, -- 部门id
   FOREIGN KEY (dept_id)  -- 声明外键: 语法 可以不带constraint 外键名称
   REFERENCES dept(id) 

) ;
INSERT INTO employee(NAME,gender,salary,join_date,dept_id)
VALUES('文章','男',8000,'2019-11-11',1),
('马伊琍','女',9500,'2020-12-12',2) ,
('姚笛','女',13000,'2020-12-13',2) ,
('刘备','男',15000,'2013-11-11',1) ,
('张飞','男',8500,'2014-10-12',3) ;




-- 多表查询(必须满足一定条件查询)
-- 内连接

-- 需求:查询员工表和部门表所有信息
-- 问题:笛卡尔乘积(A表的记录*B表记录) = 结果集数据
--  员工表5条,部门表3条--->15条记录   (没有给设置 条件导致的笛卡尔乘积!)
SELECT
      *
FROM  
    employee,
    dept ;
    
-- 内连接之显示内连接:就是通过where 设置条件 (最简单的)
/*
    内连接:
      1)查询哪个表        查询dept 和employee
      2)查询这个表的哪个字段   部门表的部门名称  和员工的所有数据
      3)这些表有没有关系条件    员工表的deptid=部门表id
*/   

-- 需求:查询员工表的所有数据同时关联查询部门的表名称 
SELECT
    employee.*,
    dept.id,
    dept.name
FROM
     employee ,-- 员工表
     dept -- 部门表
WHERE
    employee.dept_id = dept.id;    
    
-- 优化:查询时候,如果表名比较长,使用别名
 
SELECT
   e.*,
   d.name
FROM
     employee e ,-- 员工表
     dept  d-- 部门表
WHERE
    e.dept_id = d.id ;
    
-- 隐式内连接
-- 关键字;inner(省略) join        on:作为多表的查询条件(连接条件)
-- 需求:查询员工表的(姓名,性别,工资,入职日期)同时关联查询部门的表名称     
SELECT
    e.name '员工姓名', 
    e.gender,
    e.salary,
    e.join_date,
    d.name '部门名称'
FROM
	employee e
INNER JOIN
	dept d 
ON
	e.dept_id = d.id ;
	
/*
  隐式内连接和显示内连接 :内连接查询
 
 优先采用显示内连接 :where语句 ,然后其次使用隐式内连接

*/

       





INSERT INTO employee(NAME,gender,salary,join_date)
 VALUES('高圆圆','女','18000','2011-9-10') ;

-- 外连接(推荐左外!)
-- 需求:查询员工表的所有信息以及他们所在的部门名称
--  左外:(推荐)select 字段列表 from 表名1 left outer(可以省略) join 表名2  on 表名1和表名2的连接条件
-- 将左表中的全部数据以及交集(符号连接条件)的数据查询出来
SELECT
     t1.name '姓名',
     t1.gender,
     t1.salary,
     t1.join_date,
     t2.name '部门'
FROM
  employee t1
LEFT OUTER JOIN 
  dept t2 
ON 
  t1.dept_id = t2.id ;    


--  右外:将右表的数据全部查询以及多个表之间的交集数据查询出来
-- 格式: select 字段列表 from 表名1 right outer join 表名2 on 连接条件;
-- 查询所有的部门表以及关联的员工所有信息
SELECT
	*
FROM
    dept t1
RIGHT JOIN    
    employee t2
ON 
    t1.id = t2.dept_id ;
    






-- 子查询
-- select语句嵌套select语句

-- 多行单例
-- in语句---使用or 进行或的关系
-- 多行多例

-- 需求:查询最高工资的员工信息
-- 1)查询最高工资是多少
-- select  max(salary) from employee ;-- 18000
-- 2)是18000的工资员工信息
-- select * from employee where salary = 18000 ;

-- 带入之后
SELECT
   * 
FROM 
 employee
  WHERE 
  salary  = (SELECT  MAX(salary) FROM employee ) ;
  
-- 需求:查询小于平均工资的员工信息  
-- 1) 查询平均工资
-- select avg(salary) from employee ;
-- 2)查询小于12000的员工信息
-- select * from employee where salary < 12000 ;
SELECT 
    t.name,
    t.salary,
    t.gender,
    t.join_date
FROM 
   employee t 
WHERE 
   t.salary < (SELECT AVG(salary) FROM employee) ;
   
   
-- 需求:查询员工在财务部或者是市场部的信息  
-- 1)查询出财务部和市场部的部门id是多少
SELECT
   dept.id 
FROM
   dept
WHERE 
   NAME = '财务部' OR NAME = '市场部' ;
-- 2)查询出在 2号部门或者3号部门的员工
/*
select
    name,
    gender,
    salary,
    join_date,
    dept_id
from
	employee
where 
     dept_id = 2 or dept_id = 3 ;   -- dept_id in (2,3) ;
 */
 -- 优化
SELECT
    NAME,
    gender,
    salary,
    join_date,
    dept_id
FROM
	employee
WHERE 
     dept_id 
     IN (2,3) ;	
     
-- 最终写法:
 SELECT
    NAME,
    gender,
    salary,
    join_date,
    dept_id
FROM
	employee
WHERE 
     dept_id 
     IN 
  (
SELECT
   dept.id 
FROM
   dept
WHERE 
   NAME = '财务部' OR NAME = '市场部' 
   ) ;	 
   
UPDATE employee SET dept_id = 3 WHERE id = 6 ;    


-- 子查询的第三种情况:
-- 多行多列 
/*
   将某个查询的结果集当做一张虚表 ,然后使用这个虚表和另外的其他表建立连接查询
*/ 
     
-- 需求:查询员工入职日期大于"2011-9-10"后的所有的员工信息以及的部门信息(部门名称)   

-- 1)查询大于2011-9-10的员工  
SELECT 
     *
FROM 
     employee 
WHERE 
      join_date > '2011-09-10' ;

-- 最终的写法:将上面1)查询的结果集当做虚表,给它起别名和部门表建立连接查询 (左外连接)   
SELECT
    t1.*,
    t2.name  '部门'
FROM
  
   
(SELECT 
     *
FROM 
     employee 
WHERE 
      join_date > '2011-09-10') t1
      
LEFT OUTER JOIN
   dept t2 
   
 ON 
   t1.dept_id = t2.id ;     
   
   
-- 使用传统内连接:显示内连接  多去使用where条件查询(sql优化的一种)

SELECT 
    t1.name '员工名',
    t1.gender '性别',
    t1.salary '工资',
    t1.join_date '入职时间',
    t2.name '部门'

FROM
      employee t1,
      dept t2
      
WHERE 
     t1.join_date > '2011-09-10' 
     
AND
      t1.dept_id = t2.id ;      
   
SELECT * FROM dept ;
SELECT * FROM employee ;