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):

www.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

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! */

类似文章