MySQL

11、分组查询

1、roup　by的使用

group by 可用于单个字段分组，也可用于多个字段分组

--根据gender字段分组 select gender from student group by gender; 指定以指定字段进行分组，select后只能出现该字段 --根据gender和name字段分组 select name,gender from student group by name,gender;

2、group by + group_concat()的使用

group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割。 select gender,group_concat(name) from student group by gender; roup_concat(name)指的是统计性别为男的人的姓名有哪些 group_concat()相当于聚合函数函数

3、group by+聚合函数

select gender,avg(age) from student group by gender;

4、group by + HAVING

HAVING作用和where类似都是过滤数据的，但HAVING是过滤分组数据的，只能用于group by 根据性别汇总所有人数并统计人数大于2的性别 select gender,count() from student group by gender HAVING count() > 2;

5、group by + WITH ROLLUP

WITH ROLLUP的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果 根据gender字段进行分组，汇总总人数： select gender,count(*) from student group by gender with rollup; 根据gender字段进行分组，汇总所有人的年龄：（不是求和） select gender, group_concat(age) from student group by gender with rollup;

12、内连查询

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。 连接查询分为： 1、内连接查询 2、左连接查询 3、右连接查询 4、自连接查询

1、内连接查询

查询两个表中符合条件的共有记录 语法： select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段 说明： inner join 就是内连接查询关键字 on 就是连接查询条件

内连接根据连接查询条件取出两个表的‘交集’。

准备两个表classes和student

当前python1数据库中包含两张表，即：

使用内连接查询学生表和班级表 select * from student as s inner join classes as c on s.c_id = c.id;

只显示学生姓名和班级 select s.name,c.name from student as s inner join classes as c on s.c_id = c.id;

2、左连接

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充。左连接查询时，左表的数据都会存在，右表内无数据时在返回的结果会是空值。 左连接语法： select 字段 from 表1 left join 表2 on 表1.字段 = 表2.字段 说明： 1、left join 就是左连接查询关键字。 2、on就是连接查询条件 3、表1是左表，表2是右表。

在student表中添加一行数据，使班级为空

select * from student s left join classes c on s.c_id=c.id;

3、右连接

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充。右连接查询时，右表的数据都会存在，左表内无数据时在返回的结果会是空值。 左连接语法： select 字段 from 表1 right join 表2 on 表1.字段 = 表2.字段 说明： 1、right join 就是左连接查询关键字。 2、on就是连接查询条件 3、表1是左表，表2是右表。

select * from student s right join classes c on s.c_id=c.id;

4、自连接

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。 终端内执行sql文件，用source 文件

5、子查询

在select语句中，嵌入了另外一个select语句，那么内嵌入的select语句称之为子查询语句，外部那个select语句则为主查询。

主查询和子查询的关系: 1、子查询是嵌入到主查询中。 2、子查询是辅助主查询的，要么充当条件，要么充当数据 3、子查询是可以独立存在的语句，是一条完成整的select语句。

查询大于平局年龄的学生 select * from student where age>(select avg(age) from student);

13、数据库设计三大范式

范式：对设计数据库提出的一些规范，目前有迹可循的共有8种范式，一般遵循3范式即可。 1、第一范式（1NF）：强调的是列的原子性，即列不能够在分成其他几列。 2、第二范式（2NF）：满足1NF，另外包含两部分内容，意识表必须有一个主键，二是非主键字段必须完全依赖主键，而不能只依赖主键的一部分 3、第三范式（3NF）：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

14、E-R模型

E-R模型即实体-关系模型，E-R模型就是描述数据库存储数据的结构模型。

15、外键约束

1、作用：

外键约束：对外键字段的值进行更新和插入时会引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性。

2、对于已存在的字段添加外键约束

为c_id字段添加外键约束 student表中的c_id用的是classes表中的id数据 alter table student add foreign key(c_id) references classes(id);

3、在创建数据表时设置外键约束

CREATE TABLE teacher ( id int unsigned NOT NULL AUTO_INCREMENT, name varchar(20) COLLATE utf8mb4_general_ci NOT NULL, s_id int unsigned NOT NULL, PRIMARY KEY (id,s_id DESC), KEY 1 (s_id), CONSTRAINT 1 FOREIGN KEY (s_id) REFERENCES school (id) ); 在创建表时加上 foreign key('s_id') references school('id')

4、删除外键

alter table teacher drop FOREIGN KEY 1（外键名称） CREATE TABLE teacher ( id int unsigned NOT NULL AUTO_INCREMENT, name varchar(20) COLLATE utf8mb4_general_ci NOT NULL, s_id int unsigned NOT NULL, PRIMARY KEY (id,s_id DESC), KEY 1 (s_id) );

5、修改表结构

alter table 表名 change 旧列名 新列名 int not null,change brand_name brind_id int not not null;

alter table 可以同时修改多个字段信息

Python与MySQL交互

pymysql的使用

1、导入pymysql包

import pymysql

2、创建连接对象

调用pymysql模块中的connect()函数来创建来连接对象。 conn = connect（参数列表） 参数host:连接的mysql主机，如果是本机则为 'localhost' 参数port:连接的mysql主机端口，默认是3306 参数user:连接的用户名 参数password：连接的密码 参数database：数据库的名称 参数charset:通信采用的编码方式，推荐使用utf8

3、获取游标对象

获取游标对象的目标就是执行sql语句，完成对数据库的增删改查操作

cur = conn.cursor()

游标操作使用说明： 1、使用游标执行sql语句：cur.execute(要执行的sql语句)执行sql语句，返回受影响的行数，主要执行insert、update、delete、select语句 2、获取查询结果集中的一条数据：cur.fetchone()返回一个元组，如（1:“zhangsan”） 3、获取查询结果集中的所有数据：cur.fetchall()返回一个元组，如（1:“zhangsan”）,（2:“lisi”） 4、关闭游标：cur.close()表示和数据库操作完成

4、使用

1、查询

2、增删改

增 改

删

事务

1、介绍

事务就是用户定义的一系列执行SQL语句的操作，要么完全的不执行，它是一个不可分割的工作执行单元。 事务的四大特性： 1、原子性 2、一致性 3、隔离性 4、持久性

1、原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交完成，要么全部失败回滚，对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性 2、一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态 3、隔离性：通常来说，一个事务所作的修改操作在提交事务之前，对于其他事务来说是不可见的。 4、持久性：一旦事务提交，则其所作的修改会永久保存到数据库。

事务能够保证数据的完整性和一致性，让用户的操作更加安全。

2、事务的使用

在使用事务之前，先要确保表的存储引擎是innodb类型 说明： 1、常用表的存储引擎是innoDB和MyISAM 2、innoDB是支持事务的 3、MyISAM不支持事务，优势是访问速度快，对事物没有要求或者select、insert为主的都可以使用该引擎来创建表

修改表的存储引擎使用：alter table 表名 engine=引擎类型

开启事务： begin: 或者 start transaction;

说明： 1、开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中 2、MySQL数据库默认采用自动提交（autocommit）模式，如果没有显示的开启一个事务，那么每条SQL语句都会被当作一个事务执行提交操作 3、当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit或rollback表示该事务结束。

提交事务： commit;

回滚事务： rollback;

pymysql里面的conn.commit()操作就是提交事务 pymysql里面的conn.rollback()操作就是回滚事务

索引

1、介绍

索引在mysql中也加‘键’,他是一个特殊文件，它保存这数据表里所有记录的位置信息。可以通过索引来提高数据库的查询效果。

1.1、查看表中所有索引

show index from 表名 说明：主键列会自动创建索引；添加外键约束时也会自动创建索引

1.2、索引的创建

alter table 表名 add index 索引名可选

例：给classes表中的name添加所以i你 alter table classes add index my_name(name)

索引名不指定，默认使用字段名

1.3、索引的删除

alter tables 表名 drop index 索引名

1.4、验证索引性能操作

mySQL中开启运行时间检测：

set profiling = 1;

要执行的SQL语句

查看执行时间： show profiles;

1.5、联合索引

联合索引又叫复合索引，即一个索引覆盖表中两个或多个字段，一般用在多个字段一起查询的时候 alter table 表名 add index(列名1,列名2,...) 联合索引的好处： 减少磁盘空间开销，因为每创建一个索引，其实就是创建一个索引文件，那么就会增加磁盘空间的开销。

1.6、联合索引的最左原则

在使用联合索引的时候，我们要遵循一个最左原则，即index(name,age)支持name、name和age组合查询，而不知支持单独age查询，因为没有用到创建的联合索引。

说明：在使用联合索引的查询数据时后一定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效。

1.7、MySQL中索引的优点和缺点和使用原则

优点： 1、加快数据的查询速度

缺点： 1、创建索引会耗费时间和中占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加。

使用原则： 1、通过优缺点对比，不是索引越多越好，而是需要自己合理使用 2、对经常和更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引。 3、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。 4、在一字段上相同值比较多不要建立索引，比如在学生表的性别字段只有男女两个不同值，相反的，在一个字段上不同值较多可以建立索引。