mysql成长之路_MySQL成长之路(004)

一、系统变量：所有的用户都能使用的变量

MySQL也是一种编程语言，可以使用变量！

MySQL中变量分为三类：①系统变量 ；②会话变量；③局部变量

1. 系统变量：

概念：它是所有的用户都能使用的变量，是MySQL系统自带的变量，也成为全局变量。

2.显示：使用global关键字特指系统变量

-- 显示从服务端读取到的系统变量的值!

show  variables like 'autocommit'；

-- 显示系统变量值:

show global variables like 'autocommit'；

show global variables like 'auto_increment%'；

使用global关键字特指，系统变量，否则容易把用户的会话变量也显示。

3.修改

-- 修改系统变量:

set autocommit = on；

修改的是客户端本地保存的从服务端读取到的系统变量的值!修改的是本地的副本，对其它的客户端不生效。

只在当前用户,当前会话生效,一旦重新登录,则会重新读取!

--修改系统变量的值set global autocommit = on；

set @@global.autocommit = on；

变量前加global修饰，表示修改的是服务端的变量；

使用情景：服务端的变量新登录的客户端可以使用！

二、会话变量：针对当前客户端自己定义的变量

服务端不能看到，其它的客户端也不能使用！

只对当前用户的本次登录有效；同一个客户端，重新登录也会失效；

1.格式：

set   @变量名   =  ‘值’ ；

-----定义会话变量

set @name ：= 'tom'；

select @name；

只对本地登录有效，当下一次登录时：

说明：会话变量没有类型要求，MySQL会根据值的类型自动推断。

2.会话变量的用途：

一般是从表中查询数据，将查询的结果保存在会话变量中。

3.会话的使用方式：

第一种：

说明：①专门的会话变量进行赋值的符号是“：=”

②不使用专门的赋值语句，容易被查询语句理解为比较语句！就是只用等号。

select @name := name,@gender := gender from stu2 limit 1；

第二种：

说明：不显示赋值的过程

第一步：select name,gender from stu2 limit 1 into @name,@gender；

第二步：select @name,@gender；

三、局部变量：

(用在 函数/存储过程/触发器)

1. if关键字

格式：if(条件，值1，值2)

2.对某列值进行判断

select * ,if(mark >= 100,'优秀','一般') as '评分' from student ;

进行等值判断后会在表格后方出现一列新列，以评分为列表名，根据判断结果，如果大于等于100就是优秀，反之，则是一般。

3.if在结构体中的应用

if 条件 then

//

end if ;

说明：if主要使用在结构体中，(函数，存储过程，触发器)的条件判断上。

四、常用内置函数(系统函数)

1.字符串相关

lcase('ABC').将字符串转化成小写，结果是‘abc’

ucase('abc').将字符串转化成大写，结果是‘ABC’

left('abcdef',2):截取指定位置往左的字符串，结果是‘ab’

char_length('abc中文'):返回字符数,结果是5

length('abc中文'):返回字节数,和当前使用客户端的编码相关!GBK一个中文是两个字节所以返回7，要是utf-8就返回9；

concat('abc','def'):连接字符串,结果是abcdef

instr('abc','c'):判断字符串的位置!结果是3,不存在则返回0

2.数序计算相关

abs(-1):返回绝对值，结果是1

pow(n,m):返回n的m次幂

ceiling(1.2):返回向上取整的结果(不论大小,小数部分都向上进位)，结果是2

floor(1.8):返回向下取整的结果(舍弃小数部分)，结果是1

rand():获取随机值，结果是0到1之间的随机值

round(1.81):对小数部分四舍五入，结果是2

3.时间相关

now():返回当前系统时间，2018-11-30 22:23:43

curtime():返回当前时间，22:23:43

curdate():返回当前日期，2018-11-30

datediff('2012-10-10','2011-10-10'):返回两个时间的天数差，要把大的放到前面，小的放后面，才不会得出负数值，当然也可以abs一下。

4.其它

database():返回当前正在使用的数据库

version():返回版本号

md5('abc'):使用md5加密算法对字符串加密

uuid():返回一个全库唯一的一个字符串

五、自定义函数：function

如何解决多条语句立即执行导致无法完成函数定义？

问题描述：一般情况下,函数有多条语句!但是在定义函数时,MySQL客户端遇到分号就会当成是一条完整的语句,就会立即执行.这样就导致了没有办法完成函数的定义!需要让MySQL把整个函数定义当成一个整体,而不是见到一条语句就执行!!

解决：修改语句的结束符。在函数定义前,将语句结束符改成其他值,然后在函数体内正常使用分号当成结束符,在函数定义完成后,再把语句的结束符改回来即可.这样在函数执行时,依然会正常执行其中的语句.

1.自定义函数格式：

书写注意：①returns不要忘了s ;②delimiter和后面的符合空一格；

2.定义函数，获得一个int值

3.查看函数

格式：Show function status；//查看当前有哪些函数及其属性

4.调用函数

格式：库名.函数名()；

------使用其他库下的函数：

如：select day229.myfun1( )；

5.修改函数

不能修改函数体，只能是先删除，后创建！

6.删除函数

格式：drop function 函数名

7.函数应用：

(1)计算两个变量的和

方式一：正常语句块

delimiter //

create function myadd(a int,b int) returns int

begin

return a +b;end

//delimiter ;

方式二：用一条语句书写，

说明：如果函数只有一条语句的话，begin和end都可以省略，同时，行结束符也不需要修改。

create function myadd2(a int,b int) returns int return a + b;

(2)定义函数，实现两个参数的四则运算

create function myadd2(a int,b int) returns int return a +b;create function sub(a int,b int) returns int return a -b;create function mul(a int,b int) returns int return a *b;create function mydiv(a int,b int) returns int return a / b;

(3)求1-100之和

delimiter //

create function mysum2() returns int

begin

--定义局部变量,保存最终的结果

declare sum int default 0;--定义变量,充当每次要加的值

declare i int default 1;--循环累加

while i <= 100doset sum = sum +i;set i = i + 1;--MySQL中,没有++ --

end while;--返回值

return sum;end

//delimiter ;

结果显示：

调用函数：select mysum2();

(4)函数可以使用会话变量

结果显示：

调用

(5)定义函数，计算指定范围内的所有数之和

(6) 求1-100之间 非 5的倍数的数之和

delimiter //

create function mysum4() returns int

begin

--定义局部变量

declare sum int default 0;--定义变量,充当每次要加的值

declare i int default 1;--循环累加

abc:while i <= 100do--条件判断

if i % 5 <> 0 then

set sum = sum +i;set i = i + 1;--直接执行下一次循环

iterate abc; --leave 直接结束循环

end if;--不满足条件

set i = i + 1;end whileabc;--返回值

return sum;end

//delimiter ;

六、MySQL中循环的操作

iterate 标签:直接执行下一次循环(相当于Java中的continue)；

leave 标签:直接停止循环(相当于Java中的break)；

思考：是否在函数体中是否可以使用select语句？

解答：只用少数场景是可以用的，用select语句会话变量赋值。

(1)定义函数,获取student表中mark最高值

delimiter //

create function test2() returns int

begin

--定义会话变量,用于保存最终的结果:来自于查询语句

set @res := 0;select max(mark) from student into @res;--select @res := select max(mark) from student;

return @res;end

//delimiter ;

七、存储过程:procedure

简称为过程，都是对多条语句的封装。

和函数最大的区别：过程不能有返回值，函数必须有返回值。

1.定义存储过程

2.调用过程：由于过程没有返回值，不能使用select语句

call pro1( );

如果存储过程只有一条语句，结束符不用修改，begin end 也省略

create procedure pro2( ) select * fromstu2;

call pro1( );

3.案例:使用存储过程计算1-100之和,保存在会话变量中

第一种方式:--先定义会话变量,可以在过程中使用,保存最终的结果--set @res := 0;

第二种方式:--直接在过程中定义会话变量,在过程结束后,也可以使用!!!

delimiter //

create procedurepro2()begin

declare i int default 1;--在过程中定义会话变量,在过程外也可以使用!!

set @res := 0;while i <= 100doset @res := @res +i;set i = i + 1;end while;end

//delimiter ;

八、带参数的存储过程

过程中的参数比较特殊，不断有数据本身的参数类型，还有特殊的类型。

1.in:

在过程执行完后,不会返回最后一次赋的值!!

可以使用数值赋值,也可以使用变量赋值!

2.out

只能使用变量赋值!

在过程一开始,就会被置空.

在过程执行完后,会返回最后一次赋的值到真实的实参!!!

3.inout:

只能使用变量赋值!

在过程一开始,不会被置空.

在过程执行完后,会返回最后一次赋的值到真实的实参!!!

4.验证三种变量的特性

set @n1 = 1;set @n2 = 2;set @n3 = 3;

delimiter//

create procedure pro4(in p1 int,out p2 int,inout p3 int)begin

--打印形参的值

selectp1,p2,p3;--重新赋值

set p1 = 10;set p2 = 20;set p3 = 30;selectp1,p2,p3;//形参赋值--对会话变量(实参)进行赋值

set @n1 = 'a';set @n2 = 'b';set @n3 = 'c';//实参赋值select @n1,@n2,@n3;end --相当于再次对n2,n3赋值!!!

//delimiter ;

在调用过程之后,再次查询会话变量.发现给out,inout类型形参赋值的实参的值发生了变化!是在过程中最后一次改变的值被更新到了实参!!!

九、触发器：trigger

当表中的数据发生变化时,会自动执行一些我们定义的动作!!

select 并不会导致表中的数据发生变化!!

数据变化的事件:只关心能导致数据发生变化的事件!!是update,delete,insert:

数据变化的时机:只有两种(前/后)：before,after

1.对同一张表最多有六种触发器

before update:

after update:

before delete:

after delete:

before insert:

after insert:

2.应用：

(1)准备数据，产品库存表;

create tableproduct(

idint primary keyauto_increment,

namevarchar(10),

inventoryint);

(2)订单表

create tableorders(

idint primary keyauto_increment,

product_idint,

product_countint);

(3)添加产品数据

insert into product values(null,'手机',10);

(4)在订单表上创建触发器

delimiter //

create trigger tri1 after insert on orders foreach rowbegin

--当订单表插入数据时,让库存表数量减一

update product set inventory = inventory - 1;end

//delimiter ;

说明：

after insert : 指定触发器被触发的时机

on orders : 指定触发器定义的表

for each row : 固定格式,表明不针对具体行.任何行发生变化都能触发!

-- 触发 "触发器" 的执行

insert into orders values(null,1,1);

十、特殊的关键字

old,new这两个关键字分别用于保存数据变化前后的两种状态!!

注意:

针对delete操作:只有old状态.因为删除之后,数据就没有了.

针对insert操作:只有new状态.因为插入之前,数据还不存在.

针对update操作:两种都有!

关键字的使用：关键字.字段名

例如:

new.product_count  可以获取新添加记录的product_count列的数据值.

改进案例：

优化案例：

十一、JDBC入门

JDBC:Java规定的一套使用Java代码使用数据库的规范(接口).

绝大多数数据库都实现了该接口规范.

能动态的切换底层使用的数据库,而不会导致上层的代码更改!

是代码分层和解耦合的一种表现!

导入包：java.sql.xxx，javax.sql.xxxx

publicclass Demo1 {public static void main(String[]args) throws Exception {// //创建MySQL驱动对象// Driver driver =new com.mysql.jdbc.Driver();// //注册驱动//DriverManager.registerDriver(driver);//以上代码的多次注册问题:/** 查看mysql驱动的源码:

* 发现MySQL驱动有一个静态代码块:自己先注册了一个本类驱动.

* 程序里无需再次注册!!

* 我们只需要让类加载一次即可:

* 反射:

* Class.forName("mysql驱动的全类名"); //导致了mysql类的加载,进而导致了静态代码块的执行.//Class.forName(com.mysql.jdbc.Driver);*/

//通过反射让类加载,即可达到注册驱动的目的,对于返回的Class对象并不关心

Class.forName("com.mysql.jdbc.Driver");//获取一个数据库连接对象// Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day229", "root", "root");//当数据库在本地,端口是默认端口的情况下,url可以简写!!!

Connection conn= DriverManager.getConnection("jdbc:mysql:///day229", "root", "root");//获取一个语句执行器,执行sql语句

Statement st=conn.createStatement();//执行查询语句,得到结果集

ResultSet rs= st.executeQuery("select * fromstu2");//遍历结果集,得到结果while(rs.next()){

String name= rs.getString(1);

String gender= rs.getString(2);

String zhuxiao=rs.getString("zhuxiao");//System.out.println(name+","+gender+","+zhuxiao);

}//释放资源

rs.close();

st.close();

conn.close();

}

}

导入第三方jar包

**************************************

使用JDBC访问数据库总体步骤:1.加载驱动。所以可以用反射(jdk1.6以后数据库加载都已经涵盖，不用写。)2.获取连接对象3.从连接中获取语句执行器对象4.执行sql语句,如果是查询语句的话,返回的是结果集5.从结果集中获取数据:获取结果集中一行数据中的列数据有两种方式:

一:根据列的索引获取:列索引是从1开始

二:根据列的名字获取:指定列名字!

6.释放资源：数据库资源是非内存资源，function必须释放。即：conn.close();