mysql04--查询表、连表操作
每日测验
- 你所知道的约束条件有哪些
- 表与表之间关系有几种，如何判断
- 创建上述表关系的SQL语法如何书写
昨日内容回顾
- 约束条件
# not null
# zerofill
# unsigned
# default
gender enum('male','female','others') default 'male'
# unique
单列唯一
id int unique
联合唯一
ip int,
port int,
unique(ip,port)
# primary key 主键
1.在限制效果上跟not null + unique一致
id int primary key
2.它还是Innodb存储引擎组织数据的依据
1.使用Innodb规定一张表中必须有且只有一个主键
2.当你没有设置主键的时候
1.从上往下查找非空且唯一的自动升级为主键
2.如果什么都没有设置 那么就会使用内部隐藏的字段作为主键
无法使用到
3.一张表中通常都应该有一个id字段并且该字段应该是主键
# auto_increment
自增列
该限制条件只能加在被设置成键的字段上 并且一般都是跟主键一起使用
id int primary key auto_increment
# 针对主键补充知识点
你在将表中数据删除的时候 主键的自增并不会停止
# truncate 清空数据并重置主键
- 外键
# 外键就是用来在代码层面真正的实现表与表之间的关系
foreign key
- 表与表之间建关系
# 表与表之间的关系只有三种
一对多
在MySQL中 一对多和多对一 都叫一对多
外键字段建在多的那一方
多对多
关系无需建外键 而是单独开设一张表专门用来存储关系
一对一
外键字段建在任意一方均可
但是推荐你建在查询频率较高的表中
"""判断表关系前期不熟练一定要换位思考 慢慢问自己"""
# 一对多判断
图书与出版社
先站在图书表
一本书能否被多个出版社出版 版权问题 不可以！！！
再站在出版社表
一个出版社能否出版多本书 可以
结论:单向的一对多成立 那么表关系就是一对多 书是多的一方
# 外键带来的约束
1.在创建表的时候一定要先创建被关联表
2.在插入数据的时候也要先插入被关联表
3.操作数据的时候 会出现多种限制 同步更新 同步删除
create table publish(
id ...
);
create table book(
id ...
publish_id int,
foreign key(publish_id) references publish(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
# 多对多
图书和作者
先站在图书表
一本书可不可以有多个作者 可以！！！
再站在作者表
一个作者能不能写多本书 可以！！！
结论：图书和作者是双向的一对多 那么表关系就是 多对多
一定要单独开设一张新的表存储表关系
create table book(
id ...
)
create table author(
id ...
)
create table book2author(
id ...
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
)
# 一对一
qq用户表
客户与学生表
"""
当你一张表中的数据并不都是频率需要用到的情况 但是字段有特别的多
那么这个时候你就应该考虑分表 然后做一对一的关联
节省查询时间和传输时间
"""
作者与作者详情
无论站在哪一方都不能成立一对多
双方一对多都不成立那么表关系
一对一
没有关系
create table author(
id ...
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
);
create tabel authordetail(
id ...
);
补充
# 表与表之间如果有关系的话 可以有两种建立联系的方式
1.就是通过外键强制性的建立关系
2.就是自己通过sql语句逻辑层面上建立关系
delete from emp where id=1;
delete from dep where id=1;
创建外键会消耗一定的资源 并且增加了表与表之间的耦合度
在实际项目中 如果表特别多 其实可以不做任何外键处理 直接
通过sql语句来建立逻辑层面上的关系
# 到底用不用外键取决于实际项目需求
- 修改表
alter table t1 rename new_t1;
alter table t1 add 字段名 ...;
alter table t1 add 字段名 ... first;
alter table t1 add 字段名 ... after 旧字段;
alter table t1 drop 字段名;
# modify一般都是用来修改字段的字段类型 约束条件等 不能修改字段名
# alter table t1 modify 字段名 数据类型(宽度) 约束条件
alter table t1 change 旧字段名 新字段名 ...;
- 复制表
# 我们sql语句查询出来的结果也可以看成是一张表(虚拟表)
言外之意 就是针对这个查询结果还可以继续用查询表的语法继续操作该虚拟表
今日内容详细
前期表准备
create table emp(
id int not null unique auto_increment, # 非空且唯一，会默认将ID 设置成主键
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);
# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;
# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
1.几个重要关键字的执行顺序
# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select
# 虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写
你就按照书写顺序的方式写sql
select * 先用*号占位
之后去补全后面的sql语句
最后将*号替换后你想要的具体字段
明天会一直使用 这里先理解
2.where筛选条件
# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age 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);
# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者id大于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 = NULL;
select name,post from emp where post_comment is NULL;
3.group by分组
# 分组实际应用场景 分组应用场景非常的多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
# 1 按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的
返回的是分组之后 每个组的第一条数据
但是这不符合分组的规范:分组之后不应该考虑单个数据
而应该以组为操作单位 (分组之后 没办法直接获取组内单个数据)
如果设置了严格模式 那么上述命令会直接报错
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';
# 设置严格模式之后 分组 默认只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)
"""
什么时候需要分组啊？？？
关键字
每个 平均 最高 最低
聚合函数
max
min
sum
count
avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名 也可以直接省略不写（但是不推荐 因为省略的话语意不明确 容易错乱）
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post; # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; null不行
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat不单单可以支持你获取分组之后的其他字段名 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; # 报错
select t1.id,t1.name from emp as t1;
# 查询每个人的年薪 12薪
select name,salary*12 from emp;
分组注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
# where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000; # 报错
select max(salary) from emp; # 不分组 默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
1 先求所有年龄大于30岁的员工
select * from emp where age>30;
2 再对结果进行分组
select * from emp where age>30 group by post;
select post,avg(salary) from emp where age>30 group by post;
4.having：分组之后的条件筛选
"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000;
5.distinct去重
# 一定要注意 必须是完全一样的数据才可以去重！！！
：一定不要将主键忽视了 有主键存在的情况下 是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
# ORM 对象关系映射 让不懂SQL语句的人也能够非常牛逼的操作数据库
表 类
一条条的数据 对象
字段对应的值 对象的属性
你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
select distinct id,age from emp;
select distinct age from emp;
6.order by排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
# order by
默认是升序 asc 可以省略不写
也可以修改为降序 desc
# 先按照age降序排 如果碰到age相同 则再按照salary升序排
select * from emp order by age desc,salary asc;
# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary) > 1000
order by avg(salary) desc;
7.limit限制展示条数
select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3; # 只展示三条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数
8.正则匹配
select * from emp where name regexp '^j.*(n|y)$';
9.多表操作
前期表准备
# 建表
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'公关');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
9.1多表联合查询
select * from dep,emp; # 结果 笛卡尔积
"""
了解即可 不知道也没关系
"""
select * from emp,dep where emp.dep_id = dep.id;
MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作
所以特地给你开设了对应的方法
inner join 内连接
left join 左连接
right join 右连接
union 全连接
# inner join 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分
# left join 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL
# right join 右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL
# union 全连接 左右两表所有的数据都展示出来
# mysql没办法直接全连接，需要左连接 union 右连接 等
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
9.2子查询
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
# 将一个查询语句的结果当做另外一个查询语句的条件去用
# 查询部门是技术或者人力资源的员工信息
1 先获取部门的id号
select id from dep where name='技术' or name = '人力资源';
2 再去员工表里面筛选出对应的员工
select name from emp where dep_id in (200,201);
select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
# 多表查询就两种方式
先拼接表再查询
子查询 一步一步来
作业
1.整理今日内容
2.完成下列分组查询练习题(以课上建表代码为参考)
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post, avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post, max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post, min(salary) from emp_old group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp_old group by sex;
3.练习拼表操作并理解其意义
4.理解子查询思路体会其意义
# 查询入职日期排名后5位的员工信息, 且同一天入职员工要求并列展示
# 表数据
-- Create table
create table EMP
(
empno INT(4) PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
job VARCHAR(9),
mgr INT(4),
hiredate DATE,
sal FLOAT(7,2),
comm FLOAT(7,2),
deptno INT(2)
);
-- 插入数据
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902,'1980-12-17', 800.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-20', 1600.00, 300.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698,'1981-02-22', 1250.00, 500.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839,'1981-04-02', 2975.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698,'1981-09-28', 1250.00, 1400.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839,'1981-05-01', 2850.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839,'1981-06-09', 2450.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566,'1987-04-19', 3000.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null,'1981-11-17', 5000.00, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500.00, 0.00, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788,'1987-05-23', 1100.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698,'1981-12-03', 950.00, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566,'1981-12-03', 3000.00, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782,'1982-01-23', 1300.00, null, 10);
# 方式一：并列
select
hiredate,
group_concat(empno) as empno,
group_concat(ename) as ename,
group_concat(job) as job ,
group_concat(mgr) as mgr,
group_concat(sal) as sal,
group_concat(comm) as comm,
group_concat(deptno) as deptno
from (SELECT * from emp order by hiredate desc limit 5 ) group by hiredate;
# 方式二：并排
select * from
(select *from emp
where empno in
(select group_concat(empno) as id from (SELECT * from emp order by hiredate desc limit 5 ) as e1 group by hiredate)) as e2
left join
(select *from emp
where find_in_set (empno,
(select group_concat(empno) as id from (SELECT * from emp order by hiredate desc limit 5 ) as e1 group by hiredate having count(empno) > 1)) )as e3
on e2.hiredate = e3.hiredate and e2.empno != e3.empno;
浙公网安备 33010602011771号