MySQL命令学习4 - 数据类型、视图、变量、存储过程、函数、流程控制、循环、case、if

1. 数据类型

1.1 整型 - 超出范围则抛出异常

 

zerofill - 零填充的作用 - 只能用于正数、无符号的值中。有负数则直接将值设为0

create table if not exists book(
    id int(5),
    num int(5)  zeroFill
);


insert into book values (12,12));

 

1.2 小数型 - 超出范围则抛出异常

切记：超过最大长度。则插入最大临界值。
double必须显示指定最大长度跟小数长度，否则插入值为null

 

1.3 字符型

注意：可指定字符数

 

1.4 集合型

create table if not exists test(
    enum1 enum('a', 'b', 'c'),
    set1 set('a', 'b', 'c')
)

insert into test values('a', 'a');
insert into test values('a,b', 'a,b');
insert into mytest values('d', 'a,b,d');

select * from test;

 

1.5 日期型

drop table mytest;
create table if not exists mytest(
    t1  datetime,
    t2  timestamp
);

insert into mytest values(now(),now());

select * from mytest

 

2. 视图View - 用于查询、不建议在视图执行DML语句

只保存SQL逻辑、并不是创建新表

 

视图内包含下面图片的关键字则不可在视图内执行DML语句

1. 创建、使用视图

# 创建视图1
create or replace view 视图名
    as
 平常的sql语句;


 # 创建视图2
alter view 视图名
    as
 平常的sql语句;
 
 
 # 使用视图
 select * from  视图名;

 

2. 删除视图

drop view 视图名;

 

3. 查看视图列结构 - 其实就是看表结构

desc  视图名

 

4. 查看视图创建语句、字符集

show create view 视图名;

 

3. 变量

3.1 系统变量 - 默认session当前数据库连接级别

1. 查看MySQL所有系统变量

# global：全局系统变量    session：当前数据库连接系统变量
show global/session  variables

 

2. 模糊查询系统变量

# global：全局系统变量    session：当前数据库连接系统变量
show global/session  variables like '%模糊名%';

 

3. 精确查询系统变量

# global：全局系统变量    session：当前数据库连接系统变量
select @@global/@@session.变量名;


# 简写查询会话变量、即局部变量
select @@变量名;

 

4. 修改系统变量值

# global：全局系统变量    session：当前数据库连接系统变量
set  global/session  变量名 = 值;

set @@global/ @@session.变量名 = 值;


#简写修改会话、局部变狼
set @@变量名 = 值

 

3.2 自定义变量

3.2.1 用户变量 - 当前数据库连接有效 - 另一个新的数据库连接不能使用

1. 声明并初始化、赋值

# 语法
set @变量名 = 值;

set  @变量名 := 值;

select  @变量名 := 值;

select 列名 into 变量名 from 表

 

2. 使用用户变量

# 语法
select @变量名

 

3.2.2 局部变量 - begin-end中有效（存储过程、自定义函数）

1. 声明变量

# 语法
declare 变量名  数据类型  【default  默认值】

 

2. 赋值 - 跟用户变量一样

# 语法
set 变量名 = 值;

set  变量名 := 值;

select  变量名 := 值;

select 列名 into 变量名 from 表

 

3. 使用

# 语法
select 局部变量名

 

 

4. 存储过程、自定义函数

1. into关键字一个SQL语句只能写一次
 
2. 列名与形参名重合 - 则需显示指定列名所属的表

4.1 存储过程 - SQL语句集合 - 可进行批处理 - 批量插入、更新

1. 创建存储过程

create  or replace  procedure 存储过程名( 参数类型 参数名 参数数据类型 )
begin
       存储过程体
end;

 

2. 调用、执行存储过程

call 存储过程名( 实参列表 )

 

3. 查看当时创建存储过程的语句逻辑、删除存储过程

#查看
show create procedure 存储过程名;

#删除
drop procedure 存储过程名

 

4. 简单使用In参数模式 - 创建、运行

CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myProcedure`(IN `idNum` int)
BEGIN

	declare sum int default 0;
	
	select * from book where id = idNum;
	
	select * from book;
	
	select count(*) into sum  from book;
	
END;


call myProcedure(9);

 

5. 简单使用In、out参数模式 - 创建、运行

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myProcedure`(IN `idNum` int, out sum int)
BEGIN

	select * from book where id = idNum;
	
	select * from book;
	
	select count(*) into sum  from book;
	
END;

# 声明用户变量
set  @sum =  0;

# 执行存储过程
call myProcedure(9, @sum );

# 查看变量结果
select  @sum;

 

6. 简单使用Inout参数模式 - 创建、运行

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myProcedure`(INOUT `idNum` int)
BEGIN

	set idNum := idNum *2;
	
END;


# 声明用户变量
set  @sum =  50;

# 执行存储过程
call myProcedure(@sum );

# 查看变量结果
select  @sum;

 

4.2 自定义函数 - Java方法 - 只能返回一个结果

语法 - 创建自定义函数

create function 函数名( 参数列表 ) returns 返回数据类型
begin 
    函数体（必须包含return语句）
end;

 

语法 - 调用函数

select 函数名(参数列表);

 

语法 - 查看、删除函数

# 查看函数创建
show create function 函数名

# 删除
drop  function 函数名

 

简单使用

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` FUNCTION `myFunction`(`name` varchar(100)) RETURNS int(11)
begin 
	declare ids  int default 0;
	select id into ids from book where book.name = name;
	return ids;
end;

# 执行存储过程
select myFunction('李白');

 

4.3 流程控制（函数、存储过程内）

4.3.1 分支结构 - if、case

1. case语法

# 无else，如果都不满足则返回null

case 变量|表达式|字段
when 判断的值/条件 then 返回值1;
when 判断的值/条件 then 返回值2;
else  返回值3
end case;

 

2. 简单使用case

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myProcedure`(IN `flag` int)
BEGIN
	case flag
	when flag = 1 then select 'a';
	when flag = 2 then select 'b';
	when flag = 3 then select 'c';
	else select 'd';
	end case;
END;

# 运行存储过程
call myProcedure(1);

 
3. if语法

if 条件1 then 语句;
elseif 条件2 then  语句2;
else 语句3;
end if;

 

4. 简单使用if

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myProcedure`(IN `flag` int)
BEGIN
	if flag = 1 then select 'a';
	elseif flag = 2 then select 'b';
	elseif flag = 3 then select 'c';
	else select 'd';
	end if;
END


# 运行存储过程
call myProcedure(1);

 

4.3.2 循环结构

循环语法

# while循环
while 循环条件 do
    循环体
end while 【标签】;


# loop循环 - 可用来模拟死循环
【标签: 】 loop
    循环体
end loop 【标签】;


# repeat循环
【标签: 】 repeat
    循环体
until 结束循环条件
end   repeat【标签】;

 

简单使用

# 创建存储过程
CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myprocedure2`(IN `count` int)
BEGIN
	
	declare i int default 0;
	
	-- 写法1
	/*while i < count do
		insert into book(`name`) values (concat('name',i));
		set i = i+1;
	end while;*/
	
	-- 写法2
	/*repeat 
		insert into book(name) values (concat('name',i));
		set i = i+1;
	until i = count
	end repeat;*/
	
	-- 写法3
	a:loop
		insert into book(name) values (concat('name',i));
		set i = i+1;
		if i = count then leave a;
		end if;
	end loop a;

END


# 执行存储过程
call myprocedure2(2);

# 查看结果
select * from book;

 

简单使用 - 含循环控制

CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `myprocedure2`(IN `count` int)
BEGIN
	
	declare i int default 0;
	
	-- 写法1
	a:while i < count do
		set i = i+1;
		if i = 1 then iterate a;
		end if;
		insert into book(`name`) values (concat('name',i));
		
	end while a;
	
	-- 写法2
	/*a:repeat 
		set i = i+1;
		if i = 1 then iterate a;
		end if;
		insert into book(name) values (concat('name',i));
		
	until i = count
	end repeat a;*/
	
	-- 写法3
	/*a:loop	
		set i = i+1;
		if i = 1 then iterate a;
		end if;
		
		insert into book(name) values (concat('name',i));
		
		if i = count then leave a;
		end if;
	end loop a;*/

END


# 执行存储过程
call myprocedure2(2);

# 查看结果
select * from book;