mysql数据库的使用:从安装到使用。

来源：互联网发布：java tochararray 编辑：程序博客网时间：2024/05/29 01:55

存储过程：
为了实现某一个特殊的功能，将很多的sql语句拼接到一起，也就是一个sql语句的集合。

mysql> delimiter //    -> begin    -> select * from student1;    -> select * from student3;    -> end    -> //Query OK, 0 rows affect调用过程

mysql> call pro1();
-> //
ERROR 1146 (42S02): Table ‘students.student1’ doesn’t exist

定义和调用过程

mysql> delimiter $$mysql> create procedure pro2(n int)    -> begin    -> select * from student3 where age<n;    -> end    -> $$Query OK, 0 rows affected (0.00 sec)mysql> call pro2(20);    -> $$+------+------+------+------+-------+-------+---------+-------+| 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   |+------+------+------+------+-------+-------+---------+-------+3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

局部变量:
局部变量一般用在sql语句块中，比如定义在存储过程的begin/end，其作用仅限于该语句，

格式:declare var name1 [,var_name…..] data_type[default value];

Query OK, 0 rows affected (0.00 sec)##将执行方式改为义$$符号结尾的。mysql> delimiter $$mysql> create procedure pro3(a int ,b int)    -> begin    -> declare c int default 0;    -> set c=a+b;    -> select c as C_value;    -> end    -> $$Query OK, 0 rows affected (0.00 sec)    mysql> call pro3(1,3);    +---------+    | C_value |    +---------+    |       4 |    +---------+    1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

或者使用select

mysql> delimiter $$                                                                                                                            mysql> create procedure pro5() begin declare v_age int; declare v_name varchar(20); select age,name into v_age,v_name  from student3 where code =2;  select v_age,v_name; end $$Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;mysql> call pro4();ERROR 1305 (42000): PROCEDURE students.pro4 does not existmysql> call pro5();+-------+--------+| v_age | v_name |+-------+--------+|    15 | xxc    |+-------+--------+1 row in set (0.03 sec)Query OK, 0 rows affected (0.03 se

产看

mysql> show procedure status where db="students";#查看所有的存储过程+----------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db       | Name | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |+----------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| students | pro0 | PROCEDURE | root@localhost | 2018-01-07 14:05:57 | 2018-01-07 14:05:57 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  || students | pro1 | PROCEDURE | root@localhost | 2018-01-07 13:38:27 | 2018-01-07 13:38:27 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  || students | pro2 | PROCEDURE | root@localhost | 2018-01-07 13:48:20 | 2018-01-07 13:48:20 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  || students | pro3 | PROCEDURE | root@localhost | 2018-01-07 13:55:09 | 2018-01-07 13:55:09 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  || students | pro5 | PROCEDURE | root@localhost | 2018-01-07 14:19:03 | 2018-01-07 14:19:03 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |+----------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+5 rows in set (0.08 sec)

删除

mysql> drop procedure pro4;Query OK, 0 rows affected (0.06 sec)

用户变量:在客户端连接到数据库实例的过程中用户变量都是有小的，
不需要实现声明

第一:set @num=1 或者 set @num:=1;
第二:select @num:=1 ;或者select @num:=字段名 (id) from 表名

mysql> set @num=1;Query OK, 0 rows affected (0.00 sec)mysql> select @num;+------+| @num |+------+|    1 |+------+1 row in set (0.00 sec)mysql> set @num:=2    -> ;Query OK, 0 rows affected (0.00 sec)mysql> select @num;+------+| @num |+------+|    2 |+------+1 row in set (0.00 sec)mysql>

mysql> select @num:=4;+---------+| @num:=4 |+---------+|       4 |+---------+1 row in set (0.00 sec)

mysql> select @num:=age from student3 where code=1;+-----------+| @num:=age |+-----------+|        13 |+-----------+1 row in set (0.00 sec)

写一个小的数据块:

mysql> delimiter &&  #设置结束标志为&&                                                                                                                          mysql> create procedure pro6(a int ,b int )      begin          #程序开始       declare c int default 0;       set @var1=143; #用户变量       set @var2=34;  #用户变量      set c=a+b;     #局部变量      set @d=c;      #僵局不变量赋给用户变量      select @sum:=(@var1+@var2)as sum,      @dif:=(@var1-@var2)as dif,@d as D;       set c=100;           select c as C;       end      &&            #程序到此结束Query OK, 0 rows affected (0.00 sec)

函数的调用:

    mysql> delimiter ;    mysql> call pro6(1,2);    +------+------+------+    | sum  | dif  | D    |    +------+------+------+    |  177 |  109 |    3 |    +------+------+------+    1 row in set (0.00 sec)    +------+    | C    |    +------+    |  100 |    +------+    1 row in set (0.00 sec)    Query OK, 0 rows affected (0.00 sec)

创建含有参数的存储过程-out
该值可在存储过程内部
这个值只能作输出，不能修改它的值

创建一个数据块

 create procedure pro8(out a int)  begin   select a;   set a=10;   select a;   end   &&Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;mysql> set @a=50;Query OK, 0 rows affected (0.00 sec)

mysql> call pro8(@a);+------+| a    |+------+| NULL |+------+1 row in set (0.00 sec)+------+| a    |+------+|   10 |+------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select @a;+------+| @a   |+------+|   10 |+------+1 row in set (0.00 sec)mysql>

**@一个小的数据块

mysql> delimiter &&                                                                                                                            mysql> create procedure pro10(out minage int,out maxage int,out avgage decimal(8,2)) begin  select min(age) from student3 into minage; select max(age) from student3 into maxage; select avg(age) from student3 into avgage;  end &&Query OK, 0 rows affected (0.00 sec)mysql> delimiter  ;mysql> call pro10(@minage,@maxage,@avgage);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select @minage,@maxage,@avgage;+---------+---------+---------+| @minage | @maxage | @avgage |+---------+---------+---------+|       9 |      78 |   35.71 |+---------+---------+---------+1 row in set (0.00 sec)

创建含有参数的存储过程—-in
表示该参数的值必须在调用存储时指定，在存储过程中修改该参数的值不能返回，为默认值，只能接收值的传入，不能接受值得传出

mysql> delimiter &&mysql> create procedure pro11(in a int)    -> begin    -> select a;    -> set a=10;    -> select a;    -> end    -> &&Query OK, 0 rows affected (0.02 sec)mysql> delimiter ;mysql> set @a=20;Query OK, 0 rows affected (0.00 sec)

执行过程

mysql> call pro11(@a);+------+| a    |+------+|   20 |+------+1 row in set (0.00 sec)+------+| a    |+------+|   10 |+------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select @a;+------+|   @a |+------+|   20 |+------+1 row in set (0.00 sec)

默认参数

mysql> delimiter &&mysql> create procedure pro12(a int) begin select a; set a=10; select a; end&&Query OK, 0 rows affected (0.01 sec)mysql> set @a=20;    -> &&Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call pro12(@a);+------+| a    |+------+|   20 |+------+1 row in set (0.02 sec)+------+| a    |+------+|   10 |+------+1 row in set (0.02 sec)Query OK, 0 rows affected (0.02 sec)mysql> select @a;+------+| @a   |+------+|   20 |+------+1 row in set (0.00 sec)mysql>

此处为inout a int 变名函数参数，既能被传进，又能被传出

mysql> delimiter $$mysql> create procedure pro13(inout a int)    -> begin    -> select a;    -> set a=10;    -> select a;    -> end    -> $$Query OK, 0 rows affected (0.00 sec)mysql> delimiter  ;mysql> set @a =20;Query OK, 0 rows affected (0.00 sec)mysql> call pro13(@a);+------+| a    |+------+|   20 |+------+1 row in set (0.00 sec)+------+| a    |+------+|   10 |+------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select @a;+------+| @a   |+------+|   10 |+------+1 row in set (0.00 sec)mysql>

举例:录入学生
触发器:
是一个特殊的存储过程，不同的是存储过程要用call来调用，但触发器不需要使用call,也不需要手动启动，只要当一个预订义事件发生的时候，就会被mysql自动调用

定一格式:
create trigger trigger_name trigger_time trigger_event on table_name for each row trigger body

注:
trigger_name 触发器名称
trigger_time:{before | after}
表示在事件发生之前触发，还是在事件发生后触发
trigger
table_name :该触发器作用在table_name上
trigger_body:是程序触发时执行的语句，如果是多条可以用
begin和end符合语句的结构

#创建第一张表mysql> create table student_info(    -> stu_no int not null auto_increment,    -> stu_name varchar(30) default null,    -> primary key(stu_no))default charset=utf8;Query OK, 0 rows affected (0.48 sec)#创建第二张表mysql> create table student_count(    -> student_count int default 0)default charset=utf8;Query OK, 0 rows affected (0.24 sec)mysql> insert into student_count values(0);Query OK, 1 row affected (0.07 sec)#创建添加触发器mysql> create trigger trigger_student_count_insert  after insert on student_info  for each row update student_count set student_count=student_count+1;Query OK, 0 rows affected (0.08 sec)#创建删除触发器mysql> create trigger trigger_student_count_delete    -> after delete    -> on student_info for each row    -> update student_count set student_count=student_count-1;Query OK, 0 rows affected (0.10 sec)mysql> insert into student_info(stu_name) values("张三"),("李四"),("王五"),("麻子");Query OK, 4 rows affected (0.11 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from student_count;+---------------+| student_count |+---------------+|             4 |+---------------+1 row in set (0.00 sec)mysql> delete from student_info where stu_name in("张三","李四");Query OK, 2 rows affected (0.05 sec)mysql> select *  from student_count;+---------------+| student_count |+---------------+|             2 |+---------------+1 row in set (0.00 sec)

mysql> show triggers;+------------------------------+--------+--------------+--------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+| Trigger                      | Event  | Table        | Statement                                              | Timing | Created                | sql_mode                                                                                                                                  | Definer        | character_set_client | collation_connection | Database Collation |+------------------------------+--------+--------------+--------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+| trigger_student_count_insert | INSERT | student_info | update student_count set student_count=student_count+1 | AFTER  | 2018-01-07 16:13:26.17 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  || trigger_student_count_delete | DELETE | student_info | update student_count set student_count=student_count-1 | AFTER  | 2018-01-07 16:14:37.61 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |+------------------------------+--------+--------------+--------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+2 rows in set (0.04 sec)

删除触发器:

mysql> drop trigger trigger_student_count_insert;Query OK, 0 rows affected (0.07 sec)mysql> drop trigger trigger_student_count_delete;Query OK, 0 rows affected (0.03 sec)

mysql> create table student_info1( stu_no int not null auto_increment,  stu_name varchar(30) default null,   stu_class int, primary key(stu_no))default    charset=utf8;Query OK, 0 rows affected (0.53 sec)mysql> create table student_count1(    -> student_count int default 0,    -> student_class int default 0)default charset=utf8;Query OK, 0 rows affected (0.37 sec)mysql> insert into student_count1 values(0,0),(0,1),(0,2);Query OK, 3 rows affected (0.04 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from student_count1    -> ;+---------------+---------------+| student_count | student_class |+---------------+---------------+|             0 |             0 ||             0 |             1 ||             0 |             2 |+---------------+---------------+3 rows in set (0.00 sec)#创建一个触发器(插入)mysql> delimiter $$                                                                                                                            mysql> create trigger trigger_student_count_insert1 after insert on student_info1 for each row  begin  update student_count1 set        student_count=student_count+1 where student_class=0;     -> update student_count1 set student_count=student_count+1 where student_class=NEW.stu_class;#NEW表示当前正在插入的数据    -> end    -> $$

mysql> delimiter $$mysql> create trigger trigger_student_count_delete1    -> after delete on student_info1 for each row    -> begin    -> update student_count1 set student_count=student_count-1 where student_class=0;    -> update student_count1 set student_count=student_count-1 where student_class=OLD.stu_class;    -> end    -> $$Query OK, 0 rows affected (0.09 sec)mysql> select * from student_count1;    -> $$+---------------+---------------+| student_count | student_class |+---------------+---------------+|             0 |             0 ||             0 |             1 ||             0 |             2 |+---------------+---------------+3 rows in set (0.00 sec)## 插入数据mysql> insert into student_info1(stu_name,stu_class)    -> values('AAA',1),('BBB',1),('CCC',2),('DDD',2),('CCC',1);    -> $$Query OK, 5 rows affected (0.08 sec)Records: 5  Duplicates: 0  Warnings: 0##查看mysql> select *from student_info1;    -> $$+--------+----------+-----------+| stu_no | stu_name | stu_class |+--------+----------+-----------+|      1 | AAA      |         1 ||      2 | BBB      |         1 ||      3 | CCC      |         2 ||      4 | DDD      |         2 ||      5 | CCC      |         1 |+--------+----------+-----------+5 rows in set (0.00 sec)##查看mysql> select * from student_count1;    -> $$+---------------+---------------+| student_count | student_class |+---------------+---------------+|             5 |             0 ||             3 |             1 ||             2 |             2 |+---------------+---------------+3 rows in set (0.00 sec)

问题:

为什么插入数据会一直重复

mysql> select * from student_count1;+---------------+---------------+| student_count | student_class |+---------------+---------------+|             6 |             0 ||             3 |             1 ||             2 |             2 ||             0 |             3 ||             1 |             4 ||             0 |             5 |+---------------+---------------+6 rows in set (0.00 sec)mysql> select * from student_info1;+--------+----------+-----------+| stu_no | stu_name | stu_class |+--------+----------+-----------+|      1 | AAA      |         1 ||      2 | BBB      |         1 ||      3 | CCC      |         2 ||      4 | DDD      |         2 ||      5 | CCC      |         1 ||     10 | TTT      |         4 |+--------+----------+-----------+6 rows in set (0.00 sec)

阅读全文

0 0