[原创]数据库基础知识详解

1、层次模型

2、网状模型

3、关系模型

二维关系

view (只包含固定字段，不包含其他字段)

index

row

column

表

索引

视图

<code>数据库</code> : Database

<code>表</code> : table

<code>行</code> : row

<code>列</code> : column

<code>索引</code> : index

<code>视图</code> : view

<code>用户</code> : user

<code>权限</code> : privilege

<code>存储过程</code> : procedure

<code>存储函数</code> : function

<code>触发器</code> : trigger

<code>事件调度器</code> : event schedule

SQL引擎

分析器

计划执行器

优化器

操作求解器

存储引擎

文件和存储接口

缓冲管理器

磁盘空间管理器

恢复管理器

事务管理器

锁管理器

类似于OS的shell接口，操作数据库的数据接口，也提供了编程功能

SQL接口语言的标准（其由ANSL组织定义）

SQL86

SQL89

SQL92

SQL99

SQL03

SQL接口的语言分类

grant

revoke

insert

delete

update

select

create

alter

drop

DDL:(Data Defined Language)

DML:(Data Manapulating Language)

DCL: (Data Control Language)

ACID

A : 原子性 → 不可分割的整体

C : 一致性 → 数据的变化是一致性的

I : 隔离性 → 事务彼此之间是隔离的

D : 持久性 → 只要一个事务完成，它都是持久完成的。

事务是将组织多个操作为一个整体，要么全部执行，要全部不执行。其实现机制为：

回滚机制

事务机制

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

1）第一范式（1NF）所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域，并不可分析

2）第二范式（2NF）第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，即不能有两个行是一样的。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

3）第三范式（3NF）第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。简而言之，不依赖于非主属性，表与子表中不能有相同的字段。

数据库为C/S架构，如下：

S : server ,监听于套接字止，接收并处理客户端的应用请求

C : Client

ODBC : Open Database Connection(开放式数据互联)

CLI

GUI

程序接口

应用编程接口

单进程多线程

用户连接通过线程实现

一个线程池可以定义mysql的并发连接

处理用户连接的叫连接线程

数据字典:数据一切的元数据信息,依赖mysql库来存储

1）<code>约束</code> : Constraint

<code>主键</code> : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，且必须提供数据，即<code>NOT NULL</code>

<code>唯一键</code> : 一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行，允许为<code>null</code>

<code>外键</code> : 一个表中的某字段可填入数据取决于另一个表中的主键已有的数据。

<code>检查性约束</code> : 表达式约束,取决于表达式的要求

2） <code>索引</code> : 将表中的一个或多个字段中的数据复制一份另存，并且这些字段需要按特定的次序排序存储,常见的索引类型:

<code>树形索引</code>(MariaDB默认为Btree索引)

<code>bash索引</code>索引有利于读请求,但不得写请求

3） <code>关系运算</code>

内连接

外连接

自连接

左外连接

右外连接

<code>选择</code> : 挑选出符合条件的行（部分）

<code>投影</code> : 挑选出需要的字段

<code>连接</code> : 将多张表关联起来

4）<code>数据抽象</code> :

<code>物理层</code> : 决定数据的存储格式,即RDBMS 在磁盘上如何组织文件

<code>逻辑层</code> : 描述DB存储什么数据,以及数据间存在什么样的关系

<code>视图层</code> : 描述DB中的部分数据

5) 关系模型的分类

XML格式就是一种半结构化数据

<code>实体关系模型</code>

<code>基于对象的关系模型</code>

<code>半结构化的关系模型</code>

MySQL

MariaDB

PostgreSQL(pgsql)

Oracle

MSSQL

插件式存储引擎(注:存储引擎也称之为”<code>表类型</code>“)

支持事务

不支持事务

<code>MYISAM</code> → Aria

<code>INNODB</code> → XtraDB

诸多扩展和新特性

提供了更多的测试组件

truly open source

<code>Client</code>

<code>mysql</code> : CLI交互式客户端程序

<code>mysqldump</code> : 备份工具

<code>mysqladmin</code>: 管理工具

<code>mysqlbinlog</code> : 查看二进制日志工具

<code>Server</code>

<code>mysqld</code>: 服务端进程

<code>mysqld_safe</code> : 服务端进程,默认也是运行的此进程

<code>mysqld_multi</code> : 服务端进程, 多实例

<code>mysql_upgrade</code> : 升级工具

服务端监听的两种socket地址

<code>ip socket</code>监听在3306/tcp,支持远程通信

<code>unix socket</code>监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock),仅支持本地通信,通信主机为localhost,127.0.0.1都基于unix socket文件通信

命令行交互式客户端程序—-mysql工具

<code>options</code>

<code>-uUSERNAME</code> : 用户名,默认为root

<code>-hHOST</code> : 服务器主机,默认为localhost

<code>-pPASSWD</code> : 用户的密码

<code>dDB_NAME</code>: 连接到服务端之后,指明默认数据库

<code>-e 'SCRIPT'</code> : 连接至MYSQL运行某命令后,直接退出,并返回结果

````

mysql -uroot -h127.0.0.1 -pmagedu -e ‘show databases;’

<code>支持通配符</code>:

<code>%</code> : 匹配任意长度的任意字符

<code>_</code> : 匹配任意单个字符

<code>\u DB_NAME</code> : 设定哪个库为默认数据库

<code>\q</code> : 退出

<code>\d CHAR</code> : 设定新的语句结束符

<code>\g</code> : 语句通用结束标记

<code>\G</code> : 语句结束标记,但以竖排方式显示

<code>\s</code> : 返回客户端与服务端的连接状态

<code>\c</code> : 取消命令运行

<code>获取命令帮助</code><code>help</code>

<code>VARCHAR(#)</code> : 不区分字符大小写,需要在字符长度加1,最多65536个字符

<code>VARBINARY(#)</code> : 区分字符大小写,需要在字符长度加1,最多65536个字符

<code>TEXT</code> : 可存文本(2^32)个字符,只能存纯文本,不区分大小写

<code>BLOB</code> : 文本(2^32)个字符,可以存储图片

<code>TINYTEXT</code>

<code>MEDIUMTEXT</code>

<code>LONGTEXT</code>

<code>CHAR(#)</code> : 不区分字符大小写

<code>BINARY(#)</code> : 区分字符大小写

定长字符型 : (最多255个字符)

可变长字符型 :

<code>SET</code> : 集合

<code>ENUM</code> : 枚举

<code>FLOAT</code> : 单精度

<code>DOBULE</code> : 双精度

<code>TINYINT</code> : 一个字节

<code>SMALLINT</code> : 二个字节

<code>MEDINUMINT</code> : 三个字节

<code>INT</code> : 四个字节

<code>BIGINT</code> : 八个字节

<code>精确数值型</code>

近似数据型

<code>日期时间型</code>

<code>DATE</code> : 日期型

<code>TIME</code> : 时间型

<code>DATETIME</code> : 日期时间型

<code>TIMESTAMP</code> : 时间戳(从过去到现在经过的秒数)

<code>YEAR(2)</code> : 2位年数

<code>YEAR(4)</code> : 4位年数

<code>数据类型修饰符</code>

<code>PRIMARY KEY</code> : 主键定义

<code>UNIQUE KEY</code> : 唯一键定义

<code>AUTO_INCREMENT</code> : 自增长

<code>UNSIGNED</code> : 无符号,通常用于Int后面,进行修饰为正整数

<code>NOT NULL</code> : 非空

<code>DEFAULT value</code> : 默认值

所有类型都适用:

数值型适用

字段修饰符

数据库操作

查看数据库<code>show databases;</code>

创建数据库<code>create database mydb;</code>

删除数据库<code>drop database mydb;</code>

查看支持的所有字符集<code>show character set;</code>

查看支持的所有排序规则<code>show collation;</code>

修改数据库的默认字符集<code>alter database testdb character set utf32;</code>

修改数据库的默认字符排序规则<code>alter database testdb collate utf32_sinhala_ci;</code>

表操作

查看所有的引擎(被支持的)<code>show engines</code>

查看所有数据库中的表

<code>show tables;</code>

<code>show tables from mysql;</code>

查看表结构<code>desc students;</code>

创建表<code>create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum('F','M'))</code>

向表中添加字段<code>alter table students add second_name char(30);</code>

向表中删除字段<code>alter table students drop second_name;</code>

对表添加主键<code>alter table students2 add primary key (id);</code>

对表删除主键<code>alter table students2 drop primary key;</code>

对表添加索引<code>alter table students2 add index name (name);</code>

对表删除索引

<code>alter table students2 drop index name;</code>

<code>drop index name on students2;</code>

对表添加唯一键<code>alter table students drop second_name;</code>

对表删除唯一键<code>alter table students drop index name;</code>

修改字段字义属性

<code>alter table students modify name char(20);</code>

<code>alter table students2 change name new_name char(20) after id;</code>

<code>alter table students2 change name new_name char(20);</code>

查看表状态<code>show table status like 'students2'\G</code>

修改表引擎<code>alter table students2 engine[=]myisam;</code>

查看表的字段描述<code>desc students;</code>

查看库中的所有表

<code>show tables</code>

删除表<code>drop table students2;</code>

向表中插入数据

<code>insert weizi value(1,'zhen',30,'F');</code>

<code>insert weizi values(3,'wei',30,'F'),(4,'ping',31,'F');</code>

<code>insert weizi (id,name) value (5,"weizi");</code>

删除表中的数据

<code>delete from weizi where name="zhen";</code>

<code>delete from weizi where age is null;</code>

<code>delete from weizi where age > 30;</code>

<code>delete from weizi where id >=50 and age <=20;</code>

<code>delete from weizi order by age asc limit 5;</code>

修改表中的字段值

<code>update weizi set age=35 where id=3 and age=30;</code>

<code>update weizi set age=age-5 where age=35;</code>

<code>update weizi age=age-age;</code>

<code>update weizi set age=age-5 where order by id desc limit 10;</code>

<code>update weizi set age=age-15 where name not like 'stu%';</code>

Select操作语句

使用格式

字段表示法

<code>*</code> : 表示所有字段

<code>as</code> : 字段别名, col1 as alias1

where clause

<code>rlike 'pattern'</code> : 基于正则表达式匹配

<code>between....and...</code>

<code>操作符</code>:

<code>条件逻辑操作</code>

排序

<code>desc</code> : 降序

<code>asc</code> : 升序

Select示例

<code>select name,age from students where age >30 and age<80;</code>

<code>select name,age from students where age between 30 and 80;</code>

<code>select name from students where name like '%ang%';</code>

<code>select name from students where name rlike '^.*ang.*$;</code>

<code>select name,age from students where age is null;</code>

<code>select name,age form students where age is not null;</code>

<code>select id,name from students order by name;</code>

<code>select id,name from students order by name desc;</code>

权限及授权管理

用户表示方法:

管理权限的分类

授权:

授权语法格式

<code>db_name.tbl_name</code>的表示方法:

<code>object_type</code>

<code>all privileges</code> : 表示全部权限

<code>*.*</code> : 所有库的所有表

<code>db_name.*</code> : 指定库的所有表

<code>db_name.tbl_name</code> : 指定库的特定表

<code>db_name.routine_name</code> : 指定库上的存储过程或存储函数

<code>table</code>

<code>function</code>

<code>procedure</code>

<code>grant pri_type,...on [object_type] db_name.tbl_name to 'user'@'host' [identified by 'PASSWD']</code>

授权示例:<code>grant all privileges on mydb.* to 'zhenping'@'172.16.%.%' identified by 'MT8ddd';</code>

取消权限

取消授权语法格式<code>revoke pri_type,.... on db_name.tb_name from 'user'@'host';</code>

取消授权示例:<code>revoke all privileges on mydb.* from 'zhenping'@'172.16.%.%';</code>

查看用户权限

<code>show grants for 'user'@'host';</code>

让新授权的权限立即生效

<code>flush privileges;</code>

[原创]数据库基础知识详解

继续阅读

关于Gradle配置的小结

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method