视图 索引 序列 触发器 存储过程

视图：

虚拟的表，只包含使用时动态检索数据的查询，而自身不包含任何数据。

作用：

1》sql简化。

2》安全性。

3》重用sql语句。

4》使用表的部分而不是整个表。

5》保护数据，可以给用户授予表的特定部分的使用权限。

规则：

1》视图名字唯一。

2》如果查询视图语句中含有order by，则创建视图中的order by会被覆盖。

3》视图不能有索引、触发器、或默认值。

4》视图可以和表一起使用。

赋予权限：grant create view to 用户名;

查看创建视图的信息：show create view 视图名 ;

使用视图：select * from 视图名;

创建视图：create view 视图名 as ... ;

将查询结果放到视图里，或者将结果集放到视图里，简化查询sql操作。

更新视图：create or replace view 视图名 as ... ;

删除视图：drop view ... ;

索引：

包括自动索引、手动索引。

在表中定义primary key 和 unique 约束，数据库会自动创建索引。

创建索引：create index 索引名 on 表名(列名) ;

适合创建索引的情况：

1》经常用于where子句或作为查询条件的列。

2》所含数据值范围比较大的列。

3》含有大量空值的列。

4》经常用于where子句或者连接条件的两个或多个列。

5》绝大多数情况， 只查询总记录 2% - 4% 的列。

不适合创建索引的情况：

1》表数据量很小。

2》列很少在查询中作为条件。

3》频繁更新的表。

4》绝大多数情况， 查询大于总记录 2% - 4% 。

序列：

特点：

1》序列产生器可以为表中记录自动产生序列值。

2》序列是由用户创建数据库对象并且可以被多个用户共享。

3》序列的典型应用是产生主键值。

4》序列的产生和存储与表无关，因此相同序列可以用于多个表。

创建序列：

create sequence 序列名

increment by 1

start with 1

maxvalue 999999

nocache

nocycle ;

nocache 表示指定的序列值是否被预先分配并存储在内存中。nocache表示不预先分配并存储； cache n 表示预先分配并存储，n 表示数量。

修改序列：

alter sequence 序列名

increment by 1

maxvalue 99999

nocache

nocycle ;

注：不能修改start with 项，maxvalue不能比原来值小。

查看序列信息：

select sequence_name , max_value , min_value , increment_by , last_number from 序列名;

nextval：获取序列中下一个有效的序列值。

select 序列名.nextval from dual ;

currval：获取当前有效序列值。

select 序列名.currval from dual ;

序列的使用：

insert into t_user ( id , name ) values ( 序列名.nextval , 'tom' ) ;

触发器：

mysql 响应 insert,update,delete 自动执行的一条sql语句。

只有表支持触发器，视图不支持。

特征：

1》一个表中的触发器名称唯一。

2》在一个表上最多6个触发器。

3》触发器包括前置触发器，后置触发器。

4》响应之前的触发器执行失败，响应不会执行；响应之前的触发器或响应执行失败，响应之后的触发器不会持续。

1、insert触发器

DELIMITER //

create trigger 触发器名

before/after insert on table_A

for each row

begin

insert into table_B( name ) values ( new.name );

end ;

DELIMITER ;

说明：new表示一个虚拟的表，可以查询刚插入的数据。

2、update触发器

DELIMITER //

create trigger 触发器名

before update on table_A

for each row

begin

set new.name = upper( new.name );

end ;

DELIMITER ;

3、delete触发器

DELIMITER //

create trigger 触发器名

after delete on table_A

for each row

begin

insert into table_B( name ) values ( old.name );

end ;

DELIMITER ;

4、删除触发器

drop trigger 触发器名;

存储过程：

一组为了完成某个特定功能而编写的sql程序集。

优点：

1》把处理封装在简单易用的单元中，简化复杂的操作。

2》提高性能，存储过程运行在数据库，采用存储过程，比单纯使用sql要快。

3》安全，调用者只需要调用指定的存储过程，而不用关心存储过程的内容。

缺点：

1》复杂。

2》没有权限无法创建存储过程。

实例一：参数传递

DELIMITER //

CREATE PROCEDURE 存储过程名字 (

IN param_name INT,

OUT res INT

)

BEGIN

select xxx INTO res from 表名;

END //

DELEMITER ;

说明：DELEMITER 表示临时把sql语句结束符改为//，

IN 表示输入参数，OUT 表示返回结果，INTO 表示将查询结果赋值给res。

调用存储过程：CALL 存储过程名字 ( 2 , @n);

显示结果信息：SELECT @n;

实例二：条件判断

DELIMITER //

CREATE PROCEDURE ordertotal(

IN ordernum INT,

IN tax BOOLEAN,

OUT total DECIMAL(8,2)

)

BEGIN

DECLARE ordertotal DECIMAL(8,2);

DECLARE taxvalue FLOAT DEFAULT 0.6;

select SUM(price) into ordertotal from products where order_num = ordernum;

IF tax THEN

select ordertotal + (ordertotal * taxvalue) INTO ordertotal ;

END IF;

select ordertotal into total;

END //

DELIMITER ;

说明：DECLARE 表示声明变量，ordertotal ordernum表示调用传入参数进行查询，并将结果赋值给变量。IF...THEN...END IF是条件判断语法。

调用存储过程：

CALL ordertax( 2005,1,@total );

SELECT @total;

实例三：流程控制语句

DELIMITER //

CREATE procedure test(OUT res INT)

BEGIN

DECLARE id INT DEFAULT 4;

if id='12' then

set res=0;

elseif id<12 then

set res=1;

else set res=2;

end if;

END //

DELIMITER ;

说明：if...then...elseif...else是条件判断语法，set表示对结果值进行赋值。

实例四：while循环

BEGIN

DECLARE num INT;

DECLARE total INT DEFAULT 0;

SET num=0;

WHILE num<10 DO

set total=total+num;

set num=num+1;

END WHILE;

select total;

END //

实例5：java调用存储过程（有参数无返回值）

Class.forName("com.mysql.jsbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql:///my_db","root","root");

CallableStatement cs=conn.prepareCall("

{ CALL test_proce (?,?) }

"); // 调用存储过程

cs.setString(1,"tom");

cs.setString(2,"lucy");

int rows= cs.executeUpdate();

cs.close();

conn.close();

实例6：java调用存储过程（无参数，有返回值）

Class.forName("com.mysql.jsbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql:///my_db","root","root");

CallableStatement cs=conn.prepareCall("

{ CALL test_proce () }

"); // 调用存储过程

cs.setString(1,"tom");

cs.setString(2,"lucy");

ResultSet rs=cs.executeQuery();

while ( rs.next() ){

String name=rs.getString("username");

}

cs.close();

conn.close();

实例7：java调用存储过程（有参数，有返回值）

Class.forName("com.mysql.jsbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql:///my_db","root","root");

CallableStatement cs=conn.prepareCall("

{ CALL test_proce (？,？,？) }

"); // 调用存储过程

cs.registerOutParameter(1,Types.INTEGER); //注册输出参数

cs.setString(2,"tom");

cs.setString(3,"lucy");

cs.executeUpdate();

int res=cs.getInt(1);

cs.close();

conn.close();