数据库之主从表、查询、约束
时间:2019-11-28 19:15 来源:linux.it.net.cn 作者:IT
知识概要：！！！
1.主要掌握排序查询，聚合函数的种类，分组和分页查询；
2.了解对于字段的约束，包括主键约束，非空，唯一，自增长，默认；
3.理清楚主表和从表的关系，掌握外键的使用格式
4.总结多表之间的关系，一对一，一对多，多对多
第一章 .查询记录【重点】
1.1排序查询
格式：
elect *from 表名 order by 字段 ASC/DESC；
特点：
能根据提供的字段进行升序和降序的排列，提供查询结果，
但不会改变数据库内部的排列顺序
1.2聚合函数
函数种类：
聚合函数 作用
max(字段) 用来求该字段的最大值
min(字段) 求字段中的最小值
sum（字段） 求字段值的和
avg（字段） 求字段值的平均值
count（*） 计算出字段的个数
语法格式：
select 聚合函数（字段名/列名）from 表名；
1.3分组查询
格式：
select *from 表名 group by 字段
使用：
通常分组查询会和count（*）函数搭配使用，
因为需要统计分组后各个小组的人数情况
where 和 having 过滤条件的区别：
WHERE HAVING
分组前进行过滤使用 分组后进行数据过滤操作
where中不能使用聚合函数 having中可以使用聚合函数
1.4分页查询
格式：
select *from 表名 limit a , b; （索引默认从0开始）
a: 开始的索引
b: 限制显示的记录个数
特点：
当知道页数n和显示的记录个数m时，可以计算出开始的索引
公式： 索引位置 = （n-1）* m;
查询语句的语法小结
select *from ... where ... group by....having ...
order by ... limit ...
-- 执行的顺序：from >> where >> group by >> select >> having >> order by >> limit
第二章.约束
2.1主键约束
1.主键的概念：
表中的唯一标识
2.特点：
唯一且不为空，和 UNIQUE AND NOT NULL 等价
3.自增长：
和主键搭配使用，使主键的id能够自增
4.使用：
primary key auto_increment
2.2非空，唯一，默认值
非空 NOT NULL 限制字段不能为空
唯一 UNIQUE 限制字段唯一不可重复
默认值 DEFAULT 如果没有给字段赋值，则使用默认值
第三章. 多表
3.1主表和从表
当表①中的数据是来源于表②时, 表①为从表, 表②为主表.
3.2 外键
外键: 从表中的某个字段是来源于主表的, 那么这个字段称之为外键
添加外键: 从表中添加一个外键, 指向主表的主键.
创建表时添加
create table 表 (
… ,
CONSTRAINT 外键名 foreign key(字段) references 主表(主键字段)
)
修改表时添加
alter table 表 add CONSTRAINT 外键名 foreign key(字段) references 表（字段);
删除外键
ALTER TABLE 表 drop foreign key 外键名称;
3.3 外键的练习
-- 创建主表
CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20)
)
-- 创建从表
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price DOUBLE,
pcount INT,
cid INT,
CONSTRAINT pro_cid_cat_cid_fk FOREIGN KEY(cid) REFERENCES category(cid)
)
-- 添加数据
INSERT INTO category (cname) VALUES ('手机数码'), ('交通工具');
INSERT INTO product VALUES (NULL, 'iphoneX', 5399, 40, 1);
INSERT INTO product VALUES (NULL, '华为P30', 8499, 100, 1);
INSERT INTO product VALUES (NULL, '小牛电动车', 8988, 10, 2);
INSERT INTO product VALUES (NULL, '捷安特自行车', 4288, 30, 2);
-- 以下的操作都会报错
-- 添加商品信息, 这个商品信息的分类不存在
INSERT INTO product VALUES (NULL, 'AJ8', 26999, 1, 3);
-- 修改商品表中分类信息, 改为不存在的分类
UPDATE product SET cid = 3 WHERE cid = 2;
-- 修改分类表中的分类信息
UPDATE category SET cid = 3 WHERE cid = 2;
-- 删除主表中的分类信息
DELETE FROM category WHERE cid = 2;
3.4 外键约束(了解)
外键的级联操作
如果修改或者删除了主表中带有外键约束的字段, 会将对应的内容都修改或删除
语法:
创建表时, 直接在外键后面添加: on delete cascade on update cascade
添加外键约束时添加: alter table 表 add constraint … on delete cascade on update cascade
3.5小结
主表和从表: 表①中的数据是来源于表②, 表①是从表, 表②是主表
外键: 从表中来源于主表的字段, 指向主表中的主键(具有唯一约束的字段)
格式: constraint 外建名 foreign key(外键字段) references 主表(主键字段)
知识点-多表间关系
一对多
多对多
一对一
3.1 一对多【重点】
一对多(1 : n): 例如: 班级和学生, 部门和员工, 客户和订单, 分类和商品
3.2 多对多【重点】
多对多(n : m) 例如: 老师和学生, 学生和课程, 用户和角色
3.3一对一【了解】
一对一(1:1) 例如: 老公和老婆, 用户和用户信息
4.小结
一对多: 在多的一方创建一个外键, 指向一的一方的主键
多对多: 创建一张第三方表, 该表至少有两个字段,这两个字段分别作为外键, 指向另外两张表的主键
(责任编辑：IT)
| 知识概要：！！！ 1.主要掌握排序查询，聚合函数的种类，分组和分页查询； 2.了解对于字段的约束，包括主键约束，非空，唯一，自增长，默认； 3.理清楚主表和从表的关系，掌握外键的使用格式 4.总结多表之间的关系，一对一，一对多，多对多 第一章 .查询记录【重点】 1.1排序查询 格式： elect *from 表名 order by 字段 ASC/DESC； 特点： 能根据提供的字段进行升序和降序的排列，提供查询结果， 但不会改变数据库内部的排列顺序 1.2聚合函数 函数种类： 聚合函数 作用 max(字段) 用来求该字段的最大值 min(字段) 求字段中的最小值 sum（字段） 求字段值的和 avg（字段） 求字段值的平均值 count（*） 计算出字段的个数 语法格式： select 聚合函数（字段名/列名）from 表名； 1.3分组查询 格式： select *from 表名 group by 字段 使用： 通常分组查询会和count（*）函数搭配使用， 因为需要统计分组后各个小组的人数情况 where 和 having 过滤条件的区别： WHERE HAVING 分组前进行过滤使用 分组后进行数据过滤操作 where中不能使用聚合函数 having中可以使用聚合函数 1.4分页查询 格式： select *from 表名 limit a , b; （索引默认从0开始） a: 开始的索引 b: 限制显示的记录个数 特点： 当知道页数n和显示的记录个数m时，可以计算出开始的索引 公式： 索引位置 = （n-1）* m; 查询语句的语法小结 select *from ... where ... group by....having ... order by ... limit ... -- 执行的顺序：from >> where >> group by >> select >> having >> order by >> limit 第二章.约束 2.1主键约束 1.主键的概念： 表中的唯一标识 2.特点： 唯一且不为空，和 UNIQUE AND NOT NULL 等价 3.自增长： 和主键搭配使用，使主键的id能够自增 4.使用： primary key auto_increment 2.2非空，唯一，默认值 非空 NOT NULL 限制字段不能为空 唯一 UNIQUE 限制字段唯一不可重复 默认值 DEFAULT 如果没有给字段赋值，则使用默认值 第三章. 多表 3.1主表和从表 当表①中的数据是来源于表②时, 表①为从表, 表②为主表. 3.2 外键 外键: 从表中的某个字段是来源于主表的, 那么这个字段称之为外键 添加外键: 从表中添加一个外键, 指向主表的主键. 创建表时添加 create table 表 ( … , CONSTRAINT 外键名 foreign key(字段) references 主表(主键字段) ) 修改表时添加 alter table 表 add CONSTRAINT 外键名 foreign key(字段) references 表（字段); 删除外键 ALTER TABLE 表 drop foreign key 外键名称; 3.3 外键的练习 -- 创建主表 CREATE TABLE category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) ) -- 创建从表 CREATE TABLE product ( pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20), price DOUBLE, pcount INT, cid INT, CONSTRAINT pro_cid_cat_cid_fk FOREIGN KEY(cid) REFERENCES category(cid) ) -- 添加数据 INSERT INTO category (cname) VALUES ('手机数码'), ('交通工具'); INSERT INTO product VALUES (NULL, 'iphoneX', 5399, 40, 1); INSERT INTO product VALUES (NULL, '华为P30', 8499, 100, 1); INSERT INTO product VALUES (NULL, '小牛电动车', 8988, 10, 2); INSERT INTO product VALUES (NULL, '捷安特自行车', 4288, 30, 2); -- 以下的操作都会报错 -- 添加商品信息, 这个商品信息的分类不存在 INSERT INTO product VALUES (NULL, 'AJ8', 26999, 1, 3); -- 修改商品表中分类信息, 改为不存在的分类 UPDATE product SET cid = 3 WHERE cid = 2; -- 修改分类表中的分类信息 UPDATE category SET cid = 3 WHERE cid = 2; -- 删除主表中的分类信息 DELETE FROM category WHERE cid = 2; 3.4 外键约束(了解) 外键的级联操作 如果修改或者删除了主表中带有外键约束的字段, 会将对应的内容都修改或删除 语法: 创建表时, 直接在外键后面添加: on delete cascade on update cascade 添加外键约束时添加: alter table 表 add constraint … on delete cascade on update cascade 3.5小结 主表和从表: 表①中的数据是来源于表②, 表①是从表, 表②是主表 外键: 从表中来源于主表的字段, 指向主表中的主键(具有唯一约束的字段) 格式: constraint 外建名 foreign key(外键字段) references 主表(主键字段) 知识点-多表间关系 一对多 多对多 一对一 3.1 一对多【重点】 一对多(1 : n): 例如: 班级和学生, 部门和员工, 客户和订单, 分类和商品 3.2 多对多【重点】 多对多(n : m) 例如: 老师和学生, 学生和课程, 用户和角色 3.3一对一【了解】 一对一(1:1) 例如: 老公和老婆, 用户和用户信息 4.小结 一对多: 在多的一方创建一个外键, 指向一的一方的主键 多对多: 创建一张第三方表, 该表至少有两个字段,这两个字段分别作为外键, 指向另外两张表的主键 (责任编辑：IT) |