sql面试题

1.sql关键字顺序与解析

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

	select  查询结果
	from  从哪张表里查找
	join     on        联合查询
	where  查询条件
	group by  分组
	having 对分组结果指定条件
	order by  对查询结果排序

2.case表达式

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

		select a.课程号,b.课程名称,
		sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
		sum(case when 成绩 >=70 and 成绩<85 then 1 else 0 end) as '[85-70]',
		sum(case when 成绩>=60 and 成绩<70  then 1 else 0 end) as '[70-60]',
		sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
		from score as a right join course as b 
		on a.课程号=b.课程号
		group by a.课程号,b.课程名称;

行列转换

		select 学号, 
		sum(case when 课程号 = 0001 then 成绩  else 0 end) as 课程号0001,
		sum(case when 课程号 = 0002 then 成绩  else 0 end) as 课程号0002,
		sum(case when 课程号 = 0003 then 成绩  else 0 end) as 课程号0003
		from score
		group by 学号

3.插入数据

普通插入

insert into tb(列1， 列2，。。。)
values(值1，值2，。。。)

将存在表中的列插入到其它存在表的存在列中去

INSERT INTO 目标表(目标表列名)
SELECT 源表列名表
FROM 源表

将现有表中的数据插入到新表中

SELECT 源表列名
INTO 目标表
FROM 源表

合并数据进行多行插入

INSERT INTO 目标表(目标表列名)
SELECT 列名表1 UNION
SELECT 列名表2 UNION
......
SELECT 列名表n

4.更新数据

普通更新

update 表名 set 列名=值  where  筛选条件

运用子查询更新

update device d, 
(select id from device where id not in(select min(t.id) from device t group by t.device_id)) b 
set d.scene_id=null 
where d.id=b.id;

用表b更新表a

update utd_gb_info a,(SELECT DISTINCT gbPn, imgInfo FROM utd_gb_info WHERE imgInfo!='' AND gbPn !='')b
set a.imgInfo=b.imgInfo
where a.gbPn=b.gbPn and a.imgInfo = '';

5.查看数据表每个字段的注释

SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE 
A.name = '表名'

6.理论基础

1.为什么要设置主键
给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是「平衡树」结构，换句话说，就是整个表就变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

2.为什么要设置自增主键
mysql 在频繁的更新、删除操作，会产生碎片。而含碎片比较大的表，查询效率会降低，自增主键会减少产生的碎片

1. 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页，这样就会形成一个紧凑的索引结构，近似顺序填满

2. 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面.

3.为什么索引能加快查询速度

1. DB在执行一条语句时，默认的方式是根据搜寻条件进行全表扫描，遇到匹配的就加入到搜索结果集合。如果对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
2. 添加索引的话，首先去索引列表中查询，而我们的索引列表是B类树的数据结构，查询的时间复杂度为O(log2N)，定位到特定值得行就会非常快，所以其查询速度就会非常快。
3. 索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。