精华内容
下载资源
问答
  • 被其他外键约束的表如何修改数据
    2021-01-27 19:57:13

    MySQL 数据表的约束

    约束保证数据的完整性和一致性,约束分为表级约束和列级约束。

    约束类型包括:

    NOT NULL            (非空约束)

    PRIMARY KEY       (主键约束)

    UNIQUE KEY         (唯一约束)

    DEFAULT              (默认约束)

    FOREIGN KEY       (外键约束)

    外键约束保证数据一致性,完整性,实现一对多或者一对一关系 (含有外键的表称为子表)

    外键约束的要求

    1)父表和子表必须使用相同的存储引擎,而且禁止使用临时表

    2)数据表的存储引擎只能为InnoDB

    3)外键列和参照列必须具有相似的数据类型,其中数字的长度或是否有符号位必须相同,而字符的长度则可以不同

    4)外键列和参照列必须创建索引。如果外键列不存在索引的话,MYSQL将自动创建索引

    查看外键约束

    show create table user_order;

    MariaDB [mimvp_tech]> show create table user_order;

    `id` bigint(18) NOT NULL DEFAULT 0 COMMENT '订单号ID',

    `user_email` varchar(50) NOT NULL COMMENT '用户的邮箱,作为外健与user_info表主键user_email关联',

    `seller_email` varchar(45) DEFAULT '' COMMENT '卖家的邮箱',

    `seller_id` bigint(16) DEFAULT 0 COMMENT '卖家的id',

    `seller_actions` varchar(45) DEFAULT '' COMMENT '卖家的行为,例如: SEND_GOODS',

    PRIMARY KEY (`id`),

    KEY `user_email_idx` (`user_email`),

    CONSTRAINT `user_email` FOREIGN KEY (`user_email`) REFERENCES `user_info` (`user_email`) ON DELETE NO ACTION ON UPDATE NO ACTION

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    如上,可以看到数据表 user_order 的列名 user_email 与数据表 user_info 的列名 user_email 构成了外键约束。

    另外,常用的查询表的列名:

    show indexes from user_order;

    show indexes from user_order \G;

    desc user_order;

    show columns from user_order;

    show columns from user_order \G;

    修改已经被外键(FOREIGN KEY)约束的表

    有时候,建表时考虑不仔细,导致突然需要为一张表进行一些更改,如:增加一列属性,将主键设为自增属性(这列已经被其他表引为外键)等。

    数据库处理这种东西时,不让你进行修改,因为这个操作违反了外键约束,破坏了数据库完整性。所以这个修改的行为会被DBMS(数据库管理系统)给阻止。

    那么必须修改怎么办?

    可以先取消外键,修改完后再把外键约束加回来。

    MySQL 提供了一个方法,临时关闭外键约束,当修改完成之后再将外键约束加回来。

    SET FOREIGN_KEY_CHECKS = 0;

    /* MODIFY YOUR FOREIGN Values */

    SET FOREIGN_KEY_CHECKS = 1;

    把你需要的修改操作,放在两个语句之间。

    参考推荐:

    更多相关内容
  • MySQL 数据表的约束约束保证数据的完整性和一致性,约束分为表级约束和列级约束。约束类型包括:NOT NULL (非空约束)PRIMARY KEY (主键约束)UNIQUE KEY (唯一约束)DEFAULT (默认约束)FOREIGN KEY (外键约束)外键约束...

    MySQL 数据表的约束

    约束保证数据的完整性和一致性,约束分为表级约束和列级约束。

    约束类型包括:

    NOT NULL            (非空约束)

    PRIMARY KEY       (主键约束)

    UNIQUE KEY         (唯一约束)

    DEFAULT              (默认约束)

    FOREIGN KEY       (外键约束)

    外键约束保证数据一致性,完整性,实现一对多或者一对一关系 (含有外键的表称为子表)

    外键约束的要求

    1)父表和子表必须使用相同的存储引擎,而且禁止使用临时表

    2)数据表的存储引擎只能为InnoDB

    3)外键列和参照列必须具有相似的数据类型,其中数字的长度或是否有符号位必须相同,而字符的长度则可以不同

    4)外键列和参照列必须创建索引。如果外键列不存在索引的话,MYSQL将自动创建索引

    查看外键约束

    show create table user_order;

    MariaDB [mimvp_tech]> show create table user_order;

    `id` bigint(18) NOT NULL DEFAULT 0 COMMENT '订单号ID',

    `user_email` varchar(50) NOT NULL COMMENT '用户的邮箱,作为外健与user_info表主键user_email关联',

    `seller_email` varchar(45) DEFAULT '' COMMENT '卖家的邮箱',

    `seller_id` bigint(16) DEFAULT 0 COMMENT '卖家的id',

    `seller_actions` varchar(45) DEFAULT '' COMMENT '卖家的行为,例如: SEND_GOODS',

    PRIMARY KEY (`id`),

    KEY `user_email_idx` (`user_email`),

    CONSTRAINT `user_email` FOREIGN KEY (`user_email`) REFERENCES `user_info` (`user_email`) ON DELETE NO ACTION ON UPDATE NO ACTION

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    如上,可以看到数据表 user_order 的列名 user_email 与数据表 user_info 的列名 user_email 构成了外键约束。

    另外,常用的查询表的列名:

    show indexes from user_order;

    show indexes from user_order \G;

    desc user_order;

    show columns from user_order;

    show columns from user_order \G;

    修改已经被外键(FOREIGN KEY)约束的表

    有时候,建表时考虑不仔细,导致突然需要为一张表进行一些更改,如:增加一列属性,将主键设为自增属性(这列已经被其他表引为外键)等。

    数据库处理这种东西时,不让你进行修改,因为这个操作违反了外键约束,破坏了数据库完整性。所以这个修改的行为会被DBMS(数据库管理系统)给阻止。

    那么必须修改怎么办?

    可以先取消外键,修改完后再把外键约束加回来。

    MySQL 提供了一个方法,临时关闭外键约束,当修改完成之后再将外键约束加回来。

    SET FOREIGN_KEY_CHECKS = 0;

    /* MODIFY YOUR FOREIGN Values */

    SET FOREIGN_KEY_CHECKS = 1;

    把你需要的修改操作,放在两个语句之间。

    参考推荐:

    展开全文
  • 目录标题对数据库的操作数据表的操作 DDL 数据定义语言 创建:create 修改:alert 删除:drop 对数据库的操作 数据库的创建:create database 数据库名; 数据库如果不存在就创建:create database if not exists ...

    DDL 数据定义语言
    创建:create
    修改:alert
    删除:drop

    对数据库的操作

    • 数据库的创建:create database 数据库名;
    • 数据库如果不存在就创建:create database if not exists 数据库名;
    • 更改数据库的字符集:alert database 数据库名 character set gdk/utf8;
    • 数据库的删除:drop database if exists 数据库名;

    数据表的操作

    数据表的创建:

    create table 表名(
    	字段名 类型 约束,
    	字段名 类型 约束,
    	字段名 类型 约束,
    	...
    	字段名 类型 约束
    )
    

    表的修改

    -- 修改字段名 
    alert table 数据表名 change column 原始字段名 新字段名 类型;
    
    -- 修改列的类型或约束
    alert table 数据表名 modify column 字段名 新类型 [新约束];
    
    -- 添加一个字段
    alert table 数据表名 add column 字段名 类型 [约束];
    -- 一个字段默认的添加在表的最后的,如果我想让添加的列再表的某个位置需要加上一些东西
    alert table 数据表名 add column 字段名 类型 [first | after 字段名]   -- 这里是表示添加在最前面 或者是 添加在某个字段后
    
    -- 删除一个字段
    alert table 数据表名 drop column 字段名;
    
    -- 修改表名
    alert table 数据表名 rename to 新数据表名
    

    表的删除

    drop table 表名;
    

    表的复制

    -- 仅仅复制表的结构
    create table 新表名 like 已存在的表名;
    
    -- 复制表的结构+数据
    create table 新表名
    select * from 已存在的表名;
    
    -- 还可以只复制部分列与部分行,就直接在select后面写要复制的字段名,想要复制部分行就在查询语句后面加where条件
    

    数据类型

    整形:

    • tinyint 占用一个字节
    • smallint 占用两个字节
    • mediumint 占用三个字节
    • ing 占用四个字节
    • bigint 占用八个字节

    注意点:

    1. 整形的取值范围是有上面的类型决定的,并且有无符号和有符号的区别,就比如上面的tinyint类型,无符号的取值范围是256~256,而有符号的取值范围是-127~127
    2. 如果在创建数据表是,设置字段类型时,如果不指定有符号还是无符号,默认是有符号的,如果想无符号则需要加上unsigned关键字
    3. 如果新增的值,超过了该类型的最大取值,则会将新增的值变为该类型的临界值
    4. 对于整形的数据,如果不指定长度会有默认的长度
    5. 类型决定了取值范围,而长度决定是否需要在前面补0,默认情况下是不会补0的,如果想要需要加上zerofill关键字

    小数

    • 浮点型:
      • float(M,D) 占4字节
      • double(M,D) 占8字节
    • 定点型:
      • dec(M,D) 或者 decimal(M,D) 占M+2字节, 取值范围和double相同,但是保存的精度比double要精确一点

    注意点:

    1. M代表小数点前和小数点后的总位数,D代表小数点后保留几位,如果超过了指定的位数则进行四舍五入
    2. M和D都可以省略,如果是float和double类型默认就没有M和D了,但dec默认是dec(10,0)

    字符型

    • 较短的文本
      • char(M) , M个字符数,可以省略,默认为1
      • varchar(M) ,M个字符数,不可以省略
      • binary
      • varbinary
      • enum
      • set
    • 较长的文本
      • text
      • blob 该类型是保存文件的二进制

    charvarchar的区别是: char代表的是固定长度的字符, varchar代表的是可变长度的字符 , 也就是说假如定义一个cahr(5) ,这时候插入一个字符a ,但其实它还是占5个字符的空间,但如果是varchar(5) 那么它就只占一个字符的空间。 但是使用char性能要比varchar的性能高一些。

    binaryvarbinary是用来保存较短的二进制

    对于枚举类型,比如创建一个字段,该字段的值就只能是枚举中规定的值

    create table tab_char(
    	age ENUM('男','女')
    );
    

    set类型 和枚举很像,不同点就是枚举只能是我们规定的值中的一个,而set可以有多个

    create table tab_set(
    	s1 set('a','b','c','d')
    );
    
    -- 插入数据 如果是字母则不区分大小写,枚举也不区分大小写
    insert into tab_set values('a');
    insert into tab_set values('A');
    insert into tab_set values('a,b,c');
    

    日期类型

    在这里插入图片描述

    这里主要是讲解datatime 和 timestamp 的区别,timestamp主要的区别就是会受时区的影响来改变数据库中存储的值。

    案例如下

    -- 创建一个表
    create table tab_date(
    	t1 datatime,
    	t2 timestamp
    )
    
    -- 新增数据,两个字段都插入当前时间
    insert into tab_date values(now(),now());
    
    -- 这个时候查询数据库得到的结果都是一样的 2021-09-21 17:54:12
    select * from tab_date;
    
    -- 查看当前数据库使用的时区
    show variables like 'time_zone';
    -- 再改变时区
    set time_zone='+9:00';
    
    -- 这个时候再执行一遍查询语句,就会发现第二个字段的值自动变化了,
    -- 还有就是timestamp类型的范围是1970年--2038年
    

    约束

    基本知识

    常见约束如下:

    • primary key 主键约束
    • unique 唯一约束
    • not null 非空约束
    • default 默认约束
    • check 检查约束 mysql不支持 比如年龄和性别都可以使用该约束
    • auto_increment 主键自增

    约束的分类:

    • 列级约束:上面的约束都支持 但外键约束没有效果
    • 表级约束:除非空约束,默认约束。其他都支持

    列级约束语法:

    create table 表名(
    	字段名 类型 约束,
        字段名 类型 约束,
        ...
        字段名 类型 约束
    )
    

    表级约束语法:

    create table 表名(
    	字段名 类型,
        字段名 类型,
        ...
        字段名 类型,
        
        [constraint 自己随便起一个约束名] 约束类型(字段名)
    )
    

    约束名也可以省略不写,也会有默认的名字。

    查看约束的语法:show index from 表名

    主键约束和唯一约束的区别是:

    • 主键约束不能存在null,但唯一约束允许存在一个null
    • 主键约束表中最多只能有一个,唯一约束有多个。

    组合

    就拿主键来举例,可以把两个字段组合为一个主键,也就是当两行数据中,这两列都要相同才会冲突。但是不推荐使用组合

    create table 表名(
    	id int,
        name varchar(10),
        ...
        
        constraint XXX约束名 PRIMARY EKY(id,name)
    )
    

    修改表是添加约束

    1. 添加列级外键约束
    alert table 表名 modify column 字段名 类型 新约束;
    
    1. 添加表级约束
    alert table 表名 [constraint 自己随便起一个约束名] 新约束(字段);
    

    外键约束

    外键约束其实就是让表与表之间产生关系,进而保证数据的正确性。也可以说成保证数据的参照完整性。

    -- 创建外键约束
    create table 表名(
    	字段名 类型,
        字段名 类型,
        ...
        字段名 类型,
        
        constraint 外键约束名称 foreign key(外键列名称) references 主表名称(主表列名称)
        -- 外键约束名称一般的命名规则: fk_当前表名_主表名_外键列名称
    );
    
    -- 删除外键约束
    ALTER TABLE 表名 DROP FOREIGN KEY 我们随便起的外键名称
    
    -- 创建表之后添加外键
    ALTER TABLE 表名 ADD constraint 随便起一个外键名称 foreign key (外键列名称) references主表名称 (主表列名称)
    

    注意事项:

    • 要求在从表上设置外键约束
    • 从表外键约束字段类型要和主表的那个字段类型相同
    • 要去主表上的关联列必须是一个key,一般是主键或唯一
    • 插入数据时,应该删插入主表再插入从表,删除数据时,应该先删除从表在删除主表

    级联操作

    就是有两个表,添加了外键约束,当其中一个表的外键那个字段修改了,另一个表也就跟着自动修改

    -- 添加级联的语句是在添加外键语句的后面添加 ON UPDATE CASCADE  设置级联更新
    -- 具体代码如下:
    ALTER TABLE 表名 ADD constraint 
    外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON UPDATE CASCADE;
    
    -- 添加外键 设置级联更新 然后再设置级联删除
    ALTER TABLE 表名 ADD constraint 
    外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON UPDATE CASCADE ON DELECT CASCADE;
    
    -- 级联置空 删除主表时不要删除对应从表的数据 而是将外键列置空
    ALTER TABLE 表名 ADD constraint 
    外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON DELECT SET NULL;
    
    • 级联更新:就是修改这个表外键的数值,另一个表也跟着修改
    • 级联删除:就是删除这个表外键的一行,另一个表所有用到了这个外键行的数据也跟着修改
    展开全文
  • 外键约束下的数据操作 删除外键约束 多对多 操作案例 多查询 交叉连接 内连接查询 外连接查询 子查询 子查询关键字-ALL ✈️子查询关键字-ANY和SOME 子查询关键字-IN ⛔️子查询关键字-EXISTS3 自...

    目录

    🎈概念

    🔮外键约束

    💿外键约束的特点

    💾添加数据

    📷在外键约束下的数据操作

    🏈删除外键约束

    🔫多对多

    🍱 操作案例

    💭多表查询

    🎴交叉连接

    📆内连接查询

    💒外连接查询

    🔯子查询

    🎢子查询关键字-ALL

    ✈️子查询关键字-ANY和SOME

    🍚子查询关键字-IN

    ⛔️子查询关键字-EXISTS3

    🏪自关联查询

    🍴总结

    🚰每文一语


    🎈概念

    实际开发中,一个项目通常需要很多张表才能完成

    例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们将在单表的基础上,一起学习多表方面的知识。

    在实际的工业生产实践中,多表操作是非常常见的,其次,一般我们需要对数据表进行维度约束

    第一范式;第二范式;第三范式;第四范式,https://wxw-123.blog.csdn.net/article/details/119669283

    这里详细的介绍了,数据库的范式和基本概念,这样的数据可以保证数据的不冗余,便于我们数据的操作。

    MySQL多表之间的关系可以概括为:一对一、一对多/多对一关系,多对多

    一个学生只有一张身份证;一张身份证只能对应一学生。
    在任一表中添加唯一外键,指向另一方主键,确保一对一关系。
    一般一对一关系很少见,遇到一对一关系的表最好是合并表。

    部门和员工

    分析:一个部门有多个员工,一个员工只能对应一个部门;实现原则:在多的一方建立外键,指向一的一方的主键

    多对多

    学生和课程

    分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

    原则:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,将多对多的关系,拆成一对多的关系,中间表至少要有两个外键,这两个外键分别指向原来的那两张表的主键

    🔮外键约束

    MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。

    外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。

    比如,一个水果摊,只有苹果、桃子、李子、西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜,其它的水果都是不能购买的。

    这句话就是说,从表里面的字段一定要在主表中存在

    💿外键约束的特点

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

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

    必须为主表定义主键。

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

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

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

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

    create table 语句中,通过 foreign key 关键字来指定外键,具体的语法格式如下:

    [constraint <外键名>] foreign key 字段名 [,字段名2,…] references <主表名> 主键列1 [,主键列2,…]
    
    #主表
    -- 创建部门表
    create table if not exists dept(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
    
    #从表
    create table if not exists emp(
      eid varchar(20) primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20),  -- 员工所属部门
      constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
    );
    

    外键约束也可以在修改表时添加,但是添加外键约束的前提是:从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

    alter table <数据表名> add constraint <外键名> foreign key(<列名>) references 
    <主表名> (<列名>);
    
    -- 创建部门表
    create table if not exists dept2(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
    -- 创建员工表
    create table if not exists emp2(
      eid varchar(20) primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20)  -- 员工所属部门
     
    );
    -- 创建外键约束
    alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);
    

    💾添加数据

     -- 1、添加主表数据
     -- 注意必须先给主表添加数据
    insert into dept values('1001','研发部');
    insert into dept values('1002','销售部');
    insert into dept values('1003','财务部');
    insert into dept values('1004','人事部');
    
    -- 2、添加从表数据
    -- 注意给从表添加数据时,外键列的值不能随便写,必须依赖主表的主键列
    insert into emp values('1','乔峰',20, '1001');
    insert into emp values('2','段誉',21, '1001');
    insert into emp values('3','虚竹',23, '1001');
    insert into emp values('4','阿紫',18, '1002');
    insert into emp values('5','扫地僧',35, '1002');
    insert into emp values('6','李秋水',33, '1003');
    insert into emp values('7','鸠摩智',50, '1003'); 
    insert into emp values('8','天山童姥',60, '1005');  -- 不可以

    📷在外键约束下的数据操作

    -- 3、删除数据
     /*
       注意:
           1:主表的数据被从表依赖时,不能删除,否则可以删除
           2: 从表的数据可以随便删除
     */
    delete from dept where deptno = '1001'; -- 不可以删除
    delete from dept where deptno = '1004'; -- 可以删除
    delete from emp where eid = '7'; -- 可以删除
    

    🏈删除外键约束

    当一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系

    alter table <从表名> drop foreign key <外键约束名>;
    
    alter table emp2 drop foreign key dept_id_fk;
    

    删除外键约束的时候,只需要删除从表的约束名

    🔫多对多

    🍱 操作案例

    -- 学生表和课程表(多对多)
      -- 1 创建学生表student(左侧主表)
       create table if not exists student(
        sid int primary key auto_increment,
        name varchar(20),
        age int,
        gender varchar(20)
       );
      -- 2 创建课程表course(右侧主表)
      create table course(
       cid  int primary key auto_increment,
       cidname varchar(20)
      );
    
    -- 3创建中间表student_course/score(从表)
      create table score(
        sid int,
        cid int,
        score double
      );
        
    -- 4建立外键约束(2次)
     
    alter table score add foreign key(sid) references student(sid);
    alter table score add foreign key(cid) references course(cid);
     
    -- 5给学生表添加数据
    insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
    -- 6给课程表添加数据
    insert into course values(1,'语文'),(2,'数学'),(3,'英语');
    -- 7给中间表添加数据
    insert into score values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);
    

    注意添加数据的时候,一定要谨慎操作,从表中的字段一定要在主表中存在,不然就会出现数据无法添加。既然如此,那么我们还有必要进行外键约束了,答案是有必要的,有时候我们需要根据自己的业务场景进行适当的调整。

    💭多表查询

    多表查询就是同时查询两个或两个以上的表,因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.

    多表查询有以下分类:

    交叉连接查询 [产生笛卡尔积,了解]
          语法:select * from A,B;  

    内连接查询(使用的关键字 inner join  -- inner可以省略)
        隐式内连接(SQL92标准):select * from A,B where 条件;
        显示内连接(SQL99标准):select * from A inner join B on 条件;

    外连接查询(使用的关键字 outer join -- outer可以省略)
            左外连接:left outer join
                select * from A left outer join B on 条件;
            右外连接:right outer join
                select * from A right outer join B on 条件;
            满外连接: full outer join
                 select * from A full outer join B on 条件;
    子查询
           select的嵌套
    表自关联:
           将一张表当成多张表来用

    如果是第一次,记起来不是那么的清晰的话,你还需要记住:on后面加条件,A inner join B on 条件

    外键约束对多表查询并无影响

    下面通过准备一些数据,进行模拟测试

    -- 创建部门表
    create table if not exists dept3(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
    
    -- 创建员工表
    create table if not exists emp3(
      eid varchar(20) primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20)  -- 员工所属部门
    );
    
    -- 给dept3表添加数据
    insert into dept3 values('1001','研发部');
    insert into dept3 values('1002','销售部');
    insert into dept3 values('1003','财务部');
    insert into dept3 values('1004','人事部');
    
    -- 给emp3表添加数据
    insert into emp3 values('1','乔峰',20, '1001');
    insert into emp3 values('2','段誉',21, '1001');
    insert into emp3 values('3','虚竹',23, '1001');
    insert into emp3 values('4','阿紫',18, '1001');
    insert into emp3 values('5','扫地僧',85, '1002');
    insert into emp3 values('6','李秋水',33, '1002');
    insert into emp3 values('7','鸠摩智',50, '1002'); 
    insert into emp3 values('8','天山童姥',60, '1003');
    insert into emp3 values('9','慕容博',58, '1003');
    insert into emp3 values('10','丁春秋',71, '1005');
    -- 1.交叉连接查询
    /*
    1. 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
    2. 笛卡尔集可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
    3. 假如A表有m行数据,B表有n行数据,则返回m*n行数据
    4. 笛卡尔积会产生很多冗余的数据,后期的其他查询可以在该集合的基础上进行条件筛选
    
    格式:select * from 表1,表2,表3….; 
    */
    
    -- 内连接查询
    /*
      隐式内连接(SQL92标准):select * from A,B where 条件;
      显示内连接(SQL99标准):select * from A inner join B on 条件;
    */

    🎴交叉连接

    -- 查询每个部门的所属员工
    -- 隐式内连接
    select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
    select * from dept3 a,emp3 b where a.deptno = b.dept_id;
    
    
    -- 显式内连接
    select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
    select * from dept3 a join emp3 b on a.deptno = b.dept_id;

    在最开始接触MySQL的小伙伴,可以通过where隐式连接,也可以通过显式连接,当然可以连接多个表

    📆内连接查询

    -- 查询研发部和销售部的所属员工
    select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
    select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
     
    -- 查询每个部门的员工数,并升序排序
    select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
     
    select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
    
    -- 查询人数大于等于3的部门,并按照人数降序排序
    select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
     
    select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
    

    💒外连接查询

    外连接分为左外连接(left outer join)、右外连接(right outer join),满外连接(full outer join)

    注意:oracle里面有full join,可是在mysqlfull join支持的不好。我们可以使用union来达到目的。

     左外连接:left outer join

                select * from A left outer join B on 条件;

      右外连接:right outer join

                select * from A right outer join B on 条件;

      满外连接: full outer join

                 select * from A full outer join B on 条件;

    -- 外连接查询
    -- 查询哪些部门有员工,哪些部门没有员工
    
    select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
     
    -- 查询哪些员工有对应的部门,哪些没有
    select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
     
     
    -- 使用union关键字实现左外连接和右外连接的并集
    select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
    union 
    select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
    

    🔯子查询

    子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询

    子查询可以返回的数据类型一共分为四种:

    1. 单行单列:返回的是一个具体列的内容,可以理解为一个单值数据;
    2. 单行多列:返回一行数据中多个列的内容;
    3. 多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围;
    4. 多行多列:查询返回的结果是一张临时表

    一般子查询可以多个嵌套:

    -- 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄
    select eid,ename,age from emp3 where age = (select max(age) from emp3);
     
     
    -- 查询年研发部和销售部的员工信息,包含员工号、员工名字
    select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
     
     
    -- 查询研发部20岁以下的员工信息,包括员工号、员工名字,部门名字
    select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2
    

    在子查询中,有一些常用的逻辑关键字,这些关键字可以给我们提供更丰富的查询功能,主要关键字如下:

    1.ALL关键字

    2.ANY关键字

    3.SOME关键字

    4.IN关键字

    5.EXISTS关键字

    🎢子查询关键字-ALL

    select …from …where c > all(查询语句)
    --等价于:
    select ...from ... where c > result1 and c > result2 and c > result3
    

    ALL: 与子查询返回的所有值比较为true 则返回true

    ALL可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

    ALL表示指定列中的值必须要大于子查询集的每一个值,即必须要大于子查询集的最大值;如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

    -- 查询年龄大于‘1003’部门所有年龄的员工信息
    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); 
    
    

    ✈️子查询关键字-ANY和SOME

    select …from …where c > any(查询语句)
    --等价于:
    select ...from ... where c > result1 or c > result2 or c > result3
    

    ANY:与子查询返回的任何值比较为true 则返回true

    ANY可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。

    表示制定列中的值要大于子查询中的任意一个值,即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。

    SOME和ANY的作用一样,SOME可以理解为ANY的别名

    -- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
    select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);
    

    🍚子查询关键字-IN

    select …from …where c in(查询语句)
    --等价于:
    select ...from ... where c = result1 or c = result2 or c = result3
    

    IN关键字,用于判断某个记录的值,是否在指定的集合中

    在IN关键字前边加上not可以将条件反过来

    -- 查询研发部和销售部的员工信息,包含员工号、员工名字
    select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部')

    ⛔️子查询关键字-EXISTS3

    select …from …where exists(查询语句)
    

    该子查询如果“有数据结果”(至少返回一行数据), 则该EXISTS() 的结果为“true”,外层查询执行
    该子查询如果“没有数据结果”(没有任何数据返回),则该EXISTS()的结果为“false”,外层查询不执行

    EXISTS后面的子查询不返回任何实际数据,只返回真或假,当返回真时 where条件成立
    注意,EXISTS关键字,比IN关键字的运算效率高,因此,在实际开发中,特别是大数据量时,推荐使用EXISTS关键

    -- 查询公司是否有大于60岁的员工,有则输出
    select * from emp3 a where exists(select * from emp3 b where a.age > 60);
     
    -- 查询有所属部门的员工信息
    select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
    

    这里也是MySQL优化的一个突破口,有时候遇到大量的数据集,我们可以使用exists,对查询数据提前进行封装,如果有值则进行查询,没有的话就不查询

    🏪自关联查询

    MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当成多张表来用注意自关联时表必须给表起别名

    select 字段列表 from 表1 a , 表1 b where 条件;
    或者 
    select 字段列表 from 表1 a [left] join 表1 b on 条件;
    

    创建数据集

    -- 创建表,并建立自关联约束
    create table t_sanguo(
        eid int primary key ,
        ename varchar(20),
        manager_id int,
     foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
    );
    
    -- 添加数据 
    insert into t_sanguo values(1,'刘协',NULL);
    insert into t_sanguo values(2,'刘备',1);
    insert into t_sanguo values(3,'关羽',2);
    insert into t_sanguo values(4,'张飞',2);
    insert into t_sanguo values(5,'曹操',1);
    insert into t_sanguo values(6,'许褚',5);
    insert into t_sanguo values(7,'典韦',5);
    insert into t_sanguo values(8,'孙权',1);
    insert into t_sanguo values(9,'周瑜',8);
    insert into t_sanguo values(10,'鲁肃',8);
     
    -- 进行关联查询
    -- 1.查询每个三国人物及他的上级信息,如:  关羽  刘备 
    select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
    

    这种情况在业务是上,相对来说存在的情况比较少,因为需要避免数据的冗余

    🍴总结

    面对多个案例操作,大家一定要自己动手操作,上手才能理解,纸上谈兵终觉浅

    点击下方链接获取mysql多表操作思维导图,详细总结案例

    MySQL多表操作.xmind-MySQL文档类资源-CSDN下载

    文末资源推荐

    🍓 🍑 🍈 🍌 🍐 🍍 🍠 🍆 🍅 🌽

    点击下方即可下载 ⤵️⤵️⤵️⤵️⤵️

    CDA全套资源练习题及讲义资料最新版模拟试题_CDA笔记-讲义文档类资源-CSDN下载

    🚰每文一语

    .*?

    展开全文
  • MySQL为添加外键约束

    千次阅读 2021-01-18 22:13:53
    添加外键约束的语法...为student添加外键约束执行成功后,使用DESC来查看学生和班级可以看出,grade中的id是主键,student中的gid是外键使用show create table,查看的详细结构可以看出,gid为st...
  • 一、MYSQL中的约束1、主键:primary key 唯一非空的特性并且可以优化查询速度2、外键:foreign key 外键的作用保证2个或2个以上的数据表的数据一致性和完整性3、唯一:unique4、非空:not null5、默认值:deafult6、...
  • MySQL外键约束

    2021-10-03 10:01:07
    外键约束涉及到了两张:父和子,父和子不能是同一张,且它们必须使用相同的存储引擎。 父包含引用键(referenced key),子包含外键,即子引用了父。 外键和引用键必须具有相同的数据类型:...
  • alter table stu5 add constraint 编号 foreign key (bh) ...说明:stu2和stu5是两张,“编号”为字段bh的约束名(是stu5中的一个属性),bh是stu2中的主键,而bh并不是stu5的主键,那么则称stu2为主,stu5为...
  • mysql外键约束

    2021-10-31 20:34:16
    为两个数据建立连接,,约束两个数据的一致性和完整性。比如,一个水果摊,只有苹果、 桃子、李子、西瓜等4中水果,那么你选择就只能 选择 苹果 、桃子、李子、西瓜 其他的水果都是不能购买的 (上面也就是...
  • 外键约束

    千次阅读 2018-09-27 18:09:19
    如果公共关键字在一个关系中是主关键字,那么这个公共关键字称为另一个关系的外键
  • MySQL的约束——外键约束
  • 如何在MySQL中设置外键约束

    千次阅读 2021-01-18 21:31:24
    (1) 外键的使用:外键的作用,主要有两个:一个是让数据库自己通过外键来保证数据的完整性和一致性一个就是能够增加ER图的可读性有些人认为外键...例如:有一个基础数据表,用来记录商品的所有信息。其他表都保存商品I...
  • 详解MySQL 外键约束

    2021-02-08 05:48:05
    详解MySQL 外键约束,...官方文档:https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html1.外键作用:MySQL通过外键约束来保证之间的数据的完整性和准确性。2.外键的使用条件两个必须...
  • mysql中设置外键约束的方法:可以通过FOREIGN KEY关键字来指定外键,语法“ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名 (列名);”。(1) 外键的使用:外键的作用,主要有两个:一个是...
  • 外键约束如果公共关键字在一个关系中是主关键字,那么这个公共关键字称为另一个关系的外键外键(FOREIGN KEY)假设有有A、B两张数据表,A表有一个字段id用来唯一标识A中的一条记录,B表有一个字段a_id来关联A表的一...
  • 1,创建时加入的约束a)非空约束,not nullb)唯一约束,uniquec)主键约束,primary keyd)外键约束,foreign key1,非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空drop table if existst_student;...
  • MySQL 外键约束(FOREIGN KEY)是的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的而言,相关联字段中主键所在的就是主(父),外键所在的就是从(子)。 外键用来建立主与从的...
  • mysql主键约束主键(PRIMARY KEY)的完整称呼...1、在创建时设置主键约束在 CREATE TABLE 语句中,通过 PRIMARY KEY 关键字来指定主键。在定义字段的同时指定主键,语法格式如下: PRIMARY KEY [默认值]例 1在 test...
  • 一、外键约束1、什么是外键?外键指的是其他表中的主键,当做该的外键。2、创建外键。2.1 可在创建的时候直接创建外键,如图所示:create table table_name (字段名 字段属性,[add constraint 外键name] ...
  • FOREIGN KEY约束添加规则1、外键约束并不仅仅可以与另一的主键约束相链接,它还可以定义为引用另一个中 UNIQUE 约束的列。2、如果在 FOREIGN KEY 约束的列中输入非 NULL 值,则此值必须在引用列中存在;否则,...
  • 外键 # 外键前戏 以创建一张员工为例 表头内容包含: id name age dep_name dep_desc """ 缺陷: 1.的重点不清晰 ... 带来了一个小问题 之间的数据没有对应关系了 """ # 解决:外键
  • 话题MariaDB InnoDB外键问题想开始说我是InnoDB的新手并且昨天花了一整天阅读帖子我一路上尝试了很多东西让我现在在哪里,所以我已经冲出了这条黑暗的森林还有其他方法.我有一个,它是我数据模型中许多的核心.所以...
  • 外键是用来实现参照完整性的,不同的外键约束方式将可以使两张紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。这里以MySQL为例,总结一下3种外键约束方式的区别和联系。这里以用户...
  • 开发规范中为什么要禁用外键约束

    千次阅读 多人点赞 2022-03-11 13:39:11
    阿里JAVA规范 不得使用外键与级联,一切外键概念必须在应用层解决。 外键的优缺点 缺点:每次做DELETE或者UPDATE都...当每一次向订单明细添加数据时,会检查订单表里是否存在对于的外键约束数据id是否存。 并发问题
  • 数据库外键约束的优点和缺点

    千次阅读 2021-09-25 23:35:33
    1,由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。 eg:数据库和应用是一...
  • 本文介绍了Mysql的外键约束使用,并举了一个简单的实例:利用外键的CASCADE实现了常用收藏功能的数据表设计。
  • (一)Student (学生)添加主键约束学号SNO(二)Course(课程)添加主键约束课程号CON,添加外键约束教工编号TON(三)Score(成绩)添加两个外键约束学号SNO和课程号CNO,主键约束是Sno+ Cno。(四)Teacher(教师...
  • 在Navicat中建立两个之间的外键约束,但是出现了下面的报错 这是什么情况呢? 我查找了一些资料,最多的说法是: MyISAM引擎适合全文搜索,InnoDB适合外键。(前面语句中我将默认的InnoDB引擎修改成了MyISAM) ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 59,937
精华内容 23,974
热门标签
关键字:

被其他外键约束的表如何修改数据

友情链接: DA529.rar