精华内容
下载资源
问答
  • 介绍完数据类型后,接着介绍MySQL中的常见约束、事务以及视图。本文就分别从这三个方面进行介绍。 文章目录一、常见约束1. 创建表时添加约束2. 修改表时添加约束3. 修改表时删除约束二、事务控制语言三、视图1. 视图...

    介绍完数据类型后,接着介绍MySQL中的常见约束、事务以及视图。本文就分别从这三个方面进行介绍。

    一、常见约束

    含义:约束是一种限制,用于限制表中的数据,保证表中的数据的准确和可靠性
    分类:六大约束

    • PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
    • UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空,一个表中可以有多个唯一键
    • NOT NULL:非空,用于保证该字段的值不能为空
    • DEFAULT:默认,用于保证该字段有默认值,比如性别
    • FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
      在从表添加外键约束,用于引用主表中某列的值
    • CHECK:检查约束【mysql中不支持】

    添加约束的时机:可以在创建表时添加;也可以在修改表时添加
    约束的分类

    • 列级约束:六大约束语法上都支持,但外键约束没有效果(primary key, unique, not null, defalut 4个)
    • 表级约束:除了非空、默认,其他的都支持 (primary key, unique, foreign key 3个)

    列级约束与表级约束对比

    类型位置支持的约束类型(都不考虑check)是否可以起约束名
    列级约束列的后面语法都支持,但外键没有效果不可以
    表级约束所有列的下面默认和非空不支持,其他支持可以(主键没有效果)

    外键的特点

    1. 要求在从表设置外键关系
    2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
    3. 主表的关联列必须是一个key(一般是主键或唯一)
    4. 插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表

    语法

    CREATE TABLE 表名(
    	字段名 字段类型 列级约束,
    	字段名 字段类型,
    	表级约束
    );
    

    1. 创建表时添加约束

    (1)添加列级约束

    列级约束只支持:主键、唯一、默认、非空
    案例(测试列级约束):创建stuinfo表,并添加列级约束

    CREATE TABLE stuinfo(
    	id INT PRIMARY KEY,#主键
    	stuName VARCHAR(20) NOT NULL,#非空
    	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查,可以写,但没有效果
    	seat INT UNIQUE,#唯一
    	age INT DEFAULT  18,#默认
    	majorId INT REFERENCES major(id)#外键,表级约束中不支持
    );
    

    查看stuinfo表中的所有索引,包括主键、外键、唯一

    SHOW INDEX FROM stuinfo;

    (2)添加表级约束

    表级约束只支持:主键、唯一、外键
    语法:在各个字段的最下面添加
    【constraint 约束名】 约束类型(字段名)
    案例(测试表级约束):创建上述的stuinfo表,并添加表级约束

    CREATE TABLE stuinfo(
    	id INT,
    	stuname VARCHAR(20),
    	gender CHAR(1),
    	seat INT,
    	age INT,
    	majorid INT,
    	
    	CONSTRAINT pk PRIMARY KEY(id),#主键
    	CONSTRAINT uq UNIQUE(seat),#唯一键
    	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查,写上也没效果,不支持
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键	
    );
    

    (3)创建表时添加约束的通用写法

    CREATE TABLE stuinfo(
    	id INT PRIMARY KEY,
    	stuname VARCHAR(20) NOT NULL,
    	sex CHAR(1),
    	age INT DEFAULT 18,
    	seat INT UNIQUE,
    	majorid INT,
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    );
    

    主键和唯一键的大对比【面试题★】

    类型保证唯一性是否允许为空一个表中可以有多少个是否允许组合
    主键×至多有1个√(不推荐)
    唯一键√(最多允许一个为空)可以有多个√(不推荐)

    2. 修改表时添加约束

    语法:

    1、添加列级约束
    alter table 表名 modify column 字段名 字段类型 新约束;
    
    2、添加表级约束
    alter table 表名 addconstraint 约束名】 约束类型(字段名) 【外键的引用】;
    

    案例:使用修改表的方式添加约束

    CREATE TABLE stuinfo(
    	id INT,
    	stuname VARCHAR(20),
    	gender CHAR(1),
    	seat INT,
    	age INT,
    	majorid INT
    );
    
    #修改表时添加约束
    #1.添加非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
    #2.添加默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    #3.添加主键
    #(1)添加列级约束
    ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
    #(2)添加表级约束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);
    #4.添加唯一
    #(1)添加列级约束
    ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
    #(2)添加表级约束
    ALTER TABLE stuinfo ADD UNIQUE(seat);
    #5.添加外键
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
    

    3. 修改表时删除约束

    五种约束的删除方式为:默认和非空使用modify的方式删除、主键,唯一键和外键使用drop的方式删除
    案例:

    #1.删除非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
    #2.删除默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT ;
    
    #3.删除主键
    ALTER TABLE stuinfo DROP PRIMARY KEY;
    #4.删除唯一
    ALTER TABLE stuinfo DROP INDEX seat;
    #5.删除外键
    ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
    

    存在外键时的删除方式

    1. 级联删除
      1)先删除原来的外键约束
      ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
      2)添加新的有级联删除的外键约束
      ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
      3)删除从表的数据时,主表的数据自动删除

    2. 级联置空
      ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

    二、事务控制语言

    除了之前介绍的DQL(数据查询语言)、DML(数据操纵语言)以及DDL(数据定义语言),现在介绍TCL(事务控制语言,Transaction Control Language)。

    用通俗的语言讲Spring事务 这篇文章的开头部分,已经比较详细地介绍了事务。下面再简单介绍一下。

    事务的特性: ACID
    原子性:一个事务不可再分割,要么都执行要么都不执行
    一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
    隔离性:一个事务的执行不受其他事务的干扰
    持久性:一个事务一旦提交,则会永久的改变数据库的数据

    事务的隔离级别:

    级别 \ 存在问题脏读不可重复读幻读
    read uncommitted
    read committed (oracle默认级别)×
    repeatable read (mysql默认级别)××
    serializable×××

    查看隔离级别:select @@tx_isolation;
    设置隔离结果:set global|session isolation level 隔离级别;

    事务的分类:

    • 隐式事务:事务没有明显的开启和结束的标记,比如insert、update、delete语句,执行语句后自动提交
    • 显式事务:事务具有明显的开启和结束的标记

    事务的创建步骤:
    前提:必须先设置自动提交功能为禁用,可以通过SHOW VARIABLES LIKE 'autocommit'; 查看是否开启了自动提交
    set autocommit=0;
    步骤1:开启事务
    set autocommit=0;
    start transaction;【可选的语句,自动开启】
    步骤2:编写事务中的sql语句(select insert update delete 只能是这些语句,DDL语句是不支持的)
    语句1;
    语句2;
    【savepoint 保存点节点名】
    语句3;

    步骤3:结束事务
    commit; 提交事务
    rollback; 回滚事务
    rollback to 保存点名; 回滚事务到保存点

    事务案例:转账

    #开启事务
    SET autocommit=0;
    START TRANSACTION;
    #编写一组事务的语句
    UPDATE account SET balance = 500 WHERE username='张无忌';
    UPDATE account SET balance = 1500 WHERE username='赵敏';
    #结束事务
    COMMIT;
    #ROLLBACK;
    
    SELECT * FROM account;
    

    案例2:保存点的使用

    SET autocommit=0;
    START TRANSACTION;
    DELETE FROM account WHERE id=25;
    SAVEPOINT a;#设置保存点
    DELETE FROM account WHERE id=28;
    ROLLBACK TO a;#回滚到保存点
    
    SELECT * FROM account;
    

    案例3:比较使用delete清空表和 truncate清空表进行回滚的区别

    #delete清空的表能够回滚
    SET autocommit=0;
    START TRANSACTION;
    DELETE FROM account;
    ROLLBACK;
    
    #truncate清空的表不能够回滚
    SET autocommit=0;
    START TRANSACTION;
    TRUNCATE TABLE account;
    ROLLBACK;
    

    三、视图

    1. 视图介绍

    含义:视图是一张虚拟表,和普通表一样使用;视图是mysql5.1版本后出现的新特性,是通过表动态生成的数据

    使用视图的好处

    1. 重用sql语句
    2. 简化复杂的sql操作,不必知道它的查询细节
    3. 保护数据,提高安全性(视图只提供了一部分信息,原始表中的保密信息如身份证号可以不提供出来)

    视图和普通表的对比

    类型创建语法的关键字是否实际占用物理空间使用
    视图create view只是保存了sql逻辑增删改查,只是一般不能增删改
    普通表create table保存了数据增删改查

    2. 创建视图

    语法:

    create view 视图名
    as
    查询语句;
    

    案例1:使用视图查询姓张的学生名和专业名

    CREATE VIEW v1
    AS
    SELECT stuname,majorname
    FROM stuinfo s
    INNER JOIN major m ON s.`majorid`= m.`id`;
    
    SELECT * FROM v1 WHERE stuname LIKE '张%';
    

    案例2:使用视图查看每个部门的平均工资级别

    #(1)创建视图查看每个部门的平均工资
    CREATE VIEW myv2
    AS
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id;
    
    #(2)使用
    SELECT myv2.`ag`,g.grade_level
    FROM myv2
    JOIN job_grades g
    ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
    

    3. 修改视图

    方式一语法:

    create or replace view  视图名
    as
    查询语句;
    

    方式二语法:

    alter view 视图名
    as 
    查询语句;
    

    案例:查询每个工种的平均工资

    CREATE OR REPLACE VIEW myv3
    AS
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    
    或者
    
    alter view myv3
    AS
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    

    4. 删除视图

    语法:

    drop view 视图名,视图名, ...;
    

    案例:

    DROP VIEW emp_v1,emp_v2,emp_v3;
    

    5. 查看视图

    DESC myv3;
    
    SHOW CREATE VIEW myv3;
    

    6. 更新视图

    #1.插入
    INSERT INTO myv1 VALUES('张飞','zf@qq.com');
    
    #2.修改
    UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
    
    #3.删除
    DELETE FROM myv1 WHERE last_name = '张无忌';
    

    具备以下特点的视图不允许更新

    (1)包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
    例如,如下所示的视图是不允许更改的:

    CREATE OR REPLACE VIEW myv1
    AS
    SELECT MAX(salary) m,department_id
    FROM employees
    GROUP BY department_id;
    
    #不允许更新
    UPDATE myv1 SET m=9000 WHERE department_id=10; #该操作报错,不允许更新
    

    (2)常量视图

    CREATE OR REPLACE VIEW myv2
    AS
    SELECT 'john' NAME;
    
    #不允许更新
    UPDATE myv2 SET NAME='lucy'; #该操作报错,不允许更新
    

    (3)select中包含的子查询

    CREATE OR REPLACE VIEW myv3
    AS
    SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
    FROM departments;
    
    #不允许更新
    UPDATE myv3 SET 最高工资=100000; #该操作报错,不允许更新
    

    (4)含join的连接查询

    CREATE OR REPLACE VIEW myv4
    AS
    SELECT last_name,department_name
    FROM employees e
    JOIN departments d
    ON e.department_id  = d.department_id;
    
    #允许更新,不允许插入
    UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen'; #视图和表的数据同时都改了
    INSERT INTO myv4 VALUES('陈真','xxxx');#插入失败
    

    (5)from一个不能更新的视图

    CREATE OR REPLACE VIEW myv5
    AS
    SELECT * FROM myv3;
    
    #不允许更新
    UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
    

    (6)where子句的子查询引用了from子句中的表

    CREATE OR REPLACE VIEW myv6
    AS
    SELECT last_name,email,salary
    FROM employees
    WHERE employee_id IN(
    	SELECT  manager_id
    	FROM employees
    	WHERE manager_id IS NOT NULL
    );
    
    #不允许更新
    UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
    
    展开全文
  • 数据库中的常见约束类型

    千次阅读 2020-06-17 22:45:20
    一般分为五大约束: 主键约束(Primary Key ...默认约束(Default constraint):某列的默认值,如在数据库里一项数据很多重复,可以设为默认值。 外键约束(Foreign Key constraint):用于在两个表之间建立关系,需

    一般分为五大约束:

    主键约束(Primary Key constraint):要求主键列数据唯一,不允许为空。

    唯一约束(Unique constraint):要求该列唯一,允许为空,但只能出现一个空值。

    检查约束(Check constraint):某列取值范围限制,格式限制等,如有关年龄的约束。

    默认约束(Default constraint):某列的默认值,如在数据库里有一项数据很多重复,可以设为默认值。

    外键约束(Foreign Key constraint):用于在两个表之间建立关系,需要指定引用主表的哪一列。

    主外键和索引的区别:

    展开全文
  • 文章目录常见约束01 基本概念1.1 含义:1.2 分类:六大约束1.3 添加约束的时机:1.4 约束的添加分类:1.5 主键和唯一的大对比:1.6 外键:02 具体讲解2.1 创建表时添加约束1.添加列级约束2.添加表级约束2.2 修改表时...

    常见约束

    01 基本概念

    1.1 含义:

    一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性

    1.2 分类:六大约束

    NOT NULL:非空,用于保证该字段的值不能为空

    比如姓名、学号等
    

    DEFAULT:默认,用于保证该字段有默认值

    比如性别
    

    PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空

    比如学号、员工编号等
    

    UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空

    	比如座位号
    

    CHECK:检查约束【mysql中不支持】

     	比如年龄、性别
    

    FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值

    ​ 在从表添加外键约束,用于引用主表中某列的值

    比如学生表的专业编号,员工表的部门编号,员工表的工种编号
    

    1.3 添加约束的时机:

    1.创建表时

    2.修改表时

    1.4 约束的添加分类:

    列级约束:六大约束语法上都支持,但外键约束没有效果

    表级约束:除了非空、默认,其他的都支持

    CREATE TABLE 表名(
        字段名 字段类型 列级约束,
        字段名 字段类型,
        表级约束
    ) 
    

    1.5 主键和唯一的大对比:

    保证唯一性是否允许为空一个表中可以有多少个是否允许组合
    主键×至多有1个√,但不推荐
    唯一可以有多个√,但不推荐

    1.6 外键:

    1、要求在从表设置外键关系

    2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求

    3、主表的关联列必须是一个key(一般是主键或唯一)

    4、插入数据时,先插入主表,再插入从表

    删除数据时,先删除从表,再删除主表

    02 具体讲解

    CREATE DATABASE students;

    2.1 创建表时添加约束

    1.添加列级约束

    语法:

    直接在字段名和类型后面追加 约束类型即可。

    只支持:默认、非空、主键、唯一

    USE students;
    DROP TABLE stuinfo;
    CREATE TABLE stuinfo(
        id INT PRIMARY KEY,#主键
        stuName VARCHAR(20) NOT NULL UNIQUE,#非空
        gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
        seat INT UNIQUE,#唯一
        age INT DEFAULT  18,#默认约束
        majorId INT REFERENCES major(id)#外键
    );
    CREATE TABLE major(
        id INT PRIMARY KEY,
        majorName VARCHAR(20)
    );
    

    #查看stuinfo中的所有索引,包括主键、外键、唯一

    SHOW INDEX FROM stuinfo;
    

    image.png

    2.添加表级约束

    语法:在各个字段的最下面

    【constraint 约束名】 约束类型(字段名)

    DROP TABLE IF EXISTS stuinfo;
    CREATE TABLE stuinfo(
        id INT,
        stuname VARCHAR(20),
        gender CHAR(1),
        seat INT,
        age INT,
        majorid INT,
        
        CONSTRAINT pk PRIMARY KEY(id),#主键
        CONSTRAINT uq UNIQUE(seat),#唯一键
        CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键 
    );
    SHOW INDEX FROM stuinfo;
    

    通用的写法:★

    CREATE TABLE IF NOT EXISTS stuinfo(
        id INT PRIMARY KEY,
        stuname VARCHAR(20),
        sex CHAR(1),
        age INT DEFAULT 18,
        seat INT UNIQUE,
        majorid INT,
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    );
    

    2.2 修改表时添加约束

    1.添加列级约束

    alter table 表名 modify column 字段名 字段类型 新约束;
    

    2.添加表级约束

    alter table 表名 addconstraint 约束名】 约束类型(字段名) 【外键的引用】;
    

    案例:

    DROP TABLE IF EXISTS stuinfo;
    CREATE TABLE stuinfo(
        id INT,
        stuname VARCHAR(20),
        gender CHAR(1),
        seat INT,
        age INT,
        majorid INT
    )
    DESC stuinfo;
    

    image.png

    3.案例实操

    #1.添加非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
    #2.添加默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    #3.添加主键
    #①列级约束
    ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
    #②表级约束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);
    #4.添加唯一
    #①列级约束
    ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
    #②表级约束
    ALTER TABLE stuinfo ADD UNIQUE(seat);
    #5.添加外键
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
    

    2.3 修改表时删除约束

    #1.删除非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
    #2.删除默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT ;
    #3.删除主键
    ALTER TABLE stuinfo DROP PRIMARY KEY;
    #4.删除唯一
    ALTER TABLE stuinfo DROP INDEX seat;
    #5.删除外键
    ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
    SHOW INDEX FROM stuinfo;
    

    03 案例实操

    1.向表emp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)

    ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;

    ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

    2. 向表dept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)

    3. 向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束,与之相关联的列是dept2表中的id列。

    ALTER TABLE emp2 ADD COLUMN dept_id INT;

    ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

    位置支持的约束类型是否可以起约束名
    列级约束:列的后面语法都支持,但外键没有效果不可以
    表级约束:所有列的下面默认和非空不支持,其他支持可以(主键没有效果)

    标识列

    01 基本概念

    又称为自增长列

    含义:可以不用手动的插入值,系统提供默认的序列值

    02 特点:

    1、标识列必须和主键搭配吗?不一定,但要求是一个key(主键、外键、唯一、自定义)

    2、一个表可以有几个标识列?至多一个!

    3、标识列的类型只能是数值型

    4、标识列可以通过 SET auto_increment_increment=3;设置步长

    可以通过 手动插入值,设置起始值

    03 案例演示

    3.1 创建表时设置标识列

    DROP TABLE IF EXISTS tab_identity;
    CREATE TABLE tab_identity(
        id INT  ,
        NAME FLOAT UNIQUE AUTO_INCREMENT,
        seat INT 
    );
    
    TRUNCATE TABLE tab_identity;
    INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
    INSERT INTO tab_identity(NAME) VALUES('lucy');
    SELECT * FROM tab_identity;
    

    3.2 查询自增长的值

    SHOW VARIABLES LIKE '%auto_increment%';
    

    image.png

    • 步长
    • 起始值
    • MySQL不支持修改起始值

    3.3 修改步长

    SET auto_increment_increment=3;
    

    image.png

    3.4 需要修改起始值

    就直接插入指定起始值,后面再插入NULL

    3.5 小结

    自增长列 特点:

    1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1

    auto_increment_increment

    如果要更改起始值:手动插入值

    如果要更改步长:更改系统变量

    set auto_increment_increment=值;

    2、一个表至多有一个自增长列

    3、自增长列只能支持数值型

    4、自增长列必须为一个key

    一、创建表时设置自增长列

    create table 表(

    ​ 字段名 字段类型 约束 auto_increment

    )

    二、修改表时设置自增长列

    alter table 表 modify column 字段名 字段类型 约束 auto_increment

    三、删除自增长列

    alter table 表 modify column 字段名 字段类型 约束

    【学习资料】

    【01学习笔记】

    【02课程资料】

    展开全文
  • SQL 语言通过定义一个关系所对应的基本表来完成关系模式的定义,其语句格式为: CREATE TABLE 表名( <列名1> <数据类型1> [<列级完整约束条件>], [<列名2> <数据.

    以前写的太乱了,翻出来重新整理下

    小系列目录:

    (一) MySQL入门,问题不大

    https://blog.csdn.net/weixin_44652781/article/details/106466485

    (一) 引入约束

    (1) 约束出现在哪里?

    想要讲解约束,就要知道约束用在哪里,用来干嘛?

    SQL 语言通过定义一个关系所对应的基本表来完成关系模式的定义,其语句格式为:

    CREATE TABLE 表名(
    	<列名1> <数据类型1> [<列级完整约束条件>],
    	[<列名2> <数据类型2> [<列级完整约束条件>],...],
    	[<表级完整约束条件>]
    );
    

    符号规定:下面展示一些定义的时候,为简便理解,使用中文配合符号表述(会有具体举例,不用担心理解不了)

    • <> 中的内容为实际的语义
    • [] 中的内容为任选项(不填写也可)
    • {} 中的内容必须显式的指定
    • | 为选项符
    • [,...n] 表示前面的项可以重复多次

    (2) 约束用来干嘛?

    约束,就是针对属性值的一些约束条件,只针对某一列,叫做列级约束针对多列属性的约束,叫做表级约束

    怎么理解呢?就例如某一列叫做 学号,我们就指定约束,这一行不允许为 NULL ,同时我们还能指定它为主键,这样通过学号就可以查找到一条唯一的学生记录了,还有例如外键知识等等…

    总结起来就一句话:约束用来对表中的数据进行限定,保证数据的正确性、有效性和完整性

    同样,有了约束知识的铺垫,我们就可以引申出后面的一些知识,例如多表操作等等,所以约束虽然简单,还是非常重要的哈~

    (二) 常见约束

    (1) 主键约束

    A:基本概念

    在关系模型中,主键的本质其实就是一个候选键

    理解非常简单,就是能通过这个主键,确定一个唯一的记录:例如学号是学生实体的候选键,一个学号就能确定这个学生到底哪个学生,而我们不选择姓名,这是因为,姓名在实际的情况中,不能作为一个唯一的标识,确认一个唯一的学生记录

    候选键:关系中能唯一标志一个元组的最小属性集

    B:特点

    确定为主键的列,不能为空,也不能重复!!!

    C:具体操作

    指定主键约束,使用的是 PRIMARY KEY 关键字

    一般来说,主键约束主要用在创建表时,指定约束的方式有两种:

    • ① 定义在列后
    CREATE TABLE students  (
      sid INT(8) PRIMARY KEY,
      sname VARCHAR(5),
      department VARCHAR(32),
      birthday date
    )
    
    • ② 独立定义
    CREATE TABLE students  (
      sid INT(8),
      sname VARCHAR(5),
      department VARCHAR(32),
      birthday date,
      PRIMARY KEY (sid) 
    )
    

    如果在表已经创建好的前提下,还可以通过下列两种方式进行主键的指定和删除

    • ① 删除主键
    ALTER TABLE students DROP PRIMARY KEY;
    
    • ② 指定主键
    ALTER TABLE students ADD PRIMARY KEY(sid);
    
    ALTER TABLE students MODIFY sid INT PRIMARY KEY;
    

    D:主键自增

    提到主键,就必须提到主键自增了,这个功能也是非常常用的,当设置主动自增后,例如你使用高级语言,操作数据库,向学生表插入一条记录后,即使不给出主键值,主键值也会自动生成出来,并且会在最大主键值的基础上 + 1,例如 0,1,2 … ,n

    最重要的一点,主键必须是整型,才能实现自增喔~

    如果主键例如 sid 为 varchar 类型,就会有这样的报错:Incorrect column specifier for column ‘sid’

    同样,主键自增一般用在创建表的时候,使用 AUTO_INCREMENT,直接跟在列名后即可

    CREATE TABLE students  (
      sid INT(8) PRIMARY KEY AUTO_INCREMENT,
      sname VARCHAR(5),
      department VARCHAR(32),
      birthday date
    )
    

    如果表已经创建好了,还可以进行是否自增的修改

    • ① 设置主键自增
    ALTER TABLE students CHANGE sid sid INT AUTO_INCREMENT;
    
    ALTER TABLE students MODIFY sid INT AUTO_INCREMENT;
    
    • ② 删除主键自增
    ALTER TABLE students CHANGE sid sid INT;
    
    ALTER TABLE students MODIFY sid INT;
    

    说明:上面设置以及删除都给出了 CHANGE 和 MODIFY 两种,有什么区别呢?

    其实细心的朋友也可以看出来, CHANGE 后要多一个列名 sid(可以修改) ,所以总结如下:

    • 只修改类型用 MODIFY
    • 既修改列名,也修改类型用 CHANGE

    (2) 非空约束

    非空约束很好理解,就是指定非空约束列的值不能为空,我们使用 NOT NULL 来实现这个功能

    CREATE TABLE students  (
      sid INT(8) PRIMARY KEY AUTO_INCREMENT,
      sname varchar(5) NOT NULL, -- sname 不为空
      department varchar(32),
      birthday date
    );
    

    很简单吧,我们已经将 sname 这个字段(列)在创建时添加了非空约束,如果 sname 在插入时为NULL ,则会报错 Column ‘sname’ cannot be null

    如果表已经创建好了怎么办呢?

    • 创建表完后,添加非空约束
    ALTER TABLE students MODIFY sname VARCHAR(5) NOT NULL;
    
    • 删除 sname 的非空约束
    ALTER TABLE students MODIFY sname VARCHAR(5);
    

    (3) 唯一约束

    唯一约束,就是指定这个字段(列)的值必须是唯一的,这种感觉就类似主键,例如我们下面要求创建表的时候,指定 sname 不能重名

    CREATE TABLE students  (
      sid INT(8) PRIMARY KEY AUTO_INCREMENT,
      sname VARCHAR(5) NOT NULL UNIQUE, -- sname唯一
      department VARCHAR(32),
      birthday date
    );
    

    如果添加两条重名的记录,就会报错

    INSERT INTO students VALUES (NULL,'张三','计算机系','2020-06-16');
    INSERT INTO students VALUES (NULL,'张三','工商管理系','2019-06-16');
    

    错误信息:Duplicate entry ‘张三’ for key ‘sname’

    同样,如果已经创建表后,又该怎么设置或者删除唯一约束呢?

    • 在创建表后,添加唯一约束
    ALTER TABLE students MODIFY sname VARCHAR(8) UNIQUE;
    
    • 删除唯一约束(本质上就是删除索引)
    ALTER TABLE students DROP INDEX sname;
    -- 这两种方法都是可以的
    drop index sname on students;
    

    (4) 外键约束

    A:概念理解

    外键的理论定义是比较复杂的,我在以前公众号写过的一篇数据库理论文章中有提及过,但是这一篇我们重点讲解 MySQL 的使用,所以,我们把理论都换成例子和通俗的大白话,先来看个问题:

    学生实体和课程实体分别用关系“学生”和“课程”来表示,它们之间的联系用关系“选课”来表示

    学生(学号,姓名,所在系,生日)
    课程(课程编号,课程名,授课老师)
    选课(学号,课程编号,成绩)

    问题:判断各关系的候选键、主键、外键

    答:

    • 学生中(students) 学号可以确认唯一的学生是候选键,可做主键,姓名需要在不重名的情况下也可以,但是实际情况不能保证没有重名不合适,课程中(course) 课程编号可以确认唯一的课程是候选键,可做主键,而选课中(sc_relation),需要由学号和课程编号共同才能确定唯一的值,所以两者共同构成候选键,并做主键
    • 选课关系中的 学号(sc_relation.sid)课程号(sc_relation.cid) ,分别代表选课关系的外键,他们分别对应 学生关系的学号(students.sid)课程关系的课程号(course.sid)(不一定要同名,但是为了好理解,一般写成同名)
    • 模拟了几张简单的表,给大家直观的理解
      • 说明:第一张为 学生表 students ,第二张为 课程表 course,第三张为 选课表 sc_relation

    看完这个例子,是不是从理解上感觉清晰了很多,那么接下来,我们就实际操作一下:

    C:基本格式

    CREATE TABLE 表名(
    		....
    		CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
    );
    
    -- 创建表之后,删除外键
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
    
    -- 创建表之后,添加外键
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
    

    B:具体操作

    我们下面,就按照这张图的规划来做

    • 创建学生表 students,学号 sid 为主键
    CREATE TABLE students (
        sid INT(8) PRIMARY KEY AUTO_INCREMENT,
    	sname VARCHAR(5) NOT NULL UNIQUE,
    	department VARCHAR(32),
    	birthday date
    );
    
    • 创建课程表 course,课程号 cid 为主键
    CREATE TABLE course (
    	cid INT(8) PRIMARY KEY AUTO_INCREMENT,
    	cname VARCHAR(5),
    	teacher VARCHAR(32)
    );
    
    • 创建选课关系表,sc_sid、sc_cid 分别为外键,指向学生表中的学号 sid 和 课程表中的课程号 cid
    CREATE TABLE sc_relation  (
    	sid INT(8),
        cid INT(8),
        cscore VARCHAR(5),
    	CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid),
    	CONSTRAINT sc_cid FOREIGN KEY (cid) REFERENCES course(cid)
    );
    

    随便提供一些数据,方便大家测试

    -- 插入学生数据
    INSERT INTO students VALUES (1001, '王五', '工商管理系', '2020-06-16');
    INSERT INTO students VALUES (1002, '汤姆', '音乐与舞蹈系', '2020-06-16');
    INSERT INTO students VALUES (1003, '杰克', '美术系', '2020-06-16');
    
    -- 插入课程数据
    INSERT INTO course VALUES (1, '大学英语', '老师1');
    INSERT INTO course VALUES (2, '大学物理', '老师2');
    INSERT INTO course VALUES (3, '数据库', '老师3');
    INSERT INTO course VALUES (4, '操作系统', '老师4');
    INSERT INTO course VALUES (5, '高等数学', '老师5');
    
    -- 插入选课数据
    INSERT INTO sc_relation VALUES (1001, 2, '88');
    INSERT INTO sc_relation VALUES (1001, 3, '92');
    INSERT INTO sc_relation VALUES (1001, 4, '78');
    INSERT INTO sc_relation VALUES (1001, 5, '83');
    INSERT INTO sc_relation VALUES (1002, 1, '77');
    INSERT INTO sc_relation VALUES (1002, 2, '90');
    INSERT INTO sc_relation VALUES (1002, 5, '89');
    INSERT INTO sc_relation VALUES (1003, 1, '86');
    INSERT INTO sc_relation VALUES (1003, 6, '88');
    INSERT INTO sc_relation VALUES (1003, 6, '82');
    

    有什么用呢?这个时候学生表以及课程表,就同选课表之间形成了关系,可视化软件编辑插入的时候,就会默认的给出一些可插入的选择,这是软件基于你设置的外键关系而自动寻找的

    创建表后又怎么操作呢?

    • 创建表之后,删除外键
    ALTER TABLE sc_relation DROP FOREIGN KEY sc_sid;
    
    • 创建表之后,添加外键
    ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid)
    

    C:级联操作

    如果在上述选课表中已经存储着 关于学号为 1001 学生的相关选课信息,如果这个时候,在学生表中修改或者删除这条记录,就会直接报错

    Cannot add or update a child row: a foreign key constraint fails (`mysql_grammar_test`.`sc_relation`, CONSTRAINT `sc_sid` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`))
    

    所以我们使用级联操作就可以达到同时更新或者删除多张表内的相关数据

    先给出基本格式:

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
    
    • A:级联更新:ON UPDATE CASCADE
    • B:级联删除:ON DELETE CASCADE

    例如测试一下

    ALTER TABLE sc_relation ADD CONSTRAINT sc_sid FOREIGN KEY (sid) REFERENCES students(sid) ON UPDATE CASCADE ON DELETE CASCADE;
    

    之前不能操作的内容,现在已经可以了,例如我们在学生表中将 1001学号修改为 1008 ,这样选课表中相关的内容就会自动根据修改变化了哈

    (三) 结尾

    如果文章中有什么不足,欢迎大家留言交流,感谢朋友们的支持!

    如果能帮到你的话,那就来关注我吧!如果您更喜欢微信文章的阅读方式,可以关注我的公众号

    在这里的我们素不相识,却都在为了自己的梦而努力 ❤

    一个坚持推送原创开发技术文章的公众号:理想二旬不止

    展开全文
  • 数据库中表的常见七大约束

    万次阅读 2018-05-14 20:15:55
    真正约束字段的是数据类型,但是数据类型约束很单一,需要一些额外的约束,更好的保证数据的合法性,从业 务逻辑角度保证数据的正确性。比如一个字段是email,要求是唯一的。 1、空属性not null:not null ...
  • 由于该系列文章阅读顺序性,所以请跳转至该系列文章第一篇从头开始阅读,并按照文章末尾指示按顺序阅读,否则会云里雾里,传送门在此:...1. 基本时钟周期约束 1.1 时钟周期约束的性质 1.2 Prim...
  • postgresql常见的基础数据类型 参考:https://www.runoob.com/postgresql/postgresql-data-type.html 数值型 integer(int):整形,4个字节 real:浮点型,4个字节 ,可以小数点;有点像float serial:序列...
  • 关系型数据库MySQL常见的几种约束

    千次阅读 2018-08-23 23:29:39
    关系型数据库MySQL常见的几种约束   转载:https://blog.csdn.net/jiekexu/article/details/80226943  对于已经创建好的表,虽然字段的数据类型决定了所能存储的数据类型,但是表中所存储的数据是否合法并没有...
  • 在具体设置 AUTO_INCREMENT 约束时,一个数据库表中只能一个字段使用该约束,该字段的数据类型必须是整数类型。由于设置了AUTO_INCREMENT 约束后的字段会生成唯一的 ID,所以该字段也经常会设置成 PK 主键。 基本...
  • 时序约束可以很复杂,这里我们先介绍基本的时序路径约束,复杂的时序约束我们将在后面进行介绍。 在本节的主要内容如下所示:  ·时序路径和关键路径的介绍  ·建立时间、保持时间简述  ·时钟的约束(寄存器...
  • 时序约束可以很复杂,这里我们先介绍基本的时序路径约束,复杂的时序约束我们将在后面进行介绍。 在本节的主要内容如下所示:  ·时序路径和关键路径的介绍  ·建立时间、保持时间简述  ·时钟的约束(寄存器...
  • 1.约束 表的约束 往表中插入数据的时候,可能出现一些问题,比如:重复插入数据,内容不对(性别) -------如何保证数据库表中数据的完整性和一致性呢?...-----常见约束有: 主键约束(prima...
  • 数字IC之路-SDC篇(一):基本的时序路径约束

    万次阅读 多人点赞 2019-05-07 17:57:20
    时序约束可以很复杂,这里我们先介绍基本的时序路径约束,复杂的时序约束我们将在后面进行介绍。 在本节的主要内容如下所示: ·时序路径和关键路径的介绍  ·建立时间、保持时间简述  ·时钟的约束(寄存器-...
  • ** 小白终是踏上了这条不归路—-小文的mysql学习笔记(1) 小白终是踏上了这条不归路—-小文的mysql学习笔记(2)—-条件查询 ...直接在字段名和类型后面追加约束类型即可。 只支持: 默认(DEFAULT)、非空(NO
  • 数据库索引常见四种类型

    万次阅读 2019-03-19 12:53:34
     索引唯一扫描,当可以优化器发现某个查询条件可以利用到主键、唯一键、具有外键约束的列,或者只是访问其中某行索引所在的数据的时候,优化器会选择这种扫描类型。   第二种: index range scan  索引...
  • 欢迎来到unity学习、unity培训、unity企业培训教育专区,这里很多U3D资源、U3D培训视频、U3D教程、U3D常见问题、U3D项目源码,我们致力于打造业内unity3d训、学习第一品牌。一、列的基本操作。 1、增加一列。  ...
  • TypeScript基本类型总结

    万次阅读 多人点赞 2019-07-11 16:34:16
    基本类型 布尔值 let isDone: boolean = false; 数字 和JavaScript一样,TypeScript里的所有数字都是浮点数。 这些浮点数的类型是number。 除了支持十进制和十六进制字面量,TypeScript还支持ECMAScript 2015中...
  • 数据库主键 4种类型及6种约束

    万次阅读 2018-03-27 13:29:43
    导读:在看《SQL必知必会(第四版)》看到运用外键建立表关系,联想到工作时建表主键类型的选择问题,查资料的时候遇到了比较简洁的一个文章; 1. 基础理论: 1.主键和外键的设计原则。 a. 主键应尽量分离于业务的...
  • mysql中的约束

    2019-08-27 16:08:39
    常见的表的约束: 上表中列举的约束条件都是针对表中字段进行限制, 从而保证数据表中数据的正确性和唯一性。 一、主键约束 在MySQL中,为了快速查找表中的某条信息,可以通过设置主键来实现。主键约束是...
  • 为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;这些规则常称为表的约束。常见约束如下:
  • MySQL数据库表约束

    千次阅读 多人点赞 2018-01-16 14:48:27
    常见的表的约束:    上表中列举的约束条件都是针对表中字段进行限制, 从而保证数据表中数据的正确性和唯一性。 一、主键约束   在MySQL中,为了快速查找表中的某条信息,可以通过设置主键来实现...
  • Mysql 各种约束

    千次阅读 2018-08-01 21:05:21
    使用SQL脚本创建数据库,操作主键、外键与各种约束(MS SQL Server) 在实际开发中,可能很少人会手写sql脚本来操作数据库的种种。特别是微软的MS SQL Server数据库,它的SQL Server Management Studio对数据库的图形...
  • 常见软件测试类型分类

    万次阅读 2018-09-20 09:48:05
    软件测试类型 1)回归测试 回归测试: (regression testing): 回归测试两类:用例回归和错误回归;用例回归是过一段时间以后再回头对以前使用过的用例在重新进行测试,看看会重新发现问题。错误回归,就是在新...
  • ** 小白终是踏上了这条不归路----小文的mysql学习笔记(1) ...小白终是踏上了这条不归路----小文的mysql学习笔记(4)----常见基本函数 小白终是踏上了这条不归路----小文的mysql学习笔记(5)----分组查询 … ...
  • 简述Oracle基本数据类型

    千次阅读 2017-06-13 14:47:08
    Oracle基本数据类型分类 总共6大类型,具体如下: 字符串类型 数字类型 日期类型 LOB类型 LONG RAW & RAW 类型 ROWID & UROWID 类型 每个类型下详细的数据类型 字符串类型 列表内容
  • XDC 约束技巧

    千次阅读 2017-12-28 22:29:34
    IO 时序约束
  • vivado xdc时钟约束

    千次阅读 2019-08-12 12:53:57
    XDC是Xilinx Design Constraints的简写,但其基础语法来源于业界统一的约束...XDC的基本语法可以分为时钟约束、IO约束以及时序例外约束,对一个设计进行约束的先后顺序也可以按照这三类约束依次进行. 时钟约束 时钟...
  • WebRTC本地媒体——约束详解

    千次阅读 2019-09-13 14:37:28
    1.1Constrainable模式的基本概念 1.2 帮助理解约束的几个示例 1.3Capabilities 1.4 Settings 1.5Constrains 和 ConstraintSet 1.6Constraints Pattern接口定义 2 算法 2.1 定义 2.2SelectStting...
  • 五种约束

    2015-12-29 21:42:01
    1. 非空约束 not nul 2. 唯一约束 unique  每一个列上的数据是不允许重复的数据 3. 主键约束 (Primary Key)一般作为数据的唯一的一个标记出现 100%  主键约束(不为空,且唯一) = 非空约束 + 唯一约束  复合...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 74,552
精华内容 29,820
关键字:

常见的约束的基本类型有哪些