python笔记-13 mysql与sqlalchemy

一、RDBMS relational database management system 关系型数据库引入

1、数据库的意义

更有效和合理的存储读取数据的一种方式

关系模型基础上的数据库 ->使用集合代数等数学方法来处理数据库中的数据

2、一些常见的数据库

2.1 非关系型数据库如redis 表之间没有相应的联系

2.2 关系型数据库

oracle、mysql、（mysql也oracle现在是同一家公司的，oracle收费，mysql开源免费）

sql server microsoft

psql

sqllite

access

3、关系型数据库几个基本概念

表: 表是数据的矩阵，数据以表格形式展现。

行：一条记录，（一组相关的数据）或各种记录的名称（表头）

列：数据元素，各种记录名称所对应的数据域（实际数据）

表单：许多行和列组成表单

数据库：若干相关联的表单的集合组合成数据库

主键：数据库主键primarykey，唯一标识，不重复，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。（可以有多列）

主键只有一个，但是可以设置为多个字段为主键，也即复合主键。

外键：用于关联两个表的key，关系表中的关系主键往往同时也是参考关联表的外键。（要分清随时谁的外键）

冗余：对于一些内容，我们经常使用，但是此内容需要关联多张表才能得到。为了加快效率，我们在几张表中都把此字段保存在表中，这样达到了只要查一张表的情况下，就能完成查询。

冗余具有两面性：他提高了数据查询的速度，但是他占用了更多的存储空间。

索引：使用索引能快速的访问数据表中的某些特定的资源，索引是对数据表中的一列或多列数据进行排序的一种方式（使用hash二分法、二叉树等快速找到资源），类似于数据目录

复合键：有复合主键和复合索引，将多个列作出复合primary key或index

关联：外联的相关联的数据之间必须同时修改或删除，以此来保证数据的完整性

参照完整性：参照的完整性要求关系中不允许引用不存在的实体。

参照完整性和实体完整性是关系模型必须满足的完整性条件，目的是保证数据的一致性。

二、mysql的基本操作

1、登录mysql 并查看已有的数据库 mysql -u root 与 show databases

mysql -u root 指定使用root用户登录mysql

show databases 查看本地mysql中有哪些databases

[root@cenos7_a ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 50
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jumpserver         |
| mysql              |
| performance_schema |
| schooldb           |
| student_system     |
| test               |
+--------------------+
7 rows in set (0.00 sec)

2、指定数据库名称进行操作 use databases

在查看了databases后，我们需要使用use 指定数据库名，告知mysql我要使用哪个数据库。

3、查看某数据库中已有的数据表show tables

在指定了数据库后，我们可以使用show tables 来查看该数据库中有哪些表

MariaDB [test]> use schooldb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [schooldb]> show tables;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| address            |
| school_info        |
| student_info       |
| t_student          |
| t_study_record     |
| test_user          |
| user_name          |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [schooldb]>

4、创建数据库，并查看数据库支持的charset create databases charset utf8 、show create databases ；

latin1为拉丁字符只支持英文字母

MariaDB [(none)]> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show create database schooldb;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| schooldb | CREATE DATABASE `schooldb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database blogtest;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database blogtest;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| blogtest | CREATE DATABASE `blogtest` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database utf8test charset utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database utf8test;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| utf8test | CREATE DATABASE `utf8test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

5、查看数据表属性 desc tables 、select column from tables

MariaDB [schooldb]> SHOW COLUMNS FROM school_info;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| school_name | char(32) | NO   |     | NULL    |                |
| address     | char(32) | NO   |     | NULL    |                |
| tel         | char(20) | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [schooldb]> desc school_info;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| school_name | char(32) | NO   |     | NULL    |                |
| address     | char(32) | NO   |     | NULL    |                |
| tel         | char(20) | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [schooldb]>

6、查看数据表的内容 select * from tables 、select* from tables G；

MariaDB [schooldb]> select * from school_info;
+----+-----------------+-----------+-------------+
| id | school_name     | address   | tel         |
+----+-----------------+-----------+-------------+
|  4 | 北京校区        | 朝阳区    | 18511112222 |
|  5 | 陕西校区        | 雁塔区    | 18533332222 |
|  6 | 北京二校区      | 海淀区    | 18033332222 |
|  9 | 上海校区        | 普陀区    | 19966229988 |
+----+-----------------+-----------+-------------+
4 rows in set (0.00 sec)
MariaDB [schooldb]> select * from school_info G;
*************************** 1. row ***************************
         id: 4
school_name: 北京校区
    address: 朝阳区
        tel: 18511112222
*************************** 2. row ***************************
         id: 5
school_name: 陕西校区
    address: 雁塔区
        tel: 18533332222
*************************** 3. row ***************************
         id: 6
school_name: 北京二校区
    address: 海淀区
        tel: 18033332222
*************************** 4. row ***************************
         id: 9
school_name: 上海校区
    address: 普陀区
        tel: 19966229988
4 rows in set (0.00 sec)

7、删除数据库 drop databases（谨慎使用）

MariaDB [schooldb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blogtest           |
| jumpserver         |
| mysql              |
| performance_schema |
| schooldb           |
| student_system     |
| test               |
| utf8test           |
+--------------------+
9 rows in set (0.00 sec)

MariaDB [schooldb]> 
MariaDB [schooldb]> drop database utf8test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [schooldb]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blogtest           |
| jumpserver         |
| mysql              |
| performance_schema |
| schooldb           |
| student_system     |
| test               |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [schooldb]> drop database blogtest;
Query OK, 0 rows affected (0.00 sec)

MariaDB [schooldb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jumpserver         |
| mysql              |
| performance_schema |
| schooldb           |
| student_system     |
| test               |
+--------------------+
7 rows in set (0.01 sec)

MariaDB [schooldb]>

8、3种常见的数据类型

数值类型，字符串类型，日期时间类型

其中初学者常见的数值类型int（4字节）、float（4字节）、char（0-255字节，在设定时要指定长度）、date（3字节，年月日没有具体时间）、time（3字节，时分秒）、datetime(8字节，年月日时分秒组合)

数值类型

日期时间类型

字符串类型

三、基本的sql操作

1、创建表

语法类型

create table 表名（

属性名1 属性类型（INT/CHAR/DATE）是否为空（NOT NULL）是否自增(AUTO_INCREMENT)，

属性名2 属性类型是否为空是否自增,

PRIMARY KEY(属性名)

）;

注意char形要指定长度

mysql> create table student(
-> sid INT NOT NULL AUTO_INCREMENT,
-> name CHAR(32) NOT NULL,
-> age INT NOT NULL,
-> birth DATE NULL,
-> PRIMARY KEY(sid)
-> );
Query OK, 0 rows affected (0.08 sec)
-----------------------------

MariaDB [test]> create table student( sid INT NOT NULL AUTO_INCREMENT, name CHAR(32) NOT NULL, birth DATE ,PRIMARY KEY(sid));
Query OK, 0 rows affected (0.00 sec)

2、表格内容的增删改查

2.1 insert 增加

insert 表名（属性1，属性2） values （属性对应的值1，属性对应的值2）

MariaDB [test]> insert into student (name,birth) values('tom','1990-01-01');
Query OK, 1 row affected (0.21 sec)

MariaDB [test]> insert into student (name,birth) values('jack','1980-01-01');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into student (name,birth) values('lucy','1993-02-02');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from student;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   2 | jack | 1980-01-01 |
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
3 rows in set (0.01 sec)

MariaDB [test]>

2.2 删除 delete from tablename where 过滤条件;

mysql> select * from student;
+-----+------+-----+------------+
| sid | name | age | birth      |
+-----+------+-----+------------+
|   1 | tom  |  15 | 1990-01-01 |
|   2 | jack |  25 | 2000-05-01 |
|   3 | 小明 |  25 | 2000-05-01 |
|   4 | alex |  10 | 2010-05-03 |
|   5 | 东东 |  29 | 2009-05-07 |
|   6 | 小楠 |  32 | 1981-12-30 |
+-----+------+-----+------------+
6 rows in set (0.00 sec)

mysql> delete from student where sid =5;
Query OK, 1 row affected (0.03 sec)

mysql> select * from student;
+-----+------+-----+------------+
| sid | name | age | birth      |
+-----+------+-----+------------+
|   1 | tom  |  15 | 1990-01-01 |
|   2 | jack |  25 | 2000-05-01 |
|   3 | 小明 |  25 | 2000-05-01 |
|   4 | alex |  10 | 2010-05-03 |
|   6 | 小楠 |  32 | 1981-12-30 |
+-----+------+-----+------------+
5 rows in set (0.00 sec)

2.3 修改 update

update from tablename set key=value，key2=v2 where 过滤条件;

mysql> update student set age=29,birth='2009-05-07' where sid =5 ;#同时改两个
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.4 查询select 及where过滤条件的说明（以select为例）

2.4.1 select

select */属性1,属性2。。 from tablename where 过滤条件;

MariaDB [test]> select * from student;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   2 | jack | 1980-01-01 |
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
3 rows in set (0.00 sec)

MariaDB [test]> select name from student;
+------+
| name |
+------+
| tom  |
| jack |
| lucy |
+------+
3 rows in set (0.00 sec)

MariaDB [test]> select sid,name from student;
+-----+------+
| sid | name |
+-----+------+
|   1 | tom  |
|   2 | jack |
|   3 | lucy |
+-----+------+
3 rows in set (0.00 sec)

2.4.2 where 过滤，以select 为例子

limit 限制输出的数量 offset(只能和limit结合使用)，用来对limit 输出结果的偏移

MariaDB [test]> select sid,name from student;
+-----+------+
| sid | name |
+-----+------+
|   1 | tom  |
|   2 | jack |
|   3 | lucy |
+-----+------+
3 rows in set (0.00 sec)

MariaDB [test]> select sid,name from student limit 1;
+-----+------+
| sid | name |
+-----+------+
|   1 | tom  |
+-----+------+
1 row in set (0.00 sec)

MariaDB [test]> select sid,name from student limit 1 offset 1;
+-----+------+
| sid | name |
+-----+------+
|   2 | jack |
+-----+------+
1 row in set (0.00 sec)

MariaDB [test]> select sid,name from student limit 1 offset 2;
+-----+------+
| sid | name |
+-----+------+
|   3 | lucy |
+-----+------+
1 row in set (0.00 sec)

MariaDB [test]> select sid,name from student limit 1 offset 3;
Empty set (0.00 sec)

MariaDB [test]>

2.4.3 逻辑关系 and or 比较关系 > < != 的使用

MariaDB [test]> select * from student where sid != 3;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   2 | jack | 1980-01-01 |
+-----+------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student where sid =1 or sid =3;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student where sid < 3;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   2 | jack | 1980-01-01 |
+-----+------+------------+
2 rows in set (0.01 sec)

MariaDB [test]> select * from student where sid < 3 and name ='tom';
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
+-----+------+------------+
1 row in set (0.00 sec)

MariaDB [test]>

2.4.4 时间的比较

MariaDB [test]> select * from student where birth > '1990-01-01';
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
1 row in set (0.00 sec)

2.4.5 模糊匹配 like %

MariaDB [test]> select * from student where birth like '%199%';
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   1 | tom  | 1990-01-01 |
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student where name like '%uc%';
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
1 row in set (0.00 sec)

MariaDB [test]>

2.5 排序

order by 属性名以表的某个属性为顺序进行排序

排序的方式有两种

asc 1->2->3 正序

desc 3->2->1 倒序

MariaDB [test]> select * from student order by birth;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   2 | jack | 1980-01-01 |
|   1 | tom  | 1990-01-01 |
|   3 | lucy | 1993-02-02 |
+-----+------+------------+
3 rows in set (0.00 sec)

MariaDB [test]> select * from student order by birth desc;
+-----+------+------------+
| sid | name | birth      |
+-----+------+------------+
|   3 | lucy | 1993-02-02 |
|   1 | tom  | 1990-01-01 |
|   2 | jack | 1980-01-01 |
+-----+------+------------+
3 rows in set (0.00 sec)

2.6 group 排序（对数据进行分类汇总)

group by 对表中某个字段出现的频率count 或总和sum进行分类统计

coumt的使用：

MariaDB [student_system]> select * from study_log;
+----+------------+----------+-----------+------------+------------+---------------+-------+
| id | study_date | class_id | lesson_id | student_id | study_stat | homework_stat | score |
+----+------------+----------+-----------+------------+------------+---------------+-------+
|  1 | 2018-06-08 |        1 |         1 |          1 | 出勤       | yes           |    90 |
|  2 | 2018-06-08 |        1 |         1 |          2 | 出勤       | empty         |     0 |
|  3 | 2018-06-08 |        1 |         1 |          3 | 出勤       | empty         |     0 |
|  4 | 2018-06-08 |        1 |         1 |          4 | 出勤       | empty         |     0 |
|  5 | 2018-06-08 |        2 |        11 |          4 | 旷课       | empty         |     0 |
+----+------------+----------+-----------+------------+------------+---------------+-------+
5 rows in set (0.00 sec)

MariaDB [student_system]> select student_id as 学号 ,count(*) as 上课记录 from study_log group by student_id;
+--------+--------------+
| 学号   | 上课记录     |
+--------+--------------+
|      1 |            1 |
|      2 |            1 |
|      3 |            1 |
|      4 |            2 |
+--------+--------------+
4 rows in set (0.00 sec)

MariaDB [student_system]>

sum的使用：

MariaDB [student_system]> select * from study_log;
+----+------------+----------+-----------+------------+------------+---------------+-------+
| id | study_date | class_id | lesson_id | student_id | study_stat | homework_stat | score |
+----+------------+----------+-----------+------------+------------+---------------+-------+
|  1 | 2018-06-08 |        1 |         1 |          1 | 出勤       | yes           |    90 |
|  2 | 2018-06-08 |        1 |         1 |          2 | 出勤       | empty         |     0 |
|  3 | 2018-06-08 |        1 |         1 |          3 | 出勤       | empty         |     0 |
|  4 | 2018-06-08 |        1 |         1 |          4 | 出勤       | empty         |     0 |
|  5 | 2018-06-08 |        2 |        11 |          4 | 旷课       | empty         |     0 |
+----+------------+----------+-----------+------------+------------+---------------+-------+
5 rows in set (0.00 sec)

MariaDB [student_system]> select student_id as 学号 ,sum(score) as 课程总分 from study_log group by student_id;+--------+--------------+
| 学号   | 课程总分     |
+--------+--------------+
|      1 |           90 |
|      2 |            0 |
|      3 |            0 |
|      4 |            0 |
+--------+--------------+
4 rows in set (0.00 sec)

MariaDB [student_system]>

rollup 计算全体

MariaDB [student_system]> select student_id as 学号 ,count(*) as `上课记录(次)` from study_log group by student_id with rollup;
+--------+-------------------+
| 学号   | 上课记录(次)      |
+--------+-------------------+
|      1 |                 1 |
|      2 |                 1 |
|      3 |                 1 |
|      4 |                 2 |
|   NULL |                 5 |
+--------+-------------------+
5 rows in set (0.00 sec)

coalesce 与rollup结合

MariaDB [student_system]> select coalesce(student_id,'课程记录总数') as 学号 ,count(*) as `上课记录(次)` from study_log group by student_id with rollup;
+--------------------+-------------------+
| 学号               | 上课记录(次)      |
+--------------------+-------------------+
| 1                  |                 1 |
| 2                  |                 1 |
| 3                  |                 1 |
| 4                  |                 2 |
| 课程记录总数         |                 5 |
+--------------------+-------------------+
5 rows in set (0.00 sec)

3、对表属性的增删改查

3.1 添加属性 alter table tables add 属性名类型约束；

MariaDB [test]> desc student
    -> ;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [test]> alter table student add sex char(3) not null;
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
| sex   | char(3)  | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]>

3.2 删除属性 alter table tablename drop 属性名；

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
| sex   | char(3)  | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> alter table student drop sex;
Query OK, 3 rows affected (0.03 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [test]>

3.3 修改属性

修改属性 modify 新写的属性会覆盖旧的属性

alter table tablename modify 属性名类型限制；

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
| sex   | char(3)  | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> alter table student modify sex char(4) ;
Query OK, 3 rows affected (0.03 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]>

3.4 修改列名称 change

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| sid   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(32) | NO   |     | NULL    |                |
| birth | date     | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> alter table student change sex sexxxx char(4) ;
Query OK, 3 rows affected (0.07 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc student;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| sid    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(32) | NO   |     | NULL    |                |
| birth  | date     | YES  |     | NULL    |                |
| sexxxx | char(4)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.5 修改表名称 rename

MariaDB [test]> alter table student rename studentnew;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| studentnew     |
+----------------+
1 row in set (0.00 sec)

3.6 查询属性 desc tablename ；不做过多描述

四、外键与外键约束

1、建立外键

两步

一步声明key（可以省略，mysql会自己建立一个，标准的索引此索引有啥用，此处不做深究）

一步建立关联

key shuibianxie(sid), <-key为一个索引index，名称可以随便写
constraint `shuibianxie` foreign key (sid) References student(sid)

注意点

1、建立表时必须要声明谁是primary key 2、涉及表相关变量的内容用撇号`变量`（数字1左边符号圈出），如constraint `index_fk_xxx`，如果涉及为字符内容的用单引号表示'yes'(如default 'yes')

MariaDB [test]> create table study_record( id int not null auto_increment primary key, status char(10) not null default 'yes', sid int not null, KEY index_fk_xxx(sid), constraint `index_fk_xxx` foreign key (sid) references student(sid));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> desc study_record;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| status | char(10) | NO   |     | yes     |                |
| sid    | int(11)  | NO   | MUL | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [test]>

2、外键约束

MariaDB [test]> desc study_record;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| status     | char(10) | NO   |     | yes     |                |
| sid        | int(11)  | NO   | MUL | NULL    |                |
| study_date | date     | NO   |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> select * from student;
+-----+------+------------+--------+
| sid | name | birth      | sexxxx |
+-----+------+------------+--------+
|   1 | tom  | 1990-01-01 |        |
|   2 | jack | 1980-01-01 |        |
|   3 | lucy | 1993-02-02 |        |
+-----+------+------------+--------+
3 rows in set (0.00 sec)

MariaDB [test]> insert into study_record (sid,study_date) values (1,'2018-01-01');
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> insert into study_record (sid,study_date) values (4,'2018-01-01');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`study_record`, CONSTRAINT `index_fk_xxx` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`))
MariaDB [test]> 
MariaDB [test]> delete from student where sid = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`study_record`, CONSTRAINT `index_fk_xxx` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`))
MariaDB [test]> select * from study_record;
+----+--------+-----+------------+
| id | status | sid | study_date |
+----+--------+-----+------------+
|  1 | yes    |   1 | 2018-01-01 |
+----+--------+-----+------------+
1 row in set (0.00 sec)

MariaDB [test]> delete from study_record where id =1;
Query OK, 1 row affected (0.00 sec)

不能插入外链中不存在的值，当关联存在时，数据无法被删除，保证数据完整性。（参照完整性）

3、连接查询

left join、right join、inner join、full join

最常见的是内连接 inner join

select * from a inner join b on a.a=b.b

select * from a,b where a.a=b.b

实际是a.a和b.b之间求交集（当数据在b中无重复的情况下）

如果有重复，或出现a.a 对应多个b.b 的情况此处先不考虑

MariaDB [student_system]> select * from school_class inner join school_course on school_class.class_course=school_course.course_id;
+----------+------------+--------------+-----------+-------------+
| class_id | class_name | class_course | course_id | course_name |
+----------+------------+--------------+-----------+-------------+
|        1 | python001  |            1 |         1 | python      |
|        2 | linux001   |            3 |         3 | linux       |
+----------+------------+--------------+-----------+-------------+
2 rows in set (0.00 sec)
MariaDB [student_system]>
MariaDB [student_system]> select * from school_class ,school_course where school_class.class_course=school_course.course_id;
+----------+------------+--------------+-----------+-------------+
| class_id | class_name | class_course | course_id | course_name |
+----------+------------+--------------+-----------+-------------+
|        1 | python001  |            1 |         1 | python      |
|        2 | linux001   |            3 |         3 | linux       |
+----------+------------+--------------+-----------+-------------+
2 rows in set (0.00 sec)

MariaDB [student_system]>

left join 与right join 此处都不考虑用来关联的key有重复的情况

MariaDB [test]> select * from a;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

MariaDB [test]> select * from b;
+---+
| b |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
+---+
4 rows in set (0.00 sec)

MariaDB [test]> select * from a left join b on a.a=b.b;
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
| 2 | NULL |
| 3 |    3 |
| 4 |    4 |
+---+------+
4 rows in set (0.00 sec)

MariaDB [test]> select * from a right join b on a.a=b.b;
+------+---+
| a    | b |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 5 |
| NULL | 6 |
+------+---+
4 rows in set (0.01 sec)

MariaDB [test]> select * from b left join a on a.a=b.b;
+---+------+
| b | a    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 5 | NULL |
| 6 | NULL |
+---+------+
4 rows in set (0.00 sec)

MariaDB [test]> select * from b right join a on a.a=b.b;
+------+---+
| b    | a |
+------+---+
| NULL | 1 |
| NULL | 2 |
|    3 | 3 |
|    4 | 4 |
+------+---+
4 rows in set (0.00 sec)

full join

mysql 并不直接支持full join，需要用union组合使用

MariaDB [test]> 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    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)

MariaDB [test]>

4、索引

将一列或多列值通过hash等方式生成一个顺序，并存在索引表中，在数据量非常大时，使用索引能有效的提高数据的查询时间

在创建索引时，我们要确保索引时使用在sql语句的过滤条件。

实际上索引也是一张表，保存着主键和索引字段，并指向实体表的记录

索引的优点:加快了查询速度

索引的缺点:占用空间,增加了插入数据，修改数据的时间（因为要刷新索引）

4.1 查询索引

show index

MariaDB [test]> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | sid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [test]> show index from study_record;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| study_record |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_fk_xxx |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4.2 创建索引

方法一通过create

MariaDB [test]> create index index_data on study_record(study_date);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show index from study_record;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| study_record |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_fk_xxx |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_data   |            1 | study_date  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [test]>

方法二在创建表的时候，就添加索引

index index_name (name(32))

MariaDB [test]> create table c (
    -> id int not null primary key,
    -> name char(32) not null,
    -> index index_name (name(32))
    -> );
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> show index from c;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| c     |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| c     |          1 | index_name |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4.3 删除索引

drop index indexname on tablename；

MariaDB [test]> show index from study_record;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| study_record |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_fk_xxx |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_data   |            1 | study_date  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [test]> drop index index_data on study_record;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show index from study_record;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| study_record |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| study_record |          1 | index_fk_xxx |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MariaDB [test]>

5 、事务

对于数据库的操作中，为了避免操作过程中出现误操作，设备宕机等意外情况对数据的损坏，出现了事务的概念，即操作完成后，确认成功，再进行保存，如果操作失败，则进行回滚

5.1 了解基本概念

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

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

5.2 3个基本操作

begin、commit、rollback

操作流程

begin；开始一个事务

执行指令

commit;提交

rollback；回滚

五、python与mysql交互

pymysql模块

直接执行原始sql语句

操作思路 1、建立链接 2、建立操作对象 3、excute 或excutemany

查询结果 fetchone 逐个查询、fetchall全部查看、fetchmany(n)查询指定数量，直接打印返回值，输出查询结果的条数

import pymysql

#建立连接
conn=pymysql.connect(host='192.168.99.106',port=3306,user='root',db='test',passwd='123456',charset='utf8')

#建立操作对象
cmd_obj=conn.cursor(cursor=pymysql.cursors.DictCursor)
tmp_result=cmd_obj.execute('select * from student')
#fetch的三种使用 fetchall fetchone fetchmany
print(tmp_result,cmd_obj.fetchall())#直接打印结果tmp_result，为查询到的数量 fetchall()返回一个列表，每个列表元素为一个字典。

#print(cmd_obj.fetchone())
#print(cmd_obj.fetchmany(3))

#操作数据
#x="insert into school_info (school_name,address,tel) values ('上海校区','普陀区',19966229988)"
#cmd_obj.execute(x)
#tmp_result=cmd_obj.execute('select * from school_info where id >5')
#print(tmp_result,cmd_obj.fetchall())
#conn.commit()
#cmd_obj.close()
#conn.close()

student_list=[
    ('stu_001','小东','13700010001'),
    ('stu_002','小明','13700010002'),
    ('stu_003','jack','13700010004')]
#cmd_obj.executemany("insert into student_info (sid,name,tel) values (%s,%s,%s)",student_list)
-----------------------------------
3 [{'sid': 1, 'name': 'tom', 'birth': datetime.date(1990, 1, 1), 'sexxxx': ''}, {'sid': 2, 'name': 'jack', 'birth': datetime.date(1980, 1, 1), 'sexxxx': ''}, {'sid': 3, 'name': 'lucy', 'birth': datetime.date(1993, 2, 2), 'sexxxx': ''}]

六、sqlalchemy orm操作

1、orm的介绍

orm--object relation mapping

orm 编程语言和数据库实现映射关系

使用对象来映射关系

orm的优点:：隐藏数据访问细节交互变得简单

orm的缺点：效率比原生sql低

2、sqlalchemy的介绍

orm为一个框架，不是真实的软件，在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用。

3、sqlalchemy实现数据库的增删改查

3.1 创建table

思路，建立conn、建立Base、class 继承base定义表结构、Base进行create_all

import sqlalchemy
from sqlalchemy import Column,String,Integer,DATE,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Table,UniqueConstraint
from sqlalchemy.orm import sessionmaker,relationship
#step 1 建立连接
engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test?charset=utf8')
#step2 建立Base
Base=declarative_base()
#step3 通过base定义表
class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
#repr为打印数据对象时输出的类型。下文会说明
    def __repr__(self):
        return self.username
#step4 通过base进行create_all
Base.metadata.create_all(engine)
-----------------------------

engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test?charset=utf8')
echo=True时能看到sqlalchemy生成的sql语句：

CREATE TABLE tstudent (
student_id INTEGER AUTO_INCREMENT,
student_name VARCHAR(64),
`student_QQ` INTEGER,
passwd VARCHAR(64),
PRIMARY KEY (student_id),
UNIQUE (student_name),
UNIQUE (`student_QQ`)
)

进数据库中验证

MariaDB [test]> desc tstudent;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(64) | YES  | UNI | NULL    |                |
| student_QQ   | int(11)     | YES  | UNI | NULL    |                |
| passwd       | varchar(64) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> show create table tstudent;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                               |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tstudent | CREATE TABLE `tstudent` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(64) DEFAULT NULL,
  `student_QQ` int(11) DEFAULT NULL,
  `passwd` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `student_name` (`student_name`),
  UNIQUE KEY `student_QQ` (`student_QQ`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]>

3.2 数据表中插入数据

思路先建立连接->建立base->定义表class->建立session_obj->建立session->使用class表生成具体的数据->session.add()->session.commit() / session.rollback()

import sqlalchemy
from sqlalchemy import Column,String,Integer,DATE,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Table,UniqueConstraint
from sqlalchemy.orm import sessionmaker,relationship
#step 1 建立连接
engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test?charset=utf8',echo=True)
#step2 建立Base
Base=declarative_base()
#step3 通过base定义表
class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
#repr为打印数据对象时输出的类型。下文会说明
    def __repr__(self):
        return self.username
def insert_data():
    #step 4 建立session_obj
    session_obj=sessionmaker(bind=engine)
    #step5  建立session
    session = session_obj()
    #step6 生成具体数据
    tmp_data=tstudent(student_name='stu001',student_QQ=11111,passwd='123456')
    session.add
    session.add(tmp_data)
    session.commit()
    S1 = tstudent(student_name='Student001', passwd='123456', student_QQ='10001')
    S2 = tstudent(student_name='Student002', passwd='123456', student_QQ='10002')
    S3 = tstudent(student_name='Student003', passwd='123456', student_QQ='10003')
    S4 = tstudent(student_name='Student004', passwd='123456', student_QQ='10004')
    S5 = tstudent(student_name='Student005', passwd='123456', student_QQ='10005')
    data_list=[S1,S2,S3,S4,S5]
    session.add_all(data_list)
    session.commit()

insert_data()

需要注意add()和add_all()

echo=True生成的部分log

2018-06-18 12:11:14,757 INFO sqlalchemy.engine.base.Engine INSERT INTO tstudent (student_name, `student_QQ`, passwd) VALUES (%(student_name)s, %(student_QQ)s, %(passwd)s)
2018-06-18 12:11:14,757 INFO sqlalchemy.engine.base.Engine {'student_name': 'Student001', 'student_QQ': '10001', 'passwd': '123456'}
2018-06-18 12:11:14,757 INFO sqlalchemy.engine.base.Engine INSERT INTO tstudent (student_name, `student_QQ`, passwd) VALUES (%(student_name)s, %(student_QQ)s, %(passwd)s)
2018-06-18 12:11:14,757 INFO sqlalchemy.engine.base.Engine {'student_name': 'Student002', 'student_QQ': '10002', 'passwd': '123456'}
2018-06-18 12:11:14,758 INFO sqlalchemy.engine.base.Engine INSERT INTO tstudent (student_name, `student_QQ`, passwd) VALUES (%(student_name)s, %(student_QQ)s, %(passwd)s)
2018-06-18 12:11:14,758 INFO sqlalchemy.engine.base.Engine {'student_name': 'Student003', 'student_QQ': '10003', 'passwd': '123456'}
2018-06-18 12:11:14,758 INFO sqlalchemy.engine.base.Engine INSERT INTO tstudent (student_name, `student_QQ`, passwd) VALUES (%(student_name)s, %(student_QQ)s, %(passwd)s)
2018-06-18 12:11:14,758 INFO sqlalchemy.engine.base.Engine {'student_name': 'Student004', 'student_QQ': '10004', 'passwd': '123456'}
2018-06-18 12:11:14,759 INFO sqlalchemy.engine.base.Engine INSERT INTO tstudent (student_name, `student_QQ`, passwd) VALUES (%(student_name)s, %(student_QQ)s, %(passwd)s)
2018-06-18 12:11:14,759 INFO sqlalchemy.engine.base.Engine {'student_name': 'Student005', 'student_QQ': '10005', 'passwd': '123456'}

进入mysql验证数据

MariaDB [test]> select * from tstudent;
+------------+--------------+------------+--------+
| student_id | student_name | student_QQ | passwd |
+------------+--------------+------------+--------+
|          1 | stu001       |      11111 | 123456 |
|          2 | Student001   |      10001 | 123456 |
|          3 | Student002   |      10002 | 123456 |
|          4 | Student003   |      10003 | 123456 |
|          5 | Student004   |      10004 | 123456 |
|          6 | Student005   |      10005 | 123456 |
+------------+--------------+------------+--------+
6 rows in set (0.00 sec)

3.3 数据表中删除数据

要删除数据首先要把要操作的数据查出来之后session.delete() 只能一条记录一条记录删不能全部.all()删除

import sqlalchemy
from sqlalchemy import Column,String,Integer,DATE,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Table,UniqueConstraint
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import or_
#step 1 建立连接
engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test?charset=utf8')
#step2 建立Base
Base=declarative_base()
#step3 通过base定义表
class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
#repr为打印数据对象时输出的类型。下文会说明
    def __repr__(self):
        return self.student_name
def delete_data():
    #step 4 建立session_obj
    session_obj=sessionmaker(bind=engine)
    #step5  建立session
    session = session_obj()
    #step6 session.query
    tmp_result=session.query(tstudent).first()
    print(tmp_result)
    #step7 session.delete
    session.delete(tmp_result)
    session.commit()

delete_data()
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
MariaDB [test]> select * from tstudent;
+------------+--------------+------------+--------+
| student_id | student_name | student_QQ | passwd |
+------------+--------------+------------+--------+
|          1 | stu001       |      11111 | 123456 |
|          2 | Student001   |      10001 | 123456 |
|          3 | Student002   |      10002 | 123456 |
|          4 | Student003   |      10003 | 123456 |
|          5 | Student004   |      10004 | 123456 |
|          6 | Student005   |      10005 | 123456 |
+------------+--------------+------------+--------+
6 rows in set (0.00 sec)

MariaDB [test]> select * from tstudent;
+------------+--------------+------------+--------+
| student_id | student_name | student_QQ | passwd |
+------------+--------------+------------+--------+
|          2 | Student001   |      10001 | 123456 |
|          3 | Student002   |      10002 | 123456 |
|          4 | Student003   |      10003 | 123456 |
|          5 | Student004   |      10004 | 123456 |
|          6 | Student005   |      10005 | 123456 |
+------------+--------------+------------+--------+
5 rows in set (0.00 sec)

MariaDB [test]>

3.4 数据表中修改数据

要修改数据首先要把要操作的数据查出来

MariaDB [test]> select * from tstudent;
+------------+--------------+------------+--------+
| student_id | student_name | student_QQ | passwd |
+------------+--------------+------------+--------+
|          2 | Student001   |      10001 | 123456 |
|          3 | Student002   |      10002 | 123456 |
|          4 | Student003   |      10003 | 123456 |
|          5 | Student004   |      10004 | 123456 |
|          6 | Student005   |      10005 | 123456 |
+------------+--------------+------------+--------+
5 rows in set (0.00 sec)

MariaDB [test]> select * from tstudent;
+------------+--------------+------------+--------+
| student_id | student_name | student_QQ | passwd |
+------------+--------------+------------+--------+
|          1 | Student001   |      10001 | 123456 |
|          3 | Student002   |      10002 | 123456 |
|          4 | Student003   |      10003 | 123456 |
|          5 | Student004   |      10004 | 123456 |
|          6 | Student005   |      10005 | 123456 |
+------------+--------------+------------+--------+
5 rows in set (0.00 sec)

MariaDB [test]>

3.5 数据表中查询数据

3.5.1 普通查询无过滤条件

all与first的区别

import sqlalchemy
from sqlalchemy import Column,String,Integer,DATE,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Table,UniqueConstraint
from sqlalchemy.orm import sessionmaker,relationship
#step 1 建立连接
engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test?charset=utf8')
#step2 建立Base
Base=declarative_base()
#step3 通过base定义表
class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
#repr为打印数据对象时输出的类型。下文会说明
    def __repr__(self):
        return self.student_name
----------------------------------------
[stu001, Student001, Student002, Student003, Student004, Student005]
学生： Student001 qq号： 10001
stu001

3.5.2 过滤条件

limit session.query().limit()

    tmp_result = session.query(tstudent).limit(3).all()
    print(tmp_result)

filter 与filter_by

一般用filter多一些

    tmp_result = session.query(tstudent).filter(tstudent.student_id==1).all()
    print(tmp_result)
    tmp_result = session.query(tstudent).filter_by(student_id = 1).all()
    print(tmp_result)

3.5.3 逻辑关系与数字关系 and or > < !=

   #or 关系 from sqlalchemy import or_
    tmp_or = session.query(tstudent).filter(or_(tstudent.student_id==1,tstudent.student_id>4)).all()
    print(tmp_or)
    #and 关系
    tmp_and = session.query(tstudent).filter(tstudent.student_id==1,tstudent.student_QQ=='11111').all()
    print(tmp_and)
    #> != 对比
    tmp_compare = session.query(tstudent).filter(tstudent.student_id>1,tstudent.student_id != 3).all()
    print(tmp_result)
-----------------------------------
[stu001, Student004, Student005]
[stu001]
[stu001, Student001, Student002, Student003, Student004, Student005]

3.5.4 多表组合查询与order排序

多表查询，最后结果为列表，每个列表中又嵌套列表，对于query里面的每张表。用下标取具体的表来取相应的属性

排序用session.query().order_by(key.desc()).all()

study_data = session.query(study_log, School_class, Lesson_info, Student).filter(
        study_log.class_id == School_class.class_id,
        study_log.lesson_id == Lesson_info.lesson_id,
        study_log.student_id == Student.student_id,
        study_log.student_id == student_data.student_id,
        study_log.homework_stat == 'yes'
    ).all()
    print('目前已经交了%s份作业' % len(study_data))
    for i in study_data:
print('*******************************************************')
        print('班级名:[%s],课程名:[%s],学生姓名:[%s],作业分数:[%s]' % (
        i[1].class_name, i[2].lesson_name, i[3].student_name, i[0].score))
print('*******************************************************')
        print('该作业班级排名：')
        class_rank_data = session.query(study_log, School_class, Lesson_info, Student).filter(
            study_log.class_id == School_class.class_id,
            study_log.lesson_id == Lesson_info.lesson_id,
            study_log.student_id == Student.student_id,
            study_log.study_date==i[0].study_date,
            study_log.class_id == i[1].class_id,
            study_log.lesson_id==i[2].lesson_id
        ).order_by(study_log.score.desc()).all()
        for j in class_rank_data:
            print('班级名:[%s],课程名:[%s],学生姓名:[%s],作业分数:[%s]' % (
        j[1].class_name, j[2].lesson_name, j[3].student_name, j[0].score))

3.5.5 模糊匹配like

session.query.filter(xx,like(%%)).all()

  data=session.query(Lesson_info).filter(Lesson_info.lesson_desc.like('%rhc%')).all()

3.5.6 统计与分组count() group_by()

auto_increment 设置为0

alter table tbname auto_increment = x ;重置increment

4、外键关联 foreignkey与relationship的使用

4.1 建立外链

ForeignKey('tstudent.student_id')

4.2 组合唯一

from sqlalchemy import create_engine,Table,UniqueConstraint

__table_args__ = (UniqueConstraint('key1', 'key2'),)

class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
    def __repr__(self):
        return self.student_name
class tstudy_record(Base):
    __tablename__ = 'tstudy_record'
    id = Column(Integer, primary_key=True, nullable=True)
    sid = Column(Integer,ForeignKey('tstudent.student_id'),nullable=False)
    study_date = Column(DATE,nullable=False)
    __table_args__ = (UniqueConstraint('sid', 'study_date'),)#from sqlalchemy import create_engine,Table,UniqueConstraint

在mysql中查看

MariaDB [test]> desc tstudy_record;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| sid        | int(11) | NO   | MUL | NULL    |                |
| study_date | date    | NO   |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

数据插入

    d1 = tstudy_record(sid=1, study_date='2018-01-01')
    d2 = tstudy_record(sid=1, study_date='2018-01-02')
    d3 = tstudy_record(sid=1, study_date='2018-01-03')
    d4 = tstudy_record(sid=1, study_date='2018-01-04')
    d5 = tstudy_record(sid=4, study_date='2018-01-04')
    d6 = tstudy_record(sid=3, study_date='2018-01-04')
    d_list=[d1,d5,d6,d2,d3,d4]
    session.add_all(d_list)
    session.commit()

mysql验证

MariaDB [test]> select * from tstudy_record;
+----+-----+------------+
| id | sid | study_date |
+----+-----+------------+
|  1 |   1 | 2018-01-01 |
|  4 |   1 | 2018-01-02 |
|  5 |   1 | 2018-01-03 |
|  6 |   1 | 2018-01-04 |
|  3 |   3 | 2018-01-04 |
|  2 |   4 | 2018-01-04 |
+----+-----+------------+
6 rows in set (0.00 sec)

组合查询

MariaDB [test]> select tstudent.student_name as 学号,study_date 上课记录 from tstudent,tstudy_record where tstudy_record.sid = tstudent.student_id;
+------------+--------------+
| 学号       | 上课记录     |
+------------+--------------+
| Student001 | 2018-01-01   |
| Student001 | 2018-01-02   |
| Student001 | 2018-01-03   |
| Student001 | 2018-01-04   |
| Student002 | 2018-01-04   |
| Student003 | 2018-01-04   |
+------------+--------------+
6 rows in set (0.00 sec)

重复数据报错

(1062, "Duplicate entry '1-2018-01-01' for key 'sid'") [SQL: 'INSERT INTO tstudy_record (sid, study_date) VALUES (%(sid)s, %(study_date)s)'] [parameters: {'sid': 1, 'study_date': '2018-01-01'}] (Background on this error at: http://sqlalche.me/e/gkpj)

4.3 relation的使用

建立表格之间的联系，方便查询出来的数据查询其他表中的相关数据

此方法需要有外链相对应的情况下使用

两个关键 relationship 、backref

from sqlalchemy.orm import relationship

record_to_student=relationship(tstudent,backref='student_to_record')

class tstudent(Base):
    __tablename__='tstudent'
    student_id=Column(Integer,primary_key=True,nullable=True)
    student_name=Column(String(64),nullable=True,unique=True)
    student_QQ=Column(Integer,unique=True,nullable=True)
    passwd=Column(String(64))
#repr为打印数据对象时输出的类型。下文会说明
    def __repr__(self):
        return self.student_name
class tstudy_record(Base):
    __tablename__ = 'tstudy_record'
    id = Column(Integer, primary_key=True, nullable=True)
    sid = Column(Integer,ForeignKey('tstudent.student_id'),nullable=False)
    study_date = Column(DATE,nullable=False)
    record_to_student=relationship(tstudent,backref='student_to_record')
    __table_args__ = (UniqueConstraint('sid', 'study_date'),)
def relationship_test():
    # step 4 建立session_obj
    session_obj = sessionmaker(bind=engine)
    # step5  建立session
    session = session_obj()
    # step6 session.query
    tmp_result = session.query(tstudent).first()
    print(tmp_result)
    # step7 通过查询出的student列出对应的上课记录
    tmp_record = tmp_result.student_to_record
    for i in tmp_record:
        print(tmp_result.student_name,i.study_date)
create_tables()
relationship_test()
---------------------------------------------------
Student001
Student001 2018-01-01
Student001 2018-01-02
Student001 2018-01-03
Student001 2018-01-04

与mysql的对比

MariaDB [test]> select tstudent.student_name as 学号,study_date 上课记录 from tstudent,tstudy_record where tstudent.student_id=1 and tstudy_record.sid = tstudent.student_id;
+------------+--------------+
| 学号       | 上课记录     |
+------------+--------------+
| Student001 | 2018-01-01   |
| Student001 | 2018-01-02   |
| Student001 | 2018-01-03   |
| Student001 | 2018-01-04   |
+------------+--------------+
4 rows in set (0.00 sec)

4.4 一张表多外键关联同一张表的情况

此情况使用relationship时需要标注出foreign_keys，让程序合理区分通过哪个关系去查表

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

engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/schooldb')

Base=declarative_base()
class address(Base):
    __tablename__='address'
    id=Column(Integer,nullable=False,primary_key=True)
    L1=Column(String(32),nullable=False)

class user_name(Base):
    __tablename__='user_name'
    id=Column(Integer,nullable=False,primary_key=True)
    name=Column(String(32),nullable=False)

    home_address=Column(Integer,ForeignKey(address.id))
    study_address=Column(Integer,ForeignKey(address.id))
    home_to_addr=relationship('address',foreign_keys=[home_address])
    study_to_addr=relationship('address',foreign_keys=[study_address])

'''
MariaDB [schooldb]> desc user_name;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(32) | NO   |     | NULL    |                |
| home_address  | int(11)     | YES  | MUL | NULL    |                |
| study_address | int(11)     | YES  | MUL | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [schooldb]> desc address;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| L1    | varchar(32) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [schooldb]> 

'''


#Base.metadata.create_all(engine)
session_obj=sessionmaker(bind=engine)
session=session_obj()

#A1=address(L1='BJ')
#A2=address(L1='SH')
#A3=address(L1='GZ')
#A4=address(L1='SZ')

P1=user_name(name='a1',home_address=1,study_address=4)
P2=user_name(name='a2',home_address=1,study_address=3)
P3=user_name(name='a3',home_address=3,study_address=2)
P4=user_name(name='a4',home_address=3,study_address=4)
P5=user_name(name='a5',home_address=2,study_address=2)
P6=user_name(name='alex',home_address=1,study_address=1)

#session.add_all([P1,P2,P3,P4,P5,P6])

#session.commit()
'''
MariaDB [schooldb]> select * from address;
+----+----+
| id | L1 |
+----+----+
|  1 | BJ |
|  2 | SH |
|  3 | GZ |
|  4 | SZ |
+----+----+
4 rows in set (0.00 sec)

MariaDB [schooldb]> select * from user_name;
+----+------+--------------+---------------+
| id | name | home_address | study_address |
+----+------+--------------+---------------+
|  3 | a1   |            1 |             4 |
|  4 | a2   |            1 |             3 |
|  5 | a3   |            3 |             2 |
|  6 | a4   |            3 |             4 |
|  7 | a5   |            2 |             2 |
|  8 | alex |            1 |             1 |
+----+------+--------------+---------------+
6 rows in set (0.00 sec)
'''
data=session.query(user_name).filter_by().all()
for i in data:
   print('姓名：',i.name,'家乡：',i.home_to_addr.L1,'毕业学校：',i.study_to_addr.L1)

'''
姓名： a1 家乡： BJ 毕业学校： SZ
姓名： a2 家乡： BJ 毕业学校： GZ
姓名： a3 家乡： GZ 毕业学校： SH
姓名： a4 家乡： GZ 毕业学校： SZ
姓名： a5 家乡： SH 毕业学校： SH
姓名： alex 家乡： BJ 毕业学校： BJ

没有多个外键的情况下，同样也可以补充这个说明，并不矛盾

5、多对多的关系处理

几个作者

几本书

一本书有几个作者

一个作者有几本书

书和作者直接存在着多对多的关系

为了表示这种关系，我们引入第三张表来表示这种关系

book author book_to_author

关键点1 关系表需要用table来建立，不能用class

关键点2 relationship的secondary

关键点3 关系的删除

关键点4 重复插入不会重复

import sqlalchemy

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

engine=create_engine('mysql+pymysql://root:123456@192.168.99.106/test')
Base=declarative_base()

book_to_author=Table('book_to_author',Base.metadata,
                     Column('id',Integer,primary_key=Table,nullable=ForeignKey),
                     Column( 'book_id',Integer,ForeignKey('book.id') ),
                     Column('author_id',Integer,ForeignKey('author.id')),
                     )

class book(Base):
    __tablename__='book'
    id=Column(Integer,primary_key=True,nullable=False)
    name=Column(String(32),nullable=False)
    myauthor=relationship('author',secondary='book_to_author',backref='mybooks')
    def __repr__(self):
        return self.name


class author(Base):
    __tablename__='author'
    id=Column(Integer,primary_key=True,nullable=False)
    name=Column(String(32),nullable=False)
    def __repr__(self):
        return self.name


Base.metadata.create_all(engine)

session_obj=sessionmaker(engine)
session=session_obj()

b1=book(name='book1')
b2=book(name='book2')
b3=book(name='book3')

a1=author(name='author1')
a2=author(name='author2')
a3=author(name='author3')

b1.myauthor=[a1,a2,a3]
b2.myauthor=[a1,a3]
b3.myauthor=[a1,a2]

#session.add_all([b1,b2,b3,a1,a2,a3])
#session.commit()



data=session.query(book).all()
print(data)
for i in data:
    print(i.name,i.myauthor)
    for j in i.myauthor:
        print(j.name,j.mybooks)
    print('-----------------------------------')



tmp_author=session.query(author).first()
print(tmp_author,tmp_author.mybooks)
tmp_book=session.query(book).first()
print(tmp_book,tmp_book.myauthor)

#tmp_book.myauthor.remove(tmp_author)
#从书中删除作者，关系里面作者也删除了书

print(tmp_author,tmp_author.mybooks)
print(tmp_book,tmp_book.myauthor)

session.commit()




print('**********************')
data=session.query(author).filter_by(name='author2').first()
print(data,data.mybooks)
session.delete(data)
data=session.query(book).all()
for i in data:
    print(i.name,i.myauthor)

for i in range(10):
   b1=session.query(book).filter(book.id==2).first()
   a1=session.query(author).filter(author.id==2).first()
   #print(b1.id,a1.id)
   a1.mybooks.append(b1)
   session.commit()
   #a1.mybooks.append()
#循环执行 不会重复
'''
MariaDB [test]> select * from book_to_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       3 |         2 |
|  3 |       1 |         1 |
|  4 |       2 |         1 |
|  5 |       3 |         1 |
|  6 |       1 |         3 |
|  7 |       2 |         3 |
+----+---------+-----------+
7 rows in set (0.00 sec)

MariaDB [test]> select * from book_to_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       3 |         2 |
|  3 |       1 |         1 |
|  4 |       2 |         1 |
|  5 |       3 |         1 |
|  6 |       1 |         3 |
|  7 |       2 |         3 |
|  8 |       2 |         2 |
+----+---------+-----------+
8 rows in set (0.00 sec)

'''

mysql中显示

MariaDB [test]> select * from book_to_author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       3 |         2 |
|  3 |       1 |         1 |
|  4 |       2 |         1 |
|  5 |       3 |         1 |
|  6 |       1 |         3 |
|  7 |       2 |         3 |
|  8 |       2 |         2 |
+----+---------+-----------+
8 rows in set (0.00 sec)

MariaDB [test]> select * from book;
+----+-------+
| id | name  |
+----+-------+
|  1 | book1 |
|  2 | book2 |
|  3 | book3 |
+----+-------+
3 rows in set (0.00 sec)

MariaDB [test]> select * from author;
+----+---------+
| id | name    |
+----+---------+
|  1 | author1 |
|  2 | author2 |
|  3 | author3 |
+----+---------+
3 rows in set (0.00 sec)

MariaDB [test]>

一点补充：

统计 count

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

group_by 分类汇总

from sqlalchemy import func

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