你的位置:首页 > 数据库

[数据库]mysql数据对象


 
学习目标:

 
  • 了解掌握常见的几种数据库对象
  • 学会如何创建具体的数据对象

 
mysql 常见的数据对象有哪些:
  • DataBase/Schema
  • Table
  • Index
  • View/Trigger/Function/Procedure
 
多Database用途:
  • 业务的隔离
  • 资源的隔离

 
表上的常用数据对象:
  • 索引
  • 约束
  • 视图,触发器,函数,存储过程

 
 
索引

 
什么是数据库索引:
           索引就是数据库中数据的目录:
           索引和数据时两个对象
           索引主要是用来提高数据库的查询效率
           数据库中数据变更同样需要同步索引数据的变更,
                因为索引是按照B+TREE,排好位置的,一旦数据变化,则这个数据的相应位置也要变化,
                这样之后再查找,才能快速索引到,而变化位置就是索引的维护;
 
查看某个数据库命令:
 
mysql> help create indexName: '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
 
创建唯一约束(唯一索引可以促成唯一约束):
  • 唯一约束是一种特殊的索引
  • 唯一约束可以是一个或者多个字段
  • 位于约束可以在建表的时候建好,也可以后面补上
  • 主键也是一种唯一约束

 


 
索引有哪些:
  • 主键索引  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和 ALTER 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 cascadeinsertupdatedelete
parentyes只能更改子表中约束字段没有的值yes
child只能插入父表中约束字段有的值;只能更改父表中约束字段没有的值 yes
 
 
 
 
on update cascade
 
on update cascadeinsertupdatedelete
parentyesyes只能删子表中约束字段没有的值;
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_timetrigger_event ON tbl_nameFOR EACH ROW                  --涉及的每一行都会执行trigger_bodytrigger_body t trigger_time:{BEFORE | AFTER}trigger_event:{INSERT | UPDATE | DELETE}


 
delimiter
结束符的设定,默认是分号。但是在触发器应为要执行一组SQL,会出现分号所以讲结束符 设定一下;

 

 


用 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类似
 

 

 
 
重点:
  • 中间变量定义: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
 
存储过程-流控制语言

 

 


 
自定义函数
  • 自定义函数与存储过程类似,但是必须带有返回值
  • 自定义函数与sum(),max(),等mysql原生函数使用方法类似: select func(val); select * from tbl where col = func(val)
  • 由于自定义函数可能在遍历数据中使用,要注意性能损耗
 
自定义函数-基本语法

 
最终通过RETURN;
 
 
调用
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字段;