mySQL面试题

比较难的查询语句

查询人数最多的部门信息。
   select * from dept where deptno=() 
    select deptno from emp group by deptnoorder bycount(deptno) desc limit 1；

DECIMAL(N,M)中M值的是小数部分的位数，若插入的值未指定小数部分或者小数部分不足M位则会自动补到M位小数，若插入的值小数部分超过了M为则会发生截断，截取前M位小数。N值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过N-M位，否则不能成功插入，会报超出范围的错误

在MYSQL中使用GROUP BY对表中的数据进行分组时，

GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里，
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。

MySQL的时间函数？

now(),获得当前的时间（执行sql时候的函数）

sysdata(),获得当前的时间（执行到此的函数的时间）

时间加减函数

date_add(date, interval expr unit)
date_sub(date, interval expr unit)

把时间转换为字符串（按照制定的格式）

data_formart（data，'%Y%m%d'）

1、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别

char是一种固定长度的类型，varchar则是一种可变长度的类型 
　　char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）． 

(2)、varchar(50)中50的涵义

最大存储30个字符；varchar（5）和（200）存储hello所占空间一样，但后者在排序时会消耗更多内存

(3)、int（20）中20的涵义

20表示最大显示宽度为20，但仍占4个字节存储，存储范围不变；

(4)、mysql为什么这么设计

1.单列索引

1-1)    普通索引,这个是最基本的索引,

其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

第一种方式 :

  CREATE INDEX account_Index ON `award`(`account`);

第二种方式: 

ALTER TABLE award ADD INDEX account_Index(`account`)

如果是CHAR,VARCHAR,类型,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度,

1-2)    唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

1-3)    主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

2.mysql 中的行转列

CREATETABLE`TEST_TB_GRADE` (  `ID`int(10)NOTNULLAUTO_INCREMENT,  `USER_NAME`varchar(20)DEFAULTNULL,  `COURSE`varchar(20)DEFAULTNULL,  `SCORE`floatDEFAULT'0',  PRIMARYKEY(`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;

insertintoTEST_TB_GRADE(USER_NAME, COURSE, SCORE)values ("张三","数学", 34), ("张三","语文", 58), ("张三","英语", 58), ("李四","数学", 45), ("李四","语文", 87), ("李四","英语", 45), ("王五","数学", 76), ("王五","语文", 34), ("王五","英语", 89);

       查询语句：

       此处用之所以用MAX是为了将无数据的点设为0，防止出现NULL

SELECTuser_name ,   MAX(CASEcourseWHEN'数学'THENscoreELSE0END) 数学,   MAX(CASEcourseWHEN'语文'THENscoreELSE0END) 语文,   MAX(CASEcourseWHEN'英语'THENscoreELSE0END) 英语 FROMtest_tb_grade GROUPBYUSER_NAME;

http://www.jb51.net/article/109203.htm 例子的来源

https://www.cnblogs.com/prefect/p/5746624.html更好的例子

3. 主键 超键 候选键 外键

主 键：

数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值

不能缺失，即不能为空值（Null）。

超 键：

在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：

是最小超键，即没有冗余元素的超键。

外 键：

在一个表中存在的另一个表的主键称此表的外键。

4.数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致

性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不

可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务

开始前的状态，就像这个事务从来没有执行过一样。

一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。、

隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两

个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统

中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混

淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。

持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并

不会被回滚。

5.什么是sql注入 ？如何防止sql注入？

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令，比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击.

比如在一个登录界面，要求输入用户名和密码：

可以这样输入实现免帐号登录：

用户名： ‘or 1 = 1 –

密 码：

点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了.(当然现在的有些语言的数据库API已经处理了这些问题)

这是为什么呢? 下面我们分析一下：

从理论上说，后台认证程序中会有如下的SQL语句：

String sql = "select * from user_table where username=

' "+userName+" ' and password=' "+password+" '";

当输入了上面的用户名和密码，上面的SQL语句变成：

SELECT * FROM user_table WHERE username=

'’or 1 = 1 -- and password='’

分析SQL语句：

条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。

这还是比较温柔的，如果是执行

SELECT * FROM user_table WHERE

username='' ;DROP DATABASE (DB Name) --' and password=''

….其后果可想而知… 

6.如何防止sql注入？？

1.（简单又有效的方法）PreparedStatement

采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX方法传值即可。

使用好处：

(1).代码的可读性和可维护性.

(2).PreparedStatement尽最大可能提高性能.

(3).最重要的一点是极大地提高了安全性.

原理：

sql注入只对sql语句的准备(编译)过程有破坏作用

而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,

而不再对sql语句进行解析,准备,因此也就避免了sql注入问题. 

2.使用正则表达式过滤传入的参数

要引入的包：

import java.util.regex.*;

正则表达式：

private String CHECKSQL = “^(.+)\\sand\\s(.+)|(.+)\\sor(.+)\\s$”;

判断是否匹配：

Pattern.matches(CHECKSQL,targerStr);

下面是具体的正则表达式：

检测SQL meta-characters的正则表达式 ：

/(\%27)|(\’)|(\-\-)|(\%23)|(#)/ix

修正检测SQL meta-characters的正则表达式 ：/((\%3D)|(=))[^\n]*((\%27)|(\’)|(\-\-)|(\%3B)|(:))/i

典型的SQL 注入攻击的正则表达式 ：/\w*((\%27)|(\’))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix

检测SQL注入，UNION查询关键字的正则表达式 ：/((\%27)|(\’))union/ix(\%27)|(\’)

检测MS SQL Server SQL注入攻击的正则表达式：

/exec(\s|\+)+(s|x)p\w+/ix

等等…..

总的说来，防范一般的SQL注入只要在代码规范上下点功夫就可以了。

凡涉及到执行的SQL中有变量时，用JDBC（或者其他数据持久层）提供的如：PreparedStatement就可以 ，切记不要用拼接字符串的方法就可以了。