-- 索引 INDEX 
  
CREATE INDEX idx_sname ON student( sname(4)); 
  
 

   ALTER TABLE teacher add index idx_tname(tname); 
 
 
 

   DROP INDEX idx_sname on student; 
 
 
 

   -- 视图 VIEW 
  
create VIEW v_stu 
  
AS 
  
 SELECT sname,degree from student s, score sc 
  
 where s.sno=sc.sno; 
  
 

   SELECT * from v_stu; 
 
 
 

   -- 存储过程 PROCEDURE 
  
--  创建 INSERT PROCEDURE 
  
DROP PROCEDURE IF EXISTS `P_INSERT_STUDENT`;  
  
CREATE PROCEDURE P_INSERT_STUDENT( 
  
 IN xueH VARCHAR(3), 
  
 IN xingM VARCHAR(4), 
  
 IN xingB VARCHAR(2), 
  
 IN shengR DATETIME, 
  
 IN banJ VARCHAR(5) 
  
)  
  
BEGIN  
  
     SET @xueH = xueH;  
  
     SET @xingM = xingM;  
  
     SET @xingB = xingB; 
  
     SET @shengR = shengR;  
  
   SET @banJ = banJ;   
  
     SET @insertSql = CONCAT('INSERT INTO STUDENT VALUES(?,?,?,?,?)');  
  
     PREPARE stmtinsert FROM @insertSql;  
  
     EXECUTE stmtinsert USING @xueH,@xingM,@xingB,@shengR,@banJ;  
  
     DEALLOCATE PREPARE stmtinsert;  
  
END;  
  
 -- 存储过程调用 
  
CALL P_INSERT_STUDENT(412,'黄一','女','1990-12-01 10:20:01','304');  
  
CALL P_INSERT_STUDENT(413,'黄二','男','1992-12-01 10:20:01','304');  
  
CALL P_INSERT_STUDENT(414,'黄三','男','1993-12-01 10:20:01','304');  
  
CALL P_INSERT_STUDENT(416,'黄五','女','1992-02-01 10:20:01','304');  
  
 

    -- 结果验证 
  
SELECT * from student; 
  
 
 
  
-- 创建存储过程 查询 
  
DROP PROCEDURE IF EXISTS `pro_get_info`; 
  
CREATE PROCEDURE pro_get_info( 
  
 IN ckh VARCHAR(5) 
  
) 
  
BEGIN 
  
 SET @ckh=ckh; 
  
 SET @selectSQL = CONCAT('select s.*,cno,DEGREE from student s,score sc 
  
 where s.sno=sc.sno and cno=?');  
  
     PREPARE stmtSELECT FROM @selectSQL;  
  
     EXECUTE stmtSELECT USING @ckh;  
  
     DEALLOCATE PREPARE stmtSELECT;  
  
END; 
  
 

   CALL pro_get_info('3-105'); 
  
CALL pro_get_info('3-245'); 
  
 
 
  
--  触发器 TRIGGER 
  
-- 创建 INSERT TRIGGER 
  
DROP TRIGGER IF EXISTS `t_insert_score`; 
  
CREATE TRIGGER t_insert_score AFTER INSERT ON student FOR EACH ROW 
  
BEGIN 
  
  DECLARE sno VARCHAR(3) ; 
  
  DECLARE cno VARCHAR(35) ; 
  
  DECLARE degree DECIMAL(10) ; 
  
    INSERT into score VALUES(111,'3-105','99'); 
  
END; 
  
-- 结果验证 
  
SELECT * from score; 
  
################################################## 
  
#  批量造数据，插入数据库 
  
#################################################### 
  
 

   #创建测试表 
  
DROP TABLE IF EXISTS test.test; 
  
CREATE TABLE test.test( 
  
 id int(10) not null auto_increment, 
  
 a int(10) not null, 
  
 b int(10) not null, 
  
 c int(10) not null, 
  
 PRIMARY key (`id`) 
  
)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试表'; 
  
  
  
 

   #清空数据 
  
TRUNCATE table test.test; 
  
  
  
#定义存储过程 
  
delimiter // 
  
DROP PROCEDURE IF EXISTS insert_test_val; 
  
##num_limit 要插入数据的数量,rand_limit 最大随机的数值 
  
CREATE PROCEDURE insert_test_val( 
  
 in num_limit int, 
  
 in rand_limit int) 
  
BEGIN 
  
  
  
 DECLARE i int default 1; 
  
 DECLARE a int default 1; 
  
 DECLARE b int default 1; 
  
 DECLARE c int default 1; 
  
  
  
 WHILE i<=num_limit do 
  
  set a = FLOOR(rand()*rand_limit); 
  
  set b = FLOOR(rand()*rand_limit); 
  
  set c = FLOOR(rand()*rand_limit); 
  
  INSERT into test.test values (null,a,b,c); 
  
  set i = i + 1; 
  
 END WHILE; 
  
END 
  
// 
  
  
  
#调用存储过程 
  
call insert_test_val(1000,10); 
  
 

   ######################################################## 
 
 
 
 
  
DROP PROCEDURE IF EXISTS mytest; --判断进程是否存在，存在则删除 
  
CREATE PROCEDURE mytest()  --新建进程 
  
BEGIN 
  
declare i int;  
  
DECLARE j varchar(200); 
  
　　set i = 2; 
  
　　while i < 11 do  --循环体 
  
　　　　SET j=CONCAT('wwq',i);  --拼接字符串 
  
　　　　INSERT INTO pub_user(`ID`, `NAME`, `ACCOUNT`, `PASSWORD`, `USER_CODE`) 
  
　　　　VALUES (i, '王馨', j, 'e10adc3949ba59abbe56e057f20f883e', ''); 
  
 

   　　　　INSERT INTO pub_user_post (`POST_CODE`, `USER_CODE`) 
  
　　　　VALUES (i, 'A685187D29AF4AD793F2753DC17C1435'); 
  
 
 
  
　　　　INSERT INTO pub_user_role (`ROLE_CODE`, `USER_CODE`, `APP_CODE`) 
  
　　　　VALUES ('95AC32C8B9874B4085A01187C341067B', i, 'INSPUR-DZZW-TYSP'); 
  
 

   　　　　set i=i+1; 
  
　　end while; 
  
 

   end  --结束定义语句 
 
 
 
 
  
call mytest();  --调用进程