/ mysql

mariadb

Mysql: https://haobing.wang/mysql/

MongDB 和 MariaDB 将会在不同的场景下发挥这个的作用,谁也不会取代谁。

MariaDB 会逐渐取代 MySQL。

https://www.openhub.net/p/mariadb/

常用 sql

创建数据库

CREATE DATABASE rookery CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

建立了一个名为 rookery 的库,默认字符使用 utf8mb4,校对方式为 utf8mb4_general_ci。

创建表

一个表最多可以建 255 列,但不建议这么做。太多列的表,用起来麻烦,访问速度也慢。

CREATE TABLE birds (
	bird_id int PRIMARY KEY AUTO_INCREMENT,
	scientific_name varchar(50) UNIQUE,
	common_name varchar(50),
	family_id int,
	description text
);
MariaDB [rookery]> create table birds( bird_id int auto_increment primary key, scientific_name varchar(50) unique, common_name varchar(50), family_id int,  description text);
Query OK, 0 rows affected (0.01 sec)
MariaDB [rookery]> desc birds;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| bird_id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| scientific_name | varchar(50) | YES  | UNI | NULL    |                |
| common_name     | varchar(50) | YES  |     | NULL    |                |
| family_id       | int(11)     | YES  |     | NULL    |                |
| description     | text        | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+

DESCRIBE 表名,可用 DESC 表名

如果需要查看建表语句:

show create table birds;

字段的类型的选择:

  • char 最大长度
  • bit 只占一位,状态有两种: 1 代表有设值,0 代表没设值。例如表示某个物种是否濒危。

建议,想要输入数据时再建表;输入完主表后,再输入辅表。这是因为我们很难一开始就确定到底需要多少表。相反,数据库开发是一个过程,你会不断地增加表,改表结构,甚至拆分表以改善性能、简化管理。而且也正是这样,才使得数据库没那么乏味。

实际开发中提倡的是 code first,通过代码 migration 在让数据库同代码版本关联起来。PHP 中的 Yii2、Laravel,.NET Entity Framwork 均有对应的 migrate 工具。

更改表

即使计划得再好,你偶尔还是会需要更改表的结构或者其他方面的东西。所以,你无需在建表时追求完美,而应该把表看成一个不断变化的事物。也许 结构 给你一种规范的感觉,但是你得记住,表必须紧跟需求,不断调整,这个观念必须树立。

改表需谨慎,在改表之前,应该做好数据备份。无论你的改动多么小,都应该做备份。如果你改了列的大小,有可能会丢失部分数据。如果将列的数据类型改成与之前的不兼容(例如,将字符串改为数值),有可能会丢失全部数据。

规范的做法是:重构前备份数据,然后再副本上进行改动。

环境:

  • 数据库:rookery
    • 表:birds
  • 数据库账号:dba

备份单表(备份使用重定向符 >):

mysqldump -u dba -p > rookery birds > ~/birds.sql
Enter password: 

备份库:

mysqldump -u dba -p rookery  > ~/rookery.sql

如果出现异常,可以还原(备份使用重定向符 <):

mysql -u dba -p rookery  < ~/rookery.sql

修改表的语句基本结构:ALTER TABLE 表名 更改命令;

增加一列:

ALTER TABLE bird_families
ADD COLUMN order_id INT;

还有一种简单的办法,复制表,修改,如果出现问题,用复制表还原。

CREATE TABLE birds_new LIKE bird;

复制表数据:

INSERT INTO birds_new
SELECT * FROM birds;

现在开始更改新表 birds_new

ALTER TABLE birds_new ADD COLUMN wing_id char(2);

我们会发现 wing_id 列在最后,但是我们想要它紧跟着 family_id,所以让我们删除,重新添加:

ALTER TABLE birds_new DROP COLUMN wing_id;

ALTER TABLE birds_new ADD COLUMN wing_id char(2) AFTER family_id;

接下来,再添加 3 列,并将 common_name 由 text 改为 varchar(25):

ALTER TABLE birds_new 
ADD COLUMN body_id char(2) AFTER wing_id,
ADD COLUMN bill_id char(2) AFTER body_id,                                                                                        ADD COLUMN endangered bit DEFAULT b'1' AFTER bill_id,                                                                                                                                    CHANGE COLUMN common_name common_name varchar(255);

这里我们只用 endangered 表示两种状态,是否濒危。但是当需求要求更多的状态时,就需要修改:

ALTER TABLE birds_new 
MODIFY COLUMN endangered enum('灭绝', '野外绝灭', '极危', '濒危', '易危', '近危', '无危') 
AFTER family_id;
  • enum 枚举值。如果参考值不多,可以使用 enum 类型,而不是单独创建一个参考表。
  • MODIFY COLUMN 只能修改数据类型,不能修改列名。
  • 修改列名,使用 CHANGE COLUMN

如果一切顺利 birds => birds_oldbirds_new => birds

RENAME TABLE birds TO birds_old,birds_new TO birds;

例如:

ALTER TABLE conservation_status
CHANGE COLUMN conservatin_category conservation_category char(10);

注意,修改列名必须包含类型。

MariaDb 动态列

CREATE TABLE surveys (
	survey_id int UNIQUE AUTO_INCREMENT,
	survey_name varchar(255)
);

CREATE TABLE survey_questions (
	question_id int UNIQUE AUTO_INCREMENT,
	survey_id int,
	question varchar(255),
	choices blob
);

CREATE TABLE survey_answers (
	answer_id int UNIQUE AUTO_INCREMENT,
	human_id int,
	question_id int,
	date_answered datetime,
	answer varchar(255)
);

survey_questions.choices 使用 blob,它将用于动态列。

INSERT INTO surveys (survey_name)
VALUES ('最佳观鸟地点');


INSERT INTO survey_questions (survey_id, question, choices)
VALUES (last_insert_id(),
        '你最喜爱的观念地点?',
        column_create('1', '树林', '2', '水岸', '3', '后院'));


INSERT INTO surveys (survey_name)
VALUES ('最喜欢的鸟');


INSERT INTO survey_questions (survey_id, question, choices)
VALUES (last_insert_id(),
        '你最喜爱哪种鸟?',
        column_create('1', '栖鸟类', '2', '岸禽类', '3', '家禽', '4', '猛禽'));

获取下动态列:

SELECT column_get(choices, 3 AS char) AS 'location'
FROM survey_questions
WHERE survey_id=1;

+----------+
| location |
+----------+
| 后院     |
+----------+

加入模拟数据:

INSERT INTO survey_answers (human_id, question_id, date_answered, answer)
VALUES (29, 1, now(), 2),
	(29, 2, now(), 2),
	(35, 1, now(), 1),
	(35, 2, now(), 1),
	(26, 1, now(), 2),
	(26, 2, now(), 1),
	(27, 1, now(), 2),
	(27, 2, now(), 4),
	(16, 1, now(), 3),
	(3, 1, now(), 1),
	(3, 2, now(), 1);

现在对第一个问卷进行统计:

SELECT ifnull(column_get(choices, answer AS char),'总计') AS '观鸟爱好者',
       count(*) AS '投票'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
  AND question_id = 1
GROUP BY answer WITH ROLLUP;
+-----------------+--------+
| 观鸟爱好者      | 投票   |
+-----------------+--------+
| 树林            |      2 |
| 水岸            |      3 |
| 后院            |      1 |
| 总计            |      6 |
+-----------------+--------+

拆分表

insert into 新增的副表
(字段1,字段2)
select 字段1,字段2 from 庞大的主表;

7 查询数据

7.3 表达式与 LIKE

简单的可以用 LIKE 匹配:

SELECT scientific_name,common_name FROM birds
WHERE common_name LIKE '红%';

复杂的可以用 REGEXP:

SELECT scientific_name, common_name FROM birds
WHERE common_name REGEXP '红|白';

MySQL 的正则表达式比其他语言,如 Perl 和 PHP 更长。

9 表连接和子查询

在实际开发中,多表查询是很常见的。它的实现方法有很多

9.1 合并两个不相关的结果集 UNION

第一个查询语句
UNION
第二个查询语句

9.2 表连接

  • 将多个表以逗号隔开,然后在 WHERE 中写出用以连接的列。SELECT * FROM 表1,表2 WHERE 表1.x = 表2.x_id
  • 使用 JOIN 来连接两个表,并特别指定连接条件。SELECT * FROM 表1 JOIN 表2 ON(x = x_id)

为了提高性能,用于连接的列应该加索引。

9.2.1 基本的表连接查询

多种 JOIN:

  • JOIN
  • INNER JOIN
  • OUTER JOIN
    • LEFT JOIN,查出左表的所有行,而不管其在右表有没有对应的行,无法找到的行会显示为 NULL。
    • RIGHT JOIN

9.2.2 更新已连接的表
9.2.3 从已连接的表中删除数据

备份

备份计划

验证备份计划(如果你的备份并不是有效的……):

  • 验证周期:每月
  • 库恢复测试
  • 表恢复测试
  • 保留期限:三个月

好的备份脚本应该做到定期清理旧备份。

创建备份脚本:

CentoOS7 要求数据库的 root 用户,需要以系统的 root 用户登录。所以,我们需要先切换到 root

sudo su

mariadb 的构成

  • 服务器:mysqld(d 代表 daemon,即守护进程)。MySQL 和 MariaDB 的守护进程都叫做 mysqld。这个守护进程是对整个数据库进行存储和操控的实际执行者。
  • 命令行客户端:mysql,使用它的命令行接口登录和执行 SQL 查询。也可以使用其他 GUI 客户端如 Navicat 等。

特点:多线程。

常用工具:

mysqlaccess

mysqlaccess 用于创建用户账号和设置权限。

mysqladmin

mysqladmin 是服务器管理工具,可以交互式查看服务器状态和使用量,以及关闭服务器。

vagrant@ubuntu-xenial:~$ sudo mysqladmin status

Uptime: 2538 Threads: 1 Questions: 735 Slow queries: 0 Opens: 168 Flush tables: 1 Open tables: 31 Queries per second avg: 0.289

agrant@ubuntu-xenial:~$ sudo mysqladmin -v version status
mysqladmin  Ver 9.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Server version		10.0.31-MariaDB-0ubuntu0.16.04.2
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			52 min 47 sec

Threads: 1  Questions: 738  Slow queries: 0  Opens: 168  Flush tables: 1  Open tables: 31  Queries per second avg: 0.233
Uptime: 3167  Threads: 1  Questions: 738  Slow queries: 0  Opens: 168  Flush tables: 1  Open tables: 31  Queries per second avg: 0.233

mysqlshow

mysqlshow 可以查看数据库和表的。

vagrant@ubuntu-xenial:~$ sudo mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

mysqldump

mysqldump 是最流行的导出 dump 文件(包含表结构和数据的纯文本文件)的工具。这使得我们可以备份数据,或在服务器间复制数据。

vagrant@ubuntu-xenial:~$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

mysql 客户端可以将 dump 文件导回到数据库中。

mariadb 10

Configuring MariaDB with my.cnf

修改配置文件 :

  • /etc/my.cnf.d/mysql-clients.cnf[mysql] 添加 default-character-set = utf8mb4
  • /etc/my.cnf.d/server.cnf[mysqld] 添加 character-set-server = utf8mb4

重新启动 mariadb :

sudo systemctl restart mariadb

进入数据库并查看编码:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

The end result should read:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

安装 mariadb

CentOS7 下安装

如果要安装 MariaDB 10 版本的,参考文档:https://mariadb.com/kb/en/library/yum/

安装 MariaDB 5 版本:

sudo yum install mariadb-server mariadb

启动 mariadb:

sudo systemctl start mariadb
sudo systemctl status mariadb

如果状态正常,则将 mariadb 设置为自启动:

sudo systemctl enable mariadb
sudo mysql_secure_installation

Enter current password for root (enter for none): 直接按回车
OK, successfully used password, moving on...

Set root password? [Y/n]
New password: 输入新密码
Re-enter new password: 再次输入新密码
Password updated successfully!

一路回车

新建个库试试:

mysql -u root -p

CREATE DATABASE wordpress;

CREATE USER wordpressuser@localhost IDENTIFIED BY '57ul$0s5D4b7';

GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser@localhost IDENTIFIED BY '57ul$0s5D4b7';

FLUSH PRIVILEGES;

exit

如果需要禁用:

sudo systemctl stop mariadb
sudo systemctl disable mariadb

Ubuntu16.04 安装

apt search mariadb
sudo apt install mariadb-server mariadb-client
sudo systemctl start mysql
sudo systemctl status mysql

可以看到 MariaDb 使用内存:

Memory: 102.9M

vagrant@ubuntu-xenial:~$ mysql -V

mysql Ver 15.1 Distrib 10.0.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

如果状态 ok,则设置自启动:

sudo systemctl enable mysql

Secure the installation of MariaDB:

sudo mysql_secure_installation
  1. 第一次提示输入当前root密码,因为我们刚刚安装完,所以密码是空的,我们直接回车。
  2. 接下来设置 root 密码。
  3. 其它直接按回车
vagrant@ubuntu-xenial:~$ mysql -u root -p

Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

登录错误,原因是如果你想以 root 登录 MariaDb,那么你必须是系统的 root 用户。所以需要 sudo :

vagrant@ubuntu-xenial:~$ sudo mysql -u root -p

创建用户

就像系统 root 一样,MariaDB 的 root 也最好不要用于日常管理,我们需要创建一个普通用户来进行日常使用。

sudo mysql -u root -p

登入进去后执行:

GRANT USAGE ON  *.* TO 'dba'@'localhost' IDENTIFIED BY '12345678';

这里创建了一个数据库管理员的 dba 用户,能够从 localhost 登录 mysqld。其中 *.* 表示所有数据库和对应的表。但是 dba 现在还没有任何权限,如果你希望给他查看数据:

GRANT SELECT ON  *.* TO 'dba'@'localhost';
FLUSH PRIVILEGES;

这里是 SELECT,如果希望赋予他所有权限,可以将 SELECT 改为 ALL

GRANT ALL ON  *.* TO 'dba'@'localhost';
FLUSH PRIVILEGES;

如果想查看某个用户的权限:

sudo mysql -u root -p -e"SHOW GRANTS FOR 'dba'@'localhost' \G"

Grants for dba@localhost: GRANT SELECT ON *.* TO 'dba'@'localhost' IDENTIFIED BY PASSWORD '*84AAC12F54AB666ECFC2A83C676908C8BBC381B1'

重置 root 密码

修改配置文件:

sudo vi /etc/my.cnf.d/server.cnf
...
[mysqld]
skip-grant-tables # 添加这一行
...
``

保存后,重启服务:

```shell
sudo systemctl restart mariadb
mysql -uroot -p
USE mysql;
UPDATE user SET Password = password('9Xbr+VPj8HZgp.') WHERE User = 'root';
exit;

再次修改配置文件:

...
[mysqld]
#skip-grant-tables # 添加这一行
...

使用新密码登录:

mysql -uroot -p