MySql学习day04 索引 index 视图 view MySql扩充 MySql编程的基础知识 流程控制语句 系统函数

–把C语言的成绩都加10分
update choose1 set score+=10 where course_no=(select course_no from course where course_name=‘C语言’);

delete from choose1 where student_no=(select student_no from student where student_name=‘张三’);

使用子查询来代替外连接 （select 中查询）

select s.student_no,s.student_name,c.class_name from student s join classes c on s.class_no=c.class_no;

select s.student_no,s.student_name,(select c.class_name from classes c where c.class_no=s.class_no) from student s

1.索引 index
1.1作用
提高查询效率

1.2 索引和约束的关系
系统会自动为主键、外键、唯一字段创建索引
1.3 创建
1）语法
create table 表名(
字段名 数据类型[约束条件],
…
[,其他约束条件]
[[{unique|fulltext}] index 索引名(字段名)]
);

2)示例
create table t_index(
id int primary key,
name char(10) not null,
index ix_name(name)
);

1.4 查看索引
show index from t_index\G
其中：
\G 表示纵向显示结果

1.5 删除索引
– 语法
drop index 索引名 on 表名
– 示例
drop index ix_name on t_index;

2. 视图 view
   2.1 作用
   1）简化查询操作
   2）数据安全

2.2 创建视图
– 语法
create view 视图名[(视图字段列表)]
as
select 语句;

 -- 示例
 create view vw_choose
as
select * from choose1;
 -- 测试
select * from vw_choose;
insert into vw_choose values(1,'2017011',1,65,now());
update vw_choose set score=score+10 ;
delete from vw_choose where course_no=3;

create view vw_stu(学号,姓名,平局分)
as
select s.student_no,s.student_name,avg(c.score)
from stu1 s,choose1 c where s.student_no=c.student_no 
	and c.score is not null
		group by s.student_no,s.student_name;

2.3 查看视图
show tables;
desc 视图名;
select 字段列表 from information_schema.views
[where table_name=‘视图名’];
2.4 删除视图
drop view 视图名;

MySql扩充：

1. MySql编程的基础知识
   1.1 用户自定义变量
   1.1.1 用户会话变量
   只在当前会话中可见，用户的会话变量输入弱类型
   定义方式：set 或 select
   1）set
   – 语法
   set @变量名 = 值;
   – 示例
   set @user_name=‘张三’;
   select @user_name;
   set @user_name=100,@age=20;
   select @user_name,@age;
   set @age=@age+1;

   2. select
      –语法
      select @变量名:=值;
      或
      select 值 into @变量名;
      – 示例
      select @user_name:=‘李四’;-- 产生结果集
      select 15 into @age;-- 不产生结果集

   3. 用户会话变量和SQL语句
      set @cnt=(select count(*) from student);
      select @cnt;

   select @cnt:=(select count() from student);
   简化为：
   select @cnt:=count() from student;

   select count(*) into @cnt from student;

1.1.2 局部变量
– 语法
declare 变量名 数据类型 [default 值];
– 示例
declare cnt int;
局部变量的使用必须是在存储程序中
1.2 begin … end 块
begin
局部变量的声明;
错误触发条件的声明;
游标的声明;
错误处理程序;
业务逻辑代码;
end;

1.3 重置命令的结束标识
delimiter $$--表示把结束标识设置为$$

delimiter ;		-- 表示把命令结束标识重新设置为 ;

2.自定义函数 function
2.1 创建函数的语法
create function 函数名(参数列表) returns 返回值的数据类型
begin
函数体;
return 语句;
end;

fn_xxx

 2.2 函数的创建和调用
2.2.1 无参的函数
　　　-- 创建一个函数，实现为查询结果添加行号
delimiter $$
create function fn_rowno() returns int
begin
  set @row_no = @row_no+1;
  return @row_no;
end;	
$$
delimiter ;

-- 调用
set @row_no = 0;
select fn_rowno() 行号,student_no 学号,student_name 姓名 from student;	

2.2.2 带参的函数
– 创建一个函数，根据学号查询学生姓名
delimiter $$createfunctionf{n}_{g}etname(st{u}_{n}ochar(11))returnschar(10)begindeclarest{u}_{n}amechar(10);selectstuden{t}_{n}ameintost{u}_{n}amefromstudentwherestuden{t}_{n}o=st{u}_{n}o;returnst{u}_{n}ame;end;$$
delimiter ;

--　调用
select fn_getname('2017001');

2.3 查看函数
mysql.proc
– 查看指定数据库中的函数
select name,type from mysql.proc where db=‘数据库名’ and type=‘function’;

  select name,type from mysql.proc where db='choose' and type='function';

2.4 删除函数
drop function 函数名;

3. 流程控制语句
   3.1 分支语句
   3.1.1 if语句
   1）简单if
   C: if(条件){
   语句块;
   }
   SQL:if 条件表达式 then
   语句块;
   end if;

   2)if … else
   if 条件表达式 then
   语句块1;
   else
   语句块2;
   end if;

   3. 多分支if
      if 条件表达式1 then
      语句块1；
      elseif 条件表达式2 then
      语句块2;
      …
      [else 语句块ｎ;]
      end if;

练习：创建一个函数，传入３个数字，返回最大值
delimiter $$createfunctionf{n}_{M}AXX(num1int,num2int,num3int)returnsintbegindeclaretmpint;settmp=num1;ifnum2>tmpthensettmp=num2;endif;ifnum3>tmpthensettmp=num3;endif;returntmp;end;$$
delimiter ;

 3.1.2 case　语句
１）语法
　　case 表达式
　　　　when 值１　then 语句块１;
    when 值2  then 语句块２;
    ...
    [else 语句块n;]
   end case;


2)示例

创建函数，根据日期判断并打印星期几
delimiter $$createfunctionf{n}_{g}etweek(wee{k}_{n}oint)returnschar(20)begindeclareweekchar(20);casewee{k}_{n}owhen0thensetweek{=}^{\prime }星期{一}^{\prime };when1thensetweek{=}^{\prime }星期{二}^{\prime };when2thensetweek{=}^{\prime }星期{三}^{\prime };when3thensetweek{=}^{\prime }星期{四}^{\prime };when4thensetweek{=}^{\prime }星期{五}^{\prime };when5thensetweek{=}^{\prime }星期{六}^{\prime };when6thensetweek{=}^{\prime }星期{日}^{\prime };endcase;returnweek;end;$$
delimiter ;
select now(),fn_getweek(weekday(now())) 星期;
– else set week=‘周末’;

3.2 循环语句
　　 循环变量的初始化　　int i=0;
循环条件 　　　　　　i<10
　 循环变量的更新　　　i++
　 循环操作

3.2.1 while 循环
– 语法
[循环标签:]while 循环条件 do
循环操作;
end while[循环标签];

--　示例
使用while 循环实现1...n的累加
delimiter $$
create function fn_getsum1(n int) returns int
begin
	declare sum int default 0;		-- int sum=0;
	declare i int default 0;		-- int i=0;
	while i<n do				-- while(i<n){
		set i=i+1;			-- i++;
		set sum=sum+i;			-- sum+=i;
	end while;				-- }
return sum;
end;	
$$
delimiter ;

select fn_getsum1(100);-- 5050

3.2.2 leave 语句　– 相当于Ｃ语言中的break
–　语法
leave 循环标签
– 使用leave语句实现1…n的累加
delimiter $$createfunctionf{n}_{g}etsum2(nint)returnsintbegindeclaresumintdefault0;declareiintdefault0;ad{d}_{s}um:whiletruedoseti=i+1;setsum=sum+i;ifi=nthen--if(i==n)leavead{d}_{s}um;--break;endif;--endwhilead{d}_{s}um;returnsum;end;$$
delimiter ;

3.2.3 iterate语句　– 相当于C语言中的 continue
– 语法
iterate 循环标签;
– 示例
使用
delimiter $$createfunctionf{n}_{g}etsum3(nint)returnsintbegindeclaresumintdefault0;declareiintdefault0;ad{d}_{s}um:whilei<ndoseti=i+1;ifiiteratead{d}_{s}um;endif;setsum=sum+i;endwhilead{d}_{s}um;returnsum;end;$$
delimiter ;

3.2.4 repeat 语句
– 语法
[循环标签:]repeat
循环操作;
until 条件表达式
end repeat[循环标签];

重复执行循环操作，直到条件表达式成立为止.

– 示例
使用repeat循环实现1…n的累加
delimiter $$createfunctionf{n}_{g}etsum4(nint)returnsintbegindeclaresumintdefault0;declareiintdefault0;repeatseti=i+1;setsum=sum+i;untili=nendrepeat;returnsum;end;$$
delimiter ;

3.2.5 loop 语句
– 语法
[循环标签:]loop
循环操作;
if 条件表达式 then
leave 循环标签;
end if;

end loop[循环标签];

-- 示例
loop循环实现1..n的累加
delimiter $$
create function fn_getsum5(n int) returns int
begin
 declare sum int default 0;
 declare i int default 0;
 add_sum:loop
	set i=i+1;
	set sum=sum+i;
	if i=n then  		-- if(i==n){
	leave add_sum;		-- break;
	end if;			-- }
 end loop add_sum;
 return sum;
end;
$$
delimiter ;

s

4.系统函数
4.1 数学函数
1）求近似值的函数
round(x); 四舍五入(整数)
round(x,y); 四舍五入
y的取值：y为整数
y>0:四舍五入到小数点后的y位 round(345.67,1)=345.7
y<0:四舍五入到小数点前|y|位 round(567.45,-2)=600

	truncate(x,y):	截断	
		y=0:截断到个位(整数) 	truncate(345.67,0)=345
	   	y>0:截断到小数点后的y位 	truncate(345.67,1)=345.6
		y<0:截断到小数点前|y|位	truncate(567.45,-2)=500


ceil(x):	大于等于x的最小整数  ceil(3.1)=4
floor(x):	小于等于x的最大整数  floor(3.9)=3;

2)随机数
rand():		随机数

4.2 字符串函数
length(str); 获取字符串str占用的字节数
char_length(str); 获取字符串str的长度
concat(s1,s2,…): 字符串拼接
left(str,n): 从左侧截取n个字符
right(str,n): 从右侧截取n个字符
substring(str,start,n): 从字符串str的start位置向右截取n个字符
start:开始截取的位置 从1开始
>0 表示从左侧开始数
<0 表示从右侧开始数
n: 截取字符串的长度 缺省时，表示截取到字符串的末尾

	select substring('Hello World',7,5) s1, 
		substring('Hello World',-5,5) s2,
			substring('Hello world',-5) s3;
	
lower(str);	转化成小写字母
upper(str);	转换成大写字母

4.3 日期和时间函数
curdate(): 获取服务器的当前日期
curtime(): 获取服务器的当前时间
now(): 获取服务器的当前日期和时间
now()可以提供一个<=6的参数,获取更为精确的时间信息
year(d): 年
month(d): 月
dayofmonth(): 日
hour(t): 时
minute(t): 分
second(t): 秒
microsecond(x): 微秒
dayname(d): 星期几(英文的字符串)
select year(curdate()) 年,month(curdate()) 月,dayofmonth(curdate()) 日,hour(curtime()) 时,
minute(curtime()) 分,microsecond(now(3)) 微秒,
dayname(curdate()) 星期;

4.4 条件控制函数
1）if()函数
if(条件表达式,值1,值2):当条件表达式成立时，返回值1,否则返回值2
select student_no 学号,course_no 课程,score 成绩,
if(score>=60,‘是’,‘否’) 是否通过 from choose;

2. ifnull()函数
   ifnull(part1,part2):当part1为null时，返回part2,当part1不为null,返回part1
   – null参与运算，整个表达式的结果为null
   select stu_no,e_score,a_score,e_score0.8+a_score0.2 score from exam_score;

   select stu_no,e_score,a_score,ifnull(e_score,0)0.8+a_score0.2 score from exam_score;