聚合操作
聚合函数
方法 | 功能 |
---|---|
avg(字段名) | 该字段的平均值 |
max(字段名) | 该字段的最大值 |
min(字段名) | 该字段的最小值 |
sum(字段名) | 该字段所有记录的和 |
count(字段名) | 统计该字段记录的个数 |
eg1 : 找出表中的最大年龄的值?
select max(age) from employee;
eg2 : 表中共有多少个人?
select count(name) as number from employee;
聚合分组
- group by
给查询的结果进行分组
e.g. : 计算每个性别的平均身高
select sex,avg(height) from employee group by sex;
e.g. : 对多个字段创建索引,此时多个字段都相同时为一组
select sex,age,count(*) from class1 group by sex,age;
聚合筛选
- having语句
对分组聚合后的结果进行进一步筛选
eg1 : 找出平均年龄大于18的前2名,显示名字和平均年龄
select name,avg(age) from employee
group by name
having avg(age)>18
order by avg(age) DESC
limit 2;
注意
- having语句必须与group by联合使用。
- having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段。
去重语句
三国表
- distinct语句
不显示字段重复值
eg1 : 表中都有哪些国家
select distinct name,country from sanguo;
注意: distinct和from之间所有字段都相同才会去重
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)
唯一索引(unique) :字段值不允许重复,但可为 NULL,KEY标志为 UNI
- 主键索引(PRI)
一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL,KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录
索引创建
- 创建表时直接创建索引
create table 表名(
字段名 数据类型,
字段名 数据类型,
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);
- 在已有表中创建索引:
create [unique] index 索引名 on 表名(字段名);
e.g.
create unique index name_index on cls(name);
- 主键索引添加
alter table 表名 add primary key(id);
- 查看索引
1、desc 表名; --> KEY标志为:MUL 、UNI。
2、show index from 表名;
- 删除索引
drop index 索引名 on 表名;
alter table 表名 drop primary key; # 删除主键
- 扩展: 借助性能查看选项去查看索引性能
set profiling = 1; 打开功能 (项目上线一般不打开)
show profiles 查看语句执行信息
外键约束和表关联关系
外键约束
- 约束 : 约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性
- foreign key 功能 : 建立表与表之间的某种约束的关系,由于这种关系的存在,能够让表与表之间的数据,更加的完整,关连性更强,为了具体说明创建如下部门表和人员表。
- 示例
# 创建部门表
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(8,2) DEFAULT 250.00,
hire_date date NOT NULL,
dept_id int
) ;
上面两个表中每个人员都应该有指定的部门,但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门,这显然是不合理的。
- 主表和从表:若同一个数据库中,B表的外键与A表的主键相对应,则A表为主表,B表为从表。
- foreign key 外键的定义语法:
[CONSTRAINT symbol] 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 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 person drop foreign key dept_fk;
# 查看外键名称
show create table person;
注意:删除外键后发现desc查看索引标志还在,其实外键也是一种索引,需要将外键名称的索引删除之后才可以。
- 级联动作
- 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
表关联设计
- 一对一关系
一张表的一条记录一定只能与另外一张表的一条记录进行对应,反之亦然。
举例 : 学生信息和学籍档案,一个学生对应一个档案,一个档案也只属于一个学生
create table student(id int primary key auto_increment,name varchar(50) not null);
create table record(id int primary key auto_increment,
comment text not null,
st_id int unique,
constraint st_fk foreign key(st_id) references student(id)
on delete cascade
on update cascade
);
- 一对多关系
一张表中有一条记录可以对应另外一张表中的多条记录;但是反过来,另外一张表的一条记录
只能对应第一张表的一条记录,这种关系就是一对多或多对一
举例: 一个人可以拥有多辆汽车,每辆车登记的车主只有一人。
create table person(
id varchar(32) primary key,
name varchar(30),
sex char(1),
age int
);
create table car(
id varchar(32) primary key,
name varchar(30),
price decimal(10,2),
pid varchar(32),
constraint car_fk foreign key(pid) references person(id)
);
- 多对多关系
一对表中(A)的一条记录能够对应另外一张表(B)中的多条记录;同时B表中的一条记录
也能对应A表中的多条记录举例:一个运动员可以报多个项目,每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。
CREATE TABLE athlete (
id int primary key AUTO_INCREMENT,
name varchar(30),
age tinyint NOT NULL,
country varchar(30) NOT NULL,
description varchar(30)
);
CREATE TABLE item (
id int primary key AUTO_INCREMENT,
rname varchar(30) NOT NULL
);
CREATE TABLE athlete_item (
id int primary key auto_increment,
aid int NOT NULL,
tid int NOT NULL,
CONSTRAINT athlete_fk FOREIGN KEY (aid) REFERENCES athlete (id),
CONSTRAINT item_fk FOREIGN KEY (tid) REFERENCES item (id)
);
E-R模型
- 定义
E-R模型(Entry-Relationship)即 实体-关系 数据模型,用于数据库设计
用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系
- 实体、属性、关系
实体
1、描述客观事物的概念
2、表示方法 :矩形框
3、示例 :一个人、一本书、一杯咖啡、一个学生
属性
1、实体具有的某种特性
2、表示方法 :椭圆形
3、示例
学生属性 :学号、姓名、年龄、性别、专业 ...
感受属性 :悲伤、喜悦、刺激、愤怒 ...
关系
1、实体之间的联系
2、一对一关联(1:1)
3、一对多关联(1:n)
4、多对多关联(m:n)
表连接
如果多个表存在一定关联关系,可以多表在一起进行查询操作,其实表的关联整理与外键约束之间并没有必然联系,但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。
- 简单多表查询
多个表数据可以联合查询,语法格式如下:
select 字段1,字段2... from 表1,表2... [where 条件]
e.g.
select * from dept,person where dept.id = person.dept_id;
- 内连接
内连接查询只会查找到符合条件的记录,其实结果和表关联查询是一样的,官方更推荐使用内连接查询。
SELECT 字段列表
FROM 表1 INNER JOIN 表2
ON 表1.字段 = 表2.字段;
select * from person inner join dept on person.dept_id =dept.id;
- 笛卡尔积
笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。
select * from person inner join dept;
- 左连接 : 左表为主表,显示右表中与左表匹配的项
SELECT 字段列表
FROM 表1 LEFT JOIN 表2
ON 表1.字段 = 表2.字段;
select * from person left join dept on person.dept_id =dept.id;
# 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;
- 右连接 :右表为主表,显示左表中与右表匹配的项
SELECT 字段列表
FROM 表1 RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
select * from person right join dept on person.dept_id =dept.id;
注意:我们尽量使用数据量大的表作为基准表,即左表