No.015-Python-学习之路-Day12-Mysql

数据库与Mysql

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量，而所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

常用的关系型数据库：Oracle, Mysql, sqlServer, DB2, Postgresql, Sqlite, access;

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

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

RDBMS术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵,在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在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操作-简单操作

msql –uroot –p123.com # 使用用户名密码登录；
show databases; # 显示所有的数据库；
use mysql; # 使用某个数据库；
show tables; # 显示该数据库中所有的表；
desc user; # 显示表结构；
select * from userG; # 显示表中所有的内容，并竖着显示；
select User from user； # 显示表中某一列的数据；

Mysql操作-用户设置

方式一:如果你需要添加 MySQL 用户，你只需要在 mysql 数据库中的 user 表添加新用户即可。；

# 在mysql.user表中插入记录，所有的非空字段填满
INSERT INTO user (host, user, `ssl_type`, `authentication_string`, `ssl_cipher`, `x509_issuer`, `x509_subject`) VALUE ('localhost', 'alvin', '', password('123'), 0, 0, 0);
Query OK, 1 row affected (0.01 sec)
# 修改mysql.user表赋予权限
UPDATE user SET   Select_priv='Y', insert_priv='Y', update_priv='Y' WHERE user='alvin';
# 刷新权限
FLUSH privileges；

方式二:通过SQL的 GRANT 命令 ；

# 创建新用户
CREATE user 'bruce' IDENTIFIED BY '123';
# 赋权
GRANT all privileges ON *.* TO 'bruce' @'%';
# 刷新权限
FLUSH PRIVILEGES：
# 修改账户密码
# centos 7下 mysql5.7以后版本自带默认密码，在grep 'temporary password' /var/log/mysqld.log查看
alter user 'root'@'localhost' identified by '123456';
set global validate_password_policy=0; # 设置密码复杂度为0
set global validate_password_length=1; # 设置密码长度为1等，但是实际测试还是需要6位才ok，这个默认8位

Mysql操作-常用管理命令

以下列出了使用Mysql数据库过程中常用的命令：
    USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
    SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
    SHOW TABLES: #显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。
    SHOW COLUMNS FROM 数据表: #显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
    create database testdb charset "utf8"; #创建一个叫testdb的数据库，且让其支持中文，如果不指定默认为latin1
    show create database mysql; # 可以查看这个数据创建时定义的一些信息；
    drop database testdb; #删除数据库
    SHOW INDEX FROM 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

MySQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的,MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR，每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值，TIMESTAMP类型有专有的自动更新特性。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET，该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注:

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串；也就是说，它们包含字节字符串而不是字符字符串，这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据，有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

Char、varchar与text的区别

1.它们的存储方式和数据的检索方式都不一样，数据的检索及存储效率是：char > varchar > text；

2.char：存储定长数据很方便，必须在括号里定义长度，比如定义char(10)，那么不论存储字节是否达到10个字节，都占10个字节空间(自动使用空格填充)，检索的时候后面的空格会隐藏掉；

3.varchar：存储变长数据，必须在括号里定义长度，保存数据的时候，不进行空格自动填充，检索的时候后面的空格不会隐藏，会比实际长度+1字节，用于保存实际使用了多大的长度；

4.text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符，text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的；

MySQL常用命令

创建数据库及数据表

语法：

CREATE TABLE table_name (column_name column_type);

实例:

# 创建数据库fcdb
create database fcdb charset "utf8" 

# 创建一个student表
create table student(
   stu_id INT NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT每创建一条自己增加+1,y一般用于主键,NOT Null设置为空；
   name CHAR(32) NOT NULL,
   age  INT NOT NULL,
   register_date DATE,
   PRIMARY KEY ( stu_id ) # 定义主键，其实主键自动不为空，stu_id可以不用设置非空；
);

插入数据

语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

实例：

mysql> insert into student(name, age, register_date) values ("Bruce Lee", 25, "2019-1-3");
Query OK, 1 row affected (0.07 sec)

mysql>
mysql>
mysql>
mysql> select * from student;
+--------+-----------+-----+---------------+
| stu_id | name      | age | register_date |
+--------+-----------+-----+---------------+
|      1 | Bruce Lee |  25 | 2019-01-03    |
+--------+-----------+-----+---------------+
1 row in set (0.00 sec)

查询数据-SELECT语句

select 去重选项[all/distinct] 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];
#where几种语法:
方式一:基于值
= ： where 字段 = 值，查找出对应字段等于对应值的记录（相似的有<,<=,>,>=,!=），例如：where name = 'lilei'；
like：where 字段like值 ;功能与 = 相似 ，但可以使用模糊匹配来查找结果，例如：where name like 'li%'；

方式二:基于范围
in： where 字段 in 范围;查找出对应字段的值在所指定范围的记录。例如：where age in (18,19,20)
not in ： where 字段 not in 范围;查找出对应字段的值不在所指定范围的记录。例如：where age not in (18,19,20)
between x and y ：where 字段 between x and y;查找出对应字段的值在闭区间[x,y]范围的记录。例如：where age between 18 and 20；

方式三:条件复合
or ： where 条件1 or 条件2… ; 查找出符合条件1或符合条件2的记录。
and：  where 条件1 and 条件2… ; 查找出符合条件1并且符合条件2的记录。
not： where not 条件1 ;查找出不符合条件的所有记录。
&&的功能与and相同，||与or功能类似，!与not 功能类似。

注:where是从磁盘中获取数据的时候就进行筛选的，所以某些在内存是才有的东西where无法使用。(字段别名什么的是本来不是“磁盘中的数据”（是在内存这中运行时才定义的），所以where无法使用，一般都依靠having来筛选)，如下:
select name as n ,gender from student where name ="lilei";
-- select name as n ,gender from student where n ="lilei"; --报错
select name as n ,gender from student having n ="lilei";

# Mysql排序
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
select *from student where name like binary "%Li" order by stu_id desc;

# 分组统计-GROUP BY 语句
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
语法:
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
注:在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中，<测试不正确>
常用的聚合函数-sum(列名),max(列名),min(列名),avg(列名),count(列名),count(*)[与count(列名)有啥区别]
mysql> select name , sum(age) as "总年龄" from student group by name order by sum(age);
+-----------+--------+
| name      | 总年龄 |
+-----------+--------+
| Min Lee   |      2 |
| Alvin Lee |      8 |
| Bruce Lee |     50 |
+-----------+--------+
3 rows in set (0.00 sec)

mysql> select name, register_date, sum(age) as "总年龄" from student group by name order by sum(age);
+-----------+---------------+--------+
| name      | register_date | 总年龄 |
+-----------+---------------+--------+
| Min Lee   | 2019-03-31    |      2 |
| Alvin Lee | 2019-01-03    |      8 |
| Bruce Lee | 2019-01-03    |     50 |
+-----------+---------------+--------+
3 rows in set (0.00 sec)

mysql> select name,count(age) from student group by name with rollup; # 做总的统计
+-----------+------------+
| name      | count(age) |
+-----------+------------+
| Alvin Lee |          4 |
| Bruce Lee |          3 |
| Min Lee   |          1 |
| NULL      |          8 |
+-----------+------------+
4 rows in set (0.00 sec)

mysql> select coalesce(name, "总数"),count(age) from student group by name with rollup; # 设置名称替代NULL
+------------------------+------------+
| coalesce(name, "总数") | count(age) |
+------------------------+------------+
| Alvin Lee              |          4 |
| Bruce Lee              |          3 |
| Min Lee                |          1 |
| 总数                   |          8 |
+------------------------+------------+
4 rows in set (0.00 sec)

修改数据-UPDATE语句

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
update student set age=22 ,name="Bruce" where stu_id>3;

删除行-DELETE语句

DELETE FROM table_name [WHERE Clause]<br><br>delete from student where stu_id=5; 

修改表名或字段-ALTER语句

我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令；

# 表内添加字段
mysql> alter table student add sex enum("M","F") ;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+-----------+-----+---------------+------+
| stu_id | name      | age | register_date | sex  |
+--------+-----------+-----+---------------+------+
|      1 | Bruce Lee |  25 | 2019-01-03    | NULL |
|      3 | Bruce Lee |  25 | 2019-01-03    | NULL |
|      4 | Alvin Lee |   2 | 2019-01-03    | NULL |
|      5 | Alvin Lee |   2 | 2019-01-03    | NULL |
|      6 | Min Lee   |   2 | 2019-03-31    | NULL |
|      7 | Alvin Lee |   2 | 2019-01-03    | NULL |
|      8 | Alvin Lee |   2 | 2019-01-03    | NULL |
|      9 | Bruce Lee |  23 | 2019-03-31    | NULL |
+--------+-----------+-----+---------------+------+
8 rows in set (0.00 sec)

# 表内删除字段-alter add/drop
mysql> alter table student drop sex;
Query OK, 0 rows affected (1.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+--------+-----------+-----+---------------+
| stu_id | name      | age | register_date |
+--------+-----------+-----+---------------+
|      1 | Bruce Lee |  25 | 2019-01-03    |
|      3 | Bruce Lee |  25 | 2019-01-03    |
|      4 | Alvin Lee |   2 | 2019-01-03    |
|      5 | Alvin Lee |   2 | 2019-01-03    |
|      6 | Min Lee   |   2 | 2019-03-31    |
|      7 | Alvin Lee |   2 | 2019-01-03    |
|      8 | Alvin Lee |   2 | 2019-01-03    |
|      9 | Bruce Lee |  23 | 2019-03-31    |
+--------+-----------+-----+---------------+
8 rows in set (0.00 sec)

# 修改字段-alter modify/change

mysql> alter table student modify age tinyint null DEFAULT 20; # modify修改字段的数据类型及是否为空,并设置默认值；
Query OK, 8 rows affected (1.51 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table student change register_date register datetime not null ; # change可以修改名字及数据类型及是否为空；
Query OK, 8 rows affected (2.25 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table student rename to students; # 修改表的名字
Query OK, 0 rows affected (0.49 sec)
mysql> show tables;
+----------------+
| Tables_in_fcdb |
+----------------+
| students       |
| teacher        |
+----------------+
2 rows in set (0.00 sec)

单数据源与多数据源:

# 数据源可以为任意的二维表格，当然也可以是select生成的表；
select * from (select stu_id, name from student) as d where name != "Bruce Lee";
+--------+-----------+
| stu_id | name      |
+--------+-----------+
|      4 | Alvin Lee |
|      5 | Alvin Lee |
|      6 | Min Lee   |
|      7 | Alvin Lee |
|      8 | Alvin Lee |
+--------+-----------+

# 数据源为多个时，结果为A*B个表项；
mysql> select * from student, teacher;
+--------+-----------+-----+---------------+--------+---------+-----+
| stu_id | name      | age | register_date | tec_id | name    | age |
+--------+-----------+-----+---------------+--------+---------+-----+
|      1 | Bruce Lee |  25 | 2019-01-03    |      1 | Amadeus |  37 |
|      1 | Bruce Lee |  25 | 2019-01-03    |      2 | john    |  51 |
|      3 | Bruce Lee |  25 | 2019-01-03    |      1 | Amadeus |  37 |
|      3 | Bruce Lee |  25 | 2019-01-03    |      2 | john    |  51 |
|      4 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
|      4 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
|      5 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
|      5 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
|      6 | Min Lee   |   2 | 2019-03-31    |      1 | Amadeus |  37 |
|      6 | Min Lee   |   2 | 2019-03-31    |      2 | john    |  51 |
|      7 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
|      7 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
|      8 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
|      8 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
+--------+-----------+-----+---------------+--------+---------+-----+
14 rows in set (0.00 sec)

外键介绍

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

# 新建一张mytable的表，有两个字段 ID 及 status；
create table mytable(
id int auto_increment primary key,
status char(20) not null
);
  
# 新建一张student2的表，将stu_id关联外键mytable的id
create table student2(
stu_id int auto_increment primary key,
name char(20) not null,
age int not null,
KEY `fk_mytable_key` (`stu_id`),
CONSTRAINT `fk_mytable_key` FOREIGN KEY (`stu_id`) REFERENCES `mytable` (`id`)
); 
#  修改外键->先删除，再新建
删除外键关联
mysql> ALTER TABLE student2 drop FOREIGN KEY fk_class_key;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
删除相关的键
mysql> ALTER TABLE student2 drop KEY fk_class_key;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0
新建外键及关联
mysql> ALTER TABLE student2 ADD FOREIGN KEY (`s_id`) REFERENCES `mytable`(`id`);
Query OK, 0 rows affected (2.78 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
# student2表插入数据
如果mytable中没有这个数据，则无法插入
mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Bruce", 25, 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`fcdb`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `mytable` (`id`))
有就可以正常插入
mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Bruce", 25, 1);
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Alvin", 2, 2);
Query OK, 1 row affected (0.12 sec)
  
# student2与mytable相关联的数据删除
mytable无法删除被关联的数据行
mysql> DELETE FROM mytable WHERE `id`=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`fcdb`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `mytable` (`id`))
student2删除关联数据
mysql> DELETE FROM student2 WHERE `s_id`=1;
Query OK, 1 row affected (0.07 sec)
mytable的id=1行，无人关联，则可以删除
mysql> DELETE FROM mytable WHERE `id`=1;
Query OK, 1 row affected (0.10 sec)

Mysql中的NULL

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

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-2个表的交集

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.

mysql> select * from A INNER JOIN B on A.a = B.b;
+---+---+
| A | B |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+

LEFT/RIGHT JOIN

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

mysql> select * from A LEFT JOIN B on A.a = B.b;
+---+------+
| A | B    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 1 | NULL |
| 2 | NULL |
+---+------+
4 rows in set (0.00 sec)

mysql> select * from A RIGHT JOIN B on A.a = B.b;
+------+---+
| A    | B |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+---+
5 rows in set (0.00 sec)

FULL JOIN-2个表的并集

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

# 在mysql中并不支持full_join
mysql> select * from A FULL JOIN B on A.a = B.b;
ERROR 1054 (42S22): Unknown column 'A.a' in 'on clause'
# 可以UNION一个left及一个right来实现FULL_join
mysql> 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 |
| NULL |    7 |
+------+------+
7 rows in set (0.00 sec)

数据库事务

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

•在MySQL中只有使用了Innodb数据库引擎[5.0之后默认]的数据库或表才支持事务 
•事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行 
•事务用来管理insert,update,delete语句 

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

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

在Mysql控制台使用事务来操作

begin; # 开始一个事务
增删改之类的操作
rollback; # 如果中间有些操作异常，回滚，事务内操作均取消；
commit; # 如果没啥异常的，提交即可以写入数据库；

数据库索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

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

索引分单列索引和组合索引:

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；

组合索引，使用多个列来创建索引，这多个列在一起可以确定唯一性，完成对数据查询时的索引功能；

索引对数据库造成的影响:

索引大大提高了查询速度，同时却会降低更新表的速度；

更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

普通索引:加速查询

# 创建方式1：CREATE INDEX indexName ON mytable(username(length)); # 注length的长度建议不超过字符的长度，一般设置等长就ok的；

mysql> CREATE INDEX Myindex on students(name(32));
Query OK, 0 rows affected (1.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建表时创建索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

# 删除索引
alter table student drop index myindex;

# 显示索引
show index from student;

唯一索引：加速查询 和 唯一约束（可含null）

# 创建方式1: create unique index 索引名 on 表名(列名) # 比普通索引多unique关键字

# 创建表时创建唯一索引
create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

# 删除索引
alter table student drop index myindex;

组合索引：将n个列组合成一个索引，当频繁的同时使用n列来进行查询时使用；

# 创建组合索引方式1
create index mulindex on student(name, age);

# 建表时创建组合索引
student | CREATE TABLE `student` (
   `id` smallint NOT NULL AUTO_INCREMENT,
   `name` char(32) NOT NULL,
   `age` tinyint NOT NULL,
   `register_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `mulindex` (`name`,`age`)
 )

Python操作Mysql

在Python中熟知的用来操作Mysql的模块分别是MySQLClient及PyMySQL两个模块，操作基本一样；

win10上安装

# 直接pip安装mysql-python
pip install mysql-python
# 毫无疑问报错，然后按照版本查找mysqlclient
https://www.lfd.uci.edu/~gohlke/pythonlibs/#twisted
# 下载下文件后，安装
pip install D:UsersAdministratorDownloadsmysqlclient-1.4.6-cp37-cp37m-win_amd64.whl
# 然后，我也没安装成功mysql-python,但是啊，从结果来看：
我上面的操作等同于 pip install MySQLClient
在python中可以正常的导入MySQLClient模块，来操作数据库了

# 使用中报错:
MySQLdb._exceptions.OperationalError: (2059, <NULL>)
# 原因为mysql 8.0之后的新版本使用的加密方式变成caching_sha2_password；
# 提供了一个解决办法，在mysql中将加密方式改为老的加密方式；
mysql> ALTER user 'root'@'localhost' identified with mysql_native_password by 'mypassword';
Query OK, 0 rows affected (0.25 sec)
mysql> flush privileges;
# 测试有效

在mysql新增数据<逐条增与批量增>

import MySQLdb

# 建立与数据库的链接
conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123', db='fcdb')
# 选定一个光标，相当于cli里的mysql>
cur = conn.cursor()

name = "Apple2"
age =1

# 两种format语句的方式
reCount = cur.execute('insert into students (name, age) VALUES (%s, %s)', (name, age))
reCount = cur.execute('insert into students (name, age) VALUES (%(name)s, %(age)s)'
                      , {"name":name, "age":age})
# 批量插入的方式
new_students = [
    ("apple10", 10),
    ("apple11", 11),
    ("apple12", 12)
]
ManyCount = cur.executemany('insert into students (name, age) VALUES (%s, %s)', new_students)

print(reCount) # 返回的是操作条目数
print(ManyCount) # 返回的是操作条目数

# 提交操作
conn.commit()

# 关闭光标
cur.close()

# 关闭链接
conn.close()

在mysql中删除数据及修改数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123.com', db='fcdb')
cur = conn.cursor()

# 删除表内容
deCount = cur.execute('DELETE FROM students WHERE age IS NULL ')
# 修改表内容
chCount = cur.execute("UPDATE students SET register=%s WHERE register IS NUll", ("2020-4-1",))

print(deCount, chCount) # 返回的是操作条目数
conn.commit()
cur.close()
conn.close()

在mysql中查找数据
import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123.com', db='fcdb')
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) ？？？
cur = conn.cursor()

# 查表内容
seCount = cur.execute('SELECT * FROM students')

print(cur.fetchone()) # 一条条显示
print(cur.fetchone())
print(cur.fetchone())
# 相对当前位置移动行数
cur.scroll(-1, mode="relative")
print(cur.fetchone())
print(cur.fetchone())
# 直接跳转到某行
cur.scroll(0, mode="absolute")
print(cur.fetchone())

cur.scroll(0, mode="absolute")
# 一次全部显示
print("分割线".center(50, "-"))
allLine = cur.fetchall()
for line in allLine:
    print(line)
cur.scroll(0, mode="absolute")
print("分割线".center(50, "-"))
# 一次显示多条
SomeLine = cur.fetchmany(4)
for line in SomeLine:
    print(line)
    
conn.commit()
cur.close()
conn.close()

end

课件-https://www.cnblogs.com/alex3714/articles/5950372.html

char varcha text-https://blog.csdn.net/mjshldcsd/article/details/78541323

Python操作Mysql->

python-mysqldb : http://www.cnblogs.com/wupeiqi/articles/5095821.html

pymysql : http://www.cnblogs.com/wupeiqi/articles/5713330.html　　

Mysql的扩展知识->

mysql练习题 http://www.cnblogs.com/wupeiqi/articles/5729934.html 

更多mysql知识，请看http://www.cnblogs.com/wupeiqi/articles/5713323.html

ORM框架:http://www.cnblogs.com/alex3714/articles/5978329.html