数据库（sql )面试题

​​​​​一、多解示例

主要是是为了体现各种语法，所以一个题目会对应多个解，小伙伴们有其他想法的欢迎补充~

1、查询所有课程成绩大于80分的学生姓名（不需要输出成绩，这里只是为了直观展示）

解法一：

【注意】having 是对分组的结果集进行筛选；

mysql> select sname,min(score) from student group by sname having min(score)>80;
+--------+------------+
| sname  | min(score) |
+--------+------------+
| 王五   |         81 |
+--------+------------+
1 row in set (0.00 sec)

 

解法二：

【注意】where 是对结果集进行筛选；

mysql> select * from (select sname,min(score) as minScore from student group by sname) as t where t.minScore >80;
+--------+----------+
| sname  | minScore |
+--------+----------+
| 王五   |       81 |
+--------+----------+
1 row in set (0.00 sec)

解法三：

【注意】not in 后面加数据集， 单一的字段（sname)，即找到有分数小于80 的同学，将这些同学排除在外则是我们需要的结果(这样查询的结果是王五同学的所有成绩，所以加上distinct 进行去重）

mysql> select distinct sname from student where sname not in (select sname from student where score <= 80);
+--------+
| sname  |
+--------+
| 王五   |
+--------+
1 row in set (0.00 sec)

解法四：

【注意】not exists 后面加任何语句，其他同上

mysql> select distinct sname from student as t1 where not exists (select * from student as t2 where score <= 80 and t1.sname = t2.sname);
+--------+
| sname  |
+--------+
| 王五   |
+--------+
1 row in set (0.01 sec)

解法五：

【注意】if 语句 if（条件，TRUE，FALSE）,成绩大于80则记为1，否则为0，所以该同学所有科目成绩的数量等于他成绩大于80的科目数量即为所求

先看一下if 语句执行的效果：

mysql> select sname,course,score,if(score>80,1,0) as num from student;
+--------+--------+-------+-----+
| sname  | course | score | num |
+--------+--------+-------+-----+
| 张三   | 语文   |    81 |   1 |
| 张三   | 数学   |    75 |   0 |
| 李四   | 语文   |    76 |   0 |
| 李四   | 数学   |    90 |   1 |
| 王五   | 语文   |    81 |   1 |
| 王五   | 数学   |    99 |   1 |
| 王五   | 英语   |    90 |   1 |
+--------+--------+-------+-----+
7 rows in set (0.00 sec)
 

mysql> select sname from student group by sname having count(1) = sum(if(score>80,1,0));
+--------+
| sname  |
+--------+
| 王五   |
+--------+
1 row in set (0.00 sec)
 

解法六：

【注意】左连接,影响右表（连接的关键点在于 on 之后的连接条件），即如果右边没有符合条件的，全部以NUll代替，左表不受影响，全部输出；如果右表匹配上了，则显示右表的数据；

mysql> select* from student as t1 left join (select * from student where score<80) as t2 on t1.sname = t2.sname;
+--------+--------+-------+--------+--------+-------+
| sname  | course | score | sname  | course | score |
+--------+--------+-------+--------+--------+-------+
| 张三   | 语文   |    81 | 张三   | 数学   |    75 |
| 张三   | 数学   |    75 | 张三   | 数学   |    75 |
| 李四   | 语文   |    76 | 李四   | 语文   |    76 |
| 李四   | 数学   |    90 | 李四   | 语文   |    76 |
| 王五   | 语文   |    81 | NULL   | NULL   |  NULL |
| 王五   | 数学   |   100 | NULL   | NULL   |  NULL |
| 王五   | 英语   |    90 | NULL   | NULL   |  NULL |
+--------+--------+-------+--------+--------+-------+
7 rows in set (0.00 sec)

由上表显示结果可知:王五没有符合条件的成绩，所以右边的sname 值为空，即王五为所求

mysql> select distinct t1.sname from student as t1 left join (select * from student where score<80) as t2 on t1.sname = t2.sname where t2.sname is null;
+--------+
| sname  |
+--------+
| 王五   |
+--------+
1 row in set (0.00 sec)

 

2、求所有成绩中最高分的信息

解法一：

【注意】limit 1 ,即取一条数据； desc :按照降序排列

mysql> select * from student order by score desc limit 1;
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 王五   | 数学   |   100 |
+--------+--------+-------+
1 row in set (0.00 sec)
 

因为这张表只有一个成绩为100，这样求没有问题，但是要是好几个人都是100分呢？

所以我们可以先找到最高分的成绩，然后根据最高分去匹配其他信息,修改一下表中数据，

解法二：

mysql> update student set score ='100' where score = '76';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+--------+--------+-------
| sname  | course | score |
+--------+--------+-------+
| 张三   | 语文   |    81 |
| 张三   | 数学   |    75 |
| 李四   | 语文   |   100 |
| 李四   | 数学   |    90 |
| 王五   | 语文   |    81 |
| 王五   | 数学   |   100 |
| 王五   | 英语   |    90 |
+--------+--------+-------+
7 rows in set (0.00 sec)

【注意】子查询方式：在select 语句中继续嵌套select语句

mysql> select * from student where score = (select max(score) from student);
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 李四   | 语文   |   100 |
| 王五   | 数学   |   100 |
+--------+--------+-------+
2 rows in set (0.00 sec)
 

3、求出每个同学的最高分；

解法一：

mysql> select sname,max(score) from student group by sname;
+--------+------------+
| sname  | max(score) |
+--------+------------+
| 张三   |         81 |
| 李四   |        100 |
| 王五   |        100 |
+--------+------------+
3 rows in set (0.00 sec)
 

解法二：

mysql> select * from student as t1 where t1.score=(select max(t2.score) from student as t2 where t1.sname = t2.sname);
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 张三   | 语文   |    81 |
| 李四   | 语文   |   100 |
| 王五   | 数学   |   100 |
+--------+--------+-------+
3 rows in set (0.00 sec)

 

二、各种函数示例：

1、concat （拼接字符串）

mysql> select concat('a','b','c') as '结果';
+--------+
| 结果   |
+--------+
| abc    |
+--------+
1 row in set (0.00 sec)

【注意】null 和任意字符拼接结果都为null

mysql> select concat('a','b',null) as '结果';
+--------+
| 结果   |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)
 

2、查看表结构

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sname  | varchar(20) | YES  |     | NULL    |       |
| course | varchar(20) | YES  |     | NULL    |       |
| score  | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

3、模糊查询

like :一般和通配符一起使用，常用通配符（%---任意多个字符，包含0个字符、_----任意单个字符

mysql> select * from student where sname like '%三';
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 张三   | 语文   |    81 |
| 张三   | 数学   |    75 |
+--------+--------+-------+
2 rows in set (0.00 sec)

4、between and 

使用between and 可以提高语句的简洁度，包含临界值，等价于大于等于左边的值，小于等于右边的值，所以左右两边的值不可以调换位置

mysql> select * from student where score between 81 and 90;
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 张三   | 语文   |    81 |
| 李四   | 数学   |    90 |
| 王五   | 语文   |    81 |
| 王五   | 英语   |    90 |
+--------+--------+-------+
4 rows in set (0.00 sec)
 

5、排序(升序---asc,降序---desc)

【注意】默认为升序

mysql> select * from student order by score desc;
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 李四   | 语文   |   100 |
| 王五   | 数学   |   100 |
| 李四   | 数学   |    90 |
| 王五   | 英语   |    90 |
| 张三   | 语文   |    81 |
| 王五   | 语文   |    81 |
| 张三   | 数学   |    75 |
+--------+--------+-------+
7 rows in set (0.00 sec)

 

mysql> select * from student order by score asc;
+--------+--------+-------+
| sname  | course | score |
+--------+--------+-------+
| 张三   | 数学   |    75 |
| 张三   | 语文   |    81 |
| 王五   | 语文   |    81 |
| 李四   | 数学   |    90 |
| 王五   | 英语   |    90 |
| 李四   | 语文   |   100 |
| 王五   | 数学   |   100 |
+--------+--------+-------+
7 rows in set (0.00 sec)
 

6、length（获取参数值的字节个数）

mysql> select length('ceshi');
+-----------------+
| length('ceshi') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

【注意】一个汉字占三个字节
mysql> select length('周杰伦hhh');
+------------------------+
| length('周杰伦hhh')    |
+------------------------+
|                     12 |
+------------------------+
1 row in set (0.00 sec)

7、upper、lower(将字符串大小写）

mysql> select upper('ceshi');
+----------------+
| upper('ceshi') |
+----------------+
| CESHI          |
+----------------+
1 row in set (0.00 sec)

mysql> select lower('CEshi');
+----------------+
| lower('CEshi') |
+----------------+
| ceshi          |
+----------------+
1 row in set (0.00 sec)

8、substr(索引从1 开始）

mysql> select substr('小龙女和杨过在一起',5);

【注意】参数5的意思是截取指定索引5后面所有的内容
+-----------------------------------------+
| substr('小龙女和杨过在一起',5)          |
+-----------------------------------------+
| 杨过在一起                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select substr('小龙女和杨过在一起',1,3) 女主;

【注意】代表截取从指定索引1处指定字符长度为3 的字符
+-----------+
| 女主      |
+-----------+
| 小龙女    |
+-----------+
1 row in set (0.00 sec)

9、instr(返回子串  第一次  出现的索引，如果找不到，返回0

mysql> select instr('李莫愁喜欢陆展元','陆展元') result;
+--------+
| result |
+--------+
|      6 |
+--------+
1 row in set (0.01 sec)

10、lpad(用指定字符实现左填充指定长度）

       rpad(用指定字符实现右填充指定长度）

mysql> select lpad('段誉',10,'$') as result;
+----------------+
| result         |
+----------------+
| $$$$$$$$段誉   |
+----------------+

 

11、replace（替换）

mysql> select replace('张无忌喜欢周芷若','周芷若','赵敏') as result;
+-----------------------+
| result                |
+-----------------------+
| 张无忌喜欢赵敏        |
+-----------------------+
1 row in set (0.00 sec)

1 row in set (0.00 sec)

 

12、round(四舍五入）

mysql> select round(1.47);
+-------------+
| round(1.47) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

 

mysql> select round(1.475,2);

【注意】参数2代表保留两位小数
+----------------+
| round(1.475,2) |
+----------------+
|           1.48 |
+----------------+
1 row in set (0.00 sec)
 

13、ceil(向上取整,返回大于等于该参数的最小整数）

mysql> select ceil(1.03);
+------------+
| ceil(1.03) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

floor (向下取整,返回小于等于该参数的最大整数）

mysql> select floor(1.93);
+-------------+
| floor(1.93) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

14、truncate(截断)

mysql> select truncate(1.699,1);
+-------------------+
| truncate(1.699,1) |
+-------------------+
|               1.6 |
+-------------------+
1 row in set (0.00 sec)
 

15、now(返回当前系统时间和日期)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-05-04 15:16:04 |
+---------------------+
1 row in set (0.00 sec)
 

curdate(只返回当前日期，不包含时间）

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-05-04 |
+------------+
1 row in set (0.00 sec)

curtime(只返回当前时间，不包含日期）
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:18:47  |
+-----------+

可以获取指定的部分：年、月、日、时、分、秒

mysql> select year(now()) as '年';
+------+
| 年   |
+------+
| 2020 |
+------+
1 row in set (0.00 sec)

1 row in set (0.00 sec)

16、if 函数

mysql> select if(1<2,'Yes','NO');
+--------------------+
| if(1<2,'Yes','NO') |
+--------------------+
| Yes                |
+--------------------+
1 row in set (0.00 sec)
 

 

三、函数分类：

1、字符函数

concat:连接

length:获取字节长度

replace:替换

uppper:变大写

lower:变小写

lpad:左填充

rpad:右填充

substr:截取子串

trim:去掉空格

instr:获取子串第一次出现的索引

2、数学函数

ceil:  向上取整

round:四舍五入

mod:取模

floor:向下取整

truncate:截断

rand :获取随机数，返回0-1之间的小数

3、日期函数

now：返回当前日期和时间

year:返回年

month :返回月

day：返回日

date_format:将日期转换为字符

curdate:返回当前日期

str_to_date:将字符转换成日期

curtime:返回当前时间

hour :小时

minute:分钟

second :秒

datediff:返回两个日期相差的天数

monthname :以英文形式返回月

4、其他函数

version :当前数据库服务器的版本

database:当前打开的数据库

user :当前用户

5、流程控制函数

1）if（条件表达式，表达式1，表达式2）：如果条件成立，返回表达式1，否则返回表达式2

2）case 

A、

case 表达式或者变量

      when 常量1  then   值1

      when 常量2 then    值2

      .......

      else 值n

      end

B、

case 

      when 条件1  then   值1

      when 条件2 then    值2

      .......

      else 值n

      end

 

6、分组函数

max :最大值

min : 最小值

sum : 和

avg :平均值

count ：计数

count（字段）：统计该字段非空值的个数

count(*): 统计结果集的行数

count(1): 统计结果集的行数

 

【注意】sum 、avg 一般用于处理数值型，其他可以处理任何数值类型；

其次，以上五种分组函数都会忽略NUll值；都可以和distinct 关键字搭配去重

eg:select max(diatinct 字段）from table;

 

基本格式：

select 分组函数，分组后的字段

form 表

[where 筛选条件]

group by  分组的字段

[having 分组后的筛选条件]

[order by 排序列表]