9.MySQL - 《Python基础》

数据库类型(按数据模型特点分)
网状型数据库：采用记录类型为节点的网状数据模型
层次型数据库：采用层次模型模拟现实世界中按层次组织起来的事物
关系型数据库：采用二维表结构组织和管理数据，并规定了表内和表间数据的依赖关系
关系数据库是指一些相关的表和其他数据库对象的集合。对于关系数据库来说，关系就是表的同义词。

表由行和列组成(类似二维数组的结构)
列包含一组命名的属性(也称字段)
行包含一组记录，每行包含一条记录
行和列的交集称为数据项，指出了某列对应的属性在某行上的值，也称为字段值
列需定义数据类型，比如整数或者字符型的数据
MySQL数据库系统使用最常用的数据库管理语言—结构化查询语言(SQL)进行数据库管理
由于MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改
SQL语言主要是用来操作关系型数据库的一本语言，称之为结构化查询语句
SQL语句主要分为：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

MySQL的特点：
使用C和C++编写
为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
支持多种存储引擎
MySQL服务端(在Linux系统)
下载安装 sudo apt-get install mysql-server
启动服务 sudo server mysql start
下载安装命令行客户端 sudo apt install mysql-client
连接数据库
mysql -u root -p123456
-u后面跟数据库的账户名
-p密码，之间不能有空格

数据库管理
查看数据库版本 select version()；
显示当前时间 select now()；
查看所有数据库 show databases；
创建数据库 create database 数据库名 charset=utf8；
切换数据库 use 数据库名
查看当前正在使用哪个数据库 select database()；
删除数据库 drop database 数据库名

数据表设计：ER图、表的主键、字段、数据类型、约束、表之间关系的设计
E-R模型：实体-关系模型，主要用于定义数据的存储需求，该模型已经广泛用于关系数据库设计中。E-R模型由实体、属性和关系三个基本要素构成。
主键
实体间的关系与外键
约束约束类型：NOT NULL UNIQUE PRIMARYKEY FOREIGN KEY CHECK
查看当前数据库中的表：show tables；
创建表：create table 表名（
id int unsigned auto_increment primary key not null，
name varchar(10) not null，
is_delete bit(1) not null default 0
）；
删除表：drop table表名；
comment 注释
查看创建表的sql语句 show create table 表名；
添加字段 alter table 表名 add 列名类型
删除字段 alter table 表名 drop 字段名字
alter table 表名 modify 只修改类型及约束
alter table 表名 change 修改字段名

Navicat for MySQL 数据库管理和开发工具

基本查询语句
decs表名；查询表相关信息
select * from 表名；查询表中所有内容
select 字段1，字段2 from 表名；指定字段查询
插入数据
全列插入 insert into 表名 values(…) 空字段用0占位主键自增也要用0占位
部分插入 insert into 表名[字段1，字段2] values(值1，值2)；
修改数据
update 表名 set 字段=xx where 字段=xx；
删除数据
delete from 表名 where 字段=xx；
备份数据库
备份数据库的所有表的数据
mysqldump -uroot -p 数据库名 > 文件名
恢复数据库
恢复数据库之前要先手动创建库
mysql -uroot -p 新数据库名 < python.sql
条件查询
where 条件限定
模糊查询
like 表示模糊查询
% 表示任意多个字符
_表示一个字符
rlike 可以匹配正则
in 包含在里面的
如果需要匹配%本身，那么则需要使用%%
between 查询 between and 表示一段区间
null值判断：要查询为null值的使用is
排序 order by 字段[desc/asc]
desc表示降序(从大到小排序)
asc表示升序，默认排序规则(从小到大排序)

聚合函数
常见函数
count统计个数
max计算最大值
min计算最小值
数学函数
sum求和
avg求平均数
round函数
时间函数
substr函数
数学函数
mod(n，m) 即% 取模
floor(x) 向下取整
ceiling(x) 向上取整
round(x,d)四舍五入到最近的整数 d保留位数
时间函数
select now() 显示当前日期及时间
curdate()当前日期
curtime()当前时间
字符串函数
mysql
substr()函数用法：substr(string字符串，start起始位置，length长度)；截取字符串
left(str，len)函数返回字符串str的最左面len个字符
right(str，len)函数返回字符串str的最右面len个字符
分组查询
group by 字段以xx字段作为分组依据分组
as取别名
分组后条件筛选：having函数
limit分页语法：select from 表名 limit start，count
连接查询 mysql三种连接查询
内连接查询：查询的结果为两个表匹配到的数据，两个表都能匹配上的数据将返回给结果集
语法：select from 表1 inner join 表2 on 表1.列=表2.列
右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
语法：select * from 表1 right join 表2 on 表1.列=表2.列
左连接查询：类似于右连接
子查询：在一个select语句中嵌入了另外一个select语句，嵌入的这个select语句就是子查询语句。子查询是辅助主查询的，充当数据源或者充当条件。子查询是一条独立的语句。
子查询四种类型：
1.标量子查询一行一列单个值
2.列级子查询一行多列
3.行级子查询多行一列
4.表级子查询做数据源
可用于子查询的关键字有4个(in、all、any、some)
in：符合列子查询里面1个
any、some：功能相同，表示任意一个如：where 列=any(列子查询)
all：等于里面所有，如where 列=all(列子查询)
保存查询结果
语句格式：insert into 表名(列1、列2) select …
↑将查询到的结果直接保存到表里
合并查询
union all 将两次查询的结果集合并到一起显示
union 将两个查询的结果集先去重后合并到一起显示
distinct 去重

用户与权限管理
创建用户：create user ‘用户名’@’ip地址’ identified by ‘密码’
授权操作：grant 权限 on 数据库 to ‘用户名’@’ip地址’
MySQL中权限有create\alter\drop\insert\update\delete\select等
如果要分配所有权限，使用：all privileges
flush privileges权限立即生效
查看用户权限 show grants for 用户名
回收权限 revoke select on . from ‘用户’@’%’
修改密码普通用户修改自己的密码无需进到数据库
MySQL admin -u用户名 -p密码新密码
root账号修改普通用户密码，修改MySQL.user表
update MySQK.user set authentication_string=password() (新密码) where user=’用户名’
flush privileges
删除用户 drop user ‘用户名’@’主机’或进入MySQL库 delete from user where user=’用户名’

事务：
工作单元，是由一个或多个SQL语句所组成的操作序列。数据库中通过事务来保证数据的一致性
begin rollback commit
MySQL默认是自动提交的，即提交一个sql，直接执行。可以通过set autocommit=0禁止自动提交

视图：
一张虚拟的表，这个表的数据结构和数据是由select语句来指定的，不会生成真实的文件，本质上就是对查询的封装。
创建视图：create view 视图名称 as select 语句；
查看视图：查看表会将所有的视图也列出来
show tables;
show TABLE status;
删除视图：drop view 视图名称
调用视图：select * from 视图名

索引：
类似目录，MySQL中常见索引：主键、唯一、普通、全文、组合索引。
alter table 表名 add primary key(列名); 主键
alter table 表名 add unique(列名); 唯一
alter table 表名 add index 索引名称(列名); 普通
alter table 表名 add full text(列名); 全文，尽量不适用
查看索引 show index from 表名;
删除索引 drop index 索引名 on 表名;

存储过程：一条或多条SQL语句的集合
语法：
delimiter//
creat procedure 存储过程的名称(参数列表)
begin
sql语句
end
//
delimiter;
查看创建的存储过程：所有存储过程和函数，都存储在mysql数据库下的proc表中
show procedure status;
调用存储过程 call 存储过程名()
删除存储过程 drop procedure 存储过程名；
存储过程中的变量：
声明变量 declare 临时变量@变量名(直接使用)
语法：declare variable_name datetype default default-value;
设置变量值 set 变量名=值;
select into
条件语句
if语句
if then
end if
if
else
end if
if
elseif
else
endif
while语句
while do
end while

函数
字符串函数
select ascii();
select char();
select concat( , );
select length();
left(str,len)
right(str,len)
substring(str,pos,len)截取字符串
去除空格 ltrim(str) rtrim(str) trim(方向 remstr from str) 其中方向有both leading trailing 两侧左右
space(n)
替换字符串 replace(str,fromstr,tostr)
大小写转换函数 lower(str) upper(str)
日期时间函数
获取子值 year(date) month(date) day(date) hour(date) minute(time) second(time)

创建自定义函数
语法：
delimiter
create function 函数名(参数列表) returns 返回类型
begin
sql语句
end

delimiter;

Python DB-API数据库应用编程接口
PyMySQL:一个在Python3.x版本中用于连接MySQL服务器的一个实现库
pip install pymysql
连接数据库：
创建connection对象，其拥有的方法：
close关闭连接 commit提交 rollback回滚 cursor返回cursor对象
from pymysql import *
conn=connect(host=’数据库主机地址’,user=’数据库账号’,password=’数据库密码’,database=’连接的数据库名’,port=mysql端口号,charset=’utf8’)
创建游标对象，可以利用这个对象进行数据库的操作
cur=conn.cursor()#用于执行sql语句并获得结果
cursor对象拥有的方法
close()关闭cursor对象
execute(opration[,parameters])执行语句，返回受影响的行数
fetchone()获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
执行sql语句可以参数化，sql语句中使用%s占位，之后赋值。
cur.execute(sql,[0,1])