Java面试题 Mysql部分

MySQL

单项选择题(31)

1.从数据表中查找记录用以下哪一项( C )

 A、UPDATE
 B、FIND
 C、SELECT
 D、CREATE

2.以下聚合函数求最小值的是( D )

 A、COUNT
 B、MAX
 C、AVG
 D、MIN

3.FROM命令属于( C )操作

 A、DML 
 B、DDL 
 C、DQL
 D、DCL

4.mysqldump -u username -p dbname table1 table2 ...-> C:\BackupName.sql 根据上面命令判断出数据库名称是( C )

 A、mysqldump
 B、username
 C、dbname
 D、BackupName.sql

5.以下双方之间属于多对多关系的是( A )

 A、用户—角色
 B、用户—银行卡
 C、用户—手机号
 D、学生—语文成绩

6.以下说法正确的是（ C ）

 A、类属性的个数必须要和表中的字段个数一致
 B、类属性的名称必须要和表中的字段的名称一致
 C、类属性的数据类型需要和表中的字段的数据类型对应
 D、varchar类型的表字段可以对应到java类中的int类型属性

7.以下约束哪个不是单表约束？( D )

 A、主键约束
 B、为空约束
 C、唯一约束
 D、外键约束

1. DECIMAL是（ A）数据类型

 A、可变精度浮点值
 B、整数值
 C、双精度浮点值
 D、单精度浮点值

9.条件“age BETWEEN 20 AND 30”表示年龄在20到30之间，且( D )

 A、包括20岁不包括30岁
 B、不包括20岁包括30岁
 C、不包括20岁和30岁
 D、包括20岁和30岁

10.在select语句中，实现选择操作的子句是( C )

 A、select
 B、group by
 C、where
 D、from

11.以下聚合函数求平均数的是( C )

 A、COUNT
 B、MAX
 C、AVG
 D、SUM

12.为了确保表中不会出现完全相同的两条记录，应该为该表创建（ A）

A、主键
B、外键盘
C、唯一
D、非空

13.以下哪项用于显示内连接( D )

 A、JOIN
 B、RIGHT JOIN
 C、LEFT JOIN
 D、INNER JOIN

14.DROP命令属于( B )操作

 A、DML
 B、DDL
 C、DQL
 D、DCL

15.mysql -u root -p < C:\backup.sql 根据上面命令哪个是要恢复的文件名称( D )

 A、mysqldump
 B、table1
 C、root
 D、backup.sql

16.查找表结构用以下哪一项( D )

 A、FIND
 B、SELETE
 C、ALTER
 D、DESC

17.以下聚合函数求数据总和的是( B )

 A、MAX
 B、SUM
 C、COUNT
 D、AVG

18.查询姓名不是NULL的数据语法正确的是( C )

 A、WHERE NAME ! NULL
 B、WHERE NAME NOT NULL
 C、WHERE NAME IS NOT NULL
 D、WHERE NAME!=NULL

19.delete from employee语句的作用是( B )

 A、删除当前数据库中整个employee表，包括表结构
 B、删除当前数据库中employee表内的所有行
 C、由于没有where子句，因此不删除任何数据
 D、删除当前数据库中employee表内的当前行

20.例如数据库中有A表，包括学生，学科，成绩 ，序号四个字段 , 数据库结构为 学生 学科 成绩 序号 张三 语文 60 1 张三 数学 100 2 李四 语文 70 3 李四 数学 80 4 李四 英语 80 5 上述哪一列可作为主键列 ( A )

 A、序号
 B、成绩
 C、学科
 D、学生

21.表的关系，正确的说法是（ B ）

 A、一个数据库服务器只能管理一个数据库，一个数据库只能包含一个表
 B、一个数据库服务器可以管理多个数据库，一个数据库可以包含多个表
 C、一个数据库服务器只能管理一个数据库，一个数据库可以包含多个表
 D、一个数据库服务器可以管理多个数据库，一个数据库只能包含一个表

22.以下哪个是关系型数据库（ A ）

 A、MySQL
 B、Redis
 C、NoSql
 D、HBase

23.对distinct解释正确的是 ( C )

 A、去掉重复的列
 B、显示重复的列
 C、去掉重复的值
 D、删除重复的数据

24.SQL语句中的条件用以下哪一项来表达 ( C )

 A、THEN
 B、WHILE
 C、WHERE
 D、IF

25.下列不属于连接种类 （C）

 A、左外连接
 B、内连接
 C、中间连接
 D、交叉连接

26.UPDATE命令属于( A )操作

 A、DML
 B、DDL
 C、DQL
 D、DCL

27.下列语句正确的是( B )

 A、WHERE NAME NULL
 B、WHERE NAME IS NULL
 C、WHERE NAME=NULL
 D、WHERE NAME ==NULL

28.在SQL语言中，子查询是（ D ） 。

 A、选取单表中字段子集的查询语句
 B、选取多表中字段子集的查询语句
 C、返回单表中数据子集的查询语言
 D、嵌入到另一个查询语句之中的查询语句

29.MySQl安装完毕后为什么要配置环境变量( A )

 A、为了在任何地方使用MySQl命令
 B、为了启动MySQl服务
 C、为了关闭MySQl服务
 D、为了安装连接软件

30.以下不是修改表操作的是（ A ）

 A、drop table user;
 B、alter table user add remark varchar(20);
 C、alter table user character set utf8;
 D、alter table user drop remark;

31.MySQL默认的数据库管理员用户名是 ，默认端口号是 。（C）

 A、admin 1433
 B、root 1433
 C、root 3306
 D、admin 1521

简答题(63)

1、 添加记录时，列举可以使用default的情况？

自动增长、默认值、可以为null

2、 写出where条件可以使用的所有运算符？

比较运算符 >,<,>=,<=,<>,!=
关系运算符 and not or
算数运算符 +，-，*，/,mod

3、 描述什么是嵌套查询，并分析为什么嵌套查询的执行效率比较低？

一个查询语句嵌套另一个查询语句,外层查询每执行一遍，内层sql语句就需要执行一次
1.执行效率低 2.仍属于单表查询，3.适用场景小表

4、 为什么要进行多表连接查询？描述进行多表连接查询时，两张表的公共字段有什么要求？

按照数据库设计范式要求：一张表只能表示一个实体，因此生产环境的项目往往需要多个表的设计才能完成，当需要一次查询需要多个表的数据的时候，就需要使用多表联合查询。
公共字段的要求；数据类型一致，长度相同，意义相同

5、 列举5个聚合函数，并说明其作用。

sum(字段)：求指定字段下所有记录的和
avg(字段)：求指定字段下所有记录的求平均值
max(字段)：求指定字段下所有记录的最大值
min(字段)：求指定字段下所有记录的最小值
count(字段)：求指定字段下记录的个数
count(*)：求整条记录数

6、 对比where和having的区别。

 1.where 在group by之前；having在group by之后
 2.where是对原始数据的条件筛选；having是对统计后的数据的筛选条件
 3.where后不能使用聚合函数;having后可以使用聚合函数

7、 什么是主键？主键的作用是什么？主键和唯一约束有什么区别（6分）

1.主键只能是一个；唯一可以是多个
2.主键可以作为另一张表的外键约束；唯一不可以
3.主键如果是int类型，可以自动增长；唯一不可以

8、 描述一下date类型和datetime类型的区别。

date:日期类型 格式 yyyy-MM-dd
datetime：时间日期类型 格式 yyyy-MM-dd HH:mm:ss

9、 有一张表employee，有一个工资字段salary，有一个字段部门depart，编写SQL命令实现将技术部的所有员工的工资上调40%。（4分）

update employee set salary=salary*1.4 where depart='技术部'

10、 使用SQL命令创建表employee，包含字段编号、姓名、年龄、入职日期，编号为主键。（7分）

create table employee(
    code int not null primary key,
    name varchar(20) not null,
    age int not null,
    joinDate date not null
)

11、简述sql语句的执行顺序

12、请举例说明ifnull 以及 if函数如何使用?

ifnull: 数据库中如果有null的数据，是不参与运算的，任何数据和null进行任何运算结果为null，使用ifnull处理
if：条件判断

13、请写出数据库的约束都有哪些？分别有什么特点？

主键约束：唯一标识表中的记录
默认约束：设置默认值，当数据为null时，将默认值填充到对应位置
非空约束：限制字段下数据不允许为空
唯一约束：限制字段下的数据不允许重复
外键约束：当前修饰的字段数据只能来自于依附的主键的数据

14、如何备份MySQL数据库

选择数据库名字，右键弹出菜单选择导出到sql文件

15、如何将test01数据库中的tbl_user表转移到test02数据库中

将test01数据库中的tbl_user表导出成sql文件，在test02数据库中，运行该sql文件

16、什么是数据库？数据库有什么作用？数据库的英文表示是什么？英文缩写是什么？

数据库是存储数据的仓库；用来保存软件中需要保留下来的数据；数据的英文表示是database； 英文缩写是db

17、描述delete和truncate删除表中全部记录的区别

delete：删除全部记录，是一条一条删除，再次添加新记录，自动递增的字段会接着编号
truncate：删除全部记录，是一次性清空，再次添加新记录，自动递增的字段会重新编号

18、主键的作用是什么？向主键字段添加数据有什么要求？主键和唯一约束有什么区别？

主键作用是为了确保表中不会有重复记录；
向主键字段添加数据时，不允许重复，也不允许为空
区别：主键在一张表中最多只能有一个，唯一约束可以有多个； 主键字段不允许为空，唯一约束的字段可以为空

19、描述MySQL中字段的类型date、time、 datetime、timestamp存储的数据有什么区别

答案：date：年月日  time：时分秒  datetime：年月日时分秒  timestamp：年月日时分秒

20、有一个学生表stu，删除学号id为1001,1004,1006的3条数据(要求只允许用一个SQL语句完成)

（1）、delete from stu_info where id in (1001,1004,1006)
（2）、delete from stu_info where id = 1001 or id = 1004 or id = 1006

21、根据如下表，使用SQL命令实现对应的功能

表 tbl_product，包含字段id、name、price、count、addtime，分别代表商品id、商品名称、单价、数量、添加时间；

1、将所有商品的价格上浮5%

2、查询库存量不足10件的商品

3、查询价格在100-200元之间的商品

4、查询名字包含"牙膏"的商品

5、查询上个月添加的商品

1、update tbl_product set price = price *(1+0.05) 
2、select * from tbl_product where count<10
3、select * from tbl_product where price between 100 and 200
4、select * from tbl_product where name like '%牙膏%'
5、select * from tbl_product where addtime = month(now)-1

22、根据如下描述，使用SQL命令实现对应的功能

1、 创建类别表 tbl_type，包含字段id、name，分别代表类别id，类别名称，要求id为自动递增、name唯一，所有字段全部为必填字段；

2、 创建商品表 tbl_product，包含字段id、name、price、count、typeid、addtime，分别代表商品id、商品名称、单价、数量、类别id、添加时间，其中，id是主键，类别id是外键，添加时间为系统默认的当前时间，所有字段全部为必填字段；

1、create table tbl_type(id int not null auto_increment primary key ,name char(50) not null unique )
2、create table tbl_product(id int not null primary key,name char(100) not null,price double not null,count int not null,typeid int not null,addtime datetime default now(), add constraint fk_product_type_id foreign key(typeid) references tbl_type(id))

23.什么是子查询？

  当一个查询是另一个查询的条件时，称之为子查询, 子查询最常用于SELECT-SQL命令的WHERE子句中；

24.数据库删除语句 Drop/Delete/Truncate比较

Delete ：删除数据表中的行。
Drop ：删除数据表或数据库，或删除数据表字段。
Truncate：删除数据表中的数据（仅数据表中的数据，不删除表）。
TRUNCATE TABLE语句是一种快速、无日志记录的方法。
TRUNCATE TABLE语句与不含有 WHERE 子句的 DELETE 语句在功能上相同。
但是，TRUNCATE TABLE语句速度更快，并且使用更少的系统资源和事务日志资源。
删除数据的速度，一般来说: drop> truncate > delete

25.给定如下SQL数据库：Test(num INT(4)) 请用一条SQL语句返回num的最小值，但不许使用统计功能，如MIN，MAX等

SQLServer：
select top 1 num from Test order by num
mysql：
select num from Test order by num asc limit 1
oracle:
select ROWNUM as r, t.* from test t where t.r =1 

26.查数据表中第30到40条记录，有字段ID，但是ID并不连续，如何写SQL语句？

mysql：select * from table limit 30,40；
oracle：select * from (select * from table a where a.ROWNUM<=40 ) b where b.ROWNUM>=5
sqlserverl: select top 10 * from table where id not in (select top 29 id from table)

27.order by 、group by 、having的用法区别?

order by 后面必须列出排序的字段名，可以是多个字段名。
group by 必须有'聚合函数'来配合才能使用，使用时select后面的所有列中至少需要一个分组字段,没有使用聚合函数的列,必须出现在 group by 后面（重要）
Having 是结合聚合函数中使用的。当我们在用聚合函数的时候，一般都要用到GROUP BY先进行分组，然后再使用Having对聚合函数的结果进行筛选，Having必须配合分组来使用。

28.MYSQL支持事务吗?

在缺省模式下,MySQL是autocommit模式的，所有的数据库更新操作都会即时提交。
如果表类型是使用InnoDB Tables或者BDB tables的话，
就可以使用SET AUTOCOMMIT=0使MYSQL允许在非autocommit模式工作，
这是就可以使用COMMIT来进行手动提交，或者用ROLLBACK来手动回滚。
代码示例如下：
 Start Transaction;
 Select @A:=SUM(salary) FROM table1 where type=1;
 UPDATE table2 SET summary=@A where type=1;
 COMMIT;

29.如何解决MYSQL数据库中文乱码问题?

1）在安装数据库的时候指定字符集
​
2）如果在安装完了以后可以更改以下文件：
\MySql目录\my.ini里的所有的default-character-set=utf-8
\MySql目录\data\depot_development\db.opt
default-character-set=utf-8
default-collation=utf-8_chinese_ci
​
3）创建数据库的时候：指定字符集类型
CREATE DATABASE test
CHARACTER SET 'gbk'
COLLATE 'gbk_chinese_ci";
​
4）创建表的时候也指定字符集
CREATE TABLE student
(
ID varchar(40) NOT NULL default,
UuseID varchar(40) NOT NULL default
)ENGINE=InnoDB DEFAULT CHARSET=gbk;
​
5）设置URL的时候 
jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8

30.请解释一下varchar2和char的区别？

char最大可以容纳2000个字节，varchar2最大可以容纳4000个字节，此为可变长字段。

31.Oracle查看当前用户的角色和查看当前用户的系统权限和表权限？

查看当前用户的缺省表空间：
select usernamne,default_tablespace from user_users;
查看当前用户的角色：
select * from user_role_privs;
查看当前用户的系统权限和表级权限：
select * from user_sys_privs;
select * from user_tab_privs;

32.怎么获取哪些用户在使用数据库?

select username from v$session;

33.数据库中存储过程和函数的区别？

数据中存储过程和函数主要区别为：
(1)存储过程可以没有返回值，而函数必须有返回值。
(2)在调用时，存储过程必须在扩展SQL中调用，而函数可以直接在查询的sql语句中调用。
(3)存储过程主要编写业务逻辑，而函数注重编写小算法，如：生成一个订单号等。

34.oracle 常用的日期函数有哪些？

to_date(), add_months(), last_day(),months_between()

35.主键与唯一索引的区别？？

相同点：他们都是对数据库进行完整性约束
不同点：
1）唯一约束所在的列允许空值，主键约束所在的列不允许
2）可以把唯一约束放在一个或多个列上
3）建立主键的目的是让外键来引用
4）一个表最多只有一个主键，但可以有多个唯一索引

36.什么是触发器和存储过程？列出你所知道的触发器种类？每种类别有什么区别？

存储过程定义： 
将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 
那么以后要使用时，只需使用execute命令来调用即可。
触发器（trigger）：
是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发。
比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。
创建触发器的SQL语法（问你再回答）
CREATE TRIGGER <触发器名称>　
ON <表名称>　
FOR EACH ROW 
BEGIN　s
 --do something　
END 

1. 数据库的三范式是什么？

参考《数据库的范式》 https://www.cnblogs.com/knowledgesea/p/3667395.html

1. 一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

是8。此处考察的是自增字段数值不能“回头”。除此之外，请回忆自增字段的另一些要求：
必须是int
必须是主键
表中只能有一列使用自增。

39.char 和 varchar 的区别是什么？

char是固定长度的字符，varchar是可变长度的字符。

40.mysql 的内连接、左连接、右连接有什么区别？

内连接是通过公共字段的连接，取两个表的公共数据。
左连接除了通过公共字段的连接，取两个表的公共数据之外，取左表剩余的数据。 
右连接除了通过公共字段的连接，取两个表的公共数据之外，取右表剩余的数据。

41.mysql 索引是怎么实现的？

难度比较大的一道题，又比较经典的一道题。关于索引的使用和原理，请阅读以下的帖子（总结的很好），如果读不明白，请求助老师。
《深入理解MySQL索引原理和实现——为什么索引可以加速查询》
https://blog.csdn.net/tongdanping/article/details/79878302

42.如何合理的使用mysql的索引？

索引可以提高数据的检索效率，也可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。排序分组操作主要消耗的就是CPU资源和内存，所以能够在排序分组操作中好好的利用索引将会极大地降低CPU资源的消耗。如何判定是否需要创建索引？
1、较频繁地作为查询条件的字段应该连理索引。
2、唯一性太差的字段不适合建立索引。比如性别列：除了男就是女。
3、更新太频繁地字段不适合创建索引。因为每次更新索引字段的值，数据库除了修改真正的值外，还要维护索引，维护索引会增加时间。如果频繁的维护索引会降低效率。对于有索引的字段，读和写一对矛盾体。
4、不会出现在where条件中的字段不该建立索引

43.说一下数据库的事务隔离级别，都是什么意思？

数据库中事务之间存在以下的情况产生。
赃读：指一个事务读取了另一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）
虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。(insert)
数据库通过设置事务的隔离级别防止以上情况的发生：
1、READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。
2、READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。（oracle默认的）
4、REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。（mysql默认）
8、SERIALIZABLE: 避免赃读、不可重复读、虚读。
级别越高，性能越低，数据越安全

44.说一下 mysql 常用的引擎？

四种常用引擎。
简单的了解，参考《Mysql四种常见数据库引擎》：https://www.jianshu.com/p/4bb9f78b4f6d
深入理解，参考《Mysql常用引擎分析》
https://blog.csdn.net/tangyaya8/article/details/81635185

45.说一下 mysql 的行锁和表锁

此题属于比较深入的原理性的内容，没有一定理论基础理解起来有一些困难。
详细，请参考《MySQL中的锁（表锁、行锁）》：https://www.cnblogs.com/chenqionghe/p/4845693.html
《MySQL 表锁和行锁机制》
https://blog.csdn.net/xts5701046/article/details/81395958

1. 说一下乐观锁和悲观锁？

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它自己拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
​
乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
​
两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。
​
关于更多的理解，请参考《面试必备之乐观锁与悲观锁》
https://blog.csdn.net/qq_34337272/article/details/81072874

47mysql 问题排查都有哪些手段？

这个问题比较大。没办法统一总结答案。运维(DBA)
请参考: 《mysql常见问题排查https://www.csdn.net/gather_2b/MtTaIgwsODUzOC1ibG9n.html

1. 如何做 mysql 的性能优化？

这个题目很大，参考
《Mysql性能优化一》：https://www.cnblogs.com/jiekzou/p/5371085.html
《Mysql性能优化二》：[ http://www.cnblogs.com/jiekzou/p/5380073.html]      (https://www.cnblogs.com/jiekzou/p/5371085.html)
《MySQL性能优化》： https://blog.csdn.net/weixin_41580948/article/details/79573932

49.《MySQL 性能优化的最佳20多条经验分享》:

https://www.cnblogs.com/pengyunjing/p/6591660.html

50.关于写SQL语句？MySQL索引结构？介绍一下B树和B+树？MyISAM和InnoDB索引的区别？

见第42题。也可以看下面的答案。
1.在创建索引时，通常采用的数据结构有：Hash、二叉搜索树、红黑树、B树以及B+树。Mysql的索引结构为B+tree。

51.B树和B+树

B树：B树是一种多路搜索树，每个子节点可以拥有多于2个子节点，M路的B树最多可拥有M个子节点。设计成多路，其目的是为了降低树的高度，降低查询次数，提高查询效率。
​
虽然多路可以降低树的高度，但是如果设计成无限多路，就会退化成有序数组，一般B树的使用场景是用于文件的索引，这些索引会存放于硬盘中，有时内存是无法一次性加载完，此时就无法进行查找。
​
如果全部在内存中，红黑树的查找效率要高于B树，但是涉及到磁盘操作，B树要优于红黑树，所以在JDK1.8版本的HashMap中，如果单个桶的链表长度多于8或全部桶的链表总长度多余64，会将链表转换成红黑树。

B+树：B+树是对于B树进行优化的多路搜索树，主要设计是将数据全部存放于叶子节点，并将叶子节点用指针进行链表链接。
​
主要使用场景：常用于数据库的索引。 
​
数据库的索引一般数据量不小，同时又存放于磁盘中，采用多路搜索树，可以降低树的高度，同时在大数据量下可以分批载入内存，提高查询效率。
​
不同于B树的使用场景，数据库的查询中，我们一般查询的数量不会是单条数据，例如列表常用查询中的分页查询--查询第1页的10条数据，此时如果采用B树，需要进行树的中序遍历，可能需要跨层访问。
​
而B+树的所有数据全部存放于叶子节点上，且叶子节点之间采用指针进行链表链接，一次查询多条时，确定首尾位置，便可以方便的确定多条数据位置。

52.MyISAM和InnoDB索引的区别

是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

53.集索引跟非聚集索引的区别

聚集索引和非聚集索引的根本区别：
  1.表记录的排列顺序和与索引的排列顺序是否一致。
  2.聚集索引一个表只有一个，非聚集索引一个表可以存在多个。
  3.聚集索引存储记录是物理上连续存在，非聚集索引是逻辑上的连续。

54.聚集索引优点：

 1.以最快的速度缩小查询范围。
 2.以最快的速度进行字段排序。

55.聚集索引使用场合：

  1.此列包含有限数目的不同值。
  2.查询的结果返回一个区间的值。
  3.查询的结果返回某值相同的大量结果集。

56.非聚集索引优点：

  1.非聚集索引比聚集索引层次多。
  2.添加记录不会引起数据顺序的重组。

57.非聚集索引使用场合：

  1.此列包含了大量数目不同的值。
  2.查询的结束返回的是少量的结果集。

58.SQL语句面试题

表名：number
ID(number型) Name(varchar2型)
1       a
2       b
3       b
4       a
5       c
6       c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
例如：删除后的结果应如下:
ID(number型) Name(varchar2型)
1 a
2 b
5 c
请写出SQL查询语句。

delete from number where id ont in (select min(id) from number group by name);
这个时候会报一个错误：
ERROR 1093 (HY000): You can't specify target table 'number' for update in FROM clause；
这个是mysql的一个bug，意思是不能先select出同一表中的某些值，再update这个表(在同一语句中)。
解决之后的正确sql语句：
 delete from number where id not in (select a.id from ((select min(id) as id from number group by name) a));
也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，postgresql和oracle不会出现此问题。

59.SQL语句面试题

表名：student
name course score
张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76
问：用sql查询出“张”姓学生中平均成绩大于75分的学生信息；

select * from student where name in (select name from student
where name like '张%' group by name having avg(score) > 75);

60.SQL语句面试题

一张比赛结果表pmatch，
+------------+--------+
| date    | result |
+------------+--------+
| 2005-05-09 | 胜   |
| 2005-05-09 | 胜   |
| 2005-05-09 | 负   |
| 2005-05-09 | 负   |
| 2005-05-10 | 胜   |
| 2005-05-10 | 负   |
| 2005-05-10 | 负   |
+------------+--------+
问：如果要生成下列结果，如何写sql，
+------------+------+------+
| date    | 胜  | 负  |
+------------+------+------+
| 2005-05-09 |  2 |  2 |
| 2005-05-10 |  1 |  2 |
+------------+------+------+

select date,sum(case when result="胜" then 1 else 0 end) "胜",sum(case when result="负" then 1 else 0 end) "负" from pmatch group by date;

61.SQL语句面试题

有一张国家人口统计表country：
+--------+------+------------+
| name  | sex | population |
+--------+------+------------+
| 中国  |  1 |    340 |
| 中国  |  2 |    260 |
| 美国  |  1 |     45 |
| 美国  |  2 |     55 |
| 英国  |  1 |     51 |
| 英国  |  2 |     49 |
| 日本  |  1 |     7 |
| 日本  |  2 |     1 |
+--------+------+------------+
sql查询显示如下结果：
+--------+------+------+
| name  | 男  | 女  |
+--------+------+------+
| 中国  | 340 | 260 |
| 日本  |  7 |  1 |
| 美国  |  45 |  55 |
| 英国  |  51 |  49 |
+--------+------+------+

select name,sum(case when sex=1 then population else 0 end) "男",sum(case when sex=2 then population else 0 end) "女" from country group by name;

62.SQL语句面试题

面试题：怎么把这样一个表
year  month amount
1991  1   1.1
1991  2   1.2
1991  3   1.3
1991  4   1.4
1992  1   2.1
1992  2   2.2
1992  3   2.3
1992  4   2.4
查成这样一个结果
year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

select year, 
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year

63.SQL语句面试题

 

解题思路：使用自连接的SQL语句，注意排除重复的或者错误错误的数据。