八、索引

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。

8.1、为已经创建好的表建立索引

1）用CREATE INDEX方法在已经创建好的表 t_student 上为其字段名 stu_id 创建普通降序索引。

语法：

create [unique | fulltext] index index_name on table_name(co_name ase|desc , [co_name ase|desc] ) 

-- 多字段索引使用逗号（，）隔开

	create index index_student on t_student(stu_id desc)

2）用ALTER TABLE…ADD INDEX方法为表t_course的字段列course_name 创建全文索引。

语法：

	alter table table_name add [unique | fulltext] index index_name(co_name ase|desc , [co_name ase|desc] ) 
-- 多字段索引使用逗号（，）隔开

	alter table t_course add fulltext index index_course_name(course_name)

8.2、创建新表时创建索引

创建表 t_teacher2，与 t_teacher 结构一致（不能复制表结构），在创建表的过程中创建索引，为 teacher_id 创建一个唯一升序索引。

	create table t_teacher2(teacher_id char(10),teacher_name char(50),index index_tea_id(teacher_id asc));

九、视图

视图（View）其实是从一个或多个关系表（或视图）当中导出的表，是一个虚表类似一个软链接。

9.1、创建视图

建一个视图view_basic_info，数据来源于t_student表 语法：create view <视图名> as select_statement

	create view view_basic_info as select * from t_student;

9.2、修改视图

语法：create view <视图名> as select_statement

	alter view view_basic_info as select * from t_student;

9.3、删除视图

	drop view view_basic_info;

十、存储过程

存储过程和程序中的函数非常相似，它可以将某些需要多次调用、实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并通过过程名来调用，这些过程称为存储过程。

DELIMITER 定义：设置语句结束后使用的分隔符，MYSQL的默认结束符为";"。

10.1、创建存储过程

	mysql> delimiter $  
	mysql> create procedure p1()
		-> comment '读者信息'
		-> begin
		-> select * from t_reader;
		-> end $
	mysql> delimiter ;

10.2、调用存储过程

	mysql> call p1;

10.3、删除存储过程

	mysql> drop procedure p1;

10.4、创建带参数的存储过程

10.4.1、in

1.创建一个带输入参数的存储过程，查询某个学号对应的学生信息。

	mysql> delimiter $
	mysql> create procedure p3(in stuid char(10))
	comment "查询某个学号对应的学生信息" 
	begin
	select * from t_student where stu_id=stuid;
	end $
	mysql> delimiter ;

	mysql> call p3(1631607102); #调用存储过程

10.4.2、out

2.创建一个带输出参数的存储过程，查询所有学生的人数。

	注意：变量名前加@表示：会话变量
	会话变量：不局限于存储过程内部

	mysql> delimiter $
	mysql> create procedure p6(out stucount int) 
	begin 
	select COUNT(stu_id) into stucount from t_student; 
	end $
	mysql> delimiter ;

	mysql> call p6(@stucount);  #调用存储过程
	mysql> select @stucount;	#查看输出变量

10.4.3、inout

3.创建一个带输入输出参数的存储过程，对某个成绩加20分。

	mysql> delimiter $
	mysql> create procedure p9(inout testname int) 
	begin 
	update t_score set grade=grade+20 where score_id=testname; 
	select grade into testname from t_score where score_id=testname; 
	end $
	mysql> delimiter ;

	mysql> set @testname=1001;
	mysql> call p9(@testname);
	mysql> select @testname;

@pra：会话变量不局限于存储过程内部

十一、SQL编程基础

11.1、SQL编程基础语法

SQL本质就是一种编程语言，需要变量来保存数据。MySQL中许多属性控制都是通过MySQL中的变量老实现的。

变量分为两大类：系统变量和用户变量；

• 系统变量：针对所有用户，即MySQL中所有客户端都有效。
• 用户变量：针对指定对应的用户。

11.1.1、系统变量

MySQL系统变量实际上是用来存储系统参数的，用于初始化和设置MySQL对系统资源的占用，配置文件存放位置等。大部分变量都是作为MySQL系统的调节参数存在，修改这些参数会影响MySQL的运行方式。

系统变量：分为全局（global）变量和会话（session）变量。

• 全局变量：影响整个MySQL实例的整体操作。
• 会话变量：影响当前到MySQL实例的连接。

11.1.1.1、查看系统变量

11.1.1.1.1、会话变量和全局变量

语法：show variables 条件语句

条件语句：代表要查询系统变量的筛选条件（可选项）

查看所有系统变量；优先显示会话变量，其次显示全局变量。

	show variables;  //查看所有系统变量

通过global、session关键字，限制系统变量作用域。

	show global variables;  //查看全局变量
	show session variables;  //查看会话变量

如果要查看的是某些具体的系统变量，而不是全部，可以使用like和where语句：

	show variables like 'log%';  //查询所有变量名以log开头的系统变量

如果在查询时，条件中除了以变量名为条件，还要以值为条件，可以使用where语句：

	show variables where variable_name like 'log%' and value='OFF';

11.1.1.1.2、配置变量和监控变量

MySQL系统变量还能分为：配置变量和监控变量

	show variables;  // 查看配置变量
	show status; // 查看监控变量

同理，若想在查询系统状态变量时，增加条件语句，方式和查询配置变量时相同。

11.1.1.2、常见的系统变量

工作中，数据库一般分为“开发库”、“测试库”、“线上库”等环境。

查看当前MySQL版本信息

最大连接数

在连接数据库时，经常会遇到 MySQL: ERROR 1040： Too many connections 的报错信息。一种情况是，因为访问量太高，MySQL压力过大，无法处理，这个时候可以增加’从‘服务器的数量，来缓解’读‘的压力。另外一种情况是，MySQL配置文件中 max_connections 值设置过小导致。

当前连接数

如果 Threads_connected <= max_connections 才能保证正常连接数据库。

	mysql> show status like 'Threads_connected';
	+-------------------+-------+
	| Variable_name     | Value |
	+-------------------+-------+
	| Threads_connected | 1     |
	+-------------------+-------+
	1 row in set (0.00 sec)

MySQL编码配置信息

如果出现中文乱码问题，一般是MySQL编码设置不一致导致。

11.1.1.3、设置和修改系统变量

对系统变量，在MySQL服务启动前，我们可以修改 my.cnf 配置文件；或启动时，指定启动参数修改。

在服务启动以后，可以通过 set 语句修改变量值。

	//修改全局变量
	set global var_name = value;
	set @@global.var_name = value;

	//修改会话变量
	set session var_name = value;
	set @@session.var_name = value;

11.1.2、用户变量

用户变量指用户自己定义的变量，可以给用户变量分配值。可以使用set 和 select语句定义赋值（不赋值默认为空值），其中 select 语句还可以查看所赋的值。

变量作用范围：当前连接的会话范围（如果断开当前的连接，那么之前定义的变量将无法访问）。

定义用户变量的形式：以’@‘开始，如：“@var_name”，从而区分用户变量和表中列名。可以使用 '='，':=' 操作符赋值。如果使用 select 语句必须使用 ':=' 操作符赋值

语法：

set @var_name = value1 [ , @var_name = value2];
set @var_name := value1 [ , @var_name := value2];

set语句

	mysql> set @test=123;  
	Query OK, 0 rows affected (0.00 sec)

	mysql> select @test;
	+-------+
	| @test |
	+-------+
	|   123 |
	+-------+
	1 row in set (0.00 sec)


	mysql> set @var1=1,@var2=2;
	Query OK, 0 rows affected (0.00 sec)

	mysql> select @var1,@var2;
	+-------+-------+
	| @var1 | @var2 |
	+-------+-------+
	|     1 |     2 |
	+-------+-------+
	1 row in set (0.00 sec)

select语句

	mysql> select @var:=1,@var:=2;
	+---------+---------+
	| @var:=1 | @var:=2 |
	+---------+---------+
	|       1 |       2 |
	+---------+---------+
	1 row in set, 2 warnings (0.00 sec)

	mysql> select @var,@var;
	+------+------+
	| @var | @var |
	+------+------+
	|    2 |    2 |
	+------+------+
	1 row in set (0.00 sec)

11.2、SQL系统函数

11.2.1、条件判断函数

if函数

语法：

if(expr1,expr2,expr3)

如果表达式的结果是true（布尔类型），则返回expr2的值，否则返回expr3的值。

case结构

	case case_expr
	  when when_value then statement_list
	  [ when when_value then statement_list ]...
	end case

case_expr：条件判断表达式，决定哪一个 when 被执行。 when_value：表示表达式可能的值，如果某个 when_value 值与case_expr表达式值结果相同，则执行对应 then 关键字后的 statement_list 语句。

11.2.2、数学函数

常用的一种函数。主要用于处理数字，包括整型、浮点数等 数学函数

11.2.3、字符串函数

字符串函数是非常常用的一种函数。针对数据类型 数据的处理。 字符串函数

11.2.4、日期函数

日期函数

11.2.5、聚合函数

一般作用在指定字段上。 聚合函数

11.3、自定义函数

在函数种可以使用 if、case、loop、repeat、while、for语句，这部分内容请自行了解。

11.3.1、创建函数

语法：

	create function func_name(func_parameter)
	returns type
	 body

create function：创建函数的关键字 func_name：函数名称 func_parameter：函数参数列表，形式为 (param_nae type)，如：(num1 int，num2 int) type：函数放回值类型，如：int、char(50)等。 body一般格式为，如下：

	begin
	   retrun(select 查询语句);
	end

delimiter 定界符： MySQL默认定界符是分号 ‘;’ 。使用 delimiter 语句，可以修改默认定界符

例子：

创建一个函数，查询某读者的年龄。

	delimiter $ 
	create function func_reader_age2(name char(50))
	returns int
	begin
	return(select year(curdate())-year(reader_birthday) from t_reader where reader_name=name);
	end $
	delimiter ;
	select func_reader_age2("肖华");

11.3.2、删除函数

	drop function function_name;