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、增删改

增 改

删