linux|Mariadb数据库 mariadb|mysql|数据库

文章图片

Mariadb基础

关系模型：

二维关系：表
行：row, entry
列：column, attribution
索引：数据结构，辅助完成数据查找的；

SQL代码：

存储过程
存储函数
触发器
事件调度器

事务(Transaction):组织多个操作为一个整体，要么全部都执行，要么全部都不执行;

'回滚操作' ,rollback
一个存储系统是否支持事务，就看其是否满足ACID测试：
A：原子性；
C：一致性；
I：隔离性；
D：持久性；

数据库:数据集合:

表:为了满足范式设计要求，将一个数据集分拆为多个
约束: constranint,向数据表插入的数据要遵循的限制规则

主键:primary key,

一个或多个字段的集合,填入主键中的数据，必须不同于已存在的数据,不能为空,一个表只能有一个主键；

外键:foreign key,

一个表中某字段中能插入的数据,取决于另外一张表的主键中的数据

唯一键:unique key,

一个或多个字段的组合,填入唯一键中的数据,必须不同于已存在的数据,可以为空,一个表可以存在多个惟一键

检查性约束:check,取决于表达式的要求

索引:将表中的某一个或某些字段抽取出来,单独将其组织一个独特的数据结构中;

常见的索引类型:

树型 : BTree
hash : k/v

注意:索引有助于请读求,但不利于写请求

关系运算:

选择:挑选出符合条件的行;
投影:挑选出符合需要的列;
连接:将多张表关联起来;

数据抽象:

物理层: 决定数据的存储格式,即如何将数据组织成为物理文件
逻辑层: 描述DB存储什么数据,以及数据间存在什么样的关系
视图层: 描述DB中的部分数据;

关系模型分类:

关系模型
实体-关系模型
基于对象的关系模型
半结构化关系模型

MySQL：

C/S架构
S：mysqld, mysqld_safe, mysqld_multi
C：mysql, mysqladmin, mysqldump, ...
非客户端工具：工作于服务端主机，用于实现一些管理操作；

数据类型：

字符型：
定长字符型：
CHAR(#)
BINARY(#)
变长字符型：
VARCHAR(#)
VARBINARY(#)
内建数据类型：
ENUM
SET
数值型：
精确数值：
整型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
近似数值：
浮点型：
FLOAT
DOUBLE
日期时间：
DATE：日期型
TIME：时间型
DATETIME：日期时间型
YEAR(2)：
YEAR(4)：
TIMESTAMP：

MariaDB(mysql) :MariaDB Foundation - MariaDB.org

MariaDB的特性:

插件式存储引擎:存储管理器有多种实现版本,彼此间的功能和特性可能略有区别,用户可根据需要灵活选择

存储引擎也称为'表类型'

(1)更多的存储引擎
MyISAM :不支持事务
MyISAM -->Aria
InnoDB -->XtraDB:支持事务

(2)实现了诸多扩展和新特性
(3)提供了较多的测试组件
(4)truly only source;

MySQL的发行机制:
Enterprise: 提供了更丰富的功能
Community: 功能还可以,但是是免费的

安装和使用MariaDB:

安装方式:

(1)rpm包
(a)由OS的发行商提供
(b)程序官方提供
(2)源码包
(3)通用二进制格式的程序包

通用二进制格式安装MariaDB

(1)准备数据目录
以/mydata/data 目录为例
(2)安装配置mariadb

useradd -r mysql tar xvf mariadb-Version.tar.xz -C /usr/local cd /usr/local ln -sv mariadb-Version mysql cd /usr/local/mysql chown -R root:mysql ./* scripts/mysql_install_db --user=mysql --datadir=/mydata/data cp support-files/mysql.server /etc/init.d/mysqld chkconfig -add mysqld

(3)提供配置文件
ini格式的配置文件，各程序均可通过此配置文件获取配置信息
[program_name]

OS Vendor提供的mariadb rpm包安装的服务的配置文件查找次序
/etc/mysql/my.cnf -->/etc/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

通用二进制安装的服务的配置文件的查找次序
【linux|Mariadb数据库】/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

获取其读取次序的方法:

mysqld --verbose --help

cp support-files/my-large.cnf /etc/my.cnf

添加三个选项:

datadir=/mydata/data innodb_file_per_table=ON skip_name_resolve=ON

(4)启动服务

server mysqld start

关系：二维关系（行，列）
表、索引、视图、……
设计范式：
第一范式：字段是原子性的；
第二范式：存在可用的主键；
第三范式：任何字段都不应该依赖于其它表的非主属性；
约束：primary key, unique key, foreign key, check

MariaDB安装方式：

通用二进制格式包
源码编译安装：cmake

SQL：
数据库、表、索引、视图、存储过程、存储函数、触发器、事件调度器、用户和权限；

DDL，DML，DCL
DDL：CREATE，ALTER，DROP
DML：INSERT， DELETE，UPDATE，SELECT
DCL：GRANT，REVOKE

MariaDB程序的组成

C:client
mysql:CLI交互式客户端程序
mysqldump:备份工具
mysqladmin:管理工具
mysqlbinlog:查看二进制程序的日志
S:server
mysqld
mysqld_safe:建议运行服务端程序
mysqld_multi:多实例

三类套接字地址:

IPV4,3306/tcp
IPV6
Unix Sock:/var/lib/mysql/mysql.sock,/tmp/mysql.sock

C和S之间的通信: localhost,127.0.0.1

命令行交互式客户端程序:

mysql [OPTIONS] [database]

常用选项:
-uUSERNAME :用户名，默认为root
-hHOST :远程主机(即mysql服务器)地址，默认为localhost
-p[PASSWORD] :USERNAME所表示的用户的密码,默认为空

注意 :mysql的用户账号有两部分组成:'uUSERNAME'@'HOST'; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务

HOST的表示方式,支持使用通配符
% :匹配任意长度的任意字符
_ :匹配任意单个字符

-Ddb_name :连接到服务器端之后,设定期处指明的数据库为默认数据库
-e 'SQL COMMAND' :连接至服务器并让其执行此命令后直接返回

命令:

客户端命令:本地执行

mysql > help\u db_name :设定哪个库为默认数据库 \q :退出 \d CHAR :设定新的语句结束符 \g :语句结束标记 \G :语句结束标记,结果是竖排方式显示 \s :status ,显示状态 \! SHELL_COMMAND :执行shell命令 \. /path/to/some_sql_script :运行SQL脚本

服务端命令:通过mysql连接发往服务器执行并取回结果
DDL,DML,DCL
(1) 语句结束符；
(2) 建立了与某服务器有效通信连接；

注意:每个语句必须有语句结束符,结束符默认为 '; '

数据类型:

表:行和列
创建表:
定义表中的字段
定义列时需要选定合适的数据类型；
符合范式设计的要求；
定义约束关系

定义字段时,关键的一步即为确定其数据类型
用于确定:数据存储格式,能参与运算种类,可表示的有效的数据范围

字符型:字符集
码表:在字符和二进制数字之间建立映射关系
show character set; 查看字符集

字段类型

字符型:
定长字符型:
CHAR(#) :不区分字符大小写
BINARY(#) :区分字符大小写
变长字符型:
VARCHAR(#)
VARBINARY(#)
对象存储:
TEXT:
BLOB
内置类型:
SET
ENUM
数值型:
精确数值型:
INT(TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT) : 1,2,3,4,8 字节大小
近似数值型:
FLOAT
DOUBLE
日期时间型:
日期型 : DATE
时间型 : TIME
日期时间型 : DATETIME
时间戳 : TIMESTAMP
年份 : YEAR(2),YEAR(4)

数据类型有修饰符:

UNSIGNED : 无符号
NOT NULL : 非空
DEFAULT value : 默认值

服务器端命令:

DDL : 数据定义语言,主要用于管理数据库组件,例如表,索引,视图,用户,存储过程
CREATE,ALTER,DROP
DML : 数据操纵语言,主要用于管理表中的数据,实现数据的增删改查;
INSERT,DELETE,UPDATE,SELECT

获取命令帮助:

mysql> help KEYWORDHELP CREATE

SQL语句：

DDL：数据定义语言，主要用于管理库组件，例如数据库、表、索引、视图、用户、存储过程、存储函数、触发器、……
CREATE，ALTER，DROP
DML：数据操纵语言，主要用于管理表中数据，实现数据CRUD操作
DCL：管理授权
数据库管理

创建:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[DEFAULT] CHARACTER SET [=] charset_name[DEFAULT] COLLATE [=] collation_name

查看支持的所有字符集: show character set;
查看支持的所有排序规则: show collation;

create database nihao if not exists testdb;

修改:

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name

删除:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看:

SHOW DATABASES;

表管理

创建:

CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]

create_defination :
字段: col_name data_type
键:
PRIMARY KEY (col1,col2,...)
UNIQUE KEY (col1,col2,...)
索引:
KEY|INDEX [index_name] (col1,col2,...)
约束：
PRIMARY KEY
UNIQUE KEY
FOREIGN KEY
CHECK(expr)
索引：
{INDEX|KEY}
{FULLTEXT|SPATIAL}
table_options:
ENGINE=engine_name (存储引擎)
ENGINE=engine_name (存储引擎)

查看数据库支持的所有存储引擎类型:

show engines;

查看某表的存储引擎类型:

show tables status [like 'tbl_name']

CREATE TABLE students(id INT UNSIGNED NOT NULL,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('f','m')); CREATE TABLE students(id INT UNSIGNED NOT NULL,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('f','m'),PRIMARY KEY(id,name)); CREATE TABLE students(id INT UNSIGNED NOT NULL PRIMARY KEY,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('f','m'));

DESC students; #查看表信息 show tables; #查看数据包 DROP TABLE students; #删除表

修改:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

alter_specification:
字段:
添加: ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
alter table students add cless VARCHAR(100) NOT NULL; [after name] : 放到name后面
删除: DROP [COLUMN] col_name
alter table students drop class;
修改:(只有不改名字就用modify)
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
alter table students modify class varchar(100) after age;

键:
添加: ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1,col2,...)
alter table students add unique key (name);
删除:
主键: DROP PRIMARY KEY
外键: DROP FOREIGN KEY fk_symbol

索引:
添加: ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
alter table students add index class (class);
删除: DROP {INDEX|KEY} index_name
alter table students drop index name;
表选项
ENGINE[=] engine_name ; 表引擎
查看表上的索引信息:

show indexs from students;

删除:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...[RESTRICT | CASCADE]

索引管理：

索引是特殊的数据结构;
索引:要有索引名称

创建:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH]ON tbl_name (index_col_name,...)create index name_and_class on students (name,class);

删除:

DROP INDEX index_name ON tbl_namedrop index name_and_class on students;

查看:

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

标的引用方式:
tbl_name
db_name.tbl_name
第二种创建方式:
复制表结构
第三种创建方式:
复制表数据
DML: INSERT,DELETE,UPDATE,SELECT

INSERT INTO :

INSERT [INTO] tbl_name [(col1,...)] {values|value} (val1,val2,...),(...)insert into students values (1,'nihao',43,'xixi','m'); insert into students (id,name) values (2,'aa'),(3,'dd');

注意:
字符型:引号
数值型:一定不要引号

SELECT :

(1) SELECT * FROM tbl_name;
(2) SELECT col1,col2,... from tbl_name;
显示时,字段可以显示为别名;

col_name as col_alias

(3) SELECT col1,... from tbl_name where clause;
where clause :用于指明挑选条件
col_name 操作符 value;
age > 30;

操作符:
>,<,=,!=,<=,>=;
BGTWEEN...AND... #表示两者之间
LIKE 'PATTERN'
通配符:
% :任意长度的任意字符
_ :任意单个字符
RLIKE 'PATTERN'

正则表达式对字符串做模式匹配
IS NULL : 对应的值为空
IS NOT NULL :对应的值为非空

select * from students where id=1;

组合条件:
and
or
not
(4) SELECT col1,... from tbl_name [where clause] order by col_name,col_name[ASC|DESC]; ASC表示升序排序,DESC表示降序排序

DELETE:

DELETE FROM tbl_name [where where_condition] [order by ...] [limit row_count]

create table students (id int,name varchar(30),age int); for i in {1..100} ; do a=$[$RANDOM%100]; mysql -e "insert into stu.students values ($i,'stu$k',$a); " -proot ; doneselect * from stu.students; select * from stu.students order by age desc; delete from stu.students order by age desc limit 20; #从高到低排序并删除最多20行

(1) DELETE FROM tbl_name where where_condition
(2) DELETE FROM tbl_name [order by...][limit row_count]

UPDATE:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition][ORDER BY ...][LIMIT row_count]

update stu.students set age=age-5 order by id desc limit 10; select id,name,age from stu.students order by id desc;

用户账号及权限管理用户账号: 'username'@'host'
host : 此用户访问当前mysql服务器时,允许其通过哪些主机远程创建连接
表示方式: ip , 网络地址,主机名,通配符(%,_)

禁止检查主机名 : my.cnf

[mysqld]skip_name_resolve = ON

创建用户账号:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

删除用户账号:

DROP USER 'username'@'host' [, 'username'@'host'] ...

授权:
权限级别:管理权限,数据库,表,字段,存储例程

GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'username'@'host' [IDENTIFIED BY 'password'];

priv_type : ALL [PRIVILEGES]
db_name.tbl_name :
*.* : 所有库的所有表
db_name.* :
db_name.routine_name : 指定库上的存储过程或存储函数

[object_type]
TABLE
FUNCTION
PROCEDURE
查看指定用户所获得的权限:

show grants for 'username'@'host'; show grants for current_user; #查看自己的权限

回收权限:

revoke priv_type,... on db_name.tbl_name from 'username'@'host'; revoke insert on stu.students from 'stuuser'@'localhost'; flush privileges; #刷新权限

注意: MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中
(1) GRANT 或 REVOKE 命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表，权限修改会立即生效
(2) 其他方式实现的权限修改,想要生效,必须手动运行 FLUSH PRIVILEGES; 命令即可

加固mysql服务器,在安装完成后,运行 mysql_secure_installation 命令,根据提示操作.

图形管理组件: phpmyadmin
php-mbstring
php-mcrypt

phpmyadmin配置信息

config.inc.php

$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */openssl rand -base64 15 oieid9Wk6sEREEXxzcm5$cfg['blowfish_secret'] = 'oieid9Wk6sEREEXxzcm5'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */