MySQL高级
##数据表的引擎对比
MySQL支持MyISAM ,InnoDB, HEAP, BDB, ARCHIVE, CSV等多种数据表引擎类型
1.InnoDB 是较新的事务安全型存储引擎，用于事务处理应用程序，支持BDB的几乎所有特性，并具有众多新特性，包括ACID事务支持。
InnoDB表类型
特点: InnoDB给MySQL提供了具有提交,回滚和崩溃回复能力的事务安全存储引擎.InnoDB也支持外键机制,InnoDB类型的表与去他MySQL的表类型混合起来,在同一查询中也可以混合
缺点: InnoDB数据表的空间占用量要比同样内容的MyISAM数据表大很多,另外,该类型不支持全文索引
特性：
1.事务处理机制
2.支持外链
3.崩溃后能立即恢复
4.支持外键功能，级联删除
5.支持并发能力
6.在硬盘上的存储方式：InnoBDB frm
7.最新版本的Mysql已经计划移除对BDB的支持，转而全力发展InnoDB。InnoDB对Mysql有更好的特性支持，而且开发社区活跃。
2.MyISAM 是默认的MySQL插件式存储引擎，它是基于ISAM类型，但它增加了许多有用的扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎
MyISAM表类型
优点：
1.比ISAM表更小，所占资源更少
2.可以在不同平台间二进制移植表的类型在创建表时指定。
特点: 成熟,稳定,和易于管理,他使用一种表格锁定的机制来优化多个并发的读/写操作,MyISAM强调了快速的读写操作,所以在web前端会有大量的数据要进行读取操作
MYISAM和InnoDB的总结: 如果希望以最节约空间和时间挥着响应速度快的方式来管理数据表,MyISAM数据表就应该是首选,如果应用程序需要用到事务,使用外键或需要更高的安全性,以及需要允许很多用户同时修改某张数据表的数据,那么InnoDB更值得考虑
3.ISAM 是MyISAM类型出现之前MySQL表使用的默认类型，现在已经被MyISAM代替。
4.Memory （HEAP） 是MySQL表中访问最快的表，将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。注意，这种类型下数据是非持久化设计的，它一般适应于临时表，如果MySQL或者服务器崩溃，表中数据全部丢失。
5.Merge 是一种值得关注的新式表，它是由一组MyISAM表组成，之所合并主要出于性能上考虑，因为它能够 提高搜索速度，提高修复效率，节省磁盘空 间。允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。有时它以 MRG_MYISAM 名称出现。
6.Archive 为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
7.Federated 能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
8.NDBCluster/Cluster/NDB MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。
9.CSV 使用标准的CSV格式，由逗号隔开的文本文件，适应于外部数据交换
10.Blackhole 它会接受数据但不保存，而且对任何检索请求返回一个空集。它一般适应于数据会被自动复制并不进行本地保存的分布式数据库设计。（不确定的）似乎也可以用于临时禁止/忽略对数据库的应用程序输入。
11.Example 这是一个测试引擎，你可以创建一个这样的表，但既不能写入数据，也不能检索数据。它似乎是一个针对MySql开发者提供的示例引擎。
查看默认的引擎
show variables like 'default_storage_engine';
查看支持的引擎
show engines
查看当前库里面所有的表引擎
show table status from 库名
修改表引擎
alter table 表名 engine = 新的表引擎
查看当前选择的数据库
select database()
查看当前的数据库的版本号
select version()
创建一个表结构和另一个表一样的结构
create table t3 like t1;
那么现在t3表的结构和t1表的结构是一样的,但是要注意数据是没有的
## 数据表创建索引
索引在数据库开发中起着非常重要的作用,通过在表字段中建立索引可以优化查询,确保数据的唯一性.并且可以对任何全文索引字段中大量文本的所搜进行优化,在MySQL中有4类索引:主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、常规索引(INDEX)、全文索引(FULLTEXT).
查看表中都有哪些索引
show index from 表名\G
如果在创建索引的时候,不添加索引名的话 默认会把字段名当做索引名
###常规索引
添加表字段的常规索引
create index 索引名 on 表名(字段名)
alter table 表名 add index 索引名(字段名)
在创建表的时候也可以进行添加
create table t2(
id int(10) unsigned auto_increment primary key,
name varchar(255), index 索引名(字段名)
);
删除表字段常规索引
drop index 索引名 on 表名
alter table 表名 drop index 索引名
###唯一索引
添加表字段的唯一索引
create unique index 索引名 on 表名(字段名)
alter table 表名 add unique (字段名)
在创建表的时候也可以进行添加
create table t2(
id int(10) unsigned auto_increment primary key,
name varchar(255), unique 索引名(字段名)
);
删除唯一索引
drop index 索引名 on 表名
###主键索引
添加表字段的主键索引
alter table 表名 add primary key (字段名)
添加自增
alter table 表名 modify id int(4) auto_increment
删除主键索引
如果字段有了auto_increment 和 primary key的时候,需要删除主键的话,
先删除自增,然后再删除主键
删除自增
alter table 表名 change 字段名 字段名 类名
删除主键
alter table 表名 drop primary key
在数据库中唯一索引和主键索引的区别
唯一索引是可以给每个字段进行添加的,添加完了之后字段里面的值就不可以重复了,主键索引和唯一索引类似,但是数据表里的主键索引只能加在一个字段里(一般都加在id上),id是自增的,索引不会有重复的时候出现
###全文索引
###全文索引
添加表字段的全文索引
alter table 表名 add fulltext (字段名)
删除全文索引
drop index (索引名) on 表名
alter table 表名 drop index 索引名
##修改表的字段信息
修改表的字符集
alter table 表名 character set utf8
修改字段的类型
alter table 表名 modify 字段名 类型
修改字段的名字并同时修改字段类型
alter table 表名 change 旧字段名 新字段名 字段类型
修改字段的字符集
alter table 表名 modify 字段名 类型 character set utf8
添加新字段
alter table 表名 add 字段名 类型
删除字段
alter table 表名 drop 字段名
修改表名
alter table 旧表名 rename as 新表名
##left join、right join、inner join
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
举例:
left join
select * from p1 left join p2 on p1.id = p2.id;
right join
select * from p1 right join p2 on p1.id = p2.id;
inner join
select * from p1 inner join p2 on p1.id = p2.id;
##子查询
1.单行子查询
select name,age,score
from user
where age=(select age from stu where id = 1)；
2.多行子查询
select name,age,class
from user
where age in (select age from stu where id < 10);
select name,age.class
from user
where age in (select age from stu where name like '王%');
##插入数据
1. 插入指定字段
insert into t1(字段1,字段2,字段3...) values ('值1','值2','值3',....);
2. 插入所有的字段
insert into t1 values ('值1','值2','值3',....);
3. 插入多条数据
insert into t1(字段1,字段2,字段3...) values ('值1','值2'),('值1','值2')....;
4. 插入结果
insert into t1(字段1,字段2,字段3...) select 字段1,字段2,字段3... from t1;
insert into t1(name,age) select name,age from t1;
5. 插入单条数据
insert into t1 set 字段1=值1,字段2=值2....;
##分组 group by
1. 查询各个班级中年龄大于20，性别为男的人数姓名和班级 (数据表: stu)
为了防止班级里面有重名的现象发生(例如 两个男生都是20岁 以上的) 所以会有count(*)
select group_concat(name),classid,count(name) from stu where sex = "男" and age > 20 group by classid;
2. 学校评选先进学生,要求平均成绩大于等于90分的学生,并且语文课必须在95分以上，请列出有资格的学生 (数据表: score)
select classid,name from sc where (yw+sx+en)/3 >= 90 and yw>=95 group by classid,name;
select classid,group_concat(name) from sc where (yw+sx+en)/3>=90 and yw>=95 group by classid;
3. 用一条sql语句查询出每门课都大于80分的学生姓名 (数据表: courseinfo)
select name from courseinfo group by name having min(score) > 80;
select DISTINCT name from courseinfo where name not in (select distinct name from courseinfo where score < 80);
##创建视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
如果你想创建视图的话 首先你的数据库要有创建视图的权限,不然的话是创建不成功的
create view v1 as select * from t1 where id > 1;
创建v1的视图 把查询t1表里面的符合条件的数据放到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存储过程
```
简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能
MySQL存储过程的创建
创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...]过程体
举例说明：
修改结束符
mysql-> delimiter //
创建
mysql-> create procedure proc()
-> begin
-> select * from user;
->end//
使用
mysql-> call proc()//
修改结束符
mysql-> delimiter ;
这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样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//
注意 : 写存储过程名字的时候 不要使用关键字 和 数字
例如:
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 t2 read
解锁
unlock tables;
写锁: 当前的终端对表进行写锁之后,代表本终端可以进行读写操作,但别的终端不可以进行读写操作
lock table t2 write;
解锁
unlock tables;
```
##mysql的权限管理
格式:
grant 权限 on 数据库.数据表 to 用户名@登录主机 identified by '密码'
举例:
grant select,insert,update,delete on \*.\* to wjs@'%' 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][LOCAL] 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;
```
##开启慢查询记录
需要进行配置 在mysql中的my.ini中进行设置
slow-query-log = 1 # 开启慢查询操作
slow-query-log-file = slow.log # 文件默认存放在mysql的data目录下 (文件名自定义)
long-query-time = 1 # 超时时间 如果执行的sql语句超过1秒钟就把语句存放到slow.log文件中 (不能使用小数)
