MYSQL 数据库

1 数据存储：结绳记事，甲骨石刻，图书。

缺点：不易保存，备份困难，查找不便

2 现代化手段----文件

特点：使用简单，例如python中的open可以打开文件，用read/write对文件进行读写，close关闭文件

缺点：对于数据容量较大的数据，不能够很好的满足，而且性能较差不易扩展

3 现代化手段----数据库

数据库就是一种特殊的文件，其中存储着需要的数据

特点：持久化存储，读写速度极高，保证数据的有效性，对程序支持性非常好，容易扩展

RDBMS（关系型数据库）

Relational Database Management System

通过表来表示关系型

当前主要使用两种类型的数据库：关系型数据库、非关系型数据库，本部分主要讨论关系型数据库，对于非关系型数据库会在后面学习

所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

关系型数据库核心元素

数据行(记录)，数据列(字段)，数据表(数据行的集合)，数据库(数据表的集合)

SQL

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库

SQL语句主要分为：

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

DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete

TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback

DCL：数据控制语言，进行授权与权限回收，如grant、revoke

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

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

对于web程序员来讲，重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它语言如TPL、DCL、CCL了解即可

SQL 是一门特殊的语言,专门用来操作关系数据库

不区分大小写

MySQL 简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品

特点

使用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等多种数据库连接途径
提供用于管理、检查、优化数据库操作的管理工具
大型的数据库。可以处理拥有上千万条记录的大型数据库
支持多种存储引擎
MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库
MySQL使用标准的SQL数据语言形式
Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
在线DDL更改功能
复制全局事务标识
复制无崩溃从机
复制多线程从机

开源免费不要钱使用范围广,跨平台支持性好,提供了多种语言调用的 API

是学习数据库开发的首选

服务器端安装

安装服务器端：在终端中输入如下命令，回车后，然后按照提示输入

sudo apt-get install mysql-server

当前使用的ubuntu镜像中已经安装好了mysql服务器端，无需再安装，并且设置成了开机自启动
服务器用于接收客户端的请求、执行sql语句、管理数据库
服务器端一般以服务方式管理，名称为mysql
启动服务

sudo service mysql start

查看进程中是否存在mysql服务

ps ajx|grep mysql

停止服务

sudo service mysql stop

重启服务

sudo service mysql restart

配置

配置文件目录为/etc/mysql/mysql.cnf

进入conf.d目录，打开mysql.cnf，发现并没有配置
进入mysql.conf.d目录，打开mysql.cnf，可以看到配置项

主要配置项如下

bind-address表示服务器绑定的ip，默认为127.0.0.1

port表示端口，默认为3306

datadir表示数据库目录，默认为/var/lib/mysql

general_log_file表示普通日志，默认为/var/log/mysql/mysql.log

log_error表示错误日志，默认为/var/log/mysql/error.log

客户端

客户端为开发人员与dba使用，通过socket方式与服务端通信，常用的有navicat、命令行mysql

图形化界面客户端navicat

可以到Navicat官网下载
将压缩文件拷贝到ubuntu虚拟机中，放到桌面上，解压

tar zxvf navicat112_mysql_cs_x64.tar.gz

进入解压的目录，运行如下命令

./start_navicat

问题一：中文乱码

解决：打开start_navicat文件

将export LANG="en_US.UTF-8"改为export LANG="zh_CN.UTF-8"

问题二：试用期
解决：删除用户目录下的.navicat64目录

cd ~ rm -r .navicat64

命令行客户端

在终端运行如下命令，按提示填写信息

sudo apt-get install mysql-client

当前使用的ubuntu镜像中已经安装好了mysql客户端，无需再安装
详细连接的命令可以查看帮助文档

mysql --help

最基本的连接命令如下，输入后回车

mysql -u root -pmysql

数据完整性

一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

数据类型

可以通过查看帮助文档查阅所有支持的数据类型
使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
常用数据类型如下：
    整数：int，bit
    小数：decimal
    字符串：varchar,char
    日期时间: date, time, datetime
    枚举类型(enum)
特别说明的类型如下：
    decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
    char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
    varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
    字符串text表示存储大文本，当字符大于4000时推荐使用
    对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

约束

主键primary key：物理上存储的顺序
非空not null：此字段不允许填写空值
惟一unique：此字段的值不允许重复
默认default：当不填写此值时会使用默认值，如果填写时以填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

创建数据库

查看数据

退出登录

quit 和 exit或ctrl+d

登录成功后，输入如下命令查看效果

查看版本：select version();
显示当前时间：select now();

数据库

查看所有数据库

show databases;

使用数据库

use 数据库名;

查看当前使用的数据库

select database();

创建数据库

create database 数据库名 charset=utf8;
例：
create database python charset=utf8;

删除数据库

drop database 数据库名;
例：
drop database python;

数据表

查看当前数据库中所有表

show tables;

查看表结构

desc 表名;

创建表

auto_increment表示自动增长

例：创建班级表

create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);

例：创建学生表

create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
)

修改表-添加字段

alter table 表名 add 列名 类型;
例：
alter table students add birthday datetime;

修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;
例：
alter table students modify birth date not null;

修改表-删除字段

alter table 表名 drop 列名;
例：
alter table students drop birthday;

删除表

drop table 表名;
例：
drop table students;

查看表的创建语句

show create table 表名;
例：
show create table classes;

增删改查(curd)

curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）

查询基本使用

查询所有列

select * from 表名;
例：
select * from classes;

查询指定列

可以使用as为列或表指定别名

select 列1,列2,... from 表名;
例：
select id,name from classes;

增加

格式:INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准
全列插入：值的顺序与表中字段的顺序对应

insert into 表名 values(...) 例： insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');

部分列插入：值的顺序与给出的列顺序对应

insert into 表名(列1,...) values(值1,...) 例： insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');

上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信

全列多行插入：值的顺序与给出的列顺序对应

insert into 表名 values(...),(...)...; 例： insert into classes values(0,'python1'),(0,'python2');

insert into 表名(列1,...) values(值1,...),(值1,...)...; 例： insert into students(name) values('杨康'),('杨过'),('小龙女');

修改

格式: UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]...[where 条件判断]

update 表名 set 列1=值1,列2=值2... where 条件
例：
update students set gender=0,hometown='北京' where id=5;

删除

DELETE FROM tbname [where 条件判断]

delete from 表名 where 条件
例：
delete from students where id=5;

逻辑删除，本质就是修改操作

update students set isdelete=1 where id=1;

（解释）逻辑删除

对于重要数据，并不希望物理删除，一旦删除，数据无法找回
删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0
对于非重要数据，可以进行物理删除
数据的重要性，要根据实际开发决定

备份

运行mysqldump命令

mysqldump –uroot –p 数据库名 > python.sql;

按提示输入mysql的密码

恢复

连接mysql，创建新的数据库
退出连接，执行如下命令

mysql -uroot –p 新数据库名 < python.sql

根据提示输入mysql密码

数据库设计

关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步
在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系
设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成
现阶段不需要独立完成数据库设计，但是要注意积累一些这方面的经验

三范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式(Normal Form)
目前有迹可寻的共有8种范式，一般需要遵守3范式即可
◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。

    考虑这样一个表：【联系人】（姓名，性别，电话） 如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。

◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

    考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。 因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。

    可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

    考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。 其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。 通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。 *第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

E-R模型

E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多
关系也是一种数据，需要通过一个字段存储在表中
实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值

实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

示例

设计两张表：班级表、学生表
班级表classes
    id
    name
    isdelete
学生表students
    id
    name
    birthday
    gender
    clsid
    isdelete

扩展阅读

58到家网站 -----数据库30条军规解读原创： 58沈剑架构师之路 2017-02-15

军规适用场景：并发量大、数据量大的互联网业务

军规：介绍内容

解读：讲解原因，解读比军规更重要

一、基础规范

（1）必须使用InnoDB存储引擎

解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

（2）必须使用UTF8字符集

解读：万国码，无需转码，无乱码风险，节省空间

（3）数据表、数据字段必须加入中文注释

解读：N年后谁tm知道这个r1,r2,r3字段是干嘛的

（4）禁止使用存储过程、视图、触发器、Event

解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

（5）禁止存储大文件或者大照片

解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

二、命名规范

（6）只允许使用内网域名，而不是ip连接数据库

（7）线上环境、开发环境、测试环境数据库内网域名遵循命名规范

业务名称：xxx

线上环境：dj.xxx.db

开发环境：dj.xxx.rdb

测试环境：dj.xxx.tdb

从库在名称后加-s标识，备库在名称后加-ss标识

线上从库：dj.xxx-s.db

线上备库：dj.xxx-sss.db

（8）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用

（9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

三、表设计规范

（10）单实例表数目必须小于500

（11）单表列数目必须小于30

（12）表必须有主键，例如自增主键

解读：

a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用

b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率

c）无主键的表删除，在row模式的主从架构，会导致备库夯住

（13）禁止使用外键，如果有外键完整性约束，需要应用程序控制

解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

四、字段设计规范

（14）必须把字段定义为NOT NULL并且提供默认值

解读：

a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识

d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

（15）禁止使用TEXT、BLOB类型

解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

（16）禁止使用小数存储货币

解读：使用整数吧，小数容易导致钱对不上

（17）必须使用varchar(20)存储手机号

解读：

a）涉及到区号或者国家代号，可能出现+-()

b）手机号会去做数学运算么？

c）varchar可以支持模糊查询，例如：like“138%”

（18）禁止使用ENUM，可使用TINYINT代替

解读：

a）增加新的ENUM值要做DDL操作

b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？

五、索引设计规范

（19）单表索引建议控制在5个以内

（20）单索引字段数不允许超过5个

解读：字段超过5个时，实际已经起不到有效过滤数据的作用了

（21）禁止在更新十分频繁、区分度不高的属性上建立索引

解读：

a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能

b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

（22）建立组合索引，必须把区分度高的字段放在前面

解读：能够更加有效的过滤数据

六、SQL使用规范

（23）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

解读：

a）读取不需要的列会增加CPU、IO、NET消耗

b）不能有效的利用覆盖索引

c）使用SELECT *容易在增加或者删除字段后出现程序BUG

（24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

解读：容易在增加或者删除字段后出现程序BUG

（25）禁止使用属性隐式转换

解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）

（26）禁止在WHERE条件的属性上使用函数或者表达式

解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

（27）禁止负向查询，以及%开头的模糊查询

解读：

a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

b）%开头的模糊查询，会导致全表扫描

（28）禁止大表使用JOIN查询，禁止大表使用子查询

解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

（29）禁止使用OR条件，必须改为IN查询

解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

（30）应用程序必须捕获SQL异常，并有相应处理

总结：大数据量高并发的互联网业务，极大影响数据库性能的都不让用，不让用哟。

==【完】==

自转到58到家，负责过较长一段时间DBA部门，搭建团队，讨论流程，讨论规范，讨论自动化，尝试平台化，故希望沉淀一些东西。

python 人工智能基础 14 day(上)

MYSQL 数据库

SQL

MySQL 简介

客户端

命令行客户端

数据完整性

数据类型

创建数据库

查看数据

数据库

数据表

增删改查(curd)

查询基本使用