以下文章为参考的,文章出处:http://blog.csdn.net/l1028386804/article/details/46612859
①:存储过程
1、创建语法 copy
示例
-- 简单的存储过程(无参数查询全部数据) copy
这里的逻辑是
1、先判断是否有Proc() 这个存储过程,有就drop掉
2、创建Proc() 存储过程
3、执行Proc() 存储过程
4、查看Proc()存储过程
5、删除Proc()存储过程
注意:“DELIMITER //”语句的作用是将MYSQL的结束符设置为//,因为MYSQL默认的语句结束符为分号;,
为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER 改变存储过程的结束符,并以“END//”结束存储过程。
存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符!!!!!!!!!
注意:当使用DELIMITER命令时,应该避免使用反斜杠(\)字符,因为反斜杠是MYSQL的转义字符!!!-- 输出参数的存储过程
2、存储过程操作符:
l 算术运算符
+ 加 SET var1=2+2; 4
- 减 SET var2=3-2; 1
* 乘 SET var3=3*2; 6
/ 除 SET var4=10/3; 3.3333
DIV 整除 SET var5=10 DIV 3; 3
% 取模 SET var6=10%3 ; 1l 比较运算符
> 大于 1>2 False
< 小于 2<1 False
<= 小于等于 2<=2 True
>= 大于等于 3>=2 True
BETWEEN 在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN 在集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True
= 等于 2=3 False
<>, != 不等于 2<>3 False
<=> 严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL 为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL Truel 逻辑运算符
与(AND)
或(OR)
异或(XOR)
l 位运算符
| 位或
& 位与
<< 左移位
>> 右移位
~ 位非(单目运算,按位取反)3.存储过程基本函数:(内置函数)
mysq存储过程中常用的函数,字符串类型操作,数学类,日期时间类
l 字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring )//返回substring首次在string中出现的位置,不存在返回0
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LCASE (string2 ) //转换成小写
UCASE (string2 ) //转换成大写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
TRIM (string2 ) //去除空格
SPACE(count) //生成count个空格
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符RIGHT(string2,length) //取string2最后length个字符
l 数学类
RAND([seed]) //随机数
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]l 日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CURRENT_DATE ( ) //当前日期(2017-10-26)
CURRENT_TIME ( ) //当前时间(13:52:20)
CURRENT_TIMESTAMP ( ) //当前时间戳(2017-10-26 13:52:20)
DATE (datetime ) //返回datetime的日期部分('2003-12-31 01:02:03')
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差
DATE_FORMAT (datetime ,格式 ) //使用formatcodes格式显示datetime
%Y(年份|4位)、%y(年份|2位)、%M(月份|January-December)、%m(月份|00-12)、
%W(周|周日-周六)、%w(周|0-6)、
%p(上下午|上午AM、下午PM)
%D( 日期|0th.1st.2nd.)、%d( 日期|00-31)、%H( 小时|0-23)、%h( 小时|01-12)、%i( 分钟|00-59)、%S( 秒|00-59);MONTHNAME (date ) //英文月份名示例:SELECT DATE_FORMAT('1995-04-24 12:21:00','%H:%i:%S') 查询得到'12:21:00'
SELECT DATE_FORMAT('1995-04-24 12:21:00','%W:%M:%Y') 查询得到'Saturday October 1995'
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
MONTH(datetime) //月DAY (date ) //返回日期的天
HOUR(datetime) //小时,0-23
MINUTE(datetime) //分,0-59SECOND(datetime)//秒,0-59
MICROSECOND(datetime) //微秒
NOW ( ) //当前时间
DAYNAME (date ) //英文星期
DAYOFYEAR (date ) //一年中的第几天,1-365
DAYOFMONTH(datetime) //月的第几天,1-31
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
LAST_DAY(date) //date的月的最后日期QUARTER(date)//返回date对应的季度,1-4
4. 存储过程优化:(一)SQL优化。存储过程封装的SQL自身的优化;SQL优化详情请见本系列前几篇。
(二)操作前检查。在向表插入数据之前,要判断该条数据在表中是否已经存在了,如果存在,则不再插入数据。同理,在从表中查询数据之前,要先判断该条数据在表中是否存在,如果存在,才能从表中查找数据。
(三)结果标识规范化。不管向表插入数据的操作执行成功与否,都应该有一个标识值来表示执行的结果,这样也方便开发人员对程序流程的追踪和调试。比如,在每条leave语句之前,都应该有一个返回值,我们为此定义一个输出参数。
(四)列出具体的字段名。在以后的软件版本中,新增了字段,那么这条insert语句极有可能会报错。所以罗列出所需字段是规范化很重要的一点。
(五)失败返回。在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果。比如:如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。
DEMO:
@error_count如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
②:变量的使用
变量可以在子程序中声明并使用,这些变量的作用范围是在BEGIN...END程序中
1、定义变量
在存储过程中定义变量 copy
var_name为局部变量的名称。DEFAULT VALUE子句给变量提供一个默认值。值除了可以被声明为一个常数外,还可以被指定为一个表达式。
如果没有DEFAULT子句,初始值为NULL view plain copy
2、为变量赋值
定义变量之后,为变量赋值可以改变变量的默认值,MYSQL中使用SET语句为变量赋值
声明3个变量,分别为var1,var2和var3 view plai copy
MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值 view plain copy
③:光标
MYSQL里叫光标,SQLSERVER里叫游标,实际上一样的
查询语句可能查询出多条记录,在存储过程和函数中使用光标来逐条读取查询结果集中的记录。
光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前,并且声明在变量和条件之后。
1.声明光标
MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下: copy
cursor_name参数表示光标的名称;select_statement参数表示SELECT语句的内容,返回一个用于创建光标的结果集
下面声明一个名为cur_employee的光标。代码如下: copy
上面的示例中,光标的名称为cur_employee;SELECT语句部分是从employee表中查询出name和age字段的值。
2.打开光标
MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下: copy
其中,cursor_name参数表示光标的名称。
下面打开一个名为cur_employee的光标,代码如下: copy
3.使用光标
MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下: copy
其中,cursor_name参数表示光标的名称;var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。
下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中,代码如下: view plain copy
上面的示例中,将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。
4.关闭光标
MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下: view pla copy
其中,cursor_name参数表示光标的名称。
【示例14-11】 下面关闭一个名为cur_employee的光标。代码如下: copy
上面的示例中,关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。
注意:MYSQL中,光标只能在存储过程和函数中使用!!
存储过程光标DEMO:题目和SQL语句,可直接在MYSQL中运行。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
④:流程控制 view pl cop
用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
每个流程中可能包含一个单独语句,或者是使用BEGIN...END构造的复合语句,构造可以被嵌套
1.IF语句
IF语句用来进行条件判断。根据是否满足条件,将执行不同的语句。其语法的基本形式如下: copy
其中,search_condition参数表示条件判断语句;statement_list参数表示不同条件的执行语句。
注意:MYSQL还有一个IF()函数,他不同于这里描述的IF语句
下面是一个IF语句的示例。代码如下:
2.CASE语句
CASE语句也用来进行条件判断,其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下: copy
其中,case_value参数表示条件判断的变量;
when_value参数表示变量的取值;
statement_list参数表示不同when_value值的执行语句。
CASE语句还有另一种形式。该形式的语法如下: copy
其中,search_condition参数表示条件判断语句;
statement_list参数表示不同条件的执行语句。
下面是一个CASE语句的示例。代码如下: copy
代码也可以是下面的形式: copy
本示例中,如果age值为20,count1的值加1;否则count2的值加1。CASE语句都要使用END CASE结束。
注意:这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句
并且用END CASE替代END来终止!!
3.LOOP语句
LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。
但是LOOP语句本身没有停止循环的语句,必须是遇到LEAVE语句等才能停止循环。
LOOP语句的语法的基本形式如下: copy
statement_list参数表示需要循环执行的语句。
下面是一个LOOP语句的示例。代码如下:
该示例循环执行count加1的操作。因为没有跳出循环的语句,这个循环成了一个死循环。
LOOP循环都以END LOOP结束。
4.LEAVE语句
LEAVE语句主要用于跳出循环控制。其语法形式如下: view plain copy
其中,label参数表示循环的标志。
下面是一个LEAVE语句的示例。代码如下: copy
该示例循环执行count加1的操作。当count的值等于100时,则LEAVE语句跳出循环。
5.REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:
其中,statement_list参数表示循环的执行语句;search_condition参数表示结束循环的条件,满足该条件时循环结束。
下面是一个ITERATE语句的示例。代码如下: copy
该示例循环执行count加1的操作,count值为100时结束循环。
6.WHILE语句
WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。
WHILE语句是当满足条件时,执行循环内的语句。
WHILE语句的基本语法形式如下:
其中,search_condition参数表示循环执行的条件,满足该条件时循环执行;
statement_list参数表示循环的执行语句。
下面是一个ITERATE语句的示例。代码如下: view plain copy
该示例循环执行count加1的操作,count值小于100时执行循环。
⑤:索引(index)
优点:
大大加快数据的检索速度 ;
创建唯一性索引,保证数据库表中每一行数据的唯一性 ;
加速表和表之间的连接 ;
在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序 的时间。
缺点:
索引需要占物理空间
当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护, 降低了数据的维护速度。
1.普通(唯一)索引的创建: view plain copy
2.查看索引:Show index keys from 表名;
3.删除索引:drop index 索引名 ON 表名。
⑥:数据库性能优化
1.SQL语句优化选择合理的表名顺序
避免使用SELECT *
删除重复记录
减少对表的查询
使用表的别名
用EXISTS替代IN
NOT EXISTS替代NOT IN
用表连接替换EXISTS
用EXISTS替换DISTINCT
2.索引优化当有多个索引列时,且包含非”=”号时,用”=”号索引;当有多个索引列时,且都为非”=”号时,只用一个索引;用>=替代>;
用UNION替换OR;
避免在索引列上使用IS NULL和IS NOT NULL;
用UNION-ALL替换UNION;
3.EM工具优化
Oracle 10g的EM中提供了两个新工具SQL优化指导和SQL优化顾问
SQL优化指导
SQL访问指导