MySQL高级

MySQL高级

MySQL高级

约束

什么是约束？

约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。

MySQL中的约束

1. 主键约束 primary key
   用于标识字段不能为空并且保证唯一，主要用于唯一标识表的一个字段，一个表中只能有一个主键

主键约束
– 创建表时创建主键约束
create table student1(
id int primary key,
name varchar(20) not null,
phone varchar(11) unique
)
– 删除主键约束
– alter table student1 modify id int; – 错误的
– 一个表中只有一个主键，因此不用写主键的字段名
alter table student1 drop primary key;
– 创建表之后再添加主键
alter table student1 modify id int primary key;
2. 非空约束 not null
用于标识限定该字段的值不能为null

1.在创建表的时候对字段添加约束条件
create table student(
    id int,
    name varchar(20) not null
)
-- 在创建表的同时创建了not null约束
create table student1(
    id int,
    name varchar(20) not null
)

create table student2(
    id int,
    name varchar(20)
)

-- 创建表之后再添加not null约束
alter table student2 modify name varchar(20) not null;

-- 删除not null约束
alter table student2 modify name varchar(20);

3. 唯一约束 unique
   用于标识该字段的数据不能出现重复(唯一的)

   – 创建表时创建唯一约束
   create table student1(
   id int,
   name varchar(20) not null,
   phone varchar(11) unique
   )

   – 删除唯一约束
   – alter table student1 modify phone varchar(20);-- 错误的
   alter table student1 drop INDEX phone;

   – 创建表之后添加唯一约束
   alter table student1 add card varchar(18) unique;
   alter table student1 modify phone varchar(20) unique;

4. 外键约束 foreign key
   用于标识该字段和其他表进行关联关系

   – 创建部门表dept
   create table dept1(
   id int PRIMARY key,-- 部门编号
   name varchar(30),-- 部门名称
   descript varchar(100) – 部门工作描述
   )
   – 创建员工表emp
   create table emp1(
   id int PRIMARY key,-- 员工的编号
   name varchar(20) not null,-- 员工的姓名
   dept_id int,-- 员工所在部门
   CONSTRAINT fk_emp_dept FOREIGN key(dept_id) REFERENCES dept1(id)
   )
   – CONSTRAINT表示约束
   – fk_emp_dept外键名
   – REFERENCES参考

   – 删除外键
   alter table emp1 drop foreign key fk_emp_dept;

   – 创建表之后添加外键
   alter table emp1 add CONSTRAINT fk_emp_dept
   FOREIGN key(dept_id) REFERENCES dept1(id);

   – 级联删除、更新
   – on delete CASCADE
   – on update CASCADE

   alter table emp1 add CONSTRAINT fk_emp_dept
   FOREIGN key(dept_id) REFERENCES dept1(id)
   on DELETE CASCADE ON UPDATE CASCADE;
   5.自动增长 auto_increment

   create table emp1(
   id int PRIMARY key auto_increment,-- 员工的编号
   name varchar(20) not null,-- 员工的姓名
   dept_id int,-- 员工所在部门
   CONSTRAINT fk_emp_dept FOREIGN key(dept_id) REFERENCES dept1(id)
   )
   练习：
   1.创建一个员工表emp(id，name,phone，salary,time,dept_id)
   id表示员工编号设置为主键
   name表示员工姓名设置为非空
   phone表示员工电话号码唯一
   salary表示员工工资设置默认为0
   time表示员工入职日期非空
   dept_id表示员工部门编号，外键

2.创建一个部门表dept(id,name,descript)
id表示部门的编号主键
name表示部门名字非空
descript表示部门描述

数据库建表范式

范式：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
一般来说，数据库只需满足第三范式(3NF）就行了。

2.各范式的要求：

1.第一范式：要求字段具有原子性，唯一
2.第二范式：要求表中的记录不能出现重复并有字段或字段组能唯一标识每一行记录
3.第三范式：要求将数据的冗余降低，以拆分表的形式降低冗余
多表之间的关系

一对一
如： 人和身份证号码之间的关系，一个人只能有一个身份证号，一个身份证号只能从属一个人
一对多（多对一）
如：一个人和银行卡之间的关系，一个人可以有很多张银行卡，很多种银行卡从属于一个人
多对多
如：老师和学生的关系，多个学生具有多个老师教学，多个老师同时教学同个学生

多表查询

就是在多个表之间进行数据查询

笛卡尔积：
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
3.多表查询的几种情况：

内连接（表与表之间的交集）

隐式内连接
格式： select 字段 from 表1,表2 where 表1.字段=表2.字段
显示内连接
格式：select 字段 from 表1 [inner] join 表2 on 表1.字段=表2.字段
– 查看所有员工的信息（emp）及其所在的部门名称(dept)
select emp.,dept. from emp,dept where emp.dept_id=dept.id;
– 显示内连接来更改
select e.,d. from emp e join dept d on e.dept_id=d.id;
– 对表取别名
select e.,d. from emp e,dept d where e.dept_id=d.id;

-- 查询工资大于7000的同事所在的部门
select  
    e.name, -- 员工信息
    e.salary,-- 员工工资
    d.name  -- 员工所在部门的名称
from 
    emp e,dept d -- 员工表、部门表
where 
    e.salary>7000 -- 员工工资>7000
and 
    e.dept_id=d.id;

select 
    e.name,
    e.salary,
    d.name 
from 
    emp e
join 
    dept d
ON 
    e.dept_id=d.id 
AND 
    e.salary>7000;

外连接（查询的是表与表之间的交集和左表或右表的所有数据）

左外连接
格式：select 字段 from 表1 left [out] join 表2 on 表1.字段=表2.字段
右外连接
– 查询公司所有同事的信息及其所在的部门，没有部门的忽略部门
– 左外连接
select
e.,d.
from
emp e LEFT OUTER JOIN dept d
ON
e.dept_id=d.id;
– 右外连接
select
e.,d.
from
dept d RIGHT OUTER JOIN emp e
ON
e.dept_id=d.id;
子查询：将查询的结果作为一个虚拟的表，再来针对这个虚拟表进行查询

子查询的结果只有一条记录；
一般查询的结果作用是另一个查询的条件使用
select name from emp where salary=(select Max(salary) from emp);

             -- 查询工资比白骨精高的同事的名字
                select name from emp where salary
                 >
               (select salary from emp where name='白骨精');

查询的结果是很多条记录；
可以作为另一个查询的条件，但是不能使用关系运算符了，可以使用IN、NOT IN
查询的结果可以作为另一个查询的虚表来继续进行查询，但是子查询的虚表必须取个别名
select name from emp where gender=‘女’
and
salary in (select salary from emp where gender=‘男’);

select e.name from (select name,salary from emp where gender=‘男’)e;

数据库备份和恢复

备份

1. 通过命令备份
   mysqldump -u用户名 -p密码 数据库名 > 盘符（存储位置）
2. 通过可视化工具备份

恢复

1. 通过命令恢复数据库
   source sql文件所在的路径
   mysql -uroot -p123456 mydb < E:\mydb.sql
2. 通过工具恢复数据库

事务

1. 事务的概念

一个业务由多个步骤（SQL）共同协助完成，这多个步骤要么一起成功，要么一起失败的这么一个过程就称为事务
事务的几个操作：
开启事务 start transction
回滚事务 rollback 将数据回滚到开启事务之前的状态
提交事务 commit 将数据提交到数据中，将数据持久化到数据库表中
注意：
MySQL的增删改是默认自动提交事务的，当我们自己开启事务以后，事务就不再默认自动提交，需要手动提交
更改提交事务的状态
查询事务的提交状态：Select @@autocommit;
更改事务提交的状态：Set @@autocommit=0;
事务的状态0表示手动提交，1表示自动提交
事务的四大特征：

原子性：一个事务具有原则性，是不可以分割的
持久性：事务提交或回滚具有持久性，表中的数据的持久存储
隔离性：事务是相互独立的，具有隔离性
一致性：事务中的数据具有一致性，开始事务之前，和提交或回滚事务之后的数据总量一致
事务的隔离级别

隔离级别：事务具有隔离性，如果有多个事务共同去处理同一批数据的时候，就会出现问题，可以通过设置不同的隔离级别来去解决这些问题
问题：
脏读
重复读
幻读
隔离级别：
read uncommitted 读不提交
1.出现问题：脏读、重复读、幻读
read committed 读并提交
1.出现问题：重复读、幻读
repeatable read 可重复读（MySQL默认的隔离级别）
1.出现问题：幻读
serializable 可串行化
解决所有问题
设置和查看隔离级别：
数据库查询隔离级别：
select @@global.tx_isolation;
数据库设置隔离级别：
set global transaction isolation level 级别;
注意：隔离级别是从低到高，产生的问题就从多到无，但是并不是隔离级别越高越好，因为隔离级别越高性能越低

DCL（数据库控制语言）：对MySQL用户的增删改查，以及相应的权限操作

查看用户
select host,user,password from mysql.user
添加用户
创建用户时就赋予权限
1.grant 权限 on 数据库.表 to ‘用户名’@‘主机名’ identified by ‘密码’；
仅创建用户，并不具有相应的权限
2.create user ‘用户名’@‘主机名’ identified by ‘密码’

grant all on . to ‘lisi’@’%’ IDENTIFIED by ‘123456’;
all代表所有权限
*表示所有数据库和所有表
%表示任意主机名，意思就是在任何一台电脑上都可以访问数据库
删除用户
drop user ‘用户名’@‘主机名’；
授权
grant 权限 on 数据库.表名 to ‘用户名’@‘主机名’’
忘记MySQL中root用户的密码，如何找回

1. 关闭MySQL的服务 系统管理员权限下： net stop mysql

2. 启动MySQLD服务 mysqld --skip-grant-tables

3. 执行修改密码的SQL语句：update mysql.user set password=password(‘密码’) where user=‘root’ and host=‘localhost’;
   4.刷新权限 flush privileges

4. 关闭mysqld的服务，重开窗口登录MySQL
   视图

5. 就是将经常查询需要使用到的使用，单独查询出来，创建建出一张虚拟表，这个虚拟表并没有真实的数据，数据类之于基表，这个就称为视图

6. 视图优点：

           1.  简化查询（简化经常查询的内容）
            2.   使数据更加安全
   

创建视图：
create [or replace] view 视图名 as select_statment
4.删除视图：

drop view 视图名
5.修改视图：

create [or replace] view 视图名 as select_statment
alter view 视图名 as select_statement
视图的管理：
查询视图的数据：
select * from 视图名
update 视图名 set 字段=值 [where子句]
delete from 视图名 [where子句]
插入数据