数据库结构模型分类
1、层次模型
2、网状模型
3、关系模型
关系模型的组成部分
- 二维关系
- 表
- row
- column
- 索引
- index
- 视图
- view (只包含固定字段,不包含其他字段)
- 表
关系型数据库的常见组件:
数据库
: Database表
: table行
: row列
: column
索引
: index视图
: view用户
: user权限
: privilege存储过程
: procedure存储函数
: function触发器
: trigger事件调度器
: event schedule
DBA的组成部分
- SQL引擎
- 分析器
- 计划执行器
- 优化器
- 操作求解器
- 存储引擎
- 文件和存储接口
- 缓冲管理器
- 磁盘空间管理器
- 恢复管理器
- 事务管理器
- 锁管理器
SQL接口(structured query Language)
类似于OS的shell接口,操作数据库的数据接口,也提供了编程功能
SQL接口语言的标准(其由ANSL组织定义)
- SQL86
- SQL89
- SQL92
- SQL99
- SQL03
SQL接口的语言分类
- DDL:(Data Defined Language)
- create
- alter
- drop
- DML:(Data Manapulating Language)
- insert
- delete
- update
- select
- DCL: (Data Control Language)
- grant
- revoke
- DDL:(Data Defined Language)
DB是否支持事务的测试标准
ACID
- A : 原子性 --> 不可分割的整体
- C : 一致性 --> 数据的变化是一致性的
- I : 隔离性 --> 事务彼此之间是隔离的
- D : 持久性 --> 只要一个事务完成,它都是持久完成的。
事务是将组织多个操作为一个整体,要么全部执行,要全部不执行。其实现机制为:
- 回滚机制
- 事务机制
RDBMS设计范式基础概念
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。目前关系数据库有六种范式:第一范式(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
- 程序接口
- CLI
- GUI
- 应用编程接口
- ODBC : Open Database Connection(开放式数据互联)
- 程序接口
MYSQL的工作模型
- 单进程多线程
- 用户连接通过线程实现
- 一个线程池可以定义mysql的并发连接
- 处理用户连接的叫连接线程
- 数据字典:数据一切的元数据信息,依赖mysql库来存储
数据库的基础概念
- 1)
约束
: Constraint主键
: 一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行,且必须提供数据,即NOT NULL
唯一键
: 一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行,允许为null
外键
: 一个表中的某字段可填入数据取决于另一个表中的主键已有的数据。检查性约束
: 表达式约束,取决于表达式的要求
- 2)
索引
: 将表中的一个或多个字段中的数据复制一份另存,并且这些字段需要按特定的次序排序存储,常见的索引类型:树形索引
(MariaDB默认为Btree索引)bash索引
索引有利于读请求,但不得写请求
- 3)
关系运算
选择
: 挑选出符合条件的行(部分)投影
: 挑选出需要的字段连接
: 将多张表关联起来- 内连接
- 外连接
- 左外连接
- 右外连接
- 自连接
- 4)
数据抽象
:物理层
: 决定数据的存储格式,即RDBMS 在磁盘上如何组织文件逻辑层
: 描述DB存储什么数据,以及数据间存在什么样的关系视图层
: 描述DB中的部分数据
- 5) 关系模型的分类
关系模型
实体关系模型
基于对象的关系模型
半结构化的关系模型
- XML格式就是一种半结构化数据
常见的RDBMS数据库
- MySQL
- MariaDB
- PostgreSQL(pgsql)
- Oracle
- MSSQL
=============
MariaDB的特性
- 插件式存储引擎(注:存储引擎也称之为"
表类型
")MYISAM
--> Aria- 不支持事务
INNODB
--> XtraDB- 支持事务
- 诸多扩展和新特性
- 提供了更多的测试组件
- truly open source
Mariadb的安装
通用二进制格式安装过程
a. 准备数据目录
以/mydata/data为例;
b. 配置mariadb
# groupadd -r -g 306 mysql
# useradd -r -g 306 -u 306 mysql
# tar xf mariadb-VERSION.tar.xz -C /usr/local
# ln -sv mariadb-version mysql
# cd /usr/local/mysql
# chown -R root:mysql ./*
# scripts/mysql_install_db --datadir=/mydata/data --user=mysql
# cp supper-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
c. 准备配置文件
配置格式:类ini格式,为各程序均通过单个配置文件提供配置信息;
[prog_name]
能用二进制格式安装,配置文件查找次序:
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
OS提供的mariadb rpm包安装的配置文件查找次序:
/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra=/PATH/TO/conf_file --> ~/my.cnf
以上两者越靠后就是最后生效的.
# mkdir /etc/mysql
# cp support-files/my-large.cnf /etc/mysql/my.cnf
添加三个选项:
[mysqld]
datadir = /mydata/data
innodb_file_per_table = on
skip_name_resolve = on
MariDB程序的组成
Client
mysql
: CLI交互式客户端程序mysqldump
: 备份工具mysqladmin
: 管理工具mysqlbinlog
: 查看二进制日志工具
Server
mysqld
: 服务端进程mysqld_safe
: 服务端进程,默认也是运行的此进程mysqld_multi
: 服务端进程, 多实例mysql_upgrade
: 升级工具
服务端监听的两种socket地址
ip socket
- 监听在3306/tcp,支持远程通信
unix socket
- 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock),仅支持本地通信,通信主机为localhost,127.0.0.1都基于unix socket文件通信
命令行交互式客户端程序---mysql工具
options
-uUSERNAME
: 用户名,默认为root-hHOST
: 服务器主机,默认为localhost-pPASSWD
: 用户的密码dDB_NAME
: 连接到服务端之后,指明默认数据库-e 'SCRIPT'
: 连接至MYSQL运行某命令后,直接退出,并返回结果
````
mysql -uroot -h127.0.0.1 -pmagedu -e 'show databases;'
注意: mysql的用户帐号由两部分组成,'username'@'hostname',其中host用于限制此用户可通过哪些主机连接当前的MSYQL服务器
支持通配符
:%
: 匹配任意长度的任意字符172.16.%.%
_
: 匹配任意单个字符
内置命令
\u DB_NAME
: 设定哪个库为默认数据库\q
: 退出\d CHAR
: 设定新的语句结束符\g
: 语句通用结束标记\G
: 语句结束标记,但以竖排方式显示\s
: 返回客户端与服务端的连接状态\c
: 取消命令运行
通过mysql协议发往服务器执行并取回结果,每个命令都必须有结束符,默认为";",示例如下:
for i in {1..100};do AGE=$[$RANDOM%100];mysql -uroot -pM8T9cw -e "insert mydb.student(id,name,age) value ($i,\"stu$i\",$AGE);"; done
获取命令帮助
help
SQL中的数据类型
字符型
- 定长字符型 : (最多255个字符)
CHAR(#)
: 不区分字符大小写BINARY(#)
: 区分字符大小写
- 可变长字符型 :
VARCHAR(#)
: 不区分字符大小写,需要在字符长度加1,最多65536个字符VARBINARY(#)
: 区分字符大小写,需要在字符长度加1,最多65536个字符TEXT
: 可存文本(2^32)个字符,只能存纯文本,不区分大小写TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
BLOB
: 文本(2^32)个字符,可以存储图片
- 定长字符型 : (最多255个字符)
内置类型
SET
: 集合ENUM
: 枚举
数值型
精确数值型
INT
TINYINT
: 一个字节SMALLINT
: 二个字节MEDINUMINT
: 三个字节INT
: 四个字节BIGINT
: 八个字节
- 近似数据型
FLOAT
: 单精度DOBULE
: 双精度
日期时间型
DATE
: 日期型TIME
: 时间型DATETIME
: 日期时间型TIMESTAMP
: 时间戳(从过去到现在经过的秒数)YEAR(2)
: 2位年数YEAR(4)
: 4位年数
数据类型修饰符
- 所有类型都适用:
NOT NULL
: 非空DEFAULT value
: 默认值
- 数值型适用
AUTO_INCREMENT
: 自增长UNSIGNED
: 无符号,通常用于Int后面,进行修饰为正整数
- 字段修饰符
PRIMARY KEY
: 主键定义UNIQUE KEY
: 唯一键定义
- 所有类型都适用:
==========
数据库操作语言
数据库操作
使用格式
create database | schema [if not exists]'DB_NAME';
drop database | schema 'DB_NAME';
- 查看数据库
show databases;
- 创建数据库
create database mydb;
- 删除数据库
drop database mydb;
- 查看支持的所有字符集
show character set;
- 查看支持的所有排序规则
show collation;
- 修改数据库的默认字符集
alter database testdb character set utf32;
- 修改数据库的默认字符排序规则
alter database testdb collate utf32_sinhala_ci;
表操作
使用格式
create table [if not exists] tb_name (col1 datatype 修饰符, col2 datatype 修饰符) engine=' '
- 查看所有的引擎(被支持的)
show engines
- 查看所有数据库中的表
show tables;
show tables from mysql;
- 查看表结构
desc students;
- 创建表
create table students(id int unsigned not null primary key, name varchar(30) not null, age tinyint unsigned not null,gender enum('F','M'))
- 向表中添加字段
alter table students add second_name char(30);
- 向表中删除字段
alter table students drop second_name;
- 对表添加主键
alter table students2 add primary key (id);
- 对表删除主键
alter table students2 drop primary key;
- 对表添加索引
alter table students2 add index name (name);
- 对表删除索引
alter table students2 drop index name;
drop index name on students2;
- 对表添加唯一键
alter table students drop second_name;
- 对表删除唯一键
alter table students drop index name;
修改字段字义属性
alter table students modify name char(20);
alter table students2 change name new_name char(20) after id;
alter table students2 change name new_name char(20);
modify : 只可以修改字段定义的属性
change : 可以修改字段名称和定义的字段属性
- 查看表状态
show table status like 'students2'\G
- 修改表引擎
alter table students2 engine[=]myisam;
- 查看表的字段描述
desc students;
- 查看库中的所有表
show tables
show tables from mysql;
- 删除表
drop table students2;
向表中插入数据
insert weizi value(1,'zhen',30,'F');
insert weizi values(3,'wei',30,'F'),(4,'ping',31,'F');
insert weizi (id,name) value (5,"weizi");
删除表中的数据
delete from weizi where name="zhen";
delete from weizi where age is null;
delete from weizi where age > 30;
delete from weizi where id >=50 and age <=20;
delete from weizi order by age asc limit 5;
修改表中的字段值
update weizi set age=35 where id=3 and age=30;
update weizi set age=age-5 where age=35;
update weizi age=age-age;
update weizi set age=age-5 where order by id desc limit 10;
update weizi set age=age-15 where name not like 'stu%';
Select操作语句
- 使用格式
select col1,col2,....from tb1_name [where clause] [order y 'col_name'] [limit [m,]n]
- 字段表示法
*
: 表示所有字段as
: 字段别名, col1 as alias1
- where clause
操作符
:==
<
>
<=
>=
!=
between....and...
条件逻辑操作
and
or
not
模糊匹配
like
%
_
rlike 'pattern'
: 基于正则表达式匹配is null
is no null
- 排序
desc
: 降序asc
: 升序
Select示例
select name,age from students where age >30 and age<80;
select name,age from students where age between 30 and 80;
select name from students where name like '%ang%';
select name from students where name rlike '^.*ang.*$;
select name,age from students where age is null;
select name,age form students where age is not null;
select id,name from students order by name;
select id,name from students order by name desc;
权限及授权管理
- 用户表示方法:
username@host
- 管理权限的分类
管理权限
数据库
表
字段
存储例程
授权:
- 授权语法格式
grant pri_type,...on [object_type] db_name.tbl_name to 'user'@'host' [identified by 'PASSWD']
pri_type
all privileges
: 表示全部权限
db_name.tbl_name
的表示方法:*.*
: 所有库的所有表db_name.*
: 指定库的所有表db_name.tbl_name
: 指定库的特定表db_name.routine_name
: 指定库上的存储过程或存储函数
object_type
table
function
procedure
- 授权示例:
grant all privileges on mydb.* to 'zhenping'@'172.16.%.%' identified by 'MT8ddd';
取消权限
- 取消授权语法格式
revoke pri_type,.... on db_name.tb_name from 'user'@'host';
- 取消授权示例:
revoke all privileges on mydb.* from 'zhenping'@'172.16.%.%';
查看用户权限
show grants for 'user'@'host';
让新授权的权限立即生效
flush privileges;
1 Mariadb服务进程启动时会读取mysql库中的所有授权表至内存中;
2 grant和revoke等执行权限操作时会保存于表中,mariadb的服务进程会自动重读授权表
3 对于不能够或不能及时重读授权表,可手动让服务进程重启授权表,使用flush privileges
作者:魏镇坪
链接:http://www.jianshu.com/p/e59afa955a2d
來源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。