表关系判断：
表关系分类：
1.一对多
2.多对多
3.一对一
4.没有关系
表关系的判断：
思想：换位思考
一对多：
外键字段建在多的一方。
1.以员工表与部门表为例：
1.先站在员工表角度
问：一个员工能否对应多个部门
答：不可以
2.再站在部门表角度
问：一个部门能否对应多名员工
答：可以
结论：两边一个可以，一个不可以，那么表关系就是一对多。
SQL语句实现：
1.先创建基本字段
2.在考虑外键字段
create table emp(
id int primary key auto_increment,
emp_name varchar(32),
emp_salary int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
注意：
1.在创建表时先创建被关联的表（没有外键字段的表）。
2.在录入数据的时候，先录入被关联表的数据。
3.修改或删除被关联的数据，没法做修改。
级联更新级联删除SQL实现：
create table emp(
id int primary key auto_increment,
emp_name varchar(32),
emp_salary int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade #级联更新
on delete cascade #级联删除
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
多对多
针对多对多关系，外键字段并不会建在任何一张表，而是单独开设一张新表，单独存储
SQL实现：
create table book(
id int primary key auto_increment,
title varchar(32),
price float(8,2)
);
create table book(
id int primary key auto_increment,
title varchar(32),
price float(8,2)
);
create table booktoauthor(
id int primary key auto_increment,
author_id int,
foreign key(author_id) refrenxes author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
一对一
以用户表与用户详情表为例
先站在用户表角度
问：一名用户能否对应多个用户详情
答：不可以
再站在用户详情表角度：
问：一个用户详情是否能对应多名用户
答： 不可以
针对一对一关系，外键字段建在任何一方都可以，但是推荐你建立在查询频率较高的表中。
SQL实现：
create table userinfo(
id int primary key auto_increment,
name varchar(32),
age int,
detail_id int unique, #设置唯一索引建立一对一关系
foreign key(detail_id) references userinfo_detail(id)
on update cascade
on delete cascade
);
查询关键字
1.select
用来指定表的字段数据
select * from emp;
select id, name from emp;
在工作中一般很少使用*。
2.from
后面跟需要查询的表名。
3.where 筛选数据
1.查询id大于等于3，小于等于6的数据
select id ,name from emp where id >=3 and id <= 6;
select id ,name 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);
模糊查询:
关键字 like
关键符号:
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
3.查询姓名中带有字母o的员工姓名和薪资
select name,salary from emp where name like ‘%o%’;
4.查询姓名中有四个字符组成的员工姓名和薪资
select name,salary from emp where name like ‘____’;
select name,salary from emp where char_length(name) = 4;
5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
7.查询岗位描述为空的员工与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
4.group by 分组
分组
将单个单个的个体按照指定条件分成一个个整体。
分组之后默认只能获取分组的依据，其他字段无法在直接获取（可以间接获取）
但是5.6需要手动开启严格模式
set global sql_mode = ‘STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,ONLY_FULL_GROUP_BY’
聚合函数
分组之后频繁需要使用的
max 最大值
min 最小值
sum 求和
count 计数
avg 平均值
1.每个部门的最高工资
select post,max(salary) from emp group by post;
2.每个部门的最低工资
select post,min(salary) from emp group by post;
3.每个部门的平均薪资
select post,avg(salary) from emp group by post;
4.每个部门的人数
select post,count(id) from emp group by post;
5.每个部门的月工资总和
select post,sum(salary) from emp group by post;
可以使用as起别名，也可以给表起别名
select post as ‘部门’,sum(salary) as ‘总和’ from emp group by post;
查看分组之后的部门名称和每个部门下所有的员工姓名
group_count() 获取分组以外的字段数据，并且支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,’:’,salary) from emp group by post;
concat() 未分组之前使用的拼接功能
select concat(name,’:’,sex) from emp;
concat_ws()
select concat_ws(’:’,name,sex,salary,age) from emp;
group_concat(name,’:’,salary) 获取分组以外的字段数据，
5.having 过滤
功能上having与where是一样的，但是使用位置上有所不同
where在分组之前使用，having在分组之后使用。
举例：
统计各部门年龄在30岁以上的员工
1.先筛选出所有30岁以上的员工
select * from emp where age>30;
2.然后再按照部门分组
select post,avg(salary) from emp where age>30 group by post;
3.分组之后做过滤操作
select post,avg(salary) from emp
where age>30
group by post
having avg(salary)>10000;
6.distinct 去重
带有主键的数据肯定无法去重
select distinct age from emp;
7.order by 排序
默认升序 关键字 asc
降序排序 关键字 desc
select * from emp order by salary desc;
指定多个字段
select * from emp order by age desc,salary asc;
统计各部门年龄在20岁以上的员工的平均工资，并且保留平均高工资大于1000的部门，然后对平均工资进行排序。
不在代码行敲的代价，未知原因报错就是不行，后来也不知道为什么就又好了，神。
select post,avg(salary) from emp
where age>20
group by post
having avg(salary)>1000
order by avg(salary) desc;
在代码行敲的就可以。
select post,avg(salary) from emp
where age>20
group by post
having avg(salary)>1000
order by avg(salary) desc;
8.limit 分页
用来限制数据的显示：
select * from emp limit 5,5;
# 查询工资最高的人的详细信息，先按照工资排序，然后限制展示条数
select * from emp order by salary desc limit 1;
9.regexp 正则
正则表达式 用一些特殊复合的组合筛选出符合条件的数据
select * from emp where name regexp '^j.*(n|y)$';
# '^j.*(n|y)$' j开头 中间无所谓 n或者y结尾
多表查询的思想
1.子查询
分布解决问题
将一条SQL语句的查询结果用括号括起来，作为另一条语句的查询条件。
select * from dep where id in (select dep_id from emp where name=‘jason’)
2.连表操作
前戏(了解)
select * from emp,dep
基于上表筛选数据
为了避免字段冲突可以在字段名前加表名明确。
select * from emp,dep where emp.dep_id = dep.id
现将所有需要用到的表拼接到一起（一张表）
然后就是转换成单表查询
连表操作：
inner join 内连接 连接公共部分
select * from emp inner join dep on emp.dep_id=dep.id;
left join 左连接 以左表为基准展示所有数据，没有的null填充
select * from emp left join deo on emp.dep_id=dep.id;
right join 右连接 以右表为基准展示所有数据，没有的null填充
select * from emp right join deo on emp.dep_id=dep.id;
union 全连接 以所有表为基准展示所有数据，没有的null填充
select * from emp left join deo on emp.dep_id=dep.id
union
select * from emp right join deo on emp.dep_id=dep.id;
建议：在书写SQL语句的时候一定不要想着一次成功，写一点看一点再写一点，慢慢拼凑起来
表字段操作的补充
1.添加表字段
alter table t_name add age int; #默认尾部追加
alter table t_name add gender enum(‘male’,‘female’) after age; #指定追加位置
alter table t_name add 字段名 字段类型 first #追加在开头
2.修改字段
modify只能改字段数据类型完整约束，不能改字段名，但是change可以
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [完整性约束条件…];
3.删除字段
ALTER TABLE 表名 DROP 字段名;
290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
