Mariadb数据库

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

通用二进制安装的服务的配置文件的查找次序

/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 KEYWORD
 
HELP 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_name
 
drop 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 ;done
 
select * 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! */