MySQL(二)

关于MySQL更为基础的部分请见 enginelong的博客

聚合函数

聚合操作是指在数据查找基础上对数据的进一步正理筛选的行为，实际上聚合操作同样属于数据查询筛选范围。

聚合函数

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

select max(attack) from sanguo;

【注意】
此时SELECT只能写聚合函数，无法查找其他字段

聚合分组

1. GROUP BY(将查询的结果按照某字段进行分组)

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

e.g. : 对多个字段创建索引，此时多个字段都相同时为一组
select age,sex,count(*) from class1 group by age,sex;

【注意】

• 使用分组时，SELECT后的字段为GROUP BY分组的字段和聚合函数，不能包含其他内容。当然，GROUP BY也可以按照多个字段分组，比如：group by A, B，此时必须满足A，B字段同时相同时才会被分为一组。

聚合筛选

1. HAVING语句
   对分组后的结果进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力

select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

【注意】

• HAVING语句必须与GROUP BY联合使用；
• HAVING语句弥补了WHERE关键字不能与聚合函数联合使用的不足，WHERE只能操作表中实际存在的字段

2. DISTINCT(去重语句)
   不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct name,country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

聚合运算

查询表记录时做数学运算
运算符：+、-、*、/、%

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

索引操作

定义

索引是对数据表中一列或者多列的值进行排序的一种结构，使用索引可以快速访问数据表中特定的信息。
- 优点：加快数据表的查询速度，提高查询效率；
- 缺点：占用数据库物理存储空间，当数据表数据更新时，索引需要动态维护，降低数据写入效率

【注意】

• 通常只在查询操作占主要部分的字段上创建索引
• 对于数据量很小的数据表或者以写操作为主要操作的数据表不适合建立索引

索引分类

• 普通(MUL)

• 普通索引：字段值无约束，KEY标志为MUL

• 唯一(UNI)

• 唯一索引：字段值不允许重复，但是可以是NULL，KEY标志位UNI

• 主键(PRI)

• 主键索引：一个数据表中只能有主键(可包括多个字段)，而且主键字段不允许重复，不可以为NULL，KEY标志为PRI，通常设置编号ID等可以唯一锁定数据记录的字段作为主键。

索引创建

• 创建表时直接创建索引

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

• 在已存在的表中创建索引

create [unique] index 索引名 on 表名(字段名);

• 主键索引添加

alter table 表名 add primary key(主键字段);

• 查看索引

1、desc 表名; 
2、show index from 表名;

【注意】

• KEY：MUL(普通索引)，UNI(唯一索引)

• 删除索引

drop index 索引名 on 表名;
alter table 表名 drop primary key; # 删除主键

• 测试索引性能

set profiling = 1;
show profiles # 查看语句执行信息

外键约束和表关联联系

外键约束

- 约束：约束是一种限制，通过对表的行或者列的数据进行限制，从而确保数据表的数据的完整性、唯一性；
- foreign key(外键)：外键建立了表与表之间的某种约束关系，由与约束的存在，可以使得表与表之间关联的数据更加完整，关联性更强。
- 主表和从表：同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表

级联动作

- restrict(默认) : `on delete restrict on update restrict`
	- 当主表删除记录时，如果从表中有相关联记录则不允许主表删除；
	- 当主表更改记录时，如果从表中有相关联记录则不允许主表更改；
- cascade：数据表级联更新，`on delete cascade on update cascade`
	- 当主表删除记录时，如果从表中有相关联记录则级联删除；
	- 当主表更改记录时，如果从表中有相关联记录则级联更改；
- set null：` on delete set null on update set null`
	- 当主表删除记录时，如果从表中有相关联记录则设置为null；
	- 当主表更改记录时，如果从表中有相关联记录则设置为null；

[CONSTRAINT 外键名] FOREIGN KEY（外键字段） 

REFERENCES tbl_name (主表主键)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

创建外键

可以使用CREATE TABLE(建表时创建) 或者ALTER TABLE(为存在的表添加外键)

# 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

# 创建人员表
# 创建表时直接添加外键
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint DEFAULT 0,
  sex enum('m','w','o') DEFAULT 'o',
  salary decimal(10,2) DEFAULT 250.00,
  hire_date date NOT NULL,
  dept_id int ,
  constraint dept_fk foreign key(dept_id) references dept(id));

# 建表后添加外键
alter table person add constraint dept_fk foreign key(dept_id) references dept(id);
) ;

【注意】

• 从表的外键字段数据类型应该与指定的主表主键的数据类型相同

解除外键约束

alter table 表名 drop foreign key 外键名;
# 查看外键名
show create table 表名; 

【注意】

• 删除外键后通过desc会发现索引标志还在，事实上外键同时是一种索引，因此需要手动将外键名称的索引删除。

接下来的操作请见enginelong的博客

OK，先这样吧！！！