mysql学习之四

游标:

一般，select 这些查询语句都是对一行记录而言，如果想查询多行记录的值，则需要使用游标或者是while()等循环

游标实际上是一个能够从包括多条数据记录的结果集中每次提取一条记录的机制，游标充当指针的作用，尽管游标能够遍历所有的行，但是每次只能遍历一次
使用游标的步骤:

1.必须先声明  declare 游标名  cursor  for select_statement;2.打开游标  open  游标名3.通过游标根据需要取出数据  fetch 游标名 into var1,var2,var[....]4.使用完必须关闭游标  close  游标名

mysql> create procedure p2()  begin declare row_code int ;  declare row_name varchar(20);  declare row_age int;  declare row_sex varchar(10); declare row_score int; declare row_grade int; declare row_address varchar(20); declare row_major varchar(20);  declare leveler cursor for select code,name,age,sex,score,grade,address,major from  student3; open leveler; fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; select    row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; close leveler;end $$Query OK, 0 rows affected (0.00 sec)mysql> call p2();    -> $$+----------+----------+---------+---------+-----------+-----------+-------------+-----------+| row_code | row_name | row_age | row_sex | row_score | row_grade | row_address | row_major |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+|        1 | xxx      |      13 | m       |        78 |         1 | asdfg       | sss       |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+1 row in set (0.00 sec)+----------+----------+---------+---------+-----------+-----------+-------------+-----------+| row_code | row_name | row_age | row_sex | row_score | row_grade | row_address | row_major |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+|        2 | xxc      |      15 | w       |        88 |         2 | afdhd       | nnn       |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+1 row in set (0.00 sec)+----------+----------+---------+---------+-----------+-----------+-------------+-----------+| row_code | row_name | row_age | row_sex | row_score | row_grade | row_address | row_major |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+|        3 | cxx      |       9 | m       |        99 |         3 | sdsvfbf     | vvv       |+----------+----------+---------+---------+-----------+-----------+-------------+-----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

 create procedure p5()  begin declare row_code int ; declare row_name varchar(20); declare row_age int; declare row_sex varchar(10); declare row_score int; declare row_grade int; declare row_address varchar(20); declare row_major varchar(20); declare cnt int default 0;declare i int default 0;declare leveler cursor for select code,name,age,sex,score,grade,address,major from  student3;select count(*) into cnt from student3; open leveler;repeat set i:=i+1;fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;until i>=cnt end repeat ;close leveler; end $$Query OK, 0 rows affected (0.00 sec)mysql> call p3();    -> $$+----------+----------+---------+---------+-----------+-----------+-------------+-----------+|----------+-------------+-----------+1 row in set (0.00 sec)+----------+----------+---------+---------+-----------+--+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)在mysql游标中，可以定义continue handler 来操作一个越界标志，使用语法：   declare continue handler for NOT FOUND statment当内有数据的时候要执行的语句:continue handler create procedure p8()  begin  declare row_code int;  declare row_name varchar(20);   declare row_age int;  declare row_sex varchar(10);  declare row_score int; declare row_grade int;   declare row_address varchar(20);  declare row_major varchar(20);  declare go_on int default 1;  declare leveler cursor for select code,name,age,sex,score,grade,address,major from student3;   declare continue handler for NOT FOUND set go_on:=0; open leveler; repeat fetch leveler into row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major;   if(go_on=1)  then select row_code,row_name,row_age,row_sex,row_score,row_grade,row_address,row_major; end if; until go_on=0 end repeat; close leveler;  end $$注:游标在获取数据的时候十一行一行向下获取的，获取到最后一行，会继续向下获取，但是这个时候会触发越界标志，如果在打印则会获取上一次的结果，会出现多打印一行的情况。//1.注意在这里不能同名

create procedure pc11()  begin  declare go_on int default 1;  declare sum int default 0;  declare row_name varchar(20);   declare row_count int; declare leveler cursor for select name,count from store where name='iphone';  declare continue handler for NOT FOUND set go_on:=0; open leveler; repeat fetch leveler into row_name,row_count;   if(go_on=1)  then select row_count,row_name; set sum=sum+row_count; end if; until go_on=0 end repeat; select sum; close leveler; end $$

//2.在这里给不同的if语句的判断

create procedure pc13()  begin  declare go_on int default 1;  declare sum int default 0;  declare row_name varchar(20);   declare row_count int; declare leveler cursor for select name,count from store ;  declare continue handler for NOT FOUND set go_on:=0; open leveler; repeat fetch leveler into row_name,row_count;   if(go_on=1&&row_name='iphone')  then select row_count,row_name; set sum=sum+row_count; end if; until go_on=0 end repeat; select sum; close leveler; end $$

3.

create procedure pc20()  begin  declare go_on int default 1;  declare sum int default 0;  declare row_name varchar(20);   declare row_count int; declare leveler cursor for select name,count from store  where name='iphone' ;  declare continue handler for NOT FOUND set go_on:=0; open leveler; read_loop:loop fetch leveler into row_name,row_count; if(go_on=0) then  #0=done        leave read_loop;#结束循环  end if; set sum=sum+row_count; end loop; close leveler; select sum; end $$

4.

create procedure pc16()  begin  declare go_on int default 1;  declare sum int default 0;  declare row_name varchar(20);   declare row_count int; declare leveler cursor for select name,count from store  where name='iphone' ;  declare continue handler for NOT FOUND set go_on:=0; open leveler;    fetch leveler into row_name,row_count;  while(go_on=1) do     set sum=sum+row_count;     fetch leveler into row_name,row_count;   end while; close leveler; select sum; end $$

mysql里面的函数:
mysql数据库函数

mysql> select abs(-1);    -> $$+---------+| abs(-1) |+---------+|       1 |+---------+1 row in set (0.00 sec)mysql> select pi();+----------+| pi()     |+----------+| 3.141593 |+----------+1 row in set (0.00 sec)mysql> select sqrt(16);+----------+| sqrt(16) |+----------+|        4 |+----------+1 row in set (0.00 sec)mysql> select mod(5,3);+----------+| mod(5,3) |+----------+|        2 |+----------+1 row in set (0.00 sec)mysql> select rand();  ## 0-1的随机数+---------------------+| rand()              |+---------------------+| 0.21902671942396715 |+---------------------+1 row in set (0.00 sec)mysql> select round(10.5);#最近的整数，对x进行四设+-------------+| round(10.5) |+-------------+|          11 |+-------------+1 row in set (0.00 sec)mysql> select round(10.1253,2);#保留两位小数，最后一位四舍五入+------------------+| round(10.1253,2) |+------------------+|            10.13 |+------------------+1 row in set (0.01 sec) 返回一个数的符号位:整数 1,负数:-1,0:mysql> select sign(-1);+----------+| sign(-1) |+----------+|       -1 |+----------+1 row in set (0.00 sec)mysql> select sign(1);+---------+| sign(1) |+---------+|       1 |+---------+1 row in set (0.00 sec)mysql> select sign(0);+---------+| sign(0) |+---------+|       0 |+---------+1 row in set (0.00 sec)求2的3次方mysql> select Pow(2,3);+----------+| Pow(2,3) |+----------+|        8 |+----------+1 row in set (0.00 sec)mysql> select Power(2,3);+------------+| Power(2,3) |+------------+|          8 |+------------+1 row in set (0.00 sec)

默认的是

e的n次方mysql> select exp(2);+------------------+| exp(2)           |+------------------+| 7.38905609893065 |+------------------+1 row in set (0.00 sec)2的自然对数mysql> select log(2);+--------------------+| log(2)             |+--------------------+| 0.6931471805599453 |+--------------------+1 row in set (0.00 sec)

##计算以10为底的对数mysql> select log2(2);+---------+| log2(2) |+---------+|       1 |+---------+1 row in set (0.00 sec)

字符串函数:     mysql> select length("asfg");+----------------+| length("asfg") |+----------------+|              4 |+----------------+1 row in set (0.00 sec)mysql> select char_length("asdgfas");+------------------------+| char_length("asdgfas") |+------------------------+|                      7 |+------------------------+1 row in set (0.00 sec)mysql> select concat("leige","999");+-----------------------+| concat("leige","999") |+-----------------------+| leige999              |+-----------------------+1 row in set (0.00 sec)//将多个字符川以第一个字符连接起来mysql> select concat_ws(":","磊哥说","你好");+-------------------------------------+| concat_ws(":","磊哥说","你好")      |+-------------------------------------+| 磊哥说:你好                         |+-------------------------------------+1 row in set (0.00 sec)upppermysql> select ucase("asdsa");+----------------+| ucase("asdsa") |+----------------+| ASDSA          |+----------------+1 row in set (0.00 sec)mysql> select upper("asdsafasdf");+---------------------+| upper("asdsafasdf") |+---------------------+| ASDSAFASDF          |+---------------------+1 row in set (0.00 sec)+---------------+| lcase("ASDD") |+---------------+| asdd          |+---------------+1 row in set (0.00 sec)mysql> select lower("ASDD");+---------------+| lower("ASDD") |+---------------+| asdd          |+---------------+1 row in set (0.00 sec)重复打印三次mysql> select repeat("xxx",3);+-----------------+| repeat("xxx",3) |+-----------------+| xxxxxxxxx       |+-----------------+1 row in set (0.00 sec)mysql> select strcmp("aaa","aaa");+---------------------+| strcmp("aaa","aaa") |+---------------------+|                   0 |+---------------------+1 row in set (0.00 sec)mysql> select strcmp("asa","aaa");+---------------------+| strcmp("asa","aaa") |+---------------------+|                   1 |+---------------------+1 row in set (0.00 sec)mysql> select strcmp("qsa","zaa");+---------------------+| strcmp("qsa","zaa") |+---------------------+|                  -1 |+---------------------+1 row in set (0.00 sec)

mysql> select substring("asdfasdfa",3,5);mysql> select mid("asdfasdfa",3,5);+----------------------+| mid("asdfasdfa",3,5) |+----------------------+| dfasd                |+----------------------+1 row in set (0.00 sec)//将字符串按倒序的顺序输出mysql> select reverse("asd");+----------------+| reverse("asd") |+----------------+| dsa            |+----------------+1 row in set (0.00 sec)//过去当前的时间mysql> select curdate();+------------+| curdate()  |+------------+| 2018-01-08 |+------------+1 row in set (0.00 sec)mysql> select current_date();+----------------+| current_date() |+----------------+| 2018-01-08     |+----------------+1 row in set (0.00 sec)mysql> select current_time();+----------------+| current_time() |+----------------+| 16:14:29       |+----------------+1 row in set (0.00 sec)mysql> select curr_time();mysql> select curtime();+-----------+| curtime() |+-----------+| 16:14:40  |+-----------+1 row in set (0.00 sec)now()mysql> select now();+---------------------+| now()               |+---------------------+| 2018-01-08 16:16:19 |+---------------------+1 row in set (0.00 sec)

条件判断函数if(expr,v1,v2):如果表达式expr成立,  则返回一个goodmysql> select code,name,if(score>=90,"food","so-so") from student3;+------+------+------------------------------+| code | name | if(score>=90,"food","so-so") |+------+------+------------------------------+|    1 | xxx  | so-so                        ||    2 | xxc  | so-so                        ||    3 | cxx  | food                         ||    4 | ccc  | so-so                        ||    5 | xbb  | so-so                        ||    6 | ppp  | so-so                        ||    7 | lll  | so-so                        |+------+------+------------------------------+7 rows in set (0.00 sec)

自定义函数:
语法:
create function function_name(参数列表);
returns 返回值类型
func_body
注:
1.返回值类型，一定要有return 返回值语句
2.关于函数体
函数提示有合法的sql语句组成
如果是符合语句组成使用begin …end
查看自定义:show function status;
删除函数的语句:drop function function_name

注释:单行 #
多行:/* ….*/

create function assUser(username varchar(20),userage int) returns intbegin  insert into stu(name,age) values(username,userage); return (select max(age) from stu); end $$mysql> select assUser("xxx",14);    -> $$+-------------------+| assUser("xxx",14) |+-------------------+|                33 |+-------------------+1 row in set (0.06 sec)mysql> select * from stu;    -> $$+------+-----------+------+------+| id   | name      | age  | sex  |+------+-----------+------+------+|    1 | 张无忌    |   26 | 男   ||    2 | 乔峰      |   33 | 男   ||    3 | 独孤      |   18 | 男   ||    5 | 风清扬    |   22 | 男   || NULL | xxx       |   14 | NULL || NULL | xxx       |   14 | NULL |+------+-----------+------+------+6 rows in set (0.00 sec)

 create function fun1(str varchar(10),num int)  returns int begin case str when 'power' then set @result=power(num,2);  when 'sqrt' then set @result=sqrt(num);  when 'mod' then set @result = mod(num,2);  when 'round' then set @result = round(num);  else set @result=-1;  end case;  return (select @result);  end $$mysql> select fun1("power",2);ysql> select fun1("sqrt",2);mysql> select fun1("",1);

什么是视图:
视图是由查询结果形成的一张虚拟的表，与包含数据的表不一样
什么时候用到视图
如果某个查询结果出现非常频繁，也就是经常使用这个查询结果

怎么创建
create view 视图名

mysql> select * from stu;    -> $$+------+-----------+------+------+| id   | name      | age  | sex  |+------+-----------+------+------+|    1 | 张无忌    |   26 | 男   ||    2 | 乔峰      |   33 | 男   ||    3 | 独孤      |   18 | 男   ||    5 | 风清扬    |   22 | 男   |+------+-----------+------+------+4 rows in set (0.00 sec)mysql> create view test_view as select * from stu;    -> $$Query OK, 0 rows affected (0.05 sec)mysql> select * from test_view;    -> $$+------+-----------+------+------+| id   | name      | age  | sex  |+------+-----------+------+------+|    1 | 张无忌    |   26 | 男   ||    2 | 乔峰      |   33 | 男   ||    3 | 独孤      |   18 | 男   ||    5 | 风清扬    |   22 | 男   |+------+-----------+------+------+4 rows in set (0.00 sec)

直接对视图进行select 语句查询和

mysql> alter view test_view as select * from student3;    -> $$Query OK, 0 rows affected (0.05 sec)mysql> select * from test_view;    -> $$+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    6 | ppp  |   78 | m    |    78 |     1 | fgh     | vvv   ||    7 | lll  |   56 | w    |    78 |     3 | ddd     | aaa   |+------+------+------+------+-------+-------+---------+-------+7 rows in set (0.00 sec)ysql> select * from test_view;    -> $$+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    6 | ppp  |   78 | m    |    78 |     1 | fgh     | vvv   ||    7 | lll  |   56 | w    |    78 |     3 | ddd     | aaa   |+------+------+------+------+-------+-------+---------+-------+7 rows in set (0.00 sec)

//表与视图之间一直保持着联系，更动表会视图也会发生改变

mysql> insert into student3(name,age) values("4ee",7);$$Query OK, 1 row affected (0.05 sec)mysql> select * from student3;    -> $$+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    6 | ppp  |   78 | m    |    78 |     1 | fgh     | vvv   ||    7 | lll  |   56 | w    |    78 |     3 | ddd     | aaa   ||    8 | 4ee  |    7 | NULL |  NULL |  NULL | 未知    | NULL  |+------+------+------+------+-------+-------+---------+-------+8 rows in set (0.00 sec)mysql> select * from test_view;    -> $$+------+------+------+------+-------+-------+---------+-------+| code | name | age  | sex  | score | grade | address | major |+------+------+------+------+-------+-------+---------+-------+|    1 | xxx  |   13 | m    |    78 |     1 | asdfg   | sss   ||    2 | xxc  |   15 | w    |    88 |     2 | afdhd   | nnn   ||    3 | cxx  |    9 | m    |    99 |     3 | sdsvfbf | vvv   ||    4 | ccc  |   45 | m    |    56 |     1 | cdfsff  | sss   ||    5 | xbb  |   34 | w    |    66 |     1 | cdfsff  | sss   ||    6 | ppp  |   78 | m    |    78 |     1 | fgh     | vvv   ||    7 | lll  |   56 | w    |    78 |     3 | ddd     | aaa   ||    8 | 4ee  |    7 | NULL |  NULL |  NULL | 未知    | NULL  |+------+------+------+------+-------+-------+---------+-------+8 rows in set (0.00 sec)mysql> 

查看视图:

删除视图: drop view view_name(图形);show table status where comment='view';

使用视图的好处:
1.简化查询语句;
2.可以进行权限的控制;把表的权限封闭，但只能开放相应的视图权限
3.

create procedure pc()  begin declare go_on int default 1; declare j int default 1; declare leveler cursor for select name,count from store;  declare continue handler for NOT FOUND set go_on:=0; open leveler; if(go_on=1)  then end if; until go_on=0 end repeat; select j; close leveler; end $$