大家好，我是讯享网，很高兴认识大家。

理论

数据分类：

结构化数据：数字、字母、属性信息

非结构化数据：图片、MP3、文件

半结构化数据

关系模型：

二维关系：

数据库：由多张有关系的表组合在一起

表(row,column)：数据存储的地方

索引(index)：相当于表的目录，储存在内存

视图(view)：虚表，从表中抽出用户可看到的数据

DDL：数据定义语言，用于定义一张表的字段

CREATE,ALTER,DROP,SHOW

DML：数据操纵语言，用于对表中数据进行增删改查

INSERT,DELETE,UPDATE,SELECT

SQL代码

存储过程（procedure）：执行后没有返回值

存储函数（function）：执行后有返回值

触发器（trigger）：满足条件后触发另外一个执行

事件调度器（ecent scheduler）：周期性计划执行

关系运算：

 选择：挑选出符合条件的行

 投影：挑选出符合需要的列

 连接：将多张表关联起来

数据抽象：

 物理层：数据的存储格式

 逻辑层：数据间关系

 视图层：看到的DB数据

约束constralnt：向数据表插入的数据要遵守的限制规则

 主键：能作为表中唯一数据的key，而且不能为空；一个表只能有一个

 唯一键：能作为表中唯一数据的key，可以为空；一个表可以存在多个

 外键：主表能填的数据取决于外表有没有这个数据

 检查性约束：自定义表达式限制

索引类型：树形（可排序、范围查询）、hash（精确查询，只有MEMORY表才支持）

---

MarlaDB（mysql）

插件式存储引擎，存储引擎也称为“表类型”

MyISAM：不支持事务，表级锁，崩溃后不保证安全恢复

InnoDB->XtraDB：支持事务，行级锁，外键，热备

安装MarlaDB（使用EPEL源）

yum -y install mariadb mariadb-server

MarlaDB组成

Client

Mysql：CLI交换式客户端程序

Mysqldump：备份工具

Mysqladmin：管理工具

Mysqlbinlog：日志工具

Server

Mysqld

Mysqld_safe

Mysqld_multi

监听端口：3306/tcp（远程）；/var/lib/mysql/mysql.sock（本机）

本机通信必须使用地址：localhost或127.0.0.1才可以使用UNIX SOCK共享内存通信

如果用二进制方式安装，sock文件在/tmp/mysql.sock

配置文件(INI风格，用一个文件为多个程序提供配置)

my_print_defaults命令可以查看配置文件加载顺序；mysql的各类程序启动读取不止一个配置文件，按顺序读取，且最后读取的为最终生效。

配置文件加载顺序：/etc/mysql.cnf ->  /etc/my.cnf（由/etc/my.cnf+/etc/my.cnf.d/*组成）-> ~/.my.cnf

命令行交互式客户端命令：

格式：mysql [OPTIONS] [database]

mysql -uusername -hhostname -pabc -Dname -e ‘SHOW TABLES’

常用选项：

-u , --user=username 用户名，默认为root

-h , --host=hostname 远程主机（即mysql服务器）地址，默认为localhost；客户端连接服务器端时，服务器会反解客户的IP为主机名。关闭此功能（skip_name_resolve=ON）

注意：mysql的用户账号有两部分组成：“USERNAME@HOST”；其中HOST是指USERNAME此用户只能通过HOST这台主机连接mysql服务器

HOST的表示方式，支持使用通配符：

% ：匹配任意长度的任意字符

172.16.%.% = 172.16.0.0/16

-  ：匹配任意单个字符

-p , --password[=PASSWORD] 用户密码，默认为空

-P , --port=# ：指明mysql服务器监听的端口，默认为3306/tcp

-S , --socket=/PATH/TO/mysql.sock ：指明套接字文件路径

-D , --database=name ：连接到服务器端之后，设定此处指明的数据库为默认数据库

-e , --execute=’SQL语句’ ：不登录执行sql命令

---

用户账号及权限管理：

用户账号： ‘user’@’host’

Host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接

表示方式：IP，网络地址，主机名，通配符(%和_)

机制检查主机名：my.cnf ，添加

[mysqld]

skip_name_resolve = ON

创建用户账号：CREATE USER ‘user’@’host’ [IDENTIFIED BY ‘password’];

删除用户账号：DROP USER ‘user’@’host’;

授权：GRANT priv_type,... ON [object_type] db_name.tbl_name TO ‘user’@’host’ [IDENTIFIED BY ‘password’];

priv_type：ALL,DELETE,UPDATE,INSERT,SELECT,....增删改查

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’ 

注意：MarlaDB服务进程启动时，会读取mysql库的所有授权表至内存中

1. GRANT或REVOKE命令等执行的权限操作会保存于表中，MarlaDB此时一般会自动重读授权表，权限修改会立即生效；
2. 其他方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令；

加固mysql服务器，在安装完成后，在shell中运行mysql_secure_Installation命令

---

命令（不区分大小写）：

客户端命令（默认使用小写）：本地执行

mysql>\u db_name：设定默认数据库

\q：退出

\d CHAR：设定新的语句结束符

\g：语句结束标记

\G：语句结束标记，结果竖排方式显示

\s：查看状态

\c：相当于shell中的ctrl+c

\!：执行shell命令

\.：执行sql语句脚本，脚本必须放在mysql有权限的文件夹下

服务端（默认使用大写）：通过mysql连接发往服务器执行并取回结果（SQL语句）

mysql>help contents：查看命令类别

help ‘命令类别’：查看使用格式

Account management 账号管理

Administration 服务器管理

Data Definition 数据库定义

Data Manipulation 数据库增删改查

Data Types 定义数据类型

字符集，默认是拉丁字母

mysql>SHOW CHARACTER SET; 查看支持的字符集

     >SHOW COLLATION; 查看排序规则

>SHOW ENGINES; 查看数据库支持引擎

>SHOW WARNING; 查看告警信息

种类：（TINY 28<SMALL 216<MEDIUM 224<BIG 264）

讯享网

字符型：

定长字符：CHAR(#)：不区分大小写; BINARY(#)：区分大小写

变长字符：VARCHAR(#);VARBINARY(#)，超过256个字符必须使用两个结束符，最多只能存储65535个字符

    对象存储：TEXT 不区分大小写;BLOB区分大小写

内置：SET(集合);ENUM (枚举)

数值型：

精确数值：INT,DECIMAL

近似数值：FLOAT,DOBULE

字段修饰符：

NOT NULL：不能为空

AUTO_INCRMEN：自动增长，默认从1开始

NULL：允许为空

DEFAULT value ：默认值

PRIMARY KEY：主键

UNIQUE KEY：唯一键

---

DDL：数据定义语言

数据库管理：

CREATE 创建

格式：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[IF NOT EXISTS]如果存在就不创建

CREATE DATABASE test;

ALTER 修改

格式：ALTER {DATABASE | SCHEMA} [db_name] [CHARACTER SET ‘charset’ | COLLATE ‘collation’]

CHARACTER SET ‘charset’ 字符集

COLLATE ‘collation’ 排序规则

ALTER DATABASE test CHARACTER SET ‘utf8’;

DROP 删除

格式：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

SHOW 查看

格式：SHOW DATABASES LIKE ‘db_name’

db_name 支持通配符

表管理：

CREATE 创建

格式：CREATE TABLE [IF NOT EXISTS] [db_name.]tbl_name (create_defination) [table_options]

create_defination：创建的定义

字段：col_name ,db_type

约束：

PRIMARY KEY

UNIQUE KEY

FOREIGN KEY

索引：

KEY INDEX [index_name]

table_options：表选项

ENGINE engine_name 定义表引擎

CHAARACTER SET charset_name 定义表字符集，默认从数据库继承

COLLATE collation_name 定义排序规则

CREATE TABLE tbl2 (id SMALLINT UNSIGNED NOT NULL AUTO_INSREMENT,name VARCHAR(60) NOT NULL,UNIQUE KEY(id)); 创建2万个以内自然数非空自动增长唯一键的id，60个以内变长字符非空的id

ALTER 修改

格式：ALTER [ONLINE |OFFLINE] [IGNORE] TABLE tbl_name [alter_speclfication,....]

alter_speclfication：

字段：

添加：ADD col_name data_type [FIRST | AFTER] col_name

删除：DROP col_name

修改：CHANGE 字段的名称和定义

  MODIFY 字段的定义

约束：

添加：ADD {PRIMARY| UNIQUE|FOREIGN} KEY (col1,col2,...)

删除：DROP

索引：

添加：ADD INDEX (col1,col2,...)

删除：DROP

ALTER TABLE tbl2 ADD gender ENUM(‘F’,’M’) AFTER id; 在tbl2表中添加gender这个枚举字段，放在id后面（不指明默认放最后）

ALTER TABLE tbl2 ADD INDEX (name); 为name字段创建索引

ALTER TABLE tbl2 CHANGE id stuid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; 修改id定义和名称为stuid（必须跟上原有参数，不然参数为空）

 SHOW 查看

SHOW TABLES STATUS [LIKE ‘tbl_name’]\G 查看表状态

DESC tbl_name 查看表定义

SHOW INDEXES FORM tbl_name 查看某个表的索引

复制表结构：

CREATE TABLE tbl7 LIKE mysql.user; 复制mysql.user表结构并创建为tbl7表

复制表数据：

CREATE TABLE tbl7 SELECT Host,User,Password FORM mysql.user; 复制mysql.user表中的SELECT Host,User,Password数据并创建为tbl7表

索引管理：

CREATE 创建

格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name(col1,col2,...) 默认为B树索引

DROP 删除

格式：FROP INDEX index_name ON tbl_name

---

DML：数据操控语言

INSERT|REPLACE 增加

格式：INSET|REPLACE INTO tbl_name VALUES (col1,col2,...)

        INSERT 增量添加，如果与原有数据有重复，不会添加

  REPLACE 覆盖添加，修改原有数据

注意：字符要使用引号，数值不能用引号

SELECT 选择

格式：（1）SELECT * FROM tbl_name,tbl2_name,....; 返回指定表的所有数据,慎用

1. SELECT col1,col2,... FROM tbl_name;

显示时，字段可以显示为别名，慎用

col_name AS col_alias

1. SELECT col1,col2... FROM tbl_name WHERE clause;

WHERE clause：用于指明挑选条件

Clause：

Col_name 操作符/组合条件 value

操作符（1）：>,<,>=,<=,==,!=,+,-,*,/

组合条件：AND,OR,NOT

操作符（2）：IS NULL,IS NOT NULL

IN (PATTERN1,PATTERN2,...)

LIKE ‘PATTERN’

通配符：% 任意字符 ，_ 任意单个字符

RLIKE ‘PATTERN’（正则表达式）

1. SELECT col1,col2... FROM tbl_name [WHERE clause] ORDER BY col_name,col_name2,... [ASC|DESC]

ASC：升序，默认

DESC：降序

1. 分组：

GROUP BY

count(),sum(),avg(),max(),min()

HAVING：对聚合的结果做条件过滤

DELETE 删除行

格式：DELETE FROM tbl_name [WHERE where_conditton] [ORDER BY ...] [LIMIT row_count]

DELETE FROM students ORDER BY age DESC LIMIT 100; 删除降序排序后的前100个age

DELETE FROM students WHERE id=3; 删除id是3的一行

UPDATE 修改字段

UPDATE [LOW_PRIORITY] [IGNORE] table_regerence SET col_name1=value1 [,col_name2=value2] ... [WHERE where_condittion] [ORDER BY ...] [LIMIT row_count]

UPDATE students SET classid=5 WHERE id=2; 把id为2的classid字段改成5