Mysql外键与查询关键字

最新推荐文章于 2023-11-03 17:14:23 发布
最新推荐文章于 2023-11-03 17:14:23 发布
阅读量1.9k 收藏 13
点赞数 1
文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_64178950/article/details/124591432
版权

今日内容概要

1 > 自增特性

   自增的作用已经了解但是自增他有他自己的一些特性，在实操过程中当我们将id值设为自增属性之后，当我们删除某个数据后，再次插入数据会发现自增的值就会出现断层了。演示如下：


	 create table t1(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );

在这里插入图片描述
   正常插入数据时没有问题自增没有出现断层，数字连续。


	insert into t1(name) values('jason'),('bob'),('kevin');

在这里插入图片描述
   当我们删除id=4，id= 7 后插入oscar他的id值不会回退。如下：

delete from t1 where id=4;
delete from t1 where id=7;
insert into t1(name) value('oscar');
select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | bob   |
|  3 | kevin |
|  5 | rr    |
|  6 | rr    |
|  8 | oscar |
+----+-------+
6 rows in set (0.00 sec)

在这里插入图片描述
   如何解决呢？ 就是我们必须要在删除数据后再重置主键值，自增属性才会继续连续自增。


mysql> truncate t1;
Query OK, 0 rows affected (1.85 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1(name) values('jason'),('kevin'),('tony');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t1(name) values('oscar');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tony  |
|  4 | oscar |
+----+-------+
4 rows in set (0.00 sec)

在这里插入图片描述

2 > 约束条件之外键

2.1 > 外键简介

   外键前戏:


	创建一张员工表(有以下几个字段)
		id name age dep_name dep_desc
	
	就这样创建一个表有不少的缺陷:
		1.表的重点不清晰
			既可以说是员工表也可以说是部门表
		2.表中的某些字段对应的数据一直在重复
			浪费了存储空间
		3.表的拓展性极差 牵一发而动全身
			耦合度太高 不利于维护
	
	解决方法
		将表一分为二
		员工表						部门表
		id name age					id dep_name dep_desc
	# 拆完之后确实解决了上述三个缺陷，但是也有了一个致命缺点
	# 员工与部门表之间没有了绑定关系
	
	解决方法	
		在员工表中添加一个部门编号字段与部门表中的主键字段相对应
	'''
		该字段就是外键字段
			是用来记录表与表之间的数据关系
	'''

2.2 > 外键关系

   四种外键关系：


	1.一对多
	2.多对多
	3.一对一
	4.没有关系

   对于关系的判断就需要记住一个口诀：换位思考

2.2.1 > 一对多

   关系梳理如下：


	1.以员工表与部门表为例
		先站在员工表的角度
	  	问:一个员工能否对应多个部门
	    答:不可以
		
		再站在部门表的角度
	  	问:一个部门能否对应多个员工
	    答:可以
	    
	结论:
		换位思考之后得出的答案是一个可以一个不可以
		所以关系是"一对多" 部门是'一'员工是'多
		 '''关系表达只能用一对多 不能用多对一'''
		一对多关系 外键字段建在"多"的一方(员工表)

   创建外键SQL语句如下：


	'''
		如果表中有外键字段 那么建议先编写普通字段 最后再考虑外键字段
	'''
	
	create table emp(
		id int primary key auto_increment comment '编号',
		name varchar(32) comment '姓名',
		age int comment '年龄',
		dep_id int comment '部门编号',
		foreign key(dep_id) references dep(id)
	);
	create table dep(
		id int primary key auto_increment comment '编号',
		dep_name varchar(32) comment '部门名称',
		dep_desc varchar(32) comment '部门描述'
	);
'''
	但是按这个顺序你这么些 你会发现员工表创建不出来，就是因为有这 foreign key的约束条件存在
'''

在这里插入图片描述
   foreign key的约束条件具体如下：


	1.创建表的时候 应该先创建被关联表（没有外键字段的表）
	2.插入数据的时候 应该先插入被关联表（没有外键字段的表）
		外键字段填入的值只能是被关联表中已经存在的值
	3.修改、删除被关联表数据都会出现障碍
		需要使用到级联更新来解决这个障碍

   级联更新级联删除的具体作用，就是当我们创建完成员工表和部门表之后向对部门表(被关联表)做修改的时候员工表(关联表)中的关联数据也会做出变动的操做，具体代码如下：



	'''
		再关联表中去添加级联跟新级联删除代码
			on update cascade  # 级联更新
			on delete cascade  # 级联删除
	'''
	create table emp1(
	id int primary key auto_increment comment '编号',
	  name varchar(32) comment '姓名',
	  age int comment '年龄',
	  dep_id int comment '部门编号',
	  foreign key(dep_id) references dep1(id) 
	  on update cascade  # 级联更新
	  on delete cascade  # 级联删除
	);
	create table dep1(
		id int primary key auto_increment comment '编号',
	  dep_name varchar(32) comment '部门名称',
	  dep_desc varchar(32) comment '部门描述'
	);

	"""
	在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
	并且会增加表的复杂度 
	表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
	"""

2.2.2 > 多对多

   关系梳理如下：


	以图书与作者表为例
		1.先站在图书表的角度
	  	问:一本书籍能否对应多名作者
	    答:可以
	  2.再站在作者表的角度
	  	问:一名作者能否对应多本书籍
	    答:可以
	  结论:换位思考之后两边都可以 那么就是"多对多"关系

   创建关联表代码过程如下：


	"""
  		针对多对多关系 需要单独开设第三张表专门存储关系
  	"""
	# 先依此创建书表和作者表(两张表里不写两张表的关系)
	 create table book(
	    id int primary key auto_increment,
	    title varchar(32),
	    price float(10,2)
	);
	
	create table author(
	    id int primary key auto_increment,
	    name varchar(32),
	    gender enum('male','female','others')
	);
	
	# 再创建第三张表专门存储关系的表
	create table book_author(
	    id int primary key auto_increment,
	    author_id int,
	    book_id int,
	    # 将字段author_id 与作者表的id关联
	    foreign key(author_id) references author(id) 
	    on update cascade   # 级联更新
	    on delete cascade,  # 级联删除
	    # 将字段book_id 与书表的id关联
	    foreign key(book_id) references book(id) 
	    on update cascade  # 级联更新
	    on delete cascade  # 级联删除
	);

在这里插入图片描述

2.2.3 > 一对一关系

   关系梳理如下：


	针对qq用户表 其实里面的数据可以分成两类
		热数据:经常需要使用的数据
	    	eg:qq号码 座右铭 个人简介 爱好
	  	冷数据:不怎么经常需要使用的数据
	    	eg:邮箱 电话 学校 ...
	  	为了节省资源并降低数据库压力 会将表一分为二
		  	用户表
		    	存使用频率较高的数据字段
		    用户详情表
		    	存使用频率较低的数据字段
	1.先站在用户表的角度
		问:一个用户数据能否对应多个用户详情数据
	  答:不可以
	2.再站在用户详情表的角度
		问:一个用户详情数据能否对应多个用户数据
	  答:不可以
	结论:换位思考之后两边都不可以 那么关系可能有两种

   创建关联表代码过程如下：


create table User(
	id int primary key auto_increment,
	name varchar(32),
	gender enum('male','female','others'),
	user_detail_id int unique,  
	foreign key(user_detail_id) references UserDetail(id) 
	on update cascade   # 级联更新
	on delete cascade,  # 级联删除
);
create table UserDetail(
	id int primary key auto_increment,
	phone bigint,
	age int
);

3 > 查询关键字

   数据准备。


create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	sex enum('male','female') not null default 'male', #大部分是男的
	age int(3) unsigned not null default 28,
	hire_date date not null,
	post varchar(50),
	post_comment varchar(100),
	salary double(15,2),
	office int, #一个部门一个屋子
	depart_id int
);

   插入记录。


#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

在这里插入图片描述

3.1 > 查询关键字之select与from


	select用于指定查询的字段
	from用于指定查询的表
	select id,name from mysql.user;
	'''查询关键字其实有先后执行顺序 但是无需过多在意!!!'''

3.2 > 查询关键字之where筛选

   1. 查询id大于等于3 小于等于6的数据


	select * from emp where id>=3 and id<=6;
	select * from emp where id between 3 and 6;

在这里插入图片描述

   2. 查询薪资是20000或者18000或者17000的数据


	select * from emp where salary=20000 or salary=18000 or salary=17000;
	select * from emp where salary in (20000,18000,17000);

在这里插入图片描述

   3. 查询id小于3或者大于6的数据

	
	select * from emp where id not between 3 and 6;

在这里插入图片描述

   4. 查询薪资不在20000,18000,17000的数据


	select * from emp where salary not in (20000, 18000, 17000);

在这里插入图片描述
   5. 查询岗位描述为空的数据

	
	select * from emp where post_comment = null; # 报错
		'''针对null只能用is 而不能用符号'''
	select * from emp where post_comment is null;

在这里插入图片描述
   6. 查询员工姓名中包含字母o的员工姓名和薪资


	'''
		当查找信息不准确时 我们统一称为‘模糊查询’
		使用关键字
			like: 开启模糊查询关键字
		
		关键符号
			%:匹配任意个数的任意字符
			_:匹配单个个数的任意字符
	'''

	select name,salary from emp where name like '%o%';

在这里插入图片描述
   7. 查询员工姓名是由四个字符组成的数据


	select * from emp where name like '____';
	select * from emp where char_length(name)=4;

在这里插入图片描述

3.3 > 查询关键字之group by分组

   什么是分组?

	
	按照指定的条件将单个单个的个体组织成一个个整体
	例如：
		按照性别分组 按照部门分组 按照年龄分组 按照国家分组...

   为何需要分组?


	分组的好处在于可以快速统计出某些数据
	eg:
		最大薪资 平均年龄 最小年龄 总人数 ...

   如何分组?

	
	'''按照部门分组 '''
	select * from emp group by post;	# 5.7及以上版本会直接报错	
	
	'''
		mysql5.7及以上版本默认自带sql_mode=only_full_group_by
		该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段

		原因是分组的目的就是按照分组的条件来管理诸多数据 
		最小单位应该是分组的依据而不是单个单个的数据

		如果是MySQL5.6及以下版本 需要自己手动添加
		可以直接在配置文件里面直接改方便不然需要将之前写的所有配置再写一遍
		set global sql_mode = only_full_group_by......
	'''
	# 所以真确的写法是下面这种
	select post from emp group by post;

在这里插入图片描述

3.4 > 聚合函数

   聚合函数是专门用于分组之后的数据统计。


	max				统计最大值
	min				统计最小值
	sum				统计求和
	count			统计计数
	avg				统计平均值
	
ps:是否需要分组 我们可以在题目或者需求中发现

   1 > 统计每个部门的最高薪资


	select post,max(salary) from emp group by post;

在这里插入图片描述
   2 > 统计每个部门的平均薪资


	select post,avg(salary) from emp group by post;

在这里插入图片描述
   3 > 统计每个部门的员工人数


	select post,count(id) from emp group by post

在这里插入图片描述
   4 > 统计每个部门的月工资开销


	select post,sum(salary) from emp group by post;

在这里插入图片描述
   5 > 统计每个部门最小的年龄数


	select post,min(age) from emp group by post;

在这里插入图片描述

3.5 > 间接获取分组以外其他字段的数据

   1 > 统计每个部门下所有员工的姓名


	select post,group_concat(name) from emp group by post;

在这里插入图片描述
   2 > 统计每个部门下所有员工的姓名和年龄


	select post,group_concat(name, age) from emp group by post;

在这里插入图片描述
   3 > 给字段起别名


	select post,group_concat(name) as '姓名' from group by post;
	'''
		也可以不写as关键字 但是语义不明确 建议还是加上
		select post,id '序列'，name '姓名' from emp; 
	'''

在这里插入图片描述
在这里插入图片描述

乔治我的M4呢？
关注
  • 1
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL约束（主键，唯一，非空，外键）
12-14
MySQL约束（主键，唯一，非空，外键） 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。 分类： 1. 主键约束：primary key 2. 非空约束：not null 3. 唯一约束：unique 4. 外键约束：foreign key 目录MySQL约束（主键，唯一，非空，外键）1. 非空约束2. 唯一约束3. 主键约束4. 外键约束例子 1. 非空约束 非空约束是为了让数据在存入数据时，保证值不为null。 关键字：not null 1. 创建表时添加约束 CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NUL
MySQL外键使用详解
12-15
最近有开始做一个实验室管理系统，因为分了几个表进行存储·所以要维护表间的关联··研究了一下MySQL的外键。 （1）只有InnoDB类型的表才可以使用外键，mysql默认是MyISAM，这种类型不支持外键约束 （2）外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作； （3）外键的作用： 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！ （4）建立外键的前提： 两个表必须是InnoDB表类型。 使用在外键关系的域必须为索引型(Index)。 使用在外键关系的域必须与数据类型相似 （5）创建的步骤 指定主键关键字：
MySQL联表查询的简单示例
01-19
MySql会用到联表查询，对于刚学习的新手来说，可能会理解起来有难度。下面这篇文章就来给大家详细介绍MySQL联表查询的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧 关系型数据库，免不了表之间存在各种引用与关联。这些关联是通过主键与外键搭配来形成的。所以，取数据时，很大情况下单张表无法满足需求，额外的数据则需要将其他表加入到查询中来，这便是 JOIN 关键字完成的操作。 MySQL 中 JOIN, CROSS JOIN 和 INNER JOIN 三者语法功能上相同，可互换，而 SQL 标准中，INNER JOIN 需要搭配 ON 语句。 多表联合查询时，可省略
mysql建立外键
01-20
建立外键的前提：本表的列必须与外键类型相同(外键必须是外表主键)。 外键作用： 使两张表形成关联，外键只能引用外表中的列的值！ 指定主键关键字： foreign key(列名) 引用外键关键字： references <外键表名>(外键列名) 事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action 例如： outTable表 主键 id 类型 int 创建含有外键的表： create table temp( id i
mysql查询指定数据库的所有外键
qq_44445489的博客
05-25 646
mysql中查询指定数据库的所有外键 SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE //注意指定约束类型, 是为了过滤掉 "PRIMARY KEY, UNIQUE"这两个索引 CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA = '数据库名' ...
MySQL语法：mysql数据库常用sql语句
Ben_boba的博客
05-24 1043
MySQL 外键约束
喝醉酒的小白
08-11 1005
此外，如果已经存在数据，那么在添加外键约束之前，请确保orders表中的customer_id值与customers表中的id列的值是匹配的，以避免违反外键约束。执行这个 ALTER TABLE 命令后，数据库会将外键约束应用于相应的列，确保只有在 “customers” 表中存在对应的 “id” 值时，才能插入到包含 “customer_id” 列的表中。以上命令将向你指定的表中的 “customer_id” 列添加外键约束，该列将引用 “customers” 表中的 “id” 列。id列被指定为主键。
mysql添加外键语句
qq_33329834的博客
03-04 7672
sql语句格式： 1.添加外键约束：altertable从表addconstraint外键（形如：FK_从表_主表）foreignkey(从表外键字段)references主表(主键字段); 如：alter table t_book add constraint `fk` foreign key (`bookTypeId`) references t_booktype(`...
MySQL3_外键及查询
qq_39457834的博客
05-19 3431
文章目录MySQL_外键及查询1.数据的完整性(1).保证实体的完整(2).保证域的完整性(3).引用的完整性(4).自定义完整性2.外键3.实体之间的关系(1).一对一:主键关系(2)一对多|多对一(3)多对多4.数据库的设计5.数据的规范(1)第一范式(2)第二范式(3)第三范式6.规范化和性能7.查询语句语句位置顺序(1)字段表达式(2)from子句(3)dual(肚哦)表(4)where子句(5)运算符(6)聚合函数(7)通配符(8)模糊查询8.分组查询9.回溯统计10.having(条件)11.o
mysql查询某张表的所有外键_[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？...
weixin_39616686的博客
01-19 1812
[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？问题描述如题，[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？方案一查看一张表的所有外键：SELECTTABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROMINFORMATION...
总结三种MySQL外键约束方式
12-14
如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。 　外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常...
使用Masonry实现动画
weixin_44838373的博客
04-02 578
使用Masonry实现动画 [self setNeedsUpdateConstraints]; [UIView animateWithDuration:time animations:^{ [animationView mas_remakeConstraints:^(MASConstraintMaker *make) { 写上新的约束 }]; [self layoutIf...
MySQL中sql查询语句-------外键查询
YinChenLeilei的博客
08-03 2万+
一.为了便于说明,先创建两张相关联的表 DROP TABLE IF EXISTSdept; CREATE TABLEdept( deptnoint(11) NOT NULL, dnamevarchar(20) default NULL, locvarchar(20) default NULL, PRIMARY KEY (deptno) ) ENGINE=InnoDB DEFAU...
MySQL查询数据库、表、字段、索引、唯一键、外键、存储过程、视图是否存在
最新发布
时光下的旅途
11-03 423
MySQL查询数据库、表、字段、索引、唯一键、外键、存储过程、视图等是否存在的SQL语句
MYSQL——外键约束
zhou641694375的博客
10-31 3118
常见的6种约束/规则：主键约束、外键约束、非空约束、唯一性约束、默认值约束、自增约束、` 如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段称为表二的外键。`以公共关键字作为主键的表为主键表（父表、主表）以公共关键字作为外键的表为外键表（从表、外表） ` 注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束（不包括主键约束）。`
Oracle 中用sql语句查看外键约束和表结构
KevinChen2019的博客
12-02 8470
constraint_type：表示约束类型，R表示外键，P表示主键。constraint_name：表示约束名称。
MySQL数据库——MySQL外键约束（FOREIGN KEY）
热门推荐
Itmastergo的博客
04-20 2万+
MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
sql语句之子查询，外键和索引
atian1的博客
03-23 249
【代码】sql语句之子查询。
MySQL外键设置之Cascade、NO ACTION、Restrict、SET NULL详解
weixin_53679823的博客
08-28 3610
cascade：在父表上 update / delete记录时，同步 update / delete掉子表的匹配记录 No action：如果子表中有匹配的记录, 则不允许对父表对应候选键进行update / delete操作 Restrict：同 no action, 都是立即检查外键约束 set null：在父表上 update / delete记录时，将子表上匹配记录的列设为 null要注意子表的外键列不能为 not null default：父表有变更时, 子表将外键列设置成一个默认的值但Innod
mysql数据库外键索引
09-17
MySQL数据库中外键索引是用来保证数据的完整性和一致性的。外键索引是指在一个表中，创建一个列作为另一个表的主键或唯一键的引用。 在MySQL中，外键可以通过以下方式进行定义： ``` CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(50), INDEX email_idx(email) ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, product VARCHAR(50), FOREIGN KEY (user_id) REFERENCES users(id) ); ``` 这段代码创建了两个表：users和orders。在orders表中，user_id列被定义为一个外键，它引用了users表的id列。在创建外键时，MySQL会自动创建一个索引来加速关联查询。 外键索引可以提高查询效率，并且可以保证数据的完整性和一致性。例如，在上面的例子中，如果我们尝试在orders表中插入一个无效的user_id值，MySQL将会拒绝该操作，因为该值不在users表中存在。 总之，外键索引是MySQL中非常重要的一个特性，可以保证数据的完整性和一致性，同时提高查询效率。在设计数据库时，应该合理使用外键索引来提高数据的质量和性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值