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;