Python学习之 ---Mysql数据库的基本操作+Mysql 数据库入门

数据库基础知识

截止2019年6月数据库受欢迎排行(https://db-engines.com/en/ranking)

下面我们开始学习其中的一种关系型数据库**Mysql**.
由于版权问题,因此使用MariaDB

一:数据库安装

[root]# yum install mariadb-server  # yum 安装
[root]# systemctl start mariadb.service  # 启动服务
[root]# systemctl enable mariadb.service # 开机启动
[root]# mysql_secure_installation 为了安全设置Mysql服务
相关操作
1>mysql -u root -p # 切换数据库
#密码123 
2>查看当前mysql 
show databases;
3> use mysql # 使用mysql 
4> 查看当前的 tables 
show tables
5> 查看user 信息
desc user 
6>查看user 内信息
select host,user,password from user;
能够查看到当前user 创建的用户名,密码,% 后面的用户名表示都可以看到
7> 传入sql文件  
8> drop database test; # 移除test 文件
9> 重新导入文件
10> mysql -u root -p < test.sql  # 在入test.sql
11>show databases;    ====> use test;  # qie换sql数据库
12>show tablse;  # 查看test 表信息
13>select * from titles; #  查砍tieles表数据
14>创建新账户,且只给test.sql 相关权限,设置小权限 
grant all on test.* to 'eric01'@'%' identified by 'eric01'
# sql 语句对大小写不敏感
15> remoke all on *.* from eric01 # 移除用户eric01 的所有权限  不长用
16> Drop user eric01

SQL 语句

SQL 语句分为:

• DDL数据定义语言，负责数据库定义、数据库对象定义，由CREATE、ALTER与DROP三种语句组成
• DML数据操作语言，负责对数据库对象的操作，CRUD增删改查
• DCL数据控制语言，负责数据库权限访问控制，由 GRANT 和 REVOKE 两个指令组成
• TCL事务控制语言，负责处理ACID事务，支持commit、rollback指令

数据库关系操作

关系：在关系数据库中，关系就是二维表。

关系操作就是对表的操作。

选择（selection）：又称为限制，是从关系中选择出满足给定条件的元组。

投影（projection）：在关系上投影就是从选择出若干属性列组成新的关系。

连接（join）：将不同的两个关系连接成一个关系。

手动创建一个数据库表:

-------------------------------
-- Table structure for salaries
-- ----------------------------
DROP TABLE IF EXISTS `employees02`;
CREATE TABLE `employees02` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of titles
-- ----------------------------

Insert 语句

INSERT INTO 表名字 (列参数名,...) VALUES (相对于的参数值);
-- 向表中插入一行数据，自增字段、缺省值字段、可为空字段可以不写values括号内值要与前面括号内一一对应
# 举例:  insert into reg ( loginname,name,password) values ('tom','tom','tom')

INSERT INTO table_name SELECT ... ;
-- 将select查询的结果插入到表中

INSERT INTO table_name (col_name1,...) VALUES (value1,...) ON DUPLICATE KEY UPDATE
col_name1=value1,...;
-- 如果主键冲突、唯一键冲突就执行update后的设置。这条语句的意思，就是主键不在新增记录，主键在就更新部分字

INSERT IGNORE INTO table_name (col_name,...) VALUES (value1,...);
-- 如果主键冲突、唯一键冲突就忽略错误，返回一个警告。编程中可以捕获警告
# 主键 ip 设置了自动累加,在插入数据时可以不用给参数

Update 语句

update [IGNORE] 表名 set  参数名 = 新参数值
举例:
update reg set name = 'ben'  # 没有限定条件的更新禁止使用,这样会对整个数据表更新 
update reg set name = 'ben',password = '123'  where id = 5  # 添加限制条件更新

Delete 语句 -----基本不操作

DELETE FROM 表名 [WHERE where_definition]
-- 删除符合条件的记录
delete from reg where id = 1

Select 语句

SELECT
    [DISTINCT]
    select_expr, ...
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
       [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
       [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR UPDATE | LOCK IN SHARE MODE]]

FOR UPDATE会把行进行写锁定，这是排它锁。

查询
查询的结果成为结果集 recodset

举例:

select * from 表名 # 禁止使用,查了整个表
select * from 表名+ where  id = ' xxx'# 用主键定位查找
select emp_no from 表名  # 用参数名查找.没有用 where id = 'xx '查询快速
select emp_no as 'no' ,concat (first_name,' ',last_name )as name from employees
# 其中的as 子句为别名, as 后面加一个字符串,且as 可以不写.

Limit 语句

-- 返回5条记录
SELECT * FROM employees emp LIMIT 5;
-- 返回5条记录，偏移18条
SELECT * FROM employees as emp LIMIT 5 OFFSET 18;  # 偏移18条取 5 条,
SELECT * FROM employees as emp LIMIT 18, 5;  # 偏移18 条信息取5 条
# limit 可以配合 where  一起使用 在筛选出的结果中取出指定个数的数据

Where 子句

注意：如果很多表达式需要使用AND、OR计算逻辑表达式的值的时候，由于有结合律的问题，建议使用小括号来
避免产生错误

-- 条件查询
SELECT * FROM employees WHERE emp_no < 10015 and last_name LIKE 'P%';
SELECT * FROM employees WHERE emp_no BETWEEN 10010 AND 10015 AND last_name LIKE 'P%';
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010);
SELECT * from employees as emp  WHERE emp_no > 10005 LIMIT 10 ,2

Order by 子句

对查询结果进行排序，可以升序ASC、降序DESC。

-- 降序
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010) ORDER BY emp_no DESC;
# 默认 的ASC 可以不写

SELECT * FROM employees ORDER BY first_name LIMIT 10 ,5  
 # limit 的排序是先将数据进行排序,然后才能用limit 进行取 需要的几个.如果交换二者数据,排序将没有意义.

DISTINCT

不返回重复记录 # 会自动去重 # 与下面的group by 相类似,但是

-- DISTINCT使用
SELECT DISTINCT dept_no from dept_emp;
SELECT DISTINCT emp_no from dept_emp;
SELECT DISTINCT dept_no, emp_no from dept_emp;  

聚合函数

-- 聚合函数
SELECT COUNT(*), AVG(emp_no), SUM(emp_no), MIN(emp_no), MAX(emp_no) FROM employees;
# 结果只会出现一行,

分组查询:

使用Group by 子句,如果有条件,使用Having 子句过滤分组,聚合过的结果

-- 聚合所有
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries;
-- 聚合被选择的记录
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries WHERE emp_no < 10003;
# 上面两种写法都存在列项投影,emp_no取到的值已经没有意义了
-- 分组
SELECT emp_no FROM salaries GROUP BY emp_no;  
SELECT * FROM salaries GROUP BY emp_no;   # 分组查询,列出emp_no 列所有去重后ID 号 
SELECT emp_no FROM salaries WHERE emp_no < 10003 GROUP BY emp_no;
-- 按照不同emp_no分组，每组分别聚合
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries WHERE emp_no < 10003 GROUP
BY emp_no;
-- HAVING子句对分组结果过滤
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries GROUP BY emp_no 
HAVING AVG(salary) > 45000;  # having 后面可以加别名使用,因为取的是上面已经计算出的结果,因此可以使用别名.放在逻辑最后执行
-- 使用别名
SELECT emp_no, SUM(salary), AVG(salary) AS sal_avg, COUNT(emp_no) from salaries GROUP BY emp_no
HAVING sal_avg > 60000;
-- 最后对分组过滤后的结果排序
SELECT emp_no, SUM(salary), AVG(salary) AS sal_avg, COUNT(emp_no) from salaries GROUP BY emp_no
HAVING sal_avg > 60000 ORDER BY sal_avg;

HAVING ------ 对分组结果进行过滤

分组是将数据按照指定的字段分组,最终每组只能出来一条记录,这就带来了问题，每一组谁做代表，其实谁做代表都不合适。

如果只投影分组字段、聚合数据，不会有问题，如果投影非分组字段，显示的时候不能确定是组内谁的数据。

-- 分组
SELECT emp_no, MAX(salary) FROM salaries; -- 10001 88958
SELECT emp_no, MIN(salary) FROM salaries; -- 10001 40006

上例很好的说明了使用了聚合函数，虽然没有显式使用Group By语句，但是其实就是把所有记录当做一组，每组只能出
一条，那么一组也只能出一条，所以结果就一条。
但是emp_no就是非分组字段，那么它就要开始覆盖，所以，显示为10001。当求最大值的时候，正好工资表中10001的工
资最高，感觉是对的。但是，求最小工资的时候，明明最小工资是10003的40006，由于emp_no不是分组字段，导致最后
被覆盖为10001。
SELECT emp_no, MIN(salary) FROM salaries GROUP BY emp_no;
上句才是正确的语义,按照不同员工emp_no 工号分组,每一个人一组,每一个人有多个工资记录,按时每组只能按照人
头出一条记录。

总结:

查询顺序:

select * from xxx where 关键词函数 limit 5 ,5 
select  查找值 as 别名 [聚合函数] from 表名  group by  分组关键词   Having  过滤函数 order by 排序关键词  desc  降序排序

子查询

查询语句可以嵌套,内部查询就是子查询
子查询必须在一组小括号中
子查询中不能使用Order by ,使用也没有任何意义

-- 子查询
# 根据查到的结果到另一个表中在次匹配出正确的数据
SELECT * FROM employees WHERE emp_no in (SELECT emp_no from employees WHERE emp_no > 10015)
ORDER BY emp_no DESC;
# 将匹配的数据做为一张可查询的表提供给另一个查询
SELECT emp.emp_no, emp.first_name, gender FROM (SELECT * from employees WHERE emp_no > 10015) AS
emp WHERE emp.emp_no < 10019 ORDER BY emp_no DESC;  # 少用

连接 Join

交叉连接cross join
笛卡尔乘积，全部交叉
在MySQL中，CROSS JOIN从语法上说与INNER JOIN等同
Join会构建一张临时表

CROSS JOIN

-- 工资表有40行
SELECT * FROM salaries;
-- 员工表有20行
SELECT * FROM employees;
-- 800行
SELECT * from employees CROSS JOIN salaries;
-- 隐式连接，800行
SELECT * FROM employees, salaries; # 隐式连接之间写两张表的名字即可.
# 这里 A cross join B  结果的  A 的全部值,都与B 的第一行进行连接,然后是ALL of A JOING WITH SECOND B
# JOIN后的数据长度为len(A) *len(B)

INNER JOIN 可以省略为 join
等值连接,只选某些field 相等的元组(行) 使用ON 限制关联的结果.
自然连接,特殊的等值连接,会去掉重复的列,用得少

-- 内连接，笛卡尔乘积 800行
SELECT * from employees JOIN salaries;
SELECT * from employees INNER JOIN salaries;
-- ON等值连接 40行
SELECT * from employees JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 自然连接，去掉了重复列，且自行使用employees.emp_no = salaries.emp_no的条件
SELECT * from employees NATURAL JOIN salaries; # 自然连接.一般不用

外连接:

outer join 可以省略为join
分为左外连接,右外连接

-- 左连接
SELECT * from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no;
# A 左连 B ,拿 A 的值与B中值进行连接,若 A 与B中都存在和则保留,同时也保留 A中与B 不匹配的项,此时,B位置对应的值为Null
-- 右连接
SELECT * from employees RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 这个右连接等价于上面的左连接
SELECT * from salaries RIGHT JOIN employees ON employees.emp_no = salaries.emp_no;
# 注意在得出的结果中,Null的位置

自连接
表,自己和自己连接

select manager.* from emp manager,emp worker where manaer.empno=worker.mgr and worker.empno=1;
# 匹配员工1 的老大是谁
select manager.* from emp manager inner join emp worker on manaer.empno=worker.mgr where
worker.empno=1;

存储过程,触发器

存储过程(store procedure) 数据库系统中,一般完成特定功能的sql语句,编写成类似函数的方式,参与调用,支持流程控制语句

触发器,由事件触发的特殊的存储过程,例如insert 数据时触发.

这两种技术,虽然是数据库高级内容,性能不错,但基本很少使用
它们移植性查,使用时占用的服务器资源,排错,维护不方便,
最大的原因,不太建议把逻辑放在数据库中.

事物Transaction

InnoDB引擎，支持事务。

事务，由若干条语句组成的，指的是要做的一系列操作。

关系型数据库中支持事务，必须支持其四个属性（ACID）：

原子性,要求事物中的所有操作,不可分割,不能做了一部分操作,还剩一部分.
一致性，多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线程读写共
享资源一样不可预期，就不能保证一致性。

隔离性，就是指多个事务访问共同的数据了，应该互不干扰。隔离性，指的是究竟在一个事务处理期间，其
他事务能不能访问的问题

持久性，比较好理解，就是事务提交后，数据不能丢失。

Mysql 隔离级别

隔离性不好,事务的操作就会互相影响,带来不同严重程度的后果.
首先看看隔离性不好,带来哪些问题:

1> 更新丢失LostUpdate
事务A和B，更新同一个数据，它们都读取了初始值100，A要减10，B要加100，A减去10后更新为90，B加
100更新为200，A的更新丢失了，就像从来没有减过10一样。

2>脏读
事务A和B，事务B读取到了事务A未提交的数据（这个数据可能是一个中间值，也可能事务A后来回滚事
务）。事务A是否最后提交并不关心。只要读取到了这个被修改的数据就是脏读。

3>不可重复读Unrepeatable read
事务A在事务执行中相同查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可
以重复读。
例如，事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致了。
注意，脏读讲的是可以读到相同的数据的，但是读取的是一个未提交的数据，而不是提交的最终结果。

4>幻读Phantom read
事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。
数据集有记录增加了，可以看做是增加了记录的不可重复读。

隔离级别表:

隔离级别越高,串行化越高,数据执行效率越低,隔离级别越低,并行度越高,性能越高,
隔离级别越高,当前事物处理的中间结果对其他事务不可见程度越高.

-- 设置会话级或者全局隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
-- 查询隔离级别
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 禁用自动提交
SET AUTOCOMMIT = 0

举例:

查看当前系统隔离级别:
现有表reg ,两个xshell  A,B
select @@tx_isolation;  # mysql 默认的是可重复读级别
关闭自动提交
set autocommit = 0 ,对全部xshell 作用

更改其中一个数据
update 或者插入新的数据

再次select * from reg ; # 可以看出左右的数据是不一样的
将A 表commit 再次查看表数据, A ,B 数据不一致,此时B 的操作没有commit ,就代表没有完成
将B 表commit ,再次看表数据, A,B 数据一致了.因为B,也commit l 
举例读已提交模式:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 设置当前会话的读已提交模式,注意设置时仅针对当前模式,不要更改全局模式
更新A shell 数据
查看AB shell 数据,可以看出A 已经更新,但是B还没有
commit A ,再次查看AB的信息. A 不变,B 此时也变了.对比上面的可重复读隔离.总结不同点

SERIALIZABLE，串行了，解决所有问题

REPEATABLE READ，事务A中同一条查询语句返回同样的结果，就是可以重复读数据了。例如语句为(select *
from user)。解决的办法有：

1、对select的数据加锁，不允许其它事务删除、修改的操作

2、第一次select的时候，对最后一次确切提交的事务的结果做快照

解决了不可以重复读，但是有可能出现幻读。因为另一个事务可以增删数据。

READ COMMITTED，在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的
数据，解决了脏读，但是不能解决 不可重复读 和 幻读 的问题。因为其他事务前后修改了数据或增删了数据。

READ UNCOMMITTED，能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其他问题
都可能出现。

事务语法

START TRANSACTION或BEGIN开始一个事务，START TRANSACTION是标准SQL的语法。
使用COMMIT提交事务后，变更成为永久变更。

ROLLBACK可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样（原子性）。

SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。SET AUTOCOMMIT = 0禁用自
动提交事务。如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘。

数据仓库和数据库的区别

本质上来说没有区别，都是存放数据的地方。

但是数据库关注数据的持久化、数据的关系，为业务系统提供支持，事务支持；

数据仓库存储数据的是为了分析或者发掘而设计的表结构，可以存储海量数据。

数据库存储在线交易数据OLTP（联机事务处理OLTP，On-line Transaction Processing）；数据仓库存储历史数据
用于分析OLAP（联机分析处理OLAP，On-Line Analytical Processing）。

数据库支持在线业务，需要频繁增删改查；数据仓库一般囤积历史数据支持用于分析的SQL，一般不建议删改。

其他概念

游标

操作查询的 结果集的一种方法
可以将游标当做一个指针,指向结果集中的某一行.