week7-Day1

1.数据库约束(重点)

​
非空约束not null
唯一约束uniqe:约束字段值不能重复
    删除唯一约束:alter table 表名 drop index 唯一约束所在的字段名称
主键约束:非空且唯一;
    删除主键约束:alter table 表名 primary key;
    删除只是删除主键约束,not null依旧存在;
    一般和自增长一起使用
    查询最后一次自增长主键的id值:
    select LAST_INSERT_ID;  
级联操作

1.1默认约束default

-- 默认约束default:当前操作表的时候,如果某个字段没有插入值,那么默认约束可以起作用;
​
-- 重新创建表格,创建表的时候给性别加入非空约束
CREATE TABLE student2(
     id INT,
     NAME VARCHAR(10),
     age INT,
     gender VARCHAR(5) DEFAULT '女');
-- 插入正常数据
INSERT INTO student2 VALUES(1,'张三',20,'男'),(2,'李四',21,'女');
-- 插入部分数据
INSERT INTO student2(id,NAME,age)VALUES(3,'王五',25);
​
SELECT * FROM student2;
​
-- 输出结果  高圆圆性别为 女

1.2非空约束not null

-- 加入了非空约束not null,null插不进去,会报错
CREATE TABLE student3(
  id INT,
  NAME VARCHAR(10),
  gender VARCHAR(5) NOT NULL-- 加入了非空约束
);
-- 插入正常数据
INSERT INTO student3 VALUES (1,'文章','男'),(2,'王宝强','男');
-- 插入null值
INSERT INTO student3 VALUES(3,'邓超');-- 插不进去,报错Column count doesn't match value count at row 1
​
SELECT * FROM student3;
​
-- 通过sql语句将非空约束删除
ALTER TABLE student3 MODIFY gender VARCHAR(5);
INSERT INTO student3 VALUES(3,'邓超');
-- 通过sql语句将非空约束添加上
DELETE FROM student3 WHERE id= 3;-- 先删除里面的null值;
ALTER TABLE student3 MODIFY gender VARCHAR(5) NOT NULL;

1.3唯一约束unique

-- phone_number加上唯一约束
CREATE TABLE student3(
   id INT,
   NAME VARCHAR(10),
   phone_num VARCHAR(12) UNIQUE
);
INSERT INTO student3 VALUES(1,'张三','123456'),(2,'李四','456789');
-- 加入id相同的电话号码的信息
INSERT INTO student3 VALUES(3,'王五','123456');-- Duplicate entry '123456' for key 'phone_num'
​
-- 删除唯一约束:alter table 表名 drop index 唯一约束所在的字段
ALTER TABLE student3 DROP INDEX phone_num;
​
-- 修改表,sql语句添加唯一约束给phone_num
DELETE FROM student3 WHERE id = 3;
ALTER TABLE student3 MODIFY phone_num VARCHAR(11) UNIQUE;
SELECT * FROM student3;

1.4主键约束primary key

-- 主键约束特点:主键作用在一张表的非业务字段(id),非空并且唯一
-- 主键约束,非业务字段上使用
CREATE TABLE student2(
   id INT PRIMARY KEY,-- 主键约束
   NAME VARCHAR(20),
   gender VARCHAR(5),
   address VARCHAR(50)
);
INSERT INTO student2 VALUES(1,'刘备','男','蜀国'),(2,'周瑜','男','东吴'),
(3,'曹操','男','魏国');
INSERT INTO student2 VALUES(NULL,'秦琼','男','隋唐');-- Column 'id' cannot be null
INSERT INTO student2 VALUES(3,'秦琼','男','隋唐');-- Duplicate entry '3' for key 'PRIMARY'
-- 通过sql语句删除主键约束
-- alter table 表名 drop primary key;
ALTER TABLE student2 DROP PRIMARY KEY;
-- 通过sql语句添加主键约束
DELETE FROM student2 WHERE NAME = '秦琼';
ALTER TABLE student2 MODIFY id INT PRIMARY KEY;
SELECT * FROM student2;

1.5自增长约束aoto_increment

-- 自增长约束aoto_increment
-- 应用场景:每一张表都有一个非业务字段,id设置为自增长约束
-- 自增长约束+主键约束
CREATE TABLE student4(
   id INT PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(10),
   address VARCHAR(50)
);
INSERT INTO student4(NAME,address)VALUES('张三','西安'),('李四','汉中'),('王五','咸阳');
INSERT INTO student4 VALUES(4,'赵六','渭南');
INSERT INTO student4(NAME,address)VALUES('朱八','长沙'); 
SELECT * FROM student4;
-- mysql提供函数LAST_INSERT_ID()查询最后一次自增长的id值
SELECT LAST_INSERT_ID();
-- 删除自增长+主键约束
ALTER TABLE student4 MODIFY id INT;

1.6外键约束foreign key

-- 外键约束foreign key
-- 关联主键的字段名称,解决字段名称冗长问题
-- 外键约束 foreign key
-- 部门表
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,-- 部门编号自增长
    dept_name VARCHAR(50)-- 部门名称
);
-- 插入部门名称
INSERT INTO dept(dept_name)VALUES('开发部'),('测试部'),('运营部');
-- 员工表,加入外键约束
CREATE TABLE employee(
   id INT PRIMARY KEY AUTO_INCREMENT,-- 员工编号
   NAME VARCHAR(20), -- 员工名称
   age INT,          -- 员工年龄
   gender VARCHAR(5),-- 员工性别
   dept_id INT,       -- 部门表id
   CONSTRAINT        -- 声明
   dept_employee_fk  -- 规范:主表名_从表明_fk
   FOREIGN KEY(dept_id)  -- 外键名称作用在指定的外键字段
   REFERENCES         -- 关联
   dept(id)           -- 主表的主键id
);
INSERT INTO employee(NAME,age,gender,dept_id) VALUES
('文章',35,'男',1),
('高圆圆',43,'女',2),
('张佳宁',30,'女',2),
('邓超',25,'男',1),
('张三丰',40,'男',3);
​
SELECT * FROM dept;
SELECT * FROM employee;

-- 外键的删除与连接
-- 通过sql约束,删除外键约束
-- alter table 表名 drop foreign key 外键的名称;
ALTER TABLE employee DROP FOREIGN KEY dept_employee_fk;
-- 通过sql将外键约束重新添加上
-- alter table 表名 add CONSTRAINT 外键名称 FOREIGN (外键作用的从表的字段名称) REFERENCES 主表主键id;
DELETE FROM employee WHERE id = 7;
ALTER TABLE employee ADD CONSTRAINT dept_employee_fk FOREIGN KEY (dept_id) REFERENCES dept(id);

1.7级联操作CASCADE---修改和删除

-- 级联修改ON UPDATE CASCADE和级联删除ON DELETE CASCADE 
-- 级联修改和删除作用:当修改或者删除主表的时候,和主表相关的从表数据也随之修改或删除
-- 级联修改和级联删除是将外键约束的基础上加入这两个约束
ALTER TABLE employee DROP FOREIGN KEY dept_employee_fk;
-- 通过sql语句加入外键约束以及级联删除和级联修改
ALTER TABLE employee 
  ADD 
  CONSTRAINT 
  dept_employee_fk 
  FOREIGN KEY
 (dept_id) 
 REFERENCES 
 dept (id) 
 ON UPDATE CASCADE -- 级联修改
 ON DELETE CASCADE ;-- 级联删除
 -- 将部门表的id=3的部分改为id=4
 UPDATE dept SET id =4  WHERE id =3;
 -- 将id=4的产品部删除,在员工表的员工也随之删除
 DELETE FROM dept WHERE id= 4;

SELECT * FROM dept;
SELECT * FROM employee;

2.数据库备份和还原

2.1 方式1:sqlyog图形界面化工具

备份:
	选中库--->右键--->backup/export--->以sql转存文件导出到指定的目录中--->选中structure and data--->选择指定的路径
	
还原:将原来的库删除,然后再创建新的库
	新的数据库--->右键--->import--->Execute Sql Script(执行sql脚本)--->找到sql脚本路径--->将sql脚本中的存储的数据表数据和结构全部执行;

2.2方式2:命令行的方式

备份:使用dos控制台的方式
	管理员身份运行dos控制台,不需要登录mysql--->mysqldump -uroot -p密码 库名 > 路径
还原:
	mysql -uroot -p--->回车输入密码--->dos控制台登录mysql--->将原来的库删除--->创建一个新的库--->使用这个库--->source 路径;

3.数据库三大范式

第一范式1NF 
	在设计数据库的时候,该库中的每一列是不可拆分的原子数据项
	每一列(字段)是独立的,不可再拆分

第二范式2NF
	在1NF的基础上,非主键的列(字段)完全依赖于主键列(字段)
	在1NF的基础上一张表只描述一件事情
	表中的每一列都完全依赖于主键

第三范式3NF
	在2NF的基础上,非主键的列不能产生传递依赖主键列;
依赖关系:主键列--->x非主键列--->y非主键列
	拆分表---->使用外键建立练习

4.多表查询---select语句嵌套select语句

-- 创建部门表
CREATE TABLE dept(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 部门id
 NAME VARCHAR(20) -- 部门名称
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
SELECT * FROM dept ; 
-- 创建员工表
CREATE TABLE emp ( 
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	NAME VARCHAR(10), 		   -- 员工名称
	gender VARCHAR(1), -- 性别 	   
	salary DOUBLE, -- 工资 		
	join_date DATE, -- 入职日期 
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) 
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男 ',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男 ',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女 ',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女 ',4500,'2011-03-14',1);
SELECT * FROM emp;

4.1内连接查询

隐式内连接:使用where条件连接-如果有多个条件后面就直接加and
	select 字段列表 from 表名1,表名2 where 表名1的某个字段 = 表名2.某个字段;
显示内连接:关键字 inner join----->两张表中交际数据,都可以通过内连接来查询
	select 字段列表 from 表名1 inner(可省略) join 表名2 on 连接条件;

-- 隐式内连接
-- select 字段列表 from 表名1,表名2 where 表名1的某个字段 = 表名2.某个字段;
SELECT 
  e.*,
  d.`name` '部门名称' 
FROM
  emp e,
  dept d 
WHERE e.`dept_id` = d.`id` ;

-- 显示内连接
-- select 字段列表 from 表名1 join 表名2 on 连接条件
-- 查询员工表的name,gender,salary以及部门表的部门的名称
SELECT
	e.`NAME` '员工姓名',
	e.`gender` '性别',
	e.`salary` '工资',
	d.`name` '部门名称'
FROM
	emp e
JOIN
	dept d
ON
	e.`dept_id` = d.`id`;

4.2外连接查询

外连接查询:
	左外连接:left outer join作为连接条件查询,左表的数据和连接的数据全部查询出来
	select 字段列表
	from 左表
	left outer(可以省略) join 右表
	on 连接条件;
	
	右外连接:和左外连接相反;

-- 左外连接查询
SELECT 
  * 
FROM
  emp e -- 左表
  LEFT JOIN dept d -- 右表
    ON e.`dept_id` = d.`id` ;

-- 右外连接查询
-- 右外连接查询
SELECT 
*
FROM
emp e
RIGHT JOIN dept d
ON e.`dept_id` = d.`id`;

4.3子查询

子查询:
	1)利用聚合函数以及比较运算符进行select语句的嵌套
	2)利用int集合数据(值1,值2,值3)
	3)将某个查询语句的结果多行多列,看作"虚表"和当前其他表进行关联查询; 

4.3.1情况1:

-- 查询工资最高的员工信息
-- a)先查询最高的工资是多少
SELECT 
  MAX(salary) 
FROM
  emp ;

-- 查询工资9000的员工信息
SELECT 
  * 
FROM
  emp 
WHERE salary = 9000 ;

-- 一步走
SELECT 
  * 
FROM
  emp 
WHERE salary = 
  (SELECT 
    MAX(salary) 
  FROM
    emp) ;

-- 查询员工工资小于平均工资的员工
-- a)平均工资是多少
SELECT AVG(salary) FROM emp; -- 5860
-- b)查询工资小于5860的员工
SELECT
*
FROM
emp
WHERE salary<5860;

-- 一步走

SELECT
* 
FROM
emp
WHERE salary<(SELECT AVG(salary) FROM emp);

4.3.2情况2

-- 利用 in(集合数据)查询多个表
-- 查询在市场部或者财务部的员工信息
-- 1)先查询市场部或者财务部的id是多少
SELECT
	d.`id`
FROM
dept d
WHERE
d.`name` = '市场部' OR d.`name` = '财务部';
 
-- 2)查询在2号或者3号部门的员工信息
SELECT
*
FROM
emp e
WHERE
	e.`dept_id` = 2 OR e.`dept_id` = 3;
	
-- 优化一步执行
SELECT
*
FROM
emp e
WHERE e.`dept_id` IN 
(SELECT
d.`id`
FROM
dept d
WHERE d.`name` IN ('市场部','财务部')
);