索引和存储过程、存储过程程序的实现
mysql> select * from student;
+------+----+------+
| name | id | d_id |
+------+----+------+
| 小明 | 1 | 2 |
| 小波 | 2 | 1 |
| 小何 | 3 | 3 |
| 小宝 | 4 | 1 |
| 小米 | 5 | 2 |
+------+----+------+
5 rows in set (0.03 sec)
关键字：CREATE INDEX ON SHOW DROP EXPLAIN FROM WHERE
一、创建索引：indexName 索引名字 student表名 name字段
字段后面需要：(length) 当字段为：varchar和char可不用加长度
mysql> create index indexName on student(name);
Query OK, 0 rows affected (0.10 sec)
查看索引结构：
mysql> show index from student;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------
| student | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | |
| student | 1 | fk | 1 | d_id | A | 5 | NULL | NULL | | BTREE | |
| student | 1 | indexName | 1 | name | A | 5 | NULL | NULL | | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------
3 rows in set (0.00 sec)
解释说明：
mysql> explain select * from student where name = '小何';
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | student | ref | indexName | indexName | 152 | const | 1 | Using where |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
删除索引：
mysql> drop index indexName on student;
Query OK, 0 rows affected (0.11 sec)
mysql> explain select * from student where name = '小何';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
可以看出创建了索引：rows=1 删除索引后rows=5 适用于快速查找。
二、存储过程：
mysql> delimiter //
mysql> create procedure p1()
-> begin
-> select count(*) from student;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;（在delimiter与分号之间有一个空格）
mysql> call p1();
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
注：
（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（3）过程体的开始与结束使用BEGIN与END进行标识。
查看存储过程命令:
mysql> show procedure status;显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
+--------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+-------------
-------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Col
lation |
+--------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+-------------
-------+
| db16 | p1 | PROCEDURE | test@localhost | 2016-11-10 17:05:47 | 2016-11-10 17:05:47 | DEFINER | | gbk | gbk_chinese_ci | utf8_general
_ci |
mysql> show create procedure p1; 显示某一个MySQL存储过程的详细信息
+-----------+----------------------------------------------------------------+-----------------------------------------------------
------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure
racter_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------+-----------------------------------------------------
------------------+----------------------+--------------------+
| p1 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`test`@`localhost` PROCEDURE `p1`()
begin
select count(*) from student;
end | utf8 | utf8_general_ci | utf8_general_ci |
+-----------+----------------------------------------------------------------+-----------------------------------------------------
------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> drop procedure if exists p1;删除存储过程
Query OK, 0 rows affected (0.00 sec)
三、带参数存储过程：
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名数据类形...])
IN输入参数:
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:
该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:
调用时指定，并且可被改变和返回
1. 带输出参数返回值的存储过程：
--删除存储过程
DROP PROCEDURE IF EXISTS p1;
--设置分割符为//
mysql> DELIMITER //
创建存储过程：带参
mysql> create procedure p1(out count int)
-> begin
-> select count(*) into count from employee;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
--设置分割符为;
mysql> DELIMITER ;
--调用存储过程
mysql> call p1(@count);
Query OK, 1 row affected (0.00 sec)
--查看输出变量值
mysql> select @count;
+--------+
| @count |
+--------+
| 9 |
+--------+
1 row in set (0.00 sec)
mysql> select * from employee;
+------+--------+-------+---------+----------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
+------+--------+-------+---------+----------+----------+-----------+
| 1001 | 李强 | 1 | 20 | 会计 | 800 | 2015-9-9 |
| 1002 | 王刚 | 1 | 30 | HR | 1600 | 2014-10-9 |
| 1003 | 珊珊 | 0 | 30 | HR | 1250 | 2014-11-7 |
| 1004 | 张阳 | 1 | 20 | 出纳 | 2975 | 2015-4-9 |
| 1005 | 小丽 | 0 | 40 | 销售经理 | 2850 | 2015-2-10 |
| 1006 | 王二 | 1 | 10 | 产品策划 | 2450 | 2015-4-23 |
| 1007 | 小冬 | 1 | 40 | 销售经理 | 2750 | 2015-3-10 |
| 1008 | 码子 | 1 | 30 | HR | 1600 | 2014-10-9 |
| 1009 | 李四 | 1 | 20 | 会计 | 2450 | 2014-10-9 |
+------+--------+-------+---------+----------+----------+-----------+
9 rows in set (0.00 sec)
2.带输入参数的存储过程：
--设置分割符为//
mysql> DELIMITER //
--创建存储过程
mysql> create procedure p2(in n int)
-> begin
-> select * from employee where e_no = n;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
设置分隔符为;
mysql> DELIMITER ;
--定义变量
mysql> set @n =1002;
Query OK, 0 rows affected (0.00 sec)
--调用存储过程
mysql> call p2(@n);
+------+--------+-------+---------+-------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
+------+--------+-------+---------+-------+----------+-----------+
| 1002 | 王刚 | 1 | 30 | HR | 1600 | 2014-10-9 |
+------+--------+-------+---------+-------+----------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
3.带输入输出参数:
1.输入一个int型变量p，2.查询输出p，3.设置p为10,4.查询输出p 5.改分隔符为; 6.设置p为5
7.调用存储过程p3 输出p为5 10 在这里是先设置p为5，后将p设置为10(赋值给p)
mysql> DELIMITER //
mysql> create procedure p3(INOUT p int)
-> begin
-> select p;
-> set p = 10;
-> select p;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> set @p = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> call p3(@p);
+------+
| p |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
+------+
| p |
+------+
| 10 |
+------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
4.多个参数：
mysql> select * from employee;
+------+--------+-------+---------+----------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
+------+--------+-------+---------+----------+----------+-----------+
| 1001 | 李强 | 1 | 20 | 会计 | 800 | 2015-9-9 |
| 1002 | 王刚 | 1 | 30 | HR | 1600 | 2014-10-9 |
| 1003 | 珊珊 | 0 | 30 | HR | 1250 | 2014-11-7 |
| 1004 | 张阳 | 1 | 20 | 出纳 | 2975 | 2015-4-9 |
| 1005 | 小丽 | 0 | 40 | 销售经理 | 2850 | 2015-2-10 |
| 1006 | 王二 | 1 | 10 | 产品策划 | 2450 | 2015-4-23 |
| 1007 | 小冬 | 1 | 40 | 销售经理 | 2750 | 2015-3-10 |
| 1008 | 码子 | 1 | 30 | HR | 1600 | 2014-10-9 |
| 1009 | 李四 | 1 | 20 | 会计 | 2450 | 2014-10-9 |
+------+--------+-------+---------+----------+----------+-----------+
9 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE p5(IN p int,OUT c int) //输入p，输出c
-> BEGIN
-> SELECT MAX(e_salary) INTO c FROM employee WHERE dept_no = p; //从employee dept_no=p 查询最大赋值(INTO)给c
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> set @p = 30;
Query OK, 0 rows affected (0.00 sec)
mysql> call p5(@p,@c);
Query OK, 1 row affected (0.00 sec)
mysql> select @c;
+------+
| @c |
+------+
| 1600 |
+------+
1 row in set (0.00 sec)
六、变量
DECLARE 变量名 变量类型 [DEFAULT 默认值]
如:
declare x varchar(5) default 'outer';
七、基本控制语句
if语句
if(条件成立){
语句
}
IF 条件成立 THEN
语句
END IF
if-else语句
if(){
语句1;
}else{
语句2;
}
IF 条件 THEN
语句1;
ELSE
语句2;
END IF
mysql> CREATE PROCEDURE proc_declare()
-> BEGIN
-> DECLARE x int default 1;
-> SELECT x;
-> IF x = 1 THEN
-> SELECT * FROM employee WHERE e_no = 1001;
-> ELSE
-> SELECT COUNT(*) FROM employee;
-> END IF;
-> END;
-> //
多分支语句
switch(值){
case 值1：
语句块1;
case 值2:
语句块2
}
CASE 值
WHEN 值1 THEN
语句块1;
WHEN 值2 THEN
语句块2;
WHEN 值3 THEN
语句块3;
END CASE;
->case var
->when 0 then
-> insert into t values(17);
->when 1 then
-> insert into t values(18);
->else
-> insert into t values(19);
-> end case;
循环语句
while(条件){
语句块;
}
WHILE 条件 do
语句块;
END WHILE;
->while var<6 do
-> insert into t values(var);
-> set var=var+1;
->end while;
package cn.procedure.day10;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import com.mysql.jdbc.CallableStatement;
public class ProcedureDemo {
private static final String url = "jdbc:mysql://192.168.5.17:3306/db16";
private static final String user = "test";
private static final String password = "123321";
private static SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
public ProcedureDemo(){
try {
Class.forName("com.mysql.jdbc.Driver");//加载驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
//executeProcedureIn();
// addEmployee();
// executeProcedureOut();
executeProcedureInOut();
}
/**
* 2.带输入参数的存储过程：查询指定e_no信息
* 1009 李四 1 会计 2450
*/
public static void executeProcedureIn(){
Connection connect = null;
java.sql.CallableStatement cs = null;
ResultSet rs = null;
try {
connect = DriverManager.getConnection(url, user, password);
String sql = "{call p2(?)}";// 调用存储过程语句
cs = connect.prepareCall(sql);
cs.setInt(1, 1009);
rs = cs.executeQuery();
if(rs.next()){
int no = rs.getInt("e_no");
String name = rs.getString("e_name");
int sex = rs.getInt("e_sex");
String job = rs.getString("e_job");
int salary = rs.getInt("e_salary");
System.out.println(no+"\t"+name+"\t"+sex+"\t"+job+"\t"+salary);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connect != null)
connect.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 建立连接
*/
public static Connection getConnection(){
Connection connect = null;
try {
connect = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connect;
}
/**
* 关闭数据库连接
*/
public static void closeConnection(Connection connect) {
try {
if (connect != null)
connect.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* employee 添加一员工信息
*
*/
public static void addEmployee() {
String sql = "INSERT INTO employee (e_no,e_name,e_sex,e_job,e_salary,hire_date,dept_no)VALUES(?,?,?,?,?,?,?)";
Connection connect = null;
PreparedStatement ps = null;
try {
connect = getConnection();
ps = connect.prepareStatement(sql);
ps.setInt(1, 2001);
ps.setString(2, "小明");
ps.setString(3, "1");
ps.setString(4, "界面设计");
ps.setInt(5, 2500);
ps.setString(6, format.format(new Date()));
ps.setInt(7, 10);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 带参。查询记录条数
* 输出变量值：
* 10
*/
public static void executeProcedureOut(){
String sql = "{call p1(?)}";
Connection connect = null;
java.sql.CallableStatement cs = null;
try {
connect = getConnection();
cs = connect.prepareCall(sql);
cs.registerOutParameter(1, Types.INTEGER);//类型
cs.execute();
int count = cs.getInt(1);
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(cs != null)
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
/**
* 多个参数:查询dept_no为30 的最高薪资
* 最高工资：1600
*/
public static void executeProcedureInOut(){
String sql = "call p5(?,?)";
Connection connect = null;
java.sql.CallableStatement cs = null;
try {
connect = getConnection();
cs = connect.prepareCall(sql);
cs.setInt(1,30);//设置输入参数
cs.registerOutParameter(2, Types.INTEGER);//设置输出参数类型
cs.execute();
int max = cs.getInt(2);
System.out.println("最高工资："+max);
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(cs != null)
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
closeConnection(connect);
}
}
}
985
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
