mysql 笔记1

一.数据库命令

1.字符集
SHOW VARIABLES LIKE 'character%'   查看数据库的默认字符编码
通过命令  set  
如果不是utf-8 在 my-default.ini  配置文件中
 5.6版本以前  default-character-set=utf8
 5.6版本添加  character-set-server=utf8

alter database note  default char set 'utf8';
ALTER TABLE t1 DEFAULT CHARACTER SET=utf8; 

show databases;

create database study;

show create database study;
drop database  study;
show engines ;
/*
   innodb:支持事务,支持外键，行锁，表空间，1.共享表空间：所有表的数据索引保存在一个文件中。2.独立表空间：每个表中的数据发在.idb文件中

             show variables like "innodb_file_per_table" 查看是否使用 独立表空间

   myisam: 支持全文索引，表锁
   memory：数据放在内存中，断电 数据会丢失。 支持哈希索引
*/

use study;

二用户命令

/*权限表

   user 表
   db
   host
   tables_priv
   columns_priv
   procs_priv
   
   create user  'user'[@'host'] identified by 'mypass' | [ password  ' 加密的密码']
   
   grant privileges on db.tables TO user@host [identified by 'password'] []
   
   grant all on  赋值所有的权限
   
   revoke all on  清除所有的权限
   
   root 忘记密码
   windows 
   1.net stop MySql
   2.mysqld --skip-grant-tables
   3. 新窗口  
    mysql -u root
   update mysql.user set password=password('123456') where user='root' and host='localhost';
   flush privilege
    
   linux  
   mysql_safe --skip-grant-tables user=mysql
*/


select * from mysql.user;
select * from mysql.db;
select * from mysql.host;
select * from mysql.tables_priv;
select * from mysql.columns_priv;
select * from mysql.procs_priv;

create user  'u1'@'127.0.0.1' identified by 'mypass';
select password('mypass');

grant select , update  ON *.* To 'testUser' identified by 'testpwd';

drop user u1;


FLUSH PRIVILEGES;
set password=password('root');


FLUSH PRIVILEGES;
set password for 'xm'@'127.0.0.1' =password('xumeng');

三.表

create table t1(
  id int(11) primary key,
  nick varchar(100) not null
);

insert into t1(id,nick)values
(1,'1'),
(2,'2');

create table t2(
  uid int(11),
  uname varchar(100),
  tid int(11),
  field1 varchar(10) not null,
  field2 varbinary(10) unique,
  field3 varbinary(10) default '1',
  field4 int(11) auto_increment, /** 只能包含一个自增类型，且需要为主键  **/
  primary key(field4),
  constraint fk_t1_tid foreign key(tid) references t1(id)
);

create table t_user2 like t_user;
show create table t_user2;
insert into t_user2 select * from t_user;

-- ALTER TABLE `t_user`  ADD unique(`username`);
show create table t1;
desc t2;
drop table t2;
alter table t2 rename t22;
alter table t22 rename t2;
show tables;
alter table t2 modify field1 int(11);
alter table t2 change field1  field11 varchar(100);
alter table t2 add field5 varchar(100) not null;
alter table t2 add field6 varchar(100) not null;
alter table t2  drop field6 ;

alter table t2 drop foreign key fk_t1_tid;

alter table t2 engine=memory;

alter table t2 engine=innodb;

alter table t2 add foreign key fk_t1_tid(tid) references t1(id);

drop table if exists t3,t4;

四.数据类型和运算

   数字类型 ：tinyint  smallint  mediumint  int  bigint  (1,2,3,4,8）字节 

                    int(11)表示显示的宽度，与数据的范围无关。unsigned 无符号位。
                    float  double   decimal(m,n)  m:总位数，n：精度
   时间：year  time  date  datetime(1000-01-01:0~ 9999)   

  时间函数   sysdate() , now() , curdate() ,current_date()  curtime()  
     sysdate()                  now()                           curdate()     current_date()
    2017-09-01 14:33:462017-09-01 14:33:46 2017-09-012017-09-01

              

   日期加减格式化      date_add('2017-01-01', interval 3 day) , date_sub(now(), interval 3 hour) ,date_format(now(),'%Y%m%d') ;

   周     select weekday('2017-09-01') , dayname('2017-09-01') ;

   
   字符：char 固定长度  字符的长度，不是字节的长度      varchar  text 2^16  enum 

            设置默认字符

   二进制： binary      varbinary blob     
   
   操作符：  is null  ,is not null , in   not in; least(t1,t2) 最小的 , greatest（)  between and；
             like(% ,-) regexp:   XOR 异或
             
    函数：数学运算函数，format(x,n) 保留几位小数
          字符函数 ：char_length('length');
                     连接 concat('t1','t2') concat_ws(分割符,s1,s2) ; 
                     大小写装换 lower  upper  ;
 左右几位left(s,n)  right(s,n);
                      左右填充lpad rpad, 
                      截取空格 trim  trim(s1 from s) 删除s两端的s1
                       repeat('sd',5)
                       replace(s,s1,s2) 用s2替换s中的s1
                       strcmp(s1,s2)比较字符串
                       substring(s,n,length)截取字符
                       locate(str1,str) 返回 str1 在 str 中的位置
                       insert(s1,x,len,s2) 返回在s1 x位置插入的字符s2
       
         
          条件函数 if(expr,v1,v2)  ifnull(v1,v2)  case expr when v1 then 'v1' when v2 then 'v2' else more end;
          
          系统函数：version(); connection_ID() ;show processlist;
user() current_user() system_user() 
                     charset()
                     last_insert_id()
           加密： password(str) md5(str) encode(str,key) decode(str,key)          



select format(123.3123,2);

select least(10,23,25) ,10 between 5 and 100 , 12 in(2,3,12) ,'stu' like 'st_' , 'A2123' regexp('a[0-9]{1,10}');

select concat_ws('*','item1','item2','item3') , upper('item'),substring('1234567',3,3),locate('all','ball');

select now() ,sysdate(), curdate(),curdate()+0,curtime() ,adddate(now(), interval 3 hour) ,date_format(now(),'%Y%m%d') ;

select  if(2>5,'yes','no') , ifnull(null,10) ,case 2 when 1 then '1' when 2 then '2' else 'more' end;

set @v='192.168.2.5';
select  @v ,inet_aton(@v)  , inet_ntoa(inet_aton(@v) ) ;

五.视图

   create [or replace] [algorithm={undefined | merge | temptable}]
view view_name [(column_list)]
        as select_statement
       (with [cascaded | local] check option) 

drop view if exists emview;

drop view if exists emview2;


create view emview(a,b)
as select e_no,e_name from employee;
    
create  view emview2(no,name,dname)
as select e.e_no,e.e_name,d.d_name from employee e,dept d where  e.dept_no=d.d_no;
  
 select * from emview; 
 
 select * from emview2;
 
 describe emview;
 
 show table status like 'emview';
 
 show create view emview;
 
 select * from information_schema.views

六.查询

 索引的建立：unique|fulltext（MyIsam）|spatialMyIsam）  index [index_name] (col_name(length:索引的长度))
alter table suoying add index key3(field3);
alter table suoying drop index key1;  

explain select * from suoying where field3  like'123%';   /* rows 表示扫描的行数 ，key 表示使用的索引*/

 like 第一个字段为%不会使用索引

alter table  suoying  disable keys;

alter table suoying enable keys;

对一个表添加新的索引，在数据量比较大的时候会操作比较慢，在期间对表的修改，添加数据会被阻塞。

   limit  4,3
    
     内连接 只有满足条件的数据才会出现
     left  join  左边表的数据会包含，
     
     子查询
       > any|some|all (select num from t2 )
        exists  in
        union 合并结果集

       explain  select  * from t1

    is  null   ，  or   like '%sd'  会失去索引，

  where 多个条件的顺序 会 影响 查询效率。

小结果集驱动大结果集

七.系统变量

show variables;

set @@log_bin='log_bin';

八.过程

/*
   创建存储过程  执行者的权限分配
   
   mysql 定义条件
   
   if case while loop

*/

drop procedure if exists employeecont;

/*登录存储过程*/
DELIMITER //
create procedure employeecont
(
out num int
)
BEGIN
 declare v int default 100;
 declare v2 int ;
 declare curso cursor for select v, v2  ;
 
 set v2=v+1;
 select count(1) into num from employee;
 open curso;
end //
DELIMITER ;


call employeecont(@num);
select @num;

show procedure status like '%employeecont%';

show create procedure employeecont;

select * from information_schema.ROUTINES where routine_name='employeecont';

/*
create function fangfai(in v1 int,in v2 int)
begin
 returns int;
 declare temp;
   set temp=v1+v2;
       return temp;
end;
*/

delimiter //
CREATE PROCEDURE  due(
  in base int,
    in length int
)begin
   /*  DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1;
  
 insert into t_user3(id,name)value(3,'sd');
   insert into t_user3(name)value('123'); 
   
    if result_code=1 then
      rollback;
    else   
       commit;
     end if;   */
   
   declare i int default 0; 


start transaction;
while i <length do
insert into t_user3 (id,name) values (base+i,base+i); 
set i = i +1; 
end while; 
 commit;
  
end //
delimiter ;


call due(1,1000000);

九.触发器

/*
    触发器 一个表的触发器不能再
    
    when you insert a record mysql is doing some lock stuff. 
    you can't insert/update/delete rows of the 
    same table where you insert.. because then the trigger would called again and again.. ending up in a recursion


    create trigger trigger_name trigger_time[before|after] trigger_event[ insert, update, delete]
    on table_name fro each row trigger_stmt [begin end]
    
    当某个字段更新的时候，
    
new是新插入的数据，old是原来的数据
insert只会有new，代表着要插入的新记录
delete只会有old，代表着要删除的记录
*/
use crmxun;
select * from employee;
drop trigger if exists employeeupdate;
create trigger employeeupdate after update on employee  for
  each row  set @selery=new.e_salary;
  
  update  employee set e_salary=1500 where e_no=1001;
  
  show triggers ;update  employee set e_salary=1500 where e_no=1001;

select @selery;

show triggers;
select * from information_schema.TRIGGERS where trigger_name='employeeupdate'

十.日志

/*
二进制日志 log_bin  记录所有更改数据的语句，可以用于数据复制。
错误日志  log_error
查询慢的日志  slow_query_log  long_query_time  开发时候开启  slow_query_log_file 文件路径
通用日志 general_log  所用的操作
*/

show variables like   'log%' ;
show variables like 'log_error';
show variables like 'log_bin%';

在my-default.ini 中设置 log_bin='log_bin'

查看日志记录的文件

\mysql-5.6.25-winx64\bin>mysqlbinlog --base64-output=DECODE-ROWS -v ../data/log_bin.000001

show variables like 'slow_query_log';
show variables like  'long_query_time';
show variables like 'slow_query_log_file';
set  global slow_query_log=1;

show variables like 'general_log%';
set global general_log=1;

optimize   table tal_name 优化表格中的碎片