SDay2之外键、范式、级联、函数、多表、子查询

最新推荐文章于 2024-07-24 21:33:53 发布

Darcy,

最新推荐文章于 2024-07-24 21:33:53 发布

阅读量68

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_54828688/article/details/131409531

版权

外键约束（FK）

定义外键时，需要遵守下列规则
- 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
- 必须为主表定义主键
- 主键不能包含空值null，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
- 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键（唯一性）。
- 外键中列的数目必须和主表的主键中列的数目相同
- 外键中列的数据类型必须和主表中对应列的数据类型相同。
外键的删除
- 级联删除
  - 设置为null
  - 阻止删除
  - 删除主表数据的时候，要保证这个id没有被字表所使用
foreign key

级联删除和级联置空

#mysql中的级联删除和级联置空
#
CREATE TABLE nativePlace(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	province VARCHAR(30) COMMENT '省份',
	city VARCHAR(30) COMMENT '市/区',
	county VARCHAR(30) COMMENT '县',
	Township VARCHAR(30) COMMENT '乡/镇',
	address VARCHAR(100) COMMENT '家庭住址'
)COMMENT = '籍贯表';
#
SELECT * FROM nativePlace;
#
INSERT INTO nativePlace VALUES
(NULL,'江西省', '赣州市', '于都县', NULL, '渡江大道66666号'),
(NULL,'北京市', '海定区', 'A县', NULL, '黄埔路8888号'),
(NULL,'湖南省', '长沙市', 'C县', NULL, '南京路9999号'),
(NULL,'湖北省', '武汉市', 'D县', NULL, '人民路5555号'),
(NULL,'江苏省', '南京市', 'F县', NULL, '长安路8686号');
#
 
#
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	idCard VARCHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
	pname VARCHAR(100) COMMENT '姓名',
	gender VARCHAR(1) COMMENT '性别',
	birthday DATETIME COMMENT '出生日期',
	nation VARCHAR(30) COMMENT '民族',
	nativePlaceId INT COMMENT '籍贯'
)COMMENT = '户口信息表';
#
SELECT * FROM person;
 
#
INSERT INTO person VALUES
(NULL, '420102200808270010', '令狐冲', '男', '2008-08-27', '汉族', 4),
(NULL, '360731199803120010', '韦小宝', '男', '1998-03-12', '汉族', 1),
(NULL, '360731199605210010', '张无忌', '男', '1996-05-21', '汉族', 1),
(NULL, '420102199506080010', '杨过', '女', '1995-06-08', '汉族', 4),
(NULL, '320100199311120010', '段誉', '男', '1993-11-12', '汉族', 5),
(NULL, '430101199201250010', '乔峰', '女', '1992-01-25', '汉族', 3);
 
#
DESC person;
DESC nativePlace;
SHOW CREATE TABLE person;
SHOW CREATE TABLE nativePlace;
SHOW FULL COLUMNS FROM nativePlace;
SHOW FULL COLUMNS FROM person;
#
 
#传统的方式添加外键
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id);
 
#理论上来说，我们应该是先删除从表，再删除主表，但是我们可以通过级联删除来强制删除主表
#注意：级联删除和级联置空是写在从表，如下：
#方式一：级联删除(删除主表记录的同时删除从表相关联记录，心狠手辣型)
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON DELETE CASCADE;
 
#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;
 
#
SHOW INDEX FROM person;
 
#方式二：级联置空(删除主表记录的同时将从表相关联记录的外键的值置为null，温柔留有余地型)
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON DELETE SET NULL;
 
#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;
 
#级联更新
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON UPDATE CASCADE;
 
#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;
 
#级联更新置空
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON UPDATE SET NULL;
 
#
SELECT * FROM person;
SELECT * FROM nativePlace;
 
#删除主表记录
DELETE FROM nativePlace WHERE id = 4;
 
#删除主表记录
DELETE FROM nativePlace WHERE id = 1;
 
#更新主表中的主键
UPDATE nativePlace SET id = 666 WHERE id = 5;
 
#更新主表中的主键
UPDATE nativePlace SET id = 888 WHERE id = 666;

范式

目前关系数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。一般说来，数据库只需满足**第三范式(3NF)**就行了。

第一范式

第一范式主要是保证数据表中的每一个字段的值必须具有原子性，也就是数据表中的每个字段的值是不可再拆分的最小数据单元
属性的原子性是主观的，我们要根据实际项目的需求来设计，比如说地址，如果项目没有说要细分为省，市，县，镇这么具体的话，我们一般就可以不拆分。

第二范式（要有主键）

要求在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的，而且所有的非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
如果知道主键的所有属性的值，我们就可以检索任何元组(行)的任何属性的任何值(要求中的主键可以拓展替换为候选键)

第三范式（不能间隔依赖主键）

第三范式建立在已经满足第二范式的基础上
数据表中的每一个非主键字段都和主键字段直接相关
也就是说数据表中的所有非主键字段不能依赖于其他非主键字段
这个规则的意思是所有非主属性之间不能有依赖关系，它们是互相独立的
这里的主键可以拓展成为候选键

多表关系

主表和副表=主表和从表

一对一

主键关联

一对多

主键绑定非主键

多对多

非主键绑定非主键
需要一张中间表记录关联的数据

笛卡尔积

SELECT * FROM emp,dept;

子查询

in exists嵌套查询

1.先查询出开发部和产品部的部门编号

SELECT did FROM dept WHERE dname='开发部' OR dname='产品部';

2.从emp表中查询这个部门编号等于刚才查出来的部门编号

SELECT * FROM emp WHERE dept_id in (SELECT did FROM dept WHERE dname='开发部' OR dname='产品部');

exists
存在返回true 或者 false，主要取决于是否有结果集

SELECT * FROM emp WHERE dept_id=2222 AND EXISTS (SELECT did FROM dept WHERE dname='市场部');

any=some

– 找到比开发部工资高的员工的姓名
SELECT did FROM dept WHERE dname=‘开发部’;
SELECT salary FROM emp WHERE dept_id=1111;
SELECT ename,salary FROM emp
WHERE salary> any (SELECT salary FROM emp WHERE dept_id=1111);

union union all

UNION——合并且去重
UNION ALL——只合并不去重

合并查询结果集

SELECT ename,age FROM emp WHERE dept_id=1111
UNION
SELECT ename,age FROM emp WHERE dept_id=4444;

-- 查询 年龄大于16岁的部门编号
-- 查询 年龄小于40岁的部门编号
SELECT dept_id FROM emp WHERE age>16
UNION
SELECT dept_id FROM emp WHERE age<40;

函数

字符串函数

SELECT ASCII('admin');-- 97
SELECT CHAR_LENGTH(ename) FROM emp;-- 返回传递的每个整数的字符
-- 返回串联字符串
SELECT CONCAT('My', 'S', 'QL');-- 'MySQL'
SELECT CONCAT('My', NULL, 'QL');-- NULL
SELECT CONCAT(14.3);-- '14.3'
-- 正则
SELECT 'Michael!' REGEXP '.*';
SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
SELECT 'a' REGEXP '^[a-d]';

数值函数

RAND()

rand()是系统自带的获取随机数的函数

无参数：产生获取[0,1)之间的float型的数字

SELECT RAND();

有参数：rand(n)指定随机数生产的种子n,则返回可重复的随机数序列。(两次随机生成的数是重复，即rand出来的结果是伪随机)

SELECT RAND(2);

rand()n：获取n以内的随机数

SELECT RAND()*2;

FLOOR(X)

返回不大于 X 的最大整数值

SELECT FLOOR(1.23), FLOOR(-1.23);

CEIL(X)

返回不小于 X 的最小整数值

SELECT CEILING(1.23);

日期函数

日期函数 最后更新时间 默认是now()
SELECT NOW();
SHOW VARIABLES LIKE'%time_zone';-- 查时区
yyyy-mm-dd
SELECT YEAR('2001-11-20');-- 返回年份
SELECT MONTH('2001-11-20');-- 返回月份
SELECT DAY('2001-11-20');-- 返回天
SELECT WEEK('2001-11-20');-- 返回周数
添加日期
SELECT DATE_ADD('2000-1-1',INTERVAL 30 DAY);
减去日期
SELECT DATE_SUB('2000-1-31',INTERVAL 30 DAY);
相差的时间
SELECT DATEDIFF('2000-1-30','2000-1-31');
//后面的日期比前面的日期晚一天

流程函数

SELECT case 2 WHEN 2 THEN 'two' WHEN 3 THEN 'three' else 'more' END;
SELECT case 10 WHEN 2 THEN 'two' WHEN 3 THEN 'three' else 'more' END;
-- if()函数
SELECT if(2>3,'true','false');
SELECT if(2<3,'true','false');
-- IFNULL
SELECT IFNULL(1,2);-- 如果expr1不是null，返回expr1，否则返回expr2

排序函数

row_number()

依次递增排名，无重复排名
将select查询到的数据进行排序，每一条数据加一个序号
当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同
即第一名100分，第二名98分，第三名98分，第四名95分…；

rank()

相同分数有重复排名，但是重复后下一个人按照实际排名
对查询数据进行排序，值相同的排序后序号也会相同，注意rank()函数排序序号是不连续的
遇到相同成绩的时候，和row_number()不同
即第一名100分，第二名98分，第二名98分，第四名92分…；

dense_rank()

和rank()类似
对查询数据进行排序，值相同的排序后序号也会相同，注意dense_rank()函数排序序号是连续的，这也是与rank()函数的区别
即第一名100分，第二名98分，第二名98分，第三名92分…；

ntile(group_num)

将查询数据分成group_num数目的组数，ntile(1)就是将查询数据分成一组，ntile(2)就是将查询数据分成二组，以此类推；

开窗函数

Darcy,

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
SDay2之外键、范式、级联、函数、多表、子查询

将查询数据分成group_num数目的组数，ntile(1)就是将查询数据分成一组，ntile(2)就是将查询数据分成二组，以此类推；对查询数据进行排序，值相同的排序后序号也会相同，注意dense_rank()函数排序序号是连续的，这也是与rank()函数的区别。对查询数据进行排序，值相同的排序后序号也会相同，注意rank()函数排序序号是不连续的。即第一名100分，第二名98分，第二名98分，第四名92分…即第一名100分，第二名98分，第二名98分，第三名92分…返回不小于 X 的最小整数值。
复制链接

扫一扫