MySQL高级(一)

MySQL高级(一)

MySQL视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，是从主表中取出一定条件的数据存放在视图中，视图和主表是依赖关系，如果主表数据变化，视图也会随之变化。

特点

1. 若主表删除，则视图失效
2. 视图只用于查询
3. 在一定条件下能减轻主表压力

创建视图
	create view v1 as select * from t1 where id > 1;
查看视图
	select * from v1;
删除视图
	drop view v1;		

MySQL内置函数

字符串拼接   concat('name','id');
最大值 max('price')
最小值 min('price')
平均值  avg('price')
定义变量  set @a = 10
获取变量的值  select @a
计算  select @a + @b;          select 10 + 20
字符串转换成大写和小写    select ucase('name')     select lcase('NAME')
计算字符串的长度  select length('namesss') as len;
		select * from user where length(name) > 5;  名字大于5的长度找出来
去除两侧的空白  select trim('   pass   ');
		select length(trim('  over '));
随机数 从0-1之间的随机数  select rand();
获取0-10之间的随机整数向上取整   select ceil(rand()*10)

MySQL预处理

设置一个占位符
	prepare 名字 from "select * from t2 where id > ?";
设置变量
	set @id = 2;
执行
	execute 名字 using @id;
设置多个占位符
	prepare 名字 from "select * from t2 where id > ? and id < ?";
设置变量
	set @id = 2;
	set @id1 = 5;
执行
	execute 名字 using @id,$id1;

MySQL存储过程

mysql存储过程类似于PHP写的方法
创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...]过程体

举例说明：
	修改结束符
    mysql-> \d //
    创建
	mysql-> create procedure proc()  
     	-> begin 开始
     	-> select * from user;  
     	->end// 结束

	使用
	mysql-> call proc()//

	修改结束符 
     mysql-> \d ;
 注意：\d //和\d ;两句，\d是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，
	 那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，
	 所以要事先用\d关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，
	 不会执行这些代码，用完了之后要把分隔符还原。    
 格式:
	\d //  把结束符的分号改成 //结束本条sql语句

	\d  和 delimiter 是一样的 都可以修改mysql的结束符

（2）过程体的开始与结束使用BEGIN与END进行标识

（3）存储过程根据需要可能会有输入输出参数，输入参数x 类型int型 ,输出参数num 类型是int型，如果有多个参数用","分割开。

举例说明:

	->create procedure lamp172(in x int,out num int)
  	-> begin
  	-> set num = x + 20;
  	-> end//
调用 
 	->call lamp172(10,@s)//

	->select @s as num//

使用循环插入数据
	->create procedure total(num int(10))
 	->begin
 	->set @i = 0;
 	->while @i<num do
 	->insesrt into t1 values (null,'www',20);
 	->set @i = @i+1;
 	->end while;
 	->end//
调用 -> call total(10); 	

注意 : 写存储过程名字的时候 不要使用关键字 和 数字	

例如:
    create procedure 111()   数字错误
    create procedure  add()   关键字错误

删除存储名

	drop procedure if exists 存储名

MySQL触发器 trigger

在t2表插入数据的同时使用trigger 修改其他表的信息

例如:
create trigger tg_1 before insert on t2 for each row
begin
insert into t3(name) values (new.name);
end//
插入数据
insert into t3(name) values ('wwww')//

create trigger tg_2 after update on t1 for each row
begin
update t2 set t2.name= new.name where t2.id = old.id;
end//

update t1 set name = 'xxoo' where id = 2//

create trigger tg_3 after delete on t1 for each row
begin
delete from t2 where id = old.gid;          // old.gid 这里面的gid是t1表与t2表相关的id号
end//
delete from t1 where gid = 2//    gid=2   ====> old.gid

//查看触发器
show triggers;
//删除触发器
drop trigger 名字;

MySQL读写锁

读锁：你和别人的终端都能读取数据，单不能进行写入(增删改)操作

	使用 	lock table 表名 read;
	解锁 	unlock tables;

写锁：你的终端对表加了写锁后，你的终端可以对表进行读写操作，但其他终端不能读写此表

	使用 	lock table 表名 write;
	解锁 	unlock tables;

MySQL权限管理

格式:
    grant 权限 on 数据库.数据表 to 用户名@登录主机  identified  by '密码'

举例:
    grant select,insert,update,delete on . to  'xxx'@'%' identified by "12345"  
         
----> %的问题
如果这样设置权限的话是非常不安全的,所用的用户都可以进入到你的数据库,对里面的所有的数据表进行增删改查

安全的做法

grant select,insert,update,delete on pass.* to 'root'@'localhost'  identified by '12345'   
只针对localhost主机里面的  pass数据库里面的数据表进行增删该查

MySQL表数据的备份与恢复操作

使用SQL语句备份和恢复

你可以使用SELECT INTO OUTFILE语句备份数据，并用LOAD DATA INFILE语句恢复数据。
这种方法只能导出数据的内容，不包括表的结构，如果表的结构文件损坏，你必须要先恢复原来的表的结构。

格式:

备份操作
SELECT * FROM tbl_name INTO {OUTFILE | DUMPFILE} 'file_name.txt' ;
恢复操作
LOAD DATA LOW_PRIORITY INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name;

举例:
导出
select * from t2 into outfile '../lamp185.txt';  //相对于mysql.exe文件的路径
清空
truncate table t2;

导入
load data infile '../lamp185.txt' into table t2; 

定位慢语句

1.修改mysql配置文件my.ini
		[mysqld]
		slow-query-log=1   # 开启记录慢查询的操作
		slow-query-log-file=slow.log  #文件默认存放在mysql的data目录下
		long-query-time=1  #超时时间   不能使用小数
2.重启mysql服务

SQL语句优化

1.查询	
		1. 加适当的索引 注意：索引不易太多 
		2. 缓存 
		3. 表分割
2.增，删，改
		1. 数据库表看看是不数据太多了
		2. 看看电脑的cup使用情况 
		3. 检测表的索引是不是太多了 因为索引太多会占用资源
		4. 读写分离，分库分表、