原标题:白屏化背后,DBA应有的数据库自动化建设思路

 

一、 数据库介绍

什么是数据库?

AG娱乐场,数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。

所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational
Database Management System)的特点:

  • 数据以表格的形式出现
  • 每行为各种记录名称
  • 每列为记录名称所对应的数据域
  • EVO真人视讯,许多的行和列组成一张表单
  • 若干的表单组成database

RDBMS 术语

在我们开始学习MySQL
数据库前,让我们先了解下RDBMS的一些术语:

  • IG彩票,数据库: 数据库是一些关联表的集合。.
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素)
    包含了相同的数据, 例如邮政编码的数据。
  • 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
  • 冗余:存储两倍数据,冗余可以使系统速度更快。(表的规范化程度越高,表与表之间的关系就越多;查询时可能经常需要在多个表之间进行连接查询;而进行连接操作会降低查询速度。例如,学生的信息存储在student表中,院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称,必须从student表中查找学生所在院系的编号(dept_id),然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时,连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name,该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
  • 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键:外键用于关联两个表。
  • 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
  • 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。

www.4355mg.com, 

 

作者介绍茹作军,曾任职我查查运维工程师、1号店MySQL
DBA,现就职于平安好医生。Lepus开源数据库监控系统作者(www.lepus.cc)。

对于DBA来说,数据库Job在熟悉不过了,因为经常要数据库定时的自动执行一些脚本,或做数据库备份,或做数据的提炼,或做数据库的性能优化,包括重建索引等等的工作。但是,Oracle定时器Job时间的处理上,千变万化,今天我把比较常用的写法汇总如下:

二、MySQL管理

MySQL的安装、配置、启动、该密码等操作不再赘述。。。

1、数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型:

  • MySQL支持所有标准SQL数值数据类型。
  • 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE
    PRECISION)。
  • 关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
  • BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
  • 作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT

下面的表显示了需要的每个整数类型的存储和范围

EVO真人视讯 1


日期和时间类型:

  • 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
  • 每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。
  • TIMESTAMP类型有专有的自动更新特性,将在后面描述

EVO真人视讯 2


字符串类型:

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型

  • CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
  • BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
  • BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
  • 有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求

EVO真人视讯 3

 

2、管理命令

以下列出了使用Mysql数据库过程中常用的命令:

  • USE 数据库名
    :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
  • SHOW DATABASES: 列出 MySQL
    数据库管理系统的数据库列表。
  • SHOW TABLES:
    #显示指定数据库的所有表,使用该命令前需要使用
    use命令来选择要操作的数据库。
  • GD视讯直播,SHOW COLUMNS FROM 数据表:
    #显示数据表的属性,属性类型,主键信息 ,是否为
    NULL,默认值等其他信息。
  • create database testdb charset “utf8”;
    #创建一个叫testdb的数据库,且让其支持中文
  • drop database testdb;
    #删除数据库
  • SHOW INDEX FROM
    数据表:显示数据表的详细索引信息,包括PRIMARY KEY(主键)

① 创建数据表:

# 创建表

mysql> create table student(
    -> stu_id INT NOT NULL AUTO_INCREMENT,
    -> name CHAR(32) NOT NULL,
    -> age INT NOT NULL,
    -> register_date DATE,
    -> PRIMARY KEY(stu_id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| stu_id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name          | char(32) | NO   |     | NULL    |                |
| age           | int(11)  | NO   |     | NULL    |                |
| register_date | date     | YES  |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

注:AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1;如果你不想字段为 NULL 可以设置字段的属性为 NOT
NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错;PRIMARY
KEY关键字用于定义列为主键。
您可以使用多列来定义主键,列间以逗号分隔

② 插入数据表:

#插入数据表

mysql> insert into student (name,age,register_date) values ("LianZhiLei",22,"2016-08-08");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  22 | 2016-08-08    |
+--------+------------+-----+---------------+
1 row in set (0.00 sec)


查询数据表:

语法:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
  • 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
  • SELECT
    命令可以读取一条或者多条记录。
  • www.52444.com,你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
  • 你可以使用 WHERE
    语句来包含任何条件。
  • 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  • 你可以使用 LIMIT
    属性来设定返回的记录数。

    # 查询

    mysql> select * from student limit 3 offset 2;
    +——–+————+—–+—————+
    | stu_id | name | age | register_date |
    +——–+————+—–+—————+
    | 3 | XinZhiYu | 12 | 2015-02-08 |
    | 4 | Alex li | 32 | 2016-08-08 |
    | 5 | LianZhiLei | 22 | 2016-08-08 |
    +——–+————+—–+—————+
    3 rows in set (0.00 sec)
    比如这个SQL ,limit后面跟的是3条数据,offset后面是从第3条开始读取

    mysql> select * from student limit 3 ,1;
    +——–+———+—–+—————+
    | stu_id | name | age | register_date |
    +——–+———+—–+—————+
    | 4 | Alex li | 32 | 2016-08-08 |
    +——–+———+—–+—————+
    1 row in set (0.00 sec)
    而这个SQL,limit后面是从第3条开始读,读取1条信息

where子句:

语法:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

mg4355电子游戏网址,以下为操作符列表,可用于 WHERE
子句中

EVO真人视讯 4

#where查询

mysql> select * from student where register_date > '2016-03-04'
    -> ;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  22 | 2016-08-08    |
|      2 | GaoCheng   |  22 | 2016-08-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      5 | LianZhiLei |  22 | 2016-08-08    |
|      6 | LianZhiLei |  22 | 2016-08-08    |
|      7 | LianZhiLei |  22 | 2016-08-08    |
|      8 | LianZhiLei |  22 | 2016-08-08    |
+--------+------------+-----+---------------+
7 rows in set (0.00 sec)

mysql> select * from student where stu_id = 3 ;
+--------+----------+-----+---------------+
| stu_id | name     | age | register_date |
+--------+----------+-----+---------------+
|      3 | XinZhiYu |  12 | 2015-02-08    |
+--------+----------+-----+---------------+
1 row in set (0.01 sec)

模糊查询(like):

#like

mysql> select * from student where register_date like "2016-08-%";
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  22 | 2016-08-08    |
|      2 | GaoCheng   |  22 | 2016-08-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      5 | LianZhiLei |  22 | 2016-08-08    |
|      6 | LianZhiLei |  22 | 2016-08-08    |
|      7 | LianZhiLei |  22 | 2016-08-08    |
|      8 | LianZhiLei |  22 | 2016-08-08    |
+--------+------------+-----+---------------+
7 rows in set, 1 warning (0.00 sec)

查询排序:

#查询排序

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列

mysql> select * from student order by age desc;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      6 | wupeiqi    |  33 | 2016-08-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      1 | LianZhiLei |  18 | 2016-08-08    |
|      3 | XinZhiYu   |  12 | 2015-02-08    |
+--------+------------+-----+---------------+
4 rows in set (0.00 sec)

分组统计:

#分组统计

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> select * from student;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  18 | 2016-08-08    |
|      3 | XinZhiYu   |  12 | 2015-02-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      6 | wupeiqi    |  33 | 2016-08-08    |
|      9 | LianZhiLei |  18 | 2016-08-08    |
|     10 | LianZhiLei |  18 | 2016-02-08    |
+--------+------------+-----+---------------+
6 rows in set (0.00 sec)

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> select name,count(*) from student group by name;
+------------+----------+
| name       | count(*) |
+------------+----------+
| Alex li    |        1 |
| LianZhiLei |        3 |
| wupeiqi    |        1 |
| XinZhiYu   |        1 |
+------------+----------+
4 rows in set (0.00 sec)

统计相同姓名的人年龄加起来,按组划分,并显示所有年龄的汇总
mysql> select name,sum(age) from student group by name with rollup;
+------------+----------+
| name       | sum(age) |
+------------+----------+
| Alex li    |       32 |
| LianZhiLei |       54 |
| wupeiqi    |       33 |
| XinZhiYu   |       12 |
| NULL       |      131 |
+------------+----------+
5 rows in set (0.00 sec)

其中记录 NULL 表示所有人的年龄和
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
mysql> select coalesce(name,"Total"),sum(age) from student group by name with rollup;
+------------------------+----------+
| coalesce(name,"Total") | sum(age) |
+------------------------+----------+
| Alex li                |       32 |
| LianZhiLei             |       54 |
| wupeiqi                |       33 |
| XinZhiYu               |       12 |
| Total                  |      131 |
+------------------------+----------+
5 rows in set (0.00 sec)

④ 修改数据表:

#update

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

mysql> update student set name = "wupeiqi",age = 33 where stu_id =6;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> select * from student;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  22 | 2016-08-08    |
|      2 | GaoCheng   |  22 | 2016-08-08    |
|      3 | XinZhiYu   |  12 | 2015-02-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      5 | LianZhiLei |  22 | 2016-08-08    |
|      6 | wupeiqi    |  33 | 2016-08-08    |
|      7 | LianZhiLei |  22 | 2016-08-08    |
|      8 | LianZhiLei |  22 | 2016-08-08    |
+--------+------------+-----+---------------+
8 rows in set (0.00 sec)

⑤ 删除数据表:

#delete

mysql> delete from student where age = 22;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from student;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  18 | 2016-08-08    |
|      3 | XinZhiYu   |  12 | 2015-02-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      6 | wupeiqi    |  33 | 2016-08-08    |
+--------+------------+-----+---------------+
4 rows in set (0.00 sec)

 ⑥
操作表字段(alter):

我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL
ALTER命令

插入表字段:

#插入表字段

mysql> alter table student add sex enum("F","M") not null ;
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+------------+-----+---------------+-----+
| stu_id | name       | age | register_date | sex |
+--------+------------+-----+---------------+-----+
|      1 | LianZhiLei |  18 | 2016-08-08    | F   |
|      3 | XinZhiYu   |  12 | 2015-02-08    | F   |
|      4 | Alex li    |  32 | 2016-08-08    | F   |
|      6 | wupeiqi    |  33 | 2016-08-08    | F   |
|      9 | LianZhiLei |  18 | 2016-08-08    | F   |
|     10 | LianZhiLei |  18 | 2016-02-08    | F   |
+--------+------------+-----+---------------+-----+
6 rows in set (0.00 sec)

mysql> desc student;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| stu_id        | int(11)       | NO   | PRI | NULL    | auto_increment |
| name          | char(32)      | NO   |     | NULL    |                |
| age           | int(11)       | NO   |     | NULL    |                |
| register_date | date          | YES  |     | NULL    |                |
| sex           | enum('F','M') | NO   |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改表字段:

#change修改字段名 类型

mysql> alter table student change sex gender char(32) not null default "X";
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+------------+-----+---------------+--------+
| stu_id | name       | age | register_date | gender |
+--------+------------+-----+---------------+--------+
|      1 | LianZhiLei |  18 | 2016-08-08    | F      |
|      3 | XinZhiYu   |  12 | 2015-02-08    | F      |
|      4 | Alex li    |  32 | 2016-08-08    | F      |
|      6 | wupeiqi    |  33 | 2016-08-08    | F      |
|      9 | LianZhiLei |  18 | 2016-08-08    | F      |
|     10 | LianZhiLei |  18 | 2016-02-08    | F      |
+--------+------------+-----+---------------+--------+
6 rows in set (0.00 sec)

mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| stu_id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name          | char(32) | NO   |     | NULL    |                |
| age           | int(11)  | NO   |     | NULL    |                |
| register_date | date     | YES  |     | NULL    |                |
| gender        | char(32) | NO   |     | X       |                |
+---------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

删除表字段:

#删除表字段

mysql> alter table student drop sex;
ERROR 1091 (42000): Can't DROP 'sex'; check that column/key exists
mysql> alter table student drop gender;
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+------------+-----+---------------+
| stu_id | name       | age | register_date |
+--------+------------+-----+---------------+
|      1 | LianZhiLei |  18 | 2016-08-08    |
|      3 | XinZhiYu   |  12 | 2015-02-08    |
|      4 | Alex li    |  32 | 2016-08-08    |
|      6 | wupeiqi    |  33 | 2016-08-08    |
|      9 | LianZhiLei |  18 | 2016-08-08    |
|     10 | LianZhiLei |  18 | 2016-02-08    |
+--------+------------+-----+---------------+
6 rows in set (0.00 sec)

 

3、外键  

外键,一个特殊的索引,用于关键2个表,只能是指定内容

#外键

mysql> create table class(
    -> id  int not null primary key,
    -> name char(16));
Query OK, 0 rows affected (0.02 sec)

CREATE TABLE `student2` (
  `id` int(11) NOT NULL,
  `name` char(16) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_class_key` (`class_id`),
  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
)

mysql> desc class;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc student2
    -> ;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | NO   | PRI | NULL    |       |
| name     | char(16) | NO   |     | NULL    |       |
| class_id | int(11)  | NO   | MUL | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

此时如果class 表中不存在id 1,student表也插入不了,这就叫外键约束
mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, 
CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))


mysql> insert into class(id,name) values(1,"linux");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
Query OK, 1 row affected (0.00 sec)


#如果有student表中跟这个class表有关联的数据,你是不能删除class表中与其关联的纪录的
mysql> delete from class where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`,
 CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

 

 

4、MySQL NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及
WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL
时,该命令可能就无法正常工作。
为了处理这种情况,MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>:
比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL
在列中查找 NULL 值 。
在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即 NULL
= NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

 

5、Mysql 连接(left join, right join, inner join
,full join)

我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的
JOIN 在两个或多个表中查询数据。

你可以在SELECT, UPDATE 和 DELETE
语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类:

  • INNER
    JOIN(内连接,或等值连接)
    :获取两个表中字段匹配关系的记录。
  • LEFT
    JOIN(左连接):
    获取左表所有记录,即使右表没有对应匹配的记录。
  • RIGHT JOIN(右连接): 与 LEFT
    JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

Suppose you have two tables, with a single
column each, and data as follows:  

A    B
-    -
1    3
2    4
3    5
4    6

Inner join

An inner join using either of the
equivalent queries gives the intersection of the two tables, i.e. the
two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

其实就是只显示2个表的交集

Left join

A left join will give all rows in A, plus
any common rows in B.

select * from a LEFT JOIN b on a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right join

A right join will give all rows in B, plus
any common rows in A

select * from a RIGHT JOIN b on a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full join

A full outer join will give you the union
of A and B, i.e. all the rows in A and all the rows in B. If something
in A doesn’t have a corresponding datum in B, then the B portion is
null, and vice versa

select * from a FULL JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

mysql 并不直接支持full join,but
总是难不到我们

select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)

 

6、事务

MySQL
事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

  • 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
  • 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
  • 事务用来管理insert,update,delete语句

一般来说,事务是必须满足4个条件(ACID):
Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

  • 1、事务的原子性:一组事务,要么成功;要么撤回。
  • 2、稳定性 :
    有非法数据(外键约束之类),事务撤回。
  • 3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
  • 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,
    innodb_flush_log_at_trx_commit选项
    决定什么时候吧事务保存到日志里。

业务与技术往往是共同前进的,2016年,我加入平安好医生,在业务快速发展的同时,我们的数据库自动化平台也得到了快速的建设和发展。

    在总结之前,先把Job的参数一一说明一下:

在Mysql控制台使用事务来操作

mysql> begin; #开始一个事务

mysql> insert into a (a) values(555);

mysql>rollback; 回滚 , 这样数据是不会写入的

当然如果上面的数据没问题,就输入commit提交命令就行;

7、索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

创建索引时,你需要确保该索引是应用在 SQL
查询语句的条件(一般作为 WHERE 子句的条件)。 

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引

创建索引:

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

CREATE INDEX indexName ON mytable(username(length));  

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定
length

修改表结构:

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定:

CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL, 

INDEX [indexName] (username(length)) 

); 

删除索引的语法:

DROP INDEX [indexName] ON mytable;

 

一、背景

    Job参数是有Submit()过程返回的binary_integer。

唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

创建索引:

创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))


修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))


创建表的时候直接指定
CREATE TABLE mytable( 

ID INT NOT NULL,  

username VARCHAR(16) NOT NULL, 

UNIQUE [indexName] (username(length)) 

); 

使用ALTER
命令添加和删除索引:

有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。


以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用 ALTER
命令添加和删除主键

主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除指定时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。

显示索引信息:

mysql> SHOW INDEX FROM table_nameG

  

mysql练习题
http://www.cnblogs.com/wupeiqi/articles/5729934.html 

python
操作mysql 

 

 

 

 

 

 

 

 

  

 

两年多的时间里,我们DBA
Team快速完成了数据库自动化、白屏化、闭环化、服务化的建设。完成了JKDB数据库自动化平台(含元数据管理、自动化部署调度系统、监控系统、备份系统、高可用和在线切换、容量趋势分析规划、校验中心等)、数据库自助查询平台、权限申请和审批平台、自助变更执行平台、流程引擎、工单系统、敏感信息探测系统等等。

    what参数是将被执行的PL/SQL代码块。

在这期间,除了偶尔故障和特殊支持之外,DBA基本不需要登录服务器去部署和操作数据。从2016年到现在,我们管理的数据库实例大概翻了3倍,但是DBA人数基本没有变化,目前是4个DBA维护了约1000+的MySQL实例、1500+Redis实例,另外还维护着若干PostgreSQL
/ Oracle / MongoDB / Hbase集群。

    next_date参数指何时将运行这个工作。写Job的时候可以不指定该值。

本文就将针对我们DBA
Team完成的数据库自动化平台构建和期间的建设思路做一些简单介绍,主要分享前期标准化构建和自动化模型搭建思路方面的部分。后续如果大家有兴趣,我可以更加深入的介绍一下自动化平台其他方面的内容。

    interval参数何时这个工作将被重执行

关于数据库标准化构建

其中Interval这个值是决定Job何时,被重新执行的关键。

2016年,当我入职公司时,大概经过了两周的熟悉,俨然发现公司数据库自动化的影子。

 

其一是标准化,标准化是自动化的重要前提。那个时候,我们这边标准化是做得比较好的,从OS的标准化到DB层的标准化都有着统一的标准。比如OS的操作系统版本、文件系统格式、磁盘挂载点、预装软件、内核参数等等,我们所有MySQL服务器基本都是一致的。

例如:有存储过程p_dosomethings,需要被不同的时间间隔执行。

这里我们是怎么做到保持一致的呢?

 

首先是我们DBA对其中一台服务器经过初始化设置和优化,比如按数据库的最优策略调整内核参数,分区和挂在磁盘,预装pt-tool
MHA Node Xtrbackup Innotop
oak-tool等数据库常用的管理软件,然后交付给运维同学进行打包镜像,之后所有交付给DBA的服务器都是按此镜像进行部署。这样一来,我们的OS服务器就非常标准化了,同时也预装了我们常用的管理工具。

declare

我们的数据库也有自己的部署标准,比如配置文件标准化,除了部分可调参数是变量,其他参数全部使用标准化模板;另外像MySQL的安装目录、数据目录、二进制日志目录、临时文件目录都有统一的标准,根据不同的实例端口来区分。

    jobno number;

当然MySQL严格要做到标准化,在未做到自动化部署之前,是比较困难的,困难的不是部署技术,而是规则意识。通常一个公司都有好多个DBA共同管理数据库,由于之前的工作习惯大家喜欢按照自己的方式来部署数据库,或者没有标准部署规则、有规则但是没有严格遵守,都是无法做到标准化的。我们是从一开始就做了标准化规则和自动化部署脚本,所以我们目前线上所有数据库的部署都是标准化的,为后续自动化平台建设打下了非常好的基础。

begin

例如,我们在管理机使用如下命令,则会在对应的IP服务器上创建一个innodb_buffer_pool等于10GB的数据库实例,端口为3306,挂载设备为fioa,版本为MySQL-5.6.28-OS7-x86_64,数据库编码为utf8:

    dbms_job.submit(

#pythonInstall_MySQL_Multi.py –ip=xx.xx.xx.xx –port=3306
–mem=10240
–device=/storage/fioa–mysql-version=MySQL-5.6.28-OS7-x86_64
–character=utf8

    jobno,

自动化创建的实例按照端口进行标准化部署,如下所示,某台服务器安装了3306、3307、3308三个端口,则部署目录如下所示:

    ‘p_dosomething’, –what

配置文件路径:

    to_date(), –next_date,可以不填

/etc/my3306.cnf

    ‘Interval时间字符串’ –interval,关键设置

/etc/my3307.cnf

    );

/etc/my3308.cnf

    commit;

数据库安装路径:

end;

/storage/fioa/mysql3306:

 

binlog

1、每分钟执行

data

    Interval => TRUNC(sysdate, ‘mi’)+1/(24*60)

mysql-error.log

2、每天定时执行

mysql-tmpdir

    例如:每天的凌晨2点执行

/storage/fioa/mysql3307:

    Interval => TRUNC(sysdate)+1+2/(24)

binlog

3、每周定时执行

data

    例如:每周一凌晨2点执行

mysql-error.log

    Interval => TRUNC(next_day(sysdate, 2))+2/24
–星期一,一周的第二天

mysql-tmpdir

4、每月定时执行

/storage/fioa/mysql3308:

    例如:每月1日凌晨2点执行

binlog

    Interval => TRUNC(LAST_DAY(SYSDATE))+1+2/24

data

5、每季度定时执行

mysql-error.log

    例如每季度的第一天凌晨2点执行

mysql-tmpdir

    Interval => TRUNC(ADD_MONTH(SYSDATE), 3),’Q’)+2/24

这样部署的数据库达到了标准化的水平,所以我们DBA只要知道IP和端口,就可以很容易地知道这个实例的所有信息,无疑是自动化的良好基石。

6、每半年定时执行

二、自动化任务平台构建

    例如:每年7月1日和1月1日凌晨2点

有了好的标准化基础,我们就开始着手构建平台了。

    Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),6)+2/24

既然作为平台,那么WEB管理界面、任务调度、API服务几个核心部分是不可以少的。下面展示一个建设初期的一个基础架构:

7、每年定时执行

EVO真人视讯 5

    例如:每年1月1日凌晨2点执行

如上图所示,自上而下,系统核心部分由3层架构组成:

    Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),12)+2/24

  • 第一层为WEB控制层;
  • 第二层为任务管理层和数据采集层,用于任何调度管理和数据的交互处理;
  • 第三层为工作模块层,用于实现各功能的功能,比如安装实例、配置Replication、配置MHA、创建数据库、授权等等,这些都是由不同的底层模块来完成,通常由一系列脚本组成。

同时系统将提供Restful API用于内部数据更新,提供HTTP
API用于外部系统对接,例如和CMDB、发布平台等平时实现数据共享和任务对接,提供消息通知功能用于发送各类报警和服务类的通知功能,提供任务上报功能用于各工作模块和WEB层的信息对接。

当然,后期我们数据库平台和中间件团队、SA团队、配置中心团队完成了很多数据和功能的对接,打造了数据库管理的闭环,例如CDMD创建好DB的资源后会通过我们的API将机器信息推送到元数据中心,我们也会调用DNS平台的服务接口来更改DNS,或者我们的平台自动化部署完数据库后会将域名、端口、授权用户密码自动推送到发布平台实现数据库自动配置,开发在配置中心申请git库时就可以同步申请数据库等等。

通过DB平台和公司其他部门的平台相互打通,减少了很多人为操作环节,实现了数据库管理闭环。

如下图所示为我们平台更加详细的架构图:

EVO真人视讯 6

系统的核心是任务调度管理层,我们任务管理的界面如下所示,可以看到每个任务都有一个任务模块名称,并实时记录任务执行状态和执行日志:

EVO真人视讯 7

三、关于模块化设计构建

在上面我们简单介绍了系统的基础架构,里面提到了底层任务模块,比如安装实例、创建主从模块等等,那么这些模块底层如何优雅地设计呢?

我们平台从开始设计时后端代码层就遵循高内聚、低耦合的设计思想进了模块化开发,这是我们后端设计的核心思想。

很多人在想,代码实现功能不就好了吗?还需要什么设计思想?这可能也就是开发与运维同学的思维差别。

我们知道运维同学常常忙于很多琐碎的事情,效率优先,也习惯于脚本化开发,可能分分钟就写一个脚本实现某个功能。但是在平台建设中,这种方式是不可取的。如果代码没有规范的思想指导,当多人协同开发的过程中,很难进行项目的管理和跟进。

我们在设计时,在遵照模块化开发思想的同时,根据任务情况,设计出了任务三层调度模式,类似堆积木方式,可以很快地完成不同需求的底层任务模块,同时可维护性可非常高。另外就是复用和解耦,模块不允许同级模块相互调用和依赖,只允许高级模块调用低级模块。

如下面所示:

EVO真人视讯 8

上面这幅图可以很好的解释底层的三级模块调用流程:

EVO真人视讯 9

  • Level
    1为底层支持模块:
    例如SSH操作模块、MySQL连接和操作模块、消息模块(短信,邮件,内部信息)、日志模块、外部接口模块(DNS变更,CDMD同步等)、元数据维护模块(meatdata)等。
  • Level
    2为基础单元模块:
    比如安装MySQL节点、配置主从、配置MHA、创建数据库、DB授权等等,这些都是二级模块,基本就是完成某一个特定功能。注意Level
    2里代码除了业务逻辑部分,其余只需要调用Level
    1的模块即可。例如下面是一个安装MySQL实例的截图,属于二级模块:

  • Level 3则为服务模块:真正经常使用的模块,都是调用Level
    2模块来进行封装的。例如在一般业务方使用数据库中,DBA至少需要安装2个实例,配置个主从复制,也需要配置MHA,当然备份和监控配置也不能少。这些工作一个DBA来完成通常大半天时间过去了。那么如果需要部署10套呢?会花费更多的时间。所以这种情况下就需要一键部署,一键通通搞定。说到这里,还有一个问题——大家大概也注意到了安装实例、创建数据库等这些单一模块在Level
    2模块都有,那么Level 3干嘛呢?其实就是调用Level
    2就可以了。如下是一键部署页面截图,DBA填写好提交任务即可,剩下的时候就可以处理其他工作了:

EVO真人视讯 10

然后我们监控上报的任务日志可以看到底层执行过程,大家可以看到任务会创建2个实例,然后配置了主从,最后配置了MHA,当然这里面还有一些元数据维护,备份和监控开关设置等等,其实在后台已经完成了。大概6分钟,完成了一个DBA半天的工作,并且保证了部署的数据库都是标准化的,不同DBA部署没有任何差异。

EVO真人视讯 11

再举另外一个场景例子,通常公司对核心大业务会做TDDL分库分表,比如十库百表、百库千表,需要部署在不同的物理机,这时候我们就开发了TDDL批量部署模块,基本就是封装并行任务调用Level
2模块的各个模块,例如创建100个数据库sharding的TDDL集群,无非就是并行调用200次安装MySQL实例的模块,然后调用100次配置主从,调用100次配置MHA,最后发个消息通知。一般手工操作需要1-2天时间的任务几十分钟就完成了。

EVO真人视讯 12

有了上述自动化任务调度平台和设计规范作为基础,我们DBA基本都很快参与进行了进行模块开发。模块开发的好处就是大家很容易上手开发,甚至之前有不会Python的同学,在简单学习了Python之后也能照猫画虎很快完成一个模块。

在大家的共同努力下,MySQL以及Redis日常部署和维护工作都实现了任务调度化管理。通常需要大家登录服务器的操作现在基本都在WEB界面端就完成了。一般除了需要登服务器定位问题和处理线上故障,基本就白屏化了数据库管理。

这样下来,对于整个公司而言效率高了,DBA不需要那么多了,数据库人为故障也少了;但对个人而言,职业工作就受到了挑战,机会也少了,所以个人的发展只能说主要是看自己,靠自己。

最后讲一点题外话,经常看到一些文章在讲数据库自动化、未来AI智能化,预测将来DBA可能会失业。这个观点我是二分之一认同的:随着很多公司的自动化越来越完善,可能需要的DBA会越来越少,但我认为DBA这个岗位在任何时候都不会被淘汰。

虽然数据库完全自动化后,难免对DBA的职业发展造成影响,但换个角度来看,留给DBA思考创新、提升自我价值的时间也更多了。其实从数据库在公司的重要性和敏感性来看,从业务向技术转换过程中,DBA作为数据库的专业评审员,发挥的作用是其他岗位所无法替代的。而未来DBA应该做的,是试着转变观念去接收一些新事物,比如可以尝试开发,参与到平台开发中,或者学习一些大数据、机器学习相关的技能,又或者更深入钻研数据库。我相信,只要自己努力,是金子总会发光的。返回搜狐,查看更多

责任编辑: