SQL 常见面试题

1. 删除冗余信息

自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001 张三 0001     数学    69
2        2005002 李四 0001      数学    89
3        2005001 张三 0001      数学    69
删除除了自动编号不同, 其他都相同的学生冗余信息

答案：delete from tablename where id not in (
select t.id from 
(select min(id) as id from xuanke group by snum,cnum,score) as t 
)

注释：这里必须加一个中间表，否则会报：You can't specify target table 'tempA' for update in FROM clause

2.自连接查询

怎么把这样一个表儿
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

疑问：month 为什么不需要加表的别名？（我是小白 求大神解答。。。。）

3.复制表( 只复制结构, 源表名：a新表名：b) 

SQL: select * into b from a where 1<>1       (where1=1，拷贝表结构和数据内容)
（Oracle:create table b

As

Select * from a where 1=2）

4 说明：拷贝表( 拷贝数据, 源表名：a目标表名：b) 

SQL: insert into b(a, b, c) select d,e,f from a; 

5.关联查询

curse表：                               xuanke表：

  

 

为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 Java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------

答案1(mysql)：select x.cnum,cname,score ,
case when score>=60 then 'pass' else 'false' end as mark
from xuanke x  join course c 
on x.cnum=c.cnum

（case when then else end 的使用）

答案二(mysql)：select x.cnum,cname,score,
if(score>=60,'pass','fail') mark 
from xuanke x join course c
on x.cnum=c.cnum

（if(表达式，true，false) 的使用）

6.外连接查询（及ifnul()函数用法）

create table testtable1
(
id int IDENTITY,
department varchar(12) 
)

select * from testtable1
insert into testtable1 values('设计')
insert into testtable1 values('市场')
insert into testtable1 values('售后')
/*
结果
id department
1   设计
2   市场
3   售后 
*/
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'张三')
insert into testtable2 values(1,'李四')
insert into testtable2 values(2,'王五')
insert into testtable2 values(3,'彭六')
insert into testtable2 values(4,'陈七')
/*
用一条SQL语句，怎么显示如下结果
id dptID department name
1   1      设计        张三
2   1      设计        李四
3   2      市场        王五
4   3      售后        彭六
5   4      黑人        陈七
*/

答案：

SELECT testtable2.id,testtable2.deptID,IFNULL(department,'黑人') department,testtable2.name 
FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

注释：

mysql中：

1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0

2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替

3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为   expr1。

原文：

sqlserver 中有isnull的函数，那么mysql中对应的函数是什么呢？

虽然mysql中也有isnull这个函数，但其使用方法与sqlserver有所不同。其对应的函数应该为ifnull。

7.外键约束的添加

• 方式一：在创建表的时候进行添加
• 方式二：表已经创建好了，继续修改表的结构来添加外键

方式一：

[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)

REFERENCES tbl_name (主表的字段2)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION} 

例子：create table t2 (id int ,t1id int,name varchar(255), constraint foeign_key_name foreign key (t1id) references t1 (id))

注释：外键必须是主表的的主键，外键不能是从表的主键。

方式二：

ALTER TABLE table1 ADD FOREIGN KEY(dept_id) REFERENCES table2(id); 

8.巧妙利用聚合函数

有表A，结构如下： 
A: p_ID p_Num s_id 
1 10 01 
1 12 02 
2 8 01 
3 11 01 
3 8 03 
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为： 
p_ID s1_id s2_id s3_id 
1 10 12 0 
2 8 0 0 
3 11 0 8 
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

结果：

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id