mysql 5.1 学习笔记

drop database if exists mysqlstudy; 
create database if not exists mysqlStudy;

use mysqlstudy;

create table t_user(
 userid int AUTO_INCREMENT,
 userName varchar(20),
 primary key (userid)
);
create table t_role(
 roleid int auto_increment,
 roleName varchar(20) not null,
 primary key (roleid),
 index ind_first (roleName(4))
);
create table t_user_role(
 infoid int auto_increment,
 userid int not null references t_user(userid),
 roleid int not null references t_role(roleid),
 primary key(infoid),
 foreign key (roleid) references t_role(roleid),
 foreign key (userid) references t_user(userid)
);
/*创建索引*/
create index ind_second on t_user(userName);
create index ind_third on t_role(roleName);

insert into t_user values (null,'你好'),(null,'小胖子'),(null,'大胖子'),(null,'胖子'),(null,null);
insert into t_role values (null,'good'),(null,'isw2'),(null,'mail'),(null,'haha');
insert into t_user_role values (null,1,2),(null,2,2),(null,4,2),(null,1,4);

/* 第一个查询将一无所获,第二个查询不会包含null项;null 为不确定因素,使查询无法进行 */
select * from t_user where username not in ('你好','小胖子',null);
select * from t_user where username in ('你好','小胖子',null);
select * from t_user where username is null;
select userid,if(username is null,'暂无',username) as username from t_user;
select userid,(case username when username then username else '暂无' end) as username from t_user;

select * from t_role;
select * from t_user_role;

/* 存储过程 */
delimiter $$
/* delimiter $$ 用于定义结束符代替";" */
drop procedure if exists pro_first $$
create procedure pro_first()
BEGIN
 select 'good'; 
END$$
DELIMITER ;

call pro_first;

delimiter //
drop procedure if exists pro_second //
create procedure pro_second(var_name char(40))
LABEL_PROC:
begin
    declare v_sql varchar(200);
    set v_sql = 'select * from t_user where userName like ? limit 0, 3 ';
    set @sql = v_sql;
    set @var_name = var_name;
    prepare sl from @sql;/* 定义 预定义语句  */
    execute sl using @var_name;/*执行语句*/
    deallocate prepare sl;/*解除分配预定义语句*/
end LABEL_PROC //
delimiter ;

call pro_second('%胖%');

/* 自定义函数 */
delimiter $$
drop function if exists fun_first $$
create function fun_first() returns varchar(20)
begin
 return 'good';
end $$
delimiter ;

select fun_first();

/*触发器*/
delimiter $$
drop trigger if exists tri_first $$
create trigger tri_first after insert on t_user for each row
begin
insert into t_user_role values(null,NEW.userid,4);
end $$
delimiter ;
insert into t_user values(null,'谷歌');

/* 视图 */
drop view if exists vie_first;
create view vie_first as
select tur.infoid as number ,tu.userName as userName,tr.roleName as roleName 
from t_user_role as tur left join t_user as tu on tur.userid = tu.userid
			left join t_role as tr on tur.roleid = tr.roleid;

select * from vie_first;

explain select * from vie_first;


delimiter $$
drop procedure if exists pro_second $$
create procedure pro_second()
begin
declare uname varchar(20) default 'root';
select username into uname from t_user where userid = 4;
select uname;  
end $$
delimiter ;
call pro_second;


explain t_user;
/* Extra :Select tables optimized away  这里仅仅查询索引,没有查询数据 */
explain select max(userid),min(userid) from t_user;
/* extended关键字时,explain产生附加信息,通过show warnings 浏览.显示重写并且执行优化规则后select语句是什么样子 */
explain extended select * from t_user where 1=1 and 2 = 2;
show warnings;
/* 显示优化结果select `mysqlstudy`.`t_user`.`userid` AS `userid`,`mysqlstudy`.`t_user`.`userName` AS `userName` from `mysqlstudy`.`t_user` where 1 */
explain select userName from t_user;
explain select * from t_user where userid =4;
/* 强制使用指定索引查询 use index (ind_second) */
/* like'%胖' 无法使用索引 */
explain select * from t_user where username like '%胖';
explain select * from t_user use index (ind_second) where username like '胖%'; 


/* 用于分析表达式 */
select benchmark(1000,1+1);
/* 修复索引 */
analyze table t_user;
show index from t_user;


/* 查询 指定 自定义函数,视图,存储过程 */
show create function fun_first;
show create view vie_first;
show create procedure pro_first;
/*显示表的相关信息*/
show create table t_user;
/* 显示索引详情 */
show index from t_user;
show tables;
/*显示表结构*/
describe t_user;

/*系统表相关查询*/
use information_schema;
/*查询索引相关*/
select * from STATISTICS where TABLE_SCHEMA = 'mysqlstudy';
/*查询数据表相关*/
select * from tables where table_schema = 'mysqlstudy';
/*查询相关触发器*/
select * from triggers where trigger_schema = 'mysqlstudy';
/* 查询用户权限 */
select * from USER_PRIVILEGES;
/*查询表权限*/
select * from TABLE_PRIVILEGES where table_schema = 'mysqlstudy';
/*查询列权限*/
select * from COLUMN_PRIVILEGES where table_schema = 'mysqlstudy';
/*查询表约束*/
select * from TABLE_CONSTRAINTS where table_schema = 'mysqlstudy';
/*查询键约束*/
select * from KEY_COLUMN_USAGE where TABLE_SCHEMA = 'mysqlstudy' ;
/* 查询自定义函数,存储过程 */
select * from routines;
/*查询视图*/
select * from views;
/* 查询触发器 */
select * from triggers;


show status;
show variables;
show processlist;