Python自动化开发学习12-MariaDB
关系型数据库主流的关系型数据库大概有下面这些：
[*]Oracle ： 甲骨文公司的企业级的数据库
[*]SQL Server ： 微软的
[*]MySQL ： 免费的数据库，现在也属于Oracle的旗下产品
[*]MariaDB ： 开源的数据库，MySQL的一个分支
[*]PostgreSQL ： 也是开源的
[*]SQLite ： 一款轻量级的数据库
[*]DB2 ： IBM的
RDBMS 术语
RDBMS(Relational Database Management System)即关系数据库管理系统，在开始之前，先了解下RDBMS的一些术语：
[*]数据库： 数据库是一些关联表的集合。.
[*]数据表： 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
[*]列： 一列(数据元素) 包含了相同的数据，例如邮政编码的数据。
[*]行： 一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
[*]冗余： 存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
[*]主键： 主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
[*]外键： 外键用于关联两个表。
[*]复合键： 复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
[*]索引： 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
[*]参照完整性： 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
MariaDB安装
直接使用yum来安装：
$ yum groupinstall mariadb mariadb-client
开启服务，以及开启自启动：
$ systemctl start mariadb $ systemctl enable mariadb
安装后建议运行一下安全加固：
$ mysql_secure_installation
几个交互式的问答，可以设置root密码。其他都可以选yes，主要是删除匿名用户登录，关闭root账号远程登录，删除test库。
如果需要被远程访问，还要开启防火墙：
$ firewall-cmd --permanent --add-service=mysql
$ firewall-cmd --reload
配置文件
/etc/my.cnf 这个文件就是配置文件，一般情况下，你不需要修改该配置文件。
如果你的数据库是单机运行的，那么建议关闭联网，具体就是添加一行配置：
在 中加一行， skip-networking=1
基本操作
太具体的例子和语句就不一个一个试了，就在下面列出常用的操作和命令简单的语法。太复杂的查询语句还是在需要的时候再上网查吧。
账号
登录数据库：
$ mysql [-u root] [-h localhost] -p
注意-p后面可以不跟密码，这样可以在之后的交互界面输入密文的密码。也可以在-p后面直接跟上明文的密码，但是中间不要用空格
-u 缺省就是root登录， -h 缺省就是登录到localhost
用户账户记录在mysql库的user表里，权限在db表里。
创建一个用户，并且设置账号权限：
> GRANT SELECT, UPDATE, DELETE, INSERT ON 库名.表名 TO 用户名@主机 INDENTIFIED BY '密码' ;
也可以赋予完全的权限，比如创建一个admin账号，赋予所有的权限：
> GRANT ALL PRIVILEGES ON *.* TO admin>
账号权限有很多，最常用的就是增删改查的操作，所有的权限可以看db表：
> USE mysql
> DESC db;
查看账号权限：
> SHOW GRANTS ;
查看有多少账号：
> SELECT user, host FROM user;
> SELECT * FROM user \G;# 或者查看全部，不过内容比较多，用\G参数按列打印
删除账户：
DEOP USER 用户名@主机;
最后注意，账号的设置不会马上生效。重启一下服务最保险，或者：
> FLUSH PRIVILEGES;
命令
以下列出了使用Mysql数据库过程中常用的命令：
[*]USE 数据库名 ： 选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
[*]SHOW DATABASES ： 列出 MySQL 数据库管理系统的数据库列表。
[*]SHOW TABLES ： 显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。
[*]SHOW COLUMNS FROM 表名 ： 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
[*]DESC 表名 ： 同上，貌似一般都用这个
[*]CREATE DATABASE 库名 CHARSET "utf8" ： 创建一个支持中文的数据库
[*]SHOW CREATE DATABASE 库名 ： 上面创建后，查看这个库的字符编码。默认是'latin1'。
[*]DROP DATABASE 库名 ： 删除数据库
[*]SHOW INDEX FROM 表名 ： 显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
操作
创建表，然后进行增删改查的操作，简单列一下：
[*]创建表 ： CREATE TABLE 表名 (表结构，主键);
[*]插入数据 ： INSERT INTO 表名 (字段名列表) VALUES (值的列表);
[*]查询数据 ： SELECT 字段名 FROM 表名;
[*]修改数据 ： UPDATE 表名 SET 字段名1=值[, 字段名2=值 ...] ;
[*]删除数据 ： DELETE FROM 表名 ; ，如果没有WHERE，所有记录都将被删除
使用下面的语句，加到SELECT语句后面，设置查询条件或者输出额：
[*]WHERE ： 查询的条件
[*]OFFSET ： 开始查询数据的偏移量，可以不用每次都从头开始查
[*]LIMIT ： 设定返回的记录数
[*]DESC ： 查询结果降序排列，默认是升序（ASC，ASC默认缺省，加不加都一样）
[*]GROUP BY ： 将数据进行分组
ALTER命令，修改数据表名或者修改数据表字段使用的命令。
[*]删除字段 ： ALTER TABLE 表名 DROP 字段名;
[*]添加字段 ： ALTER TABLE 表名 ADD 字段名 字段类型; ，新字段添加在表的末尾。
[*]加到开头：最后再加上FIRST
[*]加到指定位置：最后加上AFTER 字段名，就是插入到指定字段名的后面。
[*]FIRST 和 AFTER 关键字只作用于 ADD 子句。所以无法调整字段位置，或先 DROP 删除然后再 ADD 添加并设置位置
[*]修改字段类型 ： ALTER TABLE 表名 MODIFY 字段名 字段新类型;
[*]ALTER TABLE 表名 MODIFY 字段名 BIGINT NOT NULL DEFAULT 100; ， 设定字段类型为BIGINT，并且不能为空默认值100。如果不设置默认值，则自动设置该字段默认为 NULL。
[*]修改字段名及类型 ： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段新类型;
[*]修改字段默认值 ： ALTER TABLE 表名>
[*]删除字段默认值 ： ALTER TABLE 表名>
[*]修改表名 ： ALTER TABLE 表名 RENAME TO 新表名;
ALTER 命令不只上面这些，还可以用来创建及删除数据表的索引，先这样吧。
外键关联
先准备好数据，顺便复习前面的内容：
> CREATE DATABASE week12 CHARSET utf8;# 创建数据库
> USE week12
按照下面的表，创建表格
学生信息表（student）：
id
name
age
1
Adam
36
2
Bob
32
3
Clare
27
4
Dan
26
> CREATE TABLE student (
->> -> name VARCHAR(20) NOT NULL,
-> age TINYINT,
-> PRIMARY KEY (id)
-> );
然后插入数据：
> INSERT INTO student (name, age) VALUES ('Adam', 36);
再创建下面的这张考勤表。考勤表中的 student_id 要和学生信息表这的> 考勤表（record）：
day
student_id
checkin
late
level_early
2018-01-01
1
1
0
0
2018-01-01
2
1
0
0
2018-01-01
3
1
0
0
2018-01-02
1
1
0
0
2018-01-02
2
1
0
0
2018-01-02
3
1
0
0
> CREATE TABLE record( -> day DATE,
-> student_id INT UNSIGNED,
-> checkin BOOL,
-> late BOOL,
-> level_early BOOL,
-> PRIMARY KEY (day, student_id),
-> KEY fk_student_key (studeng_id),
-> CONSTRAINT fk_student_key FOREIGN KEY (student_id) REFERENCES studnet (id)
-> );
尝试添加记录：
> INSERT INTO record (day, student_id, checkin, late, level_early) VALUES ('2018-1-2', 3, 1 ,0, 0);
record表的主键是 (day, student_id) ，这是一个复合主键。所以日期和id都可以重复出现，但是同一日期不下不能由相同的id。
无法在record表中插入在student表中不存在的student_id，这个叫外键约束
尝试删除记录：
> DELETE FROM record WHERE day='2018-01-01' AND student_id=1;# 这条没问题
> DELETE FROM student WHERE name LIKE 'Adam';# 这条数据如果被关联了，就无法删除。
查询表的外键关联，通过查看建表的语句就能看到外键的SQL语句
> SHOW CREATE TABLE record;
然后被关联的表可以用下面的语句查询到关联关系
> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGEwhere REFERENCED_TABLE_NAME='student';
NULL 值处理
我们已经知道数据库使用 SELECT 命令及 WHERE 子句来读取数据表中的数据，但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。用下面的 IS NULL 和 IS NOT NULL。NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 也返回 false 。
为了处理这种情况，使用如下的三大运算符：
[*]IS NULL ： 当列的值是NULL，此运算符返回true。
[*]IS NOT NULL ： 当列的值不为NULL， 运算符返回true。
[*]<=> ： 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
多表查询
上面例子中的2个表，要输出一张考勤表，但是考勤表中没有name字段。想要name字段需要根据student_id到student表中查找对应的id获取。这就需要多表联合查询
> SELECT * FROM record, student WHERE record.student_id = student.id;# 也可以使用JOIN方法
或者也可以使用JOIN。另外只需要从student表中取到name字段，别的字段不需要。SELECT * 也可以修改一下：
> SELECT record.*, student.name FROM record, student WHERE record.student_id = student.id;
> SELECT record.*, student.name FROM record JOIN student ON record.student_id = student.id;
上面的2句一样。
另外JOIN其实分4种类：
[*]INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录。默认缺省 INNER 就是这个。
[*]LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
[*]RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
[*]FULL JOIN ： 没有这句命令，不直接支持，但是可以实现
多表联查的2张表不需要有外键关联。由于上面建立的2张表建立了外键关联，record表中的student_id一定是在student表中的，所以上面 JOIN 语句使用 LEFT 是不会有更多记录的。但是使用 RIGHT，会把record表中没有记录的student的name也生成一条记录。
SELECT record.*,student.name FROM record RIGHT JOIN student ON record.student_id = student.id;
间接实现FULL JOIN的方法就是做 LEFT JOIN 和 RIGHT JOIN 各做一次，然后把结果拼起来就是了：
> SELECT record.*,student.name FROM record LEFT JOIN student ON record.student_id = student.id -> UNION
-> SELECT record.*,student.name FROM record RIGHT JOIN student ON record.student_id = student.id;
FULL JOIN 知道就行了，因为貌似也没啥用。
事务
事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。再比如上面的例子，你如果要删除一个学生，还需要先删除这个学生的考勤记录，这就是2个步骤。我们希望这2个步骤可以都完成。如果完成了考勤记录的删除，但是之后删除学生的时候出现了问题，那么可以会退到整个删除过程之前的状态，既恢复之前删除的考勤记录。直白一点，就是一列的操作，所有的步骤要么都成功，要么一个都不执行。
[*]只有使用了Innodb数据库引擎的数据库或表才支持事务，默认使用的数据库引擎就是Innodb。
[*]事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
[*]事务用来管理 INSERT, UPDATE, DELETE 语句。没有 SELECT 因为并不会对表进行修改
一般来说，事务需要满足4个条件（ACID）：
[*]原子性 ： 一组事务，要么成功；要么撤回。
[*]稳定性 ： 有非法数据（外键约束之类），事务撤回。
[*]隔离性 ： 事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
[*]可靠性 ： 软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项决定什么时候吧事务保存到日志里。
操作起来很简单：
> BEGIN;# 声明开始一个事务
> INSERT INTO student (name, age) VALUES ('Frank', 18);# 执行一些操作，这里就插入一条记录
> ROLLBACK;# 回滚，如果数据是不会写入的，回到初始得状态
> COMMIT; # 提交，如果数据没有问题就执行提交而不是回滚
另外如果步骤比较多还可以设置多个临时保存点，可以进行回滚：
保存点(Savepoint) ： 事务集中的一个临时占位符，可进行回滚。
> SAVEPOINT delete1;# 设置保存点
> ROLLBACK TO delete1;# 回滚到保存点
索引
索引的建立对于数据库的高效运行是很重要的，索引可以大大提高数据的检索速度。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
上面是使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
查看索引：
> SHOW INDEX FROM student [\G];# 看不清楚，就加上\G
即时还没有创建过索引，但是依然能查看到索引信息。因为默认已经对主键做了索引了。
创建索引：
> CREATE INDEX index_name ON student(name(5));# 创建单列索引，长度可以缺省
> CREATE INDEX index_name_age ON student (name,age);# 创建联合索引，这里缺省了长度
索引也是一张表，所以要取一个索引名（‘index_name’）。然后要指定一下长度（例子中是5，也可以缺省）。如果是CHAR，VARCHAR类型，长度可以小于字段实际长度（或者不写）；如果是BLOB和TEXT类型，必须指定长度。
删除索引：
> DROP INDEX index_name ON student;# 删除索引
用ALTER添加、删除索引：
>>
>>
另外，在创建表的时候也可以指定索引。
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。要创建唯一索引，只需要加上UNIQUE这个关键字就好了：
> CREATE UNIQUE INDEX index_name ON student(name(10));# 加上UNIQUE
>>
PyMySql 模块
这是一个第三方库，需要安装。使用的时候基本都是用源生SQL语句来操作数据库。
连接查询数据库：
import pymysql
conn = pymysql.connect(host='192.168.246.134', port=3306,
user='operator', passwd='operator123',
db='week12')# 创建连接
cursor = conn.cursor()# 创建游标
effect_row = cursor.execute('SELECT * FROM student')# 执行SQL语句
print(effect_row)# 返回值是受影响的行数
print(cursor.fetchone())# 获取1条
print(cursor.fetchmany(2))# 获取多条
print(cursor.fetchall())# 获取所有
cursor.close()# 关闭游标
conn.close()# 关闭连接
这里执行SQL命令的方法excute，有2个参数。第一个是SQL语句的字符串。第二个参数上面是缺省的。
插入数据：
import pymysql
conn = pymysql.connect(host='192.168.246.134', port=3306,
user='operator', passwd='operator123',
db='week12')# 创建连接
cursor = conn.cursor()# 创建游标
effect_row = cursor.execute("INSERT INTO student (name, age) "
"VALUES ('Gina', 20)")# 执行SQL语句
effect_row = cursor.execute("INSERT INTO student (name, age) VALUES (%s, %s)",
('Helena', 21))# 变量可以作为第二个参数写成一个元组
print(effect_row)# 一次插入1行，所以返回值是1
conn.commit()# 必须提交，默认都是事务操作
cursor.close()# 关闭游标
conn.close()# 关闭连接
这里注意，默认所有的修改操作都是事务，所以执行后得提交，否则不会生效。
还可以一次插入多条数据，用 executemany 执行多条：
import pymysql
conn = pymysql.connect(host='192.168.246.134', port=3306,
user='operator', passwd='operator123',
db='week12')# 创建连接
cursor = conn.cursor()# 创建游标
student_list = [('Ivy', 21), ('Jimmy', 22), ('Kane', 23)]# 数据的列表
effect_row = cursor.executemany("INSERT INTO student (name, age) VALUES (%s, %s)",
student_list)# 把列表直接作为第二个参数
print(effect_row)# 一次插入3行，所以返回值是3
conn.commit()# 必须提交，默认都是事务操作
cursor.close()# 关闭游标
conn.close()# 关闭连接
SQLAlchemy 模块
现在已经可以使用SQL语句通过python来操作数据库了。但是我并不是专业的DBA，使用SQL语句并不熟练（复杂点的语句可能写出来，根本不能执行）。我还需要更高级的封装。
ORM介绍
全称object> ORM的优点：
[*]隐藏了数据访问细节，“封闭”的通用数据库交互是ORM的核心。他使得我们与通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
[*]ORM使我们构造固化数据结构变得简单易行。
ORM的缺点：
[*]无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。
SQLAlchemy 操作数据库
首先，这也是一个第三方库，使用前需要安装。
在Python中，最有名的ORM框架是SQLAlchemy。该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
SQLAlchemy本身无法操作数据库，其必须通过pymsql等第三方插件，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
mysql 通过 PyMySQL%60mysql+pymysql://<username>:<password>@<host>/<dbname>%5B?<options>%5D%60br/>`mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8`
Oracle 通过 cx_Oracle%60oracle+cx_oracle://user:pass@host:port/dbname%5B?key=value&key=value...%5D%60
页:
[1]