MySql日常开发涉及知识

 

1.oracle 与 mysql区别(待完善)

    1)mysql识别单引号和双引号,oracle只识别单引号

    2).mysql有 ifnull , oracle 有nvl [如果为空,设置默认值]

    3).mysql有与or,and,not 等价的|| && !, oracle只有 or,and,not

    4).mysql默认转义字符\,也可以使用escape 'character' 来定义, oracle只能使用escape方式

    select * from where name like '_a_zhangsan' escape 'a';

    5).流程控制函数

        mysql:

          if(expr, condition1, condition2) //如果条件为真返回condition1,否则返回condition2

         if(lastName='my','my', 'xindanding')

      case用法:

•  等值判断

select lastName, case id when 1 then '上海' when 2 then '北京' else "湖南" end from employee

• 条件判断

select lastName, case when id=1 then '上海' when id>=2 and id < 4 then '北京' else '广州' end as '地点' from employee;

oracle:

decode: decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

case 用法如上

6). oracle 计算必须用dual虚表

    mysql: select 1+2; select 1+2 from dual;

    oracle: select 1+2 from dual;

7).字符连接

    mysql: concat(c1,c2,....)

    oracle: 'c1'||'c2'||... 也有 concat(c1,c2,....)

8).日期转换

    mysql: str_to_date(value, pattern) 字符串转日期 select str_to_date('2020-01-01', '%Y-%c-%d')

    date_format(value, pattern) select date_format('2020-01-01', '%Y-%m-%d')

    oracle: to_date(value, pattern) 转日期 to_char(value, pattern) 转字符串

9).字符串截取

    mysql: substr() 与 substring()等价 substr('xindanding',1,3) //xin

    oracle: 只有substr();

 

2.复制表：

create table t1 like srctable;

只是复制表结构 不会复制表数据

create table t1 【as】select * from srctable [where ...]

复制表的同时会复制表的数据

 

3.联合删除

delete e.*,d.* from emp e, dept d where e.deptid = d.id;

4.单表删除

delete from emp;

delete emp.* from emp

单表删除 要么不用* 要么加上表名或者表的别名

 

5.修改表结构：

修改列名

alter table t1 change column c1 newname newtype;

修改类型

alter table t1 modify column c1 newtype;

增加列

alter table t1 add column newcolumn newtype;

删除列

alter table t1 drop column c1;

表重命名

alter table t1 rename to t2;

 

6.约束:

1. 创建表时增加约束:

create table class(

id int primary key, --列级约束

name varchar(10) not null,

classNo varchar(10),

stuId int,

constraint fk_sid foreign key(stuId) references user(id), --表级约束

constraint cno unique(classNo)

)

constraint 名称 约束名(列) 默认以字段名为约束名

   2.修改表时增加约束

• 增加列级

alter table 表名 modify column 字段名 字段类型 新约束;

• 增加表级

alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]

alter talbe 表名 add constraint fk foreign key(sid) references student(id)

    3.删除约束

mysql:

alter table drop primary key| foreign key [名称]| index [名称]

oracle:

alter table drop constraint 名称

 

7.savepoint使用:

.... --执行多条sql语句

savepoint pointName;

..... --执行多条sql语句

rollback to pointName; //回滚到savepoint结点

 

 

8.视图: (作用 将公共的sql抽取出来)

create view viewName

as

select 语句 [with check option ]

视图只能对单表进行更新,不能对带有group by ,子查询,join等的语句结果更新

with check option 只能对select 的视图查询结果进行更新

更改视图:

1)create or replace view viewName as....

2)alter view viewName as ...

 

9.全局变量: 默认为session

• 查看全局变量 show [global|session] variables;
• 查看部分全局变量 show [ global|session ] variables like '%内容%';
• 查询全局变量的值 select @@[global|session .] 变量名 //select @@global.autocommit
• 为全局变量赋值 set @@[global|session .] 变量名=值

 

10.用户变量: 只在session内有效

• 定义并初始化

set @用户变量名=值

set @用户变量名:=值

select @用户变量名:=值

• 赋值

1) set @用户变量名=值

set @用户变量名:=值

select @用户变量名:=值

2) select 字段 into @变量名 from 表;

• 使用

select @用户变量名

 

11.局部变量: 只在begin end中有效

• 声明

declare 变量名 类型 [default 值]

• 赋值

1) set 局部变量名=值

set 局部有变量名:=值

select @局部变量名:=值

2) select 字段 into 变量名 from 表;

• 使用

select 局部变量名

 

12.存储过程:

create procedure pname ([模式名 变量名 变量类型][...]) //模式 in输入参数 out输出参数 inout输入输出参数

begin

.... //只有一条语句时,begin, end可以省略

end;

1)带in参数使用:

delimiter $; //声明存储过程结束标识

create procedure myp1(in ename varchar(20))

begin

select * from employee where lastName = ename;

end $;

 

call myp1('zhangsan');

2)带 in out参数使用

drop procedure if exists myp2;

delimiter $;

create procedure myp2(in lastName varchar(20), out email varchar(20))

begin

select e.email into email

from employee e

where e.lastName = lastName;

end $;

 

call myp2('张三', @email);

select @email;

3)带 inout 参数

drop procedure if exists myp3;

delimiter $;

create procedure myp3(inout v1 int, inout v2 int)

begin

set v1=v1+v2;

set v2:=v1*v2;

end $;

 

set @v1=10;

set @v2:=20;

call myp3(@v1, @v2);

select @v1,@v2;

 

13.查看存储过程:

show create procedure pName;

14.删除存储过程

drop procedure pName;

 

 

15.函数:

mysql可能没有开启函数 功能

show variables like '%func%';

set global log_bin_trust_function_creators=1;

create function fName([参数列表]) //参数名称 参数类型

returns 参数类型

begin

...

return val;

end

调用:

select fName([参数列表])

案例:

delimiter $

create function myfun1(num1 float, num2 float)

returns float

begin

declare sum1 float default 0;

set sum1=num1 + num2;

return sum1;

end $

 

16.查看函数

show create function fName;

17.删除函数

drop function fName;

 

18.case 作为表达式 VS case 用在存储过程或函数中:

case 作为表达式:

1)case 表达式 (只能用于select 后)

when 值1 then 值1

when 值2 then 值2

...

else 值n

end;

2)case 表达式

when 条件1 then 值1

when 条件2 then 值2

...

else 值n

end;

case作为独立的语句:

1)case 表达式

when 值1 then 语句1;

when 值2 then 语句2;

...

else 语句n;

end case;

2)case

when 条件1 then 语句1;

when 条件2 then 语句2;

...

else 语句n;

end case;

 

case在 begin...end中的使用案例

delimiter $

create function myfun2(score int)

returns char

begin

declare msg char default null;

case

when score >=90 and score <=100

then set msg='A';

when score >=80

then set msg ='B';

when score >=70

then set msg='C';

else set msg='D';

end case;

return msg;

end $

调用:

select myfun2(99);

 

 

19.if用在存储过程或函数中:

if 条件1 then 语句1;

elseif 条件2 then 语句2;

...

[else 语句n;]

end if;

 

使用案例:

delimiter $

create procedure myp5(in score int)

begin

if score >= 90 and score <=100

then select 'A';

elseif score >=80

then select 'B';

elseif score >=70

then select 'C';

else select 'D';

end if;

end $

调用:

call myp5(99);

 

20.循环结构:

while,loop,repeat

循环控制

iterate 类似于 continue

leave 类似于 break

1)while

[标签:] while 循环条件 do

循环体;

end while [标签];

使用标签 可以结合 循环控制使用

drop procedure if exists myp6; delimiter $ create procedure myp6(in `count` int) begin declare i int default 1; m: while i <= count do if i%2 = 0 then leave m; end if; insert into employee(id, lastName) values(11, concat('xinzhi', i)); set i = i +1; end while m; end $ call myp6(20);

2)loop

[标签:] loop

循环体;

end loop [标签];

没有循环控制时,就是死循环

drop procedure if exists my7 ; delimiter $ create procedure my7(in times int) begin declare i int default 1; f: loop set i = i+1; if mod(i,2)=0 then iterate f; end if; insert into employee(id, email) values(12+i, concat('shanghai',i,'@qq.com')); if i=5 then leave f; end if; end loop f; end $ call my7(20);

3)repeat

[标签:] repeat

循环体;

until 结束循环条件;

end repeat [标签];

drop procedure if exists myp8; delimiter ! create procedure myp8() begin declare i int default 13; repeat delete from employee where id = i; set i = i + 1; until i=18 end repeat; end ! call myp8();

 

21.执行计划 explain + sql语句:

id select_type table type possible_keys key key_len ref rows extra

 

id:id相同,视为同一组, 从上往下顺序执行;id越大,优先级越高,越先执行

select_type:

simple: 简单select查询,查询中不包含子查询或UNION

primary: 查询中包含复杂的子部分,最外层标记为primary

subquery: select 或where中包含子查询

derived(派生,衍生): from 列表中包含的子查询被标记为derived;mysql会递归执行这些子查询,把结果放到临时表里

union: 若第二个select出现在union之后,则被标记为union;

union result: 从union表获取的结果

 

type: 从好到差 system>const>eq_ref>ref>range>index>all (比较常用的)

system: 表只有一行记录,const的特例

const: 通过索引一次就能找到,用于比较primary key 或unique索引;如将主键置于where列表中,mysql就能将查询转换为一个常量

eq_ref: 唯一索引扫描,对于每个索引键只有一条记录与之匹配;常见于主键索引或唯一索引扫描

ref: 非唯一性索引扫描,返回匹配某个单独值的所有行,可能会找出符合条件的多个索引行

range: 检索给定范围的行,使用一个索引来选择行.key列显示使用了哪个索引;一般 在where中使用between,<,>,in等查询

index: 和all一样 全表扫描,但是index从索引中读取,all从硬盘读取

all: 全表扫描

 

possible_keys: 显示可能应用到这张表中的索引,一个或多个;查询涉及到的字段若有索引,则该索引被列出,但不一定被查询实际使用

 

key: 实际使用的索引. 如果为null,则没有索引.查询中若使用了覆盖索引,则该索引仅出现在key列表中

 

key_len: 显示的值为索引字段的最大可能长度,并非实际长度

 

ref: 显示索引的哪一列被使用.如果可能的话,是一个常数.哪些列或常量被用于查找索引列上的值

 

rows: 根据表统计信息及索引选用情况,记录所需读取的行数

 

extra:

using filesort: 对数据使用一个外部的索引排序.mysql中无法利用索引完成的排序操作称为文件排序

主要出现在sql语句有order by的情况 解决:order by 后的字段走索引

using temporary: 使用临时表保存中间结果

主要出现在sql语句有group by的情况 解决: group by 后的字段走索引

using index: 表示相应的selec操作中使用了覆盖索引

using where: 使用了where进行过滤

using join buffer: 使用了连接缓存

impossible where: where子句的值总是false,不能用来获取任何元组

select tables optimized away: 在没有group by情况下基于索引优化

distinct: 优化distinct操作,在找到第一个匹配的元组后即停止找同样值的动作

 

22.两表关联优化:

左连接,右表建立索引;右连接,左边建立索引

 

23.索引优化:

假设有employee表 id,lastname,email,gender字段 id为主键 (lastname,email,gender)存在一个索引 gender是数值

1).索引坚持从左到右匹配

select * from employee where lastname='z'; --lastname走索引

select * from employee where lastname='z' and email ='z'; --lastname email走索引

select * from employee where lastname='z' and email ='z' and gender = 1; --lastname email gender走索引

select * from employee where lastname='z' and gender = 1; --只有lastname走索引

2).对索引列使用函数,计算等会导致索引失效

select * from employee where left(lastname,2)='my' --不走索引

3).存在范围条件的,会导致范围条件之后的索引失效

select * from employee where lastname='my' and email !='z' and gender= 1; --lastname email走索引

4).尽量使用覆盖索引(查询的字段就是索引包含的字段)

查询的lastname,email,gender存在一个索引

select lastname,gender from employee where lastname='my' ;

select lastname,email from employee where lastname='my' and email ='z' ;

select lastname,email,gender from employee where lastname='my' and email ='z' and gender= 1;

gender改为字符串类型

5).使用!=或<>

select * from employee where lastname = 'z' and email ='z' and gender != '222';

mysq5.0l会导致索引失效,mysql8会使用索引 lastname不能使用!= 否则全表

6).使用is null, is not null

根据版本而定

select * from employee where lastname = 'z' and email ='z' and gender is null;

explain select * from employee where lastname = 'z' and email ='z' and gender is null;

我是用的是mysql8以上版本 gender字段都是走索引的 mysql5.X不会走索引

7).使用like

explain select * from employee where lastname = 'z' and email ='z' and gender like'1%'; --lastname email gender走索引

explain select * from employee where lastname = 'z' and email ='z' and gender like'%1'; --lastname,email走索引 gender不走索引

explain select * from employee where lastname = 'z' and email ='z' and gender like'%1%'; --lastname,email走索引 gender不走索引

8).字符串不加引号

select * from employee where lastname = 'z' and email ='z' and gender = 1; --lastname,email走索引 gender不走索引

select * from employee where lastname = 'z' and email ='z' and gender = '1'; --lastname,email,gender走索引

9). 使用or

和mysql版本有关 我是用的是mysql8以上版本 mysql5.X不会走索引

select * from employee where lastname = 'z' or lastname = 'a'; --lastname 走索引

10). 使用order by 与版本有关,我用的是mysql8.x 只针对排序,不考虑查询是否走索引

select * from employee order by lastname; --全表扫描 没用到索引

 

select * from employee where lastname = 'x' order by lastname,email desc,gender; --排序不走索引, 排序字段必须同增同减

select * from employee where lastname = 'x' order by email,gender; --当where中索引字段是常量时, where,order 根据从最左开始匹配索引 此处 排序是走索引的

 

select * from employee where lastname != 'x' order by lastname,email,gender; --排序不会走索引

select * from employee where lastname >= 'x' order by lastname,email,gender; --排序走索引

select * from employee where lastname >= 'x' order by email; --排序不走索引

 

24.小表驱动大表

select * from employee where did in (select id from department);

使用in 先执行子查询from department 然后执行 from employee 适合 employee数据量多于department的情况

select * from employee where exists (select id from department where employee.did = department.id)

使用exists 先执行外部查询from employee 然后执行 from department 适合 employee数据量少于department的情况

 

25.慢查询日志:

show variables like 'slow_query_log'

查询结果: 日志文件开关slow_query_log slow_query_log_file 日志文件存放地址; 如果需要永久性有效需要在配置文件中配置 .ini(Window) .cnf(Linux)

 

26.show profile : 分析当前会话中语句执行的资源消耗情况

show variables like '%profiling%'

查询结果 have_profiling 是否支持profile,默认YES ; profiling 开关,默认off ; profiling_history_size 保存最近运行的sql记录,默认15条

 

set profiling=on;

select * from employee;

show profiles;

查询结果:

Query_ID Duration(花费时间) Query(查询语句)

'1', '0.00012600', 'SHOW WARNINGS'

'2', '0.00037100', 'select * from employee LIMIT 0, 1000'

 

诊断sql: show profile cpu,block io for query query_id;

show profile cpu,block io for query 5;

 Status     Duration    CPU_user    CPU_system Block_ops_in  Block_ops_out 
'starting', '0.000064', '0.000000', '0.000000', NULL, NULL 
'Executing hook on transaction ',   '0.000009', '0.000000', '0.000000', NULL, NULL 'starting', '0.000009', '0.000000', '0.000000', NULL, NULL 
'checking permissions', '0.000008', '0.000000', '0.000000', NULL, NULL 
'Opening tables', '0.000036', '0.000000', '0.000000', NULL, NULL 
'init', '0.000008', '0.000000', '0.000000', NULL, NULL 
'System lock', '0.000010', '0.000000', '0.000000', NULL, NULL 
'optimizing', '0.000005', '0.000000', '0.000000', NULL, NULL 
'statistics', '0.000015', '0.000000', '0.000000', NULL, NULL 
'preparing', '0.000014', '0.000000', '0.000000', NULL, NULL 
'executing', '0.000002', '0.000000', '0.000000', NULL, NULL 
'Sending data', '0.000059', '0.000000', '0.000000', NULL, NULL 
'end', '0.000005', '0.000000', '0.000000', NULL, NULL 
'query end', '0.000004', '0.000000', '0.000000', NULL, NULL 'waiting for handler commit', '0.000010', '0.000000', '0.000000', NULL, NULL 
'closing tables', '0.000009', '0.000000', '0.000000', NULL, NULL 
'freeing items', '0.000089', '0.000000', '0.000000', NULL, NULL 
'cleaning up', '0.000016', '0.000000', '0.000000', NULL, NULL

参数type:

ALL --显示所有开销信息

BLOCK IO --显示块IO相关开销

CONTEXT SWITCHES --上下文切换相关开销

CPU --CPU相关开销

IPC --发送和接收相关开销

MEMORY --内存相关开销

PAGE FAULTS --错误信息相关开销

SOURCE --SOURCE_FUNCTION, SOURCE_FILE, SOURCE_LINE相关开销

SWAPS --交换次数相关开销