精华内容
下载资源
问答
  • 创建外键约束的关键字
    千次阅读
    2019-10-23 13:50:21

    1. 创建表时创建外键

    CREATE TABLE T1
       (employee_id     NUMBER(4), 
        last_name       VARCHAR2(10), 
        job_id          VARCHAR2(9), 
        salary          NUMBER(7,2), 
        department_id   CONSTRAINT fk_deptno 
                        REFERENCES T2(department_id) ); 
    

    其中T2叫父表,T1叫子表。

    注意事项:

    1. 父子表中相应列的数据类型必须一致,列名可以不同;
    2. 父表字段必须是主键或者唯一键;
    3. 子表中外键的值必须在父表中,否则会报错:未找到父项关键字。

    2. 修改时创建外键约束

    有三种形式的外键约束:

    2.1 普通外键约束

    如果存在子表引用父表主键,则无法删除父表记录

    ALTER TABLE T1 ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY(INVOICE_ID ) REFERENCES T2 (ID);

    2.2 级联外键约束

    可删除存在引用的父表记录,而且同时把所有有引用的子表记录也删除

    ALTER TABLE T1 ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY(INVOICE_ID ) REFERENCES T2(ID) ON DELETE CASCADE;

    2.3 置空外键约束

    可删除存在引用的父表记录,同时将子表中引用该父表主键的外键字段自动设为NULL,但该字段应允许空值
    ALTER TABLE T1 ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY(INVOICE_ID ) REFERENCES T2(ID) ON DELETE SET NULL;

    3. 启用/禁用外键

    DISABLE(禁用) | ENABLE (启用)CONSTRAINT constraint_name

    4. 删除外键约束

    drop constraint constraint_name;

    5. 其他

    创建主键:
    ALTER TABLE T1 ADD CONSTRAINT PK_T1_ID PRIMARY KEY(ID);

    更多相关内容
  • 创建一张员工表为例 表头内容包含: id name age dep_name dep_desc """ 缺陷: 1.表的重点不清晰 到底是员工表还是部门表 可以忽略 2.表中相关字段一直在重复存储 浪费存储空间 可以忽略 3.表的...

    外键

    # 外键前戏
    以创建一张员工表为例
    表头内容包含:
    	id name age	dep_name dep_desc	
    """
    缺陷:
    	1.表的重点不清晰
    		到底是员工表还是部门表 可以忽略
    	
    	2.表中相关字段一直在重复存储
    		浪费存储空间 可以忽略
    		
    	3.表的扩展性极差,牵一发而动全身  
    		不能忽略
    
    
    解决方式:
    	将上述一张表拆分成两张表 
    	emp与dep
    	上述三个缺陷全部解决
    	
    	
    但是:
    	带来了一个小问题 表与表之间的数据没有对应关系了
    """
    
    
    # 解决:外键
    	外键字段>>>:部门编号
    """
    外键:
    	其实就是用来标识表与表之间的数据关系
    	简单的理解为该字段可以让你去到其他表中查找数据
    """
    

    表与表之间建关系

    	# 表关系总共就四种
    	一对多
    	多对多
    	一对一
    	没有关系
    

    一对多

    # 判断表关系的方式:换位思考
    	# 一对多
    		以员工和部门表为例
    		先站在员工表的基础之上
    			问:一个员工信息能否对应多个部门信息
    			答:不可以
    		再站在部门表的基础之上
    			问:一个部门信息能否对应多个员工信息
    			答:可以
    		结论:一个可以一个不可以 那么表关系就是"一对多"
    			员工表是多 部门表是一
    		"""
    		针对一对多的表关系 外键字段建在多的一方
    		表关系没有'多对一'一说 都是'一对多'
    		"""
    """
    使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表
    之后再添加外键字段
    create table dep(
    	id int primary key auto_increment,
    	dep_name varchar(32),
    	dep_desc varchar(254)
    );
    
    create table emp(
    	id int primary key auto_increment,
    	name varchar(32),
    	age int,
    	dep_id int,
    	foreign key(dep_id) references dep(id)  # emp表中的dep_id必须在dep表中包含
    ); 
    
    insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('安保部','保卫家园'),('人事部','招兵买马'),('财务部','军饷保证');
    
    insert into emp(name,age,dep_id) values('jason',18,1),('tony',28,2),('mary',38,3),('jack',48,4);
    """
    

    在这里插入图片描述
    多对多关系

    	# 多对多关系
    	以书籍表与作者表为例
    	先站在书籍表的基础之上
    		问:一个书籍信息能否对应多个作者信息
    			答:可以
    	再站在作者表的基础之上
    		问:一个作者信息能否对应多个书籍信息
    			答:可以
    	结论:两个都可以 那么表关系就是"多对多"
    	# 多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)
    
    """
    create table book(
    	id int primary key auto_increment,
    	title varchar(32),
    	price float(6,2)
    );
    
    create table author(
    	id int primary key auto_increment,
    	name varchar(32),
    	age int
    );
    
    create table book2author(
    	id int primary key auto_increment,
    	author_id int,
    	book_id int,
    	foreign key(author_id) references author(id)
    	on update cascade
    	on delete cascade,
    	foreign key(book_id) references book(id)
    	on update cascade
    	on delete cascade
    );
    """
    

    一对一表关系

    	# 一对一表关系
    	以作者表与作者详情表为例
    		先站在作者表的基础之上
    			问:一个作者信息能否对应多个作者详情信息
    				答:不可以
    		再站在作者详情表的基础之上
    			问:一个作者详情信息能否对应多个作者信息
    				答:不可以
    		结论:两个都不可以 
                
    	那么表关系可能是"一对一"或者"没有关系"
    	# 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
        
    """
    create table author(
    	id int primmary key auto_increment,
    	name varchar(32),
    	age int,
    	author_id int unique,
    	foreign key(author_id) references author_detail(id)
    	on update cascade
    	on delete cascade
    );
    
    create table author_detail(
    	id int primary key auto_increment,
    	phone varchar(32),
    	address varchar(32)
    );
    """
    

    外键约束

    # 1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
    
    # 2.在插入新数据的时候 应该先确保被关联表中有数据
    
    # 3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
    
    # 4.在修改和删除被关联表中的数据的时候 无法直接操作
    
    """如果想要数据之间自动修改和删除需要添加额外的配置"""
    create table dep(
    	id int primary key auto_increment,
    	dep_name varchar(32),
    	dep_desc varchar(254)
    );
    
    create table emp(
    	id int primary key auto_increment,
    	name varchar(32),
    	age int,
    	dep_id int,
    	foreign key(dep_id) references dep(id)  # emp表中的dep_id必须在dep表中包含
    	on update cascade  # 级联更新(不加逗号)
    	on delete cascade  # 级联删除
    );
    
    
    """
    由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
    	所以在实际开发项目中 有时候并不会使用外键创建表关系
    	而是通过SQL语句层面 建立逻辑意义上的表关系
    	
    	eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
    """
    

    在这里插入图片描述
    在这里插入图片描述

    操作表的SQL语句补充

    show tables;  # 展示
    desc 表名;  # 查看
    create table t1(id int);  # 创建
    alter table t1 change id nid int;  # 改变
    drop table t1;  # 删除
    
    # 语法:
    1. 修改表名  
    	alter table 表名 rename 新表名;
                              
    2. 增加字段
    	alter table 表名 add 字段名 数据类型 [完整性约束条件…],
                              
    	alter table 表名 add 字段名 数据类型 [完整性约束条件…] first;  # 增加到首行
                              
    	alter table 表名 add 字段名 数据类型 [完整性约束条件…] after 字段名;  # 增加到某行之后      
                                               
    3. 删除字段
    	alter table 表名 drop 字段名;
                              
    4. 修改字段  # modify只能改字段数据类型完整约束,不能改字段名,但是change可以!
    	alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
                              
    	alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];                   
    

    查询关键字

    # 数据准备
    create table emp(
    	id int primary key auto_increment,
    	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);
    

    在这里插入图片描述
    关键字之select与from

    # from 控制的是查询哪张表
    
    # select 控制的是查询表里面的哪些字段
    
    select * from emp;
    select id,name from emp;
    

    关键字之where筛选

    # where筛选功能 
    模糊查询:没有明确的筛选条件
    关键字:like
    关键符号:
    	%:匹配任意个数任意字符
    	_:匹配单个个数任意字符
    show variables like '%mode%se';
    
    
    # 1.查询id大于等于3小于等于6的数据
    	select * from emp where id >= 3 and id <= 6;  # 写法一
    	select * 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字母的员工姓名和薪资
    	select * from emp where name like '%o%';
    """
    在刚开始接触 mysql查询的时候,建议按照查询的优先级顺序拼写出你的sql语句
    
    先是查哪张表 from emp
    再是根据什么条件去查 where name like ‘%o%’
    再是对查询出来的数据筛选展示部分 select name,salary
    """
    
    # 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;
    

    在这里插入图片描述
    在这里插入图片描述

    关键字之group by分组

    # 分组
    按照某个指定的条件将单个单个的个体分成一个个整体
    eg:
    	按照男女将人分组
    	按照肤色分组
    	按照年龄分组
        
    # 分组之后默认只能够直接拿到该分组的依据 其他数据都不能直接获取
    # 数据分组应用场景:每个部门的平均薪资,男女比例等
        
    # 聚合函数
    	聚合函数主要就是配合分组一起使用
    	max min sum count avg
    
    
    # 1.按部门分组
    	针对5.6MySQL 需要自己设置sql_mode
    	set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
    	设置完成后 重新进入MySQL 再执行:
    		select post from emp group by post;
    
    """
    设置sql_mode为only_full_group_by,意味着以后但凡分组,只能取到分组的依据
    
    不应该再去取组里面的单个元素的值,那样的话分组就没有意义了,因为不分组就是对单个元素信息的随意获取
    """
    
    	select * from emp group by post;  # 报错
    	select id,name,sex from emp group by post;  # 报错
    	select post from emp group by post;  # 获取部门信息
    	# 强调:只要分组了,就不能够再“直接”查找到单个数据信息了,只能获取到组名
    
    
    # 2.获取每个部门的最高工资 
    	先以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
    	# 再每个部门的最高工资
    		select post,max(salary) from emp group by post;
        
    	补充:在显示的时候还可以给字段取别名
    		select post as '部门',max(salary) as '最高工资' from emp group by post;
    	as也可以省略 但是不推荐省 因为寓意不明确
        
    	# 每个部门的最低工资
    		select post,min(salary) from emp group by post;
            
    	# 每个部门的平均工资
    		select post,avg(salary) from emp group by post;
            
    	# 每个部门的工资总和
    		select post,sum(salary) from emp group by post;
            
    	# 每个部门的人数
    		select post,count(id) from emp group by post;
    

    在这里插入图片描述

    分组补充说明

    # group_concat 分组之后使用
    如果真的需要获取分组以外的数据字段 可以使用group_concat()
    
    # 每个部门的员工姓名
    select post,group_concat(name) from emp group by post;
    
    select post,group_concat(name,'|',sex) from emp group by post;  # 以 | 分隔显示多项数据拼接的结果
    
    
    # concat  不分组使用
    	select concat(name,sex) from emp;
    	select concat(name,'|',sex) from emp;
    

    在这里插入图片描述

    在这里插入图片描述

    关键字之having过滤

    """
    where与having都是筛选功能 但是有区别:
    	where在分组之前对数据进行筛选
    	having在分组之后对数据进行筛选
    
    我们一定要有一个简单的认识:
    	一条SQL语句的结果也可以看成是一张全新的表!
    """
    
    # 统计各部门年龄在30岁以上的员工平均工资 并且保留平均工资大于10000的部门
    	select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
    

    在这里插入图片描述

    关键字之distinct去重

    # 对有重复的展示数据进行去重操作 一定要是重复的数据
    	select distinct id,age from emp;
    	select distinct post from emp;
    

    在这里插入图片描述

    关键字之order by排序

    # order by默认就是升序排
    	select * from emp order by salary asc; 
    
    # 降序排
    	select * from emp order by salary desc;
    
    # 先按照age降序排,在年轻相同的情况下再按照薪资升序排
    	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;
    

    在这里插入图片描述

    关键字之limit分页

    # 限制展示条数
    	select * from emp limit 3;
        
    # 查询工资最高的人的详细信息
    	select * from emp order by salary desc limit 1;
    
    # 分页显示
    	select * from emp limit 0,5;  # 第一个参数表示起始位置,第二个参数表示的是条数,不是索引位置
    
    	select * from emp limit 5,5;
    

    在这里插入图片描述

    在这里插入图片描述

    关键字之regexp正则

    # 匹配 以j开头 中间任意字符 以 n或者y结尾
    	select * from emp where name regexp '^j.*(n|y)$';
    

    在这里插入图片描述

    展开全文
  • 文章目录今日学习目标学习内容一、自增特性二、约束条件之外键外键的定义外键约束创建三、查询关键字查询关键字之select与from查询关键字之where筛选查询关键字之group by分组 学习内容 自增特性 约束条件之外...

    今日学习目标

    • 正式学习MySQL数据库语句。


    学习内容

    • 自增特性

    • 约束条件之外键

    • 查询关键字


    一、自增特性

    自增约束一般都是与主键约束一起使用。
    这种方式是使用数据库提供的自增数值型字段作为自增主键,它的优点是:
    (1)数据库自动编号,速度快,而且是增量增长,按顺序存放,对于检索非常有利;
    (2)数字型,占用空间小,易排序,在程序中传递也方便;
    (3)如果通过非系统增加记录时,可以不用指定该字段,不用担心主键重复问题。

    因为是系统自增所以当我们人为打断顺序的话,系统是不会修改的
    自增不会随着数据的删除而回退

    二、约束条件之外键

    外键的定义

    如果同一个属性字段X 在表一中是主键,而在表二中不是主键,则字段X称为表二的外键。

    创建外键约束,保证数据的完整性和一致性

    注意:

    1. 与外键关联的主表的字段必须设置为主键。
    2. 要求从表不能是临时表,主从表的字段具备相同的数据类型、字符长度和约束。

    外键关系

    1. 一对多
    2. 多对多
    3. 一对一
    4. 没有关系

    外键约束创建

    • 创建外键约束的条件

      MySQL数据库外键的创建,需要满足以下四个条件,否则会被MySQL数据库拒绝:
      1、创建外键的表和列存在
      2、组成外键的列存在索引
      3、必须指定数据表的引擎为InnoDB
      4、外键字段和关联字段,数据类型必须一致

    • 在创建数据表时创建外键约束

      在创建数据表时创建外键约束,只需要在创建数据表的create语句后面,使用foreign key关键字指定本表的外键字段,使用reference关键字指定关联表的关联字段,并且明确约束行为即可。
      创建外键约束的SQL语句示例如下:

      create table student (
      		id int primary key auto_increment,
      		name varchar(20),
      		department varchar(20) ,
      		index (id)
      );
      		
      create table grade (
      		id int primary key auto_increment,
      		Sid int,
      		Cid int,
      		score int,
      		foreign key (Sid) references student(id) 
      );
      
    • foreign key的约束效果
      1.创建表的时候 应该先创建被关联表(没有外键字段的表)
      2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
      外键字段填入的值只能是被关联表中已经存在的值
      3.修改、删除被关联表数据都会出现障碍

    • 在创建数据表后添加外键约束
      同样的,MySQL也支持在创建数据表后再添加外键约束。在上例中,我们先删除grade表,然后再创建grade表,现不创建外键,尝试在创建grade表后添加外键,相关SQL命令如下:

      create table grade(
        	id int primary key auto_increment,
      		Sid int,
      		Cid int,
      		score int
      );
      alter table grade add index(Sid);
      alter table grade add foreign key (Sid) references student(id) on delete restrict on update cascade;
      
    • 级联更新级联删除
      概念:

      当对主键表中的数据进行删除和更新时,数据库会对关联的数据会自动删除和更新。

      on update cascade  # 级联更新
      on delete cascade  # 级联删除
      

      语句主要放入设计了外键约束的表里,并且要和外键约束一起

      create table student (
      		id int primary key auto_increment,
      		name varchar(20),
      		department varchar(20) ,
      		index (id)
      );
      		
      create table grade (
      		id int primary key auto_increment,
      		Sid int,
      		Cid int,
      		score int,
      		foreign key (Sid) references student(id)
      		on update cascade 
              on delete cascade
      );
      
    • 多对多关系

      以图书与作者表为例

      1. 先站在图书表的角度
        问:一本书籍能否对应多名作者
        答:可以
      2. 再站在作者表的角度
        问:一名作者能否对应多本书籍
        答:可以

      结论:换位思考之后两边都可以 那么就是"多对多"关系

      create table book(
          id int primary key auto_increment,
          title varchar(32),
          price float(10,2),
          author_id int,
          foreign key(author_id) references author(id) 
          on update cascade  
          on delete cascade  
      );
      create table author(
          id int primary key auto_increment,
          name varchar(32),
          gender enum('male','female','others'),
          book_id int,
          foreign key(book_id) references book(id) 
          on update cascade  
          on delete cascade  
      );
      

      分析

      针对多对多关系 需要单独开设第三张表专门存储关系

      create table book(
          id int primary key auto_increment,
          title varchar(32),
          price float(10,2)
      );
      create table author(
          id int primary key auto_increment,
          name varchar(32),
          gender enum('male','female','others')
      );
      create table book2author(
          id int primary key auto_increment,
          author_id int,
          book_id int,
          foreign key(author_id) references author(id) 
          on update cascade   
          on delete cascade,  
          foreign key(book_id) references book(id) 
          on update cascade  
          on delete cascade  
      );
      

      针对多对多表关系

      两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

    • 一对一关系

      针对qq用户表 其实里面的数据可以分成两类

      • 热数据:经常需要使用的数据
        eg:qq号码 座右铭 个人简介 爱好
      • 冷数据:不怎么经常需要使用的数据
        eg:邮箱 电话 学校 …
        为了节省资源并降低数据库压力 会将表一分为二
        • 用户表
          存使用频率较高的数据字段
        • 用户详情表
          存使用频率较低的数据字段

      1.先站在用户表的角度

      问:一个用户数据能否对应多个用户详情数据
      答:不可以

      2.再站在用户详情表的角度

      问:一个用户详情数据能否对应多个用户数据
      答:不可以

      结论:换位思考之后两边都不可以 那么关系可能有两种
      ‘没有关系’:用膝盖都能判断出来
      ‘一对一关系’
      针对’一对一关系’外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方

      create table User(
          id int primary key auto_increment,
          name varchar(32),
          gender enum('male','female','others'),
          user_detail_id int unique,  
          foreign key(user_detail_id) references UserDetail(id) 
          on update cascade   
          on delete cascade, 
      );
      create table UserDetail(
          id int primary key auto_increment,
          phone bigint,
          age int
      );
      

    三、查询关键字

    查询关键字之select与from

    • select用于指定查询的字段

    • from用于指定查询的表

      select 字段名 from 表名;
      

    查询关键字之where筛选

    (1)、按条件表达式筛选
    
    条件运算符:>  <  =  !=  <>  >=  <=
    
    (2)、按逻辑表达式筛选
    
    逻辑运算符:    &&        ||        and        or        not
    
    (3)、模糊查询
    
    like        between and        in        is null
    
    """
    查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
    关键字 				
    		like:开启模糊查询的关键字
    关键符号
    		%:匹配任意个数的任意字符	
    		_:匹配单个个数的任意字符
    """
    

    用法举例:

    select last_name, salary, commission_pct from employees where salary >= 10000 and salary <= 20000;
     
    #查询部门编号不是在90和110之间,或者工资高于15000的员工信息。
     
    select * from employees where not (department_id >= 90 and department_id <= 110) or salary > 15000;
    
    select * from employees where last_name like '%a%';            #%代表任意多个字符
     
    #查询员工名中第三个字符为e, 第五个字符为a的员工名和工资
    select last_name, salary from employees where last_name like '__e_a%';         #“_”代表任意一个字符
     
    #查询员工名中第二个字符为_的员工名
    select last_name from employees where last_name like '_/_%';                #“/”代表一个转义字符
     
    #between and 包含边界值
    select * from employees where employee_id between 100 and 200;
             
    #查询员工编号为 IT_DPOG、AD_VP、 AD_PRES中的一个员工名和工种编号————or实现
    select last_name,job_id from employees where job_id = 'IT_PROT' or job_id = 'AD_VP' or JOB_ID = 'AD_PRES';
             
    #查询员工编号为 IT_DPOG、AD_VP、 AD_PRES中的一个员工名和工种编号————in实现
    select last_name,job_id from employees where job_id in ('IT_PROT',  'AD_VP',  'AD_PRES');        #注意此处不支持统配符,相当于“=”,只有like是支持通配符的。
     
    #查询没有奖金的员工名和奖金率
    select last_name, commission_pct from employees where commission_pct = null; #这样不可以,因为“=”不能判断null值,只能用“is”,还有is not null,注意is只是与null搭配的!!!不能is not 后边跟字符串和数值
     
    #安全等与<=>可以同时替代=和is,也即是说它既可以判断null值也可以判断数值
    select last_name, commission_pct from employees where commission_pct <=> nul
    

    查询关键字之group by分组

    关键字:group by 后面根列名,通常和聚合函数一起使用

    • 聚合函数
      max(列名) --------统计该列的最大值
      min(列名) --------统计该列的最大值
      avg(列名) --------统计该列的平均值
      sum(列名) --------统计该列的和
      count(列名) ------统计该列的行数,如果该列的值是null,不统计在内
      count(*) ------统计所有的列,相当于统计行数,不会忽略null

    例题:查询每门课的最高分
    分两步:
    1、按照课程编号分成5部分,即分成5组 ------》group by
    2、找出每一个组的最高分 ------》max

    SELECT MAX(grade),cid FROM score GROUP BY cid;
    

    例如:
    查询考试科目小于3门课的学生
    1、使用外连接连接两个表:成绩表、学生表 ,以学生表为主表
    2、按sid分组
    3、统计行数
    4、过滤出行数大于等于3的学生 ------》having

    注: 分组以后再过滤,不能用where,要用having

    SELECT COUNT(grade),s.sname FROM student s LEFT JOIN score sc ON s.sid = sc.sid GROUP BY s.sid HAVING COUNT(grade) < 3 ;
    

    总结:

    语法:select 1个或多个聚合函数,分组的列名 from 表名 group by 列名 having 统计函数 比较运算符 值;
    where跟在表名from的后面,是根据列来进行查询数据的 having跟在group
    by的后面,是针对分组之后的数据在进行过滤查询,即根据聚合函数来查询数据


    展开全文
  • 外键约束

    千次阅读 2018-09-27 18:09:19
    如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键

    外键约束

    如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键

    外键(FOREIGN KEY)

    什么是外键

    假设有有AB两张数据表,A表有一个字段id用来唯一标识A中的一条记录,B表有一个字段a_id来关联A表的一条记录,则字段a_id被称为B表的外键。

    外键、主键的区别

    • 定义

      • 主键:唯一标识一条记录,不能有重复,不允许为空,一张数据表只能有一个主键
      • 外键:表的外键是另一张表中可以唯一标识的字段(如主键,一般都是主键),外键可以有重复值,可以是空值,一张数据表可以有多个外键
    • 作用

      • 主键:用来保证数据完整性
      • 外键:用来关联其他数据表,保持数据一致性,完整性,主要目的是控制存储在外键表中的数据

    外键的功能

    外键功能具有两种形式。

    1、 阻止执行

    • 从表插入新行,其外键值不是主表的主键值便阻止插入
    • 从表修改外键值,新值不是主表的主键值便阻止修改
    • 主表删除行,其主键值在从表里存在便阻止删除(要想删除,必须先删除从表的相关行)
    • 主表修改主键值,旧值在从表里存在便阻止修改(要想修改,必须先删除从表的相关行)

    2、级联执行

    • 主表删除行,连带从表的相关行一起删除
    • 主表修改主键值,连带从表相关行的外键值一起修改

    两种方法提供给用户选择。无论选取哪种方法,从表里都不会有多余行。从另一个角度理解,用拒绝同一事物在从表中的标志与主表不一致来实现与主表中的标志一致。

    MySQL中外键的介绍

    • MySQL中只有InnoDB支持外键
    • 由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。
    • 设置外键约束的两个表之间会具有父子关系,即子表中外键的字段的取值范围由父表所决定
    • 设置外键一定程度上降低数据库的速度
    • 子表的外键字段的数据类型和父表中要一致

    外键约束的相关语法(MySQL)

    创建标识生成外键约束(CREATE TABLE)

    语法

    CREATE TABLE table_name
    (
    	.
        .
        key_name ...
    	...
        CONSTRAINT constraint_name
    	FOREIGN KEY foreign_key_name (column_name1)
    	REFERENCES parent_table (column_name2)
    	ON DELETE action1
    	ON UPDATE action2
    )
    

    语法关键字详解:

    • key_name:用来当做外键的字段,用于下面FOREIGN KEY子句
    • CONSTRAINT:为外键约束定义约束名称constraint_name,如果省略它,MySQL将自动生成一个名称
    • FOREIGN KEY:指定子表中关联父表中记录的字段column_name1,可以在FOREIGN KEY子句后放置一个外键名称foreign_key_name,或者MySQL自动创建一个名称
    • REFERENCES:指定父表parent_table及其在子表中被引用的列column_name2
    • ON DELETE:定义当父表中的记录被删除时,子表的记录要执行的操作action1,如果省略ON DELETE子句并删除父表中的记录,则MySQL将拒绝删除子表中相关联的数据。action1有以下几个选项:
      • RESTRICT(约束):当在父表(即外键的来源表)中删除一条或多条记录时,首先检查该记录是否有对应外键,如果有则不允许删除。
      • NO ACTION:在MySQL中,同RESTRICT,如果存在从数据,不允许删除主数据
      • CASCADE(级联):当在父表(即外键的来源表)中删除一条或多条记录记录时,首先检查该记录是否有对应外键,如果有则也删除外键在子表(即包含外键的表)中的记录。
      • SET NULL:当在父表(即外键的来源表)中删除一条或多条记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(注意:此时要求该外键允许为null)
    • ON UPDATE:定义在父表中的记录更新时,子表中的记录要执行的操作action2,当父表中的行被更新时,如果省略ON UPDATE子句,MySQL将拒绝对子表中的行的任何更新。action2有以下几个选项:
      • RESTRICT(约束):当在父表(即外键的来源表)中更新一条或多条记录时,首先检查该记录是否有对应外键,如果有则不允许更新。
      • NO ACTION:在MySQL中,同RESTRICT,如果存在从数据,不允许更新主数据
      • CASCADE(级联):当在父表(即外键的来源表)中更新一条或多条记录的主键值时,首先检查该记录是否有对应外键,有则更新子表中对应外键的值
      • SET NULL:当在父表(即外键的来源表)中更新一条或多条记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(注意:此时要求该外键允许为null)。

    示例

    收货地址表中用户IDuser_id设置为外键,并定义约束

    CREATE TABLE `address` (
      `id` int(11) NOT NULL,
      `user_id` int(11) DEFAULT NULL,
      `address` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      CONSTRAINT `c_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    修改表结构时添加外键约束(ALTER TABLE)

    语法

    ALTER table_name
    ADD CONSTRAINT constraint_name
    FOREIGN KEY foreign_key_name(column_name1)
    REFERENCES parent_table(column_name2)
    ON DELETE action1
    ON UPDATE action2;
    

    语法关键字含义同上

    示例

    创建收货地址表address,给该表增加外键约束:用户IDuser_id设置为外键,并定义约束

    # 生成address表
    CREATE TABLE `address` (
      `id` int(11) NOT NULL,
      `user_id` int(11) DEFAULT NULL,
      `address` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    # 添加外键约束
    ALTER `address`
        ADD CONSTRAINT `c_user_id`
        FOREIGN KEY `fk_user_id`(`user_id`)
        REFERENCES `users`(`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE;
    

    撤销外键约束

    ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
    

    语法关键字含义同上

    示例

    ALTER TABLE `address` DROP FOREIGN KEY `c_user_id`;
    


    展开全文
  • 我们可以在创建表时设置外键约束。在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键,具体的语法格式如下:[CONSTRAINT ] FOREIGN KEY 字段名 [,字段名2,…]REFERENCES 主键列1 [,主键列2,…]举例:...
  • MySQL外键约束-foreign key

    千次阅读 2022-04-13 20:04:31
    外键也称之为外键约束 : 关键字foreign key 外键:外面的键,一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键。 外键所在的表称之为子表(附表);外键所指向的主键所在的表称之为父表(主表...
  •  外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。  这里以MySQL为例,总结一下3种外键约束方式的区别和联系。  ...
  • SQL Server外键约束

    千次阅读 2022-04-28 15:22:16
    SQL Server外键约束 SQL Server外键约束简介 外键是一个表中的一列或一组列,它唯一地标识另一个表的行。 vendor_groups 和v endor 表,它们的结构如下: 每个供应商属于供应商组,每个供应商组可能有零个或...
  • 外键约束(foreign key) [MySQL][数据库]

    千次阅读 2022-03-20 00:59:47
    外键约束(FOREIGN KEY(字段) REFERENCES 主表名(字段)) 外键约束的作用: 限定某个字段的引用完整性 我们之前在讲数据完整性的时候就提到过引用完整性 引用完整性(eg: 员工所在的部门,在部门表中一定要能找到这个...
  • MySQL外键约束详解

    千次阅读 2021-12-19 08:02:01
    今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL外键约束详解。 一、MySQL外键约束作用 ...(一)创建外键约束的条件 (二)在创建数据表时创建外键约束 (三)在创建数据表后添加外键约束 三、外键约束功能演示
  • mysql创建表时设置外键约束的方法

    千次阅读 2021-01-18 19:18:26
    mysql创建表时设置外键约束的方法发布时间:2020-06-17 15:54:11来源:亿速云阅读:312作者:元一MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就...
  • 关键字 foreign key 外键演练 新建老师生表,演练外键的相关操作 主表创建 # 建库 CREATE DATABASE dbSchool0523 CHARACTER SET utf8 # 用库 USE dbSchool0523 # 建主表 CREATE TABLE teacher( id INT PRIMARY ...
  • 创建:create 修改:alert 删除:drop 对数据库的操作 数据库的创建:create database 数据库名; 数据库如果不存在就创建:create database if not exists 数据库名; 更改数据库的字符集:alert database 数据库名 ...
  • -- 创建数据表 tb_emp2,并在表 tb_emp2 上创建外键约束,让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id CREATE TABLE tb_emp2 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary ...
  • mysql外键约束怎么写

    千次阅读 2021-01-18 19:17:33
    mysql外键约束的写法:【[CONSTRAINT ] FOREIGN KEY 字段名 REFERENCES 主键列1】。外键约束是表的一个特殊字段,经常与主键约束一起使用。在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键。(推荐学习:...
  • 数据库约束
  • 文章目录数据表的约束主键约束 数据表的约束 为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;...在创建数据表时设置主键约束,既可以为表中的一个字段设置主键,也可以为表中多个字
  • mysql中设置外键约束的方法:可以通过FOREIGN KEY关键字来指定外键,语法“ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名 (列名);”。(1) 外键的使用:外键的作用,主要有两个:一个是...
  • 外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。这里以MySQL为例,总结一下3种外键约束方式的区别和联系。这里以用户表...
  • Mysql外键约束设置使用方法

    千次阅读 2021-01-17 10:44:27
    外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的完整性和一致性两个表必须是InnoDB表,MyISAM表...
  • 外键约束如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键外键(FOREIGN KEY)假设有有A、B两张数据表,A表有一个字段id用来唯一标识A中的一条记录,B表有一个字段a_id来关联A表的一...
  • mysql外键约束

    2022-03-30 16:19:59
    mysql 外键创建、添加及使用
  • MySQL怎么设置外键约束

    千次阅读 2021-01-18 19:07:52
    MySQL 外键约束(FOREIGN KEY)用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键的值必须等于另一个...
  • 外键约束三种形式

    千次阅读 2019-05-13 16:21:15
    外键有三种约束模式: district : 严格模式(默认的),父表不能删除或更新一个已经被子表数据引用的记录。 cascade : 级联模式,父表的操作,对应子表关联的数据也跟着操作。 set null : 置空模式,父表被操作...
  • 外键约束 外键约束的特点 添加数据 在外键约束下的数据操作 删除外键约束 多对多 操作案例 多表查询 交叉连接 内连接查询 外连接查询 子查询 子查询关键字-ALL ✈️子查询关键字-ANY和SOME 子查询...
  • 在学习外键之前,我们必须先搞懂一个概念,什么是“参照完整性”。 参照的关系中的属性值必须能够在被参照关系找到或者取空值,否则不符合数据库的语义。 在实际操作时如更新、删除、插入一个表中的数据,通过参照...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 39,262
精华内容 15,704
热门标签
关键字:

创建外键约束的关键字