Mysql数据库与pymysql

数据库：

    1> 特点：

        -> 持久化存储

        -> 读写速度极高

        -> 保证数据的有效性

        -> 对程序支持性非常好，容易扩展

    2>关系型数据库：

        a> 关系型数据库RDBMS（Relational Database Management System）

            -> 通过表来表示关系型，建立在关系模型基础上。借助集合袋鼠等数学概念来处理数据

            -> 查看数据库排名：https://db-engines.com/en/ranking

            -> 关系型数据库的主要产品：

                - oracle： 在以前的大型项目中使用，银行、电信等

                - mysql：web时代使用最广泛的关系型数据库

                - ms sql server：在微软项目中使用

                - sqlite：轻量级数据库，主要应用在移动平台

            -> 关系型数据库核心元素：

                - 数据行（记录）- 元组

                - 数据列（字段）- 属性

                - 数据表（数据行的集合）

                - 数据库（数据表的集合）

                

        b> 结构化查询语言SQL(Structured Query Language)：

            i> SQL是一种用来操作RDBMS，可以用来操作oracle，sqlite等关系型数据库

            ii> SQL语句主要分为（不缺分大小写，一般而言关键字大写）：

                - DQL：数据库查询语言，对于数据进行查询，如select

                - DML：数据操作语言，对数据进行crud（增删改查），如insert，update，delete等

                - DCL：数据控制语言，进行授权与权限挥手，如grant、revoke

                - DDL：数据定义语言，进行数据库、表的管理等，如create、drop等

                - CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

            iii> 对于web程序员，重点是增删改查，必须熟练掌握DQL和DML，能够编写DDL对数据库、表的操作。

        c> MySQL介绍：

            i> MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司手工，Sun又被

               Oracle收购，目前属于Oracle。

            ii> 特点：

                - 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性

                - 支持多种操作系统，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、

                  NovellNetware、OpenBSD、OS/2 Wrap、Solaris等

                - 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等

                - 支持多线程，充分利用CPU资源

                - 优化的SQL查询算法，有效地提高查询速度

                - 提供多语言支持，常见的编码如GB2312、BIG5、UTF8

                - 提供TCP/IP、ODBC和JDBC等多种数据库连接途径

                - 提供用于管理、检查、优化数据库操作的管理工具

                - 大型的数据库。可以处理拥有上千万条记录的大型数据库

                - 支持多种存储引擎（InnoDB）

                - MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本

                  低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库

                - MySQL使用标准的SQL数据语言形式

                - Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统

                - 在线DDL更改功能

        

        d> mysql操作：

            i> 图形化客户端Navicat：

                -> 启动：./start_navicat

                -> 中文乱码：打开start_navicat

                -> 试用期：cd ~ -> rm -r .navicat64

                -> 创建数据库：指定数据库名，字符集(utf-8)，排序规则(generic)

                   新建表结构：指定表名，设置字段名，类型，约束

                   新建表数据

            ii> 命令行连接mysql数据库

                -> 安装服务端：

                        linux: sudo apt-get install mysql-server

                        mac: brew install mysq

                -> 启动服务端：

                        linux: sudo service mysql start

                        mac: sudo mysql.server start

                -> 查看进程中是否有mysql服务：ps aux | grep mysql

                -> 打开mysql客户端服务：

                -> 打开终端 mysql -u[username] -p[passwd]

                -> 停止/重启服务：sudo service mysql stop/restart

            iii> 数据的完整性：

                -> 为了保证数据库的高效和准确性，会给数据增加强制性的验证，包括字段约束和类型

                -> 数据类型:

                    - 原则：够用就行，尽量使用取值范围小的，节省存储空间

                    - 常用数据类型如下：

• 整数：int，bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间: date, time, datetime
• 枚举类型(enum)

                    - 特别说明的类型如下：

• decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位，整数2位
• char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
• varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
• 字符串text表示存储大文本，当字符大于4000时推荐使用
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

                    - 更全的数据类型可以参考：

                      http://blog.csdn.net/anxpp/article/details/51284106

                -> 约束：

                    - 主键primary key：规定表格必须要有主键，主键字段的值可以唯一确定记录

                    - 非空not null：此字段不允许填写空值

                    - 唯一unique：此字段的值不允许重复

                    - 默认default：当不填写此值时会使用默认值为准

                    - 外键foreign key：当为关系字段填写时，会到关联的表中查询，如果存在则填写成功，如果

                      不存在则会抛出异常。

---

数据库设计：

    1> 三范式：

        a> 第一范式（1NF）：强调的是列的原子性，即不可再分。

        

        b> 第二范式（2NF）：首先是1NF，另外包含两部分内容，一是表必须由一个主键；二是没有包含在主键的列必须

           完全依赖于主键。

        

        

        c> 第三范式（3NF）：首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖

        

    2> E-R模型：

        a> E代表entry-实体，指定从哪些方面描述对象，一个实体转换为数据库中的一个表。

        b> R表示relationship-关系，描述两个实体之间的对应规则，包含一对一，一对多，多对多。

        c> 关系也是一种数据，需要通过一个字段存储在表中。

        d> 三种关系具体操作：

            i> 一对一：A对B一对一，会在A或B中创建一个字段保存A的主键值

            ii> 一对多：A对B一对多，会在B中创建一个字段保存A的主键值

            iii> 多对多：A对B多对多，会创建一个中间表，用于保存A和B的主键，转换为表的一对多

---

pymysql：

    1> 使用过程：

            a> 创建connection

                -> conn = pymysql.connect(host=‘localhost’, port=3306,

                                           db=‘jing_dong’, user=‘root’,

                                           password=‘chuanzhi’, charset=‘utf8’)

            b> 创建cursor

               cur = conn.cursor()

               # cursor就是一个迭代器，每次fetch都会往下迭代一次。

            c> 执行查询，执行命令，获取数据，处理数据

               -> sql = ‘’’插入sql语句’’'

                  row_count = cur.execute(sql)

                  # 执行sql语句，返回值是影响的行数  

                  

               -> print(cur.fetchon())

                  for record in cur.fetchall():

                         print(record)

                

               -> # mysql中凡是对数据进行增删改，需要commit，否则数据会rollback

                  conn.commit()

                  # 默认情况下是rollback, conn.rollback()

            d> 关闭cursor

               -> cur.close()

            e> 关闭连接

               -> conn.close()

---

sql注入漏洞：

    1> 手动拼接SQL语句会造成SQL注入漏洞，为了防止此类问题，需要将sql需要的参数放到一个列表中作为excute参数。

    2> cur.execute(sql, res.group(1,2,3,4,5))

---

mysql 账户管理：

    1> 账户体系：根据权限不同，分以下几种：

        -> 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表

        -> 数据库级别账号：对特定数据库执行增删改查的所有操作

        -> 数据表级别账号：对特定表执行增删改查等所有操作

        -> 字段级别的权限：对某些表的特定字段进行操作

        -> 存储程序级别的账号：对存储程序进行增删改查的操作

    2> 主要字段说明：select host,user,authentication_string from user \G； #  

        -> Host 表示允许访问的主机

        -> User 表示用户名

        -> authentication_string 表示密码，加密后的值

    3> 创建账户&授权：grant 权限列表 on 数据库 to ‘用户名’@‘对方访问主机’ identified by ‘passwd’;

        -> 常用权限包括：create、alter、drop、insert、update、delete、select

        -> 全权限：all privileges

        -> 访问主机如果用%，代表任意主机

        -> 例：>> grant select on jing_dong.* to ‘laowang’@‘localhost’ identified by ‘chuanzhi’;

              >> flush privileges; # 建议修改权限以后都刷新一下权限

    4> 修改权限：grant 权限名称 on 数据库 to 账户@主机 with grant option;

        -> grant select,update on py8 to ‘laowang’@‘localhost’ with grant option;

    5> 修改密码：update user set authentication_string=password('密码’) where user=‘用户名’;

        -> 本质上是对mysql内的user表进行更新；

    

    6> 远程登录：mysql -u用户名 -p密码 -h 服务端ip地址

        -> 需要对配置文件修改：vim /etc/mysql/mysql.conf.d/mysqld.cnf

        -> 将bind-address：127.0.0.1的注释去掉

        -> service mysql restart 

    7> 删除账户：drop user ‘用户名’@’主机’；

---

mysql主从同步配置：

    1> 主从同步可以从一个数据库服务器复制到其他服务器，复制是异步进行的，甚至可以通过拨号断续连接。

    2> 配置要领：

        -> 在主上备份一份文件

        -> 数据还原到相同起点

        -> 将主从进行网络连接。

        -> 主服务器利用日志通过网络将用户操作分发到从上。（有时会设置响应设置，保证数据完整性）

    3> 数据热备：创建、维护和监控多个服数据库，保证数据结构不受故障影响

       数据容灾：当某个服务器出现宕机或者失效时，通过其他服务器的数据备份，可以保证数据的精确

       读写分离：主写入，从读出（只能够解决小批量的数据读写差别问题，不推荐）。

       负载均衡：大型公司一般会考虑来解决问题。