必做题目:https://sqlzoo.net/wiki/SELECT_basics/zh
常见的SQL面试题:经典50题,知乎:https://zhuanlan.zhihu.com/p/38354000
MySQL基础面试题总结:https://blog.csdn.net/whl190412/article/details/90081511
常见面试题:https://www.cnblogs.com/hsmwlyl/p/10719152.html
mysql 经典面试题:https://blog.csdn.net/weixin_42688876/article/details/81480311
2019最新21个MySQL高频面试题介绍:https://www.jb51.net/article/179725.htm
基础入门视频,B站:https://www.bilibili.com/video/BV1CW411E7C7?p=1
最全的MySQL基础【燕十八传世】:https://www.cnblogs.com/lms520/p/5427685.html
概念超全:http://c.biancheng.net/view/2361.html
mysql深入学习笔记及实战指南:http://www.notedeep.com/note/38/page/327
很全的mysql教程,偏高级教程:https://www.w3school.com.cn/sql/sql_join.asp
面试题:
事务四大特性
- 原子性:不可分割的操作单元,事务中所有操作,要么全部成功;要么撤回到执行事务之前的状态
- 一致性:如果在执行事务之前数据库是一致的,那么在执行事务之后数据库也还是一致的;
- 隔离性:事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 持久性:事务一旦提交,其结果就是永久的。即便发生系统故障,也能恢复。
事务隔离级别
未提交读(Read Uncommitted):允许脏读,其他事务只要修改了数据,即使未提交,本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
可重复读(Repeated Read):可重复读。无论其他事务是否修改并提交了数据,在这个事务中看到的数据值始终不受其他事务影响。
串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞
MySQL数据库(InnoDB引擎)默认使用可重复读( Repeatable read)
索引相关
数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用 B_TREE。B_TREE 索引加速了数据访问,因为存储引擎不会再去扫描整张表得到需要的数据;相反,它从根节点开始,根节点保存了子节点的指针,存储引擎会根据指针快速寻找数据。
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址,即:MyISAM索引文件和数据文件是分离的,MyISAM的索引文件仅仅保存数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。
InnoDB引擎也使用B+Tree作为索引结构,但是InnoDB的数据文件本身就是索引文件,叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚焦索引”。InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。InnoDB的索引实现后,不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。在Innodb中也不建议使用非单调的字段作为主键,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,建议使用自增字段作为主键。
MySQL数据库的四类索引:
index ---- 普通索引,数据可以重复,没有任何限制。
unique ---- 唯一索引,要求索引列的值必须唯一,但允许有空值;如果是组合索引,那么列值的组合必须唯一。
primary key ---- 主键索引,是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在创建表的同时创建主键索引。
组合索引 ---- 在多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。
fulltext ---- 全文索引,是对于大表的文本域:char,varchar,text列才能创建全文索引,主要用于查找文本中的关键字,并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎,配合match against操作使用,而不是一般的where语句加like。
注:全文索引目前只有MyISAM存储引擎支持全文索引,InnoDB引擎5.6以下版本还不支持全文索引
所有存储引擎对每个表至少支持16个索引,总索引长度至少为256字节,索引有两种存储类型,包括B型树索引和哈希索引。
索引可以提高查询的速度,但是创建和维护索引需要耗费时间,同时也会影响插入的速度,如果需要插入大量的数据时,最好是先删除索引,插入数据后再建立索引。
索引生效条件
假设index(a,b,c)
- 最左前缀匹配:模糊查询时,使用%匹配时:’a%‘会使用索引,’%a‘不会使用索引
- 条件中有or,索引不会生效
- a and c,a生效,c不生效
- b and c,都不生效
- a and b > 5 and c,a和b生效,c不生效。
检测索引的效果:
show status like '%handler_read%' 越大越好
sql语句分类:
- DDL:数据定义语言(create drop)
- DML:数据操作语句(insert update delete)
- DQL:数据查询语句(select )
- DCL:数据控制语句,进行授权和权限回收(grant revoke)
- TPL:数据事务语句(commit collback savapoint)
数据库三范式:
- 第一范式:1NF是对属性的原子性约束,要求字段具有原子性,不可再分解;(只要是关系型数据库都满足1NF)
- 第二范式:2NF是在满足第一范式的前提下,非主键字段不能出现部分依赖主键;解决:消除复合主键就可避免出现部分以来,可增加单列关键字。
- 第三范式:3NF是在满足第二范式的前提下,非主键字段不能出现传递依赖,比如某个字段a依赖于主键,而一些字段依赖字段a,这就是传递依赖。解决:将一个实体信息的数据放在一个表内实现。
脏读、幻读、不可重复读
脏读: 是指事务T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的。
不可重复读 :是指在数据库访问时,一个事务范围内的两次相同查询却返回了不同数据。在一个事务内多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么在第一个事务中的两次读数据之间,由于第二个事务的修改,第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
幻读: 是指当事务不是独立执行时发生的一种现象,比如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么就会发生,操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
不可重复读&幻读区别:
如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。需要Serializable隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。
不可重复读重点在于update和delete,而幻读的重点在于insert。如何通过锁机制来解决他们产生的问题
存储引擎 MyISAM 和 InnoDB区别:
- InnoDB支持事务,MyISAM不支持。
- MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及涉及到安全性较高的应用。
- InnoDB支持外键,MyISAM不支持。
- 从MySQL5.5.5以后,InnoDB是默认引擎。
- MyISAM支持全文类型索引,而InnoDB不支持全文索引。
- InnoDB中不保存表的总行数,select count(*) from table时,InnoDB需要扫描整个表计算有多少行,但MyISAM只需简单读出保存好的总行数即可。注:当count(*)语句包含where条件时MyISAM也需扫描整个表。
- 对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引。
- 清空整个表时,InnoDB是一行一行的删除,效率非常慢。MyISAM则会重建表。MyisAM使用delete语句删除后并不会立刻清理磁盘空间,需要定时清理,命令:OPTIMIZE table dept;
- InnoDB支持行锁(某些情况下还是锁整表,如 update table set a=1 where user like ‘%lee%’)
- Myisam创建表生成三个文件:.frm 数据表结构 、 .myd 数据文件 、 .myi 索引文件,Innodb只生成一个 .frm文件,数据存放在ibdata1.log
- 现在一般都选用InnoDB,主要是MyISAM的全表锁,读写串行问题,并发效率锁表,效率低,MyISAM对于读写密集型应用一般是不会去选用的。
- 应用场景:
-
- MyISAM不支持事务处理等高级功能,但它提供高速存储和检索,以及全文搜索能力。如果应用中需要执行大量的SELECT查询,那么MyISAM是更好的选择。
- InnoDB用于需要事务处理的应用程序,包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,这样可以提高多用户并发操作的性能。
CHAR和VARCHAR的区别:
- CHAR和VARCHAR类型在存储和检索方面有所不同
- CHAR列长度固定为创建表时声明的长度,长度值范围是1到255
- 当CHAR值被存储时,它们被用空格填充到特定长度,检索CHAR值时需删除尾随空格。
Mysql中的锁类型
- MyISAM支持表锁,InnoDB支持表锁和行锁,默认为行锁
- 表级锁:开销小,加锁快,不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发量最低
- 行级锁:开销大,加锁慢,会出现死锁。锁力度小,发生锁冲突的概率小,并发度最高
存储过程
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
优点:
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
delete、drop、truncate区别
- truncate 和 delete只删除数据,不删除表结构 ,drop删除表结构,并且释放所占的空间。
- 删除数据的速度,drop> truncate > delete
- delete属于DML语言,需要事务管理,commit之后才能生效。drop和truncate属于DDL语言,操作立刻生效,不可回滚。
- 使用场合:
- 当你不再需要该表时, 用 drop;
- 当你仍要保留该表,但要删除所有记录时, 用 truncate;
- 当你要删除部分记录时(always with a where clause), 用 delete.
注意: 对于有主外键关系的表,不能使用truncate而应该使用不带where子句的delete语句,由于truncate不记录在日志中,不能够激活触发器
学习笔记:
腾讯文档:https://docs.qq.com/doc/DQ0FEclFMaEF5TWpN
-
数据保存路径
C:\ProgramData\MySQL\MySQL Server 8.0\Data\yan
-
报错
1064 语法错误
1366 字符集问题 set names gbk;
-
没有设置环境变量,无法连接数据库
-
连服务器
mysql -u用户名 -p密码;
-
查看有什么数据库
show databases;
-
选库
USE 库名 ;
-
查看库下面的所有表
show tables;
-
创建数据库
create database 库名 charset utf8;
-
删除库
drop database 库名;
-
改名
表/列可改名 rename table stu to newstu;
数据库不能改名
-
建表语句
mysql> create table stu(
-> snum int,
-> sname varchar(10))
-> engine myisam charset utf8;
-
删除表
drop table stu;
-
插入行
mysql> insert into newstu values
-> (1,'zhangsan'),
-> (2,'lisi'),
-> (3,'wangwu');
-
清空表数据 truncate newstu; truncate相当于删表再重建,得到的是一张全新的表.....truncate 速度快
删除行数据 delete newstu; delete相当于数据擦掉
-
查看全部行
select * from newstu;
-
乱码
set name gbk;
-
表:增
create table class(id int primary key auto_increment,sname varchar(10) not null default '',gender char(1) not null default '',company varchar(20) not null default '',salary decimal(6,2) not null default 0.00,fanbu smallint not null default 0)engine myisam charset utf8;
查看表信息desc class
增加行的操作,几个问题
一,往哪张表添加行
二、添哪几列
三、添加什么值
set names gbk;
insert into class
(id,sname,gender,company,salary,fanbu)
values(1,'张三','m','百度',8888.89,100);
insert into class
(id,sname,gender,salary,fanbu)
values(2,'李四','f' ,8888.89,100);
insert into class
(sname,gender,salary,fanbu)
values('王五','f' ,8838.89,150);
-
表:删
-
删哪张表 delete from 表名
-
删哪行 where
delete from class
where fanbu=150;
-
表:改
-
改哪张表
-
改哪几列的值
-
改成什么值
-
在哪些行生效
update class
set company=''
where id='1';
update class
set company='买买'
where sname='李四' and gender='f';
-
表:查
-
查哪张表
-
查哪些列
-
查哪些行
最基本:select sname,id from class where id=1;
查全部数据:select * from class; * 代表全部列
select sname,id from class where id in (1,2); id=1和id=2;
and 优先级高于 or
模糊查询 like
select sname,id from class where sname like '张%'; %匹配出 _下划线匹配单个字符
聚集函数
count(*) 数绝对行数
count(sname) 不数null的其他行数
分组:select sum(salary),id from class group by id;
分了组再统计(聚集)
group by + 没聚集的列
筛选having
having对结果操作,where对表操作
select sum(salary),id from class group by id having sum(salary)>1000;
having + 聚集函数
这里不能用where条件查询,因为having是筛选,先group by 分组再having筛选,
综合题目:
答案:select name,subject,score,avg(score) as avgscore,sum(score<60) as lowscore from result group by name having lowscore>=2;
或者:
order by 排序,用在最终结果出来后,针对最终结果集
desc 降序,asc升序
select name,subject,score,avg(score) as avgscore,count(score<60) as sumscore from result group by name having sum(score<60)>=2 order by score;
limit
题目:
name是第一次出现的值
所以先将id降序排列,选出最大的id,并按cat分组,where id=子查询
select id,cat,name from goods where id=(select max(id) from goods group by cat);
-
where子查询
select id,name from goods where id=(select max(id) from goods);
-
from子查询
查询结果可以看成表
-
exists子查询
select cat,name from goods where exists(select * from goods where goods.cat=category.cat);
-
建表
建表过程就是声明字段过程
create table class(id int primary key auto_increment,sname varchar(10) not null default '',gender char(1) not null default '',company varchar(20) not null default '',salary decimal(6,2) not null default 0.00,fanbu smallint not null default 0)engine myisam charset utf8;
create table member(
id int unsigned primary key auto_increment,
usename varchar(20) not null default'',
gender varchar(5) not null default'',
kg float(4,1) not null default 0,
brith date not null default '0000-00-00',
salary float(7,2) not null default 0.00,
denglutime timestamp not null default '00:00:00',
pr varchar(100) not null default'')
engine myisam charset utf8;
-
列。。。。增删改查
增加列alter table class add score tinyint unsigned not null default 0; 名称 类型 参数
用first将新增列放到第一:alter table member add pidfirst int unsigned not null default 0 first;
默认加列到最后:alter table member add pid int unsigned not null default 0;
删列:alter table member drop pid;
对比:删除表delete from class where fanbu=150;
修改列:
改变列类型 alter table member modify kg float(5,1) not null default 0.0;
改列名及类型alter table member change pidfirst pid1 int not null default 0;
查看列:desc memble;
整型:int(M) unsigned zerofill 补零,M用来控制宽度
浮点float(M,D) M标度,代表总位数;D精度,代表小数位
定点decimal 精度高,把整数部分和小数部分分开存储
char(6) 定长类型,寻址快,
varchar(6) 变长类型
日期时间
timestamp 提取当前时间
-
特殊的null
判断:is null is not null
-
连接查询
绝对名
两表查询
全相乘:结果用where查询,缺点是浪费内存
左连接
(A left join B on cat=id)
右连接
(A right join B on cat=id)
左右连接区别:左右连接可以互换A left join B=A right join B。。。出于移植兼容性,尽量采用左连接
内连接:是左右连接的交集(A inner join B on cat=id)
外连接:是左右连接的并集(mysql不支持)
(A outer join B on cat=id)
题目:左连接面试题讲解
select mid,hid,h.tname,mres,gid,g.tname from ((m left join n as h on m.hid=h.tid)left join n as g on m.gid=g.tid) order by mid;
-
union
面试经典题
答案:select id,sum(num) from ((select * from aaa) union all (select * from bbb) )as t1 group by id order by id;
记得用union all,
这道题用到了union all合并,from子查询,聚集求和,分组
-
数学函数与字符串函数
数学函数
abs(x) 返回x的绝对值
bin(x) 返回x的二进制(oct返回八进制,hex返回十六进制)
ceiling(x) 返回大于x的最小整数值==>向上取整
exp(x) 返回值e(自然对数的底)的x次方
floor(x) 返回小于x的最大整数值==>向下取整
greatest(x1,x2,...,xn)返回集合中最大的值
least(x1,x2,...,xn) 返回集合中最小的值
ln(x) 返回x的自然对数
log(x,y)返回x的以y为底的对数
mod(x,y) 返回x/y的模(余数)
pi()返回pi的值(圆周率)
rand()返回0或1的随机值,可以通过提供一个参数(种子)使rand()生成器生成1.
round(x,y)返回参数x的四舍五入的有y位小数的值
sign(x) 返回代表数字x的符号的值
sqrt(x) 返回一个数的平方根
truncate(x,y) 返回数字x截短为y位小数的结果
聚合函数(常用于group by从句的select查询中,不跟在group by后,跟在having后)
avg(col)返回指定列的平均值
count(col)返回指定列中非null值的个数
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由属于一组的列值连接组合而成的结果
字符串函数
ascii(char)返回字符的ascii码值
bit_length(str)返回字符串的比特长度
concat(s1,s2...,sn)将s1,s2...,sn连接成字符串
concat_ws(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串,并用sep字符间隔
insert(str,x,y,instr) 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果
find_in_set(str,list)分析逗号分隔的list列表,如果发现str,返回str在list中的位置
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果
left(str,x)返回字符串str中最左边的x个字符
length(s)返回字符串str中的字符数
ltrim(str) 从字符串str中切掉开头的空格
position(substr,str) 返回子串substr在字符串str中第一次出现的位置
quote(str) 用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)返回字符串str重复x次的结果
reverse(str) 返回颠倒字符串str的结果
right(str,x) 返回字符串str中最右边的x个字符
rtrim(str) 返回字符串str尾部的空格
strcmp(s1,s2)比较字符串s1和s2
trim(str)去除字符串首部和尾部的所有空格
ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果
日期和时间函数
curdate()或current_date() 返回当前的日期
curtime()或current_time() 返回当前的时间
date_add(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) 返回date是一个月的第几天(1~31)
dayofyear(date) 返回date是一年的第几天(1~366)
dayname(date) 返回date的星期名,如:select dayname(current_date);
from_unixtime(ts,fmt) 根据指定的fmt格式,格式化unix时间戳ts
hour(time) 返回time的小时值(0~23)
minute(time) 返回time的分钟值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名,如:select monthname(current_date);
now() 返回当前的日期和时间
quarter(date) 返回date在一年中的季度(1~4),如select quarter(current_date);
week(date) 返回日期date为一年中第几周(0~53)
year(date) 返回日期date的年份(1000~9999)
一些示例:
获取当前系统时间:select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回两个日期值之间的差值(月数):select period_diff(200302,199802);
在mysql中计算年龄:
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
这样,如果brithday是未来的年月日的话,计算结果为0。
下面的sql语句计算员工的绝对年龄,即当birthday是未来的日期时,将得到负值。
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') < date_format(birthday, '00-%m-%d')) as age from employee
加密函数
aes_encrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果,调用aes_encrypt的结果是一个二进制字符串,以blob类型存储
aes_decrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
decode(str,key) 使用key作为密钥解密加密字符串str
encrypt(str,salt) 使用unixcrypt()函数,用关键词salt(一个可以惟一确定口令的字符串,就像钥匙一样)加密字符串str
encode(str,key) 使用key作为密钥加密字符串str,调用encode()的结果是一个二进制字符串,它以blob类型存储
md5() 计算字符串str的md5校验和
password(str) 返回字符串str的加密版本,这个加密过程是不可逆转的,和unix密码加密过程使用不同的算法。
sha() 计算字符串str的安全散列算法(sha)校验和
示例:
select encrypt('root','salt');
select encode('xufeng','key');
select decode(encode('xufeng','key'),'key');#加解密放在一起
select aes_encrypt('root','key');
select aes_decrypt(aes_encrypt('root','key'),'key');
select md5('123456');
select sha('123456');
控制流函数
mysql有4个函数是用来进行条件操作的,这些函数可以实现sql的条件逻辑,允许开发者将一些应用程序业务逻辑转换到数据库后台。
mysql控制流函数:
case when[test1] then [result1]...when[test2] then [result2]...else [default] end
如果testn是真,则返回resultn,否则返回default
case [test] when[val1] then [result]...else [default]end 如果test和valn相等,则返回resultn,否则返回default
if(test,t,f) 如果test是真,返回t;否则返回f
相当于三目运算符
ifnull(arg1,arg2) 如果arg1不是空,返回arg1,否则返回arg2
nullif(arg1,arg2) 如果arg1=arg2返回null;否则返回arg1
这些函数的第一个是ifnull(),它有两个参数,并且对第一个参数进行判断。
==>如果第一个参数不是null,函数就会向调用者返回第一个参数;如果是null,将返回第二个参数。
如:select ifnull(1,2), ifnull(null,10),ifnull(4*null,'false');
nullif()函数将会检验提供的两个参数是否相等,如果相等,则返回null,如果不相等,就返回第一个参数。
如:select nullif(1,1),nullif('a','b'),nullif(2+3,4+1);
和许多脚本语言提供的if()函数一样,mysql的if()函数也可以建立一个简单的条件测试,这个函数有三个参数:
==>第一个是要被判断的表达式,如果表达式为真,if()将会返回第二个参数,如果为假,if()将会返回第三个参数。
如:selectif(1<10,2,3),if(56>100,'true','false');
if()函数在只有两种可能结果时才适合使用。然而,在现实世界中,我们可能发现在条件测试中会需要多个分支。
---在这种情况下,mysql提供了case函数,它和php及perl语言的switch-case条件例程一样。
case函数的格式有些复杂,通常如下所示:
case [expression to be evaluated]
when [val 1] then [result 1]
when [val 2] then [result 2]
when [val 3] then [result 3]
......
when [val n] then [result n]
else [default result]
end
这里,第一个参数是要被判断的值或表达式,接下来的是一系列的when-then块,每一块的第一个参数指定要比较的值,如果为真,就返回结果。
所有的when-then块将以else块结束,当end结束了所有外部的case块时
==>如果前面的每一个块都不匹配就会返回else块指定的默认结果。如果没有指定else块,而且所有的when-then比较都不是真,mysql将会返回null。
case函数还有另外一种句法,有时使用起来非常方便,如下:
case
when [conditional test 1] then [result 1]
when [conditional test 2] then [result 2]
else [default result]
end
这种条件下,返回的结果取决于相应的条件测试是否为真。
示例:
mysql>select case 'green'
when 'red' then 'stop'
when 'green' then 'go' end;
select case 9 when 1 then 'a' when 2 then 'b' else 'n/a' end;
select case when (2+2)=4 then 'ok' when(2+2)<>4 then 'not ok' end asstatus;
select name,if((isactive = 1),'已激活','未激活') as result fromuserlogininfo;
select fname,lname,(math+sci+lit) as total,
case when (math+sci+lit) < 50 then 'd'
when (math+sci+lit) between 50 and 150 then 'c'
when (math+sci+lit) between 151 and 250 then 'b'
else 'a' end
as grade from marks;
select if(encrypt('sue','ts')=upass,'allow','deny') as loginresultfrom users where uname = 'sue';#一个登陆验证
格式化函数
date_format(date,fmt) 依照字符串fmt格式化日期date值
format(x,y) 把x格式化为以逗号隔开的数字序列,y是结果的小数位数
inet_aton(ip) 返回ip地址的数字表示
inet_ntoa(num) 返回数字所代表的ip地址
time_format(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是format()函数,它可以把大的数值格式化为以逗号间隔的易读的序列。
示例:
select format(34234.34323432,3);
select date_format(now(),'%w,%d %m %y %r');
select date_format(now(),'%y-%m-%d');
select date_format(19990330,'%y-%m-%d');
select date_format(now(),'%h:%i %p');
select inet_aton('10.122.89.47');
select inet_ntoa(175790383);
类型转化函数
为了进行数据类型转化,mysql提供了cast()函数,它可以把一个值转化为指定的数据类型。类型有:binary,char,date,time,datetime,signed,unsigned 示例:
select cast(now() as signed integer),curdate()+0;
select 'f'=binary 'f','f'=cast('f' as binary);
系统信息函数
database() 返回当前数据库名
benchmark(count,expr) 将表达式expr重复运行count次
connection_id() 返回当前客户的连接id
found_rows() 返回最后一个select查询进行检索的总行数
user()或system_user() 返回当前登陆用户名
version() 返回mysql服务器的版本
示例:
select database(),version(),user();
selectbenchmark(9999999,log(rand()*pi()));#该例中,mysql计算log(rand()*pi())表达式9999999次。
函数使用注意事项
-
视图view详解
临时表,虚拟表,是表通过某种运算得到的一个投影
表变化会影响视图,联动的
create view v1 as select id,sum(num) from ((select * from aaa) union all (select * from bbb) )as t1 group by id order by id;
视图algorithm
-
GB2312与UTF8区别
-
存储引擎
engine引擎就是mysql存储数据的不同方式