新手入门MySQL——篇4

最新推荐文章于 2024-09-24 08:48:48 发布

Onssd

最新推荐文章于 2024-09-24 08:48:48 发布

阅读量342

点赞数

分类专栏： MYSQL 文章标签：数据库开发 python

仅限个人学习使用

本文链接：https://blog.csdn.net/Onssd/article/details/124025890

版权

MYSQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文介绍了数据库中多表关系的一对一、一对多/多对一、多对多，以及如何通过外键约束进行关联。详细阐述了多表联合查询的交叉连接、内连接、外连接和子查询的使用，包括内联接、外联接（左、右、全）、子查询嵌套以及自关联查询。同时，提供了具体的SQL实例，如创建部门和员工表，插入数据，并进行了各种查询操作，如按条件连接查询、聚合函数应用、子查询比较等。内容涵盖了数据库查询的基础和进阶技巧。

摘要由CSDN通过智能技术生成

多表操作

1——多表关系

一对一 (身份证号，学号)
一对多/多对一 (职工与部门)
多对多 (学生与课程) 建立中间表

constraint 外键名称 foreign key 字段名 references 主表名 主键列1, 主键列2, 主键列3
alter table 表名 drop foreign key 外键名称

2——多表联合查询

交叉连接查询 { 笛卡尔积 }

select * from A, B;

内连接 (inner可省略) join

select * from A, B where 条件;
select * from A inner join B on 条件;

外连接查询

select * from A left  outer(可省略) join B on 条件;
select * from A right outer(可省略) join B on 条件;
select * from A full  outer(可省略) join B on 条件;

子查询 { select嵌套 }
表自连接 { 一张表当作多张表使用 }

3——多表查询案例

 -- 1. 创建部门表
create table if not exists dept3 ( 
deptno varchar(20) primary key  comment '部门号',
name varchar(20) comment '部门名称'
);
-- 2. 创建员工表
create table if not exists emp3 (  
eid varchar(20) primary key comment '员工编号',
ename varchar(20) comment '员工姓名',
age int comment '员工年龄',
dept_id varchar(20) comment '部门号'
);

-- 3. 添加数据
insert into dept3 values('1001', '研发部');
insert into dept3 values('1002', '市场部');
insert into dept3 values('1003', '财务部');
insert into dept3 values('1004', '人事部');
insert into dept3 values('1005', '运营部');
insert into dept3 values('1006', '销售部');
------------------------------------------------
insert into emp3 values('1', '徐飞', 17, '1001');
insert into emp3 values('2', '刘琦', 27, '1001');
insert into emp3 values('3', '张军', 22, '1001');
insert into emp3 values('4', '刘莲', 23, '1001');
insert into emp3 values('5', '柯祁', 34, '1001');
insert into emp3 values('6', '郭川', 25, '1001');
insert into emp3 values('7', '陆奇', 20, '1001');
insert into emp3 values('8', '杨静', 24, '1001');
insert into emp3 values('9', '陆奇', 20, '1001');
------------------------------------------------
insert into emp3 values('10', '杨静', 24, '1002');
insert into emp3 values('11', '赵军', 22, '1002');
insert into emp3 values('12', '钱慧', 24, '1002');
insert into emp3 values('13', '孙烨', 32, '1002');
insert into emp3 values('14', '刘华', 25, '1002');
insert into emp3 values('15', '胡娟', 34, '1002');
insert into emp3 values('16', '谢琴', 22, '1002');
insert into emp3 values('17', '李嘉', 36, '1002');
insert into emp3 values('18', '牛丽', 42, '1002');
------------------------------------------------
insert into emp3 values('19', '张三', 24, '1003');
insert into emp3 values('20', '刘恩', 19, '1003');
insert into emp3 values('21', '贾设', 24, '1003');
insert into emp3 values('22', '秦璐', 32, '1003');
insert into emp3 values('23', '赵梦', 25, '1003');
insert into emp3 values('24', '周飞', 29, '1003');
------------------------------------------------
insert into emp3 values('25', '刘玄德', 36, '1004');
insert into emp3 values('26', '张飞燕', 21, '1004');
insert into emp3 values('27', '孙悟空', 88, '1004');
insert into emp3 values('28', '蜘蛛侠', 32, '1004');
insert into emp3 values('29', '小霸王', 15, '1004');
insert into emp3 values('30', '老干妈', 26, '1004');
insert into emp3 values('31', '伽利略', 44, '1004');
------------------------------------------------
insert into emp3 values('32', '朱丽门', 26, '1005');
insert into emp3 values('33', '刘卡其', 23, '1005');
insert into emp3 values('34', '朱武龙', 51, '1005');
insert into emp3 values('35', '孙华飞', 36, '1005');
insert into emp3 values('36', '李梦雅', 46, '1005');
insert into emp3 values('37', '郭启凯', 21, '1005');
insert into emp3 values('38', '费炳忠', 40, '1005');
------------------------------------------------
insert into emp3 values('39', '王阳明', 32, '1006');
insert into emp3 values('40', '邓丽娜', 46, '1006');
insert into emp3 values('41', '莫小贝', 20, '1006');
insert into emp3 values('42', '江腾旗', 19, '1006');
insert into emp3 values('43', '朱新筑', 21, '1006');
insert into emp3 values('44', '苏武力', 35, '1006');
insert into emp3 values('45', '何夏来', 44, '1006');

-- 4. 连接查询语句
select * from dept3, emp3 where dept_id = deptno;
select * from dept3 { inner } join emp3 on dept_id = deptno;
select * from dept3 { inner } join emp3 on dept_id = deptno and name='财务部';
select name, count(*) as '人数' from dept3, emp3 where dept_id = deptno group by name;
select name, count(*) as cn1 from dept3, emp3 where dept_id = deptno group by name having cn1 > 6;
select * from dept3 left {outer} join emp3 on dept_id = deptno;
select * from t1 
	left join t2 on '条件1'
	left join t3 on '条件2';
select * from t1 left  join t2 on '条件1'
	union all(不去重)
select * from t1 right join t2 on '条件2';

-- 5. 子查询 vs 连接查询
select * from emp3 where age = (select max(age) from emp3); -- 单行单列用等号(=)
select * from emp3 join dept3 where deptno = dept_id and (name = '研发部' or name = '销售部');
select * from emp3 where dept_id in (select deptno from dept3 where name ='研发部' or name = '销售部');
select emp3.*, name from emp3 join dept3 on dept_id = deptno and (name = '财务部' and age > 22);
select t1.*, t2.name from
	(select * from emp3 where age > 22) t1
join
	(select * from dept3 where name = '财务部') t2
on 
	t1.dept_id = t2.deptno;

-- 6. 子查询关键字
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
select * from emp3 where dept_id !=  all(select deptno from dept3);
select * from emp3 where dept_id not in (select deptno from dept3);
select * from emp3 where age > some(select age from emp3 where dept_id = '1003') and dept_id != '1003';
select * from emp3 where age >  any(select age from emp3 where dept_id = '1003') and dept_id != '1003';
select * from emp3 where dept_id in(select deptno from dept3 where name='人事部' or name='运营部' );
select * from emp3 e1 where  exists (select eid from emp3 e2 where e1.age >= 40); -- 是否有40岁以上的职工
select * from emp3 e1 where exists (select * from dept3 d1 where e1.dept_id = d1.deptno); -- 有部门的职工

all
any
some
in
exists

-- 7. 自关联查询
create table if not exists laoban (
id int primary key comment '工号',
name varchar(20) comment '姓名',
manager_id int comment '经理工号', 
foreign key (manager_id) references laoban (id) -- 自关联
);
insert into laoban values(1, '贾母', NULL);
insert into laoban values(2, '贾政', 1);
insert into laoban values(3, '宝玉', 2);
insert into laoban values(4, '袭人', 3);
insert into laoban values(5, '凤姐', 1);
insert into laoban values(6, '可卿', 1);
insert into laoban values(7, '贾琏', 5);
insert into laoban values(8, '王夫', 1);
insert into laoban values(9, '宝琴', 3);
select t1.name, t2.name  from laoban t1 join laoban t2 on t1.manager_id = t2.id;
select t1.name, t2.name, t3.name from laoban t1 left join laoban t2 on t1.manager_id = t2.id left join laoban t3 on t2.manager_id = t3.id;