MySQL(3)
表与表之间的关系
表关系:一对多,多对多,一对一,没有关系
怎么判断:换位思考
1、一对多:以员工表和部门表为例,站在员工表的基础上,思考一个员工是否可以对应多个部门,站在部门表的基础上,思考一个部门是否可以对应多个员工,外键通常建在查询需求比较大的表中。
2、多对多:以书籍表和作者表为例,站在书籍表的基础上,一个书籍信息是否可以对应多个作者信息,再站在作者表的基础上,一个作者信息是否可以对应多个书籍信息。
(1)多对多的表关系需要创建第三张表 以书籍表和作者表为例,创建一张第三张表书籍与作者的关系表。字段为id book_id author_id,外键关系创建在第三张表中。
(3) 一对一 外键字段建在查询频率高的表中,一对一和一对多,就差一个约束条件unique。
外键约束条件
1、在创建表的时候,需要先创建被关联表。
2、在插入数据的时候,应该先确保被关联表格中有数据。
3、在插入新数据的时候,外键字段只能填写被关联表中字段的值。
4、如果想要数据之间自动修改和删除需要在外键创建时添加额外的配置
on update cascade 级联更新
on delete cascade 级联删除
💐 由于外键有实质性的诸多约束,当表特别多的时候,外键增多,反而会增加耦合程度。所以在实际开发项目中,有时候并不会使用外键创,而是使用sql语句层面,建立逻辑意义上的关系。第三张表也可以不绑定关系。
操作表的SQL语句的补充
1、添加字段
alter table t1 add name varchar(32);
alter table t1 add name varchar(32) first;
alter table t1 add name varchar(32) last;
alter table t1 add name varchar(32) after id;
2、删除字段
alter table t1 drop name;
3、修改字段
change 修改字段类型和名字
modifiy 修改字段类型
4、修改表名
alter table t1 rename t2;
SQl语句查询关键字
select和from
from:控制查询哪张表
select:查询表里的哪些字段
where筛选
where:相当于筛选功能
模糊查询:没有明确的筛选条件
关键字:like;
关键符号:%匹配任意个数的任意字符;\_(短下划线)表示匹配单个任意字符。
比如说查询员工姓名中含有字母o的员工姓名和薪资
select name,salary from emp where name like '%o%';
注意:(1)between...and 在...和...之间
(2)针对null不能用等号,只能用is
比如select name from emp where post_comment is not null;
group by分组
分组:按照某个指定的条件将单个单个的个体分成一个个整体,分组之后只能直接获取到分组的数据,其他数据不能直接获取。
比如:select post from emp group by post;
而select * from emp group by post;分组之后取出的事每个组的第一条数据。
针对5.6需要自己设置sql_mode set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
聚合函数
聚合函数主要是配合分组一起使用
(1)max(获取最大值)
select post,max(salary) from emp group by post;
(2)min(获取最小)
select post,min(salary) from emp group by post;
(3)sum(获取总和) select post,sum(salary) from emp group by post;
(4)avg(平均值) select post,avg(salary) from emp group by post;
(5)count(个数) select post,count(id) from emp group by post;
⚠️:
(1)聚合函数可以起别名 select post,sum(salary) as 'total' from emp group by post;
(2)在统计数量的时候,最好以有唯一标识的字段比较好。
补充 concat 字符拼接
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
having过滤
where和having都是有筛选功能,但是有区别,where是在分组之前对数据进行筛选,而having是在分组之后对数据进行过滤
distanct
对有重复展示的数据进行去重操作,一定是有重复的数据。
select distinct id,age from emp;
select distinct post from emp;
order by排序
select * from emp order by age asc; 默认升序排,
select * from emp order by salary desc; 降序,
select * from emp order by age desc, salary asc;先按照age降序排,在年纪相等的情况下再按照薪资升序排。
🌼例题:统计各部门年龄在10岁以上的员工的平均工资,并且保留平均工资大于1000的部门,然后对平均工资进行排序。
select post, avg(salary) from emp where group by post age>10 having avg(salary) >1000 order by avg(salary);
limit分页
(1) 限制展示条数
select * from emp limit 3;
(2) 查询工资最高的人的详细信息 select * from emp order by salary desc limit 1;
(3) 分页显示
select * from emp limit 0,5; 第一个参数表示起始位置,第二个参数表示的是条数。
regexp正则
select * from emp where name regexp '^j\.\*(n|y)$';