今日内容
- DQL:查询语句
- 排序查询
- 聚合函数
- 分组查询
- 分页查询
- 约束
- 多表之间的关系
- 范式
- 数据库的备份和还原
1 DQL:查询语句
- 排序查询
- 语法:order by 子句
*oreder by 排序字段1 排序方式1, 排序字段2 排序方式2…- 排序方式:
*ASC:升序,默认。
· *DESC:降序- 注意:
*如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。- 聚合函数:将一列数据作为一个整体,进行纵向的计算
- count:计算个数
* 一般选择非空的列
* count(*)- max:计算最大值
- min:计算最小值
- sum:计算和
- avg:计算平均值
*注意:聚合函数的计算,排除null值。
解决方案:1.选择不包含非空的列进行计算。2.IFNULL函数- 分组查询
- 语法:group by 分组字段;
- 注意:
- 分组之后查询的字段:分组字段、聚合函数
- where 和 having 的区别?
- where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where后不能跟聚合函数,having可以进行聚合函数的判断。
-- 按照性别分组。分别查询男、女同学的平均分 SELECT sex , AVG(math) FROM student GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
注意:分组和聚合都是在一起配合着使用的,在分组之前我们对表中的数据每一条每一条的过滤,使用where,因为是单独的数据,所以where后面不能加聚合函数,在分组后,我对整体进行过滤使用HAVING,而分组后 的数据是一个整体,所以在HAVING后可以使用聚合函数,分组之后进行排序,一般都使用聚合后的字段进行排序,否则是没有意义的,但是聚合函数的可读性不强,所以我们都会对聚合字段其别名
- 分页查询
- 语法:limit开始的索引,每页查询的条数;
- 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
- select * from student limit 0,3;
limit是一个MySQL“方言”
2 约束
- 概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性
- 分类:
1.主键约束:primary key
- 非空约束:not null
- 唯一约束:unique
- 外键约束:foreign key
- 非空约束:not null 值不能为null
1. 创建表时添加约束 create table stu( id int, name varchar(20) not null );
2.创建表完后,添加非空约束 alter table stu modify name varchar(20) not null;
3.删除name的非空约束 alter table stu modify name varchar(20);
- 唯一约束:unique,值不能重复
1. 创建表时,添加唯一约束 create table stu( id int, phone_number varchar(20) unique ); 注意mysql中,唯一约束限定的值可以有多个null
2. 删除唯一约束 alter table stu drop index phone_number
3. 在创建表后,添加唯一约束 alter table stu modify phone_number varchar(20) unique;
- 主键约束:primary key
- 注意:
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
2.在创建表时,添加主键约束 create table stu( id int primary key, name varchar(20) );
3. 删除主键 alter table stu drop primary key;
4. 创建完表后,添加主键 alter table stu modify id int primary key;
5. 自动增长 1. 概念:如果某一列是数值类型,可以使用auto_increment完成值的自动增长 2. 在创建表时,添加主键约束并完成主键自增长 create table stu( id int primary key auto_increment, name varchar(20) ); 3. 删除自动增长 alter table stu modify id int; 4. 添加自动增长 alter table stu modify id int auto_increment;
- 外键约束:foreign key,让表与表产生关系,从而保证数据的正确性。
1. 在创建表时,可以添加外键 语法: create table 表名( .... 外键列, constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) ); constraint 外键名称:可省略 2. 删除外键 alter table 表名 drop foreing key 外键名称; 3. 创建表之后,添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称); 4. 级联操作 1. 添加级联操作 语法:alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名) on update cascade on delete casecade; 2. 分类: 1. 级联更新:on update cascade 2. 级联删除:on delete cascade
3 数据库的设计
- 多表之间的关系
- 一对一:人和身份证
在任意一方添加唯一外键指向另一方主键- 一对多(多对一):部门和员工
在多的一方建立外键,指向单一方的主键- 多对一:学生和课程
借助第三张中间表,中间至少包含两个字段作为外键,分别指向两张表的主键- 联合主键
CREATE TABLE tab_favorite ( rid INT, -- 线路id DATE DATETIME, uid INT, -- 用户id -- 创建复合主键 PRIMARY KEY(rid,uid), -- 联合主键 FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY(uid) REFERENCES tab_user(uid) );
- 数据库的设计范式
设计数据库时需要遵循一些规范,要遵循后边的范式,必须先遵循前边的所有范式
- 第一范式(1NF):每一列都是不可分割的原子数项
- 第二范式(2NF):在1NF的基础上消除非主属性对主码的部分函数依赖
(每个非主属性完全依赖于主属性)
- 几个概念:
函数依赖:通过A确定唯一的B,则B依赖于A
完全函数依赖:A是属性组,B的确定需要A的所有属性
部分函数依赖:A是属性组,B的确定只需要A的某一些属性
传递函数依赖:B依赖于A,C依赖于B,则C传递依赖于A
码:此属性(组)被其他所有属性完全依赖,则称为码
主属性:码属性组中所有属性
非主属性:除过码属性组的属性- 第三范式(3NF):在2NF的基础上消除传递依赖
(不能存在传递依赖)
4 数据库的备份和还原
- 命令行:
- 备份:mysqldump -y用户名 -p密码 数据库名称 > 保存的路径及文件名
- 还原:
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件: source 文件路径及文件名
- 图形化工具
- 备份:备份导出转储SQL文件
- 还原:执行SQL脚本