Fork me on GitHub

python自动化编程-第十二天 MySQL

python自动化编程-第十二天 MySQL

目录

数据库介绍

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

  1. 数据以表格的形式出现
  2. 每行为各种记录名称
  3. 每列为记录名称所对应的数据域
  4. 许多的行和列组成一张表单
  5. 若干的表单组成database

RDMBS 术语

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
  • 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

Mysql数据库

Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

  • Mysql是开源的，所以你不需要支付额外的费用。
  • Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
  • MySQL使用标准的SQL数据语言形式。
  • Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
  • Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
  • MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
  • Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

MySql安装及部署

windows

Window上安装Mysql相对来说会较为简单，你只需要载 MySQL 下载中下载window版本的mysql安装包，并解压安装包。

双击 setup.exe 文件，接下来你只需要安装默认的配置点击"next"即可，默认情况下安装信息会在C:\mysql目录中。

接下来你可以通过"开始" =》在搜索框中输入 " cmd" 命令 =》 在命令提示符上切换到 C:\mysql\bin 目录，并输入一下命令：

mysqld.exe --console

如果安装成功以上命令将输出一些mysql启动及InnoDB信息。

Centos上

Linux平台上推荐使用RPM包来安装Mysql,MySQL AB提供了以下RPM包的下载地址：

  • MySQL - MySQL服务器。你需要该选项，除非你只想连接运行在另一台机器上的MySQL服务器。
  • MySQL-client - MySQL 客户端程序，用于连接并操作Mysql服务器。
  • MySQL-devel - 库和包含文件，如果你想要编译其它MySQL客户端，例如Perl模块，则需要安装该RPM包。
  • MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用MySQL。
  • MySQL-bench - MySQL数据库服务器的基准和性能测试工具。

从mysql5.6开始使用的是mariadb

建议通过yum来安装mariadb

yum install mariadb-server

如果可以的话修改数据库配置文件

datadir = /mydata/data
innodb_file_per_table = ON
skip_name_resolve = ON

然后使用命令mysql_secure_installation来设置root密码

MySql命令

命令行交互式客户端程序：mysql

mysql
mysql [OPTIONS] [database]

常用选项：
-uUSERNAME：用户名，默认为root；
-hHOST：远程主机（即mysql服务器）地址，默认为localhost;
-p[PASSWORD]：USERNAME所表示的用户的密码； 默认为空；
-S，--socket=name：The socket file to use for connection.多实例时使用；

注意：mysql的用户账号由两部分组成：'USERNAME'@'HOST'; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；
HOST的表示方式，支持使用通配符：
%：匹配任意长度的任意字符；
172.16.%.%, 172.16.0.0/16
_：匹配任意单个字符；

-Ddb_name：连接到服务器端之后，设定其处指明的数据库为默认数据库；
-e 'SQL COMMAND;'：连接至服务器并让其执行此命令后直接返回；

客户端交互式命令：本地执行

mysql> help
\u db_name：设定哪个库为默认数据库
\q：退出；
\d CHAR：设定新的语句结束符；
\g：语句结束标记；
\G：语句结束标记，结果以竖排方式显式；
\s：status
\c：取消语句，相当于bash中的CTRL+c

服务端命令：通过mysql连接发往服务器执行并取回结果；
DDL， DML， DCL

注意：每个语句必须有语句结束符，默认为分号(😉

获取命令帮助：
mysql> help KEYWORD

DDL：数据定义语言，主要用于管理数据库组件，例如表、索引、视图、用户、存储过程：CREATE、ALTER、DROP

DML：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；INSERT， DELETE， UPDATE， SELECT

DDL语句

数据库管理

创建：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

查看支持的所有字符集：SHOW CHARACTER SET
查看支持的所有排序规则：SHOW COLLATION

修改：

ALTER {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

删除：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看：

SHOW DATABASES LIKE ’‘;

表管理

创建：

CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]

create_defination:
字段：col_name data_type
键：
PRIMARY KEY (col1, col2, ...)
主键
UNIQUE KEY (col1, col2,...)
惟一键，必须跟在数据类型后面；
FOREIGN KEY (column)
外键
索引：
KEY|INDEX [index_name] (col1, col2,...)

示例：

或者
create table students（id INT UNSIGNED NOT NULL ，name CHAR（30） NOT NULL，age TINYINT UNSIGNED，gender ENUM（'f','m'），PRIMARY KEY（id，name））;

table_options：
ENGINE [=] engine_name

查看数据库支持的所有存储引擎类型：
mysql> SHOW ENGINES;

查看某表的存储引擎类型：
mysql> SHOW TABLES STATUS [LIKE 'tbl_name']

修改：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

alter_specification:
字段：
添加：ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]
删除：DROP [COLUMN] col_name
修改：
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
键：
添加：ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1, col2,...)
删除：
主键：DROP PRIMARY KEY
外键：DROP FOREIGN KEY fk_symbol
索引：
添加：ADD {INDEX|KEY} [index_name] (col1, col2,...)
删除：DROP {INDEX|KEY} index_name
（可以删除UNIQUE的键）
表选项：（不建议修改表的引擎）
ENGINE [=] engine_name

查看表上的索引的信息：
mysql> SHOW INDEXES FROM tbl_name;

删除：（很危险）

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

表的引用方式：

相对路径：tbl_name
绝对路径：db_name.tbl_name

第二种创建方式：

复制表结构；

第三种创建方式：

复制表数据；

索引管理：

索引是特殊的数据结构；

索引：必须要有索引名称；

创建：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)
UNIQUE：可省略，创建惟一键即可；
FULLTEXT：全文索引；
只有Memory支持HASH索引，所以一般是能用树状索引；

删除：

DROP INDEX index_name ON tbl_name

DML语句

INSERT INTO：

INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...
最后的每个小括号表示一行内的所有数组；
注意：
字符型：都必须要用引号；
数值型：不能用引号；
示例：
for i in {1..100}; do AGE=$[$tandom%100]; mysql -e "insert mydb.students (id,name,age) values ($i,"stu$i",$AGE);";done

SELECT：

SELECT语句的执行流程：
FROM Clause --> WHERE Clause（选择行） --> GROUP BY --> HAVING Clause --> ORDER BY --> SELECT（挑选符合条件的字段） --> LIMIT

单表查询：

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

DISTINCT: 数据去重；
SQL_CACHE: 显式指定存储查询结果于缓存之中；
SQL_NO_CACHE: 显式查询结果不予缓存；

mysql>show global variables link "query%";
query_cache_type的值为'ON'时，查询缓存功能打开；
SELECT的结果符合缓存条件即会缓存，否则，不予缓存；
显式指定SQL_NO_CACHE，不予缓存；
query_cache_type的值为'DEMAND'时，查询缓存功能按需进行；
显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存；

查看缓存命中：
mysql>show global status link 'Qcache'；

查看查询的次数：
mysql> show global status 'Com_se%'；

字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...

WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式；

算术操作符：+, -, *, /, %
比较操作符：=, !=、 <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
LIKE:
%: 任意长度的任意字符；
_：任意单个字符；
RLIKE：匹配字符串可用正则表达式书写模式；可能无法使用索引；
REGEXP：匹配字符串可用正则表达式书写模式；可能无法使用索引；

逻辑操作符：

NOT
AND
OR
XOR：异或，二者不同则为真，相同则为假；

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算：
avg(), max(), min(), count(), sum()

mysql> select avg(age),Gender from students group by Gender;
首先from，然后group by，最后执行select；

HAVING: 对分组聚合运算后的结果指定过滤条件；

mysql> select avg(age) as AAge,Gender from students group by Gender having AAge>20;

ORDER BY: 根据指定的字段对查询结果进行排序；
升序：ASC
降序：DESC

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制；
略过offset，然后取row_count行；

对查询结果中的数据请求施加“锁”：
FOR UPDATE: 写锁，独占锁，排他锁；
LOCK IN SHARE MODE: 读锁，共享锁

多表查询：

交叉连接：笛卡尔乘积；
内连接：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接
自连接：需要给一个表创建2个别名；

select s.Name,t.Name from students as s,teacher as t where s.teacherID=t.TID;

外连接：可以将NULL的字段保留；
左外连接：左表中每一项都要出现，而右表中没有的字段则留空表示；
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：右表中每一项都要出现，而左表中没有的字段则留空表示；
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询：在查询语句嵌套着查询语句

基于某语句的查询结果再次进行的查询

用在WHERE子句中的子查询：
(1) 用于比较表达式中的子查询；子查询仅能返回单个值；
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
(2) 用于IN中的子查询：子查询应该单键查询(只能查询一个字段)并返回一个或多个值从构成列表；
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
(3) 用于EXISTS；存在性判断

用于FROM子句中的子查询；当做虚表来使用；
使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
示例：
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

联合查询：UNION，两个表的字段类型一样时使用；

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

DELETE：只能删除一整行，若要修改字段内容，则使用update更新表内容；

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

(1) DELETE FROM tbl_name WHERE where_condition
(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]
示例：
DELETE FROM STUDENTS ORDER BY age DESC LIMIT 20；

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
示例：
UPDATE students SET age=age-5 ORDER BY id DESC LIMIT 10；
UPDATE students SET age=age-15 WHERE name NOT LIKE 'stu%';

用户账号及权限管理：

用户账号：'username'@'host'
host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接；
表示方式：IP，网络地址、主机名、通配符(%和_)；

禁止检查主机名：my.cnf
[mysqld]
skip_name_resolve = ON

创建用户账号：

CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

立即生效：

FLUSH PRIVILEGES;

删除用户账号：

DROP USER 'user'@'host' [, user@host] ...

授权：

权限级别：管理权限、数据库、表、字段、存储例程；

GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

priv_type： ALL [PRIVILEGES]
db_name.tbl_name：
.：所有库的所有表；
db_name.*：指定库的所有表；
db_name.tbl_name：指定库的特定表；
db_name.routine_name：指定库上的存储过程或存储函数；

[object_type]
TABLE
FUNCTION：函数
PROCEDURE：存储过程
示例：
GRANT select ON mydb.students TO 'test'@'172.16.%.%';
GRANT insert ON mydb.students TO 'test'@'172.16.%.%';

查看指定用户所获得的授权：

SHOW GRANTS FOR 'user'@'host'

查看当前用户所获得的授权：

SHOW GRANTS FOR CURRENT_USER;

回收权限：

REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';
示例：
REVOKE insert on mydb.students form 'test'@'172.16.%.%';

注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中；
(1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效；
(2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可；

加固mysql服务器，在安装完成后，运行mysql_secure_installation命令；

输入当前密码
是否修改密码
是否删除匿名用户
是否允许管理员远程登录
是否移除测试数据库
是否重读授权表

Mysql 连接（left join, right join, inner join ,full join）

我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
Suppose you have two tables, with a single column each, and data as follows:　　

A    B
-    -
1    3
2    4
3    5
4    6

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;
 
a | b
--+--
3 | 3
4 | 4
其实就是只显示2个表的交集

Left join

A left join will give all rows in A, plus any common rows in B.

select * from a LEFT JOIN b on a.a = b.b;
 
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right join

A right join will give all rows in B, plus any common rows in A.

select * from a RIGHT JOIN b on a.a = b.b;
 
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa

select * from a FULL JOIN b on a.a = b.b;
 
 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5
```　　 

mysql 并不直接支持full join,but 总是难不到我们

```SQL
select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)

MySql数据类型

MySql数据类型一共有四种:

字符型
数值型
日期时间型
内建类型

字符型：

CHAR, BINARY：定长数据类型；
VARCHAR, VARBINARY：变长数据类型；可以指定最大存储空间，但是用多少空间就给多少，因此需要结束符（占据一个空间）；
TEXT：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT，2^8， 2^16， 2^24， 2^32 字符个数，不区分大小写；
BLOB： TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB，2^8， 2^16， 2^24， 2^32 字符个数
ENUM, SET

数值型：

精确数值型：

整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT，2^8， 2^16， 2^24， 2^32， 2^64，范围内数字；
十进制型：DECIMAL

近似数值型

浮点型：FLOAT、DOUBLE

BIT

日期时间型：

DATE，3个字节
TIME，3个字节
DATETIME，8个字节，用于表明哪个是时间，哪个是日期；
TIMESTAMP，相抵时间计时法；
YEAR(2), YEAR(4)，

CHAR：1个字节，255个字符
VARCHAR：2个字节，有两个结束符；

字符类型修饰符：定义在字段类型之后；

NOT NULL: 非空约束；必须填入字符
NULL:不用单独写明；允许为空，默认值；
DEFAULT 'STRING': 指明默认值；
CHARACTER SET ''：使用的字符集；
COLLATION：使用的排序规则

mysql> SHOW CHARACTER SET;
mysql> SHOW COLLATION;

默认会从表或者数据继承字符集和排序规则；一般都是在数据库上定义的字符集；

整型类型修饰符：

NOT NULL：非空约束；必须填入字符
NULL：不用单独写明；允许为空，默认值；
DEFAULT NUMBER：指明默认值，

AUTO_INCREMENT：自动增长修饰符；
UNSIGNED：无符号，字段修饰符；
PRIMARY KEY|UNIQUE KEY
NOT NULL

mysql> SELECT LAST_INSERT_ID();
自动增长到哪个数据，一般一个表只有一个字段是自增的；

日期时间型修饰符：

NOT NULL
NULL
DEFAULT

内建类型SET和ENUM的修饰符：

NOT NULL
NULL
DEFAULT

ENUM不能排序和比较；

MySQL NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。

主键和外键

外键，一个特殊的索引，用于关键2个表，只能是指定内容　　

mysql> create table class(
    -> id  int not null primary key,
    -> name char(16));
Query OK, 0 rows affected (0.02 sec)
 
 
CREATE TABLE `student2` (
  `id` int(11) NOT NULL,
  `name` char(16) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_class_key` (`class_id`),
  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
)

此时如果class 表中不存在id 1,student表也插入不了，这就叫外键约束
mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
 
 
 
mysql> insert into class(id,name) values(1,"linux");
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
Query OK, 1 row affected (0.00 sec)
 
 
＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的
mysql> delete from class where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

Mysql 连接（left join, right join, inner join ,full join）

我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

mysql> begin； ＃开始一个事务
 
mysql> insert into a (a) values(555);
 
mysql>rollback; 回滚 ， 这样数据是不会写入的

使用pymysql来管理数据库

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标
cursor = conn.cursor()
  
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
  
  
# 提交，不然无法保存新建或者修改的数据
conn.commit()
  
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

SQLAchemy

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

pip3 install SQLAlchemy #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

orm的优点：

隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
ORM使我们构造固化数据结构变得简单易行。
缺点：

无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

创建表

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
 
engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb",
                                    encoding='utf-8', echo=True)
 
 
Base = declarative_base() #生成orm基类
 
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
 
Base.metadata.create_all(engine) #创建表结构

另外一种形式

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
 
metadata = MetaData()
 
user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12))
        )
 
class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password
 
mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

插入数据

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
 
 
user_obj = User(name="alex",password="alex3714") #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None
 
Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
 
Session.commit() #现此才统一提交，创建数据

查询数据

my_user = Session.query(User).filter_by(name="alex").first()
print(my_user)

# 此时你看到的输出是这样的应该

<__main__.User object at 0x105b4ba90>

我擦，这是什么？这就是你要的数据呀， 只不过sqlalchemy帮你把返回的数据映射成一个对象啦，这样你调用每个字段就可以跟调用对象属性一样啦，like this..

print(my_user.id,my_user.name,my_user.password)
 
# 输出
 alex alex3714

不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面加上这样的代码

def __repr__(self):
    return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

回滚

my_user = Session.query(User).filter_by(id=1).first()
my_user.name = "Jack"
 
 
fake_user = User(name='Rain', password='12345')
Session.add(fake_user)
 
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )  #这时看session里有你刚添加和修改的数据
 
Session.rollback() #此时你rollback一下
 
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。
 
# Session
# Session.commit()

获取所有数据

print(Session.query(User.name,User.id).all() )

多条件查询

objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()
# 上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果

统计和分组

Session.query(User).filter(User.name.like("Ra%")).count()

分组

from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

外键关联

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
 
class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(32), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))
 
    user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项
 
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

多外键关联

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
 
Base = declarative_base()
 
class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))
 
    billing_address = relationship("Address") 
    shipping_address = relationship("Address")
 
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)

针对上面代码的报错信息，代码修改如下：

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))
 
    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

多对多关系

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=book_m2m_author,backref='books')

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

插入数据

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class() #生成session实例
 
b1 = Book(name="跟Alex学Python")
b2 = Book(name="跟Alex学把妹")
b3 = Book(name="跟Alex学装逼")
b4 = Book(name="跟Alex学开车")
 
a1 = Author(name="Alex")
a2 = Author(name="Jack")
a3 = Author(name="Rain")
 
b1.authors = [a1,a2]
b2.authors = [a1,a2,a3]
 
s.add_all([b1,b2,b3,b4,a1,a2,a3])
 
s.commit()

查询数据

print('--------通过书表查关联的作者---------')
 
book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()
print(book_obj.name, book_obj.authors)
 
print('--------通过作者表查关联的书---------')
author_obj =s.query(Author).filter_by(name="Alex").first()
print(author_obj.name , author_obj.books)
s.commit()

#输出
--------通过书表查关联的作者---------
跟Alex学Python [Alex, Jack]
--------通过作者表查关联的书---------
Alex [跟Alex学把妹, 跟Alex学Python]

删除多对多

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()
 
book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()
 
book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

直接删除作者　

删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

连表查询

records = self.session.query(cte.Course, cte.Class_record, cte.League). \
            filter(cte.Course.record_id == cte.Class_record.id). \
            filter(cte.Course.id == cte.League.course_id). \
            filter(cte.Course.class_id == '7'). \
            all()

处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

MySql的其他用法

http://www.cnblogs.com/wupeiqi/articles/5713323.html

posted @ 2018-07-08 00:21  耳_东  阅读(113)  评论(0)    收藏  举报