外键，查询关键字

目录

• 自增特性
• 约束条件之外键
• 查询关键字

内容 自增特性

自增不会随着数据的删除而回退

删除数据但无法重置主键

truncate

删除数据并重置主键值

约束条件之外键 简介

给表1加入一个外键，这个外键就是表2中的学号字段

外键字段就是用来记录表与表之间数据的关系，保持数据的一致性、完整性

一对多关系

关系的判断记住四字口诀：换位思考

一对多关系，外键字段建在"多"的一方

外键SQL语句

如果表中有外键字段，那么建议你先编写普通字段，最后再考虑外键字段

在创建表字段的时候也可以给每个字段添加中文注释：comment

foreign key的约束效果：

foreign key(dep_id) references dep(id)：当前的dep_id是一个外键字段，跟dep表的id进行关联

以NBA球队和球员为例：一个球队可以对应多个球员，但是一个球员只能对应一支球队

• 创建表的时候 应该先创建被关联表（没有外键字段的表）

• 插入数据的时候 应该先插入被关联表（没有外键字段的表），外键字段填入的值只能是被关联表中已经存在的值

• 修改、删除被关联表数据都会报错

级联更新级联删除

级联更新：on update cascade

级联删除：on delete cascade

 

 

在实际工作中外键也可能不会使用，因为外键会消耗额外的资源 并且会增加表的复杂度

表很多的情况下，我们也可以通过SQL语句的形式建立逻辑意义上的表关系

多对多关系

以英雄联盟的英雄和位置为例：一个英雄可以胜任多个位置，同时一个位置可以由不同英雄来胜任

两边都可以，那么就是"多对多"关系

针对多对多关系，需要单独开设第三张表专门存储关系

 

 

针对多对多表关系 ：两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除，绑定之后就不行了

 

 

 

 

一对一关系

用民政局登记表做示范：结婚是两个人登记，需要身份证等重要信息，一个人只能和一个人结婚（性别不强求，现在崇尚婚姻自由），如果想换需要出轨，不不不是离婚，口误

两边都不可以对应多个数据，一对一关系，外键字段建在任意一方都可以，但是推荐建在查询频率较高的较好的一方

为了简单方便（排除同性婚姻，固定男女结婚）

查询关键字 数据准备

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);

查询关键字之select与from

select用于指定查询的字段；from用于指定查询的表

select id,name from mysql.user;

查询关键字其实有先后执行顺序，但是无需过多在意（先执行from）

查询关键字之where筛选

查询id大于等于2，小于等于7的数据（取反加 not）

查询薪资是20000或者18000或者17000的数据（取反加 not）

查询员工姓名中包含字母o的员工姓名和薪资

查询条件如果不是很明确的情况下，我们统一称之为：模糊查询

关键字：like；

关键符号：

%：匹配任意个数的任意字符 _：匹配单个个数的任意字符

查询员工姓名是由四个字符组成的数据

查询岗位描述为空的数据

查询关键字之group by分组

分组：按照指定的条件将单个单个的个体组织成一个个整体

分组的好处在于可以快速统计出某些数据

如何分组：按照部分分组：select * from emp group by post;

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

1. max 统计最大值
2. min 统计最小值
3. sum 统计求和
4. count 统计计数
5. avg 统计平均值

统计每个部门的最高薪资、平均薪资、员工人数、月工资开销、最小的年龄数

统计每个部门下所有员工的姓名、姓名和年龄

字段起别名

 也可以不加as

查询关键字之having过滤

having与where的功能是一模一样的，都是对数据进行筛选

where用在分组之前的筛选，having用在分组之后的筛选（过滤）

统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门：

1.先获取每个部门年龄在30岁以上的员工的平均薪资

2.在过滤出平均薪资大于10000的数据

针对分组之后的数据再次筛选，需要使用having而不是where

查询关键字之distinct去重

去重的前提：数据必须是一模一样的才可以

如果数据有主键肯定无法去重

查询关键字之order by排序

1.按照薪资高低排序

升序：asc（可以省略）；降序：desc

2.先按照年龄升序排序，如果年龄相同，则再按照薪资降序排序

3.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门并按照从大到小的顺序排序

查询关键字之limit分页

分页即限制展示条数，减少资源的消耗

1.限制只展示五条数据

2.分页效果

3.查询工资最高的人的详细信息

查询关键字之regexp正则