MySQL数据库初见解

Jvav南神

已于 2022-08-15 20:45:07 修改

阅读量144

点赞数 2

分类专栏： Java南神进阶之路文章标签：数据库 mysql

于 2022-08-13 17:51:57 首次发布

本文链接：https://blog.csdn.net/qq_52700033/article/details/126315884

版权

Java南神进阶之路专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库

什么是数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。其本质是一个文件系统,用户可以对文件中的数据进行新增、查询、更新、删除等操作。

数据库的优点

数据库是按照特定的格式将数据持久化存储在文件中，通过SQL语句可以方便的对大量数据进行增、删、改、查操作，数据库是对大量的信息进行管理的高效的解决方案。

结构化查询语言SQL

结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。

SQL作用

通过SQL语句我们可以方便的操作数据库中的数据、表、数据库。

SQL是数据库管理系统都需要遵循的规范。

用户管理与权限管理

#创建用户与设置用户密码
#语法：create user "username"@"host" identified by "password";
create user 'test'@'localhost' identified by '123456';

#删除用户
#语法：drop user 用户名@主机名；
drop user test@'localhost';
drop user命令会删除用户以及对应的权限,
执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

#给用户授权
#语法：grant 权限码 on 数据库名.表名 to 用户名@主机名 identified by "password";
grant all on db.* to test@localhost;

#取消用户权限
#语法：REVOKE 权限码 ON 库名.表名 FROM 用户名@主机名;
REVOKE all ON db.* FROM test@localhost;

#查看用户权限
#语法：show grants for 用户名@主机名; 
show grants for 'test'@'localhost';

权限码表示授予的权限类型，常用的有以下几种类型：

all ：所有权限

select：读取权限

delete：删除权限

update：更新权限

create：创建权限

drop：删除数据库、数据表权限

疑惑

当我创建了一个新用户并赋予了all权限,用新用户创建新的连接后,在新的连接里面不能创建数据库,会报错误,提示没有权限

不理解明明给了所有的权限却不能创建数据库

没有权限创建库我就通过根用户root创建一个新的名为db的我想要的数据库,

执行 grant all on db.* to 'test'@'localhost'; 命令

这样test用户创建的新连接里面就出现了db数据库

MySQL常用的数据类型

整数	int
浮点数	float	单精度
浮点数	double	双精度
定点小数	decimal	对decimal(M,D) ，如果M>D，为M+2否则为D+2,用于小数值
日期时间	datatime	格式: YYYY-MM-DD HH:MM:SS
日期时间	data	格式: YYYY-MM-DD
字符串	char	定长字符串
字符串	varchar	变长字符串

decimal型的默认整数位为10，小数位为0，即默认为整数。

在MySQL中，定点数以字符串形式存储，因此，其精度比浮点数要高，而且浮点数会出现误差，这是浮点数一直存在的缺陷。如果要对数据的精度要求比较高，还是选择定点数decimal比较安全

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换，CHAR删除尾部的空格，VARCHAR则保留尾部的空格。

因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度

char的处理效率比varchar快,因为varchar还要占1个byte用于存储信息长度

当确定字符串为定长、数据变更频繁、数据检索需求少时，使用char

当不确定字符串长度、对数据的变更少、查询频繁时，使用varchar

MySQL的增删改查

插入insert

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
#没有添加数据的字段会使用NULL

删除delete

1. 不带条件删除,删除所有数据
   DELETE FROM 表名;

2. 带条件删除数据
   DELETE FROM 表名 WHERE 字段名=值;

修改update

1. 不带条件修改数据
   UPDATE 表名 SET 字段名=值;

2. 带条件修改数据
   UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

查询select

1. 使用*表示所有列
   SELECT * FROM 表名;

2. 查询指定列的数据,多个列之间以逗号分隔
   SELECT 字段名1, 字段名2... FROM 表名;

子查询

select型子查询

select型子查询就是子查询出现在主查询的select字段列表中

#需求：要查出每个部门的员工数有多少
    SELECT d.dept_name, ( 
        SELECT COUNT(*)
        FROM empl e
        WHERE e.dept_id = d.dept_id
     ) empl_num
     FROM dept d;

where型子查询

where型子查询就是子查询出现在主查询的where条件子句中

#需求：列出开发部的员工列表
select empl_id, empl_name from empl
where dept_id = (select dept_id from dept where dept_name like '%开发部%');

from型子查询

查询结果集在结构上可以当成表看，那他就可以当成临时表对他进行再次查询

#需求：列出开发部和测试部的姓李的员工列表
    select empl_id, empl_name
    from (select empl_id, empl_name from empl, dept
          where empl.dept_id = dept.dept_id
          and dept_name in('开发部','测试部') ) ta
        where ta.empl_name like '李%';

exists型子查询

exists型子查询，又叫相关子查询

其语法是在where子句中，exists(完整的查询语句）。这时外层父查询先执行，在处理每一条记录时，执行子查询，子查询有结果返回，不是0条时，则此记录进入返回的结果集，否则将被剔除

    SELECT b.*
    FROM boys b
        WHERE NOT EXISTS(
        SELECT boyfriend_id
        FROM girls g
        WHERE b.b_id=g.boyfriend_id
    );

聚合函数

AVG函数	AVG()函数计算一组值的平均值。它计算过程中是忽略`NULL`值的
MAX()函数	MAX()函数返回一组值中的最大值
MIN()函数	MIN()函数返回一组值中的最小值
COUNT()函数	COUNT()函数返回结果集中的行数,在统计时，会计入null值
SUM()函数	SUM()函数返回一组值的总和，SUM()函数忽略`NULL`值。如果找不到匹配行，则SUM()函数返回NULL值

常用关键字及子句

group by	分组, 同一列里面相同的字段分为同一组
where	后面加过滤条件,一般放在 group by 前面
having	后面加过滤条件放在 group by 后面
union / union all	两者都是用来连接多个结果集,union会去重,union all不会去重
distinct	去重,对数据库表中一个或者多个字段重复的数据进行过滤，只返回其中的一条数据,distinct只可以在select中使用,底层是分组
order by	排序,默认为升序(ASC),后面加DESC表示降序
limit	用于强制 SELECT 语句返回指定的记录数 select * from emp limit m,n; 表示在emp表从m行开始返回n条记录其中包括第m行记录,常用来分页查询

any、some和all关键字

在where型子查询中，使用了 in 与 not in 关键字。这是一种集合处理。还有集合处理的关键字，也经常用到子查询中，就是 any 和 all 关键字（some是 any的同义词，较少使用）

运算符\关键字	ANY	ALL
> 、>=	最小值	最大值
<、<=	最大值	最小值
=	任意值
<>、!=		任意值

注意：= any 等价于 in，!= all 等价于 not in，= all 是没有意义的

MySQL约束

PRIMARY KEY	主键,不能为NULL,每个表只能定义一个主键。主键值必须唯一标识表中的每一行，即表中不可能存在有相同主键值的两行数据。这是唯一性原则
FOREIGN KEY	外键,可以为NULL,外键中列的数目必须和主表的主键中列的数目相同。外键中列的数据类型必须和主表主键中对应列的数据类型相同
UNIQUE KEY	唯一约束,确保列的唯一性,在表中可以有多个,允许有空值,但只能有一个
CHECK	检查约束,用来检查数据表中字段值有效性,设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入
DEFAULT	默认值约束,用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。
NOT NULL	非空约束,指字段的值不能为空

MySQL事务(重点)

事务具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性

关于事务的定义有几点需要注意：

1.数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。
2.构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。
3.构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功,数据库总能保持一致性状态。
4.以上即使在数据库出现故障以及并发事务存在的情况下依然成立。

如何实现数据库事务

在事务的ACID特性中,C即一致性是事务的根本追求,而对数据一致性的破坏主要来自两个方面

1.事务的并发执行
2.事务故障或系统故障

数据库系统是通过并发控制技术和日志恢复技术来避免这种情况发生的。

并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

并发事务处理带来的问题

更新丢失（Lost Update） 当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，有于每个事务都不知道其他事务的存在，就会发生更新问题：最后的更新覆盖了由其他事务所做的更新。
脏读（Dirty Reads） 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为“脏读”。事务A读取到事务B已经修改但未提交的数据，还在这个数据基础上做了修改。此时，如果事务B回滚了，事务A的数据无效，不符合一致性要求。
不可重读（Non-Repetable Reads） 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现起读出的数据已经发生了改变、或某些记录已经被删除。这种现象叫做“不可重读”。事务A读取到了事务B已经提交的修改数据，不符合隔离性。
幻读（Phantom Reads） 一个事务按照相同的查询条件读取以前检索过的数据，却发现某些事务插入了满足其查询条件的新数据，这种现象称为“幻读”。事务A读取了事务B提交的新增数据，不符合隔离性。

事务的隔离等级

读未提交(READ UNCOMMITTED):能读到其他事务未提交的数据
读已提交(READ COMMITTED):只能读取到其他事务已提交的数据
可重复读(REPEATABLE READ)
串行化(SERIALIZABLE):事务依次执行,不存在并发问题

数据库三大范式(重点)

第一范式（1NF）

第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式（2NF）

满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求实体中每一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。
若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。