MySQL 学习笔记 二
本文内容纲要：
上一篇：http://www.cnblogs.com/hoojo/archive/2011/06/20/2085390.html
Øfunction函数
函数的作用比较大，一般多用在select查询语句和where条件语句之后。按照函数返回的结果，
可以分为：多行函数和单行函数；所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。
如：字符串函数；而多行函数，就是多条记录同时计算，得到最终只有一条结果记录。如：sum、avg等
多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能。MySQL的单行函数有如下特征：
单行函数的参数可以是变量、常量或数据列。单行函数可以接受多个参数，但返回一个值。
单行函数就是它会对每一行单独起作用，每一行（可能包含多个参数）返回一个结果。
单行函数可以改变参数的数据类型。单行函数支持嵌套使用：内层函数的返回值是外层函数的参数。
单行函数可以分为：
类型转换函数；
位函数；
流程控制语句；
加密解密函数；
信息函数
单行函数
1、char_length字符长度
selectchar_length(tel)fromuser;
2、sin函数
selectsin(age)fromuser;
selectsin(1.57);
3、添加日期函数
selectdate_add('2010-06-21',interval2month);
interval是一个关键字，2month是2个月的意思，2是数值，month是单位
selectaddDate('2011-05-28',2);
在前面的日期上加上后面的天数
4、获取当前系统时间、日期
selectcurdate();
selectcurtime();
5、加密函数
selectmd5('zhangsan');
6、Null处理函数
selectifnull(birthday,'isnullbirthday')fromuser;
如果birthday为null，就返回后面的字符串
selectnullif(age,245)fromuser;
如果age等于245就返回null，不等就返回age
selectisnull(birthday)fromuser;
判断birthday是否为null
selectif(isnull(birthday),'birthdayisnull','birthdaynotisnull')fromuser;
如果birthday为null或是0就返回birthdayisnull，否则就返回birthdaynotisnull；类似于三目运算符
7、case流程函数
case函数是一个流程控制函数，可以接受多个参数，但最终只会返回一个结果。
selectname,
age,
(casesex
when1then'男'
when0then'女'
else'火星人'
end
)sex
fromuser;
组函数
组函数就是多行函数，组函数是完成一行或多行结果集的运算，最后返回一个结果，而不是每条记录返回一个结果。
1、avg平均值运算
selectavg(age)fromuser;
selectavg(distinctage)fromuser;
2、count记录条数统计
selectcount(*),count(age),count(distinctage)fromuser;
3、max最大值
selectmax(age),max(distinctage)fromuser;
4、min最小值
selectmin(age),min(distinctage)fromuser;
5、sum求和、聚和
selectsum(age),sum(distinctage)fromuser;
selectsum(ifnull(age,0))fromuser;
6、groupby分组
selectcount(*),sexfromusergroupbysex;
selectcount(*)fromusergroupbyage;
select*fromusergroupbysex,age;
7、having进行条件过滤
不能在where子句中过滤组，where子句仅用于过滤行。过滤groupby需要having
不能在where子句中用组函数，having中才能用组函数
selectcount(*)fromusergroupbysexhavingsex<>2;
Ø多表查询和子查询
数据库的查询功能最为丰富，很多时候需要用到查询完成一些事物，而且不是单纯的对一个表进行操作。而是对多个表进行联合查询，
MySQL中多表连接查询有两种规范，较早的SQL92规范支持，如下几种表连接查询：
等值连接
非等值连接
外连接
广义笛卡尔积
SQL99规则提供了可读性更好的多表连接语法，并提供了更多类型的连接查询，SQL99支持如下几种多表连接查询：
交叉连接
自然连接
使用using子句的连接
使用on子句连接
全部连接或者左右外连接
SQL92的连接查询
SQL92的连接查询语法比较简单，多将多个table放置在from关键字之后，多个table用“，”隔开；
连接的条件放在where条件之后，与查询条件直接用and逻辑运算符进行连接。如果条件中使用的是相等，
则称为等值连接，相反则称为非等值，如果没有任何条件则称为广义笛卡尔积。
广义笛卡尔积：selects.*,c.*fromstudents,classesc;
等值：selects.*,c.*fromstudents,classescwheres.cid=c.id;
非等值：selects.*,c.*fromstudents,classescwheres.cid<>c.id;
selects.*,c.nameclassesfromclassesc,studentswherec.id=s.classes_idands.nameisnotnull;
SQL99连接查询
1、交叉连接crossjoin，类似于SQL92的笛卡尔积查询，无需条件。如：
selects.*,c.namefromstudentscrossjoinclassesc;
2、自然连接naturaljoin查询，无需条件，默认条件是将2个table中的相同字段作为连接条件，如果没有相同字段，查询的结果就是空。
selects.*,c.namefromstudentsnaturaljoinclassesc;
3、using子句连接查询：using的子句可以是一列或多列，显示的指定两个表中同名列作为连接条件。
如果用naturaljoin的连接查询，会把所有的相同字段作为连接查询。而using可以指定相同列及个数。
selects.*,c.namefromstudentsjoinclassescusing(id);
4、join…on连接查询，查询条件在on中完成，每个on语句只能指定一个条件。
selects.*,c.namefromstudentsjoinclassescons.classes_id=c.id;
5、左右外连接：3种外连接，left[outer]join、right[outer]join，连接条件都是通过用on子句来指定，条件可以等值、非等值。
selects.*,c.namefromstudentsleftjoinclassescons.classes_id=c.id;
selects.*,c.namefromstudentsrightjoinclassescons.classes_id=c.id;
子查询
子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。子查询可以出现在2个位置：
from关键字之后，被当做一个表来进行查询，这种用法被称为行内视图，因为该子查询的实质就是一个临时视图
出现在where条件之后作为过滤条件的值
子查询注意点：
子查询用括号括起来，特别情况下需要起一个临时名称
子查询当做临时表时（在from之后的子查询），可以为该子查询起别名，尤其是要作为前缀来限定数据列名时
子查询用作过滤条件时，将子查询放在比较运算符的右边，提供可读性
子查询作为过滤条件时，单行子查询使用单行运算符，多行子查询用多行运算符
将from后面的子查询当做一个table来用：
select*from(selectid,namefromclasses)swheres.idin(1,2);
当做条件来用：
select*fromstudentswheres.classes_idin(selectidfromclasses);
select*fromstudentswheres.classes_id=any(selectidfromclasses);
select*fromstudentswheres.classes_id>any(selectidfromclasses);
Ø操作符和函数
1、boolean只判断
select1istrue,0isfalse,nullisunknown;
select1isnotunknown,0isnotunknown,nullisnotunknown;
2、coalesce函数，返回第一个非null的值
selectcoalesce(null,1);
selectcoalesce(1,1);
selectcoalesce(null,1);
selectcoalesce(null,null);
3、当有2个或多个参数时，返回最大的那个参数值
selectgreatest(2,3);
selectgreatest(2,3,1,9,55,23);
selectgreatest('D','A','B');
4、Least函数，返回最小值，如果有null就返回null值
selectleast(2,0);
selectleast(2,0,null);
selectleast(2,10,22.2,35.1,1.1);
5、控制流函数
selectcase1when1then'is1'when2then'is2'else'none'end;
selectcasewhen1>2then'yes'else'no'end;
6、ascii字符串函数
selectascii('A');
selectascii('1');
7、二进制函数
selectbin(22);
8、返回二进制字符串长度
selectbit_length(11);
9、char将值转换成字符，小数取整四舍五入
selectchar(65);
selectchar(65.4);
selectchar(65.5);
selectchar(65.6);
selectchar(65,66,67.4,68.5,69.6,'55.5','97.3');
10、using改变字符集
selectcharset(char(0*65)),charset(char(0*65usingutf8));
11、得到字符长度char_length,character_length
selectchar_length('abc');
selectcharacter_length('eft');
12、compress压缩字符串、uncompress解压缩
selectcompress('abcedf');
selectuncompress(compress('abcedf'));
13、concat_ws分隔字符串
selectconcat_ws('#','first','second','last');
selectconcat_ws('#','first','second',null,'last');
Ø事务处理
动作
开始事务：starttransaction
提交事务：commit
回滚事务：rollback
设置自动提交：setautocommit1|0
atuoCommit系统默认是1立即提交模式；如果要手动控制事务，需要设置setautoCommit0;
这样我们就可以用commit、rollback来控制事务了。
在一段语句块中禁用autocommit而不是setautocommit
starttransaction;
select@result:=avg(age)fromtemp;
updatetempsetage=@resultwhereid=2;
select*fromtempwhereid=2;//值被改变
rollback;//回滚
select*fromtempwhereid=2;//变回来了
在此期间只有遇到commit、rollback，startTransaction的禁用autocommit才会结束。然后就恢复到原来的autocommit模式；
不能回滚的语句
有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，
和创建、取消或更改表或存储的子程序的语句。
您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，
则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能回滚事务的全部效果。
一些操作也会隐式的提交事务
如alter、create、drop、renametable、locktable、setautocommit、starttransaction、truncatetable等等,
在事务中出现这些语句也会提交事务的
事务不能嵌套事务
事务的保存点
SavepointpointName/RollbacktosavepointpointName
一个事务可以设置多个保存点，rollback可以回滚到指定的保存点，恢复保存点后面的操作。
如果有后面的保存点和前面的同名，则删除前面的保存点。
Releasesavepoint会删除一个保存点，如果在一段事务中执行commit或rollback，则事务结束，所以保存点删除。
SetTransaction设计数据库隔离级别
SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL
{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}
本语句用于设置事务隔离等级，用于下一个事务，或者用于当前会话。
在默认情况下，SETTRANSACTION会为下一个事务（还未开始）设置隔离等级。
如果您使用GLOBAL关键词，则语句会设置全局性的默认事务等级，
用于从该点以后创建的所有新连接。原有的连接不受影响。使用SESSION关键测可以设置默认事务等级，
用于对当前连接执行的所有将来事务。
默认的等级是REPEATABLEREAD全局隔离等级。
Ø注释
select1+1;#单行注释
select1+1;--单行注释
select1/*多行注释*/+1;
Ø基本数据类型操作
字符串
select'hello','"hello"','""hello""','hel''lo','\'hello';
select"hello","'hello'","''hello''","hel""lo","\"hello";
\n换行
select'This\nIs\nFour\nLines';
\转义
select'hello\world!';
select'hello\world!';
select'hello\\world!';
select'hello\'world!';
Ø设置数据库mode模式
SETsql_mode='ANSI_QUOTES';
createtablet(aint);
createtable"tt"(aint);
createtable"t""t"(aint);
craatetalbetab("a""b"int);
Ø用户变量
set@num1=0,@num2=2,@result=0;
select@result:=(@num1:=5)+@num2:=3,@num1,@num2,@result;
Ø存储过程
创建存储过程：
delimiter//
createprocedureget(outresultint)
begin
selectmax(age)intoresultfromtemp;
end//
调用存储过程：
callget(@temp);
查询结果：
select@temp;
删除存储过程：
dropprocedureget;
查看存储过程创建语句：
showcreateprocedureget;
select…into可以完成单行记录的赋值：
createproceduregetRecord(sidint)
begin
declarev_namevarchar(20)default'jason';
declarev_ageint;
declarev_sexbit;
selectname,age,sexintov_name,v_age,v_sexfromtempwhereid=sid;
selectv_name,v_age,v_sex;
end;
callgetRecord(1);
Ø函数
函数类似于存储过程，只是调用方式不同
例如：selectmax(age)fromtemp;
创建函数：
createfunctionaddAge(ageint)returnsint
returnage+5;
使用函数：
selectaddAge(age)fromtemp;
删除函数：
dropfunctionifexistsaddAge;
dropfunctionaddAge;
显示创建语法：
showcreatefunctionaddAge;
Ø游标
声明游标：declarecur_Namecursorforselectnamefromtemp;
打开游标：opencur_Name;
Fetch游标：fetchcur_Nameinto@temp;
关闭游标：closecur_Name;
示例：
CREATEPROCEDUREcur_show()
BEGIN
DECLAREdoneINTDEFAULT0;
DECLAREv_id,v_ageINT;
DECLAREv_namevarchar(20);
DECLAREcur_tempCURSORFORSELECTid,name,ageFROMtemp;
DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;
OPENcur_temp;
REPEAT
FETCHcur_tempINTOv_id,v_name,v_age;
IFNOTdoneTHEN
IFisnull(v_name)THEN
updatetempsetname=concat('test-json',v_id)whereid=v_id;
ELSEIFisnull(v_age)THEN
updatetempsetage=22whereid=v_id;
ENDIF;
ENDIF;
UNTILdoneENDREPEAT;
CLOSEcur_temp;
END
Ø触发器
触发器分为insert、update、delete三种触发器事件类型
还有after、before触发时间
创建触发器：
createtriggertrg_temp_ins
beforeinsert
ontempforeachrow
begin
insertintotemp_logvalues(NEW.id,NEW.name);
end//
删除触发器：
droptriggertrg_temp_ins
本文内容总结：
原文链接：https://www.cnblogs.com/hoojo/archive/2011/06/20/2085416.html