本篇博客大概讲述一下DQL查询,多表之间的关系举例以及实现,三种约束,一二三范式以及数据库的备份。
1.DQL查询
1.排序查询
Order by 子句
排序方式:
ASC:升序排列
DESC:降序排列
当有多个排序字段时,只有排在前面的条件值一样时,才会排后边字段的序。
select * from student3 order by age desc;
2.聚合函数
聚合函数:将一列数据作为一个整体,然后进行纵向的计算
Count:计算个数
Max:最大值
Min:最小值
Sum:计算和
Avg:计算平均值
注意,计算式不包括NULL值。
select sum(math) from student3 ;
3.分组查询
分组查询
Group by 分组字段;
select sex,avg(math),count(id) from student3 where math > 20 group by sex having count(id) > 2;
4.分页查询
Limit 开始的索引,每页的条数;
开始的索引 = (第几页 - 1)* 每页的条数;
分页查询是MySQL的方言。
select * from student3 limit 4,2;
2.约束
1.主键约束:primary key
含义:非空且唯一;一张表只能有一个主键;主键就是表中记录的唯一标识。
创建表时,添加主键约束
删除主键约束:ALTER TABLE student5 DROP PRIMARY KEY;
添加主键约束:ALTER TABLE student5 MODIFY id INT PRIMARY KEY;
2.非空约束
创建表时添加约束
删除约束:ALTER TABLE student2 CHANGE NAME NAME VARCHAR(20);
添加约束:ALTER TABLE student2 CHANGE NAME NAME VARCHAR(20) NOT NULL;
3.唯一约束
创建表时添加唯一约束 直接在后边加上UNIQUE
删除约束:ALTER TABLE student4 DROP INDEX phone_number ;
添加约束:ALTER TABLE student4 MODIFY phone_number VARCHAR(20) UNIQUE;
4.外键约束
让表与表之间产生联系,从而保证数据的正确性。
创建表时添加外键约束:CONSTRAINT 外键名称 FOREIGN KEY (本表定义 的外键名) REFERENCES 参考表明(参考表明的主键)
删除外键约束:Alter table 表名 drop foreign key 外键名;
添加外键约束:Alter table 表名 add foreign key constraint 外键名称 references 参考表名(参考表的主键)
级联操作:在常见外键约束时,直接在后边加上级联操作。
级联更新:on update cascade;
级联删除:on delete cascade;
CREATE TABLE dep(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
CREATE TABLE student8(
id INT PRIMARY KEY AUTO_INCREMENT,
class VARCHAR(50),
cid INT,
CONSTRAINT dep_id FOREIGN KEY(cid)REFERENCES dep (id)
);
3.多表之间的关系
多表之间的关系分为三种:
1.一对一:这种没意思,在任意一张表上添加外键只想另一张表的主键;
2.多对一:这种可能比较常见,在多对一的关系中,在多的一方添加外键指向1的主键
3.多对多:在多对多的情况下,我们可以添加一张中间表,这张中间表至少要有两列字段,然后把这两列字段设为共享主键,然后分别设置外键指向两方表的主键。
做一个旅游网页的实例。思路分析:1.首先有一个旅游分类表,例如是一个蜜月游这种,然后又一张旅游路线表,里面包含桂林到阿富汗,桂林到希腊的路线等,然后还得有一个用户类,用户可以挑选很多条线路;2.分析。分类表与旅游路线之间是一对多的关系,旅游路线是多,分类表是1,因此,可以在路线表上建立一个外键指向分类表的主键,然后是用户与旅游路线之间是多对多的关系,需要创建一张中间表,其实也就是收藏界面。
CREATE TABLE tab_catgory(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL UNIQUE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_catgory(id)
);
CREATE TABLE user1(
uid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
);
CREATE TABLE tab_favo(
uid INT,
rid INT,
PRIMARY KEY(uid,rid),
FOREIGN KEY (uid)REFERENCES user1(uid),
FOREIGN KEY (rid)REFERENCES tab_route(rid)
);
4.范式
概念:设计数据库时,需要遵循的一些规范。大概遵循1NF,2NF,3NF就够了。
分类:
- 第一范式(1NF):每一列都是不可分割的原子项;
- 第二范式(2NF):在第一范式的基础上消除部分函数依赖
函数依赖:
完全函数依赖:
部分函数依赖:
传递函数依赖:
码:如果在一张表中,属性或者是属性组可以被其他属性完全依赖,则称这个属性或者是属性组为码;
主属性:码属性组中的所有属性
非主属性:除码属性组之外,其余的属性
- 第三范式(3NF):在第二范式的基础上消除传递函数依赖
5.数据库的备份与还原
- 命令行模式:
备份:mysqldump -uroot -proot db1 > d://a.sql;
还原:登录之后,先创建一个数据库,然后进入数据库,然后执行source d://a.sql;
图形化界面模式: