• SQL学习二十、SQL高级特性


    约束(constraint)

    管理如何插入或处理数据库数据的规则。

    DBMS 通过在数据库表上施加约束来实施引用完整性。
    大多数约束是在 表定义中定义的,用 CREATE TABLE 或 ALTER TABLE 语句。


    1、主键 (PRIMARY KEY)

    主键是一种特殊的约束,用来保证一列(或 一组列)中的值是唯一的,而且永不改动。主键可以唯一标识表中的一行。且主键值不能重用。如果从表中删除某一行,其主键值不分配给新行。
    这方便了直接或交互地处理表中的行。没有主键,要安全地 UPDATE 或 DELETE 特定行而不影响其他行会 非常困难。

    • 创建表的时候指定主键
    CREATE TABLE `supplier_new` (
    	`id`	INTEGER NOT NULL,
    	`supplier`	TEXT,
    	`supplierAddress`	TEXT,
    	`supplierTel`	TEXT NOT NULL,
    	`supplierEmail`	TEXT,
    	`supplierContact`	TEXT,
    	PRIMARY KEY(`id`)
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 指定自增主键
    CREATE TABLE `adjunctlist` (
    	`id`	integer PRIMARY KEY AUTOINCREMENT,
    	`add_time`	text,
    	`ext0`	text,
    	`ext1`	text,
    	`file_name`	text,
    	`file_no`	integer,
    	`file_path`	text,
    	`file_uri`	text,
    	`flaw_id`	integer,
    	`is_upload`	integer,
    	`local_flaw_id`	integer,
    	`user_id`	text,
    	`task_id`	integer,
    	`task_point_id`	integer,
    	`task_type`	integer
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 某些DBMS运行在ALTER TABLE语句中指定主键
    ALTER TABLE 表名  ADD CONSTRAINT PRIMARY KEY (列名);
    
    • 1

    比如这张表在创建的时候没有指定主键,我们可以通过ALTER TABLE语句为其指定主键

    alter table test
    add constraint primary key (test.no)
    
    • 1
    • 2

    **注意:**SQLite不允许使用ALTER TABLE定义键,要求在初始的CREATE TABLE 语句中定义它们。


    2、外键

    外键是表中的一列,其值必须是另一表的唯一约束列,一般用另外一张表的主键。

    当我们指定外键后,DBMS 不允许删除在另一个表中具有关联行的行,因此,可以利用外键防止意外删除。

    比如,我们我们的订单表( oderlist_new )和供应商表( supplier_new )

    • 创建时指定
      同样的我们在创建表的时候可以指定表的外键,使用REFERENCES 表名(列名)
      例如,我们在创建订单表(oderlist_new)时指定订单表(oderlist_new)的supplierId作为外键关联供应商表(supplier_new)的主键id
    CREATE TABLE `oderlist_new` (
    	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    	`goodsName`	TEXT,
    	`quantity`	integer,
    	`item_price`	real,
    	`orderNo`	text NOT NULL,
    	`userId`	INTEGER,
    	`userName`	TEXT,
    	`orderTime`	TEXT,
    	`supplierId`	INTEGER NOT NULL REFERENCES supplier_new (id)
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 利用ALTER TABLE指定
      利用ALTER TABLE语句时需要这样写
    ALTER TABLE 表1
    ADD CONSTRAINT FOREIGN KEY (表1.列) REFERENCES 表2(表2.列) 
    
    • 1
    • 2
    • 防止意外删除
      比如我们删除一条供应商信息,这条供应商信息在订单表中有作为外键使用,删除的时候就会失败。
    delete from supplier_new
    where id = 1
    
    • 1
    • 2


    3、唯一约束

    唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主 键,但存在以下重要区别。
    1、表可包含多个唯一约束,但每个表只允许一个主键。
    2、唯一约束列可包含 NULL 值。
    3、唯一约束列可修改或更新。
    4、唯一约束列的值可重复使用。
    5、与主键不一样,唯一约束不能用来定义外键。

    例如我们的订单表,id是主键(唯一约束),订单编号orderNo也是唯一约束

    唯一约束既可以用 UNIQUE 关 键字在表定义中定义,也可以用单独的 CONSTRAINT 定义。

    创建订单表的时候,指定orderNo具有唯一约束orderNo text NOT NULL UNIQUE

    CREATE TABLE `oderlist_new` (
    	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    	`goodsName`	TEXT,
    	`quantity`	integer,
    	`item_price`	real,
    	`orderNo`	text NOT NULL UNIQUE,
    	`userId`	INTEGER,
    	`userName`	TEXT,
    	`orderTime`	TEXT,
    	`supplierId`	INTEGER NOT NULL,
    	FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    4、检查约束

    检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。

    • 常见用途

    1、检查最小或最大值。例如,防止 0个物品的订单(即使0是合法的数)。
    2、指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天 起一年后的日期。
    3、只允许特定的值。例如,在性别字段中只允许 M 或 F。

    例如,我们限定订单表中商品单价和商品数量必须大于0,CHECK(quantity > 0)CHECK(item_price > 0)

    CREATE TABLE `oderlist_new` (
    	`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    	`goodsName`	TEXT,
    	`quantity`	integer CHECK(quantity > 0),
    	`item_price`	real CHECK(item_price > 0),
    	`orderNo`	text NOT NULL UNIQUE,
    	`userId`	INTEGER,
    	`userName`	INTEGER,
    	`orderTime`	TEXT,
    	`supplierId`	INTEGER NOT NULL,
    	FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
    );
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    这样我们在插入不符合要求的数据时就会失败,比如我们插入如下数据

    "菠萝"	
    "0"	
    "0.0"	
    "20181023008"	
    "30"	
    "王舍"	
    "2018-10-23 23:12:49"	
    3
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 插入不符合约束的数据
    insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
    VALUES ('菠萝',0,0.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)
    
    • 1
    • 2

    执行结果

    • 插入符合约束的数据,我们把单价修改为20.0,数量修改为10
    insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
    VALUES ('菠萝',10,20.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)
    
    • 1
    • 2

    执行结果

    插入成功


    索引

    索引用来排序数据以加快搜索和排序操作的速度。

    注意
    1、索引改善检索操作的性能,但降低了数据插入、修改和删除的性能。 在执行这些操作时,DBMS必须动态地更新索引。
    2、索引数据可能要占用大量的存储空间。

    所以,在这些情况下不应该使用索引

    • 索引不应该使用在较小的表上。

    • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

    • 索引不应该使用在含有大量的 NULL 值的列上。

    • 索引不应该使用在频繁操作的列上。

    • 创建索引

    索引用CREATE INDEX语句创建(不同DBMS创建索引的语句变化很大)。

    CREATE INDEX 索引名
    ON 
    表名 (列名); 
    
    • 1
    • 2
    • 3

    例如,我们用订单编号作为订单表的索引

    CREATE INDEX oderlist_new_ind
    ON 
    oderlist_new (orderNo); 
    
    • 1
    • 2
    • 3

    执行结果

    创建成功


    • 查询数据库中所有索引
    SELECT * 
    FROM sqlite_master 
    WHERE type = 'index';
    
    • 1
    • 2
    • 3

    查询结果

    • 查询对应表下的索引 使用 AND 子句连接表名
    SELECT * 
    FROM sqlite_master 
    WHERE type = 'index' and tbl_name = 'oderlist_new';
    
    • 1
    • 2
    • 3

    • 删除索引 DROP INDEX 索引名
    DROP INDEX 'oderlist_new_ind';
    
    • 1

    执行成功

  • 相关阅读:
    批量迁移redis实例的key
    帝国cms根据标题生成标题图片插件
    【LeetCode热题100】【图论】腐烂的橘子
    Maven高级
    线段树以及使用
    数字签名与数字证书
    2023考研常识之学硕和专硕哪个更好,有哪些不同?
    VR模拟仿真实验课件可视化编辑,提高学员实操水平
    Jmeter-实现图片的上传和下载
    Docker(13)-- Docker 网络
  • 原文地址:https://blog.csdn.net/m0_37168878/article/details/127612402