MySQL整理01-数据库安装及数据库对象

一、Linux系统安装mysql

1.1安装

1. 卸载 centos 中预安装的 mysql
   rpm -qa | grep -i mysql
   rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps
2. 上传 mysql 的安装包
   alt + p -------> put E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar
3. 解压 mysql 的安装包
   mkdir mysql
   tar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql
4. 安装依赖包
   yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 –
   setopt=protected_multilib=false
   yum update libstdc+±4.4.7-4.el6.x86_64
5. 安装 mysql-client
   rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm
6. 安装 mysql-server
   rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm

1.2启动
service mysql start
service mysql stop
service mysql status
service mysql restart
高版本操作方式：
systemctl start firewalld
systemctl stop firewalld

1.3登录
mysql -u root -p
修改密码
set password = password(‘itcast’);
授权远程访问 :
grant all privileges on . to ‘root’ @’%’ identified by ‘itcast’;
flush privileges;

1.4Linux防火墙
service iptables status
service iptable stop
service iptables start

1.5 rpm使用
1.安装软件：执行rpm -ivh rpm包名，如：
#rpm -ivh apache-1.3.6.i386.rpm
2.升级软件：执行rpm -Uvh rpm包名。
3.反安装：执行rpm -e rpm包名。
4.查询软件包的详细信息：执行rpm -qpi rpm包名
5.查询某个文件是属于那个rpm包的：执行rpm -qf rpm包名
6.查该软件包会向系统里面写入哪些文件：执行 rpm -qpl rpm包名
7，查看某个包是否被安装 rpm -qa | grep XXXX(moudle name)

二、索引

1、概述
MySQL官方定义为：索引是帮助MySQL高效获取数据的数据结构。除了数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说，索引也很大，不可能全部存储在内存中，因此索引常常以索引文件的形式存储在磁盘上。索引是数据库用来提高性能的常用工具。
2、优劣势
优势：
类似书籍的目录，减少查找次数，降低计算机IO成本。
劣势：
索引表也要占用空间。
数据库进行插入、修改、删除操作时，MySQL不仅要更新数据还要维护索引。
3、索引结构
索引是在存储引擎层面实现的，所以不同存储引擎会有不同的索引结构。MySQL支持4中索引形式。

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
  
  平时所说的索引没有特殊说明，都是指B+树索引（多路搜索树，并不一定是二叉的）。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。
  3.1 Btree
  BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：
• 树中每个节点最多包含 m个孩子。
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)]个孩子。
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由 n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1
  
  五叉B树

3.2 B+树
B+Tree为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
• 所有的非叶子节点都可以看作是key的索引部分
  
  由于B+树只有叶子节点保存key信息，所以每次查询都要从root走到叶子，因此查询效率也更加稳定。
  3.3 MySQL中的B+树
  MySQL索引数据结构对经典的B+树进行了优化。在苑B+树的基础上增加了一个指向相邻叶子节点的指针，这样就形成了带有顺序指针的B+树。这样方便了范围搜索。
  
  3.4 索引分类
• 单值索引：一个索引只包含一个列，一张表可以有多个单值索引。
• 唯一索引：索引列的值必须唯一，允许有null值。
• 复合索引：一个索引包含多个列。

3.5 索引语法
1.创建索引
create index idx_city_name on city(city_name);
组合索引
create index idx_name_email_status on city(name,email,status);
2.查看索引
show index from city;
3.删除索引
drop index idx_city_name on city;
4.alter命令

• alter table tb_name add primary key(column_list);
  该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
• alter table tb_name add unique index_name(column_list);
  这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
• alter table tb_name add index index_name(column_list);
  添加普通索引， 索引值可以出现多次。
• alter table tb_name add fulltext index_name(column_list);
  该语句指定了索引为FULLTEXT， 用于全文索引

5.存储引擎
show engines;
show create table city;
alter table city engine=innodb;

6.索引设计原则

• 索引是为了方便查询，所以要查询频率高，数据量比较大的表建立索引。
• 索引字段的选择，最佳候选应当从where子句的条件中提取，应该挑选最常用、过滤效果最好的组合。
• 尽量使用唯一索引。
• 索引的维护页需要成本，并不是越多越好。
• 利用最左前缀，N个列组合而成的组合索引，那么相当于创建了N个索引。如果where子句使用了该索引的前几个字段，那么这条查询就可以利用组合索引来提升查询效率。

三、视图

1.视图概述
视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
2.创建或修改视图
创建视图
CREATE or replace VIEW view_city_country AS SELECT c.*,t.country_name FROM city c,country t WHERE c.country_id=t.country_id;
查询视图
SELECT * FROM view_city_country;
SHOW TABLE STATUS LIKE ‘view_city_country’;

更新视图，实际更新的是基础表的数据
update view_city_country set city_name=‘西安市’ where city_id=1;
修改视图
alter view view_city_country as sql语句…。
删除视图
drop view if exists view_city_country;

四、存储过程和函数

1.概述
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，使用存储过程和函数可以减少数据在数据库和应用服务器中的传输，对于提高数据处理效率有好处。
函数必须有返回值，而存储过程则没有。

2.创建存储过程
delimiter $
create procedure pro_test1()
begin
select ‘Hello Mysql’ ;
end$
delimiter ;
说明：delimiter关键字
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

3.调用存储过程
call pro_test1();

4.查看存储过程
– 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db=‘index_test’;
– 查询存储过程的状态信息
show procedure status;
– 查询某个存储过程的定义
show create procedure index_test.pro_test1;

5.删除存储过程
drop procedure if exists index_test.pro_test1;
说明：存储过程封装了一系列sql语句，但是不能使用 use关键字，也就是无法切换数据库，只能在当前数据库操作，否则会报错。

6.语法

6.1变量

• declare
  声明局部变量，作用范围在begin…end块中。
  delimiter $
  create procedure pro_test2()
  begin
  declare num int default 5;
  select num+ 10;
  end$
  delimiter ;
• set
  DELIMITER $
  CREATE PROCEDURE pro_test3()
  BEGIN
  DECLARE NAME VARCHAR(20);
  SET NAME = ‘MYSQL’;
  SELECT NAME ;
  END$
  DELIMITER ;
• 通过select … into 方式进行赋值操作
  DELIMITER $
  CREATE PROCEDURE pro_test4()
  BEGIN
  declare countnum int;
  select count(*) into countnum from city;
  select countnum;
  END$
  DELIMITER ;

6.2 if条件判断

delimiter $
create procedure pro_test5()
begin
declare height int default 175;
declare description varchar(20);
if height>=180 then
set description=‘身材高挑’;
elseif height>=170 and height<180 then
set description=‘标准身材’;
else
set description =‘身材一般’;
end if;
select description;
end$
delimiter ;

6.3 传递参数

语法格式：
create procedure procedure_name([in/out/inout] 参数名 参数类型)
…
IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

• in
  delimiter $
  create procedure pro_test6(in height int)
  begin
  declare description varchar(20);
  if height>=180 then
  set description=‘身材高挑’;
  elseif height>=170 and height<180 then
  set description=‘标准身材’;
  else
  set description =‘身材一般’;
  end if;
  select description;
  end$
  delimiter ;

• out
  delimiter $
  create procedure pro_test7(in height int,out description varchar(20))
  begin
  if height>=180 then
  set description=‘身材高挑’;
  elseif height>=170 and height<180 then
  set description=‘标准身材’;
  else
  set description =‘身材一般’;
  end if;
  end$
  delimiter ;

调用：
call pro_test7(168, @description);
select @description;
说明：@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

6.4 case结构

delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = ‘第一季度’;
when month >= 4 and month <=6 then
set result = ‘第二季度’;
when month >= 7 and month <=9 then
set result = ‘第三季度’;
when month >= 10 and month <=12 then
set result = ‘第四季度’;
end case;
select concat(‘您输入的月份为 :’, month , ’ , 该月份为 : ’ , result) as content ;
end$
delimiter ;

6.5 while结构

delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;

6.6 repeat结构
有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行，repeat 是满足条件就退出循环。
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;

6.7 loop语句
6.8 leave语句
6.9游标

五、触发器

1.概述
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，出发并执行触发器中定义的sql语句集合。可以协助在数据库端保证数据的完整性，日志记录，数据校验等操作。
使用old或new来引用触发器中发生变化的内容。MySQL触发器还只支持行级触发器，不支持语句级触发器。oracle支持行级和语句级触发器。

说明：如果涉及事务，触发器和也会在事务提交时候执行。
2、创建触发器
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] – 行级触发器
begin
trigger_stmt ;
end;
说明：触发器是针对表的，不能跨数据库操作别的表。
2.1插入
delimiter $
create trigger emp_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs values(null,‘insert’,now(),new.id,new.name);
end$
delimiter ;

2.2更新
delimiter $
create trigger emp_update_trigger
after update
on emp
for each row
begin
insert into emp_logs values(null,‘update’,now(),new.id,concat(‘old:’,old.name,’,new:’,new.name));
end$
delimiter ;

2.3删除
delimiter $
create trigger emp_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs values(null,‘delete’,now(),old.id,old.name);
end$
delimiter ;

3、删除触发器
DROP TRIGGER emp_delete_trigger;

4、查看触发器
show triggers;

相关链接：
数据库安装及数据库对象
https://blog.csdn.net/li210530/article/details/104100741
存储引擎、索引及SQL调优
https://blog.csdn.net/li210530/article/details/104113358
参数调优及数据库锁
https://blog.csdn.net/li210530/article/details/104128217
常用工具及日志分析
https://blog.csdn.net/li210530/article/details/104146098