MySQL数据库高级使用

一、聚合函数

1.聚合函数

命令	作用
count(字段)	计算总行数
max(字段)	求此字段最大值
min(字段)	求此字段最小值
sum(字段)	求此字段之和
avg(字段)	求此字段平均值

2.聚合函数的作用

聚合函数作用：聚合函数会把当前所在表当做一个组进行统计，因此聚合函数也叫组函数

聚合函数有以下几个特点：

• 每个组函数接收一个参数（字段名或者表达式）
• 统计结果中默认忽略字段为NULL的记录
• 不允许出现嵌套 比如sum(max(xx))

二、group分组查询

1.什么是分组

什么是分组? 所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

group by分组

使用特点：

group by的含义： 将查询结果按照1个或多个字段进行分组，字段值相同的为一组

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

2.知识要点

group by
作用:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
注意：group by可用于单个字段分组，也可用于多个字段分组

group by + group_concat()
group_concat(字段名)作用：根据分组结果，使用group_concat()来放置每一个分组中某字段的集合

group by + 聚合函数
作用：聚合函数在和group by结合使用的时候 统计的对象是每一个分组

group by + having
作用：having作用和where类似，但having只能用于group by对分组后的每组数据过滤 而where是用来过滤表数据

group by + with rollup
with rollup的作用是：在数据表最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果

三、limit限制查询

1.问题

大家思考一下：

select * from 淘宝某个数据表 , 会产生什么现象?

由于数据过多很有可能直接死机了

2.limit限制查询

可以使用limit限制取出记录的数量，但limit要写在sql语句的最后

语法：limit 起始记录,记录数

说明：

• 起始记录是指从第几条记录开始取，第一条记录的下标是0
• 记录数是指从起始记录开始向后依次取的记录数

四、连接查询

1.什么是连接

连接能干什么? 当查询结果的数据来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示

2.连接查询

mysql支持三种类型的连接查询，分别为：

• 内连接查询
• 外连接查询
• 自连接查询

3.内连接

内连接查询：查询的结果为两个表符合条件匹配到的数据


以上两个表的连接条件是：学生信息表.cls_id = 班级信息表.id

最终两个表的连接效果：

内连接语法： select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

注意：

1. 内连接：根据连接条件取出两个表 “交集”

2. on 是连接条件 where是连接后筛选条件

4.外连接

4.1 左外连接

左(外)连接查询：查询的结果为两个表匹配到的数据和左表特有的数据

注意：对于右表中不存在的数据使用null填充

4.2 右外连接

右(外)连接查询：查询的结果为两个表匹配到的数据和右表特有的数据

注意：对于左表中不存在的数据使用null填充

4.3 外连接

使用left join实际上左边表的所有数据都会显示出来

4.4 语法

左连接：主表 left join 从表 on 连接条件;
右连接：从表 right join 主表 on 连接条件;

注意：

• 能够使用连接的前提是 , 多表之间有字段上的关联
• 左连接和右连接区别在于主表在SQL语句中的位置, 因此实际左连接就可以满足常见需求

5.自连接

为什么需要自连接?




自连接SQL语句：

select city.* from 
areas as city
inner join
areas as province
on city.pid = province.aidd
where province.atitle = "广东省";

五、子查询

子查询： 把一个查询的结果当做另一查询的条件

子查询分为三类：

• 标量子查询：子查询返回的结果是一个数据(一行一列)
• 列子查询：返回的结果是一列(一列多行)
• 行子查询：返回的结果是一行(一行多列)

六、MySQL实战操作

1.数据准备

• 创建京东数据库：

  create database jing_dong charset=utf8;
  

• 使用京东数据库

  use jing_dong;
  

• 创建一个商品goods数据表

  create table goods(
      id int unsigned primary key auto_increment not null,
      name varchar(150) not null,
      cate_name varchar(40) not null,
      brand_name varchar(40) not null,
      price decimal(10,3) not null default 0,
      is_show bit not null default 1,
      is_saleoff bit not null default 0
  );
  

• 向goods表中插入数据

  insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
  insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
  insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
  insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
  insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
  insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
  insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
  insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
  insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
  insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
  insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
  insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
  insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
  insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
  insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
  insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
  insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
  insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
  insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
  insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
  insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
  

2.goods表查询

-- sql强化演练( goods 表练习)

-- 查询类型 cate_name 为 '超级本' 的商品名称 name 、价格 price ( where )
select name,price from goods where cate_name="超级本";

-- 显示商品的种类
-- 1 分组的方式( group by ) 
select cate_name from goods group by cate_name;

-- 2 去重的方法( distinct )
select distinct cate_name from goods;


-- 求所有电脑产品的平均价格 avg ,并且保留两位小数( round )
select round(avg(price),2) from goods;

-- 显示 每种类型 cate_name (由此可知需要分组)的 平均价格
select avg(price),cate_name from goods group by cate_name;


-- 查询 每种类型 的商品中 最贵 max 、最便宜 min 、平均价 avg 、数量 count
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

-- 查询所有价格大于 平均价格 的商品，并且按 价格降序 排序 order desc

-- 1 查询平局价格(avg_price)
select avg(price) as avg_price from goods;


-- 2 使用子查询
select * from goods where price>(select avg(price) as avg_price from goods) order by price desc;

-- 查询每种类型中最贵的电脑信息(难)

-- 1 查找 每种类型 中 最贵的 max_price 价格
select max(price) as max_price,cate_name from goods group by cate_name;

-- 2 关联查询 inner join 每种类型 中最贵的物品信息
-- select * from goods 
-- inner join
-- (select cate_name,max(price) as max_price from goods group by cate_name) as max_price_goods
-- on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;
select * from goods
inner join
(select max(price) as max_price,cate_name from goods group by cate_name) as max_price_goods
on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;

3.表的优化步骤

为什么要对数据表进行优化？

由于只存在商品信息一张表 其中既有商品信息又有分类名称和品牌名称。当把某个商品信息删除后，本不应该删除的<独立于商品信息的分类和品牌>等信息结果也随着删除商品信息而删除，那么此时存在删除异常。

如果我们创建两张表 “商品种类表” “商品品牌表” 存储对应的信息，就不会出现删除异常了

优化步骤：

• 第一步：创建商品种类表
• 第二步：同步数据到商品种类表中
• 第三步：更新商品信息表数据
• 第四步：修改商品信息表表结构

4.表的优化实现

-- 创建"商品分类"表

-- 第一步：创建表 (商品种类表 goods_cates )

create table if not exists goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);


-- 第二步：同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中

-- 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)
select cate_name from goods group by cate_name;

-- !!!!没有values
insert into goods_cates(name) (select cate_name from goods group by cate_name);


-- 第三步 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表

-- 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
select * from goods_cates inner join goods on goods_cates.name=goods.cate_name;     	
 
-- 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
update (goods_cates inner join goods on goods_cates.name=goods.cate_name) set goods.cate_name=goods_cates.id;


-- 第四步 修改表结构

-- 查看表结构(注意 两个表中的 外键类型需要一致)

-- 修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

-- 创建 商品品牌表 goods_brands

-- 第一步 创建 "商品品牌表" 表
-- 第一种方式 先创建表
create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null);
	
-- 插入数据 brand_name(分组)
-- 按照 分组 的方式查询 goods 表中的所有 种类(brand_name)
insert into goods_brands(name) (select brand_name from goods group by brand_name);
--(注意) 把查询出来的 结果 写入 goods_brands 表里去 ( insert into ) 只插入name


-- 第二种方式 创建表的同时插入数据(了解,不建议使用)
create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null) select brand_name as name from goods group by brand_name;
	
		
-- 第二步 同步数据

-- 通过goods_brands数据表来更新goods数据表 g.brand_name=b.id
update (goods inner join goods_brands on goods.brand_name=goods_brands.name) set goods.brand_name=goods_brands.id;


-- 第三步 修改表结构
-- 通过alter table语句修改表结构 brand_id int unsigned not null
alter table goods change brand_name brand_id int unsigned not null;

七、外键的作用

为什么使用外键？

外键有一个很重要的作用：防止无效信息的插入

注意：

• 关键字：foreign key，只有 innodb 数据库引擎
• 外键本身也是一种约束 和 not null 等约束一样

外键的使用：

-- 外键的使用(了解)

-- 向goods表里插入任意一条数据
insert into goods (name,cate_id,brand_id,price) values('老王牌拖拉机', 10, 10,'6666');

-- 约束 数据的插入 使用 外键 foreign key
alter table goods add foreign key (cate_id) references goods_cates(id);
alter table goods add foreign key(brand_id) references goods_brands(id);

-- 失败原因 老王牌拖拉机 delete
delete from goods where name="老王牌拖拉机";

-- 创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)
create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

-- 如何取消外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;

-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;

总结： 在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

八、视图的概念

什么是视图？

九、视图的使用

-- 视图的作用
	
-- 视图的定义方式
	create view 视图名称(一般使用v开头) as select语句;
	
-- 查出学生的id,姓名,年龄,性别 和 学生的 班级
	select s.id,s.name,s.age,s.gender,c.name as cls_name 
	from students as s 
	inner join classes as c 
	on s.cls_id=c.id;

-- 创建上述结果的视图( v_students )
-- create view v_students as 
create view v_students as 
select s.id,s.name,s.age,s.gender,c.name as cls_name 
	from students as s 
	inner join classes as c 
	on s.cls_id=c.id;

-- 删除视图（drop view 视图名字）
drop view v_students

十、事务的概念及特点

1.事物的作用

2.什么是事物？

3.事务四大特性ACID

第一步和第五步至关重要，如果没有第五步则事务并没有真实发生

原子性（atomicity）

• 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性（consistency）

• 数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失500元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性（isolation）

• 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去500元。）

持久性（durability）

• 一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

十一、事务的使用及ACID特征的验证

-- 事物(ACID)
	
	-- 原子性 一致性
		第一步 打开 终端1 终端2
		第二步 终端1 打开事物 begin
			   终端1 update 表名 set 字段="xxx" where ...;
			   终端1 select * from 表名;  发现数据改变
		第三步 终端2 select * from 表名;  
			   发现数据其实并没有改变 其实这个时候对数据的相关操作信息存在缓存中,
			   当commit之后,这些操作才会一次性的完成
		第四步 终端1 commit 数据数数据真的改变
			   终端2 select * from 表名,数据改变了
			
	-- 隔离性
		第一步 打开 终端1 终端2
		第二步 终端1 打开事物 begin
			   终端1 update 表名 set 字段="xxx" where ...;
		第三步 终端2 update 表名 set 字段="yyy" where ...;
			   发现 处于阻塞状态 
		第四步 终端1 commit
			   终端2 阻塞状态解除 数据修改成 yyy
			   
	-- 回滚(rollback)
		第一步 打开 终端1 begin
		第二步 终端1 update 表名 set 字段="xxx" where ...;
		第三步 rollback 数据返回最开始的原始值
		
	-- 注意 
		-- innodb能使用事物

十二、索引

1.索引的作用

2.索引的使用

查看表中已有索引：show index from 表名;

创建索引：alter table 表名 add index 索引名【可选】（字段名，…）;

删除索引：drop index 索引名称 on 表名;

-- 索引

-- 创建测试表
create table test_index(title varchar(10));

-- 向表中插入10万条数据
python3 insert_data.py

-- 验证索引性能

-- 没有索引
-- 开启时间检测：
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title="ha-99999";
-- 查看执行时间
show profiles;

-- 有索引
-- 给title字段创建索引
alter table test_index add index(title);
-- 查找第1万条数据ha-99999
select * from test_index where title="ha-99999";
-- 查看执行时间
show profiles;

运行效果：

补充：insert_data.py

from pymysql import connect


def main():
    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='mysql', charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()


if __name__ == "__main__":
    main()

3.索引的注意事项

优点：

• 加快数据的查询速度

缺点：

• 创建索引会浪费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间会越来越多

使用原则：

• 1 经常发生数据更新的表避免使用过多的索引
• 2 数据量小的表没有必要使用索引
• 3 数据量较大同时不会频发发生数据更改的表可以使用索引

十三、数据库设计之三范式

1.什么是三范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小

2.范式的划分

3.一范式

第一范式（1NF）：强调的是字段的原子性，即一个字段不能够再分成其他几个字段。

4.二范式

第二范式（2NF）：满足 1NF的基础上，另外包含两部分内容

• 一是表必须有一个主键
• 二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分

单凭OrderID或者ProductID不能唯一确定一条记录，所以主键可以由多个字段共同组成
上表商品单价和商品名称只是从属于产品ID而不从属于订单ID，所以此表属于部分依赖于主键，而不是完全依赖
但是这里的折扣和数量两列是完全依赖于主键的

将上表改为二范式：

5.三范式

第三范式（3NF）：满足 2NF
另外非主键字段必须直接依赖于主键，不能存在传递依赖。

即不能存在：非主键字段 A 依赖于非主键字段 B，非主键字段 B 依赖于主键的情况。

将上表改为三范式：

6.总结

十四、E-R模型及表间关系

1.E-R模型的使用场景

• 对于大型公司开发项目，我们需要根据产品经理的设计，先使用建模工具,
  如：power designer，db desinger等这些软件来画出实体-关系模型(E-R模型)
• 然后根据三范式设计数据库表结构

2.E-R模型

E-R模型即实体-关系模型

E-R模型就是描述数据库存储数据的结构模型

表现形式：

• 实体: 用矩形表示，并标注实体名称
• 属性: 用椭圆表示，并标注属性名称
• 关系: 用菱形表示，并标注关系名称

E-R模型中的三种关系：

• 一对一
• 一对多(1-n)
• 多对多(m-n)

一对一：

一对多(1-n)：

多对多(m-n)：

十五、Python连接MySQL数据库

1.PyMysql模块

安装pymysql第三方包：sudo pip3 install pymysql

1.1 PyMysql使用步骤

1. 导入pymysql包：import pymysql

2. 创建连接对象：connect()
   

3. 获取游标对象：连接对象.cursor()
   

4. pymysql完成数据的增删改查操作：游标对象.execute()
   

5. 关闭游标和连接： 游标对象.close()   连接对象.close() （注意顺序）
   

2.查询操作

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# pymysql完成数据的查询操作
sql = "select * from students;"

# 这里获取的是sql影响的行数
# content = cs.execute(sql)
# print(content)

cs.execute(sql)
content = cs.fetchone()
print(content)
content = cs.fetchall()
print(content)

# 关闭游标和连接
# 先关游标后关连接
cs.close()
conn.close()

3.增删改操作

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# 增加数据
# sql = "insert into students(name) values('老王')"
# cs.execute(sql)
# 删除数据
# sql = "delete from students where id=18"
# cs.execute(sql)
# 修改数据
sql = "update students set name='老王' where id=1;"
cs.execute(sql)

# pymysql完成数据的查询操作
sql = "select * from students;"
# for循环显示数据
cs.execute(sql)
content = cs.fetchall()
for i in content:
    print(i)

# 提交操作
conn.commit()

# 关闭游标和连接
cs.close()
conn.close()

十六、SQL语句参数化

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# 不安全的方式
# 根据id查询学生信息
# find_name = input("请输入您要查询的学生姓名:")
# sql = "select * from students where name='%s'" % find_name
# # 显示所有的数据
# cs.execute(sql)
# content = cs.fetchall()
# for i in content:
#     print(i)

# 安全的方式
# 根据id查询学生信息
find_name = input("请输入您要查询的学生姓名:")
sql = "select * from students where name=%s"
# 显示所有的数据
cs.execute(sql, [find_name])
content = cs.fetchall()
for i in content:
    print(i)

# 关闭游标和连接
cs.close()
conn.close()

上一篇：MySQL数据库基本使用
下一篇：机器学习基础：matplotlib的使用