MySQL面试题&学习笔记

最新推荐文章于 2023-05-05 15:06:59 发布

小麦China

最新推荐文章于 2023-05-05 15:06:59 发布

阅读量339

点赞数 1

分类专栏：数据库文章标签： mysql sql 数据库 entity

本文链接：https://blog.csdn.net/qq_35683407/article/details/105337616

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

必做题目：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 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()返回０或１的随机值,可以通过提供一个参数(种子)使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存储数据的不同方式

小麦China

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL面试题&学习笔记

必做题目：https://sqlzoo.net/wiki/SELECT_basics/zh常见的SQL面试题：经典50题，知乎：https://zhuanlan.zhihu.com/p/38354000MySQL基础面试题总结：https://blog.csdn.net/whl190412/article/details/90081511常见面试题：https://www.cnb...
复制链接

扫一扫

专栏目录