数据库基础
一、数据模型
数据库模型图
1、层次模型、
2、网状模型、
3、关系模型
二维关系：
-
表：就是一个关系及属性的描述，如：学生（学好，姓名，性别，班级）
行：row, entity 列：colume, attribution
-
索引：数据结构，辅助完成数据查找；
-
视图：伪表；是一种外模式，是建立在基础表之上的数据查询
SQL接口：
用来连接到数据库系统上，用来操作的一个shell，也提供编程功能，使之能操作数据；Structured Query language（结构化的查询语言） ANSI：SQL-标准，SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ...
SQL代码：
存储例程：storage routing 存储过程：storage procedure 存储函数：storage function 触发器：trigger 事件调度器：event scheduler 授权系统：把有线多个资源让有限多个用户去使用 用户和权限： 用户：用户名和密码；在mysql中用户名是用 name@访问Ip 来定义的。 权限：管理类（负责系统的启动和停止）、数据库（可以操作数据库上的所有组件）、表级别的权限、字段级别的权限
DMBS：DataBase Management System（数据库）
RDBMS：Relational DBMS（关系型数据库）
Mysql：单进程，多线程模型 用户连接：通过线程来实现； 线程池：用来定义mysql的线程响应个数的
数据库模型图
事务机制：一个由单个或多个SQL语句组成的不可分割原子操作，Transaction；要么全执行要么全不执行
可以通过“回滚”操作 判断一个存储系统是否支持事务，测试标准 ACID: A: 原子性； C：一致性；这个加上了那个就得减去，总体不变 I：隔离性；事务于事务之间是隔离的（操作同一个事务要各自独立） D：持久性；操作的结果必须要持久保存
对于数据库必须具有三个组件
-
逻辑组件：所能看到并进行操作的组件
-
中间层（映射层）：使操作的逻辑组件转换成为物理组件 DBA 管理的组件
-
物理组件：数据库系统存储到硬盘上所需要的组件
二、RDMBS基础概念
1、数据库的设计要遵循限定：
补充材料：RDMBS设计范式基础概念
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。 (1) 第一范式（1NF） 所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。 说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。 (2) 第二范式(2NF) 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。 (3) 第三范式（3NF） 第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
一共有5种范式：一般满足第三个范式就好了，后面的
太完美反而不好
（1）表
为了满足范式设计要求，将一个数据拆分为多个
（2）数据约束：
约束：constraint，向数据表提供的数据要遵守的限制；
-
主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT NULL;
一个表只能存在一个
-
惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL；
一个表可以存在多个
-
外键：一个表中的某字段可填入数据取决于另一个表的主键已有的数据；
-
检查性约束：取决于表达式的要求；
（3）索引：
将表中的一个或多个字段中的数据复制一份另存，并且此些需要按特定次序排序存储；存在的目的是为了加速查询操作，但会减慢写入操作
索引类型
-
树状结构
-
hash结构
（4）关系运算：
选择：挑选出符合条件的行（部分）； 投影：挑选出需要的字段；选出列 连接：
（5）数据抽象：
物理层：决定数据的存储格式，即RDBMS在磁盘上如何组织文件； 逻辑层：描述DB存储什么数据，以及数据间存在什么样的关系； 视图层：描述DB中的部分数据；
（6）关系模型的分类：
关系模型 实体-关系模型 基于对象的关系模型 半结构化的关系模型
三、mariadb
1.MariaDB的特性：
插件式存储引擎：存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择； 存储引擎也称之为“表类型”； (1) 更多的存储的存储引擎 MyISAM --> Aria, InnoDB --> XtraDB (2) 诸多扩展和新特性 (3) 提供了较多测试组件 (4) truly open source MySQL is a multi-user, multi-threaded SQL database server. C/S： Server： mysqld, mysqld_safe, mysqld_multi Client：mysql
MySQL:的发行机制
Community Edtion 社区版 Enterprise Edtion 企业版
2、mariadb的安装及配置相关
MySQL
-
安装
CentOS 6: mysql-server, mysql
-
额外添加的配置项：
[mysqld] ... skip_name_resolve innodb_file_per_table=ON
注意这里在 skip_name 这一项不能等于on 他不支持这里是 mysql5.x版本
MariaDB：
-
安装
CentOS 7: mariadb-server, mariadb
配置文件：/etc/my.cnf, /etc/my.cnf.d/*.cnf
-
额外添加的配置项：
[mysqld] skip_name_resolve = ON #表示跳过主机名解析 innodb_file_per_table = ON #每表单独的表空间
Mariadb编译安装：
-
1、目前只支持Centos 7.x 因为其依赖的 glibc 包要求版本大于2.14
-
2、要求压缩包要展开至 /usr/local 目录并且叫 mysql
-
3、展开后我们不建议把这个目录进行改名而是创建连接叫 mysql
步骤1：
1、从官方下载压缩包 并解压到/usr/local目录下 2、ln -sv mariadb*（这个是展开的目录） mysql 对此目录做软连接 3、创建一个mysql用户（组）、确保是系统用户 groupadd -r -g 306 mysql 4、把 mysql里面的所有文件属主属组改为root：mysql 5、准备数据存储目录 （这里只做演示 就指定 /mydata/data），并且改属主属组为 mysql 日志文件为 6、创建/etc/mysql 并把当前 support-files/my-large.cnf.sh 复制到其内部更名为 my.cnf当配置文件样本 7、改配置，注意是在 [mysqld] 段中加 [mysqld] datadir = /mydata/data skip_name_resolve = ON innodb_file_per_table=ON
8、centos 6复制 support-files/mysql.server.sh到 /etc/ic.d/init.d/ 改名叫mysqld 加上执行权限就好了 7也行。 9.添加 mysqld 到启动项 chkconfig --add mysqld 10、执行scripts下的脚本生成元数据文件 ./mysql_install_db.sh --user=mysql --datadir=/mydata/data
2.简化版步骤
安装MariaDB： 预制的包管理器格式的程序包： rpm包： os vendor：mariadb-devel MariaDB官方 通用二进制格式的程序包： 源码包编译：项目构建工具为cmake, 而非流行的make； 通用二进制格式包的安装配置步骤： # useradd -r mysql # tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local/ # cd /usr/local/ # ln -sv mariadb-VERSION-linux-x86_64 mysql # cd mysql # chown -R root:mysql ./* # mkdir -pv /mydata/data # chown -R mysql.mysql /mydata/data # scripts/mysql_install_db --user=mysql --datadir=/mydata/data --skip_name_resolve # cp support-files/my-large.cnf /etc/my.cnf
配置文件查找次序：
—->/etc/my.cnf –> /etc/mysql/my.cnf –> –default-extra-file=/PATH/TO/CONF_FILE –> ~/.my.cnf
3、注意：
默认的管理员用户为：root，密码为空；首次安装后建议使用 mysql_secure_installation 命令进行安全设定；
mariadb的基础应用
数据类型：
作用：存储格式、数据范围、所能参与的运算、排序方式
-
字符型：
定长字符型：CHAR(#)不区分字符大小写；#表示储存多少个，BIARNY(#) 变长字符型：VARCHAR(#)不区分字符大小写，VARBINARY(#) 对象存储：（基于指针的存储，用来存储大数据） TEXT BLOB 内建类型：ENUM（集合），SET（枚举）
-
数值型：
精确数值型： INT：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT 近似数值型： FLOAT DOUBLE
-
日期时间型：
DATE TIME DATETIME TIMESTAMP YEAR
-
修饰符：
-
NOT NULL ：非空
-
DEFAULT ：默认值
-
类型符：
-
UNSIGNED：无符号
MariaDB程序组成：
-
C：Client
mysql mysqladmin mysqldump mysqlbinlog ...
-
S：Server
mysqld mysqld_safe 建议运行的程序，默认就是运行这个程序，线程安全方式 mysqld_mult 多实例；启动多个mysql
-
管理工具程序：
myisampack myisamchk ...
三类套接字地址：
IPv4:PORT IPv6:PORT Unix_Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock（源码安装）
一、命令行客户端程序mysql
mysql [OPTIONS] [database] 常用选项： -uUSERNAME 默认为root -hHOST ：远程主句（即mysql服务器）地址，默认为localhost -pPASSWORD 注意：mysql的用户账号由两部分组成：’USERNAME‘@'HOST';其中HOST用于限制此用户可以通过那些远程主连接当前mysql服务； HOST的表示方式，支持使用通配符： %表示任意字符 _：匹配任意单个字符 -Ddb_name：连接到服务器端之后，设定其处指明的数据库为默认数据库，也可以进入库后用 use 指明 -e 'STATEMENT；' 表示不再进入交互式模式，直接输入一条命令后运行并退出 -S sock_file_path -P port
在mysql命令提示符下的命令：
-
客户端命令：在客户端执行，本地执行。
mysql> help \u db_name:设定那个库为默认库 \?：获取可用的命令帮助； \q：退出客户端程序； \d CHAR：自定义语句结束符； \g：语句结束标记；这个是指定结束标记 \G：语句结束标记，竖排显示结果； \! SHELL_CMD：运行shell命令； \s：当前连接及服务器相关的状态信息； \. \c 表示这条语句不想运行 相当于shell ctrl+c
二 、服务端命令：
SQL语句，发往服务端运行，并取回结果；需要显式的语句结束符 ‘；’
-
DDL：数据定义语言，主要用于数据库组件，例如数据库、表、索引、视图、触发器、事件调度器、存储过程、存储函数；
CREATE， ALTER， DROP
-
DML：数据操纵语言，CRUD操作，主要用于操作表中的数据；
INSERT，DELETE，UPDATE，SELECT
-
DCL：数据控制语言
GRANT, REVOKE
-
授权命令：
GRANT ALL [PRIVILEGES] ON db.tbl TO 'username'@'host' IDENTIFIED BY 'password'; db：数据库的名字，可以使用*通配； tbl：表的名称，可以使用*通配； mydb.*, *.*, mydb.tbl1 FLUSH PRIVILEGES
获取帮助：
mysql> help KEYWORD mysql> help contents
(一)、数据库管理（数据库的名字区分字符大小写）：
-
创建
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS（不存在时创建）] db_name
[DEFAULT] CHARACTER SET [=] charset_name 设定默认的字符集 [DEFAULT] COLLATE [=] collation_name （设定默认的排序规则）
-
修改
ALTER {DATABASE | SCHEMA} [db_name]
CHARACTER SET [=] charset_name COLLATE [=] collation_name ‘
-
删除
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
相关命令：
SHOW CHARACTER SET SHOW COLLATION SHOW CREATE DATABASE db_name
（二）、表管理
引用方式：
tbl_name db_name.tbl_name 上一种必须先要用 use 先切换到那个数据库中才能用
1、表创建：之 一
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,…) [table_options]
create_definition:由逗号分隔的列表
字段定义：
column_name column_defination
约束定义：
PRIMARY KEY(col1[,col2, ....]) 主键
UNIQUE KEY ：惟一键
FOREIGN KEY ：外键约束
CHECK(expr) ：检查约束
索引定义：
{INDEX|KEY}
{FULLTEXT|SPATIAL}
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
table_option:
ENGINE [=] engine_name
-
查看数据库支持的存储引擎种类：
SHOW ENGINES;
-
查看表状态信息：
SHOW TABLE STATUS [WHERE CLAUSE] [LIKE CLAUSE]
第二种表创建方式：复制表结构
第三种表创建方式：复制表数据
2、表修改：
ALTER TABLE tbl_name [alter_specification [, alter_specification] …]
alter_specification:
-
(1) 表选项
ENGINE=engine_name 改引擎 ...
-
(a) 字段
-
添加：
ALTER TABLE tbl_name ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
-
删除：
ALTER TABLE tbl_name DROP [COLUMN] col_name
-
修改：
CHANGE（改名和定义）：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] MODIFY（只改定义）：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
-
(b) 键
-
添加
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...) -
删除
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name；
-
c、索引
-
添加
ADD INDEX(col1, col2, ...);
-
删除
DROP INDEX index_name；
查看表上的索引信息：
SHOW INDEXES FROM tbl_name；
查看表创建语句：
SHOW CREATE TABLE tbl_name
2、表删除：
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] …
（三）索引管理：
索引是特殊的数据结构
单独管理索引时，要有索引名称！
创建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (字段1上、字段2上、…)
默认的索引类型是btree索引，也有HASH但不能用
index_col_name: col_name [(length)] [ASC | DESC]
删除：
DROP INDEX index_name ON tbl_name
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
DML语句：
INSERT，SELECT，DELETE，UPDATE
INSERT：
INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUE} ({expr | DEFAULT},…),(…),…
注意：字符型数据要用引号 数值型数据不能用引号
查找所有的表中内容，生产工作中尽量不要用，相当于对库进行了一次攻击
–
–
–
–
SELECT：
-
(1) SELECT * FROM tbl_name; 查看表中的所有数据
-
(2) SELECT col1, col2, … FROM tbl_name;
字段可以显示为别名：col1 AS ALIAS
-
(3) SELECT col1, col2, … FROM tbl_name WHERE CLUASE;
WHERE expr：布尔表达式； col_name OPERATOR value|col_name; 操作符： >, <, <=, >=, =, != BETWEEN ... AND ... LIKE 'PATTERN'： 通配符： _：匹配任意单个字符； %：任意长度的任意字符； RLIKE 'PATTERN':可以使用正则表达式模式匹配 IN(list) 组合条件： and, or, not IS NULL IS NOT NULL
-
(4)查询以后做排序
SELECT col1, … FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2, … [ASC|DESC]
ASC：升序
DESC：降序
DELETE：删除
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY] [LIMIT row_count]
（1）、DELETE FROM tbl_name [WHERE where_condition]
（2）、DELETE FROM tbl_name [ORDER BY] [LIMIT row_count]
UPDATE：更新数据
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] … [WHERE where_condition]
—
图形管理工具：
phpMyAdmin Navicat mysql-front SQLyog ToadForMySQL
用户账号及权限管理;
用户账号：'username'@'host'
host:此用户访问当前mysql服务时，允许通过那些主机远程创建连接
表示方式：IP，网络地址、主机名、通配符（%和_）;
-
禁止检查主机名：my.cnf
[mysqld]
skip_name_resolve=ON
创建用户账号：
CREATE USER 'username'@'host' [IDENTIFIED BY 'passwd'];
再更新下数据库
FLUSH PRIVILEGES;
注意这样创建的用户没有写入权限，只有读取权限，无法查看元数据库
删除用户账号
DPOP USER 'username'@'host' [,'username'@'host']
授权：
级别：管理权限、数据库、表、字段、存储历程。
GRANT priv_type ON [object_type] db_name.tbl_name TO 'username'@'host' [IDENTIFIED BY 'passwd'];
priv_type: ALL [PRIVILEGES] db_name.tb_name: *.*: 所有库的所表； db_name.*: 指定库的所有表； db_name.tb_name: 指定库的指定表； db_name.routine_name：指定库的存储例程； object_type: TABLE | FUNCTION | PROCEDURE
-
查看指定用户获得的授权：
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER;
查看当前用户的权限
-
回收授权：
REVOKE priv_type, … ON db_name.tb_name FROM 'user'@'host';
注意：MariaDB服务进程启动时会读取mysql库中的所有授权表至内存中； (1) GRANT或REVOKE等执行权限操作会保存于表中，MariaDB的服务进程会自动重读授权表； (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表： mysql> FLUSH PRIVILEGES;
mysql刚装上后要用 mysql_secure_installation 设定一下安全权限
**重新初始化数据库命令 ：mysql_install_db –user=mysql –database=/path/to/newpath
**
在修改了数据库数据保存默认位置时使用
编译安装mariadb
安装MariaDB：
预制的包管理器格式的程序包： rpm包： os vendor：mariadb-devel MariaDB官方 通用二进制格式的程序包： 源码包编译：项目构建工具为cmake, 而非流行的make； 通用二进制格式包的安装配置步骤： # useradd -r mysql # tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local/ # cd /usr/local/ # ln -sv mariadb-VERSION-linux-x86_64 mysql # cd mysql # chown -R root:mysql ./* # mkdir -pv /mydata/data # chown -R mysql.mysql /mydata/data # scripts/mysql_install_db --user=mysql --datadir=/mydata/data --skip_name_resolve # cp support-files/my-large.cnf /etc/my.cnf [mysqld] ....... datadir = /mydata/data skip_name_resolve = ON innodb_file_per_table = ON # cp support-files/mysql.server /etc/rc.d/init.d/mysqld # chkconfig --add mysqld
原创文章，作者：qzx，如若转载，请注明出处：http://www.178linux.com/52836
