Java面试题2018---数据库

约定不等于承诺〃 2022-04-11 10:10 305阅读 0赞

### 一、运算符 ###

--------------------

**a.算数运算符**

\+：   
insert into sums values(12,13,age+height);//age和height类型是int。   
select 1+3;

\-：   
insert into sums values(12,13,age-height);//age和height类型是int。

\*：   
insert into sums values(12,13,age\*height);//age和height类型是int。

/,div:   
insert into sums values(12,13,age div height);//age和height类型是int，第三个字段被插入了0。

%,mod:   
insert into sums values(12,13,age%height);//第三字段被插入0,12%13=12；

**b.比较运算符：**   
<>、!=:不等于  
select 1<>2;//显示1，代表true   
select 1<>1;//显示0，代表false

<=>：NULL安全的等于   
select null<=>null;//显示1   
<:   <=:

select 1<2,1<=2,1>2,1>=2;//显示1,1,0,0

**c.逻辑运算符 **  
not或者!:非   
示例：select not 0;//显示1，MySQL中，0是假，其他都是真。   
and或&&:与   
示例：select 44 && 0;//显示0   
or或||:或   
示例：select 13 or 0;//显示1   
xor（异或）：   
示例：select 12 xor 12;//显示0,两个相同，为假，即0。两个数不同，为真，即1.

--------------------

### 二、存储过程基本函数  ###

--------------------

**字符串函数**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70][]

select LOWER('RGFRG')--大写字母变为小写字母
    select UPPER('ggfgrt')--小写字母变为大写字母
    select LTRIM()--去掉左侧字符空格
    select RTRIM()--去掉字符串右侧空格
    select LEFT(Title,5)from News--从某列中左侧起截取多少长度的字符串
    select RIGHT(Title,5)from News--从某列中右侧起截取多上长度的字符串
    select SUBSTRING(Title,3,5) from News--从某一列中第几个字符开始截取几个字符
    select REVERSE (Title) from News --翻转某一列
    select CHARINDEX('中国',Title)from News --查询目标内容在指定区域有没有出现以及出现位置
    select REPLACE(Title,'中国','美国')from News--替换字符串。例搜索关键字并明显标注
    select STUFF(Title,3,4,'chine')from News
    select LEN('chine')--取字符串长度

--------------------

**数值函数**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 1][]

select CEILING ( COUNT(*)/5.0) from News--取大于结果的最小整数
    select floor ( COUNT(*)/5.0) from News--取小于结果的最大整数
    select SQRT(2)--数值开平方
    select ROUND(3.45645,2)
    select abs(-5)：绝对值函数，取此数的绝对值，可以应用于数据库中一些比较乱的值的加减操作。
    select CEILING (RAND()*10)--随机生成-1之间的数字，可以以乘以的方式增大随机数范围

--------------------

**日期和时间函数**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 2][]

ps:对于日期，无论插入更新还是使用where子句进行过滤，日期格式必须为yyyy-mm-dd，而且应始终使用4位数字的年份。一般来说应用程序不适用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值

select GETDATE()--获取当前时间
    select YEAR(sbirthday)from student--取时间年份
    select month(sbirthday)from student--取日期时间月份
    select day(sbirthday)from student3--取时间天
    select datepart(yy,sbirthday)from student4-- yy，mm，dd分别代表年月日
    select CAST ('123'as int)--转换字符串

例子1：select column1, column2 from table where date(table_date) between '2013-05-20' and '2014-5-20';
    
          其中between操作符用来把2013-05-20和2014-05-20定义为一个要匹配的日期范围
    
    例子2：select column1， column2 from table where year（table-date） = 2013 and month(table_date) = 5;
    
          year（）是一个日期（从日期时间）中返回年份的函数，month（）从日期中返回月份；
    
          因此，where year（table-date） = 2013 and month(table_date) = 9检索出table_date为2013年5月的所有行！

--------------------

### 三、查询条件 ###

--------------------

**去重：union、union all、distinct**

UNION 的语法如下：

*       \[SQL 语句 1\]
 *        UNION
 *       \[SQL 语句 2\]

例：select name from A

union

select name(或其他字段) from B

结果：会将两个select语句的内容组合在一起，注意，如果有重复的语句会进行过滤；只要select语句中字段的个数相同就不会报错，和字段名称信息没关系

![20181205224848661.PNG][]

UNION ALL 的语法如下：  
     \[SQL 语句 1\]  
      UNION ALL  
     \[SQL 语句 2\]

例：select name from A

union all

select name(或其他字段) from B

结果：会将两个select语句的内容组合在一起，注意，不会过滤重复的数据，都显示出来；只要select语句中字段的个数相同就不会报错，和字段名称信息没关系；

![20181205224855913.PNG][]

效率：
    UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
    1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
    2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
    从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
    简要回答：
    UNION去重且排序
    UNION ALL不去重不排序
    此处有参考博客：https://www.cnblogs.com/areyouready/p/7827779.html

**distinct**

SELECT `DISTINCT` company FROM Orders   :查询company字段并去重

SELECT `DISTINCT` company,name FROM Orders  :查询company，name字段并去重，这俩字段都相同才会去重

SELECT count(`DISTINCT` company) FROM Orders  :统计去重后查出的个数

SELECT count(`DISTINCT` company,name) FROM Orders   :统计去重后查出的个数，mysql中支持多参数查询

注意：

distinct必须放在所有字段开头

--------------------

**子查询select**

子查询分为如下几类：

*  标量子查询：返回单一值的标量，最简单的形式。 
 *  列子查询：返回的结果集是 N 行一列。 
 *  行子查询：返回的结果集是一行 N 列。 
 *  表子查询：返回的结果集是 N 行 N 列。

**1. 标量子查询：**

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

示例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
    SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
    SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

**2. MySQL 列子查询：**

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。   
可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。   
示例：

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

**3. MySQL 行子查询：**

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。   
例子：

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
    注：(1,2) 等同于 row(1,2)
    SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

**4. MySQL 表子查询：**

指子查询返回的结果集是 N 行 N 列的一个表数据。   
例子：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

子查询优化：

很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语 句很灵活，但子查询的执行效率不高。

子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在MySQL中可以使用连接查 询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

使用连接(JOIN)来代替子查询：

`例子：`

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
    
    优化后：
    
    SELECT * FROM article
    
    inner join blog 
    
    on (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)

参考博客：[https://www.cnblogs.com/zhuiluoyu/p/5822481.html][https_www.cnblogs.com_zhuiluoyu_p_5822481.html]

--------------------

**is null / is not null**

\*\*查询某个字段的值是否为NULL

mysql> select \* from tmp ;  
\+------+----------+  
| id   | name     |  
\+------+----------+  
|    2 | lisi          |  
|    1 | zhangsan |  
|    3 | NULL     |  
\+------+----------+  
3 rows in set (0.00 sec)

mysql> select \* from tmp where name is null;  
\+------+------+  
| id   | name |  
\+------+------+  
|    3 | NULL |  
\+------+------+  
1 row in set (0.00 sec)

mysql> select \* from tmp where name is not null;  
\+------+------------+  
| id   | name       |  
\+------+------------+  
|    2 | lisi            |  
|    1 | zhangsan |  
\+------+------------+  
2 rows in set (0.00 sec)

--------------------

**limit**

limit是mysql的语法, limit子句可以用于强制select语句返回制定的记录数，limit接受一个或两个数字的参数，如果给定两个参数第一个指定返回记录行的偏移量，第二个指定返回记录行的最大数目。

1、select \* from table limit m,n  
     其中m是指记录开始的index，[从0开始][0]，表示第一条记录  
     n是指从第m+1条开始，取n条。

/*即取出第3条至第6条，4条记录*/
    SELECT * FROM YourTableName LIMIT 2,4;

2、select \* from table limit n  相当于select \* from table limit 0, n

/*当没有指定位置偏移量时默认偏移量为0，即从第一条开始取4条；
    limit 4相当于limit 0,4*/
    SELECT * FROM YourTableName LIMIT 4;

应用场合:分页

// 后台计算出页码、页数(页大小)
     int curPage = 2;
     int pageSize = 10;
     int startRow = (curPage - 1) * pageSize;
    
    SELECT * FROM YourTableName LIMIT startRow,pageSize;

--------------------

**表连接 join**

有两个表：

![20181206185655792.png][]

内连接：

select \* from A join B on A.name = B.name

相当于：select \* from A, B where A.name = B.name

![20181206185706793.png][]

外连接：

select \* from A left join B on A.name = B.name

![20181206185712469.png][]

select \* from A right join B on A.name = B.name

![20181206185716582.png][]

select \* from A full join B on A.name = B.name

![20181206185722374.png][]

--------------------

**分组聚集（group by）**

*  聚集函数：平均值（avg）、最小值（min）、最大值（max）、总和（sum）、计数（count）。
 *  select中的属性必须写在group by 中或聚集函数中

select dept\_name,avg(salary) as avg\_salary from instructor2 group by dept\_name having avg(salary)>3000

*having与where的区别：*

*  where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

--------------------

**between⋯and⋯**

select \* from instructor2 where 属性名 between ... and ... ;

--------------------

**in和 not in**

*  in：测试元组是否是集合的成员
 *  not in：测试元组是否不是集合的成员
 *  in和 not in 操作符用于枚举集合

select \* from instructor2 where name not in('ai','ye','er');

*如果有子查询：*

select \* from instructor2 where name in (select name from instructor2 where salary>3000 );

则，相当于两层for循环：

![20181206185348778.png][]

--------------------

**空关系测试（exists）**

EXISTS是一个非常牛叉的谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其 它谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN，对于EXISTS来说，UNKNOWN就是FALSE。

select course\_id  
from section as S  
where semester='Fall' and year=2009 and exists(select \*  
                 from section as T  
                 where semester='Spring' and year=2010  
                 and S.course\_id=T.course\_id  
                );

in和exists的区别：

关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外， 还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样，SQL优化 器会选择相同的执行计划。

not in 和 not exists的区别：

说到了IN和EXISTS几乎是一样的，但是，就不得不说到NOT IN和NOT EXISTS，对于输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN就会得到NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。

--------------------

**case when...then..else..end（条件判断）**

select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;

--------------------

**字符串运算（like）**

*  百分号（%）：匹配任意子串
 *  下划线（\_）：匹配任意一个字符

select \* from instructor2 where salary like '%0\_.%';

结果：

匹配的结果如：   122202.222   32309.323   32301.432

--------------------

**显示次序（order by）**

*  默认升序：asc
 *  降序：desc

select \* from instructor2 order by salary desc;

--------------------

**什么是存储过程?为什么存储过程要比单纯的Sql 语句执行起来要快?**

存储过程:是一组预先编译好的T-SQL代码 在创建存储过程时经过了语法和性能优化,执行不必重复的步骤,使用存储过程可提高运行效率

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：   
CREATE PROCEDURE(\[\[IN |OUT |INOUT \] 参数名 数据类型…\])   
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值   
OUT 输出参数:该值可在存储过程内部被改变，并可返回   
INOUT 输入输出参数:调用时指定，并且可被改变和返回

DELIMITER $$
    DROP PROCEDURE IF EXISTS `prc_test1`;
    
    CREATE DEFINER = `root`@`localhost` PROCEDURE `prc_test1`(in parameter int,out name varchar(50))
    BEGIN
         select name from test where id= parameter
    END$$
    
    DELIMITER ;

调用：

call prc_test1(1，@school);
    select @school as school

参数说明

DELIMITER $$

作用，将MySQL的结束符设置为$$，因为，MySQL默认的语句结束符号为分号;，为了避免与存储过程中，SQL语句结束符相冲突，需要使用DELIMTER改变存储过程的结束符，并且，以END $$结束存储过程

存储过程，定义完毕之后，再使用DELIMITER，恢复默认结束符

DELIMITER，可以指定其他符号作为结束符

注意，DELIMITER与设置的结束符之间，一定要有一个空格，否则设置无效

--------------------

**oracle和mysql的区别**

1，[Oracle][]没有offet,limit，在[mysql][]中我们用它们来控制显示的行数，最多的是[分页][Link 1]了。[oracle][Oracle]要分页的话，要换成rownum。

2，oracle建表时，没有auto\_increment，所有要想让表的一个字段自增，要自己添加序列，插入时，把序列的值，插入进去。

3，oracle有一个dual表，当[select][]后没有表时，加上的。不加会报错的。select 1 这个在[mysql][]不会报错的，oracle下会。select 1 from dual这样的话，oracle就不会报错了。

4，对空值的判断，name != ""这样在my[sql][]下不会报错的，但是oracle下会报错。在oracle下的要换成name is not null

5，oracle下对单引号，双引号要求的很死，一般不准用双引号，用了会报

ERROR at line 1:  
ORA-00904: "t": invalid identifier

而[MySQL][]要求就没有那么严格了，单引号，双引号都可以。

6，oracle有to\_number,to\_date这样的转换函数，oracle表字段是number型的，如果你$\_[POST][]得到的参数是123456，入库的时候，你还要to\_number来强制转换一下，不然后会被当成字符串来处理。而[mysql][MySQL]却不会。

7，group\_concat这个函数，oracle是没有的，如果要想用自已写方法。

8，mysql的用户权限管理，是放到mysql自动带的一个[数据库][MySQL]mysql里面的，而oracle是用户权限是根着表空间走的。

9，group by,在下oracle下用group by的话，group by后面的字段必须在select后面出现，不然会报错的，而mysql却不会。

10，mysql存储引擎有好多，常用的mysiam,innodb等，而创建oracle表的时候，不要这样的，好像只有一个存储引擎。

11，oracle字段无法选择位置，alter table add column before|after，这样会报错的，即使你用sql\*plus这样的工具，也没法改字段的位置。

12，oracle的表字段类型也没有mysql多，并且有很多不同，例如：mysql的int,float合成了oracle的number型等。

13，oracle查询时from 表名后面 不能加上[as][] 不然会报错的，select t.username from test as t而在mysql下是可以的。

14，oracle中是没有substring这个函数的，mysql有的。

参考博客：[https://www.cnblogs.com/TaoLeonis/p/7043543.html][https_www.cnblogs.com_TaoLeonis_p_7043543.html]

--------------------

数据库索引

合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

mysql数据中有多种索引类型，primary key，unique，normal，但底层存储的数据结构都是BTREE；有些存储引擎还提供hash索引，全文索引。

BTREE是最常见的优化要面对的索引结构，都是基于BTREE的讨论。

--------------------

本篇文章参考博客：

[https://blog.csdn.net/weixin\_40087851/article/details/81978347][https_blog.csdn.net_weixin_40087851_article_details_81978347]

[https://www.cnblogs.com/imyalost/p/6381703.html][https_www.cnblogs.com_imyalost_p_6381703.html]

[https://www.cnblogs.com/woniu-net/p/4770337.html][https_www.cnblogs.com_woniu-net_p_4770337.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70]: /images/20220411/9b95eaa8cb00409a8ce514717acf42fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 1]: /images/20220411/6b6b2671e88b439a905b08c2ec55723f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 2]: /images/20220411/a9f7792dbdd94aba9793ec038bc37cfd.png
[20181205224848661.PNG]: /images/20220411/50b4a5b90a8247cf8b63b5baf53e5943.png
[20181205224855913.PNG]: /images/20220411/6918c56ef19a4917aae62f366b56feb8.png
[https_www.cnblogs.com_zhuiluoyu_p_5822481.html]: https://www.cnblogs.com/zhuiluoyu/p/5822481.html
[0]: https://www.baidu.com/s?wd=%E4%BB%8E0%E5%BC%80%E5%A7%8B&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[20181206185655792.png]: /images/20220411/60f626c467494d6588dfb7ac90cbea9f.png
[20181206185706793.png]: /images/20220411/a0e8bfa4a6bd4a27a13a24070ab72e71.png
[20181206185712469.png]: /images/20220411/f5a4ded0da974a6ebabd621f8bdb8902.png
[20181206185716582.png]: /images/20220411/48bd0f4548db4d52bc5b6cf8bf7b07de.png
[20181206185722374.png]: /images/20220411/56c75ccba4f746ce8db5706e66f1d192.png
[20181206185348778.png]: /images/20220411/de839e10598647cf9e83544196930be8.png
[Oracle]: http://lib.csdn.net/base/oracle
[mysql]: http://www.jzxue.com/shujuku/mysql/
[Link 1]: http://www.jzxue.com/tag/%B7%D6%D2%B3/
[select]: http://www.jzxue.com/tag/Select/
[sql]: http://www.jzxue.com/shujuku/mssqlserver/
[MySQL]: http://lib.csdn.net/base/mysql
[POST]: http://www.jzxue.com/tag/post/
[as]: http://www.jzxue.com/tag/FlashAS/
[https_www.cnblogs.com_TaoLeonis_p_7043543.html]: https://www.cnblogs.com/TaoLeonis/p/7043543.html
[https_blog.csdn.net_weixin_40087851_article_details_81978347]: https://blog.csdn.net/weixin_40087851/article/details/81978347
[https_www.cnblogs.com_imyalost_p_6381703.html]: https://www.cnblogs.com/imyalost/p/6381703.html
[https_www.cnblogs.com_woniu-net_p_4770337.html]: https://www.cnblogs.com/woniu-net/p/4770337.html