mysql根据两个字段儿分两次组_MySQL学习笔记（二）

零、VsCode操作MySQL

大赞！！！vscode果然还是一如既往的优秀，没有让我失望~

0、解决密码认证方式问题

8.0以上版本特殊的密码认证方式会导致登录失败，解决方案如下：

控制台输入：

alter user 'root'@'localhost' identified with mysql_native_password by 'root';
flush privileges;

1、下载下列插件（选择1.x.x的版本）

2、在左侧的资源管理器中找到MySQL入口，点击+

host输入MySQL服务器名或ip （根据实际情况输入）

（本地服务器就输入localhost或者127.0.0.1）

user是输入数据库用户名（根据实际情况输入）

password是数据库密码（根据实际情况输入）

port是数据库端口号 默认3306（根据实际情况输入）

登陆成功：

3、建立新的query文件

4、这种结构下软件分窗就显得非常实用了

5、执行query语句快捷键：

执行所有语句：不框选语句情况下按F9

执行单个语句：选择语句情况下按F9

6、执行代码格式化：alt +shift +F

一、数据库的设计

1、多表间的关系

①、一对一

例如：人和身份证的关系

实现方式：在任意一方添加唯一外键指向另一方主键。

②、一对多/多对一

例如：一个部门有多个员工，一个员工只有一个部门

实现方式：

在多的一方建立外键并指向一的一方的主键

③、多对多

例如：一个学生有多门课程，一门课程有多个学生

实现方式：多对多需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为作为第三张表的外键，分别指向两张表的主键

2、数据设计范式

设计数据库需要遵循的规范

六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、

巴斯科特范式（BCNF）、第四范式（4NF）、第五范式（5NF）

要遵循后面的范式，要先遵循前面的范式

①、第一范式（1NF）：

每一列都是不可分割的基本数据项

意思是列不可以嵌套及混合

只按照第一范式设计当然不行，问题如下：

②、第二范式（2NF）：

解决非主属性部分依赖于主关键字。（在1NF基础上消除非主属性对主码的部份依赖）

几个概念：

a：函数依赖

A-->B

在一张表中，如果通过A属性（属性组）的值可以唯一确定B属性的值，则B依赖于A

如：

学号-->姓名 学号可以唯一确定姓名 姓名依赖于学号

学号 ！-->分数 只靠学号无法确定分数 分数不依赖于学号

(学号,课程名称)-->分数 (学号,课程名称)可以唯一确定分数，分数依赖于(学号,课程名称)

b：完全函数依赖

A-->B

在一张表中，如果通过A的属性组中的所有值可以唯一确定B属性的值，则B完全依赖于A

例如：

(学号,课程名称)-->分数 (学号,课程名称)可以唯一确定分数，分数依赖于(学号,课程名称)

c: 部分函数依赖

A-->B

在一张表中，如果通过A的属性组中的部分值可以唯一确定B属性的值，则B部分依赖于A

例如：

(学号,课程名称)-->姓名 (学号,)可以唯一确定姓名，姓名部分依赖于(学号,课程名称)

d：传递函数依赖

A-->B-->C

在一张表中，如果通过A属性（属性组）的值可以唯一确定B属性的值，通过B属性（属性组）的值可以唯一确定C属性的值，则C传递函数依赖于A

例如：学号-->系名-->系主任

e：码

在一张表中，如果一个表中的一个属性（属性组）被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。

f：主属性

码中的属性

g：非主属性

非码中的属性

现在我们来消除例子中的部分依赖：

拆分表格后，数据冗余问题解决了，但还有2、3问题

③、第三范式（3NF）：

一个数据库表中不包含已在其它表中已包含的非主关键字信息 （在2NF基础上消除传递依赖）

还应该添加课程表

二、数据库备份还原

1、命令行

①备份

mysqldump -u用户名 -p密码 数据库名 > 保存的路径

②还原

1）登录数据库服务器

2）创建数据库

3）使用数据库

4）执行： source 文件路径。

2.图形化

①、备份

② 还原

三、多表查询

1、查询笛卡尔积

select * from 表名1,表名2;

这样查询出来的结果是笛卡尔积（A和B的所有组合可能性的集合）。

这样的结果很多无用数据。

2、多表查询的分类

①、内连接查询 //查询两表交集

a）隐式内连接

select 表1.列名，表2.列名 from 表1 别名1，表2 别名2 where 条件；

b）显式内连接

select 字段列表 from 表名1 join 表2 on 条件；

注意：要用内连接查询需要确定：

a）确定从哪些表中查数据

b）条件是什么（判断有效数据）

c）确定需要查询哪些字段

②、外链接查询

a）左外链接 //查询左表所有记录及交集

select 字段列表 from 表1 left join 表2 on 条件;

例：假设有一位新员工还没有部门

如果查内连接交集就查不到了，查左连接就可以查到

b）右外链接 //查询右表所有记录及交集

select 字段列表 from 表1 right join 表2 on 条件;

例：

③、子查询

子查询就是查询中嵌套查询

a）子查询情况1：子查询的结果是单行单列的，此时子查询可以作为条件，使用运算符进行计算。

如：

b）子查询情况2：查询的结果是多行单列的 可以使用运算符in

例如：

c）子查询情况2：查询的结果是多行多列的 把子查询当作虚拟表供查询

四、事务

1、事务

①、概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

A给B要划钱，A的账户-500元，B的账户就要+500元，这两个update语句必须作为一个整体来执行，不然A扣钱了，B没有加钱这种情况很难处理。

②、操作

a）开启手动事务：start transaction

b）提交：commit

c）回滚：rollback

注意：1.即使没有提交，你在本query最后查询数据也会发现变化了，但当你退出软件重启数据库发现没有改变，你看到的只是临时数据。只有commit后才是真正的数据。上图中的数据是我重启后的数据

2.commit和rollback只能执行一个，需要类似于PHP类的后台程序做判断，数据库本身只提供语句，不能再query中写判断。

③、mysql不开启手动提交事务时，是默认自动提交的，每条语句都会提交一次。

修改自动提交：

a）查看事务的默认提交方式：

SELECT @@autocommit;

b）修改为手动提交

set @@autocommit=0;

c）如果改为手动提交 必须commit才会提交数据

2、事务的四大特征

a）原子性：不可分割的最小操作单位，要么同时成功，要么同时失败

b）持久性：当数据提交或回滚时，会持久化的改变数据

c）隔离性：多个事务之间项目独立

d）一致性：事务操作前后总量不变。

3、事务的隔离级别

事务有隔离性，但是多个事务同时操作同一批数据，会引发一些问题，设置不同的隔离级别可以解决这些问题

引发的问题：

a）脏读：一个事务读取到另一个事务没有提交的数据

b）虚读（不可重复读）：同一个事务中两次读取到的同一数据不一样

c）幻读：一个事务操作数据表中所有数据，另一个事务添加数据，则第一个事务查询不到自己的修改（MySQL中不存在）

隔离级别：

a）read uncommited 读未提交

此级别下，可出现：脏读、虚读、幻读

b）read commited 读已提交

此级别下，可出现：虚读、幻读

c）repeatable read 可重复读 （MySQL默认）

此级别下，可出现 幻读

d）serializable：串行化

此级别下，无问题。但效率最低。

查询隔离级别：

select @@tx_isolation;   /*mysql 8.0以后弃用*/
select @@transaction_isolation;/*mysql 8.0以后用*/

设置隔离级别：

set global transaction isolation level read committed;

注意：设置后需要重启生效，上图中我已经重启。

五、DCL

DCL用来管理数据库用户、授权

1、管理用户

①、添加用户：

create  user '用户名'@'主机名' identified by "密码";  /*注意，必须用单引号！*/

②、删除用户：

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

③、修改用户密码：（8.0以后新方法）

alter user 'root'@'localhost'IDENTIFIED BY 'MyNewPass';
flush privileges;

④、查询用户：

a）切换到mysql数据库

我不知道为什么workbench里不显示这个数据库，但他真的有

b）查询user表

use mysql;
show databases;
select * from user;

⑤、忘记root密码怎么办？

1.cmd执行（管理员权限） ：net stop MySQL

2.无验证方式启动mysql服务：mysql --skip-grant-tables

3.打开新的cmd窗口，输入mysql 直接回车登录成功

4.再新cmd中使用mysql数据库 ：use mysql；

5.修改root密码。

6.关闭所有cmd

7.打开任务管理器 结束 mysqld.exe 这个进程

8.重启服务，新密码登录

2、权限管理

①、查询权限

show grants for '用户名'@'服务器';

这个用户除了登录没有任何权限

查询root发现他的权限无限大

②、授予权限

grant select on 数据库.表名 to '用户名'@'主机名';

授权后只有这张表他可以看

授予所有权限

grant ALL on *.* to '用户名'@'主机名';

常用权限有：

CSDN-专业IT技术社区-登录​blog.csdn.net

show privileges;显示结果---(权限操作分类)
	all(谨慎，包含管理数据库的权限)    授予某个级别特定的所有权限，除了grant option权限:表示自己有的权限授权给别人
	alter	允许使用alter table语句        级别：global、database、table(1、2、3)
	alter routine  允许修改或删除存储例程  级别：global、database、table(1、2、3)
	create   允许创建数据库和表
	create tablespace   允许创建、修改、删除表空间和日志文件组  级别：global
	create temporary tablespace  允许创建临时表   级别：global、database
	create user   允许使用create user、drop user、rename user、revoke all privileges语句    级别：global
	create viwe   允许创建和修改视图
	shutdown   允许使用mysqldmin shutdown    级别：global
	delete  允许删除表中的行
	drop    允许删除数据库、表、视图
	event   允许使用事件   mysql的时间调度-linux下的crontab
	execute 允许用户执行存储过程
	file    允许用户使用load data infile读写文件  级别：global
	grant option  允许把自己有的权限授权给其他用户或者从其他用户那里废除  级别：global、database、table、procedure、proxy
	index    允许创建或删除索引
	insert   允许向表中插入行
	lock tables 允许使用lock tables语句   级别：global、database
 	process 允许使用show processlist语句查看正在运行的进程  级别：global

	proxy   允许用户代理  级别
	references  允许使用外键    级别：global、database、table、column
	reload  允许使用flush语句  级别：global
	select  允许查询表        级别：global、database、table、column
	show databases 允许使用show databases语句  级别：global
	show view 允许使用show create view语句     级别：global
	super  允许使用其他管理语句
	trigger   允许触发器操作
	update  允许更新表中的行      级别：global、database、table、column
	usage   "no privileges"的同义词
————————————————
版权声明：本文为CSDN博主「我说，你好」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44569143/article/details/88401103

③、撤销权限

revoke select on 数据库.表名 from '用户名'@'主机名';