视图:
虚拟的表,只包含使用时动态检索数据的查询,而自身不包含任何数据。
作用:
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();