2020-07-28 mysql -mariadb

类型：关系型数据库
联系类型
数据的操作：
数据提取：在数据集合中提取感兴趣的内容。SELECT
数据更新：变更数据库中的数据。INSERT、DELETE、UPDATE
联系的类型
一对一联系(1:1)
一对多联系(1:n)
多对多联系(m:n)

1.[数据库]中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。

2.通过表的关系，来帮助我们怎样建表，建几张表。

一对一
一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

学生表：姓名，性别，年龄，身高，体重，籍贯，家庭住址，紧急联系人
其中姓名、性别、年龄、身高，体重属于常用数据，但是籍贯、住址和联系人为不常用数据
如果每次查询都是查询所有数据，不常用的数据就会影响效率，实际又不用
常用信息表：ID(P)，姓名，性别，年龄，身高，体重
不常用信息表：ID(P)，籍贯，家庭住址，紧急联系人

解决方案：将常用的和不常用的信息分享存储，分成两张表
不常用信息表和常用信息表，保证不常用信息表与常用信息表能够对应上：找一个具有唯一性的
字段来共同连接两张表。
一个常用表中的一条记录永远只能在一张不常用表中匹配一条记录，反之亦然。</pre>

一对多
一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
只能对应第一张表的一条记录，这种关系就是一对多或多对一
母亲与孩子的关系：母亲，孩子两个实体
母亲表：ID(P),名字，年龄，性别
孩子表：ID(P),名字，年龄，性别
以上关系：一个妈妈可以在孩子表中找到多条记录（也可能是一条），但是一个孩子只能找到一个妈妈
是一种典型的一对多的关系。
但是以上设计：解决了实体的设计表问题，但是没有解决关系问题，孩子找不到母亲，母亲也找不到孩子

解决方案：在某一张表中增加一个字段，能够找到另外一张表中的记录:在孩子表中增加一个字段
指向母亲表，因为孩子表的记录只能匹配到一条母亲表的记录。
母亲表：ID(P),名字，年龄，性别
孩子表：ID(P),名字，年龄，性别，母亲表ID（母亲表主键）</pre>

多对多
一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
也能对应A表中的多条记录

老师和学生
老师表 T_ID(P),姓名，性别
学生表 S_ID(P),姓名，性别
以上设计方案：实现了实体的设计，但是没有维护实体的关系
一个老师教过多个学生，一个学生也被多个老师教过

解决方案：增加一张中间关系表
老师与学生的关系表：ID(P),T_ID,S_ID
老师表与中间表形成一对多的关系，而中间表是多表；维护了能够唯一找到一表的关系；
同样的学生表与中间表也是一个一对多的关系;
学生找老师：找出学生ID--->中间表寻找匹配记录（多条）--->老师表匹配（一条）
老师找学生：找出老师ID--->中间表寻找匹配记录（多条）--->学生表匹配（一条）</pre>
yum -y install mariadb mariadb-server yum安装
提高安全性
mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

mariadb版本 5.5.65-MariaDB MariaDB Server
 create database yangt character set utf8mb4; 建库时指定字符类型 ，避免出现乱码
MariaDB [(none)]>  create database yang;   默认建库
查看支持所有字符集：SHOW CHARACTER SET
MariaDB [(none)]> show create database yang;    
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| yang     | CREATE DATABASE `yang` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> create database yangt character set utf8mb4; 指定字符编码类型
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database yangt;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| yangt    | CREATE DATABASE `yangt` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show databases; 查看所有库
+--------------------+
| Database           |
+--------------------+


| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb   进入库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> show tables;   查看库中所有表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

union 纵向合并多表查询 
字段数一样  字段次序数据类型相匹配;例
MariaDB [hellodb]> select stuid,name,age,gender from students union select * from teachers;
+-------+---------------+-----+--------+
| stuid | name          | age | gender |
+-------+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |
|     2 | Shi Potian    |  22 | M      |
|     3 | Xie Yanke     |  53 | M      |
|     4 | Ding Dian     |  32 | M      |
|     5 | Yu Yutong     |  26 | M      |
|     6 | Shi Qing      |  46 | M      |
|     7 | Xi Ren        |  19 | F      |
|     8 | Lin Daiyu     |  17 | F      |
|     9 | Ren Yingying  |  20 | F      |
|    10 | Yue Lingshan  |  19 | F      |
|    11 | Yuan Chengzhi |  23 | M      |
|    12 | Wen Qingqing  |  19 | F      |
|    13 | Tian Boguang  |  33 | M      |
|    14 | Lu Wushuang   |  17 | F      |
|    15 | Duan Yu       |  19 | M      |
|    16 | Xu Zhu        |  21 | M      |
|    17 | Lin Chong     |  25 | M      |
|    18 | Hua Rong      |  23 | M      |
|    19 | Xue Baochai   |  18 | F      |
|    20 | Diao Chan     |  19 | F      |
|    21 | Huang Yueying |  22 | F      |
|    22 | Xiao Qiao     |  20 | F      |
|    23 | Ma Chao       |  23 | M      |
|    24 | Xu Xian       |  27 | M      |
|    25 | Sun Dasheng   | 100 | M      |
|     1 | Song Jiang    |  45 | M      |
|     2 | Zhang Sanfeng |  94 | M      |
|     3 | Miejue Shitai |  77 | F      |
|     4 | Lin Chaoying  |  93 | F      |
内连接：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接:去掉重复列的等值连接
自连接
左外连接：
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

image.png

创建视图
MariaDB [hellodb]> create view view_oldstudents as select * from students where age >30; 
视图无法添加和删除 基于原表生成
MariaDB [hellodb]> drop view view_oldstudents;   删除

权限：

create user test@'192.168.50.%' identified by '12321';   此网段可用test远程访问数据库
禁用反向解析
[root@localhost etc]# vim my.cnf

[mysqld]
skip_name_resolve     禁用反向解析
skip_grant_tables     登录密码忽略检测
skip-networking           仅本地登录

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
删除用户
MariaDB [mysql]> select user,host,password from user;
+------+--------------+-------------------------------------------+
| user | host         | password                                  |
+------+--------------+-------------------------------------------+
| root | localhost    | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root | 127.0.0.1    | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root | ::1          | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| test | 192.168.50.% | *045C8A3B828B8D7674941C5D03E5961BAB546F9A |
+------+--------------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> drop user 'test'@'192.168.50.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root | 127.0.0.1 | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root | ::1       | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant all on retl.* to test@'192.168.50.%'; 为test用户赋予retl库的所有权限。

权限	权限级别	权限说明
create	数据库、表或索引	创建数据库、表或索引权限
drop	数据库或表	删除数据库或表权限
grant option	数据库、表或保存的程序	赋予权限选项
references	数据库或表	外键权限
alter	表	更改表，比如添加字段、索引、修改字段等
delete	表	删除数据权限
index	表	索引权限
insert	表	插入权限
select	表	查询权限
update	表	更新权限
create view	视图	创建视图权限
show view	视图	查看视图权限
alter routine	存储过程	更改存储过程权限
create routine	存储过程	创建存储过程权限
execute	存储过程	执行存储过程权限
file	服务器主机上的文件访问	文件访问权限
create temporary tables	服务器管理	创建临时表权限
lock tables	服务器管理	锁表权限
create user	服务器管理	创建用户权限
proccess	服务器管理	查看进程权限
reload	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
replication client	服务器管理	复制权限
replication slave	服务器管理	复制权限
show databases	服务器管理	查看数据库权限
shutdown	服务器管理	关闭数据库权限
super	服务器管理	执行kill线程权限

mysql> revoke delete on retl.* from test@'192.168.50.%';   去除删除权限
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for test@'192.168.50.%';    查看用户拥有的权限
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.50.%                                                                                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.50.%'                                                                                                                                                                                     |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `retl`.* TO 'test'@'192.168.50.%' |
| GRANT ALL PRIVILEGES ON `mysql`.`db_erp_new` TO 'test'@'192.168.50.%'                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

优化