MySQL8学习笔记1

1.逻辑设计之宽表模式
就是把一个对象的所有属性，全存储到一个表中。如果这个对象属性很多，对应这个存储对象属性的表，列就比较多。
例如：

宽表模式存在的问题：
数据冗余：相同的数据在一个表中出现了多次，例如讲师名sqlsercn。一方面增加了对象所占用的存储空间，另一方面在进行数据维护的时候，就不得不对数据进行多次的维护。例如假如讲师职位“高级DBA”发生了变化，就要进行四次维护，如果有一条没有维护，就造成了数据的不一致。

由于数据冗余，就会引发数据更新异常的问题-
a.数据更新异常：修改一行中某列的值时，同时修改了多行数据，例如：

虽然可以把主标题作为这个表数据行的唯一标识，也就是主键来使用，通过主键对数据进行更新，虽然可以避免数据的更新异常，但是又出现了一些其他的问题，例如数据插入异常。

b.数据插入异常，部分数据由于缺失主键（非空且为1）信息而无法写入表中，例如想在方向中添加后端开发这一方向，但是目前并没有与之匹配的课程，那么由于表的主键是课程的主标题，此时主标题就是空的，违反了主键非空且为1的约束，因此后端开发这个方向就不能维护到我们的课程表中。

c.数据删除异常：删除某一数据时不得不删除另一数据，比如我们想删除数据库这一方向：

那么执行完这个语句之后，我们除了删除掉了数据库，还把所有方向为数据库的课程删除掉了，这并不是我们想要的结果。

宽表模式的适用场景：配合列存储的数据报表应用

2.数据库的设计范式
第一范式：表中的所有字段都是不可再分的。下面第一张图就违反了第一范式，因为联系方式可以再分，这样的表是无法在数据库中建立出来的，改正为第二张图即可。

第二范式：在第一范式的基础之上定义，表中必须存在业务主键，并且非主键依赖于全部业务主键。
业务主键是指那些可以唯一标识出每一行业务数据的列，或者是列的组合，如果使用列的组合作为业务主键，还要求表中的其他列必须要依赖于全部的这些组合中的列，而不能存在只依赖组合业务主键中一部分列的情况。

例如上面这个表，如果我们使用用户列作为业务主键是不可行的，因为一个用户可以拥有多条笔记，所以用户无法唯一标识出表中的记录。同样的，同一章节也可能有多个用户来写笔记…所以在这个表中我们无法使用单一的某一列来作为主键。
修改：

第三范式：在第一，第二范式的基础上所定义。要求表中的非主键列之间不能相互依赖：

3.Mysql中常用的数据类型

最大值是2的n-1次幂，最小值是2的n-1次幂-1，n就是存储空间的位数。例如tinyint，1字节是8位，所以最大值是2的7次方-1，最小值是-2的7次方。对于无符号类型，是不允许有负数的，所以它的最大值约比有符号类型扩大了一倍。

decimal(18,0)，数值中共有18位数，其中整数占18位，小数占0位。Decimal(n,m)表示数值中共有n位数，其中整数n-m位，小数m位。
例：decimal(2,1)，此时，插入数据“12.3”、“12”等会出现“数据溢出错误”的异常；插入“1.23”或“1.2345…”会自动四舍五入成“1.2”；插入“2”会自动补成“2.0”，以确保2位的有效长度，其中包含1位小数。
double是不精确的数值，经过实际计算后，会和实际数据有微小的差别，所以如果向保证精确，就要使用decimal类型。


固定长度是指不论实际有多少个字符，都占用m个字符的空间。可变长度则是根据实际字符长度确定空间。

如何为数据选择合适的数据类型：
a.虽然所有数据都可以用字符串来进行存储，但是这绝不是一个好的选择，我们应该优先选择符合存储数据需求最小的数据类型。例如3147483647这个数，可以用Unsigned int来存储，这样就比使用bigint节省了一半的存储空间。

本来字符串中占15个字节，转为Unsigned int类型后就只占用4个字节啦！
b.谨慎使用Enum，Text字符串类型
c.同财务相关的数值型数据，必须使用decimal类型


章节编号里的意思是保证章节至少有两位，不足两位的话在前面自动补零。tingint（2）并不能限制存储的数值范围，也不能限制它所使用的存储空间的大小。

4.数据库对象命名原则
如何为表和列选择合适的名字
a.不应该使用中文来命名我们的数据库对象
b.所有数据库对象名称必须使用小写字母可选用下划线分割
c.所有数据库对象名称定义禁止使用MySQL保留关键字
d.数据库对象的命名要能做到见名识义，并且最好不要超过32个字
e.临时库表必须以tmp为前缀并以日期为后缀
f.用于备份的库，表必须以bak为前缀并以日期为后缀
g.所有存储相同数据的列名和列类型必须一致

5.如何访问mysql
在linux命令行中输入 mysql -u root -p
然后输入密码，就可以访问mysql了（需要在虚拟机中安装mysql，网上的教程很多，在此不再赘述）。
推荐的图形化管理工具：Navicat

6.初识SQL
什么是SQL：是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语言的作用：对存储在关系数据库管理系统中得数据进行增删改查等操作。

常用的SQL语言种类：
DCL：数据库管理类语句的简称，是用于管理数据库访问的，比如授权语句，用户建立语句等。
DDL：数据定义类语句的简称，主要是用于建立数据库对象的，比如建立表的语句，修改表结构的语句。
DML：数据操作类语句的简称，用于操作存储在数据库中的数据，执行增删改查等任务的语句全属于DML语句。
TCL语句：事务控制类语句的简称，主要是实现对事物的控制，比如开启事物，回交事物等。

7.数据库访问控制语句
DCL
a.建立数据库账号：create user


mc_class就是用户名，192.168.1.%是访问控制列表，%是一个通配符，表示在这个网段下的所有主机都可以访问我们的数据库服务器，identified with命令是用来指定mysql认证所使用的加密方式的，以及账号的密码。
例如：

max_user_connections 1的意思是最大只能有1个线程连接这个用户。
注意要登录root用户，在root用户下创建新用户，因为此时其他新创建的用户是没有权限的。
创建完之后，我们在linux虚拟机下就可以使用命令：
mysql -u mc_class -p -h 192.168.0.10去登陆这个用户了，-h参数后接的是数据库所在的位置，因为数据库在我们的虚拟机中，所以这里填写的就是我们虚拟机的默认ip：192.168.0.10（每个人都不一样，具体可用ifconfig命令查看）

b.对用户授权：grant

可以输入命令show privileges\G来查看有哪些权限

对象可以是一个数据库，一个表，一个函数，或者是一个存储过程

注意中括号代表可选操作



可以用逗号分隔权限，然后用括弧指定是哪一列的权限。

使用grant命令注意事项：
在mysql8.0版本以后，使用grant命令不能自动创建账号了，所以使用grant授权的数据库账户必须存在，例如下面就会报错：

用户使用grant命令授权必须具有grant option，并且也只能把本身所具有的权限授予其他的用户。也就是说，如果一个用户具有mysql下user表的查询权限，那么他只能把user表查询权限授予其他用户，而不能把自己没有的权限授予其他用户，可以使用\h grant命令来查看grant命令帮助信息。

c.收回用户权限：revoke

8.常用的DDL语句

注意点：
a.drop database和table之前确认数据已经没用，并且已备份
b.alter修改表结构，在对enum类型字段进行修改的时候，可能会影响数据
c.索引不是越多越好，过多的索引反而会影响数据查询效率，要对多余的索引进行删除，删除索引不会对数据造成影响，但是可能会影响sql的执行计划。

truncate table是删除表的所有数据，并保留这个表的结构，但是不会记录数据的删除日志，所以也无法通过日志对已删除的数据进行恢复，实际上相当于drop table+create table