mysql的使用19

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> #rollup
mysql> #rollup的优点是一次取得n次group by的结果，这样可以提高查询的效率，同时减少了网络的传输流量
mysql>
mysql> select year(orderdate) as year, sum(qty) as sum from tttt
    -> group by year(orderdate) with rollup;
+------+------+
| year | sum  |
+------+------+
| 2009 |   30 |
| 2010 |  100 |
| 2011 |   90 |
| NULL |  220 |
+------+------+
4 rows in set (0.00 sec)

mysql> #最后是表示对所有的结果进行一次总和操作
mysql>
mysql> #游标（cursor）
mysql> #游标是一种面向过程的SQL编程方法，，，，
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| pp             |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| tttt           |
| updatetime     |
| x              |
| xx             |
| y              |
| yeartest       |
| yy             |
| z              |
+----------------+
29 rows in set (0.00 sec)

mysql> desc dept_manager;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| dept_no    | varchar(10) | NO   |     | NULL    |       |
| emp_no     | int(11)     | NO   |     | NULL    |       |
| birth_date | date        | YES  |     | NULL    |       |
| worktime   | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure dur_deom()
    -> begin
    ->  declare done int default 0;
    ->  declare _emp_no int;
    ->  declare _dept_no varchar(10);
    ->  declare curl cursor for select emp_no,dept_no from dept_manager;
    ->  declare continue handler for not found set done = 1;
    ->  open curl;
    ->  read_loop: LOOP
    ->  fetch cur1 into _emp_no,_dept_no;
    ->  if done then
    ->    leave read_loop;
    ->  end if;
    ->  end LOOP;
    ->  close curl;
    -> end;
    -> //
ERROR 1324 (42000): Undefined CURSOR: cur1
mysql> delimiter //
mysql> create procedure dur_deom()
    -> begin
    ->  declare done int default 0;
    ->  declare _emp_no int;
    ->  declare _dept_no varchar(10);
    ->  declare curl cursor for select emp_no,dept_no from dept_manager;
    ->  declare continue handler for not found set done = 1;
    ->  open curl;
    ->  read_loop: LOOP
    ->  fetch curl into _emp_no,_dept_no;
    ->  if done then
    ->    leave read_loop;
    ->  end if;
    ->  end LOOP;
    ->  close curl;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> #以上的过程就是创建一个游标的过程
mysql> delimiter //
mysql> call cur_demo()
    -> //
ERROR 1305 (42000): PROCEDURE test.cur_demo does not exist
mysql> delimiter ;
mysql> call cur_demo()
    -> ;
ERROR 1305 (42000): PROCEDURE test.cur_demo does not exist
mysql> delimiter ;
mysql> call cur_deom()
    -> ;
ERROR 1305 (42000): PROCEDURE test.cur_deom does not exist
mysql>
mysql>
mysql> delimiter ;
mysql> call dur_deom();
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> #尼麻   这是老眼昏花码。。。。。。
mysql>
mysql> #游标的开销主要是因为需要对每行进行处理，处理过程越复杂，游标的效率越低
mysql>
mysql> #是否要使用游标？ 如何正确的使用游标？ 怎样使用游标提高效率
mysql> #对于面向集合的SQL语句，如果该SQL 语句的扫面成本为o(n)那么使用游标不太可能带来性能上的提升，，但是如果扫描成本为o(n*n)的时候，游标或许带来性能上的提升
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| pp             |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| tttt           |
| updatetime     |
| x              |
| xx             |
| y              |
| yeartest       |
| yy             |
| z              |
+----------------+
29 rows in set (0.00 sec)

mysql> desc sessions;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| app       | varchar(10) | NO   | MUL | NULL    |                |
| usr       | varchar(10) | NO   |     | NULL    |                |
| starttime | time        | NO   |     | NULL    |                |
| endtime   | time        | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> #现在 我们看看session中最大会话数 是怎么面计算的 然后用游标计算
mysql>
mysql> select app, max(count) as max
    -> from (select app, s,
    -> (select count(1) from sessions as b
    ->  where a.app=b.app and s>=starttime and s<endtime) as count
    -> from (
    -> select distinct app, starttime as s from sessions) as a
    -> ) as bc
    -> group by app;
+------+------+
| app  | max  |
+------+------+
| app1 |    4 |
| app2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> #ok
mysql>
mysql> #这样根据每个session有两个状态satrt 和 end,可以使用下面的查询来生命游标
mysql>
mysql> select app, starttime astime,1 as type
    -> from sessions
    -> union all
    -> select app, endtime, -1
    -> from sessions
    -> order by app,time, type desc;
ERROR 1054 (42S22): Unknown column 'time' in 'order clause'
mysql>
mysql> select app, starttime as time,1 as type from sessions union all  select app, endtime, -1 from sessions  order by app,time, type desc;
+------+----------+------+
| app  | time     | type |
+------+----------+------+
| app1 | 08:30:00 |    1 |
| app1 | 08:30:00 |    1 |
| app1 | 08:45:00 |   -1 |
| app1 | 09:00:00 |    1 |
| app1 | 09:15:00 |    1 |
| app1 | 09:15:00 |    1 |
| app1 | 09:30:00 |   -1 |
| app1 | 09:30:00 |   -1 |
| app1 | 10:30:00 |    1 |
| app1 | 10:30:00 |   -1 |
| app1 | 10:30:00 |   -1 |
| app1 | 10:45:00 |    1 |
| app1 | 11:00:00 |    1 |
| app1 | 11:30:00 |   -1 |
| app1 | 12:30:00 |   -1 |
| app1 | 14:30:00 |   -1 |
| app2 | 08:30:00 |    1 |
| app2 | 08:45:00 |   -1 |
| app2 | 09:00:00 |    1 |
| app2 | 09:30:00 |   -1 |
| app2 | 11:45:00 |    1 |
| app2 | 12:00:00 |   -1 |
| app2 | 12:30:00 |    1 |
| app2 | 12:45:00 |    1 |
| app2 | 13:00:00 |    1 |
| app2 | 13:30:00 |   -1 |
| app2 | 14:00:00 |    1 |
| app2 | 14:00:00 |   -1 |
| app2 | 14:00:00 |   -1 |
| app2 | 15:30:00 |    1 |
| app2 | 16:30:00 |   -1 |
| app2 | 17:00:00 |   -1 |
+------+----------+------+
32 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _MAX int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),max int)endine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,max
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'endine=memory;

 open curl;
 fetch curl into _app, _time, _type;
 set _prev_app ' at line 21
mysql>
mysql>
mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _MAX int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),max int)engine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,max
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set _prev_app = _app;
       set _max = 0;
       set _current = 0;
   end if;

' at line 33
mysql>
mysql>
mysql>
mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _max int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),max int)engine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,max;
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current;
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> #ok  failuer is the mather of success;
mysql>
mysql> delimiter ;
mysql> call cur_max_sessions();
ERROR 1054 (42S22): Unknown column 'max' in 'field list'
mysql>
mysql>
mysql> drop procedure cur_max_sessions();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
mysql> drop procedure cur_max_sessions;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _max int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),maxInt int)engine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,max;
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current;
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call procedure_max_sessions();
ERROR 1305 (42000): PROCEDURE test.procedure_max_sessions does not exist
mysql>
mysql> delimiter ;
mysql> call cur_max_sessions();
ERROR 1054 (42S22): Unknown column 'max' in 'field list'
mysql>
mysql> drop procedure cur_max_sessions;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _max int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),maxInt int)engine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,max;
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current;
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call cur_max_sessions();
ERROR 1054 (42S22): Unknown column 'max' in 'field list'
mysql>
mysql> #没有max啊，，，why？
mysql>
mysql>
mysql> #ok  我看见了
mysql>
mysql> #再来一次
mysql>
mysql> deop procedure cur_max_sessions;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deop procedure cur_max_sessions' at line 1
mysql>
mysql> drop procedure cur_max_sessions;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter //
mysql> create procedure cur_max_sessions()
    -> begin
    ->  declare done int default 0;
    ->  declare _app varchar(10);
    ->  declare _prev_app varchar(10);
    ->  declare _time time;
    ->  declare _type int;
    ->  declare _current int;
    ->  declare _max int;
    ->  declare curl cursor for
    ->    select app, starttime as time, 1 as type
    ->    from sessions
    ->    union all
    ->    select app, endtime, -1
    ->    from sessions
    ->    order by app, time, type desc;
    ->
    ->  declare continue handler for not found set done = 1;
    ->  
    ->  drop temporary table if exists ret;
    ->  create temporary table ret( app varchar(10),maxInt int)engine=memory;
    ->
    ->  open curl;
    ->  fetch curl into _app, _time, _type;
    ->  set _prev_app = _app;
    ->  set _current = 0;
    ->  set _max = 0;
    ->
    ->  while done = 0 do
    ->  begin
    ->    if _prev_app <> _app  then
    ->        insert into ret select _prev_app,_max;
    ->        set _prev_app = _app;
    ->        set _max = 0;
    ->        set _current = 0;
    ->    end if;
    ->
    ->    set _current = _current+_type;
    ->    if _current > _max then
    ->       set _max = _current;
    ->    end if;
    ->
    ->    fetch curl into _app, _time, _type;
    ->  end;
    -> end while;
    ->
    ->
    ->  if _prev_app is not null then
    ->      insert into ret select _prev_app, _max;
    ->  end if;
    ->
    ->  select * from ret;
    ->  drop table ret;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call cur_max_sessions;
+------+--------+
| app  | maxInt |
+------+--------+
| app1 |      4 |
| app2 |      3 |
+------+--------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> #ok  ou 了
mysql> #就讲到这里了
mysql>
mysql>
mysql> xit
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xit' at line 1
mysql> exit