Mysql多表操作方法讲解教程

目录
  • 外键约束
    • 概念
    • 特点
    • 操作
  • 多表联合查询
    • 概念
    • 操作
  • 多表操作总结

外键约束

概念

特点

定义一个外键时,需要遵守下列规则:

主表必须已经存在于数据库中,或者是当前正在创建的表。

必须为主表定义主键。

主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这 个外键的内容就是正确的。

在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

外键中列的数目必须和主表的主键中列的数目相同。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

操作

建立外键约束

create database  mydb3;
use mydb3;
create table if not exists dep
(
pid int primary key,
name varchar(20)
);
create table if not exists per
(
id int primary key,
name varchar(20),
age int,
depid int,
constraint fok foreign key(depid) references dep(pid)
);
create table if not exists dep3
(
pid int primary key,
name varchar(20)
);
create table if not exists per3
(
id int primary key,
name varchar(20),
age int,
depid int
);
alter table per3 add constraint fok3 foreign key(depid) references dep3(pid);

数据插入

必须先给主表添加数据,且从表外键列的值必须依赖于主表的主键列

insert into dep3 values('1001','研发部');
insert into dep3 values('1002','销售部');
insert into dep3 values('1003','财务部');
insert into dep3 values('1004','人事部');
-- 给per3表添加数据
insert into per3 values('1','乔峰',20, '1001');
insert into per3 values('2','段誉',21, '1001');
insert into per3 values('3','虚竹',23, '1001');
insert into per3 values('4','阿紫',18, '1001');
insert into per3 values('5','扫地僧',85, '1002');
insert into per3 values('6','李秋水',33, '1002');
insert into per3 values('7','鸠摩智',50, '1002');
insert into per3 values('8','天山童姥',60, '1003');
insert into per3 values('9','慕容博',58, '1003');

数据删除

主表数据被从表依赖时不能删除,否则可以删除;从表的数据可以随便删除。

如下,第一句和第二句执行成功,第三句执行失败

delete from per3 where depid=1003;
delete from dep3 where pid=1004;
delete from dep3 where pid=1002;

删除外键约束

语法:alter table 从表drop foreign key 关键词名;

alter table per3 drop foreign key fok3;

多表联合查询

概念

操作

交叉连接查询

select * from dept,emp;

内连接查询

注释;上面是隐式内连接,下面是显式内连接

select * from dept,emp where dept.deptno=emp.dept_id;
select * from dept join emp on dept.deptno=emp.dept_id;
select * from dept join emp on dept.deptno=emp.dept_id and name='研发部';
select * from dept join emp on dept.deptno=emp.dept_id and name='研发部';
select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name='销售部');
select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name ='销售部');
select * from dept join emp on dept.deptno=emp.dept_id and name in ('研发部','销售部');
select a.name,a.deptno,count(*) from dept a join emp on a.deptno=emp.dept_id group by dept_id;
select a.name,a.deptno,count(*) total from dept a join emp on a.deptno=emp.dept_id group by dept_id having total >=3 order by total desc;

外连接查询

若是对应的外表没有数据就补NULL

select * from dept a left join emp b on a.deptno=b.dept_id;
select * from dept a right join emp b on a.deptno=b.dept_id;
-- select * from dept a full join emp b on a.deptno=b.dept_id; --不能执行
-- 用下面的方法代替上面的full join
select * from dept a left join emp b on a.deptno=b.dept_id union select * from dept a right join emp b on a.deptno=b.dept_id;
-- 对比union all,发现union all没有去重过滤
select * from dept a left join emp b on a.deptno=b.dept_id union all select * from dept a right join emp b on a.deptno=b.dept_id;

子查询

select * from emp where age<(select avg(age) from emp);
select * from emp a where a.dept_id in (select deptno from dept where name in ('研发部','销售部'));
-- 对比关联查询和子查询如下
select * from emp a join dept b on a.dept_id=b.deptno and (b.name='研发部' and age<30);
select * from (select * from dept where name='研发部') a join (select * from emp where age<30) b on b.dept_id=a.deptno;

子查询关键字

all关键字的用法

select * from emp where age>all(select age from emp where dept_id='1003');
select * from emp a where a.dept_id!=all(select deptno from dept);

any(some)关键字的用法

select * from emp where age>any(select age from emp where dept_id='1003') and dept_id!='1003';

in关键字的用法

select ename,eid from emp where dept_id in (select deptno from dept where name in ('研发部','销售部'));

exists关键字的用法

select * from emp a where a.age<30;
select * from emp a where exists(select * from emp where a.age<30);
select * from emp a where a.dept_id in (select deptno from dept b);
select * from emp a where exists (select * from dept b where a.dept_id = b.deptno);

自关联查询

多表操作总结

到此这篇关于Mysql多表操作方法讲解教程的文章就介绍到这了,更多相关Mysql多表操作内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL数据库多表操作通关指南(外键约束和多表联合查询)

    目录 1 多表关系 2 外键约束(FOREIGN KEY) 2.1 外键约束说明 2.2 外键约束的创建 2.3 外键约束实操:一对多关系 2.4 删除外键约束 2.5 外键约束实操:多对多关系 3 多表联合查询 3.1 联合查询的简介和分类 3.2 联合查询数据准备 3.3 交叉联合查询 3.4 内连接查询 3.5 外连接查询 3.6 子查询 3.6.1 子查询说明与实操 3.6.2 子查询中的关键字 3.7 自关联查询 写在最后 1 多表关系 一对一关系 比如:一个人有一个身份证,一个身份证

  • MySQL数据库设计概念及多表查询和事物操作

    目录 数据库设计概念 数据库设计简介 表关系(多对多) 表关系(一对多) 表关系之一对一 多表查询 笛卡尔积现象 内连接查询 嵌套查询(子查询) 事务操作 事务的概念 手动提交事务 自动提交事务 事务原理和四大特征 事务原理 事务的四大特征 事务的并发访问引发的三个问题(面试) 事务的隔离级别 数据库设计概念 数据库设计简介 1.数据库设计概念 数据库设计就是根据业务系统具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型. 建立数据库中的表结构以及表与表之间的关联关系的过

  • Mysql多表操作方法讲解教程

    目录 外键约束 概念 特点 操作 多表联合查询 概念 操作 多表操作总结 外键约束 概念 特点 定义一个外键时,需要遵守下列规则: 主表必须已经存在于数据库中,或者是当前正在创建的表. 必须为主表定义主键. 主键不能包含空值,但允许在外键中出现空值.也就是说,只要外键的每个非空值出现在指定的主键中,这 个外键的内容就是正确的. 在主表的表名后面指定列名或列名的组合.这个列或列的组合必须是主表的主键或候选键. 外键中列的数目必须和主表的主键中列的数目相同. 外键中列的数据类型必须和主表主键中对应列

  • MySQL数据库事务transaction示例讲解教程

    目录 1.什么是事务? 2.和事务相关的语句只有这3个DML语句:insert.delete.update 3.假设所有的业务都能使用1条DML语句搞定,还需要事务机制吗? 4.事务的原理 5.事务的四大特性:ACID 6.关于事务之间的隔离性 1)第一级别:读未提交(read uncommitted) 2)第二级别:读已提交(read committed) 3)第三级别:可重复读(repeatable read) 4)第四级别:序列化读/串行化读(serializable) 7.演示事务的隔离

  • 88秒插入1000万条数据到MySQL数据库表的操作方法

    我用到的数据库为,mysql数据库5.7版本的 首先自己准备好数据库表 其实我在插入1000万条数据的时候遇到了一些问题,现在先来解决他们,一开始我插入100万条数据时候报错,控制台的信息如下: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4232009 > 4194304). You can change this value on the server by setting the max_allo

  • mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

  • 在MySQL中删除表的操作教程

    丢弃现有MySQL的表是很容易的.但是需要非常小心,删除任何现有的一个表后将无法恢复,因为数据丢失. 语法: 下面是通用的SQL语法丢弃(删除)MySQL表: DROP TABLE table_name ; 从命令提示符删除表: 只需要在mysql>提示符下执行DROP TABLE SQL命令. 例子: 下面是一个例子,它删除表 tutorials_tbl: root@host# mysql -u root -p Enter password:******* mysql> use TUTORI

  • MySQL中表的复制以及大型数据表的备份教程

    表复制 mysql拷贝表操作我们会常常用到,下面就为您详细介绍几种mysql拷贝表的方式,希望对您学习mysql拷贝表方面能够有所帮助. 假如我们有以下这样一个表: id username password ----------------------------------- 1 admin ************* 2 sameer ************* 3 stewart ************* CREATE TABLE IF NOT EXISTS `admin` ( `id`

  • MySQL分库分表总结讲解

    项目开发中,我们的数据库数据越来越大,随之而来的是单个表中数据太多.以至于查询变慢,而且由于表的锁机制导致应用操作也受到严重影响,出现了数据库性能瓶颈. 当出现这种情况时,我们可以考虑分库分表,即将单个数据库或表进行拆分,拆分成多个库和多个数据表,然后用户访问的时候,根据一定的算法与逻辑,让用户访问不同的库.不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力.提升了数据库访问性能. 下面是对项目中分库分表的一些总结: 单库单表 单库单表是最常见的数据库设计,例如,有一张用户(use

  • Mysql纵表转换为横表的方法及优化教程

    1.纵表与横表 纵表:表中字段与字段的值采用key-value形式,即表中定义两个字段,其中一个字段里存放的是字段名称,另一个字段中存放的是这个字段名称代表的字段的值. 例如,下面这张ats_item_record表,其中field_code表示字段,后面的record_value表示这个字段的值 优缺点: 横表:表结构更加的清晰明了,关联查询的一些sql语句也更容易,方便易于后续开发人员的接手,但是如果字段不够,需要新增字段,会改动表结构. 纵表:扩展性更高,如果要增加一个字段,不需要改变表结

  • MySQL多表操作的外键约束教程

    目录 一.多表关系 一对一关系 一对多/多对一关系 多对多关系 二.外键约束 特点 创建外键约束 方式1-在创建表时设置外键约束 方式2-在创建表时设置外键约束 在外键约束下的操作 1.数据插入 2.删除数据 3.删除外键约束 外键约束-多对多关系 操作 一.多表关系 MySQL多表之间的关系可以概括为:一对一.一对多/多对一关系,多对多 一对一关系 一个学生只有一张身份证;一张身份证只能对应一学生.在任一表中添加唯─外键,指向另一方主键,确保一对一关系.一般一对一关系很少见,遇到一对一关系的表

  • MySQL 数据库 ALTER命令讲解

    MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成. 当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令. 开始本章教程前让我们先创建一张表,表名为:testalter_tbl. root@host# mysql -u root -p password; Enter password:*****

随机推荐