mariadb(mysql):
数据管理模型:层次模型、网状模型、关系模型
数据分类:结构化数据、半结构化数据、非结构化数据
关系模型:
数据库:一个方案、一个项目
二维关系:
表:row, column
索引:index
视图:view
SQL接口:Structured Query Language
类似于OS的shell接口;也提供编程功能;
ANSI: SQL标准,SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, …
PL-SQL,T-SQL,…
DDL:Data Defined Language
CREATE, ALTER, DROP、SHOW
DML: Data Manapulating Language
INSERT, DELETE, UPDATE, SELECT
编程接口:选择、循环;
SQL代码:
存储过程:procedure
存储函数:function
触发器:trigger
事件调度器:event scheduler
例程:routine
用户和权限:
用户:用户名和密码;
权限:管理类、程序类、数据库、表、字段
DBMS:DataBase Management System
RDBMS:Relational
MySQL:单进程,多线程
插件式存储引擎:存储引擎也称为表类型;
用户连接:通过线程来实现;
线程池:
AliSQL:MySQL的衍生版;
MariaDB:
PostgreSQL:PostgreSQL is an advanced Object-Relational database management system (DBMS).
Sqlite:SQLite is a C library that implements an SQL database engine. A large subset of SQL92 is supported. A complete database is stored in a single disk file. The API is designed for convenience and ease of use.
事务(Transaction)
组织多个操作为一个整体,要么全部都成功执行,要么失败回滚;
“回滚”, rollback
一个存储系统是否支持事务,测试标准:
ACID:
A:原子性atomicity;
C:一致性consistency;
I:隔离性isolation;
D:持久性durability;
数据库:数据集合
表:为了满足范式设计要求,将一个数据集分拆为多个;
- 约束:constraint,向数据表插入的数据要遵守的限制规则;
主键:一个或多个字段的组合,填入主键中的数据,必须不同于已存在的数据;而且不能为空;一个表只能有一个Primary KEY;
外键:一个表中某字段中能插入的数据,取决于另外一张表的主键中的数据;
惟一键:一个或多个字段的组合,填入惟一键中的数据,必须不同于已存在的数据;可以为空;一个表可存在多个Unique Key;
检查性约束:取决于表达式的要求; - 索引:将表中的某一个或某些字段抽取出来,单独将其组织一个独特的数据结构中;
常用的索引类型:
B+ Tree:Balance Tree
hash:注意:有助于读请求,但不利于写请求; - 关系运算:
选择:挑选出符合条件的行;
投影:挑选出符合需要的列;
连接:将多张表关联起来; - 数据抽象:
物理层:决定数据的存储格式,即如何将数据组织成为物理文件;
逻辑层:描述DB存储什么数据,以及数据间存在什么样的关系;
视图层:描述DB中的部分数据; - 关系模型的分类:
关系模型
实体-关系模型
基于对象的关系模型
半结构化关系模型MariaDB(mysql):
可用版本:
MariaDB
Percona-Server
AliSQL
TiDB
MariaDB: mariadb.org
MariaDB的特性:
插件式存储引擎:存储管理器有多种实现版本,彼此间的功能和特性可能略有区别;用户可根据需要灵活选择;
存储引擎也称为“表类型”;
- 更多的存储引擎;
MyISAM:不支持事务,表级锁,崩溃后不保证安全恢复;
MyISAM —> Aria
InnoDB —> XtraDB :支持事务,行级锁,外键,热备; - 诸多扩展和新特性;
- 提供了较多的测试组件;
-
安装和使用MariaDB:
安装方式:
rpm包;
(a) 由OS的发行商提供;
(b) 程序官方提供;- 源码包;
- 通用二进制格式的程序包;
MariaDB程序的组成:C/S
- C:Client —> mysql protocol —> Server
mysql:CLI交互式客户端程序;
mysqldump:备份工具;
mysqladmin:管理工具;
mysqlbinlog:
… - S:Server
mysqld
mysqld_safe:建议运行服务端程序;
mysqld_multi:多实例;三类套接字地址:
IPv4|IPv6, 3306/tcp
Unix Sock:/var/lib/mysql/mysql.sock, /tmp/mysql.sock
C <—> S: localhost, 127.0.0.1 - 配置文件:ini风格,用一个文件为多个程序提供配置;
[mysql]
[mysqld]
[mysqld_safe]
[server]
[client]
[mysqldump]
…
mysql的各类程序启动都读取不止一个配置文件,按顺序读取,且最后读取的为最终生效;
[root@localhost ~]# my_print_defaults
...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
...
命令行交互式客户端程序:mysql
mysql [OPTIONS] [database]
常用选项:
-u, —user=username:用户名,默认为root;
-h, —host=hostname:远程主机(即mysql服务器)地址,默认为localhost; 客户端连接服务端,服务器会反解客户的IP为主机名,关闭此功能(skipname_resolve=ON);
-p, —password[=PASSWORD]:USERNAME所表示的用户的密码; 默认为空;
注意:mysql的用户账号由两部分组成:’USERNAME’@’HOST’; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务;
HOST的表示方式,支持使用通配符:
%:匹配任意长度的任意字符;
172.16.%.%, 172.16.0.0/16
:匹配任意单个字符;
-P, —port=#:mysql服务器监听的端口;默认为3306/tcp;
-S, —socket=/PATH/TO/mysql.sock:套按字文件路径;
-D, —database=DB_name:连接到服务器端之后,设定其处指明的数据库为默认数据库;
-e, —execute=’SQL STATEMENT’:连接至服务器并让其执行此命令后直接返回;
命令:
- 客户端命令:本地执行
mysql> help
\u db_name:设定哪个库为默认数据库
\q:退出;
\d CHAR:设定新的语句结束符;
\g:语句结束标记;
\G:语句结束标记,结果竖排方式显式;
\s:获取服务器状态
!: 执行shell命令;
.: 装载并运行sql脚本; 服务端命令:通过mysql连接发往服务器执行并取回结果(SQL语句);
DDL, DML, DCL注意:每个语句必须有语句结束符,默认为分号(;)表:行和列
创建表:定义表中的字段; 定义字段时,关键的一步即为确定其数据类型; 用于确定:数据存储格式、能参与运算种类、可表示的有效的数据范围;- 字符型:字符集
码表:在字符和二进制数字之间建立映射关系; mysql> SHOW CHARACTER SET; mysql> SHOW COLLATION;- 字符型:
定长字符型:
CHAR(#):不区分字符大小写
BINARY(#):区分字符大小写
变长字符型:
VARCHAR(#):多占一个或两个字符空间;
VARBINARY(#):
对象存储:
TEXT:不区分字符大小写;TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT
BLOB:Binary Large OBject,区分字符大小写;TINYBLOB,SMALLBLOB,MEDIUMBLOB,BLOB,LONGBLOB;
内置类型:
SET
ENUM
- 字符型:
- 数值型:
精确数值型:
INT(TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT)
UNSIGNED
DECIMAL
近似数值型:
FLOAT
DOBULE- 日期时间型:
日期型:DATE
时间型:TIME
日期时间型:DATETIME
时间戳:TIMESTAMP
年份:YEAR(2), YEAR(4)
- 日期时间型:
字段数据修饰符:
NOT NULL:非空;
AUTO_INCREMENT:自动增长;
NULL:
DEFAULT value:默认值;
PRIMARY KEY:主键;
UNIQUE KEY:惟一键;服务器端命令:
DDL:数据定义语言,主要用于管理数据库组件,例如数据库、表、索引、视图、用户、存储过程
CREATE、ALTER、DROP
create database 就是在/var/lib/mysql/目录下创建一个对应的目录 ```shell MariaDB [(none)]> create database testdb; Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases -> ; +——————————+ | Database | +——————————+ | information_schema | | mysql | | performance_schema | | test | | testdb | | wordpress | +——————————+ 6 rows in set (0.04 sec)
- DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查;<br />INSERT, DELETE, UPDATE, SELECT
- 获取命令帮助:<br />mysql> help KEYWORD
<a name="oVovj"></a>
#### 数据库管理(DDL):
创建:<br /> CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;<br /> [DEFAULT] CHARACTER SET [=] charset_name<br /> [DEFAULT] COLLATE [=] collation_name<br /> <br /> 查看支持的所有字符集:SHOW CHARACTER SET <br /> 查看支持的所有排序规则:SHOW COLLATION<br /> <br /> 修改:<br /> ALTER {DATABASE | SCHEMA} [db_name]<br /> [DEFAULT] CHARACTER SET [=] charset_name<br /> [DEFAULT] COLLATE [=] collation_name<br /> <br /> 删除:<br /> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name<br /> <br /> 查看:<br /> SHOW DATABASES LIKE '';
<a name="aYiD7"></a>
## 表管理:
- 第二种创建方式:<br />复制表结构;<br />CREATE TABLE tbl_name LIKE other_table_name
- 第三种创建方式:<br />复制表数据;<br /> 创建:
CREATE TABLE [IF NOT EXISTS] [db_name.]tbl_name (create_defination) [table_options]<br /> <br /> create_defination:<br /> 字段:col_name data_type<br /> 键:<br /> PRIMARY KEY (col1, col2, ...)<br /> UNIQUE KEY (col1, col2,...)<br /> FOREIGN KEY (column)<br /> 索引:<br /> KEY|INDEX [index_name] (col1, col2,...)<br /> <br /> table_options:<br /> ENGINE [=] engine_name<br /> CHARACTER SET [=] charset_name<br /> COLLATE [=] collation_name<br /> <br /> 查看数据库支持的所有存储引擎类型:<br /> mysql> SHOW ENGINES;<br /> <br /> 查看某表的状态信息:<br /> mysql> SHOW TABLES STATUS [LIKE 'tbl_name'][WHERE clause]
```shell
MariaDB [(none)]> CREATE TABLE hidb.tbl1 (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(60) NOT NULL)
-> ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> DESC hidb.tbl1;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(60) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
MariaDB [testdb]> show table status\G
*************************** 1. row ***************************
Name: tbl1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 1
Create_time: 2019-04-28 23:56:34
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
修改:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]
alter_specification:
字段:
添加:ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
删除:DROP [COLUMN] col_name
修改:
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
键:
添加:ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1, col2,...)
删除:
主键:DROP PRIMARY KEY
外键:DROP FOREIGN KEY fk_symbol
索引:
添加:ADD {INDEX|KEY} [index_name] (col1, col2,...)
删除:DROP {INDEX|KEY} index_name
表选项:
ENGINE [=] engine_name
查看表上的索引的信息:
mysql> SHOW INDEXES FROM tbl_name;
删除:
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
表的引用方式:
tbl_name
db_name.tbl_name
DML:INSERT, DELETE, UPDATE, SELECT
INSERT:
INSERT [INTO] tbl_name [(col1,…)] {VALUES|VALUE} (val1, …),(…),…注意: <br />字符型:引号; <br />数值型:不能用引号;
SELECT ```shell (1) SELECT * FROM tbl_name[, tbl_name_2]; 返回指定表的所有数据;慎用; (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;
操作符(1) :
>, <, >=, <=, ==, !=
组合条件:
and
or
not
操作符(2) :
BETWEEN ... AND ...
LIKE 'PATTERN'
通配符:
%:任意长度的任意字符;
_:任意单个字符;
RLIKE 'PATTERN'
正则表达式对字符串做模式匹配;
IS NULL
IS NOT NULL
(4) SELECT col1, … FROM tbl_name [WHERE clause] ORDER BY col_name, col_name2, … [ASC|DESC];
ASC: 升序;
DESC: 降序;
(5) 分组: GROUP BY,为了聚合; count(), sum(), avg(), max(), min()
HAVING:对聚合的结果做条件过滤;
- DELETE:删除行;
```shell
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 [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
用户账号及权限管理:
用户账号:’username’@’host’
host:此用户访问当前mysql服务器时,允许其通过哪些主机远程创建连接;
表示方式:IP,网络地址、主机名、通配符(%和_);
禁止检查主机名:my.cnf
[mysqld]
skip_name_resolve = ON
- 创建用户账号:
CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’]; - 删除用户账号:
DROP USER ’user‘@’host’ [, user@host] … 授权:
权限级别:管理权限、数据库、表、字段、存储例程;priv_type: ALL [PRIVILEGES]
db_name.tbl_name:
*.*:所有库的所有表;
db_name.*:指定库的所有表;
db_name.tbl_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.tbl_name FROM ‘user’@’host’;
注意:MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中;
(1) GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改 会立即生效;
(2) 其它方式实现的权限修改,要想生效,必须手动运行FLUSH PRIVILEGES命令方可;
加固mysql服务器,在安装完成后,运行mysql_secure_installation命令;