MySQL学习笔记（二）

MySQL普通查询

一般顺序：

• select …聚合函数 from 表名
• where …
• group by …
• having …
• order by …
• limit …;

1. 聚合函数

• avg(字段名)：该字段的平均值
• max(字段名)：该字段的最大值
• min(字段名)：该字段的最小值
• sum(字段名)：该字段所有记录的和
• count(字段名)：统计该字段记录的个数

eg1:找出表中攻击力最大值
mysql> select max(attack) from class_1;

eg2:找出表中记录了多少人，一般使用 id 获取
mysql> select count(id) from class_1;

eg3:找出攻击力大于200的英雄数量
mysql> select count(id) from class_1 where attack > 200;

2. group by

给查询结果进行分组

• group by 后字段名必须要为 select 后的字段，查询字段和group by 后字段不一致,则必须对该字段进行聚合处理(聚合函数)。

eg1:计算每个国家的平均攻击力
mysql> select country,avg(attack) from sanguo group by country;

eg2:查询所有国家的男英雄中，英雄数量最多的前2名的，国家名称及英雄数量
mysql> select country,count(id) as number from sanguo 
mysql> where gender="m" group by country 
mysql> order by number desc 
mysql> limit 2;

3. having 语句

对分组聚合后的结果进行进一步筛选

• having 语句通常与 group by 联合使用，having 语句存在弥补了 where 关键字不能与聚合函数联合使用的不足，where 只能操作表中实际存在的字段, having 操作的是聚合函数生成的显示列。

eg：找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
mysql> select country,avg(attack) as number from sanguo 
mysql> group by country 
mysql> having avg(attack)>105 
mysql> order by avg(attack) desc 
mysql> limit 2;

4. distinct 语句

不显示字段重复值

• distinct 和 from 之间所有字段都相同才会去重， distinct 不能对任何字段做聚合处理，但可以对去重后的字段进行聚合操作。

eg1：表中都有哪些国家
mysql> select distinct country from sanguo;

eg2：查询表中名字国家唯一的值
mysql> select distinct name,country from sanguo;

eg3：计算一共有多少个国家
mysql> select count(distinct country) from sanguo;

5. 查询表记录时做数学运算

运算符 : - * / % **

eg1: 查询时显示攻击力翻倍
mysql> select name, attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力 * 2
mysql> update sanguo set attack=attack*2 where country="蜀国";

索引概述

定义

对数据库表的一列或多列的值进行排序的一种结构(Btree 方式)

B 树特点：

1. 全部节点均包含索引(id) 数据(‘Tom_0’)。
2. 范围查询，从根节点遍历至指定数据。

B 树特点：

1. 非叶子节点只保存索引。【树的宽度优于 B 树，从而降低了磁盘 IO】
2. 叶子节点保存所有的索引和数据。
3. 叶子节点之间相互连接形成链表结构。【范围查询】

优点

加快数据检索速度

缺点

占用物理存储空间 (/var/lib/mysql) 当对表中数据更新时,索引需要动态维护,降低数据维护速度

索引示例

 cursor.executemany(SQL,[data1,data2,data3])

以此IO执行多条表记录操作,效率高,节省资源

1、开启运行时间检测

mysql> show variables like '%pro%';
mysql> set profiling=1;

2、执行查询语句（无索引）

mysql> select name from students where name='Tom99999';

3、查看执行时间

mysql> show profiles;

4、在name字段创建索引

mysql> create index name on students(name);

5、再执行查询语句

mysql> select name from students where name='Tom88888';

6、查看执行时间

mysql> show profiles;

索引分类

普通(MUL) and 唯一(UNI)
1、使用规则

• 1、可设置多个字段
• 2、普通索引 :字段值无约束,KEY标志为 MUL
• 3、唯一索引(unique) :字段值不允许重复，但可为 NULL，KEY标志为 UNI
• 4、哪些字段创建索引:经常用来查询的字段、where条件判断字段、order by 排序字段

2、创建普通索引 and 唯一索引

• 创建表时

create table 表名(
字段名 数据类型,
字段名 数据类型,
index(字段名),
index(字段名),
unique(字段名)
);

example

mysql> create table student_test (
mysql> id int primary key auto_increment, 
mysql> name varchar(20), 
mysql> phone varchar(20), 
mysql> index(id), index(name), unique(phone)
mysql> );

• 在已有表中创建
  create [unique] index 索引名 on 表名(字段名);

• 查看索引
  1、desc 表名;
  –> KEY标志为:MUL 、UNI
  2、show index from 表名\G;

• 删除索引
  drop index 索引名 on 表名;

主键(PRI)and自增长(auto_increment)

使用规则

• 1、只能有一个主键字段
• 2、所带约束 :不允许重复,且不能为NULL
• 3、KEY标志(primary) :PRI
  4、通常设置记录编号字段id,能唯一锁定一条记录
  创建
  创建表添加主键

来源：https://www./content-2-403651.html