数据库高级对象

1.函数

函数可以传参,也可以有返回值。但是函数只能有一个返回值
函数可以包含0-多个参数

声明不带参数的函数(以下都以MySql数据库为例)
 CREATE FUNCTION 函数名() RETURNS 返回值类型
RETURN 返回结果;
如果函数体中有多行语句,需要使用begin end 包裹

CREATE FUNCTION f1() RETURNS VARCHAR(255)
BEGIN
DELETE  FROM tb1 WHERE id=1;
RETURN "hahahahah"; 
END
SELECT f1(); -- 调用函数
DROP FUNCTION f1;-- 销毁函数
-- 创建带参数的函数
CREATE FUNCTION f2(username VARCHAR(255),age INT,sex enum("男","女")) RETURNS VARCHAR(255)
BEGIN
INSERT INTO tb VALUES(NULL,username,age,sex);
RETURN "hahahahah"; 
END
SELECT f2("真实姓名",55,"男");

 

2.存储过程

存储过程
是一组预先存储好的SQL语句和控制语句的预编译集合。用一个名字来存储一组代码,作为一个完整的单元进行操作
优点:

①增强的sql语句的功能和灵活性;

②执行速度更快 存储过程中的SQL语句都是在声明过程时预先编译好的,只会在声明时编译一次后续执行无需再编译

③减少网络请求/流量  后台只需向数据库提交存储过程名即可,无需提交大段SQL语句
创建无参存储过程

CREATE PROCEDURE p1()
SELECT * FROM tb;
-- 删除存储过程
DROP PROCEDURE p1;
-- 使用call 调用存储过程,如果没有参数,则小括号可以省
CALL p1;

存储过程的参数与返回值
 在存储过程中,参数分为三种情况
1. in:类似于函数中的参数,需要在调用存储过程时传入
2. out:类似函数中的返回值,存储过程返回值不用return 而是将结果赋给out类型的参数
3. INOUT:调用存储过程时可以传入参数,同时又允许存储过程执行时修改其值,并返回

DROP PROCEDURE p2;
CREATE PROCEDURE p2(in iusername VARCHAR(255),in iage int,in isex enum("男","女"),OUT lastId INT,out total INT)
BEGIN
INSERT INTO tb VALUES (NULL,iusername,iage,isex);
SELECT LAST_INSERT_ID() INTO lastId;-- 使用into将查询出的最后id赋给lastId
SELECT COUNT(*) FROM tb INTO total;
END
SET @age=22;
CALL p2("xiaowu",@age,"男",@lastId,@total);-- 调用存储过程out类型的参数,必须传入一个变量接收
-- in类型的参数可以使变量也可以是字面量
SELECT @lastId;-- 调用完成后,返回的结果被存储在@lastId变量中,直接select输出即可

3.事务

事务的四大属性(ACID属性)
 1、原子性 Atomicity
 事务是不可再拆分的最小单元。 事务中的所有操作,要么都执行、要么都不执行;
 eg:转账功能, A减少1000元,B必须增加1000元。而不能出现A减少,B不增加的情况;
 2、一致性 Consistency
 事务在开始前和结束后的数据必须要保持一致。
 eg:转账功能,AB的总金额在转账前和转账后必须相同。其中短暂的不同只能出现在事务未结束时。
 3、隔离性 Isolation
 当事务开始执行后,会隔离其他事务对数据进行读写与修改。
 eg: 转账功能,A给B转账的过程中,如果C也给B转账,就会导致A给B转账的这个事务,
     在结束时的总金额与开始前不一致,也就是说无法满足事物的一致性。
 4、持久性 Durability
 事务执行完成后,数据的修改是永久的。

 

4.游标

游标 用于处理从数据库中返回的多行记录,可以逐行遍历数据。

DROP PROCEDURE youbiao;
CREATE PROCEDURE youbiao(OUT params VARCHAR(255))
BEGIN
    DECLARE cs CURSOR FOR SELECT username FROM tb;-- 为一个结果集声明游标
    OPEN cs;-- 打开游标
    FETCH cs INTO params;-- 取出游标放到变量中
    INSERT INTO tb2 VALUES(null,params);
    CLOSE cs;-- 关闭游标
END;
CALL youbiao(@params);
SELECT @params;

5.视图

视图的特点
 是一个数据库中虚拟的表
 经过查询操作形成的结果
 具有普通表的结构
 不能实现数据的存储.数据依然存在实际的数据表中
 对视图的修改将会影响实际的数据表,但是在视图新增数据时,必须保证原有数据表的视图不可见不能为非空

CREATE OR REPLACE VIEW username AS SELECT username,sex FROM tb;
SELECT * FROM username;
DELETE FROM username WHERE username="wuli";
INSERT username VALUES("wuli","男");
DROP VIEW username;

6.索引

索引特点:建立索引能大大提高查询速度,但会影响降低插入、修改的速度
 适合建立索引的列:需要频繁查询的字段,需要经常排序的字段,经常出现在where子句里的字段;
 不适合建立索引的列:很少使用查询的字段,数据量很少的字段,修改插入操作远大于查询操作的字段
 避免限制索引的方式“(能够使用索引的操作)
 避免使用不等操作符(<> 、!=)
 避免使用is null or is not null
 避免在where子句中使用函数
 避免在比较时使用不匹配的数据类型。
 
 注:MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)

CREATE INDEX index1 on tb(username);-- 创建索引

-- 修改表结构方式创建索引
ALTER TABLE tb ADD INDEX index1(username);
CREATE TABLE tt(
    -- 创建表时建立索引
    id INT PRIMARY KEY,
    username VARCHAR(255),
    INDEX index1(username)

);
CREATE UNIQUE INDEX index1 ON tb(username);-- 唯一索引 与普通索引基本相同,只是值必须唯一不能重复,可以为空,只能有一个空值
-- 全文索引
-- 只能创建在char\varchar \text等字符串字段上,用于索引大量内容,但生成全文索引非常消耗时间和磁盘空间
ALTER TABLE tb ADD FULLTEXT index1(username);

-- 删除索引
DROP INDEX index1 ON tb;

 

posted @ 2017-07-09 21:00  Cherish李  阅读(391)  评论(0编辑  收藏  举报