外键

mysql数据对象,mysql对象

  读书目标:


 

  • 刺探精通多如牛毛的三种数据库对象
  • 学会怎么创造具体的多寡对象

  mysql 成千上万的数量对象有何:

  • DataBase/Schema
  • Table
  • Index
  • View/Trigger/Function/Procedure

  多Database用途:

  • 作业的隔断
  • 财富的割裂

  表上的常用数据对象:

  • 索引
  • 约束
  • 视图,触发器,函数,存款和储蓄进度

    索引


  什么是数据库索引:            索引正是数据库中数量的目录:          
 索引和数据时七个目的            索引首假设用来加强数据库的查询功用    
       数据库中多少变动相像须求一同索引数据的改观,           
     因为索引是根据B+TREE,排好地方的,一旦数据变动,则那几个数量的对应地点也要扭转,
               
那样之后再找找,本领便捷索引到,而退换地点就是索引的掩护;  
查看某些数据库命令:  

mysql> help create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ..

UNIQUE:唯一索引(客商表的ID,手提式有线电话机号等,独一音信)
SPATIAL:地理地方索引(搜索左近有怎样人)  
三种创制索引及查看表中索引的措施

mysql> select * from vc;
+------+------+
| v    | c    |
+------+------+
| AB   | AB   |
+------+------+
1 row in set (0.00 sec)

mysql> create index idx_v on vc(v);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table vc add KEY idx_c (c);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from vc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| vc    |          1 | idx_v    |            1 | v           | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| vc    |          1 | idx_c    |            1 | c           | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show create table vc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vc    | CREATE TABLE `vc` (
  `v` varchar(5) DEFAULT NULL,
  `c` char(5) DEFAULT NULL,
  KEY `idx_v` (`v`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在mysql中 INDEX 和KEY 都意味着了目录;     约束


  约束: 独一约束: unique 外键限定: CONSTRAINT  
创制独一约束(独一索引能够导致独一约束):

  • 独一约束是一种特有的目录
  • 独一约束能够是四个恐怕八个字段
  • 放在限定能够在建表的时候建好,也得今后边补上
  • 主键也是一种独一限制

图片 1
图片 2

 

  索引有啥样:

  • 主键索引  ID
  • 单键索引  orderid
  • 单键索引  bookid
  • 组合索引 (userid + orderid)

                     独一节制有哪些:

  • 主键限制           ID
  • 单键唯一索引     orderid
  • 构成唯一索引     userid+orderid

  增多独一约束 加多主键索引: 

mysql> alter table order add primary key (id)
#实际上是给主键id增加了一个索引,而这个索引又是唯一的所以就这个索引就变成了唯一约束

加多唯一索引:

mysql>alter table order add unique key idx_uk_orderid(id)

  外键节制 将两张表的多寡经过某种条件关联起来
例子:买东西,订单表,客商新闻表;
如果未有外键约束:恐怕存在荒诞不经顾客也能下单成功;
而一旦应用外键约束,则在数据库层面是的不设有的客户不可能下单成功。  
创造外键约束: 将顾客表和订单表经过外键关联起来:

mysql > alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid)

创办外键节制的时候,能够钦定在剔除,更新父表(被参照的表)时,对子表(做外键关联的表)
举办的呼应操作,满含可:restrict(节制),cascade(串联),set null 和
no action。 个中restrict和 no action
雷同,是指约束在子表有涉嫌记录的情况下,父表不能够更新;
cascade表示父表在更新或删除时,更新大概去除子表对应的笔录; set null
表示父表在更新只怕去除时子表的呼应字段被set null。
接受后三种格局的时候要深思熟虑,大概会因为错误的操作产生数据的丢失。    
在实施LOAD DATA和 ALTE纳瓦拉 TABLE
操作的时候,为了加快速度,能够不常关张外键约束: set foreign key_checks
=0;等成功之后再张开:set foreign key_checks =1  

mysql > alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid) on delete restrict on update cascade;

  on update cascade例子: 

mysql> select * from c_A;
+----+------+
| id | age  |
+----+------+
|  1 |   22 |
|  2 |    3 |
|  3 |    4 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from c_B;
+----+------+
| id | age  |
+----+------+
|  1 |    2 |
|  2 |   33 |
|  3 |    4 |
+----+------+
3 rows in set (0.00 sec)

mysql> update c_B set id=11 where age = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from c_B;
+----+------+
| id | age  |
+----+------+
|  2 |   33 |
|  3 |    4 |
| 11 |    2 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from c_A;
+----+------+
| id | age  |
+----+------+
|  2 |    3 |
|  3 |    4 |
| 11 |   22 |
+----+------+
3 rows in set (0.00 sec)

细心:外键约束更正的只是约束的不胜字段;   on delete cascade  

on delete cascade insert update delete
parent yes 只能更改子表中约束字段没有的值 yes
child 只能插入父表中约束字段有的值; 只能更改父表中约束字段没有的值  yes

        on update cascade  

on update cascade insert update delete
parent yes yes 只能删子表中约束字段没有的值;
child 只能插入父表中约束字段有的值; 只能更新父表中约束字段没有的值  yes

        可以见child只可以删除,别的都受限余父表的羁绊字段;
而parent都能插入,但里边一条受限于子表约束字段;    
给order表扩张了多个限制,(CONSTRAINT,节制的显要词)节制名
constarint_uid;
他是贰个外键节制且order表中userid是外键,把order表中的userid 关联 到
 user表的userid;   使用外键的注意事项:

  • 必需是innodb表,别的内燃机不接济外键
  • 互相自律的字段类型一定要一律
  • 主表的束缚字段必要有目录(上边的例证中,user表正是主表,所以在user表中要userid必要加上索引
  • 封锁名称必定要唯一,即便不在一张表上(constarint_uid
    在全体库中是独占鳌头的)

  删除二个束缚:

mysql> alter table order drop FOREIGN KEY constarint_uid;

 

视图view


  view作用:

  • 视图将一组查询语句构成的结果集,是一种设想布局,并非实际上多少
  • 视图能简化数据库的拜谒,能够将多个查询语句构造形成二个假造布局
  • 视图能够掩盖数据库后端表构造,提升数据库的安全性
  • 视图也是一种权限管理,只对客户提供一些数据

  创设一个已成功订单的视图:

mysql > create view order_view as select * from order where status = 1

  视图例子:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

 

触发器


  触发器 是怎么着: 触发器是加在表上的独特程序,当表上冒出一定的风浪(insert/update/delete/alter table)时触发该程序实施。  
对二个表的一个事件mysql中只好定义二个触发器   做什么:

  • 数量校正;
  • 迁移表;
  • 兑现特定的职业逻辑;

  触发器有八个顾客:

  • 执行者
  • 调用者  

  触发器–基本语法

CREATE
[DEFINER = {user | CURRENT_USER}]     -- 定义执行着的权限
TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW                                    --涉及的每一行都会执行trigger_body
trigger_body  t

trigger_time:{BEFORE | AFTER}
trigger_event:{INSERT | UPDATE | DELETE}

  delimiter
甘休符的设定,默许是分号。不过在触发器应该为要实践一组SQL,会冒出分号所以讲得了符
设定一下;
图片 3.png)  
图片 4

 

用 NEW,OLD来引用触发器中发生变化的笔录内容   触发触发器的依次: before
触发器,行操作,after
触发器;个中任何二个手续操作产生错误都不会继续试行剩下的操作。
假设是对事务表实行的操作,那么会整整作为三个政工被回滚(rollback),不过一旦是对非事务表实行的操作
,那么早已更新的笔录将不或者回滚,那也是陈设触发器的时候须求小心得难点。  
  触发器的风味:

  • 触发器对品质有消耗,应慎用
  • 平等类事件在贰个表中只好创设三遍
  • 对这一件事务表,触发器施行停业则全体讲话回滚
  • row格式主从复制,触发器不会在从属库上进行
  • 接收触发器时应有幸免递归实践

  能够扶植应用在数量库端确定保证数据的完整性。       仓库储存进程


 
定义:存款和储蓄进程是积累在数据库端的一组SQL语句集,顾客能够因而存款和储蓄进度名和传参数次调用的次序模块。
  特点:

  • 运用灵活,能够运用流动调查节语句,自定义变量等成功复杂的专门的工作逻辑
  • 增长数据安全性,屏蔽应用程序直接对表的操作,易于实行审计
  • 减去互联网传输
  • 加强代码维护的复杂度,实际行使中要评估场景是或不是契合

  存储进度–基本语法

CREATE 
     [ DEFINER = { user | CURRENT_USER } ]                 --定义执行着的权限
     PROCEDURE  sp_name ( [ proc_parameter[ ,... ] ] )
     [ characteristic .. ]  routine_body

proc_parameter:
     [ IN | OUT | INOUT]  param_name  type
                                   type:
                                            Any valid MySQL data type

 

IN:输入参数,表示该参数值在调用存储进度中曾经钦点好了,在调用存款和储蓄进度中更正该参数不会重临OUT:输出参数,能够在仓库储存进度之中被改成,能够回来 INOUT:输入输出参数;  
characteristic:      COMMENT ‘string’                    –注释      |
[NOT] DETERMINISTIC          –是或不是会再次来到明确值   routine_body:    
 Valid SQL routine statement    –与trigger类似  
图片 5.png)
图片 6

 

    重点:

  • 高级中学级变量定义:DECLARE
  • 流动调查控语句
  • 参数传入

  查询:数据库中有何样存款和储蓄进程

mysql> show PROCEDURE STATUS ;

查阅:某个触发器的详细情况

mysql> SHOW TRIGGER STATUS ;

 

仓库储存进程的接纳: 1.安装参数值:

mysql> set @total = 5;
mysql> set @res = 0;

2.调用存款和储蓄进程:

mysql> call proc_test1(@total,@res);

3.查看再次来到值:

mysql> select @res

  删除存款和储蓄进度依然函数:

DROP {PROCEDURE | FUNCTION}  [IF  EXISTS]  sp_name

  查看存款和储蓄进度或许函数 SHOW {procedure | function}  status like
‘file_in_stock’   查看存款和储蓄进程只怕函数的定义 show  create {procedure |
 function}  sp_name   存款和储蓄过程-流动调查节语言
图片 7.png)
 图片 8

 

  自定义函数

  • 自定义函数与存款和储蓄进程看似,不过必得满含重临值
  • 自定义函数与sum(卡塔尔国,max(卡塔尔,等mysql原生函数使用办法相仿: select
    func(val卡塔尔(قطر‎; select * from tbl where col = func(val)
  • 是因为自定义函数可能在遍历数据中使用,要专心质量损耗

  自定义函数-基本语法
图片 9.png)
 图片 10
最后通过RETUCR-VN;
 图片 11   调用

mysql> select func_test1(4);

  确认权限: 创设存款和储蓄过程或者函数须要:create  routine
改进也许去除存款和储蓄进程需求:alter  routine 施行存储进程 须要 :          
execute    能够嵌套使用;   小结


 

  • 触发器和仓库储存进度不便于水平扩展,多用来总计和平运动维操作中;
  • 还会有代码管理维护资产相比高;
  • 但是:
    • 简化使用开采,减量传输,进步管理功用;

   总结


  •  索引的创制于查看
  •  约束:
    •   独一节制,
    • 外键节制:
      • alter table order add CONSTRAINT constarint_uid FOREIGN KEY
        (userid) REFERENCES user(userid);
      • 外键限制七种情势:restrict,no action,set null,cascade;
  •  VIEW
  •  TIGGER:
    • 一个表的二个风云只可以定义二个触发器
    • delimiter //
    • NEW.age  OLD.age
  •  PROCEDURE:
    • DECLARE,
    • 参数字传送入再次回到
    • 流控制语言
    • 采用三手续
      • 开端化参数
      • 调用存款和储蓄进度
      • 查阅放回值
  •  function:
    • 必须包涵重返值
    • 使用select func_test1(14);
  •  SUBSTRING( goods_name,1,5 ):从位置1截取goods_name5个字符
  •  A rigth join B on..:B显示B中null字段;

 

学习目的:
领悟通晓习认为常的两种数据库对象 学会怎么创制具体的数量对象 mysql
见惯不惊的数量对象有怎么样: DataBase/Sc…

外键的用场是作保数据的完整性。它通常包罗以下三种:

1 实体完整性,确认保障每种实体是独步一时的

2 域完整性,确认保证属性值只从一套特定可选的集纳里选择

3 关联完整性,确定保证每种外键或是NULL或包蕴与相关主键值相配的值

1.如何是外键限定

与主键节制分歧,创建外键约束不会自动创造对应的目录。
不过由于以下原因,对外键手动创制索引平常是立竿见影的:

当在询问中结成相关表中的多寡时,常常在接入条件中运用外键列,方法是将三个表的外键约束中的一列或多列与另一个表中的主键列或独一键列匹配。
索引使 数据库引擎 能够在外键表中火速搜索有关数据。
然则,创制此索引而不是须求的。
就算未有对多少个相关表定义主键或外键约束,也足以对来源这三个表中的多少开展重新组合,但多个表间的外键关系表达已用其键作为规范对其进行了优化,以便重新组合到查询中。
对主键限定的转移可由相关表中的外键限制检查。

外键节制(foreign
key卡塔尔(قطر‎正是表与表之间的某种约定的关联,由于这种关联的存在,大家能够让表与表之间的数额,越来越全体,关连性更加强。

关于数据表的完整性和关连性,能够举例

有二张表,一张是客商表,一张是订单表:

1.若是小编删除了客户表里的客户,那么订单表里面跟这么些顾客有关的数额,就成了无头数据了,不完全了。2.一旦本人在订单表里面,随意插入了一条数据,这么些订单在顾客表里面,未有与之相应的顾客。那样数据也不完全了。

若是有外键的话,就有援助多了,能够不让客商删除数据,只怕去除客户来讲,通过外键相符删除订单表里面包车型客车数量,那样也能让多少完整。

因别的键约束,每一回插入或更新数据表时,都会检查数据的完整性。

2.开立外键限制

2.1 方法一:通过create table创设外键

语法:

create table 数据表名称(...,[CONSTRAINT [约束名称]] FOREIGN KEY [外键字段] REFERENCES [外键表名](外键字段,外键字段2…..) [ON DELETE CASCADE ] [ON UPDATE CASCADE ])

参数的表达:

RESTLX570ICT: 屏相对父表的去除或更新操作。CASCADE:
从父表删除或更新且自动删除或更新子表中格外的行。ON DELETE CASCADE和ON
UPDATE CASCADE都可用

注意:on update cascade是级联更新的野趣,on delete
cascade是级联删除的意趣,意思正是说当您更新或删除主键表,这外键表也会跟随一同更新或删除。

精短化后的语法:

foreign key 当前表的字段 references 外部表名 (关联的字段) type=innodb 

2.1.1 插入测量检验数据

事例:大家成立三个数据库,饱含客商音讯表和订单表

MariaDB [book] create database market; # 创建market数据库Query OK, 1 row affected (0.00 sec)MariaDB [book] use market; # 使用market数据库Database changedMariaDB [market] create table userprofile(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb; # 创建userprofile数据表,指定使用innodb引擎Query OK, 0 rows affected (0.07 sec)MariaDB [market] create table user_order(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references userprofile(id) on delete cascade on update cascade); # 创建user_order数据表,同时为user_order表的u_id字段做外键约束,绑定userprofile表的id字段Query OK, 0 rows affected (0.04 sec)MariaDB [market] insert into userprofile(name,sex)values('HA',1),('LB',2),('HPC',1); # 向userprofile数据表插入三条记录Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [market] select * from userprofile; # 查询userprofile数据表的所有记录+----+------+-----+| id | name | sex |+----+------+-----+| 1 | HA | 1 || 2 | LB | 2 || 3 | HPC | 1 |+----+------+-----+3 rows in set (0.00 sec)MariaDB [market] insert into user_order(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256); # 向user_order数据表插入三条记录Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [market] select * from user_order; # 查询user_order数据表的所有记录+------+------+----------+-------+| o_id | u_id | username | money |+------+------+----------+-------+| 1 | 1 | HA | 234 || 2 | 2 | LB | 146 || 3 | 3 | HPC | 256 |+------+------+----------+-------+3 rows in set (0.00 sec)MariaDB [market] select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 联表查询+----+------+-----+-------+------+| id | name | sex | money | o_id |+----+------+-----+-------+------+| 1 | HA | 1 | 234 | 1 || 2 | LB | 2 | 146 | 2 || 3 | HPC | 1 | 256 | 3 |+----+------+-----+-------+------+3 rows in set (0.03 sec)

2.1.2 测量试验级联删除

MariaDB [market] delete from userprofile where id=1; # 删除user表中id为1的数据Query OK, 1 row affected (0.01 sec)MariaDB [market] select id,name,sex,money,o_id from userprofile,user_order where id=u_id;+----+------+-----+-------+------+| id | name | sex | money | o_id |+----+------+-----+-------+------+| 2 | LB | 2 | 146 | 2 || 3 | HPC | 1 | 256 | 3 |+----+------+-----+-------+------+2 rows in set (0.00 sec)MariaDB [market] select * from user_order; # 查看order表的数据+------+------+----------+-------+| o_id | u_id | username | money |+------+------+----------+-------+| 2 | 2 | LB | 146 || 3 | 3 | HPC | 256 |+------+------+----------+-------+3 rows in set (0.00 sec)

2.1.3 测量检验级联更新