Mysql基础4

用户权限DCL

我们进入公司后，有专门的DBA（数据库管理员），为你创建一个新用户，授予一定的权限…

我们只做为了解即可！

创建用户

语法

create user '用户名'@'主机名' identified by '密码';

注意事项

主机名：限定客户端登录ip
指定ip：127.0.0.1 （localhost）
任意ip：%

演示

create user 'jack'@'%' IDENTIFIED by 'jack';

查看权限

语法

show grants for '用户名'@'主机名';

演示

show grants for 'jack'@'%';

-- 当前权限 ， 仅限于登录
GRANT USAGE ON *.* TO 'jack'@'%'    

授于权限

语法

grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';

授于单个权限

演示

-- 授于 select 单个权限
-- jack 这个用户对test库的emp表有select操作权限
grant select on test.emp to 'jack'@'%' ;

-- 刷新权限
flush privileges;

-- 测试jack登录
mysql -ujack -pjack

--切换test数据库
use test;

-- 测试除select以外的命令是否有权限  
delete from emp where id = 1;

-- 测试select命令是否有权限
select * from emp;

授于多个权限

-- 授于 select, update , insert 多个权限
-- jack 这个用户对test库的emp表有 select, update , insert 操作权限
grant select，update , insert  on test.emp to 'jack'@'%' ;

-- 刷新权限
flush privileges;

授于全部-指定的库和表

-- 授于整个test 库的权限
-- jack 这个用户对test库下的所有表有操作权限
grant all  on test.* to 'jack'@'%' ;

-- 刷新权限
flush privileges;

-- 授于整个test 库的权限
-- jack 这个用户对所有库下的所有表有操作权限
grant all on *.* to 'jack'@'%' ;

-- 刷新权限
flush privileges;

撤销授权

语法

revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名'; 

演示

-- 撤销jack用户下test库中的emp表的select权限
revoke select on test.emp from 'jack'@'%' ;

-- 撤销jack用户下test库所有表的select权限
revoke select on test.* from 'jack'@'%' ;

删除用户

语法

drop user '用户名'@'主机名';

演示

drop user 'jack'@'%' ;

密码管理

超级管理员

-- 登录root
mysql -uroot -proot

-- 切换mysql默认的数据库【mysql】
use mysql;

-- 修改mysql服务器中自带的mysql数据库的user表下有一个authentication_string修改即可
update user set authentication_string =password('abc') where user='root';  

-- 或者使用以下命令进行修改密码
set password for 'root'@'localhost' = password('abc');

-- 修改后重启服务器

普通用户

set password=password('新密码');

-- 加密函数
SELECT PASSWORD('123');
select md5('123')

三大范式

​ 后期，大家需要自行根据用户的需求，设计数据库需求说明书，提升到项目经理，对甲方需要做业务分析，完毕之后的工作。要理解公司现有的数据表及其之间的关系。

​ 如果说：2张表，基本不用；但是如果有50张表的时候，需要；

​ 设计良好结构的数据库，可以有效减小数据冗余，减少增删改中出现的问题。深入理解数据库设计的三范式，对于设计“健壮的数据库“十分有必要。数据库三范式是设计数据库 时参考的准则，接下来我们一一进行介绍：

数据库第一范式

数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。（保持数据的原子性）

数据原子性很好理解，就是表中的字段不可再分。符合数据库第一范式的表，每个字段表意明确，看个例子：

工号 姓名 电话 ，针对电话列，实际上有歧义；固定电话 手机 ；拆分该电话列，成：固定电话 移动电话

这是一张简单的员工信息表，其中有工号、姓名、电话三个字段。通过电话这个字段获得的信息有可能是家庭电话，或是工作地点的电话，或是手机，因此表达的信息并不明确，我们可以改成这样：

到底什么是第一范式： 必须明确字段，列的原子性,意味着列不可拆分。

数据库第二范式

在满足第一范式的基础上，实体的每个非主键属性完全依赖于主键属性（消除部分依赖）

**主键：**凡是接触过数据库的人，肯定都会知道主键，主键明确标识了每条记录，一般是一个字段，也可以由两个或两个字段组成。

**依赖：**对于X的每个值，Y都有一个值与之对应，反过来则不一定不成立，这叫做X函数决定Y，Y函数依赖X（X往往是主键）。

还拿上面的那张表举来说，对于每个工号，都有一个姓名与之对应，即工号决定姓名，姓名依赖工号；但由于员工之间可能有重名，一个姓名可能对应多个工号，所以姓名不能决定工号。

**部分依赖：**当主键由两个或两个以上字段构成，而表中的某些信息通过主键的一个字段就能唯一确定，我们称这样的依赖关系为部分依赖，比如这个例子：

学生选课（学号，姓名，专业，课程号，课程名，成绩），该表中一个学生可以选多门课，一门课有多个学生。学号和课程号可以唯一确定一条记录，因此用学号和课程号做主键。

表中的姓名、专业通过主键中的学号就能唯一确定，而课程名通过课程号唯一确定，这就是部分依赖，这样的设计不符合第二范式。

解决表的原子性，即表中的不可再次拆分为若干子表；

不符合第二范式会带来哪些问题呢？

1、数据信息冗余（重复的数据），可见上表

2、增删改会出现问题，比如有一门《微机原理》没有人选，那么由于缺少学号（主键之一）那么这门课就不能出现在表里。

如何解决呢，我们可以用关系分解的方法消除部分依赖，将上表改成如下三张表：

让每张表只做每张表的事即可，不要把所有事都集中一块

第二范式到底什么意思： 在第一范式的基础上，明确每一张表

数据库第三范式

在满足第二范式的基础上，在实体中不存在非主键属性****传递函数依赖于主键属性。（表中字段[非主键]不存在对主键的传递依赖）

**传递依赖：**A依赖于B，B依赖于C，就可以说A依赖C。看这样一张表：

这张表中有如下决定关系： 学号–>姓名，性别，系号–>决定系名，宿舍号–>决定宿舍电话，也有 学号–>系名，学号–>宿舍电话。

在这样一张表中则存在着传递依赖。也就是系名依赖系号，系号依赖学号，那么间接的系名依赖学号，宿舍号、宿舍电话和学号之间也有同样的关系。这样设计表的同样会带来数据冗余，操作异常等问题。那么我们同样可以用关系分解的分解的方法来消除传递依赖，将这张表分成三张表：

要有主外建关系，这就是数据库设计的第三范式了，在设计数据表的过程中注意三范式的应用，多多实践，有助于对三范式有更深入的理解。

第三范式到底什么意思： 在第一范式和第二范式的基础上，让表与表之间建立关系

遵循三大范式，4fC，5fc。表 ：拆分越细，查询数据，需要表的连接查询。sql

反范式：会主动增加冗余列。好处，查的快，适当情况下，是允许不符合三范式。

订单表：

商品表：

订单表： 订单编号 商品id

​ 订单编号 商品id 商品名称 ，将来查询的时候，比较快。不用直接多个表的链接。

索引(*)

索引有作用： 提高查询的效率 。官方：帮助MySQL高效获取数据的数据结构。

字典：查字； 偏旁部首 汉语拼音等；->目录 索引类似于字典的目录。

分类：

一、单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
二、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

三、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

一般用于数据量比较大的情况下我们建立索引，如果数据量太小也必要去建立索引

应用场景：

1.经常查询列； 主键 unique

2.join列

不适合创建索引：

1.数据量少

2.基数选项少列不合适 列：性别 男 女

2.创建索引字段，频繁更新

示例

-- 创建索引
create index index_stu(索引名称) on emp(ename);

-- 查看索引
show index from emp;

-- 测试索引
select * from emp where ename = "name1577";
explain
-- 删除索引
drop index abc on emp;

横表/纵表

纵表和横表的概念及其相互转换

横表就是普通的建表方式，如表结构为：主键、字段1、字段2、字段3...。 如果变成纵表后，则表结构为： 主键、字段代码、字段值。而字段代码则为字段1、字段2、字段3...。  具体为电信行业的例子。以用户帐单表为例，一般出账时用户有很多费用，其数据一般存储为：时间，客户ID，费用科目，费用。这种存储结构一般称为纵表，其特点是行数多，字段少。纵表在使用时由于行数多，统计用户数或对用户进行分档时还需要进行GROUP BY 操作，性能低，且操作不便，为了提高性能，通常根据需要将纵表进行汇总，形成横表，比如：时间、客户ID、基本通话费、漫游通话费、国内长途费、国际长途费...。

横表
优点：一行表示了一个实体记录，清晰可见，一目了然。
缺点：如果现在要给这个表加一个字段，那么就必须重建表结构。

纵表
优点：如果现在要给这个表加一个字段，只需要添加一些记录。
缺点：数据描述不是很清晰，而且会造成数据库数据很多。另如果需要分组统计，要先group by，较繁琐。

结论：应该把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。 

纵表转横表

有如下纵表

create table proper(
   student_name varchar(20),
   course_name varchar(20),
   score double
);

INSERT INTO `proper`(`student_name`, `course_name`, `score`) VALUES ('张三', '语文', 67);
INSERT INTO `proper`(`student_name`, `course_name`, `score`) VALUES ('张三', '数学', 78);
INSERT INTO `proper`(`student_name`, `course_name`, `score`) VALUES ('李四', '语文', 90);
INSERT INTO `proper`(`student_name`, `course_name`, `score`) VALUES ('李四', '英语', 89);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SE5yIAsN-1686570551430)(images/clip_image001-1626493738328.png)]

将其转为横表

select 
-- 第一列显示的字段
p.student_name,
-- 当course_name的名字是‘语文’，把对应的成绩显示
sum(case p.course_name when '语文' then p.score end ) as 语文, 
sum(case p.course_name when '数学' then p.score end ) as 数学, 
sum(case p.course_name when '英语' then p.score end ) as 英语
from proper p 
-- 对名字进行分组
group by p.student_name

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zLSZoP0D-1686570551431)(images/clip_image002-1626493738329.png)]

横表转纵表

有如下横表

create table score_horizontal (
   student_name varchar(20),
   chinese varchar(20),
   math varchar(20),
   english varchar(20)
);

INSERT INTO `score_horizontal`(`student_name`, `chinese`, `math`, `english`) VALUES ('张三', '67', '78', NULL);
INSERT INTO `score_horizontal`(`student_name`, `chinese`, `math`, `english`) VALUES ('李四', '90', NULL, '89');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vU4GVX33-1686570551431)(images/clip_image003.png)]

将其横表转为纵表

select s.student_name,'chinese' as 科目,s.chinese as 成绩
from score_horizontal s
union all 
select s.student_name,'math' as 科目,s.math as 成绩
from score_horizontal s
union all 
select s.student_name,'english' as 科目,s.english as 成绩
from score_horizontal s;

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8fmBxYS1-1686570551431)(images/clip_image004.png)]

数据库备份

备份：把数据库里面的内容进行备份放到硬盘或者其他位置。如果数据库出现问题之后，可以进行数据的恢复。

使用命令备份

备份数据库

在命令行中  mysqldump -u 用户名 -p 数据库名 > 文件名.sql

-- 进行数据库备份
mysqldump -u root -p abc > D://abc.sql

恢复的前提，数据库一定要提前存在，因为备份的sql脚本中没有创建数据库的sql，只有表相关的sql

第一种恢复：
source 文件名.sql  

-- 示例
-- 创建一个数据库
create database abc;

-- 切换数据库
use abc;

-- 进行恢复
source D://abc.sql

-- 查看当前使用的数据库
select database();

-- 查看当前数据库下的所有表
show tables;

第二种恢复：
mysql -u root -p 数据库名 < D://数据库名.sql

-- 示例
-- 创建一个数据库
create database abc;

-- 切换数据库
use abc;

-- 进行恢复
mysql -u root -p abc < D://abc.sql

使用navicat备份

选中要备份的数据库

【右击】–【转存储sql文件】-- 【结构和数据】

【右击】–【转存储sql文件】-- 【仅结构】

恢复数据库： 先创建数据库，再拖拉拽即可

记录最后修改时间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zv8P33gx-1686570551432)(images/clip_image001.png)]

insert into emp values(null, 'bbb' , 89,90,99,null);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7iQnKSTI-1686570551432)(images/clip_image002.png)]

作业

以下所有数据库设计，必须书写数据库设计文档

订单系统设计

1、用户信息。
2、商品信息
3、订单信息。 一个订单表里 ，可以买多个商品， 每个商品有自己的数目。 其中一个商品就是一个 订单详情。
4、订单详情。

租房网

自己考虑自己设计

远程数据库

在企业开发中，并不是每一个开发人员都有一个自己的数据库，一个团队只有一个数据库，也就是说：多个人连接同一个数据库

-- 项目经理，创建一个用户,组内所有成员登录jack即可
create user 'jack'@'%' IDENTIFIED by 'jack';

-- 项目经理，创建一个用户,指定组内10.50.0.36成员登录jack即可
create user 'jack'@'10.50.0.36' IDENTIFIED by 'jack';

-- 项目经理，jack 这个用户对test库的emp表有select操作权限
grant select on test.emp to 'jack'@'10.50.0.36';

己设计

远程数据库

在企业开发中，并不是每一个开发人员都有一个自己的数据库，一个团队只有一个数据库，也就是说：多个人连接同一个数据库

-- 项目经理，创建一个用户,组内所有成员登录jack即可
create user 'jack'@'%' IDENTIFIED by 'jack';

-- 项目经理，创建一个用户,指定组内10.50.0.36成员登录jack即可
create user 'jack'@'10.50.0.36' IDENTIFIED by 'jack';

-- 项目经理，jack 这个用户对test库的emp表有select操作权限
grant select on test.emp to 'jack'@'10.50.0.36';